import csv
import json
import math
import random
import re
from collections import Counter, defaultdict
from datetime import datetime, timedelta
from pathlib import Path

BASE = Path(__file__).parent
RAW = BASE / "raw"
OUT = BASE / "output"
RAW.mkdir(exist_ok=True)
OUT.mkdir(exist_ok=True)
random.seed(42)

# Synthetic multi-source intelligence/customer-service dataset.
customers = [
    ["C001", "Fifi", "081234567890", "Ternate", "2026/09/01", "active", "20M", "120000"],
    ["C002", "Andi", "081234567891", "Ternate City", "01-09-2026", "active", "20M", "120000"],
    ["C003", "Sari", "081234567892", "Tidore", "2026-09-02", "pending", "10M", "90000"],
    ["C004", "Budi", "081234567893", "Ternate", "3 Sep 2026", "active", "50M", "250000"],
    ["C005", "Nia", "081234567894", "Jailolo", "2026-09-03", "active", "10M", "90000"],
    ["C006", "Rian", "081234567895", "Ternate", "2026-09-04", "active", "20M", "120000"],
    ["C007", "Maya", "081234567896", "Ternate", "2026-09-05", "suspended", "10M", "90000"],
    ["C008", "Doni", "081234567897", "Tidore", "2026-09-05", "active", "20M", "120000"],
    ["C009", "Lala", "081234567898", "Ternate", "2026-09-06", "active", "50M", "250000"],
    ["C010", "Umar", "081234567899", "Jailolo", "2026-09-06", "pending", "10M", "90000"],
]
with (RAW / "customers.csv").open("w", newline="", encoding="utf-8") as f:
    w = csv.writer(f); w.writerow(["customer_id","name","phone","city","event_date","status","package","monthly_fee"]); w.writerows(customers)

transactions = [
    ["T001","C001","2026-09-01","120000","paid","transfer"],
    ["T002","C002","2026-09-01","120000","paid","cash"],
    ["T003","C003","2026-09-02","90000","pending","cash"],
    ["T004","C004","2026/09/03","250000","paid","qris"],
    ["T005","C005","03-09-2026","90000","paid","transfer"],
    ["T006","C006","2026-09-04","9999999","paid","transfer"],
    ["T007","C007","2026-09-05","90000","failed","cash"],
    ["T008","C008","2026-09-05","120000","paid","qris"],
    ["T009","C009","2026-09-06","250000","paid","transfer"],
    ["T010","C010","2026-09-06","","pending",""],
    ["T011","C001","2026-09-01","120000","paid","transfer"],
]
with (RAW / "transactions.csv").open("w", newline="", encoding="utf-8") as f:
    w = csv.writer(f); w.writerow(["transaction_id","customer_id","payment_date","amount","payment_status","method"]); w.writerows(transactions)

reports = [
    ["R001","C001","2026-09-01","ONT online; signal stable","normal"],
    ["R002","C002","01/09/2026","ONT online; signal stable","normal"],
    ["R003","C003","2026-09-02","ONT offline; follow up","warning"],
    ["R004","C004","2026-09-03","ONT online; signal stable","normal"],
    ["R005","C005","2026-09-03","ONT online; signal stable","normal"],
    ["R006","C006","2026-09-04","ONT online; signal stable","normal"],
    ["R007","C007","2026-09-05","ONT offline; follow up","warning"],
    ["R008","C008","2026-09-05","ONT online; signal stable","normal"],
    ["R009","C009","2026-09-06","ONT online; signal stable","normal"],
    ["R010","C010","2026-09-06","ONT online; signal stable","normal"],
    ["R011","C010","2026-09-06","ONT online; signal stable","normal"],
]
with (RAW / "field_reports.csv").open("w", newline="", encoding="utf-8") as f:
    w = csv.writer(f); w.writerow(["report_id","customer_id","report_date","notes","severity"]); w.writerows(reports)

