
        SELECT 
            customer.id,
            customer.code,
            customer.name,
            customer.contact,
            customer.phone,
            customer.other_phone,
            customer.fax,
            customer.email,
            customer.bill_to_address,
            customer.bill_to_apt,
            customer.bill_to_city,
            customer.bill_to_state,
            customer.bill_to_zip,
            customer.ship_to_address,
            customer.ship_to_apt,
            customer.ship_to_city,
            customer.ship_to_state,
            customer.ship_to_zip,
            customer.inv_terms,
            customer.credit_limit,  
            customer.credit_hold,
            '' AS check_shipping_address,
            customer.description,
            customer.avatar,
            customer.avatarColor,
            customer.avatarColorLetter,
            customer.avatarLetter,
            customer.active AS active,
            
            -- Estadísticas Generales
            COALESCE(s_stats.total_invoiced, 0) AS total_invoiced,
            COALESCE(s_stats.total_paid, 0) AS total_paid,
            COALESCE(s_stats.balance_due, 0) AS balance_due,

            CONCAT(
            '[',
                
                '{',
                    '"tableName":"chats"', 
                    ',"data":[',
                                  GROUP_CONCAT(
                                        DISTINCT(
                                        JSON_OBJECT(
                                            'id', chat_ia.id,
                                            'avatar', users.avatar,
                                            'question', chat_ia.question,
                                            'answare', chat_ia.answare,
                                            'userId', chat_ia.userId,
                                            'dateTime', chat_ia.dateTime         
                                        )

                                        ) SEPARATOR ','),
                            ']',
                '}',  
            
            ']'
            ) AS chats,

            -- Objeto JSON con Info Extra
            CONCAT(
                '[',
                    '{',
                        '"behavior":[',
                                JSON_OBJECT(
                                    'name', CONVERT(customer.name USING utf8mb4),
                                    'roi', COALESCE(customer_behaivor.roi, 0),
                                    'roi_star', COALESCE(customer_behaivor.roi_star, 0),
                                    'profit', COALESCE(customer_behaivor.profit, 0),
                                    'profit_star', COALESCE(customer_behaivor.profit_star, 0),
                                    'roi_title', CONVERT('ROI (Return on Investment) = (Profit / Investment) × 100' USING utf8mb4), 
                                    'profit_title', CONVERT('Profit (Ganancia) = Earnings − Investment' USING utf8mb4)
                                ),
                        ']',
                    '},',
                    '{',
                        '"amount":[',
                            JSON_OBJECT(  
                                'current', COALESCE(s_stats.current, '$0.00'),
                                'total_due_30_days', COALESCE(s_stats.total_due_30_days, '$0.00'),
                                'total_due_60_days', COALESCE(s_stats.total_due_31_to_60_days, '$0.00'),
                                'total_due_90_days', COALESCE(s_stats.total_due_61_to_90_days, '$0.00'),
                                'total_due_over_90_days', COALESCE(s_stats.total_due_over_90_days, '$0.00'),
                                'total_due', COALESCE(s_stats.total_due_formatted, '$0.00')
                            ),
                        ']',
                    '},',
                    '{',
                        '"performance":[', 
                            COALESCE(
                                (
                                    SELECT CONCAT(
                                        '{',
                                            '"best": [',
                                                IFNULL(
                                                    (SELECT GROUP_CONCAT(
                                                        JSON_OBJECT(
                                                            'item_id', item_id,
                                                            'item_name', item_name,
                                                            'profit', profit,
                                                            'roi', roi,
                                                            'profit_star', profit_star,
                                                            'roi_star', roi_star
                                                        ) SEPARATOR ','
                                                    )
                                                    FROM (
                                                        SELECT 
                                                            h.item_id,
                                                            i.name AS item_name,
                                                            SUM(h.profit) AS total_profit,
                                                            h.profit AS profit,
                                                            h.roi AS roi,
                                                            h.profit_star AS profit_star,
                                                            h.roi_star AS roi_star
                                                        FROM item_customer_behaivor_history h
                                                        INNER JOIN item i ON i.id = h.item_id
                                                        WHERE h.customer_id = '55'
                                                        GROUP BY h.item_id, i.name
                                                        ORDER BY total_profit DESC
                                                        LIMIT 15
                                                    ) AS best_items ), ''
                                                ),
                                            '],',
                                            '"worst": [',
                                                IFNULL(
                                                    (SELECT GROUP_CONCAT(
                                                        JSON_OBJECT(
                                                            'item_id', item_id,
                                                            'item_name', item_name,
                                                            'profit', profit,
                                                            'roi', roi,
                                                            'profit_star', profit_star,
                                                            'roi_star', roi_star
                                                        ) SEPARATOR ','
                                                    )
                                                    FROM (
                                                        SELECT 
                                                            h.item_id,
                                                            i.name AS item_name,
                                                            SUM(h.profit) AS total_profit,
                                                            h.profit AS profit,
                                                            h.roi AS roi,
                                                            h.profit_star AS profit_star,
                                                            h.roi_star AS roi_star
                                                        FROM item_customer_behaivor_history h
                                                        INNER JOIN item i ON i.id = h.item_id
                                                        WHERE h.customer_id = '55'
                                                        GROUP BY h.item_id, i.name
                                                        ORDER BY total_profit ASC
                                                        LIMIT 15
                                                    ) AS worst_items
                                                    ), ''
                                                ),
                                            ']',
                                        '}'
                                )
                            ), JSON_OBJECT('message', 'No items sold')),
                        ']',
                    '},',
                    '{',
                        '"unpaid":[', 
                            COALESCE((
                                SELECT GROUP_CONCAT(
                                    JSON_OBJECT(
                                        'sale_id', sale.id,
                                        'customer_name', customer.name,
                                        'code', CONVERT(sale.code USING utf8mb4),
                                        'date', DATE_FORMAT(sale.sale_date, '%m/%d/%Y'),
                                        'due_date', DATE_FORMAT(sale.due_date, '%m/%d/%Y'),
                                        'due_amount', CONCAT('$', FORMAT((sale.grand_total + sale.spent_credit) - (sale.paid_amount + sale.credit_amount), 2)),
                                        'original_amount', CONCAT('$', FORMAT(sale.grand_total, 2)),
                                        'paid_amount', CONCAT('$', FORMAT(sale.paid_amount, 2)),
                                        'overdue_age', CASE
                                            WHEN DATEDIFF(CURDATE(), sale.due_date) <= 0 THEN 'No Overdue'
                                            WHEN DATEDIFF(CURDATE(), sale.due_date) <= 30 THEN '1 - 30 Days Past Due'
                                            WHEN DATEDIFF(CURDATE(), sale.due_date) <= 60 THEN '31 - 60 Days Past Due'
                                            WHEN DATEDIFF(CURDATE(), sale.due_date) <= 90 THEN '61 - 90 Days Past Due'
                                            ELSE 'Over 90 Days Past Due'
                                        END
                                    )
                                    ORDER BY sale.id ASC SEPARATOR ','
                                )
                                FROM sale
                                WHERE sale.customer_id = customer.id
                                AND sale.payment_status != 'paid'
                                AND sale.deleted = 0
                            ), JSON_OBJECT('message', 'No unpaid bills')),
                        ']',
                    '}',
                ']'
            ) AS extrainfo

        FROM customer 
        LEFT JOIN customer_behaivor ON customer_behaivor.customer_id = customer.id 
        LEFT JOIN chat_ia ON customer.id = chat_ia.entityId AND chat_ia.entity = 'customer'
        LEFT JOIN users ON users.id = chat_ia.userId
        LEFT JOIN (
            SELECT 
                s.customer_id,
                SUM(s.grand_total) AS total_invoiced,
                SUM(s.paid_amount) AS total_paid,
                SUM(s.grand_total - s.paid_amount) AS balance_due,
                CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), s.due_date) <= 0 THEN ( (s.grand_total + s.spent_credit) - (s.paid_amount + s.credit_amount ) ) ELSE 0 END),2)) AS current,
                CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), s.due_date) > 0 AND DATEDIFF(CURDATE(), s.due_date) <= 30 THEN ( (s.grand_total + s.spent_credit) - (s.paid_amount + s.credit_amount ) ) ELSE 0 END),2)) AS total_due_30_days,
                CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), s.due_date) > 30 AND DATEDIFF(CURDATE(), s.due_date) <= 60 THEN ( (s.grand_total + s.spent_credit) - (s.paid_amount + s.credit_amount ) ) ELSE 0 END),2)) AS total_due_31_to_60_days,
                CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), s.due_date) > 60 AND DATEDIFF(CURDATE(), s.due_date) <= 90 THEN ( (s.grand_total + s.spent_credit) - (s.paid_amount + s.credit_amount ) ) ELSE 0 END),2)) AS total_due_61_to_90_days,
                CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), s.due_date) > 90 THEN ( (s.grand_total + s.spent_credit) - (s.paid_amount + s.credit_amount ) ) ELSE 0 END),2)) AS total_due_over_90_days,
                CONCAT('$', FORMAT(SUM((s.grand_total + s.spent_credit) - (s.paid_amount + s.credit_amount)), 2)) AS total_due_formatted
            FROM sale s
            WHERE s.deleted = '0' AND s.payment_status != 'paid'
            GROUP BY s.customer_id
        ) AS s_stats ON s_stats.customer_id = customer.id

        LEFT JOIN (
            SELECT 
                sale.customer_id,
                CONCAT('$', FORMAT(SUM((svi.qty * svi.price) - (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / pvi.qty)))), 2)) AS total_earnings
            FROM sale_vs_item svi
            INNER JOIN sale ON sale.id = svi.saleId AND sale.deleted = 0
            INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
            WHERE svi.deleted = 0
            
        ) AS r_stats ON r_stats.customer_id = customer.id
         
        WHERE customer.deleted = '0' 
        AND customer.id = '55'
        GROUP BY customer.id;
    