DATABASE.SQL

USE master;
GO

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'ChileSSOT_ETL')
BEGIN
    CREATE DATABASE ChileSSOT_ETL
    ON PRIMARY 
    (
        NAME = ChileSSOT_ETL_Data,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ChileSSOT_ETL.mdf',
        SIZE = 500MB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 100MB
    )
    LOG ON 
    (
        NAME = ChileSSOT_ETL_Log,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\ChileSSOT_ETL_Log.ldf',
        SIZE = 100MB,
        MAXSIZE = 1GB,
        FILEGROWTH = 50MB
    );
    
    PRINT '✓ Base de datos ChileSSOT_ETL creada exitosamente';
END
ELSE
BEGIN
    PRINT '⚠ Base de datos ChileSSOT_ETL ya existe';
END
GO

USE ChileSSOT_ETL;
GO

-- ===========================================================================
-- PASO 2: CREAR ESQUEMAS
-- ===========================================================================
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'RAW')
BEGIN
    EXEC('CREATE SCHEMA RAW AUTHORIZATION dbo');
    PRINT '✓ Esquema RAW creado';
END

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'STG')
BEGIN
    EXEC('CREATE SCHEMA STG AUTHORIZATION dbo');
    PRINT '✓ Esquema STG creado';
END

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'DIM')
BEGIN
    EXEC('CREATE SCHEMA DIM AUTHORIZATION dbo');
    PRINT '✓ Esquema DIM creado';
END

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'FACT')
BEGIN
    EXEC('CREATE SCHEMA FACT AUTHORIZATION dbo');
    PRINT '✓ Esquema FACT creado';
END

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'AUDIT')
BEGIN
    EXEC('CREATE SCHEMA AUDIT AUTHORIZATION dbo');
    PRINT '✓ Esquema AUDIT creado';
END
GO

-- ===========================================================================
-- PASO 3: TABLA DE AUDITORÍA
-- ===========================================================================
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ETL_Execution_Log' AND schema_id = SCHEMA_ID('AUDIT'))
BEGIN
    CREATE TABLE [AUDIT].[ETL_Execution_Log] (
        LogID INT IDENTITY(1,1) PRIMARY KEY,
        PackageName NVARCHAR(255) NOT NULL,
        ExecutionStartTime DATETIME2 DEFAULT GETDATE(),
        ExecutionEndTime DATETIME2 NULL,
        ExecutionStatus NVARCHAR(50) NULL, -- Success, Failed, Running
        RowsProcessed INT NULL,
        ErrorMessage NVARCHAR(MAX) NULL,
        SourceFile NVARCHAR(500) NULL,
        TargetTable NVARCHAR(255) NULL
    );
    
    CREATE INDEX IX_ETL_Log_StartTime ON [AUDIT].[ETL_Execution_Log](ExecutionStartTime);
    CREATE INDEX IX_ETL_Log_Status ON [AUDIT].[ETL_Execution_Log](ExecutionStatus);
    
    PRINT '✓ Tabla AUDIT.ETL_Execution_Log creada';
END
GO

-- ===========================================================================
-- PASO 4: TABLAS RAW PARA CARGA DE EXCEL
-- ===========================================================================

-- Tabla RAW para Ministerio Público
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Excel_MP_Raw' AND schema_id = SCHEMA_ID('RAW'))
BEGIN
    DROP TABLE [RAW].[Excel_MP_Raw];
    PRINT '⚠ Tabla anterior RAW.Excel_MP_Raw eliminada';
END

CREATE TABLE [RAW].[Excel_MP_Raw] (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LoadDate DATETIME2 DEFAULT GETDATE(),
    SourceFile NVARCHAR(500) NOT NULL,
    SheetName NVARCHAR(255) NOT NULL,
    RowNumber INT NOT NULL,
    RowData NVARCHAR(MAX) NOT NULL,
    ProcessedFlag BIT DEFAULT 0,
    ProcessedDate DATETIME2 NULL
);

CREATE INDEX IX_MP_LoadDate ON [RAW].[Excel_MP_Raw](LoadDate);
CREATE INDEX IX_MP_SheetName ON [RAW].[Excel_MP_Raw](SheetName);
CREATE INDEX IX_MP_ProcessedFlag ON [RAW].[Excel_MP_Raw](ProcessedFlag);

