-- 0003_create_token_tables.up.sql
--
-- DB 接入塊 3：建立 pairing_tokens + session_tokens 兩張表（token 分表決策）。
-- 對齊 docs/autoflow/04-architecture/database.md §2.4、§4（token 分表段落）、§5.1。
--
-- 接續 0001（users + models）/ 0002（devices）；兩張 token 表的 user_id / device_id
-- 都是 REFERENCES 既有表的 FK（users 於 0001、devices 於 0002 建立）。
--
-- 環境事實（與 0001/0002 相同）：PostgreSQL 14.23。
--
-- ── 分表決策（database.md §4）──────────────────────────────────────────────
-- pairing_tokens 與 session_tokens「分表」，不共表 by kind。理由：
--   1. code 中是兩個獨立 struct + 兩個 Store interface（PairingStore / SessionTokenStore），
--      欄位與方法集不同（pairing 有 used_at 一次性語意、kind；session 有 parent_token_hash、無 used_at）。
--   2. 共表會讓 used_at / parent_token_hash 對另一類永遠為 NULL，欄位語意混淆。
--   3. 分表後各表 schema 乾淨、index 各自最佳化，repository 一對一對映 Store。
-- 代價：稽核「pairing→session 升級鏈」需跨表 join（session_tokens.parent_token_hash
--       → pairing_tokens.token_hash）；可接受（查詢頻率低）。

-- pairing_tokens（短期一次性配對 token；對齊 internal/auth.PairingToken）
-- PK = token_hash（sha256(plaintext)）：永不存明文 token（security.md §1.3）。
CREATE TABLE pairing_tokens (
    token_hash    TEXT PRIMARY KEY,                  -- sha256(plaintext)，永不存明文
    user_id       UUID NOT NULL REFERENCES users(id),
    device_id     UUID REFERENCES devices(id),       -- MarkUsed 綁定後才有（nullable）
    kind          TEXT NOT NULL DEFAULT 'pairing',   -- 固定 'pairing'（保留欄位，便於觀測/未來擴充）
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at    TIMESTAMPTZ,                        -- 15min TTL；NULL = 永不過期（測試用）
    used_at       TIMESTAMPTZ,                        -- 一次性：MarkUsed 後 Validate 失敗
    revoked_at    TIMESTAMPTZ
);

-- List by user（UI 顯示），只索引未撤銷紀錄。
CREATE INDEX idx_pairing_tokens_user_active ON pairing_tokens (user_id) WHERE revoked_at IS NULL;
-- device_id 反查（cascade 撤銷 by device，塊 5）。
CREATE INDEX idx_pairing_tokens_device ON pairing_tokens (device_id);

-- session_tokens（長期可撤銷 tunnel session token；對齊 internal/auth.SessionToken）
-- PK = token_hash；device_id 必填（session token 必綁 device）。
CREATE TABLE session_tokens (
    token_hash         TEXT PRIMARY KEY,                          -- sha256(plaintext)
    user_id            UUID NOT NULL REFERENCES users(id),
    device_id          UUID NOT NULL REFERENCES devices(id),      -- session token 必綁 device
    parent_token_hash  TEXT,                                      -- 升級來源 pairing token 的 hash（稽核鏈，可 join pairing_tokens.token_hash）
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at         TIMESTAMPTZ,                               -- 90 天 TTL；NULL = 永不過期
    revoked_at         TIMESTAMPTZ
    -- 注意：無 used_at（非一次性）、無 kind
);

-- List by user，只索引未撤銷紀錄。
CREATE INDEX idx_session_tokens_user_active ON session_tokens (user_id) WHERE revoked_at IS NULL;
-- device_id 反查（cascade 撤銷 by device，塊 5）。
CREATE INDEX idx_session_tokens_device ON session_tokens (device_id);
-- parent_token_hash 稽核鏈查詢（join pairing_tokens.token_hash）。
CREATE INDEX idx_session_tokens_parent ON session_tokens (parent_token_hash);