"""F1-conf-04: summarise sales export and reconcile 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, statistics
from pathlib import Path

DATA = Path("data"); OUT = Path("out"); OUT.mkdir(exist_ok=True)
meta = json.loads((DATA / "metadata.json").read_text())
KNOWN_MUNI = set(meta["n_by_municipality"])

with open(DATA / "sales.csv", newline="", encoding="utf-8") as f:
    lines = list(csv.reader(f))
header, body = lines[0], lines[1:]
assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"], header
n_lines = len(body)

PRICE_PLAIN = re.compile(r"\d+")
PRICE_THOUS = re.compile(r"\d{1,3}(,\d{3})+")   # e.g. "280,000" inside CSV quotes
AREA = re.compile(r"\d+(\.\d+)?")

def parse_price(s):
    if PRICE_PLAIN.fullmatch(s) or PRICE_THOUS.fullmatch(s):
        return int(s.replace(",", ""))
    return None

ledger = []
def step(name, rule, before, after, why):
    ledger.append(dict(step=name, rule=rule, n_before=len(before), n_after=len(after),
                       n_removed=len(before) - len(after), rationale=why))

# Step 1: structural readability (exactly 5 fields and a well-formed sale_id)
s1 = [r for r in body if len(r) == 5 and re.fullmatch(r"S\d{5}", r[0])]
step("1_structure", "line has exactly 5 CSV fields and sale_id matches S+5 digits",
     body, s1, "corrupted/garbled export lines cannot be read unambiguously; not reconstructed")

# Step 2: field validity
def valid(r):
    _, muni, d, p, a = r
    if muni not in KNOWN_MUNI: return False
    try: datetime.date.fromisoformat(d)
    except ValueError: return False
    if parse_price(p) is None or parse_price(p) <= 0: return False
    if not AREA.fullmatch(a) or float(a) <= 0: return False
    return True
s2 = [r for r in s1 if valid(r)]
step("2_fields", "municipality in control list, ISO date, positive integer price (plain or quoted thousands-separated), positive numeric area",
     s1, s2, "fields must be readable unambiguously")

# Step 3: duplicate sale_id
seen, s3 = set(), []
for r in s2:
    if r[0] not in seen:
        seen.add(r[0]); s3.append(r)
step("3_dedup", "drop repeated sale_id (keep first)", s2, s3, "sale_id must be unique")

n_thous = sum(1 for r in s3 if PRICE_THOUS.fullmatch(r[3]))
recs = [dict(sale_id=r[0], municipality=r[1], sale_date=r[2],
             price_cad=parse_price(r[3]), area_m2=float(r[4])) for r in s3]

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

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

ids = {int(r["sale_id"][1:]) for r in recs}
missing = sorted(set(range(1, n_ctrl + 1)) - ids)
miss_rng = f"S{missing[0]:05d}-S{missing[-1]:05d}" if missing else "none"
contig = bool(missing) and missing == list(range(missing[0], missing[-1] + 1))
n_by_muni_valid = {m: sum(1 for r in recs if r["municipality"] == m) for m in meta["n_by_municipality"]}
gap_by_muni = {m: meta["n_by_municipality"][m] - n_by_muni_valid[m] for m in n_by_muni_valid}

n_struct = ledger[0]["n_removed"]
issues = []
if n_struct:
    issues.append({"issue": f"{n_struct} data lines (sale_id numbers {miss_rng}{', a contiguous block' if contig else ''}) are garbled: wrong number of fields and/or non-conforming sale_id with random characters",
                   "handling": "excluded as unreadable; not reconstructed or imputed; their implied price total is reported as sum_price_unreadable_implied"})
if n_thous:
    issues.append({"issue": f"{n_thous} records store price_cad as a quoted string with thousands separators (e.g. \"280,000\")",
                   "handling": "read unambiguously by removing the comma separators (standard CSV quoting); records retained"})
if ledger[1]["n_removed"]:
    issues.append({"issue": f"{ledger[1]['n_removed']} structurally readable lines have invalid field values", "handling": "excluded"})
if ledger[2]["n_removed"]:
    issues.append({"issue": f"{ledger[2]['n_removed']} duplicate sale_id", "handling": "duplicates dropped, first kept"})

feas = "complete" if n_unread == 0 and sum_valid == sum_ctrl else "partial"
reason = None if feas == "complete" else (
    f"{n_unread} of the {n_ctrl} records in the provider's control totals could not be analysed: "
    f"{n_struct} export lines (sale_id {miss_rng}) are corrupted with garbled characters and wrong field counts, "
    f"so their fields cannot be read unambiguously; they account for an implied {sum_ctrl - sum_valid} CAD of the control price sum. "
    f"Per-municipality shortfall vs control: {gap_by_muni}.")

res = {
    "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": int(sum_valid),
    "sum_price_unreadable_implied": int(sum_ctrl - sum_valid),
    "mean_price_valid": round(mean_p, 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,
    # supplementary keys
    "baseline_n_lines_minus_control": n_lines - n_ctrl,
    "n_price_thousands_separator": n_thous,
    "n_by_municipality_valid": n_by_muni_valid,
    "n_by_municipality_shortfall": gap_by_muni,
    "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 6) if n_unread else None,
    "sale_date_min_valid": min(r["sale_date"] for r in recs),
    "sale_date_max_valid": max(r["sale_date"] for r in recs),
}
(OUT / "results.json").write_text(json.dumps(res, indent=2, ensure_ascii=False) + "\n")
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))