<?php
include("../../../include/startup.php");
header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors', 0); // IMPORTANT
ini_set('log_errors', 1);

// ─────────────────────────────────────────────────────────────
// Mark a single BOM/raw-material line as "picked" so it stops
// showing up in the kitting list (persisted in kitting_bom_picks).
// ─────────────────────────────────────────────────────────────
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'toggle_pick') {

    $schedule_id = intval($_REQUEST['schedule_id'] ?? 0);
    $cell        = mysqli_real_escape_string($conn, $_REQUEST['cell'] ?? '');
    $bom_key     = mysqli_real_escape_string($conn, $_REQUEST['bom_key'] ?? '');
    $picked      = isset($_REQUEST['picked']) ? intval($_REQUEST['picked']) : 1;

    if (!$schedule_id || $cell === '' || $bom_key === '') {
        echo json_encode(['error' => 'Missing schedule_id, cell or bom_key']);
        exit;
    }

    if ($picked) {
        $sql = "
            INSERT INTO kitting_bom_picks (schedule_id, cell, bom_key, picked, picked_at)
            VALUES ($schedule_id, '$cell', '$bom_key', 1, NOW())
            ON DUPLICATE KEY UPDATE picked = 1, picked_at = NOW()
        ";
    } else {
        // allow "un-picking" (undo) if ever needed from the UI
        $sql = "
            INSERT INTO kitting_bom_picks (schedule_id, cell, bom_key, picked, picked_at)
            VALUES ($schedule_id, '$cell', '$bom_key', 0, NOW())
            ON DUPLICATE KEY UPDATE picked = 0, picked_at = NOW()
        ";
    }

    $ok = mysqli_query($conn, $sql);
    if (!$ok) {
        echo json_encode(['error' => mysqli_error($conn)]);
        exit;
    }

    mysqli_close($conn);
    if (ob_get_length()) ob_clean();
    echo json_encode(['success' => true]);
    exit;
}

