<?php

declare(strict_types=1);

/**
 * M14 add device/country dimensions to daily fact tables.
 *
 * We evolve existing tables by adding columns and replacing the UNIQUE keys
 * so idempotent upserts work per (property_id,date,dimension,device,country).
 *
 * @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');
    }

    $apply = static function (string $table, array $sqls) use ($pdo, $prefix): void {
        $t = '`' . $prefix . $table . '`';
        foreach ($sqls as $sql) {
            try { $pdo->exec(str_replace('{T}', $t, $sql)); } catch (\Throwable) {}
        }
    };

    // gsc_daily_query
    $apply('gsc_daily_query', [
        "ALTER TABLE {T} ADD COLUMN `property_id` bigint unsigned NULL AFTER `id`",
        "ALTER TABLE {T} ADD COLUMN `device` varchar(16) NOT NULL DEFAULT 'all' AFTER `query`",
        "ALTER TABLE {T} ADD COLUMN `country` char(3) NOT NULL DEFAULT 'all' AFTER `device`",
        "ALTER TABLE {T} DROP INDEX `uniq_site_date_query`",
        "ALTER TABLE {T} ADD UNIQUE KEY `uniq_prop_date_query_dev_ctr` (`property_id`, `date`, `query`(191), `device`, `country`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_date` (`property_id`, `date`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_country` (`property_id`, `country`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_device` (`property_id`, `device`)",
    ]);

    // gsc_daily_page
    $apply('gsc_daily_page', [
        "ALTER TABLE {T} ADD COLUMN `property_id` bigint unsigned NULL AFTER `id`",
        "ALTER TABLE {T} ADD COLUMN `device` varchar(16) NOT NULL DEFAULT 'all' AFTER `page`",
        "ALTER TABLE {T} ADD COLUMN `country` char(3) NOT NULL DEFAULT 'all' AFTER `device`",
        "ALTER TABLE {T} DROP INDEX `uniq_site_date_page`",
        "ALTER TABLE {T} ADD UNIQUE KEY `uniq_prop_date_page_dev_ctr` (`property_id`, `date`, `page`(191), `device`, `country`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_date` (`property_id`, `date`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_country` (`property_id`, `country`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_device` (`property_id`, `device`)",
    ]);

    // gsc_daily_query_page (already has property_id)
    $apply('gsc_daily_query_page', [
        "ALTER TABLE {T} ADD COLUMN `device` varchar(16) NOT NULL DEFAULT 'all' AFTER `page`",
        "ALTER TABLE {T} ADD COLUMN `country` char(3) NOT NULL DEFAULT 'all' AFTER `device`",
        "ALTER TABLE {T} DROP INDEX `uniq_prop_date_q_p`",
        "ALTER TABLE {T} ADD UNIQUE KEY `uniq_prop_date_q_p_dev_ctr` (`property_id`, `date`, `query`(191), `page`(191), `device`, `country`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_country` (`property_id`, `country`)",
        "ALTER TABLE {T} ADD KEY `idx_prop_device` (`property_id`, `device`)",
    ]);
};

