"""
services/participant_repo.py
============================
Data-access layer for the `participant` table in the e-Recruit Postgres DB.

Responsibilities:
  • Look up a participant by `access_key` (case-insensitive).
  • Log the full DB row during initial bring-up (LOG_DB_PAYLOAD env flag).
  • Map raw DB columns to a canonical, route-friendly dict.
  • Be tolerant of column-name variations (e.g., participant_id vs id,
    candidate_name vs name, stage vs current_stage).
  • Optionally update last-login timestamp without breaking if the column
    doesn't exist in the target schema.

Nothing else in the codebase should query the participant table directly.
"""
from __future__ import annotations

from datetime import datetime, timezone
from typing import Any

from psycopg2 import sql

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

log = get_logger(__name__)


# ── Column aliases ────────────────────────────────────────────────────────────
# We don't know the exact schema in advance, so we tolerate several
# common column names per logical field. The first match wins.

_ID_COLS    = ("id", "participant_id", "candidate_id", "uid", "uuid")
_NAME_COLS  = ("name", "candidate_name", "participant_name", "full_name", "display_name")
_EMAIL_COLS = ("email", "email_id", "email_address")
_PHONE_COLS = ("phone", "mobile", "mobile_no", "contact_no")
_STAGE_COLS = ("current_stage", "stage", "stage_no", "interview_stage")
_KEY_COLS   = ("access_key", "accesskey", "access_code", "login_key")
_POSITION_COLS = ("position_applied", "position", "role_applied", "job_role", "designation")
_SKILLS_COLS = ("skill_set", "skills", "skill")


def _first_present(row: dict, candidates: tuple[str, ...], default: Any = None) -> Any:
    """Return the value from the first key present in `row`, else default."""
    for c in candidates:
        if c in row and row[c] is not None:
            return row[c]
    return default


def _coerce_skills(value: Any) -> list[str]:
    """Skills column might be text[] or comma-separated string. Normalize to list."""
    if value is None:
        return []
    if isinstance(value, list):
        return [str(v).strip() for v in value if str(v).strip()]
    if isinstance(value, str):
        return [s.strip() for s in value.split(",") if s.strip()]
    return [str(value)]


def _coerce_stage(value: Any) -> int:
    """Stage column might be int, str or NULL. Default to 1 (Assessment)."""
    if value is None:
        return 1
    try:
        return int(value)
    except (TypeError, ValueError):
        log.warning("Could not parse current_stage=%r — defaulting to 1", value)
        return 1


def _normalize(row: dict) -> dict:
    """
    Map a raw participant row → canonical dict used by the rest of the app.
    Original raw row is preserved under `_raw` for debugging.
    """
    pid       = _first_present(row, _ID_COLS, "")
    name      = _first_present(row, _NAME_COLS, "Candidate")
    email     = _first_present(row, _EMAIL_COLS, "")
    phone     = _first_present(row, _PHONE_COLS, "")
    stage     = _coerce_stage(_first_present(row, _STAGE_COLS, 1))
    key       = _first_present(row, _KEY_COLS, "")
    position  = _first_present(row, _POSITION_COLS, "")
    skills    = _coerce_skills(_first_present(row, _SKILLS_COLS, None))

    return {
        "id":               str(pid) if pid is not None else "",
        "name":             str(name or "Candidate"),
        "email":            str(email or ""),
        "phone":            str(phone or ""),
        "current_stage":    stage,
        "access_key":       str(key or "").upper(),
        "position_applied": str(position or ""),
        "skill_set":        skills,
        "last_login":       row.get("last_login"),
        "last_question_attempt": row.get("last_question_attempt"),
        "_raw":             {k: (v if not isinstance(v, datetime) else v.isoformat())
                              for k, v in row.items()},
    }


# ── Public API ────────────────────────────────────────────────────────────────

def find_by_access_key(raw_key: str) -> dict | None:
    """
    SELECT * FROM <participant> WHERE UPPER(access_key) = UPPER(%s)
    Returns the normalized participant dict, or None.
    """
    if not raw_key:
        return None

    key = raw_key.strip().upper()
    table = sql.Identifier(settings.DB_TABLE_PARTICIPANT)

    # We use UPPER() on both sides so it works regardless of how the
    # value was stored (matches the brief).
    query = sql.SQL(
        "SELECT * FROM {table} WHERE UPPER(access_key) = %s LIMIT 1"
    ).format(table=table)

    log.info("[participant] login lookup → access_key=%s", key)

    try:
        with db.get_cursor() as cur:
            cur.execute(query, (key,))
            row = cur.fetchone()
    except Exception as exc:
        log.exception("[participant] DB query failed: %s", exc)
        raise

    if not row:
        log.warning("[participant] no participant found for access_key=%s", key)
        return None

    row = dict(row)

    if settings.LOG_DB_PAYLOAD:
        # Brief asks us to print/log the full DB response initially.
        log.info("[participant] raw DB row → %s", row)
        log.info("[participant] columns available → %s", sorted(row.keys()))

    normalized = _normalize(row)
    log.info(
        "[participant] login resolved → id=%s name=%s stage=%d email=%s",
        normalized["id"], normalized["name"],
        normalized["current_stage"], normalized["email"],
    )
    return normalized


def find_by_id(participant_id: str) -> dict | None:
    """Look up a participant row by primary id. Tries each known id column."""
    if not participant_id:
        return None

    table = sql.Identifier(settings.DB_TABLE_PARTICIPANT)
    last_err: Exception | None = None

    for id_col in _ID_COLS:
        query = sql.SQL(
            "SELECT * FROM {table} WHERE {col}::text = %s LIMIT 1"
        ).format(table=table, col=sql.Identifier(id_col))
        try:
            with db.get_cursor() as cur:
                cur.execute(query, (str(participant_id),))
                row = cur.fetchone()
            if row:
                return _normalize(dict(row))
        except Exception as exc:  # column doesn't exist or type mismatch
            last_err = exc
            log.debug("[participant] id lookup via %s failed: %s", id_col, exc)
            continue

    if last_err:
        log.warning("[participant] id lookup exhausted all candidates: %s", last_err)
    return None


def touch_last_login(participant_id: str) -> None:
    """
    Best-effort: update last_login = now(). If the column doesn't exist
    in the target schema, swallow the error rather than failing the login.
    """
    if not participant_id:
        return

    now = datetime.now(timezone.utc)
    table = sql.Identifier(settings.DB_TABLE_PARTICIPANT)

    for id_col in _ID_COLS:
        query = sql.SQL(
            "UPDATE {table} SET last_login = %s WHERE {col}::text = %s"
        ).format(table=table, col=sql.Identifier(id_col))
        try:
            with db.get_cursor(dict_rows=False) as cur:
                cur.execute(query, (now, str(participant_id)))
                if cur.rowcount > 0:
                    log.debug("[participant] last_login updated for %s", participant_id)
                    return
        except Exception as exc:
            # Column missing, type mismatch, etc. — try next candidate.
            log.debug("[participant] last_login update via %s failed: %s", id_col, exc)
            continue

    log.debug("[participant] last_login not updated (no compatible column / row)")