// ===== WORK ORDER BUTTONS FUNCTION (MODIFIED WITH DB PERSISTENCE) =====

switch (msg.action) {
    case "upload-excel":
        msg._mode = "upload";
        return [msg, null, null, null, null];  // Added 5th output for DB sync

    case "refresh-work-orders":
        msg._mode = "select";
        msg.topic = "SELECT * FROM work_orders ORDER BY created_at DESC;";
        return [null, msg, null, null, null];

    case "start-work-order": {
        msg._mode = "start";
        const order = msg.payload || {};
        if (!order.id) {
            node.error("No work order id supplied for start", msg);
            return [null, null, null, null, null];
        }
        msg.startOrder = order;

        msg.topic = `
            UPDATE work_orders
            SET
                status = CASE
                    WHEN work_order_id = '${order.id}' THEN 'RUNNING'
                    ELSE 'PENDING'
                END,
                updated_at = CASE
                    WHEN work_order_id = '${order.id}' THEN NOW()
                    ELSE updated_at
                END
            WHERE status <> 'DONE';
        `;

        global.set("activeWorkOrder", order);
        global.set("cycleCount", 0);
        flow.set("lastMachineState", 0);
        global.set("scrapPromptIssuedFor", null);

        // *** NEW: Create new session ID and sync to DB ***
        const newSessionId = "session_" + Date.now();
        global.set("currentSessionId", newSessionId);
        const dbSyncMsg = createDbSyncMessage(true);  // forced sync on work order start

        return [null, null, msg, null, dbSyncMsg];
    }

    case "complete-work-order": {
        msg._mode = "complete";
        const order = msg.payload || {};
        if (!order.id) {
            node.error("No work order id supplied for complete", msg);
            return [null, null, null, null, null];
        }
        msg.completeOrder = order;
        msg.topic = `
            UPDATE work_orders
            SET status = 'DONE', updated_at = NOW()
            WHERE work_order_id = '${order.id}';
        `;

        // *** NEW: Deactivate current session before clearing ***
        const deactivateMsg = {
            _mode: "deactivate-session",
            topic: `
                UPDATE session_state
                SET is_active = 0, updated_at = ${Date.now()}
                WHERE session_id = '${global.get("currentSessionId")}';
            `
        };

        global.set("activeWorkOrder", null);
        global.set("operatingTime", 0);
        global.set("lastCycleTime", null);
        global.set("cycleCount", 0);
        flow.set("lastMachineState", 0);
        global.set("scrapPromptIssuedFor", null);
        global.set("currentSessionId", null);

        return [null, null, null, msg, deactivateMsg];
    }

    case "scrap-entry": {
        const { id, scrap } = msg.payload || {};
        const scrapNum = Number(scrap) || 0;

        if (!id) {
            node.error("No work order id supplied for scrap entry", msg);
            return [null, null, null, null, null];
        }

        const activeOrder = global.get("activeWorkOrder");
        if (activeOrder && activeOrder.id === id) {
            activeOrder.scrap = (Number(activeOrder.scrap) || 0) + scrapNum;
            global.set("activeWorkOrder", activeOrder);
        }

        global.set("scrapPromptIssuedFor", null);

        msg._mode = "scrap-update";
        msg.scrapEntry = { id, scrap: scrapNum };
        msg.topic = `
            UPDATE work_orders
            SET
              scrap_parts = scrap_parts + ${scrapNum},
              updated_at = NOW()
            WHERE work_order_id = '${id}';
        `;

        // *** NEW: Sync to DB after scrap update ***
        const dbSyncMsg = createDbSyncMessage(true);  // forced sync

        return [null, null, msg, null, dbSyncMsg];
    }

    case "scrap-skip": {
        const { id, remindAgain } = msg.payload || {};

        if (!id) {
            node.error("No work order id supplied for scrap skip", msg);
            return [null, null, null, null, null];
        }

        if (remindAgain) {
            global.set("scrapPromptIssuedFor", null);
        }

        msg._mode = "scrap-skipped";
        return [null, null, null, null, null];
    }

    case "start": {
        // START button clicked from Home dashboard
        global.set("trackingEnabled", true);
        global.set("productionStartTime", Date.now());
        global.set("operatingTime", 0);
        global.set("lastCycleTime", Date.now());

        const activeOrder = global.get("activeWorkOrder") || {};
        msg._mode = "production-state";
        msg.productionStarted = true;
        msg.machineOnline = true;

        // *** NEW: Sync to DB on production start ***
        const dbSyncMsg = createDbSyncMessage(true);  // forced sync

        return [null, msg, null, null, dbSyncMsg];
    }

    case "stop": {
        // Manual STOP button clicked from Home dashboard
        global.set("trackingEnabled", false);

        // *** NEW: Sync to DB on production stop ***
        const dbSyncMsg = createDbSyncMessage(true);  // forced sync

        return [null, null, null, null, dbSyncMsg];
    }
}

