ALTER TABLE `game_ship_templates`
    ADD COLUMN `ship_type` VARCHAR(50) NOT NULL DEFAULT 'transport' AFTER `image_path`;

----------------------

schiff erstellen

----------------------

ALTER TABLE `game_players`
    ADD COLUMN `exploration_supply_progress` INT NOT NULL DEFAULT 0 AFTER `kreativpunkte`;

---------------------

ALTER TABLE game_players ADD last_exploration_start TIMESTAMP NULL DEFAULT NULL;

ALTER TABLE player_ships MODIFY Status
ENUM('building','idle_in_harbor','loading','underway_to_island','unloading_at_island','returning_to_harbor','at_island', 'on_exploration') NOT NULL DEFAULT 'building';

CREATE TABLE player_exploration_missions (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 player_ship_id INT NOT NULL,
 mission_ends_at TIMESTAMP NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES game_players(user_id),
 FOREIGN KEY (player_ship_id) REFERENCES player_ships(id)
);

----------------------------------

CREATE TABLE `pending_discoveries` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `user_id` int(11) NOT NULL,
       `island_name` varchar(100) NOT NULL,
       `base_travel_time_minutes` int(11) NOT NULL,
       `resource_1_id` int(11) NOT NULL,
       `resource_2_id` int(11) NOT NULL,
       `resource_3_id` int(11) NOT NULL,
       `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      KEY `idx_user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

----------------------------