if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_work_orders') {
$sql = "
    SELECT
        v.id AS schedule_id,
        v.work_order_id,
        v.set_id,
        v.part_id,
        v.quantity,
        v.end_date,
        v.product_name,
        v.finished,
        'VAC' AS cell,
        COALESCE(won.work_order_name, v.product_name) AS work_order_name,
        im.part_number,
        im.product_description,
        im.tool_location,
        im.checking_fixture_location,
        im.cnc_program_no,
        COALESCE(drl.receipt_location_code, 'N/A') AS warehouse_location
    FROM vac_machine_table v
    LEFT JOIN work_order_name won
        ON won.work_order_id = v.work_order_id AND won.part_id = v.part_id
    LEFT JOIN item_management im
        ON im.id = v.part_id
    LEFT JOIN default_receipt_location drl
        ON drl.id = im.mpe
    WHERE v.finished = 0
      AND (v.is_break = 0 OR v.is_break IS NULL)

    UNION ALL

    SELECT
        c.id AS schedule_id,
        c.work_order_id,
        c.set_id,
        c.part_id,
        c.quantity,
        c.end_date,
        c.product_name,
        c.finished,
        'CNC' AS cell,
        COALESCE(won.work_order_name, c.product_name) AS work_order_name,
        im.part_number,
        im.product_description,
        im.tool_location,
        im.checking_fixture_location,
        im.cnc_program_no,
        COALESCE(drl.receipt_location_code, 'N/A') AS warehouse_location
    FROM cnc_machine_table c
    LEFT JOIN work_order_name won
        ON won.work_order_id = c.work_order_id AND won.part_id = c.part_id
    LEFT JOIN item_management im
        ON im.id = c.part_id
    LEFT JOIN default_receipt_location drl
        ON drl.id = im.mpe
    WHERE c.finished = 0
      AND (c.is_break = 0 OR c.is_break IS NULL)

    UNION ALL

    SELECT
        p.id AS schedule_id,
        p.work_order_id,
        p.set_id,
        p.part_id,
        p.quantity,
        p.end_date,
        p.product_name,
        p.finished,
        'PU' AS cell,
        COALESCE(won.work_order_name, p.product_name) AS work_order_name,
        im.part_number,
        im.product_description,
        im.tool_location,
        im.checking_fixture_location,
        im.cnc_program_no,
        COALESCE(drl.receipt_location_code, 'N/A') AS warehouse_location
    FROM pu_machine_table p
    LEFT JOIN work_order_name won
        ON won.work_order_id = p.work_order_id AND won.part_id = p.part_id
    LEFT JOIN item_management im
        ON im.id = p.part_id
    LEFT JOIN default_receipt_location drl
        ON drl.id = im.mpe
    WHERE p.finished = 0
      AND (p.is_break = 0 OR p.is_break IS NULL)

    UNION ALL

    SELECT
        ap.id AS schedule_id,
        ap.work_order_id,
        ap.set_id,
        ap.part_id,
        ap.quantity,
        ap.end_date,
        ap.product_name,
        ap.finished,
        'ASSY-PU' AS cell,
        COALESCE(won.work_order_name, ap.product_name) AS work_order_name,
        im.part_number,
        im.product_description,
        im.tool_location,
        im.checking_fixture_location,
        im.cnc_program_no,
        COALESCE(drl.receipt_location_code, 'N/A') AS warehouse_location
    FROM assypu_machine_table ap
    LEFT JOIN work_order_name won
        ON won.work_order_id = ap.work_order_id AND won.part_id = ap.part_id
    LEFT JOIN item_management im
        ON im.id = ap.part_id
    LEFT JOIN default_receipt_location drl
        ON drl.id = im.mpe
    WHERE ap.finished = 0
      AND (ap.is_break = 0 OR ap.is_break IS NULL)

    UNION ALL

    SELECT
        a.id AS schedule_id,
        a.work_order_id,
        a.set_id,
        a.part_id,
        a.quantity,
        a.end_date,
        a.product_name,
        a.finished,
        'ASSY' AS cell,
        COALESCE(won.work_order_name, a.product_name) AS work_order_name,
        im.part_number,
        im.product_description,
        im.tool_location,
        im.checking_fixture_location,
        im.cnc_program_no,
        COALESCE(drl.receipt_location_code, 'N/A') AS warehouse_location
    FROM assbly_machine_table a
    LEFT JOIN work_order_name won
        ON won.work_order_id = a.work_order_id AND won.part_id = a.part_id
    LEFT JOIN item_management im
        ON im.id = a.part_id
    LEFT JOIN default_receipt_location drl
        ON drl.id = im.mpe
    WHERE a.finished = 0
      AND (a.is_break = 0 OR a.is_break IS NULL)

    ORDER BY end_date ASC
