INFO UPDATE: 

1. Die Seite Siegpunkte wurde in Rangliste umbenannt. Dort werden jetzt mehrere Faktoren berücksichtigt
   um eine bessere Vergleichbarkeit der Spielerfortschritte zu haben.
2. Das Spenden für Siegpunkte wurde in den Recyclinghof integriert.
3. Die anzeige von Ruf und Siegpunkten sind jetzt auf der Startseite (Index)
4. Unser Dorf hat neue Hintergrundbilder passend zur Jahreszeit erhalten. Spezielle für Hafen un Markt.
   Auch die Optik für die verschiedenen Fenster wurde verändert.
5. Alles was eine Spende benötigt um zu laufen oder in einem bestimmten intervall 
   gezahlt werden muss ist auf der neuen Seite Versorgung.
6. Die Seite Suppenküche wurde aus der Navigation entfernt, ist aber über Versorgung 
   erreichbar oder es kann direkt dort gespendet werden.
7. Auf der Seite Meine Stadt sieht man alle Gebäude die man gebaut hat.
8. Erfolge sind jetzt verfügbar. Bei erreichen wird auf der Startseite ein Pokal
   eingeblendet. klickt man darauf kommt an zur Erfolgsseite und der Pokal verschwindet.
   Auf der Seite Erfolge werden neue oder aufgestiegene Erfolge mit button angezeigt.
   Nach Bestätigung verschwinden diese. So kann man immer zwischen alten und neuen unterscheiden.
   Das klicken auf den Button bestätigt nur die zur Kenntnisnahme, die 
   Belohnung wird direkt bei Erreichung gutgeschrieben.
9. Verschiedene Suchleisten wurden auf Seiten integriert um das finden von zu erleichtern.
10. Bei Nachrichten wurde eine Möglichkeit eingerichtet Expeditionsnachrichten zu verhindern.
11. Es kann jetzt das Museum gebaut werden.



Gebäude museum
Tier   rudolf
ruf 1,5



XX
CREATE TABLE `game_furniture` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `image_path` VARCHAR(255) NULL DEFAULT NULL,
  `required_reputation` INT(11) NOT NULL DEFAULT 0,
  `required_victory_points` INT(11) NOT NULL DEFAULT 0,
  `cost_taler` INT(11) NOT NULL DEFAULT 0,
  `cost_coins` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-----------------------------
XX
CREATE TABLE `game_furniture_costs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `furniture_id` INT(11) NOT NULL,
  `resource_id` INT(11) NOT NULL,
  `amount` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `furniture_resource_unique` (`furniture_id`, `resource_id`),
  CONSTRAINT `fk_furniturecosts_furniture` FOREIGN KEY (`furniture_id`) REFERENCES `game_furniture` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_furniturecosts_resource` FOREIGN KEY (`resource_id`) REFERENCES `game_resources` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
----------------------
XX
CREATE TABLE `game_rooms` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `image_path` VARCHAR(255) NULL DEFAULT NULL,
  `required_reputation` INT(11) NOT NULL DEFAULT 0,
  `required_victory_points` INT(11) NOT NULL DEFAULT 0,
  `cost_taler` INT(11) NOT NULL DEFAULT 0,
  `cost_coins` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-----------------
XX
CREATE TABLE `game_room_requirements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `room_id` INT(11) NOT NULL,
  `required_item_id` INT(11) NOT NULL,
  `required_item_type` ENUM('furniture', 'resource') NOT NULL,
  `amount` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_roomreqs_room` FOREIGN KEY (`room_id`) REFERENCES `game_rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-----------------------
XX
CREATE TABLE `game_houses` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `adds_population_type` ENUM('standard', 'elite') NOT NULL,
  `adds_population_count` INT(11) NOT NULL,
  `required_reputation` INT(11) NOT NULL DEFAULT 0,
  `required_victory_points` INT(11) NOT NULL DEFAULT 0,
  `cost_taler` INT(11) NOT NULL DEFAULT 0,
  `cost_coins` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-----------------------
XX
CREATE TABLE `game_house_requirements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `house_id` INT(11) NOT NULL,
  `required_room_id` INT(11) NOT NULL,
  `amount` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `house_room_unique` (`house_id`, `required_room_id`),
  CONSTRAINT `fk_housereqs_house` FOREIGN KEY (`house_id`) REFERENCES `game_houses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_housereqs_room` FOREIGN KEY (`required_room_id`) REFERENCES `game_rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--------------------------------
X
CREATE TABLE `player_furniture_storage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `furniture_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_furniture_unique` (`user_id`, `furniture_id`),
  KEY `fk_playerfurniture_furniture_idx` (`furniture_id`),
  CONSTRAINT `fk_playerfurniture_furniture` FOREIGN KEY (`furniture_id`) REFERENCES `game_furniture` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

----------------------------------
X
CREATE TABLE `player_room_storage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `room_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_room_unique` (`user_id`, `room_id`),
  KEY `fk_playerroom_room_idx` (`room_id`),
  CONSTRAINT `fk_playerroom_room` FOREIGN KEY (`room_id`) REFERENCES `game_rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-----------------------------------
X
ALTER TABLE `game_players`
ADD COLUMN `population_standard` INT(11) NOT NULL DEFAULT 0 AFTER `has_unclaimed_lottery_prize`,
ADD COLUMN `population_elite` INT(11) NOT NULL DEFAULT 0 AFTER `population_standard`;

-----------------------------------
X
ALTER TABLE `game_rooms`
CHANGE COLUMN `required_reputation` `cost_reputation` INT(11) NOT NULL DEFAULT 0,
CHANGE COLUMN `required_victory_points` `cost_victory_points` INT(11) NOT NULL DEFAULT 0;

--------------
X
ALTER TABLE game_houses
CHANGE COLUMN `required_reputation` `cost_reputation` INT(11) NOT NULL DEFAULT 0,
CHANGE COLUMN `required_victory_points` `cost_victory_points` INT(11) NOT NULL DEFAULT 0;

-------------------------
X
ALTER TABLE `game_furniture`
CHANGE COLUMN `required_reputation` `cost_reputation` INT(11) NOT NULL DEFAULT 0,
CHANGE COLUMN `required_victory_points` `cost_victory_points` INT(11) NOT NULL DEFAULT 0;

