-- ============================================================================
-- MIGRATION : Ajouter des clés primaires aux tables qui n'en ont pas
-- SQL Server 2022
--
-- Gère automatiquement :
--   - Tables sans clé primaire
--   - Colonnes Id existantes avec doublons
--   - Index et contraintes DEFAULT sur Id
--   - Colonnes IDENTITY existantes (ex: intervention_id)
-- ============================================================================

SET NOCOUNT ON;

PRINT '============================================';
PRINT 'AJOUT DES CLES PRIMAIRES MANQUANTES';
PRINT '============================================';
PRINT '';

-- ============================================================================
-- Tables à exclure
-- ============================================================================
DECLARE @ExcludedTables TABLE (TableName NVARCHAR(128));
INSERT INTO @ExcludedTables
VALUES
    ('sysdiagrams'),
    ('__MigrationHistory'),
    ('__EFMigrationsHistory'),
    ('dtproperties');

-- ============================================================================
-- Variables
-- ============================================================================
DECLARE @TableName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ConstraintName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @IdentityColumn NVARCHAR(128);
DECLARE @TotalTables INT = 0;
DECLARE @ProcessedTables INT = 0;
DECLARE @ErrorTables INT = 0;

-- ============================================================================
-- Curseur pour les tables sans clé primaire
-- ============================================================================
DECLARE table_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT t.name
FROM sys.tables t
WHERE t.is_ms_shipped = 0
    AND t.temporal_type = 0
    AND t.name NOT LIKE '%_History'
    AND t.name NOT IN (SELECT TableName
    FROM @ExcludedTables)
    AND NOT EXISTS (
      SELECT 1
    FROM sys.indexes i
    WHERE i.object_id = t.object_id AND i.is_primary_key = 1
  )
ORDER BY t.name;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TotalTables = @TotalTables + 1;
    PRINT '--- ' + @TableName + ' ---';

    BEGIN TRY
        -- ========================================
        -- Cas 1 : Table a déjà une colonne IDENTITY (ex: intervention_id)
        -- ========================================
        SELECT @IdentityColumn = c.name
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@TableName)
        AND c.is_identity = 1;

        IF @IdentityColumn IS NOT NULL AND @IdentityColumn <> 'Id'
        BEGIN
        -- Utiliser la colonne IDENTITY existante comme PK
        SET @sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT PK_' + @TableName + ' PRIMARY KEY (' + QUOTENAME(@IdentityColumn) + ')';
        EXEC sp_executesql @sql;
        PRINT '  -> PK ajoutée sur ' + @IdentityColumn + ' (IDENTITY existant)';
        SET @ProcessedTables = @ProcessedTables + 1;
        SET @IdentityColumn = NULL;
        FETCH NEXT FROM table_cursor INTO @TableName;
        CONTINUE;
    END
        SET @IdentityColumn = NULL;

        -- ========================================
        -- Cas 2 : Table a une colonne Id (peut avoir des doublons)
        -- ========================================
        IF EXISTS (SELECT 1
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@TableName) AND name = 'Id')
        BEGIN
        PRINT '  Colonne Id existe, nettoyage des dépendances...';

        -- Supprimer tous les index sur colonne Id
        DECLARE idx_cursor CURSOR LOCAL FAST_FORWARD 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(@TableName)
            AND c.name = 'Id'
            AND i.is_primary_key = 0
            AND i.name IS NOT NULL;

        OPEN idx_cursor;
        FETCH NEXT FROM idx_cursor INTO @IndexName;
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @sql = 'DROP INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@TableName);
            EXEC sp_executesql @sql;
            PRINT '    Index supprimé: ' + @IndexName;
            FETCH NEXT FROM idx_cursor INTO @IndexName;
        END
        CLOSE idx_cursor;
        DEALLOCATE idx_cursor;

        -- Supprimer toutes les contraintes DEFAULT sur colonne Id
        DECLARE def_cursor CURSOR LOCAL FAST_FORWARD FOR
            SELECT 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(@TableName)
            AND c.name = 'Id';

        OPEN def_cursor;
        FETCH NEXT FROM def_cursor INTO @ConstraintName;
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
            EXEC sp_executesql @sql;
            PRINT '    Default supprimé: ' + @ConstraintName;
            FETCH NEXT FROM def_cursor INTO @ConstraintName;
        END
        CLOSE def_cursor;
        DEALLOCATE def_cursor;

        -- Supprimer colonne Id
        SET @sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP COLUMN Id';
        EXEC sp_executesql @sql;
        PRINT '    Colonne Id supprimée';
    END

        -- ========================================
        -- Créer colonne Id IDENTITY + PK
        -- ========================================
        SET @sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD Id INT IDENTITY(1,1) NOT NULL';
        EXEC sp_executesql @sql;
        PRINT '  -> Colonne Id IDENTITY créée';

        SET @sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT PK_' + @TableName + ' PRIMARY KEY (Id)';
        EXEC sp_executesql @sql;
        PRINT '  -> PK ajoutée';

        SET @ProcessedTables = @ProcessedTables + 1;

    END TRY
    BEGIN CATCH
        PRINT '  [ERREUR] ' + ERROR_MESSAGE();
        SET @ErrorTables = @ErrorTables + 1;
    END CATCH

    FETCH NEXT FROM table_cursor INTO @TableName;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;

-- ============================================================================
-- Résumé
-- ============================================================================
PRINT '';
PRINT '============================================';
PRINT 'RESUME';
PRINT '============================================';
PRINT 'Tables sans PK trouvées : ' + CAST(@TotalTables AS VARCHAR);
PRINT 'Tables corrigées        : ' + CAST(@ProcessedTables AS VARCHAR);
PRINT 'Erreurs                 : ' + CAST(@ErrorTables AS VARCHAR);
PRINT '';

-- ============================================================================
-- Vérification : Tables encore sans PK
-- ============================================================================
IF EXISTS (
    SELECT 1
FROM sys.tables t
WHERE t.is_ms_shipped = 0
    AND t.name NOT LIKE '%_History'
    AND t.name NOT IN (SELECT TableName
    FROM @ExcludedTables)
    AND NOT EXISTS (SELECT 1
    FROM sys.indexes i
    WHERE i.object_id = t.object_id AND i.is_primary_key = 1)
)
BEGIN
    PRINT 'ATTENTION - Tables encore sans PK :';
    SELECT t.name AS TableSansPK
    FROM sys.tables t
    WHERE t.is_ms_shipped = 0
        AND t.name NOT LIKE '%_History'
        AND t.name NOT IN (SELECT TableName
        FROM @ExcludedTables)
        AND NOT EXISTS (SELECT 1
        FROM sys.indexes i
        WHERE i.object_id = t.object_id AND i.is_primary_key = 1)
    ORDER BY t.name;
END
ELSE
BEGIN
    PRINT 'Toutes les tables ont une clé primaire.';
END

PRINT '';
PRINT 'Migration terminée.';

