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

try {
   $storeid = isset($_GET['storeid']) ? intval($_GET['storeid']) : 0;

    $stmt3 = $pdo->prepare("
      SELECT
    d.t_item,
    r.p_name,
    ( SUM(CASE WHEN (d.t_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'GR' THEN d.t_qty ELSE 0 END)+SUM(CASE WHEN (d.t_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'PRCH' THEN d.t_qty ELSE 0 END)   )- 
            ( SUM(CASE WHEN (d.t_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'DEL' THEN d.t_qty ELSE 0 END)+SUM(CASE WHEN (d.t_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'RET PRCH' THEN d.t_qty ELSE 0 END)  )      AS opening_stock,
    SUM(CASE WHEN (d.t_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'DEL' THEN d.t_qty ELSE 0 END) - 
            SUM(CASE WHEN (d.t_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'GR' THEN d.t_qty ELSE 0 END) 
            AS total_SALES_month,
     SUM(CASE WHEN (d.t_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND d.t_type = 'RET PRCH' THEN d.t_qty ELSE 0 END) AS total_RT_PRCH,
    SUM(CASE WHEN (d.t_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') ) AND 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 = ? 
     AND d.t_status IN ('Confirmed','Approved')
    AND r.sale_count = 1  
    AND d.t_type IN ('DEL', 'GR', 'RET PRCH', 'PRCH')
GROUP BY d.t_item, r.p_name
ORDER BY d.t_item ASC;
    ");

    $stmt3->execute([$storeid]);
    $data3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);

    if (!empty($data3)) {
        foreach ($data3 as $row) {
            
             
            
             if ($row['t_type'] === 'DEL')
         {
             $style1 =  "text-danger" ;
             
             if ($row['t_status'] === 'Pending')
          {
           $style2 =  "text-secondary" ;   
          }
            if ($row['t_status'] === 'Confirmed')
          {
           $style2 =  "text-danger" ;   
          }
         }
         
            if ($row['t_type'] === 'GR')
         {
             $style1 =  "text-primary" ;
             
             if ($row['t_status'] === 'Pending')
          {
           $style2 =  "text-secondary" ;   
          }
            if ($row['t_status'] === 'Confirmed')
          {
           $style2 =  "text-primary" ;   
          }
         }
         
         $style1 =  "text-dark" ;   
          $style2 =  "text-secondary" ; 
           $style3 =  "text-success" ; 
            $style4 =  "text-primary" ; 

                       echo '
            <tr>
                
                <td class="' . $style1 . '">
                 <a data-toggle="modal" class="item " data-target="#" 
                       data-loc_id="' . htmlspecialchars($row["t_tt_doc"]) . '" 
                       data-usr="' . htmlspecialchars($store_id) . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['t_type']) . '">
                        ' . htmlspecialchars($row["p_name"]) . '
                        </a>
                        </td>
                <td class="' . $style2 . '">
                    <a data-toggle="modal" class="item " data-target="#" 
                       data-loc_id="' . htmlspecialchars($row["t_tt_doc"]) . '" 
                       data-usr="' . htmlspecialchars($store_id) . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['t_type']) . '">
                       ' . htmlspecialchars($row["opening_stock"]) . '
                       </a>
                       </td>
                <td class="' . $style3 . '">
                    <a data-toggle="modal" class="item " data-target="#" 
                       data-loc_id="' . htmlspecialchars($row["t_tt_doc"]) . '" 
                       data-usr="' . htmlspecialchars($store_id) . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['t_type']) . '">
                        ' . number_format($row["total_SALES_month"], 0, '.', ',') . '
                    </a>
                </td>
                
                <td class="' . $style4 . '">
                    <a data-toggle="modal" class="item " data-target="#" 
                       data-loc_id="' . htmlspecialchars($row["t_tt_doc"]) . '" 
                       data-usr="' . htmlspecialchars($store_id) . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['t_type']) . '">
                        ' . number_format($row["total_PRCH"], 0, '.', ',') . '
                    </a>
                </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()); // Log error for debugging
}
?>
