-- ===========================================================================
-- Migration: Personal API tokens
-- Date: 2026-07-30
--
-- Long-lived bearer tokens a user creates to call the API programmatically
-- (scripts, integrations). A token authenticates AS its owning user — same
-- farm, same role/permissions — and carries the prefix 'pfmis_pat_' so the
-- auth layer can tell it apart from a session token. Only the SHA-256 hash is
-- stored; the plaintext is shown once at creation. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS api_tokens (
  id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id      INT UNSIGNED  NOT NULL,
  name         VARCHAR(120)  NOT NULL,          -- what it's for ("Backup script")
  token_hash   CHAR(64)      NOT NULL,          -- sha256 of the plaintext token
  prefix       VARCHAR(20)   NOT NULL,          -- first chars, shown for identification
  last_used_at TIMESTAMP     NULL,
  expires_at   TIMESTAMP     NULL,              -- NULL = never expires
  created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_apitoken_hash (token_hash),
  KEY idx_apitoken_user (user_id),
  CONSTRAINT fk_apitoken_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;