<?php
/**
 * Dashboard Metrics API
 * cPanel path: /home/jewellersbd/public_html/erp.jewellersbd.com/api/reports/dashboard_metrics.php
 *
 * GET params:
 *   period = today | yesterday | this_week | this_month | this_year | all_time | custom
 *   from   = YYYY-MM-DD  (only for custom)
 *   to     = YYYY-MM-DD  (only for custom)
 */

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

gs_ensure_runtime_compatibility();
gs_require_login();

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

// ── Period → date range ──────────────────────────────────
$period   = $_GET['period'] ?? 'today';
$from_raw = $_GET['from']   ?? date('Y-m-d');
$to_raw   = $_GET['to']     ?? date('Y-m-d');

switch ($period) {
    case 'today':
        $from = $to = date('Y-m-d');
        break;
    case 'yesterday':
        $from = $to = date('Y-m-d', strtotime('-1 day'));
        break;
    case 'this_week':
        $from = date('Y-m-d', strtotime('monday this week'));
        $to   = date('Y-m-d');
        break;
    case 'this_month':
        $from = date('Y-m-01');
        $to   = date('Y-m-d');
        break;
    case 'this_year':
        $from = date('Y-01-01');
        $to   = date('Y-m-d');
        break;
    case 'all_time':
        $from = '2020-01-01';
        $to   = date('Y-m-d');
        break;
    case 'custom':
        $from = preg_match('/^\d{4}-\d{2}-\d{2}$/', $from_raw) ? $from_raw : date('Y-m-01');
        $to   = preg_match('/^\d{4}-\d{2}-\d{2}$/', $to_raw)   ? $to_raw   : date('Y-m-d');
        break;
    default:
        $from = $to = date('Y-m-d');
}

$d = []; // output

