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 { search, collection_day, from_date, to_date } = req.query;
    const { branch_id } = req.auth;
    const { offset, limit } = req.paginate;
    try {
        const formattedCollectionDay = collection_day ? collection_day.replace(/ /g, "_").toUpperCase() : null;

        const areaQuery = `
            SELECT 
                t1.area_name,
                t1.area_id,
                COALESCE(SUM(t2.sub_amount), 0) AS total_amount,
                COALESCE(MAX(t3.collected_amount), 0) AS collected_amount
            FROM 
                areas t1
            LEFT JOIN 
                orders t2 ON t1.area_id = t2.area_id AND t2.deleted_at IS NULL
            LEFT JOIN 
                (SELECT SUM(amount) AS collected_amount, order_id 
                 FROM collections 
                 WHERE deleted_at IS NULL 
                 GROUP BY order_id) AS t3 
                 ON t3.order_id = t2.order_id
            WHERE 
                t1.deleted_at IS NULL 
                AND t1.branch_id = :branch_id
                ${collection_day ? `AND t2.collection_day = :collection_day` : ''}
                ${search ? `AND t1.area_name LIKE :search` : ''}
                ${from_date && to_date ? `AND t2.order_date BETWEEN :from_date AND :to_date` : ''}
            GROUP BY t1.area_id
            ORDER BY t1.id DESC
            ${typeof offset === 'number' && typeof limit === 'number' ? `LIMIT :limit OFFSET :offset` : ''}`;

        const replacements = { branch_id };

        if (collection_day) replacements.collection_day = formattedCollectionDay;
        if (typeof offset === 'number' && typeof limit === 'number') {
            replacements.offset = offset;
            replacements.limit = limit;
        }
        if (search) replacements.search = `%${search}%`;
        if (from_date && to_date) {
            replacements.from_date = new Date(from_date);
            replacements.to_date = new Date(to_date);
        }

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

        const total = await Area.count({
            where: {
                branch_id,
                // ...(formattedCollectionDay && { collection_day: formattedCollectionDay }),
                // ...(from_date && to_date && { order_date: { [Op.between]: [from_date, to_date] } }),
            },
        });

        return res.sendRes(areas, {
            message: 'Area loaded successfully',
            ...req.meta,
            total,
            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,
};
