/**
 * config/db.js
 * better-sqlite3 connection + schema migration
 */

'use strict';

const path   = require('path');
const fs     = require('fs');
const Database = require('better-sqlite3');

const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', 'data', 'ncii.sqlite');

// Ensure data dir exists
const dataDir = path.dirname(DB_PATH);
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
db.pragma('synchronous = NORMAL');

// Apply schema immediately so models can prepare statements at require time.
const SCHEMA = `
CREATE TABLE IF NOT EXISTS users (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  email           TEXT    NOT NULL UNIQUE,
  password_hash   TEXT    NOT NULL,
  name            TEXT    NOT NULL,
  role            TEXT    NOT NULL DEFAULT 'user' CHECK(role IN ('user','admin','superadmin')),
  org_id          INTEGER,
  plan            TEXT    NOT NULL DEFAULT 'free',
  plan_override   TEXT,                     -- JSON override of plan limits
  plan_override_reason TEXT,
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,
  email_verified  INTEGER NOT NULL DEFAULT 0,
  verify_token    TEXT,
  verify_expires  INTEGER,
  reset_token     TEXT,
  reset_expires   INTEGER,
  failed_logins   INTEGER NOT NULL DEFAULT 0,
  locked_until    INTEGER,
  suspended       INTEGER NOT NULL DEFAULT 0,
  payment_failed  INTEGER NOT NULL DEFAULT 0,
  last_login_at   INTEGER,
  password_changed_at INTEGER,              -- bump to invalidate all other sessions
  email_changed_at INTEGER,                -- bump to invalidate email-bound state (e.g., 2FA enrollment)
  billing_interval   TEXT CHECK(billing_interval IN ('month','year')),
  seats             INTEGER NOT NULL DEFAULT 1,
  extra_scans       INTEGER NOT NULL DEFAULT 0,  -- non-expiring credits from credit packs
  extra_takedowns   INTEGER NOT NULL DEFAULT 0,  -- non-expiring credits from credit packs
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL,
  FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_users_org ON users(org_id);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

CREATE TABLE IF NOT EXISTS orgs (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT    NOT NULL,
  owner_id   INTEGER,
  plan       TEXT    NOT NULL DEFAULT 'agency',
  created_at INTEGER NOT NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS cases (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id         INTEGER NOT NULL,
  org_id          INTEGER,
  victim_name     TEXT    NOT NULL,
  reference_url   TEXT,
  context         TEXT,
  status          TEXT    NOT NULL DEFAULT 'open' CHECK(status IN ('open','investigating','resolved','closed')),
  billing_cycle_start INTEGER NOT NULL,
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL,
  closed_at       INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (org_id)  REFERENCES orgs(id)  ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_cases_user ON cases(user_id);
CREATE INDEX IF NOT EXISTS idx_cases_org  ON cases(org_id);

CREATE TABLE IF NOT EXISTS findings (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  case_id      INTEGER NOT NULL,
  user_id      INTEGER NOT NULL,
  url          TEXT    NOT NULL,
  domain       TEXT    NOT NULL,
  page_title   TEXT,
  page_snippet TEXT,
  image_hashes TEXT,        -- JSON array of pHashes
  tier         INTEGER NOT NULL,    -- 1|2|3
  tier3_reason TEXT,
  ai_call_id   INTEGER,
  confidence   REAL,
  is_ncii      INTEGER NOT NULL DEFAULT 0,
  status       TEXT NOT NULL DEFAULT 'pending'
               CHECK(status IN ('pending','confirmed','dismissed','takedown_queued','takedown_sent','removed')),
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER,
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (ai_call_id) REFERENCES ai_calls(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_findings_case ON findings(case_id);
CREATE INDEX IF NOT EXISTS idx_findings_user ON findings(user_id);
CREATE INDEX IF NOT EXISTS idx_findings_hash ON findings(image_hashes);
-- Defense-in-depth: a (case_id, url) pair should produce at most one
-- finding row. The model layer also dedups, but the index keeps the
-- invariant true even if someone bypasses the model.
CREATE UNIQUE INDEX IF NOT EXISTS uniq_findings_case_url ON findings(case_id, url);

-- AI classification of a finding — what TYPE of NCII it is.
-- One row per classification (re-classification overwrites). We never
-- store the page content itself; only the category + a short evidence
-- summary returned by the AI.
CREATE TABLE IF NOT EXISTS finding_classifications (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  finding_id      INTEGER NOT NULL,
  category        TEXT    NOT NULL,   -- name_only | unauthorized_video | unauthorized_image | unauthorized_other | not_ncii | unknown
  confidence      REAL    NOT NULL,   -- 0..1
  evidence        TEXT,               -- short quoted phrase from the page the model used to decide
  summary         TEXT,               -- 1-2 sentence plain-English explanation
  model           TEXT,               -- which model was used, e.g. "MiniMax"
  ai_call_id      INTEGER,
  triggered_by    TEXT NOT NULL DEFAULT 'manual',  -- manual | auto
  classified_at   INTEGER NOT NULL,
  FOREIGN KEY (finding_id)   REFERENCES findings(id)   ON DELETE CASCADE,
  FOREIGN KEY (ai_call_id)   REFERENCES ai_calls(id)   ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_finding_classifications_finding ON finding_classifications(finding_id);
CREATE INDEX IF NOT EXISTS idx_finding_classifications_category ON finding_classifications(category);

-- Per-takedown link health log. One row per (takedown, check) so we
-- can see the trajectory (when did the page come down? was it ever
-- intermittent? was the host blocking our probes at some point?).
CREATE TABLE IF NOT EXISTS link_health_checks (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  takedown_id   INTEGER NOT NULL,
  url           TEXT    NOT NULL,
  status        TEXT    NOT NULL,   -- live | removed | blocked | dns_fail | soft_404 | error
  http_status   INTEGER,            -- 200, 404, 410, 403, 503, etc. (NULL on DNS fail)
  reason        TEXT,               -- human explanation ("page still serves NCII", "404 from origin", etc.)
  body_size     INTEGER,            -- body bytes (0 if no body)
  body_hash     TEXT,               -- sha1 of body (so we can spot "page went away and came back" patterns)
  response_ms   INTEGER,
  checked_at    INTEGER NOT NULL,
  FOREIGN KEY (takedown_id) REFERENCES takedowns(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_link_health_takedown ON link_health_checks(takedown_id, checked_at DESC);
CREATE INDEX IF NOT EXISTS idx_link_health_status  ON link_health_checks(status);

-- Auto-monitor list: URLs the operator wants re-checked on a regular
-- cadence even if there's no takedown for them. Used for cross-case
-- "is this still leaked?" sweeps.
CREATE TABLE IF NOT EXISTS monitored_urls (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  url         TEXT    NOT NULL,
  domain      TEXT    NOT NULL,
  case_id     INTEGER,            -- optional; NULL = global watch
  reason      TEXT,               -- "image hash from case #3", "NCMEC tip", etc.
  added_by    INTEGER,            -- user_id, NULL for system
  next_check  INTEGER NOT NULL,   -- timestamp
  check_count INTEGER NOT NULL DEFAULT 0,
  last_status TEXT,               -- live | removed | blocked | dns_fail
  last_checked INTEGER,
  created_at  INTEGER NOT NULL,
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE SET NULL,
  FOREIGN KEY (added_by) REFERENCES users(id)  ON DELETE SET NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS uniq_monitored_url ON monitored_urls(url);
CREATE INDEX IF NOT EXISTS idx_monitored_due ON monitored_urls(next_check);

-- Webhooks — pure HTTP POST to the operator's endpoint. No email
-- provider required. Used for Slack/Discord/Teams/PagerDuty/etc.
-- payloads. Events: takedown.sent, takedown.escalated, takedown.removed,
-- case.opened, case.closed, finding.classified, link_health.removed.
CREATE TABLE IF NOT EXISTS webhooks (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  url           TEXT    NOT NULL,
  secret        TEXT,                    -- HMAC-SHA256 of payload (Slack/Discord style)
  event_filter  TEXT,                    -- comma-separated list of event names to deliver; NULL = all
  active        INTEGER NOT NULL DEFAULT 1,
  owner_id      INTEGER,                -- who created it (user_id)
  last_status   INTEGER,                -- HTTP status of last delivery
  last_error    TEXT,
  last_fired_at INTEGER,
  failures      INTEGER NOT NULL DEFAULT 0,
  created_at    INTEGER NOT NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_webhooks_active ON webhooks(active);

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  webhook_id    INTEGER NOT NULL,
  event         TEXT    NOT NULL,
  payload       TEXT    NOT NULL,        -- JSON
  status        INTEGER,                -- HTTP status (NULL = not yet tried)
  response      TEXT,                    -- body (first 500 chars)
  attempt       INTEGER NOT NULL DEFAULT 1,
  delivered_at  INTEGER NOT NULL,
  FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_deliveries_webhook ON webhook_deliveries(webhook_id, delivered_at DESC);

-- Stripe webhook replay protection. Stores processed event ids so
-- duplicate deliveries are ignored.
CREATE TABLE IF NOT EXISTS webhook_events (
  id          TEXT PRIMARY KEY,
  source      TEXT NOT NULL,
  received_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_webhook_age ON webhook_events(received_at);

-- Email digest schedule. Per-recipient: "send the weekly digest to
-- this address at this hour on this day-of-week". One row per
-- recipient. Per-event email is sent immediately via the email
-- service; this is the *batched* weekly summary.
CREATE TABLE IF NOT EXISTS email_digest_subscriptions (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       INTEGER NOT NULL,
  email         TEXT    NOT NULL,        -- override address (user.email by default)
  cadence       TEXT    NOT NULL DEFAULT 'weekly',  -- weekly | daily
  day_of_week   INTEGER NOT NULL DEFAULT 1,           -- 0=Sun ... 1=Mon ... 6=Sat
  hour_utc      INTEGER NOT NULL DEFAULT 9,           -- 0..23
  active        INTEGER NOT NULL DEFAULT 1,
  last_sent_at  INTEGER,
  created_at    INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_digest_active ON email_digest_subscriptions(active, day_of_week, hour_utc);

-- Email transport config — pluggable so the operator can run SMTP
-- direct (no third party), Resend, SendGrid, etc. without code changes.
CREATE TABLE IF NOT EXISTS email_transport (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT    NOT NULL UNIQUE,  -- 'resend' | 'smtp' | 'sendgrid' | 'mailgun' | 'postmark'
  active      INTEGER NOT NULL DEFAULT 0,
  config_json TEXT,                     -- per-transport config (api_key, host, port, user, pass, from)
  is_fallback INTEGER NOT NULL DEFAULT 0,
  updated_at  INTEGER NOT NULL,
  updated_by  INTEGER
);

CREATE TABLE IF NOT EXISTS scans (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  case_id     INTEGER NOT NULL,
  user_id     INTEGER NOT NULL,
  query       TEXT    NOT NULL,         -- search query used
  engine      TEXT    NOT NULL DEFAULT 'google',
  result_count INTEGER NOT NULL DEFAULT 0,
  tier1_hits  INTEGER NOT NULL DEFAULT 0,
  tier2_hits  INTEGER NOT NULL DEFAULT 0,
  tier3_hits  INTEGER NOT NULL DEFAULT 0,
  duration_ms INTEGER,
  created_at  INTEGER NOT NULL,
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_scans_user_month ON scans(user_id, created_at);

CREATE TABLE IF NOT EXISTS takedowns (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  case_id       INTEGER NOT NULL,
  finding_id    INTEGER,
  user_id       INTEGER NOT NULL,
  url           TEXT    NOT NULL,
  domain        TEXT    NOT NULL,
  contact_email TEXT,
  email_subject TEXT,
  email_body    TEXT,
  draft_method  TEXT NOT NULL DEFAULT 'template' CHECK(draft_method IN ('template','ai','no-direct-email','chain-only')),
  status        TEXT NOT NULL DEFAULT 'queued'
                CHECK(status IN ('queued','sent','opened','responded','removed','rejected','failed','manual_required','manual-required','production_pending','chain_only')),
  sent_at       INTEGER,
  opened_at     INTEGER,
  responded_at  INTEGER,
  resolved_at   INTEGER,
  error         TEXT,
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER,
  discovery_json  TEXT,
  escalation_json TEXT,
  escalated_at    INTEGER,
  next_escalate_at INTEGER,
  FOREIGN KEY (case_id)    REFERENCES cases(id)    ON DELETE CASCADE,
  FOREIGN KEY (finding_id) REFERENCES findings(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_takedowns_user ON takedowns(user_id);
CREATE INDEX IF NOT EXISTS idx_takedowns_case ON takedowns(case_id);

CREATE TABLE IF NOT EXISTS api_keys (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  service      TEXT    NOT NULL CHECK(service IN ('scrape_do','MiniMax','resend','stripe')),
  key_value    TEXT    NOT NULL,
  label        TEXT    NOT NULL,
  is_active    INTEGER NOT NULL DEFAULT 1,
  calls_today  INTEGER NOT NULL DEFAULT 0,
  calls_total  INTEGER NOT NULL DEFAULT 0,
  quota_limit  INTEGER NOT NULL DEFAULT 1000,
  exhausted_until INTEGER,         -- timestamp; treat as disabled until then
  last_used_at INTEGER,
  last_error   TEXT,
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_api_keys_service ON api_keys(service, is_active);

-- Agency REST API bearer tokens. Plaintext tokens are shown once; only
-- SHA-256 hashes are stored.
CREATE TABLE IF NOT EXISTS api_tokens (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id      INTEGER NOT NULL,
  token_hash   TEXT    NOT NULL UNIQUE,
  label        TEXT,
  last4        TEXT,
  created_at   INTEGER NOT NULL,
  last_used_at INTEGER,
  revoked_at   INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_api_tokens_user ON api_tokens(user_id);

CREATE TABLE IF NOT EXISTS ai_calls (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  service       TEXT    NOT NULL,
  key_id        INTEGER,
  endpoint      TEXT    NOT NULL,
  tokens_in     INTEGER,
  tokens_out    INTEGER,
  cost_estimate REAL,
  tier3_reason  TEXT,
  success       INTEGER NOT NULL DEFAULT 0,
  response_ms   INTEGER,
  error         TEXT,
  created_at    INTEGER NOT NULL,
  FOREIGN KEY (key_id) REFERENCES api_keys(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_ai_calls_created ON ai_calls(created_at);

CREATE TABLE IF NOT EXISTS usage_log (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       INTEGER NOT NULL,
  resource      TEXT    NOT NULL,   -- 'scan' | 'takedown' | 'case'
  period_start  INTEGER NOT NULL,
  count         INTEGER NOT NULL DEFAULT 0,
  UNIQUE (user_id, resource, period_start),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_usage_user_period ON usage_log(user_id, period_start);

CREATE TABLE IF NOT EXISTS audit_log (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  actor_id   INTEGER,
  actor_email TEXT,
  action     TEXT    NOT NULL,
  target     TEXT,
  meta       TEXT,        -- JSON
  ip         TEXT,
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at);

CREATE TABLE IF NOT EXISTS settings (
  key        TEXT PRIMARY KEY,
  value      TEXT,
  updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS email_templates (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT    NOT NULL UNIQUE,
  subject    TEXT NOT NULL,
  body       TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS keyword_lists (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL UNIQUE,
  words      TEXT NOT NULL,        -- JSON array
  updated_at INTEGER NOT NULL
);

-- Platform contacts table: replaces the hard-coded KNOWN map in
-- services/platformContacts.js. Populated at boot from the curated
-- fallback list, then self-updated as takedowns are sent / responded
-- / removed / failed. The system learns its own contact map from
-- real-world outcomes; admins can also override via /admin/contacts.
--
-- confidence: 0.0 (tentative, from scrape heuristics) to 1.0
--   (verified by an observed takedown outcome OR by admin override).
-- source:
--   'curated'         — hand-written fallback list
--   'security_txt'    — observed in the site's security.txt
--   'sitemap'         — observed in sitemap / footer
--   'whois'           — observed in WHOIS / RDAP
--   'admin_override'  — an operator set this manually
--   'observed_success'— a takedown to this address was sent AND the
--                       content was later marked removed/responded
--   'attempted'       — a takedown to this address was sent; no
--                       success signal yet
--   'failed'          — a send to this address was rejected by the
--                       receiving server (hard-bounce, 4xx)
CREATE TABLE IF NOT EXISTS platform_contacts (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  domain       TEXT    NOT NULL,
  contact      TEXT,                  -- email; NULL if form/HackerOne only
  form_url     TEXT,
  security_url TEXT,
  name         TEXT,
  source       TEXT    NOT NULL,
  confidence   REAL    NOT NULL DEFAULT 0.5,
  verified_at  INTEGER,
  notes        TEXT,
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL,
  UNIQUE(domain, contact, form_url, security_url)
);
CREATE INDEX IF NOT EXISTS idx_pc_domain   ON platform_contacts(domain);
CREATE INDEX IF NOT EXISTS idx_pc_source   ON platform_contacts(source);
CREATE INDEX IF NOT EXISTS idx_pc_verified ON platform_contacts(verified_at);

-- Async job queue. Takedowns are enqueued here and the worker processes
-- them in the background, so the HTTP request returns instantly with a
-- job_id. Status polling tells the UI what happened.
CREATE TABLE IF NOT EXISTS takedown_jobs (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  case_id         INTEGER NOT NULL,
  user_id         INTEGER NOT NULL,
  url             TEXT    NOT NULL,
  domain          TEXT    NOT NULL,
  finding_id      INTEGER,
  draft_method    TEXT NOT NULL DEFAULT 'auto',
  -- queued | discovering | drafting | dispatching | sent | manual_required | failed | escalated
  status          TEXT NOT NULL DEFAULT 'queued',
  progress        TEXT,                  -- human-readable current step
  attempts        INTEGER NOT NULL DEFAULT 0,
  max_attempts    INTEGER NOT NULL DEFAULT 5,
  next_run_at     INTEGER NOT NULL,     -- earliest the worker may pick this up
  last_error      TEXT,
  takedown_id     INTEGER,              -- set once the job resolves into a real takedowns row
  discovery_json  TEXT,                 -- persisted discovery result
  escalation_json TEXT,                 -- persisted escalation chain (incl. RDAP-resolved registrar)
  result_json     TEXT,                 -- whatever we return to the client on poll
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL,
  FOREIGN KEY (case_id)    REFERENCES cases(id)    ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE,
  FOREIGN KEY (finding_id) REFERENCES findings(id) ON DELETE SET NULL,
  FOREIGN KEY (takedown_id)REFERENCES takedowns(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_jobs_pickup ON takedown_jobs(status, next_run_at);
CREATE INDEX IF NOT EXISTS idx_jobs_user   ON takedown_jobs(user_id, created_at);

-- Per-IP login attempt tracking. Resets after 1 hour.
CREATE TABLE IF NOT EXISTS login_attempts (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  ip          TEXT    NOT NULL,
  email       TEXT,
  succeeded   INTEGER NOT NULL DEFAULT 0,
  attempted_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_login_ip_time ON login_attempts(ip, attempted_at);

-- Known IPs per user. If a user logs in from a new IP, we email them a
-- "new device sign-in" notice.
CREATE TABLE IF NOT EXISTS user_known_ips (
  user_id     INTEGER NOT NULL,
  ip          TEXT    NOT NULL,
  first_seen  INTEGER NOT NULL,
  last_seen   INTEGER NOT NULL,
  PRIMARY KEY (user_id, ip),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- TOTP 2FA secrets (per-user, encrypted at rest in production with a key
-- from env). For the local dev build we store the base32 secret directly.
CREATE TABLE IF NOT EXISTS user_2fa (
  user_id     INTEGER PRIMARY KEY,
  secret      TEXT    NOT NULL,
  enabled     INTEGER NOT NULL DEFAULT 0,
  backup_codes TEXT,                       -- JSON array of single-use recovery codes (hashed)
  enrolled_at INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Operator alerts. Internal-only — never exposed to end users. The user-
-- facing surface is a short generic message + a short ref (this row's id)
-- that a support operator can look up. Severity: info | warn | error | critical.
CREATE TABLE IF NOT EXISTS alerts (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  ref          TEXT    NOT NULL UNIQUE,    -- short hex id for users to quote
  severity     TEXT    NOT NULL CHECK(severity IN ('info','warn','error','critical')),
  category     TEXT    NOT NULL,           -- 'api_quota' | 'api_error' | 'auth_lockout' | 'dispatch_fail' | 'webhook' | 'other'
  service      TEXT,                       -- 'resend' | 'scrape_do' | 'MiniMax' | 'stripe' | null
  actor_id     INTEGER,                   -- user who triggered (if any)
  title        TEXT    NOT NULL,           -- one-line summary for the admin UI
  detail       TEXT,                       -- full JSON/structured details (admin-only)
  ref_url      TEXT,                       -- pointer to related row (e.g. takedown:42)
  acknowledged INTEGER NOT NULL DEFAULT 0,
  ack_by       INTEGER,
  ack_at       INTEGER,
  email_sent   INTEGER NOT NULL DEFAULT 0, -- did we already email about this?
  dedupe_key   TEXT,                       -- (service, category, ref_url) for de-dupe
  created_at   INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_alerts_created   ON alerts(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_alerts_dedupe    ON alerts(dedupe_key);
CREATE INDEX IF NOT EXISTS idx_alerts_unack     ON alerts(acknowledged, created_at DESC);

-- Per-instance alert preferences (who gets emailed, threshold severities).
CREATE TABLE IF NOT EXISTS alert_prefs (
  key   TEXT PRIMARY KEY,
  value TEXT,
  updated_at INTEGER
);

-- Deep discovery scans. One row per scan, tracks which Google queries
-- were run, which sites passed validation, and which were promoted to
-- production_pending takedowns. This is the operator's audit record.
CREATE TABLE IF NOT EXISTS case_discovery_scans (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  case_id         INTEGER NOT NULL,
  user_id         INTEGER NOT NULL,        -- who kicked off the scan
  status          TEXT NOT NULL DEFAULT 'queued',  -- queued | running | done | failed | cancelled
  queries         TEXT,                    -- JSON array of {q, name_variant, keyword} actually run
  candidates      INTEGER NOT NULL DEFAULT 0,    -- unique candidate URLs
  validated       INTEGER NOT NULL DEFAULT 0,    -- passed HTTP + name + image probe
  false_positives INTEGER NOT NULL DEFAULT 0,    -- filtered out (404, login wall, tag cloud, etc.)
  no_contact      INTEGER NOT NULL DEFAULT 0,    -- no abuse email reachable
  queued_pp       INTEGER NOT NULL DEFAULT 0,    -- promoted to production_pending
  deduped         INTEGER NOT NULL DEFAULT 0,    -- merged into an existing row (same url / same recipient)
  progress        TEXT,                    -- human-readable current step
  started_at      INTEGER,
  finished_at     INTEGER,
  updated_at      INTEGER,                 -- last progress update (worker writes frequently)
  created_at      INTEGER NOT NULL,
  FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_discovery_scans_case ON case_discovery_scans(case_id, created_at DESC);

-- Blog posts — public-facing SEO content. Stored as pre-rendered HTML so
-- routes can serve them without a template engine. Each post has its own
-- meta_description, JSON-LD-ready FAQ, hero image, and slug URL.
--
-- Slugs are SEO-optimized kebab-case ("how-dmca-takedown-notices-work") and
-- stable across edits — never reuse a slug for different content.
CREATE TABLE IF NOT EXISTS blog_posts (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  slug              TEXT    NOT NULL UNIQUE,
  title             TEXT    NOT NULL,
  subtitle          TEXT,
  excerpt           TEXT    NOT NULL,            -- 1-2 sentences for cards + meta fallback
  body_html         TEXT    NOT NULL,            -- article HTML (server-rendered as-is)
  category          TEXT    NOT NULL,            -- 'Safety Guide' | 'Regulatory' | 'Legal' | 'Platforms' | 'Advocacy' | 'Creators' | 'Technical'
  tags              TEXT    NOT NULL DEFAULT '[]',  -- JSON array of strings
  author_name       TEXT    NOT NULL DEFAULT 'Shield Team',
  author_role       TEXT,
  author_bio        TEXT,
  hero_image_url    TEXT    NOT NULL,            -- 1200x630 OG/hero image
  og_image_url      TEXT    NOT NULL,
  reading_time_min  INTEGER NOT NULL DEFAULT 5,
  is_published      INTEGER NOT NULL DEFAULT 1,
  published_at      INTEGER NOT NULL,
  updated_at        INTEGER NOT NULL,
  meta_description  TEXT    NOT NULL,            -- 140-160 chars; used for <meta name=description> + OG
  meta_keywords     TEXT,                        -- comma-separated; used for <meta name=keywords>
  related_slugs     TEXT    NOT NULL DEFAULT '[]',  -- JSON array of slugs to show as "Related"
  faq_json          TEXT,                        -- JSON array of {q, a} for FAQPage schema
  sort_order        INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_blog_posts_published ON blog_posts(is_published, published_at DESC);
CREATE INDEX IF NOT EXISTS idx_blog_posts_sort     ON blog_posts(is_published, sort_order ASC, published_at DESC);

-- Operator notes on takedowns. The audit_log table is system-only
-- (every row is a system event). The notes table is for the operator
-- to record things like "called platform rep, promised takedown by
-- Friday" — six months later when the lawyer asks what happened,
-- the note is right there next to the takedown row. Superadmin-only
-- write; superadmin + owner (the original sender) read.
CREATE TABLE IF NOT EXISTS takedown_notes (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  takedown_id INTEGER NOT NULL,
  user_id     INTEGER NOT NULL,    -- who wrote the note
  body        TEXT    NOT NULL,    -- markdown-light, plain text
  created_at  INTEGER NOT NULL,
  updated_at  INTEGER NOT NULL,
  FOREIGN KEY (takedown_id) REFERENCES takedowns(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)     REFERENCES users(id)     ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_takedown_notes_takedown ON takedown_notes(takedown_id, created_at DESC);

-- "Remember this device" — a per-device cookie the operator can opt
-- into after 2FA verify so they don't have to re-enter the 6-digit
-- code every time the session is wiped (e.g. after a deploy, since
-- data/sessions.sqlite is excluded from the deploy zip). Token is
-- stored as a sha256 hash; the plaintext only lives in the cookie.
-- Expires 30 days after issue. Revoked by DELETE FROM trusted_devices
-- (cascades from users on account deletion).
CREATE TABLE IF NOT EXISTS trusted_devices (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       INTEGER NOT NULL,
  token_hash    TEXT    NOT NULL UNIQUE,   -- sha256 of the random token
  user_agent    TEXT,                       -- first UA seen
  ip_first_seen TEXT,                       -- first IP
  last_used_at  INTEGER,
  created_at    INTEGER NOT NULL,
  expires_at    INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_trusted_devices_user ON trusted_devices(user_id, expires_at);

-- Contact messages (admin → support). UUID PK so IDs are non-guessable;
-- all reads are scoped to author_user_id server-side to prevent IDOR.
CREATE TABLE IF NOT EXISTS contact_messages (
  id              TEXT    PRIMARY KEY,                  -- uuid v4
  author_user_id  INTEGER NOT NULL,                     -- server-derived, never trusted from client
  subject         TEXT    NOT NULL,
  body            TEXT    NOT NULL,
  priority        TEXT    NOT NULL DEFAULT 'normal' CHECK(priority IN ('low','normal','high')),
  status          TEXT    NOT NULL DEFAULT 'open' CHECK(status IN ('open','replied','closed')),
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL,
  FOREIGN KEY (author_user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_contact_messages_author ON contact_messages(author_user_id, created_at DESC);
`;

