const { Order, OrderDetails, Product, Customer, Area, Landmark, User, Collection, Sequelize } = require("../../models");
const { NO_PARAMS, SERVER_ERROR } = require("../../utils/errorMessage");
const { STATUS_CODE } = require("../../utils/utility");
const routeHandler = require("../../utils/routeHandler");
const { isNull } = require("../../utils/validation");
const { sendAppError } = require("../../utils/appError");
const { Op, fn, col } = require("sequelize");
const moment = require("moment");
const fastcsv = require('fast-csv');
const path = require('path');
const fs = require('fs');

const create = routeHandler(async (req, res, extras, next) => {
    const {
        collection_day,
        order_date,
        order_no,
        customer_id,
        customer_name,
        mobile_number,
        area_id,
        landmark_id,
        sub_amount,
        advance_amount,
        total_amount,
        orderDetails
    } = req.body;

    if (!collection_day) {
        return sendAppError(extras, "Day is required", STATUS_CODE.BAD_REQUEST);
    }

    const dayFormat = collection_day.replace(/ /g, "_").toUpperCase();
    const types = ["MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY"];
    const { user_id, branch_id } = req.auth;

    const paramNames = ["Collection Day", "Order Date", "Customer Name", "Area Name", "Landmark Name", "Sub Amount", "Total Amount"];
    const { hasNull, missingParams } = isNull([collection_day, order_date, customer_name, area_id, landmark_id, sub_amount, total_amount]);

    if (hasNull || !types.includes(dayFormat)) {
        const missingParamNames = missingParams.map(index => paramNames[index]);
        if (!types.includes(dayFormat)) {
            return sendAppError(extras, `Invalid type '${collection_day}'. Expected types are: ${types.join(', ')}`, STATUS_CODE.BAD_REQUEST);
        }
        return sendAppError(extras, `${missingParamNames.join(', ')} ${missingParamNames.length > 1 ? 'are' : 'is'} required`, STATUS_CODE.BAD_REQUEST);
    }

    if (!Array.isArray(orderDetails) || orderDetails.length === 0) {
        return sendAppError(extras, NO_PARAMS, STATUS_CODE.BAD_REQUEST);
    }

    try {
        const findOrder = await Order.findOne({ where: { order_no, collection_day: dayFormat } });
        if (findOrder) {
            return sendAppError(extras, 'Order no already exists', STATUS_CODE.BAD_REQUEST);
        }

        let customer;
        const findCustomer = customer_id
            ? await Customer.findOne({ where: { customer_id } })
            : null;

        if (!findCustomer && !customer_id) {
            customer = await Customer.create({
                branch_id,
                area_id,
                landmark_id,
                customer_name,
                mobile_number,
                created_by: user_id
            }, { transaction: extras.transaction });
        } else if (findCustomer) {
            customer = await Customer.update({
                area_id,
                landmark_id,
                customer_name,
                mobile_number,
                updated_by: user_id,
            }, {
                where: { customer_id: findCustomer.customer_id },
                transaction: extras.transaction
            });
            customer = findCustomer;
        } else {
            customer = await Customer.findOne({ where: { customer_id } });
        }

        const order = await Order.create({
            branch_id,
            collection_day: dayFormat,
            order_date: new Date(order_date),
            order_no,
            customer_id: customer.customer_id,
            area_id,
            landmark_id,
            sub_amount,
            advance_amount,
            total_amount,
            created_by: user_id
        }, { transaction: extras.transaction });

        const tableRowsData = orderDetails.map(row => ({
            order_id: order.order_id,
            product_id: row.product_id,
            price: row.price,
            quantity: row.quantity,
            amount: row.amount,
            created_by: user_id
        }));

        await OrderDetails.bulkCreate(tableRowsData, { transaction: extras.transaction });
        let payment_status = advance_amount != sub_amount ? 'Partial' : 'Paid'
        if (advance_amount && advance_amount > 0) {
            await Collection.create(
                {
                    branch_id,
                    area_id: order.area_id,
                    order_id: order.order_id,
                    collection_date: new Date(order_date),
                    payment_type: "Advance",
                    payment_mode: "Cash",
                    amount: advance_amount,
                    created_by: user_id,
                },
                { transaction: extras.transaction }
            );

            await order.update({
                payment_status: payment_status,
            }, { transaction: extras.transaction });
        }
        await extras.transaction.commit();

        const newOrderDetails = await getDetails(order.order_id);
        return res.sendRes(newOrderDetails, { message: 'Order created successfully', status: STATUS_CODE.OK });
    } catch (error) {
        console.error(error);
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

const getAll = routeHandler(async (req, res, extras) => {
    const { search, collection_day, payment_status, from_date, to_date, area_id, product_id,
        landmark_id } = req.query;
    const { branch_id } = req.auth;
    let findObj = findObject(search, branch_id, collection_day, payment_status, from_date, to_date, area_id, product_id, landmark_id);
    try {
        const orders = await Order.findAll({
            attributes: [
                'order_id',
                'collection_day',
                'order_date',
                'order_no',
                'sub_amount',
                'advance_amount',
                'total_amount',
                'payment_status',
                'created_at',
                'updated_at',
                'deleted_at',
                'created_by',
                'updated_by',
                'deleted_by',
                'branch_id',
                'customer_id',
                'area_id',
                'landmark_id',
                [fn("concat", col("collection_day"), ' - ', col("order_no")), 'orderNo']
            ],
            include: [
                {
                    model: Customer,
                    as: 'customer',
                    attributes: ['customer_id', 'customer_name', 'mobile_number'],
                },
                {
                    model: Area,
                    as: 'area',
                    attributes: ['area_id', 'area_name'],
                },
                {
                    model: Landmark,
                    as: 'landmark',
                    attributes: ['landmark_id', 'landmark_name'],
                },
                {
                    model: OrderDetails,
                    as: 'orderDetails',
                    include: [
                        {
                            model: Product,
                            as: 'product',
                            attributes: ['product_id', 'product_name'],
                        }
                    ],
                    where: {
                        order_status: { [Op.eq]: 'New' },
                        ...(product_id && product_id !== 'undefined' ? { product_id } : {}),
                    },
                    separate: true,
                },
                {
                    model: Collection,
                    as: 'collectionDetails',
                    attributes: ['collection_id', 'order_id', 'collection_date', 'payment_type', 'payment_mode', 'amount'],
                    include: [
                        {
                            model: User,
                            as: 'user_created',
                            attributes: ['user_id', 'fullname'],
                        },
                        {
                            model: User,
                            as: 'user_updated',
                            attributes: ['user_id', 'fullname'],
                        }
                    ],
                    order: [['collection_date', 'DESC']],
                    separate: true,
                },
                {
                    model: User,
                    as: 'user_created',
                    attributes: ['user_id', 'fullname'],
                },
                {
                    model: User,
                    as: 'user_updated',
                    attributes: ['user_id', 'fullname'],
                },
            ],
            where: findObj,
            order: [['id', 'DESC']],
            ...req.paginate,
        });

        const ordersWithTotalAmount = await Promise.all(
            orders
                .filter(order => order.orderDetails && order.orderDetails.length > 0)
                .map(async (order) => {
                    const totalCollectedAmount = await Collection.sum('amount', {
                        where: { order_id: order.order_id },
                    });

                    return {
                        ...order.toJSON(),
                        totalCollectedAmount: totalCollectedAmount || 0,
                    };
                })
        );

        const totalOrders = await Order.count({
            include: [
                {
                    model: Customer,
                    as: 'customer',
                    attributes: [],
                },
                {
                    model: Area,
                    as: 'area',
                    attributes: [],
                },
                {
                    model: Landmark,
                    as: 'landmark',
                    attributes: [],
                },
            ],
            where: findObj,
        });

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

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

function findObject(search, branch_id, collection_day, payment_status = null, from_date = null, to_date = null, area_id = null, product_id = null, landmark_id = null) {
    let findObj = {};

    if (search) {
        findObj[Op.or] = [
            { collection_day: { [Op.substring]: search } },
            { order_no: { [Op.substring]: search } },
            { '$customer.customer_name$': { [Op.substring]: search } },
            { '$customer.mobile_number$': { [Op.substring]: search } },
            { '$area.area_name$': { [Op.substring]: search } },
            { '$landmark.landmark_name$': { [Op.substring]: search } }
        ];
    }

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

    if (branch_id) findObj.branch_id = branch_id;
    if (area_id) findObj.area_id = area_id;
    if (landmark_id) findObj.landmark_id = landmark_id;
    if (payment_status) findObj.payment_status = payment_status;
    if (collection_day) findObj.collection_day = collection_day.replace(/ /g, "_").toUpperCase();

    return findObj;
}

const getOrderId = routeHandler(async (req, res, extras) => {
    const { collection_day } = req.query;
    const { branch_id } = req.auth;
    try {
        // const data = await Order.count({
        //     where: { branch_id, collection_day: collection_day.replace(/ /g, "_").toUpperCase() },
        //     //paranoid: false
        // });

        const lastOrder = await Order.findOne({
            where: {
                branch_id,
                collection_day: collection_day.replace(/ /g, "_").toUpperCase()
            },
            order: [['created_at', 'DESC']],
            attributes: ['order_no']
        });

        let orders = 0;
        if (lastOrder) {
            const numericMatch = lastOrder.order_no.match(/^\d+/);
            orders = numericMatch ? parseInt(numericMatch[0], 10) : 0;
        }

        try {
            const ordersCount = await Order.findOne({
                attributes: ['order_no'],
                where: { branch_id, collection_day: collection_day.replace(/ /g, "_").toUpperCase() },
                order: [['id', 'DESC']],
                limit: 1
            });
            const currentOrderNo = ordersCount ? ordersCount.order_no : 0;
            const numericOrderNo = currentOrderNo.replace(/\D/g, '');
            const adjustedCount = (Number(numericOrderNo) + 1) > 999 ? 1 : Number(numericOrderNo);

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

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

const getById = routeHandler(async (req, res, extras) => {
    const { order_id } = req.params;

    try {
        const order = await Order.findOne({
            where: { order_id },
            include: [
                {
                    model: Customer,
                    as: 'customer',
                    attributes: ['customer_id', 'customer_name', 'mobile_number'],
                },
                {
                    model: Area,
                    as: 'area',
                    attributes: ['area_id', 'area_name'],
                },
                {
                    model: Landmark,
                    as: 'landmark',
                    attributes: ['landmark_id', 'landmark_name'],
                },
                {
                    model: OrderDetails,
                    as: 'orderDetails',
                    include: [
                        {
                            model: Product,
                            as: 'product',
                            attributes: ['product_id', 'product_name'],
                        }
                    ]
                },
                {
                    model: Collection,
                    as: 'collectionDetails',
                    attributes: ['amount'],
                },
            ],
        });
        if (!order) {
            return sendAppError(extras, 'Order not found', STATUS_CODE.NOT_FOUND);
        }
        const totalCollectedAmount = await Collection.sum('amount', {
            where: { order_id }
        });
        const result = {
            ...order.toJSON(),
            totalCollectedAmount: totalCollectedAmount || 0
        };

        return res.sendRes(result, { message: 'Order loaded successfully', status: STATUS_CODE.OK });
    } catch (error) {
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
}, false);

const updateById = routeHandler(async (req, res, extras) => {
    const { order_id } = req.params;
    const { user_id, branch_id } = req.auth;
    const {
        collection_day,
        order_date,
        order_no,
        customer_id,
        customer_name,
        mobile_number,
        area_id,
        landmark_id,
        sub_amount,
        advance_amount,
        total_amount,
        orderDetails
    } = req.body;

    const dayFormat = collection_day.replace(/ /g, "_").toUpperCase();
    const types = ["MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY"];

    if (!types.includes(dayFormat)) {
        return sendAppError(extras, `Invalid type '${collection_day}'. Expected types are: ${types.join(', ')}`, STATUS_CODE.BAD_REQUEST);
    }

    if (!Array.isArray(orderDetails) || orderDetails.length === 0) {
        return sendAppError(extras, NO_PARAMS, STATUS_CODE.BAD_REQUEST);
    }

    try {
        const findOrder = await Order.findOne({
            where: {
                order_no,
                order_id: { [Op.not]: order_id },
                collection_day: dayFormat
            }
        });

        if (findOrder) {
            return sendAppError(extras, 'Order number already exists', STATUS_CODE.BAD_REQUEST);
        }

        const order = await Order.findOne({ where: { order_id } });
        if (!order) {
            return sendAppError(extras, 'Order not found', STATUS_CODE.BAD_REQUEST);
        }

        let customer;
        const findCustomer = customer_id
            ? await Customer.findOne({ where: { customer_id } })
            : null;

        if (!findCustomer && !customer_id) {
            customer = await Customer.create({
                branch_id,
                area_id,
                landmark_id,
                customer_name,
                mobile_number,
                created_by: user_id
            }, { transaction: extras.transaction });
        } else if (findCustomer) {
            customer = await Customer.update({
                area_id,
                landmark_id,
                customer_name,
                mobile_number,
                updated_by: user_id,
            }, {
                where: { customer_id: findCustomer.customer_id },
                transaction: extras.transaction
            });
            customer = findCustomer;
        } else {
            customer = await Customer.findOne({ where: { customer_id } });
        }

        await order.update({
            collection_day: dayFormat,
            order_date: new Date(order_date),
            order_no,
            customer_id: customer.customer_id,
            area_id,
            landmark_id,
            sub_amount,
            advance_amount,
            total_amount,
            updated_by: user_id,
        }, { transaction: extras.transaction });

        const updatePromises = [];
        const createPromises = [];

        orderDetails.forEach(detail => {
            if (detail.order_detail_id) {
                updatePromises.push(
                    OrderDetails.update({
                        product_id: detail.product_id,
                        price: detail.price,
                        quantity: detail.quantity,
                        amount: detail.amount,
                        updated_by: user_id,
                    }, {
                        where: { order_id, order_detail_id: detail.order_detail_id },
                        transaction: extras.transaction
                    })
                );
            } else {
                createPromises.push({
                    order_id,
                    product_id: detail.product_id,
                    price: detail.price,
                    quantity: detail.quantity,
                    amount: detail.amount,
                    created_by: user_id
                });
            }
        });

        await Promise.all(updatePromises);
        if (createPromises.length > 0) {
            await OrderDetails.bulkCreate(createPromises, { transaction: extras.transaction });
        }
        if (advance_amount > 0) {
            const collection = await Collection.findOne({
                where: { order_id: order_id, payment_type: 'Advance' }
            });

            if (collection) {
                await collection.update({
                    amount: advance_amount,
                    updated_by: user_id
                }, { transaction: extras.transaction });
            } else {
                await Collection.create(
                    {
                        branch_id,
                        order_id: order.order_id,
                        area_id: order.area_id,
                        collection_date: new Date(order_date),
                        payment_type: "Advance",
                        payment_mode: "Cash",
                        amount: advance_amount,
                        created_by: user_id,
                    },
                    { transaction: extras.transaction }
                );
            }

            await order.update({
                payment_status: 'Partial',
            }, { transaction: extras.transaction });
        }
        await extras.transaction.commit();
        const updateOrderDetails = await getDetails(order.order_id);
        return res.sendRes(updateOrderDetails, { message: 'Order updated successfully', status: STATUS_CODE.OK });
    } catch (error) {
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

const deleteById = routeHandler(async (req, res, extras) => {
    const { order_id } = req.params;

    try {
        const order = await Order.findOne({ where: { order_id } });
        if (!order) {
            return sendAppError(extras, 'Order not found', STATUS_CODE.NOT_FOUND);
        }

        await order.update({
            deleted_by: req.auth.user_id,
        }, { transaction: extras.transaction });

        await order.destroy({ transaction: extras.transaction });

        const orderDetails = await OrderDetails.findAll({ where: { order_id }, transaction: extras.transaction });

        if (orderDetails.length > 0) {
            await OrderDetails.destroy({ where: { order_id }, transaction: extras.transaction });
        }

        await extras.transaction.commit();
        return res.sendRes(null, { message: 'Order details deleted successfully', status: STATUS_CODE.OK });
    } catch (error) {
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

const deleteOrderDetailById = routeHandler(async (req, res, extras) => {
    const { order_detail_id } = req.params;

    try {
        const orderDetail = await OrderDetails.findOne({ where: { order_detail_id } });
        if (!orderDetail) {
            return sendAppError(extras, 'Order not found', STATUS_CODE.NOT_FOUND);
        }

        await orderDetail.update({
            deleted_by: req.auth.user_id,
        }, { transaction: extras.transaction });

        await orderDetail.destroy({ transaction: extras.transaction });

        await extras.transaction.commit();
        return res.sendRes(null, { message: 'Order Product details deleted successfully', status: STATUS_CODE.OK });
    } catch (error) {
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

const bulkUpload = routeHandler(async (req, res, extras) => {
    const file = req.file;
    const { user_id, branch_id } = req.auth;
    if (!file) {
        return sendAppError(extras, NO_FILE, STATUS_CODE.BAD_REQUEST);
    }
    const filePath = path.join(__dirname, '../../uploads', file.filename || `${Date.now()}_${file.originalname}`);
    if (file.buffer) {
        fs.writeFileSync(filePath, file.buffer);
    }

    const orderData = [];
    const errors = [];
    const stream = fs.createReadStream(filePath);
    try {
        const processingPromises = [];

        await new Promise((resolve, reject) => {
            fastcsv.parseStream(stream, { headers: true })
                .on('data', (row) => {
                    processingPromises.push(
                        (async () => {
                            const { rowRefId, rowCollectionDay, rowOrderDate, rowOrderNo, rowCustomerId, rowAreaId, rowLandMarkId, rowSubAmount, rowTotalAmount, rowStatus, rowDeteteStatus } = row;

                            if (!rowCollectionDay || !rowOrderNo || !rowCustomerId || !rowAreaId || !rowLandMarkId || !rowSubAmount || !rowTotalAmount || !rowStatus || !rowDeteteStatus) {
                                errors.push(`Required for row: ${rowRefId}`);
                            } else {
                                try {
                                    const existingCustomer = await Customer.findOne({ where: { tbl_ref_id: rowCustomerId } });
                                    const existingArea = await Area.findOne({ where: { tbl_ref_id: rowAreaId } });
                                    const existingLandmark = await Landmark.findOne({ where: { tbl_ref_id: rowLandMarkId } });

                                    if (existingCustomer && existingArea && existingLandmark) {
                                        orderData.push({
                                            tbl_ref_id: rowRefId,
                                            branch_id: branch_id,
                                            collection_day: rowCollectionDay.replace(/ /g, "_").toUpperCase(),
                                            order_date: new Date(rowOrderDate),
                                            order_no: rowOrderNo,
                                            customer_id: existingCustomer.customer_id,
                                            area_id: existingArea.area_id,
                                            landmark_id: existingLandmark.landmark_id,
                                            sub_amount: rowSubAmount,
                                            total_amount: rowTotalAmount,
                                            payment_status: rowStatus,
                                            created_by: user_id,
                                            deleted_at: Number(rowDeteteStatus) === 0 ? new Date() : null,
                                            deleted_by: Number(rowDeteteStatus) === 0 ? user_id : null,
                                        });
                                    } else {
                                        errors.push(`Customer or Area or Landmark does not exist for row: ${rowRefId}`);
                                    }
                                } catch (dbError) {
                                    errors.push(`Database error in row: ${rowRefId}`);
                                }
                            }
                        })()
                    );
                })
                .on('end', async () => {
                    try {
                        await Promise.all(processingPromises);
                        resolve();
                    } catch (err) {
                        reject(err);
                    }
                })
                .on('error', (error) => reject(error));
        });

        if (errors.length > 0) {
            return sendAppError(extras, errors.join(', '), STATUS_CODE.BAD_REQUEST);
        }

        const BATCH_SIZE = 1000;
        await extras.transaction.begin();
        try {
            for (let i = 0; i < orderData.length; i += BATCH_SIZE) {
                const batch = orderData.slice(i, i + BATCH_SIZE);
                await Order.bulkCreate(batch, { transaction: extras.transaction });
            }
            await extras.transaction.commit();
            return res.sendRes(null, { message: 'order Data created successfully', status: STATUS_CODE.OK });
        } catch (error) {
            await extras.transaction.rollback();
            return sendAppError(extras, 'Failed to create order Data', STATUS_CODE.INTERNAL_SERVER_ERROR);
        }
    } catch (error) {
        return sendAppError(extras, 'Error processing file', STATUS_CODE.INTERNAL_SERVER_ERROR);
    } finally {
        fs.unlinkSync(filePath);
    }
});

const getAllDeleteData = routeHandler(async (req, res, extras) => {
    const { search, collection_day } = req.query;
    const { branch_id } = req.auth;

    let findObj = findObject(search, branch_id, collection_day);

    findObj.deleted_at = { [Op.not]: null };
    try {
        const orders = await Order.findAll({
            include: [
                {
                    model: Customer,
                    as: 'customer',
                    attributes: ['customer_id', 'customer_name', 'mobile_number'],
                },
                {
                    model: Area,
                    as: 'area',
                    attributes: ['area_id', 'area_name'],
                },
                {
                    model: Landmark,
                    as: 'landmark',
                    attributes: ['landmark_id', 'landmark_name'],
                },
                {
                    model: OrderDetails,
                    as: 'orderDetails',
                    include: [
                        {
                            model: Product,
                            as: 'product',
                            attributes: ['product_id', 'product_name'],
                        }
                    ],
                    paranoid: false,
                    separate: true,
                },
                {
                    model: Collection,
                    as: 'collectionDetails',
                    attributes: ['amount'],
                    separate: true,
                },
                {
                    model: User,
                    as: 'user_deleted',
                    attributes: ['user_id', 'fullname'],
                },
            ],
            where: findObj,
            order: [['id', 'DESC']],
            ...req.paginate,
            paranoid: false
        });

        const ordersWithTotalAmount = await Promise.all(
            orders.map(async (order) => {
                const totalCollectedAmount = await Collection.sum('amount', {
                    where: { order_id: order.order_id },
                });

                return {
                    ...order.toJSON(),
                    totalCollectedAmount: totalCollectedAmount || 0,
                };
            })
        );

        const totalOrders = await Order.count({
            include: [
                {
                    model: Customer,
                    as: 'customer',
                    attributes: [],
                },
                {
                    model: Area,
                    as: 'area',
                    attributes: [],
                },
                {
                    model: Landmark,
                    as: 'landmark',
                    attributes: [],
                },
            ],
            where: findObj,
            paranoid: false
        });

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

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

const restoreById = routeHandler(async (req, res, extras) => {
    const { order_id } = req.params;

    try {
        const order = await Order.findOne({ where: { order_id }, paranoid: false });
        if (!order) {
            return sendAppError(extras, 'Order not found', STATUS_CODE.NOT_FOUND);
        }

        await order.restore({ transaction: extras.transaction });
        await order.update({
            updated_by: req.auth.user_id,
        }, { transaction: extras.transaction });

        await OrderDetails.restore({ where: { order_id }, transaction: extras.transaction });
        await extras.transaction.commit();

        const restoredOrder = await Order.findOne({
            where: { order_id },
            include: [{ model: OrderDetails, as: 'orderDetails' }]
        });
        return res.sendRes(restoredOrder, { message: 'Order details restored successfully', status: STATUS_CODE.OK });
    } catch (error) {
        console.log(error);
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

async function getDetails(id) {
    const order = await Order.findOne({
        where: { order_id: id },
        attributes: [
            'order_id',
            'collection_day',
            'order_date',
            'order_no',
            'sub_amount',
            'advance_amount',
            'total_amount',
            'payment_status',
            'created_at',
            'updated_at',
            'deleted_at',
            'created_by',
            'updated_by',
            'deleted_by',
            'branch_id',
            'customer_id',
            'area_id',
            'landmark_id',
            [fn("concat", col("collection_day"), ' - ', col("order_no")), 'orderNo']
        ],
        include: [
            {
                model: Customer,
                as: 'customer',
                attributes: ['customer_id', 'customer_name', 'mobile_number'],
            },
            {
                model: Area,
                as: 'area',
                attributes: ['area_id', 'area_name'],
            },
            {
                model: Landmark,
                as: 'landmark',
                attributes: ['landmark_id', 'landmark_name'],
            },
            {
                model: OrderDetails,
                as: 'orderDetails',
                include: [
                    {
                        model: Product,
                        as: 'product',
                        attributes: ['product_id', 'product_name'],
                    }
                ],
                separate: true,
            },
            {
                model: Collection,
                as: 'collectionDetails',
                attributes: ['collection_id', 'order_id', 'collection_date', 'payment_type', 'payment_mode', 'amount'],
                include: [
                    {
                        model: User,
                        as: 'user_created',
                        attributes: ['user_id', 'fullname'],
                    },
                    {
                        model: User,
                        as: 'user_updated',
                        attributes: ['user_id', 'fullname'],
                    }
                ],
                separate: true,
            },
            {
                model: User,
                as: 'user_created',
                attributes: ['user_id', 'fullname'],
            },
            {
                model: User,
                as: 'user_updated',
                attributes: ['user_id', 'fullname'],
            },
        ],
    });

    const totalCollectedAmount = await Collection.sum('amount', {
        where: { order_id: order.order_id },
    });

    return {
        ...order.toJSON(),
        totalCollectedAmount: totalCollectedAmount || 0,
    };
}

module.exports = {
    create,
    getAll,
    getById,
    updateById,
    deleteById,
    getOrderId,
    deleteOrderDetailById,
    bulkUpload,
    getAllDeleteData,
    restoreById
};
