"""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."""
import csv, json, re, datetime, statistics
from collections import Counter
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
DATA, OUT = ROOT / "data", ROOT / "out"
ID_PAT = re.compile(r"^S\d{5}$")
PRICE_PLAIN = re.compile(r"^\d+$")
PRICE_THOUS = re.compile(r"^\d{1,3}(,\d{3})+$")
AREA_PAT = re.compile(r"^\d+(\.\d+)?$")

meta = json.loads((DATA / "metadata.json").read_text())
text = (DATA / "sales.csv").read_text(encoding="utf-8")
lines = text.split("\n")
data_lines = [l for l in lines[1:] if l != ""]
n_lines = len(data_lines)
assert lines[0].split(",") == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]

def parse(line):
    """Return (record, reason); record is None if not unambiguously readable."""
    f = next(csv.reader([line]))
    if len(f) != 5:
        return None, "field_count"
    sid, mun, d, p, a = f
    if not ID_PAT.match(sid) or mun not in meta["n_by_municipality"]:
        return None, "field_format"
    try:
        dd = datetime.date.fromisoformat(d)
    except ValueError:
        return None, "field_format"
    if PRICE_PLAIN.match(p):
        price, quoted = int(p), False
    elif PRICE_THOUS.match(p):
        price, quoted = int(p.replace(",", "")), True
    else:
        return None, "field_format"
    if not AREA_PAT.match(a) or float(a) <= 0 or price <= 0:
        return None, "field_format"
    return dict(sale_id=sid, municipality=mun, sale_date=dd.isoformat(),
                price_cad=price, area_m2=float(a), _quoted=quoted), None

recs, reasons = [], {"field_count": 0, "field_format": 0}
for l in data_lines:
    r, why = parse(l)
    if r is None:
        reasons[why] += 1
    else:
        recs.append(r)
n_bad = reasons["field_count"] + reasons["field_format"]

idc = Counter(r["sale_id"] for r in recs)
dups = {k for k, v in idc.items() if v > 1}
valid = [r for r in recs if r["sale_id"] not in dups]
n_quoted = sum(r["_quoted"] for r in valid)

n_valid = len(valid)
n_ctrl = int(meta["n_records"])
sum_ctrl = int(meta["sum_price_cad"])
sum_valid = int(sum(r["price_cad"] for r in valid))
mean_valid = sum_valid / n_valid
med_ppm2 = float(statistics.median(r["price_cad"] / r["area_m2"] for r in valid))
n_unread = n_ctrl - n_valid

obs_mun = Counter(r["municipality"] for r in valid)
gap_mun = {m: int(c) - obs_mun.get(m, 0) for m, c in meta["n_by_municipality"].items()}
present_ids = {int(r["sale_id"][1:]) for r in valid}
missing_ids = sorted(set(range(1, n_ctrl + 1)) - present_ids)
id_range = 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 n_bad:
    issues.append({"issue": f"{n_bad} data lines of sales.csv are garbled export errors (random characters; "
                   f"{reasons['field_count']} with wrong field count, {reasons['field_format']} with invalid field formats); "
                   f"the sale_ids absent from the readable data are {id_range} ({len(missing_ids)} ids, contiguous={contiguous})",
                   "handling": "excluded from the analytic dataset; not reconstructed or imputed; counted as unreadable"})
if n_quoted:
    issues.append({"issue": f"{n_quoted} records have price_cad written with a thousands separator inside a quoted CSV field (e.g. \"280,000\")",
                   "handling": "read via standard CSV quoting; value matches the strict pattern d{1,3}(,ddd)+ so it is unambiguous; separators removed and record kept"})
if dups:
    issues.append({"issue": f"{len(dups)} duplicated sale_id values", "handling": "all copies excluded as ambiguous"})
issues.append({"issue": f"readable record count ({n_valid}) and price sum ({sum_valid}) fall short of the control totals ({n_ctrl}, {sum_ctrl}); "
               f"per-municipality count gaps {gap_mun} sum to {sum(gap_mun.values())}",
               "handling": "reported as reconciliation gap; the unreadable records' price sum is implied as control minus valid, not estimated per record"})

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 total could not be analysed: "
    f"{n_bad} data lines of sales.csv are garbled export errors that cannot be read unambiguously "
    f"(sale_ids {id_range} are absent from the readable data); their implied price sum is {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, 6), median_price_per_m2_valid=round(med_ppm2, 6),
           feasibility=feas, feasibility_reason=reason, data_issues=issues,
           n_input_rows={"sales.csv": n_lines}, random_seed=None,
           n_garbled_lines=n_bad, n_quoted_price_records=n_quoted,
           reconciliation_by_municipality_gap=gap_mun)
(OUT / "results.json").write_text(json.dumps(res, indent=2) + "\n")

with open(OUT / "analytic.csv", "w", newline="") as fh:
    w = csv.writer(fh)
    w.writerow(["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])
    for r in valid:
        w.writerow([r["sale_id"], r["municipality"], r["sale_date"], r["price_cad"], r["area_m2"]])

n1 = n_lines - reasons["field_count"]
n2 = n1 - reasons["field_format"]
steps = [
    (1, "parse with CSV quoting; require exactly 5 fields", n_lines, n1, "lines with wrong field count are garbled and cannot be read unambiguously"),
    (2, "require valid formats: sale_id S+5 digits; known municipality; ISO date; positive integer price (plain or strict thousands-separated); positive numeric area", n1, n2, "fields not unambiguously readable"),
    (3, "exclude duplicated sale_id (all copies)", n2, n_valid, "sale_id must be unique"),
]
with open(OUT / "exclusions.csv", "w", newline="") as fh:
    w = csv.writer(fh)
    w.writerow(["step", "rule", "n_before", "n_after", "n_removed", "rationale"])
    for s, rule, b, a, why in steps:
        w.writerow([s, rule, b, a, b - a, why])

print(json.dumps({k: res[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))