CREATE TABLE IF NOT EXISTS `uni1_forum_categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) unsigned DEFAULT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`description` text NOT NULL,
`icon` varchar(20) NOT NULL DEFAULT '📁',
`color` varchar(20) NOT NULL DEFAULT '#38bdf8',
`sort_order` int(11) unsigned NOT NULL DEFAULT 0,
`is_locked` tinyint(1) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_topics` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`views` int(11) unsigned NOT NULL DEFAULT 0,
`is_sticky` tinyint(1) unsigned NOT NULL DEFAULT 0,
`is_locked` tinyint(1) unsigned NOT NULL DEFAULT 0,
`is_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
`last_post_time` int(11) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
`updated_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`),
KEY `user_id` (`user_id`),
KEY `last_post_time` (`last_post_time`),
KEY `is_sticky` (`is_sticky`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_posts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`content` text NOT NULL,
`like_count` int(11) unsigned NOT NULL DEFAULT 0,
`is_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
`updated_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `topic_id` (`topic_id`),
KEY `user_id` (`user_id`),
KEY `is_deleted` (`is_deleted`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_post_likes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `post_user` (`post_id`, `user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_mentions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`is_read` tinyint(1) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `uni1_system` SET `dbVersion` = 5;

-- Migration 17: Convert news table to utf8mb4 to support emoji and full Unicode in title/text

ALTER TABLE `uni1_news`
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `uni1_news`
MODIFY `title` varchar(255) NOT NULL,
MODIFY `text` mediumtext NOT NULL;

UPDATE `uni1_system` SET `dbVersion` = 17;

-- Migration 8: Convert alliance table to utf8mb4 to support full Unicode (emojis, etc.)

