import os
import json
import sys

try:
    from dotenv import load_dotenv
    load_dotenv()
except ImportError:
    pass

try:
    import pymysql
except ImportError:
    print("=" * 60)
    print("[-] ERROR: pymysql tidak terinstall!")
    print("[*] Silakan jalankan perintah berikut untuk menginstall:")
    print("    pip install pymysql")
    print("=" * 60)
    sys.exit(1)

def run_migration():
    host = os.environ.get('DB_HOST', 'localhost')
    user = os.environ.get('DB_USER', 'root')
    password = os.environ.get('DB_PASSWORD', '')
    port = int(os.environ.get('DB_PORT', 3306))
    db_name = os.environ.get('DB_DATABASE', 'obs_stream')
    
    workspace_dir = os.path.dirname(os.path.abspath(__file__))
    json_path = os.path.join(workspace_dir, "obs_connections.json")
    
    print("=" * 60)
    print("[*] Menjalankan Migrasi Database OBS WebSocket ke MySQL...")
    print("=" * 60)
    
    # 1. Hubungkan ke MySQL Server (tanpa memilih database)
    print(f"[*] Menghubungkan ke MySQL di {host}:{port}...")
    try:
        conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            port=port
        )
        cursor = conn.cursor()
        
        # 2. Buat database jika belum ada
        print(f"[*] Membuat database '{db_name}' jika belum ada...")
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name}")
        conn.commit()
        conn.close()
        
    except Exception as e:
        print(f"[-] Gagal menghubungkan ke server MySQL atau membuat database: {e}")
        print("[!] Pastikan server MySQL berjalan (misalnya Laragon MySQL) dan kredensial sudah benar.")
        return

    # 3. Hubungkan ke database terpilih
    try:
        conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=db_name,
            port=port,
            cursorclass=pymysql.cursors.DictCursor
        )
        cursor = conn.cursor()
        
        # 4. Buat tabel obs_connections
        print("[*] Membuat tabel 'obs_connections' jika belum ada...")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS obs_connections (
                id VARCHAR(50) PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                host VARCHAR(255) NOT NULL,
                port INT NOT NULL,
                password VARCHAR(255),
                stream_name VARCHAR(100),
                streamer VARCHAR(100)
            )
        """)
        conn.commit()
        try:
            cursor.execute("ALTER TABLE obs_connections MODIFY COLUMN host VARCHAR(255) NOT NULL")
            conn.commit()
        except Exception:
            pass
        print("[+] Tabel 'obs_connections' siap.")
        
        # 5. Cek data lama di JSON atau backup JSON
        json_backup_path = json_path + ".backup"
        actual_json_path = None
        if os.path.exists(json_path):
            actual_json_path = json_path
        elif os.path.exists(json_backup_path):
            actual_json_path = json_backup_path
            
        if actual_json_path:
            print(f"[*] Menemukan file konfigurasi JSON '{actual_json_path}'. Memigrasikan data...")
            try:
                with open(actual_json_path, 'r', encoding='utf-8') as f:
                    conns = json.load(f)
                    
                migrated_count = 0
                for c in conns:
                    cursor.execute("SELECT id FROM obs_connections WHERE id = %s", (c["id"],))
                    if not cursor.fetchone():
                        cursor.execute("""
                            INSERT INTO obs_connections (id, name, host, port, password, stream_name, streamer)
                            VALUES (%s, %s, %s, %s, %s, %s, %s)
                        """, (
                            c["id"],
                            c["name"],
                            c["host"],
                            int(c["port"]),
                            c.get("password"),
                            c.get("stream_name"),
                            c.get("streamer")
                        ))
                        migrated_count += 1
                
                conn.commit()
                print(f"[+] Berhasil memigrasikan {migrated_count} koneksi dari JSON ke MySQL.")
                
                # Ubah nama ke backup jika belum berupa backup
                if actual_json_path == json_path:
                    os.rename(json_path, json_backup_path)
                    print(f"[+] Backup file JSON lama disimpan ke: {json_backup_path}")
            except Exception as json_err:
                print(f"[-] Gagal memigrasikan data JSON: {json_err}")
        else:
            # Jika DB kosong, masukkan default seed
            cursor.execute("SELECT count(*) as cnt FROM obs_connections")
            count = cursor.fetchone()["cnt"]
            if count == 0:
                print("[*] Mengisi database dengan koneksi default...")
                cursor.execute("""
                    INSERT INTO obs_connections (id, name, host, port, password, stream_name, streamer)
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """, ("obs_default", "OBS Default", "127.0.0.1", 4455, "123456", "worldcup26", "streamer"))
                conn.commit()
                print("[+] Koneksi default berhasil ditambahkan.")
                
        conn.close()
        print("=" * 60)
        print("[+] PROSES MIGRASI SELESAI DENGAN SUKSES!")
        print("=" * 60)
    except Exception as e:
        print(f"[-] Gagal menjalankan migrasi di database {db_name}: {e}")

if __name__ == "__main__":
    run_migration()