#!/usr/bin/env python3
"""Compare a bulk-upload CSV against {bid}_raw_calls and export rows still missing."""
from __future__ import annotations

import argparse
import csv
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
if str(ROOT) not in sys.path:
    sys.path.insert(0, str(ROOT))

import pymysql
from config import Config
from lead_upload_service import parse_csv_bulk_rows


class ConfigWrapper:
    def __init__(self, config):
        self._config = config

    def get(self, key, default=None):
        return getattr(self._config, key, default)

    def __getattr__(self, key):
        return getattr(self._config, key)


def main() -> int:
    parser = argparse.ArgumentParser(
        description="Find CSV rows whose callid is not in {bid}_raw_calls (for re-sync)."
    )
    parser.add_argument("--bid", required=True, help="Business ID, e.g. 4444")
    parser.add_argument("--csv", required=True, help="Path to original bulk upload CSV")
    parser.add_argument(
        "--out",
        default="",
        help="Write missing rows to this CSV (default: <csv-stem>_missing_<bid>.csv)",
    )
    parser.add_argument(
        "--include-not-processed",
        action="store_true",
        help="Also write rows that exist in DB but status != 3 (pipeline incomplete)",
    )
    args = parser.parse_args()

    csv_path = Path(args.csv).expanduser().resolve()
    if not csv_path.is_file():
        print(f"CSV not found: {csv_path}", file=sys.stderr)
        return 1

    bid = str(args.bid).strip()
    csv_text = csv_path.read_text(encoding="utf-8-sig")
    try:
        parsed = parse_csv_bulk_rows(csv_text)
    except ValueError as exc:
        print(f"CSV parse error: {exc}", file=sys.stderr)
        return 1

    csv_by_id = {str(r["callid"]): r for r in parsed}

    conn = pymysql.connect(
        host=Config.DB_HOST,
        port=Config.DB_PORT,
        user=Config.DB_USER,
        password=Config.DB_PASSWORD,
        database=Config.DB_NAME,
        cursorclass=pymysql.cursors.DictCursor,
    )
    try:
        cur = conn.cursor()
        table = f"{bid}_raw_calls"
        cur.execute("SHOW TABLES LIKE %s", (table,))
        if not cur.fetchone():
            print(f"Table {table} does not exist", file=sys.stderr)
            return 1

        cur.execute(f"SELECT callid, status, fileurl FROM `{table}`")
        db_rows = {str(r["callid"]): r for r in (cur.fetchall() or [])}
    finally:
        conn.close()

    csv_ids = set(csv_by_id)
    db_ids = set(db_rows)
    missing = csv_ids - db_ids
    already = csv_ids & db_ids
    extra_in_db = db_ids - csv_ids
    not_processed = [
        cid for cid in already if int(db_rows[cid].get("status") or 0) != 3
    ]

    print(f"CSV file:              {csv_path}")
    print(f"CSV valid rows:        {len(csv_ids)}  (callid + recording URL)")
    print(f"DB rows ({table}): {len(db_ids)}")
    print(f"Missing from DB:       {len(missing)}  -> need re-import")
    print(f"Already in DB:         {len(already)}")
    print(f"  not fully processed: {len(not_processed)}  (status != 3)")
    print(f"In DB but not in CSV:  {len(extra_in_db)}")

    out_path = Path(args.out) if args.out else csv_path.with_name(
        f"{csv_path.stem}_missing_{bid}.csv"
    )

    export_ids = sorted(missing)
    if args.include_not_processed:
        export_ids = sorted(set(export_ids) | set(not_processed))

    if not export_ids:
        print("Nothing to export.")
        return 0

    fieldnames = list(parsed[0].keys())
    with out_path.open("w", newline="", encoding="utf-8") as handle:
        writer = csv.DictWriter(handle, fieldnames=fieldnames, extrasaction="ignore")
        writer.writeheader()
        for callid in export_ids:
            if callid in csv_by_id:
                writer.writerow(csv_by_id[callid])

    print(f"Wrote {len(export_ids)} row(s) to: {out_path}")
    if missing:
        print("Sample missing callids:", ", ".join(sorted(missing)[:5]))
    return 0


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