<?php
session_start();
ini_set('session.cookie_secure', 1);
require_once "../../includes/db.php";
header('Content-Type: application/json');

try {
    $query = "%" . $_GET["query"] . "%";
    $sel_store_id = isset($_GET['storeid']) ? intval($_GET['storeid']) : 0;
  
    $date1 = $_GET['date1'] ?? null;
$date2 = $_GET['date2'] ?? null;

    $stmt3 = $pdo->prepare("
    SELECT
    d.t_date,d.t_tt_doc,d.t_cr,d.t_item,r.p_name,d.t_qty,t_type,d.t_status,d.t_sub_date, u.acc_name ,u.id,
    SUM(CASE WHEN d.t_type = 'RET PRCH' THEN d.t_qty ELSE 0 END) AS total_RT_PRCH,
    SUM(CASE WHEN d.t_type = 'PRCH' THEN d.t_qty ELSE 0 END) AS total_PRCH
    FROM transactions d
    JOIN accounts u ON d.t_cr=u.id
    JOIN products r ON r.id = d.t_item 
    WHERE d.t_cr =  :sel_store_id  
    AND d.t_status <>'Pending'
    AND d.t_date BETWEEN :date1 AND :date2
    AND (d.t_type IN ('RET PRCH', 'PRCH'))
    GROUP BY d.t_item ORDER BY  d.t_item ASC;
       
    ");
     
    // $stmt3->bindParam(':query', $query, PDO::PARAM_STR);
     $stmt3->bindParam(':sel_store_id', $sel_store_id, PDO::PARAM_INT);
    
    $stmt3->bindParam(':date1', $date1, PDO::PARAM_STR); 
    $stmt3->bindParam(':date2', $date2, PDO::PARAM_STR);

    $stmt3->execute();
    
    $data3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);
    
   echo '<table class="table" style="border-spacing: 10px;">
        <thead>
            <tr>
                   <th scope="col">Item</th>
                   <th scope="col">Purchases</th>
                   <th scope="col">Purchase Returns</th>
            </tr>
        </thead>';

// The loop remains the same...


// Start the loop here

        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;";
            
            // Default styles
            $itemStyle = "color: #000;"; // Default gray
            $delStyle = "color: #008080;";
            $grStyle = "color: #d50592;";
            $salesStyle = "color: #6236FF;";

            // Assign styles based on transaction type
            switch ($t_type) {
                case 'DEL':
                    $lineStyle = "font-size: 14px; color: #000;";
                    $dateStyle = "font-size: 14px; color: #008080;";
                    $statusStyle ="font-size: 14px; color: #008080;";
                    break;
                case 'GR':
                    $lineStyle = "font-size: 14px; color: #000 ;";
                    $dateStyle = "font-size: 14px; color:  #d50592 ;";
                    $statusStyle = "font-size: 14px; color:  #d50592 ;";
                    break;
                case 'REC':
                    $lineStyle = "font-size: 14px; color: #43bf10 ;";
                    $dateStyle = "font-size: 14px; color:  #43bf10 ;";
                    $statusStyle = "font-size: 14px; color:  #43bf10 ;";
                    break;
                case 'STFR':
                    $lineStyle = "font-size: 14px; color: #E9967A;";
                    $dateStyle = "font-size: 14px; color: #E9967A;";
                    $statusStyle = "font-size: 14px; color: #E9967A;";
                    break;
                
                case 'PRCH':
                    $lineStyle = "font-size: 14px; color: #00d821 ;";
                    $dateStyle = "font-size: 14px; color: #00d821 ;";
                    $statusStyle = "font-size: 14px; color: #00d821 ;";
                    break;
                case 'RET PRCH':
                    $lineStyle = "font-size: 14px; color: #6c757d;";
                    $dateStyle = "font-size: 14px; color: #6c757d;";
                    $statusStyle = "font-size: 14px; color: #6c757d;";
                    break;
                    case 'DATE':
                    $lineStyle = "font-weight: bold; font-size: 16px; color: #FFFFFF;";
                    $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #FFFFFF;" : "font-weight: bold; font-size: 16px; color: #FFFFFF;";
                    break;
                case 'SHT/EXC':
                    $lineStyle = "font-weight: bold; font-size: 16px; color: #FFFFFF;";
                    $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #FFFFFF;" : "font-weight: bold; font-size: 16px; color: #FFFFFF;";
                    break;
                case 'GRAND TOTAL':
                    $lineStyle = "font-weight: bold; font-size: 16px; color: #FFFFFF;";
                    $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 class="data-row" style="border-spacing: 10px;">
        <td style="' . $itemStyle . '"> ' . htmlspecialchars($row["p_name"] ?? '') . ' </td>
        
         <td style="' . $delStyle . '">
                         ' . number_format($row["total_PRCH"], 0, '.', ',') . '
         </td>
         
          <td style="' . $grStyle . '">
            ' . number_format($row["total_RT_PRCH"] , 0, '.', ',') . '
         </td>
        
        
    </tr>';

// Add a border for subtotal and grand total
if ($t_type === 'SHT/EXC' || $t_type === 'GRAND TOTAL') {
    echo '<tr class="grouped" style="border-bottom: 2px solid #000;">
            <td colspan="4"></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.</td></tr>';
    error_log("Database Error: " . $e->getMessage());
}
?>
