-- The first deploy applied an earlier 0054 with fewer locator kinds. Re-run every backfill (all idempotent) after
-- clearing rows whose meaning changed: site_host now maps a host to the SITE ID, and runner rows had NULL scopes.
DELETE FROM scope_locators WHERE kind = 'site_host';
DELETE FROM scope_locators WHERE scope_id IS NULL;

INSERT OR IGNORE INTO scope_locators SELECT 'issue', id, repo_id FROM issues;
INSERT OR IGNORE INTO scope_locators SELECT 'comment', c.id, i.repo_id FROM comments c JOIN issues i ON i.id = c.issue_id;
INSERT OR IGNORE INTO scope_locators SELECT 'review', r.id, i.repo_id FROM reviews r JOIN issues i ON i.id = r.issue_id;
INSERT OR IGNORE INTO scope_locators
  SELECT 'review_comment', rc.id, i.repo_id FROM review_comments rc JOIN issues i ON i.id = rc.issue_id;
INSERT OR IGNORE INTO scope_locators SELECT 'discussion', id, repo_id FROM discussions;
INSERT OR IGNORE INTO scope_locators
  SELECT 'discussion_comment', dc.id, d.repo_id FROM discussion_comments dc JOIN discussions d ON d.id = dc.discussion_id;
INSERT OR IGNORE INTO scope_locators SELECT 'release', id, repo_id FROM releases;
INSERT OR IGNORE INTO scope_locators
  SELECT 'release_asset', ra.id, r.repo_id FROM release_assets ra JOIN releases r ON r.id = ra.release_id;
INSERT OR IGNORE INTO scope_locators SELECT 'workflow_run', id, repo_id FROM workflow_runs;
INSERT OR IGNORE INTO scope_locators SELECT 'workflow_job', id, repo_id FROM workflow_jobs;
INSERT OR IGNORE INTO scope_locators SELECT 'job_token', token_hash, repo_id FROM job_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'deploy_token', token_hash, repo_id FROM deploy_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'webhook', id, repo_id FROM webhooks;
INSERT OR IGNORE INTO scope_locators SELECT 'attachment', id, repo_id FROM attachments WHERE repo_id IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'security_advisory', id, repo_id FROM security_advisories;
INSERT OR IGNORE INTO scope_locators SELECT 'site', id, COALESCE(repo_id, 'site:' || id) FROM sites;
INSERT OR IGNORE INTO scope_locators SELECT 'site_claim', claim_token_hash, COALESCE(repo_id, 'site:' || id) FROM sites
  WHERE claim_token_hash IS NOT NULL;
INSERT OR IGNORE INTO scope_locators
  SELECT 'site_deployment', sd.id, COALESCE(s.repo_id, 'site:' || s.id) FROM site_deployments sd JOIN sites s ON s.id = sd.site_id;
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(label), id FROM sites WHERE label IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(custom_domain), id FROM sites WHERE custom_domain IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(sd.label), sd.site_id FROM site_deployments sd WHERE sd.label IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'site_host', lower(pa.label), pa.site_id FROM site_pr_aliases pa WHERE pa.label IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'session', token_hash, user_id FROM sessions;
INSERT OR IGNORE INTO scope_locators SELECT 'access_token', token_hash, user_id FROM access_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'access_token_id', id, user_id FROM access_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'auth_token', token_hash, user_id FROM auth_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'oauth_code', code_hash, user_id FROM oauth_codes;
INSERT OR IGNORE INTO scope_locators SELECT 'oauth_refresh', token_hash, user_id FROM oauth_refresh_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'unsubscribe', token, user_id FROM notification_unsubscribe_tokens;
INSERT OR IGNORE INTO scope_locators SELECT 'passkey', id, user_id FROM passkeys;
INSERT OR IGNORE INTO scope_locators
  SELECT 'runner', ru.id, COALESCE(ru.owner_id, r.owner_id) FROM runners ru LEFT JOIN repos r ON r.id = ru.repo_id;
INSERT OR IGNORE INTO scope_locators
  SELECT 'runner_token', ru.token_hash, COALESCE(ru.owner_id, r.owner_id) FROM runners ru LEFT JOIN repos r ON r.id = ru.repo_id;
INSERT OR IGNORE INTO scope_locators
  SELECT 'runner_registration', t.token_hash, COALESCE(t.owner_id, r.owner_id) FROM runner_registration_tokens t
  LEFT JOIN repos r ON r.id = t.repo_id;
INSERT OR IGNORE INTO scope_locators SELECT 'org_invitation', id, org_id FROM org_invitations;
INSERT OR IGNORE INTO scope_locators SELECT 'project', id, owner_id FROM projects;
INSERT OR IGNORE INTO scope_locators SELECT 'project_item', pi.id, p.owner_id FROM project_items pi
  JOIN projects p ON p.id = pi.project_id;
INSERT OR IGNORE INTO scope_locators SELECT 'mergefix_run', id, repo_id FROM mergefix_runs;
INSERT OR IGNORE INTO scope_locators SELECT 'stripe_customer', stripe_customer_id, account_id FROM billing_customers
  WHERE stripe_customer_id IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'credit_payment', payment_intent, account_id FROM credit_ledger
  WHERE kind = 'purchase' AND payment_intent IS NOT NULL;
INSERT OR IGNORE INTO scope_locators SELECT 'scratch_claim', scratch_id, account_id FROM usage_claims;
INSERT OR IGNORE INTO scope_locators SELECT 'scratch_account', account_id, account_id FROM (
  SELECT account_id FROM usage_periods UNION SELECT account_id FROM usage_gauges
  UNION SELECT account_id FROM usage_events UNION SELECT account_id FROM usage_reservations
) WHERE account_id LIKE 'scratch:%';
INSERT OR IGNORE INTO scope_locators SELECT 'editor_session', id, account_id FROM editor_container_sessions;
INSERT OR IGNORE INTO scope_locators SELECT 'job_artifact', a.id, r.repo_id FROM job_artifacts a JOIN workflow_runs r ON r.id = a.run_id;