#!/usr/bin/env python3
"""eeter-db – lennukite ja lennufirmade andmebaas OFFLINE (eraldi teenus, dongliga seotud pole).

Allikad (data/db/, uuendus: `eeter_db.py update`, vajab internetti):
  aircraft.csv.gz          tar1090-db (wiedehopf): ICAO → registreering, tüübikood, lipud, kirjeldus, aasta, omanik/operaator (~618 000)
  icao_aircraft_types2.js  tar1090-db: tüübikood → [nimi, kategooria (L2J …), turbulentsiklass]   (gzip JSON)
  vrs_airlines.csv         Virtual Radar Server standing-data: ICAO → nimi, IATA (VÄRSKEM – eelistatud nimi)
  airlines.dat             OpenFlights: ICAO kolmetäheline → nimi, raadiokutsung, riik (ODbL; osa vananenud, nt AUL)
  flags.js                 tar1090: ICAO 24-bit aadressiplokid → registreerimisriik
Käivitusel ehitatakse (kui allikad on uuemad) data/db/eeter.sqlite – päring < 1 ms, mälu väike.

API (127.0.0.1:8091):
  GET /api/lookup?icao=461fa3&callsign=FIN50J   → {"aircraft": {...}, "airline": {...}, "country": "...", "type": {...}}
  GET /api/aircraft/<icao>   GET /api/callsign/<kutsung>   GET /api/status
Kasutus:  eeter_db.py [serve] [--port 8091]  |  eeter_db.py update  |  eeter_db.py lookup 461fa3 FIN50J
"""
import argparse, csv, datetime as dt, gzip, io, json, os, re, sqlite3, sys, urllib.request
from pathlib import Path

DB = Path(__file__).resolve().parent.parent / "data" / "db"
SQL = DB / "eeter.sqlite"
SOURCES = {
    "aircraft.csv.gz": "https://github.com/wiedehopf/tar1090-db/raw/csv/aircraft.csv.gz",
    "icao_aircraft_types2.js": "https://raw.githubusercontent.com/wiedehopf/tar1090-db/master/db/icao_aircraft_types2.js",
    "airlines.dat": "https://raw.githubusercontent.com/jpatokal/openflights/master/data/airlines.dat",
    "flags.js": "https://raw.githubusercontent.com/wiedehopf/tar1090/master/html/flags.js",
    "vrs_airlines.csv": "https://raw.githubusercontent.com/vradarserver/standing-data/main/airlines/schema-01/airlines.csv",
}
CATEGORY = {"L": "maismaalennuk", "S": "hüdroplaan", "A": "amfiib", "H": "helikopter", "G": "güroplaan", "T": "kallutatav rootor"}
ENGINE = {"J": "reaktiiv", "T": "turbopropeller", "P": "kolb", "E": "elektri", "R": "rakett"}
WTC = {"L": "kerge", "M": "keskmine", "H": "raske", "J": "superraske (A380)"}
FLAGS = ["sõjaväe", "huvitav", "PIA (privaatsusaadress)", "LADD (omaniku soovil peidetud)"]


def log(m):
    print(f"[{dt.datetime.now():%H:%M:%S}] {m}", flush=True)


# ------------------------------------------------------------------ uuendus + ehitus

def update():
    DB.mkdir(parents=True, exist_ok=True)
    for name, url in SOURCES.items():
        tmp = DB / (name + ".tmp")
        log(f"laen {name} …")
        with urllib.request.urlopen(url, timeout=60) as r, open(tmp, "wb") as f:
            f.write(r.read())
        os.replace(tmp, DB / name)
    build(force=True)


