-- INTERLINK WhatsApp v2.8.1
-- Verificación de solo lectura del hotfix de comprobantes.
-- No inserta, actualiza ni elimina datos.

USE `interlink_crm`;

SELECT DATABASE() AS base_actual;

-- Conversaciones que están esperando o ya recibieron un comprobante.
SELECT
    id,
    normalized_phone,
    customer_id,
    current_intent,
    current_state,
    JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.topic')) AS topic,
    JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.requested_field')) AS requested_field,
    JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.payment_state')) AS payment_state,
    updated_at
FROM wa2_conversations
WHERE current_state IN ('payment_waiting_receipt', 'payment_receipt_pending_review')
   OR JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.topic')) = 'payment'
ORDER BY updated_at DESC
LIMIT 50;

-- Decisiones recientes de imágenes/documentos: después del hotfix, un archivo enviado
-- en payment_waiting_receipt debe terminar como payment_receipt_received.
SELECT
    id,
    conversation_id,
    intent,
    source,
    JSON_UNQUOTE(JSON_EXTRACT(decision_json, '$.receipt_guard_reason')) AS receipt_guard_reason,
    JSON_UNQUOTE(JSON_EXTRACT(decision_json, '$.receipt_guard_verified')) AS guard_verified,
    JSON_UNQUOTE(JSON_EXTRACT(decision_json, '$.queued_by_expected_payment_flow')) AS queued_by_expected_payment_flow,
    JSON_UNQUOTE(JSON_EXTRACT(decision_json, '$.requires_manual_review')) AS requires_manual_review,
    created_at
FROM wa2_ai_decisions
WHERE intent IN ('media_received_not_receipt', 'payment_receipt_received')
ORDER BY id DESC
LIMIT 50;

-- Comprobantes pendientes creados recientemente.
SELECT
    id,
    conversation_id,
    normalized_phone,
    customer_id,
    status,
    media_type,
    media_id,
    amount_detected,
    paid_at_detected,
    reference_detected,
    confidence,
    JSON_UNQUOTE(JSON_EXTRACT(analysis_json, '$.guard_reason')) AS guard_reason,
    JSON_UNQUOTE(JSON_EXTRACT(analysis_json, '$.guard_verified')) AS guard_verified,
    JSON_UNQUOTE(JSON_EXTRACT(analysis_json, '$.queued_by_expected_payment_flow')) AS queued_by_expected_payment_flow,
    JSON_UNQUOTE(JSON_EXTRACT(analysis_json, '$.requires_manual_review')) AS requires_manual_review,
    created_at
FROM wa2_payment_receipts
ORDER BY id DESC
LIMIT 50;
