"""
Jalankan sekali: python3 db/migrate.py
Memindahkan data dari JSON ke SQLite.
"""
import sys
import os
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), "..")))

import json
from db.database import conn, cursor

DATA_JSON = "data/data.json"
ADMINS_JSON = "data/admins.json"
VIP_JSON = "data/vip_packages.json"


def migrate_users_columns():
    cols = [r[1] for r in cursor.execute("PRAGMA table_info(users)").fetchall()]
    if "first_name" not in cols:
        cursor.execute("ALTER TABLE users ADD COLUMN first_name TEXT")
    if "username" not in cols:
        cursor.execute("ALTER TABLE users ADD COLUMN username TEXT")
    if "join_date" not in cols:
        cursor.execute("ALTER TABLE users ADD COLUMN join_date TEXT")
    if "last_active" not in cols:
        cursor.execute("ALTER TABLE users ADD COLUMN last_active TEXT")
    conn.commit()
    print("✅ users — kolom diupdate")


def migrate_member_links():
    if not os.path.exists(DATA_JSON):
        print("⚠️  data/data.json tidak ditemukan, skip member_links")
        return
    with open(DATA_JSON) as f:
        data = json.load(f)
    links = data.get("link", [])
    inserted = 0
    for item in links:
        try:
            cursor.execute("""
                INSERT OR IGNORE INTO member_links
                    (user_id, link, chat_id, group_name, status, promo)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (
                item.get("user_id"),
                item.get("link"),
                item.get("chat_id"),
                item.get("group_name"),
                item.get("status", "Aktif"),
                item.get("promo")
            ))
            inserted += cursor.rowcount
        except Exception as e:
            print(f"  ⚠️  skip link {item.get('link')}: {e}")
    conn.commit()
    print(f"✅ member_links — {inserted} link diimport")


def migrate_promos():
    if not os.path.exists(DATA_JSON):
        print("⚠️  data/data.json tidak ditemukan, skip promos")
        return
    with open(DATA_JSON) as f:
        data = json.load(f)
    promos = data.get("promo", {})
    inserted = 0
    for kode, info in promos.items():
        cursor.execute("""
            INSERT OR IGNORE INTO promos (kode, kuota, terpakai, harga, persen, type)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (
            kode,
            info.get("kuota", 0),
            info.get("terpakai", 0),
            info.get("harga"),
            info.get("persen"),
            info.get("type", "fixed")
        ))
        inserted += cursor.rowcount
    conn.commit()
    print(f"✅ promos — {inserted} promo diimport")


def migrate_admins():
    if not os.path.exists(ADMINS_JSON):
        print("⚠️  data/admins.json tidak ditemukan, skip admins")
        return
    with open(ADMINS_JSON) as f:
        admins = json.load(f)
    inserted = 0
    for admin in admins:
        cursor.execute("""
            INSERT OR IGNORE INTO admins (id, nama, vs_enabled)
            VALUES (?, ?, ?)
        """, (
            admin.get("id"),
            admin.get("nama"),
            1 if admin.get("vs_enabled") else 0
        ))
        inserted += cursor.rowcount
    conn.commit()
    print(f"✅ admins — {inserted} admin diimport")


def migrate_vip_packages():
    if not os.path.exists(VIP_JSON):
        print("⚠️  data/vip_packages.json tidak ditemukan, skip vip_packages")
        return
    with open(VIP_JSON) as f:
        packages = json.load(f)
    inserted = 0
    for pkg in packages:
        cursor.execute("""
            INSERT OR IGNORE INTO vip_packages (id, nama, harga, group_id, link)
            VALUES (?, ?, ?, ?, ?)
        """, (
            pkg.get("id"),
            pkg.get("nama"),
            pkg.get("harga"),
            pkg.get("group_id"),
            pkg.get("link")
        ))
        inserted += cursor.rowcount
    conn.commit()
    print(f"✅ vip_packages — {inserted} paket diimport")


def migrate_users_from_links():
    cursor.execute("SELECT DISTINCT user_id FROM member_links")
    user_ids = [r[0] for r in cursor.fetchall()]
    inserted = 0
    for uid in user_ids:
        cursor.execute("INSERT OR IGNORE INTO users (user_id) VALUES (?)", (uid,))
        inserted += cursor.rowcount
    conn.commit()
    print(f"✅ users — {inserted} user dari member_links ditambahkan")


if __name__ == "__main__":
    print("🚀 Mulai migrasi database...\n")
    migrate_users_columns()
    migrate_member_links()
    migrate_promos()
    migrate_admins()
    migrate_vip_packages()
    migrate_users_from_links()

    cursor.execute("SELECT COUNT(*) FROM users")
    print(f"\n📊 Total users    : {cursor.fetchone()[0]}")
    cursor.execute("SELECT COUNT(*) FROM member_links")
    print(f"📊 Total links    : {cursor.fetchone()[0]}")
    cursor.execute("SELECT COUNT(*) FROM admins")
    print(f"📊 Total admins   : {cursor.fetchone()[0]}")
    cursor.execute("SELECT COUNT(*) FROM vip_packages")
    print(f"📊 Total packages : {cursor.fetchone()[0]}")
    cursor.execute("SELECT COUNT(*) FROM promos")
    print(f"📊 Total promos   : {cursor.fetchone()[0]}")

    print("\n✅ Migrasi selesai!")