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");

router.post("/getCompletedWorkCount", authMiddleware, async (req, res) => {
    try {
        const { startDate, endDate } = req.body;

        if (!startDate || !endDate) {
            return res.status(400).json({
                ok: false,
                msg: "startDate, endDate가 필요합니다.",
            });
        }

        const rows = await query(
            `
            SELECT COUNT(*) AS count
            FROM \`jinsungmes\`.erp_cutting_list
            WHERE created_at BETWEEN ? AND ?
              AND status = '완료'
            `,
            [startDate, endDate]
        );

        return res.json({
            ok: true,
            count: rows[0].count,
        });

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

        return res.status(500).json({
            ok: false,
            msg: "오류 발생",
            error: process.env.NODE_ENV === "development"
                ? err.message
                : undefined,
        });
    }
});

router.post("/getFaultRateTotal", authMiddleware, async (req, res) => {
    try {
        const {
            measureStartTimeRange,
            measureEndTimeRange,
        } = req.body;

        const params = [];

        let beltDateCondition = `a.datetime >= DATE_SUB(NOW(), INTERVAL 1 MONTH)`;
        let ropeDateCondition = `r.datetime >= DATE_SUB(NOW(), INTERVAL 1 MONTH)`;

        if (measureStartTimeRange && measureEndTimeRange) {
            beltDateCondition = `a.datetime BETWEEN ? AND ?`;
            ropeDateCondition = `r.datetime BETWEEN ? AND ?`;

            params.push(
                measureStartTimeRange,
                measureEndTimeRange,
                measureStartTimeRange,
                measureEndTimeRange
            );
        }

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

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

        const ropePassCondition = `
            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 (${ropeActualCuttingMeter})
                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 beltOutlierExcludeCondition = `
            CAST(a.Rope_left AS DECIMAL(10,3))
                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,3))
                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,3))
                BETWEEN mm.belt_min - ((mm.belt_max - mm.belt_min) * 2)
                AND mm.belt_max + ((mm.belt_max - mm.belt_min) * 2)
        `;

        const ropeOutlierExcludeCondition = `
            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 ABS(
                (${ropeActualCuttingMeter}) - 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
        `;

        const rows = await query(
            `
            WITH belt_total AS (
                SELECT
                    COUNT(*) AS totalCount,
                    SUM(CASE WHEN NOT (${beltPassCondition}) THEN 1 ELSE 0 END) AS faultCount
                FROM \`belt1\`.thickness a

                INNER JOIN (
                    SELECT MAX(no) AS no
                    FROM \`belt1\`.thickness
                    GROUP BY meter, bon, datetime
                ) x ON x.no = a.no

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

                WHERE ${beltDateCondition}
                    AND (${beltOutlierExcludeCondition})
            ),

            rope_total AS (
                SELECT
                    COUNT(*) AS totalCount,
                    SUM(CASE WHEN NOT (${ropePassCondition}) THEN 1 ELSE 0 END) AS faultCount
                FROM (
                    SELECT 'rope2' AS source_schema, ju.*
                    FROM \`rope2\`.job_update ju

                    UNION ALL

                    SELECT 'rope6' AS source_schema, ju.*
                    FROM \`rope6\`.job_update ju
                ) 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

                WHERE ${ropeDateCondition}
                    AND (${ropeOutlierExcludeCondition})
            ),

            merged_total AS (
                SELECT totalCount, faultCount FROM belt_total
                UNION ALL
                SELECT totalCount, faultCount FROM rope_total
            )

            SELECT
                SUM(totalCount) AS totalCount,
                SUM(faultCount) AS faultCount,
                ROUND(
                    IFNULL(SUM(faultCount) / NULLIF(SUM(totalCount), 0) * 100, 0),
                    2
                ) AS faultRate
            FROM merged_total
            `,
            params
        );

        return res.json({
            ok: true,
            data: {
                totalCount: Number(rows[0]?.totalCount ?? 0),
                faultCount: Number(rows[0]?.faultCount ?? 0),
                faultRate: Number(rows[0]?.faultRate ?? 0),
            },
        });

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

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

module.exports = router;