

-- Create security settings table
CREATE TABLE IF NOT EXISTS security_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    require_2fa BOOLEAN DEFAULT false,
    backup_codes_enabled BOOLEAN DEFAULT false,
    password_expiry_days INT DEFAULT 90,
    notify_on_login BOOLEAN DEFAULT true,
    notify_on_password_change BOOLEAN DEFAULT true,
    notify_on_2fa_change BOOLEAN DEFAULT true,
    last_password_change TIMESTAMP,
    password_history TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user (user_id)
);

-- Create security questions table
CREATE TABLE IF NOT EXISTS security_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question TEXT NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



-- Create active sessions table
CREATE TABLE IF NOT EXISTS active_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    session_id VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    last_activity TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_session (session_id)
);

-- Insert default security questions
INSERT INTO security_questions (question) VALUES
('What was the name of your first pet?'),
('In which city were you born?'),
('What was your childhood nickname?'),
('What is your mother''s maiden name?'),
('What was the make of your first car?'),
('What elementary school did you attend?'),
('What is the name of your favorite childhood friend?'),
('What street did you grow up on?'),
('What was your favorite food as a child?'),
('What was the first concert you attended?');
