-- Copyright 2026 Beacon Contributors
-- SPDX-License-Identifier: AGPL-3.0-or-later

-- known_routes grew unbounded (28 GB / 32.8M rows in 24 days on prod) and its
-- UNIQUE(node_ids, iata) key indexed whole UUID arrays. Rebuild keyed on a
-- 16-byte md5 of node_ids and add reconfirm bookkeeping. No rows are pruned
-- here: retention is deployment config (routes.retention/grace), so the first
-- cleanup tick after startup enforces it; a schema migration must not bake in
-- one deployment's policy. id stays (the API serializes it) but loses its
-- index; uniqueness lives on (iata, path_key).

CREATE TABLE known_routes_new (
  id                  BIGINT      GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  path_key            BYTEA       NOT NULL,
  node_ids            UUID[]      NOT NULL,
  hash_prefix         BYTEA[]     NOT NULL,
  iata                CHAR(3)     NOT NULL REFERENCES iata_codes(iata) ON DELETE CASCADE,
  hop_count           INT         NOT NULL,
  first_seen          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  observation_count   BIGINT      NOT NULL DEFAULT 1,
  last_reconfirmed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (iata, path_key)
);

INSERT INTO known_routes_new
  (id, path_key, node_ids, hash_prefix, iata, hop_count, first_seen, last_seen, observation_count)
SELECT id,
       decode(md5(array_to_string(node_ids, ',')), 'hex'),
       node_ids, hash_prefix, iata, hop_count, first_seen, last_seen, observation_count
FROM known_routes;

SELECT setval(pg_get_serial_sequence('known_routes_new', 'id'),
              (SELECT COALESCE(MAX(id), 1) FROM known_routes_new));

DROP TABLE known_routes;
ALTER TABLE known_routes_new RENAME TO known_routes;
ALTER TABLE known_routes RENAME CONSTRAINT known_routes_new_pkey TO known_routes_pkey;
ALTER TABLE known_routes RENAME CONSTRAINT known_routes_new_iata_fkey TO known_routes_iata_fkey;

CREATE INDEX idx_known_routes_hop_count ON known_routes(iata, hop_count);
CREATE INDEX idx_known_routes_last_seen ON known_routes(last_seen DESC);
CREATE INDEX idx_known_routes_reconfirm ON known_routes(last_reconfirmed_at);