-- ============================================
-- Ladle History Screen - Database Setup Script
-- ============================================
-- This script creates:
-- 1. REF schema (if not exists)
-- 2. REF.LadleHistoryComments table
-- 3. REF.GetLadleEquipmentHistory stored procedure
-- 4. REF.UpsertLadleHistoryComment stored procedure
-- 5. Routes and Menu entries
-- 6. Role and permissions
-- ============================================

USE [MES]
GO

-- ============================================
-- 1. Create REF schema if not exists
-- ============================================
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'REF')
BEGIN
    EXEC('CREATE SCHEMA REF')
END
GO

-- ============================================
-- 2. Create REF.LadleHistoryComments table
-- ============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[REF].[LadleHistoryComments]') AND type in (N'U'))
BEGIN
    CREATE TABLE [REF].[LadleHistoryComments] (
        [HeatId] VARCHAR(50) NOT NULL,
        [SentBy] VARCHAR(100) NULL,
        [CompletedBy] VARCHAR(100) NULL,
        [Comments] VARCHAR(500) NULL,
        [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(),
        [UpdatedAt] DATETIME NOT NULL DEFAULT GETDATE(),
        [CreatedBy] VARCHAR(100) NULL,
        [UpdatedBy] VARCHAR(100) NULL,
        CONSTRAINT [PK_LadleHistoryComments] PRIMARY KEY CLUSTERED ([HeatId] ASC)
    )
END
GO

-- ============================================
-- 3. Create REF.GetLadleEquipmentHistory stored procedure
-- ============================================
  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[REF].[GetLadleEquipmentHistory]') AND type in (N'P', N'PC'))
      DROP PROCEDURE [REF].[GetLadleEquipmentHistory]
  GO

  CREATE PROCEDURE [REF].[GetLadleEquipmentHistory]
      @start   DATETIME = NULL,
      @end     DATETIME = NULL,
      @LadleId INT      = -1      -- pass -1 to include ALL ladles
  AS
  BEGIN
      SET NOCOUNT ON;

      IF @end IS NULL
          SET @end = GETUTCDATE();

      IF @start IS NULL
          SET @start = DATEADD(DAY, -1, @end);

      IF DATEDIFF(DAY, @start, @end) > 3
          SET @start = DATEADD(DAY, -3, @end);

      -------------------------------------------------------------------------
      -- Resolve ladles ONCE (table variable avoids CTE scope issues)
      -------------------------------------------------------------------------
      DECLARE @Ladles TABLE (
          LadleId INT PRIMARY KEY
      );

      IF @LadleId = -1
          INSERT INTO @Ladles (LadleId)
          SELECT l.LadleId
          FROM LAD.Ladles AS l;
      ELSE
          INSERT INTO @Ladles (LadleId) VALUES (@LadleId);

      -------------------------------------------------------------------------
      -- Heats: TOP 100 per LadleId, StartTap from EAF.Processes
      -------------------------------------------------------------------------
      ;WITH HeatsRN AS (
          SELECT
              m.LadleId,
              HeatId         = m.[Name],
              TapTime        = CONVERT(VARCHAR(23), IH.ToLocal(e.StartTap, 'EDTUSCND'), 121),
              StartTap       = e.StartTap,
              TapTemperature = (
                  SELECT TOP (1) z.Temperature
                  FROM EAF.Temperatures z
                  WHERE z.ProcessId = e.Id
                  ORDER BY z.[Timestamp] DESC
              ),
              m.SteelWeight,
              rn = ROW_NUMBER() OVER (PARTITION BY m.LadleId ORDER BY m.Id DESC)
          FROM MES.Heats AS m
          INNER JOIN @Ladles AS ls
              ON ls.LadleId = m.LadleId
          LEFT JOIN EAF.Processes AS e
              ON e.HeatId = m.Id
          WHERE m.ProductionDate BETWEEN @start AND @end
            AND e.StartTap IS NOT NULL
      )
      SELECT
          LadleId,
          HeatId,
          TapTime,
          StartTap,
          TapTemperature,
          SteelWeight
      INTO #t
      FROM HeatsRN
      WHERE rn <= 25;

      CREATE INDEX IX_t_Ladle_Start ON #t (LadleId, StartTap DESC);

      -------------------------------------------------------------------------
      -- Drains for selected ladles + window
      -------------------------------------------------------------------------
      INSERT INTO #t (LadleId, HeatId, TapTime, StartTap)
      SELECT
          d.LadleId,
          'Drain' AS HeatId,
          CONVERT(VARCHAR(23), IH.ToLocal(d.[Timestamp], 'EDTUSCND'), 121),
          d.[Timestamp]
      FROM MES.LAD.LadleDrains AS d
      INNER JOIN @Ladles AS ls ON ls.LadleId = d.LadleId
      WHERE d.[Timestamp] BETWEEN @start AND DATEADD(DAY, 1, @end);

      -------------------------------------------------------------------------
      -- Counters per row (per LadleId & StartTap)
      -------------------------------------------------------------------------
      SELECT
          t.LadleId,
          t.HeatId,
          t.TapTime,
          t.StartTap,
          t.TapTemperature,
          t.SteelWeight,
          ec.typeid,
          [Name] = REPLACE(et.[Name], ' ', ''),
          ec.heatcount,
          ec.[offset],
          ec.drains,
          correctedHeatcount =
              ec.heatcount + ec.[offset]
              + CASE WHEN ec.typeid = 49 THEN ec.drains ELSE 0 END
      INTO #t2
      FROM #t AS t
      OUTER APPLY LAD.GetEquipmentHeatCountersByLadle_History(t.LadleId, t.StartTap) AS ec
      LEFT JOIN IHBoxSystem.equip.EquipmentTypes AS et
          ON et.Id = ec.typeid;

      -------------------------------------------------------------------------
      -- Insert 'Last' rows per ladle
      -------------------------------------------------------------------------
      INSERT INTO #t2 (LadleId, TypeId, [Name], HeatId, TapTime, StartTap, correctedHeatcount)
      SELECT
          ls.LadleId,
          ec.TypeId,
          REPLACE(et.[Name], ' ', '') AS [Name],
          'Last' AS HeatId,
          CONVERT(VARCHAR(23), IH.ToLocal(GETUTCDATE(), 'EDTUSCND'), 121) AS TapTime,
          GETUTCDATE() AS StartTap,
          ec.heatcount + ec.[offset]
          + CASE WHEN ec.typeid = 49 THEN ec.drains ELSE 0 END
      FROM @Ladles AS ls
      OUTER APPLY LAD.GetEquipmentHeatCountersByLadle_History(ls.LadleId, NULL) AS ec
      LEFT JOIN IHBoxSystem.equip.EquipmentTypes AS et
          ON et.Id = ec.typeid;

      -------------------------------------------------------------------------
      -- Pivot with LEFT JOIN to REF.LadleHistoryComments
      -------------------------------------------------------------------------
      SELECT
          piv.LadleId,
          piv.HeatId,
          piv.StartTap,
          piv.TapTime,
          piv.TapTemperature,
          piv.SteelWeight,
          piv.[LadleShell],
          piv.[LadleSlideGate],
          piv.[MountingPlate],
          piv.[PlateAssembly],
          piv.[SafetyLining],
          piv.[UpperNozzle],
          piv.[WorkingLining],
          piv.[PorousPlugNorth],
          piv.[PorousPlugSouth],
          ISNULL(c.SentBy, '') AS SentBy,
          ISNULL(c.CompletedBy, '') AS CompletedBy,
          ISNULL(c.Comments, '') AS Comments
      FROM (
          SELECT
              LadleId, HeatId, StartTap, TapTime, TapTemperature, SteelWeight,
              correctedHeatcount, [Name]
          FROM #t2
      ) AS src
      PIVOT (
          MAX(correctedHeatcount)
          FOR [Name] IN (
              [LadleShell],
              [LadleSlideGate],
              [MountingPlate],
              [PlateAssembly],
              [SafetyLining],
              [UpperNozzle],
              [WorkingLining],
              [PorousPlugNorth],
              [PorousPlugSouth]
          )
      ) AS piv
      LEFT JOIN REF.LadleHistoryComments c ON piv.HeatId = c.HeatId
      ORDER BY piv.LadleId, piv.StartTap DESC;

      RETURN 0;
  END
  GO

-- ============================================
-- 4. Create REF.UpsertLadleHistoryComment stored procedure
-- ============================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[REF].[UpsertLadleHistoryComment]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [REF].[UpsertLadleHistoryComment]
GO

CREATE PROCEDURE [REF].[UpsertLadleHistoryComment]
    @HeatId VARCHAR(50),
    @SentBy VARCHAR(100) = NULL,
    @CompletedBy VARCHAR(100) = NULL,
    @Comments VARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM REF.LadleHistoryComments WHERE HeatId = @HeatId)
    BEGIN
        -- Update existing record
        UPDATE REF.LadleHistoryComments
        SET
            SentBy = @SentBy,
            CompletedBy = @CompletedBy,
            Comments = @Comments,
            UpdatedAt = GETDATE(),
            UpdatedBy = SYSTEM_USER
        WHERE HeatId = @HeatId
    END
    ELSE
    BEGIN
        -- Insert new record
        INSERT INTO REF.LadleHistoryComments (HeatId, SentBy, CompletedBy, Comments, CreatedBy, UpdatedBy)
        VALUES (@HeatId, @SentBy, @CompletedBy, @Comments, SYSTEM_USER, SYSTEM_USER)
    END

    SELECT 1 AS Success
