<?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}-\d{2}$/', $_GET['t_type']) 
    ? $_GET['t_type'] 
    : '0000-00-00';

    $stmt3 = $pdo->prepare("
    
    SELECT 
    d.t_cr,d.co_id, u.acc_name AS crname,
    DATE_FORMAT(d.t_date, '%Y-%m') AS month_key,
    DATE_FORMAT(d.t_date, '%b-%Y') AS t_date,
    DATE_FORMAT(d.t_date, '%b %Y') AS M_date,
    MAX(DATE_FORMAT(d.t_date, ' %b %Y')) AS t_doc_no, 
    '' AS acc_name, 
    'MONTHLY' AS t_type, 
    SUM(COALESCE(d.t_debit, 0)) - SUM(COALESCE(d.t_credit, 0)) AS t_amount,
    2 AS sort_order,
    MIN(d.t_date) AS original_date
FROM transactions d
JOIN accounts u ON d.t_cr = u.id AND d.co_id = u.co_id
JOIN users x ON x.id = u.user_id
WHERE 
    d.t_cr = ?
    AND d.co_id = ?
    AND d.t_status IN ('Confirmed', 'Approved')
    AND u.acc_categ IN ('sales_person', 'customer')
    AND d.t_type IN ('DEL', 'GR', 'REC', 'CS INV', 'STK INV')
     AND d.t_date < DATE_SUB(NOW(), INTERVAL 9 HOUR)
GROUP BY month_key

-- Yearly Subtotal Section
UNION ALL

SELECT 
    d.t_cr,d.co_id, u.acc_name AS crname,
    DATE_FORMAT(d.t_date, '%Y') AS year_key,
    DATE_FORMAT(d.t_date, '%Y') AS t_date, 
    '' AS M_date,
    'SUBTOTAL ' || DATE_FORMAT(d.t_date, '%Y') AS t_doc_no, 
    DATE_FORMAT(d.t_date, '%Y') AS acc_name, 
    '-YEARLY TOTAL' AS t_type, 
    SUM(COALESCE(d.t_debit, 0)) - SUM(COALESCE(d.t_credit, 0)) AS t_amount,
    5 AS sort_order,
    NULL AS original_date
FROM transactions d
JOIN accounts u ON d.t_cr = u.id AND d.co_id = u.co_id
JOIN users x ON x.id = u.user_id
WHERE 
    d.t_cr = ?
    AND d.co_id = ?
    AND d.t_status IN ('Confirmed', 'Approved') 
    AND u.acc_categ IN ('sales_person', 'customer')
    AND d.t_type IN ('DEL', 'GR', 'REC', 'CS INV', 'STK INV')
     AND d.t_date < DATE_SUB(NOW(), INTERVAL 9 HOUR)
GROUP BY year_key

-- Grand Total Section
UNION ALL

SELECT 
    d.t_cr,d.co_id, u.acc_name AS crname,
    NULL AS month_key,
    '' AS t_date, 
    '' AS M_date,
    'GRAND TOTAL' AS t_doc_no, 
    '' AS acc_name, 
    'GRAND TOTAL' AS t_type, 
    SUM(COALESCE(d.t_debit, 0)) - SUM(COALESCE(d.t_credit, 0)) AS t_amount,
    6 AS sort_order,
    NULL AS original_date
FROM transactions d
JOIN accounts u ON d.t_cr = u.id AND d.co_id = u.co_id
JOIN users x ON x.id = u.user_id
WHERE 
    d.t_cr = ?
    AND d.co_id = ?
    AND d.t_status IN ('Confirmed', 'Approved')
    AND u.acc_categ IN ('sales_person', 'customer')
    AND d.t_type IN ('DEL', 'GR', 'REC', 'CS INV', 'STK INV')
     AND d.t_date < DATE_SUB(NOW(), INTERVAL 9 HOUR)
ORDER BY 
    STR_TO_DATE(CONCAT(month_key, '-01'), '%Y-%m-%d') DESC, 
    sort_order, 
    t_doc_no
    ");

    $stmt3->execute([$user_id,$loc_id, $user_id,$loc_id, $user_id,$loc_id]);
    $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: #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; 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 'MONTHLY':
                    $lineStyle = "font-size: 16px; color: transparent; border-bottom: 0.5px solid #f2f3f4 ;";
                    $dateStyle = "font-size: 16px; color: #000; border-bottom: 0.5px solid #f2f3f4;";
                    $statusStyle = ($row['t_amount'] < 0) ? " font-size: 16px; color: #dc3545; border-bottom: 0.5px solid #f2f3f4;" : " font-size: 16px; color: #000; border-bottom: 0.5px solid #f2f3f4;";
                    break;
                case '-YEARLY TOTAL':
                    $lineStyle = "font-weight: bold; font-size: 16px; color: #000; border-bottom: 1px solid #6c757d;";
                    $dateStyle = "font-weight: bold; font-size: 16px; color: #FFFFFF; border-top: 1px solid #6c757d;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 16px; color: #dc3545; border-bottom: 1px solid #6c757d;" : "font-weight: bold; font-size: 16px; color: #000; border-bottom: 1px solid #6c757d;";
                    break;
                case 'GRAND TOTAL':
                    $lineStyle = "font-weight: bold; font-size: 20px; color: transparent;";
                    $dateStyle = "font-weight: bold; font-size: 20px; color: #000;";
                    $statusStyle = ($row['t_amount'] < 0) ? "font-weight: bold; font-size: 20px; color: #dc3545;" : "font-weight: bold; font-size: 20px; color: #000;";
                    break;
            }

            echo '<tr class="data-row" style="border: none;">
        <td style="' . $dateStyle . '">
         <a data-toggle="modal" class="item fetch-cr_unclbl_monthly" data-target="#ModalBasic_3" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars($row['t_cr']) . '"
                       data-type="' . htmlspecialchars($row['month_key']) . '" data-accname="' . htmlspecialchars($row["crname"]) . '">
                       ' . htmlspecialchars($row["t_doc_no"] ?? '') . '
                        </a>
                    </td>
         <td style="' . $lineStyle . '">
         <a class="item " 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars($row['t_cr']) . '"
                       data-type="' . htmlspecialchars($row['month_key']) . '"
                       data-accname="' . htmlspecialchars($row["crname"]) . '">
                       ' . htmlspecialchars($row["acc_name"] ?? '') . '
         </a>
         </td>
        
        <td style="' . $lineStyle . '">
         <a class="item "  
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars($row['t_cr']) . '"
                       data-type="' . htmlspecialchars($row['month_key']) . '"data-accname="' . htmlspecialchars($row["crname"]) . '">
                       ' . htmlspecialchars($t_type) . '
         </a>
         </td>
        <td style="text-align: right; ' . $statusStyle . '">
         <a data-toggle="modal" class="item fetch-cr_unclbl_monthly" data-target="#ModalBasic_3" 
                       data-loc_id="' . htmlspecialchars($row["co_id"]) . '" 
                       data-usr="' . htmlspecialchars($row['t_cr']) . '"
                       data-type="' . htmlspecialchars($row['month_key']) . '"data-accname="' . htmlspecialchars($row["crname"]) . '">
                       ' . 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());
}
?>
