from __future__ import annotations

import argparse
import os
import sqlite3
from pathlib import Path

import psycopg2

from database import init_db


TABLES = {
    "users": {
        "columns": [
            "id",
            "email",
            "password_hash",
            "display_name",
            "role",
            "is_active",
            "approval_status",
            "approved_by",
            "approved_at",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "id",
    },
    "app_settings": {
        "columns": [
            "setting_key",
            "value_json",
            "updated_at",
        ],
        "conflict_key": "setting_key",
    },
    "permission_role_defaults": {
        "columns": [
            "role",
            "modules_json",
            "actions_json",
            "updated_at",
        ],
        "conflict_key": "role",
    },
    "permission_user_overrides": {
        "columns": [
            "email",
            "role_hint",
            "modules_json",
            "actions_json",
            "updated_at",
        ],
        "conflict_key": "email",
    },
    "password_reset_tokens": {
        "columns": [
            "token",
            "email",
            "expires_at",
            "used_at",
            "created_at",
        ],
        "conflict_key": "token",
    },
    "purchase_orders": {
        "columns": [
            "id",
            "po_number",
            "po_date",
            "po_time",
            "simple_domestic_pt",
            "dept_number",
            "vendor_name",
            "vendor_address",
            "vendor_attention",
            "vendor_email",
            "buyer_name",
            "buyer_email",
            "shipping_address",
            "mailing_address",
            "ship_to_name",
            "ship_to_address",
            "mark_for",
            "store_number",
            "order_date",
            "ship_date",
            "cancel_date",
            "duns",
            "bealls_code",
            "purchase_disc",
            "post_dist",
            "co_op",
            "fob_point",
            "terms",
            "as_of",
            "rtv",
            "reference_number",
            "new_store_disc",
            "freight_allowance",
            "total_packs_ordered",
            "total_units",
            "total_cost",
            "total_qty",
            "special_instructions",
            "source_pdf",
            "raw_json",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "id",
    },
    "purchase_order_items": {
        "columns": [
            "id",
            "purchase_order_id",
            "sku",
            "mfg_style",
            "mfg_color",
            "size_desc",
            "description",
            "upc",
            "cost_unit",
            "comp",
            "retail",
            "pack_qty",
            "total_packs",
            "total_units",
        ],
        "conflict_key": "id",
    },
    "costing_imports": {
        "columns": [
            "id",
            "collection",
            "season",
            "source_pdf",
            "debug_dir",
            "raw_json",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "id",
    },
    "costing_variants": {
        "columns": [
            "id",
            "costing_import_id",
            "style_code",
            "color_label",
            "description",
            "image_path",
            "page_image_path",
            "page_number",
            "crop_meta_json",
            "collection",
            "season",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "id",
    },
    "costing_ws_imports": {
        "columns": [
            "id",
            "collection",
            "season",
            "source_workbook",
            "source_sheet",
            "raw_json",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "id",
    },
    "costing_ws_rows": {
        "columns": [
            "id",
            "costing_ws_import_id",
            "dept_number",
            "mj_class",
            "sub_class",
            "brand",
            "style_code",
            "description",
            "fabrication",
            "composition",
            "manf_color",
            "total_units",
            "inner_pack",
            "outer_pack",
            "size_range",
            "size_scale",
            "est_cbm",
            "item_type_for_freight",
            "color_label",
            "collection",
            "season",
            "source_row_number",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "id",
    },
    "tech_pack_records": {
        "columns": [
            "style_code",
            "record_json",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "style_code",
    },
    "tech_pack_hidden_styles": {
        "columns": [
            "style_code",
            "updated_at",
        ],
        "conflict_key": "style_code",
    },
    "sample_records": {
        "columns": [
            "record_id",
            "style_code",
            "request_number",
            "record_json",
            "created_at",
            "updated_at",
        ],
        "conflict_key": "record_id",
    },
}

SERIAL_ID_TABLES = [
    "users",
    "purchase_orders",
    "purchase_order_items",
    "costing_imports",
    "costing_variants",
    "costing_ws_imports",
    "costing_ws_rows",
]


def sqlite_has_table(sqlite_conn: sqlite3.Connection, table_name: str) -> bool:
    row = sqlite_conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?",
        (table_name,),
    ).fetchone()
    return row is not None


def read_rows(sqlite_conn: sqlite3.Connection, table_name: str, columns: list[str]) -> list[tuple]:
    if not sqlite_has_table(sqlite_conn, table_name):
        return []
    quoted_columns = ", ".join(columns)
    rows = sqlite_conn.execute(f"SELECT {quoted_columns} FROM {table_name}").fetchall()
    return [tuple(row[column] for column in columns) for row in rows]


def write_rows(pg_conn, table_name: str, columns: list[str], rows: list[tuple], conflict_key: str) -> None:
    if not rows:
        return
    placeholders = ", ".join(["%s"] * len(columns))
    column_list = ", ".join(columns)
    update_columns = [column for column in columns if column != conflict_key]
    update_list = ", ".join(f"{column} = EXCLUDED.{column}" for column in update_columns)
    sql = f"""
    INSERT INTO {table_name} ({column_list})
    VALUES ({placeholders})
    ON CONFLICT ({conflict_key}) DO UPDATE SET
    {update_list}
    """
    with pg_conn.cursor() as cur:
        cur.executemany(sql, rows)


def truncate_postgres_tables(pg_conn) -> None:
    table_list = ", ".join(TABLES.keys())
    with pg_conn.cursor() as cur:
        cur.execute(f"TRUNCATE TABLE {table_list} RESTART IDENTITY CASCADE")


def reset_postgres_sequences(pg_conn) -> None:
    with pg_conn.cursor() as cur:
        for table_name in SERIAL_ID_TABLES:
            cur.execute(
                f"SELECT COALESCE(MAX(id), 0) FROM {table_name}"
            )
            max_id = int(cur.fetchone()[0] or 0)
            cur.execute(
                "SELECT pg_get_serial_sequence(%s, 'id')",
                (table_name,),
            )
            sequence_name = cur.fetchone()[0]
            if not sequence_name:
                continue
            if max_id > 0:
                cur.execute("SELECT setval(%s, %s, true)", (sequence_name, max_id))
            else:
                cur.execute("SELECT setval(%s, 1, false)", (sequence_name,))


def migrate(sqlite_path: Path, postgres_dsn: str) -> None:
    if not sqlite_path.exists():
        raise FileNotFoundError(f"SQLite database not found: {sqlite_path}")

    sqlite_conn = sqlite3.connect(sqlite_path)
    sqlite_conn.row_factory = sqlite3.Row
    pg_conn = psycopg2.connect(postgres_dsn)
    previous_dsn = os.environ.get("SUNNY_ERP_DATABASE_URL")
    os.environ["SUNNY_ERP_DATABASE_URL"] = postgres_dsn
    try:
        init_db(Path("postgres-runtime"))
        truncate_postgres_tables(pg_conn)
        for table_name, config in TABLES.items():
            rows = read_rows(sqlite_conn, table_name, list(config["columns"]))
            write_rows(
                pg_conn,
                table_name,
                list(config["columns"]),
                rows,
                str(config["conflict_key"]),
            )
        reset_postgres_sequences(pg_conn)
        pg_conn.commit()
    except Exception:
        pg_conn.rollback()
        raise
    finally:
        if previous_dsn is None:
            os.environ.pop("SUNNY_ERP_DATABASE_URL", None)
        else:
            os.environ["SUNNY_ERP_DATABASE_URL"] = previous_dsn
        sqlite_conn.close()
        pg_conn.close()


def main() -> None:
    parser = argparse.ArgumentParser(description="Migrate CitiFox ERP App SQLite database to PostgreSQL.")
    parser.add_argument("--sqlite", required=True, help="Path to the source SQLite database file.")
    parser.add_argument(
        "--postgres-dsn",
        required=True,
        help="PostgreSQL DSN, for example: postgresql://postgres:password@127.0.0.1:5432/po_internal_app",
    )
    args = parser.parse_args()
    migrate(Path(args.sqlite), args.postgres_dsn)
    print("Migration completed successfully.")


if __name__ == "__main__":
    main()
