SET QUOTED_IDENTIFIER ON

GO

-- =====================================================================
-- USG.WaterMeters.IncludeInStaleAlert -- one-time seed for this release.
-- The column ships as opt-in (DEFAULT 0), so nothing is monitored until it is turned on:
-- these are the four mill meters (AssetId 11/12), the only ones that report continuously
-- and whose silence means the ingestion path is broken. The municipal / street meters
-- (AssetId 1) stay at 0 -- they only report when the utility reads them, so they would
-- warn every single day.
--
-- A direct UPDATE rather than USG.UpsertWaterMeter: the upsert's UPDATE branch rewrites
-- MeterSerial, AccountId, MeterSize, AssetId and Description from its arguments, so this
-- touches the one column it means to touch and nothing else.
-- (PostDeployment is emptied after each release; the data persists in the DB.)
-- =====================================================================
UPDATE USG.WaterMeters
SET IncludeInStaleAlert = 1
WHERE AssetId IN (11, 12)
  AND IncludeInStaleAlert = 0;

GO

-- =====================================================================================
-- Mill 1 - Re-freeze shift incentive targets after the overlapping-events fix
--
-- M1 event 52 was scheduled on top of event 54 (both starting 2026-08-06 06:38:32 UTC).
-- M1.LockShiftTargetsJob packs events by (Start ASC, EventId ASC), so the tie was won by
-- the lower id (52, TargetTPH 12.3) instead of the real one (54, TargetTPH 9.0), and that
-- wrong segment was frozen into M1.ShiftIncentiveEvents. The calendar has since been
-- corrected (event 52 was deleted), but the job never re-freezes a shift that already has
-- frozen rows, so those shifts keep a TargetTons computed from an event that no longer
-- exists.
--
-- Fix: for the affected shifts only, drop the frozen snapshot and clear TargetTons (the
-- state the job treats as "row exists but not frozen yet"), then run the job so it rebuilds
-- the snapshot from the corrected calendar. The M1.ShiftIncentives row itself is kept, so
-- manager overrides / adjustments / notes survive.
--
-- Idempotent: once re-frozen there are no frozen rows pointing at deleted events left, so a
-- second run selects nothing and does nothing.
-- =====================================================================================
DECLARE @AssetId      INT      = 11,                      -- Mill 1
        @Now          DATETIME = GETUTCDATE(),
        @WindowStart  DATETIME = '2026-08-05',             -- bounds the fix to the affected days
        @WindowEnd    DATETIME = '2026-08-07',
        @LookbackDays INT,
        @Deleted      INT      = 0,
        @Cleared      INT      = 0;

DECLARE @Affected TABLE (
    ShiftId    INT      NOT NULL PRIMARY KEY,
    ShiftStart DATETIME NOT NULL
);

-- Affected = shifts whose frozen snapshot references an event that is no longer on the
-- calendar (deleted => IsActive = 0, or the row is gone altogether).
INSERT INTO @Affected (ShiftId, ShiftStart)
SELECT DISTINCT si.ShiftId, sh.[Start]
FROM M1.ShiftIncentiveEvents sie
INNER JOIN M1.ShiftIncentives si ON si.ShiftIncentivesId = sie.ShiftIncentivesId
INNER JOIN MES.Shifts sh         ON sh.ShiftId = si.ShiftId
LEFT JOIN M1.Events e            ON e.EventId = sie.EventId
WHERE sh.AssetId = @AssetId
  AND sh.[Start] >= @WindowStart
  AND sh.[Start] <  @WindowEnd
  AND (e.EventId IS NULL OR e.IsActive = 0);

IF NOT EXISTS (SELECT 1 FROM @Affected)
BEGIN
    PRINT 'M1 incentive re-freeze: nothing to fix, skipped.';
