-- 002_items_and_economy
--
-- The item catalog and everything priced against it: reward definitions, the
-- shop, and the unlock catalog. Player-owned rows live in 009.

-- item_id is NOT auto-increment: ids come from the Unity catalog sync, so the
-- game client and the database agree on them.
CREATE TABLE IF NOT EXISTS `item_catalog` (
  `item_id` int NOT NULL,
  `category_id` int DEFAULT NULL,
  `theme_id` int DEFAULT NULL,
  `rarity` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `base_price` int NOT NULL DEFAULT '0',
  `icon_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `item_id` (`item_id`),
  KEY `rarity` (`rarity`),
  KEY `item_catalog_ibfk_2` (`category_id`),
  KEY `item_catalog_ibfk_3` (`theme_id`),
  CONSTRAINT `item_catalog_ibfk_1` FOREIGN KEY (`rarity`) REFERENCES `rarities` (`code`),
  CONSTRAINT `item_catalog_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `item_catalog_ibfk_3` FOREIGN KEY (`theme_id`) REFERENCES `themes` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `item_catalog_translations` (
  `item_id` int NOT NULL,
  `language_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `display_name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`item_id`,`language_code`),
  CONSTRAINT `item_catalog_translations_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A reward payload. The replay_*_pct columns scale it down on a replay, so a
-- second run through a set is worth something but not the full first-clear.
-- use_full_catalog = 1 draws from every obtainable item instead of the pool.
CREATE TABLE IF NOT EXISTS `rewards` (
  `reward_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gold_amount` int NOT NULL DEFAULT '0',
  `rocks_amount` int NOT NULL DEFAULT '0',
  `replay_gold_pct` int NOT NULL DEFAULT '50',
  `replay_rocks_pct` int NOT NULL DEFAULT '0',
  `use_full_catalog` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`reward_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reward_pool_items` (
  `reward_id` int NOT NULL,
  `item_id` int NOT NULL,
  PRIMARY KEY (`reward_id`,`item_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `reward_pool_items_ibfk_1` FOREIGN KEY (`reward_id`) REFERENCES `rewards` (`reward_id`) ON DELETE CASCADE,
  CONSTRAINT `reward_pool_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Items bought outright with rocks rather than stocked in the rotating shop.
CREATE TABLE IF NOT EXISTS `unlock_catalog` (
  `item_id` int NOT NULL,
  `tier_index` int NOT NULL DEFAULT '0',
  `price` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  KEY `idx_unlock_tier` (`tier_index`),
  CONSTRAINT `fk_unlock_catalog_item` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shop tiers open as the player clears sets; tier_index 0 is available at once.
CREATE TABLE IF NOT EXISTS `shop_tiers` (
  `tier_index` int NOT NULL,
  `unlock_after_sets` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`tier_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Single-row table (id is pinned to 1) holding the shop rotation parameters.
CREATE TABLE IF NOT EXISTS `shop_config` (
  `id` tinyint NOT NULL DEFAULT '1',
  `random_offer_count` tinyint NOT NULL DEFAULT '3',
  `random_offer_refresh_seconds` int NOT NULL DEFAULT '86400',
  `random_offer_discount_min` tinyint NOT NULL DEFAULT '10',
  `random_offer_discount_max` tinyint NOT NULL DEFAULT '30',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_items` (
  `item_id` int NOT NULL,
  `tier_index` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  KEY `idx_tier` (`tier_index`),
  CONSTRAINT `fk_shop_items_item` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Global, admin-scheduled discounts. Per-player random offers live in 009.
CREATE TABLE IF NOT EXISTS `shop_offers` (
  `offer_id` bigint NOT NULL AUTO_INCREMENT,
  `item_id` int NOT NULL,
  `discount_pct` tinyint NOT NULL,
  `starts_at_unix` bigint NOT NULL,
  `ends_at_unix` bigint DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`offer_id`),
  KEY `idx_active_window` (`is_active`,`starts_at_unix`,`ends_at_unix`),
  KEY `idx_item` (`item_id`),
  CONSTRAINT `fk_shop_offers_item` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
