ALTER TABLE affiliate_users
  ADD COLUMN IF NOT EXISTS role ENUM('affiliate', 'manager', 'admin') NOT NULL DEFAULT 'affiliate' AFTER password_hash,
  ADD COLUMN IF NOT EXISTS parent_affiliate_user_id BIGINT UNSIGNED NULL AFTER role,
  ADD COLUMN IF NOT EXISTS sub_affiliate_rate_override DECIMAL(5,2) NULL AFTER commission_override_note,
  ADD COLUMN IF NOT EXISTS sub_affiliate_override_note VARCHAR(255) NULL AFTER sub_affiliate_rate_override;

ALTER TABLE affiliate_user_commissions
  MODIFY type ENUM('subscription', 'renewal', 'adjustment', 'sub_affiliate') NOT NULL DEFAULT 'subscription';

CREATE TABLE IF NOT EXISTS affiliate_sub_commission_tiers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(120) NOT NULL,
  min_active_sub_affiliates INT UNSIGNED NOT NULL DEFAULT 1,
  max_active_sub_affiliates INT UNSIGNED NULL,
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 5.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_affiliate_sub_tiers_range (min_active_sub_affiliates, max_active_sub_affiliates)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO affiliate_sub_commission_tiers
  (label, min_active_sub_affiliates, max_active_sub_affiliates, commission_rate, is_active, sort_order)
VALUES
  ('1 à 3 sous-affiliés actifs', 1, 3, 5.00, 1, 10),
  ('4 à 9 sous-affiliés actifs', 4, 9, 7.50, 1, 20),
  ('10 sous-affiliés actifs et plus', 10, NULL, 10.00, 1, 30)
ON DUPLICATE KEY UPDATE commission_rate = VALUES(commission_rate), is_active = VALUES(is_active), sort_order = VALUES(sort_order);