Phase 1: Database Schema Verification & Updates

 Goal: Ensure work_orders table has all required columns

 Step 1.1: Verify Database Schema

 - Read current work_orders table structure from migration files
 - Check if good_parts column exists (confirmed needed but may be missing)
 - Verify cycle_count column exists (added in migration.sql)
 - Check if progress_percent column exists

 Step 1.2: Create Migration SQL (if needed)

 - Add missing columns: good_parts INT DEFAULT 0
 - Ensure indexes exist on work_order_id and status
 - Create backup of work_orders table before schema changes

 Files Modified:
 - New file: migration_work_order_persistence.sql

 Dependencies: None
 Risk Level: LOW (schema changes with defaults won't break existing data)

 ---
 Phase 2: Add Cycle Persistence to work_orders Table

 Goal: Write good_parts and cycle_count to database every 5 seconds (throttled)

 Step 2.1: Add Fourth Output to Machine Cycles Function

 - Modify modified_machine_cycles.js
 - Add calculation for good_parts = (cycles × cavities) - scrap
 - Create throttled DB update message (same 5s logic as session_state)
 - Output on port 4: SQL UPDATE for work_orders table

 Code Location: /home/mdares/.node-red/projects/Plastico/modified_machine_cycles.js lines 98-100

 SQL Pattern:
 UPDATE work_orders
 SET cycle_count = ?,
     good_parts = ?,
     progress_percent = ?,
     updated_at = NOW()
 WHERE work_order_id = ?

 Step 2.2: Wire Fourth Output to Database Node

 - In flows.json, find "Machine cycles" function node
 - Add wire from output[3] (4th port) to mariaDB node
 - Ensure proper error handling

 Files Modified:
 - modified_machine_cycles.js
 - flows.json (node wiring)

 Dependencies: Phase 1 complete
 Risk Level: LOW (only adds writes, doesn't change existing logic)

My feedback: Machine cycles already has a 4th output that is connected to DB Guard, this is used for persistence of cycles and so that graphs doesn't autorefresh with each cycles. Please verify how this would affect this

 ---
 Phase 3: Implement Resume/Restart Prompt on Load

 Goal: Prevent accidental progress loss when clicking Load

 Step 3.1: Add Progress Check Before Load

 - Modify modified_work_order_buttons.js case "start-work-order"
 - Query work_orders table for existing cycle_count and good_parts
 - If progress exists (cycle_count > 0 OR good_parts > 0), send prompt to UI
 - Store work order data in temporary variable pending user choice

 Code Location: modified_work_order_buttons.js lines 13-47

 Step 3.2: Add Resume/Restart Action Handlers

 - Create new action: resume-work-order
   - Keep existing cycle_count and good_parts
   - Create new session_id
   - Set status to RUNNING
   - Load current values into global state
 - Create new action: restart-work-order
   - Reset cycle_count and good_parts to 0 in database
   - Create new session_id
   - Set status to RUNNING
   - Initialize global state to 0

 Step 3.3: Add UI Prompt Component

 - In Home template or Work Orders template, add hidden dialog
 - Show when msg.topic === "resumePrompt"
 - Display current progress: "WO-123 has 150/500 parts. Resume or Restart?"
 - Two buttons: "Resume" (green) and "Restart" (orange with confirmation)

 Files Modified:
 - modified_work_order_buttons.js
 - flows.json (Home or Work Orders template for dialog)

 Dependencies: Phase 2 complete (ensures good_parts exists)
 Risk Level: MEDIUM (changes core Load button behavior, needs thorough testing)

 ---
 Phase 4: Fix Complete Button to Persist Final Counts

 Goal: Ensure final production numbers are written to work_orders before marking DONE

 Step 4.1: Modify Complete Work Order Logic

 - Before setting status to DONE, write current session state to work_orders
 - Update SQL to:
 UPDATE work_orders
 SET status = 'DONE',
     cycle_count = ?,
     good_parts = ?,
     progress_percent = 100,
     updated_at = NOW()
 WHERE work_order_id = ?

 Code Location: modified_work_order_buttons.js lines 49-82

 Files Modified:
 - modified_work_order_buttons.js

 Dependencies: Phase 2 complete
 Risk Level: LOW (just ensures final sync before completion)

 ---
 Phase 5: Update Session Restore to Set RUNNING Status

 Goal: When restoring session, automatically set work order back to RUNNING

 Step 5.1: Modify Restore Session Logic

 - In restore_session.js, after loading global state
 - Add database update to set status back to RUNNING
 - Only if work order exists and was previously RUNNING

 SQL:
 UPDATE work_orders
 SET status = 'RUNNING',
     updated_at = NOW()
 WHERE work_order_id = ? AND status != 'DONE'

 Code Location: restore_session.js (after global.set calls)

 Files Modified:
 - restore_session.js

 Dependencies: None (independent improvement)
 Risk Level: LOW (restores expected state)

My Feedback: Make sure user still has to click start for it to start counting, should be running so doesn't have to load again, but user must confirm with start button

 ---
 Phase 6: Load Work Order Data from Database (Not Session)

 Goal: Make work_orders table the source of truth for UI display

 Step 6.1: Modify Work Order Load/Start to Query Database First

 - When loading work order, query database for current cycle_count and good_parts
 - Use these values to initialize global state (not assume zeros)
 - For resume: global.set("cycleCount", dbRow.cycle_count)
 - For restart: global.set("cycleCount", 0) + UPDATE database

 Step 6.2: Update Home Template to Show DB Values

 - When displaying active work order, prioritize msg.payload.good from database
 - Ensure work_orders refresh query includes cycle_count and good_parts
 - Update "Back to UI" function to send accurate database values

 Code Locations:
 - modified_work_order_buttons.js (load logic)
 - flows.json (Home template $watch for workOrderCycle updates)

 Files Modified:
 - modified_work_order_buttons.js
 - Potentially flows.json (Home template if needed)

 Dependencies: Phase 2-4 complete
 Risk Level: MEDIUM (changes data flow, requires testing of UI updates)

My feedback: Be extra careful with this step, you are modifying existing logic. Make sure to check for potential side-effects, what can go wrong, what your modifications will do to existing nodes/wiring/logic

 ---
 Phase 7: Add Tab Switch State Refresh (Optional Enhancement)

 Goal: Ensure UI shows latest data when returning to Home tab

 Step 7.1: Add Tab Change Listener

 - In Home template, listen for tab activation event
 - On activation, query work_orders for active RUNNING order
 - Update UI with fresh data from database

 Code Location: flows.json Home template script section

 Files Modified:
 - flows.json (Home template)

 Dependencies: Phase 6 complete
 Risk Level: LOW (purely UI enhancement)

 ---
 Phase 8: Testing & Validation

 Goal: Verify all scenarios work correctly

 Test Cases:

 1. New Work Order Start:
   - Load WO with 0 progress → should start normally (no prompt)
 2. Resume Existing Work Order:
   - Load WO with 50/200 parts → prompt shows
   - Click Resume → continues from 50
   - Machine cycles → good_parts increments correctly
 3. Restart Existing Work Order:
   - Load WO with 100/500 parts → prompt shows
   - Click Restart → confirmation dialog
   - After restart → cycle_count and good_parts reset to 0 in DB
 4. Tab Switch:
   - Start production, switch to Graphs, switch back to Home
   - Progress should still show correctly
 5. Node-RED Restart (with restore):
   - Production running at 75/300 parts
   - Restart Node-RED
   - Click "Restore Session"
   - Work order status → RUNNING
   - Progress shows 75 parts
   - Click Start → production continues
 6. Complete Work Order:
   - Finish work order at 250/250 parts
   - Click Done
   - Database shows cycle_count and good_parts persisted
   - Status = DONE
 7. Power Failure Simulation:
   - Production at 150 parts
   - Kill Node-RED process
   - Restart
   - Maximum 5 seconds of data loss (1-2 cycles depending on cycle time)

 ---
 Implementation Order Summary

 1. Phase 1 → Database schema (foundation)
 2. Phase 2 → Cycle persistence (core fix)
 3. Phase 4 → Complete button fix (complements Phase 2)
 4. Phase 5 → Session restore status (independent)
 5. Phase 3 → Resume/Restart prompt (depends on Phase 2 data)
 6. Phase 6 → Database as source of truth (depends on all above)
 7. Phase 7 → Tab switch refresh (optional polish)
 8. Phase 8 → Full testing

 ---
 Rollback Plan

 If issues arise:
 - Phase 1-2: Safe to rollback (schema defaults to 0, won't break)
 - Phase 3-4: Can disable prompt by commenting out check, falls back to old behavior
 - Phase 5-6: Revert files from git
 - Database: Keep backups before each phase

 ---
 Key Files to Modify

 | File                                 | Phases  | Risk   |
 |--------------------------------------|---------|--------|
 | migration_work_order_persistence.sql | 1       | LOW    |
 | modified_machine_cycles.js           | 2       | LOW    |
 | modified_work_order_buttons.js       | 2,3,4,6 | MEDIUM |
 | restore_session.js                   | 5       | LOW    |
 | flows.json                           | 2,3,7   | MEDIUM |

 ---
 Success Criteria

 ✅ Work orders persist progress across Node-RED restarts
 ✅ Resume/Restart prompt prevents accidental data loss
 ✅ work_orders table always reflects current production state
 ✅ Tab switches don't lose data
 ✅ Multi-day work orders can be interrupted and resumed
 ✅ Maximum 5 seconds of data loss on crash (acceptable for throttled writes)



 Ready to proceed to Phase 2 when you give the go-ahead! Phase 2 will modify the machine cycles function to persist good_parts and cycle_count to the
  work_orders table (using the existing 4th output that goes to DB Guard).

> Go with phase 2 
  ⎿  ⧉ Selected 97 lines from projects/Plastico/migration_work_order_persistence.sql in Visual Studio Code

● Perfect! Let me start Phase 2. First, I need to understand the current 4th output setup and how DB Guard works.

● Search(pattern: ""name":\s*"DB Guard"", path: "projects/Plastico", glob: "flows.json", output_mode: "content")
  ⎿  Found 0 lines 

● Search(pattern: "DB Guard|DBGuard|db-guard|db_guard", path: "projects/Plastico", glob: "flows.json", output_mode: "content")
  ⎿  Found 6 lines (ctrl+o to expand)

● Great! Now let me find the Machine Cycles function node and see how it's wired:

● Search(pattern: ""name":\s*"Machine [Cc]ycles"", path: "projects/Plastico", glob: "flows.json", output_mode: "content")
  ⎿  Found 41 lines (ctrl+o to expand)

● Perfect! I can see the Machine Cycles function has 4 outputs:
  - Output 1 (port 0): Scrap prompt
  - Output 2 (port 1): Production state
  - Output 3 (port 2): DB sync for session_state
  - Output 4 (port 3): Goes to DB Guard (Cycles) - this is what we need to modify!

  Now let me check what DB Guard (Cycles) does and what the 4th output currently sends:

● Read(projects/Plastico/flows.json)
  ⎿  Read 50 lines

● Good! I can see output 4 currently just goes to a debug node. Now let me look at what the current modified_machine_cycles.js sends on output 4 (the
  session-sync message):

  Looking at modified_machine_cycles.js lines 98-100, it currently sends the session-sync message. Now I need to:

  1. Add a 5th output for work_order persistence (to keep session-sync separate)
  2. OR Modify the existing 4th output to send work_order updates instead of/in addition to session updates

  Based on your feedback that "Machine cycles already has a 4th output connected to DB Guard for persistence of cycles", I'll create a 5th output for
  work_order updates to keep the existing session persistence logic intact.

  Let me modify the machine cycles function:

● Update(projects/Plastico/modified_machine_cycles.js)

● Update(projects/Plastico/modified_machine_cycles.js)

● Update(projects/Plastico/modified_machine_cycles.js)