<?php
// POST /api/projects/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', '');
$priority = post('priority', '');
$myOnly   = post('my_only', '');
$where    = ['1=1'];
$params   = [];

if ($search)   { $where[] = 'p.name LIKE ?'; $params[] = "%$search%"; }
if ($status)   { $where[] = 'p.status = ?';  $params[] = $status; }
if ($priority) { $where[] = 'p.priority = ?'; $params[] = $priority; }

if ($myOnly || $user['role_id'] == 2 || $user['role_id'] == 3) {
    $where[] = 'EXISTS (SELECT 1 FROM project_members pm WHERE pm.project_id = p.id AND pm.user_id = ?)';
    $params[] = $user['id'];
}

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

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

$stmt = $db->prepare("
    SELECT p.*,
           c.company_name AS client_name,
           u.full_name AS manager_name,
           (SELECT COUNT(*) FROM project_todos pt WHERE pt.project_id = p.id AND pt.status != 'done') AS open_todos,
           (SELECT COUNT(*) FROM project_bugs pb WHERE pb.project_id = p.id AND pb.status = 'open') AS open_bugs,
           (SELECT COUNT(*) FROM project_members pm WHERE pm.project_id = p.id) AS member_count
    FROM projects p
    LEFT JOIN clients c ON c.id = p.client_id
    LEFT JOIN users u ON u.id = p.project_manager
    WHERE $whereStr
    ORDER BY p.updated_at DESC
    LIMIT {$p['limit']} OFFSET {$p['offset']}
");
$stmt->execute($params);

apiSuccess([
    'projects'   => $stmt->fetchAll(),
    'pagination' => ['total' => $total, 'page' => $p['page'], 'limit' => $p['limit'], 'pages' => ceil($total / $p['limit'])]
]);