<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateTahfidzReports extends Migration
{
    public function up()
    {
        $this->db->query('
            CREATE TABLE IF NOT EXISTS `tahfidz_student_reports` (
              `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
              `program_id`   BIGINT UNSIGNED NOT NULL,
              `student_id`   BIGINT UNSIGNED NOT NULL,
              `fashahah`     TINYINT UNSIGNED NOT NULL,
              `tajwid`       TINYINT UNSIGNED NOT NULL,
              `adab`         TINYINT UNSIGNED NOT NULL,
              `catatan`      TEXT NULL,
              `created_by`   BIGINT UNSIGNED NOT NULL,
              `created_at`   DATETIME NULL,
              `updated_at`   DATETIME NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `uq_student_program_report` (`program_id`, `student_id`),
              CONSTRAINT `fk_report_program`
                FOREIGN KEY (`program_id`) REFERENCES `tahfidz_programs` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `fk_report_student`
                FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
                ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ');
    }

    public function down()
    {
        $this->db->query('DROP TABLE IF EXISTS `tahfidz_student_reports`');
    }
}
