<?php
// POST /api/projects/get.php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user = requireAuth();
$db   = getDB();
$id   = (int)post('id', 0);

if (!$id) apiError('Project ID required.', 422);

$stmt = $db->prepare("
    SELECT p.*, c.company_name AS client_name, u.full_name AS manager_name, ub.full_name AS created_by_name
    FROM projects p
    LEFT JOIN clients c ON c.id = p.client_id
    LEFT JOIN users u ON u.id = p.project_manager
    LEFT JOIN users ub ON ub.id = p.created_by
    WHERE p.id = ?
");
$stmt->execute([$id]);
$project = $stmt->fetch();
if (!$project) apiError('Project not found.', 404);

// Sections with todos
$sections = $db->prepare("SELECT * FROM project_sections WHERE project_id = ? ORDER BY sort_order ASC, name ASC");
$sections->execute([$id]);
$project['sections'] = $sections->fetchAll();

foreach ($project['sections'] as &$section) {
    $todos = $db->prepare("
        SELECT pt.*, u.full_name AS assigned_name
        FROM project_todos pt
        LEFT JOIN users u ON u.id = pt.assigned_to
        WHERE pt.section_id = ?
        ORDER BY pt.sort_order ASC, pt.created_at ASC
    ");
    $todos->execute([$section['id']]);
    $section['todos'] = $todos->fetchAll();
}

// Members
$members = $db->prepare("
    SELECT pm.*, u.full_name, u.email, u.avatar, r.name AS system_role_name
    FROM project_members pm
    JOIN users u ON u.id = pm.user_id
    JOIN roles r ON r.id = u.role_id
    WHERE pm.project_id = ?
");
$members->execute([$id]);
$project['members'] = $members->fetchAll();

// Recent bugs
$bugs = $db->prepare("
    SELECT pb.*, u.full_name AS reporter_name, ua.full_name AS assigned_name
    FROM project_bugs pb
    JOIN users u ON u.id = pb.reported_by
    LEFT JOIN users ua ON ua.id = pb.assigned_to
    WHERE pb.project_id = ? AND pb.status IN ('open','in_progress')
    ORDER BY pb.created_at DESC
    LIMIT 10
");
$bugs->execute([$id]);
$project['open_bugs'] = $bugs->fetchAll();

// Test requests
$tests = $db->prepare("
    SELECT ptr.*, u.full_name AS requested_by_name, ut.full_name AS tester_name
    FROM project_test_requests ptr
    JOIN users u ON u.id = ptr.requested_by
    LEFT JOIN users ut ON ut.id = ptr.assigned_tester
    WHERE ptr.project_id = ?
    ORDER BY ptr.created_at DESC
    LIMIT 5
");
$tests->execute([$id]);
$project['test_requests'] = $tests->fetchAll();

// Activity
$activity = $db->prepare("
    SELECT pa.*, u.full_name AS user_name
    FROM project_activity pa
    JOIN users u ON u.id = pa.user_id
    WHERE pa.project_id = ?
    ORDER BY pa.created_at DESC
    LIMIT 20
");
$activity->execute([$id]);
$project['activity'] = $activity->fetchAll();

apiSuccess(['project' => $project]);