<?php
// /patient_functions.php
// Este archivo contiene toda la lógica de negocio para las peticiones AJAX del paciente.
// Es llamado por patient_ajax.php.

/**
 * Actualiza la marca de tiempo 'last_seen' del paciente.
 */
function updatePatientLastSeen($db, $patient_id) {
    // CAMBIO: datetime('now') -> NOW()
    $stmt = $db->prepare("UPDATE cuentas SET last_seen = NOW() WHERE id = ?");
    $stmt->execute([$patient_id]);
    return ['status' => 'success'];
}

/**
 * Obtiene la lista de tratamientos activos (En curs) para el selector.
 */
function getActiveTreatmentData($db, $patient_id) {
    $stmt = $db->prepare( "SELECT id, title FROM tratamientos WHERE paciente_id = :patient_id AND status = 'En curs' ORDER BY start_date DESC" );
    $stmt->execute([':patient_id' => $patient_id]);
    $activeTreatmentsList = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return ['status' => 'success', 'active_treatments' => $activeTreatmentsList];
}

/**
 * Comprueba si el paciente tiene algún tratamiento en el historial (para habilitar menús).
 */
function checkPatientHistoryExists($db, $patient_id) {
    $stmt_check = $db->prepare("SELECT 1 FROM tratamientos WHERE paciente_id = :patient_id AND status IN ('Completat', 'Omés', 'Finalitzat') LIMIT 1");
    $stmt_check->execute([':patient_id' => $patient_id]);
    $history_exists = (bool)$stmt_check->fetch();
    return ['status' => 'success', 'exists' => $history_exists];
}

/**
 * Obtiene todos los detalles de un tratamiento específico, incluyendo widgets y progreso.
 */
function getPatientTreatmentDetails($db, $patient_id, $treatment_id) {
    if ($treatment_id === 0) { throw new Exception('ID de tractament no proporcionat.'); }
    $today_str = date('Y-m-d');
    $today_dt = new DateTimeImmutable('now');

    $stmt_check = $db->prepare( "SELECT id, title, start_date, end_date FROM tratamientos WHERE id = :treatment_id AND paciente_id = :patient_id AND status = 'En curs' LIMIT 1" );
    $stmt_check->execute([':treatment_id' => $treatment_id, ':patient_id' => $patient_id]);
    $treatmentDetails = $stmt_check->fetch(PDO::FETCH_ASSOC);
    if (!$treatmentDetails) { throw new Exception('Tractament no trobat o no en curs per a este pacient.'); }

    // --- INICIO CÁLCULOS PARA WIDGETS ---
    $widget_data = [
        'total_recorded_days' => 0, 'total_recorded_exercises' => 0, 'avg_pain_last_week' => null,
        'avg_effort_last_week' => null, 'streak_days' => 0, 'assigned_fisios_string' => 'No assignat'
    ];
    $widgets_html = '';

    try {
        $stmt_evo_full = $db->prepare("SELECT fecha_realizacion, tratamiento_ejercicio_id, dolor_percibido, esfuerzo_percibido FROM tratamiento_evolucion ev JOIN tratamiento_ejercicios te ON ev.tratamiento_ejercicio_id = te.id WHERE te.tratamiento_id = ?");
        $stmt_evo_full->execute([$treatment_id]);
        $all_evolution_records_widgets = $stmt_evo_full->fetchAll(PDO::FETCH_ASSOC);

        $stmt_fisios = $db->prepare("SELECT c.nombre, c.apellido FROM tratamiento_fisios_asignados tfa JOIN cuentas c ON tfa.fisio_id = c.id WHERE tfa.tratamiento_id = ? ORDER BY c.apellido, c.nombre");
        $stmt_fisios->execute([$treatment_id]);
        $assigned_fisios = $stmt_fisios->fetchAll(PDO::FETCH_ASSOC);

        $recorded_dates = []; $pain_sum_week = 0; $effort_sum_week = 0; $count_week = 0;
        $seven_days_ago = date('Y-m-d', strtotime('-7 days'));
        foreach ($all_evolution_records_widgets as $rec) {
            $recorded_dates[$rec['fecha_realizacion']] = true;
            if ($rec['fecha_realizacion'] >= $seven_days_ago) {
                if (is_numeric($rec['dolor_percibido'])) { $pain_sum_week += (float)$rec['dolor_percibido']; }
                if (is_numeric($rec['esfuerzo_percibido'])) { $effort_sum_week += (float)$rec['esfuerzo_percibido']; }
                if (is_numeric($rec['dolor_percibido']) || is_numeric($rec['esfuerzo_percibido'])) { $count_week++; }
            }
        }
        $widget_data['total_recorded_days'] = count($recorded_dates);
        $widget_data['total_recorded_exercises'] = count($all_evolution_records_widgets);
        $widget_data['avg_pain_last_week'] = ($count_week > 0 && $pain_sum_week > 0) ? ($pain_sum_week / $count_week) : null;
        $widget_data['avg_effort_last_week'] = ($count_week > 0 && $effort_sum_week > 0) ? ($effort_sum_week / $count_week) : null;

        $streak_days = 0;
        if (!empty($recorded_dates)) {
            $unique_sorted_dates = array_keys($recorded_dates); sort($unique_sorted_dates);
            $current_streak = 0; $expected_date = null;
            for ($i = count($unique_sorted_dates) - 1; $i >= 0; $i--) {
                $current_date_str = $unique_sorted_dates[$i]; $current_date_dt = new DateTimeImmutable($current_date_str);
                if ($i === count($unique_sorted_dates) - 1) {
                    $interval = $current_date_dt->diff($today_dt);
                    if ($interval->days <= 1) { $current_streak = 1; $expected_date = $current_date_dt->modify('-1 day'); } else { break; }
                } elseif ($expected_date && $current_date_str === $expected_date->format('Y-m-d')) { $current_streak++; $expected_date = $expected_date->modify('-1 day'); } else { break; }
            }
            $streak_days = $current_streak;
        }
        $widget_data['streak_days'] = $streak_days;

        // Usar función anónima compatible con PHP 7.3+ (ya estaba en tu código)
        $fisio_names = array_map(function($f) {
            return trim(($f['nombre'] ?? '') . ' ' . ($f['apellido'] ?? ''));
        }, $assigned_fisios);
        $widget_data['assigned_fisios_string'] = !empty($fisio_names) ? implode(', ', $fisio_names) : 'No assignat';

        ob_start();
        if (file_exists('partials/patient_widgets.php')) { include 'partials/patient_widgets.php'; }
        else { echo '<div class="col-12"><div class="alert alert-danger small">Error: No se encontró el archivo de widgets.</div></div>'; error_log("Error: patient_widgets.php no encontrado."); }
        $widgets_html = ob_get_clean();
    } catch (Exception $widget_ex) { error_log("Error al generar widgets para tID $treatment_id: " . $widget_ex->getMessage()); $widgets_html = '<div class="col-12"><div class="alert alert-warning small">Error en carregar el resum del progrés.</div></div>'; }
    // --- FIN CÁLCULOS Y GENERACIÓN WIDGETS ---

    // --- CÁLCULOS DE PROGRESO (existentes) ---
    $progress_percent_time = 0; $progress_percent_adherence = 0;
     try {
         $start = new DateTimeImmutable($treatmentDetails['start_date']); $end = new DateTimeImmutable($treatmentDetails['end_date']);
         $total_duration_interval = $start->diff($end); $total_duration = $total_duration_interval->days + ($total_duration_interval->h / 24) + ($total_duration_interval->i / 1440);
         if ($total_duration > 0) {
             $days_passed = 0;
             if ($today_dt >= $start) { $passed_interval = $start->diff($today_dt); $days_passed = $passed_interval->days + ($passed_interval->h / 24) + ($passed_interval->i / 1440); }
             $progress_percent_time = min(100, max(0, ($days_passed / $total_duration) * 100));
         }
         $progress_percent_time = round($progress_percent_time);
     } catch (Exception $e) { $progress_percent_time = 0; error_log("Error calculando progress_time: ".$e->getMessage()); }

     try {
        if (!isset($all_evolution_records_widgets)) { $stmt_evo_full = $db->prepare("SELECT fecha_realizacion, tratamiento_ejercicio_id FROM tratamiento_evolucion ev JOIN tratamiento_ejercicios te ON ev.tratamiento_ejercicio_id = te.id WHERE te.tratamiento_id = ?"); $stmt_evo_full->execute([$treatment_id]); $all_evolution_records_widgets = $stmt_evo_full->fetchAll(PDO::FETCH_ASSOC); }
        $ex_stmt = $db->prepare( "SELECT te.id, te.ejercicio_id, te.frecuencia FROM tratamiento_ejercicios te WHERE te.tratamiento_id = ? ORDER BY te.id" ); $ex_stmt->execute([$treatment_id]); $exercises = $ex_stmt->fetchAll(PDO::FETCH_ASSOC);
        $evolution_map = []; foreach ($all_evolution_records_widgets as $rec) { $evolution_map[$rec['fecha_realizacion']][$rec['tratamiento_ejercicio_id']] = true; }
        $total_scheduled = 0; $total_completed = 0;
        $loop_start_date = new DateTimeImmutable($treatmentDetails['start_date']); $loop_end_date_treatment = new DateTimeImmutable($treatmentDetails['end_date']); $loop_end_date = ($today_dt > $loop_end_date_treatment) ? $loop_end_date_treatment : $today_dt; $current_loop_date = $loop_start_date;
        while ($current_loop_date <= $loop_end_date) {
            $dateStr = $current_loop_date->format('Y-m-d'); $dayOfWeek = (int)$current_loop_date->format('w');
            foreach ($exercises as $ex) {
                $is_scheduled = false; $freq = $ex['frecuencia'];
                if (!$freq || $freq === 'Diari') { $is_scheduled = true; }
                else if ($freq === '3xSetmana' && in_array($dayOfWeek, [1, 3, 5])) { $is_scheduled = true; }
                else if ($freq === '2xSetmana' && in_array($dayOfWeek, [2, 4])) { $is_scheduled = true; }
                else if ($freq === 'Altern') { $dayDiff = $loop_start_date->diff($current_loop_date)->days; if ($dayDiff >= 0 && $dayDiff % 2 === 0) { $is_scheduled = true; } }
                if ($is_scheduled) { $total_scheduled++; if (isset($evolution_map[$dateStr][$ex['id']])) { $total_completed++; } }
            }
            $current_loop_date = $current_loop_date->modify('+1 day');
        }
        $progress_percent_adherence = ($total_scheduled > 0) ? round(($total_completed / $total_scheduled) * 100) : 0;
     } catch (Exception $e) { $progress_percent_adherence = 0; error_log("Error calculando adherence: ".$e->getMessage()); }

    $ex_details_stmt = $db->prepare( "SELECT e.title, e.notes as exercise_general_notes, v.filename as video_filename, i.filename as image_filename, te.*, te.ejercicio_id FROM tratamiento_ejercicios te JOIN ejercicios e ON te.ejercicio_id = e.id LEFT JOIN videos v ON e.id_video = v.id LEFT JOIN images i ON e.id_image = i.id WHERE te.tratamiento_id = ? ORDER BY te.id" );
    $ex_details_stmt->execute([$treatment_id]); $exercises_details = $ex_details_stmt->fetchAll(PDO::FETCH_ASSOC);

    return [
        'status' => 'success', 'treatment' => $treatmentDetails, 'exercises' => $exercises_details,
        'progress_percent_time' => $progress_percent_time, 'progress_percent_adherence' => $progress_percent_adherence,
        'widgets_html' => $widgets_html
    ];
}

