<?php
declare(strict_types=1);

return function (\App\Core\Database $db): void {
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'sa_group_coaches'"
    );

    if (!$exists) {
        $db->raw("
            CREATE TABLE `sa_group_coaches` (
                `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                `group_id` BIGINT UNSIGNED NOT NULL,
                `coach_id` BIGINT UNSIGNED NOT NULL,
                `role` VARCHAR(20) NOT NULL DEFAULT 'primary' COMMENT 'primary, assistant',
                `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY `uq_sa_gc_group_coach` (`group_id`, `coach_id`),
                INDEX `idx_sa_gc_coach` (`coach_id`),
                CONSTRAINT `fk_sa_gc_group` FOREIGN KEY (`group_id`) REFERENCES `sa_groups`(`id`) ON DELETE CASCADE,
                CONSTRAINT `fk_sa_gc_coach` FOREIGN KEY (`coach_id`) REFERENCES `sa_coaches`(`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");

        // Backfill from existing sa_groups.coach_id
        $db->raw("
            INSERT IGNORE INTO sa_group_coaches (group_id, coach_id, role, created_at)
            SELECT id, coach_id, 'primary', NOW()
            FROM sa_groups
            WHERE coach_id IS NOT NULL AND coach_id > 0 AND is_archived = 0
        ");
    }
};
