<?php
declare(strict_types=1);

return [
    'up' => function (\App\Core\Database $db): void {
        $cols = [
            'is_employed'            => "ALTER TABLE transfer_requests ADD COLUMN is_employed            TINYINT(1)  NULL DEFAULT NULL AFTER notes",
            'graduation_date'        => "ALTER TABLE transfer_requests ADD COLUMN graduation_date        DATE        NULL DEFAULT NULL AFTER is_employed",
            'work_date'              => "ALTER TABLE transfer_requests ADD COLUMN work_date              DATE        NULL DEFAULT NULL AFTER graduation_date",
            'date_turned_25'         => "ALTER TABLE transfer_requests ADD COLUMN date_turned_25         DATE        NULL DEFAULT NULL AFTER work_date",
            'effective_transfer_date'=> "ALTER TABLE transfer_requests ADD COLUMN effective_transfer_date DATE       NULL DEFAULT NULL AFTER date_turned_25",
        ];

        foreach ($cols as $col => $sql) {
            $exists = $db->selectOne(
                "SELECT 1 FROM information_schema.COLUMNS
                  WHERE table_schema = DATABASE() AND table_name = 'transfer_requests' AND column_name = ?",
                [$col]
            );
            if (!$exists) {
                $db->raw($sql);
            }
        }
    },
    'down' => "
ALTER TABLE transfer_requests
    DROP COLUMN IF EXISTS is_employed,
    DROP COLUMN IF EXISTS graduation_date,
    DROP COLUMN IF EXISTS work_date,
    DROP COLUMN IF EXISTS date_turned_25,
    DROP COLUMN IF EXISTS effective_transfer_date;
",
];
