-- Remove the obsolete generic USD withdrawal request table from the wallet database.
-- Salary settlement now writes role-specific wallets only:
-- HOST_SALARY_USD, AGENCY_SALARY_USD, BD_SALARY_USD, ADMIN_SALARY_USD.
-- Keep an archive copy before dropping the source table so production history is not lost.

SET @source_exists := (
  SELECT COUNT(*)
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'wallet_withdrawal_requests'
);

SET @archive_exists := (
  SELECT COUNT(*)
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'wallet_withdrawal_requests_removed_20260602'
);

SET @sql := IF(
  @source_exists = 1 AND @archive_exists = 0,
  'CREATE TABLE wallet_withdrawal_requests_removed_20260602 LIKE wallet_withdrawal_requests',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @source_exists = 1,
  'INSERT IGNORE INTO wallet_withdrawal_requests_removed_20260602 SELECT * FROM wallet_withdrawal_requests',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  @source_exists = 1,
  'DROP TABLE wallet_withdrawal_requests',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT
  SUM(TABLE_NAME = 'wallet_withdrawal_requests') AS source_table_exists,
  SUM(TABLE_NAME = 'wallet_withdrawal_requests_removed_20260602') AS archive_table_exists
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    'wallet_withdrawal_requests',
    'wallet_withdrawal_requests_removed_20260602'
  );