// ── 1. Cash IN / OUT ────────────────────────────────────
try {
    $s = $pdo->prepare("
        SELECT
            COALESCE(SUM(CASE WHEN direction='IN'  THEN amount ELSE 0 END), 0) AS cash_in,
            COALESCE(SUM(CASE WHEN direction='OUT' THEN amount ELSE 0 END), 0) AS cash_out
        FROM cash_transactions
        WHERE is_deleted = 0
          AND DATE(created_at) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $d['cash_in']  = (float)$r['cash_in'];
    $d['cash_out'] = (float)$r['cash_out'];
} catch (Throwable $e) {
    $d['cash_in'] = $d['cash_out'] = 0;
}

// ── 2. Sales (sales + barcode_sales combined) ───────────
$sale_count = 0; $sale_total = 0;
try {
    $s = $pdo->prepare("
        SELECT COUNT(*) AS cnt, COALESCE(SUM(total_amount), 0) AS tot
        FROM sales
        WHERE DATE(sale_date) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $sale_count += (int)$r['cnt'];
    $sale_total += (float)$r['tot'];
} catch (Throwable $e) {}
try {
    $s = $pdo->prepare("
        SELECT COUNT(*) AS cnt, COALESCE(SUM(grand_total), 0) AS tot
        FROM barcode_sales
        WHERE DATE(sale_date) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $sale_count += (int)$r['cnt'];
    $sale_total += (float)$r['tot'];
} catch (Throwable $e) {}
$d['sale_count'] = $sale_count;
$d['sale_total'] = $sale_total;

// ── 3. Sales due / paid ─────────────────────────────────
$sale_due = 0; $sale_paid = 0;
try {
    $s = $pdo->prepare("
        SELECT
            COALESCE(SUM(due_amount),  0) AS due,
            COALESCE(SUM(paid_amount), 0) AS paid
        FROM sales
        WHERE DATE(sale_date) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $sale_due  += (float)$r['due'];
    $sale_paid += (float)$r['paid'];
} catch (Throwable $e) {}
try {
    $s = $pdo->prepare("
        SELECT
            COALESCE(SUM(due_amount),  0) AS due,
            COALESCE(SUM(paid_amount), 0) AS paid
        FROM barcode_sales
        WHERE DATE(sale_date) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $sale_due  += (float)$r['due'];
    $sale_paid += (float)$r['paid'];
} catch (Throwable $e) {}
$d['sale_due']  = $sale_due;
$d['sale_paid'] = $sale_paid;

// ── 4. Purchase ─────────────────────────────────────────
$pur_count = 0; $pur_total = 0; $pur_due = 0; $pur_paid = 0;
try {
    $s = $pdo->prepare("
        SELECT COUNT(*) AS cnt,
               COALESCE(SUM(total_amount), 0) AS tot,
               COALESCE(SUM(due_amount),   0) AS due,
               COALESCE(SUM(paid_amount),  0) AS paid
        FROM purchases
        WHERE DATE(purchase_date) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $pur_count += (int)$r['cnt'];
    $pur_total += (float)$r['tot'];
    $pur_due   += (float)$r['due'];
    $pur_paid  += (float)$r['paid'];
} catch (Throwable $e) {}
try {
    $s = $pdo->prepare("
        SELECT COUNT(*) AS cnt, COALESCE(SUM(total_value), 0) AS tot
        FROM new_stock
        WHERE DATE(stock_date) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $pur_count += (int)$r['cnt'];
    $pur_total += (float)$r['tot'];
} catch (Throwable $e) {}
$d['purchase_count'] = $pur_count;
$d['purchase_total'] = $pur_total;
$d['purchase_due']   = $pur_due;
$d['purchase_paid']  = $pur_paid;

// ── 5. Pledges (active = current state) ─────────────────
try {
    $s = $pdo->query("
        SELECT COUNT(*) AS cnt,
               COALESCE(SUM(outstanding_principal), 0) AS total
        FROM pawn_pledges
        WHERE status = 'active'
    ");
    $r = $s->fetch(PDO::FETCH_ASSOC);
    $d['pledge_active'] = (int)$r['cnt'];
    $d['pledge_amount'] = (float)$r['total'];
} catch (Throwable $e) {
    $d['pledge_active'] = $d['pledge_amount'] = 0;
}

// Pledges returned/closed in selected period
try {
    $s = $pdo->prepare("
        SELECT COUNT(*) FROM pawn_pledges
        WHERE status IN ('returned','closed')
          AND DATE(updated_at) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $d['pledge_returned'] = (int)$s->fetchColumn();
} catch (Throwable $e) {
    $d['pledge_returned'] = 0;
}

// Pledge locker-rent collected in period
try {
    $s = $pdo->prepare("
        SELECT COALESCE(SUM(amount), 0)
        FROM cash_transactions
        WHERE is_deleted = 0
          AND category_code = 'PLEDGE_INTEREST'
          AND DATE(created_at) BETWEEN ? AND ?
    ");
    $s->execute([$from, $to]);
    $d['pledge_rent_collected'] = (float)$s->fetchColumn();
} catch (Throwable $e) {
    $d['pledge_rent_collected'] = 0;
}

// ── 6. Pledge metal weight (active) ─────────────────────
try {
    $s = $pdo->query("
        SELECT COALESCE(SUM(pi.gross_weight), 0) AS gw
        FROM pledge_items pi
        JOIN pawn_pledges pp ON pi.pledge_id = pp.id
        WHERE pp.status = 'active'
    ");
    $gw = (float)$s->fetchColumn();
    $vori = $gw / 11.664;
    $d['pledge_metal_gram'] = round($gw, 3);
    $d['pledge_metal_vori'] = round($vori, 3);
} catch (Throwable $e) {
    $d['pledge_metal_gram'] = $d['pledge_metal_vori'] = 0;
}

// ── 7. Total cash balance (current) ─────────────────────
try {
    $s = $pdo->query("SELECT COALESCE(SUM(current_balance), 0) FROM cash_accounts");
    $d['total_balance'] = (float)$s->fetchColumn();
} catch (Throwable $e) {
    $d['total_balance'] = 0;
}

$d['period'] = $period;
$d['from']   = $from;
$d['to']     = $to;

echo json_encode(['success' => true, 'data' => $d]);