#!/usr/bin/env python3
"""
Build core/location/src/main/assets/databases/cities.db from GeoNames cities500.txt
(and optional Hebcal Hebrew-aliases JSON).

Produces a Room-compatible SQLite DB for CitiesDatabase (version 1,
identity_hash fdb546c772c4903107d362a1da37031e).
"""
import argparse
import csv
import json
import os
import sqlite3
import sys
from pathlib import Path

ROOM_IDENTITY_HASH = "eeab5d7ef78f570400201c18a9916555"
ROOM_VERSION = 1

CREATE_CITIES = """
CREATE TABLE IF NOT EXISTS `cities` (
  `id` INTEGER NOT NULL,
  `name_en` TEXT NOT NULL,
  `name_he` TEXT,
  `asciiname` TEXT NOT NULL,
  `country` TEXT NOT NULL,
  `admin1` TEXT,
  `lat` REAL NOT NULL,
  `lng` REAL NOT NULL,
  `tzid` TEXT NOT NULL,
  `population` INTEGER NOT NULL,
  `elevation_m` REAL NOT NULL,
  PRIMARY KEY(`id`)
)
"""

CREATE_FTS = """
CREATE VIRTUAL TABLE IF NOT EXISTS `cities_fts` USING FTS4(
  `name_en` TEXT NOT NULL,
  `name_he` TEXT,
  `asciiname` TEXT NOT NULL,
  `country` TEXT NOT NULL,
  `admin1` TEXT,
  content=`cities`
)
"""

TRIGGERS = [
    """CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_cities_fts_BEFORE_UPDATE BEFORE UPDATE ON `cities` BEGIN DELETE FROM `cities_fts` WHERE `docid`=OLD.`rowid`; END""",
    """CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_cities_fts_BEFORE_DELETE BEFORE DELETE ON `cities` BEGIN DELETE FROM `cities_fts` WHERE `docid`=OLD.`rowid`; END""",
    """CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_cities_fts_AFTER_UPDATE AFTER UPDATE ON `cities` BEGIN INSERT INTO `cities_fts`(`docid`, `name_en`, `name_he`, `asciiname`, `country`, `admin1`) VALUES (NEW.`rowid`, NEW.`name_en`, NEW.`name_he`, NEW.`asciiname`, NEW.`country`, NEW.`admin1`); END""",
    """CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_cities_fts_AFTER_INSERT AFTER INSERT ON `cities` BEGIN INSERT INTO `cities_fts`(`docid`, `name_en`, `name_he`, `asciiname`, `country`, `admin1`) VALUES (NEW.`rowid`, NEW.`name_en`, NEW.`name_he`, NEW.`asciiname`, NEW.`country`, NEW.`admin1`); END""",
]


def load_admin1(path):
    """Load admin1CodesASCII.txt: `<country>.<code>\\t<name>\\t<ascii>\\t<geonameid>`."""
    if not path or not os.path.exists(path):
        return {}
    names = {}
    with open(path, encoding="utf-8", newline="") as f:
        for row in csv.reader(f, delimiter="\t", quoting=csv.QUOTE_NONE):
            if len(row) < 2:
                continue
            key = row[0]  # e.g. "US.NY"
            names[key] = row[1]  # "New York"
    return names


def load_hebcal(path):
    if not path or not os.path.exists(path):
        return {}
    try:
        with open(path, encoding="utf-8") as f:
            data = json.load(f)
    except Exception as e:
        print(f"warn: failed to parse hebcal json: {e}", file=sys.stderr)
        return {}
    aliases = {}
    items = data if isinstance(data, list) else data.get("cities", [])
    for it in items:
        if not isinstance(it, dict):
            continue
        he = it.get("he") or it.get("nameHe") or it.get("name_he")
        en = (it.get("en") or it.get("name") or it.get("asciiname") or "").lower()
        if he and en:
            aliases[en] = he
    return aliases


def parse_geonames(path, hebcal, admin1_names):
    with open(path, encoding="utf-8", newline="") as f:
        reader = csv.reader(f, delimiter="\t", quoting=csv.QUOTE_NONE)
        for row in reader:
            if len(row) < 19:
                continue
            try:
                geoid = int(row[0])
                name_en = row[1]
                asciiname = row[2] or name_en
                lat = float(row[4])
                lng = float(row[5])
                country = row[8]
                admin1_code = row[10]
                admin1 = admin1_names.get(f"{country}.{admin1_code}") if admin1_code else None
                if not admin1 and admin1_code:
                    admin1 = admin1_code
                try:
                    population = int(row[14])
                except ValueError:
                    population = 0
                elev_s = row[15] or row[16] or "0"
                try:
                    elevation = float(elev_s)
                except ValueError:
                    elevation = 0.0
                tzid = row[17]
            except (ValueError, IndexError):
                continue
            if not tzid or not country or not name_en:
                continue
            name_he = hebcal.get(asciiname.lower())
            yield (
                geoid,
                name_en,
                name_he,
                asciiname,
                country,
                admin1,
                lat,
                lng,
                tzid,
                population,
                elevation,
            )


def build(geonames_path, hebcal_path, admin1_path, out_path):
    out = Path(out_path)
    out.parent.mkdir(parents=True, exist_ok=True)
    if out.exists():
        out.unlink()

    hebcal = load_hebcal(hebcal_path)
    print(f"hebcal aliases: {len(hebcal)}")
    admin1_names = load_admin1(admin1_path)
    print(f"admin1 codes loaded: {len(admin1_names)}")

    conn = sqlite3.connect(out)
    cur = conn.cursor()
    cur.execute("PRAGMA journal_mode=OFF")
    cur.execute("PRAGMA synchronous=OFF")

    cur.execute(CREATE_CITIES)
    cur.execute(CREATE_FTS)
    for t in TRIGGERS:
        cur.execute(t)

    cur.execute(
        "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY, identity_hash TEXT)"
    )
    cur.execute(
        "INSERT OR REPLACE INTO room_master_table (id, identity_hash) VALUES (42, ?)",
        (ROOM_IDENTITY_HASH,),
    )

    rows = list(parse_geonames(geonames_path, hebcal, admin1_names))
    print(f"parsed rows: {len(rows)}")

    cur.executemany(
        """INSERT OR REPLACE INTO cities
           (id, name_en, name_he, asciiname, country, admin1, lat, lng, tzid, population, elevation_m)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        rows,
    )

    cur.execute(f"PRAGMA user_version = {ROOM_VERSION}")

    cur.execute("CREATE INDEX IF NOT EXISTS idx_cities_lat ON cities(lat)")
    cur.execute("CREATE INDEX IF NOT EXISTS idx_cities_population ON cities(population)")

    cur.execute("INSERT INTO cities_fts(cities_fts) VALUES('rebuild')")

    cur.execute("INSERT INTO cities_fts(cities_fts) VALUES('optimize')")

    conn.commit()
    cur.execute("VACUUM")
    conn.close()

    print(f"wrote {out} ({out.stat().st_size / 1024 / 1024:.1f} MB)")


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--geonames", required=True, help="cities500.txt from GeoNames")
    ap.add_argument("--hebcal", default=None, help="optional hebcal hebrew aliases JSON")
    ap.add_argument("--admin1", default=None, help="optional admin1CodesASCII.txt for full state/province names")
    ap.add_argument("--out", required=True, help="output cities.db path")
    args = ap.parse_args()
    build(args.geonames, args.hebcal, args.admin1, args.out)


if __name__ == "__main__":
    main()
