<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

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

        $this->createPaymentBackupTable();
        $this->ensurePaymentAuditColumns();
        $this->repairPaymentSequences();
        $this->normalizePaymentsFromInvoices();
        $this->ensureRepairTables();
        $this->ensurePaymentIndexes();
    }

    public function down()
    {
        $this->dropIndexIfExists('payments', 'uniq_payments_invoice_sequence');
        $this->dropIndexIfExists('payments', 'uniq_payments_idempotency_key');
        $this->dropIndexIfExists('payments', 'idx_payments_reversal');

        foreach ([
            'payment_repair_decisions',
            'payment_repair_transition_review',
            'payment_repair_invoice_review',
            'payment_repair_analysis',
            'invoice_adjustments',
            'payments_backup_20260718',
        ] as $table) {
            if ($this->db->tableExists($table)) {
                $this->forge->dropTable($table, true);
            }
        }

        foreach ([
            'idempotency_key',
            'is_void',
            'reversal_of_payment_id',
            'voided_at',
            'voided_by',
            'void_reason',
        ] as $column) {
            if ($this->db->fieldExists($column, 'payments')) {
                $this->forge->dropColumn('payments', $column);
            }
        }
    }

    private function createPaymentBackupTable(): void
    {
        if ($this->db->tableExists('payments_backup_20260718')) {
            return;
        }

        $this->db->query('CREATE TABLE `payments_backup_20260718` AS SELECT * FROM `payments`');
    }

    private function ensurePaymentAuditColumns(): void
    {
        $columns = [];

        if (!$this->db->fieldExists('idempotency_key', 'payments')) {
            $columns['idempotency_key'] = [
                'type' => 'VARCHAR',
                'constraint' => 100,
                'null' => true,
                'after' => 'transaction_id',
            ];
        } else {
            $this->forge->modifyColumn('payments', [
                'idempotency_key' => [
                    'type' => 'VARCHAR',
                    'constraint' => 100,
                    'null' => true,
                ],
            ]);
        }

        if (!$this->db->fieldExists('request_fingerprint_hash', 'payments')) {
            $columns['request_fingerprint_hash'] = [
                'type' => 'CHAR',
                'constraint' => 64,
                'null' => true,
                'after' => 'idempotency_key',
            ];
        }

        if (!$this->db->fieldExists('is_void', 'payments')) {
            $columns['is_void'] = [
                'type' => 'TINYINT',
                'constraint' => 1,
                'default' => 0,
                'null' => false,
                'after' => 'status',
            ];
        }

        if (!$this->db->fieldExists('evidence_status', 'payments')) {
            $columns['evidence_status'] = [
                'type' => 'VARCHAR',
                'constraint' => 30,
                'null' => true,
                'after' => 'check_file',
            ];
        }

        if (!$this->db->fieldExists('evidence_failure_message', 'payments')) {
            $columns['evidence_failure_message'] = [
                'type' => 'VARCHAR',
                'constraint' => 255,
                'null' => true,
                'after' => 'evidence_status',
            ];
        }

        if (!$this->db->fieldExists('reversal_of_payment_id', 'payments')) {
            $columns['reversal_of_payment_id'] = [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => true,
                'after' => 'is_void',
            ];
        }

        if (!$this->db->fieldExists('voided_at', 'payments')) {
            $columns['voided_at'] = [
                'type' => 'DATETIME',
                'null' => true,
                'after' => 'reversal_of_payment_id',
            ];
        }

        if (!$this->db->fieldExists('voided_by', 'payments')) {
            $columns['voided_by'] = [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => true,
                'after' => 'voided_at',
            ];
        }

        if (!$this->db->fieldExists('void_reason', 'payments')) {
            $columns['void_reason'] = [
                'type' => 'VARCHAR',
                'constraint' => 255,
                'null' => true,
                'after' => 'voided_by',
            ];
        }

        if ($columns !== []) {
            $this->forge->addColumn('payments', $columns);
        }
    }

    private function repairPaymentSequences(): void
    {
        if (!$this->db->fieldExists('installment_seq', 'payments')) {
            $this->forge->addColumn('payments', [
                'installment_seq' => [
                    'type' => 'INT',
                    'constraint' => 11,
                    'null' => true,
                    'after' => 'number_of_installments',
                ],
            ]);
        }

        $rows = $this->db->table('payments')
            ->select('id, invoice_id, installment_seq')
            ->orderBy('invoice_id', 'ASC')
            ->orderBy('payment_date', 'ASC')
            ->orderBy('id', 'ASC')
            ->get()
            ->getResultArray();

        $sequenceByInvoice = [];
        foreach ($rows as $row) {
            $invoiceId = (int) ($row['invoice_id'] ?? 0);
            $sequenceByInvoice[$invoiceId] = ($sequenceByInvoice[$invoiceId] ?? 0) + 1;
            $expectedSeq = $sequenceByInvoice[$invoiceId];

            if ((int) ($row['installment_seq'] ?? 0) === $expectedSeq) {
                continue;
            }

            $this->db->table('payments')
                ->where('id', (int) $row['id'])
                ->update(['installment_seq' => $expectedSeq]);
        }

        if ($this->db->fieldExists('number_of_installments', 'payments')) {
            $this->db->query(
                "UPDATE `payments` p
                 JOIN (
                    SELECT invoice_id, COUNT(*) AS installment_count
                    FROM `payments`
                    WHERE LOWER(COALESCE(status, '')) NOT IN (
                        'void', 'voided', 'refunded', 'failed', 'chargeback',
                        'declined', 'reversed', 'canceled', 'cancelled'
                    )
                    GROUP BY invoice_id
                 ) c ON c.invoice_id = p.invoice_id
                 SET p.`number_of_installments` = c.installment_count"
            );
        }
    }

    private function normalizePaymentsFromInvoices(): void
    {
        if (!$this->db->tableExists('invoices')) {
            return;
        }

        $this->db->query(
            'UPDATE `payments` p
             JOIN `invoices` i ON i.`id` = p.`invoice_id`
             SET p.`parent_id` = i.`parent_id`,
                 p.`school_year` = i.`school_year`
             WHERE p.`parent_id` <> i.`parent_id`
                OR NOT (p.`school_year` <=> i.`school_year`)'
        );
    }

    private function ensureRepairTables(): void
    {
        foreach ([
            'payment_repair_transition_review',
            'payment_repair_invoice_review',
            'payment_repair_analysis',
        ] as $view) {
            $this->db->query(sprintf('DROP VIEW IF EXISTS `%s`', $view));
        }

        $this->db->query(
            "CREATE TABLE IF NOT EXISTS `invoice_adjustments` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `invoice_id` INT UNSIGNED NOT NULL,
                `adjustment_type` ENUM('charge','discount','waiver','credit','reversal','correction') NOT NULL,
                `amount` DECIMAL(10,2) NOT NULL,
                `description` VARCHAR(255) NOT NULL,
                `source_reference` VARCHAR(100) DEFAULT NULL,
                `school_year` VARCHAR(9) NOT NULL,
                `created_by` INT UNSIGNED DEFAULT NULL,
                `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_invoice_adjustments_invoice` (`invoice_id`),
                UNIQUE KEY `uniq_invoice_adjustment_source` (`invoice_id`, `source_reference`)
            ) ENGINE=InnoDB"
        );

        $this->db->query(
            "CREATE TABLE IF NOT EXISTS `payment_repair_decisions` (
                `payment_id` INT UNSIGNED NOT NULL,
                `invoice_id` INT UNSIGNED NOT NULL,
                `decision` ENUM(
                    'balance_is_wrong',
                    'missing_payment',
                    'missing_credit',
                    'missing_charge',
                    'missing_reversal',
                    'leave_unchanged'
                ) NOT NULL,
                `correction_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
                `evidence_reference` VARCHAR(255) NOT NULL,
                `approved_by` INT UNSIGNED NOT NULL,
                `approved_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `notes` TEXT,
                PRIMARY KEY (`payment_id`)
            ) ENGINE=InnoDB"
        );

        $this->db->query(
            "CREATE TABLE IF NOT EXISTS `payment_repair_analysis` (
                `payment_id` INT UNSIGNED NOT NULL,
                `invoice_id` INT UNSIGNED NOT NULL,
                `paid_amount` DECIMAL(10,2) NOT NULL,
                `balance` DECIMAL(10,2) DEFAULT NULL,
                `payment_date` DATETIME NOT NULL,
                `installment_seq` INT DEFAULT NULL,
                `expected_installment_seq` INT NOT NULL,
                `running_paid_total` DECIMAL(10,2) NOT NULL,
                PRIMARY KEY (`payment_id`),
                KEY `idx_payment_repair_analysis_invoice` (`invoice_id`)
            ) ENGINE=InnoDB"
        );

        $this->db->query(
            "CREATE TABLE IF NOT EXISTS `payment_repair_invoice_review` (
                `invoice_id` INT UNSIGNED NOT NULL,
                `payment_count` INT NOT NULL,
                `ledger_paid_total` DECIMAL(10,2) NOT NULL,
                `sequence_matches` TINYINT(1) NOT NULL,
                PRIMARY KEY (`invoice_id`)
            ) ENGINE=InnoDB"
        );

        $this->db->query(
            "CREATE TABLE IF NOT EXISTS `payment_repair_transition_review` (
                `payment_id` INT UNSIGNED NOT NULL,
                `invoice_id` INT UNSIGNED NOT NULL,
                `balance` DECIMAL(10,2) DEFAULT NULL,
                `previous_balance` DECIMAL(10,2) DEFAULT NULL,
                `paid_amount` DECIMAL(10,2) NOT NULL,
                `expected_balance` DECIMAL(10,2) DEFAULT NULL,
                `unexplained_change` DECIMAL(10,2) DEFAULT NULL,
                PRIMARY KEY (`payment_id`),
                KEY `idx_payment_repair_transition_invoice` (`invoice_id`)
            ) ENGINE=InnoDB"
        );

        $this->refreshRepairTables();
    }

    private function refreshRepairTables(): void
    {
        $this->db->table('payment_repair_transition_review')->truncate();
        $this->db->table('payment_repair_invoice_review')->truncate();
        $this->db->table('payment_repair_analysis')->truncate();

        $payments = $this->db->table('payments')
            ->select('id, invoice_id, paid_amount, balance, payment_date, installment_seq')
            ->orderBy('invoice_id', 'ASC')
            ->orderBy('payment_date', 'ASC')
            ->orderBy('id', 'ASC')
            ->get()
            ->getResultArray();

        $sequenceByInvoice = [];
        $runningByInvoice = [];
        $invoiceReview = [];
        $previousByInvoice = [];

        foreach ($payments as $payment) {
            $invoiceId = (int) ($payment['invoice_id'] ?? 0);
            $paidAmount = round((float) ($payment['paid_amount'] ?? 0), 2);
            $balance = array_key_exists('balance', $payment) && $payment['balance'] !== null
                ? round((float) $payment['balance'], 2)
                : null;

            $sequenceByInvoice[$invoiceId] = ($sequenceByInvoice[$invoiceId] ?? 0) + 1;
            $runningByInvoice[$invoiceId] = round(($runningByInvoice[$invoiceId] ?? 0.0) + $paidAmount, 2);

            $expectedSeq = $sequenceByInvoice[$invoiceId];
            $actualSeq = $payment['installment_seq'] !== null ? (int) $payment['installment_seq'] : null;

            $this->db->table('payment_repair_analysis')->insert([
                'payment_id' => (int) $payment['id'],
                'invoice_id' => $invoiceId,
                'paid_amount' => $paidAmount,
                'balance' => $balance,
                'payment_date' => $payment['payment_date'],
                'installment_seq' => $actualSeq,
                'expected_installment_seq' => $expectedSeq,
                'running_paid_total' => $runningByInvoice[$invoiceId],
            ]);

            if (!isset($invoiceReview[$invoiceId])) {
                $invoiceReview[$invoiceId] = [
                    'payment_count' => 0,
                    'ledger_paid_total' => 0.0,
                    'sequence_matches' => 1,
                ];
            }

            $invoiceReview[$invoiceId]['payment_count']++;
            $invoiceReview[$invoiceId]['ledger_paid_total'] = round($invoiceReview[$invoiceId]['ledger_paid_total'] + $paidAmount, 2);
            if ($actualSeq !== $expectedSeq) {
                $invoiceReview[$invoiceId]['sequence_matches'] = 0;
            }

            if (isset($previousByInvoice[$invoiceId])) {
                $previous = $previousByInvoice[$invoiceId];
                $expectedBalance = $previous['balance'] !== null
                    ? round($previous['balance'] - $paidAmount, 2)
                    : null;
                $unexplainedChange = ($balance !== null && $expectedBalance !== null)
                    ? round($balance - $expectedBalance, 2)
                    : null;

                if ($unexplainedChange !== null && abs($unexplainedChange) > 0.01) {
                    $this->db->table('payment_repair_transition_review')->insert([
                        'payment_id' => (int) $payment['id'],
                        'invoice_id' => $invoiceId,
                        'balance' => $balance,
                        'previous_balance' => $previous['balance'],
                        'paid_amount' => $paidAmount,
                        'expected_balance' => $expectedBalance,
                        'unexplained_change' => $unexplainedChange,
                    ]);
                }
            }

            $previousByInvoice[$invoiceId] = [
                'balance' => $balance,
            ];
        }

        foreach ($invoiceReview as $invoiceId => $review) {
            $this->db->table('payment_repair_invoice_review')->insert([
                'invoice_id' => $invoiceId,
                'payment_count' => $review['payment_count'],
                'ledger_paid_total' => $review['ledger_paid_total'],
                'sequence_matches' => $review['sequence_matches'],
            ]);
        }
    }

    private function ensurePaymentIndexes(): void
    {
        $this->addIndexIfMissing('payments', 'idx_payments_reversal', ['reversal_of_payment_id']);
        $this->addIndexIfMissing('payments', 'uniq_payments_idempotency_key', ['idempotency_key'], true);

        if (!$this->hasDuplicatePaymentSequences()) {
            $this->addIndexIfMissing('payments', 'uniq_payments_invoice_sequence', ['invoice_id', 'installment_seq'], true);
        }
    }

    private function hasDuplicatePaymentSequences(): bool
    {
        $row = $this->db->table('payments')
            ->select('invoice_id, installment_seq')
            ->where('installment_seq IS NOT NULL', null, false)
            ->groupBy('invoice_id, installment_seq')
            ->having('COUNT(*) >', 1, false)
            ->get(1)
            ->getRowArray();

        return $row !== null;
    }

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

        $quotedColumns = implode(', ', array_map(static fn (string $column): string => '`' . $column . '`', $columns));
        $type = $unique ? 'UNIQUE INDEX' : 'INDEX';
        $this->db->query(sprintf('ALTER TABLE `%s` ADD %s `%s` (%s)', $table, $type, $indexName, $quotedColumns));
    }

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

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

    private function indexExists(string $table, string $indexName): bool
    {
        if (!$this->db->tableExists($table)) {
            return false;
        }

        $rows = $this->db->query(sprintf('SHOW INDEX FROM `%s`', $table))->getResultArray();
        foreach ($rows as $row) {
            if (($row['Key_name'] ?? null) === $indexName) {
                return true;
            }
        }

        return false;
    }
}