"""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
from pathlib import Path
import numpy as np

DATA = Path("data"); OUT = Path("out"); OUT.mkdir(exist_ok=True)
COLS = ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]
meta = json.loads((DATA / "metadata.json").read_text())

with open(DATA / "sales.csv", newline="", encoding="utf-8") as f:
    raw_lines = f.read().splitlines()
header, data_lines = raw_lines[0], [l for l in raw_lines[1:]]
assert header.split(",") == COLS, header
n_lines = len(data_lines)
parsed = list(csv.reader(data_lines))

ID_RE = re.compile(r"S\d{5}")
PLAIN_INT = re.compile(r"\d+")
THOUSANDS = re.compile(r"\d{1,3}(,\d{3})+")   # e.g. "280,000" (quoted in file)
AREA_RE = re.compile(r"\d+(\.\d+)?")

def valid_date(s):
    try:
        datetime.date.fromisoformat(s); return bool(re.fullmatch(r"\d{4}-\d{2}-\d{2}", s))
    except ValueError:
        return False

# Step 1: structural parse (exactly 5 fields)
s1 = [r for r in parsed if len(r) == 5]
# Step 2: every field matches its declared format (price plain integer OR
# integer with comma thousands separators), date valid, area positive number,
# municipality non-empty alphabetic
s2, n_thousands = [], 0
for r in s1:
    sid, mun, d, p, a = r
    ok = (ID_RE.fullmatch(sid) and re.fullmatch(r"[A-Za-z][A-Za-z \-']*", mun)
          and valid_date(d) and (PLAIN_INT.fullmatch(p) or THOUSANDS.fullmatch(p))
          and AREA_RE.fullmatch(a) and float(a) > 0)
    if ok:
        if THOUSANDS.fullmatch(p): n_thousands += 1
        s2.append([sid, mun, d, int(p.replace(",", "")), float(a), bool(THOUSANDS.fullmatch(p))])
# Step 3: duplicate sale_id removal (keep none ambiguous: drop all copies)
from collections import Counter
cnt = Counter(r[0] for r in s2)
s3 = [r for r in s2 if cnt[r[0]] == 1]

ledger = [
    ("1", "row has exactly 5 CSV fields", n_lines, len(s1),
     "lines with wrong field count are corrupted export garbage; content cannot be read unambiguously"),
    ("2", "all fields match declared formats (S+5 digits id, municipality text, valid ISO date, integer price incl. quoted comma-thousands form, positive numeric area)", len(s1), len(s2),
     "fields that do not match their format cannot be read unambiguously"),
    ("3", "sale_id unique", len(s2), len(s3), "duplicate ids would be ambiguous"),
]
with open(OUT / "exclusions.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(["step", "rule", "n_before", "n_after", "n_removed", "rationale"])
    for st, rule, b, a, rat in ledger:
        w.writerow([st, rule, b, a, b - a, rat])

with open(OUT / "analytic.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(COLS)
    for r in s3:
        w.writerow(r[:5])

n_valid = len(s3)
price = np.array([r[3] for r in s3], dtype=np.int64)
area = np.array([r[4] for r in s3], dtype=float)
thou = np.array([r[5] for r in s3], dtype=bool)
n_ctrl = int(meta["n_records"]); sum_ctrl = int(meta["sum_price_cad"])
sum_valid = int(price.sum())
n_unread = n_ctrl - n_valid
n_bad_struct = n_lines - len(s1)
n_bad_fmt = len(s1) - len(s2)
n_dup = len(s2) - len(s3)

# municipality reconciliation (diagnostic only)
mun_cnt = Counter(r[1] for r in s3)
mun_gap = {m: int(meta["n_by_municipality"].get(m, 0)) - mun_cnt.get(m, 0)
           for m in sorted(set(meta["n_by_municipality"]) | set(mun_cnt))}
ids = sorted(int(r[0][1:]) for r in s3)
missing_ids = sorted(set(range(1, n_ctrl + 1)) - set(ids))

issues = []
if n_bad_struct:
    issues.append({"issue": f"{n_bad_struct} data lines (file lines {min(i+2 for i,r in enumerate(parsed) if len(r)!=5)}-{max(i+2 for i,r in enumerate(parsed) if len(r)!=5)}) are corrupted: garbage characters and wrong number of fields; sale_id sequence gap S{missing_ids[0]:05d}-S{missing_ids[-1]:05d} ({len(missing_ids)} ids) coincides",
                   "handling": "excluded; not reconstructed or imputed; their count and implied price sum reported via n_unreadable_records and sum_price_unreadable_implied"})
if n_bad_fmt:
    issues.append({"issue": f"{n_bad_fmt} rows with 5 fields but a field not matching its format", "handling": "excluded"})
if n_thousands:
    issues.append({"issue": f"{n_thousands} rows store price_cad as a quoted string with comma thousands separators (e.g. \"280,000\")",
                   "handling": "kept: quoting makes the field unambiguous under CSV rules and the pattern d{1,3}(,ddd)+ has a single integer reading; commas removed and converted to integer"})
if n_dup:
    issues.append({"issue": f"{n_dup} rows with duplicated sale_id", "handling": "excluded"})

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_bad_struct} data lines of sales.csv are corrupted (unreadable garbage, wrong field count), "
    f"{n_bad_fmt} rows had malformed fields and {n_dup} duplicated ids; these records were not reconstructed. "
    f"Their implied price sum 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": 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(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,
    "diagnostics": {
        "n_lines_wrong_field_count": n_bad_struct,
        "n_rows_bad_format": n_bad_fmt,
        "n_rows_duplicate_id": n_dup,
        "n_price_thousands_separator_parsed": n_thousands,
        "municipality_count_gap_control_minus_valid": mun_gap,
        "n_missing_ids_in_sequence": len(missing_ids),
        "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 6) if n_unread else None,
        "sensitivity_excluding_thousands_separator_rows": {
            "n": int((~thou).sum()), "sum_price": int(price[~thou].sum()),
            "mean_price": round(float(price[~thou].mean()), 6),
            "median_price_per_m2": round(float(np.median(price[~thou] / area[~thou])), 6)},
        "sale_date_min": min(r[2] for r in s3), "sale_date_max": max(r[2] for r in s3),
    },
}
(OUT / "results.json").write_text(json.dumps(results, indent=2, ensure_ascii=False) + "\n")
print(f"n_lines={n_lines} n_valid={n_valid} n_unreadable={n_unread} sum_valid={sum_valid} "
      f"mean={results['mean_price_valid']} median_ppm2={results['median_price_per_m2_valid']} feas={feas}")