-- Phase 2 migration: WebAuthn passkey credentials, sessions, recovery codes.
-- Matches PROJECT.md Appendix A. The audit_log table created in Phase 0 is
-- the persistence layer for register/login/logout/recovery events.

CREATE TABLE IF NOT EXISTS webauthn_credentials (
    id            TEXT PRIMARY KEY,        -- base64url credential id from the authenticator
    public_key    BLOB NOT NULL,           -- COSE public key bytes
    counter       INTEGER NOT NULL,        -- last-seen signature counter
    transports    TEXT,                    -- JSON array of transports (usb/nfc/ble/internal)
    name          TEXT,                    -- operator-chosen device name (e.g. "iPhone")
    created_at    INTEGER NOT NULL,
    last_used_at  INTEGER
);

CREATE TABLE IF NOT EXISTS recovery_codes (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    code_hash   BLOB NOT NULL,             -- bcrypt hash of the recovery code
    used_at     INTEGER,                   -- NULL when unused; set on first redeem
    created_at  INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_recovery_codes_unused
    ON recovery_codes(used_at) WHERE used_at IS NULL;

CREATE TABLE IF NOT EXISTS sessions (
    token         TEXT PRIMARY KEY,        -- random 256-bit token, base64url
    created_at    INTEGER NOT NULL,
    expires_at    INTEGER NOT NULL,
    last_seen_at  INTEGER NOT NULL,
    user_agent    TEXT,
    ip_address    TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_expires
    ON sessions(expires_at);
