<?php
require_once __DIR__ . '/../core/init.php';

header('Content-Type: application/json; charset=utf-8');

$draw = intval($_POST['draw'] ?? 0);
$start_row = intval($_POST['start'] ?? 0);
$length = intval($_POST['length'] ?? 10);

$user_id = intval($_POST['user_id'] ?? 0);
$store_id = intval($_POST['store_id'] ?? 0);
$start_date = $_POST['start_date'] ?? null;
$end_date = $_POST['end_date'] ?? null;

$whereClauses = [];
$params = [];
$types = '';
if ($user_id > 0) { $whereClauses[] = "user_init = ?"; $params[] = $user_id; $types .= 'i'; }
if ($store_id > 0) { $whereClauses[] = "store_id = ?"; $params[] = $store_id; $types .= 'i'; }

$opening_balance = 0;
if ($start_date) {
    $receipt_before_sql = "SELECT SUM(total_money) FROM cms_receipt WHERE deleted = 0 AND receipt_date < ?";
    $stmt_receipt = $conn->prepare($receipt_before_sql);
    $stmt_receipt->bind_param('s', $start_date);
    $stmt_receipt->execute();
    $receipt_total_before = $stmt_receipt->get_result()->fetch_row()[0] ?? 0;
    $stmt_receipt->close();
    
    $payment_before_sql = "SELECT SUM(total_money) FROM cms_payment WHERE deleted = 0 AND payment_date < ?";
    $stmt_payment = $conn->prepare($payment_before_sql);
    $stmt_payment->bind_param('s', $start_date);
    $stmt_payment->execute();
    $payment_total_before = $stmt_payment->get_result()->fetch_row()[0] ?? 0;
    $stmt_payment->close();

    $opening_balance = $receipt_total_before - $payment_total_before;
}

$union_sql = "
    (SELECT ID, receipt_code as code, receipt_date as transaction_date, notes, user_init, store_id, total_money as thu, 0 as chi FROM cms_receipt WHERE deleted = 0)
    UNION ALL
    (SELECT ID, payment_code as code, payment_date as transaction_date, notes, user_init, store_id, 0 as thu, total_money as chi FROM cms_payment WHERE deleted = 0)
";

$fullQuery = "SELECT t.*, u.display_name FROM ({$union_sql}) as t LEFT JOIN cms_users u ON t.user_init = u.ID";

$date_params = [];
$date_types = '';
if ($start_date && $end_date) {
    $whereClauses[] = "transaction_date BETWEEN ? AND ?";
    $date_params[] = $start_date . ' 00:00:00';
    $date_params[] = $end_date . ' 23:59:59';
    $date_types .= 'ss';
}
$whereSql = !empty($whereClauses) ? ' WHERE ' . implode(' AND ', $whereClauses) : '';
$final_params = array_merge($params, $date_params);
$final_types = $types . $date_types;

$summaryQuery = "SELECT COUNT(*) as total_vouchers, SUM(thu) as total_thu, SUM(chi) as total_chi FROM ({$fullQuery}) as final_query {$whereSql}";
$summaryStmt = $conn->prepare($summaryQuery);
if($final_types) $summaryStmt->bind_param($final_types, ...$final_params);
$summaryStmt->execute();
$summary = $summaryStmt->get_result()->fetch_assoc();
$summaryStmt->close();

$total_thu = $summary['total_thu'] ?? 0;
$total_chi = $summary['total_chi'] ?? 0;
$closing_balance = $opening_balance + $total_thu - $total_chi;
$recordsFiltered = $summary['total_vouchers'] ?? 0;

$dataStmt = $conn->prepare("{$fullQuery} {$whereSql} ORDER BY transaction_date ASC, ID ASC LIMIT ?, ?");
$current_params = array_merge($final_params, [$start_row, $length]);
$current_types = $final_types . 'ii';
$dataStmt->bind_param($current_types, ...$current_params);
$dataStmt->execute();
$transactions = $dataStmt->get_result()->fetch_all(MYSQLI_ASSOC);
$dataStmt->close();

$running_balance = $opening_balance;
$data = [];
foreach($transactions as $row) {
    $running_balance += $row['thu'] - $row['chi'];
    $row['ton'] = $running_balance;
    $data[] = $row;
}

// ----- SỬA LỖI TẠI ĐÂY -----
// Thêm "WHERE deleted = 0" vào câu lệnh đếm tổng số phiếu
$totalRecordsQuery = $conn->query("SELECT (SELECT COUNT(*) FROM cms_receipt WHERE deleted = 0) + (SELECT COUNT(*) FROM cms_payment WHERE deleted = 0)");
$recordsTotal = $totalRecordsQuery->fetch_row()[0];

echo json_encode([
    "draw" => $draw,
    "recordsTotal" => intval($recordsTotal),
    "recordsFiltered" => intval($recordsFiltered),
    "data" => $data,
    "summary" => [
        "openingBalance" => $opening_balance,
        "totalThu" => $total_thu,
        "totalChi" => $total_chi,
        "closingBalance" => $closing_balance,
        "totalVouchers" => $recordsFiltered
    ]
]);
?>