<?php
ini_set('session.cookie_secure', 1);
session_start(['read_and_close' => true]);
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
    

    // Second query: the second part of the UNION
    $stmt3_2 = $pdo->prepare("
        SELECT 
      
        '' AS p_name, 
        d.t_type,
         'TOTAL' AS t_qty,
        SUM(COALESCE(d.t_credit, 0) + COALESCE(d.t_debit, 0)) AS t_debit,  
        
        2 AS row_order  
   FROM transactions d
JOIN accounts u 
    ON d.t_cr = u.id 
    AND (d.t_type = 'REC' OR d.t_type = 'EXP' OR d.t_type = 'CS INV' OR d.t_type = 'INV PMT')    

JOIN accounts y
    ON d.t_ct_det = y.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
    ");
    $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 
      
        y.acc_name AS p_name, 
        u.acc_type  AS t_qty, 
        SUM(COALESCE(d.t_credit, 0) + COALESCE(d.t_debit, 0)) AS t_debit,  
        
        3 AS row_order  
   FROM transactions d
JOIN accounts u 
    ON d.t_cr = u.id 
    AND (d.t_type = 'REC' OR d.t_type = 'EXP'  OR d.t_type = 'CTFR' OR d.t_type = 'BNK' OR d.t_type = 'CS INV')   

JOIN accounts y
    ON d.t_tt_doc = y.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, d.t_id
    ");
    $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 
         
        'Account' AS p_name, 
        'Type' AS t_qty, 
        NULL AS t_debit,  
        
        4 AS row_order  
   FROM transactions d
JOIN accounts u 
    ON d.t_cr = u.id 
    AND (d.t_type = 'REC' OR d.t_type = 'EXP' OR d.t_type = 'CTFR' OR d.t_type = 'BNK'  OR d.t_type = 'CS INV')    

JOIN accounts y
    ON d.t_ct_det = y.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
    ");
    $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_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['t_qty'];

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

        // Assign styles based on transaction type
        switch ($t_type) {
            case 'Type':
                 $lineStyle = " color: #6c757d;";
                $dateStyle = "font-size: 0px; color: #FFFFFF;";
              //  $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'Cash':
                 $lineStyle = " font-size: 14px; 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 'Discount':
                 $lineStyle = " font-size: 14px; color: #6c757d;";
                $dateStyle = " font-size: 14px; color: #6c757d;";
              //  $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545;" : "font-weight: bold; font-size: 16px; color: #000;";
                break;
            case 'BANK':
                $lineStyle = " font-size: 14px; 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 'NAME':
                $lineStyle = " font-size: 14px; color: #000;";
                $dateStyle = " 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 = " font-size: 14px; color: #000;";
                $dateStyle = " 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 . '">' . number_format($row["t_debit"], 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());
}
?>
