-- Webstation database schema (SQLite via node:sqlite)

CREATE TABLE IF NOT EXISTS organizations (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  plan_name     TEXT NOT NULL DEFAULT 'Team',
  price_cents   INTEGER NOT NULL DEFAULT 4900,
  site_limit    INTEGER NOT NULL DEFAULT 20,
  seat_limit    INTEGER NOT NULL DEFAULT 10,
  bandwidth_gb  INTEGER NOT NULL DEFAULT 1000,
  renews_at     TEXT NOT NULL,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS users (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  email         TEXT NOT NULL UNIQUE,
  password_hash TEXT,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- role: OWNER | ADMIN | DEVELOPER | CLIENT_VIEWER | VIEWER
-- status: ACTIVE | INVITED | SUSPENDED
-- scope: ALL_SITES | SPECIFIC
CREATE TABLE IF NOT EXISTS memberships (
  id          TEXT PRIMARY KEY,
  user_id     TEXT NOT NULL REFERENCES users(id),
  org_id      TEXT NOT NULL REFERENCES organizations(id),
  role        TEXT NOT NULL DEFAULT 'VIEWER',
  status      TEXT NOT NULL DEFAULT 'ACTIVE',
  scope       TEXT NOT NULL DEFAULT 'ALL_SITES',
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(user_id, org_id)
);

CREATE TABLE IF NOT EXISTS invite_tokens (
  id              TEXT PRIMARY KEY,
  token           TEXT NOT NULL UNIQUE,
  user_id         TEXT NOT NULL UNIQUE REFERENCES users(id),
  membership_id   TEXT NOT NULL UNIQUE REFERENCES memberships(id),
  expires_at      TEXT NOT NULL,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- status: LIVE | BUILDING | ERROR | PAUSED
CREATE TABLE IF NOT EXISTS sites (
  id                  TEXT PRIMARY KEY,
  org_id              TEXT NOT NULL REFERENCES organizations(id),
  name                TEXT NOT NULL,
  url                 TEXT NOT NULL,
  platform            TEXT NOT NULL DEFAULT 'WordPress',
  build_command       TEXT NOT NULL DEFAULT 'npm run build',
  production_branch   TEXT NOT NULL DEFAULT 'main',
  status              TEXT NOT NULL DEFAULT 'LIVE',
  uptime_pct          REAL,
  traffic_count       INTEGER,
  malware_status      TEXT NOT NULL DEFAULT 'Unknown',
  ssl_grade           TEXT,
  ssl_expires_in_days INTEGER,
  firewall_blocked    INTEGER NOT NULL DEFAULT 0,
  critical_cves       INTEGER NOT NULL DEFAULT 0,
  connected_at        TEXT NOT NULL DEFAULT (datetime('now')),
  disconnected_at     TEXT
);

CREATE TABLE IF NOT EXISTS site_access (
  id              TEXT PRIMARY KEY,
  membership_id   TEXT NOT NULL REFERENCES memberships(id),
  site_id         TEXT NOT NULL REFERENCES sites(id),
  UNIQUE(membership_id, site_id)
);

CREATE TABLE IF NOT EXISTS domains (
  id          TEXT PRIMARY KEY,
  site_id     TEXT NOT NULL REFERENCES sites(id),
  domain      TEXT NOT NULL,
  is_primary  INTEGER NOT NULL DEFAULT 1,
  status      TEXT NOT NULL DEFAULT 'Active'
);

CREATE TABLE IF NOT EXISTS deployments (
  id            TEXT PRIMARY KEY,
  site_id       TEXT NOT NULL REFERENCES sites(id),
  message       TEXT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'Success',
  duration_sec  INTEGER NOT NULL DEFAULT 30,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS customers (
  id          TEXT PRIMARY KEY,
  org_id      TEXT NOT NULL REFERENCES organizations(id),
  name        TEXT NOT NULL,
  email       TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'Active',
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS customer_sites (
  id            TEXT PRIMARY KEY,
  customer_id   TEXT NOT NULL REFERENCES customers(id),
  site_id       TEXT NOT NULL REFERENCES sites(id),
  UNIQUE(customer_id, site_id)
);

CREATE TABLE IF NOT EXISTS invoices (
  id            TEXT PRIMARY KEY,
  org_id        TEXT NOT NULL REFERENCES organizations(id),
  number        TEXT NOT NULL,
  amount_cents  INTEGER NOT NULL,
  status        TEXT NOT NULL DEFAULT 'Paid',
  issued_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS integrations (
  id          TEXT PRIMARY KEY,
  org_id      TEXT NOT NULL REFERENCES organizations(id),
  type        TEXT NOT NULL,
  config      TEXT NOT NULL DEFAULT '{}',
  status      TEXT NOT NULL DEFAULT 'Not connected',
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS audit_log (
  id                    TEXT PRIMARY KEY,
  org_id                TEXT NOT NULL REFERENCES organizations(id),
  actor_membership_id   TEXT REFERENCES memberships(id),
  action                TEXT NOT NULL,
  target_type           TEXT NOT NULL,
  target_id             TEXT,
  meta                  TEXT,
  created_at            TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_sites_org ON sites(org_id);
CREATE INDEX IF NOT EXISTS idx_memberships_org ON memberships(org_id);
CREATE INDEX IF NOT EXISTS idx_memberships_user ON memberships(user_id);
CREATE INDEX IF NOT EXISTS idx_customers_org ON customers(org_id);
CREATE INDEX IF NOT EXISTS idx_domains_site ON domains(site_id);
CREATE INDEX IF NOT EXISTS idx_deployments_site ON deployments(site_id);
CREATE INDEX IF NOT EXISTS idx_audit_org ON audit_log(org_id);