-- Migration: Create transactions table
-- Description: Records all financial transactions (debits, credits, refunds)
-- Date: 2026-01-26

CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  order_id INT,
  deposit_id INT,
  type ENUM('debit', 'credit', 'bonus', 'withdrawal', 'refund') NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  post_balance DECIMAL(15, 2),
  status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
  description TEXT,
  reference VARCHAR(255),
  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,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  FOREIGN KEY (deposit_id) REFERENCES deposits(id) ON DELETE SET NULL,

  INDEX idx_user_id (user_id),
  INDEX idx_type (type),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
