-- V023__refactor_chargeable_char_config_to_product_type.sql
-- PRC-001 scope delta: ChargeableCharConfig per ProductType (reemplaza per-Medio).
--
-- Cambios:
--   1. DROP MedioId + FK_ChargeableCharConfig_Medio + índices que lo referencian.
--   2. ADD ProductTypeId (nullable = global fallback) + FK_ChargeableCharConfig_ProductType.
--   3. Recrea índices con ProductTypeId (UX_Vigente + IX_Query).
--   4. DROP+CREATE usp_ChargeableCharConfig_InsertWithClose (@MedioId → @ProductTypeId).
--   5. DROP usp_ChargeableCharConfig_GetActiveForMedio + CREATE usp_ChargeableCharConfig_GetActiveForProductType.
--   6. NEW SP usp_ChargeableCharConfig_ReactivateWithGuard (opción A+guard para feature 3).
--   7. DROP CK_ChargeableCharConfig_Price_Positive (se permite 0.0000 para opt-in billing).
--      Reemplaza con CK_ChargeableCharConfig_Price_NonNegative (>= 0).
--
-- Patrón: idempotente con IF EXISTS guards. Bloque principal protegido por la presencia
--         de la columna MedioId — si no existe ya fue refactorizada, el bloque no ejecuta.
-- SYSTEM_VERSIONING: OFF al inicio del ALTER block, ON al final (con history table + retention).
-- Depende de: V017 (dbo.ProductType debe existir).
-- Reversa: V023_ROLLBACK.sql.
-- Run on: SIGCM2 (dev), SIGCM2_Test_App, SIGCM2_Test_Api.
--
-- SDD Design: engram sdd/prc-001-word-counter-spike/design
-- Scope delta: engram sdd/prc-001-word-counter-spike/scope-delta-1

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

-- ═══════════════════════════════════════════════════════════════════════
-- Bloque principal: solo ejecuta si la tabla existe Y todavía tiene MedioId
-- (guard idempotente: si ya fue refactorizada, el bloque se saltea completo).
-- ═══════════════════════════════════════════════════════════════════════

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ChargeableCharConfig' AND schema_id = SCHEMA_ID('dbo'))
   AND EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('dbo.ChargeableCharConfig')
                 AND name = 'MedioId')
