-- sites: squashed from 1300_sites.sql, 1301_sites_domain_checks.sql, 1302_scratch_git_usage.sql.

-- Dynamic sites: isolated per-repo origins on SITES_DOMAIN, access control, branch previews and scratch deploys.
CREATE TABLE sites (
  id TEXT PRIMARY KEY,
  owner_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT UNIQUE REFERENCES repos(id) ON DELETE CASCADE,
  label TEXT NOT NULL UNIQUE,
  source TEXT NOT NULL CHECK (source IN ('branch', 'workflow', 'upload')),
  branch TEXT NOT NULL DEFAULT 'main',
  folder TEXT NOT NULL DEFAULT '',
  artifact_name TEXT NOT NULL DEFAULT 'site',
  spa INTEGER NOT NULL DEFAULT 0,
  previews INTEGER NOT NULL DEFAULT 1,
  access TEXT NOT NULL DEFAULT 'public' CHECK (access IN ('public', 'password', 'gitfor', 'oidc')),
  audience TEXT NOT NULL DEFAULT 'readers' CHECK (audience IN ('readers', 'members')),
  password_hash TEXT,
  oidc_issuer TEXT,
  oidc_client_id TEXT,
  oidc_secret_ct TEXT,
  oidc_secret_iv TEXT,
  oidc_allowed TEXT,
  access_version INTEGER NOT NULL DEFAULT 1,
  env_version INTEGER NOT NULL DEFAULT 1,
  custom_domain TEXT UNIQUE,
  custom_hostname_id TEXT,
  domain_status TEXT,
  domain_claimed_at INTEGER,
  domain_token TEXT,
  active_deployment_id TEXT,
  scratch INTEGER NOT NULL DEFAULT 0,
  claim_token_hash TEXT,
  creator_ip_hash TEXT,
  expires_at INTEGER,
  disabled_reason TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  domain_checked_at INTEGER,
  push_token_hash TEXT
);

CREATE INDEX sites_scratch_expiry ON sites(scratch, expires_at);

CREATE INDEX sites_creator_ip ON sites(creator_ip_hash, created_at);

CREATE INDEX sites_push_token ON sites(push_token_hash);

CREATE TABLE site_deployments (
  id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  environment TEXT NOT NULL CHECK (environment IN ('production', 'preview')),
  label TEXT NOT NULL,
  branch TEXT,
  source TEXT NOT NULL CHECK (source IN ('branch', 'artifact', 'upload')),
  ref TEXT NOT NULL,
  sha TEXT NOT NULL,
  artifact_id TEXT,
  kind TEXT NOT NULL DEFAULT 'static' CHECK (kind IN ('static', 'worker')),
  status TEXT NOT NULL CHECK (status IN ('queued', 'building', 'active', 'failed', 'superseded')),
  file_count INTEGER NOT NULL DEFAULT 0,
  total_bytes INTEGER NOT NULL DEFAULT 0,
  error TEXT,
  creator_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  created_at INTEGER NOT NULL,
  completed_at INTEGER
);

CREATE INDEX site_deployments_site ON site_deployments(site_id, created_at DESC);

CREATE INDEX site_deployments_label ON site_deployments(label, status);

CREATE TABLE site_env (
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  secret INTEGER NOT NULL DEFAULT 0,
  value_ct TEXT NOT NULL,
  value_iv TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (site_id, name)
);

CREATE TABLE site_abuse_reports (
  id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  reporter_ip_hash TEXT NOT NULL,
  reason TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE (site_id, reporter_ip_hash)
);

CREATE TABLE site_preview_comments (
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  pr_number INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (site_id, pr_number)
);

-- One row per site per UTC day. account_id is NULL for unclaimed scratch sites (the free tier).
CREATE TABLE site_usage (
  site_id TEXT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  day TEXT NOT NULL,
  account_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  requests INTEGER NOT NULL DEFAULT 0,
  cpu_ms INTEGER NOT NULL DEFAULT 0,
  bytes_out INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (site_id, day)
);

CREATE INDEX site_usage_account ON site_usage(account_id, day);

INSERT OR IGNORE INTO accounts (id, name, kind, display_name, created_at) VALUES ('sitesscratchaccount0', 'scratch', 'org', 'Scratch sites', 0);