#!/usr/bin/env python3
"""
listings_check.py — Check apartment listings for active/inactive status.

Lives on: Pi 2 at /home/ubuntu/second-brain/listings_check.py
Imports: turso_execute, RAPIDAPI_KEY from serve.py (same directory)
         — keeps credentials in one place, no duplication

Usage:
    cd /home/ubuntu/second-brain
    python3 listings_check.py --dry-run         # report only
    python3 listings_check.py                   # actually update Turso
    python3 listings_check.py --id <id> --dry-run   # check single listing

Detection rules:
    - Zillow:      data.isRentalListingOffMarket == true  (via RapidAPI)
    - Craigslist:  HTTP 410 OR body contains "this posting has been deleted"
    - All others:  marked 'unchecked_unsupported_source', no status change

When a listing is detected inactive:
    - status -> 'rented'  (repurposed: "no longer available, any reason")
    - last_check_result -> 'inactive'
    - last_checked_at -> timestamp

Listings NOT changed:
    - System errors (network, API failure) -> last_checked_at NOT updated,
      so the listing remains stale and gets retried next run
    - Unsupported sources -> last_check_result='unchecked_unsupported_source'
    - Confirmed active -> last_check_result='active'
"""

import sys
import os
import re
import json
import time
import argparse
import urllib.request
import urllib.error
from urllib.parse import urlparse

# Make sure we can import from serve.py regardless of where the script is invoked from
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))

try:
    import requests
except ImportError:
    print("ERROR: requests library not installed.", file=sys.stderr)
    print("Run: pip install requests --break-system-packages", file=sys.stderr)
    sys.exit(1)


def _load_credentials_from_serve():
    """Read TURSO_URL, TURSO_TOKEN, RAPIDAPI_KEY out of serve.py as text.
    
    We do NOT import serve.py — it has side effects (starts HTTP servers) at
    import time. Instead we parse the constants from the source file. The
    source of truth still lives in serve.py; this is just a read.
    """
    serve_path = os.path.join(SCRIPT_DIR, "serve.py")
    if not os.path.exists(serve_path):
        print(f"ERROR: Cannot find serve.py at {serve_path}", file=sys.stderr)
        sys.exit(1)
    with open(serve_path) as f:
        src = f.read()
    creds = {}
    for name in ("TURSO_URL", "TURSO_TOKEN", "RAPIDAPI_KEY"):
        # Match: NAME = "value"   or   NAME = 'value'
        m = re.search(rf'^{name}\s*=\s*["\']([^"\']+)["\']', src, re.MULTILINE)
        if not m:
            print(f"ERROR: Could not find {name} in serve.py", file=sys.stderr)
            sys.exit(1)
        creds[name] = m.group(1)
    return creds


_creds = _load_credentials_from_serve()
TURSO_URL = _creds["TURSO_URL"]
TURSO_TOKEN = _creds["TURSO_TOKEN"]
RAPIDAPI_KEY = _creds["RAPIDAPI_KEY"]


def turso_execute(sql, args=None):
    """Direct Turso call. Mirrors serve.py's turso_execute() exactly."""
    payload = {
        "requests": [
            {"type": "execute", "stmt": {"sql": sql, "args": args or []}},
            {"type": "close"},
        ]
    }
    req = urllib.request.Request(
        f"{TURSO_URL}/v2/pipeline",
        data=json.dumps(payload).encode(),
        headers={
            "Authorization": f"Bearer {TURSO_TOKEN}",
            "Content-Type": "application/json",
        },
        method="POST",
    )
    with urllib.request.urlopen(req, timeout=30) as resp:
        return json.loads(resp.read())


# ---------- Configuration ----------

RAPIDAPI_HOST = "real-time-real-estate-data.p.rapidapi.com"
ACTIVE_STATUSES = ("new", "contacted", "tour")
REQUEST_DELAY_SECONDS = 1.5

HTTP_HEADERS = {
    "User-Agent": (
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) "
        "AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/120.0.0.0 Safari/537.36"
    ),
    "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
    "Accept-Language": "en-US,en;q=0.9",
}

RESULT_ACTIVE = "active"
RESULT_INACTIVE = "inactive"
RESULT_UNSUPPORTED = "unchecked_unsupported_source"
RESULT_ERROR = "error"


# ---------- Turso helpers ----------

def turso_arg(value, type_="text"):
    """Wrap a value in Turso's typed-arg format. None/empty -> {type: null}."""
    if value is None or value == "":
        return {"type": "null"}
    return {"type": type_, "value": str(value)}


def turso_rows_to_dicts(result_payload):
    """Convert a turso_execute() result payload into a list of dicts.
    
    Mirrors how serve.py's geocode_address parses results:
    payload['results'][0]['response']['result']['rows']
    """
    try:
        result = result_payload["results"][0]["response"]["result"]
    except (KeyError, IndexError, TypeError):
        return []
    cols = [c["name"] for c in result.get("cols", [])]
    out = []
    for row in result.get("rows", []):
        d = {}
        for i, col in enumerate(cols):
            cell = row[i]
            d[col] = cell.get("value") if isinstance(cell, dict) else cell
        out.append(d)
    return out


