SET QUOTED_IDENTIFIER ON;
GO



PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 1a: ADD AreaAssetId COLUMN TO FAB.Products
-- ────────────────────────────────────────────────────────────
-- Added as nullable in its own batch so the next batch can reference the
-- column with static SQL (compile-time validation succeeds).
IF OBJECT_ID('FAB.Products', 'U') IS NOT NULL
BEGIN
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.Products') AND name = 'AreaAssetId')
    BEGIN
        ALTER TABLE [FAB].[Products] ADD [AreaAssetId] INT NULL;
        PRINT '[STEP 1a] Added column AreaAssetId (nullable)';
    END
    ELSE
        PRINT '[STEP 1a] SKIP - AreaAssetId already exists';
END
GO

-- ────────────────────────────────────────────────────────────
-- STEP 1b: BACKFILL AreaAssetId + VERIFY NO ORPHANS
-- ────────────────────────────────────────────────────────────
-- Runs only if column is present. DACPAC applies NOT NULL + FK + CHECK
-- via the updated Products.sql table definition after this script.
IF OBJECT_ID('FAB.Products', 'U') IS NOT NULL
   AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.Products') AND name = 'AreaAssetId')
BEGIN
    UPDATE p
    SET [AreaAssetId] = COALESCE(
        (SELECT TOP 1 [AreaAssetId] FROM [FAB].[FabricationSpecs] WHERE [ProductId] = p.[ProductId]),
        CASE WHEN EXISTS (SELECT 1 FROM [FAB].[SawSpecs] WHERE [ProductId] = p.[ProductId]) THEN 20 END
    )
    FROM [FAB].[Products] p
    WHERE [AreaAssetId] IS NULL;
    PRINT '[STEP 1b] Backfilled AreaAssetId from specs existence';

    IF EXISTS (SELECT 1 FROM [FAB].[Products] WHERE [AreaAssetId] IS NULL)
    BEGIN
        DECLARE @orphans INT = (SELECT COUNT(*) FROM [FAB].[Products] WHERE [AreaAssetId] IS NULL);
        DECLARE @msg NVARCHAR(400) = CONCAT(@orphans,
            ' products have no derivable AreaAssetId. Manual cleanup required before deployment.');
        THROW 50200, @msg, 1;
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 2: DROP Beam* COLUMNS FROM FAB.FabricationSpecs
-- ────────────────────────────────────────────────────────────
-- Beam specs belonged to the saw parent; they are resolved via lineage now.
IF OBJECT_ID('FAB.FabricationSpecs', 'U') IS NOT NULL
BEGIN
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'BeamPartNumber')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [BeamPartNumber];
        PRINT '[STEP 2] Dropped column BeamPartNumber';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'BeamCustomerPartNumber')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [BeamCustomerPartNumber];
        PRINT '[STEP 2] Dropped column BeamCustomerPartNumber';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'BeamLength')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [BeamLength];
        PRINT '[STEP 2] Dropped column BeamLength';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'BeamTolerance')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [BeamTolerance];
        PRINT '[STEP 2] Dropped column BeamTolerance';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'BeamPunched')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [BeamPunched];
        PRINT '[STEP 2] Dropped column BeamPunched';
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 3: DROP WebHoles + NextOperationId FROM FAB.FabricationSpecs
-- ────────────────────────────────────────────────────────────
-- WebHoles is now derived from PunchSpecs presence in lineage; NextOperationId
-- is dropped entirely (lineage branching makes "next op" ambiguous).
IF OBJECT_ID('FAB.FabricationSpecs', 'U') IS NOT NULL
BEGIN
    IF OBJECT_ID('FAB.FK_FabricationSpecs_NextOperations', 'F') IS NOT NULL
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP CONSTRAINT [FK_FabricationSpecs_NextOperations];
        PRINT '[STEP 3] Dropped constraint FK_FabricationSpecs_NextOperations';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'WebHoles')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [WebHoles];
        PRINT '[STEP 3] Dropped column WebHoles';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'NextOperationId')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [NextOperationId];
        PRINT '[STEP 3] Dropped column NextOperationId';
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 4: DROP FAB.catNextOperations + ITS CRUD SPs
-- ────────────────────────────────────────────────────────────
-- Catalog is unused after NextOperationId is dropped from FabricationSpecs.
IF OBJECT_ID('FAB.GetNextOperations', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [FAB].[GetNextOperations];
    PRINT '[STEP 4] Dropped procedure FAB.GetNextOperations';
END
IF OBJECT_ID('FAB.UpsertNextOperation', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [FAB].[UpsertNextOperation];
    PRINT '[STEP 4] Dropped procedure FAB.UpsertNextOperation';
END
IF OBJECT_ID('FAB.DeleteNextOperation', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [FAB].[DeleteNextOperation];
    PRINT '[STEP 4] Dropped procedure FAB.DeleteNextOperation';
END
IF OBJECT_ID('FAB.catNextOperations', 'U') IS NOT NULL
BEGIN
    DROP TABLE [FAB].[catNextOperations];
    PRINT '[STEP 4] Dropped table FAB.catNextOperations';
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 5: DROP BandLength + GroovedTimber FROM FAB.FabricationSpecs
-- ────────────────────────────────────────────────────────────
-- Bundle delivery attributes moved to the dedicated FAB.FinishSpecs table.
IF OBJECT_ID('FAB.FabricationSpecs', 'U') IS NOT NULL
BEGIN
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'BandLength')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [BandLength];
        PRINT '[STEP 5] Dropped column BandLength';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.FabricationSpecs') AND name = 'GroovedTimber')
    BEGIN
        ALTER TABLE [FAB].[FabricationSpecs] DROP COLUMN [GroovedTimber];
        PRINT '[STEP 5] Dropped column GroovedTimber';
    END
END

-- ────────────────────────────────────────────────────────────
-- STEP 6: ADD ExtendedPartNumber + DROP UQ_Products_PartNumber
-- ────────────────────────────────────────────────────────────
-- PartNumber repeats freely across stages (and within a stage); identity is
-- carried by ExtendedPartNumber, which is globally unique when present.
IF OBJECT_ID('FAB.Products', 'U') IS NOT NULL
BEGIN
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.Products') AND name = 'ExtendedPartNumber')
    BEGIN
        ALTER TABLE [FAB].[Products] ADD [ExtendedPartNumber] VARCHAR(50) NULL;
        PRINT '[STEP 6] Added column ExtendedPartNumber';
    END

    IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'UQ_Products_PartNumber' AND parent_object_id = OBJECT_ID('FAB.Products'))
    BEGIN
        ALTER TABLE [FAB].[Products] DROP CONSTRAINT [UQ_Products_PartNumber];
        PRINT '[STEP 6] Dropped constraint UQ_Products_PartNumber';
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 7: REPLACE PunchPerforations.PunchToolId WITH PunchPartNumber + ButtonPartNumber
-- ────────────────────────────────────────────────────────────
-- The catPunchTools catalog is dropped; per-row tool identification is now
-- two free-text VARCHAR columns. The DACPAC can't drop the FK column while
-- the FK still exists, so handle it explicitly here.
IF OBJECT_ID('FAB.PunchPerforations', 'U') IS NOT NULL
BEGIN
    IF OBJECT_ID('FAB.FK_PunchPerforations_PunchTools', 'F') IS NOT NULL
    BEGIN
        ALTER TABLE [FAB].[PunchPerforations] DROP CONSTRAINT [FK_PunchPerforations_PunchTools];
        PRINT '[STEP 7] Dropped constraint FK_PunchPerforations_PunchTools';
    END
    IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_PunchPerforations_PunchToolId' AND object_id = OBJECT_ID('FAB.PunchPerforations'))
    BEGIN
        DROP INDEX [IX_PunchPerforations_PunchToolId] ON [FAB].[PunchPerforations];
        PRINT '[STEP 7] Dropped index IX_PunchPerforations_PunchToolId';
    END
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.PunchPerforations') AND name = 'PunchPartNumber')
    BEGIN
        ALTER TABLE [FAB].[PunchPerforations] ADD [PunchPartNumber] VARCHAR(50) NULL;
        PRINT '[STEP 7] Added column PunchPartNumber';
    END
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.PunchPerforations') AND name = 'ButtonPartNumber')
    BEGIN
        ALTER TABLE [FAB].[PunchPerforations] ADD [ButtonPartNumber] VARCHAR(50) NULL;
        PRINT '[STEP 7] Added column ButtonPartNumber';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.PunchPerforations') AND name = 'PunchToolId')
    BEGIN
        ALTER TABLE [FAB].[PunchPerforations] DROP COLUMN [PunchToolId];
        PRINT '[STEP 7] Dropped column PunchToolId';
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 8: DROP FAB.catPunchTools + ITS CRUD SPs
-- ────────────────────────────────────────────────────────────
-- Catalog is unused after PunchToolId is replaced by free-text columns.
IF OBJECT_ID('FAB.GetPunchTools', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [FAB].[GetPunchTools];
    PRINT '[STEP 8] Dropped procedure FAB.GetPunchTools';
END
IF OBJECT_ID('FAB.UpsertPunchTool', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [FAB].[UpsertPunchTool];
    PRINT '[STEP 8] Dropped procedure FAB.UpsertPunchTool';
END
IF OBJECT_ID('FAB.DeletePunchTool', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [FAB].[DeletePunchTool];
    PRINT '[STEP 8] Dropped procedure FAB.DeletePunchTool';
END
IF OBJECT_ID('FAB.catPunchTools', 'U') IS NOT NULL
BEGIN
    DROP TABLE [FAB].[catPunchTools];
    PRINT '[STEP 8] Dropped table FAB.catPunchTools';
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 9: DROP Side COLUMN FROM FAB.PunchBeamPositions
-- ────────────────────────────────────────────────────────────
-- Each plate now has a single ordered axis of 7 positions running from
-- Avenue (south) to River (north); the Side discriminator is no longer
-- needed. Drop CK + UQ that reference Side, then drop the column; DACPAC
-- recreates the new UQ on (ProductId, Plate, PositionOrder).
IF OBJECT_ID('FAB.PunchBeamPositions', 'U') IS NOT NULL
BEGIN
    IF OBJECT_ID('FAB.CK_PunchBeamPositions_Side', 'C') IS NOT NULL
    BEGIN
        ALTER TABLE [FAB].[PunchBeamPositions] DROP CONSTRAINT [CK_PunchBeamPositions_Side];
        PRINT '[STEP 9] Dropped constraint CK_PunchBeamPositions_Side';
    END
    IF OBJECT_ID('FAB.UQ_PunchBeamPositions_Slot', 'UQ') IS NOT NULL
    BEGIN
        ALTER TABLE [FAB].[PunchBeamPositions] DROP CONSTRAINT [UQ_PunchBeamPositions_Slot];
        PRINT '[STEP 9] Dropped constraint UQ_PunchBeamPositions_Slot';
    END
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('FAB.PunchBeamPositions') AND name = 'Side')
    BEGIN
        ALTER TABLE [FAB].[PunchBeamPositions] DROP COLUMN [Side];
        PRINT '[STEP 9] Dropped column Side';
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 10: CONVERT FAB.FinishSpecs.BandLength FROM VARCHAR TO DECIMAL
-- ────────────────────────────────────────────────────────────
-- BandLength is migrating to a numeric type so the UI can enforce non-negative
-- input. NULL out anything that won't parse so DACPAC's ALTER COLUMN succeeds.
IF OBJECT_ID('FAB.FinishSpecs', 'U') IS NOT NULL
BEGIN
    IF EXISTS (
        SELECT 1 FROM sys.columns
        WHERE object_id = OBJECT_ID('FAB.FinishSpecs')
          AND name = 'BandLength'
          AND system_type_id = TYPE_ID('varchar')
    )
    BEGIN
        UPDATE [FAB].[FinishSpecs]
        SET [BandLength] = NULL
        WHERE [BandLength] IS NOT NULL
          AND TRY_CAST([BandLength] AS DECIMAL(10,3)) IS NULL;
        PRINT '[STEP 10] Cleared non-numeric BandLength values';
    END
END

PRINT '';

-- ────────────────────────────────────────────────────────────
-- STEP 11: RESET FAB PRODUCTS DATA
-- ────────────────────────────────────────────────────────────
-- Wipe products + dependents so PostDeployment can reseed from scratch with
-- the new ExtendedPartNumber values. DELETEs run in FK-safe order.
IF OBJECT_ID('FAB.WorkOrders', 'U')          IS NOT NULL DELETE FROM [FAB].[WorkOrders];
IF OBJECT_ID('FAB.ProductAssociations', 'U') IS NOT NULL DELETE FROM [FAB].[ProductAssociations];
IF OBJECT_ID('FAB.PunchPerforations', 'U')   IS NOT NULL DELETE FROM [FAB].[PunchPerforations];
IF OBJECT_ID('FAB.PunchBeamPositions', 'U')  IS NOT NULL DELETE FROM [FAB].[PunchBeamPositions];
IF OBJECT_ID('FAB.PunchSpecs', 'U')          IS NOT NULL DELETE FROM [FAB].[PunchSpecs];
IF OBJECT_ID('FAB.FinishSpecs', 'U')         IS NOT NULL DELETE FROM [FAB].[FinishSpecs];
IF OBJECT_ID('FAB.FabricationSpecs', 'U')    IS NOT NULL DELETE FROM [FAB].[FabricationSpecs];
IF OBJECT_ID('FAB.SawSpecs', 'U')            IS NOT NULL DELETE FROM [FAB].[SawSpecs];
IF OBJECT_ID('FAB.Products', 'U')            IS NOT NULL DELETE FROM [FAB].[Products];
PRINT '[STEP 11] Cleared products + dependents';

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