-- ===== categories：流程架构节点（任意深度 + 物化路径） =====
CREATE TABLE IF NOT EXISTS categories (
  id          TEXT PRIMARY KEY,
  parent_id   TEXT,
  code        TEXT,                       -- "1.0" / "4.1.1" / "12.4.3.1"
  name        TEXT NOT NULL,
  level       INTEGER NOT NULL,           -- 1..5
  path        TEXT NOT NULL UNIQUE,       -- "L1名 / L2名 / ..."
  status      TEXT NOT NULL DEFAULT 'active',  -- active | archived
  sort        INTEGER NOT NULL DEFAULT 0,
  created_at  INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS idx_cat_parent ON categories(parent_id);
CREATE INDEX IF NOT EXISTS idx_cat_level  ON categories(level);
CREATE INDEX IF NOT EXISTS idx_cat_status ON categories(status);

-- ===== skills：技能（强制归位 → categories.id） =====
CREATE TABLE IF NOT EXISTS skills (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  description   TEXT,
  node_id       TEXT NOT NULL,            -- 强制归位 FK → categories.id
  node_path     TEXT NOT NULL,            -- 冗余存"L1/L2/L3"，列表展示快
  source        TEXT NOT NULL,            -- upload | create
  status        TEXT NOT NULL DEFAULT 'draft',  -- draft | published | archived
  creator_name  TEXT NOT NULL,            -- 用户自填，必填
  creator_phone TEXT,                     -- 登录态自动带，后台留痕
  creator_email TEXT,                     -- 登录态自动带，后台留痕
  package_key   TEXT,                     -- R2 zip 包 key（upload 类型必填）
  package_size  INTEGER,
  package_name  TEXT,                     -- 原文件名
  created_at    INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  updated_at    INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  FOREIGN KEY (node_id) REFERENCES categories(id)
);
CREATE INDEX IF NOT EXISTS idx_skill_node          ON skills(node_id);
CREATE INDEX IF NOT EXISTS idx_skill_status        ON skills(status);
CREATE INDEX IF NOT EXISTS idx_skill_source        ON skills(source);
CREATE INDEX IF NOT EXISTS idx_skill_creator_name  ON skills(creator_name);
CREATE INDEX IF NOT EXISTS idx_skill_creator_phone ON skills(creator_phone);
CREATE INDEX IF NOT EXISTS idx_skill_created       ON skills(created_at DESC);

-- ===== audit_logs：操作日志（运营消费） =====
CREATE TABLE IF NOT EXISTS audit_logs (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  ts           INTEGER NOT NULL DEFAULT (strftime('%s','now')),
  action       TEXT NOT NULL,             -- skill.create | skill.update | skill.upload | skill.delete | category.read 等
  actor_name   TEXT,                      -- 来自 skill.creator_name 或当前请求体
  actor_phone  TEXT,                      -- 来自身份头
  actor_email  TEXT,
  target_type  TEXT,                      -- skill | category | file
  target_id    TEXT,
  target_name  TEXT,
  ip           TEXT,
  ua           TEXT,
  meta         TEXT                       -- JSON 字符串，扩展字段
);
CREATE INDEX IF NOT EXISTS idx_audit_ts     ON audit_logs(ts DESC);
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_audit_actor  ON audit_logs(actor_phone);

-- ===== files：上传文件登记（孤儿检测） =====
CREATE TABLE IF NOT EXISTS files (
  id            TEXT PRIMARY KEY,         -- 与 R2 key 关联
  r2_key        TEXT NOT NULL UNIQUE,
  filename      TEXT NOT NULL,
  size          INTEGER,
  mime          TEXT,
  uploader_phone TEXT,
  uploader_email TEXT,
  bound_skill_id TEXT,                    -- 绑定到 skill 后回填，未绑定 = 孤儿
  created_at    INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS idx_files_bound ON files(bound_skill_id);
CREATE INDEX IF NOT EXISTS idx_files_uploader ON files(uploader_phone);
