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