-- DentalCare v0.5.9.8.43 Security Hardening
-- Idempotent tables for login throttling, session/device tracking, security events, and high-risk confirmation foundation.

CREATE TABLE IF NOT EXISTS `{{prefix}}security_login_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `email` VARCHAR(190) NULL,
  `email_hash` CHAR(64) NULL,
  `ip_address` VARCHAR(64) NULL,
  `user_agent` VARCHAR(500) NULL,
  `success` TINYINT(1) NOT NULL DEFAULT 0,
  `failure_reason` VARCHAR(120) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_attempt_email_time` (`email_hash`, `created_at`),
  KEY `idx_login_attempt_ip_time` (`ip_address`, `created_at`),
  KEY `idx_login_attempt_success_time` (`success`, `created_at`),
  KEY `idx_login_attempt_user_time` (`user_id`, `created_at`),
  CONSTRAINT `{{prefix}}fk_login_attempt_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}user_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `session_id_hash` CHAR(64) NOT NULL,
  `ip_address` VARCHAR(64) NULL,
  `user_agent` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NULL,
  `logged_out_at` DATETIME NULL,
  `revoked_at` DATETIME NULL,
  `revoke_reason` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_session_hash` (`session_id_hash`),
  KEY `idx_user_sessions_user_seen` (`user_id`, `last_seen_at`),
  KEY `idx_user_sessions_revoked` (`revoked_at`, `last_seen_at`),
  CONSTRAINT `{{prefix}}fk_user_session_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}security_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `severity` ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
  `entity_type` VARCHAR(80) NULL,
  `entity_id` VARCHAR(80) NULL,
  `metadata_json` JSON NULL,
  `ip_address` VARCHAR(64) NULL,
  `user_agent` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_security_event_type_time` (`event_type`, `created_at`),
  KEY `idx_security_event_user_time` (`user_id`, `created_at`),
  KEY `idx_security_event_severity_time` (`severity`, `created_at`),
  CONSTRAINT `{{prefix}}fk_security_event_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}privileged_action_confirmations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `action_key` VARCHAR(120) NOT NULL,
  `reason` VARCHAR(500) NULL,
  `confirmed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  `ip_address` VARCHAR(64) NULL,
  `user_agent` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_priv_confirm_user_action` (`user_id`, `action_key`, `expires_at`),
  KEY `idx_priv_confirm_expires` (`expires_at`),
  CONSTRAINT `{{prefix}}fk_privileged_confirmation_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}settings` (`group_name`,`setting_key`,`setting_value`,`is_secret`,`created_at`) VALUES
('security','login_throttle_enabled','1',0,NOW()),
('security','login_max_failures','5',0,NOW()),
('security','login_failure_window_minutes','15',0,NOW()),
('security','login_lockout_minutes','15',0,NOW()),
('security','privileged_action_confirmation_minutes','10',0,NOW()),
('security','track_staff_sessions','1',0,NOW()),
('security','require_mfa_for_privileged_roles','0',0,NOW())
ON DUPLICATE KEY UPDATE `setting_value`=`setting_value`, `updated_at`=NOW();
