-- Fix: old snap-creation code automatically bumped assessment_status_id to 5
-- (Verification Required) every time a snap was saved, even though no explicit
-- "Submit Assignment" was made by the executor.
--
-- This migration resets any assessment that:
--   • is currently at status 5 (Verification Required / Varification Required)
--   • has NO row in orac_verification_flow  (i.e. the verifier never received it)
--
-- Those assessments were silently locked by the old auto-bump behaviour.
-- We move them back to status 3 (In Progress / In Process) so the executor
-- can continue adding observations and explicitly submit when ready.

UPDATE `orac_assessments` a
SET    a.`assessment_status_id` = 3
WHERE  a.`assessment_status_id` = 5
  AND  NOT EXISTS (
         SELECT 1
         FROM   `orac_verification_flow` vf
         WHERE  vf.`assessment_id` = a.`assessment_id`
       );

-- Verify: should return 0 rows after the update.
SELECT `assessment_id`, `assessment_status_id`
FROM   `orac_assessments`
WHERE  `assessment_status_id` = 5
  AND  NOT EXISTS (
         SELECT 1
         FROM   `orac_verification_flow` vf
         WHERE  vf.`assessment_id` = `orac_assessments`.`assessment_id`
       );

-- ── Manual fix for assessments that cycled through verifier-returns ──────
--
-- Some assessments (e.g. assessment_id = 9) have orac_verification_flow rows
-- because the verifier returned them, but the LAST action was the old auto-bump
-- setting status = 5 again (not an explicit "Submit Assignment").
--
-- Identify them: status = 5, but most-recent action was snap creation, NOT an
-- explicit executor submit.  Safe to reset to 3 (In Progress).
--
-- Already applied 2026-03-27:
--   UPDATE orac_assessments SET assessment_status_id = 3 WHERE assessment_id = 9;
