-- Separate cash agencies; legacy agencies and agency_id continue to represent families.
CREATE TABLE IF NOT EXISTS recharge_agencies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid BIGINT UNSIGNED NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  country_code VARCHAR(4) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  image VARCHAR(500) NOT NULL,
  coins_balance BIGINT UNSIGNED NOT NULL DEFAULT 0,
  usd_balance DECIMAL(18,8) UNSIGNED NOT NULL DEFAULT 0,
  state TINYINT NOT NULL DEFAULT 9,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recharge_agency_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  agency_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(40) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  amount DECIMAL(23,8) NOT NULL,
  actor_id BIGINT UNSIGNED NOT NULL,
  operation_key VARCHAR(100) NOT NULL,
  target_uid BIGINT UNSIGNED NULL,
  withdrawal_request_id BIGINT UNSIGNED NULL,
  coins_balance BIGINT UNSIGNED NOT NULL,
  usd_balance DECIMAL(18,8) UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_cash_agency_operation (agency_id, operation_key),
  UNIQUE KEY uq_cash_agency_delivery (withdrawal_request_id, type),
  INDEX idx_cash_agency_history (agency_id,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='recharge_agency_id')=0, 'ALTER TABLE users ADD COLUMN recharge_agency_id BIGINT UNSIGNED NULL, ADD UNIQUE KEY uq_user_recharge_agency (recharge_agency_id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='recharge_requests' AND COLUMN_NAME='recharge_agency_id')=0, 'ALTER TABLE recharge_requests ADD COLUMN recharge_agency_id BIGINT UNSIGNED NULL, ADD INDEX idx_cash_agency_requests (recharge_agency_id,status,id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='wallet_transactions' AND COLUMN_NAME='recharge_agency_id')=0, 'ALTER TABLE wallet_transactions ADD COLUMN recharge_agency_id BIGINT UNSIGNED NULL, ADD INDEX idx_wallet_cash_agency (recharge_agency_id,id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='wallet_transactions' AND COLUMN_NAME='withdrawal_request_id')=0, 'ALTER TABLE wallet_transactions ADD COLUMN withdrawal_request_id BIGINT UNSIGNED NULL, ADD UNIQUE KEY uq_wallet_withdrawal_request (withdrawal_request_id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;