-- =====================================================================
-- Migration: Referral Freeze Threshold System
-- Date: 2026-08-23
-- Description: Adds ReferralFreezeThreshold table for configurable
--              balance thresholds and updates ReferralFreeze with
--              threshold tracking.
-- =====================================================================

CREATE TABLE IF NOT EXISTS `ReferralFreezeThreshold` (
  `id`                     VARCHAR(191)    NOT NULL PRIMARY KEY,
  `created_at`             DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`             DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `min_balance`            DECIMAL(30,10)  NOT NULL,
  `required_referrals`     INT            NOT NULL DEFAULT 3,
  `is_active`              BOOLEAN         NOT NULL DEFAULT TRUE,
  `sort_order`             INT            NOT NULL DEFAULT 0,
  `metadata`               JSON            NULL,
  INDEX `idx_rft_active_order` (`is_active`, `sort_order`),
  INDEX `idx_rft_min_balance` (`min_balance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `ReferralFreeze`
  ADD COLUMN `threshold_key` VARCHAR(50) NULL AFTER `user_id`,
  ADD INDEX `idx_referral_freeze_threshold` (`threshold_key`);