SET NOCOUNT ON;
SET XACT_ABORT OFF; -- Permitir manejo manual de transacciones

-- =============================================================
-- VARIABLES GLOBALES Y CONTROL DE ERRORES
-- =============================================================
DECLARE @CriticalErrorOccurred BIT = 0;
DECLARE @DropSql NVARCHAR(MAX),
    @RecreateSql NVARCHAR(MAX),
    @TableName NVARCHAR(128),
    @IdUnique NVARCHAR(128),
    @UseGuidPk BIT,
    @IdentityRemovalOnly BIT,
    @SQL NVARCHAR(MAX);
DECLARE @IdentityCol NVARCHAR(128),
    @PkCol NVARCHAR(128),
    @PkName NVARCHAR(128),
    @DataType NVARCHAR(128),
    @Precision INT,
    @Scale INT,
    @FullDataType NVARCHAR(256),
    @TempColName NVARCHAR(128);
DECLARE @idColExists BIT,
    @idIsIdentity BIT,
    @UQName NVARCHAR(128),
    @TableName_tr NVARCHAR(128),
    @LegacyColumn NVARCHAR(128),
    @SQL_tr NVARCHAR(MAX),
    @TrgName NVARCHAR(128);
DECLARE @DroppedCount INT, 
    @RemainingCount INT, 
    @DepId INT,
    @colName NVARCHAR(128);

-- =============================================================
-- PREPARACIÓN: CREACIÓN DE TABLAS TEMPORALES
-- =============================================================
IF OBJECT_ID('tempdb..#TablesToProcess') IS NOT NULL
    DROP TABLE #TablesToProcess;

CREATE TABLE #TablesToProcess (
    TableName NVARCHAR(128) COLLATE DATABASE_DEFAULT PRIMARY KEY,
    IdUnique NVARCHAR(128) COLLATE DATABASE_DEFAULT NULL,
    UseGuidPk BIT DEFAULT 0,
    IdentityRemovalOnly BIT DEFAULT 0
);

IF OBJECT_ID('tempdb..#Dependencies') IS NOT NULL
    DROP TABLE #Dependencies;

CREATE TABLE #Dependencies (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    DependencyType VARCHAR(30) COLLATE DATABASE_DEFAULT,
    ObjectName NVARCHAR(256) COLLATE DATABASE_DEFAULT,
    ObjectType VARCHAR(30) COLLATE DATABASE_DEFAULT,
    DropSQL NVARCHAR(MAX) COLLATE DATABASE_DEFAULT,
    RecreateSQL NVARCHAR(MAX) COLLATE DATABASE_DEFAULT,
    IsDropped BIT DEFAULT 0
);

IF OBJECT_ID('tempdb..#TriggersCreated') IS NOT NULL
    DROP TABLE #TriggersCreated;

CREATE TABLE #TriggersCreated (
    TriggerName NVARCHAR(256) COLLATE DATABASE_DEFAULT,
    TableName NVARCHAR(128) COLLATE DATABASE_DEFAULT,
    IsEnabled BIT DEFAULT 0
);

-- =============================================================
-- CARGA DE DATOS: TABLAS A PROCESAR
-- =============================================================
PRINT '';
PRINT '========================================';
PRINT 'PROCESO DE MIGRACIÓN - INICIO';
PRINT '========================================';
PRINT '';

INSERT INTO #TablesToProcess (tablename, idunique, UseGuidPk, IdentityRemovalOnly)
VALUES 
    ('SAACAMPOS', NULL, 0, 0), ('SAACXC', NULL, 1, 0), ('SAACXP', NULL, 1, 0), ('SAAGRUPOS', NULL, 0, 0),
    ('SAANULAC', NULL, 0, 0), ('SAAOPER', NULL, 0, 0), ('SAAUTORIZ', NULL, 0, 0), ('SACLICNV', NULL, 0, 0),
    ('SACLIE', 'CODCLIE', 0, 0), ('SACLILOT', NULL, 0, 0), ('SACLIOFE', NULL, 0, 0), ('SACMEC', 'CODMECA', 0, 0),
    ('SACODBAR', NULL, 0, 0), ('SACOMP', NULL, 1, 0), ('SACONF', 'CODSUCU', 0, 0), ('SACONV', 'CODCONV', 0, 0),
    ('SACORRELSIS', NULL, 0, 0), ('SACORTEZ', NULL, 0, 0), ('SACVEN', NULL, 0, 0), ('SADEPO', 'CODUBIC', 0, 0),
    ('SAEQUI', NULL, 0, 0), ('SAESTA', 'CODESTA', 0, 0), ('SAEXIS', NULL, 0, 0), ('SAFACT', NULL, 1, 0),
    ('SAFALO', NULL, 0, 0), ('SAFLDREF', NULL, 0, 0), ('SAICLI', NULL, 0, 0), ('SAIMEC', NULL, 0, 0),
    ('SAINITI', NULL, 0, 0), ('SAINSTA', NULL, 0, 0), ('SAIPACOM', NULL, 1, 0), ('SAIPACXC', NULL, 1, 0),
    ('SAIPACXP', NULL, 1, 0), ('SAIPAPRV', NULL, 0, 0), ('SAIPAVTA', NULL, 1, 0), ('SAIPRD', NULL, 0, 0),
    ('SAIPRV', NULL, 0, 0), ('SAITCV', NULL, 0, 0), ('SAITEMCOM', NULL, 1, 0), ('SAITEMFAC', NULL, 1, 0),
    ('SAITEMOPI', NULL, 1, 0), ('SAITEMPLANI', NULL, 0, 0), ('SAITEO', NULL, 0, 0), ('SAITFL', NULL, 0, 0),
    ('SAIVEN', NULL, 0, 0), ('SALOTE', NULL, 0, 0), ('SAMECA', 'CODMECA', 0, 0), ('SAOFER', 'NUMEROD', 0, 0),
    ('SAOPEI', NULL, 1, 0), ('SAOPER', 'CODOPER', 0, 0), ('SAPAGCXC', NULL, 1, 0), ('SAPAGCXP', NULL, 1, 0),
    ('SAPART', NULL, 0, 0), ('SAPLANI', NULL, 0, 0), ('SAPRIM', NULL, 0, 0), ('SAPRIMCOM', NULL, 0, 0),
    ('SAPRNGIROS', NULL, 0, 0), ('SAPROD', 'CODPROD', 0, 0), ('SAPROV', 'CODPROV', 0, 0),
    ('SAPRSRI', NULL, 0, 0), ('SAPTOSCLIENTE', NULL, 0, 0), ('SAPVPR', NULL, 0, 0), ('SAREPO', NULL, 0, 0),
    ('SARGOCAT', NULL, 0, 0), ('SARGORET', NULL, 0, 0), ('SARGOTJT', NULL, 0, 0), ('SASEPRCOM', NULL, 0, 0),
    ('SASEPRFAC', NULL, 1, 0), ('SASEPROPI', NULL, 1, 0), ('SASERI', NULL, 0, 0), ('SASERV', 'CODSERV', 0, 0),
    ('SASUCURSAL', 'CODSUCU', 0, 0), ('SATARJ', 'CODTARJ', 0, 0), ('SATAXCOM', NULL, 1, 0),
    ('SATAXCXC', NULL, 1, 0), ('SATAXCXP', NULL, 1, 0), ('SATAXES', 'CODTAXS', 0, 0), ('SATAXITC', NULL, 1, 0),
    ('SATAXITF', NULL, 1, 0), ('SATAXPRD', NULL, 0, 0), ('SATAXSRV', NULL, 0, 0), ('SATAXVTA', NULL, 1, 0),
    ('SATRAN', 'CODTRAN', 0, 0), ('SAUSRREP', NULL, 0, 0), ('SAVEND', 'CODVEND', 0, 0),
    ('SAZONA', 'CODZONA', 0, 0), ('SBAPPD', NULL, 0, 0), ('SBAPPM', NULL, 0, 0), ('SBBANC', NULL, 0, 0),
    ('SBBENE', NULL, 0, 0), ('SBCOBA', NULL, 0, 0), ('SBCONC', NULL, 0, 0), ('SBCONF', NULL, 0, 0),
    ('SBCTAP', NULL, 0, 0), ('SBCTAS', NULL, 0, 0), ('SBDEFPD', NULL, 0, 0), ('SBDEFPM', NULL, 0, 0),
    ('SBDESA', NULL, 0, 0), ('SBDIFE', NULL, 0, 0), ('SBDOPF', NULL, 0, 0), ('SBDTRN', NULL, 0, 0),
    ('SBFDCCONS', NULL, 0, 0), ('SBFDCDET', NULL, 0, 0), ('SBFDCPER', NULL, 0, 0), ('SBFDCREP', NULL, 0, 0),
    ('SBFMTS', NULL, 0, 0), ('SBMONE', NULL, 0, 0), ('SBOPCTAS', NULL, 0, 0), ('SBOPCXPP', NULL, 0, 0),
    ('SBOPDCTS', NULL, 0, 0), ('SBOPDPAG', NULL, 0, 0), ('SBOPFR', NULL, 0, 0), ('SBOPMSTR', NULL, 0, 0),
    ('SBOPPGOS', NULL, 0, 0), ('SBPRXC', NULL, 0, 0), ('SBRPTD', NULL, 0, 0), ('SBTRAN', NULL, 0, 0),
    ('SBTRTR', NULL, 0, 0), ('SSAUTR', NULL, 0, 0), ('SSDTAA', NULL, 0, 0), ('SSFLDS', NULL, 0, 0),
    ('SSFMTS', NULL, 0, 0), ('SSNIVL', NULL, 0, 0), ('SSUSRS', NULL, 0, 0), ('STIB01', NULL, 0, 0), 
    ('STIB02', NULL, 0, 0), ('SAAUDITEM', NULL, 0, 0), ('SAAUDLOGS', NULL, 0, 0), ('SAAUDTRAN', NULL, 0, 0), 
    ('SASETTINGS', NULL, 0, 0), ('SAUSERAUDIT', NULL, 0, 0), ('SAPAIS', NULL, 0, 1), ('SAESTADO', NULL, 0, 1), 
    ('SACIUDAD', NULL, 0, 1), ('SAMUNICIPIO', NULL, 0, 1);

