-- SQLite backfill: one snapshot per revision with materialized prefixes.

INSERT INTO prefix_snapshot (id, content_hash)
SELECT lower(hex(randomblob(16))),
    substr(replace(cr.id, '-', '') || replace(cr.id, '-', ''), 1, 64)
FROM config_revision cr
WHERE cr.prefix_snapshot_id IS NULL
  AND EXISTS (SELECT 1 FROM revision_materialized_prefix rmp WHERE rmp.revision_id = cr.id);

UPDATE config_revision
SET prefix_snapshot_id = (
    SELECT ps.id FROM prefix_snapshot ps
    WHERE ps.content_hash = substr(replace(config_revision.id, '-', '') || replace(config_revision.id, '-', ''), 1, 64)
)
WHERE prefix_snapshot_id IS NULL
  AND EXISTS (SELECT 1 FROM revision_materialized_prefix rmp WHERE rmp.revision_id = config_revision.id);

INSERT INTO prefix_snapshot_row (snapshot_id, ord, prefix, community_id, source)
SELECT cr.prefix_snapshot_id,
    (SELECT COUNT(*) FROM revision_materialized_prefix r2
     WHERE r2.revision_id = cr.id AND r2.id <= rmp.id) - 1,
    rmp.prefix,
    rmp.community_id,
    rmp.source
FROM config_revision cr
JOIN revision_materialized_prefix rmp ON rmp.revision_id = cr.id
WHERE cr.prefix_snapshot_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM prefix_snapshot_row psr WHERE psr.snapshot_id = cr.prefix_snapshot_id
  );