def build(force=False):
    srcs = [DB / n for n in SOURCES]
    missing = [p.name for p in srcs if not p.exists()]
    if missing:
        sys.exit(f"puuduvad allikad: {missing} – käivita: eeter_db.py update")
    if not force and SQL.exists() and SQL.stat().st_mtime > max(p.stat().st_mtime for p in srcs):
        return
    log("ehitan eeter.sqlite …")
    tmp = SQL.with_suffix(".tmp")
    tmp.unlink(missing_ok=True)
    c = sqlite3.connect(tmp)
    c.executescript("""
      CREATE TABLE aircraft(icao TEXT PRIMARY KEY, reg TEXT, type TEXT, flags TEXT, descr TEXT, year TEXT, ownop TEXT) WITHOUT ROWID;
      CREATE TABLE types(code TEXT PRIMARY KEY, name TEXT, cat TEXT, wtc TEXT) WITHOUT ROWID;
      CREATE TABLE airlines(icao TEXT, name TEXT, callsign TEXT, country TEXT, active INTEGER);
      CREATE TABLE ranges(start INTEGER, end INTEGER, country TEXT, cc TEXT);
      CREATE TABLE vrs(icao TEXT PRIMARY KEY, name TEXT, iata TEXT) WITHOUT ROWID;
      CREATE TABLE meta(k TEXT PRIMARY KEY, v TEXT);""")
    n = 0
    with gzip.open(DB / "aircraft.csv.gz", "rt", encoding="utf-8", errors="replace") as f:
        rows = []
        for line in f:
            p = line.rstrip("\n").split(";")
            if len(p) < 7: continue
            rows.append((p[0].upper(), p[1], p[2], p[3], p[4], p[5], p[6]))
            if len(rows) >= 50000:
                c.executemany("INSERT OR REPLACE INTO aircraft VALUES (?,?,?,?,?,?,?)", rows); n += len(rows); rows = []
        c.executemany("INSERT OR REPLACE INTO aircraft VALUES (?,?,?,?,?,?,?)", rows); n += len(rows)
    types = json.loads(gzip.open(DB / "icao_aircraft_types2.js").read())
    c.executemany("INSERT OR REPLACE INTO types VALUES (?,?,?,?)", [(k, *(v + ["", "", ""])[:3]) for k, v in types.items()])
    al = []
    with open(DB / "airlines.dat", encoding="utf-8", errors="replace") as f:
        for p in csv.reader(f):
            if len(p) >= 8 and re.fullmatch(r"[A-Z]{3}", p[4] or ""):
                al.append((p[4], p[1], "" if p[5] == "\\N" else p[5], "" if p[6] == "\\N" else p[6], 1 if p[7] == "Y" else 0))
    c.executemany("INSERT INTO airlines VALUES (?,?,?,?,?)", al)
    c.execute("CREATE INDEX airlines_icao ON airlines(icao)")
    with open(DB / "vrs_airlines.csv", encoding="utf-8-sig", errors="replace") as f:
        vrs = [(row["ICAO"], row["Name"], row["IATA"]) for row in csv.DictReader(f) if re.fullmatch(r"[A-Z]{3}", row.get("ICAO") or "")]
    c.executemany("INSERT OR REPLACE INTO vrs VALUES (?,?,?)", vrs)
    rg = [(int(m[1], 16), int(m[2], 16), m[3], m[4]) for m in
          re.finditer(r"start:\s*0x([0-9A-Fa-f]+),\s*end:\s*0x([0-9A-Fa-f]+),\s*country:\s*\"([^\"]*)\",\s*country_code:\s*\"([^\"]*)\"",
                      (DB / "flags.js").read_text(encoding="utf-8"))]
    c.executemany("INSERT INTO ranges VALUES (?,?,?,?)", rg)
    c.execute("INSERT INTO meta VALUES ('built', ?)", (dt.datetime.now().isoformat(timespec="seconds"),))
    c.commit(); c.close()
    os.replace(tmp, SQL)
    log(f"valmis: {n} lennukit, {len(types)} tüüpi, {len(al)} lennufirmat, {len(rg)} riigiplokki")


# ------------------------------------------------------------------ päringud

