<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddRefundSourcesAndPayouts extends Migration
{
    public function up()
    {
        $this->ensureRefundSourceColumns();
        $this->ensureRefundPayoutsTable();
        $this->ensureRefundPayoutSchoolYearColumn();
        $this->ensureRefundPayoutFingerprintColumns();
        $this->backfillRefundSources();
        $this->backfillLegacyPayouts();
    }

    public function down()
    {
        if ($this->db->tableExists('refund_payouts')) {
            $this->forge->dropTable('refund_payouts', true);
        }

        if ($this->db->tableExists('refunds')) {
            foreach (['source_type', 'source_id', 'requested_amount_cents', 'approved_amount_cents', 'currency'] as $column) {
                if ($this->db->fieldExists($column, 'refunds')) {
                    $this->forge->dropColumn('refunds', $column);
                }
            }
        }
    }

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

        $columns = [];
        if (!$this->db->fieldExists('source_type', 'refunds')) {
            $columns['source_type'] = ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true, 'after' => 'request'];
        }
        if (!$this->db->fieldExists('source_id', 'refunds')) {
            $columns['source_id'] = ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true, 'after' => 'source_type'];
        }
        if (!$this->db->fieldExists('requested_amount_cents', 'refunds')) {
            $columns['requested_amount_cents'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'refund_amount'];
        }
        if (!$this->db->fieldExists('approved_amount_cents', 'refunds')) {
            $columns['approved_amount_cents'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'requested_amount_cents'];
        }
        if (!$this->db->fieldExists('currency', 'refunds')) {
            $columns['currency'] = ['type' => 'CHAR', 'constraint' => 3, 'null' => false, 'default' => 'USD', 'after' => 'approved_amount_cents'];
        }

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

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

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'refund_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => false],
            'school_year' => ['type' => 'VARCHAR', 'constraint' => 9, 'null' => false],
            'amount_cents' => ['type' => 'INT', 'constraint' => 11, 'null' => false],
            'currency' => ['type' => 'CHAR', 'constraint' => 3, 'null' => false, 'default' => 'USD'],
            'payout_type' => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => false, 'default' => 'cash_out'],
            'payment_method' => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
            'status' => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => false],
            'external_reference' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'check_number' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'check_date' => ['type' => 'DATE', 'null' => true],
            'evidence_path' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'idempotency_key' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => false],
            'operation_type' => ['type' => 'VARCHAR', 'constraint' => 50, 'null' => true],
            'request_fingerprint_hash' => ['type' => 'CHAR', 'constraint' => 64, 'null' => true],
            'processed_by' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'processed_at' => ['type' => 'DATETIME', 'null' => true],
            'reversed_payout_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'failure_code' => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'failure_message' => ['type' => 'TEXT', 'null' => true],
            'created_at' => ['type' => 'DATETIME', 'null' => false],
            'updated_at' => ['type' => 'DATETIME', 'null' => false],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('refund_id');
        $this->forge->addKey('reversed_payout_id');
        $this->forge->addUniqueKey('idempotency_key', 'uniq_refund_payouts_idempotency_key');
        $this->forge->createTable('refund_payouts', true);
    }

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

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

        if ($this->db->tableExists('refunds')) {
            $this->db->query(
                "UPDATE refund_payouts rp
                 INNER JOIN refunds r ON r.id = rp.refund_id
                    SET rp.school_year = r.school_year
                  WHERE rp.school_year IS NULL OR TRIM(rp.school_year) = ''"
            );
        }

        $this->db->query("ALTER TABLE refund_payouts MODIFY school_year VARCHAR(9) NOT NULL");
    }

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

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

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

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

        $this->db->query(
            "UPDATE `refunds`
                SET `requested_amount_cents` = ROUND(COALESCE(`refund_amount`, 0) * 100),
                    `approved_amount_cents` = ROUND(COALESCE(`refund_amount`, 0) * 100),
                    `currency` = COALESCE(NULLIF(`currency`, ''), 'USD')
              WHERE `requested_amount_cents` IS NULL OR `approved_amount_cents` IS NULL"
        );

        if ($this->db->fieldExists('source_type', 'refunds')) {
            $this->db->query(
                "UPDATE `refunds`
                    SET `source_type` = CASE
                        WHEN `invoice_id` IS NOT NULL THEN 'invoice_overpayment'
                        WHEN LOWER(COALESCE(`request`, '')) = 'duplicate' THEN 'payment_duplicate'
                        ELSE 'administrative_credit'
                    END,
                    `source_id` = CASE
                        WHEN `invoice_id` IS NOT NULL THEN `invoice_id`
                        ELSE `source_id`
                    END
                  WHERE `source_type` IS NULL"
            );
        }
    }

    private function backfillLegacyPayouts(): void
    {
        if (!$this->db->tableExists('refunds') || !$this->db->tableExists('refund_payouts')) {
            return;
        }

        $refunds = $this->db->table('refunds')
            ->select('id, refund_paid_amount, currency, refund_method, check_nbr, check_file, school_year, refunded_at, updated_by, created_at, updated_at')
            ->where('refund_paid_amount >', 0)
            ->get()
            ->getResultArray();

        $now = date('Y-m-d H:i:s');
        foreach ($refunds as $refund) {
            $refundId = (int) ($refund['id'] ?? 0);
            if ($refundId <= 0) {
                continue;
            }
            $exists = $this->db->table('refund_payouts')
                ->where('refund_id', $refundId)
                ->where('idempotency_key', 'legacy-refund-' . $refundId)
                ->countAllResults();
            if ($exists > 0) {
                continue;
            }

            $this->db->table('refund_payouts')->insert([
                'refund_id' => $refundId,
                'school_year' => (string)($refund['school_year'] ?? ''),
                'amount_cents' => (int) round(((float) ($refund['refund_paid_amount'] ?? 0)) * 100),
                'currency' => (string) ($refund['currency'] ?? 'USD') ?: 'USD',
                'payout_type' => 'cash_out',
                'payment_method' => $refund['refund_method'] ?? null,
                'status' => 'completed',
                'external_reference' => 'legacy_import',
                'check_number' => $refund['check_nbr'] ?? null,
                'check_date' => null,
                'evidence_path' => $refund['check_file'] ?? null,
                'idempotency_key' => 'legacy-refund-' . $refundId,
                'operation_type' => 'refund_payout',
                'request_fingerprint_hash' => hash('sha256', json_encode([
                    'operation_type' => 'refund_payout',
                    'refund_id' => $refundId,
                    'amount_cents' => (int) round(((float) ($refund['refund_paid_amount'] ?? 0)) * 100),
                    'payment_method' => $refund['refund_method'] ?? null,
                    'currency' => (string) ($refund['currency'] ?? 'USD') ?: 'USD',
                    'external_reference' => 'legacy_import',
                ], JSON_UNESCAPED_SLASHES)),
                'processed_by' => $refund['updated_by'] ?? null,
                'processed_at' => $refund['refunded_at'] ?? $refund['updated_at'] ?? $now,
                'reversed_payout_id' => null,
                'failure_code' => null,
                'failure_message' => null,
                'created_at' => $refund['created_at'] ?? $now,
                'updated_at' => $refund['updated_at'] ?? $now,
            ]);
        }
    }
}