<?php
session_start();
ini_set('session.cookie_secure', 1);
require_once "../../includes/db.php";
header('Content-Type: application/json');
$user_id = $_SESSION['user_id'] ?? 0;

try {
     $sel_store_id = isset($_GET['storeid']) ? intval($_GET['storeid']) : 0;
    //$user_id = '46';
    //$trans_type = isset($_GET['trans_type']) ? trim($_GET['trans_type']) : '';
    $date1 = isset($_GET['date3']) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['date3']) 
    ? $_GET['date3'] 
    : '0000-00-00';

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

$stmt3 = $pdo->prepare("
SELECT 
            d.co_id, d.t_tt_doc, d.t_ct_det,
             DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
            DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
            '' AS  t_doc_no, 
            MAX(DATE_FORMAT(d.t_date, '%a, %d %b %Y') ) AS acc_name, 
            'DATE' AS t_type, 
            NULL AS t_amount,
            0 AS sort_order
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id 
        JOIN permissions p ON (u.id = p.pm_acc_id OR u.acc_categ=p.pm_acc_categ  ) AND p.user_id = :user_id
        WHERE 
             u.acc_categ IN ('expense')
            AND d.t_type IN ('exp')
             AND p.pm_acc_lvl IN (1,3,4,5,6,7,8)
           AND (d.t_date >= :date1 ) AND (d.t_date <= :date2 ) 
        GROUP BY d.t_date 

        UNION ALL
SELECT      d.co_id, d.t_tt_doc, d.t_ct_det,
            DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
            DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
            d.t_doc_no, 
            u.acc_name, 
            d.t_type, 
            SUM(COALESCE(d.t_debit, 0)) + SUM(COALESCE(d.t_credit, 0)) AS t_amount,
            1 AS sort_order
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id 
        JOIN permissions p ON (u.id = p.pm_acc_id OR u.acc_categ=p.pm_acc_categ  ) AND p.user_id = :user_id1
        WHERE 
             u.acc_categ IN ('expense')
            AND d.t_type IN ('exp')
             AND p.pm_acc_lvl IN (1,3,4,5,6,7,8)
             AND (d.t_date >= :date12 ) AND (d.t_date <= :date22 ) 
        GROUP BY d.t_date, d.t_doc_no, u.acc_name, d.t_type 

        UNION ALL 

        -- Totals per date (subtotal rows)
        SELECT 
         d.co_id, d.t_tt_doc, d.t_ct_det,
            DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
            DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
            '' AS t_doc_no, 
            '' AS acc_name, 
            'Total' AS t_type, 
             SUM(COALESCE(d.t_credit, 0)) AS t_amount,
            2 AS sort_order
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id 
        JOIN permissions p ON (u.id = p.pm_acc_id OR u.acc_categ=p.pm_acc_categ  ) AND p.user_id = :user_id2
        WHERE 
             u.acc_categ IN ('expense')
            AND d.t_type IN ('exp')
             AND p.pm_acc_lvl IN (1,3,4,5,6,7,8)
           AND (d.t_date >= :date13 ) AND (d.t_date <= :date23 )  
        GROUP BY d.t_date 
        ORDER BY t_date DESC, sort_order, t_doc_no;
");

$stmt3->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt3->bindParam(':user_id1', $user_id, PDO::PARAM_INT);
$stmt3->bindParam(':user_id2', $user_id, PDO::PARAM_INT);

$stmt3->bindParam(':date1', $date1);
$stmt3->bindParam(':date2', $date2);

$stmt3->bindParam(':date12', $date1);
$stmt3->bindParam(':date22', $date2);

$stmt3->bindParam(':date13', $date1);
$stmt3->bindParam(':date23', $date2);

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

        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 = "font-size: 14px; color: #008080;";
                    $dateStyle = "font-size: 14px; color: #008080;";
                    $statusStyle ="font-size: 14px; color: #008080;";
                    break;
                case 'GR':
                    $lineStyle = "font-size: 14px; color: #d50592 ;";
                    $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: #000;";
                    $dateStyle = "font-weight: bold; font-size: 16px; color: #000;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #000;" : "font-weight: bold; font-size: 16px; color: #000;";
                    break;
                case 'Total':
                    $lineStyle = "font-weight: bold; font-size: 15px; color: #000;";
                    $dateStyle = "font-weight: bold; font-size: 15px; color: #000;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 15px; color: #000;" : "font-weight: bold; font-size: 15px; color: #000;";
                    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: none;">
          <td style="' . $dateStyle . '">
        <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                 ' . htmlspecialchars($row["acc_name"] ?? '') . '
        
             </a>
         </td>
         
        <td style="' . $dateStyle . '">
        <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                 ' . htmlspecialchars($row["t_doc_no"] ?? '') . '
        
             </a>
         </td>
        
        <td style="' . $lineStyle . '">
         <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['t_type']) . '">
                                      ' . htmlspecialchars($t_type) . '
         </a></td>
        
        <td style="text-align: right; ' . $statusStyle . '">
                    <a data-toggle="modal" class="item inv-row-2" data-target="#ModalBasic" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars("t_ct_det") . '"
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($t_type) . '">
                        ' . number_format($row["t_amount"], 0, '.', ',') . '
                    </a>
                </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());
}
?>
