# -----------------------------------------------------------------------------
# Home-Ka — US real-estate aggregator (Groupe KA)
# Author: Simon-Pierre Boucher — contact@spboucher.ai
# propertymatch.py : PROPERTY vs LISTING separation.
#
# A Listing is one publication by one source; a Property is the physical
# asset. Every ingested listing is matched to (or creates) a row in the
# `properties` table so the property keeps existing after the listing goes
# off-market, and so several sources listing the same house converge on one
# property (feeds the dedup pass and the listing page's history).
#
# Matching keys, in order of confidence:
#   1) APN (assessor parcel number) + state — authoritative when both known
#   2) normalized address key: house number + significant street words +
#      unit + zip (or city+state when zip is missing)
# -----------------------------------------------------------------------------
from __future__ import annotations

import re
import sqlite3

from .normalize import strip_accents
from .schema import Listing

__all__ = ["addr_key", "match_or_create", "refresh_property"]

# street-suffix/directional noise dropped from the street-word key so
# "123 N Main St" and "123 North Main Street" produce the same key
_NOISE = {
    "st", "street", "ave", "avenue", "blvd", "boulevard", "dr", "drive",
    "ct", "court", "cir", "circle", "ln", "lane", "rd", "road", "pl",
    "place", "ter", "terrace", "pkwy", "parkway", "hwy", "highway", "trl",
    "trail", "sq", "square", "way", "loop", "n", "s", "e", "w", "north",
    "south", "east", "west", "ne", "nw", "se", "sw",
}

_UNIT_RE = re.compile(
    r"\b(?:apt|apartment|unit|ste|suite|#)\s*\.?\s*([a-z0-9-]+)", re.I)
_NUM_RE = re.compile(r"^\s*(\d+[a-z]?(?:-\d+[a-z]?)?)\b", re.I)


def addr_key(street: str, unit: str, city: str, state: str,
             zip_code: str) -> str | None:
    """Normalized address key, or None when the address is unusable."""
    a = strip_accents((street or "").lower()).replace("’", "'")
    m = _NUM_RE.match(a)
    if not m:
        return None
    number = m.group(1)
    u = (unit or "").strip().lower()
    mu = _UNIT_RE.search(a)
    if mu and not u:
        u = mu.group(1)
    a = _UNIT_RE.sub(" ", a)
    words = [w for w in re.findall(r"[a-z]+", a[m.end():]) if w not in _NOISE]
    if not words:
        return None
    where = zip_code or f"{strip_accents((city or '').lower())},{(state or '').lower()}"
    if not where.strip(","):
        return None
    return f"{number}|{' '.join(sorted(set(words)))}|{u}|{where}"


def match_or_create(con: sqlite3.Connection, lst: Listing,
                    now: float) -> int | None:
    """Return the property id for a listing, creating the property if new.

    Address+unit key first: an APN is NOT always unit-unique (whole condo
    buildings share one assessor account in several counties), so the APN is
    only a fallback when no usable address key exists."""
    key = addr_key(lst.street_address, lst.unit, lst.city, lst.state,
                   lst.zip_code)
    if key:
        row = con.execute(
            "SELECT id FROM properties WHERE addr_key=?", (key,)).fetchone()
        if row:
            return row["id"]
    elif lst.apn and lst.state:
        row = con.execute(
            "SELECT id FROM properties WHERE apn=? AND state=?",
            (lst.apn, lst.state)).fetchone()
        if row:
            return row["id"]
    if not key and not lst.apn:
        return None   # nothing reliable to key the property on
    cur = con.execute(
        """INSERT INTO properties (addr_key, apn, street_address, unit, city,
           state, zip_code, county, property_type, year_built,
           living_area_sqft, lot_size_sqft, lat, lng, details,
           first_seen, last_seen)
           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?, '{}', ?, ?)""",
        (key, lst.apn or None, lst.street_address, lst.unit, lst.city,
         lst.state, lst.zip_code, lst.county, lst.property_type,
         lst.year_built, lst.living_area_sqft, lst.lot_size_sqft,
         lst.lat, lst.lng, now, now))
    return cur.lastrowid


def refresh_property(con: sqlite3.Connection, property_id: int,
                     lst: Listing, now: float) -> None:
    """Fill the property's missing facts from a fresh listing (never null-out)."""
    con.execute(
        """UPDATE properties SET
             apn=COALESCE(NULLIF(apn,''), ?),
             county=COALESCE(NULLIF(county,''), ?),
             property_type=COALESCE(NULLIF(property_type,''), ?),
             year_built=COALESCE(year_built, ?),
             living_area_sqft=COALESCE(living_area_sqft, ?),
             lot_size_sqft=COALESCE(lot_size_sqft, ?),
             lat=COALESCE(lat, ?), lng=COALESCE(lng, ?),
             last_seen=?
           WHERE id=?""",
        (lst.apn or None, lst.county or None, lst.property_type or None,
         lst.year_built, lst.living_area_sqft, lst.lot_size_sqft,
         lst.lat, lst.lng, now, property_id))