"""
check_duplicate_leads.py -- read-only: lists every company_name that has more than one Lead row.

Investigating a user report (2026-09-22): "Key West Boats" showing twice on the Pipeline tab. Confirmed:
every duplicate found so far is source="Manual entry", created 2026-09-15/17 -- crud.is_duplicate() (the
case-insensitive company_name check) wasn't wired into the "Add a Lead" popover's endpoint until it went
live 2026-09-17 (see app/api/leads.py create_lead()'s own docstring), so a rep could accidentally submit
the same company twice before that fix landed. It's already wired into every creation path now (manual,
AI discovery, CSV import, Google Maps) -- these groups are historical leftovers from just before the fix,
not an ongoing bug.

    uv run python scripts/check_duplicate_leads.py

Read-only -- prints what it finds, changes nothing. Prints enough per row (deal value, category, meeting/
callback dates) to tell which one is the real record before deciding which to delete.
"""

from __future__ import annotations

import sys
from collections import defaultdict
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from sqlalchemy import select  # noqa: E402

from app.database import SessionLocal  # noqa: E402
from app.models import Lead  # noqa: E402


def main() -> int:
    db = SessionLocal()
    try:
        leads = list(db.scalars(select(Lead).order_by(Lead.company_name, Lead.created_at)).all())
        groups: dict[str, list[Lead]] = defaultdict(list)
        for lead in leads:
            key = (lead.company_name or "").strip().lower()
            if key:
                groups[key].append(lead)

        dupes = {k: v for k, v in groups.items() if len(v) > 1}
        if not dupes:
            print("No duplicate company names found.")
            return 0

        print(f"{len(dupes)} company name(s) with more than one lead row:\n")
        for key, rows in sorted(dupes.items(), key=lambda kv: -len(kv[1])):
            print(f"'{rows[0].company_name}' -- {len(rows)} rows:")
            for lead in rows:
                print(
                    f"    #{lead.id:<6} stage={lead.pipeline_stage:<15} "
                    f"deal_value={lead.deal_value}  industry_tag={lead.industry_tag!r}"
                )
                print(
                    f"           meeting_at={lead.meeting_at}  callback_at={lead.callback_at}  "
                    f"sent_to_desk_at={lead.sent_to_desk_at}  hidden_at={lead.hidden_at}"
                )
                print(
                    f"           created_at={lead.created_at}  source={lead.source_prompt!r}  "
                    f"notes={'yes' if (lead.notes or '').strip() else 'no'}"
                )
            print()
        return 0
    finally:
        db.close()


if __name__ == "__main__":
    sys.exit(main())