-- Insertar tablas dinámicas basadas en patrones
INSERT INTO #TablesToProcess (tablename)
SELECT t.name
FROM sys.tables t
WHERE (t.name LIKE 'SACLIE_%') OR (t.name LIKE 'SAPROD_%') OR (t.name LIKE 'SAPROV_%') OR
    (t.name LIKE 'SADEPO_%') OR (t.name LIKE 'SAVEND_%') OR (t.name LIKE 'SASERV_%') OR
    (t.name LIKE 'SAMECA_%') OR (t.name LIKE 'SAOPER_%') OR (t.name LIKE 'SATRAN_%') OR
    (t.name LIKE 'SAINSTA_%') OR (t.name LIKE 'SAOPEI_%') OR (t.name LIKE 'SBBANC_%') OR
    (t.name LIKE 'SBBENE_%') OR (t.name LIKE 'SBCTAS_%') OR (t.name = 'DBTHIRD') OR
    (t.name LIKE 'SAITEPRDFAC_%') OR (t.name LIKE 'SAITEPRDCOM_%') OR (t.name LIKE 'SAITEPRDOPI_%') OR
    (t.name LIKE 'SAITESRVFAC_%') OR (t.name LIKE 'SAITESRVCOM_%') OR (t.name LIKE 'SAITESRVOPI_%') OR
    (t.name LIKE 'RT%') OR (t.name LIKE 'MP%') OR (t.name LIKE 'PR%')
    AND NOT EXISTS (SELECT 1 FROM #TablesToProcess WHERE TableName = t.name COLLATE DATABASE_DEFAULT)
ORDER BY t.name;

--PRINT 'Total de tablas a procesar: ' + CAST((SELECT COUNT(*) FROM #TablesToProcess) AS VARCHAR(10));
PRINT '';

-- =============================================================
-- FASE 0: DESARMANDO DEPENDENCIAS
-- =============================================================
BEGIN TRY
    PRINT '========================================';
    PRINT 'FASE 0: DESARMANDO DEPENDENCIAS';
    PRINT '========================================';
    PRINT '';

    -- 1. Recolectar Foreign Keys
    PRINT '→ Recolectando Foreign Keys...';
    INSERT INTO #Dependencies (DependencyType, ObjectName, ObjectType, DropSQL, RecreateSQL)
    SELECT 
        'FOREIGN KEY',
        QUOTENAME(fk.name),
        'CONSTRAINT',
        'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + 
        QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';',
        'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + 
        QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(fk.name) + 
        ' FOREIGN KEY(' + 
        STUFF((
            SELECT ',' + QUOTENAME(c.name)
            FROM sys.foreign_key_columns fkc
            JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + 
        ') REFERENCES ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + 
        QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + ' (' + 
        STUFF((
            SELECT ',' + QUOTENAME(c.name)
            FROM sys.foreign_key_columns fkc
            JOIN sys.columns c ON fkc.referenced_object_id = c.object_id AND fkc.referenced_column_id = c.column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ');'
    FROM sys.foreign_keys fk
    WHERE fk.parent_object_id IN (SELECT OBJECT_ID(TableName) FROM #TablesToProcess WHERE OBJECT_ID(TableName) IS NOT NULL)
       OR fk.referenced_object_id IN (SELECT OBJECT_ID(TableName) FROM #TablesToProcess WHERE OBJECT_ID(TableName) IS NOT NULL);

    PRINT '  ✓ FKs recolectadas: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

    -- 2. Recolectar Índices (no PK/UQ)
    PRINT '→ Recolectando Índices...';
    INSERT INTO #Dependencies (DependencyType, ObjectName, ObjectType, DropSQL, RecreateSQL)
    SELECT 
        'INDEX',
        QUOTENAME(i.name),
        'INDEX',
        'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + 
        QUOTENAME(OBJECT_NAME(i.object_id)) + ';',
        'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 
        i.type_desc + ' INDEX ' + QUOTENAME(i.name) + ' ON ' + 
        QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + '(' + 
        STUFF((
            SELECT ',' + QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')' + 
        ISNULL(' INCLUDE (' + STUFF((
            SELECT ',' + QUOTENAME(c.name)
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
            ORDER BY ic.index_column_id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')', '') + ';'
    FROM sys.indexes i
    WHERE i.object_id IN (SELECT OBJECT_ID(TableName) FROM #TablesToProcess WHERE OBJECT_ID(TableName) IS NOT NULL)
      AND i.is_primary_key = 0
      AND i.is_unique_constraint = 0
      AND i.type > 0;

    PRINT '  ✓ Índices recolectados: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

    -- 3. Recolectar Constraints (CHECK, DEFAULT)
    PRINT '→ Recolectando Constraints...';
    INSERT INTO #Dependencies (DependencyType, ObjectName, ObjectType, DropSQL, RecreateSQL)
    SELECT 
        o.type_desc,
        QUOTENAME(o.name),
        'CONSTRAINT',
        'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(o.parent_object_id)) + '.' + 
        QUOTENAME(OBJECT_NAME(o.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(o.name) + ';',
        'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(o.parent_object_id)) + '.' + 
        QUOTENAME(OBJECT_NAME(o.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(o.name) + 
        CASE o.type 
            WHEN 'D' THEN ' DEFAULT ' + dc.definition + ' FOR ' + QUOTENAME(c.name)
            WHEN 'C' THEN ' CHECK ' + cc.definition
        END + ';'
    FROM sys.objects o
    LEFT JOIN sys.check_constraints cc ON o.object_id = cc.object_id
    LEFT JOIN sys.default_constraints dc ON o.object_id = dc.object_id
    LEFT JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
    WHERE o.parent_object_id IN (SELECT OBJECT_ID(TableName) FROM #TablesToProcess WHERE OBJECT_ID(TableName) IS NOT NULL)
      AND o.type IN ('D', 'C');

    PRINT '  ✓ Constraints recolectados: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

    -- 4. Recolectar Triggers
    PRINT '→ Recolectando Triggers...';
    INSERT INTO #Dependencies (DependencyType, ObjectName, ObjectType, DropSQL, RecreateSQL)
    SELECT 
        'TRIGGER',
        QUOTENAME(tr.name),
        'TRIGGER',
        'DROP TRIGGER ' + QUOTENAME(OBJECT_SCHEMA_NAME(tr.parent_id)) + '.' + QUOTENAME(tr.name) + ';',
        sm.definition
    FROM sys.triggers tr
    JOIN sys.sql_modules sm ON tr.object_id = sm.object_id
    WHERE tr.parent_id IN (SELECT OBJECT_ID(TableName) FROM #TablesToProcess WHERE OBJECT_ID(TableName) IS NOT NULL)
      AND tr.is_ms_shipped = 0;

    PRINT '  ✓ Triggers recolectados: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

    -- 5. Recolectar Vistas, Procedimientos y Funciones (usando DMVs modernas)
    PRINT '→ Recolectando Vistas, Procedimientos y Funciones...';
    ;WITH ObjectDependencies AS (
        SELECT d.referencing_id
        FROM sys.sql_expression_dependencies AS d
        WHERE d.referenced_id IN (SELECT OBJECT_ID(TableName) FROM #TablesToProcess WHERE OBJECT_ID(TableName) IS NOT NULL)
        UNION ALL
        SELECT d.referencing_id
        FROM sys.sql_expression_dependencies AS d
        INNER JOIN ObjectDependencies AS od ON d.referenced_id = od.referencing_id
        WHERE d.referencing_id <> od.referencing_id
    )
    INSERT INTO #Dependencies (DependencyType, ObjectName, ObjectType, DropSQL, RecreateSQL)
    SELECT DISTINCT
        o.type_desc,
        QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(o.name),
        o.type,
        'DROP ' + CASE o.type 
            WHEN 'V' THEN 'VIEW' 
            WHEN 'P' THEN 'PROCEDURE' 
            ELSE 'FUNCTION' 
        END + ' ' + QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(o.name) + ';',
        sm.definition
    FROM ObjectDependencies od
    JOIN sys.objects o ON od.referencing_id = o.object_id
    JOIN sys.sql_modules sm ON o.object_id = sm.object_id
    WHERE o.type IN ('V', 'P', 'FN', 'IF', 'TF') 
      AND o.is_ms_shipped = 0
      AND NOT EXISTS (
          SELECT 1 FROM #Dependencies d 
          WHERE d.ObjectName = QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(o.name) COLLATE DATABASE_DEFAULT
      );

    PRINT '  ✓ Objetos dependientes recolectados: ' + CAST(@@ROWCOUNT AS VARCHAR(10));
    PRINT '';

    -- 6. Bucle de eliminación resiliente
    PRINT '→ Eliminando dependencias...';
    DECLARE @IterationCount INT = 0;
    DECLARE @MaxIterations INT = 10;
    
    SET @DroppedCount = 1;
    WHILE @DroppedCount > 0 AND @IterationCount < @MaxIterations
    BEGIN
        SET @DroppedCount = 0;
        SET @IterationCount = @IterationCount + 1;
        
        DECLARE dep_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT ID, DropSQL
        FROM #Dependencies
        WHERE IsDropped = 0
        ORDER BY CASE ObjectType 
            WHEN 'V' THEN 1
            WHEN 'P' THEN 2
            WHEN 'FN' THEN 3
            WHEN 'IF' THEN 3
            WHEN 'TF' THEN 3
            WHEN 'TRIGGER' THEN 4
            WHEN 'INDEX' THEN 5
            WHEN 'CONSTRAINT' THEN 6
            ELSE 7
        END;
        
        OPEN dep_cursor;
        FETCH NEXT FROM dep_cursor INTO @DepId, @DropSql;
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                EXEC sp_executesql @DropSql;
                UPDATE #Dependencies SET IsDropped = 1 WHERE ID = @DepId;
                SET @DroppedCount = @DroppedCount + 1;
            END TRY
            BEGIN CATCH
                -- Silencioso: intentar en próxima iteración
            END CATCH;
            
            FETCH NEXT FROM dep_cursor INTO @DepId, @DropSql;
        END;
        
        CLOSE dep_cursor;
        DEALLOCATE dep_cursor;
        
        IF @DroppedCount > 0
            PRINT '  Iteración ' + CAST(@IterationCount AS VARCHAR(10)) + ': ' + CAST(@DroppedCount AS VARCHAR(10)) + ' dependencias eliminadas';
    END;

    -- 7. Verificación final
    SELECT @RemainingCount = COUNT(*) FROM #Dependencies WHERE IsDropped = 0;
    
    IF @RemainingCount > 0
    BEGIN
        PRINT '';
        PRINT '  !! ADVERTENCIA: ' + CAST(@RemainingCount AS VARCHAR(10)) + ' dependencias no pudieron eliminarse';
        PRINT '  El proceso continuará, pero pueden ocurrir errores.';
        
        -- Mostrar las dependencias problemáticas
        SELECT ObjectName, ObjectType, DropSQL 
        FROM #Dependencies 
        WHERE IsDropped = 0;
    END
    ELSE
    BEGIN
        PRINT '  ✓ Todas las dependencias eliminadas exitosamente';
    END;
    
    PRINT '';
    PRINT '✓ FASE 0 COMPLETADA';
    PRINT '';

END TRY
BEGIN CATCH
    SET @CriticalErrorOccurred = 1;
    PRINT '';
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';
    PRINT '!! ERROR CRÍTICO EN FASE 0 (DESARME DE DEPENDENCIAS)';
    PRINT '!! No se puede continuar con el proceso de migración.';
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';
    PRINT '!! Error: ' + ERROR_MESSAGE();
    PRINT '!! Línea: ' + CAST(ERROR_LINE() AS VARCHAR(10));
    PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';
    PRINT '';
    GOTO Cleanup;
END CATCH;

-- =============================================================
-- FASE 1: PROCESAMIENTO DE PRIMARY KEYS (VERSIÓN CORREGIDA)
-- =============================================================
PRINT '========================================';
PRINT 'FASE 1: PROCESAMIENTO DE PRIMARY KEYS';
PRINT '========================================';
PRINT '';

DECLARE @TablesProcessed INT = 0;
DECLARE @TablesSkipped INT = 0;
DECLARE @TablesWithErrors INT = 0;

DECLARE table_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT TableName, IdUnique, UseGuidPk, IdentityRemovalOnly
FROM #TablesToProcess
ORDER BY TableName;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName, @IdUnique, @UseGuidPk, @IdentityRemovalOnly;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Validar que la tabla existe
    IF OBJECT_ID(@TableName) IS NULL
    BEGIN
        PRINT '  !! ADVERTENCIA: Tabla ' + QUOTENAME(@TableName) + ' no existe. Omitida.';
        SET @TablesSkipped = @TablesSkipped + 1;
        FETCH NEXT FROM table_cursor INTO @TableName, @IdUnique, @UseGuidPk, @IdentityRemovalOnly;
        CONTINUE;
    END;

    PRINT '→ Procesando tabla: ' + QUOTENAME(@TableName) + 
          ' [UseGuidPk=' + CAST(@UseGuidPk AS VARCHAR(1)) + 
          ', IdentityRemovalOnly=' + CAST(@IdentityRemovalOnly AS VARCHAR(1)) + ']';

    BEGIN TRANSACTION;
    BEGIN TRY
        -- =====================================================================
        -- VERIFICACIÓN: Tabla ya cumple con estándar
        -- =====================================================================
        DECLARE @isCompliant BIT = 0;
        DECLARE @hasIdIdentity BIT = 0;
        DECLARE @hasGuidPk BIT = 0;
        
        -- Verificar si tiene ID IDENTITY
        IF EXISTS (
            SELECT 1 FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@TableName)
              AND c.name = 'ID' COLLATE DATABASE_DEFAULT
              AND c.is_identity = 1
        )
            SET @hasIdIdentity = 1;

        -- Verificar si GUIDD es PK
        IF EXISTS (
            SELECT 1 FROM sys.columns c
            JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
            WHERE c.object_id = OBJECT_ID(@TableName)
              AND c.name = 'GUIDD' COLLATE DATABASE_DEFAULT
              AND i.is_primary_key = 1
        )
            SET @hasGuidPk = 1;

        -- Verificar si tiene ID IDENTITY como PK
        IF EXISTS (
            SELECT 1 FROM sys.columns c
            JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
            WHERE c.object_id = OBJECT_ID(@TableName)
              AND c.name = 'ID' COLLATE DATABASE_DEFAULT
              AND c.is_identity = 1
              AND i.is_primary_key = 1
        )
            SET @isCompliant = 1;

        -- Omitir si ya cumple con el estándar apropiado
        IF @IdentityRemovalOnly = 0
        BEGIN
            IF @UseGuidPk = 0 AND @isCompliant = 1
            BEGIN
                PRINT '  ✓ Ya tiene ID IDENTITY como PK. Omitida.';
                COMMIT TRANSACTION;
                SET @TablesSkipped = @TablesSkipped + 1;
                GOTO EndOfLoop;
            END;
            
            IF @UseGuidPk = 1 AND @hasIdIdentity = 1 AND @hasGuidPk = 1
            BEGIN
                PRINT '  ✓ Ya tiene ID IDENTITY y GUIDD como PK. Omitida.';
                COMMIT TRANSACTION;
                SET @TablesSkipped = @TablesSkipped + 1;
                GOTO EndOfLoop;
            END;
        END;

        -- =====================================================================
        -- PASO 1: IDENTIFICAR COLUMNAS IDENTITY Y PRIMARY KEY EXISTENTES
        -- =====================================================================
        SELECT @IdentityCol = NULL, @PkCol = NULL, @PkName = NULL;

        SELECT @IdentityCol = c.name
        FROM sys.identity_columns c
        WHERE c.object_id = OBJECT_ID(@TableName);

        SELECT TOP 1 @PkName = pk.name, @PkCol = c.name
        FROM sys.key_constraints pk
        JOIN sys.index_columns ic ON pk.parent_object_id = ic.object_id AND pk.unique_index_id = ic.index_id
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE pk.parent_object_id = OBJECT_ID(@TableName) 
          AND pk.type = 'PK';

        PRINT '  • Columna IDENTITY detectada: ' + ISNULL(QUOTENAME(@IdentityCol), 'NINGUNA');
        PRINT '  • Primary Key detectada: ' + ISNULL(QUOTENAME(@PkName) + ' en ' + QUOTENAME(@PkCol), 'NINGUNA');

        -- =====================================================================
        -- PASO 2: PROCESAR COLUMNA IDENTITY LEGACY (si existe y != 'ID')
        -- =====================================================================
        IF @IdentityCol IS NOT NULL AND @IdentityCol <> 'ID'
        BEGIN
            PRINT '  → Removiendo propiedad IDENTITY de ' + QUOTENAME(@IdentityCol);
            
            -- Obtener tipo de dato completo
            SELECT 
                @DataType = T.name, 
                @Precision = C.precision, 
                @Scale = C.scale
            FROM sys.columns C
            JOIN sys.types T ON C.user_type_id = T.user_type_id
            WHERE C.object_id = OBJECT_ID(@TableName) 
              AND C.name = @IdentityCol COLLATE DATABASE_DEFAULT;

            -- Construir tipo de dato completo
            SET @FullDataType = @DataType;
            IF @DataType IN ('numeric', 'decimal')
                SET @FullDataType = @DataType + '(' + CAST(@Precision AS NVARCHAR) + ',' + CAST(@Scale AS NVARCHAR) + ')';
            ELSE IF @DataType IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary')
            BEGIN
                DECLARE @MaxLength INT;
                SELECT @MaxLength = C.max_length 
                FROM sys.columns C 
                WHERE C.object_id = OBJECT_ID(@TableName) AND C.name = @IdentityCol COLLATE DATABASE_DEFAULT;
                
                IF @MaxLength = -1
                    SET @FullDataType = @DataType + '(MAX)';
                ELSE IF @DataType LIKE 'n%'
                    SET @FullDataType = @DataType + '(' + CAST(@MaxLength/2 AS NVARCHAR) + ')';
                ELSE
                    SET @FullDataType = @DataType + '(' + CAST(@MaxLength AS NVARCHAR) + ')';
            END;

            -- Si la columna legacy ES el PK actual, eliminarlo primero
            IF @PkCol = @IdentityCol AND @PkName IS NOT NULL
            BEGIN
                PRINT '  → Eliminando PK de ' + QUOTENAME(@IdentityCol);
                SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@PkName) + ';';
                EXEC sp_executesql @SQL;
                SET @PkName = NULL;
            END;

            -- Remover propiedad IDENTITY preservando valores
            SET @TempColName = @IdentityCol + '_temp_val';
            
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD ' + QUOTENAME(@TempColName) + ' ' + @FullDataType + ';';
            EXEC sp_executesql @SQL;
            
            SET @SQL = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + QUOTENAME(@TempColName) + ' = ' + QUOTENAME(@IdentityCol) + ';';
            EXEC sp_executesql @SQL;
            
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP COLUMN ' + QUOTENAME(@IdentityCol) + ';';
            EXEC sp_executesql @SQL;
            
            SET @SQL = 'EXEC sp_rename ''' + @TableName + '.' + @TempColName + ''', ''' + @IdentityCol + ''', ''COLUMN'';';
            EXEC sp_executesql @SQL;

            -- =====================================================================
            -- CASO ESPECIAL: IdentityRemovalOnly = 1
            -- =====================================================================
            IF @IdentityRemovalOnly = 1
            BEGIN
                PRINT '  → Modo: Solo remover IDENTITY (sin cambiar PKs)';
                
                SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ALTER COLUMN ' + QUOTENAME(@IdentityCol) + ' ' + @FullDataType + ' NOT NULL;';
                EXEC sp_executesql @SQL;

                DECLARE @DefaultNameForIdRO NVARCHAR(256) = 'DF_' + @TableName + '_' + @IdentityCol;
                SET @SQL = 'IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(''' + @TableName + ''') AND name = ''' + @DefaultNameForIdRO + ''') ' +
                           'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@DefaultNameForIdRO) + ' DEFAULT 0 FOR ' + QUOTENAME(@IdentityCol) + ';';
                EXEC sp_executesql @SQL;

                PRINT '  ✓ Tabla procesada (IdentityRemovalOnly)';
                COMMIT TRANSACTION;
                SET @TablesProcessed = @TablesProcessed + 1;
                GOTO EndOfLoop;
            END;
            
            -- Hacer NOT NULL y agregar DEFAULT para columna legacy
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ALTER COLUMN ' + QUOTENAME(@IdentityCol) + ' ' + @FullDataType + ' NOT NULL;';
            EXEC sp_executesql @SQL;

            DECLARE @DefaultNameLegacy NVARCHAR(256) = 'DF_' + @TableName + '_' + @IdentityCol;
            SET @SQL = 'IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(''' + @TableName + ''') AND name = ''' + @DefaultNameLegacy + ''') ' +
                       'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@DefaultNameLegacy) + ' DEFAULT 0 FOR ' + QUOTENAME(@IdentityCol) + ';';
            EXEC sp_executesql @SQL;
        END;

        -- Si es modo IdentityRemovalOnly pero no había columna IDENTITY, omitir
        IF @IdentityRemovalOnly = 1
        BEGIN
            PRINT '  ✓ Sin columna IDENTITY para procesar. Omitida.';
            COMMIT TRANSACTION;
            SET @TablesSkipped = @TablesSkipped + 1;
            GOTO EndOfLoop;
        END;

        -- =====================================================================
        -- PASO 3: AGREGAR COLUMNAS DE AUDITORÍA (CreatedAt, UpdatedAt)
        -- =====================================================================
        IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'CreatedAt' COLLATE DATABASE_DEFAULT AND object_id = OBJECT_ID(@TableName))
        BEGIN
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CreatedAt DATETIME NOT NULL CONSTRAINT DF_' + REPLACE(@TableName, ' ', '_') + '_CreatedAt DEFAULT GETDATE();';
            EXEC sp_executesql @SQL;
            PRINT '  • Columna CreatedAt agregada';
        END;

        IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'UpdatedAt' COLLATE DATABASE_DEFAULT AND object_id = OBJECT_ID(@TableName))
        BEGIN
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD UpdatedAt DATETIME NOT NULL CONSTRAINT DF_' + REPLACE(@TableName, ' ', '_') + '_UpdatedAt DEFAULT GETDATE();';
            EXEC sp_executesql @SQL;
            PRINT '  • Columna UpdatedAt agregada';
        END;

        -- =====================================================================
        -- PASO 4: GESTIONAR COLUMNA ID (debe existir como INT IDENTITY)
        -- =====================================================================
        SELECT @idColExists = 0, @idIsIdentity = 0;
        
        SELECT @idColExists = 1, @idIsIdentity = is_identity
        FROM sys.columns
        WHERE object_id = OBJECT_ID(@TableName) 
          AND name = 'ID' COLLATE DATABASE_DEFAULT;

        IF @idColExists = 1 AND @idIsIdentity = 0
        BEGIN
            PRINT '  → Recreando columna ID como IDENTITY';
            
            -- Si ID es PK, eliminar constraint
            IF @PkCol = 'ID' AND @PkName IS NOT NULL
            BEGIN
                SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@PkName) + ';';
                EXEC sp_executesql @SQL;
                SET @PkName = NULL;
            END;
            
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP COLUMN ID;';
            EXEC sp_executesql @SQL;
            
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD ID INT IDENTITY(1,1) NOT NULL;';
            EXEC sp_executesql @SQL;
        END
        ELSE IF @idColExists = 0
        BEGIN
            PRINT '  → Creando columna ID IDENTITY';
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD ID INT IDENTITY(1,1) NOT NULL;';
            EXEC sp_executesql @SQL;
        END
        ELSE
        BEGIN
            PRINT '  • Columna ID IDENTITY ya existe';
        END;

        -- =====================================================================
        -- PASO 5: ELIMINAR PRIMARY KEY EXISTENTE (si aún existe)
        -- =====================================================================
        -- Refrescar el PK actual después de todos los cambios
        SELECT @PkName = NULL, @PkCol = NULL;
        SELECT TOP 1 @PkName = pk.name, @PkCol = c.name
        FROM sys.key_constraints pk
        JOIN sys.index_columns ic ON pk.parent_object_id = ic.object_id AND pk.unique_index_id = ic.index_id
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE pk.parent_object_id = OBJECT_ID(@TableName) 
          AND pk.type = 'PK';

        IF @PkName IS NOT NULL
        BEGIN
            PRINT '  → Eliminando PK existente: ' + QUOTENAME(@PkName);
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@PkName) + ';';
            EXEC sp_executesql @SQL;
        END;

        -- =====================================================================
        -- PASO 6: CREAR NUEVA PRIMARY KEY SEGÚN UseGuidPk
        -- =====================================================================
        IF @UseGuidPk = 0
        BEGIN
            -- PK en ID
            PRINT '  → Creando Primary Key en columna ID';
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME('PK_' + REPLACE(@TableName, ' ', '_')) + ' PRIMARY KEY (ID);';
            EXEC sp_executesql @SQL;
        END
        ELSE
        BEGIN
            -- PK en GUIDD
            PRINT '  → Creando Primary Key en columna GUIDD';
            
            DECLARE @GuidColExists BIT = 0;
            IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = 'GUIDD' COLLATE DATABASE_DEFAULT)
                SET @GuidColExists = 1;

            IF @GuidColExists = 0
            BEGIN
                PRINT '  • Creando columna GUIDD';
                SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD GUIDD UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID();';
                EXEC sp_executesql @SQL;
            END
            ELSE
            BEGIN
                PRINT '  • Columna GUIDD ya existe';
            END;
            
            -- Asegurar que GUIDD tenga valores válidos
            SET @SQL = 'UPDATE ' + QUOTENAME(@TableName) + ' SET GUIDD = NEWID() WHERE GUIDD IS NULL OR GUIDD = ''00000000-0000-0000-0000-000000000000'';';
            EXEC sp_executesql @SQL;
            
            -- Asegurar que GUIDD sea NOT NULL
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ALTER COLUMN GUIDD UNIQUEIDENTIFIER NOT NULL;';
            EXEC sp_executesql @SQL;
            
            -- Agregar DEFAULT si no existe
            IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(@TableName) AND parent_column_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = 'GUIDD'))
            BEGIN
                SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT DF_' + REPLACE(@TableName, ' ', '_') + '_GUIDD DEFAULT NEWID() FOR GUIDD;';
                EXEC sp_executesql @SQL;
            END;

            -- Crear PK en GUIDD
            SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME('PK_' + REPLACE(@TableName, ' ', '_')) + ' PRIMARY KEY (GUIDD);';
            EXEC sp_executesql @SQL;
        END;

        PRINT '  ✓ Tabla ' + QUOTENAME(@TableName) + ' procesada exitosamente';
        PRINT '';
        COMMIT TRANSACTION;
        SET @TablesProcessed = @TablesProcessed + 1;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        SET @TablesWithErrors = @TablesWithErrors + 1;
        SET @CriticalErrorOccurred = 1;
        
        PRINT '  ✗ ERROR procesando tabla ' + QUOTENAME(@TableName);
        PRINT '    Mensaje: ' + ERROR_MESSAGE();
        PRINT '    Línea: ' + CAST(ERROR_LINE() AS VARCHAR(10));
        PRINT '';
    END CATCH;

    EndOfLoop:
    FETCH NEXT FROM table_cursor INTO @TableName, @IdUnique, @UseGuidPk, @IdentityRemovalOnly;
END;

CLOSE table_cursor;
DEALLOCATE table_cursor;

PRINT '';
PRINT '→ Resumen Fase 1:';
PRINT '  Procesadas: ' + CAST(@TablesProcessed AS VARCHAR(10));
PRINT '  Omitidas: ' + CAST(@TablesSkipped AS VARCHAR(10));
PRINT '  Con errores: ' + CAST(@TablesWithErrors AS VARCHAR(10));
PRINT '';
PRINT '✓ FASE 1 COMPLETADA';
PRINT '';
-- =============================================================
-- FASE 2: CREACIÓN DE UNIQUE CONSTRAINTS
-- =============================================================
PRINT '========================================';
PRINT 'FASE 2: UNIQUE CONSTRAINTS';
PRINT '========================================';
PRINT '';
DECLARE @DuplicateCheckSQL NVARCHAR(MAX), @DuplicateCount INT;
DECLARE @UniqueCreated INT = 0;
DECLARE @UniqueSkipped INT = 0;
DECLARE unique_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT TableName, IdUnique
FROM #TablesToProcess
WHERE IdUnique IS NOT NULL
AND OBJECT_ID(TableName) IS NOT NULL;
OPEN unique_cursor;
FETCH NEXT FROM unique_cursor INTO @TableName, @IdUnique;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Validar que la columna existe
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName) AND name = @IdUnique COLLATE DATABASE_DEFAULT)
BEGIN
PRINT '  !! Columna ' + QUOTENAME(@IdUnique) + ' no existe en ' + QUOTENAME(@TableName) + '. Omitida.';
SET @UniqueSkipped = @UniqueSkipped + 1;
FETCH NEXT FROM unique_cursor INTO @TableName, @IdUnique;
CONTINUE;
END;
-- Verificar duplicados
SET @DuplicateCount = 0;
SET @DuplicateCheckSQL = N'
    SELECT @cnt = COUNT(*)
    FROM (
        SELECT 1 AS Grp
        FROM ' + QUOTENAME(@TableName) + '
        WHERE ' + QUOTENAME(@IdUnique) + ' IS NOT NULL
        GROUP BY ' + QUOTENAME(@IdUnique) + '
        HAVING COUNT(*) > 1
    ) AS Duplicates;';

EXEC sp_executesql @DuplicateCheckSQL, N'@cnt INT OUTPUT', @cnt = @DuplicateCount OUTPUT;

IF @DuplicateCount > 0
BEGIN
    PRINT '  !! ADVERTENCIA: Duplicados en ' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IdUnique) + '. UNIQUE no creado.';
    SET @UniqueSkipped = @UniqueSkipped + 1;
END
ELSE
BEGIN
    BEGIN TRY
        SET @UQName = 'UQ_' + @TableName + '_' + @IdUnique;
        SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@UQName) + ' UNIQUE (' + QUOTENAME(@IdUnique) + ');';
        EXEC sp_executesql @SQL;
        
        PRINT '  ✓ UNIQUE creado: ' + QUOTENAME(@UQName);
        SET @UniqueCreated = @UniqueCreated + 1;
    END TRY
    BEGIN CATCH
        PRINT '  ✗ ERROR creando UNIQUE en ' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IdUnique) + ': ' + ERROR_MESSAGE();
        SET @UniqueSkipped = @UniqueSkipped + 1;
    END CATCH;
END;

FETCH NEXT FROM unique_cursor INTO @TableName, @IdUnique;
END;
CLOSE unique_cursor;
DEALLOCATE unique_cursor;
PRINT '';
PRINT '→ Resumen Fase 2:';
PRINT '  UNIQUE creados: ' + CAST(@UniqueCreated AS VARCHAR(10));
PRINT '  Omitidos: ' + CAST(@UniqueSkipped AS VARCHAR(10));
PRINT '';
PRINT '✓ FASE 2 COMPLETADA';
PRINT '';
-- =============================================================
-- FASE 3: GESTIÓN DE TRIGGERS DE SINCRONIZACIÓN
-- =============================================================
PRINT '========================================';
PRINT 'FASE 3: TRIGGERS DE SINCRONIZACIÓN';
PRINT '========================================';
PRINT '';
DECLARE @TriggersCreatedCount INT = 0;
DECLARE @TriggersSkippedCount INT = 0;
DECLARE trigger_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT TableName
FROM #TablesToProcess
WHERE IdentityRemovalOnly = 0
AND OBJECT_ID(TableName) IS NOT NULL;
OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @TableName_tr;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LegacyColumn = NULL;
-- Identificar columna legacy en orden de prioridad
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName_tr) AND name = 'NROUNICO' COLLATE DATABASE_DEFAULT)
    SET @LegacyColumn = 'NROUNICO';
ELSE IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName_tr) AND name = 'CODINST' COLLATE DATABASE_DEFAULT)
    SET @LegacyColumn = 'CODINST';
ELSE IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@TableName_tr) AND name = 'NROTURNO' COLLATE DATABASE_DEFAULT)
    SET @LegacyColumn = 'NROTURNO';

IF @LegacyColumn IS NOT NULL
BEGIN
    SET @TrgName = 'TR_ADM_IDENT_' + @TableName_tr;
    
    BEGIN TRY
        -- =====================================================================
        -- PASO 1: PREPARAR COLUMNA LEGACY
        -- =====================================================================
        DECLARE @LegacyColDataType NVARCHAR(256);
        
        SELECT @LegacyColDataType = T.name + 
            CASE 
                WHEN T.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') 
                    THEN '(' + IIF(C.max_length = -1, 'MAX', CAST(IIF(T.name LIKE 'n%', C.max_length/2, C.max_length) AS VARCHAR(10))) + ')'
                WHEN T.name IN ('decimal', 'numeric') 
                    THEN '(' + CAST(C.precision AS VARCHAR(10)) + ',' + CAST(C.scale AS VARCHAR(10)) + ')'
                ELSE ''
            END
        FROM sys.columns C
        JOIN sys.types T ON C.user_type_id = T.user_type_id
        WHERE C.object_id = OBJECT_ID(@TableName_tr) 
          AND C.name = @LegacyColumn COLLATE DATABASE_DEFAULT;

        -- Actualizar NULLs a 0
        SET @SQL_tr = 'UPDATE ' + QUOTENAME(@TableName_tr) + ' SET ' + QUOTENAME(@LegacyColumn) + ' = 0 WHERE ' + QUOTENAME(@LegacyColumn) + ' IS NULL;';
        EXEC sp_executesql @SQL_tr;

        -- Hacer NOT NULL
        SET @SQL_tr = 'ALTER TABLE ' + QUOTENAME(@TableName_tr) + ' ALTER COLUMN ' + QUOTENAME(@LegacyColumn) + ' ' + @LegacyColDataType + ' NOT NULL;';
        EXEC sp_executesql @SQL_tr;

        -- Agregar DEFAULT si no existe
        DECLARE @DefaultName NVARCHAR(256) = 'DF_' + @TableName_tr + '_' + @LegacyColumn;
        SET @SQL_tr = 'IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(''' + @TableName_tr + ''') AND name = ''' + @DefaultName + ''') ' +
                      'ALTER TABLE ' + QUOTENAME(@TableName_tr) + ' ADD CONSTRAINT ' + QUOTENAME(@DefaultName) + ' DEFAULT 0 FOR ' + QUOTENAME(@LegacyColumn) + ';';
        EXEC sp_executesql @SQL_tr;

        -- =====================================================================
        -- PASO 2: CREAR TRIGGER (DESHABILITADO INICIALMENTE)
        -- =====================================================================
        -- Eliminar si existe
        SET @SQL_tr = 'IF OBJECT_ID(''' + @TrgName + ''', ''TR'') IS NOT NULL DROP TRIGGER ' + QUOTENAME(@TrgName) + ';';
        EXEC sp_executesql @SQL_tr;
        
        -- Crear trigger
        SET @SQL_tr =
'CREATE TRIGGER ' + QUOTENAME(@TrgName) + ' ON ' + QUOTENAME(@TableName_tr) + '
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE t 
SET ' + QUOTENAME(@LegacyColumn) + ' = i.ID 
FROM ' + QUOTENAME(@TableName_tr) + ' AS t 
JOIN inserted AS i ON t.ID = i.ID 
WHERE t.' + QUOTENAME(@LegacyColumn) + ' = 0;
END;';
EXEC sp_executesql @SQL_tr;
        -- DESHABILITAR INMEDIATAMENTE
        SET @SQL_tr = 'DISABLE TRIGGER ' + QUOTENAME(@TrgName) + ' ON ' + QUOTENAME(@TableName_tr) + ';';
        EXEC sp_executesql @SQL_tr;

        -- Registrar en tabla de control
        INSERT INTO #TriggersCreated (TriggerName, TableName, IsEnabled)
        VALUES (@TrgName, @TableName_tr, 0);

        PRINT '  ✓ Trigger creado (deshabilitado): ' + QUOTENAME(@TrgName) + ' en ' + QUOTENAME(@TableName_tr);
        SET @TriggersCreatedCount = @TriggersCreatedCount + 1;

    END TRY
    BEGIN CATCH
        PRINT '  ✗ ERROR creando trigger ' + QUOTENAME(@TrgName) + ': ' + ERROR_MESSAGE();
        SET @TriggersSkippedCount = @TriggersSkippedCount + 1;
    END CATCH;
END
ELSE
BEGIN
    SET @TriggersSkippedCount = @TriggersSkippedCount + 1;
END;

FETCH NEXT FROM trigger_cursor INTO @TableName_tr;
END;
CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;
PRINT '';
PRINT '→ Resumen Fase 3:';
PRINT '  Triggers creados (deshabilitados): ' + CAST(@TriggersCreatedCount AS VARCHAR(10));
PRINT '  Tablas sin trigger: ' + CAST(@TriggersSkippedCount AS VARCHAR(10));
PRINT '';
PRINT '✓ FASE 3 COMPLETADA';
PRINT '';
-- =============================================================
-- FASE 4: RECONSTRUCCIÓN DE DEPENDENCIAS
-- =============================================================
PRINT '========================================';
PRINT 'FASE 4: RECONSTRUCCIÓN DE DEPENDENCIAS';
PRINT '========================================';
PRINT '';
DECLARE @DepsRecreated INT = 0;
DECLARE @DepsFailed INT = 0;
DECLARE dep_recreate_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT RecreateSQL, ObjectType, ObjectName
FROM #Dependencies
WHERE RecreateSQL IS NOT NULL
AND IsDropped = 1
ORDER BY CASE
WHEN ObjectType = 'FN' THEN 1
WHEN ObjectType = 'IF' THEN 1
WHEN ObjectType = 'TF' THEN 1
WHEN ObjectType = 'V' THEN 2
WHEN ObjectType = 'P' THEN 3
WHEN ObjectType = 'INDEX' THEN 4
WHEN ObjectType = 'CONSTRAINT' THEN 5
WHEN ObjectType = 'TRIGGER' THEN 6
ELSE 7
END;
DECLARE @ObjType VARCHAR(30), @ObjName NVARCHAR(256);
OPEN dep_recreate_cursor;
FETCH NEXT FROM dep_recreate_cursor INTO @RecreateSql, @ObjType, @ObjName;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC sp_executesql @RecreateSql;
SET @DepsRecreated = @DepsRecreated + 1;
    IF @DepsRecreated % 50 = 0
        PRINT '  → Recreados: ' + CAST(@DepsRecreated AS VARCHAR(10)) + ' objetos...';
        
END TRY
BEGIN CATCH
    SET @DepsFailed = @DepsFailed + 1;
    PRINT '  ✗ ERROR recreando ' + @ObjType + ' ' + @ObjName + ': ' + ERROR_MESSAGE();
END CATCH;

FETCH NEXT FROM dep_recreate_cursor INTO @RecreateSql, @ObjType, @ObjName;
END;
CLOSE dep_recreate_cursor;
DEALLOCATE dep_recreate_cursor;
PRINT '';
PRINT '→ Resumen Fase 4:';
PRINT '  Dependencias recreadas: ' + CAST(@DepsRecreated AS VARCHAR(10));
PRINT '  Fallos al recrear: ' + CAST(@DepsFailed AS VARCHAR(10));
PRINT '';
PRINT '✓ FASE 4 COMPLETADA';
PRINT '';
-- =============================================================
-- FASE 5: ESTANDARIZACIÓN DE DEFAULTS
-- =============================================================
PRINT '========================================';
PRINT 'FASE 5: ESTANDARIZACIÓN DE DEFAULTS';
PRINT '========================================';
PRINT '';
DECLARE @DefaultsApplied INT = 0;
-- =====================================================================
-- PARTE 1: DEFAULT 0 en columnas numéricas
-- =====================================================================
PRINT '→ Aplicando DEFAULT 0 a columnas numéricas...';
DECLARE numeric_default_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
t.name AS TableName,
c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE
t.name COLLATE DATABASE_DEFAULT IN (
SELECT TableName
FROM #TablesToProcess
WHERE IdentityRemovalOnly = 0
AND OBJECT_ID(TableName) IS NOT NULL
)
AND ty.name IN ('tinyint', 'smallint', 'int', 'bigint', 'numeric', 'decimal', 'smallmoney', 'money', 'float', 'real')
AND c.default_object_id = 0
AND c.is_identity = 0
AND c.name NOT IN ('ID', 'GUIDD');
OPEN numeric_default_cursor;
FETCH NEXT FROM numeric_default_cursor INTO @TableName, @colName;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) +
' ADD CONSTRAINT ' + QUOTENAME('DF_' + @TableName + '' + REPLACE(@colName, ' ', '')) +
' DEFAULT 0 FOR ' + QUOTENAME(@colName) + ';';
EXEC sp_executesql @SQL;
    SET @SQL = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + QUOTENAME(@colName) + ' = 0 WHERE ' + QUOTENAME(@colName) + ' IS NULL;';
    EXEC sp_executesql @SQL;
    
    SET @DefaultsApplied = @DefaultsApplied + 1;
END TRY
BEGIN CATCH
    -- Silencioso: probablemente ya existe el default
END CATCH;

FETCH NEXT FROM numeric_default_cursor INTO @TableName, @colName;
END;
CLOSE numeric_default_cursor;
DEALLOCATE numeric_default_cursor;
PRINT '  ✓ Defaults aplicados a columnas numéricas: ' + CAST(@DefaultsApplied AS VARCHAR(10));
-- =====================================================================
-- PARTE 2: DEFAULT NEWID() en columnas GUIDD
-- =====================================================================
PRINT '→ Aplicando DEFAULT NEWID() a columnas GUIDD...';
DECLARE @GuidDefaultsApplied INT = 0;
DECLARE guid_default_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT t.TableName
FROM #TablesToProcess t
JOIN sys.columns c ON OBJECT_ID(t.TableName) = c.object_id
WHERE t.UseGuidPk = 1
AND c.name = 'GUIDD' COLLATE DATABASE_DEFAULT
AND c.default_object_id = 0
AND OBJECT_ID(t.TableName) IS NOT NULL;
OPEN guid_default_cursor;
FETCH NEXT FROM guid_default_cursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @colName = 'GUIDD';
SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableName) +
' ADD CONSTRAINT ' + QUOTENAME('DF_' + @TableName + '_' + @colName) +
' DEFAULT NEWID() FOR ' + QUOTENAME(@colName) + ';';
EXEC sp_executesql @SQL;
    SET @SQL = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + QUOTENAME(@colName) + ' = NEWID() WHERE ' + QUOTENAME(@colName) + ' IS NULL OR ' + QUOTENAME(@colName) + ' = ''00000000-0000-0000-0000-000000000000'';';
    EXEC sp_executesql @SQL;
    
    SET @GuidDefaultsApplied = @GuidDefaultsApplied + 1;
END TRY
BEGIN CATCH
    -- Silencioso
END CATCH;

FETCH NEXT FROM guid_default_cursor INTO @TableName;
END;
CLOSE guid_default_cursor;
DEALLOCATE guid_default_cursor;
PRINT '  ✓ Defaults NEWID() aplicados: ' + CAST(@GuidDefaultsApplied AS VARCHAR(10));
PRINT '';
PRINT '✓ FASE 5 COMPLETADA';
PRINT '';
-- =============================================================
-- VERIFICACIÓN ANTES DE FASE 6
-- =============================================================
IF @CriticalErrorOccurred = 1
BEGIN
PRINT '';
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';
PRINT '!! ADVERTENCIA: Se detectaron errores críticos en fases anteriores';
PRINT '!! Los triggers TR_ADM_IDENT_* permanecerán DESHABILITADOS';
PRINT '!! por seguridad.';
PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';
PRINT '';
PRINT 'Para habilitar manualmente los triggers, ejecute:';
PRINT '';
SELECT 
    'ENABLE TRIGGER ' + QUOTENAME(TriggerName) + ' ON ' + QUOTENAME(TableName) + ';' AS CommandToRun
FROM #TriggersCreated
WHERE IsEnabled = 0;

GOTO Cleanup;
END;
-- =============================================================
-- FASE 6: HABILITACIÓN DE TRIGGERS DE SINCRONIZACIÓN
-- =============================================================
PRINT '========================================';
PRINT 'FASE 6: HABILITACIÓN DE TRIGGERS';
PRINT '========================================';
PRINT '';
PRINT '→ Habilitando triggers de sincronización...';
PRINT '';
DECLARE @TriggerName NVARCHAR(256);
DECLARE @TriggerTableName NVARCHAR(128);
DECLARE @EnableSQL NVARCHAR(MAX);
DECLARE @TriggersHabilitados INT = 0;
DECLARE @TriggersError INT = 0;
DECLARE trigger_enable_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT TriggerName, TableName
FROM #TriggersCreated
WHERE IsEnabled = 0
ORDER BY TableName;
OPEN trigger_enable_cursor;
FETCH NEXT FROM trigger_enable_cursor INTO @TriggerName, @TriggerTableName;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @EnableSQL = 'ENABLE TRIGGER ' + QUOTENAME(@TriggerName) + ' ON ' + QUOTENAME(@TriggerTableName) + ';';
EXEC sp_executesql @EnableSQL;
    -- Actualizar registro
    UPDATE #TriggersCreated 
    SET IsEnabled = 1 
    WHERE TriggerName = @TriggerName AND TableName = @TriggerTableName;
    
    PRINT '  ✓ Trigger habilitado: ' + QUOTENAME(@TriggerName) + ' en ' + QUOTENAME(@TriggerTableName);
    SET @TriggersHabilitados = @TriggersHabilitados + 1;
END TRY
BEGIN CATCH
    PRINT '  ✗ ERROR al habilitar ' + QUOTENAME(@TriggerName) + ': ' + ERROR_MESSAGE();
    SET @TriggersError = @TriggersError + 1;
END CATCH;

FETCH NEXT FROM trigger_enable_cursor INTO @TriggerName, @TriggerTableName;
END;
CLOSE trigger_enable_cursor;
DEALLOCATE trigger_enable_cursor;
PRINT '';
PRINT '→ Resumen Fase 6:';
PRINT '  Triggers habilitados: ' + CAST(@TriggersHabilitados AS VARCHAR(10));
IF @TriggersError > 0
BEGIN
PRINT '  Triggers con error: ' + CAST(@TriggersError AS VARCHAR(10));
PRINT '';
PRINT '  !! ADVERTENCIA: Algunos triggers no se pudieron habilitar';
END
ELSE
BEGIN
PRINT '  ✓ Todos los triggers habilitados correctamente';
END;
PRINT '';
PRINT '✓ FASE 6 COMPLETADA';
PRINT '';
-- Verificación final de triggers deshabilitados
DECLARE @DisabledTriggersCount INT;
SELECT @DisabledTriggersCount = COUNT(*)
FROM sys.triggers t
WHERE t.name LIKE 'TR_ADM_IDENT_%'
AND t.is_disabled = 1
AND t.parent_id IN (
SELECT OBJECT_ID(TableName)
FROM #TablesToProcess
WHERE IdentityRemovalOnly = 0
AND OBJECT_ID(TableName) IS NOT NULL
);
IF @DisabledTriggersCount > 0
BEGIN
PRINT '!! ADVERTENCIA: ' + CAST(@DisabledTriggersCount AS VARCHAR(10)) + ' triggers TR_ADM_IDENT_* permanecen DESHABILITADOS:';
PRINT '';
SELECT 
    t.name AS TriggerName,
    OBJECT_NAME(t.parent_id) AS TableName,
    'ENABLE TRIGGER ' + QUOTENAME(t.name) + ' ON ' + QUOTENAME(OBJECT_NAME(t.parent_id)) + ';' AS CommandToEnable
FROM sys.triggers t
WHERE t.name LIKE 'TR_ADM_IDENT_%'
  AND t.is_disabled = 1
  AND t.parent_id IN (
      SELECT OBJECT_ID(TableName) 
      FROM #TablesToProcess 
      WHERE IdentityRemovalOnly = 0 
        AND OBJECT_ID(TableName) IS NOT NULL
  );

PRINT '';
END;
-- =============================================================
-- LIMPIEZA Y RESUMEN FINAL
-- =============================================================
Cleanup:
PRINT '';
PRINT '========================================';
PRINT 'LIMPIEZA Y RESUMEN FINAL';
PRINT '========================================';
PRINT '';
-- Limpiar tablas temporales
IF OBJECT_ID('tempdb..#TablesToProcess') IS NOT NULL
DROP TABLE #TablesToProcess;
IF OBJECT_ID('tempdb..#Dependencies') IS NOT NULL
DROP TABLE #Dependencies;
IF OBJECT_ID('tempdb..#TriggersCreated') IS NOT NULL
DROP TABLE #TriggersCreated;
PRINT '✓ Tablas temporales eliminadas';
PRINT '';
-- =============================================================
-- REPORTE FINAL CONSOLIDADO
-- =============================================================
PRINT '';
PRINT '╔════════════════════════════════════════════════════════════════╗';
PRINT '║          PROCESO DE MIGRACIÓN COMPLETADO                       ║';
PRINT '╚════════════════════════════════════════════════════════════════╝';
PRINT '';
PRINT '→ RESUMEN DE RESULTADOS:';
PRINT '';
PRINT '  FASE 1 - Primary Keys:';
PRINT '    • Tablas procesadas: ' + CAST(@TablesProcessed AS VARCHAR(10));
PRINT '    • Tablas omitidas: ' + CAST(@TablesSkipped AS VARCHAR(10));
PRINT '    • Tablas con errores: ' + CAST(@TablesWithErrors AS VARCHAR(10));
PRINT '';
PRINT '  FASE 2 - UNIQUE Constraints:';
PRINT '    • UNIQUE creados: ' + CAST(@UniqueCreated AS VARCHAR(10));
PRINT '    • Omitidos: ' + CAST(@UniqueSkipped AS VARCHAR(10));
PRINT '';
PRINT '  FASE 3 - Triggers de Sincronización:';
PRINT '    • Triggers creados: ' + CAST(@TriggersCreatedCount AS VARCHAR(10));
PRINT '    • Omitidos: ' + CAST(@TriggersSkippedCount AS VARCHAR(10));
PRINT '';
PRINT '  FASE 4 - Reconstrucción de Dependencias:';
PRINT '    • Dependencias recreadas: ' + CAST(@DepsRecreated AS VARCHAR(10));
PRINT '    • Fallos: ' + CAST(@DepsFailed AS VARCHAR(10));
PRINT '';
PRINT '  FASE 5 - Estandarización:';
PRINT '    • Defaults aplicados: ' + CAST(@DefaultsApplied + @GuidDefaultsApplied AS VARCHAR(10));
PRINT '';
PRINT '  FASE 6 - Habilitación de Triggers:';
PRINT '    • Triggers habilitados: ' + CAST(@TriggersHabilitados AS VARCHAR(10));
PRINT '    • Errores: ' + CAST(@TriggersError AS VARCHAR(10));
PRINT '';
IF @CriticalErrorOccurred = 1
BEGIN
PRINT '╔════════════════════════════════════════════════════════════════╗';
PRINT '║  ⚠️  ADVERTENCIA: PROCESO COMPLETADO CON ERRORES CRÍTICOS     ║';
PRINT '╚════════════════════════════════════════════════════════════════╝';
PRINT '';
PRINT 'Se recomienda:';
PRINT '  1. Revisar los mensajes de error detallados arriba';
PRINT '  2. Verificar la integridad de los datos';
PRINT '  3. Habilitar triggers manualmente si es necesario';
PRINT '  4. Ejecutar pruebas exhaustivas antes de poner en producción';
END
ELSE
BEGIN
PRINT '╔════════════════════════════════════════════════════════════════╗';
PRINT '║  ✓ PROCESO COMPLETADO EXITOSAMENTE                            ║';
PRINT '╚════════════════════════════════════════════════════════════════╝';
PRINT '';
PRINT 'Acciones completadas:';
PRINT '  ✓ Todas las tablas estandarizadas';
PRINT '  ✓ Primary Keys configuradas correctamente';
PRINT '  ✓ UNIQUE constraints aplicados';
PRINT '  ✓ Triggers de sincronización habilitados';
PRINT '  ✓ Dependencias restauradas';
PRINT '  ✓ Defaults estandarizados';
PRINT '';
PRINT 'La base de datos está lista para su uso.';
END;
PRINT '';
PRINT '════════════════════════════════════════════════════════════════';
PRINT 'Fecha/Hora finalización: ' + CONVERT(VARCHAR(30), GETDATE(), 121);
PRINT '════════════════════════════════════════════════════════════════';
PRINT '';
-- Estadísticas finales adicionales
PRINT '→ VERIFICACIONES ADICIONALES:';
PRINT '';
-- Contar tablas con ID IDENTITY PK
DECLARE @TablesWithIdPk INT;
SELECT @TablesWithIdPk = COUNT(DISTINCT c.object_id)
FROM sys.columns c
JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.name = 'ID'
AND c.is_identity = 1
AND i.is_primary_key = 1
AND c.object_id IN (
SELECT OBJECT_ID(t.name)
FROM sys.tables t
WHERE t.type = 'U' AND t.is_ms_shipped = 0
);
PRINT '  • Tablas con ID IDENTITY PK: ' + CAST(@TablesWithIdPk AS VARCHAR(10));
-- Contar tablas con GUIDD PK
DECLARE @TablesWithGuidPk INT;
SELECT @TablesWithGuidPk = COUNT(DISTINCT c.object_id)
FROM sys.columns c
JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.name = 'GUIDD'
AND i.is_primary_key = 1
AND c.object_id IN (
SELECT OBJECT_ID(t.name)
FROM sys.tables t
WHERE t.type = 'U' AND t.is_ms_shipped = 0
);
PRINT '  • Tablas con GUIDD PK: ' + CAST(@TablesWithGuidPk AS VARCHAR(10));
-- Contar triggers TR_ADM_IDENT habilitados
DECLARE @EnabledSyncTriggers INT;
SELECT @EnabledSyncTriggers = COUNT(*)
FROM sys.triggers
WHERE name LIKE 'TR_ADM_IDENT_%'
AND is_disabled = 0;
PRINT '  • Triggers TR_ADM_IDENT activos: ' + CAST(@EnabledSyncTriggers AS VARCHAR(10));
PRINT '';
PRINT '════════════════════════════════════════════════════════════════';
PRINT '';
GO