90 lines
3.6 KiB
Python
Executable File
90 lines
3.6 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""Backfill Jasmin product mappings for Odoo-imported opportunity lines.
|
|
|
|
Maps Odoo line metadata product ids to the ClientFlow catalogue convention:
|
|
``product_id=3`` -> ``products.sku='ODOO-3'`` -> ``jasmin_sales_item``.
|
|
|
|
Usage:
|
|
PYTHONPATH=. python scripts/backfill_opportunity_product_mappings.py
|
|
PYTHONPATH=. python scripts/backfill_opportunity_product_mappings.py --opportunity-id <uuid> --apply
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
from sqlalchemy import text
|
|
|
|
from app.db import engine
|
|
from app.product_service import ensure_product_schema
|
|
|
|
|
|
def main() -> int:
|
|
parser = argparse.ArgumentParser()
|
|
parser.add_argument("--opportunity-id", default="", help="Optional opportunity UUID to restrict the backfill")
|
|
parser.add_argument("--apply", action="store_true", help="Apply changes. Without this flag it runs as dry-run.")
|
|
args = parser.parse_args()
|
|
|
|
ensure_product_schema()
|
|
where_opp = "AND oi.opportunity_id = CAST(:opportunity_id AS UUID)" if args.opportunity_id else ""
|
|
params = {"opportunity_id": args.opportunity_id} if args.opportunity_id else {}
|
|
|
|
with engine.begin() as conn:
|
|
rows = conn.execute(text(f"""
|
|
SELECT
|
|
oi.id::text AS item_id,
|
|
oi.opportunity_id::text AS opportunity_id,
|
|
oi.product_name,
|
|
oi.sku AS old_sku,
|
|
oi.jasmin_sales_item AS old_jasmin_sales_item,
|
|
oi.metadata->>'product_id' AS odoo_product_id,
|
|
p.id::text AS product_id,
|
|
p.sku AS new_sku,
|
|
p.jasmin_sales_item AS new_jasmin_sales_item,
|
|
p.name AS catalog_name
|
|
FROM opportunity_items oi
|
|
JOIN products p
|
|
ON p.sku = ('ODOO-' || (oi.metadata->>'product_id'))
|
|
WHERE oi.metadata->>'source_system' = 'odoo'
|
|
AND COALESCE(oi.metadata->>'product_id','') <> ''
|
|
AND (
|
|
oi.product_id IS DISTINCT FROM p.id
|
|
OR COALESCE(oi.sku,'') IS DISTINCT FROM COALESCE(p.sku,'')
|
|
OR COALESCE(oi.jasmin_sales_item,'') IS DISTINCT FROM COALESCE(p.jasmin_sales_item,'')
|
|
)
|
|
{where_opp}
|
|
ORDER BY oi.created_at DESC
|
|
"""), params).mappings().all()
|
|
|
|
print(f"Candidatos a atualizar: {len(rows)}")
|
|
for row in rows:
|
|
print(dict(row))
|
|
|
|
if args.apply and rows:
|
|
result = conn.execute(text(f"""
|
|
UPDATE opportunity_items oi
|
|
SET product_id = p.id,
|
|
sku = p.sku,
|
|
jasmin_sales_item = p.jasmin_sales_item,
|
|
metadata = COALESCE(oi.metadata, '{{}}'::jsonb) || jsonb_build_object(
|
|
'resolved_sku', p.sku,
|
|
'resolved_jasmin_sales_item', p.jasmin_sales_item,
|
|
'product_mapping_status', CASE WHEN COALESCE(p.jasmin_sales_item,'') <> '' THEN 'mapped' ELSE 'missing_jasmin' END,
|
|
'catalog_name', p.name,
|
|
'backfilled_at', now()::text
|
|
),
|
|
updated_at = now()
|
|
FROM products p
|
|
WHERE p.sku = ('ODOO-' || (oi.metadata->>'product_id'))
|
|
AND oi.metadata->>'source_system' = 'odoo'
|
|
AND COALESCE(oi.metadata->>'product_id','') <> ''
|
|
{where_opp}
|
|
"""), params)
|
|
print(f"Aplicado: {result.rowcount or 0} linha(s) atualizada(s)")
|
|
elif not args.apply:
|
|
print("Dry-run. Usa --apply para aplicar.")
|
|
|
|
return 0
|
|
|
|
|
|
if __name__ == "__main__":
|
|
raise SystemExit(main())
|