class Db:
    def __init__(self):
        build()
        self.c = sqlite3.connect(f"file:{SQL}?mode=ro", uri=True, check_same_thread=False)
        self.ranges = self.c.execute("SELECT start, end, country, cc FROM ranges ORDER BY end - start").fetchall()

    def country(self, icao):
        try: a = int(icao, 16)
        except (TypeError, ValueError): return None
        for s, e, name, cc in self.ranges:                  # kitsaim plokk enne (nt Eesti enne laiemat Euroopa plokki)
            if s <= a <= e: return {"name": name, "code": cc}
        return None

    def aircraft(self, icao):
        icao = (icao or "").strip().upper().lstrip("~")
        r = self.c.execute("SELECT reg, type, flags, descr, year, ownop FROM aircraft WHERE icao=?", (icao,)).fetchone()
        out = {"icao": icao, "country": self.country(icao)}
        if r:
            reg, typ, flags, descr, year, ownop = r
            out.update(registration=reg or None, typecode=typ or None, description=descr or None, year=year or None,
                       owner_operator=ownop or None, flags=[FLAGS[i] for i, ch in enumerate(flags or "") if ch == "1" and i < len(FLAGS)])
            if typ: out["type"] = self.type(typ)
        return out

    def type(self, code):
        r = self.c.execute("SELECT name, cat, wtc FROM types WHERE code=?", (code.upper(),)).fetchone()
        if not r: return {"code": code}
        name, cat, wtc = r
        t = {"code": code, "name": name, "manufacturer": name.split(" ")[0] if name else None, "wtc": WTC.get(wtc, wtc)}
        if len(cat) == 3:                                   # L2J = maismaalennuk, 2 mootorit, reaktiiv
            t.update(category=CATEGORY.get(cat[0], cat[0]), engines=cat[1], engine_type=ENGINE.get(cat[2], cat[2]))
        return t

    def callsign(self, cs):
        cs = (cs or "").strip().upper()
        m = re.match(r"^([A-Z]{3})(\d[0-9A-Z]*)$", cs)
        if not m: return {"callsign": cs, "airline": None}
        of = self.c.execute("SELECT name, callsign, country FROM airlines WHERE icao=? ORDER BY active DESC", (m[1],)).fetchone()
        vr = self.c.execute("SELECT name, iata FROM vrs WHERE icao=?", (m[1],)).fetchone()
        if not (of or vr): return {"callsign": cs, "flight": m[2], "airline": None}
        return {"callsign": cs, "flight": m[2],
                "airline": {"icao": m[1], "name": (vr or of)[0], "iata": (vr[1] or None) if vr else None,
                            "telephony": (of[1] or None) if of else None, "country": (of[2] or None) if of else None,
                            "old_name": of[0] if of and vr and of[0] != vr[0] else None}}

    def status(self):
        q = lambda s: self.c.execute(s).fetchone()[0]
        return {"aircraft": q("SELECT count(*) FROM aircraft"), "types": q("SELECT count(*) FROM types"),
                "airlines": q("SELECT count(*) FROM airlines"), "airlines_vrs": q("SELECT count(*) FROM vrs"), "ranges": len(self.ranges),
                "built": q("SELECT v FROM meta WHERE k='built'"),
                "sources": {n: dt.datetime.fromtimestamp((DB / n).stat().st_mtime).isoformat(timespec="seconds") for n in SOURCES}}


def serve(port):
    from aiohttp import web
    db = Db()
    log(f"eeter-db API :{port} – {db.status()['aircraft']} lennukit")

    async def lookup(req):
        icao, cs = req.query.get("icao"), req.query.get("callsign")
        return web.json_response({"aircraft": db.aircraft(icao) if icao else None, "callsign": db.callsign(cs) if cs else None})

    app = web.Application()
    app.add_routes([web.get("/api/lookup", lookup),
                    web.get("/api/aircraft/{icao}", lambda r: web.json_response(db.aircraft(r.match_info["icao"]))),
                    web.get("/api/callsign/{cs}", lambda r: web.json_response(db.callsign(r.match_info["cs"]))),
                    web.get("/api/status", lambda r: web.json_response(db.status()))])
    web.run_app(app, host="127.0.0.1", port=port, print=None)


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("cmd", nargs="?", default="serve", choices=["serve", "update", "build", "lookup"])
    ap.add_argument("args", nargs="*")
    ap.add_argument("--port", type=int, default=8091)
    a = ap.parse_args()
    if a.cmd == "update": update()
    elif a.cmd == "build": build(force=True)
    elif a.cmd == "lookup":
        db = Db()
        print(json.dumps({"aircraft": db.aircraft(a.args[0]), "callsign": db.callsign(a.args[1]) if len(a.args) > 1 else None},
                         ensure_ascii=False, indent=1))
    else: serve(a.port)


if __name__ == "__main__":
    main()
