USE cashless_db;

ALTER TABLE payments
  MODIFY COLUMN status ENUM('pending','pending_verification','paid','failed','rejected','refunded') NOT NULL DEFAULT 'pending',
  ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'PHP',
  ADD COLUMN manual_reference_no VARCHAR(80) DEFAULT NULL,
  ADD COLUMN receipt_path VARCHAR(255) DEFAULT NULL,
  ADD COLUMN verified_at DATETIME DEFAULT NULL,
  ADD COLUMN verified_by INT UNSIGNED DEFAULT NULL,
  ADD COLUMN rejected_at DATETIME DEFAULT NULL,
  ADD COLUMN rejected_by INT UNSIGNED DEFAULT NULL,
  ADD COLUMN rejection_reason VARCHAR(255) DEFAULT NULL,
  ADD CONSTRAINT fk_payments_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
  ADD CONSTRAINT fk_payments_rejected_by FOREIGN KEY (rejected_by) REFERENCES users(id) ON DELETE SET NULL,
  ADD UNIQUE KEY uq_payments_manual_reference (manual_reference_no);