-- ============================================
-- Database initialization script for Tower Defence Game
-- For phpMyAdmin: 
-- 1. Tạo database mới trong phpMyAdmin (ví dụ: TOWERDEFENCE_LOCAL)
-- 2. Chọn database đó
-- 3. Vào tab SQL và paste/paste file này vào
-- ============================================

-- ============================================
-- USERINFO_NORMAL - Basic user information
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_NORMAL (
    UNIQ_ID VARCHAR(255) PRIMARY KEY COMMENT 'Unique user ID',
    USER_NAME VARCHAR(255) DEFAULT '' COMMENT 'User name',
    IP VARCHAR(50) DEFAULT '' COMMENT 'IP address',
    LEVEL INT DEFAULT 1 COMMENT 'User level',
    GOLD BIGINT DEFAULT 0 COMMENT 'Gold amount',
    RUBY INT DEFAULT 0 COMMENT 'Ruby amount',
    DIA INT DEFAULT 0 COMMENT 'Diamond amount',
    LAST_LOGIN DATETIME COMMENT 'Last login time',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation time',
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update time',
    INDEX idx_user_name (USER_NAME),
    INDEX idx_last_login (LAST_LOGIN)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Basic user information';

-- ============================================
-- USERINFO_HERO - User hero information
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_HERO (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    HERO_DATA TEXT COMMENT 'Hero data (JSON or encrypted)',
    HERO_LEVEL INT DEFAULT 1 COMMENT 'Hero level',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User hero information';

-- ============================================
-- USERINFO_STAGE - User stage progress
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_STAGE (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    STAGE_DATA TEXT COMMENT 'Stage data (JSON or encrypted)',
    CURRENT_STAGE INT DEFAULT 1 COMMENT 'Current stage',
    HIGHEST_STAGE INT DEFAULT 1 COMMENT 'Highest stage cleared',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User stage progress';

-- ============================================
-- USERINFO_TOWER - User tower information
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_TOWER (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    TOWER_DATA TEXT COMMENT 'Tower data (JSON or encrypted)',
    TOWER_LEVEL INT DEFAULT 1 COMMENT 'Tower level',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User tower information';

-- ============================================
-- USERINFO_ITEM - User item inventory
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_ITEM (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    ITEM_DATA TEXT COMMENT 'Item data (JSON or encrypted)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User item inventory';

-- ============================================
-- USERINFO_CHARBOOK - User character book
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_CHARBOOK (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    CHARBOOK_DATA TEXT COMMENT 'Character book data (JSON or encrypted)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User character book';

-- ============================================
-- USERINFO_MISSION - User mission progress
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_MISSION (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    MISSION_DATA TEXT COMMENT 'Mission data (JSON or encrypted)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User mission progress';

-- ============================================
-- USERINFO_QUEST - User quest progress
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_QUEST (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    QUEST_DATA TEXT COMMENT 'Quest data (JSON or encrypted)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User quest progress';

-- ============================================
-- USERINFO_GUILD - User guild information
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_GUILD (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    GUILD_DATA TEXT COMMENT 'Guild data (JSON or encrypted)',
    GUILD_ID INT DEFAULT 0 COMMENT 'Guild ID',
    GUILD_ROLE VARCHAR(50) DEFAULT '' COMMENT 'Guild role',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE,
    INDEX idx_guild_id (GUILD_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User guild information';

-- ============================================
-- USERINFO_PVP - User PVP data
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_PVP (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    PVP_DATA TEXT COMMENT 'PVP data (JSON or encrypted)',
    PVP_SCORE INT DEFAULT 0 COMMENT 'PVP score',
    PVP_RANK INT DEFAULT 0 COMMENT 'PVP rank',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE,
    INDEX idx_pvp_score (PVP_SCORE),
    INDEX idx_pvp_rank (PVP_RANK)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User PVP data';

-- ============================================
-- USERINFO_MYTHS - User myths data
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_MYTHS (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    MYTHS_DATA TEXT COMMENT 'Myths data (JSON or encrypted)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User myths data';

-- ============================================
-- USERINFO_RUBYDIAGOLD - User currency data
-- ============================================
CREATE TABLE IF NOT EXISTS USERINFO_RUBYDIAGOLD (
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    RUBY INT DEFAULT 0 COMMENT 'Ruby amount',
    DIA INT DEFAULT 0 COMMENT 'Diamond amount',
    GOLD BIGINT DEFAULT 0 COMMENT 'Gold amount',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (UNIQ_ID),
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User currency data';

-- ============================================
-- REWARD_COMPLETED - Completed rewards
-- ============================================
CREATE TABLE IF NOT EXISTS REWARD_COMPLETED (
    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    REWARD_TYPE VARCHAR(50) COMMENT 'Reward type',
    REWARD_DATA TEXT COMMENT 'Reward data (JSON)',
    COMPLETED_AT DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Completion time',
    FOREIGN KEY (UNIQ_ID) REFERENCES USERINFO_NORMAL(UNIQ_ID) ON DELETE CASCADE,
    INDEX idx_uniq_id (UNIQ_ID),
    INDEX idx_reward_type (REWARD_TYPE),
    INDEX idx_completed_at (COMPLETED_AT)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Completed rewards';

-- ============================================
-- GUILD_INFO - Guild information
-- ============================================
CREATE TABLE IF NOT EXISTS GUILD_INFO (
    GUILD_ID INT AUTO_INCREMENT PRIMARY KEY,
    GUILD_NAME VARCHAR(255) NOT NULL UNIQUE COMMENT 'Guild name',
    GUILD_LEADER_ID VARCHAR(255) NOT NULL COMMENT 'Guild leader UNIQ_ID',
    GUILD_LEVEL INT DEFAULT 1 COMMENT 'Guild level',
    GUILD_SCORE INT DEFAULT 0 COMMENT 'Guild score',
    MEMBER_COUNT INT DEFAULT 0 COMMENT 'Member count',
    MAX_MEMBERS INT DEFAULT 30 COMMENT 'Maximum members',
    GUILD_DATA TEXT COMMENT 'Additional guild data (JSON)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_guild_name (GUILD_NAME),
    INDEX idx_guild_score (GUILD_SCORE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Guild information';

-- ============================================
-- PVP_HISTORY - PVP match history
-- ============================================
CREATE TABLE IF NOT EXISTS PVP_HISTORY (
    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    UNIQ_ID VARCHAR(255) NOT NULL COMMENT 'User ID',
    OPPONENT_ID VARCHAR(255) COMMENT 'Opponent ID',
    RESULT VARCHAR(20) COMMENT 'WIN/LOSE/DRAW',
    SCORE_CHANGE INT DEFAULT 0 COMMENT 'Score change',
    MATCH_DATA TEXT COMMENT 'Match data (JSON)',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_uniq_id (UNIQ_ID),
    INDEX idx_created_at (CREATED_AT)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='PVP match history';

-- ============================================
-- BLACKLIST - Blacklisted users/IPs
-- ============================================
CREATE TABLE IF NOT EXISTS BLACKLIST (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    UNIQ_ID VARCHAR(255) COMMENT 'User ID (optional)',
    IP VARCHAR(50) COMMENT 'IP address (optional)',
    REASON TEXT COMMENT 'Blacklist reason',
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_uniq_id (UNIQ_ID),
    INDEX idx_ip (IP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Blacklisted users/IPs';

-- ============================================
-- End of database initialization
-- ============================================

