-- --------------------------------------------------------
-- Security Tables for Failed Login Attempts and Password Change History
-- --------------------------------------------------------

USE `cms_db`;

-- Table structure for failed_login_attempts
CREATE TABLE IF NOT EXISTS `failed_login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login_id` varchar(100) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `attempted_at` timestamp NULL DEFAULT current_timestamp(),
  `reason` varchar(255) DEFAULT 'Invalid credentials',
  PRIMARY KEY (`id`),
  KEY `idx_login_id` (`login_id`),
  KEY `idx_organization_id` (`organization_id`),
  KEY `idx_attempted_at` (`attempted_at`),
  KEY `idx_ip_address` (`ip_address`),
  CONSTRAINT `failed_login_attempts_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Tracks failed login attempts for security monitoring';

-- Table structure for password_change_history
CREATE TABLE IF NOT EXISTS `password_change_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `changed_by` int(11) NOT NULL,
  `organization_id` int(11) NOT NULL DEFAULT 1,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `changed_at` timestamp NULL DEFAULT current_timestamp(),
  `change_type` enum('self','admin_reset','forced') DEFAULT 'self' COMMENT 'Type of password change',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_changed_by` (`changed_by`),
  KEY `idx_organization_id` (`organization_id`),
  KEY `idx_changed_at` (`changed_at`),
  CONSTRAINT `password_change_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `password_change_history_ibfk_2` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `password_change_history_ibfk_3` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Tracks password change history for security auditing';

-- Create indexes for better query performance
CREATE INDEX idx_failed_login_attempts_24h ON failed_login_attempts(attempted_at DESC);
CREATE INDEX idx_password_change_history_7d ON password_change_history(changed_at DESC);