"""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
import pandas as pd

meta = json.load(open("data/metadata.json"))
with open("data/sales.csv", newline="") 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)

ID = re.compile(r"S\d{5}")
PRICE_PLAIN = re.compile(r"\d+")
PRICE_THOUS = re.compile(r"\d{1,3}(,\d{3})+")
AREA = re.compile(r"\d+(\.\d+)?")
MUNIS = set(meta["n_by_municipality"])

def date_ok(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 = []
# Step 1: structural parse (exactly 5 fields, valid sale_id)
s1 = [r for r in lines if len(r) == 5 and ID.fullmatch(r[0])]
garbled_idx = [i + 2 for i, r in enumerate(lines) if not (len(r) == 5 and ID.fullmatch(r[0]))]
steps.append(("1", "line parses into exactly 5 CSV fields with sale_id matching S+5 digits",
              n_lines, len(s1), "garbled/corrupted export lines cannot be read unambiguously; not reconstructed"))
# Step 2: field validity
def valid(r):
    return (r[1] in MUNIS and date_ok(r[2])
            and (PRICE_PLAIN.fullmatch(r[3]) or PRICE_THOUS.fullmatch(r[3]))
            and AREA.fullmatch(r[4]) and float(r[4]) > 0)
s2 = [r for r in s1 if valid(r)]
steps.append(("2", "municipality in control list, valid ISO date, numeric price (plain or quoted thousands-separated), positive numeric area",
              len(s1), len(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)
steps.append(("3", "unique sale_id (drop exact duplicate ids)", len(s2), len(s3), "sale_id is documented as unique"))

n_thous = sum(1 for r in s3 if PRICE_THOUS.fullmatch(r[3]))
df = pd.DataFrame(s3, columns=header)
df["price_cad"] = df["price_cad"].str.replace(",", "", regex=False).astype("int64")
df["area_m2"] = df["area_m2"].astype(float)
df.to_csv("out/analytic.csv", index=False)

ex = pd.DataFrame([dict(step=s, rule=ru, n_before=b, n_after=a, n_removed=b - a, rationale=ra)
                   for s, ru, b, a, ra in steps])
ex.to_csv("out/exclusions.csv", index=False)

n_valid = len(df)
n_ctrl = meta["n_records"]
sum_valid = int(df["price_cad"].sum())
sum_ctrl = int(meta["sum_price_cad"])
ids = set(df["sale_id"])
missing = [f"S{i:05d}" for i in range(1, n_ctrl + 1) if f"S{i:05d}" not in ids]
by_muni = df["municipality"].value_counts().to_dict()
muni_gap = {m: meta["n_by_municipality"][m] - int(by_muni.get(m, 0)) for m in meta["n_by_municipality"]}
n_unread = n_ctrl - n_valid

issues = []
if garbled_idx:
    issues.append({"issue": f"{len(garbled_idx)} data lines (file lines {garbled_idx[0]}-{garbled_idx[-1]}) are garbled/corrupted (random characters, wrong field count, invalid sale_id); sale_ids {missing[0]}-{missing[-1]} absent from readable rows" if missing else f"{len(garbled_idx)} garbled lines",
                   "handling": "excluded as unreadable; not reconstructed or imputed; their implied price sum is derived only as control minus valid sum"})
if n_thous:
    issues.append({"issue": f"{n_thous} records have price_cad exported as a quoted string with comma thousands separators (e.g. \"280,000\")",
                   "handling": "parsed unambiguously by removing the thousands separators (pattern d{1,3}(,ddd)+ enforced); records retained"})
if n_lines - len(s1) != n_unread:
    issues.append({"issue": "unreadable line count differs from control-count gap", "handling": "reported"})
issues.append({"issue": f"per-municipality shortfall versus control counts: {muni_gap}",
               "handling": "reported; shortfalls sum to the number of unreadable records"})

import numpy as np
SEED = 20261030
rng = np.random.default_rng(SEED)
pr = df["price_cad"].to_numpy(float); ppm = (df["price_cad"] / df["area_m2"]).to_numpy()
B = 2000
bm, bmed = [], []
for _ in range(B):
    i = rng.integers(0, n_valid, n_valid)
    bm.append(pr[i].mean()); bmed.append(np.median(ppm[i]))
boot = {"B": B, "mean_price_ci95": [round(float(x), 2) for x in np.percentile(bm, [2.5, 97.5])],
        "median_price_per_m2_ci95": [round(float(x), 2) for x in np.percentile(bmed, [2.5, 97.5])]}

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": sum_valid,
    "sum_price_unreadable_implied": sum_ctrl - sum_valid,
    "mean_price_valid": round(float(df["price_cad"].mean()), 4),
    "median_price_per_m2_valid": round(float((df["price_cad"] / df["area_m2"]).median()), 4),
    "feasibility": "complete" if n_unread == 0 else "partial",
    "feasibility_reason": None if n_unread == 0 else
        f"{n_unread} of the {n_ctrl} control records could not be analysed: their export lines "
        f"(file lines {garbled_idx[0]}-{garbled_idx[-1]}, sale_ids {missing[0]}-{missing[-1]} by gap) are corrupted with random "
        f"characters and cannot be read unambiguously; they were excluded, not reconstructed. Their implied price sum is "
        f"{sum_ctrl - sum_valid} CAD (control minus valid).",
    "data_issues": issues,
    "n_input_rows": {"sales.csv": n_lines},
    "random_seed": SEED,
    "_diagnostics": {"bootstrap": boot, "mean_price_all_control_records": round(sum_ctrl / n_ctrl, 2), "municipality_shortfall": muni_gap, "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 2) if n_unread else None},
}
json.dump(res, open("out/results.json", "w"), indent=2)
print(json.dumps(res, indent=2))