----------
X
ALTER TABLE `game_village_status_levels`
ADD COLUMN `required_population_standard` INT(11) NOT NULL DEFAULT 0 AFTER `max_stars`,
ADD COLUMN `required_population_elite` INT(11) NOT NULL DEFAULT 0 AFTER `required_population_standard`;

-----------------------
X
 -- 1. Spalte für die Herstellungszeit zu game_furniture hinzufügen
     ALTER TABLE `game_furniture`
     ADD COLUMN `build_time_seconds` INT NOT NULL DEFAULT 0 COMMENT 'Herstellungszeit in Sekunden'
      AFTER `cost_coins`;
    
     -- 2. Spalte für die Herstellungszeit zu game_rooms hinzufügen
     ALTER TABLE `game_rooms`
     ADD COLUMN `build_time_seconds` INT NOT NULL DEFAULT 0 COMMENT 'Herstellungszeit in Sekunden'
      AFTER `cost_coins`;
    
     -- 3. Spalte für die Herstellungszeit zu game_houses hinzufügen
    ALTER TABLE `game_houses`
    ADD COLUMN `build_time_seconds` INT NOT NULL DEFAULT 0 COMMENT 'Herstellungszeit in Sekunden'
      AFTER `cost_coins`;
   
    -- 4. Neue Tabelle für die Herstellungswarteschlange der Spieler erstellen
    CREATE TABLE `player_crafting_queue` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `user_id` INT NOT NULL,
      `item_id` INT NOT NULL,
      `item_type` ENUM('furniture', 'room', 'house') NOT NULL,
      `quantity` INT NOT NULL,
      `status` ENUM('queued', 'in_progress', 'completed') NOT NULL DEFAULT 'queued',
      `start_time` DATETIME NULL DEFAULT NULL,
      `finish_time` DATETIME NULL DEFAULT NULL,
      `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      INDEX `idx_user_status` (`user_id`, `status`),
      INDEX `idx_finish_time` (`finish_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-------------------
X
ALTER TABLE game_players
    ADD COLUMN ausgegebene_siegpunkte INT NOT NULL DEFAULT 0 AFTER exchanged_victory_points;

-----------------------------
X
ALTER TABLE player_crafting_queue MODIFY COLUMN status ENUM('queued','in_progress',
     'completed','cancelled') NOT NULL DEFAULT 'queued';

------------------------
X
ALTER TABLE `game_rooms`
ADD COLUMN `is_always_visible` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Raum wird immer angezeigt, ignoriert Entdecker-Regeln' AFTER `image_path`;

----------------------
X
ALTER TABLE `game_houses`
ADD COLUMN `is_always_visible` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Haus wird immer angezeigt, ignoriert Entdecker-Regeln' AFTER `description`,
ADD COLUMN `build_limit` INT(11) NOT NULL DEFAULT 0 COMMENT '0 = unbegrenzt, 1 = einmalig, >1 = limitiert' AFTER `is_always_visible`,
ADD COLUMN `reward_type` ENUM(
    'population',
    'unlock_ship',
    'unlock_expedition_type',
    'unlock_island',
    'global_production_boost',
    'reduce_expedition_time',
    'unlock_ui_animal_stats'
) NOT NULL DEFAULT 'population' COMMENT 'Definiert die Belohnung bei Fertigstellung' AFTER `adds_population_count`,
ADD COLUMN `reward_data` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Zusatzdaten für die Belohnung (z.B. ID des Schiffs)' AFTER `reward_type`;

-------------------------------
X
ALTER TABLE `game_ship_templates`
ADD COLUMN `requires_unlock` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = Schiff ist unsichtbar bis zur Freischaltung' AFTER `is_buildable`;

