backend/app/schema.sql

-- =====================================================================
-- 知識庫系統 V5 — DB Schema（L1, PRD §5 / §27.5 / §34 / §49 對齊）
-- SQLite WAL, foreign_keys=ON, busy_timeout=30000
-- 對 PRD 模糊處的補完，皆於該表上方註解 "PRD-FILL" 標註。
-- =====================================================================

-- ---- 系統 / migration ----
CREATE TABLE IF NOT EXISTS schema_migrations (
    version     INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    applied_at  TEXT NOT NULL
);

-- =====================================================================
-- 核心 7 張（PRD §5.1）
-- =====================================================================

-- ---- users（PRD §5.1）----
CREATE TABLE IF NOT EXISTS users (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    username                TEXT NOT NULL UNIQUE,
    email                   TEXT NOT NULL UNIQUE,
    password_hash           TEXT NOT NULL,
    role                    TEXT NOT NULL CHECK(role IN ('admin','senior_manager','manager','user','guest')),
    permissions             TEXT NOT NULL DEFAULT '{"categories":["all"]}',
    department              TEXT NOT NULL DEFAULT '',
    expires_at              TEXT,                                    -- §50.2.2 guest 帳號自動失效時間
    guest_allowed_depts     TEXT NOT NULL DEFAULT '[]',             -- §50.2.2 guest 可看部門 JSON
    created_at              TEXT NOT NULL,
    last_login              TEXT,
    is_active               INTEGER NOT NULL DEFAULT 1,
    is_deleted              INTEGER NOT NULL DEFAULT 0,
    deleted_at              TEXT,
    deleted_by              INTEGER REFERENCES users(id),
    force_password_change   INTEGER NOT NULL DEFAULT 0,
    jwt_version             INTEGER NOT NULL DEFAULT 1,
    totp_secret             TEXT,
    totp_enabled            INTEGER NOT NULL DEFAULT 0,
    totp_grace_until        TEXT,
    failed_login_count      INTEGER NOT NULL DEFAULT 0,
    locked_until            TEXT
);

-- PRD-FILL: documents 表 PRD §5.1 僅列擴充欄位，核心欄位依 §6 frontmatter + §11 visibility + §15 embedding 推導
CREATE TABLE IF NOT EXISTS documents (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    title                   TEXT NOT NULL,
    category                TEXT NOT NULL,
    tags                    TEXT NOT NULL DEFAULT '[]',     -- JSON array
    summary                 TEXT,
    body                    TEXT NOT NULL,                  -- Markdown
    vault_path              TEXT UNIQUE,                    -- vault/ 對應 md 路徑
    author_id               INTEGER NOT NULL REFERENCES users(id),
    owner_department        TEXT NOT NULL DEFAULT '',
    visibility              TEXT NOT NULL DEFAULT 'department'
                            CHECK(visibility IN ('public','department','selected_departments','restricted','confidential')),
    allowed_departments     TEXT NOT NULL DEFAULT '[]',     -- JSON array of dept names
    access_list             TEXT NOT NULL DEFAULT '[]',     -- JSON array of user_ids
    related                 TEXT NOT NULL DEFAULT '[]',     -- §41 JSON array of related doc ids（防護C 跨部門互掛）
    up_moc_id               INTEGER REFERENCES documents(id),  -- §41 所屬統一 MOC 文件 id（防護D）
    keywords                TEXT NOT NULL DEFAULT '[]',     -- §8.5 JSON array of ai_keywords
    last_restored_from      INTEGER,                        -- §7.5 最近一次還原的來源版次（任何 edit 後清空）
    template_id             INTEGER REFERENCES document_templates(id),
    referrer_count          INTEGER NOT NULL DEFAULT 0,
    view_count              INTEGER NOT NULL DEFAULT 0,
    favorite_count          INTEGER NOT NULL DEFAULT 0,
    status                  TEXT NOT NULL DEFAULT 'active'
                            CHECK(status IN ('active','archived')),
    embedding               BLOB,                            -- 384-dim float32
    created_at              TEXT NOT NULL,
    updated_at              TEXT NOT NULL,
    is_deleted              INTEGER NOT NULL DEFAULT 0,
    deleted_at              TEXT,
    deleted_by              INTEGER REFERENCES users(id)
);

