select 'pre_deployment_script';

SET QUOTED_IDENTIFIER ON; -- IMPORTANT TO ADD IN ALL DEPLOYS
GO

BEGIN
-- ═══════════════════════════════════════════════════════════════════
-- EQP Core Convergence: Drop TaskTimestamp Migration
-- Run BEFORE deploying schema changes
-- ═══════════════════════════════════════════════════════════════════

-- Step 1: Merge TaskTimestamp into Timestamp where they differ
-- TaskTimestamp was the user-editable display time; Timestamp was system time.
-- After this migration, Timestamp is the single source of truth (user-settable).
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('EQP.Tasks') AND name = 'TaskTimestamp')
BEGIN
    UPDATE EQP.Tasks
    SET [Timestamp] = TaskTimestamp
    WHERE TaskTimestamp IS NOT NULL
      AND TaskTimestamp <> [Timestamp];

    ALTER TABLE EQP.Tasks DROP COLUMN TaskTimestamp;
    PRINT 'Dropped TaskTimestamp column from EQP.Tasks';
END

-- Step 2: Drop OperatorNumber column (moved to RBD layer)
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('EQP.Tasks') AND name = 'OperatorNumber')
BEGIN
    ALTER TABLE EQP.Tasks DROP COLUMN OperatorNumber;
    PRINT 'Dropped OperatorNumber column from EQP.Tasks';
END

-- Step 3: Drop LastModifiedAt column
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('EQP.Tasks') AND name = 'LastModifiedAt')
BEGIN
    ALTER TABLE EQP.Tasks DROP COLUMN LastModifiedAt;
    PRINT 'Dropped LastModifiedAt column from EQP.Tasks';
END

-- Step 4: Drop LastModifiedByOperator column
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('EQP.Tasks') AND name = 'LastModifiedByOperator')
BEGIN
    ALTER TABLE EQP.Tasks DROP COLUMN LastModifiedByOperator;
    PRINT 'Dropped LastModifiedByOperator column from EQP.Tasks';
END

-- Step 5: Move tables to RBD schema
IF EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('EQP') AND name = 'PartDetails')
BEGIN
    ALTER SCHEMA RBD TRANSFER EQP.PartDetails;
    PRINT 'Transferred EQP.PartDetails to RBD schema';
END

-- PartInitialValues stays in EQP schema as EQP.catPartInitialValues (core table)

IF EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('EQP') AND name = 'TaskNotes')
BEGIN
    ALTER SCHEMA RBD TRANSFER EQP.TaskNotes;
    PRINT 'Transferred EQP.TaskNotes to RBD schema';
END

-- Step 6: Rename TaskNotes to TaskMetadata
IF EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('RBD') AND name = 'TaskNotes')
   AND NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('RBD') AND name = 'TaskMetadata')
BEGIN
    EXEC sp_rename 'RBD.TaskNotes', 'TaskMetadata';
    PRINT 'Renamed RBD.TaskNotes to RBD.TaskMetadata';
END

-- Step 7: Rename TaskNoteId to TaskMetadataId
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('RBD.TaskMetadata') AND name = 'TaskNoteId')
BEGIN
    EXEC sp_rename 'RBD.TaskMetadata.TaskNoteId', 'TaskMetadataId', 'COLUMN';
    PRINT 'Renamed TaskNoteId to TaskMetadataId';
END

-- Step 8: Drop old renamed SPs (SSDT will recreate with new names)
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'GetPartDetails')
    DROP PROCEDURE [RBD].[GetPartDetails];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'GetRoofPartDetails')
    DROP PROCEDURE [RBD].[GetRoofPartDetails];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'GetSidewallPartDetails')
    DROP PROCEDURE [RBD].[GetSidewallPartDetails];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'GetCasterPartDetails')
    DROP PROCEDURE [RBD].[GetCasterPartDetails];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'AddTaskNote')
    DROP PROCEDURE [RBD].[AddTaskNote];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'GetTaskNotes')
    DROP PROCEDURE [RBD].[GetTaskNotes];

-- Step 9: Drop UpsertRemoveTask (merged into UpsertTask with @ToDefaultArray)
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('EQP') AND name = 'UpsertRemoveTask')
BEGIN
    DROP PROCEDURE [EQP].[UpsertRemoveTask];
    PRINT 'Dropped EQP.UpsertRemoveTask (merged into EQP.UpsertTask)';
