"""One-shot migration from the Emergent MongoDB to cPanel MySQL.

Usage:
  1. Make sure the cPanel MySQL is reachable from wherever you run this
     (or run it on the cPanel SSH shell).
  2. Export MONGO_URL / DB_NAME for the OLD Emergent Mongo.
  3. Export MYSQL_* (or DATABASE_URL) for the NEW cPanel MySQL.
  4. Run:  python scripts/migrate_from_mongo.py

If you do NOT have direct network access to the cPanel MySQL, run the
script with the `--dump` flag instead — it'll write `mongo_dump.sql`
that you can upload via phpMyAdmin.
"""
import argparse
import os
import sys
from datetime import datetime, timezone
from pathlib import Path

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

from dotenv import load_dotenv
load_dotenv(HERE.parent / ".env")


def _log(msg: str) -> None:
    print(f"[migrate] {msg}", flush=True)


def _connect_mongo():
    from pymongo import MongoClient
    url = os.environ.get("SOURCE_MONGO_URL") or os.environ.get("MONGO_URL")
    name = os.environ.get("SOURCE_DB_NAME") or os.environ.get("DB_NAME")
    if not url or not name:
        raise SystemExit("Set SOURCE_MONGO_URL + SOURCE_DB_NAME (or MONGO_URL + DB_NAME)")
    _log(f"Mongo: {name}")
    return MongoClient(url)[name]


def _strip_mongo_id(doc: dict) -> dict:
    return {k: v for k, v in doc.items() if k != "_id"}


def migrate_direct():
    """Read from Mongo, write directly into SQL via SQLAlchemy."""
    from sqlalchemy.orm import Session

    from db.session import get_engine, init_db
    from db.models import Device, Setting, StatusCheck, User
    import uuid

    init_db()
    engine = get_engine()
    mongo = _connect_mongo()

    with Session(engine) as s:
        # users
        n = 0
        for u in mongo.users.find({}):
            u = _strip_mongo_id(u)
            if not u.get("id"):
                u["id"] = str(uuid.uuid4())
            existing = s.get(User, u["id"])
            if existing:
                continue
            s.add(User(
                id=u["id"],
                username=(u.get("username") or "").strip().lower(),
                password_hash=u.get("password_hash") or "",
                role=u.get("role") or "user",
                created_at=u.get("created_at") or datetime.now(timezone.utc).isoformat(),
                expiry_at=u.get("expiry_at"),
                xtream_mode=u.get("xtream_mode") or "shared",
                xtream_server=u.get("xtream_server") or "",
                xtream_username=u.get("xtream_username") or "",
                xtream_password=u.get("xtream_password") or "",
                note=u.get("note") or "",
            ))
            n += 1
        _log(f"  users: {n} inserted")

        # devices
        n = 0
        for d in mongo.devices.find({}):
            d = _strip_mongo_id(d)
            device_id = d.get("device_id")
            if not device_id or s.get(Device, device_id):
                continue
            s.add(Device(
                device_id=device_id,
                mac_address=d.get("mac_address") or "",
                pin=d.get("pin") or "000000",
                paired=bool(d.get("paired")),
                server_url=d.get("server_url"),
                username=d.get("username"),
                password=d.get("password"),
                profile_name=d.get("profile_name"),
                created_at=d.get("created_at") or datetime.now(timezone.utc).isoformat(),
                paired_at=d.get("paired_at"),
            ))
            n += 1
        _log(f"  devices: {n} inserted")

        # settings  (one singleton row)
        mongo_set = mongo.settings.find_one({"id": "global"})
        if mongo_set:
            mongo_set = _strip_mongo_id(mongo_set)
            mongo_set.pop("id", None)
            existing = s.get(Setting, "global")
            if existing:
                existing.data = mongo_set
            else:
                s.add(Setting(id="global", data=mongo_set))
            _log("  settings: upserted")

        # status_checks (optional)
        n = 0
        for row in mongo.status_checks.find({}).limit(1000):
            row = _strip_mongo_id(row)
            rid = row.get("id") or str(uuid.uuid4())
            if s.get(StatusCheck, rid):
                continue
            ts = row.get("timestamp")
            if hasattr(ts, "isoformat"):
                ts = ts.isoformat()
            s.add(StatusCheck(
                id=rid,
                client_name=row.get("client_name") or "",
                timestamp=ts or datetime.now(timezone.utc).isoformat(),
            ))
            n += 1
        _log(f"  status_checks: {n} inserted")

        s.commit()
    _log("Direct migration complete.")