END
GO

-- ============================================
-- 5. Create Role (if not exists)
-- ============================================
USE [IHBoxSystem]
GO

IF DATABASE_PRINCIPAL_ID('ref_lh_r') IS NULL
BEGIN
    CREATE ROLE ref_lh_r;
END
GO

IF DATABASE_PRINCIPAL_ID('ref_lh_w') IS NULL
BEGIN
    CREATE ROLE ref_lh_w;;
END
GO

-- Add admin to role
EXEC sp_addrolemember 'ref_lh_r', 'admin'
GO

EXEC sp_addrolemember 'ref_lh_w', 'admin'
GO

-- ============================================
-- 6. Routes and Menu
-- ============================================
DECLARE @parent INT, @result_routeId INT

-- Get parent menu item for Ladles
SELECT @parent = id FROM [FrontEnd].[Menu] WHERE [Name] = 'Ladles' AND Parent IS NULL

-- If Ladles menu doesn't exist, try to find Refractories or create under root
IF @parent IS NULL
BEGIN
    SELECT @parent = id FROM [FrontEnd].[Menu] WHERE [Name] = 'Refractories' AND Parent IS NULL
END

-- Insert route
INSERT INTO [FrontEnd].[Routes]
    ([Route], [ModuleName], [ModulePath], [RequiredRole])
