"""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, 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 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 local.execute("""UPDATE district_economics SET real_velocity_6mo=?, real_velocity_prior_6mo=?, real_trend_ratio=?, trend_factor=? WHERE district=?""", (v_rec, v_prior, ratio, 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}{'trend':>6}{'v_rec':>6}{'v_pr':>6}{'ratio':>6}") for r in local.execute("""SELECT district, price_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, tf, vr, vp, rr = r print(f"{d:<22}{pf or 0:>8.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()