// ========================================
// HELPER FUNCTION: Create DB Sync Message
// ========================================
function createDbSyncMessage(forceUpdate) {
    const now = Date.now();
    const lastSync = flow.get("lastSessionSync") || 0;

    // Throttle: only sync every 5 seconds unless forced
    if (!forceUpdate && (now - lastSync) < 5000) {
        return null;
    }

    flow.set("lastSessionSync", now);

    const sessionId = global.get("currentSessionId") || "session_" + Date.now();
    global.set("currentSessionId", sessionId);

    const activeOrder = global.get("activeWorkOrder");
    const activeOrderId = activeOrder ? activeOrder.id : null;
    const activeOrderData = activeOrder ? JSON.stringify(activeOrder) : null;

    const sessionData = {
        session_id: sessionId,
        production_start_time: global.get("productionStartTime") || null,
        operating_time: global.get("operatingTime") || 0,
        last_update_time: now,
        tracking_enabled: global.get("trackingEnabled") ? 1 : 0,
        cycle_count: global.get("cycleCount") || 0,
        active_work_order_id: activeOrderId,
        active_work_order_data: activeOrderData,
        machine_online: global.get("machineOnline") ? 1 : 0,
        production_started: global.get("productionStarted") ? 1 : 0,
        last_cycle_time: global.get("lastCycleTime") || null,
        last_machine_state: flow.get("lastMachineState") || 0,
        created_at: now,
        updated_at: now,
        is_active: 1
    };

    // Escape single quotes in JSON data
    const escapedOrderData = sessionData.active_work_order_data
        ? sessionData.active_work_order_data.replace(/'/g, "''")
        : null;

    // Create upsert query
    const query = `
        INSERT INTO session_state (
            session_id, production_start_time, operating_time, last_update_time,
            tracking_enabled, cycle_count, active_work_order_id, active_work_order_data,
            machine_online, production_started, last_cycle_time, last_machine_state,
            created_at, updated_at, is_active
        ) VALUES (
            '${sessionData.session_id}',
            ${sessionData.production_start_time},
            ${sessionData.operating_time},
            ${sessionData.last_update_time},
            ${sessionData.tracking_enabled},
            ${sessionData.cycle_count},
            ${sessionData.active_work_order_id ? "'" + sessionData.active_work_order_id + "'" : "NULL"},
            ${escapedOrderData ? "'" + escapedOrderData + "'" : "NULL"},
            ${sessionData.machine_online},
            ${sessionData.production_started},
            ${sessionData.last_cycle_time},
            ${sessionData.last_machine_state},
            ${sessionData.created_at},
            ${sessionData.updated_at},
            ${sessionData.is_active}
        )
        ON DUPLICATE KEY UPDATE
            production_start_time = VALUES(production_start_time),
            operating_time = VALUES(operating_time),
            last_update_time = VALUES(last_update_time),
            tracking_enabled = VALUES(tracking_enabled),
            cycle_count = VALUES(cycle_count),
            active_work_order_id = VALUES(active_work_order_id),
            active_work_order_data = VALUES(active_work_order_data),
            machine_online = VALUES(machine_online),
            production_started = VALUES(production_started),
            last_cycle_time = VALUES(last_cycle_time),
            last_machine_state = VALUES(last_machine_state),
            updated_at = VALUES(updated_at),
            is_active = VALUES(is_active);
    `;

    return {
        _mode: "session-sync",
        topic: query,
        sessionData: sessionData
    };
}