const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
const erpService = require("../erps/erp.service");
require("dotenv").config();

module.exports = {
    getAll,
    getAllShipper,
    getAllCosignee,
    filter,
    option,
    count,
    create,
    update,
    enabled,
    completed,
    delete: _delete,
};

function normalizeIdArray(value) {
    if (value == null) return [];
    const arr = Array.isArray(value) ? value : [value];
    return arr
        .map((v) => parseInt(v, 10))
        .filter((n) => Number.isFinite(n));
}

/** View all data when access_read_all_data OR administrator_of_pirlo is true. */
function isViewAll(params) {
    const a = params.access_read_all_data === true || params.access_read_all_data === "true" || params.access_read_all_data === 1;
    const b = params.administrator_of_pirlo === true || params.administrator_of_pirlo === "true" || params.administrator_of_pirlo === 1;
    return Boolean(a || b);
}

async function enrichGoodsReceiptsWithEmployeeNames(goodsReceipts) {
    const rows = Array.isArray(goodsReceipts) ? goodsReceipts : [];

    const rowsPromises = rows.map(async (goodsReceipt) => {
        const row = goodsReceipt?.dataValues != null ? goodsReceipt.dataValues : goodsReceipt;
        if (!row || typeof row !== "object") return {};

        const shipperId = row.shipper != null ? parseInt(row.shipper, 10) : null;
        const cosigneeId = row.cosignee != null ? parseInt(row.cosignee, 10) : null;

        const shipperPromise =
            Number.isFinite(shipperId) && shipperId > 0
                ? dbSAPPS.Employee.findOne({
                      where: { id: shipperId },
                      attributes: ["id", "employee_name", "company_id"],
                      include: [
                          {
                              model: dbSAPPS.Company,
                              as: "company",
                              attributes: ["id", "display_name"],
                          },
                      ],
                  })
                : null;

        const cosigneePromise =
            Number.isFinite(cosigneeId) && cosigneeId > 0
                ? dbSAPPS.Employee.findOne({
                      where: { id: cosigneeId },
                      attributes: ["id", "employee_name", "company_id"],
                      include: [
                          {
                              model: dbSAPPS.Company,
                              as: "company",
                              attributes: ["id", "display_name"],
                          },
                      ],
                  })
                : null;

        const [shipper, cosignee] = await Promise.all([shipperPromise, cosigneePromise]);

        return {
            ...row,
            shipper_name: shipper?.employee_name ?? null,
            cosignee_name: cosignee?.employee_name ?? null,
            shipper_company_name: shipper?.company?.display_name ?? null,
            cosignee_company_name: cosignee?.company?.display_name ?? null,
        };
    });

    const result = await Promise.all(rowsPromises);
    return Array.isArray(result) ? result : [];
}

/**
 * Enrich rows with mr_approval_date (MR modified when docstatus=1) and po_creation_date from ERPNext.
 * po_creation_date is taken from Purchase Order "creation" only (when PO was created in ERPNext).
 * Uses first API key from apikeys table. If no key or ERP fails, leaves fields null.
 */
async function enrichGoodsReceiptsWithErpDates(rows) {
    const list = Array.isArray(rows) ? rows : [];
    if (list.length === 0) return list;

    let apiKeyRow;
    try {
        apiKeyRow = await dbSAPPS.ApiKey.findOne({ order: [["id", "ASC"]] });
    } catch {
        return list.map((r) => ({ ...r, mr_approval_date: null, po_creation_date: null }));
    }
    if (!apiKeyRow?.api_url || !apiKeyRow?.api_key || !apiKeyRow?.api_secret) {
        return list.map((r) => ({ ...r, mr_approval_date: null, po_creation_date: null }));
    }

    const params = {
        api_url: String(apiKeyRow.api_url || "").trim() || null,
        api_key: apiKeyRow.api_key,
        api_secret: apiKeyRow.api_secret,
    };
    if (!params.api_url) {
        return list.map((r) => ({ ...r, mr_approval_date: null, po_creation_date: null }));
    }

    const mrNames = [...new Set(list.map((r) => (r.material_request_name != null ? String(r.material_request_name).trim() : "")).filter(Boolean))];
    const poNames = [...new Set(list.map((r) => (r.purchase_order_name != null ? String(r.purchase_order_name).trim() : "")).filter(Boolean))];

    let mrMap = {};
    let poMap = {};
    try {
        const [mrRes, poRes] = await Promise.all([
            mrNames.length ? erpService.getMaterialRequestsByNames({ ...params, names: mrNames }) : Promise.resolve({ success: true, data: {} }),
            poNames.length ? erpService.getPurchaseOrdersByNames({ ...params, names: poNames }) : Promise.resolve({ success: true, data: {} }),
        ]);
        if (mrRes?.success && mrRes.data && typeof mrRes.data === "object") mrMap = mrRes.data;
        if (poRes?.success && poRes.data && typeof poRes.data === "object") poMap = poRes.data;
    } catch {
        mrMap = {};
        poMap = {};
    }

    return list.map((row) => {
        const mrKey = row.material_request_name != null ? String(row.material_request_name).trim() : "";
        const poKey = row.purchase_order_name != null ? String(row.purchase_order_name).trim() : "";
        const mr = mrKey ? (mrMap[row.material_request_name] ?? mrMap[mrKey]) : null;
        const po = poKey ? (poMap[row.purchase_order_name] ?? poMap[poKey]) : null;
        const poCreationDate = po?.creation != null ? po.creation : null;
        return {
            ...row,
            mr_approval_date: mr?.modified ?? null,
            po_creation_date: poCreationDate,
        };
    });
}

