ALTER TABLE affiliate_users
  MODIFY payout_method VARCHAR(80) NOT NULL DEFAULT 'bank_transfer',
  ADD COLUMN IF NOT EXISTS payout_account_holder VARCHAR(160) NULL AFTER payout_address,
  ADD COLUMN IF NOT EXISTS payout_iban VARCHAR(80) NULL AFTER payout_account_holder,
  ADD COLUMN IF NOT EXISTS payout_bic VARCHAR(40) NULL AFTER payout_iban,
  ADD COLUMN IF NOT EXISTS payout_bank_name VARCHAR(120) NULL AFTER payout_bic,
  ADD COLUMN IF NOT EXISTS payout_country VARCHAR(80) NULL AFTER payout_bank_name,
  ADD COLUMN IF NOT EXISTS commission_rate_override DECIMAL(5,2) NULL AFTER payout_country,
  ADD COLUMN IF NOT EXISTS commission_override_note VARCHAR(255) NULL AFTER commission_rate_override;

UPDATE affiliate_users
SET payout_method = 'bank_transfer'
WHERE payout_method IS NULL OR payout_method = '' OR payout_method LIKE '%USDT%';

ALTER TABLE affiliate_link_clicks
  ADD COLUMN IF NOT EXISTS source VARCHAR(80) NULL AFTER landing_url,
  ADD COLUMN IF NOT EXISTS medium VARCHAR(80) NULL AFTER source,
  ADD COLUMN IF NOT EXISTS campaign VARCHAR(120) NULL AFTER medium;

ALTER TABLE affiliate_conversions
  ADD COLUMN IF NOT EXISTS referred_user_id BIGINT UNSIGNED NULL AFTER affiliate_link_id,
  ADD COLUMN IF NOT EXISTS provider VARCHAR(40) NOT NULL DEFAULT 'topstats' AFTER subscription_plan,
  ADD COLUMN IF NOT EXISTS provider_event_id VARCHAR(190) NULL AFTER provider,
  ADD COLUMN IF NOT EXISTS provider_payment_id VARCHAR(190) NULL AFTER provider_event_id,
  ADD COLUMN IF NOT EXISTS gross_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER provider_payment_id,
  ADD COLUMN IF NOT EXISTS net_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER gross_amount,
  ADD COLUMN IF NOT EXISTS commission_rate DECIMAL(5,2) NULL AFTER net_amount,
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE affiliate_conversions
  ADD UNIQUE KEY uq_affiliate_conversions_provider_event (provider, provider_event_id);

ALTER TABLE affiliate_user_commissions
  ADD COLUMN IF NOT EXISTS provider_event_id VARCHAR(190) NULL AFTER status;

ALTER TABLE affiliate_user_commissions
  ADD UNIQUE KEY uq_affiliate_user_commissions_provider_event (provider_event_id);

ALTER TABLE affiliate_user_payouts
  MODIFY method VARCHAR(80) NOT NULL DEFAULT 'bank_transfer',
  ADD COLUMN IF NOT EXISTS account_holder VARCHAR(160) NULL AFTER address,
  ADD COLUMN IF NOT EXISTS iban VARCHAR(80) NULL AFTER account_holder,
  ADD COLUMN IF NOT EXISTS bic VARCHAR(40) NULL AFTER iban,
  ADD COLUMN IF NOT EXISTS bank_name VARCHAR(120) NULL AFTER bic,
  ADD COLUMN IF NOT EXISTS country VARCHAR(80) NULL AFTER bank_name;

UPDATE affiliate_user_payouts
SET method = 'bank_transfer'
WHERE method IS NULL OR method = '' OR method LIKE '%USDT%';

CREATE TABLE IF NOT EXISTS affiliate_commission_tiers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(120) NOT NULL,
  min_active_customers INT UNSIGNED NOT NULL DEFAULT 1,
  max_active_customers INT UNSIGNED NULL,
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 20.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_commission_tiers_range (min_active_customers, max_active_customers)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO affiliate_commission_tiers
  (label, min_active_customers, max_active_customers, commission_rate, is_active, sort_order)
VALUES
  ('1 à 3 clients actifs', 1, 3, 20.00, 1, 10),
  ('4 à 7 clients actifs', 4, 7, 25.00, 1, 20),
  ('8 clients actifs et plus', 8, NULL, 30.00, 1, 30)
ON DUPLICATE KEY UPDATE commission_rate = VALUES(commission_rate), is_active = VALUES(is_active), sort_order = VALUES(sort_order);