<?php

declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    // Add column
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'members' AND column_name = 'membership_origin_date'"
    );

    if (!$exists) {
        $db->raw("ALTER TABLE members ADD COLUMN membership_origin_date DATE NULL COMMENT 'Original membership creation date — survives death/waiver transfers' AFTER ownership_transfer_date");
    }

    // Backfill: death-transferred members
    $db->raw("
        UPDATE members m
        INNER JOIN death_cases dc ON dc.id = m.transferred_from_death_id
        INNER JOIN members deceased ON deceased.id = dc.member_id
        SET m.membership_origin_date = COALESCE(deceased.membership_origin_date, deceased.form_date, DATE(deceased.created_at))
        WHERE m.transferred_from_death_id IS NOT NULL
          AND m.membership_origin_date IS NULL
    ");

    // Backfill: waiver-transferred members
    $db->raw("
        UPDATE members m
        INNER JOIN waiver_requests wr ON wr.id = m.transferred_from_waiver_id
        INNER JOIN members source ON source.id = wr.source_member_id
        SET m.membership_origin_date = COALESCE(source.membership_origin_date, source.form_date, DATE(source.created_at))
        WHERE m.transferred_from_waiver_id IS NOT NULL
          AND m.membership_origin_date IS NULL
    ");
};
