CREATE TABLE IF NOT EXISTS affiliate_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  slug VARCHAR(60) NOT NULL,
  promo_code VARCHAR(60) NOT NULL,
  status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
  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_profiles_user (user_id),
  UNIQUE KEY uq_affiliate_profiles_slug (slug),
  UNIQUE KEY uq_affiliate_profiles_promo (promo_code),
  CONSTRAINT fk_affiliate_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_referrals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NOT NULL,
  source ENUM('link', 'checkout', 'manual', 'promo') NOT NULL DEFAULT 'link',
  first_cookie_code VARCHAR(60) NULL,
  locked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_affiliate_referrals_referred (referred_user_id),
  KEY idx_affiliate_referrals_profile (affiliate_profile_id),
  KEY idx_affiliate_referrals_affiliate_user (affiliate_user_id),
  CONSTRAINT fk_affiliate_referrals_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_referrals_affiliate_user FOREIGN KEY (affiliate_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_referrals_referred_user FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_commissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NOT NULL,
  referral_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'stripe',
  provider_event_id VARCHAR(190) NULL,
  provider_payment_id VARCHAR(190) NULL,
  subscription_id VARCHAR(190) NULL,
  plan VARCHAR(40) NULL,
  gross_amount_cents INT NOT NULL DEFAULT 0,
  net_amount_cents INT NOT NULL DEFAULT 0,
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  commission_amount_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  status ENUM('pending', 'payable', 'paid', 'canceled') NOT NULL DEFAULT 'payable',
  period_month CHAR(7) NOT NULL,
  paid_at DATETIME NULL,
  metadata JSON NULL,
  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_event (provider, provider_event_id),
  KEY idx_affiliate_commissions_profile_month (affiliate_profile_id, period_month),
  KEY idx_affiliate_commissions_referred (referred_user_id),
  KEY idx_affiliate_commissions_payment (provider_payment_id),
  CONSTRAINT fk_affiliate_commissions_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_commissions_referral FOREIGN KEY (referral_id) REFERENCES affiliate_referrals(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_commissions_affiliate_user FOREIGN KEY (affiliate_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_commissions_referred_user FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_payout_methods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  account_holder VARCHAR(160) NULL,
  iban VARCHAR(80) NULL,
  bic VARCHAR(40) NULL,
  country VARCHAR(80) NULL,
  address TEXT NULL,
  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_payout_profile (affiliate_profile_id),
  CONSTRAINT fk_affiliate_payout_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_payouts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  period_month CHAR(7) NOT NULL,
  amount_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  status ENUM('pending', 'paid') NOT NULL DEFAULT 'pending',
  due_date DATE NULL,
  paid_at DATETIME NULL,
  admin_note TEXT NULL,
  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_payout_profile_month (affiliate_profile_id, period_month),
  CONSTRAINT fk_affiliate_payouts_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;