"""
Reuse cache helpers for ``user_question_log`` (normalized question + executed='Y').

Dedup strategy: application-level merge on (user_id, question_normalized) for successful
rows — see ``try_merge_success_user_question_log`` used from ``create_user_question_log``.
SQLite unique indexes on nullable columns are avoided; ``user_id`` is stored as normalized text.
"""
from __future__ import annotations

import json
import re
from typing import Any, Dict, List, Optional, Union

from sqlalchemy import desc

from app.core.config import settings
from app.core.database import SessionLocal
from app.models.question import UserQuestionLog
from app.services.log_field_serialization import (
    api_field_to_db_value as _shared_api_field_to_db_value,
    sql_to_db_value as _shared_sql_to_db_value,
)


def normalize_question_for_cache(question: Optional[str]) -> str:
    """trim, lower, collapse whitespace to single underscores."""
    if question is None:
        return ""
    s = str(question).strip().lower()
    if not s:
        return ""
    s = re.sub(r"\s+", "_", s)
    return s


def canonical_agent_id_for_cache(agent_id_val: Any) -> str:
    """Stable string comparable to ``UserQuestionLog.agent_id`` (JSON list or plain id)."""
    if agent_id_val is None:
        return ""
    if isinstance(agent_id_val, list):
        ids = [str(x).strip() for x in agent_id_val if str(x).strip()]
        return json.dumps(ids) if len(ids) > 1 else (ids[0] if ids else "")
    s = str(agent_id_val).strip()
    if s.startswith("["):
        try:
            parsed = json.loads(s)
            if isinstance(parsed, list):
                ids = [str(x).strip() for x in parsed if str(x).strip()]
                return json.dumps(ids) if len(ids) > 1 else (ids[0] if ids else "")
        except json.JSONDecodeError:
            pass
    return s


def parse_agent_ids_from_log_column(agent_id_val: Any) -> List[str]:
    """Ordered agent ids as stored in ``user_question_log.agent_id``."""
    if agent_id_val is None:
        return []
    if isinstance(agent_id_val, list):
        return [str(x).strip() for x in agent_id_val if str(x).strip()]
    s = str(agent_id_val).strip()
    if not s:
        return []
    if s.startswith("["):
        try:
            parsed = json.loads(s)
            if isinstance(parsed, list):
                return [str(x).strip() for x in parsed if str(x).strip()]
        except json.JSONDecodeError:
            return []
    return [s]


def _row_has_executable_sql(sql_val: Any) -> bool:
    if sql_val is None:
        return False
    if isinstance(sql_val, str):
        t = sql_val.strip().upper()
        return bool(t and t not in ("N", "NONE", "[]", "{}"))
    if isinstance(sql_val, (dict, list)):
        return bool(sql_val)
    return True


def _row_has_executable_api(row: UserQuestionLog) -> bool:
    ep = (row.api_endpoint or "").strip().upper()
    pr = (row.api_params or "").strip().upper()
    if not ep or ep in ("N", "NONE", "[]", ""):
        return False
    if not pr or pr in ("N", "NONE", "[]", ""):
        return False
    return True


def row_has_executable_payload(row: UserQuestionLog) -> bool:
    return _row_has_executable_sql(row.sql) or _row_has_executable_api(row)


def find_reusable_log_row(
    *,
    question_normalized: str,
    user_id: Optional[str],
) -> Optional[UserQuestionLog]:
    """
    Latest completed row: question_normalized + executed='Y' + user_id match (exact text).
    Lookup does not filter on agent_id.
    """
    if not question_normalized:
        return None
    uid = str(user_id).strip() if user_id is not None else ""
    db = SessionLocal()
    try:
        q = (
            db.query(UserQuestionLog)
            .filter(UserQuestionLog.question_normalized == question_normalized)
            .filter(UserQuestionLog.executed == "Y")
            .filter(UserQuestionLog.user_id == uid)
            .order_by(desc(UserQuestionLog.updated_at), desc(UserQuestionLog.id))
        )
        row = q.first()
        if row is None or not row_has_executable_payload(row):
            return None
        return row
    finally:
        db.close()


def try_merge_success_user_question_log(
    *,
    user_id: str,
    question_normalized: str,
    agent_id: str,
    question: str,
    sql: Any,
    chart: str,
    web: str,
    executed: str,
    execution_time: str,
    api_endpoint: str,
    api_params: str,
    api_response_validator: str,
) -> Optional[int]:
    """
    If cache is enabled and executed is success ('Y'), update the newest row for
    (user_id, question_normalized) instead of inserting a duplicate.
    """
    if not getattr(settings, "ENABLE_USER_QUESTION_LOG_CACHE", False):
        return None
    if executed != "Y":
        return None
    qn = (question_normalized or "").strip()
    if not qn:
        return None
    uid = str(user_id).strip() if user_id is not None else ""

    # Coerce using save-prompt serializer semantics.
    sql_value = _shared_sql_to_db_value(sql, none_default=None)
    if sql_value is None or (isinstance(sql_value, str) and not sql_value.strip()):
        sql_value = "N"

    if agent_id is None:
        agent_id_value = ""
    elif isinstance(agent_id, list):
        agent_id_value = json.dumps(agent_id)
    else:
        agent_id_value = str(agent_id)

    from datetime import datetime

    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    db = SessionLocal()
    try:
        row = (
            db.query(UserQuestionLog)
            .filter(UserQuestionLog.user_id == uid)
            .filter(UserQuestionLog.question_normalized == qn)
            .order_by(desc(UserQuestionLog.updated_at), desc(UserQuestionLog.id))
            .first()
        )
        # No prior row for this (user_id, question_normalized) — caller should INSERT.
        if row is None:
            return None
        row.agent_id = agent_id_value
        row.question = question if question is not None else ""
        row.sql = sql_value
        row.chart = chart if chart is not None else "N"
        row.web = web if web is not None else "N"
        row.executed = executed
        row.execution_time = execution_time
        row.api_endpoint = _shared_api_field_to_db_value(api_endpoint, none_default="") or ""
        row.api_params = _shared_api_field_to_db_value(api_params, none_default="") or ""
        row.api_response_validator = (
            _shared_api_field_to_db_value(api_response_validator, none_default="") or ""
        )
        row.updated_at = current_time
        db.commit()
        db.refresh(row)
        return row.id
    except Exception:
        db.rollback()
        return None
    finally:
        db.close()


