<?php

declare(strict_types=1);

/**
 * M17 Multi-user ready: scope data by user_id.
 *
 * - settings: add user_id and use composite primary key (user_id,name)
 * - projects: add user_id + index
 * - google_accounts: add user_id + index, backfill existing rows to user_id=1
 *
 * @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');
    }

    $settings = '`' . $prefix . 'settings`';
    $projects = '`' . $prefix . 'projects`';
    $accounts = '`' . $prefix . 'google_accounts`';

    // SETTINGS: add user_id and composite PK
    try { $pdo->exec("ALTER TABLE {$settings} ADD COLUMN `user_id` int unsigned NOT NULL DEFAULT 1 FIRST"); } catch (\Throwable) {}
    // Backfill (if column existed but was NULL somehow)
    try { $pdo->exec("UPDATE {$settings} SET user_id = 1 WHERE user_id IS NULL OR user_id = 0"); } catch (\Throwable) {}
    // Replace PK from (name) to (user_id,name)
    try { $pdo->exec("ALTER TABLE {$settings} DROP PRIMARY KEY"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$settings} ADD PRIMARY KEY (`user_id`, `name`)"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$settings} ADD KEY `idx_user_updated` (`user_id`, `updated_at`)"); } catch (\Throwable) {}

    // PROJECTS: add user_id and index
    try { $pdo->exec("ALTER TABLE {$projects} ADD COLUMN `user_id` int unsigned NOT NULL DEFAULT 1 AFTER `id`"); } catch (\Throwable) {}
    try { $pdo->exec("UPDATE {$projects} SET user_id = 1 WHERE user_id IS NULL OR user_id = 0"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$projects} ADD KEY `idx_user_name` (`user_id`, `name`)"); } catch (\Throwable) {}

    // GOOGLE_ACCOUNTS: add user_id and index
    try { $pdo->exec("ALTER TABLE {$accounts} ADD COLUMN `user_id` int unsigned NOT NULL DEFAULT 1 AFTER `id`"); } catch (\Throwable) {}
    try { $pdo->exec("UPDATE {$accounts} SET user_id = 1 WHERE user_id IS NULL OR user_id = 0"); } catch (\Throwable) {}
    try { $pdo->exec("ALTER TABLE {$accounts} ADD KEY `idx_user_provider` (`user_id`, `provider`)"); } catch (\Throwable) {}
};

