SpaceWanderer/lib/db/schema.ts

import { integer, primaryKey, real, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core'

// Drizzle schema for game state. Single-player by design: exactly ONE save row ever exists
// (no auth/accounts), so every table hangs off saves.id but only one save is in play.
// Column names/types here are the source of truth; matching CREATE TABLE statements live
// in lib/db/client.ts (DDL) and must stay in sync with this file.

export const saves = sqliteTable('saves', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  // Seed for the deterministic world generator — same seed always regenerates the same world.
  seed: text('seed').notNull(),
  turn: integer('turn').notNull().default(0),
  createdAt: text('created_at').notNull(),
  // Which schema version this save was last brought up to (NULL on saves from before the
  // column existed = v1). ensureSave runs ordered idempotent backfill steps for anything below.
  schemaVersion: integer('schema_version'),
})

export const solarSystems = sqliteTable('solar_systems', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  name: text('name').notNull(),
  // 0–100 coordinate space; seeds the force-directed WORLD MAP layout (components/world-map.tsx).
  x: real('x').notNull(),
  y: real('y').notNull(),
  discovered: integer('discovered', { mode: 'boolean' }).notNull().default(false),
  isCurrent: integer('is_current', { mode: 'boolean' }).notNull().default(false),
})

// Jump routes, stored as directed pairs (both directions of each route) so adjacency and
// "reachable from current" are single-row lookups. Read side dedupes for display.
export const jumpRoutes = sqliteTable(
  'jump_routes',
  {
    fromSystem: integer('from_system').notNull().references(() => solarSystems.id),
    toSystem: integer('to_system').notNull().references(() => solarSystems.id),
  },
  (t) => [primaryKey({ columns: [t.fromSystem, t.toSystem] })],
)

// Bodies of a solar system: planets (parent_id NULL) and moons (parent_id = their planet's
// id). Generated when the system is generated, so every system has 2–5 planets, some with
// 1–3 moons. Insertion order is display order — a moon always follows its parent row.
export const planets = sqliteTable('planets', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  systemId: integer('system_id').notNull().references(() => solarSystems.id),
  parentId: integer('parent_id'), // NULL for a planet, set for a moon
  name: text('name').notNull(),
  type: text('type').notNull(),
  hazard: integer('hazard', { mode: 'boolean' }).notNull().default(false),
  explored: integer('explored', { mode: 'boolean' }).notNull().default(false),
})

// Dumb-simple cargo hold on the save: what exploration has found so far. SHIP SYSTEMS (#8)
// will spend parts from here later; nothing consumes it yet.
export const inventoryItems = sqliteTable('inventory_items', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  kind: text('kind').notNull(), // 'resource' | 'part'
  name: text('name').notNull(),
  qty: integer('qty').notNull().default(1),
})

// The ship's crew roster: the starting crew (seeded on new game) plus members found while
// exploring planets/moons. Display-only for now — no dismiss/assign actions yet (#7 scope).
export const crew = sqliteTable('crew', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  name: text('name').notNull(),
  role: text('role').notNull(),
  traitGood: text('trait_good').notNull(),
  traitBad: text('trait_bad').notNull(),
  // Where the member came from: 'STARTING CREW' or '<planet>, <system>' for found members.
  origin: text('origin').notNull(),
})

// The ship's systems, one row per known type (see SHIP_TYPES in lib/game/ship-costs.ts).
// `built` is derived, not stored: level > 0 means built — unbuilt rows sit at level/capacity 0.
export const shipSystems = sqliteTable(
  'ship_systems',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    saveId: integer('save_id').notNull().references(() => saves.id),
    type: text('type').notNull(),
    level: integer('level').notNull().default(0), // 0 = not built yet
    capacity: integer('capacity').notNull().default(0),
  },
  (t) => [uniqueIndex('ship_systems_save_type_unique').on(t.saveId, t.type)],
)

