-- =====================
-- MercyV Coinshop Database Schema
-- =====================

CREATE TABLE IF NOT EXISTS `mercy_coinshop_coins` (
    `identifier` VARCHAR(60) NOT NULL PRIMARY KEY,
    `coins` INT NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `mercy_coinshop_transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `identifier` VARCHAR(60) NOT NULL,
    `player_name` VARCHAR(100) NOT NULL,
    `type` VARCHAR(30) NOT NULL,
    `item_name` VARCHAR(100) NOT NULL,
    `price` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_identifier` (`identifier`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `mercy_coinshop_stock` (
    `item_model` VARCHAR(60) NOT NULL PRIMARY KEY,
    `sold_count` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `mercy_coinshop_redeemed` (
    `code` VARCHAR(100) NOT NULL PRIMARY KEY,
    `identifier` VARCHAR(60) NOT NULL,
    `redeemed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;