-- ====================================================================
-- Remove the referral program completely (2026-09-26)
-- The store owner decided to drop referrals for now (may come back later;
-- the code is in git history before this date). Removes:
--   - tables referral_earnings, withdrawal_requests (all their rows);
--   - users.referral_code, users.referred_by (+ FK / unique key);
--   - site_settings.referral_enabled / _reward_type / _reward_value /
--     _min_order_to_earn;
--   - permissions withdrawal_requests:read / :update (and their role grants).
--
-- SAFETY: if a withdrawal request is still pending or approved (money a
-- customer asked for and was not paid yet), the migration STOPS before
-- changing anything, with the error
--   "Table '...REFERRAL_REMOVAL_BLOCKED_unpaid_withdrawal_requests' doesn't exist".
-- Pay or reject those requests in the admin first, then run it again.
-- Safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

-- ─── Guard: unpaid withdrawal requests ──────────────────────────────────────
SET @has_wr = (SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'withdrawal_requests');
SET @sql = IF(@has_wr = 1,
  'SELECT COUNT(*) INTO @unpaid FROM withdrawal_requests WHERE status IN (''pending'', ''approved'')',
  'SET @unpaid = 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(@unpaid > 0, 'SELECT 1 FROM REFERRAL_REMOVAL_BLOCKED_unpaid_withdrawal_requests', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ─── Tables ─────────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS `referral_earnings`;
DROP TABLE IF EXISTS `withdrawal_requests`;

-- ─── users.referred_by (FK first) and users.referral_code ───────────────────
SET @has_fk = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'fk_users_referred_by');
SET @sql = IF(@has_fk = 1, 'ALTER TABLE `users` DROP FOREIGN KEY `fk_users_referred_by`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Any other FK on users.referred_by (e.g. an auto-named one from create_table.sql).
SET @fk = (SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'referred_by'
    AND REFERENCED_TABLE_NAME IS NOT NULL LIMIT 1);
SET @sql = IF(@fk IS NOT NULL, CONCAT('ALTER TABLE `users` DROP FOREIGN KEY `', @fk, '`'), 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'referred_by');
SET @sql = IF(@has_col = 1, 'ALTER TABLE `users` DROP COLUMN `referred_by`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'referral_code');
SET @sql = IF(@has_col = 1, 'ALTER TABLE `users` DROP COLUMN `referral_code`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ─── site_settings referral columns ─────────────────────────────────────────
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_settings' AND COLUMN_NAME = 'referral_enabled');
SET @sql = IF(@has_col = 1,
  'ALTER TABLE `site_settings` DROP COLUMN `referral_enabled`, DROP COLUMN `referral_reward_type`, DROP COLUMN `referral_reward_value`, DROP COLUMN `referral_min_order_to_earn`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ─── Permissions ────────────────────────────────────────────────────────────
DELETE rp FROM role_permissions rp
  JOIN permissions p ON p.id = rp.permission_id
  WHERE p.name IN ('withdrawal_requests:read', 'withdrawal_requests:update');
DELETE FROM permissions WHERE name IN ('withdrawal_requests:read', 'withdrawal_requests:update');
