<?php
include("../../../include/startup.php");
header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors',1);
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_work_orders') {

    $response = [
        'ready_orders'   => [],
        'wip_vac'        => [],
        'pending_orders' => [],
        'machines'       => []
    ];

    // ------------------------------------
    // MACHINES
    // ------------------------------------
    $machineQuery = "
        SELECT id, resource_code
        FROM station_detail_table
        WHERE machine_type = 3
        ORDER BY id
    ";
    $result = mysqli_query($conn, $machineQuery);
    while ($row = mysqli_fetch_assoc($result)) {
        $response['machines'][] = [
            'id'   => $row['id'],
            'name' => stripslashes($row['resource_code'])
        ];
    }

    $now = date('Y-m-d H:i:s');

    // READY ORDERS (VAC STARTED IN PAST)
    /*$query_ready = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            p.id AS vac_id,
            p.start_date,
            w.work_order_name,
            im.part_number,
            im.product_description
        FROM manufacture_machine_table AS m
        JOIN manufacture_machine_children AS mc
            ON mc.work_order_id = m.work_order_id
           AND mc.part_id = m.part_id
        JOIN vac_machine_table AS p
            ON p.work_order_id = mc.work_order_id
           AND p.part_id = mc.child_part_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        WHERE s.machine_type = 3
          AND m.in_scheduler_check = 0
          AND p.start_date < '$now'
    ";*/
	$query_ready = "
    SELECT 
        m.work_order_id,
        m.part_id,
        m.end_date,
        m.machine_id,
        m.quantity,
        m.total_hours,
        m.in_scheduler_check,
        m.id,
        m.parent_ids,
        p.id AS vac_id,
        p.start_date,
        w.work_order_name,
        im.part_number,
        im.product_description
    FROM manufacture_machine_table AS m
    LEFT JOIN manufacture_machine_children AS mc
        ON mc.work_order_id = m.work_order_id
       AND mc.part_id = m.part_id
    LEFT JOIN vac_machine_table AS p
        ON p.work_order_id = mc.work_order_id
       AND p.part_id = mc.child_part_id
    LEFT JOIN work_order_name AS w
        ON w.work_order_id = m.work_order_id
       AND w.part_id = m.part_id
    LEFT JOIN item_management AS im
        ON im.id = m.part_id
    JOIN station_detail_table AS s
        ON s.id = m.machine_id
    WHERE s.machine_type = 3
      AND m.in_scheduler_check = 0
      AND (
            mc.work_order_id IS NULL           
            OR p.start_date < '$now'         
          )
