erd 수정본
테이블 (19)
ACHIEVEMENTS
- achievement_id — VARCHAR(250) — 기본 키 — NOT NULL
- achievement_name — VARCHAR(100) — NOT NULL
- description — VARCHAR(255) — NOT NULL
- condition_type — VARCHAR(50) — NOT NULL
- condition_value — INT — NOT NULL
- condition_weather — VARCHAR(30)
ADMIN
- admin_id — VARCHAR(250) — 기본 키 — NOT NULL
- admin_name — VARCHAR(50) — NOT NULL
- email — VARCHAR(100) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- role — VARCHAR(30) — NOT NULL
- is_active — TINYINT(1) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- last_login_at — TIMESTAMP
ADMIN_LOGS
- log_id — VARCHAR(250) — 기본 키 — NOT NULL
- admin_id — VARCHAR(250) — 외래 키 → ADMIN.admin_id — NOT NULL
- action — VARCHAR(50) — NOT NULL
- target_table — VARCHAR(50) — NOT NULL
- target_id — VARCHAR(50) — NOT NULL
- before_value — TEXT
- after_value — TEXT
- reason — VARCHAR(255)
- created_at — TIMESTAMP — NOT NULL
- INDEX — idx_alog_admin
- INDEX — idx_alog_table
- INDEX — idx_alog_created
BOT_ACTION_LOGS
- Bot_id — VARCHAR(250) — 기본 키 — NOT NULL
- session_id (session_id2) — VARCHAR(250) — 외래 키 → GAME_SESSIONS.session_id — NOT NULL
- session_player_id — VARCHAR(250) — NOT NULL
- session_id — VARCHAR(250) — NOT NULL
- priority_level — INT — NOT NULL
- action_type — VARCHAR(30) — NOT NULL
- target_id — VARCHAR(250)
- created_at — TIMESTAMP — NOT NULL
CHARACTER_TYPES
- character_type_id — VARCHAR(250) — 기본 키 — NOT NULL
- type_name — VARCHAR(20) — NOT NULL
- collect_speed_mult — FLOAT — NOT NULL
- inventory_slots — INT — NOT NULL
- plant_speed_mult — FLOAT — NOT NULL
- plant_growth_mult — FLOAT — NOT NULL
- gold_gain_mult — FLOAT — NOT NULL
- battery_drain_mult — FLOAT — NOT NULL
- move_speed_mult — FLOAT — NOT NULL
COMMON_CODES
- code_no — VARCHAR(250) — 기본 키 — NOT NULL
- category — VARCHAR(50) — NOT NULL
- code_name — VARCHAR(100) — NOT NULL
- INDEX — idx_category
ENVIRONMENTS
- environment_id — VARCHAR(250) — 기본 키 — NOT NULL
- weather_name — VARCHAR(50) — NOT NULL
- description — VARCHAR(255)
- battery_drain_multiplier — FLOAT — NOT NULL
GAME_SESSIONS
- session_id — VARCHAR(250) — 기본 키 — NOT NULL
- host_player_id (player_id) — VARCHAR(250) — 외래 키 → PLAYER.player_id — NOT NULL
- player_count — INT — NOT NULL
- map_seed — INT — NOT NULL
- status — VARCHAR(20) — NOT NULL
- current_round — INT — NOT NULL
- current_pollution_level — FLOAT — NOT NULL
- game_over_reason — VARCHAR(50)
- created_at — TIMESTAMP — NOT NULL
- started_at — TIMESTAMP
- ended_at — TIMESTAMP
INVENTORY
- inventory_id — VARCHAR(250) — 기본 키 — NOT NULL
- session_player_id — VARCHAR(250) — 외래 키 → SESSION_PLAYERS.session_player_id — NOT NULL
- item_name — VARCHAR(50) — 외래 키 → ITEMS.item_name — NOT NULL
- quantity — INT — NOT NULL
- acquired_date — TIMESTAMP — NOT NULL
ITEMS
- item_id — VARCHAR(250) — 기본 키 — NOT NULL
- item_name — VARCHAR(50) — NOT NULL
- item_type — VARCHAR(20) — NOT NULL
- base_price — INT — NOT NULL
- buy_price — INT — NOT NULL
- is_sellable — TINYINT(1) — NOT NULL
- is_buyable — TINYINT(1) — NOT NULL
- INDEX — idx_item_type
LARGE_WASTE_OBJECTS
- waste_id — VARCHAR(250) — 기본 키 — NOT NULL
- pos_x — FLOAT — NOT NULL
- pos_y — FLOAT — NOT NULL
- is_destroyed — TINYINT(1) — NOT NULL
- destroyed_at — TIMESTAMP
- INDEX — idx_waste_session
MAPS
- map_id — VARCHAR(250) — 기본 키 — NOT NULL
- session_id — VARCHAR(250) — 외래 키 → GAME_SESSIONS.session_id — NOT NULL
- map_seed — INT — NOT NULL
- active_range — INT — NOT NULL
- pollution_level — FLOAT — NOT NULL
- tree_count — INT — NOT NULL
- picked_trash_count — INT — NOT NULL
- trash_spawn_interval — FLOAT — NOT NULL
- max_trash_count — INT — NOT NULL
- player_count_modifier — FLOAT — NOT NULL
- elapsed_days — INT — NOT NULL
NPCS
- npc_id — VARCHAR(250) — 기본 키 — NOT NULL
- npc_name — VARCHAR(50) — NOT NULL
- zone_type — VARCHAR(30) — NOT NULL
- pos_x — FLOAT — NOT NULL
- pos_y — FLOAT — NOT NULL
NPC_INTERACTIONS
- interaction_id — VARCHAR(250) — 기본 키 — NOT NULL
- npc_id — VARCHAR(250) — 외래 키 → NPCS.npc_id — NOT NULL
- session_player_id — VARCHAR(250) — 외래 키 → SESSION_PLAYERS.session_player_id — NOT NULL
- session_id — VARCHAR(250) — 외래 키 → GAME_SESSIONS.session_id — NOT NULL
- item_name — VARCHAR(50)
- ai_response — TEXT
- created_at — TIMESTAMP — NOT NULL
- INDEX — idx_ni_session
- INDEX — idx_ni_created
PLAYER
- player_id — VARCHAR(250) — 기본 키 — NOT NULL
- player_name — VARCHAR(50) — NOT NULL
- email — VARCHAR(100) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- tutorial_completed — TINYINT(1) — NOT NULL
- tutorial_last_step — INT — NOT NULL
- created_at — TIMESTAMP — NOT NULL
PLAYER_ACHIEVEMENTS
- player_achievement_id — VARCHAR(250) — 기본 키 — NOT NULL
- player_id — VARCHAR(250) — 외래 키 → PLAYER.player_id — NOT NULL
- achievement_id — VARCHAR(250) — 외래 키 → ACHIEVEMENTS.achievement_id — NOT NULL
- achieved_at — TIMESTAMP — NOT NULL
ROUND_RESULTS
- round_result_id — VARCHAR(250) — 기본 키 — NOT NULL
- round_number — INT — NOT NULL
- total_trash_collected — INT — NOT NULL
- total_trees_planted — INT — NOT NULL
- total_large_waste_removed — INT — NOT NULL
- pollution_reduced — FLOAT — NOT NULL
- quota_achieved — TINYINT(1) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
SESSION_PLAYERS
- session_player_id — VARCHAR(250) — 기본 키 — NOT NULL
- player_id — VARCHAR(250) — 외래 키 → PLAYER.player_id
- character_type_id — VARCHAR(250) — 외래 키 → CHARACTER_TYPES.character_type_id — NOT NULL
- session_id — VARCHAR(250) — 외래 키 → GAME_SESSIONS.session_id — NOT NULL
- Bot_id — VARCHAR(250) — 외래 키 → BOT_ACTION_LOGS.Bot_id — NOT NULL
- is_bot — TINYINT(1) — NOT NULL
- is_ready — TINYINT(1) — NOT NULL
- current_battery — FLOAT — NOT NULL
- gold — INT — NOT NULL
- pos_x — FLOAT — NOT NULL
- pos_y — FLOAT — NOT NULL
- is_knocked_out — TINYINT(1) — NOT NULL
- total_trash_collected — INT — NOT NULL
- total_trees_planted — INT — NOT NULL
- total_large_waste_removed — INT — NOT NULL
- total_gold_earned — INT — NOT NULL
TRADE_LOGS
- trade_id — VARCHAR(250) — 기본 키 — NOT NULL
- session_id — VARCHAR(250) — 외래 키 → GAME_SESSIONS.session_id — NOT NULL
- player_id — VARCHAR(250) — 외래 키 → PLAYER.player_id — NOT NULL
- npc_id — VARCHAR(250) — 외래 키 → NPCS.npc_id — NOT NULL
- trade_type — VARCHAR(10) — NOT NULL
- item_name — VARCHAR(50) — NOT NULL
- quantity — INT — NOT NULL
- gold_amount — INT — NOT NULL
- gold_after — INT — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- INDEX — idx_trade_session_user
- INDEX — idx_trade_created