-- ============================================
-- Migration 0025: Correction type id_equipement dans RetourEquipement
-- Date: 2026-02-02
-- Description: Changer id_equipement de INT a VARCHAR(50)
-- ============================================

IF OBJECT_ID('RetourEquipement', 'U') IS NOT NULL
BEGIN
    -- Verifier si la colonne est de type INT
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'RetourEquipement'
        AND COLUMN_NAME = 'id_equipement'
        AND DATA_TYPE = 'int'
    )
    BEGIN
        -- Supprimer les index sur id_equipement
        DECLARE @indexName NVARCHAR(128);
        DECLARE index_cursor CURSOR FOR
            SELECT i.name
            FROM sys.indexes i
            INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
            INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE i.object_id = OBJECT_ID('RetourEquipement') AND c.name = 'id_equipement' AND i.is_primary_key = 0;

        OPEN index_cursor;
        FETCH NEXT FROM index_cursor INTO @indexName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC('DROP INDEX ' + @indexName + ' ON RetourEquipement');
            PRINT 'Index supprime: ' + @indexName;
            FETCH NEXT FROM index_cursor INTO @indexName;
        END
        CLOSE index_cursor;
        DEALLOCATE index_cursor;

        -- Supprimer la contrainte DEFAULT si elle existe
        DECLARE @constraintName NVARCHAR(128);
        SELECT @constraintName = 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 = 'id_equipement';

        IF @constraintName IS NOT NULL
        BEGIN
            EXEC('ALTER TABLE RetourEquipement DROP CONSTRAINT ' + @constraintName);
            PRINT 'Contrainte DEFAULT supprimee: ' + @constraintName;
        END

        -- Modifier le type de la colonne
        ALTER TABLE RetourEquipement ALTER COLUMN id_equipement VARCHAR(50) NOT NULL;
        PRINT 'Colonne id_equipement modifiee de INT a VARCHAR(50)';

        -- Ajouter la nouvelle contrainte DEFAULT
        ALTER TABLE RetourEquipement ADD CONSTRAINT DF_RetourEquipement_id_equipement DEFAULT ('') FOR id_equipement;
        PRINT 'Nouvelle contrainte DEFAULT ajoutee';
    END
    ELSE
    BEGIN
        PRINT 'La colonne id_equipement est deja de type VARCHAR, aucune modification necessaire';
    END
END
ELSE
BEGIN
    PRINT 'La table RetourEquipement n''existe pas';
END
