"""
build_kb.py — parse the curated coach-questions Excel into a normalized knowledge base.

Input:  knowledge/Chat Shortcuts Barkha Neel chatbot.xlsx  (5 sheets, heterogeneous layouts)
Output: data/kb.json   — flat list of {id, question, answer, topic, tags, shortcut, source, coach_note}

No API key needed. Run: python3 scripts/build_kb.py
This is the curated, approved knowledge ONLY — no Freshchat transcripts (Ram's call).
"""
import json
import os
import re
import openpyxl

# Program-specificity markers. An answer that makes a hard Relaxed claim goes to P9, a hard Original
# claim to P3, one that mentions both stays general, none stays the sheet default.
RELAXED_MARK = re.compile(r"\b42\b|quitsure \(?relaxed\)?|relaxed program|42[- ]?day", re.I)
ORIGINAL_MARK = re.compile(r"6[- ]?12[- ]?days?|6 to 12 days|6[- ]?day( program| quit)?|"
                           r"\bday 6\b|only 6 days|days 3 (through|to) 6|complete it in 6 days|in 6 days", re.I)

# Explicit overrides from the Fable KB tag audit (docs/KB_AUDIT_FINDINGS.md), by entry id.
# NOTE: ids are positional to the current Excel snapshot; re-verify if the sheet is re-ordered.
TAG_OVERRIDE = {
    # -> P9 (Relaxed-specific answers wrongly reaching Original users)
    34: ["P9"], 43: ["P9"], 46: ["P9"], 55: ["P9"], 45: ["P9"],
    # -> all (switch/comparison/generic entries the tag was hiding from their audience)
    32: ["all"],  # generic "what content each day" — nothing Relaxed-specific; P3 users need it
    64: ["all"], 66: ["all"], 67: ["all"], 73: ["all"], 74: ["all"], 42: ["all"],
    121: ["all"], 431: ["all"], 56: ["all"], 30: ["all"], 71: ["all"], 65: ["all"],
    75: ["all"], 406: ["all"], 435: ["all"],
    # -> P3 (Original-specific: Day 6 / Day 3-4 module structure / 10-mindful / 6-12 days)
    289: ["P3"], 320: ["P3"], 309: ["P3"], 423: ["P3"], 456: ["P3"], 152: ["P3"],
    155: ["P3"], 160: ["P3"], 163: ["P3"], 184: ["P3"], 195: ["P3"], 198: ["P3"],
    199: ["P3"], 200: ["P3"], 201: ["P3"], 224: ["P3"], 81: ["P3"], 15: ["P3"],
}
EXCLUDE_IDS = {
    # coach-instruction SOPs (not user Q&A)
    140, 141, 142, 221, 222, 226, 228, 249, 302, 128, 130, 132, 133, 136, 137, 468,
    301, 303, 124, 125, 138, 167, 168, 211, 452,
    # internal leaks / false-capability claims / paywall bypass
    112, 79, 111, 225, 97, 178, 448,
}

# Answer corrections sourced from the live refund-form eligibility engine
# (quitsure-refund-form/app.py: REFUND_WINDOW {Original:21, Relaxed:60}, REQUIRED_DAYS {6,42},
#  first-subscription-only). id 72 previously stated "within 60 days" flat, which is wrong for
#  Original (21 days). Route to the form so the bot never asserts a per-program number incorrectly.
ANSWER_OVERRIDE = {
    72: (
        "First of all, I want you to know that it's okay. Quitting is a journey, and completing the "
        "whole program shows real dedication.\n\n"
        "Our money-back guarantee covers your first subscription if you completed the full program "
        "and request within the refund window from your purchase date. The window is 21 days for the "
        "QuitSure (Original) program and 60 days for the QuitSure (Relaxed) program.\n\n"
        "The quickest way to check and request is our 2-minute refund eligibility form (please use "
        "the same email you registered with):\n"
        "https://web.quitsure.app/refund\n\n"
        "Our team will review and get back to you within 7 working days if eligible. You can read the "
        "full policy here: https://www.quitsure.app/refunds"
    ),
}


def program_tag(blob, default):
    r = bool(RELAXED_MARK.search(blob))
    o = bool(ORIGINAL_MARK.search(blob))
    if r and o:
        return ["all"]      # explicitly compares both programs
    if r:
        return ["P9"]
    if o:
        return ["P3"]
    return default


# Rows the AI must NOT send verbatim:
PH_RE = re.compile(r'%[\w.]+%|_{3,}|\{\{|\[(?:name|user|user name|amount|date|link|coach)\]', re.I)
CO_RE = re.compile(r'only to be sent|to be sent by|do not send|don.t send this|'
                   r'after the issue has been resolved|escalate to (?:tiasa|nidhi|team)', re.I)

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
XLSX = os.path.join(ROOT, "knowledge", "Chat Shortcuts Barkha Neel chatbot.xlsx")
OUT = os.path.join(ROOT, "data", "kb.json")


def no_dashes(s):
    """Remove em/en dashes (user preference: never in replies)."""
    s = s.replace(" — ", ", ").replace(" – ", ", ").replace("—", ", ").replace("–", "-")
    return s.replace(" ,", ",").replace(",,", ",")


def clean(v):
    if v is None:
        return ""
    return no_dashes(" ".join(str(v).split()).strip())


def clean_multiline(v):
    """Preserve paragraph breaks but trim trailing whitespace per line."""
    if v is None:
        return ""
    return no_dashes("\n".join(line.rstrip() for line in str(v).splitlines()).strip())


