-- Update users table
ALTER TABLE users
    DROP COLUMN username,
    ADD COLUMN email_verified BOOLEAN DEFAULT FALSE,
    ADD COLUMN verification_token VARCHAR(100),
    ADD COLUMN verification_expires TIMESTAMP,
    ADD COLUMN two_factor_enabled BOOLEAN DEFAULT FALSE,
    ADD COLUMN two_factor_secret VARCHAR(32),
    ADD COLUMN login_attempts INT DEFAULT 0,
    ADD COLUMN last_login TIMESTAMP,
    ADD COLUMN last_login_ip VARCHAR(45),
    ADD COLUMN profile_picture VARCHAR(255),
    ADD COLUMN verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    ADD COLUMN verification_documents VARCHAR(255),
    ADD COLUMN terms_accepted BOOLEAN DEFAULT FALSE,
    ADD COLUMN terms_version VARCHAR(10),
    ADD COLUMN terms_accepted_at TIMESTAMP;

-- Create login history table
CREATE TABLE IF NOT EXISTS login_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create password reset tokens table
CREATE TABLE IF NOT EXISTS password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(100) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);