-- Saved-coordinate book (wdcoordinates_edit equivalent).
CREATE TABLE empyrion_coords (
  id           BIGSERIAL PRIMARY KEY,
  instance_id  TEXT NOT NULL,
  name         TEXT NOT NULL,
  playfield    TEXT NOT NULL,
  x            DOUBLE PRECISION NOT NULL,
  y            DOUBLE PRECISION NOT NULL,
  z            DOUBLE PRECISION NOT NULL,
  notes        TEXT,
  created_by   TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (instance_id, name)
);
CREATE INDEX idx_empyrion_coords_instance ON empyrion_coords(instance_id);

-- Player-warning log (wdwarnings + ctrlplayerwarnings equivalent).
-- Append-only history of admin- or auto-issued warnings.
CREATE TABLE empyrion_warnings (
  id           BIGSERIAL PRIMARY KEY,
  instance_id  TEXT NOT NULL,
  player_id    INT NOT NULL,
  player_name  TEXT,
  reason       TEXT NOT NULL,
  given_by     TEXT,
  auto_rule    TEXT,
  given_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_empyrion_warnings_player ON empyrion_warnings(instance_id, player_id, given_at DESC);

-- Configurable warning rules (CEL-driven, matches scheduler_event.go's CEL env).
CREATE TABLE empyrion_warning_rules (
  id              TEXT PRIMARY KEY,
  instance_id     TEXT,         -- NULL = applies to all empyrion instances
  description     TEXT NOT NULL DEFAULT '',
  cel             TEXT NOT NULL,
  warn_message    TEXT NOT NULL,
  threshold       INT NOT NULL DEFAULT 3,
  threshold_action TEXT NOT NULL DEFAULT 'kick',  -- kick | ban | none
  enabled         BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);