const { Collection, User, Order, sequelize, Area, Customer } = require("../../models");
const { NO_FILE, 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 fastcsv = require('fast-csv');
const path = require('path');
const fs = require('fs');

const create = routeHandler(async (req, res, extras) => {
    const {
        order_id,
        collection_date,
        payment_mode,
        amount
    } = req.body;

    const { user_id, branch_id } = req.auth;
    const paramNames = ["Order Id", "Collection Date"];
    const { hasNull, missingParams } = isNull([order_id, collection_date]);
    if (hasNull) {
        const missingParamNames = missingParams.map(index => paramNames[index]);
        return sendAppError(extras, `${missingParamNames.join(', ')} ${missingParamNames.length > 1 ? 'are' : 'is'} required`, STATUS_CODE.BAD_REQUEST);
    }

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

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

        if (Number(orderDetails.sub_amount) <= (Number(totalCollectedAmount) + Number(amount))) {
            await orderDetails.update({
                payment_status: 'Paid',
            }, { transaction });
        } else {
            await orderDetails.update({
                payment_status: 'Partial',
            }, { transaction });
        }

        const collection = await Collection.create({
            branch_id,
            area_id: orderDetails.area_id,
            order_id,
            collection_date: new Date(collection_date),
            payment_mode,
            amount,
            created_by: user_id
        }, { transaction });

        await transaction.commit();
        const newCollectionDetails = await getDetails(collection.collection_id);
        return res.sendRes(newCollectionDetails, { message: 'Collection created successfully', status: STATUS_CODE.OK });
    } catch (error) {
        console.error('Error creating collection:', error);
        if (transaction) {
            await transaction.rollback();
        }
        return sendAppError(extras, SERVER_ERROR, STATUS_CODE.INTERNAL_SERVER_ERROR);
    }
}, false);

