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

try {
    $coid   = $_SESSION['co_id'];
    $uid  =$_SESSION['user_id'];
    $query = "%" . $_GET["query"] . "%";
    $storeid = isset($_GET['storeid']) ? intval($_GET['storeid']) : 0;
   // $trans_type = isset($_GET['trans_type']) ? trim($_GET['trans_type']) : '';
    $date1 = isset($_GET['date1']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date1']) 
    ? $_GET['date1'] 
    : '0000-00-00';

   $date2 = isset($_GET['date2']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date2']) 
    ? $_GET['date2'] 
    : '0000-00-00';


    $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, e.acc_name, u.acc_name ,u.id,
    SUM(CASE WHEN d.t_type = 'DEL' THEN d.t_qty ELSE 0 END) AS total_DEL,
    SUM(CASE WHEN d.t_type = 'GR' THEN d.t_qty ELSE 0 END) AS total_GR,
    SUM(CASE WHEN d.t_type = 'PRCH' THEN d.t_qty ELSE 0 END) AS total_PRCH,
    (SUM(CASE WHEN d.t_type = 'DEL' THEN d.t_qty ELSE 0 END) - SUM(CASE WHEN d.t_type = 'GR' THEN d.t_qty ELSE 0 END)) AS total_SALES
    FROM transactions d
    JOIN accounts u ON d.t_cr=u.id
    JOIN accounts e ON d.t_ct_det=e.id
    JOIN products r ON r.id = d.t_item 

        WHERE 
-- ✅ Permission logic (safe version)
    EXISTS (
        SELECT 1
        FROM permissions p
        WHERE d.co_id  = :coid
          AND p.user_id = :user_id       
        AND p.pm_acc_lvl IN (0,1,2,4,5,6,7)
          AND (
                p.pm_acc_id = u.id 
                OR (
                    p.pm_acc_categ = u.acc_categ 
                    AND (p.pm_acc_id IS NULL OR p.pm_acc_id = '')
                )
          )
    )
    
   AND d.co_id = :coid1 
    AND (e.acc_name LIKE :query ) 
    AND d.t_copy='O' 
    AND d.t_status IN ('Approved', 'Confirmed')
    AND (u.acc_name LIKE '%' ) 
    AND (d.t_date >= :date1 ) AND (d.t_date <= :date2 ) AND (d.t_tt_doc LIKE '%' ) -- No conversion needed here
    AND (d.t_type IN ('DEL', 'GR'))
    GROUP BY d.t_item ORDER BY  d.t_item ASC;
       
    ");
     
     $stmt3->bindParam(':query', $query, PDO::PARAM_STR);
    /// $stmt3->bindParam(':storeid', $storeid, PDO::PARAM_INT);
     
     $stmt3->bindParam(':user_id', $uid, PDO::PARAM_INT);
     $stmt3->bindParam(':coid', $coid, PDO::PARAM_INT);
     $stmt3->bindParam(':coid1', $coid, 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">Del</th>
                   <th scope="col">GRs</th>
                   <th scope="col">Total Sales</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_DEL"], 0, '.', ',') . '
         </td>
         
          <td style="' . $grStyle . '">
            ' . number_format($row["total_GR"] , 0, '.', ',') . '
         </td>
        
        <td style="text-align: right; ' . $salesStyle . '">
            ' . number_format($row["total_SALES"], 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());
}
?>
