<?php

declare(strict_types=1);

/**
 * Fix UNIQUE constraint to apply only to active jobs.
 *
 * Previous schema used UNIQUE(user_id, property_id, job_type, is_active) which also
 * prevents multiple historical rows (is_active=0) for the same job type.
 *
 * We introduce a generated column `active_marker` that is 1 only when is_active=1,
 * otherwise NULL. MySQL UNIQUE allows multiple NULLs, so we enforce uniqueness only
 * for active jobs while allowing unlimited inactive history.
 *
 * @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');
    }

    $t = '`' . $prefix . 'sync_jobs`';

    // Drop the old UNIQUE key if present.
    try { $pdo->exec("ALTER TABLE {$t} DROP INDEX `uniq_active_job`"); } catch (\Throwable) {}

    // Add generated column if missing.
    try {
        $pdo->exec(
            "ALTER TABLE {$t}
             ADD COLUMN `active_marker` tinyint(1)
             GENERATED ALWAYS AS (CASE WHEN `is_active` = 1 THEN 1 ELSE NULL END) STORED
             AFTER `is_active`"
        );
    } catch (\Throwable) {}

    // Enforce uniqueness for active jobs only.
    try {
        $pdo->exec("ALTER TABLE {$t} ADD UNIQUE KEY `uniq_active_job` (`user_id`, `property_id`, `job_type`, `active_marker`)");
    } catch (\Throwable) {}
};