# ---------- Source detection ----------

def detect_source(url):
    if not url:
        return "unknown"
    host = urlparse(url).netloc.lower().replace("www.", "")
    if "zillow.com" in host:
        return "zillow"
    if "craigslist.org" in host:
        return "craigslist"
    if "apartments.com" in host:
        return "apartments"
    if "rentsfnow.com" in host:
        return "rentsfnow"
    if "trulia.com" in host:
        return "trulia"
    if "realtor.com" in host:
        return "realtor"
    if "hotpads.com" in host:
        return "hotpads"
    if "rent.com" in host:
        return "rent"
    if "redfin.com" in host:
        return "redfin"
    return host


def extract_zpid(url):
    """Extract zpid from a Zillow URL. Returns None if not found."""
    if not url:
        return None
    m = re.search(r"/(\d+)_zpid/?", url)
    return m.group(1) if m else None


# ---------- Per-source checkers ----------
# Each returns: (result, detail)

def check_zillow(url):
    zpid = extract_zpid(url)
    if not zpid:
        return RESULT_ERROR, "could not extract zpid from URL"

    api_url = f"https://{RAPIDAPI_HOST}/property-details?zpid={zpid}"
    req = urllib.request.Request(
        api_url,
        headers={
            "x-rapidapi-host": RAPIDAPI_HOST,
            "x-rapidapi-key": RAPIDAPI_KEY,
        },
    )
    try:
        with urllib.request.urlopen(req, timeout=20) as resp:
            payload = json.loads(resp.read())
    except urllib.error.HTTPError as e:
        return RESULT_ERROR, f"RapidAPI HTTP {e.code}"
    except urllib.error.URLError as e:
        return RESULT_ERROR, f"RapidAPI URL error: {e.reason}"
    except (json.JSONDecodeError, ValueError) as e:
        return RESULT_ERROR, f"RapidAPI bad JSON: {e}"
    except Exception as e:
        return RESULT_ERROR, f"RapidAPI unexpected: {type(e).__name__}: {e}"

    data = payload.get("data") or {}
    if not data:
        return RESULT_INACTIVE, "RapidAPI returned no data block"

    off_market = data.get("isRentalListingOffMarket")
    home_status = data.get("homeStatus")

    # Conservative rule: only mark inactive when BOTH signals agree.
    # off_market=true alone is not enough — Zillow sometimes has the two fields
    # disagree (e.g. off_market=true while homeStatus=FOR_RENT during re-listing).
    # When they disagree, we wait for them to align rather than risk a false positive.
    home_status_active = home_status in ("FOR_RENT", "FOR_SALE")

    if off_market is True and not home_status_active:
        return RESULT_INACTIVE, f"isRentalListingOffMarket=true, homeStatus={home_status}"
    if off_market is True and home_status_active:
        return RESULT_ACTIVE, f"FIELDS DISAGREE: off_market=true but homeStatus={home_status} — staying active"
    if off_market is False:
        return RESULT_ACTIVE, f"isRentalListingOffMarket=false, homeStatus={home_status}"

    # Field missing — fall back to homeStatus alone
    if home_status_active:
        return RESULT_ACTIVE, f"homeStatus={home_status} (off-market field missing)"
    if home_status:
        return RESULT_INACTIVE, f"homeStatus={home_status} (off-market field missing)"
    return RESULT_ERROR, "neither isRentalListingOffMarket nor homeStatus present"


def check_craigslist(url):
    try:
        resp = requests.get(url, headers=HTTP_HEADERS, timeout=15, allow_redirects=True)
    except requests.exceptions.RequestException as e:
        return RESULT_ERROR, f"HTTP fetch failed: {type(e).__name__}"

    if resp.status_code == 410:
        return RESULT_INACTIVE, "HTTP 410"
    if resp.status_code == 404:
        return RESULT_INACTIVE, "HTTP 404"
    if resp.status_code >= 400:
        return RESULT_ERROR, f"HTTP {resp.status_code}"

    body_lower = resp.text.lower()
    if "this posting has been deleted" in body_lower:
        return RESULT_INACTIVE, "body: 'this posting has been deleted'"
    if "this posting has expired" in body_lower:
        return RESULT_INACTIVE, "body: 'this posting has expired'"

    return RESULT_ACTIVE, f"HTTP {resp.status_code}, no deletion phrases"


# ---------- Main check loop ----------

def fetch_listings_to_check(specific_id=None):
    """Pull listings to check from Turso via serve.py's turso_execute()."""
    if specific_id:
        payload = turso_execute(
            "SELECT id, address, status, url, last_check_result, last_checked_at "
            "FROM apartment_listings WHERE id = ?",
            [turso_arg(specific_id)]
        )
    else:
        placeholders = ",".join("?" * len(ACTIVE_STATUSES))
        payload = turso_execute(
            f"SELECT id, address, status, url, last_check_result, last_checked_at "
            f"FROM apartment_listings "
            f"WHERE status IN ({placeholders}) AND url IS NOT NULL AND url != '' "
            f"ORDER BY COALESCE(last_checked_at, '1970') ASC",
            [turso_arg(s) for s in ACTIVE_STATUSES]
        )
    return turso_rows_to_dicts(payload)


