-- ============================================
-- Migration 0014: Migration des donnees Atelier
-- Date: 2026-01-28
-- ============================================
-- A executer APRES la migration 0013
-- Migration des donnees et creation des index
-- ============================================

-- Verifier que la migration 0013 a ete appliquee
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'id_equipement'
)
BEGIN
    PRINT 'Migration 0013 detectee - Migration des donnees...';

    -- Migration des donnees : remplir id_equipement depuis Reparations (si table existe)
    IF OBJECT_ID('Reparations', 'U') IS NOT NULL
    BEGIN
        EXEC('
            UPDATE i
            SET i.id_equipement = r.id_equipement
            FROM Interventions i
            INNER JOIN Reparations r ON i.id_reparation = r.id_reparation
            WHERE i.id_equipement IS NULL
              AND r.id_equipement IS NOT NULL;
        ');
        PRINT 'Migration id_equipement depuis Reparations terminee';
    END
    ELSE
    BEGIN
        PRINT 'Table Reparations non presente - migration id_equipement ignoree';
    END

    -- Migration des donnees : calculer temps_cumule depuis heure_debut/heure_fin (si colonnes existent)
    -- Utilisation de SQL dynamique car SQL Server parse les colonnes meme dans un bloc conditionnel
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'heure_debut'
    ) AND EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'heure_fin'
    )
    BEGIN
        EXEC('
            UPDATE Interventions
            SET temps_cumule_minutes = DATEDIFF(MINUTE, heure_debut, heure_fin)
            WHERE heure_debut IS NOT NULL
              AND heure_fin IS NOT NULL
              AND temps_cumule_minutes = 0
              AND heure_fin > heure_debut;
        ');
        PRINT 'Calcul temps_cumule_minutes termine';
    END
    ELSE
    BEGIN
        PRINT 'Colonnes heure_debut/heure_fin non presentes - calcul temps_cumule ignore';
    END

    -- Index pour performance
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Interventions_id_equipement' AND object_id = OBJECT_ID('Interventions'))
    BEGIN
        CREATE INDEX IX_Interventions_id_equipement ON Interventions(id_equipement);
        PRINT 'Index IX_Interventions_id_equipement cree';
    END

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Interventions_chrono_actif' AND object_id = OBJECT_ID('Interventions'))
    BEGIN
        CREATE INDEX IX_Interventions_chrono_actif ON Interventions(chrono_actif) WHERE chrono_actif = 1;
        PRINT 'Index IX_Interventions_chrono_actif cree';
    END

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Interventions_technicien_actif' AND object_id = OBJECT_ID('Interventions'))
    BEGIN
        CREATE INDEX IX_Interventions_technicien_actif ON Interventions(id_technicien, chrono_actif, cloture_session);
        PRINT 'Index IX_Interventions_technicien_actif cree';
    END

    PRINT 'Migration 0014 terminee avec succes';
END
ELSE
BEGIN
    PRINT 'Migration 0013 non detectee - Executer 0013 d''abord';
END
