<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateAssessmentsTable extends Migration
{
    public function up()
    {
        // Cek apakah tabel sudah ada
        if ($this->db->tableExists('assessment_types')) {
            return;
        }

        // 1. Tabel assessment_types
        $this->forge->addField([
            'id'          => ['type' => 'TINYINT', 'constraint' => 2, 'unsigned' => true, 'auto_increment' => true],
            'name'        => ['type' => 'VARCHAR', 'constraint' => 50],
            'code'        => ['type' => 'VARCHAR', 'constraint' => 10],
            'created_by'  => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'is_active'   => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->createTable('assessment_types');

        // 2. Tabel assessments
        $this->forge->addField([
            'id'                   => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'assessment_type_id'   => ['type' => 'TINYINT', 'unsigned' => true],
            'name'                 => ['type' => 'VARCHAR', 'constraint' => 100],
            'description'          => ['type' => 'TEXT', 'null' => true],
            'academic_period_id'   => ['type' => 'BIGINT', 'unsigned' => true],
            'subject_id'           => ['type' => 'BIGINT', 'unsigned' => true],
            'classroom_id'         => ['type' => 'BIGINT', 'unsigned' => true],
            'teacher_id'           => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'start_date'           => ['type' => 'DATE', 'null' => true],
            'end_date'             => ['type' => 'DATE', 'null' => true],
            'is_open'              => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'is_visible'           => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_by'           => ['type' => 'BIGINT', 'unsigned' => true],
            'created_at'           => ['type' => 'DATETIME', 'null' => true],
            'updated_at'           => ['type' => 'DATETIME', 'null' => true],
            'deleted_at'           => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('assessment_type_id', 'assessment_types', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('academic_period_id', 'academic_periods', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('subject_id', 'school_subjects', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('classroom_id', 'classrooms', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('teacher_id', 'teachers', 'id', 'SET NULL', 'CASCADE');
        $this->forge->addForeignKey('created_by', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('assessments');

        // 3. Tabel assessment_settings
        $this->forge->addField([
            'id'                   => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'assessment_id'        => ['type' => 'BIGINT', 'unsigned' => true],
            'kkm'                  => ['type' => 'TINYINT', 'default' => 75],
            'weight_multiple_choice' => ['type' => 'TINYINT', 'default' => 100],
            'weight_essay'         => ['type' => 'TINYINT', 'default' => 0],
            'answer_key'           => ['type' => 'TEXT', 'null' => true],
            'total_questions'      => ['type' => 'SMALLINT', 'default' => 0],
            'essay_mode'           => ['type' => 'TINYINT', 'default' => 0],
            'total_essay_questions'=> ['type' => 'SMALLINT', 'default' => 0],
            'created_at'           => ['type' => 'DATETIME', 'null' => true],
            'updated_at'           => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('assessment_id', 'assessments', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('assessment_settings');

        // 4. Tabel assessment_scores
        $this->forge->addField([
            'id'                   => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'assessment_id'        => ['type' => 'BIGINT', 'unsigned' => true],
            'student_id'           => ['type' => 'BIGINT', 'unsigned' => true],
            'multiple_choice_score'=> ['type' => 'TINYINT', 'null' => true],
            'essay_score'          => ['type' => 'TINYINT', 'null' => true],
            'final_score'          => ['type' => 'TINYINT', 'null' => true],
            'answers'              => ['type' => 'TEXT', 'null' => true],
            'is_complete'          => ['type' => 'TINYINT', 'default' => 0],
            'graded_by'            => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'graded_at'            => ['type' => 'DATETIME', 'null' => true],
            'created_at'           => ['type' => 'DATETIME', 'null' => true],
            'updated_at'           => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['assessment_id', 'student_id']);
        $this->forge->addForeignKey('assessment_id', 'assessments', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('student_id', 'students', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('graded_by', 'users', 'id', 'SET NULL', 'CASCADE');
        $this->forge->createTable('assessment_scores');

        // 5. Tabel assessment_essay_details
        $this->forge->addField([
            'id'                   => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'score_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'question_number'      => ['type' => 'SMALLINT'],
            'score'                => ['type' => 'DECIMAL', 'constraint' => '5,2', 'null' => true],
            'created_at'           => ['type' => 'DATETIME', 'null' => true],
            'updated_at'           => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('score_id', 'assessment_scores', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('assessment_essay_details');

        // Seed data
        $this->db->table('assessment_types')->insertBatch([
            ['name' => 'Asesmen Sumatif Harian', 'code' => 'ASH', 'created_by' => 2],
            ['name' => 'Asesmen Sumatif Tengah Semester', 'code' => 'ASTS', 'created_by' => 1],
            ['name' => 'Asesmen Sumatif Akhir Semester', 'code' => 'ASAS', 'created_by' => 1],
            ['name' => 'Asesmen Sumatif Akhir Tahun', 'code' => 'ASAT', 'created_by' => 1],
        ]);
    }

    public function down()
    {
        $this->forge->dropTable('assessment_essay_details');
        $this->forge->dropTable('assessment_scores');
        $this->forge->dropTable('assessment_settings');
        $this->forge->dropTable('assessments');
        $this->forge->dropTable('assessment_types');
    }
}