-- migrate:up

CREATE TABLE task_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  description VARCHAR(500) NULL,
  icon_reference VARCHAR(255) NULL,
  display_order INT NOT NULL DEFAULT 0,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_categories_public_id (public_id),
  UNIQUE KEY uq_task_categories_slug (slug),
  KEY idx_task_categories_status_order (status, display_order),
  CONSTRAINT fk_task_categories_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(191) NOT NULL,
  description TEXT NOT NULL,
  icon_reference VARCHAR(255) NULL,
  destination_url VARCHAR(2048) NOT NULL,
  browser_mode ENUM('telegram', 'external') NOT NULL DEFAULT 'telegram',
  task_type ENUM('one_time', 'daily', 'repeatable') NOT NULL DEFAULT 'one_time',
  verification_mode ENUM('timer', 'manual', 'callback', 'telegram_membership') NOT NULL DEFAULT 'timer',
  reward_minor BIGINT UNSIGNED NOT NULL,
  wait_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  attempt_expiry_seconds INT UNSIGNED NOT NULL DEFAULT 3600,
  repeat_cooldown_seconds INT UNSIGNED NULL,
  daily_completion_limit SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  total_completion_limit BIGINT UNSIGNED NULL,
  eligibility_rules JSON NULL,
  provider_code VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
  priority INT NOT NULL DEFAULT 0,
  status ENUM('draft', 'scheduled', 'active', 'paused', 'ended', 'archived') NOT NULL DEFAULT 'draft',
  starts_at DATETIME(6) NULL,
  ends_at DATETIME(6) NULL,
  published_at DATETIME(6) NULL,
  created_by_admin_id BIGINT UNSIGNED NOT NULL,
  updated_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  deleted_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tasks_public_id (public_id),
  KEY idx_tasks_listing (status, starts_at, ends_at, priority, id),
  KEY idx_tasks_category_listing (category_id, status, priority),
  CONSTRAINT fk_tasks_category FOREIGN KEY (category_id) REFERENCES task_categories (id) ON DELETE SET NULL,
  CONSTRAINT fk_tasks_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT fk_tasks_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT fk_tasks_updater FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_tasks_reward CHECK (reward_minor > 0),
  CONSTRAINT chk_tasks_dates CHECK (ends_at IS NULL OR starts_at IS NULL OR ends_at > starts_at),
  CONSTRAINT chk_tasks_limits CHECK (daily_completion_limit > 0 AND attempt_expiry_seconds > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE task_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  task_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('started', 'eligible', 'verifying', 'completed', 'expired', 'rejected', 'cancelled') NOT NULL DEFAULT 'started',
  verification_mode VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reward_minor_snapshot BIGINT UNSIGNED NOT NULL,
  currency_code_snapshot CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  currency_decimals_snapshot TINYINT UNSIGNED NOT NULL,
  idempotency_key_hash BINARY(32) NULL,
  started_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  eligible_at DATETIME(6) NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  completed_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_attempts_public_id (public_id),
  UNIQUE KEY uq_task_attempts_user_idempotency (user_id, idempotency_key_hash),
  KEY idx_task_attempts_user_status (user_id, status, eligible_at),
  KEY idx_task_attempts_task_status (task_id, status, expires_at),
  CONSTRAINT fk_task_attempts_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE RESTRICT,
  CONSTRAINT fk_task_attempts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT chk_task_attempts_reward CHECK (reward_minor_snapshot > 0),
  CONSTRAINT chk_task_attempts_time CHECK (eligible_at >= started_at AND expires_at > eligible_at),
  CONSTRAINT chk_task_attempts_decimals CHECK (currency_decimals_snapshot <= 6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE task_completions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  attempt_id BIGINT UNSIGNED NOT NULL,
  task_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  completion_period_key VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reward_minor BIGINT UNSIGNED NOT NULL,
  currency_code CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  ledger_transaction_id BIGINT UNSIGNED NULL,
  completed_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_task_completions_public_id (public_id),
  UNIQUE KEY uq_task_completions_attempt (attempt_id),
  UNIQUE KEY uq_task_completions_period (task_id, user_id, completion_period_key),
  KEY idx_task_completions_user_time (user_id, completed_at, id),
  KEY idx_task_completions_task_time (task_id, completed_at),
  CONSTRAINT fk_task_completions_attempt FOREIGN KEY (attempt_id) REFERENCES task_attempts (id) ON DELETE RESTRICT,
  CONSTRAINT fk_task_completions_task FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE RESTRICT,
  CONSTRAINT fk_task_completions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT chk_task_completions_reward CHECK (reward_minor > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE task_verifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  attempt_id BIGINT UNSIGNED NOT NULL,
  mode ENUM('manual', 'callback', 'telegram_membership') NOT NULL,
  status ENUM('pending', 'verified', 'rejected', 'expired') NOT NULL DEFAULT 'pending',
  provider_event_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NULL,
  request_hash BINARY(32) NULL,
  evidence JSON NULL,
  reviewed_by_admin_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_task_verifications_public_id (public_id),
  UNIQUE KEY uq_task_verifications_provider_event (provider_event_key),
  KEY idx_task_verifications_attempt_status (attempt_id, status),
  CONSTRAINT fk_task_verifications_attempt FOREIGN KEY (attempt_id) REFERENCES task_attempts (id) ON DELETE RESTRICT,
  CONSTRAINT fk_task_verifications_reviewer FOREIGN KEY (reviewed_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS task_verifications;
DROP TABLE IF EXISTS task_completions;
DROP TABLE IF EXISTS task_attempts;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS task_categories;