"""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);
CREATE TABLE IF NOT EXISTS api_tokens (
    id          SERIAL PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES navi_users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    token_hash  TEXT NOT NULL UNIQUE,
    token_prefix TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    last_used_at TIMESTAMPTZ,
    revoked_at  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_api_tokens_user_id ON api_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_api_tokens_token_hash ON api_tokens(token_hash);
"""

# Columns added after initial table creation — boot-time migration.
_NAVI_USERS_MIGRATION_COLUMNS = [
    ("username", "TEXT"),
    ("first_name", "TEXT"),
    ("last_name", "TEXT"),
    ("phone", "TEXT"),
    ("birth_date", "TEXT"),
    ("country", "TEXT"),
    ("city", "TEXT"),
    ("locale", "TEXT"),
]


async def _ensure_auth_tables() -> None:
    """Create auth tables if they don't exist and run boot-time migrations."""
    from navi.config import settings

    conn = await asyncpg.connect(settings.database_url)
    try:
        await conn.execute(_DDL)
        # Migrate existing navi_users tables that lack newer columns
        for col_name, col_type in _NAVI_USERS_MIGRATION_COLUMNS:
            row = await conn.fetchrow(
                """
                SELECT 1 FROM information_schema.columns
                WHERE table_name = 'navi_users' AND column_name = $1
                """,
                col_name,
            )
            if row is None:
                await conn.execute(
                    f'ALTER TABLE navi_users ADD COLUMN {col_name} {col_type}'
                )
    finally:
        await conn.close()