const express = require("express");
const router = express.Router();
const { query } = require("../mods/dbconnection");
const config = require("../conf/config.json");
const authMiddleware = require("../middleware/authMiddleware");
const adminMiddleware = require("../middleware/adminMiddleware");
const workerOrAuthMiddleware = require("../middleware/workerOrAuthMiddleware");
const { tableExists } = require("../utils/dbUtils");

const WORK_ORDER_TABLE_NAME = {
    rope2 : 'rope2_logs',
    rope6 : 'rope6_logs',
    belt1 : 'belt1_logs',
    belt2 : 'belt2_logs'
}

router.post("/getWorkHistory", authMiddleware, async (req, res) => {
    try {
        const onlyNotCompleted = req.body.onlyNotCompleted;
        const orderStartDate = req.body.orderStartDate;
        const orderEndDate = req.body.orderEndDate;
        const limitStartCount = req.body.limitStartCount;
        const limitEndCount = req.body.limitEndCount;
        const deliveryStartDate = req.body.deliveryStartDate;
        const deliveryEndDate = req.body.deliveryEndDate;
        const completed = req.body.completed;
        const drum = req.body.drum;
        const swaging = req.body.swaging;
        const bb = req.body.bb;
        const company = req.body.company;
        const num = req.body.num;
        const pjtName = req.body.pjtName;
        const model = req.body.model;
        const facilityNo = req.body.facilityNo;
        const worker = req.body.worker;
        const lotNo = req.body.lotNo;

        const where = [];
        const params = [];

        where.push(`a.created_at BETWEEN ? AND ?`);
        params.push(orderStartDate, orderEndDate);

        const useLimit =
            limitStartCount !== undefined &&
            limitEndCount !== undefined;

        if(onlyNotCompleted){
            where.push(`a.status != ?`);
            params.push("완료");
            if(completed){
                where.push(`a.status = ?`);
                params.push(completed == 'wait' ? '대기' : '부분완료');
            }
        }

        if(!onlyNotCompleted){
            where.push(`a.status = ?`);
            params.push("완료");
        }

        if (deliveryStartDate && deliveryEndDate) {
            where.push(`a.structure_dt BETWEEN ? AND ?`);
            params.push(deliveryStartDate, deliveryEndDate);
        }

        if (pjtName) {
            where.push(`a.pjt_name LIKE ?`);
            params.push(`%${pjtName}%`);
        }

        if (company) {
            where.push(`b.company LIKE ?`);
            params.push(`%${company}%`);
        }

        if (num) {
            where.push(`a.num LIKE ?`);
            params.push(`%${num}%`);
        }

        if (model) {
            where.push(`
                COALESCE(
                    c.spec,
                    j.spec,
                    m.drawing_number,
                    n.shackle_car_idntf,
                    o.shackle_car_idntf
                ) LIKE ?
            `);
            params.push(`%${model}%`);
        }

        if (Number(drum) !== 0) {
            where.push(`a.drum = ?`);
            params.push(Number(drum) === 1 ? 1 : 0);
        }

        if (Number(swaging) !== 0) {
            where.push(`a.swaging = ?`);
            params.push(Number(swaging) === 1 ? 1 : 0);
        }

        if (Number(bb) !== 0) {
            where.push(`a.bb = ?`);
            params.push(Number(bb) === 1 ? 1 : 0);
        }

        if (useLimit) {
            params.push(
                Number(limitStartCount),
                Number(limitEndCount)
            );
        }

        const history = await query(
            `
            SELECT 
                CASE
                    WHEN c.pk IS NOT NULL THEN 'BOX10'
                    WHEN j.pk IS NOT NULL THEN 'jqpr'
                    WHEN m.pk IS NOT NULL THEN '유지보수'
                    WHEN n.pk IS NOT NULL THEN 'BOX10 컴팬'
                    WHEN o.pk IS NOT NULL THEN 'jqpr 컴팬'
                    ELSE NULL
                END AS source_table,
                a.status,
                b.company,
                a.created_at,
                a.structure_dt,
                a.num,
                a.pjt_name,
                COALESCE(
                    c.spec,
                    j.spec,
                    m.drawing_number,
                    n.shackle_car_idntf,
                    o.shackle_car_idntf
                ) AS spec,
                a.idntf,
                a.meter,
                a.bon,
                a.total_length,
                a.drum,
                a.swaging,
                a.bb
            FROM \`${config.dbName.jinsungmes}\`.erp_cutting_list a

            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_master b 
                ON a.parent_id = b.id

            LEFT JOIN \`${config.dbName.jinsungmes}\`.boxten_main c 
                ON CAST(c.pk AS CHAR(20)) COLLATE utf8mb4_general_ci = a.num

            LEFT JOIN \`${config.dbName.jinsungmes}\`.jqpr_install j
                ON CAST(j.pk AS CHAR(20)) COLLATE utf8mb4_general_ci = a.num

            LEFT JOIN \`${config.dbName.jinsungmes}\`.maintenance m
                ON CAST(m.pk AS CHAR(20)) COLLATE utf8mb4_general_ci = a.num

            LEFT JOIN \`${config.dbName.jinsungmes}\`.boxten_compen n
                ON CAST(n.pk AS CHAR(20)) COLLATE utf8mb4_general_ci = a.num

            LEFT JOIN \`${config.dbName.jinsungmes}\`.jqpr_compen o
                ON CAST(o.pk AS CHAR(20)) COLLATE utf8mb4_general_ci = a.num

            WHERE ${where.join("\n AND ")}

            ORDER BY a.created_at DESC

            ${useLimit ? "LIMIT ?, ?" : ""}
            `,
            params
        );

        return res.json({
            ok: true,
            history,
        });

    } catch (err) {
        console.error("[notCompletedWorkHistory ERROR]");
        console.error(err);

        return res.status(500).json({
            ok: false,
            msg: "미완료 작업 이력 조회 중 오류가 발생했습니다.",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});

router.post("/getWorkOrder", workerOrAuthMiddleware, async (req, res) => {
    try{
        const equipId = req.body.equipId
        const tableName = WORK_ORDER_TABLE_NAME[equipId]
        const workOrderRows  = await query(
            `
            SELECT * FROM device_mqtt.\`${tableName}\`
            ORDER BY db_insert_time DESC
            LIMIT 1
            `
        )

        const workOrder = workOrderRows[0];

        if(!workOrder){
            return res.status(400).json({
                ok: true,
                msg: "진행중인 작업이 없습니다.",
            });
        }

        if(workOrder.sub_name == '완료'){
            return res.status(400).json({
                ok: true,
                msg: "진행중인 작업이 없습니다.",
            });
        }

        const workOrderList = await query(
            `
            SELECT 
                b.company,
                a.created_at,
                a.structure_dt,
                a.num,
                a.pjt_name,
                a.idntf,
                a.meter,
                a.bon,
                a.total_length,
                a.drum,
                a.swaging,
                a.bb
            FROM \`${config.dbName.jinsungmes}\`.erp_cutting_list a
            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_master b ON b.id = a.parent_id
            WHERE master_num = ?
            `,
            [workOrder.master_num]
        )

        if(!workOrderList){
            return res.status(400).json({
                ok: true,
                msg: "진행중인 작업이 없습니다.",
            });
        }

        return res.json({
            ok: true,
            workOrderList
        });

    } catch (err){
        console.error(err);

        return res.status(500).json({
            ok: false,
            msg: "진행중인 작업조회 중 오류가 발생했습니다.",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});

router.post("/getEquipWorkStatus", authMiddleware, async (req, res) => {
    try {
        const orderStartDate = req.body.orderStartDate;
        const orderEndDate = req.body.orderEndDate;
        const limitStartCount = req.body.limitStartCount;
        const limitEndCount = req.body.limitEndCount;
        const deliveryStartDate = req.body.deliveryStartDate;
        const deliveryEndDate = req.body.deliveryEndDate;
        const drum = req.body.drum;
        const swaging = req.body.swaging;
        const bb = req.body.bb;
        const company = req.body.company;
        const num = req.body.num;
        const pjtName = req.body.pjtName;
        const model = req.body.model;
        const facilityNo = req.body.facilityNo;
        const worker = req.body.worker;
        const lotNo = req.body.lotNo;

        const where = [];
        const params = [];
        let facilityQuery = '';

        if(orderStartDate && orderEndDate){
            where.push(`b.created_at BETWEEN ? AND ?`);
            params.push(orderStartDate, orderEndDate);
        }

        const useLimit =
            limitStartCount !== undefined &&
            limitEndCount !== undefined;

        if (deliveryStartDate && deliveryEndDate) {
            where.push(`a.structure_dt BETWEEN ? AND ?`);
            params.push(deliveryStartDate, deliveryEndDate);
        }

        if (pjtName) {
            where.push(`a.pjt_name LIKE ?`);
            params.push(`%${pjtName}%`);
        }

        if (company) {
            where.push(`b.company LIKE ?`);
            params.push(`%${company}%`);
        }

        if (num) {
            where.push(`a.num LIKE ?`);
            params.push(`%${num}%`);
        }

        if(facilityNo) {
            facilityQuery = `WHERE x.facility = '${facilityNo}'`
        } else {
            facilityQuery = 'WHERE x.facility IS NOT NULL';
        }

        if (Number(drum) !== 0) {
            where.push(`a.drum = ?`);
            params.push(Number(drum) === 1 ? 1 : 0);
        }

        if (Number(swaging) !== 0) {
            where.push(`a.swaging = ?`);
            params.push(Number(swaging) === 1 ? 1 : 0);
        }

        if (Number(bb) !== 0) {
            where.push(`a.bb = ?`);
            params.push(Number(bb) === 1 ? 1 : 0);
        }

        if (useLimit) {
            params.push(
                Number(limitStartCount),
                Number(limitEndCount)
            );
        }

        // const history = await query(
        //     `
        //     SELECT 
        //         b.company,
        //         a.created_at,
        //         a.structure_dt,
        //         a.num,
        //         a.pjt_name,
        //         a.idntf,
        //         a.meter,
        //         a.bon,
        //         a.total_length,
        //         a.drum,
        //         a.swaging,
        //         a.bb
        //     FROM \`${config.dbName.jinsungmes}\`.erp_cutting_list a
        //     LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_master b ON b.id = a.parent_id

        //     WHERE ${where.join("\n AND ")}

        //     ORDER BY a.created_at DESC

        //     ${useLimit ? "LIMIT ?, ?" : ""}
        //     `,
        //     params
        // );

        const history = await query(
            `
            SELECT *
            FROM (
                SELECT 
                    b.company,
                    a.created_at,
                    a.structure_dt,
                    a.num,
                    a.pjt_name,
                    a.idntf,
                    a.meter,
                    a.bon,
                    a.total_length,
                    a.drum,
                    a.swaging,
                    a.bb,

                    CASE
                        WHEN c.num IS NOT NULL THEN 'belt1'
                        WHEN d.num IS NOT NULL THEN 'rope2'
                        WHEN f.num IS NOT NULL THEN 'rope6'
                        ELSE NULL
                    END AS facility
                    
                FROM jinsungmes.erp_cutting_list a
                LEFT JOIN jinsungmes.erp_cutting_master b 
                    ON b.id = a.parent_id

                LEFT JOIN (
                    SELECT DISTINCT 
                        CAST(num AS CHAR(20)) COLLATE utf8mb4_general_ci AS num,
                        CAST(master_num AS CHAR(50)) COLLATE utf8mb4_general_ci AS master_num
                    FROM belt1.thickness
                ) c 
                    ON c.num = a.num COLLATE utf8mb4_general_ci
                AND c.master_num = b.num COLLATE utf8mb4_general_ci

                LEFT JOIN (
                    SELECT DISTINCT 
                        CAST(num AS CHAR(20)) COLLATE utf8mb4_general_ci AS num,
                        CAST(master_num AS CHAR(50)) COLLATE utf8mb4_general_ci AS master_num
                    FROM rope2.job_update
                ) d 
                    ON d.num = a.num COLLATE utf8mb4_general_ci
                AND d.master_num = b.num COLLATE utf8mb4_general_ci

                LEFT JOIN (
                    SELECT DISTINCT 
                        CAST(num AS CHAR(20)) COLLATE utf8mb4_general_ci AS num,
                        CAST(master_num AS CHAR(50)) COLLATE utf8mb4_general_ci AS master_num
                    FROM rope6.job_update
                ) f 
                    ON f.num = a.num COLLATE utf8mb4_general_ci
                AND f.master_num = b.num COLLATE utf8mb4_general_ci

                WHERE ${where.join("\n AND ")}
                AND a.\`status\` = '완료'
            ) x
            ${facilityQuery}
            ORDER BY x.created_at DESC
            ${useLimit ? "LIMIT ?, ?" : ""}
            `,
            params
        )

        return res.json({
            ok: true,
            history,
        });

    } catch (err) {
        console.error("[getEquipWorkStatus ERROR]");
        console.error(err);

        return res.status(500).json({
            ok: false,
            msg: "미완료 작업 이력 조회 중 오류가 발생했습니다.",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});
router.post("/getPerformanceHistory", authMiddleware, async (req, res) => {
    try {
        const {
            orderStartDate,
            orderEndDate,
            deliveryStartDate,
            deliveryEndDate,
            num,
            pjtName,
            orderNo,
            limitStartCount,
            limitEndCount
        } = req.body;

        const where = [];
        const params = [];

        if (orderStartDate && orderEndDate) {
            where.push(`b.created_at BETWEEN ? AND ?`);
            params.push(orderStartDate, orderEndDate);
        }

        const useLimit =
            limitStartCount !== undefined &&
            limitEndCount !== undefined;

        if (deliveryStartDate && deliveryEndDate) {
            where.push(`b.structure_dt BETWEEN ? AND ?`);
            params.push(deliveryStartDate, deliveryEndDate);
        }

        if (pjtName) {
            where.push(`b.pjt_name LIKE ?`);
            params.push(`%${pjtName}%`);
        }

        if (num) {
            where.push(`a.project_num LIKE ?`);
            params.push(`%${num}%`);
        }

        if (orderNo) {
            where.push(`a.master_num LIKE ?`);
            params.push(`%${orderNo}%`);
        }

        const tableChecks = await Promise.all(
            Object.entries(WORK_ORDER_TABLE_NAME).map(async ([equipId, tableName]) => ({
                equipId,
                tableName,
                exists: await tableExists("device_mqtt", tableName),
            }))
        );

        const existingTables = tableChecks.filter(item => item.exists);

        if (existingTables.length === 0) {
            return res.json({
                ok: true,
                history: [],
            });
        }

        const unionQuery = existingTables
            .map(({ equipId, tableName }) => `
                SELECT
                    '${equipId}' AS equip_id,
                    '${tableName}' AS table_name,
                    master_num,
                    project_num,
                    cutting_length,
                    cutting_bon,
                    cutting_total_bon,
                    db_insert_time
                FROM device_mqtt.\`${tableName}\`
            `)
            .join("\nUNION ALL\n");

        if (useLimit) {
            params.push(
                Number(limitStartCount),
                Number(limitEndCount)
            );
        }

        const history = await query(
            `
            SELECT
                a.equip_id,
                a.table_name,
                COALESCE(NULLIF(a.master_num, ''), b.master_num) AS master_num,
                a.project_num,
                a.cutting_length,
                a.cutting_bon,
                a.cutting_total_bon,
                DATE_FORMAT(b.structure_dt, '%Y-%m-%d %H:%i:%s') as structure_dt,
                b.pjt_name,
                DATE_FORMAT(b.created_at, '%Y-%m-%d %H:%i:%s') as created_at,
                DATE_FORMAT(a.db_insert_time, '%Y-%m-%d %H:%i:%s') as db_insert_time
            FROM (
                ${unionQuery}
            ) a
            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_list b
                ON a.project_num COLLATE utf8mb4_unicode_ci =
                b.num COLLATE utf8mb4_unicode_ci

            ${where.length ? `WHERE ${where.join(" AND ")}` : ""}

            ORDER BY b.structure_dt DESC, a.db_insert_time DESC

            ${useLimit ? "LIMIT ?, ?" : ""}
            `,
            params
        );

        return res.json({
            ok: true,
            history,
        });

    } catch (err) {
        console.error("[performance ERROR]");
        console.error(err);

        return res.status(500).json({
            ok: false,
            msg: "실적 이력 조회 중 오류가 발생했습니다.",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});

router.post("/getFacilityBeltDataHistory", authMiddleware, async (req, res) => {
    try {
        const {
            measureStartTimeRange,
            measureEndTimeRange,
            orderStartDate,
            orderEndDate,
            deliveryStartDate,
            deliveryEndDate,
            company,
            num,
            pjtName,
            passed,
            limitStartCount,
            limitEndCount,
        } = req.body;

        const useLimit =
            limitStartCount !== undefined &&
            limitEndCount !== undefined;

        const where = [];
        const params = [];

        const baseWhere = [];
        const baseParams = [];

        if (measureStartTimeRange && measureEndTimeRange) {
            baseWhere.push(`datetime BETWEEN ? AND ?`);
            baseParams.push(measureStartTimeRange, measureEndTimeRange);
        }

        if (num) {
            baseWhere.push(`num LIKE ?`);
            baseParams.push(`%${num}%`);
        }

        if (orderStartDate && orderEndDate) {
            where.push(`b.created_at BETWEEN ? AND ?`);
            params.push(orderStartDate, orderEndDate);
        }

        if (deliveryStartDate && deliveryEndDate) {
            where.push(`b.structure_dt BETWEEN ? AND ?`);
            params.push(deliveryStartDate, deliveryEndDate);
        }

        if (company) {
            where.push(`c.company LIKE ?`);
            params.push(`%${company}%`);
        }

        if (pjtName) {
            where.push(`b.pjt_name LIKE ?`);
            params.push(`%${pjtName}%`);
        }

        const passCondition = `
            CAST(a.Rope_left AS DECIMAL(10,4)) BETWEEN mm.belt_min AND mm.belt_max
            AND CAST(a.Rope_Center AS DECIMAL(10,4)) BETWEEN mm.belt_min AND mm.belt_max
            AND CAST(a.Rope_Right AS DECIMAL(10,4)) BETWEEN mm.belt_min AND mm.belt_max
        `;

        const beltOutlierExcludeCondition = `
            CAST(a.Rope_left AS DECIMAL(10,4))
                BETWEEN mm.belt_min - ((mm.belt_max - mm.belt_min) * 2)
                AND mm.belt_max + ((mm.belt_max - mm.belt_min) * 2)

            AND CAST(a.Rope_Center AS DECIMAL(10,4))
                BETWEEN mm.belt_min - ((mm.belt_max - mm.belt_min) * 2)
                AND mm.belt_max + ((mm.belt_max - mm.belt_min) * 2)

            AND CAST(a.Rope_Right AS DECIMAL(10,4))
                BETWEEN mm.belt_min - ((mm.belt_max - mm.belt_min) * 2)
                AND mm.belt_max + ((mm.belt_max - mm.belt_min) * 2)
        `;

        where.push(`(${beltOutlierExcludeCondition})`);

        if (passed !== undefined && passed !== null && passed !== "") {
            if (Number(passed) === 1) {
                where.push(`(${passCondition})`);
            }

            if (Number(passed) === 2) {
                where.push(`NOT (${passCondition})`);
            }
        }

        const baseWhereSql = baseWhere.length
            ? `WHERE ${baseWhere.join("\n AND ")}`
            : "";

        const whereSql = where.length
            ? `WHERE ${where.join("\n AND ")}`
            : "";

        /**
         * 중복 제거 전에 가져올 후보 개수
         * 중복이 3~5건 정도 있어서, 화면 30건이면 1000건 정도면 충분할 가능성이 큼
         */
        const candidateLimit = useLimit
            ? Math.max(Number(limitEndCount) * 50, 100000)
            : 5000;

        const finalParams = [
            ...baseParams,
            candidateLimit,
            Number(limitStartCount),
            Number(limitEndCount),
            ...params,
        ];

        const history = await query(
            `
            SELECT 
                a.num,
                a.master_num,
                a.meter,
                a.bon,
                b.meter AS cutting_meter,
                b.bon AS cutting_bon,
                b.idntf,
                a.Rope_left,
                a.Rope_Center,
                a.Rope_Right,
                a.datetime,
                b.structure_dt,
                b.created_at,
                b.pjt_name,
                c.company,

                mm.belt_min,
                mm.belt_max,

                CASE 
                    WHEN ${passCondition}
                    THEN '합격'
                    ELSE '불합격'
                END AS passed

            FROM (
                SELECT *
                FROM (
                    SELECT 
                        t.*,
                        ROW_NUMBER() OVER (
                            PARTITION BY t.meter, t.bon, t.datetime
                            ORDER BY t.no DESC
                        ) AS rn
                    FROM (
                        SELECT *
                        FROM \`belt1\`.thickness
                        ${baseWhereSql}
                        ORDER BY datetime DESC
                        LIMIT ?
                    ) t
                ) ranked
                WHERE ranked.rn = 1
                ORDER BY ranked.datetime DESC
                ${useLimit ? "LIMIT ?, ?" : ""}
            ) a

            JOIN (
                SELECT
                    MAX(CASE WHEN type = 'belt_min' THEN CAST(level AS DECIMAL(10,4)) END) AS belt_min,
                    MAX(CASE WHEN type = 'belt_max' THEN CAST(level AS DECIMAL(10,4)) END) AS belt_max
                FROM \`boas-se\`.tb_sensor_data_min_max
                WHERE type IN ('belt_min', 'belt_max')
            ) mm

            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_list b 
                ON b.num COLLATE utf8mb4_general_ci = a.num
                AND b.master_num COLLATE utf8mb4_general_ci = a.master_num

            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_master c
                ON c.num COLLATE utf8mb4_general_ci = a.master_num

            ${whereSql}

            ORDER BY a.datetime DESC
            `,
            finalParams
        );

        return res.json({
            ok: true,
            history,
        });

    } catch (err) {
        console.error("[getFacilityBeltDataHistory ERROR]");
        console.error(err);

        return res.status(500).json({
            ok: false,
            msg: "벨트 이력 조회 중 오류가 발생했습니다.",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});
router.post("/getFacilityRopeDataHistory", authMiddleware, async (req, res) => {
    try {
        const {
            measureStartTimeRange,
            measureEndTimeRange,
            orderStartDate,
            orderEndDate,
            deliveryStartDate,
            deliveryEndDate,
            company,
            num,
            pjtName,
            passed,
            limitStartCount,
            limitEndCount,
        } = req.body;

        const useLimit =
            limitStartCount !== undefined &&
            limitEndCount !== undefined;

        const baseWhere = [];
        const baseParams = [];

        const where = [];
        const params = [];

        if (measureStartTimeRange && measureEndTimeRange) {
            baseWhere.push(`ju.datetime BETWEEN ? AND ?`);
            baseParams.push(measureStartTimeRange, measureEndTimeRange);
        }

        if (num) {
            baseWhere.push(`ju.num LIKE ?`);
            baseParams.push(`%${num}%`);
        }

        if (orderStartDate && orderEndDate) {
            where.push(`b.created_at BETWEEN ? AND ?`);
            params.push(orderStartDate, orderEndDate);
        }

        if (deliveryStartDate && deliveryEndDate) {
            where.push(`b.structure_dt BETWEEN ? AND ?`);
            params.push(deliveryStartDate, deliveryEndDate);
        }

        if (company) {
            where.push(`c.company LIKE ?`);
            params.push(`%${company}%`);
        }

        if (pjtName) {
            where.push(`b.pjt_name LIKE ?`);
            params.push(`%${pjtName}%`);
        }

        const actualCuttingMeter = `
            CAST(r.cutting_meter AS DECIMAL(10,3)) / 10
        `;

        const passCondition = `
            r.cutting_meter IS NOT NULL
            AND r.meter IS NOT NULL
            AND mm.rope_min IS NOT NULL
            AND mm.rope_max IS NOT NULL
            AND (${actualCuttingMeter})
                BETWEEN
                    CAST(r.meter AS DECIMAL(10,3)) - CAST(mm.rope_min AS DECIMAL(10,3))
                AND
                    CAST(r.meter AS DECIMAL(10,3)) + CAST(mm.rope_max AS DECIMAL(10,3))
        `;

        const ropeOutlierExcludeCondition = `
            ABS(
                (${actualCuttingMeter}) - CAST(r.meter AS DECIMAL(10,3))
            ) <= GREATEST(
                CAST(mm.rope_min AS DECIMAL(10,3)),
                CAST(mm.rope_max AS DECIMAL(10,3))
            ) * 2
        `;

        where.push(`(${ropeOutlierExcludeCondition})`);

        if (passed !== undefined && passed !== null && passed !== "") {
            if (Number(passed) === 1) {
                where.push(`(${passCondition})`);
            }

            if (Number(passed) === 2) {
                where.push(`NOT (${passCondition})`);
            }
        }

        const baseWhereSql = baseWhere.length
            ? `WHERE ${baseWhere.join("\n AND ")}`
            : "";

        const whereSql = where.length
            ? `WHERE ${where.join("\n AND ")}`
            : "";

        const candidateLimit = useLimit
            ? Math.max(Number(limitEndCount) * 50, 1000)
            : 5000;

        const finalParams = [
            ...baseParams, // rope2 WHERE
            ...baseParams, // rope6 WHERE
            candidateLimit,
            ...params,
        ];

        if (useLimit) {
            finalParams.push(
                Number(limitStartCount),
                Number(limitEndCount)
            );
        }

        const history = await query(
            `
            SELECT
                r.source_schema,
                r.id,
                r.master_num,
                r.num,
                r.tag_spec,
                r.lot_drum_meter,
                r.lot_storage_meter,
                r.meter,
                r.bon,

                r.cutting_meter AS raw_cutting_meter,
                ROUND(${actualCuttingMeter}, 1) AS cutting_meter,

                r.cutting_bon,
                r.total_length,
                r.cutting_total_length,
                r.loss_meter,
                r.storage_meter,
                r.worker_id,
                r.status,
                r.datetime,

                b.idntf,
                b.pjt_name,
                b.structure_dt,
                b.created_at,
                b.meter AS order_meter,
                b.bon AS order_bon,

                c.company,

                mm.rope_min,
                mm.rope_max,

                CAST(r.meter AS DECIMAL(10,3)) - CAST(mm.rope_min AS DECIMAL(10,3)) AS pass_min,
                CAST(r.meter AS DECIMAL(10,3)) + CAST(mm.rope_max AS DECIMAL(10,3)) AS pass_max,

                CASE
                    WHEN ${passCondition}
                    THEN '합격'
                    ELSE '불합격'
                END AS passed

            FROM (
                SELECT *
                FROM (
                    SELECT 'rope2' AS source_schema, ju.*
                    FROM \`rope2\`.job_update ju
                    ${baseWhereSql}

                    UNION ALL

                    SELECT 'rope6' AS source_schema, ju.*
                    FROM \`rope6\`.job_update ju
                    ${baseWhereSql}
                ) r_base
                ORDER BY r_base.datetime DESC
                LIMIT ?
            ) r

            JOIN (
                SELECT
                    MAX(CASE WHEN type = 'rope_min' THEN CAST(level AS DECIMAL(10,3)) END) AS rope_min,
                    MAX(CASE WHEN type = 'rope_max' THEN CAST(level AS DECIMAL(10,3)) END) AS rope_max
                FROM \`boas-se\`.tb_sensor_data_min_max
                WHERE type IN ('rope_min', 'rope_max')
            ) mm

            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_list b
                ON b.num COLLATE utf8mb4_general_ci = r.num
                AND b.master_num COLLATE utf8mb4_general_ci = r.master_num

            LEFT JOIN \`${config.dbName.jinsungmes}\`.erp_cutting_master c
                ON c.num COLLATE utf8mb4_general_ci = r.master_num

            ${whereSql}

            ORDER BY r.datetime DESC

            ${useLimit ? "LIMIT ?, ?" : ""}
            `,
            finalParams
        );

        return res.json({
            ok: true,
            history,
        });

    } catch (err) {
        console.error("[getFacilityRopeDataHistory ERROR]");
        console.error(err);

        return res.status(500).json({
            ok: false,
            msg: "로프 이력 조회 중 오류가 발생했습니다.",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});

module.exports = router;