<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * 
     * Creates the questionnaire_answers table to store medical questionnaire responses
     * for insurance applicants aged 60+. This table supports the dynamic questionnaire
     * system with multiple steps and conditional logic.
     */
    public function up(): void
    {
        Schema::create('questionnaire_answers', function (Blueprint $table) {
            // Primary Key
            $table->ulid('id')->primary();
            
            // Foreign Keys
            $table->foreignUlid('person_id')
                  ->constrained('people')
                  ->onDelete('cascade')
                  ->onUpdate('cascade')
                  ->comment('The insured person who filled the questionnaire');
            
            $table->foreignUlid('inquiry_id')
                  ->constrained('inquiries')
                  ->onDelete('cascade')
                  ->onUpdate('cascade')
                  ->comment('Related insurance inquiry');
            
            $table->foreignId('questionnaire_config_id')
                  ->nullable()
                  ->constrained('questionnaire_configs')
                  ->onDelete('set null')
                  ->onUpdate('cascade')
                  ->comment('Questionnaire configuration used');
            
            // Step 1: Eligibility Check Answers (JSON storage for flexibility)
            $table->json('step1_answers')->nullable()->comment('Step 1 - Eligibility check answers');
            $table->boolean('step1_passed')->nullable()->comment('Whether step 1 was passed');
            $table->text('step1_rejection_reason')->nullable()->comment('Reason for failing step 1');
            
            // Step 2: C Rate Trigger Answers
            $table->json('step2_answers')->nullable()->comment('Step 2 - C Rate trigger answers');
            $table->boolean('step2_triggered_c_rate')->nullable()->comment('Whether step 2 triggered C rate');
            
            // Step 3: B Rate Trigger Answers
            $table->json('step3_answers')->nullable()->comment('Step 3 - B Rate trigger answers');
            $table->boolean('step3_triggered_b_rate')->nullable()->comment('Whether step 3 triggered B rate');
            
            // Step 4: Smoking and Additional Questions
            $table->json('step4_answers')->nullable()->comment('Step 4 - Smoking and additional questions');
            $table->boolean('is_smoker')->default(false)->comment('Whether the person is a smoker');
            $table->integer('years_smoking')->nullable()->comment('Years of smoking (if applicable)');
            $table->boolean('uses_vaping')->default(false)->comment('Whether uses vaping products');
            
            // Questionnaire Results
            $table->enum('status', [
                'pending',      // Questionnaire not started
                'in_progress',  // Partially completed
                'completed',    // All steps completed
                'approved',     // Passed all checks
                'rejected',     // Failed eligibility
                'requires_review' // Needs manual review
            ])->default('pending')->comment('Questionnaire completion status');
            
            $table->enum('rate_category', ['A', 'B', 'C'])->nullable()->comment('Final rate category determined');
            $table->decimal('rate_adjustment_factor', 5, 2)->default(1.00)->comment('Premium adjustment factor');
            $table->text('rejection_reason')->nullable()->comment('Overall rejection reason');
            $table->text('review_notes')->nullable()->comment('Notes from manual review');
            
            // Timestamps
            $table->timestamp('started_at')->nullable()->comment('When questionnaire was started');
            $table->timestamp('completed_at')->nullable()->comment('When questionnaire was completed');
            $table->timestamp('reviewed_at')->nullable()->comment('When questionnaire was reviewed');
            $table->timestamps();
            
            // Indexes for performance
            $table->index(['person_id', 'status']);
            $table->index(['inquiry_id', 'status']);
            $table->index(['rate_category']);
            $table->index(['completed_at']);
            $table->index(['status', 'completed_at']);
            
            // Unique constraint to prevent duplicate questionnaires for same person
            $table->unique(['person_id', 'inquiry_id'], 'unique_person_inquiry_questionnaire');
        });
        
        // Add comments to the table
        DB::statement("COMMENT ON TABLE questionnaire_answers IS 'Stores medical questionnaire responses for insurance applicants aged 60+ with support for Manulife and other insurance company requirements'");
        
        // Create a view for quick questionnaire status checking
        DB::statement("
            CREATE OR REPLACE VIEW questionnaire_summary_view AS
            SELECT 
                qa.id,
                qa.person_id,
                qa.inquiry_id,
                qa.status,
                qa.rate_category,
                qa.rate_adjustment_factor,
                qa.completed_at,
                p.first_name,
                p.last_name,
                p.calculated_age,
                i.reference_number as inquiry_reference,
                i.is_entry as insurance_type,
                a.name as agent_name
            FROM questionnaire_answers qa
            JOIN people p ON qa.person_id = p.id
            JOIN inquiries i ON qa.inquiry_id = i.id
            LEFT JOIN agents a ON i.agent_id = a.id
        ");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Drop the view first
        DB::statement('DROP VIEW IF EXISTS questionnaire_summary_view');
        
        // Drop the table
        Schema::dropIfExists('questionnaire_answers');
    }
};