-- ============================================================
--  CareBill — two-factor sign-in and login history.
--  Existing database:  mysql carebill < sql/security.sql
-- ============================================================

ALTER TABLE users
  ADD COLUMN totp_secret   VARCHAR(64) NULL AFTER password_hash,
  ADD COLUMN totp_enabled  TINYINT(1) DEFAULT 0 AFTER totp_secret,
  ADD COLUMN totp_last_used INT NULL AFTER totp_enabled;

CREATE TABLE recovery_codes (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  code_hash     VARCHAR(255) NOT NULL,
  used_at       DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_rc_user (user_id, used_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE login_log (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NULL,
  email         VARCHAR(160),
  result        ENUM('ok','bad_password','bad_code','locked','disabled','unknown_user') NOT NULL,
  ip            VARCHAR(45),
  user_agent    VARCHAR(255),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_ll_email (email, created_at), INDEX ix_ll_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (k,v) VALUES
 ('twofa_required_admins','0'), ('login_lock_attempts','6'), ('login_lock_minutes','15')
ON DUPLICATE KEY UPDATE k=k;