<?php
// POST /api/clients/list.php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user = requireAuth();
$db   = getDB();
$p    = getPagination();

$search  = post('search', '');
$status  = post('status', '');
$where   = ['1=1'];
$params  = [];

if ($search) {
    $where[] = '(c.company_name LIKE ? OR c.trading_name LIKE ? OR c.registration_no LIKE ?)';
    $params  = array_merge($params, ["%$search%", "%$search%", "%$search%"]);
}
if ($status) {
    $where[] = 'c.status = ?';
    $params[] = $status;
}

$whereStr = implode(' AND ', $where);

$total = (int)$db->prepare("SELECT COUNT(*) FROM clients c WHERE $whereStr")
    ->execute($params) ? (int)$db->prepare("SELECT COUNT(*) FROM clients c WHERE $whereStr")->execute($params) : 0;

$countStmt = $db->prepare("SELECT COUNT(*) FROM clients c WHERE $whereStr");
$countStmt->execute($params);
$total = (int)$countStmt->fetchColumn();

$stmt = $db->prepare("
    SELECT c.*,
        (SELECT COUNT(*) FROM client_contacts cc WHERE cc.client_id = c.id) AS contact_count,
        (SELECT COUNT(*) FROM projects p WHERE p.client_id = c.id) AS project_count,
        (SELECT COUNT(*) FROM job_cards jc WHERE jc.client_id = c.id) AS job_count,
        u.full_name AS created_by_name
    FROM clients c
    LEFT JOIN users u ON u.id = c.created_by
    WHERE $whereStr
    ORDER BY c.company_name ASC
    LIMIT {$p['limit']} OFFSET {$p['offset']}
");
$stmt->execute($params);
$clients = $stmt->fetchAll();

apiSuccess([
    'clients'    => $clients,
    'pagination' => [
        'total'   => $total,
        'page'    => $p['page'],
        'limit'   => $p['limit'],
        'pages'   => ceil($total / $p['limit']),
    ]
]);