SET QUOTED_IDENTIFIER ON

GO
/*
    Pre-Deployment Script
*/

PRINT '=== Pre-Deployment: START ===';
PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 1: RESOLVE DUPLICATE FAB.catClips.ClipPartNumber
-- ────────────────────────────────────────────────────────────
-- The new UX_catClips_ClipPartNumber filtered UNIQUE index would fail to
-- create if existing rows have duplicate non-NULL ClipPartNumber values.
-- Rename duplicates with a -DUP-{ClipId} suffix so the index can be applied.
-- Idempotent: subsequent runs find no duplicates and update zero rows.
IF OBJECT_ID('FAB.catClips', 'U') IS NOT NULL
BEGIN
    DECLARE @ClipPartNumberDupResolved INT = 0;

    ;WITH dup AS (
        SELECT [ClipId],
               [ClipPartNumber],
               ROW_NUMBER() OVER (PARTITION BY [ClipPartNumber] ORDER BY [ClipId]) AS rn
        FROM [FAB].[catClips]
        WHERE [ClipPartNumber] IS NOT NULL
    )
    UPDATE c
    SET [ClipPartNumber] =
        LEFT(c.[ClipPartNumber],
             50 - LEN('-DUP-') - LEN(CAST(c.[ClipId] AS VARCHAR(20))))
        + '-DUP-' + CAST(c.[ClipId] AS VARCHAR(20))
    FROM [FAB].[catClips] c
    INNER JOIN dup d ON d.[ClipId] = c.[ClipId]
    WHERE d.rn > 1;

    SET @ClipPartNumberDupResolved = @@ROWCOUNT;

    IF @ClipPartNumberDupResolved > 0
        PRINT '[STEP 1] Renamed ' + CAST(@ClipPartNumberDupResolved AS VARCHAR(20))
              + ' duplicate ClipPartNumber rows with -DUP-{ClipId} suffix';
    ELSE
        PRINT '[STEP 1] No duplicate ClipPartNumber values found';
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 2: NORMALIZE FAB.catClips.BottomKeeperSetup FOR BIT CONVERSION
-- ────────────────────────────────────────────────────────────
-- Column is changing from VARCHAR(20) to BIT. Existing values like 'X' would
-- fail the implicit cast. Convert recognized truthy values to '1', empties to
-- NULL, anything else to '0' before DACPAC alters the column type.
--
-- Wrapped in sp_executesql so the UPDATE is parsed at runtime: if the column
-- doesn't exist (already migrated, fresh DB, etc.), the outer IF skips and
-- the parser never tries to bind [BottomKeeperSetup]. Idempotent via the
-- VARCHAR type guard.
IF OBJECT_ID('FAB.catClips', 'U') IS NOT NULL
   AND EXISTS (
       SELECT 1 FROM sys.columns
       WHERE object_id = OBJECT_ID('FAB.catClips')
         AND name = 'BottomKeeperSetup'
         AND system_type_id = TYPE_ID('varchar')
   )
