clientflow_backend/scripts/repair_jasmin_reconciliation_item.py

#!/usr/bin/env python3
"""Repair a Jasmin reconciliation item affected by incorrect customer mapping.

Dry-run is the default. Use --apply only after reviewing the printed plan.
This script does not create/link opportunities and does not resolve the item.
"""
from __future__ import annotations

import argparse
import json
import sys
from decimal import Decimal, InvalidOperation
from pathlib import Path

from sqlalchemy import text

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

from app.db import engine


def _clean(value: object) -> str:
    return str(value or "").strip()


def _money(record: dict) -> Decimal | None:
    values = [
        record.get("payableAmountAmount"),
        (record.get("payableAmount") or {}).get("amount")
        if isinstance(record.get("payableAmount"), dict)
        else record.get("payableAmount"),
        record.get("grossValueAmount"),
    ]
    for value in values:
        if value in (None, ""):
            continue
        try:
            return Decimal(str(value)).quantize(Decimal("0.01"))
        except (InvalidOperation, ValueError):
            continue
    return None


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("document_number", help="Ex.: ORC.ORC2026.136")
    parser.add_argument("--apply", action="store_true", help="Aplicar a reparação; sem esta flag faz dry-run")
    args = parser.parse_args()

    with engine.begin() as conn:
        item = conn.execute(
            text("""
                SELECT ri.id::text, ri.status, ri.opportunity_id::text,
                       ri.customer_id::text, ri.customer_tax_id,
                       ri.document_number, ri.amount, ri.payload,
                       c.name AS customer_name, c.tax_id,
                       c.jasmin_customer_party_key
                FROM reconciliation_items ri
                LEFT JOIN customers c ON c.id = ri.customer_id
                WHERE ri.document_number = :document_number
                FOR UPDATE OF ri
            """),
            {"document_number": args.document_number},
        ).mappings().first()

        if not item:
            raise SystemExit(f"Documento não encontrado: {args.document_number}")
        if not item["customer_id"]:
            raise SystemExit("O item não tem customer_id; reparação automática recusada")

        payload = item["payload"] or {}
        record = payload.get("record") if isinstance(payload, dict) else None
        if not isinstance(record, dict):
            raise SystemExit("payload.record não existe ou não é um objeto")

        party_key = _clean(record.get("buyerCustomerParty") or record.get("accountingParty"))
        payload_tax_id = _clean(record.get("buyerCustomerPartyTaxId") or record.get("accountingPartyTaxId"))
        amount = _money(record)

        if not party_key:
            raise SystemExit("Não foi possível obter buyerCustomerParty/accountingParty")
        if payload_tax_id and _clean(item["tax_id"]) and payload_tax_id != _clean(item["tax_id"]):
            raise SystemExit(
                f"NIF divergente: cliente={item['tax_id']} payload={payload_tax_id}; reparação recusada"
            )

        conflict = conn.execute(
            text("""
                SELECT id::text, name, tax_id
                FROM customers
                WHERE jasmin_customer_party_key = :party_key
                  AND id <> CAST(:customer_id AS UUID)
                LIMIT 1
            """),
            {"party_key": party_key, "customer_id": item["customer_id"]},
        ).mappings().first()
        if conflict:
            raise SystemExit(
                "Party key já pertence a outro cliente: "
                + json.dumps(dict(conflict), ensure_ascii=False, default=str)
            )

        plan = {
            "mode": "apply" if args.apply else "dry-run",
            "document_number": item["document_number"],
            "reconciliation_item_id": item["id"],
            "customer_id": item["customer_id"],
            "customer_name": item["customer_name"],
            "tax_id": item["tax_id"],
            "party_key_before": item["jasmin_customer_party_key"],
            "party_key_after": party_key,
            "amount_before": str(item["amount"]) if item["amount"] is not None else None,
            "amount_after": str(amount) if amount is not None else None,
            "status_unchanged": item["status"],
            "opportunity_id_unchanged": item["opportunity_id"],
        }
        print(json.dumps(plan, ensure_ascii=False, indent=2, default=str))

        if not args.apply:
            conn.rollback()
            print("DRY-RUN: nenhuma alteração aplicada.")
            return 0

        conn.execute(
            text("""
                UPDATE customers
                SET jasmin_customer_party_key = :party_key,
                    metadata = jsonb_set(
                        COALESCE(metadata, '{}'::jsonb),
                        '{external_customer_key}',
                        to_jsonb(CAST(:party_key AS text)),
                        true
                    ),
                    updated_at = now()
                WHERE id = CAST(:customer_id AS UUID)
            """),
            {"party_key": party_key, "customer_id": item["customer_id"]},
        )
        if amount is not None:
            conn.execute(
                text("""
                    UPDATE reconciliation_items
                    SET amount = :amount,
                        updated_at = now()
                    WHERE id = CAST(:item_id AS UUID)
                """),
                {"amount": amount, "item_id": item["id"]},
            )

    print("Reparação aplicada. O item permanece aberto e sem opportunity_id.")
    return 0


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