ALTER TABLE `uni1_alliance`
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Ensure text columns are large enough and use utf8mb4
ALTER TABLE `uni1_alliance`
MODIFY `ally_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
MODIFY `ally_tag` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
MODIFY `ally_description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY `ally_text` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY `ally_request` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY `ally_web` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
MODIFY `ally_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
MODIFY `ally_owner_range` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
MODIFY `ally_events` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';

UPDATE `uni1_system` SET `dbVersion` = 8;

ALTER TABLE uni1_config ADD `storage_multiplier` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1' AFTER `resource_multiplier`;

-- Migration 10: SmartChat – global/alliance chat system
-- CHAT_MES = {prefix}chat_messages, CHAT_BAN = {prefix}chat_bans (see dbtables.php)

CREATE TABLE IF NOT EXISTS `uni1_chat_messages` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`channel` ENUM('global','alliance') NOT NULL DEFAULT 'global',
`alliance_id` INT UNSIGNED NOT NULL DEFAULT 0,
`user_id` INT UNSIGNED NOT NULL,
`username` VARCHAR(64) NOT NULL,
`message` TEXT NOT NULL,
`created_at` INT UNSIGNED NOT NULL,
`deleted_at` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_channel_alliance` (`channel`, `alliance_id`, `deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `user_id` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `alliance_id`;
ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `username` VARCHAR(64) NOT NULL DEFAULT '' AFTER `user_id`;
ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `message` TEXT NOT NULL AFTER `username`;
ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `created_at` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `message`;
ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `deleted_at` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `created_at`;
ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `alliance_id` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `channel`;
ALTER TABLE `uni1_chat_messages` ADD COLUMN IF NOT EXISTS `channel` ENUM('global','alliance') NOT NULL DEFAULT 'global' AFTER `id`;

CREATE TABLE IF NOT EXISTS `uni1_chat_bans` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED NOT NULL,
`banned_by` INT UNSIGNED NOT NULL,
`reason` VARCHAR(255) NOT NULL DEFAULT '',
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `uni1_system` SET `dbVersion` = 10;

-- Migration 15: Add combat engine tuning columns + bot_personalities table

ALTER TABLE `uni1_config` ADD COLUMN `combat_rand_variance` tinyint(3) unsigned NOT NULL DEFAULT 20;
ALTER TABLE `uni1_config` ADD COLUMN `combat_crit_chance` tinyint(3) unsigned NOT NULL DEFAULT 5;
ALTER TABLE `uni1_config` ADD COLUMN `combat_crit_mult` float NOT NULL DEFAULT 2.0;
ALTER TABLE `uni1_config` ADD COLUMN `combat_morale_enabled` tinyint(1) unsigned NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS `uni1_bot_personalities` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT 'balanced',
`allow_raids` tinyint(1) unsigned NOT NULL DEFAULT 0,
`allow_expeditions` tinyint(1) unsigned NOT NULL DEFAULT 1,
`aggression` float NOT NULL DEFAULT 0.3,
`priority_mines` float NOT NULL DEFAULT 0.7,
`priority_energy` float NOT NULL DEFAULT 0.5,
`priority_storage` float NOT NULL DEFAULT 0.3,
`priority_research` float NOT NULL DEFAULT 0.4,
`priority_fleet` float NOT NULL DEFAULT 0.3,
`priority_defense` float NOT NULL DEFAULT 0.2,
`save_resources` tinyint(1) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `uni1_bot_personalities` (`name`, `allow_raids`, `allow_expeditions`, `aggression`, `priority_mines`, `priority_energy`, `priority_storage`, `priority_research`, `priority_fleet`, `priority_defense`, `save_resources`) VALUES
('balanced', 0, 1, 0.3, 0.7, 0.5, 0.3, 0.4, 0.3, 0.2, 0),
('farmer', 0, 1, 0.2, 0.9, 0.6, 0.4, 0.3, 0.2, 0.1, 0),
('raider', 1, 0, 0.8, 0.5, 0.4, 0.2, 0.3, 0.8, 0.3, 0),
('researcher', 0, 1, 0.2, 0.5, 0.4, 0.2, 0.9, 0.2, 0.2, 0),
('miner', 0, 0, 0.1, 0.95, 0.7, 0.5, 0.3, 0.1, 0.1, 1),
('turtle', 0, 0, 0.1, 0.6, 0.5, 0.4, 0.3, 0.2, 0.9, 1);

ALTER TABLE `uni1_bots` ADD COLUMN IF NOT EXISTS `personality` varchar(64) NOT NULL DEFAULT 'balanced';

UPDATE `uni1_system` SET `dbVersion` = 15;

-- Migration 18: Create events table for in-game events displayed on overview page

CREATE TABLE IF NOT EXISTS `uni1_events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`text` mediumtext NOT NULL,
`start_time` int(11) NOT NULL DEFAULT 0,
`end_time` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_end_time` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `uni1_system` SET `dbVersion` = 18;

-- Migration 14: Ensure all forum tables exist (catch-up for existing installs)

CREATE TABLE IF NOT EXISTS `uni1_forum_categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) unsigned DEFAULT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`description` text NOT NULL,
`icon` varchar(20) NOT NULL DEFAULT '📁',
`color` varchar(20) NOT NULL DEFAULT '#38bdf8',
`sort_order` int(11) unsigned NOT NULL DEFAULT 0,
`is_locked` tinyint(1) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_topics` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`views` int(11) unsigned NOT NULL DEFAULT 0,
`is_sticky` tinyint(1) unsigned NOT NULL DEFAULT 0,
`is_locked` tinyint(1) unsigned NOT NULL DEFAULT 0,
`is_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
`last_post_time` int(11) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
`updated_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`),
KEY `user_id` (`user_id`),
KEY `last_post_time` (`last_post_time`),
KEY `is_sticky` (`is_sticky`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_posts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`content` text NOT NULL,
`like_count` int(11) unsigned NOT NULL DEFAULT 0,
`is_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
`updated_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `topic_id` (`topic_id`),
KEY `user_id` (`user_id`),
KEY `is_deleted` (`is_deleted`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_post_likes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `post_user` (`post_id`, `user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_mentions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`is_read` tinyint(1) unsigned NOT NULL DEFAULT 0,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `uni1_forum_mentions` ADD COLUMN `by_user_id` int(11) unsigned NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS `uni1_forum_reports` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) unsigned NOT NULL,
`reporter_id` int(11) unsigned NOT NULL,
`reason` varchar(500) NOT NULL DEFAULT '',
`status` enum('open','closed') NOT NULL DEFAULT 'open',
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`),
KEY `reporter_id` (`reporter_id`),
KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_subscriptions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `topic_user` (`topic_id`, `user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_topic_unreads` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`last_post_id` int(11) unsigned NOT NULL DEFAULT 0,
`updated_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `topic_user` (`topic_id`, `user_id`),
KEY `user_id` (`user_id`),
KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `uni1_cronjobs` ADD COLUMN `lockTime` int(11) DEFAULT NULL;

UPDATE `uni1_system` SET `dbVersion` = 14;

-- Migration 13: Add lockTime column to cronjobs table
ALTER TABLE `uni1_cronjobs` ADD COLUMN IF NOT EXISTS `lockTime` int(11) DEFAULT NULL;

-- Migration 16: Bot-Setting Alliance & ACS configuration columns

-- Alliance settings
ALTER TABLE `uni1_bot_setting`
ADD COLUMN IF NOT EXISTS `can_alliance` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Bots treten/gruenden Allianzen',
ADD COLUMN IF NOT EXISTS `alliance_max_count` int(11) NOT NULL DEFAULT 1 COMMENT 'Max Anzahl Bot-Allianzen pro Server (0=unbegrenzt)',
ADD COLUMN IF NOT EXISTS `alliance_max_members` int(11) NOT NULL DEFAULT 50 COMMENT 'Max Mitglieder pro Bot-Allianz',
ADD COLUMN IF NOT EXISTS `alliance_name_pool` text DEFAULT NULL COMMENT 'JSON-Array mit moeglichen Allianz-Namen, NULL=zufaellig generiert',
ADD COLUMN IF NOT EXISTS `alliance_tag_pool` text DEFAULT NULL COMMENT 'JSON-Array mit moeglichen Allianz-Tags, NULL=zufaellig generiert',
ADD COLUMN IF NOT EXISTS `alliance_internal_tag` varchar(32) NOT NULL DEFAULT '' COMMENT 'Internes Erkennungszeichen (wird NICHT angezeigt), leer=deaktiviert';

-- ACS settings
ALTER TABLE `uni1_bot_setting`
ADD COLUMN IF NOT EXISTS `can_acs` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Bots koennen koordinierte ACS-Angriffe starten',
ADD COLUMN IF NOT EXISTS `acs_max_size` int(11) NOT NULL DEFAULT 3 COMMENT 'Max Bots pro ACS-Gruppe',
ADD COLUMN IF NOT EXISTS `acs_chance_percent` int(11) NOT NULL DEFAULT 30 COMMENT 'Wahrscheinlichkeit (%) pro Tick dass Bot ACS versucht',
ADD COLUMN IF NOT EXISTS `acs_min_loot` bigint(20) NOT NULL DEFAULT 50000 COMMENT 'Mindest-Ressourcen beim Ziel fuer ACS-Angriff';

-- Defense & save settings
ALTER TABLE `uni1_bot_setting`
ADD COLUMN IF NOT EXISTS `can_defense` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Bots bauen Verteidigungsanlagen',
ADD COLUMN IF NOT EXISTS `can_save_fleet` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Bots safen Flotte bei eingehendem Angriff',
ADD COLUMN IF NOT EXISTS `bot_min_fleet_slots` int(11) NOT NULL DEFAULT 5 COMMENT 'Mindest-Flotten-Slots fuer Bots (kompensiert computer_tech=0)',
ADD COLUMN IF NOT EXISTS `spy_probes` int(11) NOT NULL DEFAULT 3 COMMENT 'Anzahl Spionage-Sonden pro Spio-Mission',
ADD COLUMN IF NOT EXISTS `raid_min_loot` bigint(20) NOT NULL DEFAULT 10000 COMMENT 'Mindest-Loot fuer Solo-Raid';

-- Update defaults in existing row
UPDATE `uni1_bot_setting` SET
`can_alliance` = 1,
`alliance_max_count` = 1,
`alliance_max_members` = 50,
`can_acs` = 1,
`acs_max_size` = 3,
`acs_chance_percent` = 30,
`can_defense` = 1,
`can_save_fleet` = 1,
`bot_min_fleet_slots` = 5,
`spy_probes` = 3,
`raid_min_loot` = 10000
WHERE 1;

UPDATE `uni1_system` SET `dbVersion` = 16;

UPDATE uni1_vars SET
cost901 = cost901 / factor,
cost902 = cost902 / factor,
cost903 = cost903 / factor
WHERE elementID IN (1,2,3,4,6,12,14,15,21,22,23,24,31,33,34,44,106,108,109,110,111,113,114,115,117,118,120,121,122,123,124,131,132,133,199);

CREATE TABLE IF NOT EXISTS `uni1_forum_reports` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(11) unsigned NOT NULL,
`reporter_id` int(11) unsigned NOT NULL,
`reason` varchar(500) NOT NULL DEFAULT '',
`status` enum('open','closed') NOT NULL DEFAULT 'open',
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`),
KEY `reporter_id` (`reporter_id`),
KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `uni1_system` SET `dbVersion` = 6;

-- Migration 9: Plugin System v1 – global plugins table

CREATE TABLE `uni1_plugins` (
`id` VARCHAR(100) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`version` VARCHAR(20) NOT NULL,
`type` VARCHAR(20) NOT NULL DEFAULT 'game',
`is_active` TINYINT(1) NOT NULL DEFAULT 0,
`installed_at` INT NOT NULL DEFAULT 0,
`updated_at` INT NOT NULL DEFAULT 0,
`config_json` LONGTEXT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `uni1_system` SET `dbVersion` = 9;

ALTER TABLE uni1_config ADD `message_delete_behavior` tinyint(1) unsigned NOT NULL DEFAULT '0' AFTER `resource_multiplier`;
ALTER TABLE uni1_config ADD `message_delete_days` tinyint(3) unsigned NOT NULL DEFAULT '7' AFTER `message_delete_behavior`;
ALTER TABLE uni1_messages ADD `message_deleted` int(11) unsigned NULL DEFAULT NULL AFTER `message_universe`;
ALTER TABLE uni1_messages DROP INDEX `message_owner`, ADD INDEX `message_owner` (`message_owner`, `message_type`, `message_unread`, `message_deleted`) USING BTREE;
ALTER TABLE uni1_messages ADD INDEX `message_deleted` (`message_deleted`);

-- Migration 7: Forum subscriptions + topic unreads for notification system

CREATE TABLE IF NOT EXISTS `uni1_forum_subscriptions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`created_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `topic_user` (`topic_id`, `user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `uni1_forum_topic_unreads` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`last_post_id` int(11) unsigned NOT NULL DEFAULT 0,
`updated_at` int(11) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `topic_user` (`topic_id`, `user_id`),
KEY `user_id` (`user_id`),
KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add by_user_id to forum_mentions if not already present (idempotent via IF NOT EXISTS workaround)
ALTER TABLE `uni1_forum_mentions`
ADD COLUMN IF NOT EXISTS `by_user_id` int(11) unsigned NOT NULL DEFAULT 0;

UPDATE `uni1_system` SET `dbVersion` = 7;

-- Migration 11: Drop and recreate chat tables with correct schema

DROP TABLE IF EXISTS `uni1_chat_messages`;
DROP TABLE IF EXISTS `uni1_chat_bans`;

CREATE TABLE `uni1_chat_messages` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`channel` ENUM('global','alliance') NOT NULL DEFAULT 'global',
`alliance_id` INT UNSIGNED NOT NULL DEFAULT 0,
`user_id` INT UNSIGNED NOT NULL,
`username` VARCHAR(64) NOT NULL,
`message` TEXT NOT NULL,
`created_at` INT UNSIGNED NOT NULL,
`deleted_at` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_channel_alliance` (`channel`, `alliance_id`, `deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `uni1_chat_bans` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED NOT NULL,
`banned_by` INT UNSIGNED NOT NULL,
`reason` VARCHAR(255) NOT NULL DEFAULT '',
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `uni1_system` SET `dbVersion` = 12;

CREATE TABLE `uni1_system` (
`dbVersion` int NOT NULL DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO uni1_system SET dbVersion = 1;