BEGIN
    PRINT 'V023: MedioId column found — proceeding with refactor.';

    -- ─── 1. Turn OFF SYSTEM_VERSIONING (idempotent — skip if already OFF) ───
    IF EXISTS (SELECT 1 FROM sys.tables
               WHERE name = 'ChargeableCharConfig'
                 AND schema_id = SCHEMA_ID('dbo')
                 AND temporal_type = 2)  -- 2 = SYSTEM_VERSIONED_TEMPORAL_TABLE
    BEGIN
        ALTER TABLE dbo.ChargeableCharConfig SET (SYSTEM_VERSIONING = OFF);
        PRINT 'V023: SYSTEM_VERSIONING = OFF.';
    END
    ELSE
        PRINT 'V023: SYSTEM_VERSIONING already OFF — skipping.';

    -- ─── 2. Drop indexes that reference MedioId ────────────────────────
    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UX_ChargeableCharConfig_Vigente'
               AND object_id = OBJECT_ID('dbo.ChargeableCharConfig'))
    BEGIN
        DROP INDEX UX_ChargeableCharConfig_Vigente ON dbo.ChargeableCharConfig;
        PRINT 'V023: UX_ChargeableCharConfig_Vigente dropped.';
    END

    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_ChargeableCharConfig_Query'
               AND object_id = OBJECT_ID('dbo.ChargeableCharConfig'))
    BEGIN
        DROP INDEX IX_ChargeableCharConfig_Query ON dbo.ChargeableCharConfig;
        PRINT 'V023: IX_ChargeableCharConfig_Query dropped.';
    END

    -- ─── 3. Drop FK to Medio ────────────────────────────────────────────
    DECLARE @fk_name sysname;
    SELECT @fk_name = name
      FROM sys.foreign_keys
     WHERE parent_object_id = OBJECT_ID('dbo.ChargeableCharConfig')
       AND referenced_object_id = OBJECT_ID('dbo.Medio');
    IF @fk_name IS NOT NULL
    BEGIN
        EXEC('ALTER TABLE dbo.ChargeableCharConfig DROP CONSTRAINT ' + @fk_name);
        PRINT 'V023: FK_ChargeableCharConfig_Medio dropped.';
    END

    -- ─── 4. Drop MedioId column (drop DF constraint first if present) ───
    DECLARE @df_medio sysname;
    SELECT @df_medio = dc.name
      FROM sys.default_constraints dc
      JOIN sys.columns c ON c.default_object_id = dc.object_id
     WHERE c.object_id = OBJECT_ID('dbo.ChargeableCharConfig')
       AND c.name = 'MedioId';
    IF @df_medio IS NOT NULL
    BEGIN
        EXEC('ALTER TABLE dbo.ChargeableCharConfig DROP CONSTRAINT ' + @df_medio);
        PRINT 'V023: Default constraint on MedioId dropped.';
    END

    ALTER TABLE dbo.ChargeableCharConfig DROP COLUMN MedioId;
    PRINT 'V023: MedioId column dropped from ChargeableCharConfig.';

    -- Drop MedioId from history table if present
    IF EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('dbo.ChargeableCharConfig_History')
                 AND name = 'MedioId')
    BEGIN
        -- Drop default constraint on history MedioId if any
        DECLARE @df_hist_medio sysname;
        SELECT @df_hist_medio = dc.name
          FROM sys.default_constraints dc
          JOIN sys.columns c ON c.default_object_id = dc.object_id
         WHERE c.object_id = OBJECT_ID('dbo.ChargeableCharConfig_History')
           AND c.name = 'MedioId';
        IF @df_hist_medio IS NOT NULL
            EXEC('ALTER TABLE dbo.ChargeableCharConfig_History DROP CONSTRAINT ' + @df_hist_medio);

        ALTER TABLE dbo.ChargeableCharConfig_History DROP COLUMN MedioId;
        PRINT 'V023: MedioId column dropped from ChargeableCharConfig_History.';
    END

    -- ─── 5. Drop CK_Price_Positive, replace with CK_Price_NonNegative ──
    --    V024 seeds PricePerUnit = 0.0000 (opt-in billing). Old check (> 0) would block it.
    IF EXISTS (SELECT 1 FROM sys.check_constraints
               WHERE name = 'CK_ChargeableCharConfig_Price_Positive'
                 AND parent_object_id = OBJECT_ID('dbo.ChargeableCharConfig'))
    BEGIN
        ALTER TABLE dbo.ChargeableCharConfig DROP CONSTRAINT CK_ChargeableCharConfig_Price_Positive;
        PRINT 'V023: CK_ChargeableCharConfig_Price_Positive dropped.';
    END

    IF NOT EXISTS (SELECT 1 FROM sys.check_constraints
                   WHERE name = 'CK_ChargeableCharConfig_Price_NonNegative'
                     AND parent_object_id = OBJECT_ID('dbo.ChargeableCharConfig'))
    BEGIN
        ALTER TABLE dbo.ChargeableCharConfig
            ADD CONSTRAINT CK_ChargeableCharConfig_Price_NonNegative
            CHECK (PricePerUnit >= 0);
        PRINT 'V023: CK_ChargeableCharConfig_Price_NonNegative added (>= 0, opt-in billing).';
    END

    -- ─── 6. Add ProductTypeId column ────────────────────────────────────
    ALTER TABLE dbo.ChargeableCharConfig
        ADD ProductTypeId INT NULL;  -- NULL = global fallback
    PRINT 'V023: ProductTypeId column added to ChargeableCharConfig.';

    ALTER TABLE dbo.ChargeableCharConfig_History
        ADD ProductTypeId INT NULL;
    PRINT 'V023: ProductTypeId column added to ChargeableCharConfig_History.';

    -- ─── 7. Add FK to ProductType ────────────────────────────────────────
    ALTER TABLE dbo.ChargeableCharConfig
        ADD CONSTRAINT FK_ChargeableCharConfig_ProductType
        FOREIGN KEY (ProductTypeId) REFERENCES dbo.ProductType(Id) ON DELETE NO ACTION;
    PRINT 'V023: FK_ChargeableCharConfig_ProductType added.';

    -- ─── 8. Recreate filtered unique index with ProductTypeId ────────────
    -- 1 vigente per (ProductTypeId, Symbol). NULL ProductTypeId = global fallback.
    -- SQL Server trata NULL como "distinto" en unique indexes → enforza 1 vigente global.
    CREATE UNIQUE NONCLUSTERED INDEX UX_ChargeableCharConfig_Vigente
        ON dbo.ChargeableCharConfig (ProductTypeId, Symbol)
        WHERE ValidTo IS NULL;
    PRINT 'V023: UX_ChargeableCharConfig_Vigente recreated (ProductTypeId, Symbol).';

    -- ─── 9. Recreate cover index with ProductTypeId ──────────────────────
    CREATE NONCLUSTERED INDEX IX_ChargeableCharConfig_Query
        ON dbo.ChargeableCharConfig (ProductTypeId, Symbol, ValidFrom, ValidTo)
        INCLUDE (PricePerUnit, IsActive, Category);
    PRINT 'V023: IX_ChargeableCharConfig_Query recreated (ProductTypeId).';

    -- ─── 10. Turn SYSTEM_VERSIONING back ON ──────────────────────────────
    ALTER TABLE dbo.ChargeableCharConfig
        SET (SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = dbo.ChargeableCharConfig_History,
            HISTORY_RETENTION_PERIOD = 10 YEARS
        ));
    PRINT 'V023: SYSTEM_VERSIONING = ON (history: dbo.ChargeableCharConfig_History, retention: 10 years).';
