#!/usr/bin/env python3
# Author: Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca
"""Phase 1 — assemble docs/provenance_report.md from the machine-readable profile / cleaning / geography / vintage outputs."""
from __future__ import annotations

import json
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "src"))
import polars as pl

from sharp_hpi import config as C

R = C.RESULTS


def md(df: pl.DataFrame, cols=None, fmt=None) -> str:
    cols = cols or df.columns
    out = ["| " + " | ".join(cols) + " |", "|" + "---|" * len(cols)]
    for row in df.select(cols).iter_rows(named=True):
        cells = []
        for c in cols:
            v = row[c]
            if isinstance(v, float):
                v = f"{v:,.3f}" if abs(v) < 10 else f"{v:,.0f}"
            cells.append(str(v))
        out.append("| " + " | ".join(cells) + " |")
    return "\n".join(out)


def main() -> None:
    man = json.loads((C.INTERIM / "raw_manifest.json").read_text())
    led = pl.read_csv(R / "cleaning" / "exclusion_ledger.csv").with_columns(pl.col("exclude_reason_headline").fill_null("(retained — headline sample)"))
    flags = pl.read_csv(R / "cleaning" / "flag_counts.csv")
    comp = pl.read_csv(R / "profile" / "completeness_by_year.csv")
    bands = pl.read_csv(R / "profile" / "municipality_size_bands.csv")
    vint = pl.read_csv(R / "vintages" / "vintage_summary.csv")
    geo_join = pl.read_csv(R / "geo" / "join_methods.csv"); agree = pl.read_csv(R / "geo" / "roll_vs_spatial_agreement.csv")
    cov = pl.read_csv(R / "profile" / "coverage_month_type.csv").group_by("month").agg(pl.col("n").sum()).sort("month")
    hsh = pl.read_csv(R / "cleaning" / "headline_share_by_year.csv").pivot(on="in_headline_sample", index="year", values="n").rename({"true": "headline", "false": "excluded"}).with_columns((pl.col("headline") / (pl.col("headline") + pl.col("excluded"))).alias("share_headline"))
    pv = pl.read_csv(R / "cleaning" / "provider_vs_roll_fields.csv")
    text = f"""<!-- Author: Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca ; generated by scripts/06_provenance_report.py -->
# Provenance report — WP22 SHARP-HPI (generated {man['written_utc'][:10]})

## 1. Source and snapshot
- Production database: `{man['snapshot_source']}` (Vrai Prix, www.vrai-prix.com), read through `sqlite3 .backup` on {man['snapshot_taken'][:16]}.
- Snapshot file `data/raw/{man['snapshot_db']}`, SHA-256 `{man['snapshot_sha256']}` (verified at every extraction).
- Tables: `transactions` {man['tables']['transactions']['rows']:,} rows ({man['tables']['transactions']['date_min']} → {man['tables']['transactions']['date_max']}); `units` {man['tables']['units_stock']['rows']:,} assessment-roll units, of which {man['tables']['units_matched']['rows']:,} distinct units are matched to a sale; `market_index` {man['tables']['market_index']['rows']} rows (the site's own median $/m² series, not used).
- Upstream: deeds of sale published by the Quebec land registry and redistributed by the Journal de Québec / Journal de Montréal "Transactions immobilières" feed (`api.qub.ca`), ingested daily by Vrai Prix (`scripts/ingest-jdm.mjs`); assessment roll = MAMH open data (current triennial roll per municipality). No credentials were used or copied; the production file was never opened for writing.

## 2. Dates
The table carries **one date per transaction** (the deed date as published by the feed). Deed signing, registration and feed publication cannot be separated. Four earlier extractions of the same table measure the publication delay:

{md(vint, ["vintage", "vintage_rows", "vintage_last_date", "vintage_max_lag_days", "final_rows_dated_le_last", "share_present", "deleted_since_vintage", "amount_revised"])}

Interpretation: in every vintage, more than 99.95 % of the transactions that the final snapshot dates on or before the vintage's last observed date were already present; no row was deleted and no price revised between vintages. The feed therefore publishes with a lag of about 10–13 days and **does not backfill** older months. Real-time simulations use a 10-day publication delay and a partial final month (results/vintages/arrival_profile_by_lag.csv).

## 3. Coverage
Monthly volumes (results/profile/coverage_month_type.csv): first month {cov['month'][0]} = {cov['n'][0]:,} sales, {cov['month'][1]} = {cov['n'][1]:,}, {cov['month'][3]} = {cov['n'][3]:,} (feed start-up); 2022–2025 average {cov.filter(pl.col('month').str.slice(0,4).is_in(['2022','2023','2024','2025']))['n'].mean():,.0f} per month; last month {cov['month'][-1]} = {cov['n'][-1]:,} ({C.SAMPLE_END} = last date, **partial month**). No sale below $50,000 is collected by the provider (a hard floor visible in every year), which is a selection on price. 2026 monthly volumes run 8–14 % below the same month of 2025.

Municipality coverage (sales matched to the roll, 2021–2026):

{md(bands)}

## 4. Geography
Spatial join of every sale to the MRNF SDA V2026-07 municipal polygons: {md(geo_join)}

Agreement between the roll's municipality code and the spatial join: {agree['share_roll_code_agrees'][0]:.4%} of {agree['n_with_roll_code'][0]:,} matched sales. Series are on 2026 boundaries (municipalities constituted or merged 2022–2026 are listed in results/geo/mamh_constituted_since_2021.csv).

## 5. Completeness by year (share of rows with a non-missing value)

{md(comp.select(['year', 'n', 'id_provinc', 'floor_area', 'year_built', 'building_type', 'cubf', 'nb_logements', 'lien_physique', 'genre_construction', 'superficie_terrain_m2', 'nb_etages', 'valeur_terrain']))}

`building_type` (provider) is backfilled over time and is never used. Provider characteristics equal the roll's for matched sales (year built {pv.filter(pl.col('pair')=='year_built')['share_equal'][0]:.1%}, floor area {pv.filter(pl.col('pair')=='floor_area')['share_equal'][0]:.1%}, lot area {pv.filter(pl.col('pair')=='land_area')['share_equal'][0]:.1%}); the provider's `valeur_role` is the assessment in force at the sale date (equal to the current roll value for {pv.filter(pl.col('pair')=='assessment_vs_current_roll')['share_equal'][0]:.1%} of sales, to the previous roll value for {pv.filter(pl.col('pair')=='assessment_vs_previous_roll')['share_equal'][0]:.1%}).

## 6. Exclusion ledger (first matching rule; headline population = house / condo / plex with roll characteristics)

{md(led)}

Flags counted independently (overlaps allowed):

{md(flags)}

Headline share by year:

{md(hsh)}

## 7. Known selection issues
- Price floor $50,000 (provider): low-price rural sales are missing; indexes for such markets start from a truncated distribution.
- Current-roll characteristics: renovations after the sale are invisible; new constructions of 2025–2026 may still be coded as vacant land in the roll and are then excluded (`built_after_sale` does not fire, `land` does), lowering new-build coverage at the end of the sample.
- No arm's-length indicator: non-market transfers are screened only through the assessment-ratio rule and the robust likelihoods.
- `indéterminé` provider type (14 % of rows): classified from the roll when possible; otherwise excluded.
"""
    (C.DOCS / "provenance_report.md").write_text(text)
    print("docs/provenance_report.md written")


if __name__ == "__main__":
    main()