-- PRD-FILL: pending_documents 核心欄位（status CHECK 依 PRD §5.1 完整列舉）
CREATE TABLE IF NOT EXISTS pending_documents (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    submitter_id            INTEGER NOT NULL REFERENCES users(id),
    submitter_department    TEXT NOT NULL DEFAULT '',
    original_content        TEXT NOT NULL,
    ai_result               TEXT,                           -- AI 整理後完整 markdown
    ai_meta_json            TEXT,                           -- AI 提取的 frontmatter（標題/分類/標籤）
    target_document_id      INTEGER REFERENCES documents(id),
    merge_target_title      TEXT,
    status                  TEXT NOT NULL DEFAULT 'processing'
                            CHECK(status IN ('processing','ai_ready','pending','approved','rejected','draft','failed')),
    reject_reason           TEXT,
    reviewer_id             INTEGER REFERENCES users(id),
    reviewed_at             TEXT,
    created_at              TEXT NOT NULL,
    updated_at              TEXT NOT NULL
);

-- PRD-FILL: document_history 版本追蹤
CREATE TABLE IF NOT EXISTS document_history (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    document_id     INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    version         INTEGER NOT NULL,
    title           TEXT NOT NULL,
    body            TEXT NOT NULL,
    summary         TEXT,
    tags            TEXT NOT NULL DEFAULT '[]',
    edited_by       INTEGER NOT NULL REFERENCES users(id),
    edited_at       TEXT NOT NULL,
    change_note     TEXT,
    UNIQUE(document_id, version)
);

-- PRD-FILL: detection_rules（PRD §5.1 補欄位 + §49 layer1 規則）
CREATE TABLE IF NOT EXISTS detection_rules (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    rule_name       TEXT NOT NULL UNIQUE,
    pattern         TEXT NOT NULL,                          -- regex
    layer           INTEGER NOT NULL DEFAULT 1,             -- 1=regex, 2=variant, 3=topic, 4=sample
    action          TEXT NOT NULL DEFAULT 'block'
                    CHECK(action IN ('block','warn','mask','log')),
    is_active       INTEGER NOT NULL DEFAULT 1,
    is_deleted      INTEGER NOT NULL DEFAULT 0,
    created_by      INTEGER REFERENCES users(id),
    created_at      TEXT NOT NULL,
    updated_at      TEXT,
    updated_by      INTEGER REFERENCES users(id)
);

-- PRD-FILL: audit_log（PRD §5.1 + §10.7 永久保留）
CREATE TABLE IF NOT EXISTS audit_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER REFERENCES users(id),
    action          TEXT NOT NULL,
    resource_type   TEXT,
    resource_id     TEXT,
    detail          TEXT,                                   -- JSON
    ip_address      TEXT,
    user_agent      TEXT,
    result          TEXT CHECK(result IN ('success','fail','blocked')) DEFAULT 'success',
    timestamp       TEXT NOT NULL
);

-- PRD-FILL: notifications（PRD §5.1 30 天 TTL）
CREATE TABLE IF NOT EXISTS notifications (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type            TEXT NOT NULL,                          -- pending_review / approved / rejected / ...
    title           TEXT NOT NULL,
    body            TEXT,
    link            TEXT,                                   -- 點擊跳轉路徑
    is_read         INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL,
    read_at         TEXT
);

-- =====================================================================
-- 衍生 5 張（PRD §5.2 / §27.3 / §30.2 / §31.1 / §32.2）
-- =====================================================================