/**
 * Obtiene los datos de evolución (para el gráfico), opcionalmente filtrados por ejercicio.
 * (Función movida desde fitxa_pacient.php)
 * NOTA: Renombrada de get_full_evolution_data a getPatientFullEvolutionData
 */
function getPatientFullEvolutionData($db, $patient_id, $treatment_id, $exercise_id_filter, $current_fisio_id, $user_rol) {
    if ($treatment_id === 0) { throw new Exception('ID de tractament no proporcionat.'); }

    // El fisio solo puede ver la evolución si tiene permiso sobre el paciente Y el tratamiento
    if ($user_rol !== 'superadmin') {
        $permStmt = $db->prepare(" SELECT 1 FROM tratamientos t LEFT JOIN tratamiento_fisios_asignados tfa ON t.id = tfa.tratamiento_id WHERE t.id = :treatment_id AND t.paciente_id = :pacient_id AND (t.creator_fisio_id = :current_fisio_id OR tfa.fisio_id = :current_fisio_id) LIMIT 1 ");
        $permStmt->execute([':treatment_id' => $treatment_id, ':pacient_id' => $patient_id, ':current_fisio_id' => $current_fisio_id ]);
         if (!$permStmt->fetch()) { throw new Exception('Accés no autoritzat a les dades d\'evolució d\'aquest tractament.'); }
    }

    $params = [':treatment_id' => $treatment_id];
    $sql = "SELECT te.id as tratamiento_ejercicio_id, ev.fecha_realizacion, ev.dolor_percibido, ev.esfuerzo_percibido, ev.comentarios FROM tratamiento_evolucion ev JOIN tratamiento_ejercicios te ON ev.tratamiento_ejercicio_id = te.id WHERE te.tratamiento_id = :treatment_id";
    if ($exercise_id_filter > 0) { $sql .= " AND te.ejercicio_id = :exercise_id"; $params[':exercise_id'] = $exercise_id_filter; }
     $sql .= " ORDER BY ev.fecha_realizacion ASC";
     $evo_stmt = $db->prepare($sql); $evo_stmt->execute($params);
     $evolution_grouped = $evo_stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
     return ['status' => 'success', 'evolution' => $evolution_grouped];
}

/**
 * Obtiene los datos de evolución (para el gráfico), opcionalmente filtrados por ejercicio.
 * (Función movida desde fitxa_pacient.php)
 * NOTA: Renombrada de get_full_evolution a getPatientFullEvolution
 */
