<?php
ob_start();
include("../../../include/startup.php");
ob_clean();

header('Content-Type: application/json');
error_reporting(0);
$todays = date('Y-m-d');

function recalculateMachineDatesforpu($conn, $machineId) {
    set_time_limit(300);

    $shiftsByDay = [];
    $shiftResult = mysqli_query($conn, "SELECT * FROM working_hours");
    while ($row = mysqli_fetch_assoc($shiftResult)) {
        $day = (int)$row['day_shifts'];
        $shiftsByDay[$day][] = $row;
    }

    $currentDateStr = date('Y-m-d');
    $tasks = [];

    $taskResult = mysqli_query($conn,
        "SELECT id, total_hours AS hours_to_schedule
         FROM pu_machine_table
         WHERE machine_id = $machineId
           AND end_date >= '$currentDateStr'
         ORDER BY priority"
    );

    while ($row = mysqli_fetch_assoc($taskResult)) {
        $tasks[] = $row;
    }

    $currentDateTime = strtotime(date('Y-m-d') . ' 00:00:00');

    foreach ($tasks as $task) {
        $hoursRemaining = floatval($task['hours_to_schedule']);
        $dateHoursMap   = [];
        $taskStartTime  = null;
        $taskEndTime    = null;
        $firstShiftUsed = null;

        while ($hoursRemaining > 0) {
            $currentDate = date('Y-m-d', $currentDateTime);
            $dayOfWeek   = date('w', $currentDateTime);

            if (!isset($shiftsByDay[$dayOfWeek])) {
                $currentDateTime = strtotime($currentDate . ' +1 day');
                continue;
            }

            foreach ($shiftsByDay[$dayOfWeek] as $shift) {
                $shiftStart = strtotime($currentDate . ' ' . $shift['start_time']);
                $shiftEnd   = strtotime($currentDate . ' ' . $shift['end_time']);
                $shiftName  = $shift['shift_name'];

                if ($shiftEnd <= $currentDateTime) continue;

                $actualStart      = max($currentDateTime, $shiftStart);
                $availableSeconds = max(0, $shiftEnd - $actualStart);
                $availableHours   = $availableSeconds / 3600;

                if ($availableHours <= 0) continue;

                $allocatable = min($hoursRemaining, $availableHours);

                if (!$taskStartTime) {
                    $taskStartTime  = $actualStart;
                    $firstShiftUsed = $shiftName;
                }

                $taskEndTime     = $actualStart + ($allocatable * 3600);
                $hoursRemaining -= $allocatable;

                $dateOnly = date('Y-m-d', $actualStart);
                if (!isset($dateHoursMap[$dateOnly])) {
                    $dateHoursMap[$dateOnly] = 0;
                }
                $dateHoursMap[$dateOnly] += $allocatable;

                $currentDateTime = $taskEndTime;

                if ($hoursRemaining <= 0) break;
            }

            if ($hoursRemaining > 0) {
                $currentDateTime = strtotime(date('Y-m-d', $currentDateTime) . ' +1 day 00:00:00');
            }
        }

        $remainingHoursStr = '';
        foreach ($dateHoursMap as $date => $hrs) {
            $remainingHoursStr .= $date . '=' . round($hrs, 2) . ',';
        }
        $remainingHoursStr = rtrim($remainingHoursStr, ',');

        $startStr = date('Y-m-d H:i:s', $taskStartTime);
        $endStr   = date('Y-m-d H:i:s', $taskEndTime);

        $query = "UPDATE pu_machine_table SET
            start_date    = '$startStr',
            end_date      = '$endStr',
            shift_ids     = '$firstShiftUsed',
            remaing_hours = '$remainingHoursStr'
            WHERE id = {$task['id']}";

        mysqli_query($conn, $query);
    }
}

