-- migrate:up

CREATE TABLE mystery_boxes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(191) NOT NULL,
  description TEXT NOT NULL,
  image_reference VARCHAR(255) NULL,
  selection_mode ENUM('fixed', 'weighted_random') NOT NULL DEFAULT 'fixed',
  frequency ENUM('daily', 'weekly', 'lifetime', 'cooldown') NOT NULL DEFAULT 'lifetime',
  required_completed_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  cooldown_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  access_cost_minor BIGINT UNSIGNED NOT NULL DEFAULT 0,
  requires_purchase TINYINT(1) NOT NULL DEFAULT 0,
  disclosure_text VARCHAR(1000) NOT NULL,
  starts_at DATETIME(6) NULL,
  ends_at DATETIME(6) NULL,
  status ENUM('draft', 'active', 'paused', 'ended', 'archived') NOT NULL DEFAULT 'draft',
  created_by_admin_id BIGINT UNSIGNED NOT 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_mystery_boxes_public_id (public_id),
  KEY idx_mystery_boxes_active (status, starts_at, ends_at),
  CONSTRAINT fk_mystery_boxes_creator FOREIGN KEY (created_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT chk_mystery_boxes_dates CHECK (ends_at IS NULL OR starts_at IS NULL OR ends_at > starts_at),
  CONSTRAINT chk_mystery_boxes_free_only CHECK (access_cost_minor = 0 AND requires_purchase = 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE mystery_box_rewards (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  mystery_box_id BIGINT UNSIGNED NOT NULL,
  currency_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(120) NOT NULL,
  amount_minor BIGINT UNSIGNED NOT NULL,
  weight INT UNSIGNED NOT NULL DEFAULT 1,
  inventory_limit BIGINT UNSIGNED NULL,
  awarded_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  display_order INT NOT NULL DEFAULT 0,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_mystery_box_rewards_public_id (public_id),
  KEY idx_mystery_box_rewards_selection (mystery_box_id, status, display_order),
  CONSTRAINT fk_mystery_box_rewards_box FOREIGN KEY (mystery_box_id) REFERENCES mystery_boxes (id) ON DELETE RESTRICT,
  CONSTRAINT fk_mystery_box_rewards_currency FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE RESTRICT,
  CONSTRAINT chk_mystery_box_rewards_amount CHECK (amount_minor > 0 AND weight > 0),
  CONSTRAINT chk_mystery_box_rewards_inventory CHECK (inventory_limit IS NULL OR awarded_count <= inventory_limit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE mystery_box_claims (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  mystery_box_id BIGINT UNSIGNED NOT NULL,
  mystery_box_reward_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  opportunity_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  idempotency_key_hash BINARY(32) NOT NULL,
  amount_minor_snapshot BIGINT UNSIGNED NOT NULL,
  currency_code_snapshot CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  selection_proof_hash BINARY(32) NULL,
  ledger_transaction_id BIGINT UNSIGNED NOT NULL,
  claimed_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_mystery_box_claims_public_id (public_id),
  UNIQUE KEY uq_mystery_box_claims_opportunity (mystery_box_id, user_id, opportunity_key),
  UNIQUE KEY uq_mystery_box_claims_user_idempotency (user_id, idempotency_key_hash),
  UNIQUE KEY uq_mystery_box_claims_ledger_tx (ledger_transaction_id),
  KEY idx_mystery_box_claims_user_time (user_id, claimed_at, id),
  CONSTRAINT fk_mystery_box_claims_box FOREIGN KEY (mystery_box_id) REFERENCES mystery_boxes (id) ON DELETE RESTRICT,
  CONSTRAINT fk_mystery_box_claims_reward FOREIGN KEY (mystery_box_reward_id) REFERENCES mystery_box_rewards (id) ON DELETE RESTRICT,
  CONSTRAINT fk_mystery_box_claims_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT fk_mystery_box_claims_ledger FOREIGN KEY (ledger_transaction_id) REFERENCES ledger_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT chk_mystery_box_claims_amount CHECK (amount_minor_snapshot > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS mystery_box_claims;
DROP TABLE IF EXISTS mystery_box_rewards;
DROP TABLE IF EXISTS mystery_boxes;