
SELECT 
    provider.*,
    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,
    CONCAT(
        '[',
            '{',
                '"behavior":[', 
                    JSON_OBJECT(
                        'name', CONVERT(provider.name USING utf8mb4),
                        'roi', COALESCE(pb.roi, 0),
                        'roi_star', COALESCE(pb.roi_star, 0),
                        'profit', COALESCE(pb.profit, 0),
                        'profit_star', COALESCE(pb.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(d.current, '$0.00'),
                        'total_due_30_days', COALESCE(d.total_due_30_days, '$0.00'),
                        'total_due_60_days', COALESCE(d.total_due_31_to_60_days, '$0.00'),
                        'total_due_90_days', COALESCE(d.total_due_61_to_90_days, '$0.00'),
                        'total_due_over_90_days', COALESCE(d.total_due_over_90_days, '$0.00'),
                        'total_due', COALESCE(d.total_due, '$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,
                                                            AVG(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_provider_behaivor_history h
                                                        INNER JOIN item i ON i.id = h.item_id
                                                        WHERE h.provider_id = '425'
                                                        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,
                                                            AVG(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_provider_behaivor_history h
                                                        INNER JOIN item i ON i.id = h.item_id
                                                        WHERE h.provider_id = '425'
                                                        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(
                                'purchase_id', combined.id,
                                'provider_name', provider.name,
                                'code', combined.code,
                                'category', CONVERT(combined.category USING utf8mb4),
                                'date', DATE_FORMAT(combined.purchase_date, '%m/%d/%Y'),
                                'due_date', DATE_FORMAT(combined.due_date, '%m/%d/%Y'),
                                'due_amount', CONCAT('$', FORMAT((combined.general_total_price - combined.paid_amount), 2)),
                                'original_amount', CONCAT('$', FORMAT(combined.general_total_price, 2)),
                                'paid_amount', CONCAT('$', FORMAT(combined.paid_amount, 2)),
                                'overdue_age', 
                                    CASE
                                        WHEN DATEDIFF(CURDATE(), combined.due_date) <= 0 THEN CONVERT('No Overdue' USING utf8mb4)
                                        WHEN DATEDIFF(CURDATE(), combined.due_date) <= 30 THEN CONVERT('1 - 30 Days Past Due' USING utf8mb4)
                                        WHEN DATEDIFF(CURDATE(), combined.due_date) <= 60 THEN CONVERT('31 - 60 Days Past Due' USING utf8mb4)
                                        WHEN DATEDIFF(CURDATE(), combined.due_date) <= 90 THEN CONVERT('61 - 90 Days Past Due' USING utf8mb4)
                                        ELSE CONVERT('Over 90 Days Past Due' USING utf8mb4)
                                    END
                            ) ORDER BY combined.purchase_date ASC SEPARATOR ','
                        )
                        FROM (
                            SELECT 
                                p.id, p.provider_id, p.code, p.purchase_date, p.due_date, p.general_total_price, p.paid_amount, 'P.O.' AS category
                            FROM purchase p
                            WHERE p.payment_status != 'paid' AND p.deleted = 0 AND p.provider_id = '425'
                            
                            UNION ALL
                            
                            SELECT 
                                o.purchaseId AS id, o.provider_id, purchase.code, o.date AS purchase_date, o.date AS due_date, o.amount AS general_total_price, o.paid_amount, 'COGS' AS category
                            FROM purchase_vs_expenses o
                            LEFT JOIN purchase ON purchase.id = o.purchaseId
                            WHERE o.payment_status != 'paid' AND o.deleted = 0 AND o.provider_id = '425'
                        ) AS combined
                    ), JSON_OBJECT('message', 'No unpaid bills')),
                ']',
            '}',
        ']'
    ) AS extrainfo

FROM provider
LEFT JOIN provider_behaivor pb ON pb.provider_id = provider.id
LEFT JOIN chat_ia ON provider.id = chat_ia.entityId AND chat_ia.entity = 'provider'
LEFT JOIN users ON users.id = chat_ia.userId
LEFT JOIN (
    SELECT
        combined.provider_id,
        CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), combined.due_date) <= 0 THEN (combined.general_total_price - combined.paid_amount) ELSE 0 END), 2)) AS current,
        CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), combined.due_date) > 0 AND DATEDIFF(CURDATE(), combined.due_date) <= 30 THEN (combined.general_total_price - combined.paid_amount) ELSE 0 END), 2)) AS total_due_30_days,
        CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), combined.due_date) > 30 AND DATEDIFF(CURDATE(), combined.due_date) <= 60 THEN (combined.general_total_price - combined.paid_amount) ELSE 0 END), 2)) AS total_due_31_to_60_days,
        CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), combined.due_date) > 60 AND DATEDIFF(CURDATE(), combined.due_date) <= 90 THEN (combined.general_total_price - combined.paid_amount) ELSE 0 END), 2)) AS total_due_61_to_90_days,
        CONCAT('$', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), combined.due_date) > 90 THEN (combined.general_total_price - combined.paid_amount) ELSE 0 END), 2)) AS total_due_over_90_days,
        CONCAT('$', FORMAT(SUM(combined.general_total_price - combined.paid_amount), 2)) AS total_due
    FROM (
        SELECT provider_id, due_date, general_total_price, paid_amount FROM purchase WHERE payment_status != 'paid' AND deleted = 0 AND provider_id = '425'
        UNION ALL
        SELECT provider_id, date AS due_date, amount AS general_total_price, paid_amount FROM purchase_vs_expenses WHERE payment_status != 'paid' AND deleted = 0 AND provider_id = '425'
    ) AS combined
    GROUP BY combined.provider_id
) AS d ON d.provider_id = provider.id

WHERE provider.deleted = 0
AND provider.id = '425';
