"""F1-conf-04: reconcile property-sales export to control totals. Reads only data/."""
import csv, json, re, datetime, statistics, os

OUT = "out"
os.makedirs(OUT, exist_ok=True)
meta = json.load(open("data/metadata.json"))
with open("data/sales.csv", newline="", encoding="utf-8") as f:
    raw_lines = f.read().splitlines()
n_lines = len(raw_lines) - 1  # data lines excluding header
rows = list(csv.reader(raw_lines))
header, data = rows[0], rows[1:]
assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]
assert len(data) == n_lines

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

# Step 0: lines read
step("0_read", "all data lines in sales.csv (excluding header)", n_lines, n_lines, "starting count")

# Step 1: structural parse (5 fields, valid id)
s1 = [r for r in data if len(r) == 5 and re.fullmatch(r"S\d{5}", r[0])]
step("1_structure", "exactly 5 CSV fields and sale_id matches S+5 digits", n_lines, len(s1),
     "corrupted/garbled lines whose fields cannot be read unambiguously; not reconstructed")

# Step 2: field validity (date, price, area, municipality)
n_quoted_thousands = 0
s2 = []
for r in s1:
    try:
        d = datetime.date.fromisoformat(r[2])
        p = r[3]
        if re.fullmatch(r"\d{1,3}(,\d{3})+", p):
            n_quoted_thousands += 1
            p = p.replace(",", "")
        if not re.fullmatch(r"\d+", p):
            continue
        a = float(r[4])
        if not (a > 0) or not r[1].strip():
            continue
        s2.append(dict(sale_id=r[0], municipality=r[1], sale_date=d.isoformat(),
                       price_cad=int(p), area_m2=a, _area_str=r[4]))
    except ValueError:
        continue
step("2_fields", "valid ISO date, integer price (CSV-quoted thousands separators allowed), area>0, non-empty municipality",
     len(s1), len(s2), "fields must be readable unambiguously")

# Step 3: duplicates
seen, s3 = set(), []
for r in s2:
    if r["sale_id"] not in seen:
        seen.add(r["sale_id"]); s3.append(r)
step("3_dedup", "unique sale_id", len(s2), len(s3), "sale_id must be unique")

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

by_mun = {}
for r in valid:
    by_mun[r["municipality"]] = by_mun.get(r["municipality"], 0) + 1
mun_gap = {m: meta["n_by_municipality"][m] - by_mun.get(m, 0) for m in meta["n_by_municipality"]}
unknown_mun = sorted(set(by_mun) - set(meta["n_by_municipality"]))

issues = []
if n_struct_bad:
    issues.append({"issue": f"{n_struct_bad} data lines of sales.csv are corrupted (garbled characters, wrong field counts, sale_id not S+5 digits); file lines {[i+2 for i,r in enumerate(data) if not (len(r)==5 and re.fullmatch(r'S[0-9]{5}', r[0]))][0]}-{[i+2 for i,r in enumerate(data) if not (len(r)==5 and re.fullmatch(r'S[0-9]{5}', r[0]))][-1]}",
                   "handling": "excluded from the analytic dataset; not reconstructed or imputed; counted as unreadable records"})
if n_quoted_thousands:
    issues.append({"issue": f"{n_quoted_thousands} records have price_cad exported as a quoted string with a comma thousands separator (e.g. \"280,000\")",
                   "handling": "CSV quoting makes the value unambiguous; parsed as integer after removing the separator; records retained"})
if len(s1) - len(s2):
    issues.append({"issue": f"{len(s1)-len(s2)} structurally valid records have unreadable field values", "handling": "excluded"})
if len(s2) - len(s3):
    issues.append({"issue": f"{len(s2)-len(s3)} duplicate sale_id records", "handling": "kept first occurrence"})
issues.append({"issue": f"record count reconciliation: control {n_ctrl} vs {n_valid} valid; per-municipality deficits {mun_gap}",
               "handling": "reported; per-municipality deficits sum to %d; unreadable records' municipalities not inferred" % sum(mun_gap.values())})
issues.append({"issue": f"price-sum reconciliation: control {sum_ctrl} vs valid {sum_valid}; implied unreadable sum {sum_ctrl-sum_valid}",
               "handling": "reported as sum_price_unreadable_implied; not allocated to individual records"})
if unknown_mun:
    issues.append({"issue": f"municipalities not in control totals: {unknown_mun}", "handling": "retained"})

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_struct_bad} data lines of sales.csv are corrupted/garbled and their fields cannot be read unambiguously "
    f"(n_input_rows {n_lines} = control count; {n_valid} records were readable). Their implied price sum is "
    f"{sum_ctrl - sum_valid} CAD; they were not reconstructed.")

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(mean_valid, 4),
    "median_price_per_m2_valid": round(med_ppm2, 4),
    "feasibility": feas,
    "feasibility_reason": reason,
    "data_issues": issues,
    "n_input_rows": {"sales.csv": n_lines},
    "random_seed": None,
    "valid_by_municipality": dict(sorted(by_mun.items())),
    "control_minus_valid_by_municipality": mun_gap,
}
json.dump(results, open(f"{OUT}/results.json", "w"), indent=2)

with open(f"{OUT}/analytic.csv", "w", newline="") as f:
    w = csv.writer(f)
    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_str"]])

step("4_final", "analytic dataset", n_valid, n_valid, "n_analysed")
with open(f"{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)

print(f"n_valid={n_valid} n_unreadable={n_unread} sum_valid={sum_valid} mean={mean_valid:.2f} median_ppm2={med_ppm2:.2f} feasibility={feas}")