<?php
ini_set('display_errors', 0);
error_reporting(E_ALL);
header('Content-Type: application/json; charset=utf-8');

require_once '/home/jewellersbd/public_html/erp.jewellersbd.com/api/_core.php';

$date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) $date = date('Y-m-d');

try {

// Sales summary
$r = $pdo->prepare("
    SELECT COUNT(*) AS sale_count,
           COALESCE(SUM(grand_total),0)    AS total_sale,
           COALESCE(SUM(paid_total),0)     AS total_collected,
           COALESCE(SUM(due_amount),0)     AS total_due,
           COALESCE(SUM(exchange_value),0) AS total_exchange
    FROM sales WHERE DATE(sale_date) = ?
");
$r->execute([$date]);
$sales = $r->fetch(PDO::FETCH_ASSOC);

// Purchase summary
$r = $pdo->prepare("
    SELECT COUNT(*) AS purchase_count,
           COALESCE(SUM(total_amount),0) AS total_purchase,
           COALESCE(SUM(paid_amount),0)  AS total_paid,
           COALESCE(SUM(due_amount),0)   AS total_due
    FROM purchases WHERE purchase_date = ? AND deleted_at IS NULL
");
$r->execute([$date]);
$purchases = $r->fetch(PDO::FETCH_ASSOC);

// Items sold
$r = $pdo->prepare("
    SELECT COUNT(*) AS items_sold
    FROM sale_items si JOIN sales s ON s.id = si.sale_id
    WHERE DATE(s.sale_date) = ?
");
$r->execute([$date]);
$items = $r->fetch(PDO::FETCH_ASSOC);

// Cash per account
$r = $pdo->prepare("
    SELECT ca.id,
           COALESCE(ca.account_name_bn, ca.account_name) AS name,
           ca.account_type,
           ca.current_balance,
           COALESCE(SUM(CASE WHEN ct.direction='IN'  THEN ct.amount ELSE 0 END),0) AS day_in,
           COALESCE(SUM(CASE WHEN ct.direction='OUT' THEN ct.amount ELSE 0 END),0) AS day_out,
           COUNT(ct.id) AS txn_count
    FROM cash_accounts ca
    LEFT JOIN cash_transactions ct
           ON ct.cash_account_id = ca.id
          AND ct.transaction_date = ?
          AND ct.is_deleted = 0
          AND ct.is_reversed = 0
    WHERE ca.is_active = 1 AND ca.deleted_at IS NULL
    GROUP BY ca.id, ca.account_name_bn, ca.account_name, ca.account_type, ca.current_balance
    ORDER BY ca.sort_order, ca.id
");
$r->execute([$date]);
$accounts = $r->fetchAll(PDO::FETCH_ASSOC);

$totalIn = $totalOut = 0;
foreach ($accounts as $a) {
    $totalIn  += (float)$a['day_in'];
    $totalOut += (float)$a['day_out'];
}

// Category breakdown — icon_emoji column নাও থাকতে পারে
// SHOW COLUMNS দিয়ে check করি
$cols = $pdo->query("SHOW COLUMNS FROM cash_categories LIKE 'icon_emoji'")->fetch();
$iconCol = $cols ? "COALESCE(cc.icon_emoji,'')" : "''";

$r = $pdo->prepare("
    SELECT cc.category_name_bn AS cat_name,
           cc.direction,
           $iconCol AS icon_emoji,
           COALESCE(SUM(ct.amount),0) AS total,
           COUNT(ct.id) AS cnt
    FROM cash_transactions ct
    JOIN cash_categories cc ON cc.id = ct.category_id
    WHERE ct.transaction_date = ?
      AND ct.is_deleted = 0
      AND ct.is_reversed = 0
    GROUP BY cc.id, cc.category_name_bn, cc.direction
    ORDER BY cc.direction, total DESC
");
$r->execute([$date]);
$categories = $r->fetchAll(PDO::FETCH_ASSOC);

echo json_encode([
    'success'    => true,
    'date'       => $date,
    'sales'      => $sales,
    'purchases'  => $purchases,
    'items_sold' => (int)($items['items_sold'] ?? 0),
    'accounts'   => $accounts,
    'cash_total' => [
        'in'  => round($totalIn, 2),
        'out' => round($totalOut, 2),
        'net' => round($totalIn - $totalOut, 2),
    ],
    'categories' => $categories,
], JSON_UNESCAPED_UNICODE);

} catch (Throwable $e) {
    echo json_encode(['success'=>false,'error'=>$e->getMessage()]);
}
