#!/usr/bin/env python3
"""Repair premature shipment tasks created from Odoo picking state assigned.

Dry-run by default. The script only touches pending CREATE_SHIPMENT tasks when:
- the opportunity has an Odoo physical_status link with an outgoing picking assigned;
- no physical_validation=validated link exists;
- the picking is not done.
"""
from __future__ import annotations

import argparse
import json
import sys
from pathlib import Path

from sqlalchemy import text

PROJECT_ROOT = Path(__file__).resolve().parent.parent
if str(PROJECT_ROOT) not in sys.path:
    sys.path.insert(0, str(PROJECT_ROOT))

from app.db import engine


def _rows(limit: int) -> list[dict]:
    with engine.begin() as conn:
        rows = conn.execute(text("""
            SELECT
                t.id::text AS task_id,
                o.id::text AS opportunity_id,
                o.customer_name,
                o.stage,
                t.action_code,
                t.metadata AS task_metadata,
                ps.payload AS physical_payload,
                COALESCE(pv.status, '') AS validation_status
            FROM tasks t
            JOIN opportunities o ON o.id = t.opportunity_id
            JOIN operation_links ps
              ON ps.opportunity_id = o.id
             AND ps.system = 'odoo'
             AND ps.external_type = 'physical_status'
            LEFT JOIN operation_links pv
              ON pv.opportunity_id = o.id
             AND pv.system = 'odoo'
             AND pv.external_type = 'physical_validation'
            WHERE t.status = 'pending'
              AND t.action_code = 'CREATE_SHIPMENT'
              AND COALESCE(pv.status, '') <> 'validated'
              AND EXISTS (
                  SELECT 1
                  FROM jsonb_array_elements(
                      COALESCE(ps.payload->'outgoing_pickings', ps.payload->'pickings', '[]'::jsonb)
                  ) p
                  WHERE COALESCE(p->>'state', '') = 'assigned'
              )
              AND NOT EXISTS (
                  SELECT 1
                  FROM jsonb_array_elements(
                      COALESCE(ps.payload->'outgoing_pickings', ps.payload->'pickings', '[]'::jsonb)
                  ) p
                  WHERE COALESCE(p->>'state', '') = 'done'
              )
            ORDER BY t.created_at
            LIMIT :limit
        """), {"limit": limit}).mappings().all()
    return [dict(r) for r in rows]


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument('--apply', action='store_true')
    parser.add_argument('--limit', type=int, default=100)
    args = parser.parse_args()

    rows = _rows(args.limit)
    print(f"Premature CREATE_SHIPMENT tasks: {len(rows)}")
    for row in rows:
        print(f"{row['stage']:18} | {row['customer_name']}")
    if not args.apply:
        print('Dry-run only. Use --apply after reviewing the tasks.')
        return 0

    if not rows:
        return 0

    with engine.begin() as conn:
        for row in rows:
            metadata = dict(row.get('task_metadata') or {})
            metadata.update({
                'corrected_by': 'migration_v4928_1_5_130',
                'previous_action_code': 'CREATE_SHIPMENT',
                'correction_reason': 'Odoo assigned means stock reserved; physical validation still required',
            })
            conn.execute(text("""
                UPDATE tasks
                SET action_code = 'VALIDATE_PHYSICAL_ORDER',
                    route = 'logistica',
                    action = 'Validar encomenda física',
                    note = 'O picking está reservado no Odoo. Confirmar produtos, quantidades, embalagem e preparação física antes de criar o envio.',
                    safe_to_post = FALSE,
                    metadata = CAST(:metadata AS JSONB),
                    updated_at = now()
                WHERE id = CAST(:task_id AS UUID)
                  AND status = 'pending'
                  AND action_code = 'CREATE_SHIPMENT'
            """), {'task_id': row['task_id'], 'metadata': json.dumps(metadata, ensure_ascii=False)})
            conn.execute(text("""
                UPDATE opportunities
                SET stage = CASE WHEN stage = 'READY_TO_SHIP' THEN 'ORDER_PREPARATION' ELSE stage END,
                    last_action_code = 'VALIDATE_PHYSICAL_ORDER',
                    updated_at = now(),
                    metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object(
                        'assigned_picking_requires_physical_validation', TRUE,
                        'assigned_picking_gate_version', 'v4928.1.5.130'
                    )
                WHERE id = CAST(:opportunity_id AS UUID)
            """), {'opportunity_id': row['opportunity_id']})
    print(f"Tasks converted: {len(rows)}")
    return 0


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