select 'post_deployment_script';

BEGIN

IF NOT EXISTS (SELECT 1 FROM SEC.catProfiles WHERE ProfileName = 'EAF Roof Fab Shop')
    INSERT INTO SEC.catProfiles (ProfileName) VALUES ('EAF Roof Fab Shop');

IF NOT EXISTS (SELECT 1 FROM SEC.catProfiles WHERE ProfileName = 'EAF Roof Operator')
    INSERT INTO SEC.catProfiles (ProfileName) VALUES ('EAF Roof Operator');

---EAF Roof Operator Profile
EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.eaf.roof-panel', 'EAF Roof Operator', 'devops'

-- Procedures actually used by the frontend:
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[SetRoofOnline]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[SetRoofOffline]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetPartsTree]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetPartDetails]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[RemovePart]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[CreatePart]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[MovePart]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[ResetPart]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpsertRoofPartDetails]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetAvailableParts]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetPartTypesMetricLimits]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpsertPartTypeMetricLimits]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[FixPartialMetric]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpdateTaskTimestamp]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpdateMetricsCache]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[DeleteTask]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[DeleteSparePart]', 'EAF Roof Operator', 'devops'

-- System procedures
EXEC SEC.UpsertProcedureAndSetToProfile '[RBD].[TrackEQPHeatsJob]', 'EAF Roof Operator', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile '[EQP.[GetTaskNotes]', 'EAF Roof Operator', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP.[AddTaskNote]', 'EAF Roof Operator', 'devops'

---EAF Roof Fab Shop Profile

EXEC SEC.UpsertProcedureAndSetToProfile 'ui.navbar.eaf.roof-panel', 'EAF Roof Fab Shop', 'devops'

-- Procedures actually used by the frontend:
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetPartsTree]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetPartDetails]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[RemovePart]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[CreatePart]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[MovePart]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[ResetPart]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpsertRoofPartDetails]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetAvailableParts]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[GetPartTypesMetricLimits]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpsertPartTypeMetricLimits]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[FixPartialMetric]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[UpdateMetricsCache]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[DeleteTask]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP].[DeleteSparePart]', 'EAF Roof Fab Shop', 'devops'

-- System procedures
EXEC SEC.UpsertProcedureAndSetToProfile '[RBD].[TrackEQPHeatsJob]', 'EAF Roof Fab Shop', 'devops'

EXEC SEC.UpsertProcedureAndSetToProfile '[EQP.[GetTaskNotes]', 'EAF Roof Fab Shop', 'devops'
EXEC SEC.UpsertProcedureAndSetToProfile '[EQP.[AddTaskNote]', 'EAF Roof Fab Shop', 'devops'


/*
================================================================================
MIGRATION: Unify Panel Types and Spare Containers
================================================================================

PURPOSE:
  Consolidate North/South variants of 55°, 60°, and Skirt panels into unified types.
  A single "55° Panel" can be installed in either South 55 or North 55 position.

CHANGES SUMMARY:
  Part Types:
    - 104 (South-55-Degree) + 111 (North-55-Degree) → 104 (55-Degree)
    - 105 (South-60-Degree) + 109 (North-60-Degree) → 105 (60-Degree)
    - 106 (South-Skirt) + 110 (North-Skirt) → 106 (Skirt)

  Spare Container Types:
    - 8 (South-55-Spares) + 15 (North-55-Spares) → 8 (55-Spares)
    - 9 (South-60-Spares) + 13 (North-60-Spares) → 9 (60-Spares)
    - 10 (South-Skirt-Spares) + 14 (North-Skirt-Spares) → 10 (Skirt-Spares)

  Spare Container Parts:
    - 23 (South 55 Spares) + 30 (North 55 Spares) → 23 (55° Spares)
    - 24 (South 60 Spares) + 28 (North 60 Spares) → 24 (60° Spares)
    - 25 (South Skirt Spares) + 29 (North Skirt Spares) → 25 (Skirt Spares)

================================================================================
*/

SET XACT_ABORT ON;
BEGIN TRANSACTION;

PRINT 'Starting panel types unification migration...';

-- ============================================================================
-- STEP 1: Update existing parts from deprecated types to unified types
-- ============================================================================

PRINT 'Step 1: Migrating parts from deprecated types to unified types...';