END
ELSE
BEGIN
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ChargeableCharConfig' AND schema_id = SCHEMA_ID('dbo'))
        PRINT 'V023: dbo.ChargeableCharConfig does not exist — skipping table refactor.';
    ELSE
        PRINT 'V023: MedioId column not found — table already refactored, skipping.';
END
GO

-- ═══════════════════════════════════════════════════════════════════════
-- SP: usp_ChargeableCharConfig_InsertWithClose (@ProductTypeId replaces @MedioId)
--     Cierre atómico forward-only: SERIALIZABLE + UPDLOCK + HOLDLOCK.
--     @ProductTypeId NULL = global; FK validada solo cuando NOT NULL (via referential integrity).
--     THROW 50404: ProductType not found.
--     THROW 50409: ForwardOnly — new ValidFrom must be > active.ValidFrom.
--     Output: @NewId (BIGINT), @ClosedId (BIGINT — NULL if first price for symbol).
-- ═══════════════════════════════════════════════════════════════════════

IF OBJECT_ID('dbo.usp_ChargeableCharConfig_InsertWithClose', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_ChargeableCharConfig_InsertWithClose;
GO

CREATE PROCEDURE dbo.usp_ChargeableCharConfig_InsertWithClose
    @ProductTypeId INT           = NULL,
    @Symbol        NVARCHAR(4),
    @Category      NVARCHAR(32),
    @PricePerUnit  DECIMAL(18,4),
    @ValidFrom     DATE,
    @NewId         BIGINT OUTPUT,
    @ClosedId      BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Validate ProductTypeId only when provided (NULL = global fallback, always valid).
        -- FK constraint handles referential integrity; we throw 50404 explicitly for better UX.
        IF @ProductTypeId IS NOT NULL
           AND NOT EXISTS (SELECT 1 FROM dbo.ProductType WITH (NOLOCK) WHERE Id = @ProductTypeId)
        BEGIN
            ROLLBACK;
            THROW 50404, 'ProductType not found', 1;
        END

        -- Read current vigente with range lock for serialization.
        DECLARE @ActiveId BIGINT, @ActiveValidFrom DATE;
        SELECT TOP 1
            @ActiveId        = Id,
            @ActiveValidFrom = ValidFrom
        FROM dbo.ChargeableCharConfig WITH (UPDLOCK, HOLDLOCK, ROWLOCK)
        WHERE ((@ProductTypeId IS NULL     AND ProductTypeId IS NULL)
            OR (@ProductTypeId IS NOT NULL AND ProductTypeId = @ProductTypeId))
          AND Symbol  = @Symbol
          AND ValidTo IS NULL;

        -- Forward-only strict: new ValidFrom must be STRICTLY greater than active.ValidFrom.
        IF @ActiveId IS NOT NULL AND @ValidFrom <= @ActiveValidFrom
        BEGIN
            ROLLBACK;
            THROW 50409, 'ChargeableCharConfigForwardOnly: new ValidFrom must be > active.ValidFrom', 1;
        END

        -- Close the current vigente: ValidTo = new ValidFrom - 1 day.
        IF @ActiveId IS NOT NULL
        BEGIN
            UPDATE dbo.ChargeableCharConfig
               SET ValidTo = DATEADD(DAY, -1, @ValidFrom)
             WHERE Id = @ActiveId;
            SET @ClosedId = @ActiveId;
        END
        ELSE
            SET @ClosedId = NULL;

        -- Insert the new vigente.
        INSERT INTO dbo.ChargeableCharConfig
            (ProductTypeId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive)
        VALUES
            (@ProductTypeId, @Symbol, @Category, @PricePerUnit, @ValidFrom, NULL, 1);
        SET @NewId = SCOPE_IDENTITY();

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

-- ═══════════════════════════════════════════════════════════════════════
-- SP: drop old GetActiveForMedio (renamed to GetActiveForProductType)
-- ═══════════════════════════════════════════════════════════════════════

IF OBJECT_ID('dbo.usp_ChargeableCharConfig_GetActiveForMedio', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_ChargeableCharConfig_GetActiveForMedio;
    PRINT 'V023: usp_ChargeableCharConfig_GetActiveForMedio dropped.';
END
GO

-- ═══════════════════════════════════════════════════════════════════════
-- SP: usp_ChargeableCharConfig_GetActiveForProductType
--     Resolución per-ProductType + global fallback: 1 fila por Symbol.
--     CTE + ROW_NUMBER PARTITION BY Symbol ORDER BY per-PT(0) vs global(1).
--     @ProductTypeId: the specific product type to resolve for.
--     @AsOfDate: resolve active rows as of this date (for pricing snapshot).
-- ═══════════════════════════════════════════════════════════════════════

CREATE PROCEDURE dbo.usp_ChargeableCharConfig_GetActiveForProductType
    @ProductTypeId INT,
    @AsOfDate      DATE
AS
BEGIN
    SET NOCOUNT ON;
    WITH Candidates AS (
        SELECT
            Id, ProductTypeId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive,
            ROW_NUMBER() OVER (
                PARTITION BY Symbol
                ORDER BY
                    CASE WHEN ProductTypeId = @ProductTypeId THEN 0 ELSE 1 END, -- prefer specific over global
                    ValidFrom DESC
            ) AS rn
        FROM dbo.ChargeableCharConfig
        WHERE IsActive   = 1
          AND ValidFrom <= @AsOfDate
          AND (ValidTo IS NULL OR ValidTo >= @AsOfDate)
          AND (ProductTypeId = @ProductTypeId OR ProductTypeId IS NULL)
    )
    SELECT Id, ProductTypeId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive
    FROM   Candidates
    WHERE  rn = 1;
END
GO

-- ═══════════════════════════════════════════════════════════════════════
-- SP: usp_ChargeableCharConfig_ReactivateWithGuard (NEW — feature 3 of scope delta)
--     Opción A+guard: literal undo of the last close for (ProductTypeId, Symbol).
--     Guards:
--       - Row must exist → THROW 50404
--       - Row must be closed (ValidTo IS NOT NULL, IsActive = 0) → THROW 50410 if already active
--       - No vigente currently exists for (ProductTypeId, Symbol) → THROW 50411
--       - No posterior rows exist for (ProductTypeId, Symbol) → THROW 50412
--     On success: UPDATE IsActive = 1, ValidTo = NULL (literal undo).
--     Preserves forward-only invariant and maintains clean history.
-- ═══════════════════════════════════════════════════════════════════════

IF OBJECT_ID('dbo.usp_ChargeableCharConfig_ReactivateWithGuard', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_ChargeableCharConfig_ReactivateWithGuard;
GO

CREATE PROCEDURE dbo.usp_ChargeableCharConfig_ReactivateWithGuard
    @Id BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Step 1: Lock + load target row.
        DECLARE @ProductTypeId INT, @Symbol NVARCHAR(4), @ValidTo DATE, @IsActive BIT;
        SELECT @ProductTypeId = ProductTypeId,
               @Symbol        = Symbol,
               @ValidTo       = ValidTo,
               @IsActive      = IsActive
          FROM dbo.ChargeableCharConfig WITH (UPDLOCK, HOLDLOCK)
         WHERE Id = @Id;

        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50404, 'ChargeableCharConfig row not found', 1;
        END

        -- Step 2: Row must be closed (ValidTo IS NOT NULL and IsActive = 0).
        --         If it is currently active (ValidTo IS NULL), reactivation is nonsensical.
        IF @ValidTo IS NULL
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50410, 'Row is already active — reactivation not needed', 1;
        END

        -- Step 3: GUARD — no vigente currently for (ProductTypeId, Symbol).
        --         Prevents re-opening a row while another is already vigente.
        IF EXISTS (
            SELECT 1 FROM dbo.ChargeableCharConfig
             WHERE ((ProductTypeId = @ProductTypeId) OR (ProductTypeId IS NULL AND @ProductTypeId IS NULL))
               AND Symbol  = @Symbol
               AND ValidTo IS NULL
        )
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50411, 'A current active row already exists for this ProductType/Symbol — cannot reactivate', 1;
        END

        -- Step 4: GUARD — no posterior rows exist for (ProductTypeId, Symbol) after @ValidTo.
        --         Ensures this is the LAST closed row; reactivating an older row would violate
        --         forward-only ordering of the temporal chain.
        IF EXISTS (
            SELECT 1 FROM dbo.ChargeableCharConfig
             WHERE ((ProductTypeId = @ProductTypeId) OR (ProductTypeId IS NULL AND @ProductTypeId IS NULL))
               AND Symbol    = @Symbol
               AND ValidFrom > @ValidTo
               AND Id        <> @Id
        )
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50412, 'Posterior rows exist for this ProductType/Symbol — reactivation not allowed', 1;
        END

        -- Step 5: Literal undo — re-open the row.
        UPDATE dbo.ChargeableCharConfig
           SET IsActive = 1,
               ValidTo  = NULL
         WHERE Id = @Id;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END
GO

PRINT '';
PRINT 'V023 applied — ChargeableCharConfig refactored to ProductType model:';
PRINT '  - MedioId dropped, ProductTypeId added (FK to dbo.ProductType)';
PRINT '  - UX_ChargeableCharConfig_Vigente + IX_ChargeableCharConfig_Query recreated';
PRINT '  - usp_ChargeableCharConfig_InsertWithClose: @MedioId → @ProductTypeId';
PRINT '  - usp_ChargeableCharConfig_GetActiveForMedio dropped';
PRINT '  - usp_ChargeableCharConfig_GetActiveForProductType created';
PRINT '  - usp_ChargeableCharConfig_ReactivateWithGuard created (NEW)';
PRINT '  - CK_Price_Positive replaced by CK_Price_NonNegative (>= 0 for opt-in billing)';
PRINT 'Next migration: V024 (reseed global rows with PricePerUnit = 0.0000).';
GO