------------------------------
X
CREATE TABLE `player_built_houses` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `house_id` INT(11) NOT NULL,
  `quantity` INT(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_house_unique` (`user_id`, `house_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-------------------------------
X
CREATE TABLE `player_unlocked_blueprints` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `blueprint_type` ENUM('room', 'house') NOT NULL,
  `blueprint_id` INT(11) NOT NULL,
  `unlocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_blueprint_unique` (`user_id`, `blueprint_type`, `blueprint_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

------------------------------------
X

CREATE TABLE `game_achievements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NOT NULL,
  `category` ENUM('Wirtschaft', 'Entdeckung', 'Sammeln', 'Soziales') NOT NULL,
  `trigger_type` VARCHAR(50) NOT NULL COMMENT 'z.B. learn_recipe, catch_animal_rarity, collect_resource',
  `trigger_data` VARCHAR(255) NULL DEFAULT NULL COMMENT 'z.B. eine recipe_id, Tier-Seltenheit, resource_id',
  `trigger_threshold` INT(11) NOT NULL COMMENT 'Benötigte Anzahl zum Abschluss',
  `reward_type` ENUM('siegpunkte', 'taler', 'coins', 'titel') NOT NULL,
  `reward_amount` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

------------------------------
XX
CREATE TABLE `player_achievements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `achievement_id` INT(11) NOT NULL,
  `unlocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_achievement_unique` (`user_id`, `achievement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

----------------------------
X
CREATE TABLE `player_unlocked_ship_templates` (
      `user_id` INT(11) NOT NULL,
      `ship_template_id` INT(11) NOT NULL,
      `unlocked_at` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`user_id`, `ship_template_id`),
      CONSTRAINT `fk_pust_user` FOREIGN KEY (`user_id`) REFERENCES `spielegruppe_main_test`.`users`
     (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `fk_pust_ship_template` FOREIGN KEY (`ship_template_id`) REFERENCES
     `game_ship_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

------------------------------------
X
 -- Schritt 1: Erweitert den ENUM-Typ, um die neuen Werte aufzunehmen und setzt einen neuen
      Standardwert
     ALTER TABLE `game_houses`
     MODIFY COLUMN `reward_type` ENUM(
         'population',
         'population_standard',
         'population_elite',
         'unlock_ship',
         'unlock_expedition_type',
         'unlock_island',
        'global_production_boost',
        'reduce_expedition_time',
        'unlock_ui_animal_stats'
    ) NOT NULL DEFAULT 'population_standard';
   
-----------------------------------------------
X
    -- Schritt 2: Entfernt die alten, überflüssigen Bevölkerungsspalten
    ALTER TABLE `game_houses`
    DROP COLUMN `adds_population_type`,
    DROP COLUMN `adds_population_count`;

----------------------------------------
X
 -- Schritt 1: Fügt die Spalte 'requires_unlock' zur Tabelle der Expeditionstypen hinzu
     ALTER TABLE `game_expedition_types`
     ADD COLUMN `requires_unlock` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Wenn 1, muss dieser
      Expeditionstyp erst freigeschaltet werden' AFTER `requires_building_id`;

------------------------------------------------------
    X
     -- Schritt 2: Erstellt die neue Tabelle, um die Freischaltungen pro Spieler zu speichern
     CREATE TABLE `player_unlocked_expedition_types` (
       `user_id` INT(11) NOT NULL,
       `expedition_type_id` INT(11) NOT NULL,
       `unlocked_at` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`user_id`, `expedition_type_id`),
      CONSTRAINT `fk_puet_user` FOREIGN KEY (`user_id`) REFERENCES `spielegruppe_main_test`.`users`
      (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `fk_puet_expedition_type` FOREIGN KEY (`expedition_type_id`) REFERENCES
      `game_expedition_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

------------------------------
X
 -- Schritt 1: Fügt die Spalte 'requires_unlock' zur Insel-Tabelle hinzu
     ALTER TABLE `game_islands`
     ADD COLUMN `requires_unlock` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Wenn 1, muss diese Insel
      erst freigeschaltet werden' AFTER `sort_order`;

-------------------------------------------------------
  XX  
CREATE TABLE `game_ui_features` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `feature_key` VARCHAR(100) NOT NULL COMMENT 'Einzigartiger Schlüssel für die Code-Prüfung, z.B.
     zoo_show_total_count',
      `description` VARCHAR(255) NOT NULL COMMENT 'Menschlich lesbare Beschreibung für das
    Admin-Panel',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_feature_key` (`feature_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
----------------------------
X
CREATE TABLE `player_unlocked_ui_features` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `user_id` INT(11) NOT NULL,
       `feature_key` VARCHAR(100) NOT NULL COMMENT 'Verweist auf den Schlüssel in game_ui_features',
       `unlocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`),
       UNIQUE KEY `player_feature_key_unique` (`user_id`, `feature_key`),
       KEY `fk_ui_feature_key` (`feature_key`),
       CONSTRAINT `fk_player_ui_feature_user` FOREIGN KEY (`user_id`) REFERENCES `game_players`
      (`user_id`) ON DELETE CASCADE,
      CONSTRAINT `fk_ui_feature_key` FOREIGN KEY (`feature_key`) REFERENCES `game_ui_features`
      (`feature_key`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   

--------------------------
X
ALTER TABLE `game_houses`
     MODIFY COLUMN `reward_type` ENUM(
         'population_standard',
         'population_elite',
         'unlock_ship',
         'unlock_expedition_type',
         'unlock_island',
         'reduce_expedition_time',
         'unlock_ui_feature'
    ) NOT NULL DEFAULT 'population_standard';

----------------------
X
ALTER TABLE `game_houses` ADD COLUMN `image_path` VARCHAR(255) NULL DEFAULT NULL AFTER
     `description`;

-------------------------------
XX
 CREATE TABLE `achievement_groups` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `group_name` VARCHAR(100) NOT NULL,
       `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`),
       UNIQUE KEY `group_name_unique` (`group_name`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-------------------------------------------------
 X   
    -- Schritt 2: Die bestehende 'game_achievements' Tabelle anpassen
    -- Wir entfernen 'category' und fügen 'group_id' und 'tier' hinzu.
    ALTER TABLE `game_achievements`
      ADD COLUMN `group_id` INT(11) NULL AFTER `description`,
      ADD COLUMN `tier` INT(11) NOT NULL DEFAULT 1 AFTER `group_id`,
      DROP COLUMN `category`,
      ADD CONSTRAINT `fk_achievement_group` FOREIGN KEY (`group_id`) REFERENCES
      `achievement_groups` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

----------------------------------
X
ALTER TABLE `game_players`
    ADD COLUMN `has_unclaimed_achievements` TINYINT(1) NOT NULL DEFAULT 0 AFTER `population_elite`;

---------------------------------
X
ALTER TABLE `player_achievements`
    ADD COLUMN `is_seen` BOOLEAN NOT NULL DEFAULT FALSE AFTER `unlocked_at`,
    ADD INDEX `idx_unseen` (`user_id`, `is_seen`);

-------------------------------------------
X
ALTER TABLE `player_stats`
    ADD COLUMN `soups_donated` INT(11) NOT NULL DEFAULT 0,
    ADD COLUMN `resources_recycled` BIGINT(20) NOT NULL DEFAULT 0,
    ADD COLUMN `pirate_attacks_own` INT(11) NOT NULL DEFAULT 0,
    ADD COLUMN `pirate_attacks_received` INT(11) NOT NULL DEFAULT 0,
    ADD COLUMN `resources_delivered_to_islands` BIGINT(20) NOT NULL DEFAULT 0;

--------------------------
X
ALTER TABLE `player_stats`
    ADD COLUMN `crafts_completed_furniture` INT NOT NULL DEFAULT 0 AFTER`resources_delivered_to_islands`,
    ADD COLUMN `crafts_completed_rooms` INT NOT NULL DEFAULT 0 AFTER `crafts_completed_furniture`,
    ADD COLUMN `crafts_completed_houses` INT NOT NULL DEFAULT 0 AFTER `crafts_completed_rooms`;

----------------------------------
X
ALTER TABLE `game_achievements`
    MODIFY COLUMN `reward_type` ENUM('siegpunkte','taler','coins','titel','ruf') NOT NULL,
    MODIFY COLUMN `reward_amount` DECIMAL(12, 4) NOT NULL DEFAULT 1.0000;

-------------------------------
X
ALTER TABLE `game_ui_features`
       ADD COLUMN `value_type` ENUM('float', 'int', 'bool', 'string') NOT NULL DEFAULT 'bool' COMMENT
      'Datentyp des Wertes' AFTER `description`,
       ADD COLUMN `value` VARCHAR(255) NOT NULL DEFAULT 'true' COMMENT 'Der Wert des Perks, z.B. 0.1'
      AFTER `value_type`,
       ADD COLUMN `is_gameplay_perk` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Trennt Gameplay-Perks
      von reinen UI-Features' AFTER `value`;

---------------------------------
   X 
     -- 2. Bestehende 'game_achievements' Tabelle anpassen (dieser Teil bleibt gleich)
     ALTER TABLE `game_achievements`
       MODIFY COLUMN `reward_type` ENUM('siegpunkte', 'taler', 'coins', 'titel', 'ruf', 'perk') NOT
      NULL;

----------------------------------------
   X
    -- 3. Den Beispiel-Perk in die erweiterte Tabelle einfügen
    INSERT INTO `game_ui_features` (`feature_key`, `description`, `value_type`, `value`,
      `is_gameplay_perk`)
    VALUES ('PIRATE_ATTACK_TALER_BONUS', 'Erhöht die Taler-Belohnung bei Piratenangriffen um einen
      festen Betrag.', 'float', '0.1', TRUE);

-----------------------------
X
ALTER TABLE `game_achievements`
    ADD COLUMN `reward_data` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Zusätzliche Daten für die
     Belohnung, z.B. die ID eines Perks' AFTER `reward_amount`;

----------------------------
X
INSERT INTO `game_ui_features` (`feature_key`, `description`, `value_type`, `value`,
     `is_gameplay_perk`)
    VALUES ('PIRATE_PROTECTION_DURATION_INCREASE', 'Erhöht den Piratenschutz nach einem Angriff von 3
     auf 3,5 Tage (84 Stunden).', 'bool', 'true', 1);

INSERT INTO `game_ui_features` (`feature_key`, `description`, `value_type`, `value`,
     `is_gameplay_perk`)
    VALUES ('RECYCLING_BOOST_COST_REDUCTION', 'Reduziert die Kosten für einen Boost aus dem
     Recyclinghof von 50 auf 45 Punkte.', 'bool', 'true', 1);

INSERT INTO `game_ui_features` (`feature_key`, `description`, `value_type`, `value`,
     `is_gameplay_perk`) VALUES
    ('ZOO_SHOW_COMMON_COUNT', 'Zeigt im Zoo die Anzahl gesammelter gewöhnlicher Tiere an (X/Y)',
     'bool', 'true', 1),
    ('ZOO_SHOW_UNCOMMON_COUNT', 'Zeigt im Zoo die Anzahl gesammelter ungewöhnlicher Tiere an (X/Y)',
     'bool', 'true', 1),
    ('ZOO_SHOW_RARE_COUNT', 'Zeigt im Zoo die Anzahl gesammelter seltener Tiere an (X/Y)', 'bool',
     'true', 1),
    ('ZOO_SHOW_EPIC_COUNT', 'Zeigt im Zoo die Anzahl gesammelter epischer Tiere an (X/Y)', 'bool',
     'true', 1),
    ('ZOO_SHOW_LEGENDARY_COUNT', 'Zeigt im Zoo die Anzahl gesammelter legendärer Tiere an (X/Y)',
     'bool', 'true', 1);

-----------------------
X
ALTER TABLE `game_houses`
         ADD COLUMN `supply_building_type` ENUM('passive', 'producer') NULL DEFAULT NULL AFTER
      `build_time_seconds`,
         ADD COLUMN `supply_resource_id` INT(11) NULL DEFAULT NULL AFTER `supply_building_type`,
         ADD COLUMN `supply_amount` INT(11) NOT NULL DEFAULT 0 COMMENT 'Benötigte Menge für eine
      Einheit/einen Zyklus' AFTER `supply_resource_id`,
         ADD COLUMN `produces_population_id` INT(11) NULL DEFAULT NULL COMMENT 'FK zu
      game_population_templates, was dieses Gebäude produziert' AFTER `supply_amount`,
        ADD COLUMN `production_duration_days` INT(11) NOT NULL DEFAULT 0 COMMENT 'Wie lange ein
      Produktionszyklus läuft (nur für Typ producer)' AFTER `produces_population_id`,
        ADD COLUMN `reward_amount` DECIMAL(10,4) NOT NULL DEFAULT 0.0000 COMMENT 'Belohnungsmenge
      pro Zyklus (z.B. Ruf)' AFTER `reward_data`,
        ADD COLUMN `supply_period_hours` INT(11) NOT NULL DEFAULT 24 COMMENT 'Dauer eines
      Belohnungszyklus (z.B. 24h für täglichen Ruf)' AFTER `reward_amount`,
        MODIFY COLUMN `reward_type` ENUM('population_standard','population_elite','unlock_ship',
      'unlock_expedition_type','unlock_island','reduce_expedition_time','unlock_ui_feature',
      'reputation','population_special') NOT NULL DEFAULT 'population_standard';

XX
 -- Schritt 2: Neue Tabelle zur Definition von speziellen Einwohner-Typen (z.B. Mönch).
    CREATE TABLE `game_population_templates` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(100) NOT NULL,
      `description` TEXT NULL,
      `image_path` VARCHAR(255) NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 X  
    -- Schritt 3: Neue Tabelle, um den Besitz von speziellen Einwohnern pro Spieler zu speichern.
    CREATE TABLE `player_population` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `user_id` INT(11) NOT NULL,
      `population_template_id` INT(11) NOT NULL,
      `quantity` INT(11) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_player_population` (`user_id`, `population_template_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  X 
    -- Schritt 4: Neue Tabelle, um den Versorgungs- und Produktionsstatus der neuen Gebäude pro
      Spieler zu verwalten.
    -- Diese Tabelle ist das Herzstück der neuen Logik.
    CREATE TABLE `player_supplied_buildings` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `user_id` INT(11) NOT NULL,
      `house_id` INT(11) NOT NULL,
      `total_instances` INT(11) NOT NULL DEFAULT 0 COMMENT 'Wie viele dieser Gebäude der Spieler
      besitzt.',
      `supply_progress` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Gesammelte Spenden für die
      nächste Aktivierung.',
      `active_runs` JSON NULL COMMENT 'Speichert aktive Produktions- oder Belohnungszyklen als
      JSON-Array.',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_player_house` (`user_id`, `house_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-----------------------------
X
ALTER TABLE `game_houses`
    ADD COLUMN `payout_cycle_hours` INT(11) NOT NULL DEFAULT 24 COMMENT 'Defines the payout cycle in
     hours for producer-type houses.' AFTER `production_duration_days`;

---------------------------------------
X
CREATE TABLE `player_message_filters` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL,
      `filter_key` varchar(50) NOT NULL,
      `is_active` tinyint(1) NOT NULL DEFAULT 1,
      PRIMARY KEY (`id`),
      UNIQUE KEY `user_filter_unique` (`user_id`,`filter_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-------------------------------
X ERLEDIGT!!!
CREATE TABLE `point_changelog` (
       `id` INT NOT NULL AUTO_INCREMENT,
       `user_id` INT NOT NULL,
       `point_type` VARCHAR(50) NOT NULL,
       `change_amount` DECIMAL(16, 4) NOT NULL,
       `old_value` DECIMAL(16, 4) NOT NULL,
       `new_value` DECIMAL(16, 4) NOT NULL,
       `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`),
      INDEX `idx_user_type` (`user_id`, `point_type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--------------------------------------
X ERLEDIGT!!!
  DELIMITER $$
    
     CREATE TRIGGER `trg_after_player_reputation_update`
     AFTER UPDATE ON `game_players`
     FOR EACH ROW
     BEGIN
         IF OLD.reputation_points <> NEW.reputation_points THEN
             INSERT INTO point_changelog (user_id, point_type, change_amount, old_value, new_value)
             VALUES (OLD.user_id, 'reputation', NEW.reputation_points - OLD.reputation_points,
      OLD.reputation_points, NEW.reputation_points);
        END IF;
    END$$
   
    DELIMITER ;

---------------------------------------
X ERLEDIGT!!!
CREATE EVENT `evt_cleanup_point_changelog`
    ON SCHEDULE EVERY 1 DAY
    DO
      DELETE FROM `point_changelog` WHERE `timestamp` < NOW() - INTERVAL 14 DAY;

---------------------------------------------
X ERLEDIGT!!!
SET GLOBAL event_scheduler = ON;

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

  -- Fügt die neue Spalte für den Anforderungstyp hinzu.
     -- Da alle bisherigen Einträge Räume waren, wird 'room' als Standardwert gesetzt.
X
     ALTER TABLE `game_house_requirements`
       ADD COLUMN `required_item_type` ENUM('room', 'resource', 'furniture') NOT NULL DEFAULT 'room'
      AFTER `house_id`;
    
     -- Benennt die ID-Spalte um, damit sie generisch ist.
X
     ALTER TABLE `game_house_requirements`
       CHANGE COLUMN `required_room_id` `required_item_id` INT(11) NOT NULL;
    
    -- Entfernt den alten Foreign Key, der nur auf die 'game_rooms'-Tabelle zeigte.
    -- Ein neuer kann nicht hinzugefügt werden, da 'required_item_id' nun auf verschiedene Tabellen
      verweisen kann.
    -- Die Spiellogik wird die Datenintegrität sicherstellen.
X
    ALTER TABLE `game_house_requirements`
      DROP FOREIGN KEY `fk_housereqs_room`;
   
    -- Entfernt den alten Index, der zum Foreign Key gehörte.
X
    ALTER TABLE `game_house_requirements`
      DROP INDEX `fk_housereqs_room`;
   
    -- Aktualisiert den UNIQUE-Constraint, um die neue Typ-Spalte einzubeziehen und Duplikate zu
      verhindern.
X
    ALTER TABLE `game_house_requirements`
      DROP INDEX `house_room_unique`,
      ADD UNIQUE KEY `house_item_unique` (`house_id`, `required_item_id`, `required_item_type`);
   
    -- Setzt den Standardwert für alle bestehenden Zeilen auf 'room' und entfernt dann den Default
      für zukünftige Einträge.
X
    UPDATE `game_house_requirements` SET `required_item_type` = 'room' WHERE `required_item_type` =
      '';
    ALTER TABLE `game_house_requirements`
      ALTER COLUMN `required_item_type` DROP DEFAULT;

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

 XX
 -- Blaupausen für die übergeordneten Großprojekte
     CREATE TABLE `game_large_scale_projects` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `name` VARCHAR(150) NOT NULL,
       `description` TEXT,
       `image_path` VARCHAR(255) DEFAULT NULL,
       `final_building_id` INT(11) NOT NULL COMMENT 'FK zu game_buildings.id, das nach Abschluss erstellt wird',
       `is_active` TINYINT(1) NOT NULL DEFAULT 1,
       `sort_order` INT(11) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name_UNIQUE` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

   XX
    -- "Teile-Bibliothek": Wiederverwendbare Blaupausen für Gebäudeteile
    CREATE TABLE `game_large_scale_project_parts` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(150) NOT NULL,
      `description` TEXT,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name_UNIQUE` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
XX
 
    -- NEU: Verknüpfungstabelle, die festlegt, welche Teile zu welchem Projekt gehören
    CREATE TABLE `game_lsp_project_to_part_mapping` (
      `project_id` INT(11) NOT NULL,
      `part_id` INT(11) NOT NULL,
      PRIMARY KEY (`project_id`, `part_id`),
      KEY `fk_map_project_idx` (`project_id`),
      KEY `fk_map_part_idx` (`part_id`),
      CONSTRAINT `fk_map_project` FOREIGN KEY (`project_id`) REFERENCES `game_large_scale_projects`
      (`id`) ON DELETE CASCADE,
      CONSTRAINT `fk_map_part` FOREIGN KEY (`part_id`) REFERENCES `game_large_scale_project_parts`
      (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
X X 
    -- Definition der vielfältigen Kosten für jeden Gebäudeteil in der Bibliothek
    CREATE TABLE `game_large_scale_project_part_costs` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `part_id` INT(11) NOT NULL,
      `cost_type` ENUM('resource', 'room', 'house', 'item', 'taler', 'reputation', 'victory_points',
      'coins', 'population_standard', 'population_elite') NOT NULL,
      `cost_target_id` INT(11) DEFAULT NULL COMMENT 'FK zu game_resources.id, game_rooms.id etc.,
      NULL für Währungen',
      `required_amount` DECIMAL(20,4) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_cost_part_idx` (`part_id`),
      CONSTRAINT `fk_cost_part` FOREIGN KEY (`part_id`) REFERENCES `game_large_scale_project_parts`
      (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
X
    -- Speichert den individuellen Spendenfortschritt eines Spielers für jede einzelne Kostenart
    CREATE TABLE `player_lsp_donations` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `user_id` INT(11) NOT NULL,
      `part_cost_id` INT(11) NOT NULL,
      `donated_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_player_cost` (`user_id`,`part_cost_id`),
      KEY `fk_donation_cost_idx` (`part_cost_id`),
      CONSTRAINT `fk_donation_user` FOREIGN KEY (`user_id`) REFERENCES `spielegruppe_main`.`users`
      (`id`) ON DELETE CASCADE,
      CONSTRAINT `fk_donation_cost` FOREIGN KEY (`part_cost_id`) REFERENCES
      `game_large_scale_project_part_costs` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
X
    -- Speichert den Status der fertiggestellten Teile für jeden Spieler
    CREATE TABLE `player_lsp_parts_status` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `user_id` INT(11) NOT NULL,
      `part_id` INT(11) NOT NULL,
      `completed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_player_part` (`user_id`,`part_id`),
      KEY `fk_status_part_idx` (`part_id`),
      CONSTRAINT `fk_status_user` FOREIGN KEY (`user_id`) REFERENCES `spielegruppe_main`.`users`
      (`id`) ON DELETE CASCADE,
      CONSTRAINT `fk_status_part` FOREIGN KEY (`part_id`) REFERENCES
      `game_large_scale_project_parts` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---------------------------------
X
 ALTER TABLE `game_large_scale_project_part_costs`
     MODIFY COLUMN `cost_type` ENUM(
         'resource',
         'room',
         'house',
         'item',
         'taler',
         'reputation',
         'victory_points',
        'coins',
        'population_standard',
        'population_elite',
        'population_specific'
    ) NOT NULL;

-------------------------------------
X
INSERT INTO `game_settings` (`setting_key`, `setting_value`, `description`)
    VALUES ('special_population_capacity', '10', 'Globales Limit für die maximale Anzahl an
     speziellen Einwohnern (z.B. Alchemist, Schmied), die ein Spieler gleichzeitig besitzen kann.');
-------------------------------
X
Game_settings Einwohner 50

-------------------------------------
X
ALTER TABLE `game_furniture` MODIFY `cost_taler` DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

ALTER TABLE `game_furniture` MODIFY `cost_reputation` DECIMAL(10, 4) NOT NULL DEFAULT 0.0000;

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

INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Altar', 'Ein heiliger Tisch
       für Rituale und Opfergaben.', 110, 4.3, 6.5, 2700, 0, NULL);
      SET @last_id = LAST_INSERT_ID();
      INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 36, 42),
      (@last_id, 64, 55), (@last_id, 60, 28);
     
      -- Beet
      INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Beet', 'Ein gepflegtes Stück
       Erde mit Blumen oder Gemüse.', 50, 3.1, 1, 720, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 45, 25),
       (@last_id, 14, 41), (@last_id, 55, 33), (@last_id, 28, 58);
    
     -- Bett
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
     cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Bett', 'Ein einfaches
       Bettgestell aus Holz mit Matratze.', 70, 3.5, 2.5, 2280, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 4, 31),
       (@last_id, 25, 55), (@last_id, 6, 24);
    
     -- Brunnen
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Brunnen', 'Ein kunstvoller
       Brunnen, der Wasser speit.', 120, 4.4, 6, 3000, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 27, 59),
       (@last_id, 2, 48);
    
     -- Gemälde
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Gemälde', 'Ein Ölgemälde in
       einem prunkvollen Rahmen.', 80, 3.8, 3.5, 2100, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 62, 29),
      (@last_id, 96, 37), (@last_id, 29, 51);
    
     -- Gitter
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
      cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Gitter', 'Eine stabile Eisenstange oder ein Gitterelement.', 55, 3.1, 1.5, 1200, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 48, 46),
       (@last_id, 31, 34);
    
     -- Herd
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Herd', 'Ein gusseiserner Ofen
       zum Kochen und Heizen.', 95, 3.9, 4.5, 2400, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 22, 22),
       (@last_id, 54, 39), (@last_id, 65, 54);
    
     -- Kamin
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Kamin', 'Ein gemauerter Kamin,
       der Wärme und Atmosphäre spendet.', 115, 4.2, 6, 2880, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 63, 47),
       (@last_id, 68, 31), (@last_id, 30, 26);
    
     -- Kessel
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Kessel', 'Ein großer,
       bauchiger Topf aus Gusseisen.', 85, 3.6, 4, 1500, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 33, 52),
       (@last_id, 44, 28), (@last_id, 89, 41);
    
     -- Kristallspiegel
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Kristallspiegel', 'Ein
       facettierter Spiegel in einem vergoldeten, verzierten Rahmen.', 100, 4.0, 5, 2520, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 47, 44),
       (@last_id, 78, 23), (@last_id, 74, 38), (@last_id, 72, 30);
    
     -- Lampe
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Lampe', 'Eine Petroleumlampe,
       die ein warmes, beständiges Licht spendet.', 65, 3.4, 2, 1080, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 5, 43),
       (@last_id, 7, 29), (@last_id, 37, 35);
    
     -- Regal
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Regal', 'Ein einfaches
       Holzregal für Vorräte oder Geschirr.', 55, 0, 1.5, 900, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 3, 58),
       (@last_id, 85, 41);
    
     -- Schrank
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Schrank', 'Ein zweitüriger
       Holzschrank zur Aufbewahrung.', 85, 3.7, 3.5, 1800, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 66, 49),
      (@last_id, 51, 27);
    
     -- Sitzbank
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
      cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Sitzbank', 'Eine gepolsterte
       Bank, die Platz für mehrere Personen bietet.', 75, 3.6, 2.5, 1680, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 3, 36),
       (@last_id, 71, 22), (@last_id, 4, 48);
    
     -- Spiegel
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Spiegel', 'Ein schlichter
       Wandspiegel in einem einfachen Holzrahmen.', 60, 3.2, 1.5, 1320, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 75, 25),
       (@last_id, 7, 53);
    
     -- Stuhl
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Stuhl', 'Ein einfacher
       Holzstuhl mit vier Beinen und einer Lehne.', 0, 0, 1, 600, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 69, 44),
       (@last_id, 57, 30), (@last_id, 30, 39);
    
     -- Teich
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Teich', 'Ein kleiner,
       künstlich angelegter Teich, oft mit Fischen.', 90, 3.9, 4, 2700, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 27, 59),
       (@last_id, 16, 21), (@last_id, 34, 28);
    
     -- Tisch
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Tisch', 'Ein kleiner, robuster
       Holztisch für Mahlzeiten oder Arbeit.', 55, 0, 1.5, 1200, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 64, 40),
       (@last_id, 63, 33), (@last_id, 65, 47);
    
     -- Toilette
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Toilette', 'Ein einfaches
       Wasserklosett mit Spülkasten und Kette.', 90, 3.8, 3.5, 1980, 0, NULL);
     SET @last_id = LAST_INSERT_ID();
     INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 35, 56),
       (@last_id, 29, 24), (@last_id, 46, 20);
    
     -- Treppe
     INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
      cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Treppe', 'Eine einfache
       Konstruktion aus Holz oder Stein.', 55, 3.1, 1.5, 1020, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 83, 45),
       (@last_id, 84, 21), (@last_id, 85, 55);
   
    -- Tresen
    INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Tresen', 'Ein hoher, langer
       Tisch, der als Theke oder Rezeption dient.', 80, 3.7, 3, 1560, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 3, 58),
       (@last_id, 59, 42), (@last_id, 76, 25);
   
    -- Vorhang
    INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Vorhang', 'Ein blickdichter
       Stoffvorhang für das Fenster oder als Raumtrenner.', 0, 3.0, 1, 840, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 5, 33),
       (@last_id, 4, 51), (@last_id, 80, 29);
   
    -- Wand
    INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Wand', 'Ein grundlegendes,
       massives Bauelement.', 0, 0, 1, 600, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 28, 59),
       (@last_id, 8, 47), (@last_id, 2, 52);
   
    -- Wanne
    INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
      cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Wanne', 'Eine freistehende
       Badewanne aus Gusseisen mit Löwenfüßen.', 125, 4.5, 6.5, 2940, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 61, 28),
      (@last_id, 21, 49), (@last_id, 111, 36);
   
    -- Waschbecken
    INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Waschbecken', 'Ein an der Wand
       montiertes Keramikbecken mit Wasserhahn.', 70, 3.5, 2.5, 1920, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 35, 50),
       (@last_id, 49, 22), (@last_id, 61, 31), (@last_id, 6, 45);
   
    -- Zaun
    INSERT INTO game_furniture (name, description, cost_victory_points, cost_reputation,
       cost_taler, build_time_seconds, cost_coins, image_path) VALUES ('Zaun', 'Ein einfacher
       Holzzaun, der das Grundstück abgrenzt.', 55, 3.2, 1.5, 960, 0, NULL);
    SET @last_id = LAST_INSERT_ID();
    INSERT INTO game_furniture_costs (furniture_id, resource_id, amount) VALUES (@last_id, 34, 48),
       (@last_id, 81, 57), (@last_id, 88, 24);

-------------------------------
X
ALTER TABLE `game_rooms`
    MODIFY COLUMN `cost_reputation` DECIMAL(10, 4) NOT NULL DEFAULT '0.0000',
    MODIFY COLUMN `cost_taler` DECIMAL(10, 2) NOT NULL DEFAULT '0.00';

---------------------------------
XX
CREATE TABLE `game_timed_backgrounds` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `image_path` VARCHAR(255) NOT NULL,
       `display_area` VARCHAR(50) NOT NULL COMMENT 'z.B. global, hafen',
       `start_date` DATE NOT NULL,
       `end_date` DATE NOT NULL,
       `priority` INT(11) NOT NULL DEFAULT 0 COMMENT 'Höhere Zahlen haben eine höhere Priorität',
       `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (`id`),
      INDEX `idx_display_area_dates` (`display_area`, `start_date`, `end_date`)
    ) COMMENT='Speichert zeitlich gesteuerte Hintergrundbilder für verschiedene Bereiche.';

-----------------------------------------
X
  CREATE TABLE `timed_border_images` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `display_area` varchar(50) NOT NULL DEFAULT 'global',
       `image_path` varchar(255) NOT NULL,
       `border_slice_top` int(11) NOT NULL DEFAULT 100,
       `border_slice_right` int(11) NOT NULL DEFAULT 80,
      `border_slice_bottom` int(11) NOT NULL DEFAULT 100,
      `border_slice_left` int(11) NOT NULL DEFAULT 80,
      `start_date` varchar(5) NOT NULL COMMENT 'Format MM-DD',
      `end_date` varchar(5) NOT NULL COMMENT 'Format MM-DD',
      `priority` int(11) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
    -- --------------------------------------------------------
    -- Fügt die Standardeinstellungen für den Rahmen zur settings-Tabelle hinzu
    -- --------------------------------------------------------
X
    INSERT INTO `game_settings` (`setting_key`, `setting_value`, `description`) VALUES
    ('border_image_path', 'uploads/textures/rolle.png', 'Standard-Pfad zum Bild für den Fensterrahmen.'),
    ('border_slice_top', '150', 'Standard-Wert für border-image-slice (oben).'),
    ('border_slice_right', '80', 'Standard-Wert für border-image-slice (rechts).'),
    ('border_slice_bottom', '150', 'Standard-Wert für border-image-slice (unten).'),
    ('border_slice_left', '80', 'Standard-Wert für border-image-slice (links).');



------------------------------------------
X
INSERT INTO `game_settings` (`setting_key`, `setting_value`, `description`) VALUES
    ('heading_border_image_path', '', 'Pfad zum Rahmenbild für Überschriften (h1, h2 etc.).'),
    ('heading_border_slice_top', '150', 'Oberer Slice-Wert für den Überschriften-Rahmen.'),
    ('heading_border_slice_right', '80', 'Rechter Slice-Wert für den Überschriften-Rahmen.'),
    ('heading_border_slice_bottom', '150', 'Unterer Slice-Wert für den Überschriften-Rahmen.'),
    ('heading_border_slice_left', '80', 'Linker Slice-Wert für den Überschriften-Rahmen.')
    ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;

-------------------------------
X
ALTER TABLE `game_large_scale_projects`
    CHANGE COLUMN `final_building_id` `final_building_name` VARCHAR(150) NOT NULL;

-------------------------
X
CREATE TABLE `player_artworks` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL,
      `image_path` varchar(255) NOT NULL,
      `title` varchar(150) NOT NULL,
      `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      KEY `idx_user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---------------------------------
X
CREATE TABLE IF NOT EXISTS `player_museum_slots` (
         `id` INT(11) NOT NULL AUTO_INCREMENT,
         `user_id` INT(11) NOT NULL,
         `slot_position` INT(11) NOT NULL,
         `artwork_id` INT(11) NULL DEFAULT NULL,
         `assigned_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`),
         UNIQUE KEY `uq_user_slot` (`user_id`, `slot_position`),
         KEY `fk_museum_slot_user` (`user_id`),
        KEY `fk_museum_slot_artwork` (`artwork_id`),
        CONSTRAINT `fk_museum_slot_user` FOREIGN KEY (`user_id`) REFERENCES `game_players` (`user_id`) ON
      DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT `fk_museum_slot_artwork` FOREIGN KEY (`artwork_id`) REFERENCES `player_artworks` (`id`) ON
      DELETE SET NULL ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

----------------------------
X
INSERT INTO `game_settings` (`setting_key`, `setting_value`, `description`)
    VALUES ('MUSEUM_SLOT_COUNT', '5', 'Anzahl der verfügbaren Ausstellungsplätze im Spielermuseum.')
    ON DUPLICATE KEY UPDATE
        `setting_value` = VALUES(`setting_value`),
        `description` = VALUES(`description`);

----------------------------------------------------
X
ALTER TABLE `game_building_levels`
    ADD COLUMN `bonus_museum_slots` INT(11) NOT NULL DEFAULT 0 COMMENT 'Anzahl der Museumsplätze, die dieses
     Gebäudelevel hinzufügt' AFTER `population_bonus`;

------------------------------
X
CREATE TABLE `artwork_ratings` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `artwork_id` int(11) NOT NULL,
       `user_id` int(11) NOT NULL,
       `rating` tinyint(2) NOT NULL COMMENT 'Bewertung von 1 bis 10',
       `comment` text DEFAULT NULL,
       `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
       PRIMARY KEY (`id`),
       UNIQUE KEY `uq_artwork_user_rating` (`artwork_id`,`user_id`),
      KEY `idx_user_id` (`user_id`),
      CONSTRAINT `fk_rating_artwork_id` FOREIGN KEY (`artwork_id`) REFERENCES `player_artworks` (`id`) ON
      DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-------------------------
X
ALTER TABLE `wandelndes_dorf_game`.`game_players` ADD COLUMN `kreativpunkte` INT NOT NULL DEFAULT 1;

-----------------------
X
UPDATE `wandelndes_dorf_game`.`game_players` SET `kreativpunkte` = 0;
    ALTER TABLE `wandelndes_dorf_game_test`.`game_players` CHANGE COLUMN `kreativpunkte` `kreativpunkte` INT
     NOT NULL DEFAULT 0;

-----------------------------
XX
CREATE TABLE `game_advent_calendar_prizes` (
      `day` tinyint(2) UNSIGNED NOT NULL,
      `prize_type` enum('ruf','siegpunkte','kreativpunkte','tier','muenzen','taler') NOT NULL,
      `prize_value` varchar(255) NOT NULL,
      `prize_name` varchar(100) NOT NULL,
      PRIMARY KEY (`day`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  Tabelle zum Speichern der geöffneten Türchen:
X
    CREATE TABLE `player_advent_calendar_claims` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL,
      `day` tinyint(2) UNSIGNED NOT NULL,
      `claimed_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      UNIQUE KEY `user_day_unique` (`user_id`,`day`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---------------------------------------
X
 CREATE TABLE `game_exhibitions` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(150) NOT NULL,
       `description` text DEFAULT NULL,
       `start_time` datetime NOT NULL,
       `end_time` datetime NOT NULL,
       `is_active` tinyint(1) NOT NULL DEFAULT 1,
       `disable_standard_creation` tinyint(1) NOT NULL DEFAULT 0,
       PRIMARY KEY (`id`),
      KEY `idx_active_times` (`is_active`,`start_time`,`end_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  Tabelle für Ausstellungsthemen:
X
     CREATE TABLE `game_exhibition_themes` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `exhibition_id` int(11) NOT NULL,
       `title` varchar(255) NOT NULL,
       `description` text DEFAULT NULL,
       `sort_order` int(11) NOT NULL DEFAULT 0,
       PRIMARY KEY (`id`),
       KEY `fk_theme_exhibition` (`exhibition_id`),
       CONSTRAINT `fk_theme_exhibition` FOREIGN KEY (`exhibition_id`) REFERENCES `game_exhibitions` (`id`) ON
      DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

  Tabelle für Einreichungen zu den Themen:
X
     CREATE TABLE `game_exhibition_submissions` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `theme_id` int(11) NOT NULL,
       `user_id` int(11) NOT NULL,
       `artwork_id` int(11) DEFAULT NULL,
       `submission_title` varchar(255) NOT NULL,
       `submission_format` varchar(50) NOT NULL,
       `submission_text` text NOT NULL,
       `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      UNIQUE KEY `uq_user_theme` (`user_id`,`theme_id`),
      KEY `fk_submission_theme` (`theme_id`),
      KEY `fk_submission_artwork` (`artwork_id`),
      CONSTRAINT `fk_submission_artwork` FOREIGN KEY (`artwork_id`) REFERENCES `player_artworks` (`id`) ON
      DELETE SET NULL,
      CONSTRAINT `fk_submission_theme` FOREIGN KEY (`theme_id`) REFERENCES `game_exhibition_themes` (`id`) ON
      DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;