<?php
// Nettoyage des doublons dans equipments et ajout de contrainte d'unicité
// Conserve le plus petit id par (name, category|type)

require_once __DIR__ . '/../app/helpers.php';

spl_autoload_register(function ($class) {
    $prefix = 'App\\';
    $baseDir = __DIR__ . '/../app/';
    if (strncmp($prefix, $class, strlen($prefix)) !== 0) return;
    $relative = substr($class, strlen($prefix));
    $file = $baseDir . str_replace('\\', '/', $relative) . '.php';
    if (file_exists($file)) require $file;
});

use App\Core\Database;

function out($msg) { echo $msg . PHP_EOL; }

try {
    $pdo = Database::pdo();
    out('✅ Connexion BDD OK');

    // Détecter la colonne de regroupement (category ou type)
    $cols = $pdo->query('DESCRIBE equipments')->fetchAll(PDO::FETCH_COLUMN);
    $hasCategory = in_array('category', $cols, true);
    $hasType = in_array('type', $cols, true);
    $grpCol = $hasCategory ? 'category' : ($hasType ? 'type' : null);

    // Compter avant
    $totalBefore = (int)$pdo->query('SELECT COUNT(*) FROM equipments')->fetchColumn();

    // Supprimer doublons: garder MIN(id) par (LOWER(TRIM(name)), LOWER(TRIM(grp)))
    if ($grpCol) {
        $sql = "DELETE e FROM equipments e
                JOIN (
                  SELECT MIN(id) AS keep_id,
                         LOWER(TRIM(name)) AS n,
                         LOWER(TRIM(COALESCE($grpCol,''))) AS g
                  FROM equipments
                  GROUP BY n, g
                ) k
                  ON LOWER(TRIM(e.name)) = k.n
                 AND LOWER(TRIM(COALESCE(e.$grpCol,''))) = k.g
                WHERE e.id <> k.keep_id";
    } else {
        $sql = "DELETE e FROM equipments e
                JOIN (
                  SELECT MIN(id) AS keep_id,
                         LOWER(TRIM(name)) AS n
                  FROM equipments
                  GROUP BY n
                ) k
                  ON LOWER(TRIM(e.name)) = k.n
                WHERE e.id <> k.keep_id";
    }

    $pdo->exec($sql);

    $totalAfter = (int)$pdo->query('SELECT COUNT(*) FROM equipments')->fetchColumn();
    out('🧹 Suppression des doublons effectuée. Avant: ' . $totalBefore . ' | Après: ' . $totalAfter . ' | Supprimés: ' . max(0, $totalBefore - $totalAfter));

    // Ajouter contrainte d'unicité si non existante
    $dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
    if ($dbName) {
        if ($grpCol) {
            $indexName = $grpCol === 'category' ? 'uniq_equip_name_cat' : 'uniq_equip_name_type';
            $chk = $pdo->prepare('SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME="equipments" AND INDEX_NAME=?');
            $chk->execute([$dbName, $indexName]);
            if ((int)$chk->fetchColumn() === 0) {
                $pdo->exec("ALTER TABLE equipments ADD UNIQUE KEY `$indexName` (`name`, `$grpCol`)");
                out('🔐 Contrainte d\'unicité ajoutée sur (name, ' . $grpCol . ')');
            } else {
                out('ℹ️ Contrainte d\'unicité déjà présente: ' . $indexName);
            }
        } else {
            $indexName = 'uniq_equip_name';
            $chk = $pdo->prepare('SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME="equipments" AND INDEX_NAME=?');
            $chk->execute([$dbName, $indexName]);
            if ((int)$chk->fetchColumn() === 0) {
                $pdo->exec("ALTER TABLE equipments ADD UNIQUE KEY `$indexName` (`name`)");
                out('🔐 Contrainte d\'unicité ajoutée sur (name)');
            } else {
                out('ℹ️ Contrainte d\'unicité déjà présente: ' . $indexName);
            }
        }
    }

    out('✅ Terminé');

} catch (Throwable $e) {
    out('❌ Erreur: ' . $e->getMessage());
    exit(1);
}