<?php

declare(strict_types=1);

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;
use RuntimeException;

final class ApplySchoolYearSemesterAuditCorrections extends Migration
{
    private array $schoolYearIndexTables = [
        'archived_paypal_transactions',
        'class_progress_reports',
        'exams',
        'missing_score_overrides',
        'payments',
        'placement_batches',
        'print_requests',
        'report_card_acknowledgements',
        'semester_scores',
        'staff_attendance',
        'teacher_attendance_data',
        'whatsapp_group_links',
    ];

    private array $semesterDropTables = [
        'badge_print_logs',
        'classes',
        'contactus',
        'emergency_contacts',
        'inventory_items',
        'invoice_students_list',
        'ip_attempts',
        'notification_recipients',
        'notifications',
        'parents',
        'support_requests',
        'user_notifications',
        'whatsapp_group_links',
    ];

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

        if ($this->db->tableExists('archived_paypal_transactions')
            && $this->db->fieldExists('school_year', 'archived_paypal_transactions')) {
            $this->assertValidSchoolYears('archived_paypal_transactions', 'school_year');
            $this->normalizeYearColumn('archived_paypal_transactions', 'school_year');
            $this->ensureYearCheckConstraint('archived_paypal_transactions', 'school_year');
        }

        foreach ($this->schoolYearIndexTables as $table) {
            if (! $this->db->tableExists($table) || ! $this->db->fieldExists('school_year', $table)) {
                continue;
            }

            $this->ensureIndex($table, 'school_year');
        }

        // Preserve legacy semester columns. They may be redundant for newer
        // code paths, but migration must not delete restored production data.

        foreach ($this->schoolYearIndexTables as $table) {
            if (! $this->db->tableExists($table) || ! $this->db->fieldExists('school_year', $table)) {
                continue;
            }

            $this->ensureIndex($table, 'school_year');
        }
    }

    public function down(): void
    {
        throw new RuntimeException(
            'This migration is intentionally irreversible because dropping semester columns destroys data.'
        );
    }

    private function assertValidSchoolYears(string $table, string $column): void
    {
        $tableName = $this->quoteIdentifier($table);
        $columnName = $this->quoteIdentifier($column);

        $invalid = $this->db->query(
            "SELECT COUNT(*) AS aggregate
             FROM {$tableName}
             WHERE {$columnName} IS NOT NULL
               AND (
                    {$columnName} NOT REGEXP '^[0-9]{4}-[0-9]{4}$'
                    OR CAST(RIGHT({$columnName}, 4) AS UNSIGNED)
                       <> CAST(LEFT({$columnName}, 4) AS UNSIGNED) + 1
               )"
        )->getRow();

        if ((int) ($invalid->aggregate ?? 0) > 0) {
            throw new RuntimeException(
                "Cannot migrate {$table}.{$column}: found {$invalid->aggregate} invalid school-year value(s)."
            );
        }
    }

    private function normalizeYearColumn(string $table, string $column): void
    {
        $metadata = $this->db->query(
            'SELECT IS_NULLABLE, COLLATION_NAME
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND COLUMN_NAME = ?',
            [$table, $column]
        )->getRow();

        if ($metadata === null) {
            return;
        }

        $nullable = $metadata->IS_NULLABLE === 'YES' ? 'NULL' : 'NOT NULL';
        $collation = $metadata->COLLATION_NAME && preg_match('/^[A-Za-z0-9_]+$/', $metadata->COLLATION_NAME)
            ? ' COLLATE ' . $metadata->COLLATION_NAME
            : '';

        $this->db->query(sprintf(
            'ALTER TABLE %s MODIFY COLUMN %s VARCHAR(9)%s %s',
            $this->quoteIdentifier($table),
            $this->quoteIdentifier($column),
            $collation,
            $nullable
        ));
    }

    private function ensureYearCheckConstraint(string $table, string $column): void
    {
        $constraint = $this->objectName('chk_sy', $table, $column);

        $exists = $this->db->query(
            'SELECT COUNT(*) AS aggregate
             FROM information_schema.TABLE_CONSTRAINTS
             WHERE CONSTRAINT_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND CONSTRAINT_NAME = ?
               AND CONSTRAINT_TYPE = \'CHECK\'',
            [$table, $constraint]
        )->getRow();

        if ((int) ($exists->aggregate ?? 0) > 0) {
            return;
        }

        $columnName = $this->quoteIdentifier($column);

        $this->db->query(sprintf(
            "ALTER TABLE %s ADD CONSTRAINT %s CHECK (
                %s IS NULL OR (
                    %s REGEXP '^[0-9]{4}-[0-9]{4}$'
                    AND CAST(RIGHT(%s, 4) AS UNSIGNED)
                        = CAST(LEFT(%s, 4) AS UNSIGNED) + 1
                )
            )",
            $this->quoteIdentifier($table),
            $this->quoteIdentifier($constraint),
            $columnName,
            $columnName,
            $columnName,
            $columnName
        ));
    }

    private function ensureIndex(string $table, string $column): void
    {
        $indexed = $this->db->query(
            'SELECT COUNT(*) AS aggregate
             FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = ?
               AND COLUMN_NAME = ?',
            [$table, $column]
        )->getRow();

        if ((int) ($indexed->aggregate ?? 0) > 0) {
            return;
        }

        $this->db->query(sprintf(
            'ALTER TABLE %s ADD INDEX %s (%s)',
            $this->quoteIdentifier($table),
            $this->quoteIdentifier($this->objectName('idx_sy', $table, $column)),
            $this->quoteIdentifier($column)
        ));
    }

    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->db->query(sprintf(
                'ALTER TABLE %s DROP INDEX %s',
                $this->quoteIdentifier($table),
                $this->quoteIdentifier($index->INDEX_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 objectName(string $prefix, string $table, string $column): string
    {
        return substr($prefix . '_' . $table . '_' . $column . '_' . substr(sha1($table . '.' . $column), 0, 8), 0, 64);
    }

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