CREATE TABLE admin_security (
  admin_id uuid PRIMARY KEY REFERENCES admins(id) ON DELETE CASCADE,
  email text NOT NULL DEFAULT '',
  login_notifications boolean NOT NULL DEFAULT false,
  totp_secret text,
  pending_secret text,
  pending_expires timestamptz,
  last_counter bigint NOT NULL DEFAULT -1,
  recovery_hashes jsonb NOT NULL DEFAULT '[]'
);
CREATE TABLE login_challenges (
  token text PRIMARY KEY,
  admin_id uuid NOT NULL REFERENCES admins(id) ON DELETE CASCADE,
  expires timestamptz NOT NULL,
  attempts integer NOT NULL DEFAULT 0,
  context jsonb NOT NULL
);
CREATE TABLE mail_config (
  id integer PRIMARY KEY CHECK (id = 1),
  data jsonb NOT NULL
);
CREATE TABLE login_events (
  id bigserial PRIMARY KEY,
  admin_id uuid NOT NULL REFERENCES admins(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  context jsonb NOT NULL,
  factor text NOT NULL,
  mail_status text NOT NULL DEFAULT 'disabled'
);
CREATE INDEX login_events_admin_time ON login_events(admin_id, created_at DESC);
CREATE INDEX login_challenges_expires ON login_challenges(expires);