USE cashless_db;

ALTER TABLE payments
  ADD COLUMN manual_reference_normalized VARCHAR(80) DEFAULT NULL,
  ADD COLUMN receipt_sha256 CHAR(64) DEFAULT NULL,
  ADD COLUMN ocr_reference_no VARCHAR(80) DEFAULT NULL,
  ADD COLUMN ocr_amount DECIMAL(10,2) DEFAULT NULL,
  ADD COLUMN ocr_transaction_at DATETIME DEFAULT NULL,
  ADD COLUMN ocr_transaction_status VARCHAR(30) DEFAULT NULL,
  ADD COLUMN ocr_status VARCHAR(20) NOT NULL DEFAULT 'unavailable',
  ADD COLUMN ocr_warnings VARCHAR(255) DEFAULT NULL,
  ADD COLUMN amount_mismatch TINYINT(1) NOT NULL DEFAULT 0,
  ADD UNIQUE KEY uq_payments_manual_reference_normalized (manual_reference_normalized),
  ADD UNIQUE KEY uq_payments_receipt_sha256 (receipt_sha256);

CREATE TABLE IF NOT EXISTS payment_verification_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NOT NULL,
  actor_id INT UNSIGNED DEFAULT NULL,
  action VARCHAR(30) NOT NULL,
  from_status VARCHAR(30) DEFAULT NULL,
  to_status VARCHAR(30) NOT NULL,
  reason VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_history_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_payment_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_payment_history_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_payment_history_order_created (order_id, created_at)
) ENGINE=InnoDB;