<?php
declare(strict_types=1);

return [
    'up' => function (\App\Core\Database $db): void {
        $table = 'rental_contracts';

        $columns = [
            'escalation_type'          => "ALTER TABLE `{$table}` ADD COLUMN `escalation_type` VARCHAR(10) NOT NULL DEFAULT 'none' AFTER `late_fee_rate`",
            'escalation_rate'          => "ALTER TABLE `{$table}` ADD COLUMN `escalation_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER `escalation_type`",
            'escalation_tiers_json'    => "ALTER TABLE `{$table}` ADD COLUMN `escalation_tiers_json` JSON NULL AFTER `escalation_rate`",
            'utilities_mode'           => "ALTER TABLE `{$table}` ADD COLUMN `utilities_mode` VARCHAR(20) NOT NULL DEFAULT 'rent_pct' AFTER `escalation_tiers_json`",
            'utilities_rent_pct'       => "ALTER TABLE `{$table}` ADD COLUMN `utilities_rent_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER `utilities_mode`",
            'utilities_facility_pct'   => "ALTER TABLE `{$table}` ADD COLUMN `utilities_facility_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER `utilities_rent_pct`",
            'facility_monthly_cost'    => "ALTER TABLE `{$table}` ADD COLUMN `facility_monthly_cost` DECIMAL(15,2) NULL AFTER `utilities_facility_pct`",
            'payment_due_day'          => "ALTER TABLE `{$table}` ADD COLUMN `payment_due_day` TINYINT UNSIGNED NOT NULL DEFAULT 5 AFTER `facility_monthly_cost`",
            'late_fee_bank_rate'       => "ALTER TABLE `{$table}` ADD COLUMN `late_fee_bank_rate` DECIMAL(7,4) NOT NULL DEFAULT 0.00 AFTER `payment_due_day`",
            'grace_period_months'      => "ALTER TABLE `{$table}` ADD COLUMN `grace_period_months` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `late_fee_bank_rate`",
            'early_termination_months' => "ALTER TABLE `{$table}` ADD COLUMN `early_termination_months` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `grace_period_months`",
        ];

        foreach ($columns as $column => $sql) {
            $exists = $db->selectOne(
                "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?",
                [$table, $column]
            );
            if (!$exists) {
                $db->raw($sql);
            }
        }

        // Data migration: backfill utilities_rent_pct from legacy utilities_percentage
        $db->raw(
            "UPDATE `{$table}` SET `utilities_rent_pct` = `utilities_percentage` WHERE `utilities_rent_pct` = 0.00 AND `utilities_percentage` > 0"
        );
    },

    'down' => "
ALTER TABLE `rental_contracts`
    DROP COLUMN IF EXISTS `escalation_type`,
    DROP COLUMN IF EXISTS `escalation_rate`,
    DROP COLUMN IF EXISTS `escalation_tiers_json`,
    DROP COLUMN IF EXISTS `utilities_mode`,
    DROP COLUMN IF EXISTS `utilities_rent_pct`,
    DROP COLUMN IF EXISTS `utilities_facility_pct`,
    DROP COLUMN IF EXISTS `facility_monthly_cost`,
    DROP COLUMN IF EXISTS `payment_due_day`,
    DROP COLUMN IF EXISTS `late_fee_bank_rate`,
    DROP COLUMN IF EXISTS `grace_period_months`,
    DROP COLUMN IF EXISTS `early_termination_months`;
",
];