def is_header_question(q):
    return q.strip().lower() in {"question", "q", ""}


def parse():
    wb = openpyxl.load_workbook(XLSX, data_only=True)
    entries = []

    def add(question, answer, topic, programs, platform="all",
            tags="", shortcut="", source="", coach_note=""):
        q = clean(question)
        a = clean_multiline(answer)
        note = clean_multiline(coach_note)
        # strip a leading "Q:" the PQP sheet uses
        if q.lower().startswith("q:"):
            q = q[2:].strip()
        if not q or not a or is_header_question(q):
            return
        entries.append({
            "question": q,
            "answer": a,
            "topic": clean(topic),
            "programs": programs,          # which programs this answer applies to
            "platform": platform,          # all | ios | android
            "tags": clean(tags),
            "shortcut": clean(shortcut),
            "source": source,
            "coach_note": note,
            # rows the AI must NOT send verbatim (kept in KB, excluded from the answer index)
            "coach_only": bool(CO_RE.search(a) or CO_RE.search(note)),
            "has_placeholder": bool(PH_RE.search(a)),
        })

    # Program tagging is content-based via program_tag() for every sheet: Relaxed-specific -> P9,
    # Original-specific -> P3, both -> all, neither -> the sheet's natural default.

    # --- PQP Shortcuts: A=section(carry), B=question, C=answer. Post-quit default = both programs.
    section = ""
    for row in wb["PQP Shortcuts"].iter_rows(values_only=True):
        if clean(row[0]):
            section = clean(row[0])
        add(row[1], row[2], topic=f"PQP / Stay Free — {section}" if section else "PQP / Stay Free",
            programs=program_tag(clean(row[1]) + " " + clean_multiline(row[2]), ["P3", "P9"]),
            source="PQP Shortcuts")

    # --- LTP Shortcuts: A=section(carry), B=question, E=answer. Default = all (only Relaxed-specific -> P9).
    section = ""
    for row in wb["LTP Shortcuts"].iter_rows(values_only=True):
        if clean(row[0]):
            section = clean(row[0])
        add(row[1], row[4], topic=f"LTP / Relaxed — {section}" if section else "LTP / Relaxed",
            programs=program_tag(clean(row[1]) + " " + clean_multiline(row[4]), ["all"]),
            source="LTP Shortcuts")

    # --- Tech shortcuts: C=device, D=scenario, F=response, E=code, G=coach-do, A=status. Default = all.
    for row in wb["Tech shortcuts"].iter_rows(values_only=True):
        if clean(row[0]) == "Status":  # header
            continue
        dev = clean(row[2]).lower() if len(row) > 2 else ""
        platform = "ios" if ("ios" in dev or "iphone" in dev) else ("android" if "android" in dev else "all")
        add(row[3], row[5], topic="Technical", platform=platform,
            programs=program_tag(clean(row[3]) + " " + clean_multiline(row[5]), ["all"]),
            shortcut=row[4] if len(row) > 4 else "",
            source="Tech shortcuts", coach_note=row[6] if len(row) > 6 else "")

    # --- Final shortcut sheet: B=question, F=New Answer (fallback G=Old), C=topic, D=shortcut, E=tags. Default = all.
    for row in wb["Final shortcut sheet"].iter_rows(values_only=True):
        if clean(row[1]) == "Question":  # header
            continue
        answer = row[5] if clean(row[5]) else (row[6] if len(row) > 6 else "")
        add(row[1], answer, topic=clean(row[2]), tags=row[4] if len(row) > 4 else "",
            programs=program_tag(clean(row[1]) + " " + clean_multiline(answer), ["all"]),
            shortcut=row[3] if len(row) > 3 else "", source="Final shortcut sheet")

    # --- New queries: B=question, E=Answer (fallback F), C=topic, D=tags, G=coach instructions. Default = all.
    for row in wb["New queries"].iter_rows(values_only=True):
        if clean(row[1]) == "Question":  # header
            continue
        answer = row[4] if clean(row[4]) else (row[5] if len(row) > 5 else "")
        add(row[1], answer, topic=clean(row[2]), tags=row[3] if len(row) > 3 else "",
            programs=program_tag(clean(row[1]) + " " + clean_multiline(answer), ["all"]),
            source="New queries", coach_note=row[6] if len(row) > 6 else "")

    # assign ids
    for i, e in enumerate(entries):
        e["id"] = i

    # apply the Fable tag audit overrides (by stable id)
    n_ids = len(entries)
    for oid in list(TAG_OVERRIDE) + list(EXCLUDE_IDS):
        if oid >= n_ids:
            print(f"  WARN: override id {oid} out of range ({n_ids} entries) — ignored")
    for e in entries:
        if e["id"] in TAG_OVERRIDE:
            e["programs"] = TAG_OVERRIDE[e["id"]]
        if e["id"] in EXCLUDE_IDS:
            e["coach_only"] = True
        if e["id"] in ANSWER_OVERRIDE:
            e["answer"] = ANSWER_OVERRIDE[e["id"]]
    return entries


if __name__ == "__main__":
    entries = parse()
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    with open(OUT, "w") as f:
        json.dump(entries, f, indent=2, ensure_ascii=False)

    by_source = {}
    for e in entries:
        by_source[e["source"]] = by_source.get(e["source"], 0) + 1
    print(f"Wrote {len(entries)} Q&A entries to {OUT}")
    for s, n in by_source.items():
        print(f"  {n:4d}  {s}")