63 lines
2.0 KiB
Python
63 lines
2.0 KiB
Python
"""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()
|