#!/usr/bin/env python3
"""Migrate data from PostgreSQL (helix_db) to MySQL (helix).

Usage:
    python migrate-pg-to-mysql.py

Requires:
    pip install psycopg2-binary pymysql

Environment variables (or edit defaults below):
    PG_URL  - PostgreSQL connection string (default: postgresql://helix:helix@localhost:5432/helix_db)
    MY_URL  - MySQL connection string       (default: mysql+pymysql://helix:helix@localhost:3306/helix)

This script:
1. Reads all rows from each PostgreSQL table
2. Inserts them into the corresponding MySQL table
3. Preserves UUIDs as-is (MySQL CHAR(32) via SQLAlchemy Uuid type)
4. Handles JSONB → JSON conversion automatically
"""

import os
import re
import sys
from uuid import UUID

import json

# ── Connection strings ──
PG_DSN = os.environ.get("PG_URL", "postgresql://helix:helix@localhost:5432/helix_db")
MY_DSN = os.environ.get("MY_URL", "mysql+pymysql://helix:helix@localhost:3306/helix")

# Strip SQLAlchemy dialect prefix for raw driver usage
MY_DSN_CLEAN = MY_DSN.replace("mysql+pymysql://", "")

# Tables in dependency order (parents before children)
TABLES = [
    "identity_persons",
    "identity_org_memberships",
    "identity_org_nodes",
    "identity_external_identities",
    "auth_roles",
    "auth_user_roles",
    "auth_permissions",
    "auth_role_permissions",
    "auth_entitlements",
    "audit_logs",
    "approval_requests",
    "approval_steps",
    "hr_employees",
    "hr_lifecycle_events",
    "asset_suppliers",
    "asset_legal_entities",
    "asset_items",
    "asset_locations",
    "asset_category_configs",
    "asset_tag_sequences",
    "asset_history",
    "asset_operations",
    "asset_procurement_orders",
    "asset_software_catalog",
    "asset_business_payments",
    "asset_office_addresses",
    "asset_employee_requests",
]

_UUID_RE = re.compile(r'^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$', re.I)


def convert_value(val):
    """Convert PostgreSQL values to MySQL-compatible values."""
    if val is None:
        return None
    if isinstance(val, UUID):
        return val.hex  # 32 chars, no dashes
    if isinstance(val, str) and _UUID_RE.match(val):
        return val.replace("-", "")
    if isinstance(val, (dict, list)):
        return json.dumps(val, ensure_ascii=False, default=str)
    if isinstance(val, bool):
        return 1 if val else 0
    return val


def main():
    try:
        import psycopg2
        import psycopg2.extras
    except ImportError:
        print("ERROR: psycopg2 not installed. Run: pip install psycopg2-binary")
        sys.exit(1)

    try:
        import pymysql
    except ImportError:
        print("ERROR: pymysql not installed. Run: pip install pymysql")
        sys.exit(1)

    # Parse MySQL connection
    from urllib.parse import urlparse
    parsed = urlparse("mysql://" + MY_DSN_CLEAN)
    user_info = parsed.netloc.rsplit("@", 1)[0] if "@" in parsed.netloc else ""
    # (host/port come from ``parsed.hostname`` / ``parsed.port`` below — the
    # hand-split host segment this line used to compute was never read.)
    my_user, my_pass = user_info.split(":", 1) if ":" in user_info else (user_info, "")
    my_host = parsed.hostname or "localhost"
    my_port = parsed.port or 3306
    my_db = parsed.path.lstrip("/")

    print(f"Source: {PG_DSN}")
    print(f"Target: mysql://{my_user}:***@{my_host}:{my_port}/{my_db}")
    print()

    pg_conn = psycopg2.connect(PG_DSN)
    pg_conn.set_client_encoding("UTF8")

    my_conn = pymysql.connect(
        host=my_host, port=my_port, user=my_user, password=my_pass,
        database=my_db, charset="utf8mb4",
    )

    total_migrated = 0

    for table in TABLES:
        pg_cur = pg_conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        # Check if table exists in PostgreSQL
        pg_cur.execute(
            "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = %s)",
            (table,),
        )
        if not pg_cur.fetchone()["exists"]:
            print(f"  SKIP {table} (not in PostgreSQL)")
            pg_cur.close()
            continue

        pg_cur.execute(f'SELECT * FROM "{table}"')  # noqa: S608
        rows = pg_cur.fetchall()
        pg_cur.close()

        if not rows:
            print(f"  {table}: 0 rows (empty)")
            continue

        columns = list(rows[0].keys())
        placeholders = ", ".join(["%s"] * len(columns))
        col_names = ", ".join(f"`{c}`" for c in columns)
        insert_sql = f"INSERT INTO `{table}` ({col_names}) VALUES ({placeholders})"

        my_cur = my_conn.cursor()
        # Disable FK checks for bulk insert
        my_cur.execute("SET FOREIGN_KEY_CHECKS=0")

        count = 0
        for row in rows:
            values = tuple(convert_value(row[c]) for c in columns)
            try:
                my_cur.execute(insert_sql, values)
                count += 1
            except Exception as e:
                print(f"    ERROR inserting into {table}: {e}")
                print(f"    Row: {row}")
                continue

        my_cur.execute("SET FOREIGN_KEY_CHECKS=1")
        my_conn.commit()
        my_cur.close()

        print(f"  {table}: {count} rows migrated")
        total_migrated += count

    pg_conn.close()
    my_conn.close()

    print(f"\nDone. Total rows migrated: {total_migrated}")


if __name__ == "__main__":
    main()