-- pages: squashed from 0800_pages.sql, 0903_pages_domain_claims.sql.

-- Static site hosting: one site per repository, deployed into content-addressed R2 objects.
CREATE TABLE pages_sites (
  repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  source TEXT NOT NULL CHECK (source IN ('branch', 'workflow')),
  branch TEXT NOT NULL,
  folder TEXT NOT NULL DEFAULT '',
  artifact_name TEXT NOT NULL DEFAULT 'pages',
  spa INTEGER NOT NULL DEFAULT 0,
  previews INTEGER NOT NULL DEFAULT 1,
  custom_domain TEXT UNIQUE,
  custom_hostname_id TEXT,
  domain_status TEXT,
  active_deployment_id TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  domain_claimed_at INTEGER
);

CREATE TABLE pages_deployments (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  environment TEXT NOT NULL CHECK (environment IN ('production', 'preview')),
  pr_number INTEGER,
  source TEXT NOT NULL CHECK (source IN ('branch', 'artifact')),
  ref TEXT NOT NULL,
  sha TEXT NOT NULL,
  artifact_id TEXT,
  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),
  created_at INTEGER NOT NULL,
  completed_at INTEGER
);

CREATE INDEX pages_deployments_repo ON pages_deployments(repo_id, created_at DESC);

CREATE INDEX pages_deployments_preview ON pages_deployments(repo_id, pr_number, created_at DESC);