"""F1-conf-04: reconcile property-sales export to provider control totals.
Run from workspace root: ~/wp29/venv/bin/python out/analysis.py"""
import csv, json, re
from datetime import date
import numpy as np, pandas as pd

DATA, OUT = "data/sales.csv", "out"
meta = json.load(open("data/metadata.json"))
COLS = ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]
ID_RE = re.compile(r"^S\d{5}$")
NUM_RE = re.compile(r"^\d+(\.\d+)?$")
THOUS_RE = re.compile(r"^\d{1,3}(,\d{3})+$")
MUNIS = set(meta["n_by_municipality"])

with open(DATA, newline="", encoding="utf-8") as f:
    lines = f.read().splitlines()
header, body = lines[0], lines[1:]
assert next(csv.reader([header])) == COLS
n_lines = len(body)

rows, bad, n_thousands = [], [], 0
for ln, raw in enumerate(body, start=2):
    try:
        fields = next(csv.reader([raw]))
    except Exception:
        fields = None
    reason = None
    if fields is None or len(fields) != 5:
        reason = "wrong field count"
    else:
        sid, muni, sdate, price, area = [x.strip() for x in fields]
        if not ID_RE.match(sid): reason = "bad sale_id"
        elif muni not in MUNIS: reason = "unknown municipality"
        else:
            try: date.fromisoformat(sdate)
            except ValueError: reason = "bad date"
        if reason is None:
            if THOUS_RE.match(price):
                n_thousands += 1; price = price.replace(",", "")
            if not NUM_RE.match(price) or not NUM_RE.match(area): reason = "non-numeric price/area"
            elif float(area) <= 0: reason = "non-positive area"
    if reason:
        bad.append((ln, raw, reason)); continue
    rows.append(dict(sale_id=sid, municipality=muni, sale_date=sdate,
                     price_cad=int(round(float(price))), area_m2=float(area)))

df = pd.DataFrame(rows, columns=COLS)
n_parsed = len(df)
dup = df.sale_id.duplicated(keep=False)
n_dup = int(dup.sum())
df = df[~dup].reset_index(drop=True)
n_valid = len(df)

ledger = [
    dict(step=1, rule="data lines read (excluding header)", n_before=n_lines, n_after=n_lines, n_removed=0,
         rationale="all non-header lines of sales.csv"),
    dict(step=2, rule="drop lines that cannot be parsed unambiguously (CSV field count != 5, sale_id not S+5 digits, unknown municipality, invalid ISO date, non-numeric price/area)",
         n_before=n_lines, n_after=n_parsed, n_removed=n_lines - n_parsed,
         rationale="corrupted/garbled export lines; content not reconstructed per task rules"),
    dict(step=3, rule="drop records whose sale_id is duplicated", n_before=n_parsed, n_after=n_valid, n_removed=n_dup,
         rationale="sale_id must be unique; duplicates would be ambiguous"),
]
pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)
df.to_csv(f"{OUT}/analytic.csv", index=False)

n_ctrl, s_ctrl = int(meta["n_records"]), int(meta["sum_price_cad"])
s_valid = int(df.price_cad.sum())
n_unread = n_ctrl - n_valid
bad_ids = [l for l, _, _ in bad]
missing_ids = sorted(set(f"S{i:05d}" for i in range(1, n_ctrl + 1)) - set(df.sale_id))
by_muni_valid = df.municipality.value_counts().to_dict()
by_muni_gap = {m: int(meta["n_by_municipality"][m] - by_muni_valid.get(m, 0)) for m in meta["n_by_municipality"]}

issues = []
if bad:
    issues.append(dict(issue=f"{len(bad)} data lines (file lines {min(bad_ids)}-{max(bad_ids)}) are garbled/corrupted and cannot be parsed into the 5 intended fields; "
                             f"sale_ids absent from the analytic data: {missing_ids[0]}..{missing_ids[-1]} ({len(missing_ids)} ids)" if missing_ids else "",
                       handling="excluded; not reconstructed or imputed; their implied count and price sum are derived from the control totals"))
if n_thousands:
    issues.append(dict(issue=f"{n_thousands} records have price_cad written with a thousands separator inside CSV quotes (e.g. \"280,000\")",
                       handling="quoted field is unambiguous under standard CSV quoting; comma removed and value parsed as integer CAD; records kept"))
issues.append(dict(issue=f"record count reconciliation: control {n_ctrl} vs {n_valid} analysable (gap {n_unread}); municipality gaps {by_muni_gap}",
                   handling="reported as partial feasibility; gap equals number of unreadable lines" if n_unread == len(bad) + n_dup else "reported; gap not fully explained by unreadable lines"))
issues.append(dict(issue=f"price sum reconciliation: control {s_ctrl} vs analysable {s_valid}; implied unreadable sum {s_ctrl - s_valid}",
                   handling="reported; cannot be verified record-by-record because the corresponding lines are unreadable"))
if n_dup:
    issues.append(dict(issue=f"{n_dup} records with duplicated sale_id", handling="all copies excluded"))

feas = "complete" if n_unread == 0 else "partial"
reason = None if feas == "complete" else (
    f"{n_unread} of the {n_ctrl} records in the provider's control count could not be analysed: "
    f"{len(bad)} export lines (file lines {min(bad_ids)}-{max(bad_ids)}, corresponding to sale_ids {missing_ids[0]}-{missing_ids[-1]}) are corrupted/garbled "
    f"and cannot be read unambiguously" + (f"; {n_dup} duplicated-id records excluded" if n_dup else "") +
    f". Their implied price sum is {s_ctrl - s_valid} CAD (control minus analysable).")

SEED = 20261030
rng = np.random.default_rng(SEED)
ppm = (df.price_cad / df.area_m2).to_numpy()
pr = df.price_cad.to_numpy(dtype=float)
se_mean = float(pr.std(ddof=1) / np.sqrt(len(pr)))
boot_med = np.array([np.median(rng.choice(ppm, len(ppm))) for _ in range(2000)])
med_ci = [float(x) for x in np.percentile(boot_med, [2.5, 97.5])]
res = dict(task_id="F1-conf-04", n_records_control=n_ctrl, n_valid_records=n_valid, n_unreadable_records=n_unread,
           n_analysed=n_valid, sum_price_control=s_ctrl, sum_price_valid=s_valid,
           sum_price_unreadable_implied=s_ctrl - s_valid,
           mean_price_valid=float(df.price_cad.mean()),
           median_price_per_m2_valid=float(np.median(df.price_cad / df.area_m2)),
           feasibility=feas, feasibility_reason=reason, data_issues=issues,
           n_input_rows={"sales.csv": n_lines}, random_seed=SEED, se_mean_price_valid=se_mean,
           ci95_mean_price_valid=[float(pr.mean() - 1.96 * se_mean), float(pr.mean() + 1.96 * se_mean)],
           ci95_median_price_per_m2_valid_bootstrap=med_ci,
           implied_mean_price_unreadable=float((s_ctrl - s_valid) / n_unread) if n_unread else None,
           n_by_municipality_valid={k: int(v) for k, v in by_muni_valid.items()},
           n_by_municipality_gap=by_muni_gap)
json.dump(res, open(f"{OUT}/results.json", "w"), indent=2)
print(json.dumps(res, indent=2))