 SET QUOTED_IDENTIFIER ON

GO

-- SCRIPT PRE DEPLOYMENT
-- ============================================
-- Script: Update LocationKey for m2.Locations
-- Description: Drop index, alter column to VARCHAR(3),
--              recreate index, and assign unique LocationKey values
-- ============================================

-- Step 1: Drop the existing index
DROP INDEX [UX_RS2_Locations_LocationKey] ON m2.Locations;

-- Step 2: Alter column to VARCHAR(3)
ALTER TABLE m2.Locations
ALTER COLUMN LocationKey VARCHAR(3);

-- Step 3: Recreate the filtered unique index
CREATE UNIQUE NONCLUSTERED INDEX [UX_RS2_Locations_LocationKey] 
ON m2.Locations ([LocationKey] ASC) 
WHERE IsActive = 1 AND LocationKey IS NOT NULL;

-- Step 4: Update LocationKey for each location
UPDATE m2.Locations
SET LocationKey = CASE LocationId
    WHEN 1  THEN 'MIL'  -- #2 Mill
    WHEN 2  THEN 'RS'   -- Rollshop
    WHEN 3  THEN 'BU'   -- #2 Mill Buildup
    WHEN 4  THEN 'RR'   -- #2 Mill Roll Racks
    WHEN 5  THEN 'PD'   -- #2 Mill Pond
    WHEN 6  THEN 'FL'   -- Flood Wall
    WHEN 7  THEN '2A'   -- 2nd Avenue
    WHEN 8  THEN 'FM'   -- Finishing Mill
    WHEN 9  THEN 'RM'   -- Roughing Mill
    WHEN 10 THEN 'F1'   -- F1 Stand
    WHEN 11 THEN 'F2'   -- F2 Stand
    WHEN 12 THEN 'F3'   -- F3 Stand
    WHEN 13 THEN 'F4'   -- F4 Stand
    WHEN 14 THEN 'F5'   -- F5 Stand
    WHEN 15 THEN 'F6'   -- F6 Stand
    WHEN 16 THEN 'F7'   -- F7 Stand
    WHEN 17 THEN 'R1'   -- R1 Stand
    WHEN 18 THEN 'R2'   -- R2 Stand
    WHEN 19 THEN 'R3'   -- R3 Stand
    WHEN 20 THEN 'R4'   -- R4 Stand
    WHEN 21 THEN 'PB'   -- MillPondBranch (Pond Branch)
END
WHERE LocationId BETWEEN 1 AND 21;

-- Step 5: Verify results
SELECT LocationId, LocationName, LocationKey, IsActive
FROM m2.Locations
ORDER BY LocationId;
 
  -- ============================================
  -- Script to fix Yield values (decimal -> percentage)
  -- Database: swv-mill
  -- Table: M2.ProductionEvents only
  -- ============================================

  -- Capture affected EventIds before correction
  DECLARE @AffectedEvents TABLE (EventId INT);

  INSERT INTO @AffectedEvents (EventId)
  SELECT EventId
  FROM M2.ProductionEvents
  WHERE (ActualMillYield > 0 AND ActualMillYield <= 1)
     OR (ActualFinishedYield > 0 AND ActualFinishedYield <= 1);

  -- Show affected records BEFORE correction
  PRINT '=== BEFORE CORRECTION ===';
  SELECT p.EventId, p.ActualMillYield, p.ActualFinishedYield
  FROM M2.ProductionEvents p
  INNER JOIN @AffectedEvents a ON p.EventId = a.EventId;

  -- Fix ActualMillYield
  UPDATE M2.ProductionEvents
  SET ActualMillYield = ActualMillYield * 100
  WHERE ActualMillYield > 0 AND ActualMillYield <= 1;

  -- Fix ActualFinishedYield
  UPDATE M2.ProductionEvents
  SET ActualFinishedYield = ActualFinishedYield * 100
  WHERE ActualFinishedYield > 0 AND ActualFinishedYield <= 1;

  -- Show records AFTER correction
  PRINT '=== AFTER CORRECTION ===';
  SELECT p.EventId, p.ActualMillYield, p.ActualFinishedYield
  FROM M2.ProductionEvents p
  INNER JOIN @AffectedEvents a ON p.EventId = a.EventId;

