-- Migration 0038: Simplification table RetourEquipement
-- Suppression des colonnes liées à la décision (fonctionnalité simplifiée)

-- Supprimer l'index sur statut
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_RetourEquipement_Statut' AND object_id = OBJECT_ID('RetourEquipement'))
BEGIN
    DROP INDEX [IX_RetourEquipement_Statut] ON [RetourEquipement]
END

-- Supprimer les contraintes par défaut avant de supprimer les colonnes
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'ALTER TABLE [RetourEquipement] DROP CONSTRAINT [' + dc.name + ']; '
FROM sys.default_constraints dc
INNER 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('RetourEquipement')
AND c.name IN ('decision', 'id_commercial', 'date_decision', 'motif_refus', 'nouvelle_intervention_id', 'statut')

IF @sql <> ''
BEGIN
    EXEC sp_executesql @sql
END

-- Supprimer les colonnes inutilisées
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RetourEquipement' AND COLUMN_NAME = 'decision')
BEGIN
    ALTER TABLE [RetourEquipement] DROP COLUMN [decision]
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RetourEquipement' AND COLUMN_NAME = 'id_commercial')
BEGIN
    ALTER TABLE [RetourEquipement] DROP COLUMN [id_commercial]
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RetourEquipement' AND COLUMN_NAME = 'date_decision')
BEGIN
    ALTER TABLE [RetourEquipement] DROP COLUMN [date_decision]
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RetourEquipement' AND COLUMN_NAME = 'motif_refus')
BEGIN
    ALTER TABLE [RetourEquipement] DROP COLUMN [motif_refus]
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RetourEquipement' AND COLUMN_NAME = 'nouvelle_intervention_id')
BEGIN
    ALTER TABLE [RetourEquipement] DROP COLUMN [nouvelle_intervention_id]
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RetourEquipement' AND COLUMN_NAME = 'statut')
BEGIN
    ALTER TABLE [RetourEquipement] DROP COLUMN [statut]
END
