<?php
require_once '../includes/db.php';

try {
    $pdo->beginTransaction();
    
    echo "Starting database cleanup for scoring_format_judges duplicates...\n";
    
    // Step 1: Find and remove duplicate records, keeping only the latest one for each format_id
    $findDuplicatesQuery = "
        SELECT format_id, COUNT(*) as count 
        FROM scoring_format_judges 
        GROUP BY format_id 
        HAVING COUNT(*) > 1
    ";
    
    $duplicates = $pdo->query($findDuplicatesQuery)->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($duplicates)) {
        echo "No duplicates found.\n";
    } else {
        echo "Found duplicates for format_ids: " . implode(', ', array_column($duplicates, 'format_id')) . "\n";
        
        foreach ($duplicates as $duplicate) {
            $formatId = $duplicate['format_id'];
            
            // Keep the record with the highest ID (most recent) and delete others
            $cleanupQuery = "
                DELETE FROM scoring_format_judges 
                WHERE format_id = ? AND id NOT IN (
                    SELECT * FROM (
                        SELECT MAX(id) 
                        FROM scoring_format_judges 
                        WHERE format_id = ?
                    ) as temp
                )
            ";
            
            $stmt = $pdo->prepare($cleanupQuery);
            $stmt->execute([$formatId, $formatId]);
            
            $deletedCount = $stmt->rowCount();
            echo "Deleted $deletedCount duplicate records for format_id: $formatId\n";
        }
    }
    
    // Step 2: Add unique constraint on format_id
    try {
        $pdo->exec("ALTER TABLE scoring_format_judges ADD UNIQUE KEY unique_format_id (format_id)");
        echo "Added unique constraint on format_id.\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key name') !== false) {
            echo "Unique constraint already exists.\n";
        } else {
            throw $e;
        }
    }
    
    $pdo->commit();
    echo "Database cleanup completed successfully!\n";
    
} catch (PDOException $e) {
    $pdo->rollback();
    echo "Error: " . $e->getMessage() . "\n";
}
?>