def _request_str_entry(val: Any) -> str:
    """Stable request value: dict/list -> JSON string, scalar -> str."""
    if isinstance(val, (dict, list)):
        return json.dumps(val)
    return str(val)


def _sql_trace_rows_from_payload(val: Any) -> List[Dict[str, str]]:
    """
    Keep multi-agent sql_traces rows intact for replay.
    Returns [] when payload isn't trace-like.
    """
    traces: List[Any] = []
    if isinstance(val, dict) and isinstance(val.get("sql_traces"), list):
        traces = list(val.get("sql_traces") or [])
    elif isinstance(val, list):
        traces = list(val)
    else:
        return []

    out: List[Dict[str, str]] = []
    for item in traces:
        if not isinstance(item, dict):
            continue
        raw = item.get("sql")
        if raw is None:
            continue
        sql_text = raw.strip() if isinstance(raw, str) else str(raw).strip()
        if not sql_text:
            continue
        out.append(
            {
                "agent_id": str(item.get("agent_id") or "").strip(),
                "sql": sql_text,
                "sub_question": str(item.get("sub_question") or "").strip(),
            }
        )
    return out


def coerce_sql_field_for_request(sql_val: Any) -> Optional[Union[str, List[Any]]]:
    """Shape ``request.sql`` from a log cell (string or SQL trace rows)."""
    if sql_val is None:
        return None
    if isinstance(sql_val, list):
        trace_rows = _sql_trace_rows_from_payload(sql_val)
        if trace_rows:
            return trace_rows
        out = [_request_str_entry(x) for x in sql_val if str(x).strip()]
        if len(out) == 1:
            return out[0]
        return out if out else None
    if isinstance(sql_val, dict):
        trace_rows = _sql_trace_rows_from_payload(sql_val)
        if trace_rows:
            return trace_rows
        return json.dumps(sql_val)
    s = str(sql_val).strip()
    if not s or s.upper() in ("N", "NONE"):
        return None
    if (s.startswith("[") and s.endswith("]")) or (s.startswith("{") and s.endswith("}")):
        try:
            parsed = json.loads(s)
            trace_rows = _sql_trace_rows_from_payload(parsed)
            if trace_rows:
                return trace_rows
            if isinstance(parsed, list) and parsed:
                strs = [_request_str_entry(x) for x in parsed if str(x).strip()]
                if len(strs) == 1:
                    return strs[0]
                return strs if len(strs) > 1 else None
        except json.JSONDecodeError:
            pass
    return s


def coerce_api_field_for_request(val: Any) -> Optional[Union[str, List[str]]]:
    if val is None:
        return None
    if isinstance(val, list):
        out = [_request_str_entry(x) for x in val if str(x).strip()]
        if len(out) == 1:
            return out[0]
        return out if out else None
    if isinstance(val, dict):
        return json.dumps(val)
    s = str(val).strip()
    if not s or s.upper() in ("N", "NONE", "[]"):
        return None
    if (s.startswith("[") and s.endswith("]")) or (s.startswith("{") and s.endswith("}")):
        try:
            parsed = json.loads(s)
            if isinstance(parsed, list):
                out = [_request_str_entry(x) for x in parsed if str(x).strip()]
                if len(out) == 1:
                    return out[0]
                return out if out else None
            if isinstance(parsed, dict):
                return json.dumps(parsed)
        except json.JSONDecodeError:
            pass
    return s


def insert_pending_user_question_log(
    *,
    user_id: str,
    question: str,
    agent_id: Any,
    start_time: str,
) -> Optional[int]:
    """
    Optional pending row (executed='N') for in-flight dedup — callers may omit.
    Not wired into talkback by default to avoid extra writes.
    """
    if not getattr(settings, "ENABLE_USER_QUESTION_LOG_CACHE", False):
        return None
    qn = normalize_question_for_cache(question)
    if not qn:
        return None
    uid = str(user_id).strip() if user_id is not None else ""
    if agent_id is None:
        agent_id_value = ""
    elif isinstance(agent_id, list):
        agent_id_value = json.dumps(agent_id)
    else:
        agent_id_value = str(agent_id)
    from datetime import datetime

    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    db = SessionLocal()
    try:
        row = UserQuestionLog(
            user_id=uid,
            agent_id=agent_id_value,
            question=question if question is not None else "",
            sql="N",
            chart="N",
            web="N",
            executed="N",
            execution_time="0",
            api_endpoint="",
            api_params="",
            api_response_validator="",
            question_normalized=qn,
            created_at=current_time,
            updated_at=current_time,
        )
        db.add(row)
        db.commit()
        db.refresh(row)
        return row.id
    except Exception:
        db.rollback()
        return None
    finally:
        db.close()
