# Author: Simon-Pierre Boucher — contact@spboucher.ai
#
"""Extraction of the house sample from the raw SQLite database.

The extraction logic replicates the original ``hedonic_maison.py`` exactly:
single-family listings (``category = 'house'``) with a positive price and a
description of at least 20 characters, keeping the same field parsing and the
same defaults so the resulting sample is byte-for-byte identical
(n = 17,087).
"""

import json
import sqlite3

import numpy as np
import pandas as pd


def _parse_land_size(size_str):
    """Parse the leading numeric token of Land.SizeTotal (e.g. '5000 sqft')."""
    return float("".join(c for c in size_str.split()[0].replace(",", "") if c.isdigit() or c == "."))


def load_houses(db_path):
    """Return the analysis sample of houses as a DataFrame.

    Columns: id, price, log_price, bedrooms, bathrooms, half_baths, parking,
    stories, land_size, latitude, longitude, prop_type, remarks,
    remarks_length.
    """
    conn = sqlite3.connect(db_path)
    rows = conn.execute(
        "SELECT id, category, price_value, bedrooms, bathrooms, data "
        "FROM properties WHERE category = 'house'"
    ).fetchall()
    conn.close()

    records = []
    for pid, _category, price, beds, baths, data_json in rows:
        try:
            data = json.loads(data_json)
        except (json.JSONDecodeError, TypeError):
            continue

        remarks = data.get("PublicRemarks", "")
        if not remarks or not isinstance(remarks, str) or len(remarks.strip()) < 20:
            continue
        if not price or price <= 0:
            continue

        parking = 0
        try:
            parking = int(data["Property"].get("ParkingSpaceTotal", 0))
        except (KeyError, TypeError, ValueError):
            pass

        stories = 0
        try:
            stories = int(data["Building"].get("StoriesTotal", 0))
        except (KeyError, TypeError, ValueError):
            pass

        half_bath = 0
        try:
            half_bath = int(data["Building"].get("HalfBathTotal", 0))
        except (KeyError, TypeError, ValueError):
            pass

        land_size = 0
        try:
            land_size = _parse_land_size(data.get("Land", {}).get("SizeTotal", "0"))
        except (IndexError, ValueError):
            pass

        prop_type = data.get("Property", {}).get("Type", "")

        lat = lon = None
        try:
            lat = float(data["Property"]["Address"]["Latitude"])
            lon = float(data["Property"]["Address"]["Longitude"])
        except (KeyError, TypeError, ValueError):
            pass

        records.append({
            "id": pid,
            "price": price,
            "log_price": np.log(price),
            "bedrooms": beds or 0,
            "bathrooms": baths or 0,
            "half_baths": half_bath,
            "parking": parking,
            "stories": stories,
            "land_size": land_size,
            "latitude": lat,
            "longitude": lon,
            "prop_type": prop_type,
            "remarks": remarks,
            "remarks_length": len(remarks),
        })

    return pd.DataFrame(records).reset_index(drop=True)