spb/home-ka

1# -----------------------------------------------------------------------------2# Home-Ka — US real-estate aggregator (Groupe KA)3# Author: Simon-Pierre Boucher — contact@spboucher.ai4# propertymatch.py : PROPERTY vs LISTING separation.5#6# A Listing is one publication by one source; a Property is the physical7# asset. Every ingested listing is matched to (or creates) a row in the8# `properties` table so the property keeps existing after the listing goes9# off-market, and so several sources listing the same house converge on one10# property (feeds the dedup pass and the listing page's history).11#12# Matching keys, in order of confidence:13#   1) APN (assessor parcel number) + state — authoritative when both known14#   2) normalized address key: house number + significant street words +15#      unit + zip (or city+state when zip is missing)16# -----------------------------------------------------------------------------17from __future__ import annotations1819import re20import sqlite32122from .normalize import strip_accents23from .schema import Listing2425__all__ = ["addr_key", "match_or_create", "refresh_property"]2627# street-suffix/directional noise dropped from the street-word key so28# "123 N Main St" and "123 North Main Street" produce the same key29_NOISE = {30    "st", "street", "ave", "avenue", "blvd", "boulevard", "dr", "drive",31    "ct", "court", "cir", "circle", "ln", "lane", "rd", "road", "pl",32    "place", "ter", "terrace", "pkwy", "parkway", "hwy", "highway", "trl",33    "trail", "sq", "square", "way", "loop", "n", "s", "e", "w", "north",34    "south", "east", "west", "ne", "nw", "se", "sw",35}3637_UNIT_RE = re.compile(38    r"\b(?:apt|apartment|unit|ste|suite|#)\s*\.?\s*([a-z0-9-]+)", re.I)39_NUM_RE = re.compile(r"^\s*(\d+[a-z]?(?:-\d+[a-z]?)?)\b", re.I)404142def addr_key(street: str, unit: str, city: str, state: str,43             zip_code: str) -> str | None:44    """Normalized address key, or None when the address is unusable."""45    a = strip_accents((street or "").lower()).replace("’", "'")46    m = _NUM_RE.match(a)47    if not m:48        return None49    number = m.group(1)50    u = (unit or "").strip().lower()51    mu = _UNIT_RE.search(a)52    if mu and not u:53        u = mu.group(1)54    a = _UNIT_RE.sub(" ", a)55    words = [w for w in re.findall(r"[a-z]+", a[m.end():]) if w not in _NOISE]56    if not words:57        return None58    where = zip_code or f"{strip_accents((city or '').lower())},{(state or '').lower()}"59    if not where.strip(","):60        return None61    return f"{number}|{' '.join(sorted(set(words)))}|{u}|{where}"626364def match_or_create(con: sqlite3.Connection, lst: Listing,65                    now: float) -> int | None:66    """Return the property id for a listing, creating the property if new.6768    Address+unit key first: an APN is NOT always unit-unique (whole condo69    buildings share one assessor account in several counties), so the APN is70    only a fallback when no usable address key exists."""71    key = addr_key(lst.street_address, lst.unit, lst.city, lst.state,72                   lst.zip_code)73    if key:74        row = con.execute(75            "SELECT id FROM properties WHERE addr_key=?", (key,)).fetchone()76        if row:77            return row["id"]78    elif lst.apn and lst.state:79        row = con.execute(80            "SELECT id FROM properties WHERE apn=? AND state=?",81            (lst.apn, lst.state)).fetchone()82        if row:83            return row["id"]84    if not key and not lst.apn:85        return None   # nothing reliable to key the property on86    cur = con.execute(87        """INSERT INTO properties (addr_key, apn, street_address, unit, city,88           state, zip_code, county, property_type, year_built,89           living_area_sqft, lot_size_sqft, lat, lng, details,90           first_seen, last_seen)91           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?, '{}', ?, ?)""",92        (key, lst.apn or None, lst.street_address, lst.unit, lst.city,93         lst.state, lst.zip_code, lst.county, lst.property_type,94         lst.year_built, lst.living_area_sqft, lst.lot_size_sqft,95         lst.lat, lst.lng, now, now))96    return cur.lastrowid979899def refresh_property(con: sqlite3.Connection, property_id: int,100                     lst: Listing, now: float) -> None:101    """Fill the property's missing facts from a fresh listing (never null-out)."""102    con.execute(103        """UPDATE properties SET104             apn=COALESCE(NULLIF(apn,''), ?),105             county=COALESCE(NULLIF(county,''), ?),106             property_type=COALESCE(NULLIF(property_type,''), ?),107             year_built=COALESCE(year_built, ?),108             living_area_sqft=COALESCE(living_area_sqft, ?),109             lot_size_sqft=COALESCE(lot_size_sqft, ?),110             lat=COALESCE(lat, ?), lng=COALESCE(lng, ?),111             last_seen=?112           WHERE id=?""",113        (lst.apn or None, lst.county or None, lst.property_type or None,114         lst.year_built, lst.living_area_sqft, lst.lot_size_sqft,115         lst.lat, lst.lng, now, property_id))116