<?php

declare(strict_types=1);

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;
use RuntimeException;

final class AlignSchemaToScoolViewDump extends Migration
{
    /**
     * Columns present in school_prod.sql.zip but absent from scool_view.sql.zip.
     *
     * This migration intentionally makes the live database match the scool_view
     * dump captured on 2026-07-17.
     */
    private array $columnsToDrop = [
        'classSection' => ['semester'],
        'contactus' => ['semester'],
        'discount_vouchers' => ['semester'],
        'emergency_contacts' => ['semester', 'school_year'],
        'ip_attempts' => ['semester'],
        'login_activity' => ['semester'],
        'notification_recipients' => ['semester'],
        'notifications' => ['semester'],
        'parents' => ['semester', 'school_year'],
        'payment_error' => ['semester'],
        'payment_transactions' => ['semester'],
        'payments' => ['semester'],
        'paypal_transactions' => ['semester'],
        'preferences' => ['school_year'],
        'refunds' => ['semester'],
        'reimbursement_batch_items' => ['semester'],
        'reimbursement_batches' => ['semester'],
        'reimbursements' => ['semester'],
        'students' => ['semester', 'school_year'],
        'support_requests' => ['semester'],
        'user_notifications' => ['semester'],
        'users' => ['semester', 'school_year'],
        'whatsapp_group_links' => ['semester'],
        'whatsapp_group_memberships' => ['semester'],
    ];

    /**
     * Tables that should own a required school_year value after aligning the schema.
     *
     * @var list<string>
     */
    private array $schoolYearTables = [
        'admin_notification_subjects',
        'attendance_comment_template',
        'class_progress_attachments',
        'competition_class_winners',
        'competition_scores',
        'competition_winners',
        'inventory_categories',
        'paypal_payments',
        'placement_scores',
        'promotion_queue',
        'qcmquestions',
        'school_year_closing_batches',
        'school_year_closing_items',
        'school_year_transition_logs',
        'staff',
        'whatsapp_group_links',
        'whatsapp_invites_log',
    ];

    public function up(): void
    {
        if ($this->db->DBDriver !== 'MySQLi') {
            throw new RuntimeException('This migration requires MySQL/MariaDB through the MySQLi driver.');
        }

        $this->dropIndexIfExists('competition_winners', 'competition_id_class_section_id_rank');
        $this->dropIndexIfExists('refunds', 'idx_refunds_parent_year_semester_status');
        $this->dropIndexIfExists('teacher_class', 'idx_sy_teacher_class_school_year_223511c0');
        $this->dropIndexIfExists('whatsapp_group_links', 'uq_section_term');
        $this->dropIndexIfExists('whatsapp_group_memberships', 'uniq_whatsapp_membership');
        $this->dropIndexIfExists('whatsapp_group_memberships', 'class_section_id_school_year_semester');

        foreach ($this->columnsToDrop as $table => $columns) {
            foreach ($columns as $column) {
                $this->dropColumnIfExists($table, $column);
            }
        }

        $this->db->resetDataCache();

        foreach ($this->schoolYearTables as $table) {
            $this->ensureRequiredSchoolYearColumn($table);
        }

        $this->db->resetDataCache();

        $this->ensureIndex(
            'refunds',
            'idx_refunds_parent_year_semester_status',
            ['parent_id', 'school_year', 'status']
        );

        $this->ensureIndex(
            'teacher_class',
            'unique_teacher_assignment',
            ['teacher_id', 'class_section_id', 'school_year'],
            true
        );

        $this->ensureIndex(
            'whatsapp_group_links',
            'uq_section_term',
            ['class_section_id', 'school_year'],
            true
        );

        $this->ensureIndex(
            'whatsapp_group_memberships',
            'uniq_whatsapp_membership',
            ['class_section_id', 'school_year', 'subject_type', 'subject_id'],
            true
        );

        $this->ensureIndex(
            'whatsapp_group_memberships',
            'class_section_id_school_year_semester',
            ['class_section_id', 'school_year']
        );

        if ($this->db->tableExists('whatsapp_group_links')) {
            $this->db->query(
                'ALTER TABLE `whatsapp_group_links` ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci'
            );
        }
    }

    public function down(): void
    {
        throw new RuntimeException(
            'This migration is intentionally irreversible because matching scool_view drops columns and their data.'
        );
    }

    private function ensureRequiredSchoolYearColumn(string $table): void
    {
        if (! $this->db->tableExists($table)) {
            return;
        }

        if (! $this->db->fieldExists('school_year', $table)) {
            $this->forge->addColumn($table, [
                'school_year' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 9,
                    'null'       => true,
                ],
            ]);
        }

        $year = $this->currentSchoolYear();
        $this->db->query(
            sprintf(
                'UPDATE %s SET `school_year` = ? WHERE `school_year` IS NULL OR TRIM(`school_year`) = \'\'',
                $this->quoteIdentifier($table)
            ),
            [$year]
        );

