"""
services/coding_repo.py
=======================
Data-access layer for the Coding-Assessment questions table.

⚠ Schema is not finalized yet. Treat this as a scaffold:
- Table name from settings.DB_TABLE_CODING_QUESTIONS (.env).
- Returns raw rows so the caller can map them to the API shape.

When the table is finalized, add explicit normalization (problem_text,
sample_input, expected_output, difficulty, language, time_limit_seconds, …).
"""
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 _run(query: sql.Composable, params: tuple) -> list[dict]:
    with db.get_cursor() as cur:
        cur.execute(query, params)
        return [dict(r) for r in cur.fetchall()]


def list_questions(limit: int = 5,
                   difficulty: str | None = None,
                   language: str | None = None) -> list[dict]:
    """
    Fetch coding questions. Optional filters are best-effort —
    if the column doesn't exist we silently skip that filter.
    """
    table = sql.Identifier(settings.DB_TABLE_CODING_QUESTIONS)

    where_parts: list[sql.Composable] = []
    params: list = []

    if difficulty:
        where_parts.append(sql.SQL("difficulty ILIKE %s"))
        params.append(f"%{difficulty}%")
    if language:
        where_parts.append(sql.SQL("language ILIKE %s"))
        params.append(f"%{language}%")

    where_clause = (
        sql.SQL(" WHERE ") + sql.SQL(" AND ").join(where_parts)
        if where_parts else sql.SQL("")
    )
    query = sql.SQL("SELECT * FROM {table}{where} LIMIT %s").format(
        table=table, where=where_clause
    )
    params.append(limit)

    try:
        rows = _run(query, tuple(params))
        log.info("[coding] %d questions fetched (filters=%s/%s)", len(rows), difficulty, language)
        return rows
    except Exception as exc:
        log.warning("[coding] table query failed (table may not exist yet): %s", exc)
        return []


def find_by_id(question_id: str | int) -> dict | None:
    table = sql.Identifier(settings.DB_TABLE_CODING_QUESTIONS)
    for id_col in ("id", "question_id", "coding_question_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(question_id),))
            if rows:
                return rows[0]
        except Exception as exc:
            log.debug("[coding] id lookup via %s failed: %s", id_col, exc)
    return None