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

import asyncpg

_DDL = """
CREATE TABLE IF NOT EXISTS navi_users (
    id          TEXT PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT,
    username     TEXT,
    first_name   TEXT,
    last_name    TEXT,
    phone        TEXT,
    birth_date   TEXT,
    country      TEXT,
    city         TEXT,
    locale       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.config import settings

    conn = await asyncpg.connect(settings.database_url)
    try:
        await conn.execute(_DDL)
    finally:
        await conn.close()