-- ============================================================
-- KOL Manager — Migration v3
-- Run AFTER schema.sql on existing installs
-- Adds: login_attempts, audit_log, password_resets
-- ============================================================

SET NAMES utf8mb4;

-- ── Login Attempts (rate limiting) ────────────────────────────
CREATE TABLE IF NOT EXISTS login_attempts (
  id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  ip            VARCHAR(45)   NOT NULL,
  un            VARCHAR(50)   NOT NULL DEFAULT '',
  succeeded     TINYINT(1)    NOT NULL DEFAULT 0,
  attempted_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_ip_time (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Audit Log ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_log (
  id           INT UNSIGNED  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(100)  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 (user_id),
  INDEX idx_action (action),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Password Resets ───────────────────────────────────────────
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 (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;