CREATE TABLE IF NOT EXISTS detection_rules_history (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    rule_id         INTEGER REFERENCES detection_rules(id),
    action          TEXT NOT NULL CHECK(action IN ('create','update','delete','restore')),
    old_pattern     TEXT,
    new_pattern     TEXT,
    old_rule_name   TEXT,
    new_rule_name   TEXT,
    changed_by      INTEGER REFERENCES users(id),
    changed_at      TEXT NOT NULL,
    created_at      TEXT NOT NULL
);

-- PRD §27.3
CREATE TABLE IF NOT EXISTS ai_usage (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id),
    department      TEXT,
    provider        TEXT,
    model           TEXT,
    action          TEXT,
    input_tokens    INTEGER,
    output_tokens   INTEGER,
    cost_usd_x1000  INTEGER,
    cache_hit       INTEGER NOT NULL DEFAULT 0,
    timestamp       TEXT NOT NULL,
    pending_id      INTEGER REFERENCES pending_documents(id)
);

-- PRD §30.2
CREATE TABLE IF NOT EXISTS sessions (
    id              TEXT PRIMARY KEY,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    user_agent      TEXT,
    ip_address      TEXT,
    created_at      TEXT NOT NULL,
    last_used_at    TEXT NOT NULL,
    expires_at      TEXT NOT NULL
);

-- PRD §31.1
CREATE TABLE IF NOT EXISTS feedback (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER REFERENCES users(id),
    category    TEXT NOT NULL CHECK(category IN ('bug','feature','improvement','question','document_error')),
    title       TEXT NOT NULL,
    description TEXT NOT NULL,
    page_url    TEXT,
    doc_id      INTEGER REFERENCES documents(id),  -- §50.3.2 報告錯誤關聯文件
    user_agent  TEXT,
    status      TEXT NOT NULL DEFAULT 'open' CHECK(status IN ('open','reviewing','resolved','wontfix')),
    admin_reply TEXT,
    created_at  TEXT NOT NULL,
    updated_at  TEXT
);

-- PRD §32.2
CREATE TABLE IF NOT EXISTS user_preferences (
    user_id               INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    notify_pending        INTEGER NOT NULL DEFAULT 1,
    notify_account_apply  INTEGER NOT NULL DEFAULT 1,
    notify_visibility     INTEGER NOT NULL DEFAULT 1,
    notify_abnormal       INTEGER NOT NULL DEFAULT 1,
    notify_announcement   INTEGER NOT NULL DEFAULT 1,
    default_view          TEXT NOT NULL DEFAULT 'grid' CHECK(default_view IN ('grid','list')),
    font_size             TEXT NOT NULL DEFAULT 'medium' CHECK(font_size IN ('small','medium','large')),
    page_size             INTEGER NOT NULL DEFAULT 50,
    seen_whatsnew_version TEXT,
    onboarding_completed  INTEGER NOT NULL DEFAULT 0,
    onboarding_checklist_progress TEXT NOT NULL DEFAULT '{}'  -- §50.3.1 {doc_id: viewed_at}
);

-- =====================================================================
-- 其他（PRD §27.5 / §34 / §47 / §48 / §49 / 標籤 / 待辦 / 分享）
-- =====================================================================

-- PRD §27.5.1
CREATE TABLE IF NOT EXISTS departments (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,
    is_active       INTEGER NOT NULL DEFAULT 1,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_by      INTEGER REFERENCES users(id),
    created_at      TEXT NOT NULL,
    deactivated_at  TEXT,
    deactivated_by  INTEGER REFERENCES users(id),
    onboarding_doc_ids TEXT NOT NULL DEFAULT '[]'  -- §50.3.1 部門新人必讀文件 id JSON
);

-- PRD §34.1
CREATE TABLE IF NOT EXISTS ai_cache (
    content_hash    TEXT PRIMARY KEY,
    ai_result_json  TEXT NOT NULL,
    provider        TEXT NOT NULL,
    model           TEXT NOT NULL,
    cached_at       TEXT NOT NULL,
    hit_count       INTEGER NOT NULL DEFAULT 0,
    last_hit_at     TEXT
);

