-- Dual-Table Work Order Alignment - Database Setup and Verification
-- Run these queries to verify and set up the database for dual-table functionality

-- ============================================================================
-- 1. CHECK IF use_both_tables COLUMN EXISTS
-- ============================================================================
-- Run this first to see if the column already exists
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'item_management'
  AND COLUMN_NAME = 'use_both_tables';

-- If the above query returns no results, run the migration below


-- ============================================================================
-- 2. ADD use_both_tables COLUMN (if it doesn't exist)
-- ============================================================================
-- Only run this if the column doesn't exist
ALTER TABLE item_management 
ADD COLUMN use_both_tables TINYINT(1) DEFAULT 0 
COMMENT '1 = uses both tables, 0 = single table';


-- ============================================================================
-- 3. VERIFY COLUMN WAS ADDED
-- ============================================================================
DESCRIBE item_management;


-- ============================================================================
-- 4. SET DUAL-TABLE FLAG FOR TESTING
-- ============================================================================
-- Update specific parts to use both tables (replace part IDs as needed)
-- Example: Set part ID 123 to use both tables
-- UPDATE item_management SET use_both_tables = 1 WHERE id = 123;


-- ============================================================================
-- 5. VIEW DUAL-TABLE PARTS
-- ============================================================================
SELECT 
    id,
    part_number,
    part_name,
    use_both_tables
FROM item_management
WHERE use_both_tables = 1;


-- ============================================================================
-- 6. VIEW ALIGNMENT BREAKS
-- ============================================================================
-- Check for alignment breaks that have been inserted
SELECT 
    id,
    machine_id,
    table_id,
    start_date,
    end_date,
    total_hours,
    break_reason,
    priority,
    created_at
FROM cnc_machine_table
WHERE is_break = 1
  AND break_reason = 'Dual-Table Alignment'
ORDER BY created_at DESC;


-- ============================================================================
-- 7. VIEW CURRENT SCHEDULE FOR BOTH TABLES
-- ============================================================================
-- Replace [MACHINE_ID] with actual machine ID
SELECT 
    table_id,
    priority,
    CASE 
        WHEN is_break = 1 THEN CONCAT('[BREAK: ', break_reason, ']')
        ELSE product_name
    END as item_name,
    work_order_id,
    part_id,
    start_date,
    end_date,
    total_hours,
    is_break
FROM cnc_machine_table
WHERE machine_id = [MACHINE_ID]
  AND finished = 0
ORDER BY table_id, priority;


-- ============================================================================
-- 8. VERIFY DUAL-TABLE WORK ORDER ALIGNMENT
-- ============================================================================
-- This query shows if dual-table work orders are properly aligned
-- Replace [MACHINE_ID] with actual machine ID
WITH table_schedules AS (
    SELECT 
        table_id,
        MAX(end_date) as last_end_date
    FROM cnc_machine_table
    WHERE machine_id = [MACHINE_ID]
      AND finished = 0
    GROUP BY table_id
)
SELECT 
    t1.table_id as table_1_id,
    t1.last_end_date as table_1_end,
    t2.table_id as table_2_id,
    t2.last_end_date as table_2_end,
    TIMESTAMPDIFF(MINUTE, t1.last_end_date, t2.last_end_date) as time_diff_minutes,
    CASE 
        WHEN ABS(TIMESTAMPDIFF(MINUTE, t1.last_end_date, t2.last_end_date)) <= 1 
        THEN 'ALIGNED ✓'
        ELSE 'MISALIGNED ✗'
    END as alignment_status
FROM table_schedules t1
CROSS JOIN table_schedules t2
WHERE t1.table_id = 1 AND t2.table_id = 2;


-- ============================================================================
-- 9. CLEANUP TEST DATA (if needed)
-- ============================================================================
-- Remove all alignment breaks (use with caution!)
-- DELETE FROM cnc_machine_table 
-- WHERE is_break = 1 
--   AND break_reason = 'Dual-Table Alignment';

-- Reset use_both_tables for all parts
-- UPDATE item_management SET use_both_tables = 0;
