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

try {
      $user_id = isset($_GET['usr_id']) ? intval($_GET['usr_id']) : 0;
    $loc_id = isset($_GET['loc_id']) ? intval($_GET['loc_id']) : 0;
    $date = isset($_GET['t_type']) && preg_match('/^\d{4}-\d{2}$/', $_GET['t_type']) 
    ? $_GET['t_type'] 
    : '0000-00';


    $stmt3 = $pdo->prepare("
                
        SELECT 
            d.t_tt_doc,d.co_id,
            DATE_FORMAT(d.t_date, '%d-%b-%y') AS t_date,
            DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
            MAX(DATE_FORMAT(d.t_date, '%a, %d %b %Y')) AS t_doc_no, 
            '' 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 AND d.co_id = u.co_id
        WHERE 
            d.t_cr = ?
            AND d.co_id = ?
            AND d.t_status = 'Confirmed'
            AND u.acc_categ = 'sales_person'
            AND d.t_type IN ('DEL', 'GR', 'REC')
            AND d.t_date >= CONCAT(?, '-01')
            AND d.t_date < DATE_ADD(CONCAT(?, '-01'), INTERVAL 1 MONTH)
        GROUP BY DATE(d.t_date)
        
        UNION ALL
        
        SELECT       
            d.t_tt_doc,d.co_id,
            DATE_FORMAT(d.t_date, '%d-%b-%y'),
            DATE_FORMAT(d.t_date, '%d %b%y'),
            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
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id AND d.co_id = u.co_id
        WHERE 
            d.t_cr = ?
            AND d.co_id = ?
            AND d.t_status = 'Confirmed'
            AND u.acc_categ = 'sales_person'
            AND d.t_type IN ('DEL', 'GR', 'REC')
            AND d.t_date >= CONCAT(?, '-01')
            AND d.t_date < DATE_ADD(CONCAT(?, '-01'), INTERVAL 1 MONTH)
        GROUP BY DATE(d.t_date), d.t_doc_no, u.acc_name, d.t_type 
        
        UNION ALL 
        
    -- Totals per date (subtotal rows) 
        SELECT 
            d.t_tt_doc,d.co_id,
            DATE_FORMAT(d.t_date, '%d-%b-%y'),
            DATE_FORMAT(d.t_date, '%d %b%y'),
            '',
            '',
            'SHT/EXC',
            SUM(COALESCE(d.t_debit, 0)) - SUM(COALESCE(d.t_credit, 0)) AS t_amount,
            2
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id AND d.co_id = u.co_id
        WHERE 
            d.t_cr = ?
            AND d.co_id = ?
            AND d.t_status = 'Confirmed'
            AND u.acc_categ = 'sales_person'
            AND d.t_type IN ('DEL', 'GR', 'REC')
            AND d.t_date >= CONCAT(?, '-01')
            AND d.t_date < DATE_ADD(CONCAT(?, '-01'), INTERVAL 1 MONTH)
        GROUP BY DATE(d.t_date)
        
        UNION ALL
        
    -- Grand total row (appears at the very bottom)
        SELECT 
            d.t_tt_doc,d.co_id,
            '',
            '',
            'GRAND TOTAL',
            '',
            'GRAND TOTAL',
             SUM(COALESCE(d.t_debit, 0)) - SUM(COALESCE(d.t_credit, 0)) AS t_amount,
            3
        FROM transactions d
        JOIN accounts u ON d.t_cr = u.id AND d.co_id = u.co_id
        WHERE 
            d.t_cr = ?
            AND d.co_id = ?
            AND d.t_status = 'Confirmed'
            AND u.acc_categ = 'sales_person'
            AND d.t_type IN ('DEL', 'GR', 'REC')
            AND d.t_date >= CONCAT(?, '-01')
            AND d.t_date < DATE_ADD(CONCAT(?, '-01'), INTERVAL 1 MONTH)
        
        ORDER BY t_date DESC, sort_order, t_doc_no;
    ");

        $stmt3->execute([
    $user_id, $loc_id, $date, $date,
    $user_id, $loc_id, $date, $date,
    $user_id, $loc_id, $date, $date,
    $user_id, $loc_id, $date, $date
]);
    $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; border-top: 0.5px solid #6c757d;";
                    $dateStyle = "font-size: 14px; color: #008080; border-top: 0.5px solid #6c757d;";
                    $statusStyle ="font-size: 14px; color: #008080; border-top: 0.5px solid #6c757d;";
                    break;
                case 'GR':
                    $lineStyle = "font-size: 14px; color: #d50592 ; border-top: 0.5px solid #6c757d;";
                    $dateStyle = "font-size: 14px; color:  #d50592 ; border-top: 0.5px solid #6c757d;";
                    $statusStyle = "font-size: 14px; color:  #d50592 ; border-top: 0.5px solid #6c757d;";
                    break;
                case 'REC':
                    $lineStyle = "font-size: 14px; color: #43bf10 ; border-top: 0.5px solid #6c757d;";
                    $dateStyle = "font-size: 14px; color:  #43bf10 ; border-top: 0.5px solid #6c757d;";
                    $statusStyle = "font-size: 14px; color:  #43bf10 ; border-top: 0.5px solid #6c757d;";
                    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: transparent;";
                    $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; border-top: 0.5px solid #6c757d;";
                    $dateStyle = "font-weight: bold; font-size: 16px; color: #000; border-top: 0.5px solid #6c757d;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545; border-top: 0.5px solid #6c757d;" : "font-weight: bold; font-size: 16px; color: #000; border-top: 0.5px solid #6c757d;";
                    break;
                case 'GRAND TOTAL':
                    $lineStyle = "font-weight: bold; font-size: 16px; color: transparent;";
                    $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 fetch-details2" data-target="#ModalBasic_4" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['t_type']) . '">
                       ' . htmlspecialchars($row["t_doc_no"] ?? '') . '
                        </a>
                    </td>
        
        <td style="' . $lineStyle . '">
         <a data-toggle="modal" class="item fetch-details2" data-target="#ModalBasic_4" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       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 fetch-details2" data-target="#ModalBasic_4" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-ord="' . htmlspecialchars($row['t_doc_no']) . '"
                       data-type="' . htmlspecialchars($row['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());
}
?>
