#!/usr/bin/env python3
# -----------------------------------------------------------------------------
# House-Ka — one-shot migration (2026-08-27, kept for reference)
# scripts/migrate_types_en.py :
#   1. translates the historical French canonical property types to English;
#   2. derives the missing property_type from the DDF `details` fields
#      ("Building Type" / "Property Type") via normalize_property_type;
#   3. recomputes dedup + quality (publication) for the whole base.
# Run from the repo root: .venv/bin/python scripts/migrate_types_en.py
# -----------------------------------------------------------------------------
import json
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from immoka import db, quality
from immoka.normalize import normalize_property_type

FR_TO_EN = {
    "Maison": "House", "Maison mobile": "Mobile home", "Jumelé": "Semi-detached",
    "Maison de ville": "Townhouse", "Multiplex": "Multi-family",
    "Chalet": "Cottage", "Terrain": "Land", "Fermette/Agricole": "Farm",
}

con = db.connect()

# 1. FR -> EN on the existing canonical values
for fr, en in FR_TO_EN.items():
    n = con.execute("UPDATE listings SET property_type=? WHERE property_type=?",
                    (en, fr)).rowcount
    if n:
        print(f"{fr} -> {en}: {n}")
con.commit()

# 2. derive the type from details for rows without one
updates = []
for r in con.execute("SELECT uid, details FROM listings"
                     " WHERE (property_type IS NULL OR property_type='')"
                     " AND details LIKE '%Type%'"):
    try:
        d = json.loads(r["details"] or "{}")
    except ValueError:
        continue
    raw = d.get("Building Type") or d.get("Property Type") or d.get("Type")
    if not raw:
        continue
    pt = normalize_property_type(str(raw))
    if pt:
        updates.append((pt, r["uid"]))
if updates:
    con.executemany("UPDATE listings SET property_type=? WHERE uid=?", updates)
    con.commit()
print(f"derived from details: {len(updates)}")

# 3. dedup + quality (publication rule: price + city)
hidden = db.refresh_dedup(con)
print(f"dedup: {hidden} hidden")
q = quality.refresh(con)
print(f"quality: {q}")
con.execute("PRAGMA optimize")
con.close()