CREATE TABLE `player_islands` (
       `id` INT NOT NULL AUTO_INCREMENT,
       `user_id` INT NOT NULL,
       `name` VARCHAR(255) NOT NULL,
       `base_travel_time_minutes` INT NOT NULL,
       `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`),
       UNIQUE KEY `user_id_unique` (`user_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

----------------------------------------------------
   
    CREATE TABLE `player_island_resources` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `player_island_id` INT NOT NULL,                                                                               
      `user_id` INT NOT NULL,                                                                                        
      `resource_id` INT NOT NULL,                                                                                    
      PRIMARY KEY (`id`),                                                                                            
      FOREIGN KEY (`player_island_id`) REFERENCES `player_islands`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--------------------------------------

CREATE TABLE `player_island_build_progress` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `user_id` INT NOT NULL,
      `resource_id` INT NOT NULL,
      `amount_delivered` BIGINT NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`),
      UNIQUE KEY `user_resource_unique` (`user_id`, `resource_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


--------------------------------------

ALTER TABLE `player_island_build_progress`
     ADD COLUMN `island_id` INT(11) NOT NULL AFTER `user_id`;

-----------------------------------------

ALTER TABLE `player_island_build_progress`
    MODIFY COLUMN `id` INT(11) NOT NULL;   

---------------------------------

ALTER TABLE `player_island_build_progress`
    DROP PRIMARY KEY;

-------------------------

ALTER TABLE `player_island_build_progress`
    ADD PRIMARY KEY (`user_id`, `island_id`, `resource_id`);

------------------------

ALTER TABLE `player_island_build_progress`
    ADD CONSTRAINT `fk_player_build_progress_island` FOREIGN KEY (`island_id`) REFERENCES `player_islands`(`id`) ON
      DELETE CASCADE ON UPDATE CASCADE;

-------------------------

CREATE TABLE `game_island_building_templates` (
         `id` INT(11) NOT NULL AUTO_INCREMENT,
         `name` VARCHAR(100) NOT NULL UNIQUE,
         `type` ENUM('Inselhafen', 'Lager', 'Produktion', 'Spezial') NOT NULL DEFAULT 'Inselhafen',
         `description` TEXT DEFAULT NULL,
         `build_time_seconds` INT(11) NOT NULL DEFAULT 60,
         `initial_limit` INT(11) NOT NULL DEFAULT 1,
         `build_cost_coins` INT(11) NOT NULL DEFAULT 0,
         `build_cost_taler` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `required_rank_id_build` INT(11) DEFAULT NULL,
        `image_path` VARCHAR(255) DEFAULT NULL,
        `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `building_name_unique` (`name`),
        KEY `idx_req_rank_build` (`required_rank_id_build`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

---------------------------------

CREATE TABLE `game_island_building_levels` (
         `id` INT(11) NOT NULL AUTO_INCREMENT,
         `building_id` INT(11) NOT NULL, -- References game_island_building_templates.id
         `level` INT(11) NOT NULL DEFAULT 1,
         `unlocks_id` INT(11) DEFAULT NULL, -- What this level unlocks (e.g., a new feature, another building)
         `produces_resource_id` INT(11) DEFAULT NULL,
         `production_amount` INT(11) DEFAULT NULL,
         `production_cycle_seconds` INT(11) DEFAULT NULL,
         `shop_capacity` INT(11) DEFAULT 0,
        `special_shop_capacity` INT(11) DEFAULT 0,
        `bonus_capacity` INT(11) DEFAULT 0, -- General storage bonus
        `bonus_decryption_points` INT(11) DEFAULT 0,
        `bonus_research_points` INT(11) DEFAULT 0,
        `victory_points` INT(11) DEFAULT 0,
        `upgrade_cost_coins` INT(11) DEFAULT 0,
        `upgrade_cost_taler` DECIMAL(15,2) DEFAULT 0.00,
        `upgrade_requires_rank_id` INT(11) DEFAULT NULL,
        `taler_production` DECIMAL(15,2) DEFAULT 0.00,
        `shop_offer_volume_limit` INT(11) DEFAULT NULL,
        `bonus_museum_slots` INT(11) DEFAULT 0,
        PRIMARY KEY (`id`),
        UNIQUE KEY `idx_building_level` (`building_id`, `level`),
        KEY `fk_island_building_levels_building` (`building_id`),
        KEY `fk_island_building_levels_unlocks` (`unlocks_id`),
        KEY `fk_island_building_levels_produces_resource` (`produces_resource_id`),
        KEY `fk_island_building_levels_upgrade_rank` (`upgrade_requires_rank_id`),
        CONSTRAINT `fk_island_building_levels_building` FOREIGN KEY (`building_id`) REFERENCES `game_island_building_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `fk_island_building_levels_produces_resource` FOREIGN KEY (`produces_resource_id`) REFERENCES `game_resources` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
        CONSTRAINT `fk_island_building_levels_upgrade_rank` FOREIGN KEY (`upgrade_requires_rank_id`) REFERENCES `game_ranks` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

----------------------------

CREATE TABLE `game_island_building_build_costs` (
        `building_id` INT(11) NOT NULL, -- References game_island_building_templates.id
        `resource_id` INT(11) NOT NULL, -- References game_resources.id
        `amount` INT(11) NOT NULL DEFAULT 1,
        PRIMARY KEY (`building_id`, `resource_id`),
        KEY `fk_island_build_costs_resource` (`resource_id`),
        CONSTRAINT `fk_island_build_costs_building` FOREIGN KEY (`building_id`) REFERENCES
     `game_island_building_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `fk_island_build_costs_resource` FOREIGN KEY (`resource_id`) REFERENCES `game_resources` (`id`) ON
     DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

----------------------------

CREATE TABLE `game_island_building_upgrade_costs` (
         `building_id` INT(11) NOT NULL, -- References game_island_building_templates.id
         `to_level` INT(11) NOT NULL,
         `resource_id` INT(11) NOT NULL, -- References game_resources.id
         `amount` INT(11) NOT NULL DEFAULT 1,
         PRIMARY KEY (`building_id`, `to_level`, `resource_id`),
         KEY `fk_island_upgrade_costs_resource` (`resource_id`),
         CONSTRAINT `fk_island_upgrade_costs_building` FOREIGN KEY (`building_id`) REFERENCES `game_island_building_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
         CONSTRAINT `fk_island_upgrade_costs_resource` FOREIGN KEY (`resource_id`) REFERENCES `game_resources` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-------------------------

ALTER TABLE player_islands
    ADD COLUMN harbor_level INT NOT NULL DEFAULT 0;

-------------------------

ALTER TABLE `game_players`
    ADD `last_shipment_to_player_island_at` TIMESTAMP NULL DEFAULT NULL
    COMMENT 'Zeitstempel der letzten Entsendung eines Schiffs zur Spieler-Insel für den 3-Tage-Cooldown';

------------------------

ALTER TABLE `player_ships` MODIFY `status` VARCHAR(255);

---------------

ALTER TABLE player_ships
    ADD COLUMN mission_type TINYINT NOT NULL DEFAULT 0 COMMENT '0: Manueller Transport, 1: Automatisierte Mission, 2:
     Erkundungsmission, 3: Inselaufbau' AFTER applied_sink_chance_modifier;

----------------------

ALTER TABLE `player_island_build_progress` MODIFY `id` INT NULL;

-----------------------

UPDATE `player_ships` SET `status` = 'Im Haupthafen' WHERE `status` = 'idle_in_harbor';

UPDATE `player_ships` ps
     JOIN `player_islands` pi ON ps.`current_island_id` = pi.`id`
     SET ps.`status` = CONCAT('Angelegt bei ''', pi.`name`, '''')
     WHERE ps.`status` IN ('idle_at_player_island', 'at_island');

UPDATE `player_ships` ps
    JOIN `player_islands` pi ON ps.`target_island_id` = pi.`id`
    SET ps.`status` = CONCAT('Unterwegs zu ''', pi.`name`, '''')                                                     
    WHERE ps.`status` = 'underway_to_player_island';

UPDATE `player_ships` SET `status` = 'Kehrt zum Haupthafen zurück' WHERE `status` = 'returning_from_player_island'

UPDATE `player_ships` SET `status` = 'Auf Erkundung' WHERE `status` = 'on_exploration';

UPDATE `player_ships` ps
    JOIN `player_islands` pi ON ps.`current_island_id` = pi.`id`
    SET ps.`status` = CONCAT('Wird be-/entladen bei ''', pi.`name`, '''')
    WHERE ps.`status` IN ('loading', 'unloading_at_island');

----------------------------

ALTER TABLE `pending_discoveries`
    ADD COLUMN `discovery_type` ENUM('new_island', 'new_resource') NOT NULL DEFAULT 'new_island' AFTER `user_id`,
    MODIFY COLUMN `island_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
    MODIFY COLUMN `base_travel_time_minutes` int(11) NULL,
    MODIFY COLUMN `resource_2_id` int(11) NULL,
    MODIFY COLUMN `resource_3_id` int(11) NULL;

-----------------------------

CREATE TABLE IF NOT EXISTS `player_island_storage` (
         `id` INT AUTO_INCREMENT PRIMARY KEY,
         `player_island_id` INT NOT NULL COMMENT 'Referenz zur Insel des Spielers in player_islands',
         `resource_id` INT NOT NULL COMMENT 'Referenz zur Ressource in game_resources',
         `quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Menge der gelagerten Ressource',
         `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (`player_island_id`) REFERENCES `player_islands`(`id`) ON DELETE CASCADE,
        FOREIGN KEY (`resource_id`) REFERENCES `game_resources`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `unique_island_resource` (`player_island_id`, `resource_id`)
    ) COMMENT='Lager für Ressourcen auf Spieler-Inseln.';

--------------------------------------------

CREATE TABLE `player_market_prices` (
         `id` INT(11) NOT NULL AUTO_INCREMENT,
         `user_id` INT(11) NOT NULL COMMENT 'Referenz zum Benutzer (aus der users-Tabelle). Der Besitzer dieses
      Marktes.',
         `resource_id` INT(11) NOT NULL COMMENT 'Referenz zum Rohstoff in der game_resources Tabelle.',
         `price` DECIMAL(10, 4) NOT NULL COMMENT 'Der für den Tag berechnete Preis für diesen Spieler-Markt.',
         `updated_at` DATE NOT NULL COMMENT 'Datum der letzten Preisaktualisierung.',
         PRIMARY KEY (`id`),
        UNIQUE KEY `user_resource_unique` (`user_id`, `resource_id`),
        FOREIGN KEY (`resource_id`) REFERENCES `game_resources`(`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabelle für tägliche,
      spielerspezifische Rohstoffpreise.';

-------------------------------

ALTER TABLE game_players ADD COLUMN ship_protection_donated INT DEFAULT 0;

-----------------------------------

ALTER TABLE player_artworks ADD COLUMN locked_until DATETIME NULL DEFAULT NULL;

----------

ALTER TABLE player_artworks ADD INDEX idx_locked_until (locked_until);