-- Update parts with North-55-Degree (111) to 55-Degree (104)
UPDATE [EQP].[catParts]
SET [PartTypeId] = 104
WHERE [PartTypeId] = 111;

PRINT '  - Migrated North-55-Degree parts to 55-Degree';

-- Update parts with North-60-Degree (109) to 60-Degree (105)
UPDATE [EQP].[catParts]
SET [PartTypeId] = 105
WHERE [PartTypeId] = 109;

PRINT '  - Migrated North-60-Degree parts to 60-Degree';

-- Update parts with North-Skirt (110) to Skirt (106)
UPDATE [EQP].[catParts]
SET [PartTypeId] = 106
WHERE [PartTypeId] = 110;

PRINT '  - Migrated North-Skirt parts to Skirt';

-- ============================================================================
-- STEP 2: Move tasks referencing deprecated container parts to unified containers
-- ============================================================================

PRINT 'Step 2: Migrating tasks from deprecated containers...';

-- Update ParentPartId to point to unified containers instead of deprecated ones
-- ParentPartId is the container where a part was moved to

-- Move tasks from North 55 Spares (30) to 55° Spares (23)
UPDATE [EQP].[Tasks]
SET [ParentPartId] = 23
WHERE [ParentPartId] = 30;

PRINT '  - Migrated tasks from North 55 Spares to 55° Spares';

-- Move tasks from North 60 Spares (28) to 60° Spares (24)
UPDATE [EQP].[Tasks]
SET [ParentPartId] = 24
WHERE [ParentPartId] = 28;

PRINT '  - Migrated tasks from North 60 Spares to 60° Spares';

-- Move tasks from North Skirt Spares (29) to Skirt Spares (25)
UPDATE [EQP].[Tasks]
SET [ParentPartId] = 25
WHERE [ParentPartId] = 29;

PRINT '  - Migrated tasks from North Skirt Spares to Skirt Spares';

-- ============================================================================
-- STEP 3: Update catPartTypesTree to accept unified types in both positions
-- ============================================================================

PRINT 'Step 3: Updating catPartTypesTree for unified types...';

-- Update Roof positions to accept unified types
-- Position 4 (South 55) already accepts type 104, keep it
-- Position 11 (North 55) was accepting type 111, change to type 104
UPDATE [EQP].[catPartTypesTree]
SET [PartTypeId] = 104
WHERE [ParentPartTypeId] = 3 AND [PositionId] = 11 AND [PartTypeId] = 111;

PRINT '  - Position 11 (North 55) now accepts 55-Degree (104)';

-- Position 5 (South 60) already accepts type 105, keep it
-- Position 9 (North 60) was accepting type 109, change to type 105
UPDATE [EQP].[catPartTypesTree]
SET [PartTypeId] = 105
WHERE [ParentPartTypeId] = 3 AND [PositionId] = 9 AND [PartTypeId] = 109;

PRINT '  - Position 9 (North 60) now accepts 60-Degree (105)';

-- Position 6 (South Skirt) already accepts type 106, keep it
-- Position 10 (North Skirt) was accepting type 110, change to type 106
UPDATE [EQP].[catPartTypesTree]
SET [PartTypeId] = 106
WHERE [ParentPartTypeId] = 3 AND [PositionId] = 10 AND [PartTypeId] = 110;

PRINT '  - Position 10 (North Skirt) now accepts Skirt (106)';

-- Remove deprecated spares container type entries from catPartTypesTree
-- North-55-Spares (15), North-60-Spares (13), North-Skirt-Spares (14)
DELETE FROM [EQP].[catPartTypesTree]
WHERE [PartTypeId] IN (13, 14, 15) OR [ParentPartTypeId] IN (13, 14, 15);

PRINT '  - Removed deprecated spares container types from tree';

-- ============================================================================
-- STEP 4: Update and deactivate deprecated container parts (catParts)
-- ============================================================================

PRINT 'Step 4: Updating and deactivating deprecated container parts...';

-- Update PartTypeId of deprecated container parts to unified types
-- This is required before we can delete the deprecated part types (FK constraint)
-- Also set IsDefaultArray = 0 so uninstalled parts go to unified containers instead
-- North 60 Spares (28): PartTypeId 13 → 9 (60-Spares)
UPDATE [EQP].[catParts]
SET [PartTypeId] = 9, [IsActive] = 0, [IsDefaultArray] = 0
WHERE [PartId] = 28;