END
ELSE
BEGIN
    BEGIN TRANSACTION;

    DELETE sia
    FROM M1.ShiftIncentiveEventAttributes sia
    INNER JOIN M1.ShiftIncentiveEvents sie ON sie.ShiftIncentiveEventId = sia.ShiftIncentiveEventId
    INNER JOIN M1.ShiftIncentives si       ON si.ShiftIncentivesId = sie.ShiftIncentivesId
    INNER JOIN @Affected a                 ON a.ShiftId = si.ShiftId;

    DELETE sie
    FROM M1.ShiftIncentiveEvents sie
    INNER JOIN M1.ShiftIncentives si ON si.ShiftIncentivesId = sie.ShiftIncentivesId
    INNER JOIN @Affected a           ON a.ShiftId = si.ShiftId;

    SET @Deleted = @@ROWCOUNT;

    UPDATE si
    SET si.TargetTons = NULL,
        si.UpdatedAt  = @Now,
        si.UpdatedBy  = 'PostDeployment'
    FROM M1.ShiftIncentives si
    INNER JOIN @Affected a ON a.ShiftId = si.ShiftId;

    SET @Cleared = @@ROWCOUNT;

    COMMIT TRANSACTION;

    PRINT 'M1 incentive re-freeze: cleared ' + CAST(@Cleared AS VARCHAR(10))
        + ' shift(s), removed ' + CAST(@Deleted AS VARCHAR(10)) + ' frozen event row(s).';

    -- The job only considers shifts started within @LookbackDays, so widen the window to
    -- reach the oldest affected shift. Shifts that are already frozen are untouched by it.
    SELECT @LookbackDays = DATEDIFF(DAY, MIN(ShiftStart), @Now) + 1 FROM @Affected;

    EXEC [M1].[LockShiftTargetsJob] @AssetId = @AssetId, @LookbackDays = @LookbackDays;

    -- LockShiftTargetsJob swallows its own errors (logs them to SYSTEM.Logs), so verify the
    -- snapshot was actually rebuilt instead of trusting a silent success.
    IF EXISTS (
        SELECT 1
        FROM @Affected a
        INNER JOIN M1.ShiftIncentives si ON si.ShiftId = a.ShiftId
        WHERE si.TargetTons IS NULL
    )
    BEGIN
        ;THROW 51000, 'M1 incentive re-freeze failed: TargetTons is still NULL for at least one shift. Check SYSTEM.Logs for M1.LockShiftTargetsJob.', 1;
    END

    SELECT ShiftId       = a.ShiftId,
           ShiftStartUtc = a.ShiftStart,
           TargetTons    = si.TargetTons,
           FrozenEvents  = (SELECT COUNT(*) FROM M1.ShiftIncentiveEvents sie
                            WHERE sie.ShiftIncentivesId = si.ShiftIncentivesId)
    FROM @Affected a
    INNER JOIN M1.ShiftIncentives si ON si.ShiftId = a.ShiftId
    ORDER BY a.ShiftStart;
END

GO