PRINT '✓ Tabla RAW.Excel_MP_Raw creada';
GO

-- Tabla RAW para Policiales
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Excel_Policiales_Raw' AND schema_id = SCHEMA_ID('RAW'))
BEGIN
    DROP TABLE [RAW].[Excel_Policiales_Raw];
    PRINT '⚠ Tabla anterior RAW.Excel_Policiales_Raw eliminada';
END

CREATE TABLE [RAW].[Excel_Policiales_Raw] (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LoadDate DATETIME2 DEFAULT GETDATE(),
    SourceFile NVARCHAR(500) NOT NULL,
    SheetName NVARCHAR(255) NOT NULL,
    RowNumber INT NOT NULL,
    RowData NVARCHAR(MAX) NOT NULL,
    ProcessedFlag BIT DEFAULT 0,
    ProcessedDate DATETIME2 NULL
);

CREATE INDEX IX_Pol_LoadDate ON [RAW].[Excel_Policiales_Raw](LoadDate);
CREATE INDEX IX_Pol_SheetName ON [RAW].[Excel_Policiales_Raw](SheetName);
CREATE INDEX IX_Pol_ProcessedFlag ON [RAW].[Excel_Policiales_Raw](ProcessedFlag);

PRINT '✓ Tabla RAW.Excel_Policiales_Raw creada';
GO

-- ===========================================================================
-- PASO 5: STORED PROCEDURES DE UTILIDAD
-- ===========================================================================

-- SP para limpiar tablas RAW
CREATE OR ALTER PROCEDURE [RAW].[usp_TruncateRawTables]
    @TableName NVARCHAR(100) = NULL -- NULL = todas, 'MP' o 'Policiales'
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF @TableName IS NULL OR @TableName = 'MP'
        BEGIN
            TRUNCATE TABLE [RAW].[Excel_MP_Raw];
            PRINT '✓ Tabla RAW.Excel_MP_Raw truncada';
        END
        
        IF @TableName IS NULL OR @TableName = 'Policiales'
        BEGIN
            TRUNCATE TABLE [RAW].[Excel_Policiales_Raw];
            PRINT '✓ Tabla RAW.Excel_Policiales_Raw truncada';
        END
        
        RETURN 0;
    END TRY
    BEGIN CATCH
        PRINT '✗ Error: ' + ERROR_MESSAGE();
        RETURN -1;
    END CATCH
END
GO

PRINT '✓ SP RAW.usp_TruncateRawTables creado';
GO

-- SP para consultar datos de hojas específicas
CREATE OR ALTER PROCEDURE [RAW].[usp_GetSheetData]
    @SourceType NVARCHAR(50), -- 'MP' o 'Policiales'
    @SheetName NVARCHAR(255) = NULL,
    @TopRows INT = 100
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'
    SELECT TOP (@TopRows)
        ID,
        LoadDate,
        SourceFile,
        SheetName,
        RowNumber,
        RowData,
        ProcessedFlag
    FROM [RAW].[Excel_' + @SourceType + N'_Raw]
    WHERE 1=1';
    
    IF @SheetName IS NOT NULL
        SET @SQL = @SQL + N' AND SheetName = @SheetName';
    
    SET @SQL = @SQL + N' ORDER BY RowNumber';
    
    EXEC sp_executesql @SQL, 
        N'@TopRows INT, @SheetName NVARCHAR(255)', 
        @TopRows, @SheetName;
END
GO

PRINT '✓ SP RAW.usp_GetSheetData creado';
GO

-- SP para ver estadísticas de carga
CREATE OR ALTER PROCEDURE [RAW].[usp_GetLoadStatistics]
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        'MP' as SourceType,
        COUNT(DISTINCT SheetName) as TotalSheets,
        COUNT(*) as TotalRows,
        MIN(LoadDate) as FirstLoad,
        MAX(LoadDate) as LastLoad,
        SUM(CASE WHEN ProcessedFlag = 1 THEN 1 ELSE 0 END) as ProcessedRows,
        SUM(CASE WHEN ProcessedFlag = 0 THEN 1 ELSE 0 END) as PendingRows
    FROM [RAW].[Excel_MP_Raw]
    
    UNION ALL
    
    SELECT 
        'Policiales' as SourceType,
        COUNT(DISTINCT SheetName),
        COUNT(*),
        MIN(LoadDate),
        MAX(LoadDate),
        SUM(CASE WHEN ProcessedFlag = 1 THEN 1 ELSE 0 END),
        SUM(CASE WHEN ProcessedFlag = 0 THEN 1 ELSE 0 END)
    FROM [RAW].[Excel_Policiales_Raw];
