-- Scratch sites backed by a git repository, and per-site usage metering.

-- Unclaimed scratch repositories live under this reserved org; nobody is a member, so only push tokens reach them.
INSERT OR IGNORE INTO accounts (id, name, kind, display_name, created_at)
VALUES ('sitesscratchaccount0', 'scratch', 'org', 'Scratch sites', 0);

ALTER TABLE sites ADD COLUMN push_token_hash TEXT;
CREATE INDEX sites_push_token ON sites(push_token_hash);

-- 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);