"""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, datetime, statistics
from collections import Counter

DATA = "data/sales.csv"
meta = json.load(open("data/metadata.json"))
with open(DATA, newline="", encoding="utf-8") as f:
    raw_lines = f.read().splitlines()
n_lines = len(raw_lines) - 1  # data lines excluding header
with open(DATA, newline="", encoding="utf-8") as f:
    rdr = csv.reader(f)
    header = next(rdr)
    rows = [(rdr.line_num, r) for r in rdr]
assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]
assert len(rows) == n_lines  # no multi-line quoted fields

MUNIS = set(meta["n_by_municipality"])
ID_RE = re.compile(r"S\d{5}")
PRICE_RE = re.compile(r"\d+|\d{1,3}(,\d{3})+")
NUM_RE = re.compile(r"\d+(\.\d+)?")

def check_date(s):
    try:
        datetime.date.fromisoformat(s); return re.fullmatch(r"\d{4}-\d{2}-\d{2}", s) is not None
    except ValueError:
        return False

steps = []
cur = rows
def step(name, rule, keep_fn, rationale):
    global cur
    before = len(cur)
    cur = [x for x in cur if keep_fn(x[1])]
    steps.append(dict(step=name, rule=rule, n_before=before, n_after=len(cur),
                      n_removed=before - len(cur), rationale=rationale))

bad_lines = [ln for ln, r in rows if not (len(r) == 5 and ID_RE.fullmatch(r[0]))]
step("1", "row parses into exactly 5 CSV fields", lambda r: len(r) == 5,
     "rows with wrong field count are corrupted (garbled bytes); content cannot be read unambiguously")
step("2", "sale_id matches S + 5 digits", lambda r: ID_RE.fullmatch(r[0]) is not None,
     "malformed identifier indicates a corrupted record")
step("3", "municipality in control-total list", lambda r: r[1] in MUNIS,
     "unknown municipality indicates a corrupted record")
step("4", "sale_date is a valid ISO date", lambda r: check_date(r[2]), "unreadable date")
step("5", "price_cad is a positive integer (optionally with thousands separators inside quotes)",
     lambda r: PRICE_RE.fullmatch(r[3]) is not None and int(r[3].replace(",", "")) > 0, "unreadable price")
step("6", "area_m2 is a positive number", lambda r: NUM_RE.fullmatch(r[4]) is not None and float(r[4]) > 0,
     "unreadable area")
ids = [r[0] for _, r in cur]
step("7", "sale_id unique", lambda r, c=Counter(ids): c[r[0]] == 1, "duplicate records would double count")

n_sep = sum(1 for _, r in cur if "," in r[3])
recs = [dict(sale_id=r[0], municipality=r[1], sale_date=r[2],
             price_cad=int(r[3].replace(",", "")), area_m2=float(r[4])) for _, r in cur]
n_valid = len(recs)
sum_valid = sum(x["price_cad"] for x in recs)
mean_valid = sum_valid / n_valid
med_ppm2 = statistics.median(x["price_cad"] / x["area_m2"] for x in recs)
n_ctrl = meta["n_records"]; sum_ctrl = meta["sum_price_cad"]
n_unread = n_ctrl - n_valid
by_muni = Counter(x["municipality"] for x in recs)
muni_gap = {m: meta["n_by_municipality"][m] - by_muni.get(m, 0) for m in meta["n_by_municipality"]}
present = sorted(int(x["sale_id"][1:]) for x in recs)
missing_ids = [i for i in range(1, n_ctrl + 1) if i not in set(present)]
id_rng = f"S{missing_ids[0]:05d}-S{missing_ids[-1]:05d}" if missing_ids else "none"
contiguous = bool(missing_ids) and missing_ids == list(range(missing_ids[0], missing_ids[-1] + 1))

issues = []
if bad_lines:
    issues.append(dict(issue=f"{len(bad_lines)} data lines (file lines {bad_lines[0]}-{bad_lines[-1]}) are corrupted/garbled "
                       f"(wrong field count, malformed sale_id, non-numeric content)",
                       handling="excluded as unreadable; not reconstructed or imputed; their price total is only known "
                                "implicitly via the control sum"))
if n_sep:
    issues.append(dict(issue=f"{n_sep} records have price_cad written as a quoted string with a thousands separator (e.g. \"280,000\")",
                       handling="read unambiguously as integers after removing the comma separator (standard CSV quoting); retained"))
if missing_ids:
    issues.append(dict(issue=f"sale_id values {id_rng} ({len(missing_ids)} ids, contiguous={contiguous}) are absent from the readable records",
                       handling="consistent with the corrupted block; reported only, ids not assigned to the garbled lines"))
issues.append(dict(issue=f"readable record count by municipality falls short of provider control: {muni_gap}",
                   handling="shortfall sums to the number of unreadable records; reported, no adjustment"))

feas = "complete" if n_unread == 0 else "partial"
reason = None if feas == "complete" else (
    f"{n_unread} of {n_ctrl} control records could not be analysed: {len(bad_lines)} lines of sales.csv "
    f"(file lines {bad_lines[0]}-{bad_lines[-1]}) are corrupted export rows whose fields cannot be read unambiguously; "
    f"they were excluded, not reconstructed. Implied unreadable price sum = {sum_ctrl - sum_valid} CAD.")

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=sum_ctrl,
           sum_price_valid=sum_valid, sum_price_unreadable_implied=sum_ctrl - sum_valid,
           mean_price_valid=round(mean_valid, 4), median_price_per_m2_valid=round(med_ppm2, 4),
           feasibility=feas, feasibility_reason=reason, data_issues=issues,
           n_input_rows={"sales.csv": n_lines}, random_seed=None,
           n_by_municipality_valid=dict(by_muni), n_by_municipality_shortfall=muni_gap)
json.dump(res, open("out/results.json", "w"), indent=2)
with open("out/analytic.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])
    w.writeheader(); w.writerows(recs)
with open("out/exclusions.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=["step", "rule", "n_before", "n_after", "n_removed", "rationale"])
    w.writeheader(); w.writerows(steps)
print(json.dumps(res, indent=2)); print(steps)