<?php
require_once __DIR__ . '/../app/bootstrap.php';

use App\Core\Auth;
use App\Core\Database;

Auth::requireRole(['admin', 'staff']);
$pageTitle = 'Customer Report | Admin';

$q = mb_substr(trim((string)($_GET['q'] ?? '')), 0, 100);
$status = (string)($_GET['status'] ?? '');
if (!in_array($status, ['', 'active', 'suspended', 'pending'], true)) {
    $status = '';
}

$where = ['u.role="customer"'];
$params = [];
if ($q !== '') {
    $where[] = '(u.name LIKE ? OR u.email LIKE ? OR u.phone LIKE ?)';
    $like = '%' . $q . '%';
    array_push($params, $like, $like, $like);
}
if ($status !== '') {
    $where[] = 'u.status=?';
    $params[] = $status;
}
$whereSql = implode(' AND ', $where);

$summary = Database::safeFetchOne(
    'SELECT COUNT(*) total_customers,
            SUM(CASE WHEN u.status="active" THEN 1 ELSE 0 END) active_customers,
            SUM(CASE WHEN u.created_at>=DATE_SUB(NOW(),INTERVAL 30 DAY) THEN 1 ELSE 0 END) new_30_days,
            COALESCE(SUM((SELECT COALESCE(SUM(o.total_amount),0) FROM orders o WHERE o.customer_id=u.id AND o.order_status="delivered")),0) delivered_value
     FROM users u WHERE u.role="customer"'
) ?: [];

$rows = Database::safeFetchAll(
    "SELECT u.id,u.name,u.email,u.phone,u.status,u.created_at,u.last_login_at,
            (SELECT COUNT(*) FROM orders o WHERE o.customer_id=u.id) order_count,
            (SELECT COUNT(*) FROM orders o WHERE o.customer_id=u.id AND o.order_status='delivered') delivered_orders,
            (SELECT COALESCE(SUM(o.total_amount),0) FROM orders o WHERE o.customer_id=u.id AND o.order_status='delivered') lifetime_value,
            (SELECT COALESCE(SUM(w.balance),0) FROM wallets w WHERE w.user_id=u.id) wallet_balance,
            (SELECT COALESCE(SUM(rp.points),0) FROM reward_points rp WHERE rp.user_id=u.id AND (rp.expires_at IS NULL OR rp.expires_at>=CURDATE())) reward_balance
     FROM users u
     WHERE {$whereSql}
     ORDER BY lifetime_value DESC,u.id DESC
     LIMIT 500",
    $params
);

require ROOT_PATH . '/app/Layouts/admin_header.php';
?>
<div class="d-flex justify-content-between align-items-start flex-wrap gap-3 mb-4">
    <div><h1 class="h3 mb-1">Customer Report</h1><p class="text-muted mb-0">Read-only customer activity, order value, wallets and reward-point balances.</p></div>
    <a class="btn btn-outline-primary" href="<?= url('api/report-export.php?table=users&module=customer-report&format=csv') ?>">Export customer records</a>
</div>
<div class="row g-3 mb-4">
    <div class="col-md-3"><div class="stat-tile"><div class="small text-muted">Total customers</div><strong><?= number_format((int)($summary['total_customers'] ?? 0)) ?></strong></div></div>
    <div class="col-md-3"><div class="stat-tile"><div class="small text-muted">Active</div><strong><?= number_format((int)($summary['active_customers'] ?? 0)) ?></strong></div></div>
    <div class="col-md-3"><div class="stat-tile"><div class="small text-muted">New in 30 days</div><strong><?= number_format((int)($summary['new_30_days'] ?? 0)) ?></strong></div></div>
    <div class="col-md-3"><div class="stat-tile"><div class="small text-muted">Delivered value</div><strong><?= money((float)($summary['delivered_value'] ?? 0)) ?></strong></div></div>
</div>
<div class="card module-card">
    <div class="card-header bg-white">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-6"><label class="form-label">Search</label><input class="form-control" name="q" value="<?= e($q) ?>" placeholder="Name, email or phone"></div>
            <div class="col-md-3"><label class="form-label">Status</label><select class="form-select" name="status"><option value="">All statuses</option><?php foreach (['active','suspended','pending'] as $option): ?><option value="<?= e($option) ?>" <?= $status===$option?'selected':'' ?>><?= e(ucfirst($option)) ?></option><?php endforeach; ?></select></div>
            <div class="col-md-3 d-flex gap-2"><button class="btn btn-primary">Filter</button><a class="btn btn-outline-secondary" href="<?= url('admin/customer-report.php') ?>">Reset</a></div>
        </form>
    </div>
    <div class="table-responsive"><table class="table align-middle mb-0"><thead><tr><th>Customer</th><th>Status</th><th>Orders</th><th>Delivered</th><th>Lifetime value</th><th>Wallets</th><th>Reward points</th><th>Last login</th></tr></thead><tbody>
    <?php if (!$rows): ?><?= table_empty(8, 'No customer records match the filters.') ?><?php endif; ?>
    <?php foreach ($rows as $row): ?><tr>
        <td><strong><?= e($row['name'] ?: 'Unnamed customer') ?></strong><div class="small text-muted"><?= e($row['email'] ?: '') ?><?= !empty($row['phone']) ? ' · ' . e($row['phone']) : '' ?></div><div class="small text-muted">Joined <?= e($row['created_at'] ? date('d M Y', strtotime((string)$row['created_at'])) : '—') ?></div></td>
        <td><?= badge((string)$row['status']) ?></td>
        <td><?= number_format((int)$row['order_count']) ?></td>
        <td><?= number_format((int)$row['delivered_orders']) ?></td>
        <td><?= money((float)$row['lifetime_value']) ?></td>
        <td><?= money((float)$row['wallet_balance']) ?></td>
        <td><?= number_format((int)$row['reward_balance']) ?></td>
        <td><?= e($row['last_login_at'] ? date('d M Y H:i', strtotime((string)$row['last_login_at'])) : 'Never') ?></td>
    </tr><?php endforeach; ?>
    </tbody></table></div>
</div>
<?php require ROOT_PATH . '/app/Layouts/admin_footer.php'; ?>