BEGIN
    EXEC sp_executesql N'
        UPDATE [FAB].[catClips]
        SET [BottomKeeperSetup] = CASE
                WHEN UPPER(LTRIM(RTRIM([BottomKeeperSetup]))) IN (''X'', ''1'', ''Y'', ''YES'', ''TRUE'') THEN ''1''
                WHEN LTRIM(RTRIM([BottomKeeperSetup])) = '''' THEN NULL
                ELSE ''0''
            END
        WHERE [BottomKeeperSetup] IS NOT NULL;
        PRINT ''[STEP 2] Normalized BottomKeeperSetup VARCHAR values for BIT conversion ('' + CAST(@@ROWCOUNT AS VARCHAR(20)) + '' rows touched)'';
    ';
END
ELSE
    PRINT '[STEP 2] SKIP - BottomKeeperSetup not present as VARCHAR';

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 3: CLEAR NON-NUMERIC FAB.catClips.TestCheckCycle FOR INT CONVERSION
-- ────────────────────────────────────────────────────────────
-- Column is changing from VARCHAR(50) to INT. Non-numeric strings would fail
-- the implicit cast. NULL out anything that won't parse as INT before DACPAC
-- alters the column type. Idempotent via type guard.
--
-- See STEP 2 for the sp_executesql rationale.
IF OBJECT_ID('FAB.catClips', 'U') IS NOT NULL
   AND EXISTS (
       SELECT 1 FROM sys.columns
       WHERE object_id = OBJECT_ID('FAB.catClips')
         AND name = 'TestCheckCycle'
         AND system_type_id = TYPE_ID('varchar')
   )
BEGIN
    EXEC sp_executesql N'
        UPDATE [FAB].[catClips]
        SET [TestCheckCycle] = NULL
        WHERE [TestCheckCycle] IS NOT NULL
          AND TRY_CAST([TestCheckCycle] AS INT) IS NULL;
        DECLARE @rc INT = @@ROWCOUNT;
        IF @rc > 0
            PRINT ''[STEP 3] Cleared '' + CAST(@rc AS VARCHAR(20)) + '' non-numeric TestCheckCycle values for INT conversion'';
        ELSE
            PRINT ''[STEP 3] No non-numeric TestCheckCycle values found'';
    ';
END
ELSE
    PRINT '[STEP 3] SKIP - TestCheckCycle not present as VARCHAR';

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 4: PRESERVE FAB.catClips.SWVADrawingNumber INTO DrawingNumber
-- ────────────────────────────────────────────────────────────
-- The new schema consolidates SWVADrawingNumber into the existing DrawingNumber
-- column (Master = SWVA per Roger). Copy values across before DACPAC drops
-- SWVADrawingNumber, but only where DrawingNumber is empty so we never
-- overwrite an existing value.
--
-- Wrapped in sp_executesql so the parser doesn't bind [SWVADrawingNumber]
-- after DACPAC drops it on subsequent deploys (or on DBs that never had it).
IF OBJECT_ID('FAB.catClips', 'U') IS NOT NULL
   AND EXISTS (
       SELECT 1 FROM sys.columns
       WHERE object_id = OBJECT_ID('FAB.catClips')
         AND name = 'SWVADrawingNumber'
   )
BEGIN
    EXEC sp_executesql N'
        UPDATE [FAB].[catClips]
        SET [DrawingNumber] = [SWVADrawingNumber]
        WHERE [DrawingNumber] IS NULL
          AND [SWVADrawingNumber] IS NOT NULL;
        DECLARE @rc INT = @@ROWCOUNT;
        IF @rc > 0
            PRINT ''[STEP 4] Preserved '' + CAST(@rc AS VARCHAR(20)) + '' SWVADrawingNumber values into DrawingNumber'';
        ELSE
            PRINT ''[STEP 4] No SWVADrawingNumber values to preserve'';
    ';
END
ELSE
    PRINT '[STEP 4] SKIP - SWVADrawingNumber column not present';

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 5: ALLOW NULL ON FAB.Bundles.EndTime AND FAB.Layers.EndTime
-- ────────────────────────────────────────────────────────────
-- Open bundles and open layers (EndTime IS NULL) are now supported.
-- Pre-alter the columns to NULL so DACPAC publish doesn't fail.
-- Idempotent: skipped when the column already allows NULL.
IF OBJECT_ID('FAB.Bundles', 'U') IS NOT NULL
   AND EXISTS (
       SELECT 1 FROM sys.columns
       WHERE object_id = OBJECT_ID('FAB.Bundles')
         AND name = 'EndTime'
         AND is_nullable = 0
   )
BEGIN
    ALTER TABLE [FAB].[Bundles] ALTER COLUMN [EndTime] DATETIME2(3) NULL;
    PRINT '[STEP 5] FAB.Bundles.EndTime altered to NULL';
END
ELSE
    PRINT '[STEP 5] FAB.Bundles.EndTime already allows NULL';

IF OBJECT_ID('FAB.Layers', 'U') IS NOT NULL
   AND EXISTS (
       SELECT 1 FROM sys.columns
       WHERE object_id = OBJECT_ID('FAB.Layers')
         AND name = 'EndTime'
         AND is_nullable = 0
   )
BEGIN
    ALTER TABLE [FAB].[Layers] ALTER COLUMN [EndTime] DATETIME2(3) NULL;
    PRINT '[STEP 5] FAB.Layers.EndTime altered to NULL';
END
ELSE
    PRINT '[STEP 5] FAB.Layers.EndTime already allows NULL';

IF OBJECT_ID('FAB.Batches', 'U') IS NOT NULL
   AND EXISTS (
       SELECT 1 FROM sys.columns
       WHERE object_id = OBJECT_ID('FAB.Batches')
         AND name = 'EndTime'
         AND is_nullable = 0
   )
BEGIN
    ALTER TABLE [FAB].[Batches] ALTER COLUMN [EndTime] DATETIME2(3) NULL;
    PRINT '[STEP 5] FAB.Batches.EndTime altered to NULL';
END
ELSE
    PRINT '[STEP 5] FAB.Batches.EndTime already allows NULL';

PRINT '';
PRINT '=== Pre-Deployment: COMPLETE ===';

