-- Admin & payments schema hardening (safe to re-run)

CREATE TABLE IF NOT EXISTS payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider ENUM('MTN MoMo','Airtel Money','Bank Transfer','USDT') NOT NULL DEFAULT 'MTN MoMo',
  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,
  INDEX idx_active (is_active),
  INDEX idx_provider (provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_developer_keys (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    label        VARCHAR(80) NOT NULL DEFAULT 'Default',
    api_key      VARCHAR(64) NOT NULL,
    api_secret   VARCHAR(128) NOT NULL,
    scopes       SET('post','like','read') NOT NULL DEFAULT 'post,like,read',
    status       ENUM('active','revoked') NOT NULL DEFAULT 'active',
    last_used_at DATETIME NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_api_key (api_key),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add deposit columns if missing (ignore errors on re-run via procedure pattern)
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'deposits' AND column_name = 'payment_method_id');
SET @sql = IF(@col_exists = 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_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'deposits' AND column_name = 'transaction_ref');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE deposits ADD COLUMN transaction_ref VARCHAR(100) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO payment_methods (id, provider, label, merchant_code, account_name, instructions, is_active, sort_order) VALUES
(1, 'MTN MoMo', 'MTN Mobile Money — Main Merchant', '720123', 'FXCAPXPRESS Ltd', 'Dial *165# → Pay Merchant → enter code → submit reference below.', 1, 1),
(2, 'Airtel Money', 'Airtel Money — Main Merchant', '450789', 'FXCAPXPRESS Ltd', 'Dial *185# → Pay Merchant → enter code → submit reference below.', 1, 2);