        $this->db->query(sprintf(
            'ALTER TABLE %s MODIFY `school_year` VARCHAR(9) NOT NULL',
            $this->quoteIdentifier($table)
        ));
    }

    private function currentSchoolYear(): string
    {
        if ($this->db->tableExists('configuration')) {
            $row = $this->db->table('configuration')
                ->select('config_value')
                ->whereIn('config_key', ['school_year', 'current_school_year'])
                ->where('config_value IS NOT NULL', null, false)
                ->where('TRIM(config_value) <>', '')
                ->orderBy("FIELD(config_key, 'school_year', 'current_school_year')", '', false)
                ->get(1)
                ->getRowArray();

            if (isset($row['config_value']) && preg_match('/^\d{4}-\d{4}$/', (string) $row['config_value'])) {
                return (string) $row['config_value'];
            }
        }

        $year = (int) date('Y');

        return $year . '-' . ($year + 1);
    }

    private function dropColumnIfExists(string $table, string $column): void
    {
        if (! $this->db->tableExists($table) || ! $this->db->fieldExists($column, $table)) {
            return;
        }

        $this->dropForeignKeysContainingColumn($table, $column);
        $this->dropIndexesContainingColumn($table, $column);
        $this->dropChecksContainingColumn($table, $column);

        $this->forge->dropColumn($table, $column);
    }

    private function ensureIndex(string $table, string $index, array $columns, bool $unique = false): void
    {
        if (! $this->db->tableExists($table) || ! $this->hasColumns($table, $columns)) {
            return;
        }

        if ($this->indexExists($table, $index)) {
            return;
        }

        $keyword = $unique ? 'UNIQUE INDEX' : 'INDEX';
        $columnList = implode(', ', array_map([$this, 'quoteIdentifier'], $columns));

        $this->db->query(sprintf(
            'ALTER TABLE %s ADD %s %s (%s)',
            $this->quoteIdentifier($table),
            $keyword,
            $this->quoteIdentifier($index),
            $columnList
        ));
    }

    private function dropIndexIfExists(string $table, string $index): void
    {
        if (! $this->db->tableExists($table) || ! $this->indexExists($table, $index)) {
            return;
        }

        $this->db->query(sprintf(
            'ALTER TABLE %s DROP INDEX %s',
            $this->quoteIdentifier($table),
            $this->quoteIdentifier($index)
        ));
    }

    private function dropIndexesContainingColumn(string $table, string $column): void
    {
        $indexes = $this->db->query(
            'SELECT DISTINCT INDEX_NAME
             FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND COLUMN_NAME = ?
               AND INDEX_NAME <> \'PRIMARY\'',
            [$table, $column]
        )->getResult();

        foreach ($indexes as $index) {
            $this->dropIndexIfExists($table, $index->INDEX_NAME);
        }
    }

    private function dropForeignKeysContainingColumn(string $table, string $column): void
    {
        $constraints = $this->db->query(
            'SELECT DISTINCT CONSTRAINT_NAME
             FROM information_schema.KEY_COLUMN_USAGE
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND COLUMN_NAME = ?
               AND REFERENCED_TABLE_NAME IS NOT NULL',
            [$table, $column]
        )->getResult();

        foreach ($constraints as $constraint) {
            $this->db->query(sprintf(
                'ALTER TABLE %s DROP FOREIGN KEY %s',
                $this->quoteIdentifier($table),
                $this->quoteIdentifier($constraint->CONSTRAINT_NAME)
            ));
        }
    }

    private function dropChecksContainingColumn(string $table, string $column): void
    {
        $checks = $this->db->query(
            'SELECT tc.CONSTRAINT_NAME
             FROM information_schema.TABLE_CONSTRAINTS tc
             INNER JOIN information_schema.CHECK_CONSTRAINTS cc
                ON cc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
               AND cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
             WHERE tc.CONSTRAINT_SCHEMA = DATABASE()
               AND tc.TABLE_NAME = ?
               AND tc.CONSTRAINT_TYPE = \'CHECK\'
               AND cc.CHECK_CLAUSE LIKE ?',
            [$table, '%' . $column . '%']
        )->getResult();

        foreach ($checks as $check) {
            $this->db->query(sprintf(
                'ALTER TABLE %s DROP CHECK %s',
                $this->quoteIdentifier($table),
                $this->quoteIdentifier($check->CONSTRAINT_NAME)
            ));
        }
    }

    private function indexExists(string $table, string $index): bool
    {
        $row = $this->db->query(
            'SELECT COUNT(*) AS aggregate
             FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND INDEX_NAME = ?',
            [$table, $index]
        )->getRow();

        return (int) ($row->aggregate ?? 0) > 0;
    }

    private function hasColumns(string $table, array $columns): bool
    {
        foreach ($columns as $column) {
            if (! $this->db->fieldExists($column, $table)) {
                return false;
            }
        }

        return true;
    }

    private function quoteIdentifier(string $identifier): string
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }
}