async function getAll(params) {
    try {
        const filterConditions = {};
        const viewAll = isViewAll(params);
        const employeesId = normalizeIdArray(params.employeesId ?? params.employeeIds);
        const companyId = params.company_id ? parseInt(params.company_id, 10) : null;
        const companiesId = normalizeIdArray(params.companiesId ?? params.companyIds);

        if (viewAll) {
            filterConditions.shipper = { [Op.ne]: null };
        } else if (employeesId.length > 0) {
            filterConditions.shipper = { [Op.in]: employeesId };
        } else if (params.employee_id != null && params.employee_id !== "") {
            filterConditions.shipper = { [Op.eq]: parseInt(params.employee_id, 10) };
        } else {
            filterConditions.shipper = { [Op.eq]: -1 };
        }

        if (Number.isFinite(companyId)) {
            filterConditions[Op.or] = [{ shipper_company: { [Op.eq]: companyId } }, { cosignee_company: { [Op.eq]: companyId } }];
        } else if (companiesId.length > 0 && !viewAll) {
            filterConditions[Op.or] = [{ shipper_company: { [Op.in]: companiesId } }, { cosignee_company: { [Op.in]: companiesId } }];
        }

        const goodsReceipts = await dbSAPPS.GoodsReceipt.findAll({
            where: filterConditions,
            order: [["modified", "DESC"]],
        });

        let enriched = await enrichGoodsReceiptsWithEmployeeNames(goodsReceipts);
        enriched = await enrichGoodsReceiptsWithErpDates(enriched);

        return {
            goodsReceipts: Array.isArray(enriched) ? enriched : [],
            success: true,
            message: "goods receipts loaded successfully!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to load goods receipts!";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function getAllShipper(params) {
    try {
        const filterConditions = {};
        const viewAll = isViewAll(params);
        const employeesId = normalizeIdArray(params.employeesId ?? params.employeeIds);
        const companyId = params.company_id ? parseInt(params.company_id, 10) : null;
        const companiesId = normalizeIdArray(params.companiesId ?? params.companyIds);

        if (viewAll) {
            filterConditions.shipper = { [Op.ne]: null };
        } else if (employeesId.length > 0) {
            filterConditions.shipper = { [Op.in]: employeesId };
        } else if (params.employee_id != null && params.employee_id !== "") {
            filterConditions.shipper = { [Op.eq]: parseInt(params.employee_id, 10) };
        } else {
            filterConditions.shipper = { [Op.eq]: -1 };
        }

        if (Number.isFinite(companyId)) {
            filterConditions[Op.or] = [{ shipper_company: { [Op.eq]: companyId } }, { cosignee_company: { [Op.eq]: companyId } }];
        } else if (companiesId.length > 0 && !viewAll) {
            filterConditions.shipper_company = { [Op.in]: companiesId };
        }

        const goodsReceipts = await dbSAPPS.GoodsReceipt.findAll({
            where: filterConditions,
            order: [["modified", "DESC"]],
        });

        let enriched = await enrichGoodsReceiptsWithEmployeeNames(goodsReceipts);
        enriched = await enrichGoodsReceiptsWithErpDates(enriched);

        return {
            goodsReceipts: Array.isArray(enriched) ? enriched : [],
            success: true,
            message: "goods receipts loaded successfully!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to load goods receipts!";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function getAllCosignee(params) {
    try {
        const filterConditions = {};
        const viewAll = isViewAll(params);
        const employeesId = normalizeIdArray(params.employeesId ?? params.employeeIds);
        const companyId = params.company_id ? parseInt(params.company_id, 10) : null;
        const companiesId = normalizeIdArray(params.companiesId ?? params.companyIds);

        if (viewAll) {
            filterConditions.cosignee = { [Op.ne]: null };
        } else if (employeesId.length > 0) {
            filterConditions.cosignee = { [Op.in]: employeesId };
        } else if (params.employee_id != null && params.employee_id !== "") {
            filterConditions.cosignee = { [Op.eq]: parseInt(params.employee_id, 10) };
        } else {
            filterConditions.cosignee = { [Op.eq]: -1 };
        }

        if (Number.isFinite(companyId)) {
            filterConditions[Op.or] = [{ shipper_company: { [Op.eq]: companyId } }, { cosignee_company: { [Op.eq]: companyId } }];
        } else if (companiesId.length > 0 && !viewAll) {
            filterConditions.cosignee_company = { [Op.in]: companiesId };
        }

        const goodsReceipts = await dbSAPPS.GoodsReceipt.findAll({
            where: filterConditions,
            order: [["modified", "DESC"]],
        });

        let enriched = await enrichGoodsReceiptsWithEmployeeNames(goodsReceipts);
        enriched = await enrichGoodsReceiptsWithErpDates(enriched);

        return {
            goodsReceipts: Array.isArray(enriched) ? enriched : [],
            success: true,
            message: "goods receipts loaded successfully!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to load goods receipts!";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function filter(params) {
    try {
        const viewAll = isViewAll(params);
        const employeesId = normalizeIdArray(params.employeesId ?? params.employeeIds);
        const companyId = params.company_id ? parseInt(params.company_id, 10) : null;
        const companiesId = normalizeIdArray(params.companiesId ?? params.companyIds);

        const andParts = [];

        if (viewAll) {
            andParts.push({ [Op.or]: [{ shipper: { [Op.ne]: null } }, { cosignee: { [Op.ne]: null } }] });
        } else if (employeesId.length > 0) {
            andParts.push({ [Op.or]: [{ shipper: { [Op.in]: employeesId } }, { cosignee: { [Op.in]: employeesId } }] });
        } else if (params.employee_id != null && params.employee_id !== "") {
            const employeeId = parseInt(params.employee_id, 10);
            andParts.push({ [Op.or]: [{ shipper: { [Op.eq]: employeeId } }, { cosignee: { [Op.eq]: employeeId } }] });
        } else {
            andParts.push({ shipper: { [Op.eq]: -1 }, cosignee: { [Op.eq]: -1 } });
        }

        if (Number.isFinite(companyId)) {
            andParts.push({ [Op.or]: [{ shipper_company: { [Op.eq]: companyId } }, { cosignee_company: { [Op.eq]: companyId } }] });
        } else if (companiesId.length > 0 && !viewAll) {
            andParts.push({ [Op.or]: [{ shipper_company: { [Op.in]: companiesId } }, { cosignee_company: { [Op.in]: companiesId } }] });
        }

        const where = andParts.length === 1 ? andParts[0] : { [Op.and]: andParts };

        const goodsReceipts = await dbSAPPS.GoodsReceipt.findAll({
            where,
            order: [["modified", "DESC"]],
        });

        let enriched = await enrichGoodsReceiptsWithEmployeeNames(goodsReceipts);
        enriched = await enrichGoodsReceiptsWithErpDates(enriched);

        return {
            success: true,
            goodsReceipts: Array.isArray(enriched) ? enriched : [],
            message: "goods receipts loaded successfully!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to retrieve goods receipts. Please try again later.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function option() {
    try {
        const goodsReceipts = await dbSAPPS.GoodsReceipt.findAll({
            attributes: ["id", "material_request_name", "purchase_order_name", "item_code", "item_name"],
            order: [["modified", "DESC"]],
        });

        return {
            success: true,
            goodsReceipts,
            message: "goods receipts loaded successfully!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to retrieve goods receipts. Please try again later.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function count() {
    try {
        const count = await dbSAPPS.GoodsReceipt.count();

        return {
            success: true,
            count,
            message: "goods receipts loaded successfully!",
        };
    } catch (error) {
        const errorMessage = error.message || "Failed to retrieve goods receipts. Please try again later.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function create(params) {
    const transaction = await dbSAPPS.sequelize.transaction();
    try {
        if (!params.material_request_name || !params.purchase_order_name || !params.item_code || !params.item_name)
            return {
                success: false,
                message: "material request name, purchase order name, item code, and item name are required!",
            };

        const existing = await dbSAPPS.GoodsReceipt.findOne({
            where: {
                material_request_name: params.material_request_name,
                purchase_order_name: params.purchase_order_name,
                item_code: params.item_code,
                item_name: params.item_name
            },
        });

        if (existing)
            return {
                success: false,
                message: `goods receipt with material request name "${params.material_request_name}", purchase order name "${params.purchase_order_name}", item code "${params.item_code}", and item name "${params.item_name}" is already taken.`,
            };

        if (!params.item_status || params.item_status === "") params.item_status = "On Delivery";
        if (!params.status || params.status === "") params.status = "In Process";

        await dbSAPPS.GoodsReceipt.create(params, { transaction });
        await transaction.commit();
        return {
            success: true,
            message: "goods receipt data has been successfully added!",
        };
    } catch (error) {
        await transaction.rollback();
        const errorMessage = error?.message || "An error occurred during create goods receipt processing.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function update(id, params) {
    const pk = id != null ? parseInt(id, 10) : NaN;
    if (!Number.isFinite(pk) || pk < 1) {
        return { success: false, message: "Invalid goods receipt id." };
    }
    const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const goodsReceipt = await dbSAPPS.GoodsReceipt.findByPk(pk);
        if (!goodsReceipt)
            return {
                success: false,
                message: "goods receipt not found!",
            };

        const existing = await dbSAPPS.GoodsReceipt.findOne({
            where: {
                material_request_name: params.material_request_name,
                purchase_order_name: params.purchase_order_name,
                item_code: params.item_code,
                item_name: params.item_name
            }
        });

        if (existing) {
            if (existing.id !== pk && String(existing.id) !== String(params?.id)) {
                return {
                    success: false,
                    message: `goods receipt with material request name "${params.material_request_name}", purchase order name "${params.purchase_order_name}", item code "${params.item_code}", and item name "${params.item_name}" is already taken.`,
                };
            }
        }

        params.item_status = "Delivered";

        await goodsReceipt.update(params, { transaction });
        await transaction.commit();
        return {
            success: true,
            message: "Data goods receipt has been saved!",
        };
    } catch (error) {
        await transaction.rollback();
        const errorMessage = error?.message || "Failed to update goods receipt data.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function enabled(id, params) {
    const pk = id != null ? parseInt(id, 10) : NaN;
    if (!Number.isFinite(pk) || pk < 1) {
        return { success: false, message: "Invalid goods receipt id." };
    }
    try {
        const goodsReceipt = await dbSAPPS.GoodsReceipt.findByPk(pk);
        if (!goodsReceipt)
            return {
                success: false,
                message: "goods receipt not found!",
            };

        const updateData = {};
        updateData.enabled = params.enabled;
        if (params.modified_by) updateData.modified_by = params.modified_by;
        if (params.modified) updateData.modified = params.modified;

        await goodsReceipt.update(updateData);

        return {
            success: true,
            message: "goods receipt has been updated!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to update goods receipt.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function completed(id, params) {
    const pk = id != null ? parseInt(id, 10) : NaN;
    if (!Number.isFinite(pk) || pk < 1) {
        return { success: false, message: "Invalid goods receipt id." };
    }
    try {
        const goodsReceipt = await dbSAPPS.GoodsReceipt.findByPk(pk);
        if (!goodsReceipt)
            return {
                success: false,
                message: "goods receipt not found!",
            };

        const updateData = {};
        updateData.status = params.status;
        if (params.modified_by) updateData.modified_by = params.modified_by;
        if (params.modified) updateData.modified = params.modified;

        await goodsReceipt.update(updateData);

        return {
            success: true,
            message: "goods receipt has been updated!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to update goods receipt.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

async function _delete(id) {
    const pk = id != null ? parseInt(id, 10) : NaN;
    if (!Number.isFinite(pk) || pk < 1) {
        return { success: false, message: "Invalid goods receipt id." };
    }
    try {
        const goodsReceipt = await dbSAPPS.GoodsReceipt.findByPk(pk);
        if (!goodsReceipt) return { success: false, message: "goods receipt not found!" };

        await goodsReceipt.destroy();
        return {
            success: true,
            message: "Goods receipt successfully deleted!",
        };
    } catch (error) {
        const errorMessage = error?.message || "Failed to delete goods receipt.";
        return {
            success: false,
            message: errorMessage,
        };
    }
}

