"""Code free-text survey comments into themes with a transparent keyword list, and mark tone.
Every rule is in THEMES so a reviewer can see why a comment got a code. Usage: python code_comments.py survey_comments.csv"""
import csv, re, sys
from collections import Counter
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
THEMES = {"Price": r"expensive|price|pricey|cheaper|cost", "Booking": r"book", "Timeliness": r"late|behind schedule|waited|on time",
          "Staff": r"staff|technician|tech\b|team|rude|friendly", "Communication": r"sms|call back|email|update|reminder|explain",
          "Quality": r"missed|finished properly|fix|excellent work|quality"}
POSITIVE = r"great|friendly|excellent|all good"; EMPTY = r"^\s*(n/?a|none|nothing|-)?\s*[.!]*\s*$"
def code(text):
    t = text.lower().strip()
    if re.match(EMPTY, t): return [], "No comment"
    themes = [k for k, pat in THEMES.items() if re.search(pat, t)]
    tone = "Positive" if re.search(POSITIVE, t) and not re.search(r"rude|late|missed|hard|couldn't|never|unclear|expensive|timing out|fix|didn't", t) else ("Mixed" if re.search(POSITIVE, t) else "Negative")
    if "all good" in t: return [], "Positive"
    return themes or ["Other (review)"], tone
def main(fn):
    rows = list(csv.DictReader(open(fn))); out = []; cnt = Counter(); tones = Counter()
    for r in rows:
        th, tone = code(r["comment"]); out.append([r["response_id"], r["comment"].strip(), "; ".join(th), tone]); tones[tone] += 1
        for t in th: cnt[t] += 1
    wb = Workbook(); ws = wb.active; ws.title = "Coded comments"; ws.append(["Response", "Comment", "Themes", "Tone"]); [ws.append(o) for o in out]
    s = wb.create_sheet("Theme counts"); s.append(["Theme", "Comments", "Share of coded comments"])
    coded = sum(1 for o in out if o[2]);
    for t, n in cnt.most_common(): s.append([t, n, round(n / coded, 3)])
    s.append([]); s.append(["Tone", "Comments"]); [s.append([k, v]) for k, v in tones.most_common()]
    r = wb.create_sheet("Rules"); r.append(["Theme", "Keyword pattern (regex)"]); [r.append([k, v]) for k, v in THEMES.items()]
    for sh in wb:
        for c in sh[1]: c.font = Font(bold=True); c.fill = PatternFill("solid", fgColor="DDEBF7")
        sh.column_dimensions["A"].width = 18; sh.column_dimensions["B"].width = 60; sh.column_dimensions["C"].width = 26
    wb.save("coded_comments.xlsx")
    print(len(rows), "comments;", coded, "coded;", dict(cnt.most_common()), dict(tones))
if __name__ == "__main__": main(sys.argv[1] if len(sys.argv) > 1 else "survey_comments.csv")