-- Migration: Update deposits table for new payment system
-- Description: Adds new columns and updates structure for multi-payment gateway support
-- Date: 2026-02-20

-- Add new columns to existing deposits table
ALTER TABLE deposits
  -- Link to payment methods table
  ADD COLUMN payment_method_id INT AFTER payment_gateway,

  -- Payment reference (replaces paystack_reference)
  ADD COLUMN reference VARCHAR(100) UNIQUE AFTER payment_method_id,

  -- Transaction details
  ADD COLUMN transaction_id VARCHAR(255) COMMENT 'Provider transaction ID' AFTER reference,
  ADD COLUMN amount_paid DECIMAL(15, 2) COMMENT 'Actual amount paid by user' AFTER final_amount,
  ADD COLUMN fee DECIMAL(10, 2) DEFAULT 0 COMMENT 'Transaction fee' AFTER amount_paid,
  ADD COLUMN amount_credited DECIMAL(15, 2) COMMENT 'Amount credited to wallet (amount - fee)' AFTER fee,

  -- Enhanced status tracking
  MODIFY COLUMN status ENUM(
    'pending',
    'awaiting_proof',
    'verifying',
    'processing',
    'completed',
    'approved',
    'rejected',
    'failed',
    'cancelled',
    'expired',
    'initiated'
  ) DEFAULT 'pending',

  -- Payment proof for manual methods
  ADD COLUMN payment_account VARCHAR(255) COMMENT 'Account/wallet used for payment' AFTER status,
  ADD COLUMN payment_proof_url TEXT COMMENT 'Uploaded receipt/screenshot' AFTER payment_account,
  ADD COLUMN payment_details JSON COMMENT 'Additional payment info (account details, QR codes, etc.)' AFTER payment_proof_url,

  -- User and admin notes
  ADD COLUMN user_note TEXT AFTER payment_details,
  MODIFY COLUMN admin_note TEXT,

  -- Timestamps for tracking
  ADD COLUMN paid_at TIMESTAMP NULL COMMENT 'When payment was made',
  ADD COLUMN verified_at TIMESTAMP NULL COMMENT 'When payment was verified',
  ADD COLUMN completed_at TIMESTAMP NULL COMMENT 'When deposit was completed',
  ADD COLUMN expires_at TIMESTAMP NULL COMMENT 'Payment expiration time',

  -- Verification tracking
  ADD COLUMN verified_by INT COMMENT 'Admin user ID who verified',

  -- Foreign Keys
  ADD CONSTRAINT fk_deposits_payment_method
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id),
  ADD CONSTRAINT fk_deposits_verified_by
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL;

-- Add indexes for better query performance
ALTER TABLE deposits
  ADD INDEX idx_reference (reference),
  ADD INDEX idx_transaction_id (transaction_id),
  ADD INDEX idx_payment_method_id (payment_method_id),
  ADD INDEX idx_status_created (status, created_at),
  ADD INDEX idx_user_status (user_id, status);

-- Drop old paystack_reference column if it exists
ALTER TABLE deposits DROP COLUMN IF EXISTS paystack_reference;

-- Update existing records to use new structure
UPDATE deposits SET reference = CONCAT('LEGACY_', id) WHERE reference IS NULL;
