<?php
require_once "../../includes/db.php";
header('Content-Type: application/json');
try {
    $loc_id = isset($_GET['loc_id']) ? intval($_GET['loc_id']) : 0;
    $ord_no = isset($_GET['ord_no']) ? intval($_GET['ord_no']) : 0;
    $t_type = isset($_GET['t_type']) ? trim($_GET['t_type']) : '';

    if ($loc_id === 0  ) {
        echo '<tr><td colspan="4" class="text-center text-warning">Invalid parameters: loc_id is missing.</td></tr>';
        exit;
    }
    if ( $ord_no === 0 ) {
        echo '<tr><td colspan="4" class="text-center text-warning">Invalid parameters: ord_no  is missing.</td></tr>';
        exit;
    }
    if ( empty($t_type)) {
        echo '<tr><td colspan="4" class="text-center text-warning">Invalid parameters:  t_type is missing.</td></tr>';
        exit;
    }

    // First query: the first part of the UNION
    $stmt3_0 = $pdo->prepare("
        SELECT 
             NULL AS t_id, 
        NULL AS t_tt_doc,  
        NULL AS t_doc_no, 
        NULL AS t_date, 
        NULL AS M_date, 
        NULL AS t_cr, 
        NULL AS t_item,
            c.org_name AS p_name, 
        NULL AS t_qty, 
        NULL AS t_credit, 
        NULL AS t_debit,
        NULL AS t_amount,
        NULL AS t_type, 
        NULL AS t_narration, 
        NULL AS t_status, 
        NULL AS t_sub_date, 
        NULL AS acc_name,  
        NULL AS acc_id, 
        NULL AS user_id,
            6 AS row_order 
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id  LEFT JOIN company c
        ON c.id = d.co_id
        WHERE d.t_status IN ('Approved', 'Confirmed', 'Pending') 
        AND d.t_copy = 'D' 
        AND d.co_id = :loc_id 
        AND d.t_doc_no = :ord_no 
        AND d.t_type = :t_type
        GROUP BY d.t_doc_no, u.acc_name
    ");
    $stmt3_0->bindValue(':loc_id', $loc_id, PDO::PARAM_INT);
    $stmt3_0->bindValue(':ord_no', $ord_no, PDO::PARAM_INT);
    $stmt3_0->bindValue(':t_type', $t_type, PDO::PARAM_STR);
    $stmt3_0->execute();
    $data3_0 = $stmt3_0->fetchAll(PDO::FETCH_ASSOC);
    
    $stmt3_1 = $pdo->prepare("
        SELECT 
            d.t_id, 
            d.t_tt_doc, 
            d.t_doc_no, 
            d.t_date, 
             DATE_FORMAT(d.t_date, '%d %b %y') AS M_date,
            d.t_cr, 
            d.t_item, 
            r.p_name, 
            d.t_qty, 
            d.t_credit, 
            d.t_debit,
            COALESCE(d.t_credit, 0) + COALESCE(d.t_debit, 0) AS t_amount,
            d.t_type, 
            d.t_narration, 
            d.t_status, 
            d.t_sub_date, 
            u.acc_name, 
            u.id, 
            u.user_id,
            2 AS row_order  
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id 
        AND (d.t_type = 'DEL' OR d.t_type = 'GR')    
        JOIN products r ON r.id = d.t_item 
        WHERE d.t_status IN ('Approved', 'Confirmed', 'Pending') 
        AND d.t_copy = 'O' 
        AND d.co_id = :loc_id 
        AND d.t_doc_no = :ord_no 
        AND d.t_type = :t_type
    ");
    $stmt3_1->bindValue(':loc_id', $loc_id, PDO::PARAM_INT);
    $stmt3_1->bindValue(':ord_no', $ord_no, PDO::PARAM_INT);
    $stmt3_1->bindValue(':t_type', $t_type, PDO::PARAM_STR);
    $stmt3_1->execute();
    $data3_1 = $stmt3_1->fetchAll(PDO::FETCH_ASSOC);

    // Second query: the second part of the UNION
    $stmt3_2 = $pdo->prepare("
        SELECT 
             NULL AS t_id, 
        NULL AS t_tt_doc,  
        NULL AS t_doc_no, 
        NULL AS t_date, 
        NULL AS M_date, 
        NULL AS t_cr, 
        NULL AS t_item,
            'FROM' AS p_name, 
            u.acc_name AS t_qty, 
            NULL AS t_credit, 
        NULL AS t_debit,
        NULL AS t_amount,
        NULL AS t_type, 
        NULL AS t_narration, 
        NULL AS t_status, 
        NULL AS t_sub_date, 
        NULL AS acc_name,  
        NULL AS acc_id, 
        NULL AS user_id,
            3 AS row_order 
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id  
        WHERE d.t_status IN ('Approved', 'Confirmed', 'Pending') 
        AND d.t_copy = 'O' 
        AND d.co_id = :loc_id 
        AND d.t_doc_no = :ord_no 
        AND d.t_type = :t_type
        GROUP BY d.t_doc_no, u.acc_name
    ");
    $stmt3_2->bindValue(':loc_id', $loc_id, PDO::PARAM_INT);
    $stmt3_2->bindValue(':ord_no', $ord_no, PDO::PARAM_INT);
    $stmt3_2->bindValue(':t_type', $t_type, PDO::PARAM_STR);
    $stmt3_2->execute();
    $data3_2 = $stmt3_2->fetchAll(PDO::FETCH_ASSOC);
    
     // Third query: the third part of the UNION
    $stmt3_3 = $pdo->prepare("
        SELECT 
        NULL AS t_id, 
        NULL AS t_tt_doc,  
        NULL AS t_doc_no, 
        NULL AS t_date, 
        NULL AS M_date, 
        NULL AS t_cr, 
        NULL AS t_item, 
        d.t_type AS p_name, 
        d.t_doc_no AS t_qty,  
        NULL AS t_credit, 
        NULL AS t_debit,
        NULL AS t_amount,
        NULL AS t_type, 
        NULL AS t_narration, 
        NULL AS t_status, 
        NULL AS t_sub_date, 
        NULL AS acc_name,  
        NULL AS acc_id, 
        NULL AS user_id,
        4 AS row_order  
    FROM transactions d
    WHERE d.t_status IN ('Approved', 'Confirmed', 'Pending') 
      AND d.t_copy = 'O' 
      AND d.co_id = :loc_id 
      AND d.t_doc_no = :ord_no 
      AND d.t_type = :t_type
    GROUP BY d.t_doc_no
    ");
    $stmt3_3->bindValue(':loc_id', $loc_id, PDO::PARAM_INT);
    $stmt3_3->bindValue(':ord_no', $ord_no, PDO::PARAM_INT);
    $stmt3_3->bindValue(':t_type', $t_type, PDO::PARAM_STR);
    $stmt3_3->execute();
    $data3_3 = $stmt3_3->fetchAll(PDO::FETCH_ASSOC);
    
     // fourth query: the 4th part of the UNION
    $stmt3_4 = $pdo->prepare("
        SELECT 
        NULL AS t_id, 
        NULL AS t_tt_doc,  
        NULL AS t_doc_no, 
        NULL AS t_date, 
        NULL AS M_date, 
        NULL AS t_cr, 
        NULL AS t_item, 
        'DATE' AS p_name, 
        MAX(DATE_FORMAT(d.t_date, '%a, %d %b %Y') ) AS t_qty,  
        NULL AS t_credit, 
        NULL AS t_debit,
        NULL AS t_amount,
        NULL AS t_type, 
        NULL AS t_narration, 
        NULL AS t_status, 
        NULL AS t_sub_date, 
        NULL AS acc_name,  
        NULL AS acc_id, 
        NULL AS user_id,
        5 AS row_order  
    FROM transactions d
    WHERE d.t_status IN ('Approved', 'Confirmed', 'Pending') 
      AND d.t_copy = 'O' 
      AND d.co_id = :loc_id 
      AND d.t_doc_no = :ord_no 
      AND d.t_type = :t_type
    GROUP BY d.t_doc_no
    ");
    $stmt3_4->bindValue(':loc_id', $loc_id, PDO::PARAM_INT);
    $stmt3_4->bindValue(':ord_no', $ord_no, PDO::PARAM_INT);
    $stmt3_4->bindValue(':t_type', $t_type, PDO::PARAM_STR);
    $stmt3_4->execute();
    $data3_4 = $stmt3_4->fetchAll(PDO::FETCH_ASSOC);

    // Combine the results from both queries
$data3 = array_merge($data3_0, $data3_1, $data3_2, $data3_3, $data3_4);

// Sort the results by 'row_order'
usort($data3, function ($a, $b) {
    return $b['row_order'] <=> $a['row_order'];
});

// Limit the results to the top 2
$data3 = array_slice($data3, 0, 4);

if (!empty($data3)) {
    foreach ($data3 as $row) {
        $t_type = $row['p_name'];

        // Default styles
        $lineStyle = "color: #6c757d;"; // Default gray
        $dateStyle = "color: #FFFFFF;";
        $statusStyle = "color: #6c757d;";

        // Assign styles based on transaction type
        switch ($t_type) {
            case 'DEL':
                 $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #008080;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'GR':
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #d50592;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'STFR':
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #E9967A;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'PRCH':
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #00d821;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'RET PRCH':
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #6c757d;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'FROM':
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #000;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'DATE':
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #000;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case !NULL:
                $lineStyle = "border:none; font-weight: bold; font-size: 14px; color: #000;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'GRAND TOTAL':
                $lineStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
        }

        echo '<tr>
                <td style="' . $lineStyle . '">' . htmlspecialchars($row["p_name"]) . '</td>
                <td style="' . $lineStyle . '">' . htmlspecialchars($row["t_qty"] ?? '') . '</td>
               
            </tr>';
    }
} else {
    echo '<tr><td colspan="4" class="text-center text-warning">No matching records found.</td></tr>';
}


} catch (PDOException $e) {
    echo '<tr><td colspan="4" class="text-center text-danger">Error fetching data: ' . htmlspecialchars($e->getMessage()) . '</td></tr>';
    error_log("Database Error: " . $e->getMessage());
}
?>
