CREATE TABLE IF NOT EXISTS affiliate_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_id VARCHAR(40) NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  telegram VARCHAR(120) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('affiliate', 'manager', 'admin') NOT NULL DEFAULT 'affiliate',
  parent_affiliate_user_id BIGINT UNSIGNED NULL,
  status ENUM('pending', 'active', 'suspended') NOT NULL DEFAULT 'pending',
  payout_method VARCHAR(80) NOT NULL DEFAULT 'bank_transfer',
  payout_address VARCHAR(190) NULL,
  payout_account_holder VARCHAR(160) NULL,
  payout_iban VARCHAR(80) NULL,
  payout_bic VARCHAR(40) NULL,
  payout_bank_name VARCHAR(120) NULL,
  payout_country VARCHAR(80) NULL,
  commission_rate_override DECIMAL(5,2) NULL,
  commission_override_note VARCHAR(255) NULL,
  sub_affiliate_rate_override DECIMAL(5,2) NULL,
  sub_affiliate_override_note VARCHAR(255) NULL,
  affiliate_manager_name VARCHAR(120) NULL,
  affiliate_manager_email VARCHAR(190) NULL,
  affiliate_manager_telegram VARCHAR(120) NULL,
  review_note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_affiliate_users_affiliate_id (affiliate_id),
  UNIQUE KEY uq_affiliate_users_email (email),
  KEY idx_affiliate_users_status (status),
  KEY idx_affiliate_users_parent (parent_affiliate_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  slug VARCHAR(80) NOT NULL,
  source VARCHAR(80) NULL,
  medium VARCHAR(80) NULL,
  campaign VARCHAR(120) NULL,
  clicks_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_affiliate_links_slug (slug),
  KEY idx_affiliate_links_user (affiliate_user_id),
  CONSTRAINT fk_affiliate_links_user FOREIGN KEY (affiliate_user_id) REFERENCES affiliate_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_link_clicks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  affiliate_link_id BIGINT UNSIGNED NULL,
  visitor_id VARCHAR(80) NULL,
  ip_hash CHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  referrer VARCHAR(500) NULL,
  landing_url VARCHAR(500) NULL,
  source VARCHAR(80) NULL,
  medium VARCHAR(80) NULL,
  campaign VARCHAR(120) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_affiliate_link_clicks_user_date (affiliate_user_id, created_at),
  KEY idx_affiliate_link_clicks_link_date (affiliate_link_id, created_at),
  KEY idx_affiliate_link_clicks_visitor (visitor_id),
  CONSTRAINT fk_affiliate_link_clicks_user FOREIGN KEY (affiliate_user_id) REFERENCES affiliate_users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_link_clicks_link FOREIGN KEY (affiliate_link_id) REFERENCES affiliate_links(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_conversions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  affiliate_link_id BIGINT UNSIGNED NULL,
  referred_user_id BIGINT UNSIGNED NULL,
  user_email VARCHAR(190) NULL,
  source VARCHAR(120) NULL,
  offer VARCHAR(80) NULL,
  status ENUM('Inscription', 'Paiement', 'Renouvelé') NOT NULL DEFAULT 'Inscription',
  subscription_plan ENUM('VIP 1 semaine', 'VIP 1 mois', 'VIP 6 mois') NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'topstats',
  provider_event_id VARCHAR(190) NULL,
  provider_payment_id VARCHAR(190) NULL,
  gross_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  net_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  commission_rate DECIMAL(5,2) NULL,
  commission_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  converted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_affiliate_conversions_user_date (affiliate_user_id, converted_at),
  KEY idx_affiliate_conversions_referred (referred_user_id),
  KEY idx_affiliate_conversions_status (status),
  CONSTRAINT fk_affiliate_conversions_user FOREIGN KEY (affiliate_user_id) REFERENCES affiliate_users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_conversions_link FOREIGN KEY (affiliate_link_id) REFERENCES affiliate_links(id) ON DELETE SET NULL,
  CONSTRAINT fk_affiliate_conversions_referred FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_user_commissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  conversion_id BIGINT UNSIGNED NULL,
  type ENUM('subscription', 'renewal', 'adjustment', 'sub_affiliate') NOT NULL DEFAULT 'subscription',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status ENUM('pending', 'approved', 'paid', 'rejected') NOT NULL DEFAULT 'approved',
  provider_event_id VARCHAR(190) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_affiliate_user_commissions_user_status (affiliate_user_id, status),
  CONSTRAINT fk_affiliate_user_commissions_user FOREIGN KEY (affiliate_user_id) REFERENCES affiliate_users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_user_commissions_conversion FOREIGN KEY (conversion_id) REFERENCES affiliate_conversions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_user_payouts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  method VARCHAR(80) NOT NULL DEFAULT 'bank_transfer',
  address VARCHAR(190) NULL,
  account_holder VARCHAR(160) NULL,
  iban VARCHAR(80) NULL,
  bic VARCHAR(40) NULL,
  bank_name VARCHAR(120) NULL,
  country VARCHAR(80) NULL,
  status ENUM('pending', 'approved', 'paid', 'rejected') NOT NULL DEFAULT 'pending',
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_affiliate_user_payouts_user_status (affiliate_user_id, status),
  CONSTRAINT fk_affiliate_user_payouts_user FOREIGN KEY (affiliate_user_id) REFERENCES affiliate_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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);

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);

CREATE TABLE IF NOT EXISTS affiliate_creatives (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(120) NOT NULL,
  offer VARCHAR(80) NOT NULL,
  image_url VARCHAR(500) NULL,
  target_url VARCHAR(500) NOT NULL,
  status ENUM('active', 'archived') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_affiliate_creatives_offer_title (offer, title),
  KEY idx_affiliate_creatives_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO affiliate_creatives (title, offer, image_url, target_url, status)
VALUES
  ('TopStats VIP 1 mois', 'VIP 1 mois', NULL, 'https://topstats.fr/pro?pack=monthly', 'active'),
  ('TopStats VIP 6 mois', 'VIP 6 mois', NULL, 'https://topstats.fr/pro?pack=six_months', 'active')
ON DUPLICATE KEY UPDATE title = VALUES(title);