const { Area, sequelize } = require("../../models");
const { STATUS_CODE } = require("../../utils/utility");
const routeHandler = require("../../utils/routeHandler");
const { sendAppError } = require("../../utils/appError");
const { Op, fn, col, QueryTypes } = require("sequelize");

const getAll = routeHandler(async (req, res, extras) => {
    const { collection_day } = req.query;
    const { branch_id } = req.auth;
    try {
        const formattedCollectionDay = collection_day ? collection_day.replace(/ /g, "_").toUpperCase() : null;

        // Cleaned up SQL query without GROUP_CONCAT
        const orderQuery = `
            WITH OrderData AS (
                SELECT 
                    COALESCE(SUM(t1.sub_amount), 0) AS total_amount,
                    COALESCE(MAX(t2.collected_amount), 0) AS collected_amount
                FROM 
                    orders t1
                LEFT JOIN 
                    (SELECT SUM(amount) AS collected_amount, order_id 
                     FROM collections 
                     WHERE deleted_at IS NULL 
                     GROUP BY order_id) AS t2 
                     ON t2.order_id = t1.order_id
                WHERE 
                    t1.deleted_at IS NULL 
                    AND t1.branch_id = :branch_id
                    ${collection_day ? `AND t1.collection_day = :collection_day` : ''}
                GROUP BY t1.order_id
            )
            SELECT 
                SUM(total_amount) AS total_total_amount, 
                SUM(collected_amount) AS total_collected_amount
            FROM OrderData;
        `;

        const replacements = { branch_id };

        if (collection_day) replacements.collection_day = formattedCollectionDay;

        const result = await sequelize.query(orderQuery, {
            replacements,
            type: QueryTypes.SELECT,
        });

        // Ensure values are numbers
        const total_total_amount = parseFloat(result[0].total_total_amount || 0);
        const total_collected_amount = parseFloat(result[0].total_collected_amount || 0);

        // Calculate pending amount
        const outStanding = [{
            total_total_amount: total_total_amount.toFixed(2),
            total_collected_amount: total_collected_amount.toFixed(2),
            total_pending_amount: (total_total_amount - total_collected_amount).toFixed(2),
        }];

        return res.sendRes(outStanding, {
            message: 'Order loaded successfully',
            ...req.meta,
            total: '1',
            status: STATUS_CODE.OK,
        });

    } catch (error) {
        if (extras.transaction) {
            await extras.transaction.rollback();
        }
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
}, false);

module.exports = {
    getAll,
};
