CREATE TABLE licenses (
  license_key TEXT PRIMARY KEY,
  plan TEXT NOT NULL,
  storage_bytes_limit INTEGER NOT NULL,
  retention_days INTEGER NOT NULL,
  max_sites INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','cancelled'))
);

CREATE TABLE buckets (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  region TEXT NOT NULL,
  site_count INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE sites (
  id TEXT PRIMARY KEY,
  license_key TEXT NOT NULL REFERENCES licenses(license_key),
  site_url TEXT NOT NULL,
  bucket_id INTEGER NOT NULL REFERENCES buckets(id),
  token_version INTEGER NOT NULL DEFAULT 1,
  storage_bytes_used INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  UNIQUE (license_key, site_url)
);

CREATE TABLE blobs (
  site_id TEXT NOT NULL REFERENCES sites(id),
  sha256 TEXT NOT NULL,
  size_bytes INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','stored')),
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  PRIMARY KEY (site_id, sha256)
);

CREATE TABLE snapshots (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  site_id TEXT NOT NULL REFERENCES sites(id),
  ts TEXT NOT NULL,
  manifest_sha256 TEXT NOT NULL,
  stats_json TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'complete' CHECK (status IN ('complete','expired')),
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);
CREATE INDEX idx_snapshots_site_ts ON snapshots (site_id, ts DESC);

CREATE TABLE usage_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  site_id TEXT NOT NULL,
  op TEXT NOT NULL,
  sha256 TEXT,
  size_bytes INTEGER,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);
CREATE INDEX idx_usage_log_site ON usage_log (site_id, created_at);