ALIASES = {"ternate city":"Ternate", "ternate":"Ternate", "tidore island":"Tidore", "tidore":"Tidore", "jailolo":"Jailolo"}
DATE_FORMATS = ("%Y-%m-%d", "%Y/%m/%d", "%d-%m-%Y", "%d/%m/%Y", "%d %b %Y")
def iso_date(value):
    value = (value or "").strip()
    for fmt in DATE_FORMATS:
        try: return datetime.strptime(value, fmt).date().isoformat()
        except ValueError: pass
    return ""
def norm_city(value): return ALIASES.get((value or "").strip().lower(), (value or "").strip().title())
def norm_phone(value): return re.sub(r"\D", "", value or "")

def read_csv(path):
    with path.open(encoding="utf-8") as f: return list(csv.DictReader(f))

cust = read_csv(RAW / "customers.csv")
txn = read_csv(RAW / "transactions.csv")
reps = read_csv(RAW / "field_reports.csv")
for row in cust:
    row["city"] = norm_city(row["city"]); row["phone"] = norm_phone(row["phone"]); row["event_date"] = iso_date(row["event_date"])
for row in txn: row["payment_date"] = iso_date(row["payment_date"])
for row in reps: row["report_date"] = iso_date(row["report_date"])

# Missing-value policy: numeric amount -> median; categorical method -> mode; retain indicators.
valid_amounts = [int(r["amount"]) for r in txn if r["amount"].isdigit() and int(r["amount"]) < 1000000]
median_amount = sorted(valid_amounts)[len(valid_amounts)//2]
method_mode = Counter(r["method"] for r in txn if r["method"].strip()).most_common(1)[0][0]
for row in txn:
    row["amount_missing"] = "0"; row["method_missing"] = "0"
    if not row["amount"].strip(): row["amount"] = str(median_amount); row["amount_missing"] = "1"
    if not row["method"].strip(): row["method"] = method_mode; row["method_missing"] = "1"

# IQR outlier detection on transaction amount; classify extreme values as Error.
amounts = sorted(int(r["amount"]) for r in txn)
def percentile(values, p):
    k = (len(values)-1)*p; lo, hi = math.floor(k), math.ceil(k)
    return values[lo] if lo == hi else values[lo] + (values[hi]-values[lo])*(k-lo)
q1, q3 = percentile(amounts, .25), percentile(amounts, .75); iqr = q3-q1; low, high = q1-1.5*iqr, q3+1.5*iqr
flags = []
for row in txn:
    amount = int(row["amount"]); kind = ""
    if amount < low or amount > high: kind = "Error" if amount > 10*high else "Anomali Genuin"
    row["outlier_flag"] = kind
    if kind: flags.append({"source":"transactions","record_id":row["transaction_id"],"issue":"iqr_outlier","classification":kind,"value":str(amount),"action":"retained_with_flag" if kind == "Anomali Genuin" else "retained_for_review"})

# Exact duplicates and near duplicates.
def fingerprint(row, keys): return "|".join((row.get(k) or "").strip().lower() for k in keys)
seen = set()
for row in txn:
    fp = fingerprint(row, ["customer_id","payment_date","amount","payment_status","method"])
    if fp in seen: flags.append({"source":"transactions","record_id":row["transaction_id"],"issue":"exact_duplicate","classification":"Duplicate","value":fp,"action":"retained_with_flag"})
    seen.add(fp)
seen_reports = set()
for row in reps:
    fp = fingerprint(row, ["customer_id","report_date","notes","severity"])
    if fp in seen_reports: flags.append({"source":"field_reports","record_id":row["report_id"],"issue":"exact_duplicate","classification":"Duplicate","value":fp,"action":"retained_with_flag"})
    seen_reports.add(fp)

# Near-duplicate marker: same customer/date and same normalized first 18 chars of notes.
for i, a in enumerate(reps):
    for b in reps[i+1:]:
        if a["customer_id"] == b["customer_id"] and a["report_date"] == b["report_date"] and a["notes"][:18].lower() == b["notes"][:18].lower() and a["report_id"] != b["report_id"]:
            flags.append({"source":"field_reports","record_id":b["report_id"],"issue":"near_duplicate","classification":"Near-Duplicate","value":f"similar_to:{a['report_id']}","action":"retained_with_flag"})

# Unified clean dataset: one row per customer with aggregate transaction/report signals.
txn_by = defaultdict(list); rep_by = defaultdict(list)
for r in txn: txn_by[r["customer_id"]].append(r)
for r in reps: rep_by[r["customer_id"]].append(r)
clean = []
for c in cust:
    tx = txn_by[c["customer_id"]]; rp = rep_by[c["customer_id"]]
    clean.append({"customer_id":c["customer_id"],"name":c["name"],"phone":c["phone"],"city":c["city"],"event_date":c["event_date"],"status":c["status"],"package":c["package"],"monthly_fee":c["monthly_fee"],"transaction_count":str(len(tx)),"paid_amount":str(sum(int(x["amount"]) for x in tx if x["payment_status"] == "paid")),"field_report_count":str(len(rp)),"imputation_flag":"1" if any(x["amount_missing"] == "1" or x["method_missing"] == "1" for x in tx) else "0"})
with (OUT / "cleaned_dataset.csv").open("w", newline="", encoding="utf-8") as f:
    fields=list(clean[0]); w=csv.DictWriter(f,fieldnames=fields); w.writeheader(); w.writerows(clean)

# One-hot encode categorical features while preserving the human-readable clean dataset.
cat_cols = ["city", "status", "package"]
encoded = [dict(row) for row in clean]
one_hot_columns = []
for col in cat_cols:
    categories = sorted({row[col] for row in clean})
    for category in categories:
        key = f"{col}_{re.sub(r'[^a-z0-9]+', '_', category.lower()).strip('_')}"
        one_hot_columns.append(key)
        for row in encoded:
            row[key] = "1" if row[col] == category else "0"
with (OUT / "encoded_dataset.csv").open("w", newline="", encoding="utf-8") as f:
    fields = list(clean[0]) + one_hot_columns
    w = csv.DictWriter(f, fieldnames=fields); w.writeheader(); w.writerows(encoded)
with (OUT / "audit_flags.csv").open("w", newline="", encoding="utf-8") as f:
    fields=["source","record_id","issue","classification","value","action"]; w=csv.DictWriter(f,fieldnames=fields); w.writeheader(); w.writerows(flags)

log = f"""# Decision Log — Synthetic Multi-source Dataset\n\n- Dataset: customers.csv, transactions.csv, field_reports.csv; seed=42 for reproducibility.\n- Missing amount: imputed with median valid amount ({median_amount}); `amount_missing=1` retained.\n- Missing payment method: imputed with mode (`{method_mode}`); `method_missing=1` retained.\n- Dates: parsed to ISO-8601 `YYYY-MM-DD`; unparseable values would remain blank and be flagged.\n- City aliases: normalized `Ternate City` → `Ternate`; `Tidore Island` → `Tidore`.\n- IQR: Q1={q1:g}, Q3={q3:g}, IQR={iqr:g}, bounds=({low:g}, {high:g}).\n- Outliers: extreme value classified `Error`; unusual but plausible values classified `Anomali Genuin`; no rows silently deleted.\n- Duplicates: exact and near-duplicates flagged in audit_flags.csv, retained for traceability.\n- Unified output: one row per customer with transaction/report aggregates.\n"""
(OUT / "decision_log.md").write_text(log, encoding="utf-8")
summary={"raw_records":{"customers":len(cust),"transactions":len(txn),"field_reports":len(reps)},"clean_customers":len(clean),"audit_flags":len(flags),"median_imputation_amount":median_amount,"iqr":{"q1":q1,"q3":q3,"low":low,"high":high},"flag_counts":dict(Counter(x["issue"] for x in flags))}
(OUT / "summary.json").write_text(json.dumps(summary,indent=2),encoding="utf-8")
print(json.dumps(summary,indent=2))