db.exec(SCHEMA);

// ---- Inline column-add migrations ----
// These run during require() (before any model prepares statements that
// might reference the new columns). For existing databases, ALTER TABLE
// is idempotent at the application level because we check PRAGMA first.
(function inlineMigrations() {
  const userCols = db.prepare(`PRAGMA table_info(users)`).all();
  const ensure = (col, def) => {
    if (!userCols.find(c => c.name === col)) {
      db.exec(`ALTER TABLE users ADD COLUMN ${col} ${def}`);
      console.log(`[db] inline-migration: added users.${col}`);
    }
  };
  ensure('password_changed_at', 'INTEGER');
  ensure('email_changed_at',    'INTEGER');
  // Stripe billing metadata — used for plan limit resolution and
  // the dashboard's "how am I paying" display.
  ensure('billing_interval',   "TEXT CHECK(billing_interval IN ('month','year'))");
  ensure('seats',               'INTEGER NOT NULL DEFAULT 1');
  // Credit packs grant non-expiring usage credits that are additive
  // to the user's monthly plan quota.
  ensure('extra_scans',          'INTEGER NOT NULL DEFAULT 0');
  ensure('extra_takedowns',      'INTEGER NOT NULL DEFAULT 0');
})();

function init() {
  // ---- Lightweight migrations (idempotent) ----
  // Each entry: a column we need to exist. Missing columns get added.
  const migrations = [
    { table: 'case_discovery_scans', col: 'updated_at',         def: 'INTEGER' },
    { table: 'case_discovery_scans', col: 'deduped',            def: 'INTEGER NOT NULL DEFAULT 0' },
    { table: 'takedowns',            col: 'discovery_json',     def: 'TEXT' },
    { table: 'takedowns',            col: 'escalation_json',    def: 'TEXT' },
    { table: 'takedowns',            col: 'escalated_at',       def: 'INTEGER' },
    { table: 'takedowns',            col: 'next_escalate_at',   def: 'INTEGER' },
    // Test data flag. When set, background workers skip the row and
    // the admin dashboard can filter it out. Auto-flagged by
    // scripts/flag-test-data.js (run once after deploy).
    { table: 'users',     col: 'is_test_data', def: 'INTEGER NOT NULL DEFAULT 0' },
    { table: 'cases',     col: 'is_test_data', def: 'INTEGER NOT NULL DEFAULT 0' },
    { table: 'takedowns', col: 'is_test_data', def: 'INTEGER NOT NULL DEFAULT 0' },
  ];
  for (const m of migrations) {
    const cols = db.prepare(`PRAGMA table_info(${m.table})`).all();
    if (cols.length && !cols.find(c => c.name === m.col)) {
      db.exec(`ALTER TABLE ${m.table} ADD COLUMN ${m.col} ${m.def}`);
      console.log(`[db] migration: added ${m.table}.${m.col}`);
    }
  }
  // Indexes for the test-data column (idempotent).
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_users_test      ON users(is_test_data);
    CREATE INDEX IF NOT EXISTS idx_cases_test      ON cases(is_test_data);
    CREATE INDEX IF NOT EXISTS idx_takedowns_test  ON takedowns(is_test_data);
  `);
  // Note: password_changed_at and email_changed_at are added in the
  // inline-migration IIFE above (runs at require() time, before any
  // model prepares statements). No need to add them again here.
  // CHECK-constraint widening for `takedowns` (SQLite can't ALTER a
  // CHECK, so we detect the old shape and rebuild the table in place).
  // The new shape accepts 'chain-only' and 'chain_only' which are
  // emitted by the deep-discovery pipeline when no abuse email is
  // reachable but a registrar/ICANN escalation chain is.
  const checkRow = db.prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='takedowns'`).get();
  if (checkRow && checkRow.sql && !/chain-only/.test(checkRow.sql)) {
    console.log('[db] migration: rebuilding takedowns to widen CHECK constraints');
    // Use the same column shape as the existing table (preserves all
    // legacy rows), only swap the CHECK clauses.
    const cols = db.prepare(`PRAGMA table_info(takedowns)`).all().map(c => c.name);
    const colList = cols.join(', ');
    const newSql = `CREATE TABLE takedowns_new (
      id            INTEGER PRIMARY KEY AUTOINCREMENT,
      case_id       INTEGER NOT NULL,
      finding_id    INTEGER,
      user_id       INTEGER NOT NULL,
      url           TEXT    NOT NULL,
      domain        TEXT    NOT NULL,
      contact_email TEXT,
      email_subject TEXT,
      email_body    TEXT,
      draft_method  TEXT NOT NULL DEFAULT 'template' CHECK(draft_method IN ('template','ai','no-direct-email','chain-only')),
      status        TEXT NOT NULL DEFAULT 'queued'
                    CHECK(status IN ('queued','sent','opened','responded','removed','rejected','failed','manual_required','manual-required','production_pending','chain_only')),
      sent_at       INTEGER,
      opened_at     INTEGER,
      responded_at  INTEGER,
      resolved_at   INTEGER,
      error         TEXT,
      created_at    INTEGER NOT NULL,
      updated_at    INTEGER, discovery_json TEXT, escalation_json TEXT, escalated_at INTEGER, next_escalate_at INTEGER,
      FOREIGN KEY (case_id)    REFERENCES cases(id)    ON DELETE CASCADE,
      FOREIGN KEY (finding_id) REFERENCES findings(id) ON DELETE SET NULL,
      FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
    )`;
    db.exec(`
      BEGIN;
      ${newSql};
      INSERT INTO takedowns_new (${colList}) SELECT ${colList} FROM takedowns;
      DROP TABLE takedowns;
      ALTER TABLE takedowns_new RENAME TO takedowns;
      COMMIT;
    `);
    db.exec(`
      CREATE INDEX IF NOT EXISTS idx_takedowns_user ON takedowns(user_id);
      CREATE INDEX IF NOT EXISTS idx_takedowns_case ON takedowns(case_id);
    `);
  }
  console.log('[db] schema ready at', DB_PATH);
}

function resetDailyKeyCounters() {
  const now = Date.now();
  const stmt = db.prepare(`
    UPDATE api_keys
       SET calls_today = 0,
           exhausted_until = NULL,
           updated_at = ?
     WHERE service IN ('scrape_do','MiniMax','resend')
  `);
  stmt.run(now);
}

module.exports = { db, init, resetDailyKeyCounters, DB_PATH };
