SIG-CM2.0/database/migrations/V023_ROLLBACK.sql

-- V023_ROLLBACK.sql
-- PRC-001: Reversa de V023__refactor_chargeable_char_config_to_product_type.sql.
--
-- ADVERTENCIA: rollback destructivo — elimina ProductTypeId y restaura MedioId.
--   - Todos los datos de ProductTypeId se pierden.
--   - Las filas globales (ProductTypeId NULL) se preservan como globales (MedioId NULL).
--   - El historial temporal puede quedar inconsistente si la tabla fue modificada después.
--
-- Solo para uso en DEV/TEST. No ejecutar en producción si hay datos de ProductTypeId.
-- Run on: SIGCM2 (dev), SIGCM2_Test_App, SIGCM2_Test_Api.

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

-- ─── 1. Drop new SPs ────────────────────────────────────────────────────────

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

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

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

-- ─── 2. Reverse table alterations if ProductTypeId column exists ─────────────

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 = 'ProductTypeId')
BEGIN
    -- 2a. Turn off SYSTEM_VERSIONING
    ALTER TABLE dbo.ChargeableCharConfig SET (SYSTEM_VERSIONING = OFF);
    PRINT 'V023 ROLLBACK: SYSTEM_VERSIONING = OFF.';

    -- 2b. Drop indexes on ProductTypeId
    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 ROLLBACK: 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 ROLLBACK: IX_ChargeableCharConfig_Query dropped.';
    END

    -- 2c. Drop FK to ProductType
    DECLARE @fk_pt sysname;
    SELECT @fk_pt = name
      FROM sys.foreign_keys
     WHERE parent_object_id = OBJECT_ID('dbo.ChargeableCharConfig')
       AND referenced_object_id = OBJECT_ID('dbo.ProductType');
    IF @fk_pt IS NOT NULL
    BEGIN
        EXEC('ALTER TABLE dbo.ChargeableCharConfig DROP CONSTRAINT ' + @fk_pt);
        PRINT 'V023 ROLLBACK: FK_ChargeableCharConfig_ProductType dropped.';
    END

    -- 2d. Drop NonNegative price check; restore Positive check
    IF 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 DROP CONSTRAINT CK_ChargeableCharConfig_Price_NonNegative;
        PRINT 'V023 ROLLBACK: CK_ChargeableCharConfig_Price_NonNegative dropped.';
    END

    IF NOT 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
            ADD CONSTRAINT CK_ChargeableCharConfig_Price_Positive CHECK (PricePerUnit > 0);
        PRINT 'V023 ROLLBACK: CK_ChargeableCharConfig_Price_Positive restored.';
    END

    -- 2e. Drop ProductTypeId column from main + history
    ALTER TABLE dbo.ChargeableCharConfig DROP COLUMN ProductTypeId;
    PRINT 'V023 ROLLBACK: ProductTypeId dropped from ChargeableCharConfig.';

    IF EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID('dbo.ChargeableCharConfig_History')
                 AND name = 'ProductTypeId')
    BEGIN
        ALTER TABLE dbo.ChargeableCharConfig_History DROP COLUMN ProductTypeId;
        PRINT 'V023 ROLLBACK: ProductTypeId dropped from ChargeableCharConfig_History.';
    END

    -- 2f. Restore MedioId column
    ALTER TABLE dbo.ChargeableCharConfig ADD MedioId INT NULL;
    ALTER TABLE dbo.ChargeableCharConfig_History ADD MedioId INT NULL;
    PRINT 'V023 ROLLBACK: MedioId restored.';

    -- 2g. Restore FK to Medio
    ALTER TABLE dbo.ChargeableCharConfig
        ADD CONSTRAINT FK_ChargeableCharConfig_Medio
        FOREIGN KEY (MedioId) REFERENCES dbo.Medio(Id) ON DELETE NO ACTION;
    PRINT 'V023 ROLLBACK: FK_ChargeableCharConfig_Medio restored.';

    -- 2h. Restore indexes on MedioId
    CREATE UNIQUE NONCLUSTERED INDEX UX_ChargeableCharConfig_Vigente
        ON dbo.ChargeableCharConfig (MedioId, Symbol)
        WHERE ValidTo IS NULL;
    PRINT 'V023 ROLLBACK: UX_ChargeableCharConfig_Vigente restored (MedioId).';

    CREATE NONCLUSTERED INDEX IX_ChargeableCharConfig_Query
        ON dbo.ChargeableCharConfig (MedioId, Symbol, ValidFrom, ValidTo)
        INCLUDE (PricePerUnit, IsActive, Category);
    PRINT 'V023 ROLLBACK: IX_ChargeableCharConfig_Query restored (MedioId).';

    -- 2i. Restore SYSTEM_VERSIONING
    ALTER TABLE dbo.ChargeableCharConfig
        SET (SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = dbo.ChargeableCharConfig_History,
            HISTORY_RETENTION_PERIOD = 10 YEARS
        ));
    PRINT 'V023 ROLLBACK: SYSTEM_VERSIONING = ON restored.';
END
ELSE
    PRINT 'V023 ROLLBACK: ProductTypeId column not found — table already in MedioId state or missing, skipping.';
GO

-- ─── 3. Restore original SPs ────────────────────────────────────────────────

IF OBJECT_ID('dbo.usp_ChargeableCharConfig_InsertWithClose', 'P') IS NULL
    EXEC('CREATE PROCEDURE dbo.usp_ChargeableCharConfig_InsertWithClose AS RETURN 0');
GO

ALTER PROCEDURE dbo.usp_ChargeableCharConfig_InsertWithClose
    @MedioId      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;

        IF @MedioId IS NOT NULL
           AND NOT EXISTS (SELECT 1 FROM dbo.Medio WITH (NOLOCK) WHERE Id = @MedioId)
        BEGIN
            ROLLBACK;
            THROW 50404, 'Medio not found', 1;
        END

        DECLARE @ActiveId BIGINT, @ActiveValidFrom DATE;
        SELECT TOP 1
            @ActiveId        = Id,
            @ActiveValidFrom = ValidFrom
        FROM dbo.ChargeableCharConfig WITH (UPDLOCK, HOLDLOCK, ROWLOCK)
        WHERE ((@MedioId IS NULL     AND MedioId IS NULL)
            OR (@MedioId IS NOT NULL AND MedioId = @MedioId))
          AND Symbol  = @Symbol
          AND ValidTo IS NULL;

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

        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 INTO dbo.ChargeableCharConfig
            (MedioId, Symbol, Category, PricePerUnit, ValidFrom, ValidTo, IsActive)
        VALUES
            (@MedioId, @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

IF OBJECT_ID('dbo.usp_ChargeableCharConfig_GetActiveForMedio', 'P') IS NULL
    EXEC('CREATE PROCEDURE dbo.usp_ChargeableCharConfig_GetActiveForMedio AS RETURN 0');
GO

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

PRINT '';
PRINT 'V023 ROLLBACK complete — ChargeableCharConfig restored to MedioId model.';
GO