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

$sourceTableMap = [
    'vac'    => ['sop' => 'vac_selected_ok_parts',    'log' => 'vac_part_count_log',    'fk' => 'vac_part_count_log_id'],
    'pu'     => ['sop' => 'pu_selected_ok_parts',     'log' => 'pu_part_count_log',     'fk' => 'pu_part_count_log_id'],
    'cnc'    => ['sop' => 'cnc_selected_ok_parts',    'log' => 'cnc_part_count_log',    'fk' => 'cnc_part_count_log_id'],
    'assypu' => ['sop' => 'assypu_selected_ok_parts', 'log' => 'assypu_part_count_log', 'fk' => 'assypu_part_count_log_id'],
    'assbly' => ['sop' => 'assbly_selected_ok_parts', 'log' => 'assbly_part_count_log', 'fk' => 'assbly_part_count_log_id'],
];

function tableExists($conn, $name) {
    $e = mysqli_real_escape_string($conn, $name);
    $r = mysqli_query($conn, "SHOW TABLES LIKE '$e'");
    return $r && mysqli_num_rows($r) > 0;
}

$action = $_REQUEST['action'] ?? '';

// ─────────────────────────────────────────────────────────────────────────────
// ACTION: get_quarantine_groups
// ─────────────────────────────────────────────────────────────────────────────
if ($action === 'get_quarantine_groups') {

    $pg       = max(1, intval($_REQUEST['pg'] ?? 1));
    $perPage  = 20;
    $offset   = ($pg - 1) * $perPage;

    $filterSource   = mysqli_real_escape_string($conn, $_REQUEST['source']    ?? '');
    $filterMachine  = intval($_REQUEST['machine_id'] ?? 0);
    $filterDateFrom = mysqli_real_escape_string($conn, $_REQUEST['date_from'] ?? '');
    $filterDateTo   = mysqli_real_escape_string($conn, $_REQUEST['date_to']   ?? '');

    $branches = [];
    foreach ($sourceTableMap as $src => $tbl) {
        if ($filterSource !== '' && $filterSource !== $src) continue;
        if (!tableExists($conn, $tbl['sop']))               continue;
        if (!tableExists($conn, $tbl['log']))               continue;

        $mw  = $filterMachine  > 0    ? "AND pcl.machine_id = $filterMachine"           : '';
        $dfw = $filterDateFrom !== '' ? "AND DATE(sop.created_at) >= '$filterDateFrom'"  : '';
        $dtw = $filterDateTo   !== '' ? "AND DATE(sop.created_at) <= '$filterDateTo'"    : '';

        $branches[$src] = "
            SELECT
                sop.id                   AS sop_id,
                sop.part_id,
                sop.quantity,
                sop.created_at,
                pcl.machine_id,
                pcl.engineer_id,
                im.part_number,
                im.product_description   AS part_description,
                sd.resource_code         AS machine_name,
                a.uname                  AS engineer_name,
                '$src'                   AS source
            FROM {$tbl['sop']} sop
            INNER JOIN {$tbl['log']} pcl      ON sop.{$tbl['fk']} = pcl.id
            LEFT  JOIN item_management im      ON sop.part_id       = im.id
            LEFT  JOIN station_detail_table sd ON pcl.machine_id    = sd.id
            LEFT  JOIN admin a                 ON pcl.engineer_id   = a.id
            WHERE pcl.type = 'quarantine'
              AND (sop.review_status IS NULL OR sop.review_status = 'pending')
              $mw $dfw $dtw
        ";
    }

    if (empty($branches)) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'No matching source tables found.']);
        exit;
    }

    $wrappedBranches = array_map(function($b) { return "($b)"; }, array_values($branches));
    $union = implode(' UNION ALL ', $wrappedBranches);

    // Count distinct groups
    $countRes = mysqli_query($conn, "
        SELECT COUNT(*) AS total
        FROM (
            SELECT source, part_id, machine_id
            FROM ($union) AS r
            GROUP BY source, part_id, machine_id
        ) AS g
    ");
    if (!$countRes) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'Count query failed: ' . mysqli_error($conn)]);
        exit;
    }
    $totalRows  = (int) mysqli_fetch_assoc($countRes)['total'];
    $totalPages = max(1, (int) ceil($totalRows / $perPage));

    // Pending badge count
    $pendingCount = 0;
    foreach ($sourceTableMap as $src => $tbl) {
        if (!tableExists($conn, $tbl['sop'])) continue;
        if (!tableExists($conn, $tbl['log'])) continue;
        $res = mysqli_query($conn, "
            SELECT COUNT(*) AS total
            FROM (
                SELECT sop.part_id, pcl.machine_id
                FROM {$tbl['sop']} sop
                INNER JOIN {$tbl['log']} pcl ON sop.{$tbl['fk']} = pcl.id
                WHERE pcl.type = 'quarantine'
                  AND (sop.review_status IS NULL OR sop.review_status = 'pending')
                GROUP BY sop.part_id, pcl.machine_id
            ) AS grp
        ");
        if ($res) $pendingCount += (int) mysqli_fetch_assoc($res)['total'];
    }

    // Paginated grouped rows
    $dataRes = mysqli_query($conn, "
        SELECT
            source,
            part_id,
            machine_id,
            part_number,
            part_description,
            machine_name,
            COUNT(*)        AS record_count,
            SUM(quantity)   AS total_qty,
            GROUP_CONCAT(DISTINCT engineer_name ORDER BY engineer_name SEPARATOR ', ') AS engineers,
            MIN(created_at) AS earliest_date
        FROM ($union) AS r
        GROUP BY source, part_id, machine_id
        ORDER BY MIN(created_at) ASC
        LIMIT $perPage OFFSET $offset
    ");
    if (!$dataRes) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'Data query failed: ' . mysqli_error($conn)]);
        exit;
    }

    $rows = [];
    while ($row = mysqli_fetch_assoc($dataRes)) {
        $row['earliest_date'] = date('d M Y H:i', strtotime($row['earliest_date']));
        $row['total_qty']     = (int) $row['total_qty'];
        $row['record_count']  = (int) $row['record_count'];
        $rows[] = $row;
    }

    ob_end_clean();
    echo json_encode([
        'success'       => true,
        'data'          => $rows,
        'total'         => $totalRows,
        'pages'         => $totalPages,
        'page'          => $pg,
        'per_page'      => $perPage,
        'pending_count' => $pendingCount,
    ]);
    exit;
}

