<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddClassSectionIdToScoreComments extends Migration
{
    public function up()
    {
        if (!$this->db->tableExists('score_comments')) {
            return;
        }

        $fields = $this->db->getFieldNames('score_comments');

        if (!in_array('class_section_id', $fields, true)) {
            $this->forge->addColumn('score_comments', [
                'class_section_id' => [
                    'type'       => 'INT',
                    'constraint' => 11,
                    'unsigned'   => true,
                    'null'       => true,
                    'after'      => 'student_id',
                ],
            ]);
            try {
                $this->db->query('CREATE INDEX idx_score_comments_class_section ON score_comments(class_section_id)');
            } catch (\Throwable $e) {
                // Index may already exist; ignore
            }
        }

        if (in_array('subject_id', $fields, true)) {
            try {
                $this->forge->dropColumn('score_comments', 'subject_id');
            } catch (\Throwable $e) {
                // ignore if drop fails
            }
        }
    }

    public function down()
    {
        if (!$this->db->tableExists('score_comments')) {
            return;
        }

        $fields = $this->db->getFieldNames('score_comments');

        if (!in_array('subject_id', $fields, true)) {
            $this->forge->addColumn('score_comments', [
                'subject_id' => [
                    'type'       => 'INT',
                    'constraint' => 11,
                    'unsigned'   => true,
                    'null'       => true,
                    'after'      => 'student_id',
                ],
            ]);
        }

        if (in_array('class_section_id', $fields, true)) {
            try {
                $this->forge->dropColumn('score_comments', 'class_section_id');
            } catch (\Throwable $e) {
                // ignore if drop fails
            }
        }
    }
}
