#!/usr/bin/env python3
# Author: Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca
"""Phase 1 — immutable extraction of the Vrai Prix SQLite snapshot into parquet layers.

Verifies the snapshot checksum, dumps the SQLite schema, and writes the RAW layer
(no transformation other than column typing) plus a manifest.
"""
from __future__ import annotations

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
from sharp_hpi.io import query, sha256_file, sqlite_ro, write_manifest


def main() -> None:
    C.ensure_dirs()
    sha = sha256_file(C.SNAPSHOT_DB)
    assert sha == C.SNAPSHOT_SHA256, f"snapshot checksum mismatch: {sha}"
    con = sqlite_ro(C.SNAPSHOT_DB)
    schema = query(con, "select type, name, sql from sqlite_master where sql is not null order by type, name")
    (C.DOCS / "snapshot_schema.sql").write_text(
        "-- SQLite schema of the Vrai Prix snapshot (read-only dump)\n" + "\n\n".join(schema["sql"].to_list()) + "\n"
    )

    tx = query(con, "select * from transactions").with_columns(
        pl.col("date").str.to_date("%Y-%m-%d"),
        pl.col("year_built").cast(pl.Int32),
    )
    tx.write_parquet(C.RAW_TX)

    units_m = query(
        con,
        """select u.* from units u
           where u.id_provinc in (select distinct id_provinc from transactions where id_provinc is not null and id_provinc <> '')""",
    )
    units_m.write_parquet(C.RAW_UNITS_MATCHED)

    # Housing-stock layer: every roll unit, classification + value columns only (weights for aggregation).
    stock = query(
        con,
        """select id_provinc, code_mun, municipalite, cubf, type_prop, nb_logements, lien_physique,
                  genre_construction, annee_construction, aire_etages_m2, superficie_terrain_m2,
                  valeur_role, valeur_terrain, valeur_batiment, lat, lng, unite_voisinage, dat_cond_marche
           from units""",
    )
    stock.write_parquet(C.RAW_UNITS_STOCK)

    mi = query(con, "select * from market_index")
    mi.write_parquet(C.RAW_MARKET_INDEX)

    write_manifest(
        C.INTERIM / "raw_manifest.json",
        {
            "snapshot_db": str(C.SNAPSHOT_DB.name),
            "snapshot_sha256": sha,
            "snapshot_taken": C.SNAPSHOT_TAKEN,
            "snapshot_source": C.SNAPSHOT_SOURCE,
            "tables": {
                "transactions": {"rows": tx.height, "cols": tx.columns, "date_min": str(tx["date"].min()), "date_max": str(tx["date"].max())},
                "units_matched": {"rows": units_m.height, "cols": units_m.columns},
                "units_stock": {"rows": stock.height, "cols": stock.columns},
                "market_index": {"rows": mi.height, "cols": mi.columns},
            },
        },
    )
    print(f"transactions {tx.height:,} | units matched {units_m.height:,} | stock {stock.height:,} | market_index {mi.height}")


if __name__ == "__main__":
    main()