-- ============================================================
-- deep_agent 2.0 — SQLite 开发/测试档建表脚本
-- ============================================================
-- 与 mysql_schema.sql 结构完全等价，仅类型与索引语法按 SQLite 调整。
-- 用途：无 MySQL 可用时的本地开发与自动化测试（DB_DIALECT=sqlite）。
-- 生产/联调一律走 MySQL。
-- ============================================================

CREATE TABLE IF NOT EXISTS sessions (
  id          TEXT PRIMARY KEY,
  title       TEXT NOT NULL DEFAULT '',
  user_name   TEXT NOT NULL DEFAULT 'default',
  mode        TEXT NOT NULL DEFAULT 'full',
  pinned      INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_updated ON sessions (user_name, updated_at);
CREATE INDEX IF NOT EXISTS idx_sessions_title ON sessions (title);

CREATE TABLE IF NOT EXISTS messages (
  id          TEXT PRIMARY KEY,
  session_id  TEXT NOT NULL,
  msg_role    TEXT NOT NULL,
  content     TEXT,
  tool_calls  TEXT,
  citations   TEXT,
  created_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_messages_session_created ON messages (session_id, created_at);

CREATE TABLE IF NOT EXISTS session_context_usage (
  id                TEXT PRIMARY KEY,
  session_id        TEXT NOT NULL,
  turn              INTEGER NOT NULL DEFAULT 0,
  input_tokens      INTEGER NOT NULL DEFAULT 0,
  output_tokens     INTEGER NOT NULL DEFAULT 0,
  cache_read_tokens INTEGER NOT NULL DEFAULT 0,
  total_tokens      INTEGER NOT NULL DEFAULT 0,
  usage_source      TEXT NOT NULL DEFAULT 'provider',
  compacted         INTEGER NOT NULL DEFAULT 0,
  compact_count     INTEGER NOT NULL DEFAULT 0,
  snapshot          TEXT,
  created_at        TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ctx_session_created ON session_context_usage (session_id, created_at);

CREATE TABLE IF NOT EXISTS context_archives (
  id          TEXT PRIMARY KEY,
  session_id  TEXT NOT NULL,
  turn        INTEGER NOT NULL DEFAULT 0,
  file_path   TEXT NOT NULL DEFAULT '',
  tokens      INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_archive_session_created ON context_archives (session_id, created_at);

CREATE TABLE IF NOT EXISTS schedules (
  id          TEXT PRIMARY KEY,
  user_name   TEXT NOT NULL DEFAULT 'default',
  title       TEXT NOT NULL DEFAULT '',
  cron        TEXT NOT NULL,
  prompt      TEXT NOT NULL,
  mode        TEXT NOT NULL DEFAULT 'full',
  enabled     INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_schedules_enabled ON schedules (enabled);

CREATE TABLE IF NOT EXISTS schedule_runs (
  id          TEXT PRIMARY KEY,
  schedule_id TEXT NOT NULL,
  thread_id   TEXT NOT NULL,
  status      TEXT NOT NULL,
  started_at  TEXT NOT NULL,
  finished_at TEXT,
  error       TEXT,
  output      TEXT
);
CREATE INDEX IF NOT EXISTS idx_runs_schedule_started ON schedule_runs (schedule_id, started_at);

CREATE TABLE IF NOT EXISTS billing_usage (
  id            TEXT PRIMARY KEY,
  user_name     TEXT NOT NULL DEFAULT 'default',
  session_id    TEXT NOT NULL DEFAULT '',
  kind          TEXT NOT NULL DEFAULT 'chat',
  model         TEXT NOT NULL DEFAULT '',
  input_tokens  INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  total_tokens  INTEGER NOT NULL DEFAULT 0,
  cost          REAL NOT NULL DEFAULT 0,
  usage_day     TEXT NOT NULL,
  trace_id      TEXT NOT NULL DEFAULT '',
  created_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_billing_user_day ON billing_usage (user_name, usage_day);
CREATE INDEX IF NOT EXISTS idx_billing_session ON billing_usage (session_id);

CREATE TABLE IF NOT EXISTS billing_pricing (
  model        TEXT PRIMARY KEY,
  input_price  REAL NOT NULL DEFAULT 0,
  output_price REAL NOT NULL DEFAULT 0,
  currency     TEXT NOT NULL DEFAULT 'CNY',
  updated_at   TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS daily_quota (
  user_name    TEXT NOT NULL,
  usage_day    TEXT NOT NULL,
  limit_tokens INTEGER NOT NULL DEFAULT 0,
  used_tokens  INTEGER NOT NULL DEFAULT 0,
  updated_at   TEXT NOT NULL,
  PRIMARY KEY (user_name, usage_day)
);

CREATE TABLE IF NOT EXISTS user_preferences (
  id          TEXT PRIMARY KEY,
  user_name   TEXT NOT NULL,
  pref_key    TEXT NOT NULL,
  pref_value  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_pref_user_key ON user_preferences (user_name, pref_key);

CREATE TABLE IF NOT EXISTS execution_runs (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  user_name VARCHAR(128) NOT NULL,
  kind VARCHAR(24) NOT NULL,
  status VARCHAR(24) NOT NULL,
  error TEXT,
  started_at VARCHAR(32) NOT NULL,
  finished_at VARCHAR(32)
);
CREATE TABLE IF NOT EXISTS billing_pending (
  id TEXT PRIMARY KEY,
  payload TEXT NOT NULL,
  created_at VARCHAR(32) NOT NULL
);

CREATE TABLE IF NOT EXISTS request_keys (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  created_at VARCHAR(32) NOT NULL
);