<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // เพิ่ม comment ให้คอลัมภ์ที่ไม่มี Foreign Key ก่อน
        DB::statement("ALTER TABLE answers MODIFY COLUMN id BIGINT UNSIGNED AUTO_INCREMENT COMMENT 'รหัสคำตอบ'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN answer_text TEXT NULL DEFAULT NULL COMMENT 'ข้อความคำตอบ'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN image_path VARCHAR(255) NULL DEFAULT NULL COMMENT 'เส้นทางไฟล์รูปภาพ'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN comment TEXT NULL DEFAULT NULL COMMENT 'ความคิดเห็นเพิ่มเติม'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN DtmIns TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'วันที่และเวลาที่สร้างข้อมูล'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN DtmUpd TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'วันที่และเวลาที่แก้ไขข้อมูล'");

        // จัดการคอลัมภ์ที่มี Foreign Key Constraint
        // ลบ Foreign Key ก่อน (ใช้ชื่อที่ถูกต้อง)
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_assessment_id_foreign");
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_question_id_foreign");
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_cusins_id_foreign");
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_cusudp_id_foreign");
        
        // เพิ่ม comment ให้คอลัมภ์ที่มี Foreign Key
        DB::statement("ALTER TABLE answers MODIFY COLUMN assessment_id BIGINT UNSIGNED COMMENT 'รหัสการประเมิน'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN question_id BIGINT UNSIGNED COMMENT 'รหัสคำถาม'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN CusIns_ID INT UNSIGNED COMMENT 'รหัสลูกค้าที่สร้างข้อมูล'");
        DB::statement("ALTER TABLE answers MODIFY COLUMN CusUdp_ID INT UNSIGNED COMMENT 'รหัสลูกค้าที่แก้ไขข้อมูล'");
        
        // เพิ่ม Foreign Key กลับมา (ใช้ชื่อและกฎเดิม)
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_assessment_id_foreign FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE");
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_question_id_foreign FOREIGN KEY (question_id) REFERENCES sas_questions(id) ON DELETE CASCADE");
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_cusins_id_foreign FOREIGN KEY (CusIns_ID) REFERENCES customer(Customer_ID) ON DELETE CASCADE");
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_cusudp_id_foreign FOREIGN KEY (CusUdp_ID) REFERENCES customer(Customer_ID) ON DELETE CASCADE");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // ลบ Foreign Key ก่อน (ใช้ชื่อที่ถูกต้อง)
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_assessment_id_foreign");
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_question_id_foreign");
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_cusins_id_foreign");
        DB::statement("ALTER TABLE answers DROP FOREIGN KEY answers_cusudp_id_foreign");
        
        // ลบ comment โดยไม่ระบุ comment
        DB::statement("ALTER TABLE answers MODIFY COLUMN id BIGINT UNSIGNED AUTO_INCREMENT");
        DB::statement("ALTER TABLE answers MODIFY COLUMN assessment_id BIGINT UNSIGNED");
        DB::statement("ALTER TABLE answers MODIFY COLUMN question_id BIGINT UNSIGNED");
        DB::statement("ALTER TABLE answers MODIFY COLUMN answer_text TEXT NULL DEFAULT NULL");
        DB::statement("ALTER TABLE answers MODIFY COLUMN image_path VARCHAR(255) NULL DEFAULT NULL");
        DB::statement("ALTER TABLE answers MODIFY COLUMN comment TEXT NULL DEFAULT NULL");
        DB::statement("ALTER TABLE answers MODIFY COLUMN DtmIns TIMESTAMP DEFAULT CURRENT_TIMESTAMP");
        DB::statement("ALTER TABLE answers MODIFY COLUMN DtmUpd TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        DB::statement("ALTER TABLE answers MODIFY COLUMN CusIns_ID INT UNSIGNED");
        DB::statement("ALTER TABLE answers MODIFY COLUMN CusUdp_ID INT UNSIGNED");
        
        // เพิ่ม Foreign Key กลับมา (ใช้ชื่อและกฎเดิม)
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_assessment_id_foreign FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE");
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_question_id_foreign FOREIGN KEY (question_id) REFERENCES sas_questions(id) ON DELETE CASCADE");
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_cusins_id_foreign FOREIGN KEY (CusIns_ID) REFERENCES customer(Customer_ID) ON DELETE CASCADE");
        DB::statement("ALTER TABLE answers ADD CONSTRAINT answers_cusudp_id_foreign FOREIGN KEY (CusUdp_ID) REFERENCES customer(Customer_ID) ON DELETE CASCADE");
    }
};