-- =====================================================================================
-- LAB - Discard the training test that re-opened heat 58281
--
-- On 2026-08-06 a training pull was run on a leftover flat coupon and filed against heat
-- 58281 / section 3077 (swv-mes MechanicalSampleId 4975784, file
-- "20260806094033_58281-3077 TRAINING TEST.is_metal").
--
-- 58281 is a real production heat: it rolled section 3102 on Mill 2 in June, and Lab had
-- already dispositioned it 'P' on 2026-06-26. It never rolled 3077.
--
-- The measured values pass SWV023 (yield 51.19 in 50-65, tensile 71.80 >= 65, elongation
-- 23.45 >= 18). What failed is %RA: LAB.EvaluateMechanicalResult takes "is %RA required"
-- from the section, and 3077 has TestsBySection.Pull505 = 1, so a missing %RA is a failure.
-- A flat coupon cannot produce one. Auto-disposition wrote 'U' and the failure notification
-- went out -- both purely artifacts of the wrong section on a training sample.
--
-- Fix: soft-delete the result row and the auto disposition it triggered. The heat's last
-- active disposition then falls back to the manual 'P' from 2026-06-26, which is the state
-- Lab actually decided. Nothing is inserted, so the human decision is not re-stated by a
-- deployment script.
--
-- The June rows (samples 4975104/4975105, plus the empty 4975106 that leaked in before the
-- job had its "specimen was actually tested" guard) are left untouched -- they are the real
-- test of this heat.
--
-- Idempotent: both statements are guarded on IsActive = 1, so a second run affects nothing.
-- Paired with the sync job no longer filtering its dedupe on IsActive, the discarded sample
-- is not re-imported on the next run either.
-- =====================================================================================
DECLARE @TrainingSampleId INT = 4975784,   -- swv-mes LAB.MechanicalSamples.MechanicalSampleId
        @HeatName         VARCHAR(50) = '58281',
        @HeatId           INT,
        @SyncedAt         DATETIME,
        @ResultsDropped   INT = 0,
        @DispositionsDropped INT = 0,
        @CurrentCode      CHAR(1);

SELECT @HeatId   = r.HeatId,
       @SyncedAt = r.SyncedAt
FROM LAB.HeatMechanicalResults r
INNER JOIN MT.Heats h ON h.HeatId = r.HeatId
WHERE r.MechanicalSampleId = @TrainingSampleId
  AND h.HeatName = @HeatName
  AND r.IsActive = 1;

IF @HeatId IS NULL
BEGIN
    PRINT 'LAB 58281 training test: already discarded (or never synced), skipped.';
END
ELSE
BEGIN
    BEGIN TRANSACTION;

    UPDATE LAB.HeatMechanicalResults
    SET IsActive = 0
    WHERE MechanicalSampleId = @TrainingSampleId
      AND HeatId = @HeatId
      AND IsActive = 1;

    SET @ResultsDropped = @@ROWCOUNT;

    -- The auto disposition this sync produced. Matched by the sync timestamp rather than a
    -- hardcoded one: the job stamps the disposition with GETUTCDATE() moments after the row
    -- it just inserted, so anything from 'auto' at or after SyncedAt belongs to this run.
    -- The manual 'P' from June is older and by a real user, so it is never touched.
    UPDATE LAB.HeatTestDispositions
    SET IsActive = 0
    WHERE HeatId = @HeatId
      AND [User] = 'auto'
      AND [Timestamp] >= @SyncedAt
      AND IsActive = 1;

    SET @DispositionsDropped = @@ROWCOUNT;

    COMMIT TRANSACTION;

    PRINT 'LAB 58281 training test: dropped ' + CAST(@ResultsDropped AS VARCHAR(10))
        + ' result row(s) and ' + CAST(@DispositionsDropped AS VARCHAR(10))
        + ' auto disposition(s).';
END

-- Verify our own change landed: the training result and the disposition it triggered must be
-- inactive. Deliberately not asserting the heat ends on 'P' -- if Lab has since dispositioned
-- 58281 on purpose, that is their call and must not fail a deployment.
IF EXISTS (
    SELECT 1
    FROM LAB.HeatMechanicalResults r
    WHERE r.MechanicalSampleId = @TrainingSampleId
      AND r.IsActive = 1
)
BEGIN
    ;THROW 51001, 'LAB 58281 training test: the training result is still active. Check LAB.HeatMechanicalResults before releasing.', 1;
END

SELECT TOP 1 @CurrentCode = d.SampleStatusCode
FROM LAB.HeatTestDispositions d
INNER JOIN MT.Heats h ON h.HeatId = d.HeatId
WHERE h.HeatName = @HeatName
  AND d.IsActive = 1
ORDER BY d.[Timestamp] DESC;

PRINT 'LAB 58281 training test: current disposition is ' + ISNULL(@CurrentCode, '(none)') + '.';

GO
