-- =====================================================================
-- TITAN TELEGRAM BOT PLATFORM - DATABASE SCHEMA
-- Engine: InnoDB | Charset: utf8mb4
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- 1. USERS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
    username VARCHAR(64) NULL,
    first_name VARCHAR(128) NULL,
    last_name VARCHAR(128) NULL,
    coins BIGINT NOT NULL DEFAULT 100,
    xp BIGINT NOT NULL DEFAULT 0,
    level INT NOT NULL DEFAULT 1,
    wins INT NOT NULL DEFAULT 0,
    losses INT NOT NULL DEFAULT 0,
    draws INT NOT NULL DEFAULT 0,
    is_banned TINYINT(1) NOT NULL DEFAULT 0,
    ban_reason VARCHAR(255) NULL,
    last_active DATETIME NULL,
    join_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_xp (xp),
    INDEX idx_coins (coins)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 2. ADMINS (bot super admins / panel operators)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNSIGNED NULL,
    username VARCHAR(64) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin','moderator','viewer') NOT NULL DEFAULT 'moderator',
    added_by INT UNSIGNED NULL,
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 3. GROUPS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS groups_master (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL UNIQUE,
    title VARCHAR(255) NULL,
    owner_telegram_id BIGINT UNSIGNED NULL,
    member_count INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    added_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    removed_date DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 4. GROUP MEMBERS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS group_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role ENUM('member','admin','owner') NOT NULL DEFAULT 'member',
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_group_user (group_id, user_id),
    FOREIGN KEY (group_id) REFERENCES groups_master(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 5. GROUP SETTINGS (per-group configuration)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS group_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT UNSIGNED NOT NULL UNIQUE,
    games_enabled TINYINT(1) NOT NULL DEFAULT 1,
    spam_filter TINYINT(1) NOT NULL DEFAULT 1,
    badword_filter TINYINT(1) NOT NULL DEFAULT 1,
    link_filter TINYINT(1) NOT NULL DEFAULT 0,
    flood_filter TINYINT(1) NOT NULL DEFAULT 1,
    flood_limit INT NOT NULL DEFAULT 5,
    flood_seconds INT NOT NULL DEFAULT 8,
    verification_required TINYINT(1) NOT NULL DEFAULT 0,
    welcome_message TEXT NULL,
    warn_limit INT NOT NULL DEFAULT 3,
    warn_action ENUM('mute','kick','ban') NOT NULL DEFAULT 'ban',
    FOREIGN KEY (group_id) REFERENCES groups_master(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 6. WARNINGS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS warnings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    group_id BIGINT UNSIGNED NOT NULL,
    reason VARCHAR(255) NULL,
    given_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES groups_master(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 7. MUTES
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mutes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    group_id BIGINT UNSIGNED NOT NULL,
    reason VARCHAR(255) NULL,
    muted_by BIGINT UNSIGNED NULL,
    muted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES groups_master(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 8. BANS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    group_id BIGINT UNSIGNED NULL, -- NULL = global ban
    reason VARCHAR(255) NULL,
    banned_by BIGINT UNSIGNED NULL,
    banned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 9. GAMES CATALOG
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS games_catalog (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(32) NOT NULL UNIQUE, -- e.g. 'ttt', 'chess', 'uno', 'ludo'
    name VARCHAR(64) NOT NULL,
    description VARCHAR(255) NULL,
    min_players TINYINT NOT NULL DEFAULT 2,
    max_players TINYINT NOT NULL DEFAULT 2,
    coin_reward INT NOT NULL DEFAULT 20,
    xp_reward INT NOT NULL DEFAULT 15,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 10. GAME SESSIONS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS game_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_id INT UNSIGNED NOT NULL,
    group_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','active','finished','cancelled','timeout') NOT NULL DEFAULT 'pending',
    player1_id BIGINT UNSIGNED NOT NULL,
    player2_id BIGINT UNSIGNED NULL,
    current_turn BIGINT UNSIGNED NULL,
    board_state TEXT NULL, -- JSON serialized board
    winner_id BIGINT UNSIGNED NULL,
    is_draw TINYINT(1) NOT NULL DEFAULT 0,
    challenge_message_id BIGINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME NULL,
    ended_at DATETIME NULL,
    FOREIGN KEY (game_id) REFERENCES games_catalog(id),
    FOREIGN KEY (group_id) REFERENCES groups_master(id) ON DELETE CASCADE,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 11. GAME MOVES
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS game_moves (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    move_data VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES game_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 12. COIN TRANSACTIONS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS coin_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount BIGINT NOT NULL, -- positive = credit, negative = debit
    type ENUM('game_win','daily','weekly','tournament','admin_grant','shop_purchase','penalty') NOT NULL,
    reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 13. XP LOG
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS xp_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount INT NOT NULL,
    reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 14. ACHIEVEMENTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS achievements (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(128) NOT NULL,
    description VARCHAR(255) NULL,
    reward_coins INT NOT NULL DEFAULT 0,
    reward_xp INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_achievements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    achievement_id INT UNSIGNED NOT NULL,
    earned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_ach (user_id, achievement_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 15. LOGS (system-wide activity log)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(32) NOT NULL, -- join, leave, mute, ban, delete, game, admin_action, error
    group_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(64) NULL,
    details TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 16. BROADCASTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS broadcasts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    sent_by INT UNSIGNED NULL,
    total_groups INT NOT NULL DEFAULT 0,
    delivered INT NOT NULL DEFAULT 0,
    failed INT NOT NULL DEFAULT 0,
    status ENUM('pending','sending','completed','failed') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 17. SETTINGS (global key-value config)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(64) NOT NULL UNIQUE,
    setting_value TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 18. REPORTS (user-submitted reports)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reporter_id BIGINT UNSIGNED NOT NULL,
    reported_user_id BIGINT UNSIGNED NOT NULL,
    group_id BIGINT UNSIGNED NULL,
    reason VARCHAR(255) NULL,
    status ENUM('open','reviewed','dismissed') NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reported_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 19. TOURNAMENTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tournaments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    game_id INT UNSIGNED NOT NULL,
    status ENUM('registration','ongoing','completed','cancelled') NOT NULL DEFAULT 'registration',
    max_players INT NOT NULL DEFAULT 8,
    reward_coins INT NOT NULL DEFAULT 500,
    reward_xp INT NOT NULL DEFAULT 200,
    created_by INT UNSIGNED NULL,
    start_date DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES games_catalog(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tournament_players (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id INT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    eliminated TINYINT(1) NOT NULL DEFAULT 0,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tourn_user (tournament_id, user_id),
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 20. FLOOD TRACKER (for spam/flood detection, short-lived rows)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS flood_tracker (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    msg_count INT NOT NULL DEFAULT 1,
    window_start DATETIME NOT NULL,
    UNIQUE KEY uniq_flood (group_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 21. 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;

-- ---------------------------------------------------------------------
-- 22. 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;

-- ---------------------------------------------------------------------
-- 23. 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;

-- ---------------------------------------------------------------------
-- 24. 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
-- ---------------------------------------------------------------------
INSERT INTO games_catalog (code, name, description, min_players, max_players, coin_reward, xp_reward) VALUES
('ttt', 'Tic Tac Toe', 'Classic 3x3 grid game', 2, 2, 20, 15),
('chess', 'Chess', 'Classic chess (coming soon)', 2, 2, 50, 40),
('uno', 'UNO', 'Card game (coming soon)', 2, 4, 30, 25),
('ludo', 'Ludo', 'Board game (coming soon)', 2, 4, 30, 25)
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO settings (setting_key, setting_value) VALUES
('bot_name', 'Titan Bot'),
('maintenance_mode', '0'),
('default_flood_limit', '5'),
('default_flood_seconds', '8')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

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);

INSERT INTO achievements (code, name, description, reward_coins, reward_xp) VALUES
('first_win', 'First Blood', 'Win your first game', 50, 30),
('ten_wins', 'Rising Star', 'Win 10 games', 200, 100),
('hundred_wins', 'Legend', 'Win 100 games', 2000, 800)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Default super admin login: username=admin / password=ChangeMe123!
-- (hash below is bcrypt for 'ChangeMe123!' - CHANGE THIS IMMEDIATELY AFTER FIRST LOGIN)
INSERT INTO admins (username, password_hash, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin')
ON DUPLICATE KEY UPDATE username=VALUES(username);

SET FOREIGN_KEY_CHECKS = 1;