def update_listing(listing_id, result, become_inactive, dry_run):
    """Update last_checked_at, last_check_result, and optionally status='rented'."""
    now = time.strftime("%Y-%m-%dT%H:%M:%SZ", time.gmtime())
    if dry_run:
        return
    if become_inactive:
        turso_execute(
            "UPDATE apartment_listings "
            "SET last_checked_at=?, last_check_result=?, status='rented', updated_at=? "
            "WHERE id=?",
            [turso_arg(now), turso_arg(result), turso_arg(now), turso_arg(listing_id)]
        )
    else:
        turso_execute(
            "UPDATE apartment_listings "
            "SET last_checked_at=?, last_check_result=?, updated_at=? "
            "WHERE id=?",
            [turso_arg(now), turso_arg(result), turso_arg(now), turso_arg(listing_id)]
        )


def check_listing(listing):
    url = listing.get("url") or ""
    source = detect_source(url)
    if source == "zillow":
        return check_zillow(url)
    if source == "craigslist":
        return check_craigslist(url)
    return RESULT_UNSUPPORTED, f"source={source}"


def run(dry_run=False, specific_id=None, verbose=True):
    listings = fetch_listings_to_check(specific_id)
    if not listings:
        msg = f"No listings found for id={specific_id}." if specific_id else "No active listings to check."
        print(msg)
        return {"checked": 0, "marked_inactive": 0, "unsupported": 0, "active": 0, "errors": 0}

    print(f"Checking {len(listings)} listing(s)... (dry_run={dry_run})\n")

    counts = {"checked": 0, "marked_inactive": 0, "unsupported": 0, "active": 0, "errors": 0}
    inactive_details = []

    for i, listing in enumerate(listings, 1):
        result, detail = check_listing(listing)
        counts["checked"] += 1

        if result == RESULT_INACTIVE:
            counts["marked_inactive"] += 1
            inactive_details.append({
                "id": listing["id"],
                "address": listing.get("address"),
                "url": listing.get("url"),
                "current_status": listing.get("status"),
                "detail": detail,
            })
        elif result == RESULT_UNSUPPORTED:
            counts["unsupported"] += 1
        elif result == RESULT_ACTIVE:
            counts["active"] += 1
        elif result == RESULT_ERROR:
            counts["errors"] += 1

        if verbose:
            tag = {
                RESULT_INACTIVE: "INACTIVE",
                RESULT_ACTIVE: "active",
                RESULT_UNSUPPORTED: "skip",
                RESULT_ERROR: "ERROR",
            }.get(result, result)
            addr = (listing.get("address") or "")[:50]
            print(f"[{i:3d}/{len(listings)}] {tag:>8}  {addr:50s}  {detail}")

        # Write to DB for everything except errors. Errors leave last_checked_at
        # stale so the listing gets retried next run.
        if result != RESULT_ERROR:
            try:
                update_listing(
                    listing["id"],
                    result,
                    become_inactive=(result == RESULT_INACTIVE),
                    dry_run=dry_run,
                )
            except Exception as e:
                print(f"    -> WARNING: DB update failed: {e}", file=sys.stderr)
                counts["errors"] += 1

        if i < len(listings):
            time.sleep(REQUEST_DELAY_SECONDS)

    print()
    print("=" * 60)
    suffix = " (DRY RUN — no changes written)" if dry_run else ""
    print(f"Summary{suffix}:")
    print(f"  Checked:               {counts['checked']}")
    print(f"  Still active:          {counts['active']}")
    label = "Would mark inactive" if dry_run else "Marked inactive    "
    print(f"  {label}:   {counts['marked_inactive']}")
    print(f"  Unsupported source:    {counts['unsupported']}")
    print(f"  Errors (will retry):   {counts['errors']}")

    if inactive_details:
        print()
        verb = "Would mark" if dry_run else "Marked"
        print(f"{verb} these as inactive:")
        for d in inactive_details:
            print(f"  - {d['address']}")
            print(f"    {d['url']}")
            print(f"    detail: {d['detail']}")
            print()

    return counts


def main():
    parser = argparse.ArgumentParser(description="Check apartment listings for active/inactive status")
    parser.add_argument("--dry-run", action="store_true",
                        help="Don't write changes, just report what would happen")
    parser.add_argument("--id", help="Check only a specific listing by id (debugging)")
    parser.add_argument("--quiet", action="store_true", help="Only print summary")
    args = parser.parse_args()

    counts = run(dry_run=args.dry_run, specific_id=args.id, verbose=not args.quiet)

    # Exit non-zero on errors so HA / cron can detect failures
    if counts["errors"] > 0:
        sys.exit(2)
    sys.exit(0)


if __name__ == "__main__":
    main()