-- INTERLINK WhatsApp v2.8 - Comprobantes UI + imputación manual estilo módulo anterior.
-- No borra datos. Agrega columnas compatibles con la vista profesional.

CREATE TABLE IF NOT EXISTS wa2_payment_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  phone VARCHAR(32) NOT NULL,
  customer_id BIGINT UNSIGNED DEFAULT NULL,
  customer_name VARCHAR(190) DEFAULT NULL,
  message_id VARCHAR(190) DEFAULT NULL,
  media_id VARCHAR(190) DEFAULT NULL,
  media_type VARCHAR(40) DEFAULT NULL,
  mime_type VARCHAR(120) DEFAULT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending_review',
  note TEXT DEFAULT NULL,
  analysis_json LONGTEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_phone (phone),
  KEY idx_customer (customer_id),
  KEY idx_status (status),
  KEY idx_message_id (message_id),
  KEY idx_media_id (media_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE wa2_payment_receipts
  ADD COLUMN IF NOT EXISTS invoice_id BIGINT UNSIGNED DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS payment_id BIGINT UNSIGNED DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS local_path VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS original_filename VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS file_size BIGINT UNSIGNED DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS detected_amount DECIMAL(15,2) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS detected_date DATE DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS detected_reference VARCHAR(190) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS detected_origin_name VARCHAR(190) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS detected_destination_name VARCHAR(190) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS amount_confirmed DECIMAL(18,2) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS payment_method VARCHAR(40) DEFAULT 'transferencia',
  ADD COLUMN IF NOT EXISTS payment_reference VARCHAR(140) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS receipt_date DATE DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS raw_text LONGTEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS validation_score INT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS validation_reason TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS admin_note TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS reviewed_by BIGINT UNSIGNED DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS reviewed_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS reviewed_at DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS approved_at DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS rejected_at DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS reject_reason TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS applied_at DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS imputed_at DATETIME DEFAULT NULL;

CREATE TABLE IF NOT EXISTS wa2_receipt_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  receipt_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(80) NOT NULL,
  details_json LONGTEXT DEFAULT NULL,
  created_by BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_receipt (receipt_id),
  KEY idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE wa2_payment_receipts
SET status = 'pending'
WHERE status = 'pending_review';

UPDATE wa2_payment_receipts
SET status = 'approved'
WHERE status = 'imputed';
