"""SQL for the views two scripts both rebuild.

`fetch_reval_2026.py` (2026 proposed values) and `build_growth_6yr.py` (value
history) each own half of `parcel_reval_geom`, the matview Martin publishes as
/tiles/parcel_reval_geom. Keeping the DDL here means neither can drift from the
other, and neither has to import the other (which would be circular).

The value-year columns are what lets the map compare *any* two years rather
than the fixed 2020→now pair it started with. Every year from START_YEAR to the
newest year in the history gets a column — not just the triennial reappraisal
years, because a parcel can get a new value in any year (new construction,
splits, board of revision) and the owner of that parcel wants to see it.

Two shapes of the same data, on purpose:
  * `parcel_value_years` is **dense** — yYYYY is the value *in force* that year,
    carried forward. That is what the ask agent should read: one column, no
    arithmetic.
  * `parcel_reval_geom` (the tiles) is **sparse** — yYYYY is set only in the
    years the value actually changed, and the map walks back to the most recent
    one it finds (MapLibre `coalesce`). Dense would put 21 numbers on every
    parcel in every tile; sparse puts the ~6 that say something, so adding 13
    more selectable years made the tiles smaller, not bigger.
"""
from __future__ import annotations

import datetime as dt

# The history reaches back to 1996, but the county was a different place then
# and coverage thins out fast (43k parcels in 1996 against 96k today). The
# table keeps everything from 2005 for the ask agent to query.
START_YEAR = 2005
# What the *map* offers as chips. Deliberately shorter than the table: one
# button per year back to 2005 was 20 buttons and the user asked for fewer.
# The popup's value-history block still lists every year the county has.
MAP_START_YEAR = 2020
# What "six-year growth" meant before the years became selectable — still the
# From the map opens on.
BASE_YEAR = 2020


def newest_year(cur=None) -> int:
    """The newest year the Auditor has recorded — not the calendar year. The
    county has not set 2026 values yet, and a column of nulls would be a chip
    that colours nothing."""
    newest = None
    if cur is not None:
        cur.execute("SELECT max(year) FROM parcel_value_history")
        newest = cur.fetchone()[0]
    return int(newest or dt.date.today().year - 1)


def year_list(cur=None) -> list[int]:
    """Every year the dense table carries."""
    return list(range(START_YEAR, newest_year(cur) + 1))


def map_years(cur=None) -> list[int]:
    """The subset the map colours by and ships to the browser."""
    return list(range(MAP_START_YEAR, newest_year(cur) + 1))


def _dense_cols(years: list[int]) -> str:
    # "Value in force in Y" = the most recent row at or before Y, because the
    # county records a value only when it changes. Never count rows per year.
    return ",\n       ".join(
        f"(array_agg(total ORDER BY year DESC) FILTER (WHERE year <= {y}))[1] AS y{y}"
        for y in years)


def value_years_sql(years: list[int]) -> str:
    return f"""
CREATE MATERIALIZED VIEW IF NOT EXISTS parcel_value_years AS
SELECT parcel_no,
       {_dense_cols(years)},
       (array_agg(total ORDER BY year DESC))[1] AS y_cur
  FROM parcel_value_history
 GROUP BY parcel_no
WITH NO DATA
"""


# Kept alongside the wide view: it is what the ask agent reads for "growth since
# 2020" questions, and it is one join instead of arithmetic over 20 columns.
GROWTH_SQL = f"""
CREATE MATERIALIZED VIEW IF NOT EXISTS parcel_growth_6yr AS
SELECT parcel_no,
       (array_agg(total ORDER BY year DESC) FILTER (WHERE year <= {BASE_YEAR}))[1] AS old_total,
       (array_agg(total ORDER BY year DESC))[1] AS cur_total
  FROM parcel_value_history
 GROUP BY parcel_no
WITH NO DATA
"""


def _sparse_cols(years: list[int]) -> str:
    """First year dense (the chain has to end somewhere, and the dense table
    already carries the value in force that year), the rest only when the value
    moved. ST_AsMVT omits a NULL attribute entirely, so a parcel that sat still
    from 2020 to 2023 costs one number in the tile instead of four."""
    out = [f"vy.y{years[0]}"]
    for prev, y in zip(years, years[1:]):
        out.append(f"CASE WHEN vy.y{y} IS DISTINCT FROM vy.y{prev} THEN vy.y{y} END AS y{y}")
    return ",\n                   ".join(out)


