gendesign/site-finder/09_macro_and_trend.py

"""Macro context + per-district velocity trend.

Adds these tables/columns:
  macro_context        — single-row mortgage rate, city avg price, etc.
  district_economics + (real_velocity_6mo, real_velocity_prior_6mo, real_trend_ratio,
                        sat_factor, trend_factor, price_factor)
  scoring_weights      — single-row config (so we can change weights from UI later)

Source for trend: per-flat register_date (objective_lots).
Source for mortgage rate: prod cbr_mortgage_series (Sverdl region, latest "ставка ипотечная").
"""
import sqlite3
import pathlib
import psycopg2
import datetime as dt
DB = pathlib.Path(__file__).parent / "analysis.db"

# When v_prior == 0 we cannot compute a real ratio, so we synthesise one that
# reflects the *magnitude* of v_rec rather than assigning a flat 2.0.
# REF_VELOCITY: monthly flats/corpus that is considered "high activity" — above
# this level the district earns a ratio near TREND_CAP_VPRIOR_ZERO; below it
# the ratio slides toward neutral (1.0).  Calibrated from EKB corpus data where
# an active corpus typically records 10–20 units/month; 10 is a conservative
# "well-performing" benchmark.
_REF_VELOCITY: float = 10.0        # monthly flats per corpus → full-cap reference
_TREND_CAP_VPRIOR_ZERO: float = 1.5  # maximum ratio when v_prior == 0 (< hard 2.0 cap)

EXTRA = """
CREATE TABLE IF NOT EXISTS macro_context (
    key           TEXT PRIMARY KEY,
    value         REAL,
    label         TEXT,
    period        TEXT,
    fetched_at    TEXT DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE district_economics ADD COLUMN real_velocity_6mo REAL;
ALTER TABLE district_economics ADD COLUMN real_velocity_prior_6mo REAL;
ALTER TABLE district_economics ADD COLUMN real_trend_ratio REAL;
ALTER TABLE district_economics ADD COLUMN sat_factor REAL;
ALTER TABLE district_economics ADD COLUMN trend_factor REAL;
ALTER TABLE district_economics ADD COLUMN price_factor REAL;

CREATE TABLE IF NOT EXISTS scoring_weights (
    component   TEXT PRIMARY KEY,
    weight      REAL NOT NULL,
    note        TEXT
);
"""

def safe_alter(conn, sql):
    for stmt in sql.strip().split(";"):
        s = stmt.strip()
        if not s:
            continue
        try:
            conn.execute(s)
        except sqlite3.OperationalError as e:
            if "duplicate column" not in str(e):
                raise

