-- 009_player_inventory_and_currency
--
-- What a player owns and what they spent. The catalog side is in 002; this is
-- purely per-player state.
--
-- Currencies are not columns: player_currencies is a key/value table keyed by
-- a string currency_id (gold, rocks, ...), so a new currency needs no schema
-- change. The reward tables still carry gold_amount/rocks_amount separately,
-- which is the older shape those two were written in.

CREATE TABLE IF NOT EXISTS `player_currencies` (
  `player_id` bigint NOT NULL,
  `currency_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`,`currency_id`),
  CONSTRAINT `player_currencies_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stackable items. `count` is the quantity held, 0 meaning owned-but-empty.
CREATE TABLE IF NOT EXISTS `player_inventory` (
  `player_id` bigint NOT NULL,
  `item_id` int NOT NULL,
  `count` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`,`item_id`),
  KEY `player_inventory_ibfk_2` (`item_id`),
  CONSTRAINT `player_inventory_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `player_inventory_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Non-stackable permanent unlocks. ref_id is polymorphic on unlock_type, so
-- there is deliberately no foreign key on it.
CREATE TABLE IF NOT EXISTS `player_unlocks` (
  `player_id` bigint NOT NULL,
  `unlock_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'item',
  `ref_id` int NOT NULL,
  `unlocked_at_unix` bigint NOT NULL,
  PRIMARY KEY (`player_id`,`unlock_type`,`ref_id`),
  KEY `idx_player_unlocks_player` (`player_id`),
  CONSTRAINT `fk_player_unlocks_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The player's built island. Terrain and placed objects are JSON blobs written
-- wholesale by the client; the server does not read inside them.
CREATE TABLE IF NOT EXISTS `player_islands` (
  `player_id` bigint NOT NULL,
  `island_id` int NOT NULL,
  `chunks` json DEFAULT NULL,
  `rocks` json DEFAULT NULL,
  `roads` json DEFAULT NULL,
  `items` json DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`player_id`,`island_id`),
  CONSTRAINT `player_islands_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Audit trail of every reward paid out, and the idempotency guard for it:
-- (source_type, source_id) identifies what was cleared, and is_first_claim
-- records whether it paid full rate or replay rate. items_given is the
-- resolved draw, kept so a payout can be explained after the fact.
CREATE TABLE IF NOT EXISTS `player_reward_claims` (
  `claim_id` bigint NOT NULL AUTO_INCREMENT,
  `player_id` bigint NOT NULL,
  `reward_id` int NOT NULL,
  `source_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `source_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `is_first_claim` tinyint(1) NOT NULL DEFAULT '1',
  `gold_given` int NOT NULL DEFAULT '0',
  `rocks_given` int NOT NULL DEFAULT '0',
  `items_given` json DEFAULT NULL,
  `claimed_at_unix` bigint NOT NULL,
  PRIMARY KEY (`claim_id`),
  KEY `idx_player_source` (`player_id`,`source_type`,`source_id`),
  CONSTRAINT `player_reward_claims_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The player's personal rotating shop, regenerated when expires_at_unix passes
-- using the counts and discount band in shop_config. idx_expiry is what the
-- refresh sweep scans.
CREATE TABLE IF NOT EXISTS `player_shop_offers` (
  `player_id` bigint NOT NULL,
  `item_id` int NOT NULL,
  `discount_pct` tinyint NOT NULL,
  `generated_at_unix` bigint NOT NULL,
  `expires_at_unix` bigint NOT NULL,
  PRIMARY KEY (`player_id`,`item_id`),
  KEY `idx_expiry` (`expires_at_unix`),
  KEY `fk_pso_item` (`item_id`),
  CONSTRAINT `fk_pso_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pso_item` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Purchase ledger. price_item_id is the currency paid in and price_amount the
-- amount, both frozen at purchase time. ON DELETE RESTRICT on item_id: an item
-- someone has bought cannot be deleted out of the catalog.
CREATE TABLE IF NOT EXISTS `player_shop_purchases` (
  `purchase_id` bigint NOT NULL AUTO_INCREMENT,
  `player_id` bigint NOT NULL,
  `item_id` int NOT NULL,
  `price_item_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price_amount` int NOT NULL,
  `purchased_at_unix` bigint NOT NULL,
  PRIMARY KEY (`purchase_id`),
  KEY `idx_player_shop_rotation` (`player_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `player_shop_purchases_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE,
  CONSTRAINT `player_shop_purchases_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `item_catalog` (`item_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