function getPatientFullEvolution($db, $patient_id, $treatment_id, $exercise_id_filter) {
    if ($treatment_id === 0) { throw new Exception('ID de tractament no proporcionat.'); }
    $stmt_check_owner = $db->prepare("SELECT id FROM tratamientos WHERE id = ? AND paciente_id = ?"); $stmt_check_owner->execute([$treatment_id, $patient_id]);
    if (!$stmt_check_owner->fetch()) { throw new Exception('Accés denegat a este tractament.'); }
    $params = [':treatment_id' => $treatment_id];
    $sql = "SELECT te.id as tratamiento_ejercicio_id, ev.fecha_realizacion, ev.dolor_percibido, ev.esfuerzo_percibido, ev.comentarios FROM tratamiento_evolucion ev JOIN tratamiento_ejercicios te ON ev.tratamiento_ejercicio_id = te.id WHERE te.tratamiento_id = :treatment_id";
    if ($exercise_id_filter > 0) { $sql .= " AND te.ejercicio_id = :exercise_id"; $params[':exercise_id'] = $exercise_id_filter; }
    $sql .= " ORDER BY ev.fecha_realizacion ASC";
    $evo_stmt = $db->prepare($sql); $evo_stmt->execute($params);
    $evolution_grouped = $evo_stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
    return ['status' => 'success', 'evolution' => $evolution_grouped];
}

/**
 * Guarda (Inserta o Actualiza) el feedback de un ejercicio para un día concreto.
 */
function savePatientExerciseFeedback($db, $patient_id, $post_data) {
    $te_id = (int)$post_data['tratamiento_ejercicio_id']; $date = $post_data['fecha_realizacion'];
    $dolor = (int)$post_data['dolor_percibido']; $esfuerzo = (int)$post_data['esfuerzo_percibido'];
    $comentarios = trim($post_data['comentarios']); $hora_actual = date('H:i:s');

    // 1. Obtener información del tratamiento y ejercicio (¡NUEVO!)
    $stmt_info = $db->prepare(
        "SELECT t.id as treatment_id, t.creator_fisio_id, e.title as exercise_title, c.nombre as patient_name, c.apellido as patient_surname
         FROM tratamiento_ejercicios te
         JOIN tratamientos t ON te.tratamiento_id = t.id
         JOIN ejercicios e ON te.ejercicio_id = e.id
         JOIN cuentas c ON t.paciente_id = c.id
         WHERE te.id = :te_id AND t.paciente_id = :patient_id AND t.status = 'En curs'"
    );
    $stmt_info->execute([':te_id' => $te_id, ':patient_id' => $patient_id]);

    $info = $stmt_info->fetch(PDO::FETCH_ASSOC);

    if (!$info) { throw new Exception('No es pot registrar feedback per a este exercici (no trobat o tractament no actiu).'); }

    // 2. Guardar la evolución (como antes)
    $stmt_find = $db->prepare("SELECT id FROM tratamiento_evolucion WHERE tratamiento_ejercicio_id = :te_id AND fecha_realizacion = :fecha LIMIT 1"); $stmt_find->execute([':te_id' => $te_id, ':fecha' => $date]);
    $existing_record = $stmt_find->fetch(PDO::FETCH_ASSOC);

    if ($existing_record) {
        $stmt = $db->prepare("UPDATE tratamiento_evolucion SET dolor_percibido = :dolor, esfuerzo_percibido = :esfuerzo, comentarios = :comentarios, hora_realizacion = :hora WHERE id = :id");
        $stmt->execute([':dolor' => $dolor, ':esfuerzo' => $esfuerzo, ':comentarios' => $comentarios, ':hora' => $hora_actual, ':id' => $existing_record['id']]); $message = 'Valoració modificada correctament.';
    } else {
        $stmt = $db->prepare("INSERT INTO tratamiento_evolucion (tratamiento_ejercicio_id, fecha_realizacion, dolor_percibido, esfuerzo_percibido, comentarios, hora_realizacion) VALUES (:te_id, :fecha, :dolor, :esfuerzo, :comentarios, :hora)");
        $stmt->execute([':te_id' => $te_id, ':fecha' => $date, ':dolor' => $dolor, ':esfuerzo' => $esfuerzo, ':comentarios' => $comentarios, ':hora' => $hora_actual]); $message = 'Valoració guardada correctament.';
    }

    // --- INICIO DE LA LÓGICA DE NOTIFICACIÓN (CORREGIDA SEGÚN TU SCHEMA) ---

    // 3. Preparar el mensaje de notificación
    $patient_full_name = $info['patient_name'] . ' ' . $info['patient_surname'];
    $notification_message = "$patient_full_name ha valorat l'exercici '" . $info['exercise_title'] . "'.";

    // Usaremos 'tipo_evento' para la urgencia, ya que 'priority' no existe
    $tipo_evento = 'feedback';

    if ($dolor >= 4) {
        $notification_message .= " Ha reportat dolor alt: $dolor/5.";
        $tipo_evento = 'feedback_urgent'; // Marcar como urgente
    } else if ($dolor > 0) {
         $notification_message .= " Dolor: $dolor/5.";
    }

    // 4. Obtener TODOS los fisios (creador + colaboradores)
    $stmt_fisios = $db->prepare(
        "SELECT fisio_id FROM tratamiento_fisios_asignados WHERE tratamiento_id = :treatment_id"
    );
    $stmt_fisios->execute([':treatment_id' => $info['treatment_id']]);
    $fisio_ids = $stmt_fisios->fetchAll(PDO::FETCH_COLUMN);

    // Asegurarse de que el creador está (aunque 'tratamiento_fisios_asignados' debería incluirlo)
    if (!in_array($info['creator_fisio_id'], $fisio_ids)) {
        $fisio_ids[] = $info['creator_fisio_id'];
    }

    // 5. Insertar la notificación para cada fisio (USANDO EL SCHEMA CORRECTO)
    $stmt_notif = $db->prepare(
        "INSERT INTO notificaciones (fisio_id, tipo_evento, id_relacionado, id_secundario, mensaje, fecha_creacion, url_destino)
         VALUES (?, ?, ?, ?, ?, NOW(), ?)" // CAMBIO: datetime('now') -> NOW()
    );

    // ★★★ INICIO MODIFICACIÓN: Añadir &evo_date= a la URL ★★★
    // La variable $date está definida en la línea 234
    $link = "fitxa_pacient.php?id=$patient_id&action=open_evo&treatment_id=" . $info['treatment_id'] . "&evo_date=" . $date;
    // ★★★ FIN MODIFICACIÓN ★★★

    $id_relacionado = (int)$patient_id; // ID del paciente
    $id_secundario = (int)$info['treatment_id']; // ID del tratamiento

    foreach (array_unique($fisio_ids) as $fisio_id) {
        if ($fisio_id) { // Evitar IDs nulos
            // Ejecutar con los nombres de columna correctos
            $stmt_notif->execute([$fisio_id, $tipo_evento, $id_relacionado, $id_secundario, $notification_message, $link]);
        }
    }
    // --- FIN DE LA LÓGICA DE NOTIFICACIÓN ---

    // --- INICIO MODIFICACIÓN: Devolver datos actualizados (Tu Idea 1) ---
    // Volver a calcular todos los widgets y datos de progreso
    $updated_data = [];
    try {
        // Re-llamamos a la función que calcula todo (getPatientTreatmentDetails)
        // Esta función ya la tenemos en este mismo archivo
        $updated_data = getPatientTreatmentDetails($db, $patient_id, $info['treatment_id']);
    } catch (Exception $e) {
        // Si esto falla, no rompemos la app, solo logueamos
        error_log("Error al refrescar widgets post-feedback: " . $e->getMessage());
        $updated_data = null; // El JS sabrá que no debe actualizar
    }

    return ['status' => 'success', 'message' => $message, 'updated_data' => $updated_data];
    // --- FIN MODIFICACIÓN ---
}