def main():
    local = sqlite3.connect(DB)
    safe_alter(local, EXTRA)
    local.commit()

    # ---- 1. Pull macro from prod ----
    pg = psycopg2.connect(host="127.0.0.1", port=15432, user="gendesign",
                          password="2J2SBPMKuS998fiwhtQqDhMI", dbname="gendesign")
    cur = pg.cursor()

    # Mortgage rate (Sverdl, latest weighted average)
    cur.execute("""SELECT period, value FROM cbr_mortgage_series
                  WHERE region='sverdl' AND title ILIKE '%ставка%ипотечн%'
                  ORDER BY period DESC LIMIT 1""")
    r = cur.fetchone()
    if r:
        local.execute("""INSERT OR REPLACE INTO macro_context(key,value,label,period)
                          VALUES ('mortgage_rate_sverdl', ?,
                          'Средневзв. ставка по ипотеке (Свердл, %)', ?)""",
                       (float(r[1]), r[0]))
        print(f"Mortgage rate Sverdl ({r[0]}): {r[1]}%")

    # City average POI density per домрф_kn (для prod-стиля POI factor)
    cur.execute("""SELECT count(*)::float / (SELECT count(DISTINCT obj_id) FROM domrf_kn_infrastructure)
                    FROM domrf_kn_infrastructure
                   WHERE distance_m <= 1000""")
    avg_poi = cur.fetchone()[0]
    local.execute("""INSERT OR REPLACE INTO macro_context(key,value,label,period)
                      VALUES ('city_avg_poi_1km', ?, 'Средний POI/ЖК в 1км (Ekb)', '2026-05')""",
                   (avg_poi,))
    print(f"City avg POI 1km: {avg_poi:.1f}")

    pg.close()

    # ---- 2. Velocity trend per district (6mo vs prior 6mo) ----
    today = dt.date.today()
    cut_recent = (today - dt.timedelta(days=180)).isoformat()
    cut_prior  = (today - dt.timedelta(days=360)).isoformat()

    rows = local.execute("""
        SELECT district, register_date, project, corpus
          FROM objective_lots
         WHERE district IS NOT NULL AND register_date IS NOT NULL""").fetchall()
    by_d = {}
    for d, r, p, c in rows:
        try:
            rd = dt.date.fromisoformat(r[:10])
        except ValueError:
            continue
        bucket = "recent" if r >= cut_recent else ("prior" if r >= cut_prior else None)
        if not bucket:
            continue
        by_d.setdefault(d, {"recent": [], "prior": [], "corpuses": set()})
        by_d[d][bucket].append(rd)
        by_d[d]["corpuses"].add((p, c))

    for d, info in by_d.items():
        n_corp = max(len(info["corpuses"]), 1)
        v_rec  = len(info["recent"])  / 6 / n_corp
        v_prior = len(info["prior"]) / 6 / n_corp
        # v_prior > 0: real ratio; v_prior == 0 && v_rec == 0: neutral (None→1.0);
        # v_prior == 0 && v_rec > 0: synthesise ratio scaled by v_rec magnitude so
        # a tiny v_rec stays near 1.0 while a large v_rec approaches _TREND_CAP_VPRIOR_ZERO.
        if v_prior > 0:
            ratio: float | None = v_rec / v_prior
        elif v_rec == 0:
            ratio = None
        else:
            ratio = 1.0 + min(1.0, v_rec / _REF_VELOCITY) * (_TREND_CAP_VPRIOR_ZERO - 1.0)
        # Prod-style clamping
        trend_factor = max(0.7, min(2.0, ratio)) if ratio else 1.0
        # sat_factor: sold_pct in district. >50% = mature market (prod logic +30%
        # multiplier on velocity for mature districts since absorption is proven)
        sold_pct = local.execute(
            "SELECT real_sold_pct FROM district_economics WHERE district=?", (d,)).fetchone()
        sat_factor = 1 + ((sold_pct[0] or 50) - 50) / 100 * 0.30 if sold_pct and sold_pct[0] else 1.0
        local.execute("""UPDATE district_economics
                            SET real_velocity_6mo=?, real_velocity_prior_6mo=?,
                                real_trend_ratio=?, sat_factor=?, trend_factor=?
                          WHERE district=?""",
                       (v_rec, v_prior, ratio, sat_factor, trend_factor, d))

    # price_factor = district median / city median
    city_med = local.execute(
        "SELECT real_median_price_m2 FROM district_economics ORDER BY real_n_lots DESC LIMIT 1"
    ).fetchone()
    # actually weighted by lots
    weighted_rows = local.execute(
        "SELECT real_median_price_m2, real_n_lots FROM district_economics WHERE real_median_price_m2 IS NOT NULL"
    ).fetchall()
    if weighted_rows:
        s = sum((p or 0) * (n or 0) for p, n in weighted_rows)
        wn = sum(n or 0 for _, n in weighted_rows)
        city_med = s / wn if wn else 100
    else:
        city_med = 100
    local.execute("""INSERT OR REPLACE INTO macro_context(key,value,label,period)
                      VALUES ('city_med_price_m2', ?, 'Средневзв. цена м² Ekb (тыс ₽)', '2026-05')""",
                   (city_med,))
    print(f"City weighted median price: {city_med:.1f} тыс ₽/м²")

    local.execute("""UPDATE district_economics
                        SET price_factor = real_median_price_m2 / ?
                      WHERE real_median_price_m2 IS NOT NULL""", (city_med,))
    local.commit()

    # ---- 3. Default weights ----
    weights = [
        ("education", 0.18, "Школы, садики, ВУЗы"),
        ("health",    0.10, "Аптеки, поликлиники, больницы"),
        ("retail",    0.13, "Магазины (большие/средние/малые)"),
        ("transit",   0.15, "Метро, трамвай, автобус"),
        ("leisure",   0.09, "Парки, площадки, спорт"),
        ("economic",  0.30, "Цена, скорость продаж, тренд (Объектив)"),
        ("market",    0.05, "Конкурентная плотность, sat/trend факторы"),
    ]
    local.execute("DELETE FROM scoring_weights")
    for c, w, n in weights:
        local.execute("INSERT INTO scoring_weights VALUES (?,?,?)", (c, w, n))
    local.commit()

    print("\nDistrict trend (top 10 by recent velocity):")
    print(f"{'район':<22}{'price_f':>8}{'sat':>6}{'trend':>6}{'v_rec':>6}{'v_pr':>6}{'ratio':>6}")
    for r in local.execute("""SELECT district, price_factor, sat_factor, trend_factor,
                                     real_velocity_6mo, real_velocity_prior_6mo, real_trend_ratio
                              FROM district_economics
                              WHERE real_velocity_6mo IS NOT NULL
                              ORDER BY real_velocity_6mo DESC LIMIT 10""").fetchall():
        d,pf,sf,tf,vr,vp,rr = r
        print(f"{d:<22}{pf or 0:>8.2f}{sf or 0:>6.2f}{tf or 0:>6.2f}{vr or 0:>6.2f}{vp or 0:>6.2f}{rr or 0:>6.2f}")

    local.close()

if __name__ == "__main__":
    main()