<?php

declare(strict_types=1);

/**
 * M6.2 Sync logs (runs + per-property items).
 *
 * @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');
    }

    $runs = '`' . $prefix . 'sync_runs`';
    $items = '`' . $prefix . 'sync_run_items`';

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS {$runs} (
            `id` bigint unsigned NOT NULL AUTO_INCREMENT,
            `started_at` datetime NOT NULL,
            `finished_at` datetime NULL,
            `status` varchar(16) NOT NULL,
            `mode` varchar(32) NOT NULL,
            `range_start` date NOT NULL,
            `range_end` date NOT NULL,
            `properties_total` int unsigned NOT NULL DEFAULT 0,
            `properties_failed` int unsigned NOT NULL DEFAULT 0,
            `days_processed` int unsigned NOT NULL DEFAULT 0,
            `query_upserts` int unsigned NOT NULL DEFAULT 0,
            `page_upserts` int unsigned NOT NULL DEFAULT 0,
            `error_summary` varchar(255) NULL,
            PRIMARY KEY (`id`),
            KEY `idx_started_at` (`started_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS {$items} (
            `id` bigint unsigned NOT NULL AUTO_INCREMENT,
            `run_id` bigint unsigned NOT NULL,
            `site_url` varchar(2048) NOT NULL,
            `from_date` date NOT NULL,
            `to_date` date NOT NULL,
            `status` varchar(16) NOT NULL,
            `days_processed` int unsigned NOT NULL DEFAULT 0,
            `query_upserts` int unsigned NOT NULL DEFAULT 0,
            `page_upserts` int unsigned NOT NULL DEFAULT 0,
            `error_summary` varchar(255) NULL,
            `started_at` datetime NOT NULL,
            `finished_at` datetime NULL,
            PRIMARY KEY (`id`),
            KEY `idx_run_id` (`run_id`),
            KEY `idx_site` (`site_url`(255)),
            CONSTRAINT `fk_sync_run_items_run`
              FOREIGN KEY (`run_id`) REFERENCES {$runs} (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );
};

