<?php

declare(strict_types=1);

/**
 * M7.1 Core tables (multi-account ready).
 *
 * - google_accounts
 * - gsc_properties (linked to account)
 * - backfill from legacy google_properties if present
 *
 * @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');
    }

    $accounts = '`' . $prefix . 'google_accounts`';
    $props = '`' . $prefix . 'gsc_properties`';

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS {$accounts} (
            `id` int unsigned NOT NULL AUTO_INCREMENT,
            `provider` varchar(32) NOT NULL DEFAULT 'google',
            `connected_at` datetime NULL,
            `tokens_enc` longtext NULL,
            `reconnect_required` tinyint(1) NOT NULL DEFAULT 0,
            `token_last_error` varchar(255) NULL,
            `created_at` datetime NOT NULL,
            `updated_at` datetime NOT NULL,
            PRIMARY KEY (`id`),
            KEY `idx_provider` (`provider`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS {$props} (
            `id` bigint unsigned NOT NULL AUTO_INCREMENT,
            `google_account_id` int unsigned NOT NULL,
            `site_url` varchar(2048) NOT NULL,
            `site_type` varchar(32) NOT NULL,
            `permission_level` varchar(64) NOT NULL,
            `is_enabled` tinyint(1) NOT NULL DEFAULT 0,
            `enabled_at` datetime NULL,
            `fetched_at` datetime NOT NULL,
            `last_sync_date` date NULL,
            `last_sync_at` datetime NULL,
            `last_sync_error` varchar(255) NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_account_site` (`google_account_id`, `site_url`(255)),
            KEY `idx_enabled` (`google_account_id`, `is_enabled`),
            KEY `idx_site` (`site_url`(255)),
            CONSTRAINT `fk_gsc_properties_account`
              FOREIGN KEY (`google_account_id`) REFERENCES {$accounts} (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    // Ensure a default account exists (single-account MVP).
    $stmt = $pdo->query("SELECT id FROM {$accounts} ORDER BY id ASC LIMIT 1");
    $accId = (int) ($stmt ? $stmt->fetchColumn() : 0);
    if ($accId < 1) {
        $now = date('Y-m-d H:i:s');
        $ins = $pdo->prepare("INSERT INTO {$accounts} (provider, created_at, updated_at) VALUES ('google', ?, ?)");
        $ins->execute([$now, $now]);
        $accId = (int) $pdo->lastInsertId();
    }

    // Backfill properties from legacy table if it exists.
    $legacy = $prefix . 'google_properties';
    $chk = $pdo->prepare("SHOW TABLES LIKE ?");
    $chk->execute([$legacy]);
    $exists = (bool) $chk->fetchColumn();
    if ($exists) {
        $legacyT = '`' . $legacy . '`';
        // Copy rows (best-effort). Keep enabled/sync metadata when present.
        $pdo->exec(
            "INSERT INTO {$props} (google_account_id, site_url, site_type, permission_level, is_enabled, enabled_at, fetched_at, last_sync_date, last_sync_at, last_sync_error)
             SELECT {$accId} AS google_account_id,
                    site_url, site_type, permission_level,
                    COALESCE(is_enabled, 0) AS is_enabled,
                    enabled_at,
                    fetched_at,
                    last_sync_date,
                    last_sync_at,
                    last_sync_error
             FROM {$legacyT}
             ON DUPLICATE KEY UPDATE
                site_type = VALUES(site_type),
                permission_level = VALUES(permission_level),
                fetched_at = VALUES(fetched_at)"
        );
    }
};

