-- ========================================================
-- PHASE 1: Create Global Schema for Match Isolation
-- This creates the missing global database that the implementation requires
-- ========================================================

-- Create global schema for match-isolated data
CREATE DATABASE IF NOT EXISTS `global` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
USE `global`;

-- Chat system with match_id
CREATE TABLE IF NOT EXISTS `chat` (
  `match_id` BIGINT NOT NULL,
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `chan` tinyint(3) unsigned DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `game` varchar(3) DEFAULT NULL,
  `server` tinyint(3) unsigned DEFAULT 0,
  `post` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_match_id` (`match_id`),
  INDEX `server` (`server`),
  INDEX `chan` (`chan`),
  INDEX `game` (`game`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Colonies table (col) - the one with data leakage
CREATE TABLE IF NOT EXISTS `col` (
  `match_id` BIGINT NOT NULL,
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `goodid` int(11) DEFAULT 1,
  `datetime` datetime DEFAULT NULL,
  `class` tinyint(3) unsigned DEFAULT 0,
  `pop` int(11) DEFAULT 1,
  `loyalty` smallint(6) DEFAULT 50,
  `land` int(11) DEFAULT 0,
  `ore` int(11) DEFAULT 0,
  `food` int(11) DEFAULT 0,
  `good` int(11) DEFAULT 0,
  `housing` int(11) DEFAULT 1,
  `commercial` int(11) DEFAULT 0,
  `industry` int(11) DEFAULT 0,
  `agriculture` int(11) DEFAULT 0,
  `mining` int(11) DEFAULT 0,
  `type` tinyint(3) unsigned DEFAULT 8,
  `planet` smallint(3) unsigned DEFAULT 1,
  PRIMARY KEY (`id`),
  INDEX `idx_match_id` (`match_id`),
  INDEX `userid` (`userid`),
  INDEX `class` (`class`),
  INDEX `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Users table for match isolation
CREATE TABLE IF NOT EXISTS `user` (
  `match_id` BIGINT NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nic` varchar(50) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `race` tinyint(3) unsigned DEFAULT 1,
  `annex` bigint(20) DEFAULT NULL,
  `annexhelp` tinyint(1) DEFAULT 1,
  `annexcount` smallint(6) DEFAULT NULL,
  `fedid` int(11) DEFAULT NULL,
  `fedvote` int(11) DEFAULT NULL,
  `activeflag` tinyint(1) DEFAULT 1,
  `confirmflag` tinyint(1) DEFAULT 0,
  `adminflag` tinyint(3) unsigned DEFAULT 0,
  `trade` bigint(20) DEFAULT 0,
  `credit` bigint(20) DEFAULT 100,
  `ore` int(11) DEFAULT 10,
  `food` bigint(20) DEFAULT 50,
  `rawmaterial` bigint(20) DEFAULT 10,
  `good` bigint(20) DEFAULT 10,
  `tick` datetime DEFAULT NULL,
  `lastaccess` datetime DEFAULT NULL,
  `lastcookie` bigint(20) DEFAULT NULL,
  `lastcookie2` bigint(20) DEFAULT NULL,
  `lastip` bigint(20) DEFAULT NULL,
  `protection` datetime DEFAULT NULL,
  `protection2` datetime DEFAULT NULL,
  `protectionstart` datetime DEFAULT NULL,
  `power` double DEFAULT 0,
  `explore` bigint(20) DEFAULT 0,
  `resid` tinyint(3) unsigned DEFAULT 0,
  `resstart` int(7) DEFAULT 2,
  `resreq` int(7) DEFAULT 2,
  `reslevel` int(11) DEFAULT NULL,
  `restreeturn` smallint(6) DEFAULT NULL,
  `restreeid` bigint(3) unsigned DEFAULT NULL,
  `r_research` varchar(80) DEFAULT NULL,
  `r_researched` varchar(170) DEFAULT NULL,
  `r_ship` text DEFAULT NULL,
  `colonycount` tinyint(3) unsigned DEFAULT 1,
  `planetcount` smallint(6) DEFAULT 0,
  `planetlost` tinyint(3) DEFAULT 0,
  `server` tinyint(3) unsigned DEFAULT NULL,
  `s_battlewin` smallint(6) DEFAULT 0,
  `s_battlelose` smallint(6) DEFAULT 0,
  `s_planetwin` int(11) DEFAULT 0,
  `s_planetlose` int(11) DEFAULT 0,
  `s_planetfind` int(11) DEFAULT 0,
  `s_planetdes` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_match_id` (`match_id`),
  INDEX `activeflag` (`activeflag`),
  INDEX `nic` (`nic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- User goods table
CREATE TABLE IF NOT EXISTS `user_good` (
  `match_id` BIGINT NOT NULL,
  `id` int(11) NOT NULL,
  `g1` bigint(20) DEFAULT 0,
  `g2` bigint(20) DEFAULT 0,
  `g3` bigint(20) DEFAULT 0,
  `g4` bigint(20) DEFAULT 0,
  `g5` bigint(20) DEFAULT 0,
  `g6` bigint(20) DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_match_id` (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Events table
CREATE TABLE IF NOT EXISTS `event` (
  `match_id` BIGINT NOT NULL,
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `type` tinyint(3) unsigned DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `post` varchar(250) DEFAULT NULL,
  `server` tinyint(3) unsigned DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_match_id` (`match_id`),
  INDEX `userid` (`userid`),
  INDEX `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- Attack events table
CREATE TABLE IF NOT EXISTS `event_attack` (
  `match_id` BIGINT NOT NULL,
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `source` int(11) DEFAULT NULL,
  `target` int(11) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `winflag` tinyint(1) DEFAULT NULL,
  `attackpower` bigint(20) DEFAULT NULL,
  `defensepower` bigint(20) DEFAULT NULL,
  `server` tinyint(3) unsigned DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_match_id` (`match_id`),
  INDEX `source` (`source`),
  INDEX `target` (`target`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

-- ========================================================
-- CREATE TRIGGERS FOR AUTO-POPULATION
-- ========================================================

DELIMITER //

-- Trigger for chat table
CREATE TRIGGER global_chat_bi
BEFORE INSERT ON `global`.`chat`
FOR EACH ROW
BEGIN
  IF NEW.match_id IS NULL THEN
    SET NEW.match_id = @match_id;
  END IF;
END//

-- Trigger for col table (fixes data leakage)
CREATE TRIGGER global_col_bi
BEFORE INSERT ON `global`.`col`
FOR EACH ROW
BEGIN
  IF NEW.match_id IS NULL THEN
    SET NEW.match_id = @match_id;
  END IF;
END//

-- Trigger for user table
CREATE TRIGGER global_user_bi
BEFORE INSERT ON `global`.`user`
FOR EACH ROW
BEGIN
  IF NEW.match_id IS NULL THEN
    SET NEW.match_id = @match_id;
  END IF;
END//

-- Trigger for user_good table
CREATE TRIGGER global_user_good_bi
BEFORE INSERT ON `global`.`user_good`
FOR EACH ROW
BEGIN
  IF NEW.match_id IS NULL THEN
    SET NEW.match_id = @match_id;
  END IF;
END//

-- Trigger for event table
CREATE TRIGGER global_event_bi
BEFORE INSERT ON `global`.`event`
FOR EACH ROW
BEGIN
  IF NEW.match_id IS NULL THEN
    SET NEW.match_id = @match_id;
  END IF;
END//

-- Trigger for event_attack table
CREATE TRIGGER global_event_attack_bi
BEFORE INSERT ON `global`.`event_attack`
FOR EACH ROW
BEGIN
  IF NEW.match_id IS NULL THEN
    SET NEW.match_id = @match_id;
  END IF;
END//

-- Protection triggers to prevent match_id changes
CREATE TRIGGER global_user_bu
BEFORE UPDATE ON `global`.`user`
FOR EACH ROW
BEGIN
  IF NEW.match_id <> OLD.match_id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='match_id is immutable';
  END IF;
END//

CREATE TRIGGER global_col_bu
BEFORE UPDATE ON `global`.`col`
FOR EACH ROW
BEGIN
  IF NEW.match_id <> OLD.match_id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='match_id is immutable';
  END IF;
END//

DELIMITER ;

-- ========================================================
-- MIGRATE EXISTING DATA FROM GCDM TO GLOBAL (for testing)
-- ========================================================

-- Copy current tournament data to global with match_id = 0 (tournament mode)
INSERT IGNORE INTO `global`.`user` 
SELECT 0 as match_id, `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 gcdm.user;

INSERT IGNORE INTO `global`.`col` 
SELECT 0 as match_id, `id`, `userid`, `goodid`, `datetime`, `class`, `pop`, `loyalty`, `land`, `ore`, 
       `food`, `good`, `housing`, `commercial`, `industry`, `agriculture`, `mining`, `type`, `planet`
FROM gcdm.col;

INSERT IGNORE INTO `global`.`chat` 
SELECT 0 as match_id, `id`, `chan`, `name`, `game`, `server`, `post`
FROM gcdm.chat;

-- ========================================================
-- VERIFICATION QUERIES
-- ========================================================

SELECT 'Global schema created successfully' as status;
SELECT COUNT(*) as user_count, match_id FROM `global`.`user` GROUP BY match_id;
SELECT COUNT(*) as col_count, match_id FROM `global`.`col` GROUP BY match_id;
SELECT COUNT(*) as chat_count, match_id FROM `global`.`chat` GROUP BY match_id;