import { db } from "../../../../../../../database/dbclient.js";
import { settings } from "../../../../../../../database/schema/settings.js";
import { tryCatch } from "../../../../../../globalUtils/tryCatch.js";
import XLSX from "xlsx";
import { excelDateStuff } from "../../../../utils/excelDateStuff.js";
import { postForecast } from "../postForecast.js";
import { query } from "../../../../../sqlServer/prodSqlServer.js";
import { activeArticle } from "../../../../../sqlServer/querys/dataMart/article.js";
import { addDays } from "date-fns";

let customerID = 4;
export const lorealForecast = async (data: any, user: any) => {
    /**
     * Post a standard forecast based on the standard template.
     */

    const { data: s, error: e } = await tryCatch(db.select().from(settings));

    if (e) {
        return {
            sucess: false,
            message: `Error getting settings`,
            data: e,
        };
    }

    const plantToken = s.filter((s) => s.name === "plantToken");

    const arrayBuffer = await data.arrayBuffer();
    const buffer = Buffer.from(arrayBuffer);

    const workbook = XLSX.read(buffer, { type: "buffer" });

    const sheet: any = workbook.Sheets["Alpla HDPE"];
    const range = XLSX.utils.decode_range(sheet["!ref"]);

    const psheet: any = workbook.Sheets["Alpla PET"];
    const prange = XLSX.utils.decode_range(psheet["!ref"]);

    const headers = [];
    for (let C = range.s.c; C <= range.e.c; ++C) {
        const cellAddress = XLSX.utils.encode_cell({ r: 1, c: C }); // row 0 = Excel row 1
        const cell = sheet[cellAddress];
        headers.push(cell ? cell.v : undefined);
    }

    const pheaders = [];
    for (let C = prange.s.c; C <= prange.e.c; ++C) {
        const cellAddress = XLSX.utils.encode_cell({ r: 1, c: C }); // row 0 = Excel row 1
        const cell = psheet[cellAddress];
        pheaders.push(cell ? cell.v : undefined);
    }

    const ebmForeCastData: any = XLSX.utils.sheet_to_json(sheet, {
        defval: "",
        header: headers,
        range: 3,
    });

    const petForeCastData: any = XLSX.utils.sheet_to_json(psheet, {
        defval: "",
        header: pheaders,
        range: 3,
    });

    const ebmForecastData: any = [];
    const missingSku: any = [];

    const { data: a, error: ae } = await tryCatch(
        query(activeArticle, "Loreal calling active av")
    );

    if (ae) {
        return {
            success: false,
            message: "Error getting active av",
            data: [],
        };
    }

    const article: any = a?.data;

    // process the ebm forcast
    for (let i = 0; i < ebmForeCastData.length; i++) {
        // bottle code
        const sku = ebmForeCastData[i]["HDPE Bottle Code"];

        // ignore the blanks
        if (sku === "") continue;

        // ignore zero qty
        // if (ebmForeCastData[i][`Day ${i}`]) continue;

        for (let f = 0; f <= 90; f++) {
            const day = `Day ${f + 1}`;
            // if (ebmForeCastData[i][day] === 0) continue;

            const forcast = {
                customerArticleNo: sku,
                requirementDate: addDays(new Date(Date.now()), f), //excelDateStuff(parseInt(date)),
                quantity: ebmForeCastData[i][day] ?? 0,
            };

            if (forcast.quantity === 0) continue;

            // checking to make sure there is a real av to add to.
            const activeAV = article.filter(
                (c: any) =>
                    c?.CustomerArticleNumber ===
                    forcast.customerArticleNo.toString()
            );

            if (activeAV.length === 0) {
                continue;
            }

            ebmForecastData.push(forcast);
        }

        //console.log(ebmForeCastData.length);
    }

    // petForeCastData.forEach((item: any, index: any) => {
    //     //console.log(`Processing item ${index + 1} of ${forecastData.length}`);

    //     // Extract the customer code
    //     const customerCode = item["SOUTH PET BOTTLES"];

    //     // Process each date in the current object
    //     for (const [date, qty] of Object.entries(item)) {
    //         // Skip metadata fields
    //         if (petMetadataFields.includes(date)) continue;

    //         if (qty === 0) continue;

    //         // Create your transformed record
    //         const record = {
    //             customerArticleNo: customerCode,
    //             requirementDate: excelDateStuff(parseInt(date)),
    //             quantity: qty,
    //         };

    //         // Do something with this record
    //         petForecastData.push(record);
    //     }
    // });

    // pet forecast
    for (let i = 0; i < petForeCastData.length; i++) {
        // bottle code
        const sku = petForeCastData[i]["South PET Bottle Code"];

        // ignore the blanks
        if (sku === "") continue;

        // ignore zero qty
        // if (ebmForeCastData[i][`Day ${i}`]) continue;

        for (let f = 0; f <= 90; f++) {
            const day = `Day ${f + 1}`;
            // if (ebmForeCastData[i][day] === 0) continue;

            const forcast = {
                customerArticleNo: sku,
                requirementDate: addDays(new Date(Date.now()), f), //excelDateStuff(parseInt(date)),
                quantity: petForeCastData[i][day] ?? 0,
            };

            if (forcast.quantity === 0 || forcast.quantity === "") continue;

            if (forcast.customerArticleNo < 99999) {
                //console.log(`Sku a normal av ${forcast.customerArticleNo}`);
                continue;
            }

            // checking to make sure there is a real av to add to.
            const activeAV = article.filter(
                (c: any) =>
                    c?.CustomerArticleNumber ===
                    forcast.customerArticleNo.toString()
            );

            if (activeAV.length === 0) {
                continue;
            }

            ebmForecastData.push(forcast);
        }
    }

    //console.log(comForecast);

    // if the customerarticle number is not matching just ignore it
    const predefinedObject = {
        receivingPlantId: plantToken[0].value,
        documentName: `ForecastFromLST-${new Date(Date.now()).toLocaleString(
            "en-US"
        )}`,
        sender: user.username || "lst-system",
        customerId: customerID,
        positions: [],
    };

    let updatedPredefinedObject = {
        ...predefinedObject,
        positions: [
            ...predefinedObject.positions,
            ...ebmForecastData,

            // ...ebmForecastData.filter(
            //     (q: any) =>
            //         q.customerArticleNo != "" && q.customerArticleNo != "Total"
            // ),
            // ...petForecastData.filter(
            //     (q: any) =>
            //         q.customerArticleNo != "" && q.customerArticleNo != "Total"
            // ),
        ],
    };
    // console.log(updatedPredefinedObject);
    const posting: any = await postForecast(updatedPredefinedObject, user);

    return {
        success: posting.success,
        message: posting.message,
        data: posting.data === "" ? ebmForecastData : posting.data,
    };
};
