#!/usr/bin/env python3
"""Archive spam/falso-positivo opportunities without counting them as LOST.

Examples:
  PYTHONPATH=. python scripts/archive_spam_opportunities.py --opportunity-id <uuid>
  PYTHONPATH=. python scripts/archive_spam_opportunities.py --scan --apply
"""
from __future__ import annotations

import argparse
from sqlalchemy import text

from app.db import engine
from app.opportunity_service import archive_spam_opportunity_if_safe


def _candidate_rows(limit: int = 200):
    with engine.begin() as conn:
        return conn.execute(text("""
            SELECT DISTINCT
                o.id::text,
                o.title,
                o.customer_name,
                o.stage,
                o.status,
                o.updated_at,
                (SELECT COUNT(*) FROM commercial_documents cd WHERE cd.opportunity_id = o.id) AS docs,
                (SELECT COUNT(*) FROM reconciliation_items ri WHERE ri.opportunity_id = o.id AND ri.source_system IN ('jasmin','odoo')) AS linked_external,
                (SELECT COUNT(*) FROM tasks t WHERE t.opportunity_id = o.id AND (t.action_code = 'IGNORE_SPAM' OR t.route = 'spam')) AS spam_tasks,
                (SELECT COUNT(*) FROM communications c WHERE c.opportunity_id = o.id AND c.classification IN ('IGNORE_SPAM','SPAM')) AS spam_comms
            FROM opportunities o
            LEFT JOIN tasks t ON t.opportunity_id = o.id
            LEFT JOIN communications c ON c.opportunity_id = o.id
            WHERE COALESCE(o.status, 'open') = 'open'
              AND (
                    t.action_code = 'IGNORE_SPAM'
                 OR t.route = 'spam'
                 OR c.classification IN ('IGNORE_SPAM','SPAM')
              )
            ORDER BY o.updated_at DESC
            LIMIT :limit
        """), {"limit": int(limit)}).mappings().all()


def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--opportunity-id", help="Arquivar uma oportunidade concreta")
    parser.add_argument("--scan", action="store_true", help="Procurar oportunidades abertas com evidência de spam")
    parser.add_argument("--apply", action="store_true", help="Aplicar alterações. Sem isto é dry-run")
    parser.add_argument("--limit", type=int, default=200)
    args = parser.parse_args()

    if not args.opportunity_id and not args.scan:
        parser.error("usa --opportunity-id ou --scan")

    rows = []
    if args.opportunity_id:
        with engine.begin() as conn:
            row = conn.execute(text("""
                SELECT id::text, title, customer_name, stage, status, updated_at,
                       0 AS docs, 0 AS linked_external, 1 AS spam_tasks, 0 AS spam_comms
                FROM opportunities
                WHERE id = CAST(:opportunity_id AS UUID)
            """), {"opportunity_id": args.opportunity_id}).mappings().first()
            if row:
                rows = [row]
    elif args.scan:
        rows = list(_candidate_rows(args.limit))

    print(f"apply={args.apply} candidates={len(rows)}")
    changed = 0
    for row in rows:
        oid = str(row["id"])
        print(f"- opp={oid} status={row.get('status')} stage={row.get('stage')} docs={row.get('docs')} linked_external={row.get('linked_external')} spam_tasks={row.get('spam_tasks')} spam_comms={row.get('spam_comms')} title={row.get('title')!r}")
        if not args.apply:
            continue
        result = archive_spam_opportunity_if_safe(
            oid,
            reason="spam/falso positivo",
            actor="archive_spam_opportunities_script",
        )
        print(f"  result={result}")
        if result.get("ok") and result.get("status") in {"archived", "already_archived"}:
            changed += 1
    print(f"SUMMARY changed={changed} dry_run={not args.apply}")
    return 0


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