<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateStudentGradesTable extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id'                 => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'student_id'         => ['type' => 'BIGINT', 'unsigned' => true],
            'classroom_id'       => ['type' => 'BIGINT', 'unsigned' => true],
            'academic_period_id' => ['type' => 'BIGINT', 'unsigned' => true],
            'school_subject_id'  => ['type' => 'BIGINT', 'unsigned' => true],  // FK ke school_subjects
            'score'              => ['type' => 'DECIMAL', 'constraint' => '5,2', 'null' => true],
            'import_batch'       => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'created_at'         => ['type' => 'DATETIME', 'null' => true],
            'updated_at'         => ['type' => 'DATETIME', 'null' => true],
        ]);

        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('student_id',         'students',         'id', 'CASCADE',  'CASCADE');
        $this->forge->addForeignKey('classroom_id',       'classrooms',       'id', 'CASCADE',  'CASCADE');
        $this->forge->addForeignKey('academic_period_id', 'academic_periods', 'id', 'CASCADE',  'CASCADE');
        $this->forge->addForeignKey('school_subject_id',  'school_subjects',  'id', 'CASCADE',  'CASCADE');

        // 1 siswa, 1 mapel, 1 periode = 1 nilai
        $this->forge->addUniqueKey(['student_id', 'academic_period_id', 'school_subject_id']);

        $this->forge->createTable('student_grades');
    }

    public function down()
    {
        $this->forge->dropTable('student_grades');
    }
}