-- PRD-FILL: bookmarks（PRD §47 收藏）
CREATE TABLE IF NOT EXISTS bookmarks (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    document_id     INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    folder          TEXT NOT NULL DEFAULT '預設',          -- 收藏分類
    sort_order      INTEGER NOT NULL DEFAULT 0,
    note            TEXT,
    created_at      TEXT NOT NULL,
    UNIQUE(user_id, document_id)
);

-- PRD-FILL: bookmark_folders（PRD §12.25 收藏資料夾；支援空資料夾 + 改名/刪除）
CREATE TABLE IF NOT EXISTS bookmark_folders (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    icon            TEXT,
    color           TEXT,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL,
    UNIQUE(user_id, name)
);

-- PRD §48
-- PRD §48.4 文檔模板系統：三件套（structure_md 骨架 / ai_prompt 指令 / example_content 範例）
CREATE TABLE IF NOT EXISTS document_templates (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL,
    description     TEXT,
    scope           TEXT NOT NULL CHECK(scope IN ('global','department')),
    department_id   INTEGER REFERENCES departments(id),    -- scope='department' 時必填
    structure_md    TEXT NOT NULL DEFAULT '',              -- 區塊骨架，AI 必須產出對應段落
    ai_prompt       TEXT NOT NULL DEFAULT '',              -- 給 AI 的具體指令
    example_content TEXT,                                  -- 完整範例（選填）
    is_active       INTEGER NOT NULL DEFAULT 1,
    is_default      INTEGER NOT NULL DEFAULT 0,            -- 該範圍的預設模板
    created_by      INTEGER REFERENCES users(id),
    created_at      TEXT NOT NULL,
    updated_at      TEXT,
    updated_by      INTEGER REFERENCES users(id)
);

-- PRD §41 防護D：跨部門同主題追蹤（admin 跨部門主題管理頁 §41.5）
CREATE TABLE IF NOT EXISTS cross_dept_topics (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    topic           TEXT NOT NULL,                          -- 主題名（取自文件標題）
    doc_ids         TEXT NOT NULL DEFAULT '[]',             -- JSON 涉及文件 ids
    departments     TEXT NOT NULL DEFAULT '[]',             -- JSON 涉及部門
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK(status IN ('pending','moc_created','ignored')),
    moc_id          INTEGER REFERENCES documents(id),       -- 建立的統一 MOC 文件
    ignored_until   TEXT,                                   -- ignored：30 天內不再通知
    created_at      TEXT NOT NULL,
    updated_at      TEXT
);

-- PRD §49.6
CREATE TABLE IF NOT EXISTS sensitive_topics (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,
    description     TEXT,
    is_builtin      INTEGER NOT NULL DEFAULT 0,
    is_active       INTEGER NOT NULL DEFAULT 1,
    threshold       REAL NOT NULL DEFAULT 0.65,
    centroid        BLOB,
    created_by      INTEGER REFERENCES users(id),
    created_at      TEXT NOT NULL,
    updated_at      TEXT
);

CREATE TABLE IF NOT EXISTS sensitive_topic_samples (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    topic_id        INTEGER NOT NULL REFERENCES sensitive_topics(id) ON DELETE CASCADE,
    sample_text     TEXT NOT NULL,
    embedding       BLOB NOT NULL,
    added_by        INTEGER REFERENCES users(id),
    added_at        TEXT NOT NULL
);

-- PRD §49.7
CREATE TABLE IF NOT EXISTS confidential_samples (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    label           TEXT NOT NULL,
    description     TEXT,
    embedding       BLOB NOT NULL,
    text_length     INTEGER,
    text_hash       TEXT,
    added_by        INTEGER REFERENCES users(id),
    added_at        TEXT NOT NULL,
    is_active       INTEGER NOT NULL DEFAULT 1
);

