"""Auth DDL — table creation for navi_users and user_auth_sessions."""


_DDL = """
CREATE TABLE IF NOT EXISTS navi_users (
    id          TEXT PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT,
    role        TEXT NOT NULL DEFAULT 'user',
    permissions TEXT NOT NULL DEFAULT '[]',
    created_at  TIMESTAMPTZ NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL
);

CREATE TABLE IF NOT EXISTS user_auth_sessions (
    id               TEXT PRIMARY KEY,
    user_id          TEXT NOT NULL REFERENCES navi_users(id) ON DELETE CASCADE,
    access_token_enc TEXT NOT NULL,
    refresh_token_enc TEXT NOT NULL,
    expires_at       TIMESTAMPTZ NOT NULL,
    created_at       TIMESTAMPTZ NOT NULL,
    last_used_at     TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_user_auth_sessions_user_id ON user_auth_sessions (user_id);
"""


async def _ensure_auth_tables() -> None:
    """Create auth tables if they don't exist."""
    from navi.api.deps import get_session_store

    store = get_session_store()
    pool = await store._get_pool()
    async with pool.acquire() as conn:
        await conn.execute(_DDL)