-- dotnews-paas shared database (/srv/paas/paas.db)
-- Written by: panel (projects, webhook deploys) and bin/paas (deployment records).
-- Applied idempotently by bootstrap.sh and on panel start.
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS projects (
  name           TEXT PRIMARY KEY,              -- equals the paas app name
  repo_url       TEXT NOT NULL,
  branch         TEXT NOT NULL DEFAULT 'main',
  build_cmd      TEXT NOT NULL DEFAULT 'npm ci && npm run build',
  output_dir     TEXT NOT NULL DEFAULT '.output',
  webhook_secret TEXT NOT NULL,
  variants       TEXT,                           -- matrix projects: JSON [{app, prep_cmd}]
  group_name     TEXT,                           -- dashboard grouping (free text)
  owner_user_id  INTEGER,                        -- users.id (project owner)
  git_account_id INTEGER,                        -- git_accounts.id (NULL = manual repo URL)
  repo_full      TEXT,                           -- provider repo path, e.g. owner/name
  runtime        TEXT NOT NULL DEFAULT 'node',   -- node | laravel (== registry RUNTIME, fixed at creation)
  created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Panel users. The first admin is seeded from ADMIN_PASSWORD_HASH (env) as
-- username 'admin' when the table is empty. password_hash: scrypt:<salt>:<key>.
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'user',    -- admin | user
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Saved git provider accounts (GitHub / GitLab personal access tokens) for
-- repo browsing + automatic deploy-key/webhook provisioning at project
-- creation. Tokens are stored as-is; the DB file is 0600 on the server.
CREATE TABLE IF NOT EXISTS git_accounts (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_user_id    INTEGER,                     -- users.id — accounts are strictly personal
  provider         TEXT NOT NULL,               -- github | gitlab
  label            TEXT NOT NULL,
  token            TEXT NOT NULL,
  api_base         TEXT,                        -- for self-hosted GitLab; NULL = provider default
  auth_type        TEXT NOT NULL DEFAULT 'pat', -- pat | oauth
  refresh_token    TEXT,                        -- gitlab oauth only
  token_expires_at INTEGER,                     -- epoch ms, gitlab oauth only
  created_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

-- OAuth provider apps (admin-configured once; client IDs only — GitHub uses
-- the Device Flow, GitLab the PKCE web flow, so no client secrets needed).
CREATE TABLE IF NOT EXISTS oauth_apps (
  provider      TEXT PRIMARY KEY,               -- github | gitlab
  client_id     TEXT NOT NULL,
  client_secret TEXT,                           -- github web flow only (redirect connect); NULL = device flow
  api_base      TEXT                            -- self-hosted GitLab base URL (https://git.example.com)
);

-- Explicitly managed dashboard groups, per user (projects.group_name
-- references these by value within the same owner's scope).
CREATE TABLE IF NOT EXISTS groups (
  owner_user_id INTEGER NOT NULL,
  name          TEXT NOT NULL,
  PRIMARY KEY (owner_user_id, name)
);

CREATE TABLE IF NOT EXISTS deployments (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  app         TEXT NOT NULL,
  source      TEXT NOT NULL DEFAULT 'manual',   -- webhook | jenkins | gitlab-ci | manual | panel
  commit_sha  TEXT,
  commit_msg  TEXT,                             -- first line of the deployed commit message
  status      TEXT NOT NULL DEFAULT 'queued',   -- queued | cloning | building | deploying | live | failed
  step        TEXT,                             -- free-text current step for the dashboard
  log_path    TEXT,
  release_dir TEXT,
  started_at  TEXT NOT NULL DEFAULT (datetime('now')),
  finished_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_deployments_app ON deployments (app, id DESC);

-- Daily per-app traffic rollup, written by bin/paas-traffic-rollup (systemd
-- timer) from Caddy access logs; read by the panel. day = YYYY-MM-DD (UTC).
-- Permanent per-app monthly ledger: each month is archived ONCE (INSERT OR
-- IGNORE) by the rollup after the month has fully ended (+1 day grace), and
-- never modified again — surviving any future pruning of traffic_daily.
CREATE TABLE IF NOT EXISTS traffic_monthly (
  app          TEXT NOT NULL,
  month        TEXT NOT NULL,                   -- YYYY-MM (UTC)
  requests     INTEGER NOT NULL DEFAULT 0,
  bytes        INTEGER NOT NULL DEFAULT 0,
  hit          INTEGER NOT NULL DEFAULT 0,
  miss         INTEGER NOT NULL DEFAULT 0,
  s2xx         INTEGER NOT NULL DEFAULT 0,
  s4xx         INTEGER NOT NULL DEFAULT 0,
  s5xx         INTEGER NOT NULL DEFAULT 0,
  bytes_cache  INTEGER NOT NULL DEFAULT 0,       -- bytes served by Varnish (X-Cache: HIT)
  bytes_origin INTEGER NOT NULL DEFAULT 0,       -- bytes served by app/static
  finalized_at TEXT NOT NULL,
  PRIMARY KEY (app, month)
);

CREATE TABLE IF NOT EXISTS traffic_daily (
  app      TEXT NOT NULL,
  day      TEXT NOT NULL,
  requests INTEGER NOT NULL DEFAULT 0,
  bytes    INTEGER NOT NULL DEFAULT 0,           -- response bytes (bandwidth out)
  hit      INTEGER NOT NULL DEFAULT 0,           -- Varnish X-Cache: HIT
  miss     INTEGER NOT NULL DEFAULT 0,
  s2xx     INTEGER NOT NULL DEFAULT 0,
  s4xx     INTEGER NOT NULL DEFAULT 0,
  s5xx     INTEGER NOT NULL DEFAULT 0,
  bytes_cache  INTEGER NOT NULL DEFAULT 0,
  bytes_origin INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (app, day)
);

-- Per-article read counts, written by bin/paas-traffic-rollup (same pass as
-- traffic_daily — no extra log I/O) from Caddy access logs, but ONLY for
-- apps that ship a route-manifest.json (dotnews-an-tier-a based apps; see
-- that repo's scripts/generate-route-manifest.js) — an app without one is
-- never guessed at. Pruned to the trailing 35 days by the same rollup: only
-- a 7-day window is ever queried, so this table stays small regardless of
-- total article catalog size. day = YYYY-MM-DD (UTC).
CREATE TABLE IF NOT EXISTS article_clicks_daily (
  app    TEXT NOT NULL,
  slug   TEXT NOT NULL,
  day    TEXT NOT NULL,
  clicks INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (app, slug, day)
);

-- Watermark of the last click total pushed to each app's Strapi, written by
-- bin/paas-clicks-writeback (systemd timer, hourly). Lets the writeback skip
-- slugs whose trailing-window total hasn't changed since the last run,
-- instead of re-pushing every slug every hour.
CREATE TABLE IF NOT EXISTS article_clicks_synced (
  app        TEXT NOT NULL,
  slug       TEXT NOT NULL,
  last_value INTEGER NOT NULL DEFAULT 0,
  synced_at  TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (app, slug)
);

-- Static-page clicks (paths from the app's route-manifest exact list —
-- landing pages, guide articles, /broker, …), same shape and 35-day
-- lifecycle as article_clicks_daily. Written by bin/paas-traffic-rollup.
CREATE TABLE IF NOT EXISTS page_clicks_daily (
  app    TEXT NOT NULL,
  path   TEXT NOT NULL,
  day    TEXT NOT NULL,
  clicks INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (app, path, day)
);

-- Unified 12-month click history for articles AND static pages (articles
-- keyed as '/' || slug). Incremented in the same rollup pass as the daily
-- tables; kept 13 months — survives the 35-day daily prune and feeds the
-- admin Klick Report's monthly totals. month = YYYY-MM (UTC).
CREATE TABLE IF NOT EXISTS clicks_monthly (
  app    TEXT NOT NULL,
  path   TEXT NOT NULL,
  month  TEXT NOT NULL,
  clicks INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (app, path, month)
);

-- Watermark for the click-stats sync (bin/paas-clicks-writeback → the app's
-- /api/_hooks/clicks `stats` payload → Strapi click-stat collection). hash =
-- JSON of [clicks7, clicks30, months]; only changed paths are re-pushed.
CREATE TABLE IF NOT EXISTS click_stats_synced (
  app       TEXT NOT NULL,
  path      TEXT NOT NULL,
  hash      TEXT NOT NULL DEFAULT '',
  synced_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (app, path)
);