-- 136  Make speed_gbps nullable + one-time reversible cleanup of junk speed.
-- Requires 135 (tip_speed_is_implausible, trg_speed_plausibility) applied first.
--
-- speed_gbps was NOT NULL, which (a) forced meaningless values onto cables/accessories
-- and (b) made the guard trigger unusable: NULLing junk on write would abort the insert.
-- "Unknown speed" is honestly NULL, so we drop the constraint, then NULL the ~4708
-- unambiguous-junk rows (sub-0.09 fragments incl. <=0, and >1600 cable-part-number
-- integers). 87% are dac/aoc/accessory where speed is meaningless anyway. The old value
-- is snapshotted by primary key so the change is fully reversible. The 0.09..1600 gray
-- band (~355 legit FC/SDI/CPRI/OTN line rates) is intentionally left untouched.
--
-- PREVIEW before running (nothing legit should appear):
--   SELECT speed_gbps, count(*) FROM transceivers
--   WHERE tip_speed_is_implausible(speed_gbps) GROUP BY 1 ORDER BY 1;

BEGIN;

ALTER TABLE transceivers ALTER COLUMN speed_gbps DROP NOT NULL;

CREATE TABLE IF NOT EXISTS tip_speed_gbps_rollback (
  id             uuid PRIMARY KEY,
  old_speed_gbps numeric,
  backed_up_at   timestamptz DEFAULT now()
);

INSERT INTO tip_speed_gbps_rollback (id, old_speed_gbps)
SELECT id, speed_gbps
FROM transceivers
WHERE tip_speed_is_implausible(speed_gbps)
ON CONFLICT (id) DO NOTHING;

UPDATE transceivers
SET speed_gbps = NULL
WHERE tip_speed_is_implausible(speed_gbps);

COMMIT;

-- REVERSE (maintenance window, no concurrent writers). Disable the guard so it does
-- not re-NULL the junk being restored:
--   BEGIN;
--     ALTER TABLE transceivers DISABLE TRIGGER trg_speed_plausibility;
--     UPDATE transceivers t SET speed_gbps = r.old_speed_gbps
--     FROM tip_speed_gbps_rollback r
--     WHERE t.id = r.id AND t.speed_gbps IS NULL;
--     ALTER TABLE transceivers ENABLE TRIGGER trg_speed_plausibility;
--     -- optional, only if every row is non-null again:
--     -- ALTER TABLE transceivers ALTER COLUMN speed_gbps SET NOT NULL;
--   COMMIT;