-- =====================================================================
-- TITAN TELEGRAM BOT PLATFORM - PATCH SCRIPT
-- Adds only the NEW tables introduced in the bot.php upgrade
-- (verification, referrals, shop, inventory).
-- Safe to run on an existing database — uses IF NOT EXISTS and
-- ON DUPLICATE KEY UPDATE, so no existing data is touched or lost.
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- REFERRALS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referrals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    referrer_id BIGINT UNSIGNED NOT NULL,
    referred_id BIGINT UNSIGNED NOT NULL,
    reward_coins INT NOT NULL DEFAULT 50,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_referred (referred_id),
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- SHOP ITEMS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shop_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(128) NOT NULL,
    description VARCHAR(255) NULL,
    price INT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- USER INVENTORY
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_inventory (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    acquired_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES shop_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- PENDING VERIFICATIONS (captcha gate for new joiners)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pending_verifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    message_id BIGINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uniq_pending (group_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- SEED DATA — default shop items
-- ---------------------------------------------------------------------
INSERT INTO shop_items (code, name, description, price) VALUES
('badge_vip', '⭐ VIP Badge', 'Shows a VIP star next to your name on leaderboard', 1000),
('name_color', '🎨 Custom Name Color', 'Unlocks colored name tag (cosmetic)', 500),
('xp_boost', '⚡ XP Boost (24h)', 'Doubles XP earned from games for 24 hours', 300)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Apply "3 warnings = ban" to ALL already-existing groups too
UPDATE group_settings SET warn_limit = 3, warn_action = 'ban';