const { Order, Product, Customer, Area, Landmark, User, OrderReturn, OrderReturnDetails, OrderDetails, 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, literal, 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 { order_id, return_date, total_amount, orderReturnDetails } = req.body;
    const { user_id, branch_id } = req.auth;

    const paramNames = ["Order Id", "Return Date", "Total Amount"];
    const { hasNull, missingParams } = isNull([order_id, return_date, total_amount]);

    if (hasNull) {
        const missingParamNames = missingParams.map(index => paramNames[index]);
        return sendAppError(extras, `${missingParamNames.join(', ')} ${missingParamNames.length > 1 ? 'are' : 'is'} required`, STATUS_CODE.BAD_REQUEST);
    }

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

    const transaction = extras.transaction || await sequelize.transaction();

    try {
        const orderReturn = await OrderReturn.create({
            branch_id,
            order_id,
            return_date: new Date(return_date),
            total_amount,
            created_by: user_id
        }, { transaction });

        let totalAmount = 0;

        const updateOrderDetailsPromises = orderReturnDetails.map(async (detail) => {
            const orderDetails = await OrderDetails.findOne({ where: { order_detail_id: detail.order_detail_id } });
            if (!orderDetails) throw new Error(`Order detail with ID ${detail.order_detail_id} not found`);

            if (detail.type === 'Cash') {
                totalAmount += detail.amount;
                return orderDetails.update({
                    quantity: orderDetails.quantity - detail.quantity,
                    amount: orderDetails.amount - detail.amount,
                    order_status: 'Cash',
                }, { transaction });
            } else if (detail.type === 'Exchange') {
                return orderDetails.update({ order_status: 'Exchange' }, { transaction });
            }
        });

        await Promise.all(updateOrderDetailsPromises);
        await Order.update({
            sub_amount: literal(`sub_amount - ${totalAmount}`),
            total_amount: literal(`total_amount - ${totalAmount}`)
        }, { where: { order_id }, transaction });

        const tableRowsData = orderReturnDetails.map(row => ({
            return_id: orderReturn.return_id,
            order_detail_id: row.order_detail_id,
            product_id: row.product_id,
            price: row.price,
            quantity: row.quantity,
            amount: row.amount,
            type: row.type,
            created_by: user_id
        }));
        await OrderReturnDetails.bulkCreate(tableRowsData, { transaction });
        await transaction.commit();

        const newOrderReturnDetails = await OrderReturn.findOne({
            where: { return_id: orderReturn.return_id },
            include: [{ model: OrderReturnDetails, as: 'orderReturnDetails' }]
        });

        return res.sendRes(newOrderReturnDetails, { message: 'Order Return created successfully', status: STATUS_CODE.OK });
    } catch (error) {
        await transaction.rollback();
        console.error('Error processing order return:', error);
        return sendAppError(extras, SERVER_ERROR, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
});

const getAll = 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 orderReturnQuery = `
                SELECT 
                    t1.return_id, 
                    t2.collection_day, 
                    t1.return_date, 
                    t2.order_no, 
                    t1.total_amount, 
                    t3.customer_name, 
                    t3.mobile_number, 
                    t4.area_name, 
                    t5.landmark_name, 
                    t6.fullname AS user_created_by, 
                    t7.fullname AS user_updated_by, 
                    t1.created_at, 
                    t1.updated_at, 
                    t8.product_details
                FROM 
                    order_return t1
                LEFT JOIN 
                    orders t2 ON t1.order_id = t2.order_id AND t2.deleted_at IS NULL
                LEFT JOIN 
                    customers t3 ON t3.customer_id = t2.customer_id AND t3.deleted_at IS NULL
                LEFT JOIN 
                    areas t4 ON t4.area_id = t2.area_id AND t4.deleted_at IS NULL
                LEFT JOIN 
                    landmarks t5 ON t5.landmark_id = t2.landmark_id AND t5.deleted_at IS NULL
                LEFT JOIN 
                    users t6 ON t6.user_id = t1.created_by AND t6.deleted_at IS NULL
                LEFT JOIN 
                    users t7 ON t7.user_id = t1.updated_by AND t7.deleted_at IS NULL
                LEFT JOIN 
                    (SELECT GROUP_CONCAT(CONCAT(a2.product_name, '(', a1.type, ')', ' - ', a1.quantity, '*', a1.price, ' = ', CONCAT('₹', a1.amount)) SEPARATOR ',<br/>') AS product_details, a1.return_id 
                     FROM order_return_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.return_id) t8 ON t8.return_id = t1.return_id
                WHERE 
                    t1.deleted_at IS NULL 
                    AND t1.branch_id = :branch_id
                    ${collection_day ? `AND t2.collection_day = :collection_day` : ''}
                    ${search ? `AND (t2.order_no LIKE :search OR t3.customer_name LIKE :search OR t3.mobile_number LIKE :search OR t4.area_name LIKE :search OR t5.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 orderReturn = await sequelize.query(orderReturnQuery, {
            replacements,
            type: QueryTypes.SELECT,
        });

        return res.sendRes(orderReturn, {
            message: 'Order return loaded successfully',
            ...req.meta,
            total: await OrderReturn.count({
                where: { branch_id },
                include: [
                    {
                        model: Order,
                        as: 'order',
                        attributes: [],
                        where: { deleted_at: null, ...(collection_day && { collection_day: formattedCollectionDay }) },
                    },
                ],
            }),
            status: STATUS_CODE.OK,
        });

    } catch (error) {
        console.error('Error fetching orders:', error);
        await extras.transaction.rollback();
        return sendAppError(extras, error.message, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
}, false);

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

    try {
        const orderReturn = await OrderReturn.findOne({
            where: { return_id },
            include: [
                {
                    model: Order,
                    as: 'order',
                    attributes: ['order_id', 'collection_day', 'order_no'],
                    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: OrderReturnDetails,
                    as: 'orderReturnDetails',
                    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'],
                },
            ],
        });

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

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

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

    const transaction = extras.transaction || await sequelize.transaction();
    try {
        const orderReturn = await OrderReturn.findOne({ where: { return_id } });
        if (!orderReturn) {
            return sendAppError(extras, 'Order return not found', STATUS_CODE.NOT_FOUND);
        }
        await orderReturn.update({
            deleted_by: user_id,
        }, { transaction });
        const orderReturnDetails = await OrderReturnDetails.findAll({ where: { return_id }, transaction });

        let totalAmount = 0;
        const updateOrderDetailsPromises = orderReturnDetails.map(async (detail) => {
            const orderDetails = await OrderDetails.findOne({ where: { order_detail_id: detail.order_detail_id }, transaction });
            if (!orderDetails) throw new Error(`Order detail with ID ${detail.order_detail_id} not found`);

            if (detail.type === 'Cash') {
                totalAmount += detail.amount;
                return orderDetails.update({
                    quantity: orderDetails.quantity + detail.quantity,
                    amount: orderDetails.amount + detail.amount,
                    order_status: 'New',
                }, { transaction });
            } else if (detail.type === 'Exchange') {
                return orderDetails.update({ order_status: 'New' }, { transaction });
            }
        });

        await Promise.all(updateOrderDetailsPromises);
        await Order.update({
            sub_amount: literal(`sub_amount + ${totalAmount}`),
            total_amount: literal(`total_amount + ${totalAmount}`)
        }, { where: { order_id: orderReturn.order_id }, transaction });

        if (orderReturnDetails.length > 0) {
            await OrderReturnDetails.destroy({ where: { return_id }, transaction });
        }

        await orderReturn.destroy({ transaction });
        await transaction.commit();
        return res.sendRes(null, { message: 'Order return and details deleted successfully', status: STATUS_CODE.OK });
    } catch (error) {
        await 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 orderReturnData = [];
    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, rowOrderId, rowReturnDate, rowTotalAmount } = row;

                            if (!rowOrderId || !rowReturnDate || !rowTotalAmount) {
                                errors.push(`Order Id, Order Date and Total amount are required for row: ${rowRefId}`);
                            } else {
                                try {
                                    const existingOrder = await Order.findOne({ where: { tbl_ref_id: rowOrderId }, paranoid: false, });

                                    if (existingOrder) {
                                        orderReturnData.push({
                                            tbl_ref_id: rowRefId,
                                            branch_id: branch_id,
                                            order_id: existingOrder.order_id,
                                            return_date: new Date(rowReturnDate),
                                            total_amount: rowTotalAmount,
                                            created_by: user_id
                                        });
                                    } else {
                                        errors.push(`Order 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 < orderReturnData.length; i += BATCH_SIZE) {
                const batch = orderReturnData.slice(i, i + BATCH_SIZE);
                await OrderReturn.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);
    }
});

module.exports = {
    create,
    getAll,
    getById,
    deleteById,
    bulkUpload
};