VALUES
    ('!/ref-ladle-history(/:start)(/:end)(/:ladleId)(/)',
     'ref-ladle-history',
     'pages/ref/ref-ladle-history/',
     'ref_lh_r')

SET @result_routeId = @@IDENTITY

-- Insert menu entry
INSERT INTO [FrontEnd].[Menu]
    ([Parent], [Name], [RouteID], [SortOrder], [Enabled], [Visible], [RequiredRole], [Group])
VALUES
    (@parent,
     'Ladle Equipment History',
     @result_routeId,
     10,
     1,
     1,
     'ref_lh_r',
     NULL)
GO

-- ============================================
-- 7. SP Permissions
-- ============================================
-- Read permissions
EXECUTE sys.sp_addextendedproperty
    @level0type = N'USER',
    @level0name = [ref_lh_r],
    @name = N'securedsp-REF.GetLadleEquipmentHistory',
    @value = N''
GO

EXECUTE sys.sp_addextendedproperty
    @level0type = N'USER',
    @level0name = [ref_lh_r],
    @name = N'securedsp-LAD.GetLadles',
    @value = N''
GO

-- Write permissions
EXECUTE sys.sp_addextendedproperty
    @level0type = N'USER',
    @level0name = [ref_lh_w],
    @name = N'securedsp-REF.UpsertLadleHistoryComment',
    @value = N''
GO

-- ============================================
-- 8. Security Module Configuration
-- ============================================
DECLARE @ModuleId INT

-- Check if module already exists
IF NOT EXISTS (SELECT 1 FROM [SECURITY].[Modules] WHERE [Code] = 'ref_ladle_history')
BEGIN
    INSERT INTO [SECURITY].[Modules]([Code], [Name])
    VALUES('ref_ladle_history', 'Ladle Equipment History')

    SET @ModuleId = @@IDENTITY

    INSERT INTO [SECURITY].ModuleActions(ModuleId, Code, [Name], [Role])
    VALUES (@ModuleId, 'read', 'Read', 'ref_lh_r')

    INSERT INTO [SECURITY].ModuleActions(ModuleId, Code, [Name], [Role])
    VALUES (@ModuleId, 'write', 'Write', 'ref_lh_w')
END
GO

