<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddClassroomConditionBuckets extends Migration
{
    public function up()
    {
        $db = \Config\Database::connect();

        // --- Ensure columns exist on inventory_items ---
        $cols = $db->getFieldNames('inventory_items');

        // 1) Academic tagging
        $addAcademic = [];
        if (!in_array('school_year', $cols, true)) {
            $addAcademic['school_year'] = ['type' => 'VARCHAR', 'constraint' => 16, 'null' => true];
        }
        if (!in_array('semester', $cols, true)) {
            $addAcademic['semester'] = ['type' => 'ENUM("Spring","Fall")', 'null' => true];
        }
        if ($addAcademic) {
            $this->forge->addColumn('inventory_items', $addAcademic);
        }

        // 2) Condition buckets (classroom)
        $addBuckets = [];
        if (!in_array('good_qty', $cols, true))         $addBuckets['good_qty']          = ['type'=>'INT','unsigned'=>true,'default'=>0,'null'=>false,'after'=>'quantity'];
        if (!in_array('needs_repair_qty', $cols, true)) $addBuckets['needs_repair_qty']  = ['type'=>'INT','unsigned'=>true,'default'=>0,'null'=>false,'after'=>'good_qty'];
        if (!in_array('need_replace_qty', $cols, true)) $addBuckets['need_replace_qty']  = ['type'=>'INT','unsigned'=>true,'default'=>0,'null'=>false,'after'=>'needs_repair_qty'];
        if (!in_array('cannot_find_qty', $cols, true))  $addBuckets['cannot_find_qty']   = ['type'=>'INT','unsigned'=>true,'default'=>0,'null'=>false,'after'=>'need_replace_qty'];

        if ($addBuckets) {
            $this->forge->addColumn('inventory_items', $addBuckets);
        }

        // 3) Backfill school_year/semester from configuration (or fallback)
        [$sem, $yr] = $this->resolveCurrentAcademic($db);

        // Only fill when missing
        $db->query("
            UPDATE inventory_items
            SET school_year = COALESCE(NULLIF(school_year, ''), ?),
                semester    = COALESCE(semester, ?)
            WHERE school_year IS NULL OR school_year = '' OR semester IS NULL
        ", [$yr, $sem]);

        // 4) Backfill buckets for classroom rows (first-time init)
        $db->query("
            UPDATE inventory_items
            SET
                good_qty         = quantity,
                needs_repair_qty = COALESCE(needs_repair_qty,0),
                need_replace_qty = COALESCE(need_replace_qty,0),
                cannot_find_qty  = COALESCE(cannot_find_qty,0)
            WHERE type='classroom'
              AND (COALESCE(good_qty,0) = 0
                   AND COALESCE(needs_repair_qty,0) = 0
                   AND COALESCE(need_replace_qty,0) = 0
                   AND COALESCE(cannot_find_qty,0) = 0)
        ");

        // 5) Add index on (school_year, semester) if not present
        $idx = $db->query("
            SELECT 1
            FROM information_schema.statistics
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME   = 'inventory_items'
              AND INDEX_NAME   = 'idx_items_schoolyear_semester'
            LIMIT 1
        ")->getFirstRow();

        if (!$idx) {
            $db->query("ALTER TABLE inventory_items ADD INDEX idx_items_schoolyear_semester (school_year, semester)");
        }
    }

    public function down()
    {
        // Buckets can be dropped safely
        try { $this->forge->dropColumn('inventory_items', 'good_qty'); } catch (\Throwable $e) {}
        try { $this->forge->dropColumn('inventory_items', 'needs_repair_qty'); } catch (\Throwable $e) {}
        try { $this->forge->dropColumn('inventory_items', 'need_replace_qty'); } catch (\Throwable $e) {}
        try { $this->forge->dropColumn('inventory_items', 'cannot_find_qty'); } catch (\Throwable $e) {}

        // school_year/semester may be used elsewhere; drop only if you really want to
        try { $this->forge->dropColumn('inventory_items', 'school_year'); } catch (\Throwable $e) {}
        try { $this->forge->dropColumn('inventory_items', 'semester'); } catch (\Throwable $e) {}

        // Drop index if it exists
        $db = \Config\Database::connect();
        try { $db->query("ALTER TABLE inventory_items DROP INDEX idx_items_schoolyear_semester"); } catch (\Throwable $e) {}
    }

    /**
     * Resolve current academic year/semester from configuration table,
     * fallback to date-based inference if not set.
     */
    private function resolveCurrentAcademic($db): array
    {
        $sem = null; $yr = null;

        if ($db->tableExists('configuration')) {
            $sem = $db->table('configuration')->select('config_value')
                ->where('config_key','semester')->get()->getRow('config_value');
            $yr  = $db->table('configuration')->select('config_value')
                ->where('config_key','school_year')->get()->getRow('config_value');
        }

        if (!$sem || !$yr) {
            $now = new \DateTime('now', new \DateTimeZone('America/New_York'));
            $y = (int)$now->format('Y');
            $m = (int)$now->format('n');

            // Adjust these rules if your academic calendar differs
            $sem = $sem ?: (($m >= 2 && $m <= 5) ? 'Spring' : 'Fall');
            $yr  = $yr  ?: (($m >= 6) ? sprintf('%d-%d', $y, $y+1) : sprintf('%d-%d', $y-1, $y));
        }

        return [$sem, $yr];
    }
}