<?php
// db/staff-read.php
ob_start();
ob_clean();
require_once '../core/api.php';

header('Content-Type: application/json');

function getYoutubeId($url) {
    preg_match('/(?:youtube\.com\/(?:[^\/]+\/.+\/|(?:v|e(?:mbed)?)\/|.*[?&]v=)|youtu\.be\/)([^"&?\/\s]{11})/i', $url, $matches);
    return $matches[1] ?? '';
}

$id = isset($_GET['id']) ? (int)$_GET['id'] : null;
$details = isset($_GET['details']);
$academy = isset($_GET['academy']);
$global_academy = isset($_GET['global_academy']);

try {
    // 1. ACADEMIA
    if (($id && $academy) || $global_academy) {
        $params = [];
        $sqlV = "SELECT id, title, youtube_url, staff_id FROM staff_academy WHERE ";
        $sqlD = "SELECT id, file_name, file_path, staff_id FROM staff_documents WHERE ";

        if ($global_academy) { $sqlV .= "staff_id IS NULL"; $sqlD .= "staff_id IS NULL"; }
        else { $sqlV .= "(staff_id = ? OR staff_id IS NULL)"; $sqlD .= "(staff_id = ? OR staff_id IS NULL)"; $params[] = $id; }

        $stmtV = $pdo->prepare($sqlV . " ORDER BY created_at DESC"); $stmtV->execute($params); $videos = $stmtV->fetchAll(PDO::FETCH_ASSOC);
        foreach($videos as &$v) { $v['youtube_id'] = getYoutubeId($v['youtube_url']); $v['is_global'] = is_null($v['staff_id']); }

        $stmtD = $pdo->prepare($sqlD . " ORDER BY uploaded_at DESC"); $stmtD->execute($params); $docs = $stmtD->fetchAll(PDO::FETCH_ASSOC);
        foreach($docs as &$d) { $d['is_global'] = is_null($d['staff_id']); }

        echo json_encode(['success' => true, 'videos' => $videos, 'documents' => $docs]);
    }
    // 2. ASIGNACIONES (CLIENTES Y RUTAS)
    elseif ($id && $details) {
        $stmtC = $pdo->prepare("SELECT id, CONCAT(first_name, ' ', last_name) as name, street_address FROM clients WHERE staff_id = ?"); $stmtC->execute([$id]);
        $stmtR = $pdo->prepare("SELECT id, route_name as name, route_day FROM routes WHERE staff_id = ?"); $stmtR->execute([$id]);
        
        $allClients = $pdo->query("SELECT id, CONCAT(first_name, ' ', last_name) as name FROM clients WHERE staff_id IS NULL ORDER BY first_name ASC")->fetchAll(PDO::FETCH_ASSOC);
        $allRoutes = $pdo->query("SELECT id, route_name as name FROM routes WHERE staff_id IS NULL ORDER BY route_name ASC")->fetchAll(PDO::FETCH_ASSOC);
        $stmtB = $pdo->prepare("SELECT id, blocked_date as date, reason FROM staff_blocked_days WHERE staff_id = ?"); $stmtB->execute([$id]);

        echo json_encode([
            'success' => true, 
            'assigned_clients' => $stmtC->fetchAll(PDO::FETCH_ASSOC),
            'assigned_routes' => $stmtR->fetchAll(PDO::FETCH_ASSOC),
            'all_clients' => $allClients,
            'all_routes' => $allRoutes,
            'blocked_days' => $stmtB->fetchAll(PDO::FETCH_ASSOC)
        ]);
    }
    // 3. LISTA GENERAL
    else {
        $sql = "SELECT s.id, s.first_name, s.last_name, s.email, s.phone, s.hourly_rate, 
                s.access_code, s.public_uid, s.profile_photo_url, s.created_at, s.hired_date,
                s.is_employee_of_month, s.schedule_config, s.role,
                (SELECT COUNT(*) FROM clients c WHERE c.staff_id = s.id) as total_clients,
                (SELECT DATE_FORMAT(clock_in, '%h:%i %p') FROM work_logs wl WHERE wl.staff_id = s.id AND DATE(clock_in) = CURDATE() ORDER BY id DESC LIMIT 1) as today_entry,
                (SELECT DATE_FORMAT(clock_out, '%h:%i %p') FROM work_logs wl WHERE wl.staff_id = s.id AND DATE(clock_in) = CURDATE() ORDER BY id DESC LIMIT 1) as today_exit,
                (SELECT COUNT(*) FROM work_logs wl WHERE wl.staff_id = s.id AND wl.clock_out IS NULL AND DATE(wl.clock_in) = CURDATE()) as is_working
                FROM staff s WHERE s.status = 'active' ORDER BY s.role DESC, s.first_name ASC";
        
        $staff = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        foreach($staff as &$s) { $s['name'] = trim($s['first_name'] . ' ' . $s['last_name']); }
        
        // Obtener notificaci¨Žn global
        $notif = '';
        try {
            $notif = $pdo->query("SELECT message FROM staff_notifications WHERE id = 1")->fetchColumn();
        } catch (Exception $e) {} 

        $total_docs = $pdo->query("SELECT COUNT(*) FROM staff_documents WHERE staff_id IS NULL")->fetchColumn();
        
        echo json_encode(['success' => true, 'data' => $staff, 'total_docs' => $total_docs, 'global_notification' => $notif]);
    }
} catch (Exception $e) { echo json_encode(['success' => false, 'message' => $e->getMessage()]); }
?>