const getAll = routeHandler(async (req, res, extras) => {
    const { search, order_id } = req.query;
    const { branch_id } = req.auth;
    let findObj = findObject(search, branch_id, order_id);
    try {
        const collections = await Collection.findAll({
            where: findObj,
            include: [
                {
                    model: User,
                    as: 'user_created',
                    attributes: ['user_id', 'fullname'],
                },
                {
                    model: User,
                    as: 'user_updated',
                    attributes: ['user_id', 'fullname'],
                },
            ],
            order: [['created_at', 'DESC'],],
            ...req.paginate,
        });

        return res.sendRes(collections, {
            message: 'collections loaded successfully',
            ...req.meta,
            total: await Collection.count({ where: findObj }),
            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, order_id) {
    let findObj = {};

    if (search) {
        const startDate = new Date(search);
        const endDate = new Date(search);
        endDate.setDate(endDate.getDate() + 1);
        findObj[Op.or] = [
            { payment_mode: { [Op.substring]: search } },
            { collection_date: { [Op.between]: [startDate, endDate] } }
        ];
    }

    if (branch_id && branch_id != 'undefined') findObj.branch_id = branch_id;
    if (order_id && order_id != 'undefined') findObj.order_id = order_id;

    return findObj;
}

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

    try {
        const collection = await Collection.findOne({
            where: { collection_id }
        });

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

        return res.sendRes(collection, { message: 'Collection 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 { collection_id } = req.params;
    const { user_id } = req.auth;
    const { collection_date, payment_mode, amount } = req.body;

    try {
        const collection = await Collection.findOne({
            where: { collection_id },
        });

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

        const orderDetails = await Order.findOne({ where: { order_id: collection.order_id } });
        const totalCollectedAmount = await Collection.sum('amount', {
            where: {
                order_id: collection.order_id,
                collection_id: { [Op.ne]: collection_id },
            },
            transaction: extras.transaction
        });

        if (Number(orderDetails.sub_amount) <= (Number(totalCollectedAmount) + Number(amount))) {
            await orderDetails.update({
                payment_status: 'Paid',
            }, { transaction: extras.transaction });
        } else {
            await orderDetails.update({
                payment_status: 'Partial',
            }, { transaction: extras.transaction });
        }

        await collection.update({
            collection_date: new Date(collection_date),
            payment_mode,
            amount,
            updated_by: user_id,
        }, { transaction: extras.transaction });

        await extras.transaction.commit();
        const collectionDetails = await getDetails(collection.collection_id);
        return res.sendRes(collectionDetails, { message: 'Collection details 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 { collection_id } = req.params;

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

        const orderDetails = await Order.findOne({ where: { order_id: collection.order_id } });
        const totalCollectedAmount = await Collection.sum('amount', {
            where: {
                order_id: collection.order_id,
                collection_id: { [Op.ne]: collection_id },
            },
            transaction: extras.transaction
        });

        if (Number(orderDetails.sub_amount) <= Number(totalCollectedAmount)) {
            await orderDetails.update({
                payment_status: 'Paid',
            }, { transaction: extras.transaction });
        } else {
            await orderDetails.update({
                payment_status: 'Partial',
            }, { transaction: extras.transaction });
        }

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

        await collection.destroy({ transaction: extras.transaction });
        await extras.transaction.commit();
        return res.sendRes(null, { message: 'Collection 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 collectionsData = [];
    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, rowPaymentDate, rowPaymentType, rowAmount } = row;

                            if (!rowOrderId || !rowPaymentDate || !rowAmount) {
                                errors.push(`This are required for row: ${rowRefId}`);
                            } else {
                                try {
                                    const existingOrder = await Order.findOne({ where: { tbl_ref_id: rowOrderId }, paranoid: false, });

                                    if (existingOrder) {
                                        collectionsData.push({
                                            tbl_ref_id: rowRefId,
                                            branch_id: branch_id,
                                            order_id: existingOrder.order_id,
                                            collection_date: new Date(rowPaymentDate),
                                            payment_type: rowPaymentType === 'dfg' ? 'Advance' : 'Collection',
                                            amount: rowAmount,
                                            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 < collectionsData.length; i += BATCH_SIZE) {
                const batch = collectionsData.slice(i, i + BATCH_SIZE);
                await Collection.bulkCreate(batch, { transaction: extras.transaction });
            }
            await extras.transaction.commit();
            return res.sendRes(null, { message: 'Collection Data created successfully', status: STATUS_CODE.OK });
        } catch (error) {
            await extras.transaction.rollback();
            return sendAppError(extras, 'Failed to create Collection Data', STATUS_CODE.INTERNAL_SERVER_ERROR);
        }
    } catch (error) {
        return sendAppError(extras, 'Error processing file', STATUS_CODE.INTERNAL_SERVER_ERROR);
    } finally {
        fs.unlinkSync(filePath);
    }
});

async function getDetails(id) {
    return await Collection.findOne({
        where: { collection_id: id },
        include: [
            {
                model: User,
                as: 'user_created',
                attributes: ['user_id', 'fullname'],
            },
            {
                model: User,
                as: 'user_updated',
                attributes: ['user_id', 'fullname'],
            },
        ],
    });
}

const getCollectionReport = routeHandler(async (req, res, extras) => {
    const { search, from_date, to_date } = req.query;
    const { branch_id } = req.auth;
    const { offset, limit } = req.paginate;
    try {
        const userQuery = `
            SELECT 
                t1.user_id,
                t1.fullname,
                t2.collection_date,
                COALESCE(SUM(t2.amount), 0) AS total_amount
            FROM 
                users t1
            LEFT JOIN 
                (SELECT 
                    SUM(a2.amount) AS amount, 
                    MAX(a2.collection_date) AS collection_date, 
                    a2.created_by 
                 FROM 
                    collections a2
                 LEFT JOIN 
                    orders a1 ON a2.order_id = a1.order_id
                 WHERE 
                   -- a1.deleted_at IS NULL
                    -- AND a2.deleted_at IS NULL AND
                    a2.payment_type = 'Collection'
                    ${from_date && to_date ? `AND a2.collection_date BETWEEN :from_date AND :to_date` : ''}
                    GROUP BY a2.created_by, a2.collection_date
                ) AS t2 
                ON t2.created_by = t1.user_id
            WHERE 
                t1.deleted_at IS NULL
                AND t1.branch_id = :branch_id
                ${search ? `AND t1.fullname LIKE :search` : ''}
            GROUP BY  
                 t1.user_id, t1.fullname, t2.collection_date
            ORDER BY 
                t2.collection_date DESC
            ${typeof offset === 'number' && typeof limit === 'number' ? `LIMIT :limit OFFSET :offset` : ''}`;

        const replacements = { branch_id };

        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 = formatDate(from_date);
            replacements.to_date = formatDate(to_date);
        }

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

        const totalUserQuery = `
                SELECT 
                    COUNT(DISTINCT t1.user_id) AS total_count
                FROM 
                    users t1
                LEFT JOIN 
                    (SELECT 
                        a2.created_by 
                    FROM 
                        collections a2
                    LEFT JOIN 
                        orders a1 ON a2.order_id = a1.order_id
                    WHERE 
                      -- a1.deleted_at IS NULL
                      -- AND a2.deleted_at IS NULL AND 
                        a2.payment_type = 'Collection'
                        ${from_date && to_date ? `AND a2.collection_date BETWEEN :from_date AND :to_date` : ''}
                    GROUP BY a2.created_by
                    ) AS t2 
                    ON t2.created_by = t1.user_id
                WHERE 
                    t1.deleted_at IS NULL
                    AND t1.branch_id = :branch_id
                    ${search ? `AND t1.fullname LIKE :search` : ''}`;

        const totalReplacements = { branch_id };

        if (search) totalReplacements.search = `%${search}%`;
        if (from_date && to_date) {
            totalReplacements.from_date = formatDate(from_date);
            totalReplacements.to_date = formatDate(to_date);
        }

        const totalCountResult = await sequelize.query(totalUserQuery, {
            replacements: totalReplacements,
            type: QueryTypes.SELECT,
        });
        const totalCount = totalCountResult[0]?.total_count || 0;

        const usersData = await Promise.all(
            users
                .filter(users => users.collection_date && users.collection_date !== null)
        );

        return res.sendRes(usersData, {
            message: 'User-wise collections loaded successfully',
            ...req.meta,
            total: totalCount,
            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 getCollectionReportUser = routeHandler(async (req, res, extras) => {
    const { user_id, collection_date } = req.query;
    const { branch_id } = req.auth;
    try {
        const collections = await Collection.findAll({
            where: {
                branch_id,
                created_by: user_id,
                collection_date: formatDate(collection_date),
                payment_type: 'Collection',
            },
            paranoid: false,
            include: [
                {
                    model: Order,
                    as: 'order',
                    attributes: ['order_id', 'collection_day', 'order_date', 'order_no'],
                    paranoid: false,
                    include: [{
                        model: Customer,
                        as: 'customer',
                        attributes: ['customer_id', 'customer_name', 'mobile_number'],
                        paranoid: false,
                    },]
                },
                {
                    model: User,
                    as: 'user_created',
                    attributes: ['user_id', 'fullname'],
                    paranoid: false,
                },
                {
                    model: User,
                    as: 'user_updated',
                    attributes: ['user_id', 'fullname'],
                    paranoid: false,
                },
            ],
            order: [['created_at', 'DESC'],],
            ...req.paginate,
        });

        return res.sendRes(collections, {
            message: 'User collections loaded successfully',
            ...req.meta,
            total: await Collection.count({
                where: {
                    branch_id,
                    created_by: user_id,
                    collection_date: formatDate(collection_date)
                },
                paranoid: false,
            }),
            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 getCollectionReportArea = routeHandler(async (req, res, extras) => {
    const { user_id, collection_date } = req.query;
    const { branch_id } = req.auth;
    const { offset, limit } = req.paginate;

    try {
        const replacements = { branch_id, user_id };
        if (collection_date) {
            replacements.collection_date = formatDate(collection_date);
        }
        if (typeof offset === 'number' && typeof limit === 'number') {
            replacements.offset = offset;
            replacements.limit = limit;
        }

        const areaQuery = `
        SELECT 
            COALESCE(SUM(t1.amount), 0) AS total_amount,
            t2.area_name,
            t2.area_id
        FROM 
            collections t1
        LEFT JOIN
            areas t2 ON t1.area_id = t2.area_id
        INNER JOIN 
            orders t3 ON t3.order_id = t1.order_id
        WHERE 
            t1.deleted_at IS NULL AND 
            t2.deleted_at IS NULL
            AND t1.payment_type = 'Collection'
            AND t1.branch_id = :branch_id
            AND t1.created_by = :user_id
            AND t1.collection_date = :collection_date
        GROUP BY  
            t2.area_id, t2.area_name
        ORDER BY 
            t2.area_id DESC
        ${typeof offset === 'number' && typeof limit === 'number' ? `LIMIT :limit OFFSET :offset` : ''}`;

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

        const totalUserQuery = `
        SELECT 
            COUNT(DISTINCT t1.area_id) AS total_count
        FROM 
            collections t1
        LEFT JOIN
            areas t2 ON t1.area_id = t2.area_id
        INNER JOIN 
            orders t3 ON t3.order_id = t1.order_id
        WHERE 
          t1.deleted_at IS NULL AND
            t2.deleted_at IS NULL
            AND t1.payment_type = 'Collection'
            AND t1.branch_id = :branch_id
            AND t1.created_by = :user_id
            AND t1.collection_date = :collection_date`;

        const totalCountResult = await sequelize.query(totalUserQuery, {
            replacements,
            type: QueryTypes.SELECT,
        });
        const totalCount = totalCountResult[0]?.total_count || 0;

        return res.sendRes(areas, {
            message: 'Area-wise collections loaded successfully',
            ...req.meta,
            total: totalCount,
            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 formatDate = (date) => {
    const d = new Date(date);
    let month = '' + (d.getMonth() + 1);
    let day = '' + d.getDate();
    const year = d.getFullYear();

    if (month.length < 2) month = '0' + month;
    if (day.length < 2) day = '0' + day;

    return [year, month, day].join('-');
};

module.exports = {
    create,
    getAll,
    getById,
    updateById,
    deleteById,
    bulkUpload,
    getCollectionReport,
    getCollectionReportUser,
    getCollectionReportArea
};
