    <?php
    require_once 'database.php';

    function authenticateUser($username, $password) {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("SELECT * FROM users WHERE username = :username");
        $stmt->execute([':username' => $username]);
        $user = $stmt->fetch();
        
        if ($user && password_verify($password, $user['password'])) {
            return [
                'id' => $user['id'],
                'username' => $user['username'],
                'name' => $user['name'],
                'type' => $user['type'],
                'office_id' => $user['office_id']
            ];
        }
        
        return false;
    }

    function isLoggedIn() {
        return isset($_SESSION['user']);
    }

    function requireLogin() {
        if (!isLoggedIn()) {
            header("Location: ../index.php");
            exit();
        }
    }

    function redirectByUserType($user) {
        if (!isset($user['type'])) {
            header("Location: index.php");
            exit();
        }

        switch ($user['type']) {
            case 'admin':
                header("Location: admin/index.php");
                break;
            case 'doctor':
                header("Location: medico/index.php");
                break;
            default:
                header("Location: index.php");
        }
        exit();
    }

    // Novas funções úteis para o banco de dados
    function getSpecialties() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM specialties ORDER BY name");
        return $stmt->fetchAll();
    }

    function getOffices() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM offices ORDER BY name");
        return $stmt->fetchAll();
    }

    function getProcedures() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM procedures ORDER BY name");
        return $stmt->fetchAll();
    }

    function getPaymentMethods() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM payment_methods ORDER BY name");
        return $stmt->fetchAll();
    }

    function getDoctorById($id) {
        $db = Database::getInstance()->getConnection();
        
        // Buscar dados do médico
        $sql = "SELECT d.*, s.name as specialty_name, o.name as office_name
                FROM doctors d
                LEFT JOIN specialties s ON s.id = d.specialty_id
                LEFT JOIN offices o ON o.id = d.office_id
                WHERE d.id = :id";
                
        $stmt = $db->prepare($sql);
        $stmt->execute([':id' => $id]);
        
        $doctor = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($doctor) {
            // Buscar procedimentos do médico
            $stmt = $db->prepare("
                SELECT procedure_id 
                FROM doctor_procedures 
                WHERE doctor_id = :doctor_id
            ");
            $stmt->execute([':doctor_id' => $id]);
            
            $doctor['procedures'] = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'procedure_id');
        }
        
        return $doctor;
    }

    function saveDoctor($doctorData, $userData) {
        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Usar ID existente ou gerar novo
            $id = $doctorData['id'] ?? generateUUID();
            
            // Se for um novo registro, verificar se o username é 'admin'
            if (empty($doctorData['id'])) {
                if (strtolower($userData['username']) === 'admin') {
                    return [
                        'success' => false,
                        'message' => "O nome de usuário 'admin' é reservado. Por favor, escolha outro.",
                        'data' => $doctorData // Retorna os dados para manter o formulário preenchido
                    ];
                }
                
                // Verificar se username já existe
                $stmt = $db->prepare("SELECT COUNT(*) FROM users WHERE username = :username");
                $stmt->execute([':username' => $userData['username']]);
                if ($stmt->fetchColumn() > 0) {
                    return [
                        'success' => false,
                        'message' => "Nome de usuário já está em uso. Por favor, escolha outro.",
                        'data' => $doctorData // Retorna os dados para manter o formulário preenchido
                    ];
                }
            }
            
            // Salvar usuário
            if (!empty($doctorData['id'])) {
                // Update
                $sql = "UPDATE users SET name = :name, office_id = :office_id";
                $params = [
                    ':id' => $id,
                    ':name' => $userData['name'],
                    ':office_id' => $userData['office_id'] ?? null
                ];
                
                // Adiciona password apenas se foi fornecido
                if (!empty($userData['password'])) {
                    $sql .= ", password = :password";
                    $params[':password'] = password_hash($userData['password'], PASSWORD_DEFAULT);
                }
                
                $sql .= " WHERE id = :id";
                
            } else {
                // Insert (novo médico)
                $sql = "INSERT INTO users (id, username, password, name, type, office_id)
                        VALUES (:id, :username, :password, :name, 'doctor', :office_id)";
                $params = [
                    ':id' => $id,
                    ':username' => $userData['username'],
                    ':password' => password_hash($userData['password'], PASSWORD_DEFAULT),
                    ':name' => $userData['name'],
                    ':office_id' => $userData['office_id'] ?? null
                ];
            }
            
            $stmt = $db->prepare($sql);
            if (!$stmt->execute($params)) {
                throw new Exception("Erro ao salvar usuário");
            }
            
            // Salvar médico
            if (!empty($doctorData['id'])) {
                // Update
                $sql = "UPDATE doctors SET 
                    name = :name,
                    specialty_id = :specialty_id,
                    whatsapp = :whatsapp,
                    email = :email,
                    office_id = :office_id,
                    consultation_value = :consultation_value,
                    consultation_commission = :consultation_commission
                    WHERE id = :id";
            } else {
                // Insert
                $sql = "INSERT INTO doctors (
                    id, name, specialty_id, whatsapp, email, office_id, 
                    consultation_value, consultation_commission
                ) VALUES (
                    :id, :name, :specialty_id, :whatsapp, :email, :office_id,
                    :consultation_value, :consultation_commission
                )";
            }
            
            $params = [
                ':id' => $id,
                ':name' => $doctorData['name'],
                ':specialty_id' => $doctorData['specialty_id'],
                ':whatsapp' => preg_replace('/[^0-9]/', '', $doctorData['whatsapp']),
                ':email' => $doctorData['email'],
                ':office_id' => $doctorData['office_id'],
                ':consultation_value' => str_replace(['R$', '.', ','], ['', '', '.'], $doctorData['consultation_value']),
                ':consultation_commission' => str_replace(['R$', '.', ','], ['', '', '.'], $doctorData['consultation_commission'])
            ];
            
            $stmt = $db->prepare($sql);
            if (!$stmt->execute($params)) {
                throw new Exception("Erro ao salvar médico");
            }
            
            // Salvar procedimentos
            if (isset($doctorData['procedures'])) {
                $stmt = $db->prepare("DELETE FROM doctor_procedures WHERE doctor_id = :doctor_id");
                $stmt->execute([':doctor_id' => $id]);
                
                if (!empty($doctorData['procedures'])) {
                    $stmt = $db->prepare("
                        INSERT INTO doctor_procedures (doctor_id, procedure_id) 
                        VALUES (:doctor_id, :procedure_id)
                    ");
                    
                    foreach ($doctorData['procedures'] as $procedure_id) {
                        if (!empty($procedure_id)) {
                            $stmt->execute([
                                ':doctor_id' => $id,
                                ':procedure_id' => $procedure_id
                            ]);
                        }
                    }
                }
            }
            
            $db->commit();
            return ['success' => true];
            
        } catch (Exception $e) {
            $db->rollBack();
            error_log("Erro ao salvar médico: " . $e->getMessage());
            return [
                'success' => false,
                'message' => "Erro ao salvar médico. Por favor, tente novamente."
            ];
        }
    }

    function getAllDoctors() {
        $db = Database::getInstance()->getConnection();
        
        // Buscar médicos com suas informações básicas
        $sql = "SELECT d.*, s.name as specialty_name, o.name as office_name
                FROM doctors d 
                LEFT JOIN specialties s ON s.id = d.specialty_id
                LEFT JOIN offices o ON o.id = d.office_id 
                ORDER BY d.name";
                
        $stmt = $db->query($sql);
        $doctors = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Para cada médico, buscar seus procedimentos
        foreach ($doctors as &$doctor) {
            $stmt = $db->prepare("
                SELECT p.name 
                FROM doctor_procedures dp
                JOIN procedures p ON p.id = dp.procedure_id
                WHERE dp.doctor_id = :doctor_id
                ORDER BY p.name
            ");
            $stmt->execute([':doctor_id' => $doctor['id']]);
            $procedures = $stmt->fetchAll(PDO::FETCH_COLUMN);
            $doctor['procedures_list'] = implode(", ", $procedures);
        }
        
        return $doctors;
    }

    function getAllProcedures() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM procedures ORDER BY name");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function getAllPaymentMethods() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM payment_methods ORDER BY name");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function searchPatients($search) {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("SELECT id, name, cpf FROM patients_data 
                            WHERE name LIKE :search OR cpf LIKE :search 
                            ORDER BY name LIMIT 10");
        $stmt->execute([':search' => "%$search%"]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function createAppointment($data) {
        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Validações básicas
            if (empty($data['patient_id']) || empty($data['doctor_id']) || 
                empty($data['date']) || empty($data['time'])) {
                throw new Exception("Campos obrigatórios não preenchidos");
            }
            
            $sql = "INSERT INTO appointments (
                id, patient_id, doctor_id, office_id, procedure_id, 
                date, time, status, payment_method_id, value, notes
            ) VALUES (
                :id, :patient_id, :doctor_id, :office_id, :procedure_id,
                :date, :time, :status, :payment_method_id, :value, :notes
            )";
            
            $stmt = $db->prepare($sql);
            $result = $stmt->execute($data);
            
            if (!$result) {
                throw new Exception("Erro ao criar agendamento");
            }
            
            $db->commit();
            return true;
            
        } catch (Exception $e) {
            $db->rollBack();
            error_log("Erro ao criar agendamento: " . $e->getMessage());
            throw $e;
        }
    }

    function generateUUID() {
        return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0, 0xffff), mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0x0fff) | 0x4000,
            mt_rand(0, 0x3fff) | 0x8000,
            mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
        );
    }

    function getWaitingPatients($office_id = null) {
        $db = Database::getInstance()->getConnection();
        
        $sql = "
            SELECT p.*, o.name as office_name, pr.name as procedure_name
            FROM patients p
            LEFT JOIN offices o ON o.id = p.office_id
            LEFT JOIN procedures pr ON pr.id = p.procedure_id
            WHERE p.called = 0
        ";
        
        if ($office_id && $office_id !== 'all') {
            $sql .= " AND p.office_id = :office_id";
        }
        
        $sql .= " ORDER BY 
            CASE p.priority 
                WHEN 'Alta' THEN 1 
                WHEN 'Media' THEN 2 
                ELSE 3 
            END,
            p.arrival_time ASC";
        
        $stmt = $office_id && $office_id !== 'all' 
            ? $db->prepare($sql) 
            : $db->query($sql);
        
        if ($office_id && $office_id !== 'all') {
            $stmt->execute([':office_id' => $office_id]);
        }
        
        return $stmt->fetchAll();
    }

    function getCalledPatients($office_id = null) {
        $db = Database::getInstance()->getConnection();
        
        $sql = "
            SELECT p.*, o.name as office_name, pr.name as procedure_name
            FROM patients p
            LEFT JOIN offices o ON o.id = p.office_id
            LEFT JOIN procedures pr ON pr.id = p.procedure_id
            WHERE p.called = 1
            AND DATE(p.created_at) = CURDATE()
        ";
        
        if ($office_id && $office_id !== 'all') {
            $sql .= " AND p.office_id = :office_id";
        }
        
        $sql .= " ORDER BY p.called_time DESC";
        
        $stmt = $office_id && $office_id !== 'all' 
            ? $db->prepare($sql) 
            : $db->query($sql);
        
        if ($office_id && $office_id !== 'all') {
            $stmt->execute([':office_id' => $office_id]);
        }
        
        return $stmt->fetchAll();
    }

    function addPatient($data) {
        $db = Database::getInstance();
        
        $data['id'] = uuid();
        $data['arrival_time'] = date('H:i:s');
        $data['called'] = 0;
        $data['displayed'] = 0;
        
        try {
            return $db->insert('patients', $data);
        } catch (Exception $e) {
            error_log($e->getMessage());
            return false;
        }
    }

    function callPatient($id) {
        $db = Database::getInstance()->getConnection();
        
        try {
            $stmt = $db->prepare("SELECT arrival_time FROM patients WHERE id = :id");
            $stmt->execute([':id' => $id]);
            $patient = $stmt->fetch();
            
            if (!$patient) return false;
            
            return $db->update('patients', 
                [
                    'called' => 1,
                    'called_time' => date('H:i:s'),
                    'waiting_time' => calculateWaitingTime($patient['arrival_time'], date('H:i:s'))
                ],
                ['id' => $id]
            );
        } catch (Exception $e) {
            error_log($e->getMessage());
            return false;
        }
    }

    function deletePatient($id) {
        $db = Database::getInstance()->getConnection();
        
        try {
            return $db->delete('patients', ['id' => $id]);
        } catch (Exception $e) {
            error_log($e->getMessage());
            return false;
        }
    }

    function clearAllPatients() {
        $db = Database::getInstance()->getConnection();
        
        try {
            return $db->query("DELETE FROM patients WHERE called = 1");
        } catch (Exception $e) {
            error_log($e->getMessage());
            return false;
        }
    }

    function calculateWaitingTime($arrival_time, $called_time) {
        $arrival = strtotime($arrival_time);
        $called = strtotime($called_time);
        $diff = $called - $arrival;
        
        $hours = floor($diff / 3600);
        $mins = floor(($diff - ($hours * 3600)) / 60);
        $secs = $diff - ($hours * 3600) - ($mins * 60);
        
        return sprintf("%02d:%02d:%02d", $hours, $mins, $secs);
    }

    function cleanMonetaryValue($value) {
        // Remove R$, espaços e pontos, e substitui vírgula por ponto
        $clean = str_replace(['R$', ' ', '.'], '', $value);
        $clean = str_replace(',', '.', $clean);
        
        // Converte para float
        $number = (float) $clean;
        
        return $number;
    }

    function getPriorityClass($priority) {
        switch ($priority) {
            case 'Alta':
                return 'text-red-600 font-bold';
            case 'Media':
                return 'text-yellow-600';
            default:
                return 'text-green-600';
        }
    }

    /**
     * Formata um valor monetário para exibição
     */
    function formatMoney($value) {
        return 'R$ ' . number_format($value, 2, ',', '.');
    }

    /**
     * Converte um valor monetário de string para float
     */
    function unformatMoney($value) {
        return (float) str_replace(
            ['R$', ' ', '.', ','], 
            ['', '', '', '.'], 
            $value
        );
    }

    /**
     * Formata uma porcentagem para exibição
     */
    function formatPercent($value) {
        return number_format($value, 2, ',', '.') . '%';
    }

    /**
     * Converte uma porcentagem de string para float
     */
    function unformatPercent($value) {
        return (float) str_replace(
            ['%', ' ', '.', ','], 
            ['', '', '', '.'], 
            $value
        );
    }

    // Adicionar esta função como alias
    function formatPercentage($value) {
        return formatPercent($value);
    }

    // Adicionar esta função junto com as outras funções de get
    function getAttendanceTypes() {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->query("SELECT * FROM attendance_types ORDER BY name");
        return $stmt->fetchAll();
    }

    /**
     * Busca dados do endereço via CEP usando a API ViaCEP
     */
    function getAddressByCep($cep) {
        $cep = preg_replace('/[^0-9]/', '', $cep);
        
        try {
            $url = "https://viacep.com.br/ws/{$cep}/json/";
            $response = file_get_contents($url);
            $data = json_decode($response, true);
            
            if (isset($data['erro'])) {
                return false;
            }
            
            return [
                'cep' => $data['cep'],
                'street' => $data['logradouro'],
                'neighborhood' => $data['bairro'],
                'city' => $data['localidade'],
                'state' => $data['uf'],
                'complement' => $data['complemento']
            ];
        } catch (Exception $e) {
            error_log($e->getMessage());
            return false;
        }
    }

    /**
     * Salva ou atualiza dados do paciente
     */
    function savePatientData($data) {
        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Verificar CPF duplicado (apenas para novos registros ou se o CPF mudou)
            $cpf = preg_replace('/[^0-9]/', '', $data['cpf']);
            if ($cpf) {
                $existing = checkCPFExists($cpf);
                if ($existing && (!isset($data['id']) || $existing['id'] !== $data['id'])) {
                    throw new Exception("CPF já cadastrado para o paciente: " . $existing['name']);
                }
            }
            
            // Limpar e formatar dados
            $data['cpf'] = preg_replace('/[^0-9]/', '', $data['cpf']);
            $data['cep'] = preg_replace('/[^0-9]/', '', $data['cep']);
            $data['whatsapp'] = preg_replace('/[^0-9]/', '', $data['whatsapp']);
            $data['phone'] = preg_replace('/[^0-9]/', '', $data['phone']);
            $data['phone2'] = preg_replace('/[^0-9]/', '', $data['phone2']);
            
            if (isset($data['id'])) {
                // Update
                $sql = "UPDATE patients_data SET 
                    name = :name, cpf = :cpf, rg = :rg, birth_date = :birth_date,
                    gender = :gender, email = :email, whatsapp = :whatsapp,
                    phone = :phone, phone2 = :phone2, cep = :cep, street = :street,
                    number = :number, complement = :complement, neighborhood = :neighborhood,
                    city = :city, state = :state, notes = :notes, status = :status
                    WHERE id = :id";
            } else {
                // Insert (mantém o código existente)
                $data['id'] = uuid();
                $sql = "INSERT INTO patients_data (
                    id, name, cpf, rg, birth_date, gender, email, whatsapp, phone, phone2,
                    cep, street, number, complement, neighborhood, city, state, notes, status
                ) VALUES (
                    :id, :name, :cpf, :rg, :birth_date, :gender, :email, :whatsapp, :phone, :phone2,
                    :cep, :street, :number, :complement, :neighborhood, :city, :state, :notes, :status
                )";
            }
            
            $stmt = $db->prepare($sql);
            $result = $stmt->execute($data);
            
            if (!$result) {
                throw new Exception("Erro ao executar query");
            }
            
            $db->commit();
            return $data['id'];
            
        } catch (Exception $e) {
            $db->rollBack();
            error_log("Erro ao salvar paciente: " . $e->getMessage());
            return false;
        }
    }

    function fetchCEP($cep) {
        $cep = preg_replace('/[^0-9]/', '', $cep);
        $url = "https://viacep.com.br/ws/{$cep}/json/";
        
        $ch = curl_init();
        curl_setopt_array($ch, [
            CURLOPT_URL => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_SSL_VERIFYPEER => false,
            CURLOPT_TIMEOUT => 5,
            CURLOPT_HTTPHEADER => [
                'Accept: application/json',
                'Content-Type: application/json'
            ]
        ]);
        
        $response = curl_exec($ch);
        $error = curl_error($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);
        
        if ($error) {
            error_log("Erro CURL ao buscar CEP: " . $error);
            throw new Exception("Erro ao buscar CEP");
        }
        
        if ($httpCode !== 200) {
            error_log("Erro HTTP ao buscar CEP: " . $httpCode);
            throw new Exception("Erro ao buscar CEP");
        }
        
        $data = json_decode($response, true);
        
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log("Erro ao decodificar JSON do CEP: " . json_last_error_msg());
            throw new Exception("Erro ao processar resposta do CEP");
        }
        
        return $data;
    }

    function checkCPFExists($cpf) {
        $db = Database::getInstance()->getConnection();
        $cpf = preg_replace('/[^0-9]/', '', $cpf);
        
        $stmt = $db->prepare("SELECT id, name FROM patients_data WHERE cpf = :cpf");
        $stmt->execute([':cpf' => $cpf]);
        
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    function getAllPatients($search = '') {
        $db = Database::getInstance()->getConnection();
        
        $sql = "SELECT id, name, whatsapp, birth_date, status FROM patients_data WHERE 1=1";
        
        $params = [];
        if ($search) {
            $sql .= " AND (name LIKE :search_name OR cpf LIKE :search_cpf)";
            $params[':search_name'] = "%$search%";
            $params[':search_cpf'] = "%$search%";
        }
        
        $sql .= " ORDER BY name ASC";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function formatPhone($phone) {
        $phone = preg_replace('/[^0-9]/', '', $phone);
        
        if (strlen($phone) === 11) {
            return '(' . substr($phone, 0, 2) . ') ' . 
                substr($phone, 2, 5) . '-' . 
                substr($phone, 7);
        } elseif (strlen($phone) === 10) {
            return '(' . substr($phone, 0, 2) . ') ' . 
                substr($phone, 2, 4) . '-' . 
                substr($phone, 6);
        }
        
        return $phone;
    }

    function getPatientById($id) {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("SELECT * FROM patients_data WHERE id = :id");
        $stmt->execute([':id' => $id]);
        
        $patient = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Debug para verificar os dados retornados
        error_log('Patient data: ' . print_r($patient, true));
        
        return $patient;
    }

    function updatePatient($data) {
        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Debug dos dados recebidos
            error_log('Dados recebidos para atualização: ' . print_r($data, true));
            
            // Validações básicas
            if (empty($data['name']) || empty($data['birth_date']) || empty($data['gender'])) {
                throw new Exception("Campos obrigatórios não preenchidos");
            }
            
            // Limpar e formatar dados
            $params = [
                ':id' => $data['id'],
                ':name' => $data['name'],
                ':rg' => preg_replace('/[^0-9]/', '', $data['rg']),
                ':birth_date' => $data['birth_date'],
                ':gender' => $data['gender'],
                ':email' => $data['email'],
                ':whatsapp' => preg_replace('/[^0-9]/', '', $data['whatsapp']),
                ':phone' => preg_replace('/[^0-9]/', '', $data['phone']),
                ':phone2' => preg_replace('/[^0-9]/', '', $data['phone2']),
                ':cep' => preg_replace('/[^0-9]/', '', $data['cep']),
                ':street' => $data['street'],
                ':number' => $data['number'],
                ':complement' => $data['complement'],
                ':neighborhood' => $data['neighborhood'],
                ':city' => $data['city'],
                ':state' => $data['state'],
                ':notes' => $data['notes'],
                ':status' => $data['status']
            ];
            
            // Update
            $sql = "UPDATE patients_data SET 
                name = :name, 
                rg = :rg, 
                birth_date = :birth_date,
                gender = :gender, 
                email = :email, 
                whatsapp = :whatsapp,
                phone = :phone, 
                phone2 = :phone2, 
                cep = :cep, 
                street = :street,
                number = :number, 
                complement = :complement, 
                neighborhood = :neighborhood,
                city = :city, 
                state = :state, 
                notes = :notes, 
                status = :status
                WHERE id = :id";
            
            $stmt = $db->prepare($sql);
            
            // Debug da query e parâmetros
            error_log('SQL: ' . $sql);
            error_log('Parâmetros: ' . print_r($params, true));
            
            try {
                $result = $stmt->execute($params);
                
                if (!$result) {
                    $errorInfo = $stmt->errorInfo();
                    throw new Exception("Erro na query: " . $errorInfo[2]);
                }
                
                $db->commit();
                return true;
                
            } catch (PDOException $e) {
                throw new Exception("Erro no banco de dados: " . $e->getMessage());
            }
            
        } catch (Exception $e) {
            $db->rollBack();
            error_log("Erro detalhado ao atualizar paciente: " . $e->getMessage());
            error_log("Stack trace: " . $e->getTraceAsString());
            throw $e;
        }
    }

    function formatCPF($cpf) {
        $cpf = preg_replace('/[^0-9]/', '', $cpf);
        if (strlen($cpf) === 11) {
            return substr($cpf, 0, 3) . '.' . 
                substr($cpf, 3, 3) . '.' . 
                substr($cpf, 6, 3) . '-' . 
                substr($cpf, 9);
        }
        return $cpf;
    }

    function getDoctorTimeSlots($doctorId, $date) {
        $db = Database::getInstance()->getConnection();
        
        // Verificar se o médico atende neste dia da semana
        $weekday = date('D', strtotime($date));
        $weekdayPt = [
            'Sun' => 'Dom',
            'Mon' => 'Seg',
            'Tue' => 'Ter',
            'Wed' => 'Qua',
            'Thu' => 'Qui',
            'Fri' => 'Sex',
            'Sat' => 'Sab'
        ][$weekday];
        
        // Buscar horário de trabalho do médico para este dia
        $stmt = $db->prepare("
            SELECT start_time, end_time 
            FROM doctor_schedule 
            WHERE doctor_id = :doctor_id 
            AND weekday = :weekday 
            AND is_active = 1
        ");
        
        $stmt->execute([
            ':doctor_id' => $doctorId,
            ':weekday' => $weekdayPt
        ]);
        
        $schedule = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Se não houver horário cadastrado para este dia, retornar array vazio
        if (!$schedule) {
            return [];
        }
        
        // Gerar slots de 15 minutos entre start_time e end_time
        $start = strtotime($schedule['start_time']);
        $end = strtotime($schedule['end_time']);
        $interval = 15 * 60; // 15 minutos em segundos
        
        $workHours = [];
        for ($time = $start; $time < $end; $time += $interval) {
            $workHours[] = date('H:i', $time);
        }
        
        // Buscar agendamentos existentes
        $stmt = $db->prepare("
            SELECT a.*, p.name as patient_name 
            FROM appointments a
            LEFT JOIN patients_data p ON p.id = a.patient_id
            WHERE a.doctor_id = :doctor_id 
            AND a.date = :date
            AND a.status != 'Cancelado'
        ");
        
        $stmt->execute([
            ':doctor_id' => $doctorId,
            ':date' => $date
        ]);
        
        $appointments = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $bookedTimes = array_column($appointments, 'time');
        
        // Montar slots de horário
        $slots = [];
        foreach ($workHours as $time) {
            $timeIndex = array_search($time, $bookedTimes);
            if ($timeIndex !== false) {
                // Horário ocupado
                $slots[] = [
                    'time' => $time,
                    'status' => 'booked',
                    'id' => $appointments[$timeIndex]['id'],
                    'patient_name' => $appointments[$timeIndex]['patient_name']
                ];
            } else {
                // Horário disponível
                $slots[] = [
                    'time' => $time,
                    'status' => 'available'
                ];
            }
        }
        
        return $slots;
    }

    function getSlotBorderColor($status) {
        switch ($status) {
            case 'available':
                return 'border-yellow-500';
            case 'booked':
                return 'border-blue-500';
            default:
                return 'border-gray-500';
        }
    }

    function getSlotTimeColor($status) {
        switch ($status) {
            case 'available':
                return 'text-yellow-500';
            case 'booked':
                return 'text-blue-500';
            default:
                return 'text-gray-500';
        }
    }

    function formatTime($time) {
        return date('H:i', strtotime($time));
    }

    function getDoctorSchedule($doctorId) {
        $db = Database::getInstance()->getConnection();
        
        $sql = "SELECT weekday, start_time, end_time 
                FROM doctor_schedule 
                WHERE doctor_id = :doctor_id 
                AND is_active = 1
                ORDER BY FIELD(weekday, 'Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sab', 'Dom')";
                
        $stmt = $db->prepare($sql);
        $stmt->execute([':doctor_id' => $doctorId]);
        
        $schedule = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $schedule[$row['weekday']] = [
                'start_time' => substr($row['start_time'], 0, 5),
                'end_time' => substr($row['end_time'], 0, 5)
            ];
        }
        
        return $schedule;
    }

    function updateDoctorSchedule($doctorId, $weekdays) {
        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Desativar todos os horários existentes
            $stmt = $db->prepare("UPDATE doctor_schedule SET is_active = 0 
                                WHERE doctor_id = :doctor_id");
            $stmt->execute([':doctor_id' => $doctorId]);
            
            // Inserir ou ativar novos horários
            foreach ($weekdays as $day => $active) {
                if ($active == '1') {
                    // Horário padrão 8h às 18h
                    $stmt = $db->prepare("INSERT INTO doctor_schedule 
                        (id, doctor_id, weekday, start_time, end_time) 
                        VALUES (:id, :doctor_id, :weekday, '08:00', '18:00')
                        ON DUPLICATE KEY UPDATE is_active = 1");
                    
                    $stmt->execute([
                        ':id' => generateUUID(),
                        ':doctor_id' => $doctorId,
                        ':weekday' => $day
                    ]);
                }
            }
            
            $db->commit();
            return true;
            
        } catch (Exception $e) {
            $db->rollBack();
            error_log("Erro ao atualizar horários do médico: " . $e->getMessage());
            throw $e;
        }
    }