-- Commit aliases are owned per pull request: the same commit previewed in two pull requests keeps one row each.
CREATE TABLE site_pr_aliases_new (
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  label TEXT NOT NULL,
  pr_number INTEGER NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('pr', 'commit')),
  deployment_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (site_id, label, pr_number)
);
INSERT INTO site_pr_aliases_new SELECT site_id, label, pr_number, kind, deployment_id, created_at FROM site_pr_aliases;
DROP TABLE site_pr_aliases;
ALTER TABLE site_pr_aliases_new RENAME TO site_pr_aliases;
CREATE INDEX site_pr_aliases_pull ON site_pr_aliases(site_id, pr_number, kind, created_at);
CREATE INDEX site_pr_aliases_label ON site_pr_aliases(label);

-- When a custom domain was first fully verified; the unverified-claim sweep never touches a domain that has been.
ALTER TABLE sites ADD COLUMN domain_verified_at INTEGER;
ALTER TABLE pages_sites ADD COLUMN domain_verified_at INTEGER;
-- Domains active today count as verified; pending claims stay unverified so they can still expire or be taken over.
UPDATE sites SET domain_verified_at = COALESCE(domain_checked_at, updated_at)
  WHERE custom_domain IS NOT NULL AND domain_status LIKE 'active%';
UPDATE pages_sites SET domain_verified_at = updated_at WHERE custom_domain IS NOT NULL AND domain_status LIKE 'active%';

-- Leases for cron jobs that must not overlap (object GC); taken with a conditional UPDATE.
CREATE TABLE cron_locks (
  name TEXT PRIMARY KEY,
  holder TEXT,
  expires_at INTEGER NOT NULL DEFAULT 0
);