-- sql/145-unmasked-spec-match-cleanup.sql
-- Follow-up to sql/144: the 14 false curated 10G-on-1G rows had been RAISING
-- the evidence-based ceiling (v_switch_effective_max takes the fastest curated
-- compatible transceiver as evidence) for those 1G-only switches, thereby
-- MASKING every algorithmic spec_match row claiming >1G parts on the same
-- switches. With the curated rows corrected, the ceiling is honestly 1G again
-- and v_compat_speed_errors correctly flags the previously-hidden rows (261
-- found live 2026-07-18). Same remediation as sql/125-B/138: flip algorithmic
-- rows exceeding the ceiling to incompatible. Backup first. Idempotent.

create table if not exists unmasked_spec_match_backup_20260718 as
select c.*, now() as backed_up_at
from compatibility c
where c.id in (select compat_id from v_compat_speed_errors);

update compatibility c
   set status = 'incompatible',
       notes  = case when c.notes is null or c.notes = ''
                     then 'auto(145): transceiver speed exceeds switch max port speed (unmasked by 144 curated-row correction)'
                     else c.notes || ' | auto(145): transceiver speed exceeds switch max port speed (unmasked by 144)' end
 where c.id in (select compat_id from v_compat_speed_errors);

insert into _migrations (filename, applied_at)
select '145-unmasked-spec-match-cleanup.sql', now()
where not exists (select 1 from _migrations where filename = '145-unmasked-spec-match-cleanup.sql');