-- Payment methods table + deposit columns (idempotent)
CREATE TABLE IF NOT EXISTS payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider ENUM('MTN MoMo','Airtel Money','Bank Transfer','USDT') NOT NULL,
  label VARCHAR(100) NOT NULL,
  merchant_code VARCHAR(50) NOT NULL,
  account_name VARCHAR(100) NOT NULL,
  logo_url VARCHAR(500) NULL,
  instructions TEXT NULL,
  is_active TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_active (is_active),
  INDEX idx_provider (provider)
) ENGINE=InnoDB;

-- Add deposit columns if missing (safe for re-run via procedure pattern)
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits' AND COLUMN_NAME = 'payment_method_id');
SET @sql := IF(@col = 0, 'ALTER TABLE deposits ADD COLUMN payment_method_id INT NULL AFTER operator', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits' AND COLUMN_NAME = 'sender_name');
SET @sql := IF(@col = 0, 'ALTER TABLE deposits ADD COLUMN sender_name VARCHAR(100) NULL AFTER account_number', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'deposits' AND COLUMN_NAME = 'transaction_ref');
SET @sql := IF(@col = 0, 'ALTER TABLE deposits ADD COLUMN transaction_ref VARCHAR(100) NULL AFTER sender_name', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO payment_methods (id, provider, label, merchant_code, account_name, logo_url, instructions, is_active, sort_order) VALUES
(1, 'MTN MoMo', 'MTN Mobile Money — Main Merchant', '720123', 'Aihub Ltd',
 'https://upload.wikimedia.org/wikipedia/commons/9/93/New-mtn-logo.jpg',
 '1. Dial *165# on your MTN line\n2. Select Pay Bill\n3. Enter merchant code 720123\n4. Enter the exact deposit amount\n5. Confirm with your MTN MoMo PIN\n6. Copy the transaction reference\n7. Submit the deposit form below with that reference',
 1, 1),
(2, 'Airtel Money', 'Airtel Money — Main Merchant', '450789', 'Aihub Ltd',
 'https://upload.wikimedia.org/wikipedia/commons/4/47/Airtel_logo.svg',
 '1. Dial *185# on your Airtel line\n2. Select Pay Merchant\n3. Enter merchant code 450789\n4. Enter the exact deposit amount\n5. Confirm with your Airtel Money PIN\n6. Copy the transaction ID from the SMS receipt\n7. Submit the deposit form below with that reference',
 1, 2);