-- Migration: Create deposits table
-- Description: Tracks user account deposits/topups
-- Date: 2026-01-26

CREATE TABLE IF NOT EXISTS deposits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  payment_gateway VARCHAR(50) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  charge DECIMAL(10, 2) DEFAULT 0,
  final_amount DECIMAL(10, 2) NOT NULL,
  payment_reference VARCHAR(255),
  status ENUM('pending', 'approved', 'rejected', 'failed', 'initiated') DEFAULT 'pending',
  admin_note TEXT,
  approved_by INT,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_payment_gateway (payment_gateway),
  INDEX idx_created_at (created_at),
  INDEX idx_payment_reference (payment_reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
