const { Order, OrderDetails, Product, Customer, Area, Landmark, User, 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, QueryTypes } = 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", "Mobile Number", "Area Name", "Landmark Name", "Sub Amount", "Total Amount"];
    const { hasNull, missingParams } = isNull([collection_day, order_date, customer_name, mobile_number, 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 {
        let customer;
        const findCustomer = await Customer.findOne({
            where: {
                mobile_number
            },
        });

        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 });
        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) {
        await extras.transaction.rollback();
        return sendAppError(extras, SERVER_ERROR, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

const getAll = routeHandler(async (req, res, extras) => {
    const { search, collection_day, from_date, to_date, area_id, product_id } = req.query;
    const { branch_id } = req.auth;
    const { offset, limit } = req.paginate;
    try {
        const formattedCollectionDay = collection_day ? collection_day.replace(/ /g, "_").toUpperCase() : null;

        const orderQuery = `
            SELECT 
                t1.order_id, 
                t1.collection_day, 
                t1.order_date, 
                t1.order_no, 
                t1.sub_amount, 
                t1.advance_amount, 
                t1.total_amount, 
                t1.payment_status, 
                t2.customer_name, 
                t2.mobile_number, 
                t3.area_name, 
                t4.landmark_name, 
                t5.fullname AS user_created_by, 
                t6.fullname AS user_updated_by, 
                t1.created_at, 
                t1.updated_at, 
                CONCAT('₹', FORMAT(COALESCE(t7.collected_amount, 0), 2)) AS total_collected_amount,
                t8.product_details
            FROM 
                orders t1
            LEFT JOIN 
                customers t2 ON t1.customer_id = t2.customer_id AND t2.deleted_at IS NULL
            LEFT JOIN 
                areas t3 ON t3.area_id = t1.area_id AND t3.deleted_at IS NULL
            LEFT JOIN 
                landmarks t4 ON t4.landmark_id = t1.landmark_id AND t4.deleted_at IS NULL
            LEFT JOIN 
                users t5 ON t5.user_id = t1.created_by AND t5.deleted_at IS NULL
            LEFT JOIN 
                users t6 ON t6.user_id = t1.updated_by AND t6.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) t7 ON t7.order_id = t1.order_id
            LEFT JOIN 
                (SELECT GROUP_CONCAT(CONCAT(a2.product_name, '(', a1.order_status, ')', ' - ', a1.quantity, '*', a1.price, ' = ', CONCAT('₹', a1.amount)) SEPARATOR ',<br/>') AS product_details, a1.order_id 
                 FROM order_details a1 
                 LEFT JOIN products a2 ON a1.product_id = a2.product_id AND a2.deleted_at IS NULL 
                 WHERE a1.deleted_at IS NULL 
                 GROUP BY a1.order_id) t8 ON t8.order_id = t1.order_id
            ${product_id ? `LEFT JOIN order_details t9 ON t9.order_id = t1.order_id AND t9.deleted_at IS NULL` : ''}
            WHERE 
                t1.deleted_at IS NULL 
                AND t1.branch_id = :branch_id
                ${collection_day ? `AND t1.collection_day = :collection_day` : ''}
                ${search ? `AND (t1.order_no LIKE :search OR t2.customer_name LIKE :search OR t2.mobile_number LIKE :search OR t3.area_name LIKE :search OR t4.landmark_name LIKE :search)` : ''}
                ${from_date && to_date ? `AND t1.order_date BETWEEN :from_date AND :to_date` : ''}
                ${area_id ? `AND t1.area_id = :area_id` : ''}
                ${product_id ? `AND t9.product_id = :product_id` : ''}
            ORDER BY t1.id DESC
            ${offset !== '' && limit !== '' ? `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 (product_id) replacements.product_id = product_id;
        if (area_id) replacements.area_id = area_id;
        if (from_date && to_date) {
            replacements.from_date = new Date(from_date);
            replacements.to_date = new Date(to_date);
        }

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

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

        return res.sendRes(orders, {
            message: 'Orders 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);

const getOrderId = routeHandler(async (req, res, extras) => {
    const { collection_day } = req.query;
    const { branch_id } = req.auth;
    const formattedCollectionDay = collection_day ? collection_day.replace(/ /g, "_").toUpperCase() : null;
    try {
        const orders = await Order.findOne({
            where: { branch_id, collection_day: formattedCollectionDay },
            order: [['id', 'DESC'],],
            attributes: ['order_no'],
            paranoid: false
        });

        return res.sendRes(orders, {
            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);
    }
}, false);

const getById = 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);
        }

        const orderQuery = `
            SELECT 
                t1.order_id, 
                t1.collection_day, 
                t1.order_date, 
                t1.order_no, 
                t1.sub_amount, 
                t1.advance_amount, 
                t1.total_amount, 
                t1.payment_status, 
                t2.customer_name, 
                t2.mobile_number, 
                t3.area_name, 
                t4.landmark_name, 
                t5.fullname AS user_created_by, 
                t6.fullname AS user_updated_by, 
                t1.created_at, 
                t1.updated_at, 
                CONCAT('₹', FORMAT(COALESCE(t7.collected_amount, 0), 2)) AS total_collected_amount,
                t8.product_details
            FROM 
                orders t1
            LEFT JOIN 
                customers t2 ON t1.customer_id = t2.customer_id AND t2.deleted_at IS NULL
            LEFT JOIN 
                areas t3 ON t3.area_id = t1.area_id AND t3.deleted_at IS NULL
            LEFT JOIN 
                landmarks t4 ON t4.landmark_id = t1.landmark_id AND t4.deleted_at IS NULL
            LEFT JOIN 
                users t5 ON t5.user_id = t1.created_by AND t5.deleted_at IS NULL
            LEFT JOIN 
                users t6 ON t6.user_id = t1.updated_by AND t6.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) t7 ON t7.order_id = t1.order_id
            LEFT JOIN 
                (SELECT GROUP_CONCAT(CONCAT(a2.product_name, '(', a1.order_status, ')', ' - ', a1.quantity, '*', a1.price, ' = ', CONCAT('₹', a1.amount)) SEPARATOR ',<br/>') AS product_details, a1.order_id 
                 FROM order_details a1 
                 LEFT JOIN products a2 ON a1.product_id = a2.product_id AND a2.deleted_at IS NULL 
                 WHERE a1.deleted_at IS NULL 
                 GROUP BY a1.order_id) t8 ON t8.order_id = t1.order_id
            WHERE 
                t1.order_id = :order_id`;

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

        return res.sendRes(orderDetails, { 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 } = 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 order = await Order.findOne({ where: { order_id } });

        if (!order) {
            return sendAppError(extras, 'Order not found', STATUS_CODE.NOT_FOUND);
        }

        let customer;
        const findCustomer = await Customer.findOne({
            where: { mobile_number },
        });

        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 });
        }
        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;
    const { offset, limit } = req.paginate;

    try {
        const formattedCollectionDay = collection_day ? collection_day.replace(/ /g, "_").toUpperCase() : null;

        const orderQuery = `
            SELECT 
                t1.order_id, 
                t1.collection_day, 
                t1.order_date, 
                t1.order_no, 
                t1.sub_amount, 
                t1.advance_amount, 
                t1.total_amount, 
                t1.payment_status, 
                t2.customer_name, 
                t2.mobile_number, 
                t3.area_name, 
                t4.landmark_name, 
                t5.fullname AS user_created_by, 
                t6.fullname AS user_updated_by, 
                t1.created_at, 
                t1.updated_at, 
                CONCAT('₹', FORMAT(COALESCE(t7.collected_amount, 0), 2)) AS total_collected_amount,
                t8.product_details
            FROM 
                orders t1
            LEFT JOIN 
                customers t2 ON t1.customer_id = t2.customer_id AND t2.deleted_at IS NULL
            LEFT JOIN 
                areas t3 ON t3.area_id = t1.area_id AND t3.deleted_at IS NULL
            LEFT JOIN 
                landmarks t4 ON t4.landmark_id = t1.landmark_id AND t4.deleted_at IS NULL
            LEFT JOIN 
                users t5 ON t5.user_id = t1.created_by AND t5.deleted_at IS NULL
            LEFT JOIN 
                users t6 ON t6.user_id = t1.updated_by AND t6.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) t7 
            ON 
                t7.order_id = t1.order_id
            LEFT JOIN 
                (SELECT GROUP_CONCAT(CONCAT(a2.product_name, '(', a1.order_status, ')', ' - ', a1.quantity, '*', a1.price, ' = ', CONCAT('₹', a1.amount)) SEPARATOR ',<br/>') AS product_details, a1.order_id 
                 FROM order_details a1 
                 LEFT JOIN products a2 ON a1.product_id = a2.product_id AND a2.deleted_at IS NULL 
                 WHERE a1.deleted_at IS NOT NULL
                 GROUP BY a1.order_id) t8 ON t8.order_id = t1.order_id
            WHERE 
                t1.deleted_at IS NOT NULL 
                AND t1.branch_id = :branch_id
                ${collection_day ? `AND t1.collection_day = :collection_day` : ''}
                ${search ? `AND (t1.order_no LIKE :search OR t2.customer_name LIKE :search OR t2.mobile_number LIKE :search OR t3.area_name LIKE :search OR t4.landmark_name LIKE :search)` : ''}
            ORDER BY t1.id DESC
            ${offset && limit ? `LIMIT :limit OFFSET :offset` : ''}`;

        const replacements = { branch_id, offset, limit };
        if (collection_day) {
            replacements.collection_day = formattedCollectionDay;
        }
        if (search) {
            replacements.search = `%${search}%`;
        }
        if (offset && limit) {
            replacements.offset = offset;
            replacements.limit = limit;
        }

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

        return res.sendRes(orders, {
            message: 'Orders loaded successfully',
            ...req.meta,
            total: await Order.count({ where: { branch_id, collection_day: formattedCollectionDay }, paranoid: false }),
            status: STATUS_CODE.OK,
        });

    } catch (error) {
        if (extras.transaction) {
            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) {
    return await Order.findOne({
        where: { order_id: 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: User,
                as: 'user_created',
                attributes: ['user_id', 'fullname'],
            },
            {
                model: User,
                as: 'user_updated',
                attributes: ['user_id', 'fullname'],
            },
        ],
    });
}

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