#!/usr/bin/env python3
from __future__ import annotations

import argparse
import csv
import gzip
import json
from pathlib import Path
from typing import Any

import pymysql


LOCAL_ADMIN_ID = "10000000-0000-0000-0000-000000000001"


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Delete prod-only local test opportunities and their local dependent orders/quotes.")
    parser.add_argument("--snapshot-dir", required=True)
    parser.add_argument("--db-host", required=True)
    parser.add_argument("--db-port", type=int, default=4000)
    parser.add_argument("--db-user", required=True)
    parser.add_argument("--db-password", required=True)
    parser.add_argument("--db-database", required=True)
    parser.add_argument("--dry-run", action="store_true")
    return parser.parse_args()


def load_source_ids(snapshot_dir: Path) -> set[str]:
    ids: set[str] = set()
    with gzip.open(snapshot_dir / "Opportunity.csv.gz", "rt", encoding="utf-8-sig", newline="") as src:
        for row in csv.DictReader(src):
            row_id = (row.get("Id") or "").strip()
            if row_id:
                ids.add(row_id)
    return ids


def main() -> int:
    args = parse_args()
    snapshot_dir = Path(args.snapshot_dir).expanduser().resolve()
    source_ids = load_source_ids(snapshot_dir)

    conn = pymysql.connect(
        host=args.db_host,
        port=args.db_port,
        user=args.db_user,
        password=args.db_password,
        database=args.db_database,
        ssl={"ssl": {}},
        charset="utf8mb4",
        autocommit=False,
    )

    report: dict[str, Any] = {"opportunities": [], "orders": [], "quotes": [], "dry_run": args.dry_run}
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT Id, Name, CreatedById, OwnerId, CreatedDate
                FROM `Opportunity`
                WHERE CreatedById = %s
                  AND OwnerId = %s
                """,
                (LOCAL_ADMIN_ID, LOCAL_ADMIN_ID),
            )
            candidates = []
            for row in cur.fetchall():
                opp_id = row[0]
                if opp_id in source_ids:
                    continue
                candidates.append(
                    {
                        "Id": row[0],
                        "Name": row[1],
                        "CreatedById": row[2],
                        "OwnerId": row[3],
                        "CreatedDate": str(row[4]),
                    }
                )
            report["opportunities"] = candidates

            if not candidates:
                conn.rollback()
                print(json.dumps(report, ensure_ascii=False, indent=2))
                return 0

            opp_ids = [row["Id"] for row in candidates]
            placeholders = ",".join(["%s"] * len(opp_ids))

            cur.execute(
                f"SELECT Id, OpportunityId, CreatedById, CreatedDate FROM `Order` WHERE OpportunityId IN ({placeholders})",
                opp_ids,
            )
            report["orders"] = [
                {
                    "Id": row[0],
                    "OpportunityId": row[1],
                    "CreatedById": row[2],
                    "CreatedDate": str(row[3]),
                }
                for row in cur.fetchall()
            ]

            cur.execute(
                f"SELECT Id, OpportunityId, CreatedById, CreatedDate FROM `Quote` WHERE OpportunityId IN ({placeholders})",
                opp_ids,
            )
            report["quotes"] = [
                {
                    "Id": row[0],
                    "OpportunityId": row[1],
                    "CreatedById": row[2],
                    "CreatedDate": str(row[3]),
                }
                for row in cur.fetchall()
            ]

            if args.dry_run:
                conn.rollback()
            else:
                if report["quotes"]:
                    quote_ids = [row["Id"] for row in report["quotes"]]
                    cur.execute(
                        f"DELETE FROM `Quote` WHERE Id IN ({','.join(['%s'] * len(quote_ids))})",
                        quote_ids,
                    )
                if report["orders"]:
                    order_ids = [row["Id"] for row in report["orders"]]
                    cur.execute(
                        f"DELETE FROM `Order` WHERE Id IN ({','.join(['%s'] * len(order_ids))})",
                        order_ids,
                    )
                cur.execute(
                    f"DELETE FROM `Opportunity` WHERE Id IN ({placeholders})",
                    opp_ids,
                )
                conn.commit()
    finally:
        if args.dry_run:
            conn.rollback()
        conn.close()

    print(json.dumps(report, ensure_ascii=False, indent=2))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())