-- sql/142-speed-recovery-rerun.sql
-- Re-run of sql/125's Remediation C (part-number speed token recovery) against
-- the current genuine-optics speed gap (v_transceiver_speed_errors, 596 rows as
-- of 2026-07-17). Same exact logic, same high-confidence-only design: derives
-- speed_gbps from the manufacturer's standardized speed token in the part
-- number, applied ONLY when it is <= the form factor's physical maximum. Parts
-- whose token exceeds the form-factor cap or that have no speed token are left
-- for datasheet work (596 -> ~248 after this, per the 2026-07-17 gap analysis:
-- 348/596 had a regex-recoverable G-token, mostly Cisco Systems + Juniper).
-- Idempotent, safe to re-run (no-ops on rows that already have a plausible
-- speed).

with cand as (
  select id,
    case
      when part_number ~* '([0-9]+)X([0-9]+)G'
        then (regexp_match(part_number,'([0-9]+)X([0-9]+)G','i'))[1]::numeric
           * (regexp_match(part_number,'([0-9]+)X([0-9]+)G','i'))[2]::numeric
      when part_number ~* '([0-9]+)SFP([0-9]+)G'
        then (regexp_match(part_number,'([0-9]+)SFP([0-9]+)G','i'))[1]::numeric
           * (regexp_match(part_number,'([0-9]+)SFP([0-9]+)G','i'))[2]::numeric
      when part_number ~* '([0-9]+)G'
        then (regexp_match(part_number,'([0-9]+)G','i'))[1]::numeric
    end as derived,
    case upper(coalesce(form_factor,''))
      when 'SFP' then 1  when 'SFP+' then 16  when 'SFP28' then 32  when 'SFP56' then 56
      when 'XFP' then 10 when 'QSFP+' then 40 when 'QSFP28' then 100 when 'QSFP56' then 200
      when 'QSFP-DD' then 800 when 'QSFP-DD800' then 800
      when 'OSFP' then 1600 when 'OSFP224' then 1600
      when 'CFP' then 100 when 'CFP2' then 400 when 'CFP4' then 100 when 'CPAK' then 100
      else 99999 end as ff_cap
  from transceivers
  where id in (select id from v_transceiver_speed_errors))
update transceivers t
   set speed_gbps = c.derived,
       updated_at = now()
  from cand c
 where t.id = c.id
   and c.derived is not null and c.derived > 0
   and c.derived <= c.ff_cap;

insert into _migrations (filename, applied_at)
select '142-speed-recovery-rerun.sql', now()
where not exists (select 1 from _migrations where filename = '142-speed-recovery-rerun.sql');