"""F1-conf-04: reconcile property-sales export to control totals.
Run from workspace root: ~/wp29/venv/bin/python out/analysis.py
Reads only data/; writes out/results.json, out/analytic.csv, out/exclusions.csv. No randomness."""
import csv, json, re
from collections import Counter
from datetime import date
from pathlib import Path
import numpy as np

DATA, OUT = Path("data"), Path("out")
meta = json.loads((DATA / "metadata.json").read_text())

with open(DATA / "sales.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))
header, lines = rows[0], rows[1:]
assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]
n_lines = len(lines)
munis = set(meta["n_by_municipality"])
ID = re.compile(r"S\d{5}")
PLAIN = re.compile(r"\d+")
THOUS = re.compile(r"\d{1,3}(,\d{3})+")
AREA = re.compile(r"\d+(\.\d+)?")
DATE = re.compile(r"\d{4}-\d{2}-\d{2}")

def parse(r):
    """Return (record, used_thousands_sep) or None if not unambiguously readable."""
    sid, mun, d, p, a = r
    if not ID.fullmatch(sid) or mun not in munis or not AREA.fullmatch(a) or not DATE.fullmatch(d):
        return None
    try:
        dd = date.fromisoformat(d)
    except ValueError:
        return None
    if PLAIN.fullmatch(p):
        price, sep = int(p), False
    elif THOUS.fullmatch(p):
        price, sep = int(p.replace(",", "")), True
    else:
        return None
    area = float(a)
    if area <= 0 or price <= 0:
        return None
    return {"sale_id": sid, "municipality": mun, "sale_date": dd.isoformat(),
            "price_cad": price, "area_m2": area}, sep

# Step 1: structurally malformed lines (field count != 5)
s1 = [r for r in lines if len(r) == 5]
# Step 2: rows with 5 fields but some field not unambiguously readable
s2 = [p for p in (parse(r) for r in s1) if p is not None]
n_sep = sum(p[1] for p in s2)
recs = [p[0] for p in s2]
# Step 3: duplicate sale_id -> ambiguous, drop all copies
cnt = Counter(r["sale_id"] for r in recs)
valid = [r for r in recs if cnt[r["sale_id"]] == 1]

excl = [
    ("1", "line must split into exactly 5 CSV fields", n_lines, len(s1),
     "corrupted export lines (random characters, wrong field count); cannot be read, not reconstructed"),
    ("2", "all fields parse: sale_id S+5 digits, known municipality, ISO date, integer price (plain or comma-thousands), positive numeric area",
     len(s1), len(s2), "fields not unambiguously readable"),
    ("3", "sale_id unique", len(s2), len(valid), "duplicated ids would be ambiguous"),
]
with open(OUT / "exclusions.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["step", "rule", "n_before", "n_after", "n_removed", "rationale"])
    for s, rule, nb, na, rat in excl:
        w.writerow([s, rule, nb, na, nb - na, rat])

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(valid)

price = np.array([r["price_cad"] for r in valid], dtype=np.int64)
area = np.array([r["area_m2"] for r in valid], dtype=float)
n_valid = len(valid)
n_ctrl = int(meta["n_records"])
sum_ctrl = int(meta["sum_price_cad"])
sum_valid = int(price.sum())
n_unread = n_ctrl - n_valid
present = {int(r["sale_id"][1:]) for r in valid}
missing_ids = sorted(set(range(1, n_ctrl + 1)) - present)
muni_valid = Counter(r["municipality"] for r in valid)
muni_short = {m: meta["n_by_municipality"][m] - muni_valid.get(m, 0) for m in meta["n_by_municipality"]}

n_malformed = n_lines - len(s1)
issues = []
if n_malformed:
    issues.append({"issue": f"{n_malformed} data lines of sales.csv are corrupted (random characters, field counts other than 5); "
                            f"sale_ids S{missing_ids[0]:05d}-S{missing_ids[-1]:05d} ({len(missing_ids)} ids) are absent from readable rows",
                   "handling": "excluded from the analytic dataset; not reconstructed or imputed; reported as unreadable records"})
if len(s1) - len(s2):
    issues.append({"issue": f"{len(s1)-len(s2)} rows with 5 fields have unreadable field values", "handling": "excluded"})
if n_sep:
    issues.append({"issue": f"{n_sep} price_cad values are CSV-quoted with a comma thousands separator (e.g. \"280,000\")",
                   "handling": "parsed as integers after removing commas (pattern \\d{1,3}(,\\d{3})+ is unambiguous for integer CAD prices); kept"})
if len(s2) - n_valid:
    issues.append({"issue": f"{len(s2)-n_valid} rows with duplicated sale_id", "handling": "excluded"})
issues.append({"issue": "record count and price sum of readable rows fall short of provider control totals; per-municipality shortfall "
                        + ", ".join(f"{m} {v}" for m, v in muni_short.items()),
               "handling": "reported as partial feasibility; shortfall sum attributed to unreadable records as an implied total only"})

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"{n_malformed} lines of sales.csv are corrupted export lines (random characters, wrong number of fields) "
    f"whose fields cannot be read unambiguously, so they were excluded and not reconstructed. "
    f"The implied price total of the missing records is {sum_ctrl - sum_valid} CAD.")

results = {
    "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(float(price.mean()), 4),
    "median_price_per_m2_valid": round(float(np.median(price / area)), 4),
    "feasibility": feas,
    "feasibility_reason": reason,
    "data_issues": issues,
    "n_input_rows": {"sales.csv": n_lines},
    "random_seed": None,
    "n_malformed_lines": n_malformed,
    "n_price_thousands_separator_parsed": n_sep,
    "n_valid_by_municipality": dict(sorted(muni_valid.items())),
    "n_shortfall_by_municipality": muni_short,
}
(OUT / "results.json").write_text(json.dumps(results, indent=2) + "\n")
print(json.dumps({k: results[k] for k in ["n_valid_records", "n_unreadable_records", "sum_price_valid",
                                          "sum_price_unreadable_implied", "mean_price_valid",
                                          "median_price_per_m2_valid", "feasibility"]}, indent=1))