import sql from "mssql";
import { tryCatch } from "../../globalUtils/tryCatch.js";
import { db } from "../../../database/dbclient.js";
import { settings } from "../../../database/schema/settings.js";
import { eq } from "drizzle-orm";
import { format } from "date-fns-tz";

const username = "gpviewer";
const password = "gp$$ViewOnly!";

const sqlGPConfig = {
    server: "USMCD1VMS011",
    database: `ALPLA`,
    user: username,
    password: password,
    options: {
        encrypt: true,
        trustServerCertificate: true,
    },
    requestTimeout: 90000,
};

type GPCheck = {
    startDate: string;
    endDate: string;
    gpCode: string;
};
export const runGPQuery = async (gpCheck: GPCheck) => {
    let pool2: sql.ConnectionPool | null = null;

    try {
        // Create a brand-new pool, not touching the "global" one
        pool2 = new sql.ConnectionPool(sqlGPConfig);
        await pool2.connect();

        const query = `
            select * from (
                select 
                    case when x.POPRCTNM is null then p.POPRCTNM else p.POPRCTNM end as RCT_Num,
                    PONUMBER PO,
                    p.VENDORID Supplier,
                    ITEMNMBR Item,
                    QTYSHPPD shipped, 
                    UOFM Type,
                    TRXLOCTN Location,
                case when CONVERT(DATE, x.receiptdate) is null then convert(date, p.DATERECD) else CONVERT(DATE, x.receiptdate) end as  Date_Recived 
                from ALPLA.dbo.pop10500 (nolock) as p
                left join 
                ALPLA.dbo.POP10300 as x on p.POPRCTNM = x.POPRCTNM
                WHERE TRXLOCTN LIKE '[gpCode]%' and p.POPTYPE = 1) a
    where Date_Recived BETWEEN '[startDate]' AND '[endDate]'
        `;

        const { data: s, error: se } = (await tryCatch(
            db.select().from(settings).where(eq(settings.name, "plantToken"))
        )) as any;

        if (se) {
            console.log("Error getting articles");
            return se;
        }

        const result = await pool2
            .request()

            .query(
                query
                    .replace("[startDate]", gpCheck.startDate)
                    .replace("[endDate]", gpCheck.endDate)
                    .replace("[gpCode]", gpCheck.gpCode)
            );

        return {
            success: true,
            message: "GP data",
            data: result.recordset.map((n: any) => {
                return {
                    plantToken: s[0].value,
                    ...n,
                    RCT_Num: n.RCT_Num.trim(),
                    PO: n.PO.trim(),
                    Supplier: n.Supplier.trim(),
                    Item: n.Item.trim(),
                    // article:
                    //     n.Item.split("-").length > 1
                    //         ? n.Item.split("-")[1].trim()
                    //         : 0,
                    article: Number.parseInt(n.Item.split("-")[1]) || 0,
                    Type: n.Type.trim(),
                    Location: n.Location.trim(),
                    Date_Recived: format(n.Date_Recived, "M/d/yyyy"),
                };
            }),
        };
    } catch (error) {
        console.log(error);
        return {
            success: false,
            message: "Error Getting GP data",
            data: error,
        };
    } finally {
        if (pool2) await pool2.close(); // Always close the pool
    }
};