USE TESVISCOVENTURES;

CREATE TABLE IF NOT EXISTS refunds (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  order_number VARCHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  reason VARCHAR(255) NOT NULL DEFAULT '',
  method VARCHAR(30) NOT NULL DEFAULT 'manual',
  status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  gateway_reference VARCHAR(120) NOT NULL DEFAULT '',
  notes TEXT NULL,
  requested_by BIGINT UNSIGNED NULL,
  processed_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  INDEX idx_refund_order (order_id),
  INDEX idx_refund_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;