-- 003_inconclusive.sql
--
-- A run that fetched nothing usable is not "completed" and it is not "failed"
-- either: the job ran to the end without erroring, but there is no evidence to
-- score. Before this state existed, such a run was stored as completed and the
-- scorer's empty denominator published it as 100.00 — a WAF-blocked origin
-- looked identical to a flawless site.
--
-- Safe to re-run: the ALTER is idempotent in effect (setting the same enum
-- twice is a no-op), and no rows change.

ALTER TABLE audit_run
  MODIFY COLUMN status
    ENUM('queued','running','completed','inconclusive','failed','cancelled')
    NOT NULL DEFAULT 'queued';

-- ---------------------------------------------------------------------------
-- Retire the scores the old scorer published for runs that never fetched
-- anything. With an empty denominator it returned 100.00, so a WAF-blocked or
-- unreachable origin is sitting in the database as a perfect site. Those rows
-- cannot be repaired — there is no evidence to rescore from — so they are
-- removed and the runs relabelled. A fresh install has no such rows and this
-- section does nothing.

CREATE TEMPORARY TABLE aiseo_unfetched_runs AS
SELECT r.id
  FROM audit_run r
  LEFT JOIN crawled_url c
         ON c.run_id = r.id AND c.status_code >= 200 AND c.status_code < 300
 WHERE r.status IN ('completed','inconclusive')
 GROUP BY r.id
HAVING COUNT(c.id) = 0;

DELETE FROM score
 WHERE scope IN ('overall','category','risk','strength')
   AND run_id IN (SELECT id FROM aiseo_unfetched_runs);

UPDATE audit_run
   SET status = 'inconclusive',
       error_message = COALESCE(
         error_message,
         'Relabelled by migration 003: this run fetched no 2xx response, so the score it carried was an artefact of an empty denominator, not a measurement. Re-run the audit.'
       )
 WHERE id IN (SELECT id FROM aiseo_unfetched_runs);

DROP TEMPORARY TABLE aiseo_unfetched_runs;