<?php

declare(strict_types=1);

/**
 * Queue foundation for robust full sync/backfill jobs.
 *
 * Notes:
 * - We enforce "one active full_backfill job per (user_id, property_id)" using an `is_active` flag
 *   combined with a UNIQUE key. When a job is finished/failed/cancelled, set is_active=0.
 *
 * @return callable(PDO, string): void
 */
return static function (\PDO $pdo, string $prefix): void {
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $prefix)) {
        throw new InvalidArgumentException('Invalid table prefix');
    }

    $props = '`' . $prefix . 'gsc_properties`';
    $jobs = '`' . $prefix . 'sync_jobs`';

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS {$jobs} (
            `id` bigint unsigned NOT NULL AUTO_INCREMENT,
            `user_id` int unsigned NOT NULL,
            `property_id` bigint unsigned NOT NULL,
            `job_type` varchar(32) NOT NULL,
            `start_date` date NOT NULL,
            `end_date` date NOT NULL,
            `status` varchar(16) NOT NULL DEFAULT 'pending',
            `is_active` tinyint(1) NOT NULL DEFAULT 1,
            `attempts` int unsigned NOT NULL DEFAULT 0,
            `next_run_at` datetime NULL,
            `last_error` varchar(255) NULL,
            `created_at` datetime NOT NULL,
            `updated_at` datetime NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_active_job` (`user_id`, `property_id`, `job_type`, `is_active`),
            KEY `idx_user_status_next` (`user_id`, `status`, `next_run_at`),
            KEY `idx_property_status` (`property_id`, `status`),
            CONSTRAINT `fk_sync_jobs_property`
              FOREIGN KEY (`property_id`) REFERENCES {$props} (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );
};