-- North Skirt Spares (29): PartTypeId 14 → 10 (Skirt-Spares)
UPDATE [EQP].[catParts]
SET [PartTypeId] = 10, [IsActive] = 0, [IsDefaultArray] = 0
WHERE [PartId] = 29;

-- North 55 Spares (30): PartTypeId 15 → 8 (55-Spares)
UPDATE [EQP].[catParts]
SET [PartTypeId] = 8, [IsActive] = 0, [IsDefaultArray] = 0
WHERE [PartId] = 30;

PRINT '  - Updated and deactivated deprecated container parts (28, 29, 30)';

-- Ensure unified containers have IsDefaultArray = 1
UPDATE [EQP].[catParts]
SET [IsDefaultArray] = 1
WHERE [PartId] IN (23, 24, 25);

PRINT '  - Ensured unified containers (23, 24, 25) are default arrays';

-- ============================================================================
-- STEP 5: Rename unified types and containers
-- ============================================================================

PRINT 'Step 5: Renaming unified types and containers...';

-- Rename part types
UPDATE [EQP].[catPartTypes]
SET [PartTypeName] = '55-Degree'
WHERE [PartTypeId] = 104;

UPDATE [EQP].[catPartTypes]
SET [PartTypeName] = '60-Degree'
WHERE [PartTypeId] = 105;

UPDATE [EQP].[catPartTypes]
SET [PartTypeName] = 'Skirt'
WHERE [PartTypeId] = 106;

PRINT '  - Renamed panel types to unified names';

-- Rename spare container types
UPDATE [EQP].[catPartTypes]
SET [PartTypeName] = '55-Spares'
WHERE [PartTypeId] = 8;

UPDATE [EQP].[catPartTypes]
SET [PartTypeName] = '60-Spares'
WHERE [PartTypeId] = 9;

UPDATE [EQP].[catPartTypes]
SET [PartTypeName] = 'Skirt-Spares'
WHERE [PartTypeId] = 10;

PRINT '  - Renamed spare container types to unified names';

-- Rename spare container parts
UPDATE [EQP].[catParts]
SET [PartName] = '55° Spares'
WHERE [PartId] = 23;

UPDATE [EQP].[catParts]
SET [PartName] = '60° Spares'
WHERE [PartId] = 24;

UPDATE [EQP].[catParts]
SET [PartName] = 'Skirt Spares'
WHERE [PartId] = 25;

PRINT '  - Renamed spare container parts to unified names';

-- ============================================================================
-- STEP 6: Remove deprecated part types (after all references are updated)
-- ============================================================================

PRINT 'Step 6: Removing deprecated part types...';

-- Remove deprecated part types from catPartTypesMetricTypes (if any)
DELETE FROM [EQP].[catPartTypesMetricTypes]
WHERE [PartTypeId] IN (109, 110, 111, 13, 14, 15);

PRINT '  - Removed deprecated types from metric types table';

-- Remove deprecated part types
-- Note: Can only delete after all foreign key references are removed
DELETE FROM [EQP].[catPartTypes]
WHERE [PartTypeId] IN (109, 110, 111, 13, 14, 15);

PRINT '  - Removed deprecated part types (109, 110, 111, 13, 14, 15)';

-- ============================================================================
-- COMMIT
-- ============================================================================

COMMIT TRANSACTION;

PRINT '';
PRINT '================================================================================';
PRINT 'Migration completed successfully!';
PRINT '';
PRINT 'Summary:';
PRINT '  - Unified 55° panel types (104 absorbs 111)';
PRINT '  - Unified 60° panel types (105 absorbs 109)';
PRINT '  - Unified Skirt panel types (106 absorbs 110)';
PRINT '  - Unified 55° spares container (8 absorbs 15, 23 absorbs 30)';
PRINT '  - Unified 60° spares container (9 absorbs 13, 24 absorbs 28)';
PRINT '  - Unified Skirt spares container (10 absorbs 14, 25 absorbs 29)';
PRINT '  - Removed deprecated types: 109, 110, 111, 13, 14, 15';
PRINT '  - Deactivated deprecated parts: 28, 29, 30';
PRINT '================================================================================';



END
