INSERT OR IGNORE INTO identities(did) SELECT did FROM legacy_soft_accounts;
INSERT OR IGNORE INTO identities(did) SELECT author FROM legacy_soft_vouches;
INSERT OR IGNORE INTO identities(did) SELECT subject FROM legacy_soft_vouches;
INSERT OR IGNORE INTO identities(did) SELECT author FROM legacy_soft_versions;
INSERT OR IGNORE INTO identities(did) SELECT author FROM legacy_list_records;
INSERT OR IGNORE INTO identities(did) SELECT subject FROM legacy_list_records WHERE subject != '';
INSERT OR IGNORE INTO identities(did) SELECT author FROM legacy_list_versions;
INSERT OR IGNORE INTO identities(did) SELECT owner FROM legacy_list_snapshots;
INSERT OR IGNORE INTO identities(did) SELECT author FROM legacy_list_bootstraps;
INSERT OR IGNORE INTO identities(did) SELECT author FROM legacy_trust_dependencies;

INSERT INTO soft_accounts SELECT i.id, a.active, a.account_us, a.bootstrap_us
FROM legacy_soft_accounts a JOIN identities i ON i.did = a.did;
INSERT INTO soft_vouches SELECT a.id, s.id, v.source, v.record_key, v.score, v.created_at
FROM legacy_soft_vouches v JOIN identities a ON a.did = v.author JOIN identities s ON s.did = v.subject;
INSERT INTO soft_versions SELECT a.id, v.source, v.record_key, v.time_us
FROM legacy_soft_versions v JOIN identities a ON a.did = v.author;
INSERT INTO list_records SELECT a.id, r.source, r.record_key, r.list_uri, s.id, r.purpose, r.created_at
FROM legacy_list_records r JOIN identities a ON a.did = r.author LEFT JOIN identities s ON s.did = NULLIF(r.subject, '');
INSERT INTO list_versions SELECT a.id, v.source, v.record_key, v.time_us
FROM legacy_list_versions v JOIN identities a ON a.did = v.author;
INSERT INTO list_snapshots SELECT l.list_uri, i.id, l.bootstrap_us
FROM legacy_list_snapshots l JOIN identities i ON i.did = l.owner;
INSERT INTO list_bootstraps SELECT i.id, l.bootstrap_us
FROM legacy_list_bootstraps l JOIN identities i ON i.did = l.author;
INSERT INTO trust_dependencies SELECT d.cache_key, i.id, d.record_key, d.source
FROM legacy_trust_dependencies d JOIN identities i ON i.did = d.author;

DROP TABLE legacy_soft_vouches;
DROP TABLE legacy_soft_versions;
DROP TABLE legacy_list_records;
DROP TABLE legacy_list_versions;
DROP TABLE legacy_list_snapshots;
DROP TABLE legacy_list_bootstraps;
DROP TABLE legacy_trust_dependencies;
DROP TABLE legacy_soft_accounts;