/**
 * Elimina el feedback de un ejercicio para un día concreto.
 */
function deletePatientExerciseFeedback($db, $patient_id, $post_data) {
    $te_id = (int)$post_data['tratamiento_ejercicio_id']; $date = $post_data['fecha_realizacion'];
    $stmt_check_te = $db->prepare( "SELECT t.id FROM tratamiento_ejercicios te JOIN tratamientos t ON te.tratamiento_id = t.id WHERE te.id = :te_id AND t.paciente_id = :patient_id" ); $stmt_check_te->execute([':te_id' => $te_id, ':patient_id' => $patient_id]);
    if (!$stmt_check_te->fetch()) { throw new Exception('No es pot eliminar el feedback per a este exercici.'); }
    $stmt_delete = $db->prepare("DELETE FROM tratamiento_evolucion WHERE tratamiento_ejercicio_id = :te_id AND fecha_realizacion = :fecha"); $stmt_delete->execute([':te_id' => $te_id, ':fecha' => $date]);
    if ($stmt_delete->rowCount() > 0) { return ['status' => 'success', 'message' => 'Valoració eliminada correctament.']; }
    else { return ['status' => 'success', 'message' => 'No s\'ha trobat cap valoració per eliminar.']; }
}

/**
 * Obtiene los datos personales del paciente para el modal de perfil.
 */
function getPatientAccountData($db, $patient_id) {
    $stmt = $db->prepare("SELECT nombre, apellido, email, telefono FROM cuentas WHERE id = ?"); $stmt->execute([$patient_id]); $data = $stmt->fetch(PDO::FETCH_ASSOC);
    return ['status' => 'success', 'data' => ['data' => $data]]; // <- Envuelto en un 'data' extra para coincidir con JS
}

/**
 * Actualiza los datos personales (nombre, email, etc.) del paciente.
 * Modifica la variable de SESIÓN por referencia.
 */
function updatePatientPersonalData($db, $patient_id, $post_data, &$session) {
    $nombre = trim($post_data['nombre'] ?? ''); $apellido = trim($post_data['apellido'] ?? ''); $email = trim($post_data['email'] ?? ''); $telefono = trim($post_data['telefono'] ?? '');
    if (empty($nombre) || empty($apellido) || empty($email)) { throw new Exception('Nom, cognom i email són obligatoris.'); }
    $stmt_check = $db->prepare("SELECT id FROM cuentas WHERE email = ? AND id != ?"); $stmt_check->execute([$email, $patient_id]); if ($stmt_check->fetch()) { throw new Exception('Aquest correu electrònic ja està en ús per un altre compte.'); }
    $stmt_update = $db->prepare("UPDATE cuentas SET nombre = ?, apellido = ?, email = ?, telefono = ? WHERE id = ?"); $stmt_update->execute([$nombre, $apellido, $email, $telefono, $patient_id]);

    // Actualizar la sesión
    $session['user_nombre'] = $nombre;

    return ['status' => 'success', 'message' => 'Dades actualitzades correctament.', 'data' => ['newName' => $nombre]]; // <- Envuelto en 'data'
}

/**
 * Actualiza la contraseña del paciente.
 */
function updatePatientPassword($db, $patient_id, $post_data) {
    $pass = $post_data['new_password'] ?? ''; // <- Nombre de campo corregido
    $pass_rep = $post_data['confirm_new_password'] ?? ''; // <- Nombre de campo corregido
    if (empty($pass) || empty($pass_rep)) { throw new Exception('Has d\'omplir ambdós camps de contrasenya.'); }
    if (strlen($pass) < 6 || strlen($pass) > 12) { throw new Exception('La contrasenya ha de tindre entre 6 i 12 caràcters.'); } if (!preg_match('/[A-Z]/', $pass)) { throw new Exception('La contrasenya ha de contindre almenys una majúscula.'); } if (!preg_match('/[0-9]/', $pass)) { throw new Exception('La contrasenya ha de contindre almenys un número.'); } if ($pass !== $pass_rep) { throw new Exception('Les contrasenyes no coincideixen.'); }
    $hashed_password = password_hash($pass, PASSWORD_DEFAULT); $columna_password = 'password'; $stmt_pass = $db->prepare("UPDATE cuentas SET $columna_password = ? WHERE id = ?"); $stmt_pass->execute([$hashed_password, $patient_id]);
    return ['status' => 'success', 'message' => 'Contrasenya actualitzada correctament.'];
}

/**
 * Obtiene el historial de tratamientos (no activos) para el modal de historial.
 */
function getPatientTreatmentHistory($db, $patient_id) {
    $stmt_history = $db->prepare("SELECT t.id, t.title, t.status, t.start_date, t.end_date, t.realizacion_percent FROM tratamientos t WHERE t.paciente_id = :patient_id AND t.status IN ('Completat', 'Omés', 'Finalitzat') AND t.is_protocol = 0 ORDER BY t.end_date DESC, t.start_date DESC");
    $stmt_history->execute([':patient_id' => $patient_id]); $history_treatments = $stmt_history->fetchAll(PDO::FETCH_ASSOC);
    $stmt_ex = $db->prepare("SELECT e.title, te.series, te.repetitions, te.rest_time FROM tratamiento_ejercicios te JOIN ejercicios e ON te.ejercicio_id = e.id WHERE te.tratamiento_id = ? ORDER BY te.id");
    foreach ($history_treatments as &$treatment) {
        $stmt_ex->execute([$treatment['id']]); $treatment['exercises'] = $stmt_ex->fetchAll(PDO::FETCH_ASSOC);
        $treatment['start_date_formatted'] = (new DateTime($treatment['start_date']))->format('d/m/Y'); $treatment['end_date_formatted'] = $treatment['end_date'] ? (new DateTime($treatment['end_date']))->format('d/m/Y') : 'N/A';
        if ($treatment['status'] === 'Finalitzat' && $treatment['realizacion_percent'] === null) { $treatment['realizacion_percent_display'] = 'N/A'; }
        elseif ($treatment['realizacion_percent'] !== null) { $treatment['realizacion_percent_display'] = $treatment['realizacion_percent'] . '%'; } else { $treatment['realizacion_percent_display'] = 'N/A'; }
    }
    return ['status' => 'success', 'history' => $history_treatments];
}

/**
 * Obtiene el contador de mensajes de chat no leídos.
 * MODIFICADO: Ahora cuenta CONVERSACIONES (remitentes únicos) no leídas, no mensajes.
 */
function getPatientUnreadMessageCount($db, $patient_id) {
    // ANTERIOR: $stmt = $db->prepare("SELECT COUNT(id) FROM chat_messages WHERE to_id = :patient_id AND status != 'read'");
    $stmt = $db->prepare("SELECT COUNT(DISTINCT from_id) FROM chat_messages WHERE to_id = :patient_id AND status != 'read'");
    $stmt->execute([':patient_id' => $patient_id]);
    $count = $stmt->fetchColumn();
    return ['status' => 'success', 'count' => (int)$count]; // Aseguramos que sea un entero
}

