import openpyxl, csv, sys, collections
downloaded_at, outpath, zipdir = sys.argv[1].strip(), sys.argv[2], sys.argv[3]  # zipdir = folder where MLS_HPI_Oct_2026.zip was unzipped
URL = "https://www.crea.ca/files/mls-hpi-data/MLS_HPI_Oct_2026.zip"
geos = {"AGGREGATE": "Canada (CREA aggregate of participating boards)", "QUEBEC": "Quebec province (CREA aggregate of participating boards)",
        "MONTREAL_CMA": "Montréal CMA", "QUEBEC_CMA": "Québec CMA", "CENTRE_DU_QUEBEC": "Centre-du-Québec", "ESTRIE": "Estrie", "MAURICIE": "Mauricie"}
out = []; cov = collections.defaultdict(lambda: [None, None, 0]); allnames = None
for fname, tag in [("Not Seasonally Adjusted (M).xlsx", "NSA"), ("Seasonally Adjusted (M).xlsx", "SA")]:
    wb = openpyxl.load_workbook(f"{zipdir}/{fname}", read_only=True)
    allnames = wb.sheetnames
    for sh, geo in geos.items():
        ws = wb[sh]; it = ws.iter_rows(values_only=True); hdr = next(it)
        for row in it:
            if row[0] is None: continue
            p = row[0].strftime("%Y-%m")
            if p < "2021-01" or p > "2026-10": continue
            for col, val in zip(hdr[1:], row[1:]):
                if val is None: continue
                is_idx = "HPI" in col
                ptype = col.replace("_HPI_SA","").replace("_Benchmark_SA","").replace("_HPI","").replace("_Benchmark","").replace("_"," ")
                sname = f"MLS HPI {tag} - {col}"
                out.append({"source": "CREA MLS Home Price Index (crea.ca, full-history Excel in MLS_HPI_Oct_2026.zip)",
                            "series_name": sname, "geography": geo,
                            "property_scope": f"{ptype} benchmark (resale, MLS listings, hedonic benchmark; {'seasonally adjusted' if tag=='SA' else 'not seasonally adjusted'})",
                            "frequency": "monthly", "period": p, "value": val,
                            "base_period": "2005-01=100" if is_idx else "CAD (benchmark price)",
                            "download_url": URL, "downloaded_at": downloaded_at})
                k = (geo, sname); c = cov[k]; c[0] = c[0] or p; c[1] = p; c[2] += 1
cols = ["source","series_name","geography","property_scope","frequency","period","value","base_period","download_url","downloaded_at"]
with open(outpath, "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=cols); w.writeheader(); w.writerows(out)
print("all sheets:", allnames)
for k, v in sorted(cov.items()):
    if "Composite" in k[1]: print("coverage", k, v)
print("rows written", len(out))