def migrate_dump(dump_path: Path):
    """Read from Mongo, emit a self-contained SQL file the user can upload to phpMyAdmin."""
    import json
    import uuid

    mongo = _connect_mongo()

    def esc(v):
        if v is None:
            return "NULL"
        if isinstance(v, bool):
            return "1" if v else "0"
        if isinstance(v, (int, float)):
            return str(v)
        if isinstance(v, (dict, list)):
            v = json.dumps(v, ensure_ascii=False)
        s = str(v).replace("\\", "\\\\").replace("'", "\\'")
        return f"'{s}'"

    lines: list[str] = [
        "-- NADIPLAY export from MongoDB\n",
        "SET NAMES utf8mb4;\n",
        "SET FOREIGN_KEY_CHECKS=0;\n\n",
    ]

    # users
    for u in mongo.users.find({}):
        u = _strip_mongo_id(u)
        if not u.get("id"):
            u["id"] = str(uuid.uuid4())
        lines.append(
            "INSERT IGNORE INTO users(id,username,password_hash,role,created_at,expiry_at,"
            "xtream_mode,xtream_server,xtream_username,xtream_password,note) VALUES("
            f"{esc(u['id'])},{esc((u.get('username') or '').lower())},{esc(u.get('password_hash') or '')},"
            f"{esc(u.get('role') or 'user')},{esc(u.get('created_at') or '')},{esc(u.get('expiry_at'))},"
            f"{esc(u.get('xtream_mode') or 'shared')},{esc(u.get('xtream_server') or '')},"
            f"{esc(u.get('xtream_username') or '')},{esc(u.get('xtream_password') or '')},"
            f"{esc(u.get('note') or '')});\n"
        )

    # devices
    for d in mongo.devices.find({}):
        d = _strip_mongo_id(d)
        if not d.get("device_id"):
            continue
        lines.append(
            "INSERT IGNORE INTO devices(device_id,mac_address,pin,paired,server_url,username,"
            "password,profile_name,created_at,paired_at) VALUES("
            f"{esc(d['device_id'])},{esc(d.get('mac_address') or '')},{esc(d.get('pin') or '000000')},"
            f"{esc(bool(d.get('paired')))},{esc(d.get('server_url'))},{esc(d.get('username'))},"
            f"{esc(d.get('password'))},{esc(d.get('profile_name'))},{esc(d.get('created_at') or '')},"
            f"{esc(d.get('paired_at'))});\n"
        )

    # settings (one row, JSON column)
    mongo_set = mongo.settings.find_one({"id": "global"})
    if mongo_set:
        mongo_set = _strip_mongo_id(mongo_set)
        mongo_set.pop("id", None)
        lines.append(
            "REPLACE INTO settings(id,data) VALUES("
            f"'global',{esc(mongo_set)});\n"
        )

    lines.append("\nSET FOREIGN_KEY_CHECKS=1;\n")
    dump_path.write_text("".join(lines), encoding="utf-8")
    _log(f"SQL dump written: {dump_path}  ({dump_path.stat().st_size} bytes)")


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--dump", metavar="FILE", help="write a SQL dump instead of connecting to MySQL")
    args = ap.parse_args()
    if args.dump:
        migrate_dump(Path(args.dump))
    else:
        migrate_direct()


if __name__ == "__main__":
    main()