// =============================================
// getAllBoughtInMaterials — recursive BOM walk
// =============================================
function getAllBoughtInMaterials($conn, $part_id, &$visited = []) {
    // Prevent infinite loops
    if (in_array($part_id, $visited)) {
        return [];
    }
    $visited[] = $part_id;
    $materials = [];
    
    $query = "
        SELECT 
            b.sub_part_id,
            b.sub_part_name,
            b.type,
            im.product_group
        FROM bom b
        LEFT JOIN item_management im 
            ON im.id = b.sub_part_id
        WHERE b.main_product_id = '$part_id'
    ";
    
    $result = mysqli_query($conn, $query);
    if (!$result) {
        return [];
    }
    
    while ($row = mysqli_fetch_assoc($result)) {
        // Only collect Bought In materials with product_group = 17
        if (
            $row['type'] === 'Bought In' &&
            $row['product_group'] == 17 &&
            !empty($row['sub_part_name'])
        ) {
            $materials[] = $row['sub_part_name'];
        }
        
        // Always recurse Manufactured parts
        if (
            $row['type'] === 'Manufactured' &&
            !empty($row['sub_part_id'])
        ) {
            $sub_materials = getAllBoughtInMaterials(
                $conn,
                $row['sub_part_id'],
                $visited
            );
            $materials = array_merge($materials, $sub_materials);
        }
    }
    
    return array_unique($materials); // Remove duplicates
}