/**
 * Obtiene los mensajes de una conversación con un fisio específico.
 */
function getPatientChatMessages($db, $patient_id, $fisio_id) {
    if ($fisio_id === 0) throw new Exception("ID de fisio no vàlid.");
    $stmt_fisio = $db->prepare("SELECT last_seen FROM cuentas WHERE id = ?"); $stmt_fisio->execute([$fisio_id]); $fisio_data = $stmt_fisio->fetch(PDO::FETCH_ASSOC); $last_seen_status = $fisio_data['last_seen'] ?? null;
    $updateStmt = $db->prepare("UPDATE chat_messages SET status = 'read' WHERE from_id = :fisio_id AND to_id = :patient_id AND status != 'read'"); $updateStmt->execute([':fisio_id' => $fisio_id, ':patient_id' => $patient_id]);
    $stmt = $db->prepare("SELECT cm.id, cm.from_id, cm.body, cm.status, cm.created_at FROM chat_messages cm LEFT JOIN chat_conversation_visibility ccv ON ccv.user_id = :patient_id AND ccv.other_user_id = :fisio_id WHERE ((cm.from_id = :patient_id AND cm.to_id = :fisio_id) OR (cm.from_id = :fisio_id AND cm.to_id = :patient_id)) AND (ccv.user_id IS NULL OR cm.created_at > ccv.deleted_until) ORDER BY cm.created_at ASC");
    $stmt->execute([':patient_id' => $patient_id, ':fisio_id' => $fisio_id]); $messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return ['status' => 'success', 'messages' => $messages, 'fisio_last_seen' => $last_seen_status ];
}

/**
 * Envía un nuevo mensaje de chat de texto.
 */
function sendPatientChatMessage($db, $patient_id, $post_data) {
    $to_id = (int)($post_data['to_id'] ?? 0); $body = trim($post_data['message'] ?? ''); if ($to_id === 0 || empty($body)) throw new Exception("Destinatari o missatge no vàlids.");
    $stmt = $db->prepare("INSERT INTO chat_messages (from_id, to_id, body, status) VALUES (:from_id, :to_id, :body, 'sent')"); $stmt->execute([':from_id' => $patient_id, ':to_id' => $to_id, ':body' => $body]);
    return ['status' => 'success', 'message' => 'Missatge enviat.'];
}

/**
 * Sube un archivo de imagen al chat y lo registra en la BBDD.
 */
function uploadPatientChatImage($db, $patient_id, $post_data, $files_data) {
    $fisio_id = (int)($post_data['fisio_id'] ?? 0); if ($fisio_id === 0) { throw new Exception("Destinatari no vàlid."); }
    if (!isset($files_data['chat_image']) || $files_data['chat_image']['error'] !== UPLOAD_ERR_OK) { if (isset($files_data['chat_image']['error']) && $files_data['chat_image']['error'] === UPLOAD_ERR_INI_SIZE) { throw new Exception("L'arxiu és massa gran. Mida màxima: 2MB."); } throw new Exception("Error al rebre l'arxiu."); }
    $file = $files_data['chat_image']; $max_size = 2 * 1024 * 1024; if ($file['size'] > $max_size) { throw new Exception("L'arxiu és massa gran. Mida màxima: 2MB."); }
    $allowed_types = ['image/jpeg', 'image/png', 'image/gif']; if (!in_array($file['type'], $allowed_types)) { throw new Exception("Tipus d'arxiu no permès."); }
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION)); $new_filename = uniqid('chat_img_') . '.' . $ext; $upload_dir = __DIR__ . '/chat_img/'; $target_file = $upload_dir . $new_filename;
    if (!is_dir($upload_dir)) { if (!mkdir($upload_dir, 0777, true)) { throw new Exception("Error al crear directori."); } }
    if (!move_uploaded_file($file['tmp_name'], $target_file)) { throw new Exception("No s'ha pogut guardar l'arxiu."); }
    $message_body = 'img:' . $new_filename;
    // CAMBIO: datetime('now') -> NOW()
    $stmt = $db->prepare("INSERT INTO chat_messages (from_id, to_id, body, status, created_at) VALUES (:from_id, :to_id, :body, 'sent', NOW())"); $stmt->execute([':from_id' => $patient_id, ':to_id' => $fisio_id, ':body' => $message_body]); $new_message_id = $db->lastInsertId();
    return ['status' => 'success', 'message' => 'Imatge enviada.', 'tempId' => $post_data['tempId'], 'newId' => $new_message_id, 'body' => $message_body, 'created_at' => date('Y-m-d H:i:s')];
}

/**
 * Comprueba si hay nuevos mensajes del fisio o actualizaciones de estado.
 */
function checkPatientChatUpdates($db, $patient_id, $fisio_id) {
    if ($fisio_id === 0) throw new Exception("ID de fisio no vàlid.");
    $updateStmt = $db->prepare("UPDATE chat_messages SET status = 'delivered' WHERE from_id = :fisio_id AND to_id = :patient_id AND status = 'sent'"); $updateStmt->execute([':fisio_id' => $fisio_id, ':patient_id' => $patient_id]);
    $newMsgsStmt = $db->prepare("SELECT id, from_id, body, status, created_at FROM chat_messages WHERE from_id = :fisio_id AND to_id = :patient_id AND status = 'delivered' ORDER BY created_at ASC"); $newMsgsStmt->execute([':fisio_id' => $fisio_id, ':patient_id' => $patient_id]); $new_messages = $newMsgsStmt->fetchAll(PDO::FETCH_ASSOC);
    $statusUpdatesStmt = $db->prepare("SELECT id, status FROM chat_messages WHERE from_id = :patient_id AND to_id = :fisio_id AND status != 'sent'"); $statusUpdatesStmt->execute(['patient_id' => $patient_id, ':fisio_id' => $fisio_id]); $status_updates = $statusUpdatesStmt->fetchAll(PDO::FETCH_ASSOC);
    return ['status' => 'success', 'new_messages' => $new_messages, 'status_updates' => $status_updates];
}

/**
 * Elimina un mensaje de chat (solo si el paciente es el autor).
 * También elimina el archivo de imagen si corresponde.
 */
function deletePatientChatMessage($db, $patient_id, $post_data) {
    $message_id = (int)($post_data['message_id'] ?? 0); if ($message_id === 0) { throw new Exception("ID de missatge no vàlid."); }
    $stmt_check = $db->prepare("SELECT from_id, body FROM chat_messages WHERE id = ?"); $stmt_check->execute([$message_id]); $message = $stmt_check->fetch(PDO::FETCH_ASSOC);
    if (!$message) { throw new Exception("Missatge no trobat."); } if ($message['from_id'] != $patient_id) { throw new Exception("No tens permís per eliminar."); }
    $stmt_delete = $db->prepare("DELETE FROM chat_messages WHERE id = ?"); $stmt_delete->execute([$message_id]);
    if ($stmt_delete->rowCount() > 0) {
        $body = $message['body']; if (strpos($body, 'img:') === 0) { $filename = substr($body, 4); $upload_dir = __DIR__ . '/chat_img/'; $filepath = $upload_dir . $filename; if (file_exists($filepath)) { @unlink($filepath); } }
        return ['status' => 'success', 'message' => 'Imatge eliminada permanentment.'];
    } else { throw new Exception("No s'ha pogut eliminar (potser ja eliminat)."); }
}

