-- INTERLINK WhatsApp v2.6 - comprobantes pendientes por WhatsApp
-- Seguro para ejecutar varias veces.
CREATE TABLE IF NOT EXISTS wa2_payment_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED DEFAULT NULL,
  phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  normalized_phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  customer_id INT UNSIGNED DEFAULT NULL,
  suggested_customer_id INT UNSIGNED DEFAULT NULL,
  status VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending_review',
  media_type VARCHAR(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  media_id VARCHAR(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  local_file VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  amount_detected DECIMAL(18,2) DEFAULT NULL,
  paid_at_detected DATETIME DEFAULT NULL,
  reference_detected VARCHAR(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  confidence DECIMAL(5,2) DEFAULT NULL,
  analysis_json MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_wa2_receipts_phone (normalized_phone, status),
  KEY idx_wa2_receipts_customer (customer_id, status),
  KEY idx_wa2_receipts_media (media_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
