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

// Drizzle schema for the game state. Column names/types here are the source of
// truth; the matching CREATE TABLE statements live in lib/db/client.ts (DDL) and
// must stay in sync with this file.

export const planes = sqliteTable('planes', {
  id: text('id').primaryKey().notNull(),
  model: text('model').notNull(),
  // v0.39.0 (ISS-069): 'repairing' = grounded in the shop until repairUntilSimTime passes; the
  // engine flips it back to 'available' on a tick. All status guards key off 'available'.
  status: text('status', { enum: ['available', 'flying', 'repairing'] }).notNull(),
  fuel: real('fuel').notNull(),
  maxFuel: real('max_fuel').notNull(),
  locationIcao: text('location_icao').notNull(),
  // Current flight destination (v0.12.0) — set by fly, cleared on arrival. The
  // plane's origin is always its locationIcao while flying.
  destIcao: text('dest_icao'),
  lat: real('lat').notNull(),
  lon: real('lon').notNull(),
  // Sim-clock departure time of the current flight (null when available). While
  // flying, `fuel` is frozen at its departure value — live position/fuel are pure
  // functions of the clock (lib/sim/movement.ts), recomputed on read.
  departureSimTime: integer('departure_sim_time'),
  // Queue of upcoming legs NOT yet started (v0.25.0, ISS-043) — ordered JSON array
  // of ICAO strings ('[]' when empty). The ACTIVE leg stays destIcao +
  // departureSimTime; on landing the engine auto-chains queue[0] if fuel covers it.
  queue: text('queue').notNull().default('[]'),
  // Structural health in [0, 100] (v0.39.0, ISS-069) — wears down by WEAR_PER_KM per km of
  // landed legs; at 0 the plane is broken and cannot fly until repaired (v0.45.0, ISS-089).
  health: real('health').notNull().default(100),
  // Sim-clock epoch ms when an active repair finishes (v0.39.0, ISS-069) — set with status
  // 'repairing', cleared by the engine's flip-back pass; NULL = not in repair.
  repairUntilSimTime: integer('repair_until_sim_time'),
});

// Cargo packages (v0.12.0) replace the old contracts table end-to-end: a package
// is one cargo unit origin → dest with a reward and deadline. 'loaded' = bound to
// a plane's slot; it stays bound ('expired') or pays out ('delivered') when the
// plane arrives at its destination airport.
export const packages = sqliteTable('packages', {
  id: text('id').primaryKey().notNull(),
  originIcao: text('origin_icao').notNull(),
  destIcao: text('dest_icao').notNull(),
  reward: real('reward').notNull(),
  cargoType: text('cargo_type').notNull(),
  cargoCount: integer('cargo_count').notNull(),
  // Package weight in kg (v0.27.0, ISS-045) — drives load validation (plane payload)
  // and the per-kg part of the payout. Pre-existing rows backfill to the default.
  weightKg: real('weight_kg').notNull().default(2000),
  // Special status (v0.37.0, ISS-073) — key into lib/game/specials.ts SPECIALS; NULL = none.
  special: text('special'),
  deadlineSimTime: integer('deadline_sim_time').notNull(),
  // 'expired' = an open OR loaded package whose deadline passed on a tick. Loaded
  // expired packages keep occupying their slot, deliver no reward, and abandon free.
  status: text('status', { enum: ['open', 'loaded', 'delivered', 'expired', 'abandoned'] }).notNull(),
  planeId: text('plane_id'),
  createdSimTime: integer('created_sim_time').notNull(),
  // Real-time (Date.now()) epoch ms of creation (v0.65.0) — open jobs live on the WALL clock, not the sim
  // clock: they expire JOB_LIFETIME_MS after this regardless of time scale or fleet state. Legacy rows
  // backfill to seed time so they get a fresh full lifetime instead of expiring on the first tick.
  createdRealMs: integer('created_real_ms').notNull().default(0),
  // Real-time epoch ms when this (origin, band) slot was last taken — loaded (v0.65.0). The pool
  // spawner holds that slot for the band's refill delay after; NULL = never taken / no cooldown. Expired open
  // jobs set nothing: an untaken job's slot respawns immediately.
  slotFreeAtRealMs: integer('slot_free_at_real_ms'),
});

export const account = sqliteTable('account', {
  cash: real('cash').notNull(),
  reputation: real('reputation').notNull(),
});

export const simState = sqliteTable('sim_state', {
  clockMs: integer('clock_ms').notNull(),
  timeScale: real('time_scale').notNull(),
  paused: integer('paused', { mode: 'boolean' }).notNull(),
  // Real-time (Date.now()) of the last tick — the tick-on-read engine advances
  // clockMs by (now - lastRealTickMs) × timeScale, clamped per tick.
  lastRealTickMs: integer('last_real_tick_ms').notNull(),
  // Last MANUALLY selected speed (v0.30.0, ISS-057) — landings slow the sim to 1× without
  // touching this; flyPlane restores it when the last grounded plane takes off. Written by
  // POST /api/sim whenever timeScale is posted; pre-existing rows backfill to time_scale.
  lastSelectedTimeScale: real('last_selected_time_scale').notNull().default(1),
});

// v0.71.0: accounts + login sessions for the admin panel (and later multiplayer auth).
// The seeded admin row is inserted by lib/db/client.ts on every open() — hash-only, see there.
export const users = sqliteTable('users', {
  id: text('id').primaryKey().notNull(),
  username: text('username').notNull().unique(),
  // scrypt digest as "salt$hex" (16-byte hex salt, 32-byte hex hash) — verified with node:crypto.
  passwordHash: text('password_hash').notNull(),
  admin: integer('admin', { mode: 'boolean' }).notNull().default(false),
  createdAt: integer('created_at').notNull(),
});

export const sessions = sqliteTable('sessions', {
  id: text('id').primaryKey().notNull(),
  userId: text('user_id').notNull(),
  // Real-time (Date.now()) epoch ms — 7 days from creation; expired rows are pruned lazily on login.
  expiresAt: integer('expires_at').notNull(),
});

export type PlaneRow = typeof planes.$inferSelect;
export type PackageRow = typeof packages.$inferSelect;
export type AccountRow = typeof account.$inferSelect;
export type SimStateRow = typeof simState.$inferSelect;
export type UserRow = typeof users.$inferSelect;
export type SessionRow = typeof sessions.$inferSelect;