<?php
session_start();
require_once "../../includes/db.php";

header('Content-Type: application/json');

$coid  = $_SESSION['co_id'];

try {

    $acc_def1 = isset($_GET['acc_def1']) ? intval($_GET['acc_def1']) : 0;
    $date1 = $_GET['date1'] ?? null;

    $stmt = $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;

");

    $stmt->execute([
        ':acc_def' => $acc_def1,
        ':coid' => $coid,
        ':date1' => $date1
    ]);

    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
     $title= "font-weight: bold;  color: #9c9898;";
    // ✅ Dynamic header
    $header = '
    <tr>
        <th style="'.$title.'">Date</th>
        <th style="'.$title.'">Opening Cash</th>
        <th style="'.$title.'">Receipts</th>
        <th style="'.$title.'">Transfers In</th>
        <th style="'.$title.'">Banked</th>
        <th style="'.$title.'">Expenses</th>
        <th style="'.$title.'">Transfers Out</th>
        <th style="'.$title.'">Exp. Closing Cash</th>
        <th style="'.$title.'">Act. Closing Cash</th>
        <th style="'.$title.'">Variance</th>
    </tr>';

    // ✅ Rows
    $rows = '';

    if (!empty($data)) {

        foreach ($data as $row) {

            $isTotal = ($row['t_date'] === 'TOTAL');
            $total=$row['t_date'];

            $style = $isTotal ? 'font-weight:bold;font-size:16px;' : '';
            $varColor = ($row['variance'] < 0) ? 'color:red;' : '';
            
            // Default styles
            $itemStyle = "color: #000;"; // Default gray
            $delStyle =  "color: #000;";  //"color: #008080;";
            $delStyle2 =  "color: #000;";  //"color: #008080;";
            $grStyle = "color: #d50592;";
            $salesStyle = "color: #6236FF;";
            $total2= "  color: #000;";
           
            
            if ($variance<0) {
                $varcolor="color: #FF0000;";
            }
            
             if ($total=='TOTAL') {
                $itemStyle="font-weight: bold; font-size: 15px; ";
                $delStyle="font-weight: bold; font-size: 15px; color: #000;";
                $delStyle2 = "font-weight: bold; font-size: 16px; color: transparent;";
                $total2= "font-weight: bold; font-size: 15px; color: #9c9898;";
               if ($variance<0) {
                $varcolor="font-weight: bold; font-size: 15px; color: #FF0000;";
                }
            }

            $rows .= '<tr>
                <td style="'.$total2.'">'.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>';
        }

    } else {
        $rows = '<tr><td colspan="10">No data</td></tr>';
    }

    echo json_encode([
        "header" => $header,
        "rows" => $rows
    ]);

} catch (PDOException $e) {
    echo json_encode([
        "header" => "",
        "rows" => "<tr><td colspan='10'>Error loading data</td></tr>"
    ]);
}