foodlinkk-command-center/tools-api/app/export_intel_crm.py

"""Push export market entities into Foodlinkk CRM (clients + deals)."""
from __future__ import annotations

from typing import Any, Optional

from app.db import execute, execute_returning, fetch_one
from app.export_halal_scoring import score_entity

SECTOR_BY_TYPE: dict[str, str] = {
    "distributor": "Export · Distributie",
    "wholesaler": "Export · Groothandel",
    "importer": "Export · Import",
    "logistics": "Export · Logistiek",
    "contract_caterer": "Export · Catering",
    "restaurant": "Export · Horeca",
    "doner_shoarma": "Export · Horeca",
    "butcher": "Export · Slagerij",
    "foodservice": "Export · Foodservice",
}


def _sector(entity_type: str) -> str:
    return SECTOR_BY_TYPE.get(entity_type or "", "Export · B2B")


def _contact_name(contacts: list[dict[str, Any]]) -> Optional[str]:
    for c in contacts:
        if c.get("name"):
            return c["name"]
    return None


def _build_notes(ent: dict[str, Any], contacts: list[dict[str, Any]], halal_score: float) -> str:
    lines = [
        f"Wereldexport entity #{ent['id']}",
        f"Type: {ent.get('entity_type')}",
        f"Land: {ent.get('country_iso2')}",
    ]
    if ent.get("city"):
        lines.append(f"Stad: {ent['city']}")
    if ent.get("address_line"):
        lines.append(f"Adres: {ent['address_line']}")
    if ent.get("website"):
        lines.append(f"Website: {ent['website']}")
    if ent.get("phone"):
        lines.append(f"Telefoon: {ent['phone']}")
    lines.append(f"Halal-kans score: {halal_score}/100")
    lines.append(f"OSM bron: {ent.get('source') or '—'}")
    if contacts:
        lines.append(f"Contacten in registry: {len(contacts)}")
    lines.append("—")
    lines.append("Aangemaakt vanuit Wereldexport · Foodlinkk B2B")
    return "\n".join(lines)


def push_entity_to_crm(
    entity_id: int,
    *,
    create_deal: bool = True,
    client_stage: str = "intake",
    deal_stage: str = "lead",
) -> dict[str, Any]:
    ent = fetch_one("SELECT * FROM export_market_entities WHERE id = %s", (entity_id,))
    if not ent:
        return {"entity_id": entity_id, "status": "not_found"}

    if ent.get("client_id"):
        client = fetch_one("SELECT id, name FROM clients WHERE id = %s", (ent["client_id"],))
        return {
            "entity_id": entity_id,
            "status": "already_linked",
            "client_id": ent["client_id"],
            "deal_id": ent.get("deal_id"),
            "client_name": (client or {}).get("name"),
        }

    from app.db import fetch_all

    contacts = fetch_all(
        "SELECT * FROM export_entity_contacts WHERE entity_id = %s ORDER BY is_primary DESC, confidence DESC",
        (entity_id,),
    )
    email = ent.get("email")
    phone = ent.get("phone")
    for c in contacts:
        if not email and c.get("email"):
            email = c["email"]
        if not phone:
            phone = c.get("phone") or c.get("mobile")

    client_id: Optional[int] = None
    status = "created"

    if email:
        existing = fetch_one(
            "SELECT id, name FROM clients WHERE LOWER(email) = LOWER(%s) LIMIT 1",
            (email,),
        )
        if existing:
            client_id = existing["id"]
            status = "linked_existing_email"

    if client_id is None:
        contact_label = _contact_name(contacts) or phone or email
        halal_score, _ = score_entity({**ent, "contact_count": len(contacts)})
        notes = _build_notes(ent, contacts, halal_score)
        row = execute_returning(
            """
            INSERT INTO clients (name, contact, email, stage, sector, notes, updated_at)
            VALUES (%s, %s, %s, %s, %s, %s, NOW())
            RETURNING id
            """,
            (
                ent["name"][:255],
                (contact_label or "")[:255] or None,
                (email or "")[:255] or None,
                client_stage,
                _sector(ent.get("entity_type") or ""),
                notes,
            ),
        )
        if not row:
            return {"entity_id": entity_id, "status": "error", "error": "client_insert_failed"}
        client_id = row["id"]

    deal_id: Optional[int] = None
    if create_deal and not ent.get("deal_id"):
        country = ent.get("country_iso2") or ""
        title = f"Export · {ent['name'][:180]} ({country})"
        deal_row = execute_returning(
            """
            INSERT INTO deals (client_id, title, value, stage, agent_owner, next_action, updated_at)
            VALUES (%s, %s, 0, %s, 'herman', %s, NOW())
            RETURNING id
            """,
            (
                client_id,
                title[:255],
                deal_stage,
                f"Eerste outreach — Wereldexport entity #{entity_id}",
            ),
        )
        if deal_row:
            deal_id = deal_row["id"]

    execute(
        """
        UPDATE export_market_entities
        SET client_id = %s,
            deal_id = COALESCE(%s, deal_id),
            pipeline_stage = 'crm_linked',
            crm_pushed_at = NOW(),
            updated_at = NOW()
        WHERE id = %s
        """,
        (client_id, deal_id, entity_id),
    )

    client = fetch_one("SELECT id, name FROM clients WHERE id = %s", (client_id,))
    return {
        "entity_id": entity_id,
        "status": status,
        "client_id": client_id,
        "deal_id": deal_id,
        "client_name": (client or {}).get("name"),
    }


def push_entities_to_crm(
    entity_ids: list[int],
    *,
    create_deals: bool = True,
    client_stage: str = "intake",
    deal_stage: str = "lead",
) -> dict[str, Any]:
    results = [
        push_entity_to_crm(
            eid,
            create_deal=create_deals,
            client_stage=client_stage,
            deal_stage=deal_stage,
        )
        for eid in entity_ids
    ]
    created = sum(1 for r in results if r.get("status") == "created")
    linked = sum(1 for r in results if r.get("status") in ("linked_existing_email", "already_linked"))
    return {
        "results": results,
        "total": len(results),
        "created": created,
        "linked_existing": linked,
        "pushed": created + sum(1 for r in results if r.get("status") == "linked_existing_email"),
    }