"""Load sites into local DB:
- target parcel 66:41:0204016:10 (approximated coords from neighbouring quarters)
- all under-construction ЖК in Ekb from prod (via SSH tunnel)
"""
import sqlite3, psycopg2, pathlib

DB = pathlib.Path(__file__).parent / "analysis.db"

PARCEL = {
    "site_id": "parcel:66:41:0204016:10",
    "kind": "parcel",
    "name": "Участок 66:41:0204016:10",
    "address": "г. Екатеринбург, ул. Маневровая, 31А (Старая Сортировка, Железнодорожный) · кад.№ 66:41:0204016:10 · 0.28 га · кад.ст. 23.7 млн ₽",
    "district": None,
    "obj_class": None,
    "developer": None,
    "flat_count": None,
    "square_living": 2788.7,    # parcel area m² (Rosreestr polygon)
    "ready_dt": None,
    "obj_status": "parcel",
    "lat": 56.878730,            # polygon centroid (rosreestr2coord)
    "lon": 60.522677,
    "obj_id": None,
}

def main():
    conn = sqlite3.connect(DB)
    pg = psycopg2.connect(host="127.0.0.1", port=15432, user="gendesign",
                          password="2J2SBPMKuS998fiwhtQqDhMI", dbname="gendesign")
    cur = pg.cursor()

    # latest snapshot per obj_id
    cur.execute("""
        WITH latest AS (
            SELECT obj_id, MAX(snapshot_date) AS d FROM domrf_kn_objects
             WHERE region_cd='66' AND is_ekb=true
             GROUP BY obj_id
        )
        SELECT o.obj_id, o.comm_name, o.addr, o.district_name, o.obj_class,
               o.dev_name, o.flat_count, o.square_living::float, o.ready_dt,
               o.obj_status, o.latitude::float, o.longitude::float
          FROM domrf_kn_objects o
          JOIN latest l USING (obj_id)
         WHERE o.snapshot_date = l.d
           AND o.latitude IS NOT NULL AND o.longitude IS NOT NULL
           AND o.site_status='Строящиеся'
         ORDER BY o.obj_id;
    """)
    rows = cur.fetchall()
    print(f"Pulled {len(rows)} ЖК from prod")

    conn.execute("DELETE FROM sites;")
    conn.execute("""INSERT INTO sites(site_id,kind,name,address,district,obj_class,developer,
        flat_count,square_living,ready_dt,obj_status,lat,lon,obj_id) VALUES (
        :site_id,:kind,:name,:address,:district,:obj_class,:developer,
        :flat_count,:square_living,:ready_dt,:obj_status,:lat,:lon,:obj_id)""", PARCEL)

    for r in rows:
        (obj_id, name, addr, district, klass, dev, flat_count, sq_living, ready_dt,
         obj_status, lat, lon) = r
        if not (50 < lat < 65 and 55 < lon < 70):  # sanity for Sverdl
            continue
        conn.execute("""INSERT INTO sites(site_id,kind,name,address,district,obj_class,
            developer,flat_count,square_living,ready_dt,obj_status,lat,lon,obj_id)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (f"jk:{obj_id}", "jk", name, addr, district, klass, dev, flat_count,
             sq_living, str(ready_dt) if ready_dt else None, obj_status, lat, lon, obj_id))
    conn.commit()
    n = conn.execute("SELECT count(*) FROM sites").fetchone()[0]
    by_kind = dict(conn.execute("SELECT kind, count(*) FROM sites GROUP BY 1").fetchall())
    print(f"Sites in local DB: {n} ({by_kind})")
    conn.close(); pg.close()

if __name__ == "__main__":
    main()