";

    $res = mysqli_query($conn, $query_ready);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['ready_orders'][] = $row;
    }

    // WIP VAC (CURRENTLY RUNNING)
    $query_wip_vac = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            p.id AS vac_id,
            p.start_date,
            p.end_date,
            w.work_order_name,
            im.part_number,
            rd.route_desc
        FROM manufacture_machine_table AS m
        JOIN manufacture_machine_children AS mc
            ON mc.work_order_id = m.work_order_id
           AND mc.part_id = m.part_id
        JOIN vac_machine_table AS p
            ON p.work_order_id = mc.work_order_id
           AND p.part_id = mc.child_part_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        LEFT JOIN route_details AS rd
            ON rd.item_id = m.part_id
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        WHERE s.machine_type = 3
          AND m.in_scheduler_check = 0
          AND p.start_date <= '$now'
          AND p.end_date   >= '$now'
    ";

    $res = mysqli_query($conn, $query_wip_vac);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['wip_vac'][] = $row;
    }

    // PENDING ORDERS (VAC NOT STARTED)
    $query_pending = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            p.id AS vac_id,
            p.start_date,
            w.work_order_name,
            im.part_number,
            rd.route_desc
        FROM manufacture_machine_table AS m
        JOIN manufacture_machine_children AS mc
            ON mc.work_order_id = m.work_order_id
           AND mc.part_id = m.part_id
        JOIN vac_machine_table AS p
            ON p.work_order_id = mc.work_order_id
           AND p.part_id = mc.child_part_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        LEFT JOIN route_details AS rd
            ON rd.item_id = m.part_id
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        WHERE s.machine_type = 3
          AND m.in_scheduler_check = 0
          AND p.start_date > '$now'
    ";

    $res = mysqli_query($conn, $query_pending);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['pending_orders'][] = $row;
    }

    echo json_encode($response);
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_assembly_work_orders') {

    $response = [
        'ready_orders'      => [],
        'not_ready_orders'  => [],
        'machines'          => []
    ];

    // -------------------------------------------------
    // ASSEMBLY MACHINES (machine_type = 2)
    // -------------------------------------------------
    $machineQuery = "
        SELECT id, resource_code
        FROM station_detail_table
        WHERE machine_type = 2
        ORDER BY id
    ";
    $result = mysqli_query($conn, $machineQuery);
    while ($row = mysqli_fetch_assoc($result)) {
        $response['machines'][] = [
            'id'   => $row['id'],
            'name' => stripslashes($row['resource_code'])
        ];
    }

    $now = date('Y-m-d H:i:s');

    // =================================================
    // ASSEMBLY READY ORDERS
    // (Child CNC or VAC still running)
    // =================================================
    $query_ready = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            w.work_order_name,
            im.part_number,
            im.product_description
        FROM manufacture_machine_table AS m
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        WHERE s.machine_type = 2
          AND m.in_scheduler_check = 0
          AND EXISTS (
                SELECT 1
                FROM manufacture_machine_children c
                WHERE c.work_order_id = m.work_order_id
                  AND c.part_id = m.part_id
                  AND (
                        EXISTS (
                            SELECT 1
                            FROM cnc_machine_table cnc
                            WHERE cnc.part_id = c.child_part_id
                              AND cnc.work_order_id = c.work_order_id
                              AND cnc.end_date > '$now'
                        )
                        OR EXISTS (
                            SELECT 1
                            FROM vac_machine_table vac
                            WHERE vac.part_id = c.child_part_id
                              AND vac.work_order_id = c.work_order_id
                              AND vac.end_date > '$now'
                        )
                  )
          )
    ";

    $res = mysqli_query($conn, $query_ready);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['ready_orders'][] = $row;
    }

    // =================================================
    // ASSEMBLY NOT READY ORDERS
    // (No child CNC / VAC running)
    // =================================================
    $query_not_ready = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            w.work_order_name,
            im.part_number,
            im.product_description
        FROM manufacture_machine_table AS m
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        WHERE s.machine_type = 2
          AND m.in_scheduler_check = 0
          AND NOT EXISTS (
                SELECT 1
                FROM manufacture_machine_children c
                WHERE c.work_order_id = m.work_order_id
                  AND c.part_id = m.part_id
                  AND (
                        EXISTS (
                            SELECT 1
                            FROM cnc_machine_table cnc
                            WHERE cnc.part_id = c.child_part_id
                              AND cnc.work_order_id = c.work_order_id
                              AND cnc.end_date > '$now'
                        )
                        OR EXISTS (
                            SELECT 1
                            FROM vac_machine_table vac
                            WHERE vac.part_id = c.child_part_id
                              AND vac.work_order_id = c.work_order_id
                              AND vac.end_date > '$now'
                        )
                  )
          )
    ";

    $res = mysqli_query($conn, $query_not_ready);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['not_ready_orders'][] = $row;
    }

    echo json_encode($response);
    exit;
}
else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_assypu_work_orders') {

    $response = [
        'ready_orders'      => [],
        'not_ready_orders'  => [],
        'machines'          => []
    ];


    $machineQuery = "
        SELECT id, resource_code
        FROM station_detail_table
        WHERE machine_type = 5
        ORDER BY id
    ";
    $result = mysqli_query($conn, $machineQuery);
    while ($row = mysqli_fetch_assoc($result)) {
        $response['machines'][] = [
            'id'   => $row['id'],
            'name' => stripslashes($row['resource_code'])
        ];
    }

    $now = date('Y-m-d H:i:s');


    $query_ready = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            w.work_order_name,
            im.part_number,
            im.product_description
        FROM manufacture_machine_table AS m
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        WHERE s.machine_type = 5
          AND m.in_scheduler_check = 0
          AND EXISTS (
                SELECT 1
                FROM manufacture_machine_children c
                WHERE c.work_order_id = m.work_order_id
                  AND c.part_id = m.part_id
                  AND (
                        EXISTS (
                            SELECT 1
                            FROM pu_machine_table pu
                            WHERE pu.part_id = c.child_part_id
                              AND pu.work_order_id = c.work_order_id
                              AND pu.end_date > '$now'
                        )
                        
                  )
          )
    ";

    $res = mysqli_query($conn, $query_ready);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['ready_orders'][] = $row;
    }

    $query_not_ready = "
        SELECT 
            m.work_order_id,
            m.part_id,
            m.end_date,
            m.machine_id,
            m.quantity,
            m.total_hours,
            m.in_scheduler_check,
            m.id,
            m.parent_ids,
            w.work_order_name,
            im.part_number,
            im.product_description
        FROM manufacture_machine_table AS m
        JOIN station_detail_table AS s
            ON s.id = m.machine_id
        LEFT JOIN work_order_name AS w
            ON w.work_order_id = m.work_order_id
           AND w.part_id = m.part_id
        LEFT JOIN item_management AS im
            ON im.id = m.part_id
        WHERE s.machine_type = 5
          AND m.in_scheduler_check = 0
          AND NOT EXISTS (
                SELECT 1
                FROM manufacture_machine_children c
                WHERE c.work_order_id = m.work_order_id
                  AND c.part_id = m.part_id
                  AND (
                        EXISTS (
                            SELECT 1
                            FROM pu_machine_table pu
                            WHERE pu.part_id = c.child_part_id
                              AND pu.work_order_id = c.work_order_id
                              AND pu.end_date > '$now'
                        )
                        
                  )
          )
    ";

    $res = mysqli_query($conn, $query_not_ready);
    while ($row = mysqli_fetch_assoc($res)) {
        $response['not_ready_orders'][] = $row;
    }

    echo json_encode($response);
    exit;
}
?>