-- PRD §49.9
CREATE TABLE IF NOT EXISTS false_positive_appeals (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id),
    text_snippet    TEXT NOT NULL,
    layer           INTEGER NOT NULL,
    matched_rule    TEXT,
    similarity      REAL,
    user_reason     TEXT,
    status          TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','approved','rejected')),
    reviewed_by     INTEGER REFERENCES users(id),
    reviewed_at     TEXT,
    admin_note      TEXT,
    created_at      TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS whitelist_samples (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    embedding       BLOB NOT NULL,
    label           TEXT,
    added_by        INTEGER REFERENCES users(id),
    added_at        TEXT NOT NULL
);

-- PRD-FILL: tags + document_tags（PRD §12.21 標籤頁所需；§6 frontmatter tags 為 JSON，這裡是正規化版本）
CREATE TABLE IF NOT EXISTS tags (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,
    color           TEXT,
    created_at      TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS document_tags (
    document_id     INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    tag_id          INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY(document_id, tag_id)
);

-- PRD-FILL: tasks（PRD §12.24 個人待辦）
CREATE TABLE IF NOT EXISTS tasks (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    body            TEXT,
    status          TEXT NOT NULL DEFAULT 'open'
                    CHECK(status IN ('open','in_progress','done','archived')),
    due_at          TEXT,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL,
    updated_at      TEXT
);

-- PRD-FILL: share_links（PRD §11 分享連結）
CREATE TABLE IF NOT EXISTS share_links (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    document_id     INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    token           TEXT NOT NULL UNIQUE,
    created_by      INTEGER NOT NULL REFERENCES users(id),
    password_hash   TEXT,
    require_login   INTEGER NOT NULL DEFAULT 0,
    expires_at      TEXT,
    revoked_at      TEXT,
    created_at      TEXT NOT NULL,
    last_used_at    TEXT
);

-- =====================================================================
-- 索引（PRD §5.3）
-- =====================================================================
CREATE INDEX IF NOT EXISTS idx_documents_category    ON documents(category);
CREATE INDEX IF NOT EXISTS idx_documents_visibility  ON documents(visibility, is_deleted);
CREATE INDEX IF NOT EXISTS idx_pending_status        ON pending_documents(status);
CREATE INDEX IF NOT EXISTS idx_history_document      ON document_history(document_id);
CREATE INDEX IF NOT EXISTS idx_audit_user            ON audit_log(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_timestamp       ON audit_log(timestamp);
CREATE INDEX IF NOT EXISTS idx_notifications_user    ON notifications(user_id, is_read);
CREATE INDEX IF NOT EXISTS idx_users_department      ON users(department, is_deleted);
CREATE INDEX IF NOT EXISTS idx_ai_usage_user         ON ai_usage(user_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_ai_usage_dept         ON ai_usage(department, timestamp);
CREATE INDEX IF NOT EXISTS idx_departments_active    ON departments(is_active, sort_order);
CREATE INDEX IF NOT EXISTS idx_ai_cache_cached_at    ON ai_cache(cached_at);
CREATE INDEX IF NOT EXISTS idx_conf_samples_active   ON confidential_samples(is_active);
CREATE INDEX IF NOT EXISTS idx_sessions_user         ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_bookmarks_user        ON bookmarks(user_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_tasks_user            ON tasks(user_id, status, sort_order);
CREATE INDEX IF NOT EXISTS idx_share_links_doc       ON share_links(document_id);
CREATE INDEX IF NOT EXISTS idx_templates_scope       ON document_templates(scope, department_id, is_active);
CREATE INDEX IF NOT EXISTS idx_templates_default     ON document_templates(is_default, is_active);

-- =====================================================================
-- FTS5 全文索引（PRD §5.3）
-- 採 contentless 對應 documents 主表，由觸發器同步
-- =====================================================================
CREATE VIRTUAL TABLE IF NOT EXISTS documents_fts
USING fts5(title, summary, body, content='documents', content_rowid='id');

CREATE TRIGGER IF NOT EXISTS documents_ai AFTER INSERT ON documents BEGIN
    INSERT INTO documents_fts(rowid, title, summary, body)
    VALUES (new.id, new.title, new.summary, new.body);
END;
CREATE TRIGGER IF NOT EXISTS documents_ad AFTER DELETE ON documents BEGIN
    INSERT INTO documents_fts(documents_fts, rowid, title, summary, body)
    VALUES ('delete', old.id, old.title, old.summary, old.body);
END;
CREATE TRIGGER IF NOT EXISTS documents_au AFTER UPDATE ON documents BEGIN
    INSERT INTO documents_fts(documents_fts, rowid, title, summary, body)
    VALUES ('delete', old.id, old.title, old.summary, old.body);
    INSERT INTO documents_fts(rowid, title, summary, body)
    VALUES (new.id, new.title, new.summary, new.body);
END;

-- =====================================================================
-- L3+ trusted_devices（記住此裝置以跳過 2FA），對應 HTML「記住我 14 天」
-- PRD §10.1 / §10.2 衍生：經過 2FA 驗證的裝置在 TTL 內登入可跳過 2FA。
-- 撤銷方式：使用者於 /account/sessions 移除、改密、jwt_version+1、過期 cron。
-- 只存 token 的 SHA-256，避免 DB 外洩。
-- =====================================================================
-- =====================================================================
-- L3+ users profile 擴充（migration v3）：display_name / extension /
-- secondary_departments（兼任部門 JSON）。PRD §5 未列，依使用者需求 + §7.3 gap。
-- 註：欄位若已存在，db.py 的 migration 會 try/except 略過（SQLite 無 IF NOT EXISTS for ADD COLUMN）。
-- =====================================================================

CREATE TABLE IF NOT EXISTS trusted_devices (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash      TEXT NOT NULL,
    label           TEXT,
    user_agent      TEXT,
    ip_address      TEXT,
    created_at      TEXT NOT NULL,
    last_used_at    TEXT NOT NULL,
    expires_at      TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_trusted_devices_user ON trusted_devices(user_id, expires_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_trusted_devices_token ON trusted_devices(token_hash);

-- =====================================================================
-- L10：AI Provider 設定 / 模型對照 / 切換旗標 / 匯率快取（PRD §6.x / §27.3 / §33.4）
-- key-value JSON 設定表（PRD §33.4 system_config.exchange_rates 等）。
-- =====================================================================
CREATE TABLE IF NOT EXISTS system_config (
    key         TEXT PRIMARY KEY,
    value       TEXT NOT NULL,                  -- JSON
    updated_at  TEXT NOT NULL,
    updated_by  INTEGER REFERENCES users(id)
);

-- §50.2.1 多部門兼任（users_departments 多對多，D234）。
-- 註：PRD 範例用 department_id，但本系統全鏈路為「部門名稱制」(owner_department / allowed_departments
-- 皆存名稱)，為一致性與降低重構風險，此處改存部門名稱 department（§7.1 取較一致一方）。
CREATE TABLE IF NOT EXISTS users_departments (
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    department  TEXT NOT NULL,
    is_primary  INTEGER NOT NULL DEFAULT 0,
    assigned_at TEXT NOT NULL,
    assigned_by INTEGER REFERENCES users(id),
    PRIMARY KEY (user_id, department)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_primary ON users_departments(user_id) WHERE is_primary=1;

CREATE INDEX IF NOT EXISTS idx_ai_usage_user ON ai_usage(user_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_ai_usage_dept ON ai_usage(department, timestamp);
CREATE INDEX IF NOT EXISTS idx_ai_cache_cached_at ON ai_cache(cached_at);