#!/usr/bin/env python3
# -----------------------------------------------------------------------------
# Rent-Ka — Rental listings aggregator (Canada, outside Québec)
# scripts/migrate_types_en.py : one-shot migration of the seeded DB rows
#   (imported from Lou-Ka) to the English canon — unit_type («4½» ->
#   «2 bedrooms»), pets (oui/non -> yes/no), amenity/availability/price
#   labels through the central FR->EN funnel. Idempotent.
#   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 rentka import db                                   # noqa: E402
from rentka.normalize import (bedrooms_from_unit_type,  # noqa: E402
                              normalize_unit_type, translate_label_en)

con = db.connect()
rows = con.execute(
    "SELECT uid, unit_type, pets, availability, price_label, amenities,"
    " bedrooms FROM listings").fetchall()
n_ut = n_pets = n_lbl = n_bed = 0
for r in rows:
    sets, args = [], []
    ut = r["unit_type"] or ""
    new_ut = normalize_unit_type(ut) if ut else ut
    if new_ut != ut:
        sets.append("unit_type=?"); args.append(new_ut); n_ut += 1
        # backfill bedrooms from the new canonical type when unknown
        if r["bedrooms"] is None:
            b = bedrooms_from_unit_type(new_ut)
            if b is not None:
                sets.append("bedrooms=?"); args.append(b); n_bed += 1
    pets = r["pets"]
    if pets in ("oui", "non"):
        sets.append("pets=?"); args.append("yes" if pets == "oui" else "no")
        n_pets += 1
    avail = r["availability"] or ""
    new_avail = translate_label_en(avail) if avail else avail
    lbl = r["price_label"] or ""
    new_lbl = translate_label_en(lbl) if lbl else lbl
    try:
        ams = json.loads(r["amenities"] or "[]")
    except ValueError:
        ams = []
    new_ams = [translate_label_en(a) for a in ams]
    if new_avail != avail:
        sets.append("availability=?"); args.append(new_avail)
    if new_lbl != lbl:
        sets.append("price_label=?"); args.append(new_lbl)
    if new_ams != ams:
        sets.append("amenities=?")
        args.append(json.dumps(new_ams, ensure_ascii=False))
    if new_avail != avail or new_lbl != lbl or new_ams != ams:
        n_lbl += 1
    if sets:
        con.execute(f"UPDATE listings SET {', '.join(sets)} WHERE uid=?",
                    args + [r["uid"]])
con.commit()
con.close()
con = db.connect()
after = [r["unit_type"] for r in con.execute(
    "SELECT DISTINCT unit_type FROM listings WHERE unit_type<>''")]
con.close()
print(f"[rent-ka] migrate_types_en: unit_type={n_ut} bedrooms={n_bed} "
      f"pets={n_pets} labels={n_lbl} (of {len(rows)} rows)")
print(f"[rent-ka] distinct unit types after: {sorted(after)}")
print("[rent-ka] now run: run.py quality")