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

$coid  = $_SESSION['co_id'];
try {
    $query = "%" . $_GET["query"] . "%";
    $acc_def1 = isset($_GET['acc_def1']) ? intval($_GET['acc_def1']) : 0;
  
    $date1 = $_GET['date1'] ?? null;
$date2 = $_GET['date2'] ?? null;

    $stmt3 = $pdo->prepare("
    
WITH ledger AS (
    SELECT 
        d.co_id,
        d.t_cr,
        DATE(d.t_date) AS t_date,
        d.t_id,
        d.t_type,
        d.t_status,

        COALESCE(d.t_credit, 0) AS credit,
        COALESCE(d.t_debit, 0) AS debit,

        (COALESCE(d.t_credit, 0) - COALESCE(d.t_debit, 0)) AS cash,

        SUM(COALESCE(d.t_credit, 0) - COALESCE(d.t_debit, 0)) 
            OVER (
                PARTITION BY d.t_cr
                ORDER BY d.t_date, d.t_id
            ) AS cumulative_cash

    FROM transactions d
    WHERE 
        d.t_cr = :acc_def 
        AND d.co_id = :coid
        AND d.t_status IN ('Confirmed','Approved','Pending')
),

daily AS (
    SELECT 
        co_id,
        t_cr,
        t_date,

        SUM(CASE WHEN credit > 0 THEN credit ELSE 0 END) AS receipts,
        SUM(CASE WHEN t_type = 'CTFR' THEN credit ELSE 0 END) AS transfer_in,
        SUM(CASE WHEN t_type = 'BNK' THEN debit ELSE 0 END) AS banked,
        SUM(CASE WHEN t_type = 'EXP' THEN debit ELSE 0 END) AS expenses,
        SUM(CASE WHEN t_type = 'CTFR' THEN debit ELSE 0 END) AS transfer_out,
        SUM(CASE WHEN debit > 0 THEN debit ELSE 0 END) AS outflows,
        SUM(CASE WHEN t_type = 'csh_close' THEN credit ELSE 0 END) AS act_close_cash,

        SUBSTRING_INDEX(
            GROUP_CONCAT(cumulative_cash ORDER BY t_id DESC),
            ',', 1
        ) AS closing_cash

    FROM ledger
    WHERE 
        t_date >= DATE_FORMAT(:date1, '%Y-%m-01')
        AND t_date <  DATE_ADD(DATE_FORMAT(:date1, '%Y-%m-01'), INTERVAL 1 MONTH)
    GROUP BY co_id, t_cr, t_date
),

opening AS (
    SELECT 
        COALESCE(SUM(COALESCE(t_credit,0) - COALESCE(t_debit,0)),0) AS opening_balance
    FROM transactions
    WHERE 
        t_cr = :acc_def 
        AND co_id = :coid
        AND t_status IN ('Confirmed','Approved','Pending')
        AND t_date < DATE_FORMAT(:date1, '%Y-%m-01')
),

final_data AS (
    SELECT 
        d.co_id,
        d.t_cr,
        d.t_date,

        COALESCE(
            LAG(d.closing_cash) OVER (ORDER BY d.t_date),
            o.opening_balance
        ) AS opening_cash,

        d.receipts,
        d.transfer_in,
        d.banked,
        d.expenses,
        d.transfer_out,
        d.outflows,
        d.act_close_cash,
        d.closing_cash,
        (d.act_close_cash - d.closing_cash) AS variance

    FROM daily d
    CROSS JOIN opening o
)

-- ✅ MAIN + TOTAL ROW
SELECT * FROM final_data

UNION ALL

SELECT 
    NULL AS co_id,
    NULL AS t_cr,
    'TOTAL' AS t_date,

    NULL AS opening_cash,

    SUM(receipts),
    SUM(transfer_in),
    SUM(banked),
    SUM(expenses),
    SUM(transfer_out),
    NULL AS outflows,
    NULL AS act_close_cash,

    NULL AS closing_cash,

    SUM(variance)

FROM final_data

ORDER BY 
    CASE WHEN t_date = 'TOTAL' THEN 1 ELSE 0 END,
    t_date;
    ");
     
    // $stmt3->bindParam(':query', $query, PDO::PARAM_STR);
     $stmt3->bindParam(':acc_def', $acc_def1, PDO::PARAM_INT);
      $stmt3->bindParam(':coid', $coid, PDO::PARAM_INT);
    $stmt3->bindParam(':date1', $date1, PDO::PARAM_STR); 
    // $stmt3->bindParam(':acc_def1', $acc_def1, PDO::PARAM_INT);
   // $stmt3->bindParam(':date2', $date2, PDO::PARAM_STR);

    $stmt3->execute();
    
    $data3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);
    
   echo '<table class="table"  style="border-spacing: 10px;">
        <thead>
            <tr>
                   <th scope="col">Date</th>
                   <th scope="col">Opening Cash</th>
                   <th scope="col">Reciepts</th>
                   <th scope="col">Transfers In</th>
                   <th scope="col">Banked</th>
                   <th scope="col">Expenses</th>
                   <th scope="col">Transfers Out</th>
                   <th scope="col">Exp.Closing Cash</th>
                   <th scope="col">Act.Closing Cash</th>
                   <th scope="col">Variance</th>
            </tr>
        </thead>';

// The loop remains the same...


// Start the loop here

        if (!empty($data3)) {
        foreach ($data3 as $row) {
            $t_type = $row['t_type'];
            $variance=$row['variance'];
            $total=$row['t_date'];
            // Default styles
            $lineStyle = "color: #6c757d;"; // Default gray
            $dateStyle = "color: #FFFFFF;";
            $statusStyle = "color: #6c757d;";
            
            // Default styles
            $itemStyle = "color: #000;"; // Default gray
            $delStyle =  "color: #000;";  //"color: #008080;";
            $delStyle2 =  "color: #000;";  //"color: #008080;";
            $grStyle = "color: #d50592;";
            $salesStyle = "color: #6236FF;";
            
            if ($variance<0) {
                $varcolor="color: #FF0000;";
            }
            
            if ($total=='TOTAL') {
                $itemStyle="font-weight: bold; font-size: 16px; ";
                $delStyle="font-weight: bold; font-size: 16px; color: #000;";
                $delStyle2 = "font-weight: bold; font-size: 16px; color: transparent;";
               if ($variance<0) {
                $varcolor="font-weight: bold; font-size: 16px; color: #FF0000;";
                }
            }

            // Assign styles based on transaction type
            switch ($t_type) {
                case 'DEL':
                    $lineStyle = "font-size: 14px; color: #000;";
                    $dateStyle = "font-size: 14px; color: #008080;";
                    $statusStyle ="font-size: 14px; color: #008080;";
                    break;
                case 'GR':
                    $lineStyle = "font-size: 14px; color: #000 ;";
                    $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-spacing: 10px;">
        <td style="' . $delStyle . '"> ' . htmlspecialchars($row["t_date"] ?? '') . ' </td>
        
         <td style="' . $delStyle2 . '">
                         ' . number_format($row["opening_cash"], 0, '.', ',') . '
         </td>
         <td style="' . $delStyle . '">
                         ' . number_format($row["receipts"], 0, '.', ',') . '
         </td>
         
          <td style="' . $delStyle . '">
                         ' . number_format($row["transfer_in"], 0, '.', ',') . '
         </td>
         <td style="' . $delStyle . '">
                         ' . number_format($row["banked"], 0, '.', ',') . '
         </td>
         
          <td style="' . $delStyle . '">
                         ' . number_format($row["expenses"], 0, '.', ',') . '
         </td>
         
          <td style="' . $delStyle . '">
                         ' . number_format($row["transfer_out"], 0, '.', ',') . '
         </td>
         
         <td style="' . $delStyle2 . '">
                         ' . number_format($row["closing_cash"], 0, '.', ',') . '
         </td>
         
         <td style="' . $delStyle2 . '">
                         ' . number_format($row["act_close_cash"], 0, '.', ',') . '
         </td>
         
         <td style="' . $varcolor . '">
                         ' . number_format($row["variance"], 0, '.', ',') . '
         </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());
}
?>
