-- V017__create_product_type.sql
-- PRD-001: ProductType — tipología dinámica de productos con flags de comportamiento + límites multimedia.
--
-- Cambios:
--   1. dbo.ProductType (flags + multimedia limits, SYSTEM_VERSIONING ON, retention 10 años).
--   2. Índice filtrado unique UQ_ProductType_Nombre_Activo (unicidad CI entre activos).
--   3. Índice cubriente IX_ProductType_IsActive_Cover.
--   4. Permiso 'catalogo:tipos:gestionar' + asignación a rol 'admin'.
--
-- Patrón: V016 (dbo.Rubro con SYSTEM_VERSIONING + PAGE compression + MERGE permisos).
-- Idempotente: seguro para re-ejecutar.
-- Reversa: V017_ROLLBACK.sql.
-- Run on: SIGCM2 (dev) y SIGCM2_Test (integration tests).
--
-- Notas:
--   - SIN seed de datos — PRD-008 (V018) seedea los 12 tipos legacy.
--   - SIN FK desde dbo.Product — PRD-002 agrega ALTER TABLE con FK.
--   - Invariante aplicada en Application: si AllowImages=0, los 4 campos multimedia son NULL (handler normaliza).
--   - MaxImages/MaxImageSizeMB/MaxImageWidth/MaxImageHeight: NULL = sin límite; >=1 = tope (validator rechaza <=0).
--   - Desviación del UDT: "0 = ilimitado" → usamos NULL (convención canónica). Ver PRD-001 archive-report.
--
-- SDD Design: engram sdd/prd-001-product-type-flags-multimedia/design

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

-- ═══════════════════════════════════════════════════════════════════════
-- 1. dbo.ProductType
-- ═══════════════════════════════════════════════════════════════════════

