"""DB access for QuitSure AI Support (logging chats + feedback).

Follows the chatbot-onboarding / discovery pattern: PyMySQL, DictCursor, a fresh
connection per call, env creds (DB_HOST / DB_PORT / DB_DATABASE / DB_USERNAME /
DB_PASSWORD, loaded from .env by app.py). Naming follows the QuitSure convention:
tbl_ tables, Hungarian columns (iUserID, vMessage, bEscalate, dCreated ...).

Tables (tbl_SupportChatLog, tbl_SupportChatFeedback) are created by the BE/DBA team
in the test DB; see docs/DB_SCHEMA.sql. Writes are best-effort: logging must never
break a reply, so if the DB is unreachable or the tables don't exist, it no-ops.
"""
import os

import pymysql
import pymysql.cursors

# app kwarg -> DB column, for tbl_SupportChatLog
_CHAT_MAP = [
    ("message_id", "vMessageID"), ("user_id", "iUserID"), ("program", "vProgram"),
    ("platform", "vPlatform"), ("message", "vMessage"), ("retrieval_query", "vRetrievalQuery"),
    ("best_sim", "decBestSim"), ("escalate", "bEscalate"), ("escalate_reason", "vEscalateReason"),
    ("answer", "vAnswer"), ("gate", "vGate"),
]


def _conn():
    return pymysql.connect(
        host=os.environ["DB_HOST"],
        port=int(os.environ.get("DB_PORT", 3306)),
        user=os.environ["DB_USERNAME"],
        password=os.environ["DB_PASSWORD"],
        database=os.environ["DB_DATABASE"],
        cursorclass=pymysql.cursors.DictCursor,
        connect_timeout=5,
        read_timeout=5,
        write_timeout=5,
    )


def enabled():
    return bool(os.environ.get("DB_HOST"))


def _int_or_none(v):
    try:
        return int(v)
    except (TypeError, ValueError):
        return None


def log_chat(**f):
    if not enabled():
        return
    vals = {db: f.get(app) for app, db in _CHAT_MAP}
    vals["iUserID"] = _int_or_none(vals["iUserID"])
    cols = [db for _, db in _CHAT_MAP]
    try:
        conn = _conn()
        try:
            with conn.cursor() as cur:
                cur.execute(f"INSERT INTO tbl_SupportChatLog ({','.join(cols)}) "
                            f"VALUES ({','.join('%(' + c + ')s' for c in cols)})", vals)
            conn.commit()
        finally:
            conn.close()
    except Exception:
        pass  # logging must never break a reply


def log_feedback(ts, message_id, user_id, rating, note):
    if not enabled():
        return
    try:
        conn = _conn()
        try:
            with conn.cursor() as cur:
                # vMessageID is NOT NULL; never drop a rating just because the caller omitted the id.
                cur.execute("INSERT INTO tbl_SupportChatFeedback (vMessageID, iUserID, vRating, vNote) "
                            "VALUES (%s, %s, %s, %s)", (message_id or "unknown", _int_or_none(user_id), rating, note))
            conn.commit()
        finally:
            conn.close()
    except Exception:
        pass


def get_stats():
    empty = {"total": 0, "answered": 0, "declined": 0, "escalated": 0,
             "reasons": [], "feedback": {"up": 0, "down": 0}, "downvotes": []}
    if not enabled():
        return empty
    try:
        conn = _conn()
        try:
            with conn.cursor() as cur:
                def one(sql):
                    cur.execute(sql)
                    return list(cur.fetchone().values())[0]
                total = one("SELECT COUNT(*) FROM tbl_SupportChatLog")
                answered = one("SELECT COUNT(*) FROM tbl_SupportChatLog WHERE bEscalate=0 AND "
                               "(vEscalateReason IS NULL OR vEscalateReason='greeting')")
                declined = one("SELECT COUNT(*) FROM tbl_SupportChatLog WHERE vEscalateReason='out_of_scope'")
                escalated = one("SELECT COUNT(*) FROM tbl_SupportChatLog WHERE bEscalate=1")
                cur.execute("SELECT vEscalateReason, COUNT(*) AS c FROM tbl_SupportChatLog WHERE bEscalate=1 "
                            "GROUP BY vEscalateReason ORDER BY c DESC")
                reasons = [{"reason": r["vEscalateReason"] or "unknown", "count": r["c"]} for r in cur.fetchall()]
                cur.execute("SELECT vRating, COUNT(*) AS c FROM tbl_SupportChatFeedback GROUP BY vRating")
                fb = {r["vRating"]: r["c"] for r in cur.fetchall()}
                cur.execute("SELECT f.dCreated, f.vNote, c.vMessage, c.vAnswer, c.vEscalateReason "
                            "FROM tbl_SupportChatFeedback f "
                            "LEFT JOIN tbl_SupportChatLog c ON f.vMessageID=c.vMessageID "
                            "WHERE f.vRating='down' ORDER BY f.dCreated DESC LIMIT 25")
                downs = [{"ts": str(d["dCreated"]), "note": d["vNote"], "message": d["vMessage"],
                          "answer": (d["vAnswer"] or "")[:300], "reason": d["vEscalateReason"]}
                         for d in cur.fetchall()]
            return {"total": total, "answered": answered, "declined": declined, "escalated": escalated,
                    "reasons": reasons, "feedback": {"up": fb.get("up", 0), "down": fb.get("down", 0)},
                    "downvotes": downs}
        finally:
            conn.close()
    except Exception:
        return empty