";
    $result = mysqli_query($conn, $sql);

    if (!$result) {
        echo json_encode([
            'error' => mysqli_error($conn)
        ]);
        exit;
    }

    $rows = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }

    // ── Fetch generic BOM (used as fallback when no set) ──
    $part_ids = array_unique(array_filter(array_column($rows, 'part_id')));
    $bom_map  = [];

    if (!empty($part_ids)) {
        $in_list = implode(',', array_map('intval', $part_ids));
        $bom_res = mysqli_query($conn, "
            SELECT main_product_id, sub_part_name, quantity_required, type
            FROM bom
            WHERE main_product_id IN ($in_list)
        ");
        while ($b = mysqli_fetch_assoc($bom_res)) {
            $bom_map[$b['main_product_id']][] = $b;
        }
    }

    // ── Fetch set-specific raw materials from work_order_raw_matrial ──
    $set_pairs = [];
    foreach ($rows as $r) {
        if (!empty($r['set_id'])) {
            $key = $r['work_order_id'] . '_' . $r['set_id'];
            $set_pairs[$key] = [
                'work_order_id' => $r['work_order_id'],
                'set_id'        => $r['set_id'],
            ];
        }
    }

    $raw_map = [];
    if (!empty($set_pairs)) {
        $tuples = [];
        foreach ($set_pairs as $p) {
            $tuples[] = '(' . intval($p['work_order_id']) . ',' . intval($p['set_id']) . ')';
        }
        $tuple_list = implode(',', $tuples);

        $raw_sql = "
            SELECT work_order_id, set_id, product_id, product_name, quantity
            FROM work_order_raw_matrial
            WHERE (work_order_id, set_id) IN ($tuple_list)
        ";
        $raw_res = mysqli_query($conn, $raw_sql);
        if ($raw_res) {
            while ($rm = mysqli_fetch_assoc($raw_res)) {
                $key = $rm['work_order_id'] . '_' . $rm['set_id'];
                $raw_map[$key][] = $rm;
            }
        }
    }

    // ── Fetch already-picked BOM lines so we can filter them out below ──
    $schedule_ids = array_unique(array_filter(array_column($rows, 'schedule_id')));
    $picked_map   = [];
    if (!empty($schedule_ids)) {
        $sid_list  = implode(',', array_map('intval', $schedule_ids));
        $pick_res  = mysqli_query($conn, "
            SELECT schedule_id, cell, bom_key
            FROM kitting_bom_picks
            WHERE picked = 1 AND schedule_id IN ($sid_list)
        ");
        if ($pick_res) {
            while ($p = mysqli_fetch_assoc($pick_res)) {
                $picked_map[$p['schedule_id'] . '_' . $p['cell'] . '_' . $p['bom_key']] = true;
            }
        }
    }

    // ── Attach bom_items to each row (each item gets a stable bom_key) ──
    foreach ($rows as &$r) {
        $set_id = $r['set_id'] ?? null;
        $key    = $r['work_order_id'] . '_' . $set_id;

        if (!empty($set_id) && isset($raw_map[$key])) {
            // Set-based: quantities come directly from work_order_raw_matrial
            $bom_items = [];
            foreach ($raw_map[$key] as $rm) {
                $bom_key = 'RAW|' . $rm['product_name'];
                $bom_items[] = [
                    'type'              => 'RAW',
                    'sub_part_name'     => $rm['product_name'],
                    'quantity_required' => $rm['quantity'],
                    'bom_key'           => $bom_key,
                ];
            }
            $r['bom_items'] = $bom_items;
        } else {
            // No set: BOM qty per unit * main part quantity
            $multiplier = floatval($r['quantity']);
            $bom_items  = $bom_map[$r['part_id']] ?? [];
            $computed   = [];
            foreach ($bom_items as $b) {
                $bom_key = $b['type'] . '|' . $b['sub_part_name'];
                $computed[] = [
                    'type'              => $b['type'],
                    'sub_part_name'     => $b['sub_part_name'],
                    'quantity_required' => floatval($b['quantity_required']) * $multiplier,
                    'bom_key'           => $bom_key,
                ];
            }
            $r['bom_items'] = $computed;
        }

        // Flag anything already ticked/picked for this schedule row + cell
        // (kept in the payload, not stripped, so the client can filter by
        // status and still show "Picked" / "All" views)
        $sid = $r['schedule_id'];
        foreach ($r['bom_items'] as &$item) {
            $k = $sid . '_' . $r['cell'] . '_' . $item['bom_key'];
            $item['picked'] = isset($picked_map[$k]);
        }
        unset($item);
    }
    unset($r);

    // ── Fetch ALL warehouse locations ──
    $loc_res = mysqli_query($conn, "
        SELECT DISTINCT receipt_location_code 
        FROM default_receipt_location
        WHERE receipt_location_code IS NOT NULL 
        AND receipt_location_code != ''
    ");

    $all_locations = [];
    while ($l = mysqli_fetch_assoc($loc_res)) {
        $all_locations[] = $l['receipt_location_code'];
    }

    mysqli_close($conn);
    if (ob_get_length()) ob_clean();
    echo json_encode([
        'rows'          => $rows,
        'all_locations' => $all_locations,
    ]);
    exit;

}
ob_end_flush();
?>