-- ============================================
-- Migration 0001: Nettoyage colonnes inutilisees
-- Date: 2026-01-01
-- ============================================
-- Suppression des colonnes obsoletes
-- ============================================

DECLARE @sql NVARCHAR(MAX)
DECLARE @constraint NVARCHAR(255)
DECLARE @index NVARCHAR(255)

-- Table Dossier : supprimer Id_societe, Version_Analyse, exeVersion, archive
IF OBJECT_ID('Dossier', 'U') IS NOT NULL
BEGIN

    -- Supprimer Id_societe
    IF COL_LENGTH('Dossier', 'Id_societe') IS NOT NULL
    BEGIN
        -- Supprimer les contraintes default
        SELECT @constraint = dc.name
        FROM sys.default_constraints dc
        JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE dc.parent_object_id = OBJECT_ID('Dossier') AND c.name = 'Id_societe'

        IF @constraint IS NOT NULL
            EXEC('ALTER TABLE Dossier DROP CONSTRAINT ' + @constraint)

        -- Supprimer les index
        SELECT @index = i.name
        FROM sys.indexes i
        JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE i.object_id = OBJECT_ID('Dossier') AND c.name = 'Id_societe'

        IF @index IS NOT NULL
            EXEC('DROP INDEX ' + @index + ' ON Dossier')

        ALTER TABLE Dossier DROP COLUMN Id_societe
    END

    -- Supprimer Version_Analyse
    SET @constraint = NULL
    IF COL_LENGTH('Dossier', 'Version_Analyse') IS NOT NULL
    BEGIN
        SELECT @constraint = dc.name
        FROM sys.default_constraints dc
        JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE dc.parent_object_id = OBJECT_ID('Dossier') AND c.name = 'Version_Analyse'

        IF @constraint IS NOT NULL
            EXEC('ALTER TABLE Dossier DROP CONSTRAINT ' + @constraint)

        ALTER TABLE Dossier DROP COLUMN Version_Analyse
    END

    -- Supprimer exeVersion
    SET @constraint = NULL
    IF COL_LENGTH('Dossier', 'exeVersion') IS NOT NULL
    BEGIN
        SELECT @constraint = dc.name
        FROM sys.default_constraints dc
        JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE dc.parent_object_id = OBJECT_ID('Dossier') AND c.name = 'exeVersion'

        IF @constraint IS NOT NULL
            EXEC('ALTER TABLE Dossier DROP CONSTRAINT ' + @constraint)

        ALTER TABLE Dossier DROP COLUMN exeVersion
    END

    -- Supprimer archive
    SET @constraint = NULL
    IF COL_LENGTH('Dossier', 'archive') IS NOT NULL
    BEGIN
        SELECT @constraint = dc.name
        FROM sys.default_constraints dc
        JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE dc.parent_object_id = OBJECT_ID('Dossier') AND c.name = 'archive'

        IF @constraint IS NOT NULL
            EXEC('ALTER TABLE Dossier DROP CONSTRAINT ' + @constraint)

        ALTER TABLE Dossier DROP COLUMN archive
    END

END