END
GO

PRINT '✓ SP RAW.usp_GetLoadStatistics creado';
GO

-- SP para parsear datos delimitados
CREATE OR ALTER PROCEDURE [RAW].[usp_ParseDelimitedData]
    @SourceType NVARCHAR(50), -- 'MP' o 'Policiales'
    @SheetName NVARCHAR(255),
    @Delimiter CHAR(1) = '|'
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = N'
    SELECT 
        RowNumber,
        VALUE as ColumnValue,
        ROW_NUMBER() OVER (PARTITION BY RowNumber ORDER BY (SELECT NULL)) as ColumnIndex
    FROM [RAW].[Excel_' + @SourceType + N'_Raw]
    CROSS APPLY STRING_SPLIT(RowData, ''' + @Delimiter + N''')
    WHERE SheetName = @SheetName
    ORDER BY RowNumber, ColumnIndex';
    
    EXEC sp_executesql @SQL, 
        N'@SheetName NVARCHAR(255)', 
        @SheetName;
END
GO

PRINT '✓ SP RAW.usp_ParseDelimitedData creado';
GO

-- ===========================================================================
-- PASO 6: VISTAS DE CONSULTA RÁPIDA
-- ===========================================================================

-- Vista de hojas disponibles
CREATE OR ALTER VIEW [RAW].[vw_AvailableSheets]
AS
SELECT 
    'MP' as SourceType,
    SheetName,
    COUNT(*) as RowCount,
    MAX(LoadDate) as LastLoadDate,
    MIN(RowNumber) as FirstRow,
    MAX(RowNumber) as LastRow
FROM [RAW].[Excel_MP_Raw]
GROUP BY SheetName

UNION ALL

SELECT 
    'Policiales' as SourceType,
    SheetName,
    COUNT(*),
    MAX(LoadDate),
    MIN(RowNumber),
    MAX(RowNumber)
FROM [RAW].[Excel_Policiales_Raw]
GROUP BY SheetName;
GO

PRINT '✓ Vista RAW.vw_AvailableSheets creada';
GO

-- ===========================================================================
-- PASO 7: CONFIGURACIÓN DE PERMISOS (OPCIONAL)
-- ===========================================================================

-- Si necesitas un usuario específico para SSIS
-- CREATE LOGIN ssis_etl_user WITH PASSWORD = 'Tu_Password_Seguro123!';
-- CREATE USER ssis_etl_user FOR LOGIN ssis_etl_user;
-- GRANT CONTROL ON SCHEMA::RAW TO ssis_etl_user;
-- GRANT CONTROL ON SCHEMA::AUDIT TO ssis_etl_user;

-- ===========================================================================
-- PASO 8: VERIFICACIÓN FINAL
-- ===========================================================================
PRINT '';
PRINT '========================================================================';
PRINT 'CONFIGURACIÓN COMPLETADA - RESUMEN';
PRINT '========================================================================';

SELECT 
    'Base de Datos' as Objeto,
    DB_NAME() as Nombre,
    'Activa' as Estado
UNION ALL
SELECT 'Esquema', name, 'Creado'
FROM sys.schemas 
WHERE name IN ('RAW', 'STG', 'DIM', 'FACT', 'AUDIT')
UNION ALL
SELECT 'Tabla', SCHEMA_NAME(schema_id) + '.' + name, 'Creada'
FROM sys.tables
WHERE schema_id IN (SCHEMA_ID('RAW'), SCHEMA_ID('AUDIT'))
UNION ALL
SELECT 'Stored Procedure', SCHEMA_NAME(schema_id) + '.' + name, 'Creado'
FROM sys.procedures
WHERE schema_id = SCHEMA_ID('RAW')
UNION ALL
SELECT 'Vista', SCHEMA_NAME(schema_id) + '.' + name, 'Creada'
FROM sys.views
WHERE schema_id = SCHEMA_ID('RAW');

PRINT '';
PRINT '✓ TODO LISTO - Puedes ejecutar el paquete SSIS';
PRINT '========================================================================';
GO