<?php
declare(strict_types=1);

return [
    'up' => function (\App\Core\Database $db): void {
        $deathCols = [
            'inheritance_notice_path'  => "ALTER TABLE death_cases ADD COLUMN inheritance_notice_path  VARCHAR(500)               NULL AFTER death_certificate_path",
            'board_fee_method'         => "ALTER TABLE death_cases ADD COLUMN board_fee_method         ENUM('percentage','fixed')  NULL AFTER fee_amount",
            'board_fee_value'          => "ALTER TABLE death_cases ADD COLUMN board_fee_value          DECIMAL(10,4)              NULL AFTER board_fee_method",
            'board_fee_amount'         => "ALTER TABLE death_cases ADD COLUMN board_fee_amount         DECIMAL(15,2)              NULL AFTER board_fee_value",
            'board_decision_reference' => "ALTER TABLE death_cases ADD COLUMN board_decision_reference VARCHAR(100)               NULL AFTER board_fee_amount",
            'board_decision_date'      => "ALTER TABLE death_cases ADD COLUMN board_decision_date      DATE                       NULL AFTER board_decision_reference",
            'board_notes'              => "ALTER TABLE death_cases ADD COLUMN board_notes              TEXT                       NULL AFTER board_decision_date",
            'board_approved_by'        => "ALTER TABLE death_cases ADD COLUMN board_approved_by        BIGINT UNSIGNED            NULL AFTER board_notes",
            'board_approved_at'        => "ALTER TABLE death_cases ADD COLUMN board_approved_at        TIMESTAMP                  NULL AFTER board_approved_by",
            'payment_request_id'       => "ALTER TABLE death_cases ADD COLUMN payment_request_id       BIGINT UNSIGNED            NULL AFTER board_approved_at",
        ];

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

        $exists = $db->selectOne(
            "SELECT 1 FROM information_schema.COLUMNS
              WHERE table_schema = DATABASE() AND table_name = 'members' AND column_name = 'transferred_from_death_id'",
            []
        );
        if (!$exists) {
            $db->raw("ALTER TABLE members ADD COLUMN transferred_from_death_id BIGINT UNSIGNED NULL AFTER transferred_from_divorce_id");
        }
    },
    'down' => "
ALTER TABLE death_cases
    DROP COLUMN IF EXISTS inheritance_notice_path,
    DROP COLUMN IF EXISTS board_fee_method,
    DROP COLUMN IF EXISTS board_fee_value,
    DROP COLUMN IF EXISTS board_fee_amount,
    DROP COLUMN IF EXISTS board_decision_reference,
    DROP COLUMN IF EXISTS board_decision_date,
    DROP COLUMN IF EXISTS board_notes,
    DROP COLUMN IF EXISTS board_approved_by,
    DROP COLUMN IF EXISTS board_approved_at,
    DROP COLUMN IF EXISTS payment_request_id;

ALTER TABLE members
    DROP COLUMN IF EXISTS transferred_from_death_id;
",
];
