-- Run this on existing installs to add tables that were missing from schema v3
-- Safe to run multiple times (uses CREATE TABLE IF NOT EXISTS)

CREATE TABLE IF NOT EXISTS password_resets (
  token       CHAR(64)      NOT NULL,
  user_id     CHAR(36)      NOT NULL,
  expires_at  DATETIME      NOT NULL,
  used        TINYINT(1)    NOT NULL DEFAULT 0,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (token),
  INDEX idx_user_id (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id          INT           NOT NULL AUTO_INCREMENT,
  user_id     CHAR(36)      DEFAULT NULL,
  user_un     VARCHAR(50)   DEFAULT NULL,
  action      VARCHAR(100)  NOT NULL,
  entity_type VARCHAR(50)   DEFAULT NULL,
  entity_id   VARCHAR(36)   DEFAULT NULL,
  detail      TEXT          DEFAULT NULL,
  ip          VARCHAR(45)   DEFAULT NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_user_id (user_id),
  INDEX idx_action (action),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id            INT           NOT NULL AUTO_INCREMENT,
  ip            VARCHAR(45)   NOT NULL,
  un            VARCHAR(50)   DEFAULT '',
  succeeded     TINYINT(1)    NOT NULL DEFAULT 0,
  attempted_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_ip (ip),
  INDEX idx_at (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;