prepare("SELECT COUNT(*) FROM projects WHERE client_id = ?");
$stmt->execute([$client_id]);
$total_projects = (int)$stmt->fetchColumn();
// Active projects (Live or Targeted)
$stmt = $pdo->prepare("SELECT COUNT(*) FROM projects WHERE client_id = ? AND status IN ('Live','Targeted')");
$stmt->execute([$client_id]);
$active_projects = (int)$stmt->fetchColumn();
// Completed/Closed projects
$stmt = $pdo->prepare("SELECT COUNT(*) FROM projects WHERE client_id = ? AND status = 'Closed'");
$stmt->execute([$client_id]);
$completed_projects = (int)$stmt->fetchColumn();
// Pending support tickets (if support table exists)
$pending_support = 0;
} catch (Exception $e) {
error_log("Dashboard stats error: " . $e->getMessage());
$total_projects = 0;
$active_projects = 0;
$completed_projects = 0;
$pending_support = 0;
}
// Recent activity - last 10 project/selection events
$recent_activity = [];
try {
$stmt = $pdo->prepare("
(SELECT 'project' as type, p.project_name as name,
CONCAT('Project created') as action,
p.created_at as activity_date
FROM projects p WHERE p.client_id = ?)
UNION ALL
(SELECT 'selection' as type, ps.selection_name as name,
CONCAT('Selection created (', ps.status, ')') as action,
ps.created_at as activity_date
FROM project_selections ps WHERE ps.client_id = ?)
UNION ALL
(SELECT 'selection' as type, ps.selection_name as name,
CONCAT('Invitations sent') as action,
ps.invitations_sent_at as activity_date
FROM project_selections ps
WHERE ps.client_id = ? AND ps.invitations_sent_at IS NOT NULL)
ORDER BY activity_date DESC
LIMIT 10
");
$stmt->execute([$client_id, $client_id, $client_id]);
$recent_activity = $stmt->fetchAll();
} catch (Exception $e) {
$recent_activity = [];
}
include 'client-portal-header.php';
?>