def reval_geom_sql(years: list[int]) -> str:
    return f"""
CREATE MATERIALIZED VIEW IF NOT EXISTS parcel_reval_geom AS
-- pct MUST be cast off numeric: ST_AsMVT encodes numeric as a *string*, and a
-- MapLibre "step" expression on a string silently paints nothing ("Expected
-- value to be of type number").
SELECT g.parcel_no, g.addr, r.current_value, r.proposed_value,
       r.delta, r.pct::double precision AS pct, r.class_code,
       -- Value history rides along on the same tiles (filled by
       -- gis_scripts/build_growth_6yr.py), sparse: see _sparse_cols. LEFT JOIN
       -- because a parcel with no history still needs its 2026 bands. The map
       -- divides two of these itself, so any From→To pair colours without new
       -- tiles.
       {_sparse_cols(years)},
       vy.y_cur,
       gr.old_total AS g_old, gr.cur_total AS g_cur,
       CASE WHEN gr.old_total > 0 AND gr.cur_total > 0
            THEN round(100.0 * (gr.cur_total - gr.old_total)
                       / gr.old_total, 2)::double precision END AS g_pct,
       g.geometry
  FROM parcel_geoms g
  JOIN parcel_reval_2026 r  ON r.parcel_no = g.parcel_no
  LEFT JOIN parcel_value_years vy ON vy.parcel_no = g.parcel_no
  LEFT JOIN parcel_growth_6yr gr  ON gr.parcel_no = g.parcel_no
WITH NO DATA
"""


def _columns(cur, table: str) -> set[str]:
    # pg_attribute, not information_schema: the SQL standard has no notion of a
    # materialized view, so information_schema.columns returns *nothing* for
    # one — which reads as "no columns", i.e. always stale, and silently
    # rebuilt both views on every run.
    cur.execute("""SELECT a.attname FROM pg_attribute a
                     JOIN pg_class c ON c.oid = a.attrelid
                    WHERE c.relname = %s AND a.attnum > 0 AND NOT a.attisdropped""",
                (table,))
    return {r[0] for r in cur.fetchall()}


def ensure_views(cur) -> bool:
    """Create the three views if absent, rebuilding either of the year-shaped
    ones whose column list no longer matches the data (a new year in the
    history adds a column). Returns True when parcel_reval_geom was
    (re)created, which is also when Martin needs a restart to see it."""
    years, shown = year_list(cur), map_years(cur)
    if not ({f"y{y}" for y in years} | {"y_cur"}) <= _columns(cur, "parcel_value_years"):
        cur.execute("DROP MATERIALIZED VIEW IF EXISTS parcel_reval_geom")   # depends on it
        cur.execute("DROP MATERIALIZED VIEW IF EXISTS parcel_value_years")
    cur.execute(value_years_sql(years))
    cur.execute("CREATE UNIQUE INDEX IF NOT EXISTS parcel_value_years_pk "
                "ON parcel_value_years(parcel_no)")
    cur.execute(GROWTH_SQL)
    cur.execute("CREATE UNIQUE INDEX IF NOT EXISTS parcel_growth_6yr_pk "
                "ON parcel_growth_6yr(parcel_no)")
    # A tile view built before a year existed is silently short a column, and
    # the map's fill would grey the county for that year. A view built when the
    # map offered *more* years has columns to drop, and those are dead weight in
    # every tile. Either way rebuild rather than patch: nothing depends on it,
    # and the refresh costs ~20s.
    have = _columns(cur, "parcel_reval_geom")
    fresh = bool(have) and {c for c in have if c.startswith("y") and c != "y_cur"} \
        == {f"y{y}" for y in shown}
    if not fresh:
        cur.execute("DROP MATERIALIZED VIEW IF EXISTS parcel_reval_geom")
    cur.execute(reval_geom_sql(shown))
    cur.execute("CREATE INDEX IF NOT EXISTS parcel_reval_geom_gix "
                "ON parcel_reval_geom USING GIST (geometry)")
    return not fresh