<?php

declare(strict_types=1);

/**
 * M7.1 Link existing facts/logs to property_id (best-effort).
 *
 * @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');
    }

    $dq = '`' . $prefix . 'gsc_daily_query`';
    $dp = '`' . $prefix . 'gsc_daily_page`';
    $items = '`' . $prefix . 'sync_run_items`';
    $props = '`' . $prefix . 'gsc_properties`';

    // Add property_id columns (best-effort).
    try { $pdo->exec("ALTER TABLE {$dq} ADD COLUMN `property_id` bigint unsigned NULL"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$dp} ADD COLUMN `property_id` bigint unsigned NULL"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$items} ADD COLUMN `property_id` bigint unsigned NULL"); } catch (\Throwable) {}

    // Indices for common filters.
    try { $pdo->exec("ALTER TABLE {$dq} ADD KEY `idx_property_date` (`property_id`, `date`)"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$dp} ADD KEY `idx_property_date` (`property_id`, `date`)"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$items} ADD KEY `idx_property` (`property_id`)"); } catch (\Throwable) {}

    // Best-effort backfill property_id by site_url for existing rows (single-account assumption).
    try {
        $pdo->exec(
            "UPDATE {$dq} q
             JOIN {$props} p ON p.site_url = q.site_url
             SET q.property_id = p.id
             WHERE q.property_id IS NULL"
        );
    } catch (\Throwable) {}
    try {
        $pdo->exec(
            "UPDATE {$dp} d
             JOIN {$props} p ON p.site_url = d.site_url
             SET d.property_id = p.id
             WHERE d.property_id IS NULL"
        );
    } catch (\Throwable) {}
    try {
        $pdo->exec(
            "UPDATE {$items} i
             JOIN {$props} p ON p.site_url = i.site_url
             SET i.property_id = p.id
             WHERE i.property_id IS NULL"
        );
    } catch (\Throwable) {}
};

