-- migrate:up

CREATE TABLE daily_statistics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  statistic_date DATE NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  registered_users BIGINT UNSIGNED NOT NULL DEFAULT 0,
  active_users BIGINT UNSIGNED NOT NULL DEFAULT 0,
  completed_tasks BIGINT UNSIGNED NOT NULL DEFAULT 0,
  task_rewards_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  referral_activations BIGINT UNSIGNED NOT NULL DEFAULT 0,
  referral_rewards_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  mystery_claims BIGINT UNSIGNED NOT NULL DEFAULT 0,
  mystery_rewards_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  withdrawal_requests BIGINT UNSIGNED NOT NULL DEFAULT 0,
  withdrawal_requested_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  withdrawal_paid_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  calculated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  source_watermark BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_daily_statistics_date_currency (statistic_date, currency_id),
  CONSTRAINT fk_daily_statistics_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE task_statistics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  statistic_date DATE NOT NULL,
  task_id BIGINT UNSIGNED NOT NULL,
  starts BIGINT UNSIGNED NOT NULL DEFAULT 0,
  eligible_attempts BIGINT UNSIGNED NOT NULL DEFAULT 0,
  completions BIGINT UNSIGNED NOT NULL DEFAULT 0,
  rejections BIGINT UNSIGNED NOT NULL DEFAULT 0,
  expirations BIGINT UNSIGNED NOT NULL DEFAULT 0,
  rewards_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  calculated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_statistics_date_task (statistic_date, task_id),
  KEY idx_task_statistics_task_date (task_id, statistic_date),
  CONSTRAINT fk_task_statistics_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE user_statistics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  statistic_date DATE NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  completed_tasks BIGINT UNSIGNED NOT NULL DEFAULT 0,
  active_referrals BIGINT UNSIGNED NOT NULL DEFAULT 0,
  earned_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  withdrawn_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  calculated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_statistics_date_user (statistic_date, user_id),
  KEY idx_user_statistics_user_date (user_id, statistic_date),
  CONSTRAINT fk_user_statistics_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE leaderboard_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  metric ENUM('verified_earnings', 'completed_tasks', 'active_referrals') NOT NULL,
  period_type ENUM('daily', 'weekly', 'monthly', 'all_time') NOT NULL,
  period_start DATE NULL,
  period_end DATE NULL,
  status ENUM('building', 'published', 'expired', 'failed') NOT NULL DEFAULT 'building',
  generated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  published_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_leaderboard_snapshots_public_id (public_id),
  UNIQUE KEY uq_leaderboard_snapshots_period (metric, period_type, period_start, period_end),
  KEY idx_leaderboard_snapshots_listing (status, metric, period_type, published_at),
  CONSTRAINT chk_leaderboard_snapshots_period CHECK (
    (period_type = 'all_time' AND period_start IS NULL AND period_end IS NULL) OR
    (period_type <> 'all_time' AND period_start IS NOT NULL AND period_end IS NOT NULL AND period_end >= period_start)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE leaderboard_entries (
  leaderboard_snapshot_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  rank_position INT UNSIGNED NOT NULL,
  score_value BIGINT UNSIGNED NOT NULL,
  display_snapshot JSON NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (leaderboard_snapshot_id, user_id),
  UNIQUE KEY uq_leaderboard_entries_rank (leaderboard_snapshot_id, rank_position),
  CONSTRAINT fk_leaderboard_entries_snapshot FOREIGN KEY (leaderboard_snapshot_id) REFERENCES leaderboard_snapshots (id) ON DELETE CASCADE,
  CONSTRAINT fk_leaderboard_entries_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT chk_leaderboard_entries_rank CHECK (rank_position > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS leaderboard_entries;
DROP TABLE IF EXISTS leaderboard_snapshots;
DROP TABLE IF EXISTS user_statistics;
DROP TABLE IF EXISTS task_statistics;
DROP TABLE IF EXISTS daily_statistics;