-- migrate:up

CREATE TABLE referral_codes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status ENUM('active', 'rotated', 'disabled') NOT NULL DEFAULT 'active',
  active_user_id BIGINT UNSIGNED GENERATED ALWAYS AS (CASE WHEN status = 'active' THEN user_id ELSE NULL END) STORED,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  rotated_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_referral_codes_code (code),
  UNIQUE KEY uq_referral_codes_active_user (active_user_id),
  CONSTRAINT fk_referral_codes_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE referrals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  referrer_user_id BIGINT UNSIGNED NOT NULL,
  referee_user_id BIGINT UNSIGNED NOT NULL,
  referral_code_id BIGINT UNSIGNED NOT NULL,
  attribution_source ENUM('bot_start', 'mini_app', 'admin_import') NOT NULL,
  status ENUM('pending', 'active', 'held', 'rejected', 'reversed') NOT NULL DEFAULT 'pending',
  risk_state ENUM('normal', 'observed', 'review', 'blocked') NOT NULL DEFAULT 'normal',
  attributed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  status_changed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_referrals_public_id (public_id),
  UNIQUE KEY uq_referrals_referee (referee_user_id),
  KEY idx_referrals_referrer_status (referrer_user_id, status, attributed_at),
  KEY idx_referrals_risk_status (risk_state, status, attributed_at),
  CONSTRAINT fk_referrals_referrer FOREIGN KEY (referrer_user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_referrals_referee FOREIGN KEY (referee_user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_referrals_code FOREIGN KEY (referral_code_id) REFERENCES referral_codes (id) ON DELETE RESTRICT,
  CONSTRAINT chk_referrals_not_self CHECK (referrer_user_id <> referee_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE referral_activations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  referral_id BIGINT UNSIGNED NOT NULL,
  threshold_snapshot JSON NOT NULL,
  qualifying_task_count INT UNSIGNED NOT NULL DEFAULT 0,
  referee_account_age_seconds BIGINT UNSIGNED NOT NULL DEFAULT 0,
  activation_event_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  activated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_referral_activations_referral (referral_id),
  UNIQUE KEY uq_referral_activations_event (activation_event_key),
  CONSTRAINT fk_referral_activations_referral FOREIGN KEY (referral_id) REFERENCES referrals (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE referral_rewards (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  referral_id BIGINT UNSIGNED NOT NULL,
  beneficiary_user_id BIGINT UNSIGNED NOT NULL,
  reward_type ENUM('referrer', 'referee') NOT NULL,
  event_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  amount_minor BIGINT UNSIGNED NOT NULL,
  currency_code CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status ENUM('pending', 'posted', 'reversed') NOT NULL DEFAULT 'pending',
  ledger_transaction_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  posted_at DATETIME(6) NULL,
  reversed_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_referral_rewards_public_id (public_id),
  UNIQUE KEY uq_referral_rewards_event (event_key),
  UNIQUE KEY uq_referral_rewards_once (referral_id, beneficiary_user_id, reward_type),
  KEY idx_referral_rewards_beneficiary (beneficiary_user_id, status, created_at),
  CONSTRAINT fk_referral_rewards_referral FOREIGN KEY (referral_id) REFERENCES referrals (id) ON DELETE RESTRICT,
  CONSTRAINT fk_referral_rewards_beneficiary FOREIGN KEY (beneficiary_user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT chk_referral_rewards_amount CHECK (amount_minor > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS referral_rewards;
DROP TABLE IF EXISTS referral_activations;
DROP TABLE IF EXISTS referrals;
DROP TABLE IF EXISTS referral_codes;