<?php
// cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/reports/cash_flow.php

require_once __DIR__ . '/../../api/_core.php';
require_once __DIR__ . '/../../config/db.php';

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

try {
    $date_from  = $_GET['date_from']  ?? date('Y-m-01');
    $date_to    = $_GET['date_to']    ?? date('Y-m-d');
    $account_id = $_GET['account_id'] ?? null;

    // ── ১. Account list (ফিল্টারের জন্য) ──────────────────────────────
    $accounts = $pdo->query(
        "SELECT id, COALESCE(account_name_bn, account_name) AS name, account_type, current_balance
         FROM cash_accounts
         WHERE is_active = 1 AND deleted_at IS NULL
         ORDER BY sort_order, id"
    )->fetchAll(PDO::FETCH_ASSOC);

    // ── ২. Summary: মোট IN / OUT ──────────────────────────────────────
    $where   = "ct.transaction_date BETWEEN :df AND :dt AND ct.is_deleted = 0";
    $params  = [':df' => $date_from, ':dt' => $date_to];

    if ($account_id) {
        $where  .= " AND ct.cash_account_id = :acc";
        $params[':acc'] = $account_id;
    }

    $summary = $pdo->prepare(
        "SELECT
            SUM(CASE WHEN ct.direction='IN'  THEN ct.amount ELSE 0 END) AS total_in,
            SUM(CASE WHEN ct.direction='OUT' THEN ct.amount ELSE 0 END) AS total_out,
            COUNT(*) AS total_rows
         FROM cash_transactions ct
         WHERE $where"
    );
    $summary->execute($params);
    $sum = $summary->fetch(PDO::FETCH_ASSOC);

    // ── ৩. Category group ভেঙে breakdown ────────────────────────────
    $breakdown = $pdo->prepare(
        "SELECT
            cc.category_group,
            cc.direction,
            cc.icon,
            COALESCE(cc.category_name_bn, cc.category_name) AS cat_name,
            cc.category_code,
            SUM(ct.amount) AS subtotal,
            COUNT(*) AS cnt
         FROM cash_transactions ct
         JOIN cash_categories cc ON cc.id = ct.category_id
         WHERE $where
         GROUP BY ct.category_id
         ORDER BY cc.direction DESC, cc.sort_order"
    );
    $breakdown->execute($params);
    $groups = $breakdown->fetchAll(PDO::FETCH_ASSOC);

    // ── ৪. Transaction list (সর্বশেষ ৩০০টি) ─────────────────────────
    $txn = $pdo->prepare(
        "SELECT
            ct.id,
            ct.voucher_no,
            ct.transaction_date,
            ct.direction,
            ct.amount,
            ct.note,
            ct.reference_type,
            COALESCE(cc.category_name_bn, cc.category_name) AS category,
            cc.icon,
            COALESCE(ca.account_name_bn, ca.account_name) AS account_name,
            COALESCE(p.name,'') AS party_name
         FROM cash_transactions ct
         JOIN cash_categories cc ON cc.id = ct.category_id
         JOIN cash_accounts   ca ON ca.id = ct.cash_account_id
         LEFT JOIN parties     p  ON p.id  = ct.party_id
         WHERE $where
         ORDER BY ct.transaction_date DESC, ct.id DESC
         LIMIT 300"
    );
    $txn->execute($params);
    $transactions = $txn->fetchAll(PDO::FETCH_ASSOC);

    // ── ৫. দৈনিক summary (chart এর জন্য) ────────────────────────────
    $daily = $pdo->prepare(
        "SELECT
            ct.transaction_date AS tdate,
            SUM(CASE WHEN ct.direction='IN'  THEN ct.amount ELSE 0 END) AS day_in,
            SUM(CASE WHEN ct.direction='OUT' THEN ct.amount ELSE 0 END) AS day_out
         FROM cash_transactions ct
         WHERE $where
         GROUP BY ct.transaction_date
         ORDER BY ct.transaction_date ASC"
    );
    $daily->execute($params);
    $daily_data = $daily->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode([
        'success'      => true,
        'date_from'    => $date_from,
        'date_to'      => $date_to,
        'accounts'     => $accounts,
        'summary'      => [
            'total_in'   => (float)($sum['total_in']  ?? 0),
            'total_out'  => (float)($sum['total_out'] ?? 0),
            'net'        => (float)(($sum['total_in'] ?? 0) - ($sum['total_out'] ?? 0)),
            'total_rows' => (int)($sum['total_rows'] ?? 0),
        ],
        'breakdown'    => $groups,
        'daily'        => $daily_data,
        'transactions' => $transactions,
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}