-- Add biometric_enabled, reset_token, and reset_token_expiry columns to users table if they don't exist
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS biometric_enabled TINYINT(1) DEFAULT 0,
ADD COLUMN IF NOT EXISTS reset_token VARCHAR(255) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS reset_token_expiry DATETIME DEFAULT NULL,
ADD COLUMN IF NOT EXISTS failed_login_attempts INT DEFAULT 0;

-- Create activity_logs table if it doesn't exist
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    activity_type VARCHAR(50) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at DATETIME NOT NULL,
    INDEX (user_id),
    INDEX (activity_type),
    INDEX (created_at)
);

-- Sample data for testing
-- Update some users to have biometric enabled
UPDATE users SET biometric_enabled = 1 WHERE id IN (1, 2, 3);

-- Insert sample activity logs
INSERT INTO activity_logs (user_id, activity_type, details, ip_address, created_at) VALUES
(1, 'login', 'User logged in successfully', '127.0.0.1', NOW()),
(2, 'login', 'User logged in successfully', '127.0.0.1', NOW()),
(3, 'login_failed', 'Failed login attempt', '127.0.0.1', NOW()),
(1, 'logout', 'User logged out', '127.0.0.1', NOW()),
(2, 'pin_changed', 'User changed PIN', '127.0.0.1', NOW()),
(3, 'biometric_enabled', 'User enabled biometric authentication', '127.0.0.1', NOW());
