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

try {
     $sel_store_id = isset($_GET['storeid']) ? intval($_GET['storeid']) : 0;
    $user_id = isset($_GET['user3_id']) ? intval($_GET['user3_id']) : 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';
$trans_type = isset($_GET['trans_type']) ? trim($_GET['trans_type']) : '';

$stmt3 = $pdo->prepare("
    SELECT 
        MAX(d.co_id) AS co_id,
        MAX(d.t_tt_doc) AS t_tt_doc,
        MAX(d.t_ct_det) AS t_ct_det,
        MAX(e.acc_name) AS acc_name,
        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 first_name, 
        'DATE' AS t_type, 
        NULL AS t_amount,
        d.t_date AS raw_date,
        0 AS sort_order
    FROM transactions d
    JOIN accounts u ON d.t_cr = u.id 
    JOIN accounts e ON d.t_ct_det = e.id
    WHERE d.t_cr = :sel_store_id 
        AND d.t_status <> 'Deleted' 
        AND (:trans_type = '' OR d.t_type = :trans_type)
        AND d.t_date BETWEEN :date1 AND :date2
    GROUP BY d.t_date 

    UNION ALL

    SELECT  
        d.co_id, d.t_tt_doc, d.t_ct_det, e.acc_name,
        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, 
        SUBSTRING_INDEX(e.acc_name, ' ', 1) AS first_name, 
        d.t_type, 
        SUM(COALESCE(d.t_debit, 0) + COALESCE(d.t_credit, 0)) AS t_amount,
        d.t_date AS raw_date,
        1 AS sort_order
    FROM transactions d
    JOIN accounts u ON d.t_cr = u.id 
    JOIN accounts e ON d.t_ct_det = e.id
    WHERE d.t_cr = :sel_store_id 
        AND d.t_status <> 'Deleted' 
        AND (:trans_type = '' OR d.t_type = :trans_type)
        AND d.t_date BETWEEN :date1 AND :date2
    GROUP BY d.t_date, d.t_doc_no, e.acc_name, d.t_type 

    ORDER BY raw_date DESC, sort_order, t_doc_no
");

$stmt3->bindParam(':sel_store_id', $sel_store_id, PDO::PARAM_INT);
$stmt3->bindParam(':trans_type', $trans_type, PDO::PARAM_STR);
$stmt3->bindParam(':date1', $date1, PDO::PARAM_STR); 
$stmt3->bindParam(':date2', $date2, PDO::PARAM_STR);

$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: #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: none;">
          <td style="' . $dateStyle . '">
        <a data-toggle="modal" class="item inv-row-1" 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["first_name"] ?? '') . '
        
             </a>
         </td>
         
        <td style="' . $dateStyle . '">
        <a data-toggle="modal" class="item inv-row-1" 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-1" 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-1" 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());
}
?>