IF OBJECT_ID(N'dbo.ProductType', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ProductType (
        Id                INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductType PRIMARY KEY,
        Nombre            NVARCHAR(200)     COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

        -- Flags de comportamiento
        HasDuration       BIT               NOT NULL CONSTRAINT DF_ProductType_HasDuration      DEFAULT(0),
        RequiresText      BIT               NOT NULL CONSTRAINT DF_ProductType_RequiresText     DEFAULT(0),
        RequiresCategory  BIT               NOT NULL CONSTRAINT DF_ProductType_RequiresCategory DEFAULT(0),
        IsBundle          BIT               NOT NULL CONSTRAINT DF_ProductType_IsBundle         DEFAULT(0),

        -- Multimedia (AllowImages=0 => handler normaliza los 4 siguientes a NULL)
        AllowImages       BIT               NOT NULL CONSTRAINT DF_ProductType_AllowImages      DEFAULT(0),
        MaxImages         INT               NULL,  -- NULL = sin límite; >=1 tope (validator rechaza <=0)
        MaxImageSizeMB    DECIMAL(10,2)     NULL,  -- NULL = sin límite; DECIMAL(10,2) permite 0.5 MB, 2.75 MB
        MaxImageWidth     INT               NULL,  -- NULL = sin límite; >=1 px
        MaxImageHeight    INT               NULL,  -- NULL = sin límite; >=1 px

        -- Lifecycle
        IsActive          BIT               NOT NULL CONSTRAINT DF_ProductType_IsActive         DEFAULT(1),
        FechaCreacion     DATETIME2(3)      NOT NULL CONSTRAINT DF_ProductType_FechaCreacion    DEFAULT(SYSUTCDATETIME()),
        FechaModificacion DATETIME2(3)      NULL
    );
    PRINT 'Table dbo.ProductType created.';
END
ELSE
    PRINT 'Table dbo.ProductType already exists — skip.';
GO

-- ═══════════════════════════════════════════════════════════════════════
-- 2. SYSTEM_VERSIONING — ProductType
-- ═══════════════════════════════════════════════════════════════════════

IF COL_LENGTH('dbo.ProductType', 'ValidFrom') IS NULL
BEGIN
    ALTER TABLE dbo.ProductType
    ADD
        ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
            CONSTRAINT DF_ProductType_ValidFrom DEFAULT(SYSUTCDATETIME()),
        ValidTo   DATETIME2(3) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL
            CONSTRAINT DF_ProductType_ValidTo   DEFAULT(CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999')),
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
    PRINT 'ProductType: PERIOD FOR SYSTEM_TIME added.';
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.ProductType') AND temporal_type = 2)
BEGIN
    ALTER TABLE dbo.ProductType
    SET (SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProductType_History,
        HISTORY_RETENTION_PERIOD = 10 YEARS
    ));
    PRINT 'ProductType: SYSTEM_VERSIONING = ON (history: dbo.ProductType_History, retention: 10 years).';
END
ELSE
    PRINT 'ProductType: SYSTEM_VERSIONING already ON — skip.';
GO

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ProductType_History' AND schema_id = SCHEMA_ID('dbo'))
   AND NOT EXISTS (
       SELECT 1 FROM sys.partitions p
       JOIN sys.tables t ON t.object_id = p.object_id
       WHERE t.name = 'ProductType_History' AND p.data_compression = 2
   )
BEGIN
    ALTER TABLE dbo.ProductType_History REBUILD WITH (DATA_COMPRESSION = PAGE);
    PRINT 'ProductType_History: rebuilt with PAGE compression.';
END
GO

-- ═══════════════════════════════════════════════════════════════════════
-- 3. Índices
-- ═══════════════════════════════════════════════════════════════════════

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UQ_ProductType_Nombre_Activo' AND object_id = OBJECT_ID('dbo.ProductType'))
BEGIN
    CREATE UNIQUE INDEX UQ_ProductType_Nombre_Activo
        ON dbo.ProductType(Nombre)
        WHERE IsActive = 1;
    PRINT 'Index UQ_ProductType_Nombre_Activo created.';
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ProductType_IsActive_Cover' AND object_id = OBJECT_ID('dbo.ProductType'))
BEGIN
    CREATE INDEX IX_ProductType_IsActive_Cover
        ON dbo.ProductType(IsActive)
        INCLUDE (Nombre, HasDuration, RequiresText, RequiresCategory, IsBundle, AllowImages);
    PRINT 'Index IX_ProductType_IsActive_Cover created.';
END
GO

-- ═══════════════════════════════════════════════════════════════════════
-- 4. Permiso: catalogo:tipos:gestionar + asignación a rol 'admin'
-- ═══════════════════════════════════════════════════════════════════════

MERGE dbo.Permiso AS t
USING (VALUES
    ('catalogo:tipos:gestionar',
     N'Gestionar tipos de producto',
     N'Crear, editar y desactivar ProductTypes del catálogo (flags + límites multimedia)',
     'catalogo')
) AS s (Codigo, Nombre, Descripcion, Modulo)
ON t.Codigo = s.Codigo
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Codigo, Nombre, Descripcion, Modulo)
    VALUES (s.Codigo, s.Nombre, s.Descripcion, s.Modulo);
GO

MERGE dbo.RolPermiso AS t
USING (
    SELECT r.Id AS RolId, p.Id AS PermisoId
    FROM (VALUES ('admin', 'catalogo:tipos:gestionar')) AS x (RolCodigo, PermisoCodigo)
    JOIN dbo.Rol     r ON r.Codigo = x.RolCodigo
    JOIN dbo.Permiso p ON p.Codigo = x.PermisoCodigo
) AS s ON t.RolId = s.RolId AND t.PermisoId = s.PermisoId
WHEN NOT MATCHED BY TARGET THEN
    INSERT (RolId, PermisoId) VALUES (s.RolId, s.PermisoId);
GO

PRINT '';
PRINT 'V017 applied — dbo.ProductType (temporal, retention 10y) + permiso catalogo:tipos:gestionar.';
PRINT 'Next: V018 (PRD-008 — seed 12 tipos legacy).';
GO
