ALTER TABLE wa2_payment_receipts
  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 analysis_status VARCHAR(40) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS analysis_json LONGTEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS admin_note TEXT DEFAULT NULL,
  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 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_allocations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  receipt_id BIGINT UNSIGNED NOT NULL,
  invoice_id BIGINT UNSIGNED DEFAULT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  payment_id BIGINT UNSIGNED DEFAULT NULL,
  note VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_receipt (receipt_id),
  KEY idx_invoice (invoice_id),
  KEY idx_payment (payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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;
