<?php
declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    // 1. Update system_config: flat 100 EGP, no card/form fees
    $db->query(
        "UPDATE system_config SET config_value = '100.00' WHERE config_key = 'sa.registration_fee_member'"
    );
    $db->query(
        "UPDATE system_config SET config_value = '100.00' WHERE config_key = 'sa.registration_fee_nonmember'"
    );
    $db->query(
        "UPDATE system_config SET config_value = '0.00' WHERE config_key = 'sa.card_fee'"
    );
    $db->query(
        "UPDATE system_config SET config_value = '0.00' WHERE config_key = 'sa.form_fee'"
    );

    // 2. Add selected_disciplines JSON to sa_registrations
    $col = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'sa_registrations' AND column_name = 'selected_disciplines'"
    );
    if (!$col) {
        $db->query("ALTER TABLE sa_registrations ADD COLUMN selected_disciplines JSON NULL AFTER group_id");
    }

    // 3. Add assessment columns to sa_group_players
    $col2 = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'sa_group_players' AND column_name = 'assessed_by'"
    );
    if (!$col2) {
        $db->query("ALTER TABLE sa_group_players ADD COLUMN assessed_by BIGINT UNSIGNED NULL AFTER created_by");
        $db->query("ALTER TABLE sa_group_players ADD COLUMN assessment_notes TEXT NULL AFTER assessed_by");
        $db->query("ALTER TABLE sa_group_players ADD COLUMN skill_level VARCHAR(20) NULL AFTER assessment_notes");
        $db->query("ALTER TABLE sa_group_players ADD COLUMN assessed_at TIMESTAMP NULL AFTER skill_level");
    }

    // 4. Add sa.coach_assessment permission keys
    $existing = $db->selectOne(
        "SELECT 1 FROM system_config WHERE config_key = 'sa.swimming.coach_manage'"
    );
    if (!$existing) {
        $db->insert('system_config', [
            'config_key'   => 'sa.swimming.coach_manage',
            'config_value' => '1',
            'description'  => 'Swimming Coach Management permission flag',
        ]);
    }
};