// =============================================
// GET: Virtual table list for a physical machine
// =============================================
if (isset($_GET['action']) && $_GET['action'] === 'get_pu_tables') {
    $physicalMachineId = intval($_GET['machine_id'] ?? 0);

    if (!$physicalMachineId) {
        ob_clean();
        echo json_encode(['success' => false, 'tables' => []]);
        ob_end_flush();
        exit;
    }

    $res = $conn->query("
        SELECT no_of_table
        FROM pu_mapping_tables
        WHERE machine_id = $physicalMachineId
        LIMIT 1
    ");

    $row = $res ? $res->fetch_assoc() : null;

    if (!$row || !$row['no_of_table']) {
        ob_clean();
        echo json_encode(['success' => false, 'tables' => []]);
        ob_end_flush();
        exit;
    }

    $numTables = (int)$row['no_of_table'];
    $tables    = [];

    for ($t = 1; $t <= $numTables; $t++) {
        $tables[] = [
            'virtual_id' => ($physicalMachineId * 10) + $t,
            'table_num'  => $t,
        ];
    }

    ob_clean();
    echo json_encode(['success' => true, 'tables' => $tables]);
    ob_end_flush();
    exit;
}

// =============================================
// POST: Handle drag & drop priority update
// =============================================
if (isset($_REQUEST['action']) && $_REQUEST['action'] === 'update_pu_task_priority') {
    $taskId       = intval($_REQUEST['task_id']);
    $newMachineId = intval($_REQUEST['new_machine_id']);
    $partId       = intval($_REQUEST['part_id']);
    $workOrderId  = intval($_REQUEST['work_order_id']);
    $leftNeighborPriority = isset($_REQUEST['left_neighbor_priority'])
        ? intval($_REQUEST['left_neighbor_priority']) : null;
    $leftNeighborTaskId   = isset($_REQUEST['left_neighbor_task_id'])
        ? intval($_REQUEST['left_neighbor_task_id'])  : null;

    $table_id     = $newMachineId % 10;
    $v_machine_id = intval($newMachineId / 10);

    $today = date('Y-m-d');

    $draggedTask = $conn->query(
        "SELECT * FROM pu_machine_table WHERE id = $taskId"
    )->fetch_assoc();

    if (!$draggedTask) {
        ob_clean();
        echo json_encode(['status' => 'error', 'message' => 'Task not found']);
        ob_end_flush();
        exit;
    }

    $oldMachineId = (int)$draggedTask['machine_id'];

    // Compact priorities on SOURCE
    $p   = 1;
    $res = $conn->query("
        SELECT id FROM pu_machine_table
        WHERE machine_id = $oldMachineId
          AND end_date   >= '$today'
          AND id         != $taskId
        ORDER BY priority
    ");
    while ($r = $res->fetch_assoc()) {
        $conn->query("UPDATE pu_machine_table SET priority = $p WHERE id = {$r['id']}");
        $p++;
    }

    // Make room on DESTINATION
    $insertPriority = ($leftNeighborPriority !== null)
        ? ($leftNeighborPriority + 1)
        : 1;

    $conn->query("
        UPDATE pu_machine_table
        SET priority = priority + 1
        WHERE machine_id = $newMachineId
          AND end_date   >= '$today'
          AND priority   >= $insertPriority
    ");

    // Move the task
    $conn->query("
        UPDATE pu_machine_table
        SET machine_id   = $newMachineId,
            table_id     = $table_id,
            v_machine_id = $v_machine_id,
            priority     = $insertPriority
        WHERE id = $taskId
    ");

    // Recalculate schedules
    recalculateMachineDatesforpu($conn, $oldMachineId);
    recalculateMachineDatesforpu($conn, $newMachineId);

    ob_clean();
    echo json_encode(['status' => 'success', 'message' => 'Task updated successfully']);
    ob_end_flush();
    exit;
}

// =============================================
// GET: Main data list
// =============================================
try {

    $current_page = max(1, (int)($_GET['current_page'] ?? 1));
    $per_page     = max(1, (int)($_GET['per_page']     ?? 50));
    $offset       = ($current_page - 1) * $per_page;

    $start_date = $_GET['start_date'] ?? date('Y-m-d');
    $work_order = trim($_GET['work_order'] ?? '');
    $product    = trim($_GET['product']    ?? '');
    $machine_id = (int)($_GET['machine']   ?? 0);
    $priority   = (int)($_GET['priority']  ?? 0);

    $where = [
        "DATE(mmt.end_date) >= '$start_date'",
        "(mmt.is_break = 0 OR mmt.is_break IS NULL)"
    ];

    if ($work_order !== '') {
        $work_order_safe = $conn->real_escape_string($work_order);
        $where[] = "wo.work_order_no LIKE '%$work_order_safe%'";
    }

    if ($product !== '') {
        $product_safe = $conn->real_escape_string($product);
        $where[] = "(mmt.product_name LIKE '%$product_safe%'
                    OR won.work_order_name LIKE '%$product_safe%')";
    }

    if ($machine_id > 0) {
        $where[] = "mmt.machine_id = $machine_id";
    }

    if ($priority > 0) {
        $where[] = "mmt.priority = $priority";
    }

    $where_clause = implode(' AND ', $where);

    // Count
    $count_sql = "
        SELECT COUNT(DISTINCT mmt.id) AS total
        FROM pu_machine_table mmt
        LEFT JOIN work_order wo ON mmt.work_order_id = wo.id
        LEFT JOIN work_order_name won
            ON mmt.work_order_id = won.work_order_id
           AND mmt.part_id = won.part_id
        WHERE $where_clause
    ";

    $total_records = $conn->query($count_sql)->fetch_assoc()['total'];
    $total_pages   = ceil($total_records / $per_page);

    // Data
    $sql = "
        SELECT
            mmt.machine_id AS task,
            mmt.id         AS task_id,
            mmt.table_id, 
			mmt.v_machine_id,
            wo.work_order_no,
            wo.require_date,
            mmt.part_id,
            mmt.setup_hours,
            mmt.priority,
            mmt.total_hours,
            mmt.work_order_id,
            mmt.product_name,
            mmt.quantity,
            mmt.finished,
            mmt.set_id,
            won.work_order_name,
            mmt.shift_ids AS shift_name,
            DATE(mmt.start_date) AS start_date,
            DATE(mmt.end_date)   AS end_date,
            TIME(mmt.start_date) AS start_time,
            mmt.is_break,
            mmt.break_reason,
            mmt.is_breakdown_affected,
            mmt.breakdown_hours,
            mb.id               AS breakdown_id,
            mb.breakdown_reason AS machine_breakdown_reason,
            m.end_date          AS manufacture_end_date,
            m.description       AS manufacture_description,
            m.parent_ids,
            COALESCE(srd.setting_instruction, rd.setting_instruction) AS setting_instruction,
            im.part_number AS parent_part_number,
            sd.resource_code AS machine_name,
            GROUP_CONCAT(DISTINCT wocr.work_order_no ORDER BY wocr.work_order_no SEPARATOR ', ')
                AS customer_order_nos,
            COALESCE((
                SELECT SUM(vpcl.quantity)
                FROM pu_part_count_log vpcl
                WHERE vpcl.work_order_id  = wo.id
                  AND vpcl.pu_machine_table_id = mmt.id
                  AND vpcl.part_id         = mmt.part_id
                  AND vpcl.type            = 'ok'
                  AND vpcl.id IN (
                      SELECT MAX(vpcl2.id)
                      FROM pu_part_count_log vpcl2
                      WHERE vpcl2.engineer_id           = vpcl.engineer_id
                        AND vpcl2.work_order_id         = vpcl.work_order_id
                        AND vpcl2.pu_machine_table_id   = vpcl.pu_machine_table_id
                        AND vpcl2.part_id               = vpcl.part_id
                        AND vpcl2.type                  = 'ok'
                      GROUP BY vpcl2.engineer_id
                  )
            ), 0) AS quantity_made
        FROM pu_machine_table mmt
        LEFT JOIN work_order wo
            ON mmt.work_order_id = wo.id
        LEFT JOIN route_details rd
            ON rd.item_id = mmt.part_id
        LEFT JOIN set_route_details srd
            ON srd.set_id = mmt.set_id
        LEFT JOIN work_order_name won
            ON mmt.work_order_id = won.work_order_id
           AND mmt.part_id = won.part_id
        LEFT JOIN machine_breakdown mb
            ON mmt.machine_id = mb.machine_id
           AND mb.is_active = 1
        LEFT JOIN station_detail_table sd
            ON mmt.v_machine_id = sd.id
        LEFT JOIN (
            SELECT m1.*
            FROM manufacture_machine_table m1
            INNER JOIN (
                SELECT part_id, work_order_id, MAX(end_date) max_end_date
                FROM manufacture_machine_table
                GROUP BY part_id, work_order_id
            ) x ON m1.part_id       = x.part_id
               AND m1.work_order_id = x.work_order_id
               AND m1.end_date      = x.max_end_date
        ) m ON mmt.part_id = m.part_id
           AND mmt.work_order_id = m.work_order_id
        LEFT JOIN item_management im
            ON m.parent_ids = im.id
        LEFT JOIN work_order_customer_ref_mapping wocr
            ON mmt.work_order_id = wocr.work_order_id
        WHERE $where_clause
        GROUP BY mmt.id
        ORDER BY mmt.priority, mmt.machine_id, mmt.table_id
        LIMIT $per_page OFFSET $offset
    ";

    $result = $conn->query($sql);
    if (!$result) throw new Exception($conn->error);

    $rows = [];
    while ($row = $result->fetch_assoc()) {
        $part_id = intval($row['part_id']);

        // Bought-in materials (product_group = 17)
       $row['bought_in_materials'] = getAllBoughtInMaterials($conn, $part_id);

        // Inventory dots: each BOM sub-part → stock status
        $invDots = [];
        if ($part_id > 0) {
            $invResult = $conn->query("
                SELECT im.part_number,
                       COALESCE(i.quantity_in_stock, 0) AS quantity_in_stock
                FROM bom b
                LEFT JOIN inventory i        ON b.sub_part_id = i.item_id
                LEFT JOIN item_management im ON b.sub_part_id = im.id
                WHERE b.main_product_id = $part_id
            ");
            if ($invResult) {
                while ($inv = $invResult->fetch_assoc()) {
                    $invDots[] = [
                        'part_number' => $inv['part_number'],
                        'in_stock'    => (float)$inv['quantity_in_stock'] > 0
                    ];
                }
            }
        }
        $row['inventory_dots'] = $invDots;

        $rows[] = $row;
    }

    ob_clean();
    echo json_encode([
        'success' => true,
        'data'    => $rows,
        'pagination' => [
            'current_page'  => $current_page,
            'per_page'      => $per_page,
            'total_records' => $total_records,
            'total_pages'   => $total_pages,
            'showing_from'  => $offset + 1,
            'showing_to'    => min($offset + $per_page, $total_records)
        ]
    ]);

} catch (Exception $e) {
    ob_clean();
    echo json_encode([
        'success' => false,
        'error'   => $e->getMessage()
    ]);
}

ob_end_flush();
?>