"""Assessed value per parcel for every year the Auditor has recorded one.

Derived from parcel_value_history, which the Auditor's site fills one parcel at
a time (askapi on demand, or gis_scripts/backfill_value_history.py in bulk).
Rerun this after either, so the map's growth layer matches the table.

Writes:
  - parcel_value_years     matview: parcel_no, y2005 … yNNNN, y_cur — the value
                           *in force* that year (carried forward, since the
                           county records a value only when it changes)
  - parcel_growth_6yr      matview: parcel_no, old_total (2020), cur_total
  - wv_value_years.json    docroot: {parcel_no: [one slot per year]}, from
                           MAP_START_YEAR on — the years the map puts a chip on,
                           which is fewer than the table keeps. 0 in a slot means
                           "no change that year", so the map walks back to the
                           last non-zero, exactly as the vector tiles do. Same
                           shape both places, one bug surface.
  - refreshes parcel_reval_geom, the tile source the filled layers stream from

Martin reads the tile view's columns at startup: if this prints "restart
martin", the columns changed and `systemctl restart martin` is required before
the map can colour a new year.
"""
from __future__ import annotations

import datetime as dt
import json
import os
import sys
from pathlib import Path

import psycopg2

sys.path.insert(0, str(Path(__file__).resolve().parent))
from tile_views import BASE_YEAR, ensure_views, map_years, year_list  # noqa: E402

DATA_DIR = Path("/home/m3ac/genoa-entwuerfe.com")
# VIEW_SQL is re-exported for older callers that imported it from here.
from tile_views import GROWTH_SQL as VIEW_SQL  # noqa: E402,F401


def _dsn() -> str:
    for line in (DATA_DIR / ".env.gis").read_text().splitlines():
        if line.startswith("GISPASS="):
            return f"postgresql://gisapp:{line.split('=', 1)[1].strip()}@127.0.0.1:5432/westerville"
    raise SystemExit("GISPASS missing")


def main() -> None:
    conn = psycopg2.connect(_dsn())
    with conn, conn.cursor() as cur:
        needs_restart = ensure_views(cur)
        all_years = year_list(cur)          # what the table keeps, for the agent
        years = map_years(cur)              # what the map offers as chips
        cur.execute("REFRESH MATERIALIZED VIEW parcel_value_years")
        cur.execute("REFRESH MATERIALIZED VIEW parcel_growth_6yr")
        cols = [f"y{y}" for y in years]
        cur.execute(f"""
            SELECT parcel_no, {', '.join(cols)}
              FROM parcel_value_years
             WHERE y{years[-1]} > 0
             ORDER BY parcel_no""")
        rows, changed = {}, [0] * len(years)
        for r in cur.fetchall():
            vals = list(r[1:])
            # Same sparse convention as the tiles: keep a year only where the
            # value moved. It is what makes 21 years cost less than 8 did.
            # 0 means "no new value that year" — so a parcel genuinely valued at
            # $0 (3,599 rows: splits and combines zero one out for a year) is
            # written as -1, a full stop rather than a carry-forward. Without it
            # the dots would keep showing last year's money while the tiles,
            # which can encode a real 0, greyed the parcel out.
            out, prev = [vals[0] or 0], vals[0]
            if vals[0]:
                changed[0] += 1
            for i in range(1, len(vals)):
                v = vals[i]
                if v is not None and v != prev:
                    out.append(v if v > 0 else -1)
                    changed[i] += 1
                else:
                    out.append(0)
                prev = v if v is not None else prev
            rows[r[0]] = out
        cur.execute("""
            SELECT count(*), percentile_cont(0.5) WITHIN GROUP (
                     ORDER BY 100.0*(cur_total-old_total)/old_total),
                   100.0*sum(cur_total-old_total)::numeric/nullif(sum(old_total),0)
              FROM parcel_growth_6yr WHERE old_total > 0 AND cur_total > 0""")
        n, med, total_pct = cur.fetchone()

    # The tile source carries the same numbers for the streamed fill layer.
    with conn, conn.cursor() as cur:
        cur.execute("REFRESH MATERIALIZED VIEW parcel_reval_geom")
        cur.execute("SELECT count(*) FROM parcel_reval_geom WHERE y_cur IS NOT NULL")
        n_tiles = cur.fetchone()[0]
    conn.close()

    out = DATA_DIR / "wv_value_years.json"
    tmp = out.with_suffix(".json.tmp")
    tmp.write_text(json.dumps({
        "format": 2,                    # 1 was dense value-per-year
        "years": years,
        # How many parcels got a new value each year: the reappraisal years move
        # the whole county, the years between move only what was built or split.
        # The map shows it in each chip's tooltip.
        "changed": changed,
        "base_year": BASE_YEAR,
        "built_at": dt.datetime.now(dt.timezone.utc).isoformat(timespec="seconds"),
        "rows": rows,
    }, separators=(",", ":")))
    tmp.chmod(0o644)
    os.replace(tmp, out)

    print(f"[growth] {n} parcels comparable {BASE_YEAR}→now · median +{med:.1f}% "
          f"· county +{total_pct:.1f}%")
    print(f"[years] table {all_years[0]}–{all_years[-1]} · map {years[0]}–{years[-1]}"
          + " · new values per year: "
          + " ".join(f"{y}:{c:,}" for y, c in zip(years, changed)))
    print(f"[json] {out.name}: {len(rows)} parcels, {out.stat().st_size / 1e6:.1f} MB")
    print(f"[tiles] parcel_reval_geom: {n_tiles} polygons carry value history")
    if needs_restart:
        print("[martin] tile columns changed — run: systemctl restart martin")


if __name__ == "__main__":
    main()