-- ========================================================
-- CREATE MATCH SCHEMA VIEWS 
-- This script creates the m_<matchId> schemas with views
-- Call this for each new match created
-- Example: CALL CreateMatchSchema(14);
-- ========================================================

DELIMITER //

CREATE PROCEDURE CreateMatchSchema(IN matchId BIGINT)
BEGIN
    DECLARE schemaName VARCHAR(64);
    DECLARE sql_stmt TEXT;
    
    -- Set schema name
    SET schemaName = CONCAT('m_', matchId);
    
    -- Create the schema
    SET sql_stmt = CONCAT('CREATE DATABASE IF NOT EXISTS `', schemaName, '` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci');
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Switch to the new schema
    SET sql_stmt = CONCAT('USE `', schemaName, '`');
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Create chat view
    SET sql_stmt = CONCAT('CREATE OR REPLACE VIEW `', schemaName, '`.`chat` AS SELECT id, chan, name, game, server, post FROM `global`.`chat` WHERE match_id = ', matchId);
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Create col view (this fixes the data leakage!)
    SET sql_stmt = CONCAT('CREATE OR REPLACE VIEW `', schemaName, '`.`col` AS SELECT id, userid, goodid, datetime, class, pop, loyalty, land, ore, food, good, housing, commercial, industry, agriculture, mining, type, planet FROM `global`.`col` WHERE match_id = ', matchId);
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Create user view
    SET sql_stmt = CONCAT('CREATE OR REPLACE VIEW `', schemaName, '`.`user` AS SELECT id, nic, password, name, email, race, annex, annexhelp, annexcount, fedid, fedvote, activeflag, confirmflag, adminflag, trade, credit, ore, food, rawmaterial, good, tick, lastaccess, lastcookie, lastcookie2, lastip, protection, protection2, protectionstart, power, explore, resid, resstart, resreq, reslevel, restreeturn, restreeid, r_research, r_researched, r_ship, colonycount, planetcount, planetlost, server, s_battlewin, s_battlelose, s_planetwin, s_planetlose, s_planetfind, s_planetdes FROM `global`.`user` WHERE match_id = ', matchId);
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Create user_good view
    SET sql_stmt = CONCAT('CREATE OR REPLACE VIEW `', schemaName, '`.`user_good` AS SELECT id, g1, g2, g3, g4, g5, g6 FROM `global`.`user_good` WHERE match_id = ', matchId);
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Create event view
    SET sql_stmt = CONCAT('CREATE OR REPLACE VIEW `', schemaName, '`.`event` AS SELECT id, userid, type, datetime, post, server FROM `global`.`event` WHERE match_id = ', matchId);
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Create event_attack view  
    SET sql_stmt = CONCAT('CREATE OR REPLACE VIEW `', schemaName, '`.`event_attack` AS SELECT id, source, target, datetime, winflag, attackpower, defensepower, server FROM `global`.`event_attack` WHERE match_id = ', matchId);
    SET @sql = sql_stmt;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- Add other tables as needed:
    -- fed, fed_apply, fed_forum, fed_Log, fed_planetcount, good, project_user, ship_user, trade_good_user, user_pm, user_win
    
END//

DELIMITER ;

-- ========================================================
-- TEST THE PROCEDURE
-- ========================================================

-- Example usage:
-- CALL CreateMatchSchema(14);
-- SHOW DATABASES LIKE 'm_%';
-- SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'm_14';