-- 006_users.sql
--
-- Multi-user authentication.
--
-- Until this migration the app had no login at all: anyone who knew the URL
-- could read every audit, and the POST endpoints that queue crawls and mark
-- findings fixed accepted any request carrying a session cookie the server
-- itself had just handed out. A `noindex` meta tag is not access control.
--
-- Three tables, no ALTERs on existing tables, so this is safe to run against a
-- populated database and safe to re-run.
--
--   app_user     — the accounts themselves
--   auth_event   — an append-only log of logins, failures and lockouts
--   user_pref    — per-user UI state (theme, saved filters), so preferences
--                  survive a browser change instead of living only in
--                  localStorage
--
-- No seed row is created on purpose. Shipping a default admin password is how
-- installations end up with admin/admin in production six months later. The
-- app detects an empty app_user table and shows a one-time "create the first
-- administrator" screen instead.

CREATE TABLE IF NOT EXISTS app_user (
  id                   INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  email                VARCHAR(190)     NOT NULL,
  name                 VARCHAR(120)     NOT NULL,
  password_hash        VARCHAR(255)     NOT NULL,

  -- Three roles, deliberately few. A fourth role is a permission system, and a
  -- permission system for a four-person team is a maintenance cost with no
  -- reader.
  --   admin   — everything, including managing users
  --   analyst — run audits, mark findings fixed, connect integrations
  --   viewer  — read only; every mutating endpoint returns 403
  role                 ENUM('admin','analyst','viewer') NOT NULL DEFAULT 'viewer',

  is_active            TINYINT(1)       NOT NULL DEFAULT 1,
  must_change_password TINYINT(1)       NOT NULL DEFAULT 0,

  -- Lockout state lives on the row rather than in the session, so clearing
  -- cookies does not clear the lockout.
  failed_attempts      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until         DATETIME         NULL,

  last_login_at        DATETIME         NULL,
  last_login_ip        VARCHAR(45)      NULL,

  created_at           TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_user_email (email),
  KEY idx_user_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Append-only. Never updated, never deleted by the app. If somebody is
-- grinding passwords this table is the only place that fact is written down.
CREATE TABLE IF NOT EXISTS auth_event (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED    NULL,
  email      VARCHAR(190)    NULL COMMENT 'recorded even when no such user exists, so typo-vs-attack is distinguishable',
  event      ENUM('login','logout','failed','locked','created','updated','disabled','password_changed') NOT NULL,
  ip         VARCHAR(45)     NULL,
  user_agent VARCHAR(255)    NULL,
  detail     VARCHAR(255)    NULL,
  created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_auth_event_user (user_id, created_at),
  KEY idx_auth_event_ip   (ip, created_at),
  KEY idx_auth_event_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Small key/value store for UI state. Theme still writes to localStorage for a
-- flash-free first paint, but it is mirrored here so a user who logs in on a
-- second machine gets their own settings rather than the defaults.
CREATE TABLE IF NOT EXISTS user_pref (
  user_id    INT UNSIGNED NOT NULL,
  pref_key   VARCHAR(60)  NOT NULL,
  pref_value VARCHAR(500) NOT NULL,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, pref_key),
  CONSTRAINT fk_pref_user FOREIGN KEY (user_id) REFERENCES app_user (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;