#!/usr/bin/env python3
"""
CA Automator — one-time SQLite -> MySQL data migration.

Moves every row from the existing SQLite database into an already-schema-
initialized MySQL database (run `CA_DB_ENGINE=mysql python3 run.py init`
first, or just start the server once with CA_DB_ENGINE=mysql — db.init_db()
runs automatically on import and creates the schema from schema_mysql.sql).

This only copies data — it never touches server/db.py's engine selection.
The app keeps reading CA_DB_ENGINE at import time as always; this script
independently opens both databases directly (sqlite3 for the source,
PyMySQL for the destination) so it can run once, standalone, regardless of
which engine the app itself is currently configured for.

Usage:
    cd server
    python3 migrate_sqlite_to_mysql.py \\
        --sqlite-path ../data/ca_automator.db \\
        --mysql-host localhost --mysql-user root --mysql-password '' \\
        --mysql-database ca_automator

Safe to re-run: every table is TRUNCATEd (in FK-safe order, checks
disabled) immediately before its rows are re-copied, so a re-run after
fixing a connection issue never doubles up data. It does NOT touch the
source SQLite file at all — read-only throughout.
"""
import argparse
import sqlite3
import sys

import pymysql


# Tables whose bare name collides with a MySQL reserved word — every
# INSERT/TRUNCATE against these needs backtick-quoting. Matches server/
# db.py's own RESERVED_WORDS handling (see that file's _translate_sql and
# convert_schema.py's schema generation notes for why each one is here).
RESERVED_TABLES = {"lead"}
RESERVED_COLUMNS = {"key", "system"}


def _quote_table(name):
    return f"`{name}`" if name in RESERVED_TABLES else name


def _quote_col(name):
    return f"`{name}`" if name in RESERVED_COLUMNS else name


def migrate(sqlite_path, mysql_config, batch_size=500):
    src = sqlite3.connect(sqlite_path)
    src.row_factory = sqlite3.Row
    dst = pymysql.connect(**mysql_config)
    dst_cur = dst.cursor()

    tables = [r[0] for r in src.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
    ).fetchall()]
    print(f"Found {len(tables)} tables in {sqlite_path}")

    dst_cur.execute("SET FOREIGN_KEY_CHECKS=0")
    total_rows = 0
    for table in tables:
        qtable = _quote_table(table)
        try:
            dst_cur.execute(f"SELECT 1 FROM {qtable} LIMIT 1")
        except pymysql.err.ProgrammingError:
            print(f"  SKIP {table} — no matching table in the MySQL schema"
                 " (run `CA_DB_ENGINE=mysql python3 run.py init` first)")
            continue

        # Backtick-quoting (via qtable) works identically in SQLite — it's a
        # MySQL-compatibility feature SQLite's own parser accepts — so the
        # same query text is safe against either engine, no branching needed.
        rows = src.execute(f"SELECT * FROM {qtable}").fetchall()
        if not rows:
            dst_cur.execute(f"TRUNCATE TABLE {qtable}")
            continue

        cols = rows[0].keys()
        qcols = ", ".join(_quote_col(c) for c in cols)
        placeholders = ", ".join(["%s"] * len(cols))
        dst_cur.execute(f"TRUNCATE TABLE {qtable}")

        batch = []
        inserted = 0
        for row in rows:
            batch.append(tuple(row[c] for c in cols))
            if len(batch) >= batch_size:
                dst_cur.executemany(f"INSERT INTO {qtable} ({qcols}) VALUES ({placeholders})", batch)
                inserted += len(batch)
                batch = []
        if batch:
            dst_cur.executemany(f"INSERT INTO {qtable} ({qcols}) VALUES ({placeholders})", batch)
            inserted += len(batch)

        total_rows += inserted
        print(f"  {table}: {inserted} rows")

    dst_cur.execute("SET FOREIGN_KEY_CHECKS=1")
    dst.commit()
    print(f"\nDone — {total_rows} rows copied across {len(tables)} tables.")

    # Verify: every table's row count matches between source and destination.
    print("\nVerifying row counts...")
    mismatches = []
    for table in tables:
        qtable = _quote_table(table)
        src_n = src.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
        try:
            dst_cur.execute(f"SELECT COUNT(*) FROM {qtable}")
            dst_n = dst_cur.fetchone()[0]
        except pymysql.err.ProgrammingError:
            continue
        if src_n != dst_n:
            mismatches.append((table, src_n, dst_n))
    if mismatches:
        print("MISMATCHES FOUND:")
        for table, src_n, dst_n in mismatches:
            print(f"  {table}: sqlite={src_n} mysql={dst_n}")
        sys.exit(1)
    print("All row counts match.")

    src.close()
    dst.close()


if __name__ == "__main__":
    p = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    p.add_argument("--sqlite-path", default="../data/ca_automator.db")
    p.add_argument("--mysql-host", default="localhost")
    p.add_argument("--mysql-port", type=int, default=3306)
    p.add_argument("--mysql-user", default="root")
    p.add_argument("--mysql-password", default="")
    p.add_argument("--mysql-database", default="ca_automator")
    args = p.parse_args()

    migrate(args.sqlite_path, {
        "host": args.mysql_host, "port": args.mysql_port, "user": args.mysql_user,
        "password": args.mysql_password, "database": args.mysql_database, "charset": "utf8mb4",
    })
