#!/usr/bin/env python3
"""Read-only audit for Odoo status consistency on ClientFlow opportunities.

Reports opportunities that are in advanced operational stages but lack a linked
Odoo sale order or a recently synced physical_status operation link.
"""
from __future__ import annotations

from datetime import datetime, timezone
from pathlib import Path
import json

from sqlalchemy import text

from app.db import engine

ADVANCED_STAGES = (
    "ODOO_ORDER_CREATED", "IN_PRODUCTION", "ORDER_PREPARATION", "READY_TO_SHIP",
    "INVOICED", "SHIPMENT_CREATED", "SHIPPED", "TRACKING_SENT", "DELIVERED", "WON",
)


def main() -> int:
    with engine.begin() as conn:
        rows = conn.execute(text("""
            SELECT
                o.id::text AS opportunity_id,
                o.title,
                o.customer_name,
                o.customer_email,
                o.status,
                o.stage,
                sale.external_name AS odoo_sale,
                sale.status AS sale_status,
                phys.status AS physical_status,
                phys.last_synced_at AS physical_last_synced_at,
                inv.external_name AS invoice_number
            FROM opportunities o
            LEFT JOIN operation_links sale
              ON sale.opportunity_id = o.id
             AND sale.system = 'odoo'
             AND sale.external_type = 'sale_order'
            LEFT JOIN operation_links phys
              ON phys.opportunity_id = o.id
             AND phys.system = 'odoo'
             AND phys.external_type = 'physical_status'
            LEFT JOIN operation_links inv
              ON inv.opportunity_id = o.id
             AND inv.system = 'jasmin'
             AND inv.external_type = 'invoice'
            WHERE COALESCE(o.status, '') IN ('open','won','closed','done','completed')
              AND (
                    COALESCE(o.stage, '') = ANY(CAST(:advanced AS text[]))
                 OR inv.id IS NOT NULL
              )
            ORDER BY o.updated_at DESC
            LIMIT 200
        """), {"advanced": list(ADVANCED_STAGES)}).mappings().all()

    missing_sale = [dict(r) for r in rows if not r.get("odoo_sale")]
    missing_physical = [dict(r) for r in rows if r.get("odoo_sale") and not r.get("physical_status")]

    report = {
        "generated_at": datetime.now(timezone.utc).isoformat(),
        "checked": len(rows),
        "missing_sale_order": missing_sale[:50],
        "missing_physical_status": missing_physical[:50],
    }
    out_dir = Path("audit_reports")
    out_dir.mkdir(exist_ok=True)
    out_path = out_dir / "odoo_opportunity_sync_audit.json"
    out_path.write_text(json.dumps(report, ensure_ascii=False, indent=2, default=str))

    print("ClientFlow Odoo opportunity sync audit")
    print("=" * 80)
    print("checked:", len(rows))
    print("missing_sale_order:", len(missing_sale))
    print("missing_physical_status:", len(missing_physical))
    print("JSON:", out_path)
    if missing_sale:
        print("\nExamples missing sale order:")
        for r in missing_sale[:10]:
            print(f"- {r['opportunity_id']} | {r.get('title')} | stage={r.get('stage')} | invoice={r.get('invoice_number')}")
    if missing_physical:
        print("\nExamples missing physical status:")
        for r in missing_physical[:10]:
            print(f"- {r['opportunity_id']} | {r.get('title')} | sale={r.get('odoo_sale')} | stage={r.get('stage')}")
    return 1 if missing_sale or missing_physical else 0


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