<?php
// File: /public_html/erp.jewellersbd.com/api/cash/cash_transactions_list.php
// Cash Module 2.0 — Transaction list with filters + totals (read-only)
// GET params: from, to, account_id, direction(IN|OUT), q
// Uses $pdo (from config/db.php). NEW FILE.
// 2খ UPDATE: joins parties to expose party_id + party name; q also searches party name.

header("Content-Type: application/json; charset=UTF-8");

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
if (
    (!isset($_SESSION['user_id']) || empty($_SESSION['user_id'])) &&
    (!function_exists('is_logged_in') || !is_logged_in())
) {
    echo json_encode(["success" => false, "message" => "লগইন প্রয়োজন", "data" => null], JSON_UNESCAPED_UNICODE);
    exit;
}

try {
    $from       = trim((string)($_GET['from'] ?? ''));
    $to         = trim((string)($_GET['to'] ?? ''));
    $account_id = (int)($_GET['account_id'] ?? 0);
    $direction  = strtoupper(trim((string)($_GET['direction'] ?? '')));
    $q          = trim((string)($_GET['q'] ?? ''));

    // default range: current month
    if ($from === '') $from = date('Y-m-01');
    if ($to === '')   $to   = date('Y-m-d');

    $where  = ["ct.is_deleted = 0"];
    $params = [];

    $where[] = "ct.transaction_date >= ?"; $params[] = $from;
    $where[] = "ct.transaction_date <= ?"; $params[] = $to;

    if ($account_id > 0) { $where[] = "ct.cash_account_id = ?"; $params[] = $account_id; }
    if ($direction === 'IN' || $direction === 'OUT') { $where[] = "ct.direction = ?"; $params[] = $direction; }
    if ($q !== '') {
        $where[] = "(ct.note LIKE ? OR ct.voucher_no LIKE ? OR p.name LIKE ?)";
        $params[] = "%$q%"; $params[] = "%$q%"; $params[] = "%$q%";
    }

    $whereSql = implode(" AND ", $where);

    // totals for the filtered set — parties joined so the same WHERE (incl. p.name) is valid
    $tStmt = $pdo->prepare("
        SELECT
            COALESCE(SUM(CASE WHEN ct.direction='IN'  THEN ct.amount ELSE 0 END),0) AS total_in,
            COALESCE(SUM(CASE WHEN ct.direction='OUT' THEN ct.amount ELSE 0 END),0) AS total_out,
            COUNT(*) AS cnt
        FROM cash_transactions ct
        LEFT JOIN parties p ON p.id = ct.party_id
        WHERE $whereSql
    ");
    $tStmt->execute($params);
    $t = $tStmt->fetch() ?: ['total_in'=>0,'total_out'=>0,'cnt'=>0];
    $totIn  = round((float)$t['total_in'], 2);
    $totOut = round((float)$t['total_out'], 2);

    // rows
    $rStmt = $pdo->prepare("
        SELECT ct.id, ct.voucher_no, ct.transaction_date, ct.direction, ct.amount, ct.note,
               ct.reference_type, ct.is_locked, ct.party_id,
               cc.category_name, cc.category_name_bn, cc.icon,
               ca.account_name, ca.account_name_bn,
               p.name AS party_name, p.phone AS party_phone
        FROM cash_transactions ct
        LEFT JOIN cash_categories cc ON cc.id = ct.category_id
        LEFT JOIN cash_accounts   ca ON ca.id = ct.cash_account_id
        LEFT JOIN parties         p  ON p.id  = ct.party_id
        WHERE $whereSql
        ORDER BY ct.transaction_date DESC, ct.id DESC
        LIMIT 200
    ");
    $rStmt->execute($params);
    $rows = $rStmt->fetchAll();

    $list = [];
    foreach ($rows as $r) {
        $cat = (!empty($r['category_name_bn'])) ? $r['category_name_bn'] : ($r['category_name'] ?? '—');
        $acc = (!empty($r['account_name_bn'])) ? $r['account_name_bn'] : ($r['account_name'] ?? '—');
        $list[] = [
            "id"             => (int)$r['id'],
            "voucher_no"     => (string)($r['voucher_no'] ?? ''),
            "date"           => (string)$r['transaction_date'],
            "direction"      => (string)$r['direction'],
            "amount"         => round((float)$r['amount'], 2),
            "category"       => (string)$cat,
            "icon"           => (string)($r['icon'] ?? ''),
            "account"        => (string)$acc,
            "note"           => (string)($r['note'] ?? ''),
            "party_id"       => $r['party_id'] !== null ? (int)$r['party_id'] : 0,
            "party"          => (string)($r['party_name'] ?? ''),
            "reference_type" => (string)($r['reference_type'] ?? 'MANUAL'),
            "is_locked"      => (int)$r['is_locked'],
            "can_delete"     => ((string)($r['reference_type'] ?? 'MANUAL') === 'MANUAL' && (int)$r['is_locked'] === 0) ? 1 : 0
        ];
    }

    echo json_encode([
        "success" => true,
        "message" => "লেনদেন লোড হয়েছে",
        "data"    => [
            "filters" => ["from"=>$from,"to"=>$to,"account_id"=>$account_id,"direction"=>$direction,"q"=>$q],
            "totals"  => ["in"=>$totIn,"out"=>$totOut,"net"=>round($totIn-$totOut,2),"count"=>(int)$t['cnt']],
            "rows"    => $list,
            "limited" => (int)$t['cnt'] > 200
        ]
    ], JSON_UNESCAPED_UNICODE);

} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(["success" => false, "message" => "সার্ভার সমস্যা: " . $e->getMessage(), "data" => null], JSON_UNESCAPED_UNICODE);
}
exit;
?>