"""F1-conf-04: reconcile property-sales export to provider 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 is used (random_seed = null)."""
import csv, json, re, datetime, os
from collections import Counter
import numpy as np

DATA = "data"
OUT = "out"
os.makedirs(OUT, exist_ok=True)

meta = json.load(open(os.path.join(DATA, "metadata.json")))
with open(os.path.join(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"], header
n_lines = len(lines)

ID_RE = re.compile(r"S\d{5}")
PRICE_PLAIN = re.compile(r"\d+")
PRICE_THOUS = re.compile(r"\d{1,3}(,\d{3})+")   # e.g. "280,000" (quoted field)
KNOWN_MUNI = set(meta["n_by_municipality"])


def parse(r):
    """Return (parsed record, None) or (None, reason)."""
    if len(r) != 5:
        return None, f"wrong field count ({len(r)})"
    sid, muni, d, p, a = (x.strip() for x in r)
    if not ID_RE.fullmatch(sid):
        return None, "malformed sale_id"
    if muni not in KNOWN_MUNI:
        return None, "unknown municipality"
    if not re.fullmatch(r"\d{4}-\d{2}-\d{2}", d):
        return None, "invalid date"
    try:
        datetime.date.fromisoformat(d)
    except ValueError:
        return None, "invalid date"
    if PRICE_PLAIN.fullmatch(p):
        price, fmt = int(p), "plain"
    elif PRICE_THOUS.fullmatch(p):
        price, fmt = int(p.replace(",", "")), "thousands"
    else:
        return None, "invalid price"
    try:
        area = float(a)
    except ValueError:
        return None, "invalid area"
    if not (price > 0 and np.isfinite(area) and area > 0):
        return None, "non-positive price/area"
    return dict(sale_id=sid, municipality=muni, sale_date=d, price_cad=price,
                area_m2=area, _fmt=fmt), None


parsed, failed = [], []
for lineno, r in enumerate(lines, start=2):
    rec, why = parse(r)
    if rec:
        parsed.append(rec)
    else:
        failed.append((lineno, why))

# Step 2: duplicate sale_id among parsed records (ambiguous -> drop all copies)
idc = Counter(x["sale_id"] for x in parsed)
dups = {k for k, v in idc.items() if v > 1}
valid = [x for x in parsed if x["sale_id"] not in dups]
n_thous = sum(x["_fmt"] == "thousands" for x in valid)

excl = [
    dict(step=1, rule="line parses into 5 well-formed fields (S+5-digit id; known municipality; ISO date; integer price, plain or quoted with comma thousands separators; positive numeric area)",
         n_before=n_lines, n_after=len(parsed), n_removed=n_lines - len(parsed),
         rationale="corrupted/garbled lines cannot be read unambiguously; not reconstructed (TASK rule)"),
    dict(step=2, rule="sale_id unique among parsed records",
         n_before=len(parsed), n_after=len(valid), n_removed=len(parsed) - len(valid),
         rationale="duplicate ids would make record identity ambiguous"),
]
with open(os.path.join(OUT, "exclusions.csv"), "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=list(excl[0]))
    w.writeheader()
    w.writerows(excl)

cols = ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]
with open(os.path.join(OUT, "analytic.csv"), "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(cols)
    for x in valid:
        w.writerow([x[c] for c in cols])

price = np.array([x["price_cad"] for x in valid], dtype=np.int64)
area = np.array([x["area_m2"] for x in valid], dtype=float)
n_valid = int(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

muni_valid = Counter(x["municipality"] for x in valid)
muni_gap = {m: int(meta["n_by_municipality"][m] - muni_valid.get(m, 0)) for m in meta["n_by_municipality"]}

reasons = Counter(why for _, why in failed)
bad_lines = [l for l, _ in failed]
missing_ids = sorted(set(f"S{i:05d}" for i in range(1, n_ctrl + 1)) - {x["sale_id"] for x in valid})

issues = []
if failed:
    issues.append({
        "issue": f"{len(failed)} data lines of sales.csv (file lines {min(bad_lines)}-{max(bad_lines)}) are garbled/corrupted and cannot be parsed ({dict(sorted(reasons.items()))}); "
                 f"{len(missing_ids)} sale_ids ({missing_ids[0]}-{missing_ids[-1]}) are absent from the readable records",
        "handling": "excluded from the analytic dataset (exclusion step 1); not reconstructed or imputed; their count and implied price sum are reported via n_unreadable_records and sum_price_unreadable_implied"})
if n_thous:
    issues.append({
        "issue": f"{n_thous} records have price_cad written as a quoted string with comma thousands separators (e.g. \"280,000\") instead of a plain integer",
        "handling": "the quoted field is unambiguous under CSV quoting; parsed as integer after removing the separators and kept in the analytic dataset"})
if dups:
    issues.append({"issue": f"{len(dups)} duplicated sale_id values", "handling": "all copies excluded (step 2)"})
if n_unread or sum_ctrl != sum_valid:
    issues.append({
        "issue": f"record count and price sum do not reconcile with the control totals: {n_unread} records and {sum_ctrl - sum_valid} CAD missing; per-municipality count gaps {muni_gap}",
        "handling": f"gap reported, not imputed; the per-municipality gaps sum to {sum(muni_gap.values())}, equal to the number of corrupted lines ({len(failed)})"})

feas = "complete" if n_unread == 0 else ("partial" if n_valid > 0 else "infeasible")
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(failed)} data lines of sales.csv are corrupted (garbled characters, wrong field counts) and cannot be read unambiguously, "
    f"so they were excluded and not reconstructed; the implied price sum of these 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": int(n_unread),
    "n_analysed": n_valid,
    "sum_price_control": sum_ctrl,
    "sum_price_valid": sum_valid,
    "sum_price_unreadable_implied": int(sum_ctrl - sum_valid),
    "mean_price_valid": round(float(price.mean()), 6),
    "median_price_per_m2_valid": round(float(np.median(price / area)), 6),
    "feasibility": feas,
    "feasibility_reason": reason,
    "data_issues": issues,
    "n_input_rows": {"sales.csv": n_lines},
    "random_seed": None,
    # supplementary keys (not required by TASK.md)
    "n_parse_failures": len(failed),
    "n_price_thousands_separator": int(n_thous),
    "n_duplicate_ids": len(dups),
    "n_by_municipality_valid": dict(sorted(muni_valid.items())),
    "n_by_municipality_gap": muni_gap,
    "missing_sale_ids_range": [missing_ids[0], missing_ids[-1]] if missing_ids else None,
    "n_missing_sale_ids": len(missing_ids),
    "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 6) if n_unread else None,
    "price_min_valid": int(price.min()),
    "price_max_valid": int(price.max()),
    "date_min_valid": min(x["sale_date"] for x in valid),
    "date_max_valid": max(x["sale_date"] for x in valid),
}
with open(os.path.join(OUT, "results.json"), "w") as f:
    json.dump(results, f, indent=2)
print(json.dumps({k: results[k] for k in ["n_input_rows", "n_valid_records", "n_unreadable_records",
      "sum_price_valid", "sum_price_unreadable_implied", "mean_price_valid",
      "median_price_per_m2_valid", "feasibility"]}, indent=1))