// The technology tree, one row per known tech (see TECHS in lib/game/tech.ts). Definitions are
// seeded from the pure module on new game / backfill v5 — same pattern as ship_systems.
// `researched` is the per-save progress; prerequisites/effect are stored for completeness but
// the canonical semantics live in lib/game/tech.ts (EFFECTS).
export const tech = sqliteTable(
  'tech',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    saveId: integer('save_id').notNull().references(() => saves.id),
    slug: text('slug').notNull(), // stable key, e.g. WEAPONS_SYSTEMS
    name: text('name').notNull(), // display name, e.g. 'WEAPONS SYSTEMS'
    costKind: text('cost_kind').notNull(), // 'resource' | 'part'
    costQty: integer('cost_qty').notNull().default(1),
    prerequisites: text('prerequisites').notNull().default(''), // comma-separated tech slugs, '' when none
    effect: text('effect').notNull(), // effect slug — semantics in EFFECTS (lib/game/tech.ts)
    researched: integer('researched', { mode: 'boolean' }).notNull().default(false),
  },
  (t) => [uniqueIndex('tech_save_slug_unique').on(t.saveId, t.slug)],
)

// The mission board (#10): contracts generated deterministically from the save seed, one ACTIVE
// at a time. `type` drives completion semantics (see advanceMission in lib/game/missions.ts):
// DELIVER jumps to target_system_id, EXPLORE counts scans up to progress_target, FIND completes
// when a scan's haul contains condition_kind. The reward lands in inventory_items on completion.
export const missions = sqliteTable('missions', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  type: text('type').notNull(), // 'DELIVER' | 'EXPLORE' | 'FIND'
  description: text('description').notNull(), // human-readable objective, e.g. "Jump to Kepler Drift"
  targetSystemId: integer('target_system_id'), // DELIVER only — destination system (NULL otherwise)
  conditionKind: text('condition_kind'), // FIND only — 'resource' | 'part' (NULL otherwise)
  progressTarget: integer('progress_target').notNull().default(1), // EXPLORE: scans required; 1 for the others
  progress: integer('progress').notNull().default(0), // scans counted since acceptance; 0/1 for DELIVER/FIND
  rewardKind: text('reward_kind').notNull(), // 'resource' | 'part'
  rewardQty: integer('reward_qty').notNull().default(1),
  state: text('state').notNull().default('AVAILABLE'), // 'AVAILABLE' | 'ACTIVE' | 'DONE'
})

// Append-only game log. Writers so far: new-game + jump events; later issues add more
// writers and a read endpoint.
export const events = sqliteTable('events', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  turn: integer('turn').notNull(),
  type: text('type').notNull(),
  message: text('message').notNull(),
  createdAt: text('created_at').notNull(),
})

// Queued actions (PR A, spec §3): one row per task assigned to a team. `payload` is a JSON
// string whose shape depends on `type` (see the Task interface in lib/game/tasks.ts);
// `helpers` is a JSON array of crew ids. Upgraded saves start with an empty queue — the v7
// backfill stamps the version, the DDL in client.ts creates the table.
export const tasks = sqliteTable('tasks', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  type: text('type').notNull(), // TaskType
  payload: text('payload').notNull(),
  leadCrewId: integer('lead_crew_id').notNull().references(() => crew.id),
  helpers: text('helpers').notNull().default('[]'), // JSON array of crew ids
  duration: integer('duration').notNull(),
  remaining: integer('remaining').notNull(),
  status: text('status').notNull().default('ACTIVE'), // 'ACTIVE' | 'PENDING' | 'DONE'
  queuedAtTurn: integer('queued_at_turn').notNull().default(0),
})

// Per-member skill progression (PR B, spec §6): one row per (crew member, skill); seeded to
// STARTING_LEVELS[role][skill] by createNewGame / the v8 backfill; Task 13 adds xp + level-ups.
export const crewSkills = sqliteTable('crew_skills', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  saveId: integer('save_id').notNull().references(() => saves.id),
  crewId: integer('crew_id').notNull().references(() => crew.id),
  skill: text('skill').notNull(), // SkillName
  level: integer('level').notNull().default(0), // 0–5
  xp: integer('xp').notNull().default(0),
}, (t) => [uniqueIndex('crew_skills_crew_skill_unique').on(t.crewId, t.skill)])