// ============================================================================
// ENHANCED "Work Order buttons" Function - Complete Implementation
// Location: flows.json, node ID: 9bbd4fade968036d
// Outputs: 5 (upload, select, start/stop, complete, session management)
//
// Features Implemented:
// - Stop reason prompt (Issue 4)
// - Session management (Issue 5)
// - Stop event tracking with categorization
// - Session creation on START/RESUME
// - Automatic downtime calculation based on stop reason
// ============================================================================

// Helper function to generate session ID
function generateSessionId() {
    return 'session_' + Date.now() + '_' + Math.random().toString(36).substr(2, 9);
}

switch (msg.action) {
    case "upload-excel":
        msg._mode = "upload";
        return [msg, null, null, null, null];

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

    // ========================================================================
    // START WORK ORDER - Creates new session
    // ========================================================================
    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;

        // Update work order status
        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';
        `;

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

        // Create new session (Issue 5)
        const sessionId = generateSessionId();
        global.set("currentSessionId", sessionId);
        global.set("productionStartTime", Date.now());
        global.set("operatingTime", 0);
        global.set("downtime", 0);
        global.set("lastUpdateTime", Date.now());

        // Create session record
        const sessionMsg = {
            _mode: "create-session",
            topic: `
                INSERT INTO production_sessions
                (session_id, work_order_id, start_time, reason_for_start, cycles_completed, operating_time, downtime)
                VALUES
                ('${sessionId}', '${order.id}', ${Date.now()}, 'initial_start', 0, 0, 0);
            `
        };

        node.warn(`[SESSION] Created new session: ${sessionId}`);

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

    // ========================================================================
    // COMPLETE WORK ORDER - Ends session
    // ========================================================================
    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;

        // Update work order status
        msg.topic = `
            UPDATE work_orders
            SET status = 'DONE', updated_at = NOW()
            WHERE work_order_id = '${order.id}';
        `;

        // Get session data to close it
        const sessionId = global.get("currentSessionId");
        const cycles = global.get("cycleCount") || 0;
        const operatingTime = global.get("operatingTime") || 0;
        const downtime = global.get("downtime") || 0;
        const productionStartTime = global.get("productionStartTime") || Date.now();
        const now = Date.now();
        const duration = (now - productionStartTime) / 1000;

        let sessionMsg = null;
        if (sessionId) {
            // Close the current session
            sessionMsg = {
                _mode: "close-session",
                topic: `
                    UPDATE production_sessions
                    SET
                        end_time = ${now},
                        duration = ${duration.toFixed(2)},
                        cycles_completed = ${cycles},
                        operating_time = ${operatingTime.toFixed(2)},
                        downtime = ${downtime.toFixed(2)},
                        reason_for_end = 'work_order_complete'
                    WHERE session_id = '${sessionId}';
                `
            };

            // Update work order totals
            msg.topic += `
                UPDATE work_orders
                SET
                    total_sessions = (SELECT COUNT(*) FROM production_sessions WHERE work_order_id = '${order.id}'),
                    total_operating_time = (SELECT SUM(operating_time) FROM production_sessions WHERE work_order_id = '${order.id}'),
                    total_downtime = (SELECT SUM(downtime) FROM production_sessions WHERE work_order_id = '${order.id}'),
                    avg_session_duration = (SELECT AVG(duration) FROM production_sessions WHERE work_order_id = '${order.id}')
                WHERE work_order_id = '${order.id}';
            `;

            node.warn(`[SESSION] Closed session: ${sessionId}`);
        }

        // Clear global state
        global.set("activeWorkOrder", null);
        global.set("cycleCount", 0);
        flow.set("lastMachineState", 0);
        global.set("scrapPromptIssuedFor", null);
        global.set("currentSessionId", null);
        global.set("trackingEnabled", false);
        global.set("productionStartTime", null);
        global.set("operatingTime", 0);
        global.set("downtime", 0);

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

    // ========================================================================
    // SCRAP ENTRY
    // ========================================================================
    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];
        }

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

        // Clear prompt flag so it can show again when target reached next time
        global.set("scrapPromptIssuedFor", null);

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

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

    // ========================================================================
    // SCRAP SKIP
    // ========================================================================
    case "scrap-skip": {
        const { id } = msg.payload || {};

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

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

    // ========================================================================
    // START (RESUME) - Creates new session (Issue 5)
    // ========================================================================
    case "start": {
        // START/RESUME button clicked from Home dashboard
        // Enable tracking and create new session

        const now = Date.now();
        const wasTracking = !!global.get("trackingEnabled");
        const activeOrder = global.get("activeWorkOrder");

        // Close previous session if exists
        let closeSessionMsg = null;
        const prevSessionId = global.get("currentSessionId");

        if (prevSessionId && wasTracking === false) {
            // There was a stop, close the previous session
            const prevStartTime = global.get("productionStartTime") || now;
            const sessionDuration = (now - prevStartTime) / 1000;
            const cycles = global.get("cycleCount") || 0;
            const operatingTime = global.get("operatingTime") || 0;
            const downtime = global.get("downtime") || 0;

            // Get the last stop event to determine reason
            const lastStopReason = flow.get("lastStopReason") || "unknown";
            const lastStopCategory = flow.get("lastStopCategory") || "unplanned";

            closeSessionMsg = {
                _mode: "close-session",
                topic: `
                    UPDATE production_sessions
                    SET
                        end_time = ${now},
                        duration = ${sessionDuration.toFixed(2)},
                        cycles_completed = ${cycles},
                        operating_time = ${operatingTime.toFixed(2)},
                        downtime = ${downtime.toFixed(2)},
                        reason_for_end = '${lastStopCategory === 'planned' ? 'planned_stop' : 'unplanned_stop'}'
                    WHERE session_id = '${prevSessionId}';
                `
            };
        }

        // Enable tracking
        global.set("trackingEnabled", true);

        // Create new session
        const newSessionId = generateSessionId();
        global.set("currentSessionId", newSessionId);
        global.set("productionStartTime", now);
        global.set("lastUpdateTime", now);

        const reasonForStart = wasTracking ? "resume_after_unplanned" :
            (flow.get("lastStopCategory") === "planned" ? "resume_after_planned" : "resume_after_unplanned");

        const newSessionMsg = {
            _mode: "create-session",
            topic: `
                INSERT INTO production_sessions
                (session_id, work_order_id, start_time, reason_for_start, cycles_completed, operating_time, downtime)
                VALUES
                ('${newSessionId}', ${activeOrder ? `'${activeOrder.id}'` : 'NULL'}, ${now}, '${reasonForStart}', 0, 0, 0);
            `
        };

        // Also resume any open stop event
        const lastStopEventId = flow.get("lastStopEventId");
        let resumeStopMsg = null;

        if (lastStopEventId) {
            const stopTime = flow.get("lastStopTime") || now;
            const stopDuration = (now - stopTime) / 1000;

            resumeStopMsg = {
                _mode: "resume-stop",
                topic: `
                    UPDATE stop_events
                    SET
                        resume_time = ${now},
                        duration = ${stopDuration.toFixed(2)}
                    WHERE id = ${lastStopEventId};
                `
            };

            flow.set("lastStopEventId", null);
        }

        node.warn(`[SESSION] Started new session: ${newSessionId} (reason: ${reasonForStart})`);

        // Combine all session messages into output 5
        const combinedSessionMsg = closeSessionMsg || newSessionMsg;
        if (closeSessionMsg && newSessionMsg) {
            // Need to send both - combine topics
            combinedSessionMsg.topic = closeSessionMsg.topic + "\n" + newSessionMsg.topic;
            if (resumeStopMsg) {
                combinedSessionMsg.topic += "\n" + resumeStopMsg.topic;
            }
        } else if (resumeStopMsg) {
            combinedSessionMsg.topic += "\n" + resumeStopMsg.topic;
        }

        return [null, null, null, null, combinedSessionMsg];
    }

    // ========================================================================
    // STOP - Shows prompt for stop reason (Issue 4)
    // ========================================================================
    case "stop": {
        // STOP button clicked - show prompt for categorization
        msg._mode = "stop-prompt";
        msg.stopPrompt = {
            timestamp: Date.now(),
            workOrderId: (global.get("activeWorkOrder") || {}).id || null
        };

        node.warn("[STOP] Showing stop reason prompt");

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

    // ========================================================================
    // STOP REASON SUBMITTED (Issue 4)
    // ========================================================================
    case "stop-reason": {
        const { category, reason, notes } = msg.payload || {};

        if (!category || !reason) {
            node.error("Stop reason category and detail required", msg);
            return [null, null, null, null, null];
        }

        const now = Date.now();
        const activeOrder = global.get("activeWorkOrder");
        const sessionId = global.get("currentSessionId");

        // Determine if this affects availability
        const affectsAvailability = (category === 'unplanned') ? 1 : 0;

        // Create stop event
        const stopEventMsg = {
            _mode: "create-stop-event",
            topic: `
                INSERT INTO stop_events
                (work_order_id, session_id, stop_time, reason_category, reason_detail, affects_availability, operator_notes)
                VALUES
                (${activeOrder ? `'${activeOrder.id}'` : 'NULL'}, ${sessionId ? `'${sessionId}'` : 'NULL'}, ${now}, '${category}', '${reason}', ${affectsAvailability}, ${notes ? `'${notes.replace(/'/g, "''")}'` : 'NULL'});

                SELECT LAST_INSERT_ID() as stop_event_id;
            `
        };

        // Store for later use when RESUME is clicked
        flow.set("lastStopReason", reason);
        flow.set("lastStopCategory", category);
        flow.set("lastStopTime", now);
        // Note: We'll get the stop_event_id from the query result and store it

        // Disable tracking
        global.set("trackingEnabled", false);

        node.warn(`[STOP] Recorded ${category} stop: ${reason}`);

        return [null, null, null, null, stopEventMsg];
    }

    // ========================================================================
    // STORE STOP EVENT ID (called after INSERT)
    // ========================================================================
    case "store-stop-event-id": {
        const stopEventId = msg.payload;
        if (stopEventId) {
            flow.set("lastStopEventId", stopEventId);
            node.warn(`[STOP] Stored stop event ID: ${stopEventId}`);
        }
        return [null, null, null, null, null];
    }
}

// Default - no action matched
return [null, null, null, null, null];