Enhancement: New Effects database structure

[sum117]

We need to create a database structure to manage the effects applied to characters in our text-based game. These effects have a duration in turns and should be removed after the specified number of turns. Characters can also use items to remove the effects, and they can be afflicted by multiple effects simultaneously.

Proposed database structure:

1. Table characters:

   • id (INTEGER, PRIMARY KEY)
   • name (VARCHAR)
   • ... (other character information)

2. Table effects:

   • id (INTEGER, PRIMARY KEY)
   • name (VARCHAR)
   • description (VARCHAR)
   • duration (INTEGER) -- duration in turns

3. Table character_effects:

   • id (INTEGER, PRIMARY KEY)
   • character_id (INTEGER, FOREIGN KEY referencing characters(id))
   • effect_id (INTEGER, FOREIGN KEY referencing effects(id))
   • remaining_turns (INTEGER) -- remaining turns for effect duration

4. Table items:

   • id (INTEGER, PRIMARY KEY)
   • name (VARCHAR)
   • description (VARCHAR)
   • effect_id (INTEGER, FOREIGN KEY referencing effects(id)) -- ID of the effect that the item removes

Please implement this database structure and create the necessary tables, relationships, and constraints. Remember to update the character_effects table every turn by decreasing the remaining_turns value for each entry, and remove the entry when remaining_turns reaches 0.

When a character uses an item to remove an effect, find the corresponding entry in the character_effects table and remove it.

Let me know if you need more details or adjustments.