-- ============================================
-- Migration 0021: Nettoyage table Interventions
-- Date: 2026-01-30
-- ============================================
-- 1. Supprimer les colonnes heure_debut et heure_fin (obsoletes)
-- 2. Convertir date_intervention en DATETIME pour inclure l'heure
-- ============================================

IF OBJECT_ID('Interventions', 'U') IS NOT NULL
BEGIN
    -- 1. Supprimer heure_debut si elle existe (avec ses contraintes)
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'heure_debut'
    )
    BEGIN
        -- Supprimer la contrainte DEFAULT si elle existe
        DECLARE @constraint_heure_debut NVARCHAR(255);
        SELECT @constraint_heure_debut = d.name
        FROM sys.default_constraints d
        JOIN sys.columns c ON d.parent_column_id = c.column_id AND d.parent_object_id = c.object_id
        WHERE c.name = 'heure_debut' AND OBJECT_NAME(c.object_id) = 'Interventions';

        IF @constraint_heure_debut IS NOT NULL
        BEGIN
            EXEC('ALTER TABLE Interventions DROP CONSTRAINT ' + @constraint_heure_debut);
            PRINT 'Contrainte ' + @constraint_heure_debut + ' supprimee';
        END

        ALTER TABLE Interventions DROP COLUMN heure_debut;
        PRINT 'Colonne heure_debut supprimee';
    END
    ELSE
    BEGIN
        PRINT 'Colonne heure_debut n existe pas';
    END

    -- 2. Supprimer heure_fin si elle existe (avec ses contraintes)
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'heure_fin'
    )
    BEGIN
        -- Supprimer la contrainte DEFAULT si elle existe
        DECLARE @constraint_heure_fin NVARCHAR(255);
        SELECT @constraint_heure_fin = d.name
        FROM sys.default_constraints d
        JOIN sys.columns c ON d.parent_column_id = c.column_id AND d.parent_object_id = c.object_id
        WHERE c.name = 'heure_fin' AND OBJECT_NAME(c.object_id) = 'Interventions';

        IF @constraint_heure_fin IS NOT NULL
        BEGIN
            EXEC('ALTER TABLE Interventions DROP CONSTRAINT ' + @constraint_heure_fin);
            PRINT 'Contrainte ' + @constraint_heure_fin + ' supprimee';
        END

        ALTER TABLE Interventions DROP COLUMN heure_fin;
        PRINT 'Colonne heure_fin supprimee';
    END
    ELSE
    BEGIN
        PRINT 'Colonne heure_fin n existe pas';
    END

    -- 3. Modifier date_intervention en DATETIME si c'est un DATE
    DECLARE @current_type NVARCHAR(50);
    SELECT @current_type = DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'date_intervention';

    IF @current_type = 'date'
    BEGIN
        -- Supprimer la contrainte DEFAULT si elle existe
        DECLARE @constraint_date NVARCHAR(255);
        SELECT @constraint_date = d.name
        FROM sys.default_constraints d
        JOIN sys.columns c ON d.parent_column_id = c.column_id AND d.parent_object_id = c.object_id
        WHERE c.name = 'date_intervention' AND OBJECT_NAME(c.object_id) = 'Interventions';

        IF @constraint_date IS NOT NULL
        BEGIN
            EXEC('ALTER TABLE Interventions DROP CONSTRAINT ' + @constraint_date);
            PRINT 'Contrainte ' + @constraint_date + ' supprimee';
        END

        ALTER TABLE Interventions ALTER COLUMN date_intervention DATETIME NULL;
        PRINT 'Colonne date_intervention convertie en DATETIME';
    END
    ELSE
    BEGIN
        PRINT 'Colonne date_intervention est deja de type ' + ISNULL(@current_type, 'inconnu');
    END

END
ELSE
BEGIN
    PRINT 'Table Interventions non trouvee - migration ignoree';
END
