"""
services/role_play_repo.py
==========================
Data-access layer for the Role Play questions table.

⚠ Schema is not finalized yet. The functions below are scaffolds
that read whatever columns exist (SELECT *) and return them as-is.
Once the table is locked, replace `_normalize` with explicit field
mapping just like `participant_repo` does.

Table name comes from settings.DB_TABLE_ROLE_PLAY (.env).
"""
from __future__ import annotations

from psycopg2 import sql

from config import settings
from utils import database as db
from utils.logger import get_logger

log = get_logger(__name__)


def _normalize(row: dict) -> dict:
    """Pass-through for now; preserves raw columns for future mapping."""
    return dict(row)


def _run(query: sql.Composable, params: tuple) -> list[dict]:
    """Run a SELECT and return list[dict]. psycopg2 cursors accept Composables directly."""
    with db.get_cursor() as cur:
        cur.execute(query, params)
        return [dict(r) for r in cur.fetchall()]


def list_for_position(position: str | None = None, limit: int = 20) -> list[dict]:
    """
    Fetch role-play questions, optionally filtered by `position_applied`.
    Falls back to `SELECT *` if no `position` column exists.
    """
    table = sql.Identifier(settings.DB_TABLE_ROLE_PLAY)

    if position:
        for col in ("position", "position_applied", "role", "job_role"):
            query = sql.SQL(
                "SELECT * FROM {table} WHERE {col} ILIKE %s LIMIT %s"
            ).format(table=table, col=sql.Identifier(col))
            try:
                rows = _run(query, (f"%{position}%", limit))
                if rows:
                    log.info("[role_play] %d rows for position=%s via %s", len(rows), position, col)
                    return [_normalize(r) for r in rows]
            except Exception as exc:
                log.debug("[role_play] filter via %s failed: %s", col, exc)
                continue

    # Fallback / unfiltered
    query = sql.SQL("SELECT * FROM {table} LIMIT %s").format(table=table)
    try:
        rows = _run(query, (limit,))
        log.info("[role_play] %d rows (unfiltered)", len(rows))
        return [_normalize(r) for r in rows]
    except Exception as exc:
        log.warning("[role_play] table query failed (table may not exist yet): %s", exc)
        return []


def find_by_id(rp_id: str) -> dict | None:
    """Look up a single role-play question by id."""
    table = sql.Identifier(settings.DB_TABLE_ROLE_PLAY)
    for id_col in ("id", "role_play_id", "rp_id"):
        query = sql.SQL(
            "SELECT * FROM {table} WHERE {col}::text = %s LIMIT 1"
        ).format(table=table, col=sql.Identifier(id_col))
        try:
            rows = _run(query, (str(rp_id),))
            if rows:
                return _normalize(rows[0])
        except Exception as exc:
            log.debug("[role_play] id lookup via %s failed: %s", id_col, exc)
    return None