alrahma_sunday_school/app/Database/Migrations/2026-07-19-000400_FinancialWorkflowHardening.php

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class FinancialWorkflowHardening extends Migration
{
    public function up()
    {
        $this->ensureDiscountUsageColumns();
        $this->ensureDiscountUsageUniqueness();
        $this->ensureAdditionalChargeLedgerColumns();
        $this->ensureInvoiceLineActiveSourceKey();
        $this->expandAdditionalChargeStatus();
    }

    public function down()
    {
        if ($this->db->tableExists('discount_usages')) {
            foreach (['requested_discount_cents', 'eligible_base_cents', 'eligible_base_before_cents', 'applied_discount_cents', 'application_order'] as $column) {
                if ($this->db->fieldExists($column, 'discount_usages')) {
                    $this->forge->dropColumn('discount_usages', $column);
                }
            }
        }
    }

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

        $columns = [];
        if (!$this->db->fieldExists('requested_discount_cents', 'discount_usages')) {
            $columns['requested_discount_cents'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'discount_amount'];
        }
        if (!$this->db->fieldExists('eligible_base_cents', 'discount_usages')) {
            $columns['eligible_base_cents'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'requested_discount_cents'];
        }
        if (!$this->db->fieldExists('eligible_base_before_cents', 'discount_usages')) {
            $columns['eligible_base_before_cents'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'eligible_base_cents'];
        }
        if (!$this->db->fieldExists('applied_discount_cents', 'discount_usages')) {
            $columns['applied_discount_cents'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'eligible_base_before_cents'];
        }
        if (!$this->db->fieldExists('application_order', 'discount_usages')) {
            $columns['application_order'] = ['type' => 'INT', 'constraint' => 11, 'null' => true, 'after' => 'applied_discount_cents'];
        }

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

        $this->db->query(
            "UPDATE discount_usages
                SET requested_discount_cents = COALESCE(requested_discount_cents, ROUND(COALESCE(discount_amount, 0) * 100)),
                    eligible_base_cents = COALESCE(eligible_base_cents, ROUND(COALESCE(discount_amount, 0) * 100)),
                    eligible_base_before_cents = COALESCE(eligible_base_before_cents, eligible_base_cents, ROUND(COALESCE(discount_amount, 0) * 100)),
                    applied_discount_cents = COALESCE(applied_discount_cents, ROUND(COALESCE(discount_amount, 0) * 100))
              WHERE requested_discount_cents IS NULL
                 OR eligible_base_cents IS NULL
                 OR eligible_base_before_cents IS NULL
                 OR applied_discount_cents IS NULL"
        );

        $this->db->query(
            "UPDATE discount_usages du
                JOIN (
                    SELECT id, ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY COALESCE(used_at, created_at), id) AS rn
                      FROM discount_usages
                ) ordered ON ordered.id = du.id
                SET du.application_order = COALESCE(du.application_order, ordered.rn)
              WHERE du.application_order IS NULL"
        );
    }

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

        $indexes = $this->db->query('SHOW INDEX FROM discount_usages')->getResultArray();
        foreach ($indexes as $index) {
            if (($index['Key_name'] ?? '') === 'uniq_discount_usage_voucher_invoice') {
                return;
            }
        }

        $this->db->query(
            'CREATE UNIQUE INDEX uniq_discount_usage_voucher_invoice ON discount_usages (voucher_id, invoice_id)'
        );
        $this->assertIndexExists('discount_usages', 'uniq_discount_usage_voucher_invoice');
    }

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

        $columns = [];
        if (!$this->db->fieldExists('applied_invoice_line_id', 'additional_charges')) {
            $columns['applied_invoice_line_id'] = ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true, 'after' => 'status'];
        }
        if (!$this->db->fieldExists('applied_by', 'additional_charges')) {
            $columns['applied_by'] = ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true, 'after' => 'applied_invoice_line_id'];
        }
        if (!$this->db->fieldExists('applied_at', 'additional_charges')) {
            $columns['applied_at'] = ['type' => 'DATETIME', 'null' => true, 'after' => 'applied_by'];
        }
        if (!$this->db->fieldExists('voided_by', 'additional_charges')) {
            $columns['voided_by'] = ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true, 'after' => 'applied_at'];
        }
        if (!$this->db->fieldExists('voided_at', 'additional_charges')) {
            $columns['voided_at'] = ['type' => 'DATETIME', 'null' => true, 'after' => 'voided_by'];
        }
        if (!$this->db->fieldExists('void_reason', 'additional_charges')) {
            $columns['void_reason'] = ['type' => 'TEXT', 'null' => true, 'after' => 'voided_at'];
        }

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

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

        if (!$this->db->fieldExists('active_source_key', 'invoice_lines')) {
            $this->forge->addColumn('invoice_lines', [
                'active_source_key' => [
                    'type' => 'VARCHAR',
                    'constraint' => 120,
                    'null' => true,
                    'after' => 'source_id',
                ],
            ]);
        }

        $indexes = $this->db->query('SHOW INDEX FROM invoice_lines')->getResultArray();
        foreach ($indexes as $index) {
            if (($index['Key_name'] ?? '') === 'uniq_invoice_lines_active_source_key') {
                return;
            }
        }

        $this->db->query(
            'CREATE UNIQUE INDEX uniq_invoice_lines_active_source_key ON invoice_lines (active_source_key)'
        );
        $this->assertIndexExists('invoice_lines', 'uniq_invoice_lines_active_source_key');
    }

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

        try {
            $this->forge->modifyColumn('additional_charges', [
                'status' => [
                    'type' => 'ENUM',
                    'constraint' => ['pending', 'approved', 'applied', 'rejected', 'voided', 'reversed'],
                    'default' => 'pending',
                    'null' => false,
                ],
            ]);
        } catch (\Throwable $e) {
            throw new \RuntimeException('Could not expand additional charge statuses: ' . $e->getMessage(), 0, $e);
        }
    }

    private function assertIndexExists(string $table, string $indexName): void
    {
        $indexes = $this->db->query('SHOW INDEX FROM ' . $this->db->escapeIdentifiers($table))->getResultArray();
        foreach ($indexes as $index) {
            if (($index['Key_name'] ?? '') === $indexName) {
                return;
            }
        }

        throw new \RuntimeException("Required index {$indexName} was not created on {$table}.");
    }

}