CREATE TABLE IF NOT EXISTS withdrawal_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  currency VARCHAR(10) NOT NULL DEFAULT 'GHS',
  minimum_withdrawal DECIMAL(10,2) NOT NULL DEFAULT 50.00,
  maximum_daily_withdrawal DECIMAL(10,2) NOT NULL DEFAULT 2000.00,
  maximum_requests_per_day INT NOT NULL DEFAULT 3,
  withdrawal_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  require_admin_approval TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO withdrawal_settings (
  id,
  currency,
  minimum_withdrawal,
  maximum_daily_withdrawal,
  maximum_requests_per_day,
  withdrawal_fee,
  require_admin_approval,
  active
) VALUES (
  1, 'GHS', 50.00, 2000.00, 3, 0.00, 1, 1
)
ON DUPLICATE KEY UPDATE
  currency = VALUES(currency),
  minimum_withdrawal = VALUES(minimum_withdrawal),
  maximum_daily_withdrawal = VALUES(maximum_daily_withdrawal),
  maximum_requests_per_day = VALUES(maximum_requests_per_day),
  withdrawal_fee = VALUES(withdrawal_fee),
  require_admin_approval = VALUES(require_admin_approval),
  active = VALUES(active);

CREATE TABLE IF NOT EXISTS partner_wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_code VARCHAR(50) NOT NULL UNIQUE,
  currency VARCHAR(10) NOT NULL DEFAULT 'GHS',
  available_balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  pending_withdrawal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_earned DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_withdrawn DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_partner_wallets_partner_code (partner_code)
);

CREATE TABLE IF NOT EXISTS wallet_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entry_ref VARCHAR(100) NOT NULL UNIQUE,
  partner_code VARCHAR(50) NOT NULL,
  entry_type ENUM(
    'ride_earning',
    'withdrawal_hold',
    'withdrawal_released',
    'withdrawal_paid',
    'withdrawal_failed_return',
    'adjustment_credit',
    'adjustment_debit'
  ) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  available_balance_after DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  pending_withdrawal_after DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  source_ref VARCHAR(100) NULL,
  description TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_wallet_ledger_partner_code (partner_code),
  INDEX idx_wallet_ledger_source_ref (source_ref)
);

CREATE TABLE IF NOT EXISTS withdrawal_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_code VARCHAR(50) NOT NULL,
  provider VARCHAR(50) NOT NULL DEFAULT 'paystack',
  account_type ENUM('mobile_money','ghipss','bank') NOT NULL DEFAULT 'mobile_money',
  account_name VARCHAR(190) NOT NULL,
  account_number VARCHAR(80) NOT NULL,
  bank_code VARCHAR(50) NOT NULL,
  bank_name VARCHAR(190) NULL,
  network_name VARCHAR(80) NULL,
  recipient_code VARCHAR(100) NULL,
  is_verified TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_partner_account (partner_code, provider, account_number, bank_code),
  INDEX idx_withdrawal_accounts_partner_code (partner_code)
);

CREATE TABLE IF NOT EXISTS withdrawal_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  withdrawal_ref VARCHAR(100) NOT NULL UNIQUE,
  partner_code VARCHAR(50) NOT NULL,
  account_id BIGINT UNSIGNED NULL,
  amount DECIMAL(10,2) NOT NULL,
  withdrawal_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  net_amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'GHS',
  provider VARCHAR(50) NOT NULL DEFAULT 'paystack',
  method ENUM('mobile_money','ghipss','bank','manual') NOT NULL DEFAULT 'mobile_money',
  status ENUM(
    'requested',
    'approved',
    'processing',
    'otp',
    'pending',
    'paid',
    'failed',
    'reversed',
    'rejected',
    'cancelled'
  ) NOT NULL DEFAULT 'requested',
  provider_reference VARCHAR(100) NULL,
  transfer_code VARCHAR(100) NULL,
  failure_reason TEXT NULL,
  rules_snapshot JSON NULL,
  requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_at DATETIME NULL,
  processed_at DATETIME NULL,
  paid_at DATETIME NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_withdrawal_partner_code (partner_code),
  INDEX idx_withdrawal_status (status),
  INDEX idx_withdrawal_provider_ref (provider_reference)
);

CREATE TABLE IF NOT EXISTS webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(50) NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  event_reference VARCHAR(190) NOT NULL,
  payload LONGTEXT NOT NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_provider_event (provider, event_type, event_reference)
);