-- ============================================================
-- ErrorHex v10 — Migration SQL
-- Run this SQL to add new features to existing database
-- ============================================================

-- 1. Boss Fight Logs (tracks daily boss fight count per user per boss)
CREATE TABLE IF NOT EXISTS `boss_fight_logs` (
    `id`          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `user_id`     BIGINT          NOT NULL,
    `boss_key`    VARCHAR(32)     NOT NULL,
    `fight_date`  DATE            NOT NULL,
    `fight_count` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_user_boss_date` (`user_id`, `boss_key`, `fight_date`),
    KEY `idx_user_date` (`user_id`, `fight_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Daily Reward Logs (tracks /daily claims)
CREATE TABLE IF NOT EXISTS `daily_reward_logs` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`    BIGINT       NOT NULL,
    `claim_date` DATE         NOT NULL,
    `claimed_at` DATETIME     NOT NULL,
    `amount`     INT UNSIGNED NOT NULL DEFAULT 15,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_user_date` (`user_id`, `claim_date`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Add bot_active column to group_stats (if not exists)
-- Run this if you're upgrading from a previous version:
ALTER TABLE `group_stats`
    ADD COLUMN IF NOT EXISTS `bot_active` TINYINT(1) NOT NULL DEFAULT 1
    COMMENT '0 = bot was kicked/blocked from this group';

-- Mark existing groups as active by default
UPDATE `group_stats` SET `bot_active` = 1 WHERE `bot_active` IS NULL;

-- v11 additions: spawn_meta for anti-spam, shop pricing
CREATE TABLE IF NOT EXISTS `spawn_meta` (
    `meta_key`   VARCHAR(200) NOT NULL,
    `meta_value` TEXT NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional cleanup job: remove old spam entries (run via cron or at startup)
-- DELETE FROM spawn_meta WHERE meta_key LIKE 'spam_%' AND updated_at < NOW() - INTERVAL 30 MINUTE;