"""DB access for QuitSure Discovery personalization.

Reuses the chatbot-onboarding pattern: PyMySQL, DictCursor, a fresh connection
per call, env-based creds (DB_HOST / DB_PORT / DB_DATABASE / DB_USERNAME /
DB_PASSWORD, loaded from .env by app.py). Read-only.

get_profile(hashed_email) -> dict | None, used by GET /discovery/api/me so the
remarketing link ?u=<vHashedEmail> personalizes the funnel from screen 1.
"""
import os

import pymysql
import pymysql.cursors


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,
    )


# 1:1 joins on iUserID. Standard safety filters. Identity = vHashedEmail.
_LOOKUP = """
SELECT u.iUserID, u.iActiveDayID,
       p.vName, p.decSmokePerDay, p.vCurrencyCode,
       d.vGender, d.vAge, c.vCountry
FROM tbl_Users u
LEFT JOIN tbl_UserProfile p ON p.iUserID = u.iUserID
LEFT JOIN tbl_UserData   d ON d.iUserID = u.iUserID
LEFT JOIN tbl_UserConfig c ON c.iUserID = u.iUserID
WHERE u.vHashedEmail = %s AND u.bDeleted = 0 AND u.bTestUser = 0
LIMIT 1;
"""


def _first_name(v):
    if not v or not v.strip():
        return None
    return v.strip().split()[0].capitalize()


def _gender(v):
    s = (v or "").strip().lower()
    if s.startswith("male"):
        return "male"
    if s.startswith("female"):
        return "female"
    return None  # others / junk / null → no gendered voice


def _cigs(v):
    """Sanity-bound: only trust 1–60/day (per personalization rules)."""
    try:
        x = float(v)
        return round(x) if 1 <= x <= 60 else None
    except (TypeError, ValueError):
        return None


def _under_18(age):
    return bool(age) and "< 18" in age


def insert_clickstream(session_id, events, meta=None):
    """Batch-insert step events into tbl_DiscoveryClickstream. events: list of
    {step, stepNumber, answer, timeSpentSec}. Returns inserted count (0 on error)."""
    if not session_id or not events:
        return 0
    meta = meta or {}
    rows = [
        (
            session_id,
            str(e.get("step", ""))[:30],
            int(e.get("stepNumber", 0) or 0),
            (str(e["answer"])[:2000] if e.get("answer") not in (None, "") else None),
            int(e.get("timeSpentSec", 0) or 0),
            (meta.get("deviceType") or None),
            (meta.get("country") or None),
            (meta.get("source") or None),
            (meta.get("medium") or None),
            (meta.get("campaign") or None),
        )
        for e in events[:100]
    ]
    sql = (
        "INSERT INTO tbl_DiscoveryClickstream "
        "(vSessionId, vStep, iStepNumber, vAnswer, iTimeSpentSec, vDeviceType, vCountry, vSource, vMedium, vCampaign) "
        "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    )
    try:
        with _conn() as conn:
            with conn.cursor() as cur:
                cur.executemany(sql, rows)
            conn.commit()
        return len(rows)
    except Exception:
        return 0  # table missing / transient → never break the funnel


# Columns the session summary may set (snake_case key -> DB column).
_SESSION_COLS = {
    "iUserID": "iUserID", "vHashedEmail": "vHashedEmail", "vTomorrowCount": "vTomorrowCount",
    "vExcuses": "vExcuses", "iExcuseCount": "iExcuseCount", "vFeeling": "vFeeling",
    "bSmokeNow": "bSmokeNow", "vPath": "vPath", "vVoiceGender": "vVoiceGender", "vTrigger": "vTrigger",
    "vName": "vName", "iCigsPerDay": "iCigsPerDay", "vCurrency": "vCurrency", "vCountry": "vCountry",
    "bReachedOffer": "bReachedOffer", "bConverted": "bConverted", "vConvertProgram": "vConvertProgram",
    "vExitReason": "vExitReason", "vOnBoardPlatform": "vOnBoardPlatform", "vDeviceType": "vDeviceType",
    "vSource": "vSource", "vMedium": "vMedium", "vCampaign": "vCampaign", "bCompleted": "bCompleted",
}


def upsert_session(session_id, data):
    """Insert or update one tbl_DiscoveryOnboarding row per session (keyed by vSessionId).
    Sets dConvertedAt/dOfferSeenAt server-side when the matching flag flips on. Best-effort."""
    if not session_id:
        return False
    # Treat empty strings as absent → write NULL, never blanks.
    fields = {col: data[k] for k, col in _SESSION_COLS.items() if k in data and data[k] not in (None, "")}
    try:
        with _conn() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT id FROM tbl_DiscoveryOnboarding WHERE vSessionId=%s LIMIT 1", (session_id,))
                existing = cur.fetchone()
                sets, vals = [], []
                for col, v in fields.items():
                    sets.append(f"`{col}`=%s"); vals.append(v)
                if fields.get("bReachedOffer"):
                    sets.append("`dOfferSeenAt`=COALESCE(`dOfferSeenAt`, NOW())")
                if fields.get("bConverted"):
                    sets.append("`dConvertedAt`=COALESCE(`dConvertedAt`, NOW())")
                if existing:
                    cur.execute(f"UPDATE tbl_DiscoveryOnboarding SET {', '.join(sets)} WHERE id=%s", vals + [existing["id"]])
                else:
                    cols = ["vSessionId"] + list(fields.keys())
                    ph = ["%s"] * len(cols)
                    extra = ""
                    if fields.get("bReachedOffer"):
                        cols.append("dOfferSeenAt"); ph.append("NOW()")
                    if fields.get("bConverted"):
                        cols.append("dConvertedAt"); ph.append("NOW()")
                    colsql = ", ".join(f"`{c}`" for c in cols)
                    cur.execute(f"INSERT INTO tbl_DiscoveryOnboarding ({colsql}) VALUES ({', '.join(ph)})",
                                [session_id] + list(fields.values()))
            conn.commit()
        return True
    except Exception:
        return False


def get_profile(hashed_email):
    """Return personalization dict for a hashed email, or None if not found."""
    if not hashed_email or len(hashed_email) != 64:
        return None
    with _conn() as conn:
        with conn.cursor() as cur:
            cur.execute(_LOOKUP, (hashed_email,))
            row = cur.fetchone()
    if not row:
        return None
    country = (row.get("vCountry") or "").strip()
    code = (row.get("vCurrencyCode") or "").strip().upper()
    is_india = country.lower() in ("india", "in") or code == "INR"
    currency = "INR" if is_india else ("USD" if code == "USD" else None)
    return {
        "iUserID": row.get("iUserID"),
        "name": _first_name(row.get("vName")),
        "cigsPerDay": _cigs(row.get("decSmokePerDay")),
        "gender": _gender(row.get("vGender")),
        "country": country or None,
        "currency": currency,           # None → frontend falls back to timezone guess
        "eligible": not _under_18(row.get("vAge")),  # under-18 excluded from sends (module #5)
    }