<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddGradeRangeToBookCategories extends Migration
{
    public function up()
    {
        $db   = \Config\Database::connect();
        $cols = $db->getFieldNames('inventory_categories');

        $fields = [];
        if (!in_array('grade_min', $cols, true)) {
            $fields['grade_min'] = ['type' => 'TINYINT', 'unsigned' => true, 'null' => true, 'after' => 'description'];
        }
        if (!in_array('grade_max', $cols, true)) {
            $fields['grade_max'] = ['type' => 'TINYINT', 'unsigned' => true, 'null' => true, 'after' => 'grade_min'];
        }

        if ($fields) {
            $this->forge->addColumn('inventory_categories', $fields);
        }

        // Optional: composite index to speed up book-category lookups by range
        $idx = $db->query("
            SELECT 1 FROM information_schema.statistics
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME='inventory_categories'
              AND INDEX_NAME='idx_cat_type_grade_range'
            LIMIT 1
        ")->getFirstRow();
        if (!$idx) {
            $db->query("ALTER TABLE inventory_categories ADD INDEX idx_cat_type_grade_range (type, grade_min, grade_max)");
        }
    }

    public function down()
    {
        $db = \Config\Database::connect();
        try { $db->query("ALTER TABLE inventory_categories DROP INDEX idx_cat_type_grade_range"); } catch (\Throwable $e) {}
        try { $this->forge->dropColumn('inventory_categories', 'grade_min'); } catch (\Throwable $e) {}
        try { $this->forge->dropColumn('inventory_categories', 'grade_max'); } catch (\Throwable $e) {}
    }
}
