const { Area, Order, Collection, sequelize } = require("../../models");
const { STATUS_CODE } = require("../../utils/utility");
const routeHandler = require("../../utils/routeHandler");
const { sendAppError } = require("../../utils/appError");
const { Op, fn, col, literal } = 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 whereConditions = {
            branch_id,
        };

        if (search) {
            whereConditions.area_name = { [Op.like]: `%${search}%` };
        }

        const orderWhereConditions = '';

        if (formattedCollectionDay) {
            orderWhereConditions.collection_day = formattedCollectionDay;
        }

        if (from_date && to_date) {
            orderWhereConditions.order_date = { [Op.between]: [new Date(from_date), new Date(to_date)] };
        }

        // Query using Sequelize ORM
        const areas = await Area.findAll({
            attributes: [
                'area_name',
                'area_id',
                [fn('COALESCE', fn('SUM', col('orderDetails.sub_amount')), 0), 'total_amount'],
                [fn('COALESCE', fn('SUM', col('orderDetails.collectionDetails.amount')), 0), 'collected_amount']
            ],
            include: [
                {
                    model: Order,
                    as: 'orderDetails',
                    attributes: [],
                    where: orderWhereConditions,
                    required: false,
                    include: [
                        {
                            model: Collection,
                            as: 'collectionDetails',
                            attributes: [],
                            required: false
                        }
                    ]
                }
            ],
            where: whereConditions,
            group: ['Area.area_id'],
            order: [['id', 'DESC']],
            offset: typeof offset === 'number' ? offset : undefined,
            limit: typeof limit === 'number' ? limit : undefined
        });

        const total = await Area.count({
            where: whereConditions
        });

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

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

module.exports = {
    getAll,
};