/**
 * Oculta una conversación para el paciente.
 * Si el fisio ya la había ocultado, la elimina permanentemente (incluyendo archivos).
 */
function deletePatientConversation($db, $patient_id, $post_data) {
    $fisio_id = (int)($post_data['fisio_id'] ?? 0); if ($fisio_id === 0) { throw new Exception("ID de fisio no vàlid."); } if (!isset($patient_id) || empty($patient_id)) { throw new Exception("Error ID pacient."); }
    $stmt_check_other = $db->prepare("SELECT 1 FROM chat_conversation_visibility WHERE user_id = :fisio_id AND other_user_id = :patient_id"); $stmt_check_other->execute([':fisio_id' => $fisio_id, ':patient_id' => $patient_id]);

    if ($stmt_check_other->fetch()) {
        // El otro usuario ya la ocultó -> Borrado permanente
        $stmt_files = $db->prepare("SELECT body FROM chat_messages WHERE ((from_id = :patient_id AND to_id = :fisio_id) OR (from_id = :fisio_id AND to_id = :patient_id)) AND body LIKE 'img:%'"); $stmt_files->execute([':patient_id' => $patient_id, ':fisio_id' => $fisio_id]); $files_to_delete = $stmt_files->fetchAll(PDO::FETCH_COLUMN);
        $stmt_delete = $db->prepare("DELETE FROM chat_messages WHERE (from_id = :patient_id AND to_id = :fisio_id) OR (from_id = :fisio_id AND to_id = :patient_id)"); $stmt_delete->execute([':patient_id' => $patient_id, ':fisio_id' => $fisio_id]);
        $stmt_clear_visibility = $db->prepare("DELETE FROM chat_conversation_visibility WHERE (user_id = :patient_id AND other_user_id = :fisio_id) OR (user_id = :fisio_id AND other_user_id = :patient_id)"); $stmt_clear_visibility->execute([':patient_id' => $patient_id, ':fisio_id' => $fisio_id]);
        $upload_dir = __DIR__ . '/chat_img/'; foreach ($files_to_delete as $body_with_prefix) { $filename = substr($body_with_prefix, 4); $filepath = $upload_dir . $filename; if (file_exists($filepath)) { @unlink($filepath); } }
        return ['status' => 'success', 'message' => 'Conversa eliminada permanentment.'];

    } else {
        // Solo ocultar para el paciente

        // --- ★★★ INICIO CORRECCIÓN 2: ON CONFLICT (SQLite) -> ON DUPLICATE KEY UPDATE (MySQL) ★★★
        // (Asume que hay una Clave Única en las columnas user_id y other_user_id)
        $stmt = $db->prepare("
            INSERT INTO chat_conversation_visibility (user_id, other_user_id, deleted_until)
            VALUES (:user_id, :fisio_id, NOW())
            ON DUPLICATE KEY UPDATE deleted_until = NOW()
        ");
        // --- ★★★ FIN CORRECCIÓN 2 ★★★

        $stmt->execute([':user_id' => $patient_id, ':fisio_id' => $fisio_id]);
        return ['status' => 'success', 'message' => 'Historial de conversa ocultat per a tu.'];
    }
}

/**
 * Obtiene los anuncios activos y no vistos para el paciente.
 */
function getPatientAnnouncements($db, $patient_id, $user_rol) {
     if (!isset($patient_id)) { throw new Exception('Accés denegat.'); } $current_date = date('Y-m-d');
     // NOTA: Tu SQL original en patient_ajax.php tenía un filtro más restrictivo que el de patient_dashboard.php
     // He usado el de patient_ajax.php (línea 403) ya que es el archivo que estamos refactorizando.

     $sql = "SELECT a.id, a.title, a.content, a.message_type, a.is_blocking, c.nombre as creator_nombre, c.apellido as creator_apellido, c.rol as creator_rol FROM anuncios a LEFT JOIN cuentas c ON a.creator_id = c.id WHERE a.is_active = 1 AND (a.start_date IS NULL OR a.start_date <= :current_date) AND (a.end_date IS NULL OR a.end_date >= :current_date) AND NOT EXISTS (SELECT 1 FROM anuncio_vistos av WHERE av.anuncio_id = a.id AND av.user_id = :user_id) AND ( a.target_audience = 'all_users' OR (a.target_audience = 'all_pacientes' AND :user_role = 'paciente') OR (a.target_audience IN ('specific_pacientes') AND EXISTS (SELECT 1 FROM anuncio_recipients ar WHERE ar.anuncio_id = a.id AND ar.user_id = :user_id)) ) ORDER BY a.created_at DESC";

     $stmt = $db->prepare($sql); $stmt->execute([':current_date' => $current_date, ':user_id' => $patient_id, ':user_role' => $user_rol]); $announcements = $stmt->fetchAll(PDO::FETCH_ASSOC);
     $processed_announcements = []; foreach ($announcements as $a) { if ($a['creator_rol'] === 'superadmin') { $a['remitente_nombre'] = 'Administració'; } else if ($a['creator_rol'] === 'fisio') { $a['remitente_nombre'] = trim(($a['creator_nombre'] ?? '') . ' ' . ($a['apellido'] ?? '')); } else { $a['remitente_nombre'] = 'Sistema'; } $processed_announcements[] = $a; }
     return ['status' => 'success', 'announcements' => $processed_announcements];
}

/**
 * Marca un anuncio como visto por el paciente.
 */
function markPatientAnnouncementRead($db, $patient_id, $post_data) {
    if (!isset($patient_id)) { throw new Exception('Accés denegat.'); } $anuncio_id = (int)($post_data['anuncio_id'] ?? 0); if ($anuncio_id <= 0) { throw new Exception('ID d\'anunci no vàlid.'); }

    // --- ★★★ INICIO CORRECCIÓN 1: "INSERT OR IGNORE" (SQLite) -> "INSERT IGNORE" (MySQL) ★★★
    $stmt = $db->prepare("INSERT IGNORE INTO anuncio_vistos (anuncio_id, user_id, dismissed_at) VALUES (?, ?, NOW())");
    // --- ★★★ FIN CORRECCIÓN 1 ★★★

    $stmt->execute([$anuncio_id, $patient_id]);
    return ['status' => 'success'];
}


// =======================================================
// --- NUEVAS FUNCIONES MOVIDAS DESDE fitxa_pacient.php ---
// =======================================================

 function getFisioDependencies($db) {
     $fisiosStmt = $db->query("SELECT id, nombre, apellido, rol FROM cuentas WHERE rol IN ('fisio', 'superadmin') AND is_archived = 0 ORDER BY apellido, nombre");
     $fisios = $fisiosStmt->fetchAll(PDO::FETCH_ASSOC);

     // --- ¡AQUÍ ESTÁ LA CORRECCIÓN! ---
     $catsStmt = $db->query("SELECT id, name, parent_id FROM categorias ORDER BY parent_id, name");
     $categories = $catsStmt->fetchAll(PDO::FETCH_ASSOC);

     $tagsStmt = $db->query("SELECT id, name, parent_id FROM tags ORDER BY parent_id, name");
     $tags = $tagsStmt->fetchAll(PDO::FETCH_ASSOC);

     return ['status' => 'success', 'fisios' => $fisios, 'categories' => $categories, 'tags' => $tags];
 }

/**
 * Obtiene la lista de tratamientos (activos e históricos) para un paciente específico.
 * (Movido desde fitxa_pacient.php, case 'get_patient_treatments')
 */
function getPatientTreatments($db, $pacient_id, $current_fisio_id, $user_rol) {
    $sql = "
        SELECT t.id, t.title, t.status, t.start_date, t.end_date, t.creator_fisio_id, t.realizacion_percent,
               c.nombre as creator_name, c.apellido as creator_surname,
               (SELECT GROUP_CONCAT(tfa_inner.fisio_id)
                FROM tratamiento_fisios_asignados tfa_inner
                WHERE tfa_inner.tratamiento_id = t.id
               ) as collaborator_ids,
               (SELECT v.filename FROM tratamiento_ejercicios te JOIN ejercicios e ON te.ejercicio_id = e.id LEFT JOIN videos v ON e.id_video = v.id WHERE te.tratamiento_id = t.id ORDER BY te.orden ASC, te.id ASC LIMIT 1) as first_video_filename,
               (SELECT i.filename FROM tratamiento_ejercicios te JOIN ejercicios e ON te.ejercicio_id = e.id LEFT JOIN images i ON e.id_image = i.id WHERE te.tratamiento_id = t.id ORDER BY te.orden ASC, te.id ASC LIMIT 1) as first_image_filename
        FROM tratamientos t
        JOIN cuentas c ON t.creator_fisio_id = c.id
        LEFT JOIN tratamiento_fisios_asignados tfa ON t.id = tfa.tratamiento_id
        WHERE t.paciente_id = :pacient_id
          AND t.is_protocol = 0
          AND NOT EXISTS (
              SELECT 1 FROM archivado_personal ap
              WHERE ap.item_id = t.id AND ap.item_type = 'treatment' AND ap.fisio_id = :current_fisio_id
          )
    ";

    if ($user_rol !== 'superadmin') {
        $sql .= " AND (t.creator_fisio_id = :current_fisio_id OR tfa.fisio_id = :current_fisio_id) ";
    }

    $sql .= " GROUP BY t.id ORDER BY t.start_date DESC ";
    $stmt = $db->prepare($sql);
    $stmt->execute([
        ':pacient_id' => $pacient_id,
        ':current_fisio_id' => $current_fisio_id
    ]);
    $all_treatments = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $active_treatments = [];
    $historical_treatments = [];
    foreach ($all_treatments as $treatment) {
        if (in_array($treatment['status'], ['En curs', 'Programat'])) {
            $active_treatments[] = $treatment;
        } else {
            $historical_treatments[] = $treatment;
        }
    }

    return ['status' => 'success', 'active' => $active_treatments, 'historical' => $historical_treatments];
}

/**
 * Obtiene los detalles de un protocolo para asignarlo.
 * (Movido desde fitxa_pacient.php, case 'get_protocol_details_for_assignment')
 */
function getProtocolDetailsForAssignment($db, $protocol_id) {
    if ($protocol_id === 0) { throw new Exception('ID de protocol no vàlid.'); }

    $protocolStmt = $db->prepare("SELECT title, anamnesis, diagnostico FROM tratamientos WHERE id = ? AND is_protocol = 1");
    $protocolStmt->execute([$protocol_id]);
    $protocol = $protocolStmt->fetch(PDO::FETCH_ASSOC); // Corregido
    if (!$protocol) { throw new Exception('Protocol no vàlid.'); }

    $exercisesStmt = $db->prepare("
        SELECT e.*, te.frecuencia, te.series, te.repetitions, te.rest_time, te.notes as pauta_notes, v.filename as video_filename, i.filename as image_filename
        FROM tratamiento_ejercicios te
        JOIN ejercicios e ON te.ejercicio_id = e.id
        LEFT JOIN videos v ON e.id_video = v.id
        LEFT JOIN images i ON e.id_image = i.id
        WHERE te.tratamiento_id = ?
    ");
    $exercisesStmt->execute([$protocol_id]);
    $protocol['assigned_exercises'] = $exercisesStmt->fetchAll(PDO::FETCH_ASSOC); // Corregido
    return ['status' => 'success', 'protocol' => $protocol];
}


/**
 * Obtiene un resumen de la evolución (adherencia, tiempo, etc.).
 * (Movido desde fitxa_pacient.php, case 'get_evolution_overview')
 */
function getEvolutionOverview($db, $pacient_id, $treatment_id) {
    if ($treatment_id === 0) { throw new Exception('ID de tractament no proporcionat.'); }

    $today_str = date('Y-m-d');
    $today_dt = new DateTime('now');

    $stmt_check = $db->prepare( "SELECT id, title, start_date, end_date FROM tratamientos WHERE id = :treatment_id AND paciente_id = :pacient_id LIMIT 1" );
    $stmt_check->execute([':treatment_id' => $treatment_id, ':pacient_id' => $pacient_id]);
    $treatmentDetails = $stmt_check->fetch(PDO::FETCH_ASSOC);
    if (!$treatmentDetails) { throw new Exception('Tractament no trobat per a este pacient.'); }

    // --- Cálculo Adherencia (Lógica duplicada, idealmente refactorizar a una sola función) ---
    $ex_stmt = $db->prepare( "SELECT te.id, te.ejercicio_id, te.frecuencia FROM tratamiento_ejercicios te WHERE te.tratamiento_id = ? ORDER BY te.id" );
    $ex_stmt->execute([$treatment_id]); $exercises = $ex_stmt->fetchAll(PDO::FETCH_ASSOC);

    $stmt_evo = $db->prepare( "SELECT DISTINCT ev.fecha_realizacion, te.id as tratamiento_ejercicio_id FROM tratamiento_evolucion ev JOIN tratamiento_ejercicios te ON ev.tratamiento_ejercicio_id = te.id WHERE te.tratamiento_id = ?" );
    $stmt_evo->execute([$treatment_id]); $all_evolution_records = $stmt_evo->fetchAll(PDO::FETCH_ASSOC);

    $evolution_map = []; foreach ($all_evolution_records as $rec) { $evolution_map[$rec['fecha_realizacion']][$rec['tratamiento_ejercicio_id']] = true; }

    $total_scheduled = 0; $total_completed = 0;
    $progress_percent_adherence = 0;
     try {
         $loop_start_date = new DateTimeImmutable($treatmentDetails['start_date']);
         $loop_end_date_treatment_str = $treatmentDetails['end_date'] ?? date('Y-m-d');
         $loop_end_date_treatment = new DateTimeImmutable($loop_end_date_treatment_str);
         $loop_end_date = ($today_dt < $loop_end_date_treatment) ? $today_dt : $loop_end_date_treatment;
         $current_loop_date = $loop_start_date;

         while ($current_loop_date <= $loop_end_date) {
            $dateStr = $current_loop_date->format('Y-m-d'); $dayOfWeek = (int)$current_loop_date->format('w');
            foreach ($exercises as $ex) {
                $is_scheduled = false; $freq = $ex['frecuencia'];
                if (!$freq || $freq === 'Diari') { $is_scheduled = true; }
                else if ($freq === '3xSetmana' && in_array($dayOfWeek, [1, 3, 5])) { $is_scheduled = true; }
                else if ($freq === '2xSetmana' && in_array($dayOfWeek, [2, 4])) { $is_scheduled = true; }
                else if ($freq === 'Altern') { $dayDiff = $loop_start_date->diff($current_loop_date)->days; if ($dayDiff >= 0 && $dayDiff % 2 === 0) { $is_scheduled = true; } }
                else { $is_scheduled = true; }
                if ($is_scheduled) { $total_scheduled++; if (isset($evolution_map[$dateStr][$ex['id']])) { $total_completed++; } }
            }
            $current_loop_date = $current_loop_date->modify('+1 day');
         }
         $progress_percent_adherence = ($total_scheduled > 0) ? round(($total_completed / $total_scheduled) * 100) : 0;
     } catch (Exception $e) { error_log("Error calculando adherencia para t_id $treatment_id: " . $e->getMessage()); }
    // --- Fin Cálculo Adherencia ---

    // --- Cálculo Tiempo ---
    $progress_percent_time = 0;
    try {
        $start = new DateTime($treatmentDetails['start_date']);
        $end = new DateTime($treatmentDetails['end_date']);
        $total_duration = $start->diff($end)->days;
        if ($total_duration >= 0) {
             $days_passed = 0;
             if ($today_dt >= $start) {
                 if ($today_dt > $end) { $days_passed = $total_duration; }
                 else { $interval = $start->diff($today_dt); $days_passed = $interval->days + (($interval->h > 0 || $interval->i > 0 || $interval->s > 0) ? 1:0); $days_passed = min($days_passed, $total_duration + 1); }
             }
             if ($total_duration == 0) { $progress_percent_time = ($today_dt >= $start) ? 100 : 0; }
             else { $progress_percent_time = ($days_passed / ($total_duration + 1)) * 100; }
            if ($progress_percent_time > 100) $progress_percent_time = 100; if ($progress_percent_time < 0) $progress_percent_time = 0;
        }
        $progress_percent_time = round($progress_percent_time);
    } catch (Exception $e) { $progress_percent_time = 0; error_log("Error calculant progress_time: ".$e->getMessage()); }
    // --- Fin Cálculo Tiempo ---

    $ex_details_stmt = $db->prepare( "SELECT e.id as ejercicio_id, e.title, te.id, te.frecuencia, te.series, te.repetitions, te.rest_time, te.notes FROM tratamiento_ejercicios te JOIN ejercicios e ON te.ejercicio_id = e.id WHERE te.tratamiento_id = ? ORDER BY te.id" );
    $ex_details_stmt->execute([$treatment_id]); $exercises_details = $ex_details_stmt->fetchAll(PDO::FETCH_ASSOC);

    return ['status' => 'success', 'treatment' => $treatmentDetails, 'exercises' => $exercises_details, 'progress_percent_time' => $progress_percent_time, 'progress_percent_adherence' => $progress_percent_adherence ];
}


// --- ======================================================= ---
// --- ★★★ INICIO: FUNCIÓN DE ADHERENCIA AÑADIDA ★★★ ---
// --- ======================================================= ---

/**
 * Calcula el percentatge d'adherència per a un tractament donat.
 * FUNCIÓN GLOBAL (Copiada de treatments.php y corregida)
 *
 * @param int $treatment_id L'ID del tractament.
 * @param PDO $db La connexió a la base de dades.
 * @return int El percentatge d'adherència (0-100).
 */
function calculate_adherence_percentage(int $treatment_id, PDO $db): int {
    try {
        // --- ★★★ CORRECCIÓN: ORDENAR POR 'orden' Y 'id' ★★★ ---
        $ex_stmt = $db->prepare("
            SELECT te.id, te.ejercicio_id, te.frecuencia, t.start_date, t.end_date
            FROM tratamiento_ejercicios te
            JOIN tratamientos t ON te.tratamiento_id = t.id
            WHERE te.tratamiento_id = ? ORDER BY te.orden ASC, te.id ASC
        ");
        $ex_stmt->execute([$treatment_id]);
        $exercises_with_dates = $ex_stmt->fetchAll(PDO::FETCH_ASSOC);

        if (empty($exercises_with_dates)) {
            return 0; // Si no hi ha exercicis, l'adherència és 0.
        }

        $treatmentDetails = [
            'start_date' => $exercises_with_dates[0]['start_date'],
            'end_date' => $exercises_with_dates[0]['end_date']
        ];
        $exercises = $exercises_with_dates;

        $stmt_evo = $db->prepare("
            SELECT DISTINCT ev.fecha_realizacion, te.id as tratamiento_ejercicio_id
            FROM tratamiento_evolucion ev
            JOIN tratamiento_ejercicios te ON ev.tratamiento_ejercicio_id = te.id
            WHERE te.tratamiento_id = ?
        ");
        $stmt_evo->execute([$treatment_id]);
        $all_evolution_records = $stmt_evo->fetchAll(PDO::FETCH_ASSOC);

        $evolution_map = [];
        foreach ($all_evolution_records as $rec) {
            $evolution_map[$rec['fecha_realizacion']][$rec['tratamiento_ejercicio_id']] = true;
        }

        $total_scheduled = 0;
        $total_completed = 0;

        $loop_start_date = new DateTimeImmutable($treatmentDetails['start_date']);
        $loop_end_date_treatment = new DateTimeImmutable($treatmentDetails['end_date']);
        $today = new DateTimeImmutable('now');

        // Usar la fecha de fin o el día de hoy (lo que sea anterior)
        $loop_end_date = ($today < $loop_end_date_treatment) ? $today : $loop_end_date_treatment;

        $current_loop_date = $loop_start_date;

        while ($current_loop_date <= $loop_end_date) {
            $dateStr = $current_loop_date->format('Y-m-d');
            $dayOfWeek = (int)$current_loop_date->format('w'); // 0=Diumenge, 1=Dilluns...

            foreach ($exercises as $ex) {
                $is_scheduled = false;
                $freq = $ex['frecuencia'];

                if (!$freq || $freq === 'Diari') { $is_scheduled = true; }
                else if ($freq === '3xSetmana' && in_array($dayOfWeek, [1, 3, 5])) { $is_scheduled = true; }
                else if ($freq === '2xSetmana' && in_array($dayOfWeek, [2, 4])) { $is_scheduled = true; }
                else if ($freq === 'Altern') {
                    $dayDiff = $loop_start_date->diff($current_loop_date)->days;
                    if ($dayDiff >= 0 && $dayDiff % 2 === 0) { $is_scheduled = true; }
                } else {
                    $is_scheduled = true;
                }

                if ($is_scheduled) {
                    $total_scheduled++;
                    if (isset($evolution_map[$dateStr][$ex['id']])) {
                        $total_completed++;
                    }
                }
            }
            $current_loop_date = $current_loop_date->modify('+1 day');
        }

        return ($total_scheduled > 0) ? (int)round(($total_completed / $total_scheduled) * 100) : 0;

    } catch (Exception $e) {
        error_log("Error calculant adherència per a treatment ID $treatment_id: " . $e->getMessage());
        return 0; // Retorna 0 en cas d'error.
    }
}
// --- ======================================================= ---
// --- ★★★ FIN: FUNCIÓN DE ADHERENCIA AÑADIDA ★★★ ---
// --- ======================================================= ---
?>