END

-- Step 10: Drop EQP.SetInitialMetricValue (moved to RBD.EQPSetInitialMetricValue)
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('EQP') AND name = 'SetInitialMetricValue')
BEGIN
    DROP PROCEDURE [EQP].[SetInitialMetricValue];
    PRINT 'Dropped EQP.SetInitialMetricValue (moved to RBD.EQPSetInitialMetricValue)';
END

-- Step 11: Drop RBD.PartInitialValues table (unused, core uses EQP.catPartInitialValues)
IF EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('RBD') AND name = 'PartInitialValues')
BEGIN
    DROP TABLE [RBD].[PartInitialValues];
    PRINT 'Dropped RBD.PartInitialValues (unused, core uses EQP.catPartInitialValues)';
END

-- Step 12: Move UpdateMetricsCacheByPartId from RBD to EQP schema
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'UpdateMetricsCacheByPartId')
BEGIN
    DROP PROCEDURE [RBD].[UpdateMetricsCacheByPartId];
    PRINT 'Dropped RBD.UpdateMetricsCacheByPartId (moved to EQP.UpdateMetricsCacheByPartId)';
END

-- Step 13: Clean duplicate ReferenceId in Batches (required before UNIQUE constraint)
IF EXISTS (SELECT ReferenceId FROM EQP.Batches GROUP BY ReferenceId HAVING COUNT(*) > 1)
BEGIN
    ;WITH Dupes AS (
        SELECT BatchId, ReferenceId,
            ROW_NUMBER() OVER (PARTITION BY ReferenceId ORDER BY BatchId ASC) AS rn
        FROM EQP.Batches
        WHERE ReferenceId IN (
            SELECT ReferenceId FROM EQP.Batches GROUP BY ReferenceId HAVING COUNT(*) > 1
        )
    )
    DELETE bm FROM EQP.BatchMetrics bm
    INNER JOIN Dupes d ON d.BatchId = bm.BatchId WHERE d.rn > 1;

    ;WITH Dupes AS (
        SELECT BatchId, ReferenceId,
            ROW_NUMBER() OVER (PARTITION BY ReferenceId ORDER BY BatchId ASC) AS rn
        FROM EQP.Batches
        WHERE ReferenceId IN (
            SELECT ReferenceId FROM EQP.Batches GROUP BY ReferenceId HAVING COUNT(*) > 1
        )
    )
    DELETE FROM Dupes WHERE rn > 1;

    PRINT 'Cleaned duplicate ReferenceId rows from EQP.Batches';
END

-- Step 14: Rename tracking jobs (old names → EQP* convention)
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'TrackRoofEquipmentJob')
    DROP PROCEDURE [RBD].[TrackRoofEquipmentJob];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'TrackSidewallEquipmentJob')
    DROP PROCEDURE [RBD].[TrackSidewallEquipmentJob];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'TrackCasterEquipmentJob')
    DROP PROCEDURE [RBD].[TrackCasterEquipmentJob];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'ReassignBatchesByTimeline')
    DROP PROCEDURE [RBD].[ReassignBatchesByTimeline];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'SyncMoldToCAS')
    DROP PROCEDURE [RBD].[SyncMoldToCAS];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'EQPSyncMoldToCAS')
    DROP PROCEDURE [RBD].[EQPSyncMoldToCAS];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'EQPSyncEQPToCAS')
    DROP PROCEDURE [RBD].[EQPSyncEQPToCAS];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'InitializeCasterEQP')
    DROP PROCEDURE [RBD].[InitializeCasterEQP];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'CreatePart')
    DROP PROCEDURE [RBD].[CreatePart];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE schema_id = SCHEMA_ID('RBD') AND name = 'CreateMoldAssembly')
    DROP PROCEDURE [RBD].[CreateMoldAssembly];

-- Step 16: Add WarehouseNumber and DrawingNumber columns to RBD.PartDetails if missing
-- (needed by the Caster Mold Seed below, before dotnet publish adds them)
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('RBD.PartDetails') AND name = 'WarehouseNumber')
    ALTER TABLE RBD.PartDetails ADD WarehouseNumber VARCHAR(100) NULL;
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('RBD.PartDetails') AND name = 'DrawingNumber')
    ALTER TABLE RBD.PartDetails ADD DrawingNumber VARCHAR(100) NULL;

END
