"""Backfill task Chatwoot conversation/contact from linked opportunities.

Usage:
    PYTHONPATH=. python scripts/backfill_task_conversation_from_opportunity.py

Safe to rerun. It only fills empty task fields from the linked opportunity.
"""
from __future__ import annotations

from sqlalchemy import text

from app.db import engine


SQL = text("""
    UPDATE tasks t
    SET
        conversation_id = COALESCE(NULLIF(t.conversation_id, ''), NULLIF(o.conversation_id, '')),
        contact_id = COALESCE(NULLIF(t.contact_id, ''), NULLIF(o.contact_id, '')),
        updated_at = now(),
        metadata = COALESCE(t.metadata, '{}'::jsonb)
            || jsonb_build_object(
                'communication_context_backfilled', true,
                'communication_context_backfilled_at', now(),
                'communication_context_source', 'opportunity'
            )
    FROM opportunities o
    WHERE o.id = t.opportunity_id
      AND (
        (NULLIF(t.conversation_id, '') IS NULL AND NULLIF(o.conversation_id, '') IS NOT NULL)
        OR (NULLIF(t.contact_id, '') IS NULL AND NULLIF(o.contact_id, '') IS NOT NULL)
      )
    RETURNING
        t.id::text AS task_id,
        t.action_code,
        t.status,
        t.conversation_id,
        t.contact_id,
        o.id::text AS opportunity_id,
        o.customer_name,
        o.customer_email
""")


def main() -> None:
    with engine.begin() as conn:
        rows = conn.execute(SQL).mappings().all()

    print(f"Atualizadas: {len(rows)} task(s)")
    for row in rows:
        print("-" * 100)
        print(f"task_id: {row['task_id']}")
        print(f"action_code: {row['action_code']}")
        print(f"status: {row['status']}")
        print(f"conversation_id: {row['conversation_id'] or '-'}")
        print(f"contact_id: {row['contact_id'] or '-'}")
        print(f"opportunity_id: {row['opportunity_id']}")
        print(f"cliente: {row['customer_name'] or row['customer_email'] or '-'}")


if __name__ == "__main__":
    main()