-- Schema for the Lead Pipeline Admin Portal.
--
-- Do NOT run this automatically. The owner runs it manually against the target
-- database after filling in config/config.php.
--
-- Safe to run on an empty database. Uses utf8mb4 throughout.

SET NAMES utf8mb4;

-- Staff and admin accounts. Login is by email. A row with status = 'inactive'
-- cannot sign in. must_change_password forces a password change on next login.
CREATE TABLE IF NOT EXISTS users (
  id                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  name                  VARCHAR(120)    NOT NULL,
  email                 VARCHAR(190)    NOT NULL,
  password_hash         VARCHAR(255)    NOT NULL,
  role                  ENUM('admin','staff') NOT NULL DEFAULT 'staff',
  status                ENUM('active','inactive') NOT NULL DEFAULT 'active',
  must_change_password  TINYINT(1)      NOT NULL DEFAULT 0,
  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 uniq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login attempt log that powers the lockout. The app counts failed rows per
-- (email, ip_address) inside the lockout window, clears them on success, and
-- prunes rows older than the window on each attempt.
CREATE TABLE IF NOT EXISTS login_attempts (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email         VARCHAR(190)    NOT NULL,
  ip_address    VARCHAR(45)     NOT NULL,
  successful    TINYINT(1)      NOT NULL DEFAULT 0,
  attempted_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_attempts_email_ip_time (email, ip_address, attempted_at),
  KEY idx_attempts_time (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- No seed rows. The first admin account is created through the /setup page,
-- which self-disables once any admin row exists.
