BEGIN;
CREATE TABLE IF NOT EXISTS board_pairings (
  code CHAR(4) PRIMARY KEY CHECK (code ~ '^[0-9]{4}$'),
  token_hash CHAR(64) NOT NULL UNIQUE,
  device_info JSONB NOT NULL DEFAULT '{}',
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS board_pairings_expiry ON board_pairings(expires_at);
CREATE TABLE IF NOT EXISTS board_devices (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city_id INTEGER NOT NULL REFERENCES cities(id) ON DELETE RESTRICT,
  stop_id INTEGER NOT NULL REFERENCES stops(id) ON DELETE RESTRICT,
  token_hash CHAR(64) NOT NULL UNIQUE,
  settings JSONB NOT NULL,
  revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
  applied_revision INTEGER NOT NULL DEFAULT 0 CHECK (applied_revision >= 0 AND applied_revision <= revision),
  last_seen_at TIMESTAMPTZ,
  device_info JSONB NOT NULL DEFAULT '{}',
  created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS board_devices_city ON board_devices(city_id, created_at);
COMMIT;