-- WhatsApp CRM v6 - Comprobantes de transferencia: vista previa e imputación manual.
-- Ejecutar sobre la base del CRM luego de instalar v5.
-- No borra mensajes ni pagos existentes.

CREATE TABLE IF NOT EXISTS `whatsapp_payment_receipts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `whatsapp_media_id` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `original_filename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caption` text COLLATE utf8mb4_unicode_ci,
  `file_mime` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `extracted_amount` decimal(15,2) DEFAULT NULL,
  `extracted_date` date DEFAULT NULL,
  `extracted_reference` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sender_tax_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `receipt_date` date DEFAULT NULL,
  `raw_text` text COLLATE utf8mb4_unicode_ci,
  `validation_score` int(11) DEFAULT NULL,
  `validation_reason` text COLLATE utf8mb4_unicode_ci,
  `status` enum('pending','needs_review','not_receipt','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `admin_note` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `whatsapp_payment_receipts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `whatsapp_payment_receipts` MODIFY `status` enum('pending','needs_review','not_receipt','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending';

ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `invoice_id` bigint(20) unsigned DEFAULT NULL AFTER `customer_id`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `payment_id` bigint(20) unsigned DEFAULT NULL AFTER `invoice_id`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `amount_confirmed` decimal(18,2) DEFAULT NULL AFTER `extracted_amount`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `payment_method` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT 'transferencia' AFTER `amount_confirmed`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `payment_reference` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `payment_method`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `reviewed_by_user_id` int(10) unsigned DEFAULT NULL AFTER `admin_note`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `applied_at` datetime DEFAULT NULL AFTER `reviewed_at`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL AFTER `applied_at`;
ALTER TABLE `whatsapp_payment_receipts` ADD COLUMN IF NOT EXISTS `reject_reason` text COLLATE utf8mb4_unicode_ci AFTER `updated_at`;

-- Normalización básica de comprobantes ya recibidos sin status fuerte.
UPDATE `whatsapp_payment_receipts`
SET `status` = 'needs_review'
WHERE `status` = 'pending'
  AND (`validation_score` IS NULL OR `validation_score` >= 40);
