/* ================================================================
   003_security_auth.sql
   - Rate limit login (5x gagal lock 5 menit)
   - Audit log login activity
   ================================================================ */

SET FOREIGN_KEY_CHECKS=0;

-- ------------------------------------------------
-- TABLE: auth_login_attempts
-- Menyimpan counter gagal login per tenant + identifier (email/username) + IP
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS auth_login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NULL,
  identifier VARCHAR(190) NOT NULL,        -- email/username (yang dipakai saat login)
  ip_address VARCHAR(45) NOT NULL,         -- IPv4/IPv6
  fail_count INT UNSIGNED NOT NULL DEFAULT 0,
  first_fail_at DATETIME NULL,
  last_fail_at DATETIME NULL,
  locked_until DATETIME NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_attempts_tenant_identifier (tenant_id, identifier),
  KEY idx_attempts_tenant_ip (tenant_id, ip_address),
  KEY idx_attempts_locked_until (locked_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ------------------------------------------------
-- TABLE: auth_login_logs
-- Audit log login: sukses/gagal, reason, ip, user_agent, timestamp
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS auth_login_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  identifier VARCHAR(190) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  user_agent VARCHAR(255) NULL,
  status ENUM('SUCCESS','FAILED','LOCKED','BLOCKED') NOT NULL,
  reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_login_logs_tenant_user (tenant_id, user_id),
  KEY idx_login_logs_tenant_identifier (tenant_id, identifier),
  KEY idx_login_logs_created_at (created_at),
  CONSTRAINT fk_login_logs_user FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS=1;