<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('applications', function (Blueprint $table) {
            // Add new fields for comprehensive job application (only if they don't exist)
            // Note: Using after() clauses removed to avoid issues when columns are added conditionally
            if (!$this->hasColumn('applications', 'first_name')) {
                $table->string('first_name')->nullable();
            }
            if (!$this->hasColumn('applications', 'last_name')) {
                $table->string('last_name')->nullable();
            }
            if (!$this->hasColumn('applications', 'email')) {
                $table->string('email')->nullable();
            }
            if (!$this->hasColumn('applications', 'phone')) {
                $table->string('phone')->nullable();
            }
            if (!$this->hasColumn('applications', 'resume_path')) {
                $table->string('resume_path')->nullable(); // Path to resume file
            }
            if (!$this->hasColumn('applications', 'resume_url')) {
                $table->string('resume_url')->nullable(); // Public URL to resume
            }
            if (!$this->hasColumn('applications', 'cover_letter')) {
                $table->text('cover_letter')->nullable();
            }
            if (!$this->hasColumn('applications', 'experience_level')) {
                $table->string('experience_level')->nullable();
            }
            if (!$this->hasColumn('applications', 'available_date')) {
                $table->date('available_date')->nullable();
            }
            if (!$this->hasColumn('applications', 'notice_period')) {
                $table->string('notice_period')->nullable();
            }
            if (!$this->hasColumn('applications', 'salary_expectation')) {
                $table->unsignedInteger('salary_expectation')->nullable();
            }
            if (!$this->hasColumn('applications', 'remote_interested')) {
                $table->boolean('remote_interested')->default(false);
            }
            if (!$this->hasColumn('applications', 'additional_answers')) {
                $table->json('additional_answers')->nullable();
            }
            if (!$this->hasColumn('applications', 'consent_data')) {
                $table->boolean('consent_data')->default(false);
            }
            if (!$this->hasColumn('applications', 'consent_marketing')) {
                $table->boolean('consent_marketing')->default(false);
            }
            
            // Update status enum to match API spec (only if status column exists and needs updating)
            if ($this->hasColumn('applications', 'status')) {
                try {
                    $table->enum('status', ['applied', 'reviewing', 'shortlisted', 'rejected', 'hired'])->default('applied')->change();
                } catch (\Exception $e) {
                    // Status enum might already be updated, ignore error
                }
            }
            
            // Add indexes (only if they don't exist)
            if (!$this->hasIndex('applications', 'applications_job_post_id_status_index')) {
                $table->index(['job_post_id', 'status'], 'applications_job_post_id_status_index');
            }
            if (!$this->hasIndex('applications', 'applications_email_index')) {
                $table->index('email', 'applications_email_index');
            }
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('applications', function (Blueprint $table) {
            // Drop indexes if they exist
            if ($this->hasIndex('applications', 'applications_email_index')) {
                $table->dropIndex('applications_email_index');
            }
            if ($this->hasIndex('applications', 'applications_job_post_id_status_index')) {
                $table->dropIndex('applications_job_post_id_status_index');
            }
            
            $table->dropColumn([
                'first_name',
                'last_name',
                'email',
                'phone',
                'resume_path',
                'resume_url',
                'cover_letter',
                'experience_level',
                'available_date',
                'notice_period',
                'salary_expectation',
                'remote_interested',
                'additional_answers',
                'consent_data',
                'consent_marketing',
            ]);
        });
    }
    
    /**
     * Check if a column exists on a table
     */
    private function hasColumn(string $table, string $column): bool
    {
        $databaseName = DB::getDatabaseName();
        
        $result = DB::select(
            "SELECT COUNT(*) as count 
             FROM information_schema.columns 
             WHERE table_schema = ? 
             AND table_name = ? 
             AND column_name = ?",
            [$databaseName, $table, $column]
        );
        
        return $result[0]->count > 0;
    }
    
    /**
     * Check if an index exists on a table
     */
    private function hasIndex(string $table, string $index): bool
    {
        $databaseName = DB::getDatabaseName();
        
        $result = DB::select(
            "SELECT COUNT(*) as count 
             FROM information_schema.statistics 
             WHERE table_schema = ? 
             AND table_name = ? 
             AND index_name = ?",
            [$databaseName, $table, $index]
        );
        
        return $result[0]->count > 0;
    }
};
