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

const Client = {
    // Create new client
    create: (clientData, callback) => {
        const {
            user_id,
            insurance_scheme_id,
            phone_number,
            is_cash_client,
            membership_number,
            suffix,
            address,
            date_of_birth,
            emergency_contact_name,
            emergency_contact_phone
        } = clientData;

        const query = 
            `INSERT INTO clients (
                user_id, insurance_scheme_id, phone_number, is_cash_client, 
                membership_number, suffix, address, date_of_birth, 
                emergency_contact_name, emergency_contact_phone
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        
        db.query(query, [
            user_id,
            insurance_scheme_id || null,
            phone_number,
            is_cash_client || false,
            membership_number || null,
            suffix || null,
            address || null,
            date_of_birth || null,
            emergency_contact_name || null,
            emergency_contact_phone || null
        ], callback);
    },

    // Find client by user ID
    findByUserId: (userId, callback) => {
        const query = 'SELECT * FROM clients WHERE user_id = ?';
        db.query(query, [userId], callback);
    },

    // Get all clients joined with users
    findAllWithUsers: (callback) => {
        const query = `
            SELECT 
                c.*,
                u.id as user_id,
                u.first_name,
                u.last_name,
                u.email,
                u.role,
                u.is_verified,
                u.created_at as user_created_at,
                u.updated_at as user_updated_at
            FROM clients c
            INNER JOIN users u ON c.user_id = u.id
            ORDER BY c.id DESC
        `;
        db.query(query, callback);
    },

    // Find client by ID
    findById: (clientId, callback) => {
        const query = `
            SELECT 
                c.*,
                u.id as user_id,
                u.first_name,
                u.last_name,
                u.email,
                u.role,
                u.is_verified,
                u.created_at as user_created_at,
                u.updated_at as user_updated_at
            FROM clients c
            INNER JOIN users u ON c.user_id = u.id
            WHERE c.id = ?
        `;
        db.query(query, [clientId], callback);
    },

    // Get client with all ambulance requests
    findByIdWithRequests: (clientId, callback) => {
        // First get client data with user info
        const clientQuery = `
            SELECT 
                c.*,
                u.id as user_id,
                u.first_name,
                u.last_name,
                u.email,
                u.role,
                u.is_verified,
                u.created_at as user_created_at,
                u.updated_at as user_updated_at
            FROM clients c
            INNER JOIN users u ON c.user_id = u.id
            WHERE c.id = ?
        `;
        
        db.query(clientQuery, [clientId], (err, clientResults) => {
            if (err) {
                return callback(err, null);
            }
            
            if (clientResults.length === 0) {
                return callback(null, []);
            }
            
            const client = clientResults[0];
            const userId = client.user_id;
            
            // 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 client data with requests array
                const result = {
                    ...client,
                    requests: requestResults || []
                };
                
                callback(null, [result]);
            });
        });
    },

    // Update client
    update: (clientId, clientData, callback) => {
        const {
            insurance_scheme_id,
            phone_number,
            is_cash_client,
            membership_number,
            suffix,
            address,
            date_of_birth,
            emergency_contact_name,
            emergency_contact_phone
        } = clientData;

        const updates = [];
        const values = [];

        if (insurance_scheme_id !== undefined) {
            updates.push('insurance_scheme_id = ?');
            values.push(insurance_scheme_id);
        }
        if (phone_number !== undefined) {
            updates.push('phone_number = ?');
            values.push(phone_number);
        }
        if (is_cash_client !== undefined) {
            updates.push('is_cash_client = ?');
            values.push(is_cash_client);
        }
        if (membership_number !== undefined) {
            updates.push('membership_number = ?');
            values.push(membership_number);
        }
        if (suffix !== undefined) {
            updates.push('suffix = ?');
            values.push(suffix);
        }
        if (address !== undefined) {
            updates.push('address = ?');
            values.push(address);
        }
        if (date_of_birth !== undefined) {
            updates.push('date_of_birth = ?');
            values.push(date_of_birth);
        }
        if (emergency_contact_name !== undefined) {
            updates.push('emergency_contact_name = ?');
            values.push(emergency_contact_name);
        }
        if (emergency_contact_phone !== undefined) {
            updates.push('emergency_contact_phone = ?');
            values.push(emergency_contact_phone);
        }

        if (updates.length === 0) {
            return callback(new Error('No fields to update'), null);
        }

        values.push(clientId);
        const query = `UPDATE clients SET ${updates.join(', ')} WHERE id = ?`;
        db.query(query, values, callback);
    },

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

module.exports = Client;

