-- signing_keys moves to AccountState; signing_key_ids stays in D1 as the fingerprint/key id -> owner directory,
-- without a foreign key into the moved table. SSH keys get one row each (key_id = fingerprint).
CREATE TABLE signing_key_ids_new (
  key_id TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  signing_key_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  PRIMARY KEY (fingerprint, signing_key_id)
);

INSERT OR IGNORE INTO signing_key_ids_new (key_id, fingerprint, signing_key_id, user_id)
SELECT i.key_id, i.fingerprint, i.signing_key_id, k.user_id
FROM signing_key_ids i JOIN signing_keys k ON k.id = i.signing_key_id
ORDER BY k.created_at;

INSERT OR IGNORE INTO signing_key_ids_new (key_id, fingerprint, signing_key_id, user_id)
SELECT fingerprint, fingerprint, id, user_id FROM signing_keys WHERE kind = 'ssh' ORDER BY created_at;

DROP TABLE signing_key_ids;

ALTER TABLE signing_key_ids_new RENAME TO signing_key_ids;

CREATE INDEX signing_key_ids_key_id ON signing_key_ids(key_id);

CREATE INDEX signing_key_ids_signing_key ON signing_key_ids(signing_key_id);

CREATE INDEX signing_key_ids_user ON signing_key_ids(user_id);