const db = require('../config/database');

const User = {
    // Find user by email
    findByEmail: (email, callback) => {
        const query = 'SELECT * FROM users WHERE email = ?';
        db.query(query, [email], callback);
    },

    // Find user by email and password
    findByEmailAndPassword: (email, password, callback) => {
        const query = 'SELECT id, first_name, last_name, email, phone_number, role, is_verified FROM users WHERE email = ? AND password = ?';
        db.query(query, [email, password], callback);
    },

    // Find user by ID
    findById: (id, callback) => {
        const query = 'SELECT * FROM users WHERE id = ?';
        db.query(query, [id], callback);
    },

    // Get all users
    findAll: (callback) => {
        const query = 'SELECT id, first_name, last_name, email, phone_number, role, is_verified, created_at, updated_at FROM users ORDER BY id DESC';
        db.query(query, callback);
    },

    // Create new user
    create: (userData, callback) => {
        const { first_name, last_name, email, phone_number, password, role, otp, is_verified } = userData;
        const verified = is_verified !== undefined ? is_verified : false;
        const query = 
            `INSERT INTO users (first_name, last_name, email, phone_number, password, role, OTP, is_verified) 
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
        db.query(query, [first_name, last_name, email, phone_number || null, password, role, otp || null, verified], callback);
    },

    // Update user OTP
    updateOTP: (userId, otp, callback) => {
        const query = 'UPDATE users SET OTP = ? WHERE id = ?';
        db.query(query, [otp, userId], callback);
    },

    // Verify user email
    verifyEmail: (userId, callback) => {
        const query = 'UPDATE users SET is_verified = TRUE, OTP = NULL WHERE id = ?';
        db.query(query, [userId], callback);
    },

    // Update user
    update: (userId, userData, callback) => {
        const { first_name, last_name, email, phone_number, password, role, is_verified } = userData;
        
        // Build dynamic query based on provided fields
        const updates = [];
        const values = [];
        
        if (first_name !== undefined) {
            updates.push('first_name = ?');
            values.push(first_name);
        }
        if (last_name !== undefined) {
            updates.push('last_name = ?');
            values.push(last_name);
        }
        if (email !== undefined) {
            updates.push('email = ?');
            values.push(email);
        }
        if (phone_number !== undefined) {
            updates.push('phone_number = ?');
            values.push(phone_number);
        }
        if (password !== undefined) {
            updates.push('password = ?');
            values.push(password);
        }
        if (role !== undefined) {
            updates.push('role = ?');
            values.push(role);
        }
        if (is_verified !== undefined) {
            updates.push('is_verified = ?');
            values.push(is_verified);
        }
        
        if (updates.length === 0) {
            return callback(new Error('No fields to update'), null);
        }
        
        values.push(userId);
        const query = `UPDATE users SET ${updates.join(', ')} WHERE id = ?`;
        db.query(query, values, callback);
    },

    // Delete user
    delete: (userId, callback) => {
        const query = 'DELETE FROM users WHERE id = ?';
        db.query(query, [userId], callback);
    },

    // Get user with all ambulance requests
    findByIdWithRequests: (userId, callback) => {
        // First get user data
        const userQuery = 'SELECT id, first_name, last_name, email, role, is_verified, created_at, updated_at FROM users WHERE id = ?';
        
        db.query(userQuery, [userId], (err, userResults) => {
            if (err) {
                return callback(err, null);
            }
            
            if (userResults.length === 0) {
                return callback(null, []);
            }
            
            const user = userResults[0];
            
            // Then get all requests for this user
            const requestsQuery = `
                SELECT 
                    id,
                    is_urgent,
                    location,
                    destination,
                    latitude,
                    longitude,
                    status,
                    comment,
                    created_at,
                    updated_at
                FROM ambulance_requests
                WHERE request_by = ?
                ORDER BY id DESC
            `;
            
            db.query(requestsQuery, [userId], (err, requestResults) => {
                if (err) {
                    return callback(err, null);
                }
                
                // Combine user data with requests array
                const result = {
                    ...user,
                    requests: requestResults || []
                };
                
                callback(null, [result]);
            });
        });
    }
};

module.exports = User;

