<?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  ||  $ord_no === 0 || empty($t_type)) {
        echo '<tr><td colspan="4" class="text-center text-warning">Invalid parameters: ord_no or t_type is missing.</td></tr>';
        exit;
    }

    // First query: the first part of the UNION
    $stmt3_1 = $pdo->prepare("
        SELECT 
            d.t_id, 
            d.t_tt_doc, 
            d.t_doc_no, 
            d.t_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 =:t_type)    
        JOIN products r ON r.id = d.t_item 
        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 
        ORDER BY t_item
    ");
    $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 t_cr, 
        NULL AS t_item,
            'Item' AS p_name, 
            'Quantity' 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 = '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_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);
    
    // Second query: the second 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 t_cr, 
        NULL AS t_item,
        NULL AS p_name, 
        'TOTAL' AS t_qty, 
        NULL AS t_credit, 
        NULL AS t_debit,
        SUM(COALESCE(d.t_credit, 0) + COALESCE(d.t_debit, 0)) AS t_amount,
        'TOTAL' 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,
            1 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 = '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_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);
   

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

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

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

        // 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 = "color: #008080;";
                $dateStyle = " font-size: 14px; color: #008080;";
                $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 = "color: #d50592 ;";
                $dateStyle = " font-size: 14px; color: #d50592 ;";
                $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 = "color: #E9967A;";
                    $dateStyle = "font-size: 14px; color: #E9967A;";
                    $statusStyle = "font-size: 14px; color: #E9967A;";
                    break;
                
                case 'PRCH':
                    $lineStyle = " color: #00d821 ;";
                    $dateStyle = "font-size: 14px; color: #00d821 ;";
                    $statusStyle = "font-size: 14px; color: #00d821 ;";
                    break;
                case 'RET PRCH':
                    $lineStyle = "color: #6c757d;";
                    $dateStyle = "font-size: 14px; color: #6c757d;";
                    $statusStyle = "font-size: 14px; color: #6c757d;";
                    break;
            case '':
                $lineStyle = " color: #6c757d;";
                 $dateStyle = "font-size: 0px; color: #6c757d;";
                break;
            case 'SHT/EXC':
                $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;
            case '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>
                <td style="' . $dateStyle . '" text-align: right>' . number_format($row["t_amount"], 0, '.', ',') . '</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());
}
?>