// ─────────────────────────────────────────────────────────────────────────────
// ACTION: process_quarantine_batch
// keep_qty  → add to finished goods inventory
// scrap_qty → return BOM children to raw material stock
// All records in the group → marked reviewed
// ─────────────────────────────────────────────────────────────────────────────
elseif ($action === 'process_quarantine_batch') {

    $source    = mysqli_real_escape_string($conn, $_POST['source']     ?? '');
    $partId    = intval($_POST['part_id']    ?? 0);
    $machineId = intval($_POST['machine_id'] ?? 0);
    $keepQty   = intval($_POST['keep_qty']   ?? 0);
    $scrapQty  = intval($_POST['scrap_qty']  ?? 0);

    if (!isset($sourceTableMap[$source]) || $partId <= 0 || $machineId <= 0) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'Invalid parameters.']);
        exit;
    }
    if ($keepQty < 0 || $scrapQty < 0) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'Quantities cannot be negative.']);
        exit;
    }

    $sopTable = $sourceTableMap[$source]['sop'];
    $logTable = $sourceTableMap[$source]['log'];
    $logFkCol = $sourceTableMap[$source]['fk'];

    // Fetch all pending records for this group
    $fetchRes = mysqli_query($conn, "
        SELECT sop.id, sop.quantity, sop.$logFkCol AS log_id
        FROM $sopTable sop
        INNER JOIN $logTable pcl ON sop.$logFkCol = pcl.id
        WHERE sop.part_id    = $partId
          AND pcl.machine_id = $machineId
          AND pcl.type       = 'quarantine'
          AND (sop.review_status IS NULL OR sop.review_status = 'pending')
        ORDER BY sop.id ASC
    ");
    if (!$fetchRes || mysqli_num_rows($fetchRes) === 0) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'No pending records found for this group.']);
        exit;
    }

    $sopIds   = [];
    $dbTotal  = 0;
    $firstLog = 0;
    while ($r = mysqli_fetch_assoc($fetchRes)) {
        $sopIds[]  = intval($r['id']);
        $dbTotal  += (int) $r['quantity'];
        if (!$firstLog) $firstLog = intval($r['log_id']);
    }

    // Server-side total check
    if (($keepQty + $scrapQty) !== $dbTotal) {
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => 'Keep (' . $keepQty . ') + Scrap (' . $scrapQty . ') must equal total (' . $dbTotal . '). Please refresh and try again.']);
        exit;
    }

    // Work order info for BOM return
    $workOrderId = 0;
    $setId       = 0;
    if ($firstLog) {
        $logRow = mysqli_query($conn, "SELECT work_order_id, setId FROM $logTable WHERE id = $firstLog LIMIT 1");
        if ($logRow && mysqli_num_rows($logRow) > 0) {
            $ld = mysqli_fetch_assoc($logRow);
            $workOrderId = intval($ld['work_order_id']);
            $setId       = intval($ld['setId'] ?? 0);
        }
    }

    $sopIdsIn   = implode(',', $sopIds);
    $reviewedBy = intval($_SESSION['admin_id'] ?? $_SESSION['user_id'] ?? 0);
    $reviewedAt = date('Y-m-d H:i:s');

    mysqli_autocommit($conn, false);
    $error  = false;
    $errMsg = '';

    // 1. Keep qty → finished goods inventory
    if (!$error && $keepQty > 0) {
        $invRes = mysqli_query($conn, "SELECT id FROM inventory WHERE item_id = $partId LIMIT 1");
        if ($invRes && mysqli_num_rows($invRes) > 0) {
            if (!mysqli_query($conn, "UPDATE inventory SET quantity_in_stock = quantity_in_stock + $keepQty WHERE item_id = $partId")) {
                $error = true; $errMsg = 'Inventory update failed: ' . mysqli_error($conn);
            }
        } else {
            if (!mysqli_query($conn, "INSERT INTO inventory (item_id, quantity_in_stock) VALUES ($partId, $keepQty)")) {
                $error = true; $errMsg = 'Inventory insert failed: ' . mysqli_error($conn);
            }
        }
    }

    // 2. Scrap qty → return BOM children to raw stock
    if (!$error && $scrapQty > 0) {
        $bomRes = mysqli_query($conn, "SELECT sub_part_id, quantity_required FROM bom WHERE main_product_id = $partId");
        if ($bomRes) {
            while ($bom = mysqli_fetch_assoc($bomRes)) {
                $subPartId  = intval($bom['sub_part_id']);
                $returnQty  = (float) $bom['quantity_required'] * $scrapQty;

                $subInv = mysqli_query($conn, "SELECT id FROM inventory WHERE item_id = $subPartId LIMIT 1");
                if ($subInv && mysqli_num_rows($subInv) > 0) {
                    if (!mysqli_query($conn, "UPDATE inventory SET quantity_in_stock = quantity_in_stock + $returnQty WHERE item_id = $subPartId")) {
                        $error = true; $errMsg = 'Sub-part update failed: ' . mysqli_error($conn); break;
                    }
                } else {
                    if (!mysqli_query($conn, "INSERT INTO inventory (item_id, quantity_in_stock) VALUES ($subPartId, $returnQty)")) {
                        $error = true; $errMsg = 'Sub-part insert failed: ' . mysqli_error($conn); break;
                    }
                }

                // Return raw material allocation in work order
                if (!$error && $workOrderId > 0) {
                    mysqli_query($conn, "
                        UPDATE work_order_raw_matrial
                        SET quantity = quantity + $returnQty
                        WHERE work_order_id = $workOrderId
                          AND product_id    = $subPartId
                          AND set_id        = $setId
                    ");
                }
            }
        }
    }

    // 3. Mark all records reviewed
    if (!$error) {
        if (!mysqli_query($conn, "
            UPDATE $sopTable
            SET review_status = 'reviewed',
                reviewed_at   = '$reviewedAt',
                reviewed_by   = $reviewedBy
            WHERE id IN ($sopIdsIn)
        ")) {
            // Fallback without reviewed_by if column doesn't exist yet
            if (!mysqli_query($conn, "
                UPDATE $sopTable
                SET review_status = 'reviewed',
                    reviewed_at   = '$reviewedAt'
                WHERE id IN ($sopIdsIn)
            ")) {
                $error = true; $errMsg = 'Status update failed: ' . mysqli_error($conn);
            }
        }
    }

    if ($error) {
        mysqli_rollback($conn);
        mysqli_autocommit($conn, true);
        ob_end_clean();
        echo json_encode(['success' => false, 'message' => $errMsg]);
    } else {
        mysqli_commit($conn);
        mysqli_autocommit($conn, true);
        ob_end_clean();
        $parts = [];
        if ($keepQty  > 0) $parts[] = $keepQty  . ' kept';
        if ($scrapQty > 0) $parts[] = $scrapQty . ' scrapped';
        echo json_encode([
            'success' => true,
            'message' => count($sopIds) . ' record(s) reviewed — ' . implode(', ', $parts) . '.',
            'records' => count($sopIds),
            'keep'    => $keepQty,
            'scrap'   => $scrapQty,
        ]);
    }
    exit;
}

// ─────────────────────────────────────────────────────────────────────────────
// ACTION: get_quarantine_machines
// ─────────────────────────────────────────────────────────────────────────────
elseif ($action === 'get_quarantine_machines') {
    $result   = mysqli_query($conn, "SELECT id, resource_code FROM station_detail_table WHERE hide = 0 ORDER BY resource_code");
    $machines = [];
    while ($row = mysqli_fetch_assoc($result)) $machines[] = $row;
    ob_end_clean();
    echo json_encode(['success' => true, 'data' => $machines]);
    exit;
}

else {
    ob_end_clean();
    echo json_encode(['success' => false, 'message' => 'Unknown action: ' . htmlspecialchars($action)]);
    exit;
}
?>