<?php
function config($key = null, $default = null) {
    static $config;
    if ($config === null) {
        $config = require __DIR__ . '/config.php';
    }
    if ($key === null) return $config;
    return array_key_exists($key, $config) ? $config[$key] : $default;
}

function e($value) {
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function app_base_url() {
    $https = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') || ((isset($_SERVER['HTTP_X_FORWARDED_PROTO']) ? $_SERVER['HTTP_X_FORWARDED_PROTO'] : '') === 'https');
    $scheme = $https ? 'https' : 'http';
    $host = isset($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : 'localhost';
    $script = isset($_SERVER['SCRIPT_NAME']) ? dirname($_SERVER['SCRIPT_NAME']) : '';
    $script = rtrim(str_replace('\\', '/', $script), '/');
    if (substr($script, -4) === '/app') $script = substr($script, 0, -4);
    return $scheme . '://' . $host . ($script === '/' ? '' : $script);
}

function url($path = '') {
    return rtrim(app_base_url(), '/') . '/' . ltrim($path, '/');
}


function autoescola_sso_url(array $user): string {
    $secret = (string)config('autoescola_sso_secret', 'LINKEXPRESS_AUTOESCOLA_SSO_2026_TROQUE_ESTA_CHAVE');
    $payload = ['email'=>strtolower(trim((string)($user['email'] ?? ''))), 'name'=>(string)($user['name'] ?? 'Administrador LinkExpress'), 'exp'=>time()+120];
    $raw = json_encode($payload, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
    $b64 = rtrim(strtr(base64_encode($raw), '+/', '-_'), '=');
    $sig = hash_hmac('sha256', $b64, $secret);
    return url('autoescola/sso.php?token='.$b64.'.'.$sig);
}

function redirect($path) {
    $target = preg_match('~^https?://~i', $path) ? $path : url($path);
    // Grava flash/user_id e libera o lock da sessão antes do navegador iniciar
    // imediatamente a página seguinte. Em POST usamos 303 para garantir GET.
    if (session_status() === PHP_SESSION_ACTIVE) {
        @session_write_close();
    }
    $status = (isset($_SERVER['REQUEST_METHOD']) && strtoupper((string)$_SERVER['REQUEST_METHOD']) === 'POST') ? 303 : 302;
    header('Location: ' . $target, true, $status);
    exit;
}

function now() { return date('Y-m-d H:i:s'); }
function today() { return date('Y-m-d'); }

function money($value) {
    return 'R$ ' . number_format((float)$value, 2, ',', '.');
}

function request($key, $default = null) {
    if (isset($_POST[$key])) return is_string($_POST[$key]) ? trim($_POST[$key]) : $_POST[$key];
    if (isset($_GET[$key])) return is_string($_GET[$key]) ? trim($_GET[$key]) : $_GET[$key];
    return $default;
}

function flash($type, $message) {
    $_SESSION['flash'][] = ['type' => $type, 'message' => $message];
}

function pull_flashes() {
    $items = isset($_SESSION['flash']) ? $_SESSION['flash'] : [];
    unset($_SESSION['flash']);
    return $items;
}

function csrf_token() {
    if (empty($_SESSION['csrf'])) $_SESSION['csrf'] = bin2hex(random_bytes(24));
    return $_SESSION['csrf'];
}

function csrf_field() {
    return '<input type="hidden" name="csrf" value="' . e(csrf_token()) . '">';
}

function verify_csrf() {
    $sent = isset($_POST['csrf']) ? $_POST['csrf'] : '';
    if (!$sent || !hash_equals(csrf_token(), $sent)) {
        http_response_code(419);
        exit('Sessão expirada. Atualize a página e tente novamente.');
    }
}

function role_label($role) {
    $map = [
        'super_admin' => 'Administrador Sênior',
        'sub_admin' => 'Administrador',
        'company' => 'Empresa',
        'client' => 'Cliente',
        'courier' => 'Entregador',
        'motofreight' => 'Motofretista',
        'mototaxi' => 'Mototaxista',
        'taxi' => 'Taxista',
        'private_driver' => 'Motorista Particular',
        'service_provider' => 'Prestador de Serviços',
        'clinic_professional' => 'Profissional de Clínica',
        'property_owner' => 'Proprietário de Imóveis',
        'rental_company' => 'Locadora de Veículos',
        'employee' => 'Funcionário',
        'moving_provider' => 'Mudanças e Fretes',
        'shuttle_driver' => 'Motorista de Lotação',
    ];
    return isset($map[$role]) ? $map[$role] : ucfirst(str_replace('_', ' ', $role));
}

function role_icon($role) {
    $map = [
        'super_admin' => '👑', 'sub_admin' => '🛡️', 'company' => '🏢', 'client' => '👤', 'courier' => '🛵',
        'motofreight' => '📦', 'mototaxi' => '🏍️', 'taxi' => '🚖', 'private_driver' => '🚗',
        'service_provider' => '🛠️', 'clinic_professional' => '🩺', 'property_owner' => '🏠', 'rental_company' => '🚘', 'employee' => '🧑‍💼', 'moving_provider' => '🚚', 'shuttle_driver' => '🚐'
    ];
    return isset($map[$role]) ? $map[$role] : '•';
}

function registration_personal_roles(): array {
    return ['client','courier','motofreight','mototaxi','taxi','private_driver','service_provider','property_owner','moving_provider','shuttle_driver'];
}

function segment_registration_roles(): array {
    return ['company','courier','motofreight','mototaxi','taxi','private_driver','service_provider','property_owner','moving_provider','shuttle_driver'];
}

function segment_allowed_roles(array $segment): array {
    $raw=trim((string)($segment['allowed_roles']??''));
    if($raw==='')return ['company'];
    $roles=[];
    foreach(explode(',',$raw) as $role){$role=trim($role);if($role!==''&&!in_array($role,$roles,true))$roles[]=$role;}
    return $roles?:['company'];
}

function segment_allows_role(array $segment,string $role): bool {
    return in_array($role,segment_allowed_roles($segment),true);
}

function segments_for_registration_role(string $role): array {
    try{
        $rows=db()->query("SELECT * FROM segments WHERE status='active' ORDER BY sort_order,name")->fetchAll();
        return array_values(array_filter($rows,static function($row)use($role){return segment_allows_role($row,$role);}));
    }catch(Throwable $e){return [];}
}

function user_needs_onboarding($user=null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true))return false;
    return isset($user['profile_completed']) && (int)$user['profile_completed']!==1;
}

function user_profile_required_fields_missing(array $user): array {
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true))return [];
    $missing=[];
    if(strlen(trim((string)($user['name']??'')))<3)$missing[]='nome completo';
    $document=only_digits((string)($user['document']??''));
    if(!in_array(strlen($document),[11,14],true))$missing[]='CPF/CNPJ';
    if(trim((string)($user['home_address']??''))==='')$missing[]='endereço';
    if(trim((string)($user['city']??''))==='')$missing[]='cidade';
    if(trim((string)($user['state']??''))==='')$missing[]='estado';
    return $missing;
}

function admin_profile_completion_required(array $user): bool {
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true))return false;
    return !empty($user['admin_require_profile']) && count(user_profile_required_fields_missing($user))>0;
}

function user_can_work_deliveries($user=null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    $role=(string)($user['role']??'');
    if(in_array($role,['courier','motofreight'],true))return true;
    return $role==='mototaxi' && !empty($user['delivery_mode_enabled']);
}

function gas_courier_company_ids(int $userId): array {
    if($userId<1)return [];
    $ids=[];
    $q=db()->prepare("SELECT DISTINCT c.id FROM companies c LEFT JOIN company_couriers cc ON cc.company_id=c.id AND cc.user_id=? AND cc.status='active' LEFT JOIN users u ON u.id=? WHERE c.status='active' AND (cc.id IS NOT NULL OR c.id=u.company_id) ORDER BY c.id");
    $q->execute([$userId,$userId]);
    foreach($q->fetchAll(PDO::FETCH_COLUMN) as $companyId){
        $companyId=(int)$companyId;
        if($companyId>0&&company_is_gas($companyId))$ids[]=$companyId;
    }
    return array_values(array_unique($ids));
}

function courier_linked_companies(int $userId): array {
    if($userId<1)return [];
    $q=db()->prepare("SELECT DISTINCT c.id,c.name,c.type FROM company_couriers cc JOIN companies c ON c.id=cc.company_id WHERE cc.user_id=? AND cc.status='active' AND c.status='active' ORDER BY c.name");
    $q->execute([$userId]);return $q->fetchAll();
}

function courier_can_register_company_client(int $userId,int $companyId): bool {
    if($userId<1||$companyId<1)return false;
    $q=db()->prepare("SELECT COUNT(*) FROM company_couriers cc JOIN users u ON u.id=cc.user_id WHERE cc.user_id=? AND cc.company_id=? AND cc.status='active' AND u.status='active' AND (u.role IN ('courier','motofreight') OR (u.role='mototaxi' AND COALESCE(u.delivery_mode_enabled,0)=1))");
    $q->execute([$userId,$companyId]);return (int)$q->fetchColumn()>0;
}

function user_is_gas_courier($user=null): bool {
    $user=$user?:current_user();
    if(!$user||!user_can_work_deliveries($user))return false;
    return count(gas_courier_company_ids((int)($user['id']??0)))>0;
}

function required_platform_terms(): array {
    try{return db()->query("SELECT * FROM platform_terms WHERE status='active' AND required=1 ORDER BY sort_order,id")->fetchAll();}
    catch(Throwable $e){return [];}
}

function active_platform_terms(): array {
    try{return db()->query("SELECT * FROM platform_terms WHERE status='active' ORDER BY sort_order,id")->fetchAll();}
    catch(Throwable $e){return [];}
}

function user_has_accepted_term(int $userId,array $term): bool {
    try{$q=db()->prepare('SELECT COUNT(*) FROM term_acceptances WHERE user_id=? AND term_id=? AND term_version=?');$q->execute([$userId,(int)$term['id'],(int)$term['version']]);return (int)$q->fetchColumn()>0;}
    catch(Throwable $e){return false;}
}

function service_availability_rows(int $serviceId): array {
    try{$q=db()->prepare("SELECT * FROM service_availability WHERE service_id=? AND status='active' ORDER BY weekday");$q->execute([$serviceId]);return $q->fetchAll();}
    catch(Throwable $e){return [];}
}

function service_availability_map(int $serviceId): array {
    $out=[];foreach(service_availability_rows($serviceId) as $row)$out[(int)$row['weekday']]=$row;return $out;
}

function weekday_label(int $weekday): string {
    $labels=['Domingo','Segunda','Terça','Quarta','Quinta','Sexta','Sábado'];
    return $labels[$weekday]??'Dia';
}

function service_availability_summary(int $serviceId): string {
    $rows=service_availability_rows($serviceId);if(!$rows)return 'Agenda sem dias configurados.';
    $parts=[];foreach($rows as $row){$time='';if(!empty($row['start_time'])&&!empty($row['end_time']))$time=' '.$row['start_time'].'–'.$row['end_time'];$parts[]=weekday_label((int)$row['weekday']).$time;}return implode(' • ',$parts);
}

function service_booking_window_for_date(int $serviceId,string $date): ?array {
    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date) || strtotime($date)===false)return null;
    $weekday=(int)date('w',strtotime($date));
    try{
        // Datas liberadas em massa têm prioridade sobre a grade semanal do serviço.
        $q=db()->prepare("SELECT start_time,end_time,interval_minutes FROM service_availability_dates WHERE service_id=? AND availability_date=? AND status='active' LIMIT 1");
        $q->execute([$serviceId,$date]);$row=$q->fetch();
        if($row){
            return ['start_time'=>(string)($row['start_time']??''),'end_time'=>(string)($row['end_time']??''),'interval_minutes'=>(int)($row['interval_minutes']??60),'source'=>'bulk'];
        }
    }catch(Throwable $ignored){}
    try{
        $q=db()->prepare("SELECT start_time,end_time FROM service_availability WHERE service_id=? AND weekday=? AND status='active' LIMIT 1");
        $q->execute([$serviceId,$weekday]);$row=$q->fetch();if(!$row)return null;
        $sq=db()->prepare('SELECT booking_interval_minutes FROM services WHERE id=? LIMIT 1');$sq->execute([$serviceId]);$interval=(int)($sq->fetchColumn()?:60);
        return ['start_time'=>(string)($row['start_time']??''),'end_time'=>(string)($row['end_time']??''),'interval_minutes'=>$interval,'source'=>'weekly'];
    }catch(Throwable $e){return null;}
}

function service_booking_slots_for_date(int $serviceId,string $date): array {
    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date) || strtotime($date)===false)return [];
    try{
        $sq=db()->prepare('SELECT provider_id,booking_enabled,booking_interval_minutes FROM services WHERE id=? AND status="active" AND deleted_at IS NULL LIMIT 1');
        $sq->execute([$serviceId]);$service=$sq->fetch();
        if(!$service || empty($service['booking_enabled']))return [];
        $row=service_booking_window_for_date($serviceId,$date);if(!$row)return [];
        $start=trim((string)($row['start_time']??''));$end=trim((string)($row['end_time']??''));
        if($start===''||$end==='')return [];
        $startTs=strtotime($date.' '.$start);$endTs=strtotime($date.' '.$end);if(!$startTs||!$endTs||$startTs>=$endTs)return [];
        $interval=(int)($row['interval_minutes']??($service['booking_interval_minutes']??60));if(!in_array($interval,[15,20,30,45,60,90,120],true))$interval=60;
        $busyQ=db()->prepare("SELECT scheduled_at FROM service_requests WHERE provider_id=? AND date(scheduled_at)=? AND status IN ('requested','accepted','in_progress')");
        $busyQ->execute([(int)$service['provider_id'],$date]);$busy=[];foreach($busyQ->fetchAll(PDO::FETCH_COLUMN) as $v)$busy[date('H:i',strtotime((string)$v))]=1;
        $out=[];for($ts=$startTs;$ts<$endTs;$ts+=$interval*60){
            if($date===today() && $ts<=time())continue;
            $time=date('H:i',$ts);$out[]=['time'=>$time,'scheduled_at'=>$date.'T'.$time,'available'=>empty($busy[$time]),'start_time'=>$start,'end_time'=>$end,'interval_minutes'=>$interval];
        }
        return $out;
    }catch(Throwable $e){return [];}
}

function service_available_slots_count(int $serviceId,string $date): int {
    $n=0;foreach(service_booking_slots_for_date($serviceId,$date) as $slot)if(!empty($slot['available']))$n++;return $n;
}

function service_schedule_allows(int $serviceId,string $scheduledAt): bool {
    if(trim($scheduledAt)==='')return false;
    $ts=strtotime($scheduledAt);if(!$ts||$ts<=time())return false;
    $date=date('Y-m-d',$ts);$time=date('H:i',$ts);
    foreach(service_booking_slots_for_date($serviceId,$date) as $slot){
        if($slot['time']===$time && !empty($slot['available']))return true;
    }
    return false;
}

function company_pickup_weekdays(array $company): array {
    $raw=(string)($company['pickup_weekdays']??'1,2,3,4,5,6');
    $days=[];foreach(explode(',',$raw) as $v){$n=(int)trim($v);if($n>=0&&$n<=6)$days[$n]=$n;}
    if(!$days)$days=[1=>1,2=>2,3=>3,4=>4,5=>5,6=>6];
    ksort($days);return array_values($days);
}

function company_pickup_schedule_summary(array $company): string {
    if(empty($company['pickup_scheduling_enabled']))return 'Retirada agendada desativada.';
    $labels=[];foreach(company_pickup_weekdays($company) as $d)$labels[]=weekday_label($d);
    $start=trim((string)($company['pickup_start_time']??'08:00'))?:'08:00';
    $end=trim((string)($company['pickup_end_time']??'18:00'))?:'18:00';
    $interval=(int)($company['pickup_interval_minutes']??30);
    return implode(', ',$labels).' • '.$start.'–'.$end.' • a cada '.$interval.' min';
}

function company_pickup_time_options(array $company): array {
    if(empty($company['pickup_scheduling_enabled']))return [];
    $start=trim((string)($company['pickup_start_time']??'08:00'))?:'08:00';
    $end=trim((string)($company['pickup_end_time']??'18:00'))?:'18:00';
    $interval=(int)($company['pickup_interval_minutes']??30);if(!in_array($interval,[15,20,30,45,60,90,120],true))$interval=30;
    $base='2000-01-01 ';
    $startTs=strtotime($base.$start);$endTs=strtotime($base.$end);if(!$startTs||!$endTs||$startTs>=$endTs)return [];
    $out=[];for($ts=$startTs;$ts<$endTs;$ts+=$interval*60)$out[]=date('H:i',$ts);return $out;
}

function company_pickup_schedule_allows(array $company,string $date,string $time): bool {
    if(empty($company['pickup_scheduling_enabled']))return true;
    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)||!preg_match('/^\d{2}:\d{2}$/',$time))return false;
    $ts=strtotime($date.' '.$time);if(!$ts||$ts<=time())return false;
    if(!in_array((int)date('w',$ts),company_pickup_weekdays($company),true))return false;
    return in_array($time,company_pickup_time_options($company),true);
}

function status_label($status) {
    $map = [
        'active'=>'Ativo','inactive'=>'Inativo','pending'=>'Pendente','blocked'=>'Bloqueado',
        'available'=>'Disponível','busy'=>'Ocupado','offline'=>'Offline','online'=>'Online','on_route'=>'Em rota',
        'received'=>'Recebido','preparing'=>'Em preparo','ready'=>'Pronto','delivered'=>'Entregue',
        'requested'=>'Solicitado','accepted'=>'Aceito','in_progress'=>'Em andamento','completed'=>'Concluído',
        'cancelled'=>'Cancelado','canceled'=>'Cancelado','rejected'=>'Recusado','approved'=>'Aprovado',
        'proof_sent'=>'Comprovante enviado','paid'=>'Pago','overdue'=>'Vencido','open'=>'Aberto','closed'=>'Fechado',
        'reserved'=>'Reservado','sold'=>'Vendido','maintenance'=>'Em manutenção','scheduled'=>'Agendado',
        'waiting_authorization'=>'Aguardando autorização','not_required'=>'Não necessário',
        'fare_review'=>'Aguardando aprovação do valor','internal'=>'Próprio','autonomous'=>'Autônomo',
        'pickup'=>'Retirada','delivery'=>'Entrega','published'=>'Publicado','hidden'=>'Oculto',
        'on_hold'=>'Em espera','assigned'=>'Atribuído','awaiting_dispatch'=>'Aguardando despacho',
        'expired'=>'Expirado','processing'=>'Processando','low'=>'Baixa','normal'=>'Normal','high'=>'Alta',
        'visit'=>'Visita','pickup_ready'=>'Pronto para retirada','awaiting_address'=>'Aguardando endereço',
        'address_authorized'=>'Endereço autorizado','proof_rejected'=>'Comprovante recusado','draft'=>'Rascunho',
        'quoted'=>'Orçamento enviado','hired'=>'Contratado','negotiating'=>'Em negociação','sent'=>'Enviado',
        'read'=>'Lido','unread'=>'Não lido','refunded'=>'Reembolsado','authorized'=>'Autorizado','confirmed'=>'Confirmado','boarded'=>'Embarcado','active_trip'=>'Viagem ativa','lotado'=>'Lotado',
        'reviewed'=>'Analisada','dismissed'=>'Arquivada','actioned'=>'Providência tomada','finalized'=>'Finalizada','waiting'=>'Aguardando lotação',
        'pending_professional'=>'Aguardando profissional','pending_admin'=>'Aguardando aprovação','waiting_payment'=>'Aguardando mensalidade',
        'fare_proposed'=>'Aguardando aceite do valor','presence_confirmed'=>'Presença confirmada','checked_in'=>'Presença confirmada','in_service'=>'Em atendimento','skipped'=>'Senha pulada','no_show'=>'Falta','holiday'=>'Feriado'
    ];
    $key = strtolower((string)$status);
    return $map[$key] ?? ucfirst(str_replace('_',' ',(string)$status));
}

function status_badge($status) {
    $safe = strtolower(preg_replace('/[^a-z0-9_\\-]/i', '', (string)$status));
    return '<span class="status status-' . e($safe) . '">' . e(status_label($status)) . '</span>';
}

function setting($key, $default = '') {
    $stmt = db()->prepare('SELECT value FROM settings WHERE key_name = ? LIMIT 1');
    $stmt->execute([$key]);
    $value = $stmt->fetchColumn();
    return $value === false ? $default : $value;
}

function save_setting($key, $value) {
    $stmt = db()->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at');
    $stmt->execute([$key, $value, now()]);
}

function user_ui_state(int $userId, string $key, string $default=''): string {
    try {
        $stmt=db()->prepare('SELECT value FROM user_ui_state WHERE user_id=? AND key_name=? LIMIT 1');
        $stmt->execute([$userId,$key]);
        $value=$stmt->fetchColumn();
        return $value===false?$default:(string)$value;
    } catch (Throwable $e) { return $default; }
}

function save_user_ui_state(int $userId, string $key, string $value): void {
    db()->prepare('INSERT INTO user_ui_state(user_id,key_name,value,updated_at) VALUES(?,?,?,?) ON CONFLICT(user_id,key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at')
        ->execute([$userId,$key,$value,now()]);
}

function activity_action_label(string $action): string {
    $map=[
        'login'=>'Login', 'create_user'=>'Usuário cadastrado', 'save_user'=>'Usuário atualizado', 'renew_user'=>'Mensalidade renovada', 'save_new_area_link'=>'Link da Central de integrações salvo', 'delete_user'=>'Usuário apagado',
        'save_company'=>'Empresa atualizada', 'delete_company'=>'Empresa apagada',
        'database_backup'=>'Backup criado', 'delete_backup'=>'Backup apagado',
        'prepare_database_restore'=>'Restauração preparada', 'save_category'=>'Categoria atualizada',
        'save_segment'=>'Segmento atualizado', 'delete_category'=>'Categoria apagada',
        'delete_segment'=>'Segmento apagado', 'save_quick_actions'=>'Ações rápidas atualizadas',
        'remove_profile_avatar'=>'Foto de perfil removida','admin_remove_user_avatar'=>'Foto de usuário removida',
        'submit_user_report'=>'Denúncia enviada','review_user_report'=>'Denúncia analisada','delete_user_report'=>'Denúncia apagada',
        'clear_login_history'=>'Histórico de logins zerado',
        'upload_order_payment_proof'=>'Comprovante Pix do pedido enviado',
        'confirm_order_payment'=>'Pagamento de pedido confirmado',
        'reject_order_payment_proof'=>'Comprovante de pedido recusado',
        'submit_plan_request'=>'Solicitação de plano enviada','review_plan_request'=>'Solicitação de plano analisada',
        'request_shuttle_now'=>'Lotação solicitada','accept_shuttle_request'=>'Solicitação de lotação aceita','cancel_shuttle_request'=>'Solicitação de lotação cancelada'
    ];
    return $map[$action]??ucfirst(str_replace('_',' ',$action));
}

function log_activity($action, $entity = null, $entityId = null, $details = null) {
    $user = current_user();
    $stmt = db()->prepare('INSERT INTO activity_logs(user_id, action, entity, entity_id, details, ip, created_at) VALUES(?,?,?,?,?,?,?)');
    $stmt->execute([
        $user ? $user['id'] : null,
        $action,
        $entity,
        $entityId,
        $details,
        isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : 'cli',
        now()
    ]);
}

function selected($a, $b) { return (string)$a === (string)$b ? ' selected' : ''; }
function checked($value) { return $value ? ' checked' : ''; }

function json_features($value) {
    $decoded = json_decode((string)$value, true);
    return is_array($decoded) ? $decoded : [];
}

function short_text($value, $length = 70) {
    $value = (string)$value;
    if (function_exists('mb_strimwidth')) return mb_strimwidth($value, 0, $length, '…', 'UTF-8');
    return strlen($value) > $length ? substr($value, 0, $length - 3) . '...' : $value;
}

/**
 * Converte caminhos salvos no banco em URL pública, preservando URLs externas antigas.
 */
function media_url($path) {
    $path = trim((string)$path);
    if ($path === '') return '';
    if (preg_match('~^(https?:)?//~i', $path) || strpos($path, 'data:') === 0) return $path;
    return url($path);
}


/**
 * URL protegida para acessar comprovantes gerados pelo sistema.
 * O endpoint consegue regenerar o PNG caso o banco tenha o registro, mas o arquivo físico tenha sido perdido.
 */
function generated_receipt_url(string $receiptPath = '', string $sourceType = '', int $sourceId = 0): string {
    $receiptPath=ltrim(trim($receiptPath),'/');
    try{
        $id=0;
        if($sourceType!==''&&$sourceId>0){
            $q=db()->prepare('SELECT id FROM receipts WHERE source_type=? AND source_id=? LIMIT 1');
            $q->execute([$sourceType,$sourceId]);$id=(int)$q->fetchColumn();
        }
        if($id<1&&$receiptPath!==''){
            $q=db()->prepare('SELECT id FROM receipts WHERE receipt_path=? LIMIT 1');
            $q->execute([$receiptPath]);$id=(int)$q->fetchColumn();
        }
        if($id>0)return url('receipt.php?id='.$id);
    }catch(Throwable $ignored){}
    return $receiptPath!==''?media_url($receiptPath):'';
}

/**
 * Faz upload seguro de imagens para /uploads sem aceitar arquivos executáveis ou SVG.
 * Retorna o caminho relativo para ser salvo no SQLite.
 */
function upload_image($field, $folder, $current = '', $maxBytes = 20971520) {
    if (!isset($_FILES[$field]) || !is_array($_FILES[$field])) return (string)$current;
    $file = $_FILES[$field];
    $error = isset($file['error']) ? (int)$file['error'] : UPLOAD_ERR_NO_FILE;
    if ($error === UPLOAD_ERR_NO_FILE) return (string)$current;
    if ($error !== UPLOAD_ERR_OK) {
        $messages = [
            UPLOAD_ERR_INI_SIZE => 'A imagem ultrapassa o limite configurado no servidor.',
            UPLOAD_ERR_FORM_SIZE => 'A imagem ultrapassa o limite permitido pelo formulário.',
            UPLOAD_ERR_PARTIAL => 'O envio da imagem foi interrompido. Tente novamente.',
            UPLOAD_ERR_NO_TMP_DIR => 'A pasta temporária do servidor não está disponível.',
            UPLOAD_ERR_CANT_WRITE => 'O servidor não conseguiu gravar a imagem.',
            UPLOAD_ERR_EXTENSION => 'Uma extensão do PHP interrompeu o upload.',
        ];
        throw new RuntimeException(isset($messages[$error]) ? $messages[$error] : 'Não foi possível enviar a imagem.');
    }
    $tmp = isset($file['tmp_name']) ? $file['tmp_name'] : '';
    $size = isset($file['size']) ? (int)$file['size'] : 0;
    if ($tmp === '' || !is_uploaded_file($tmp)) throw new RuntimeException('O arquivo enviado não é válido.');
    if ($size < 1 || $size > $maxBytes) {
        $limitMb = max(1, (int)ceil($maxBytes / 1048576));
        throw new RuntimeException('A imagem deve ter no máximo ' . $limitMb . ' MB.');
    }

    $mime = '';
    if (class_exists('finfo')) {
        $finfo = new finfo(FILEINFO_MIME_TYPE);
        $mime = (string)$finfo->file($tmp);
    }
    if ($mime === '' && function_exists('getimagesize')) {
        $info = @getimagesize($tmp);
        $mime = is_array($info) && isset($info['mime']) ? (string)$info['mime'] : '';
    }
    $allowed = [
        'image/jpeg' => 'jpg',
        'image/png' => 'png',
        'image/webp' => 'webp',
    ];
    if (!isset($allowed[$mime])) throw new RuntimeException('Formato não permitido. Envie uma imagem JPG, PNG ou WebP.');
    if (@getimagesize($tmp) === false) throw new RuntimeException('O arquivo selecionado não é uma imagem válida.');

    $safeFolder = preg_replace('/[^a-z0-9_\/-]/i', '', (string)$folder);
    $safeFolder = trim($safeFolder, '/');
    if ($safeFolder === '') $safeFolder = 'geral';
    $relativeDir = 'uploads/' . $safeFolder . '/' . date('Y/m');
    $absoluteDir = dirname(__DIR__) . '/' . $relativeDir;
    if (!is_dir($absoluteDir) && !mkdir($absoluteDir, 0755, true) && !is_dir($absoluteDir)) {
        throw new RuntimeException('Não foi possível criar a pasta de imagens. Verifique as permissões de /uploads.');
    }
    $name = bin2hex(random_bytes(18)) . '.' . $allowed[$mime];
    $destination = $absoluteDir . '/' . $name;
    if (!move_uploaded_file($tmp, $destination)) throw new RuntimeException('Não foi possível salvar a imagem no servidor.');
    @chmod($destination, 0644);

    $newPath = $relativeDir . '/' . $name;
    delete_local_upload($current, $newPath);
    return $newPath;
}

function delete_local_upload($path, $except = '') {
    $path = ltrim(trim((string)$path), '/');
    $except = ltrim(trim((string)$except), '/');
    if ($path === '' || $path === $except || strpos($path, 'uploads/') !== 0) return;
    $root = realpath(dirname(__DIR__) . '/uploads');
    $file = realpath(dirname(__DIR__) . '/' . $path);
    if ($root && $file && strpos($file, $root . DIRECTORY_SEPARATOR) === 0 && is_file($file)) @unlink($file);
}

/**
 * Upload de APK administrado pelo Administrador Sênior. O arquivo é validado como pacote ZIP
 * Android e salvo apenas para download público; nunca é executado pelo servidor.
 */
function upload_android_apk($field, $current = '', $maxBytes = 262144000) {
    if (!isset($_FILES[$field]) || !is_array($_FILES[$field])) return (string)$current;
    $file = $_FILES[$field];
    $error = isset($file['error']) ? (int)$file['error'] : UPLOAD_ERR_NO_FILE;
    if ($error === UPLOAD_ERR_NO_FILE) return (string)$current;
    if ($error !== UPLOAD_ERR_OK) {
        $messages = [
            UPLOAD_ERR_INI_SIZE => 'O APK ultrapassou o limite upload_max_filesize da hospedagem (' . ini_get('upload_max_filesize') . ').',
            UPLOAD_ERR_FORM_SIZE => 'O APK ultrapassou o limite aceito pelo formulário.',
            UPLOAD_ERR_PARTIAL => 'O envio do APK foi interrompido antes de terminar. Tente novamente em uma conexão estável.',
            UPLOAD_ERR_NO_TMP_DIR => 'A hospedagem está sem uma pasta temporária para receber o APK.',
            UPLOAD_ERR_CANT_WRITE => 'A hospedagem não conseguiu gravar o APK no disco.',
            UPLOAD_ERR_EXTENSION => 'Uma extensão do PHP bloqueou o envio do APK.',
        ];
        throw new RuntimeException($messages[$error] ?? 'Não foi possível enviar o APK. Verifique os limites upload_max_filesize e post_max_size da hospedagem.');
    }

    $tmp = (string)($file['tmp_name'] ?? '');
    $size = (int)($file['size'] ?? 0);
    $original = (string)($file['name'] ?? 'aplicativo.apk');
    if ($tmp === '' || !is_uploaded_file($tmp)) throw new RuntimeException('O APK enviado não é válido.');
    if ($size < 1 || $size > $maxBytes) throw new RuntimeException('O APK deve ter no máximo 250 MB.');
    if (strtolower(pathinfo($original, PATHINFO_EXTENSION)) !== 'apk') throw new RuntimeException('Envie um arquivo com extensão .apk.');

    $validPackage = false;
    if (class_exists('ZipArchive')) {
        $zip = new ZipArchive();
        if ($zip->open($tmp) === true) {
            $hasManifest = $zip->locateName('AndroidManifest.xml', ZipArchive::FL_NODIR) !== false;
            $hasDex = false;
            for ($i=0; $i<$zip->numFiles; $i++) {
                $name = (string)$zip->getNameIndex($i);
                if (preg_match('~(^|/)classes(?:\d+)?\.dex$~i', $name)) { $hasDex = true; break; }
            }
            $validPackage = $hasManifest && $hasDex;
            $zip->close();
        }
    } else {
        $fh = @fopen($tmp, 'rb');
        $magic = $fh ? fread($fh, 4) : '';
        if ($fh) fclose($fh);
        $validPackage = $magic === "PK\x03\x04";
    }
    if (!$validPackage) throw new RuntimeException('O arquivo não parece ser um APK Android válido.');

    $relativeDir = 'uploads/aplicativo/' . date('Y/m');
    $absoluteDir = dirname(__DIR__) . '/' . $relativeDir;
    if (!is_dir($absoluteDir) && !mkdir($absoluteDir, 0755, true) && !is_dir($absoluteDir)) {
        throw new RuntimeException('Não foi possível criar a pasta do aplicativo.');
    }
    $destination = $absoluteDir . '/' . bin2hex(random_bytes(18)) . '.apk';
    if (!move_uploaded_file($tmp, $destination)) throw new RuntimeException('Não foi possível salvar o APK no servidor.');
    @chmod($destination, 0644);

    $newPath = $relativeDir . '/' . basename($destination);
    delete_local_upload($current, $newPath);
    return $newPath;
}

function platform_apk_info(): array {
    $path = ltrim(trim((string)setting('android_apk_path','')), '/');
    if ($path === '' || strpos($path, 'uploads/aplicativo/') !== 0) return [];
    $absolute = dirname(__DIR__) . '/' . $path;
    if (!is_file($absolute)) return [];
    $bytes = (int)@filesize($absolute);
    $size = $bytes > 0 ? ($bytes >= 1048576 ? number_format($bytes / 1048576, 1, ',', '.') . ' MB' : number_format($bytes / 1024, 0, ',', '.') . ' KB') : '';
    return [
        'path' => $path,
        'url' => media_url($path),
        'label' => trim((string)setting('android_apk_label','')) ?: platform_name() . ' Android',
        'size' => $size,
        'updated_at' => (string)setting('android_apk_updated_at',''),
    ];
}


/**
 * Remove um comprovante PNG gerado e limpa todas as referências ao mesmo arquivo.
 * O histórico financeiro/cobrança permanece no banco; apenas o arquivo antigo deixa de existir.
 */
function delete_generated_receipt_file(string $path): bool {
    $path = ltrim(trim($path), '/');
    if ($path === '' || strpos($path, 'uploads/comprovantes-gerados/') !== 0) return false;

    $absolute = dirname(__DIR__) . '/' . $path;
    $existed = is_file($absolute);
    delete_local_upload($path);

    foreach (['charges','subscriptions','payments','service_requests','rides','vehicle_reservations','property_bookings'] as $table) {
        try {
            db()->prepare('UPDATE ' . $table . ' SET receipt_path=NULL WHERE receipt_path=?')->execute([$path]);
        } catch (Throwable $ignored) {}
    }
    try { db()->prepare('DELETE FROM receipts WHERE receipt_path=?')->execute([$path]); } catch (Throwable $ignored) {}
    return $existed;
}

/**
 * Limpa comprovantes PNG gerados há pelo menos $months meses.
 * Para Sub Admin, respeita a regra de não alterar registros ligados a contas administrativas.
 */
function purge_generated_receipts_older_than(int $months = 3): array {
    $months = max(1, min(120, $months));
    $cutoffTs = strtotime('-' . $months . ' months');
    if ($cutoffTs === false) $cutoffTs = time() - (90 * 86400);
    $cutoff = date('Y-m-d H:i:s', $cutoffTs);
    $paths = [];
    $receiptIds = [];

    if (is_role('sub_admin')) {
        $q = db()->prepare("SELECT r.id,r.receipt_path FROM receipts r LEFT JOIN users pu ON pu.id=r.payer_user_id LEFT JOIN users ru ON ru.id=r.recipient_user_id WHERE r.created_at<=? AND (pu.role IS NULL OR pu.role NOT IN ('super_admin','sub_admin')) AND (ru.role IS NULL OR ru.role NOT IN ('super_admin','sub_admin'))");
        $q->execute([$cutoff]);
    } else {
        $q = db()->prepare('SELECT id,receipt_path FROM receipts WHERE created_at<=?');
        $q->execute([$cutoff]);
    }
    foreach ($q->fetchAll() as $row) {
        $receiptIds[] = (int)$row['id'];
        $rp = ltrim(trim((string)($row['receipt_path'] ?? '')), '/');
        if ($rp !== '' && strpos($rp, 'uploads/comprovantes-gerados/') === 0) $paths[$rp] = true;
    }

    // O Administrador Sênior também remove PNGs órfãos antigos da pasta física.
    // Isso cobre recibos antigos que possam ter sido gerados antes da tabela receipts existir.
    if (!is_role('sub_admin')) {
        $dir = dirname(__DIR__) . '/uploads/comprovantes-gerados';
        if (is_dir($dir)) {
            foreach (glob($dir . '/*') ?: [] as $file) {
                if (!is_file($file)) continue;
                $mtime = @filemtime($file);
                if ($mtime !== false && $mtime <= $cutoffTs) {
                    $paths['uploads/comprovantes-gerados/' . basename($file)] = true;
                }
            }
        }
    }

    $filesDeleted = 0;
    foreach (array_keys($paths) as $path) {
        if (delete_generated_receipt_file($path)) $filesDeleted++;
    }

    // Caso algum registro antigo não tivesse receipt_path, remova somente a entrada da pasta lógica.
    if ($receiptIds) {
        $placeholders = implode(',', array_fill(0, count($receiptIds), '?'));
        db()->prepare('DELETE FROM receipts WHERE id IN (' . $placeholders . ')')->execute($receiptIds);
    }

    // Limpa diretórios vazios sem tocar em outras áreas de upload.
    $dir = dirname(__DIR__) . '/uploads/comprovantes-gerados';
    if (is_dir($dir)) {
        foreach (glob($dir . '/*', GLOB_ONLYDIR) ?: [] as $subdir) @rmdir($subdir);
    }

    return [
        'records_deleted' => count($receiptIds),
        'files_deleted' => $filesDeleted,
        'cutoff' => $cutoff,
    ];
}

function avatar_html($user, $class = 'avatar') {
    $name = isset($user['name']) ? (string)$user['name'] : 'U';
    $avatar = isset($user['avatar']) ? trim((string)$user['avatar']) : '';
    if ($avatar !== '') {
        return '<div class="' . e($class) . ' avatar-has-image"><img src="' . e(media_url($avatar)) . '" alt="Foto de ' . e($name) . '"></div>';
    }
    return '<div class="' . e($class) . '">' . e(strtoupper(substr($name, 0, 1))) . '</div>';
}

function create_notification($userId, $title, $message, $type = 'info', $linkUrl = '', $entityType = '', $entityId = null) {
    if (!$userId) return;
    $linkUrl = trim((string)$linkUrl);
    if ($linkUrl !== '' && !preg_match('~^(dashboard\.php\?page=[a-z_]+(?:&[a-z0-9_=&%-]+)?|marketplace\.php(?:\?.*)?)$~i', $linkUrl)) {
        $linkUrl = '';
    }
    db()->prepare('INSERT INTO notifications(user_id,title,message,type,link_url,entity_type,entity_id,created_at) VALUES(?,?,?,?,?,?,?,?)')
        ->execute([(int)$userId, (string)$title, (string)$message, (string)$type, $linkUrl ?: null, $entityType ?: null, $entityId ? (int)$entityId : null, now()]);
}

function notify_super_admins($title, $message, $type = 'info', $exceptUserId = 0, $linkUrl = '', $entityType = '', $entityId = null) {
    $stmt = db()->query("SELECT id FROM users WHERE role IN ('super_admin','sub_admin') AND status='active'");
    foreach ($stmt->fetchAll() as $admin) {
        if ((int)$admin['id'] === (int)$exceptUserId) continue;
        create_notification((int)$admin['id'], $title, $message, $type, $linkUrl, $entityType, $entityId);
    }
}

function company_operational_user_ids(int $companyId): array {
    if ($companyId < 1) return [];
    try {
        $q=db()->prepare("SELECT DISTINCT u.id
            FROM users u
            LEFT JOIN companies c ON c.id=?
            WHERE u.status='active'
              AND (
                    u.id=c.owner_user_id
                    OR (u.company_id=? AND u.role IN ('company','employee'))
              )
            ORDER BY u.id");
        $q->execute([$companyId,$companyId]);
        return array_values(array_unique(array_map('intval',$q->fetchAll(PDO::FETCH_COLUMN))));
    } catch (Throwable $e) {
        return [];
    }
}

function notify_company_operational_users(int $companyId,string $title,string $message,string $type='info',string $linkUrl='',string $entityType='',?int $entityId=null,int $exceptUserId=0): void {
    foreach(company_operational_user_ids($companyId) as $uid){
        if($exceptUserId>0 && $uid===$exceptUserId) continue;
        create_notification($uid,$title,$message,$type,$linkUrl,$entityType,$entityId);
    }
}


function notification_role_options(): array {
    return [
        'company'=>'Empresas','client'=>'Clientes','employee'=>'Funcionários','courier'=>'Entregadores',
        'motofreight'=>'Moto frete','mototaxi'=>'Mototaxistas','taxi'=>'Taxistas','private_driver'=>'Motoristas particulares',
        'service_provider'=>'Prestadores de serviço','property_owner'=>'Proprietários de imóveis','rental_company'=>'Locadoras',
        'moving_provider'=>'Mudanças e fretes','shuttle_driver'=>'Motoristas de lotação'
    ];
}

function can_manage_manual_notifications($user = null): bool {
    $user = $user ?: current_user();
    if (!$user) return false;
    if (in_array(($user['role'] ?? ''), ['super_admin','sub_admin'], true)) return true;
    if (($user['role'] ?? '') === 'company') return (int)($user['company_id'] ?? 0) > 0;
    if (($user['role'] ?? '') !== 'employee' || (int)($user['company_id'] ?? 0) < 1) return false;
    $q=db()->prepare('SELECT employees_can_manage_notifications FROM companies WHERE id=?');
    $q->execute([(int)$user['company_id']]);
    return (int)$q->fetchColumn() === 1;
}

function notification_broadcast_manageable(array $row, $user = null): bool {
    $user = $user ?: current_user();
    if (!$user || empty($row['broadcast_id'])) return false;
    if (($user['role'] ?? '') === 'super_admin') return true;
    if (($user['role'] ?? '') === 'sub_admin') {
        $issuerId=(int)($row['issuer_user_id'] ?? 0);
        if($issuerId<1 || $issuerId===(int)$user['id']) return true;
        $q=db()->prepare('SELECT role FROM users WHERE id=? LIMIT 1');$q->execute([$issuerId]);$issuerRole=(string)($q->fetchColumn()?:'');
        return !in_array($issuerRole,['super_admin','sub_admin'],true);
    }
    if (!can_manage_manual_notifications($user)) return false;
    return (int)($row['source_company_id'] ?? 0) > 0 && (int)$row['source_company_id'] === (int)($user['company_id'] ?? 0);
}


function manual_notification_recipient_ids(array $issuer, string $audienceType, string $audienceValue = ''): array {
    $audienceType=trim($audienceType);$audienceValue=trim($audienceValue);$ids=[];$label='';
    if(in_array(($issuer['role']??''),['super_admin','sub_admin'],true)){
        if($audienceType==='all'){$rows=db()->query("SELECT id FROM users WHERE status='active'")->fetchAll();$label='Todos os usuários';}
        elseif($audienceType==='role'){$roles=notification_role_options();if(!isset($roles[$audienceValue]))throw new RuntimeException('Perfil de destino inválido.');$q=db()->prepare("SELECT id FROM users WHERE status='active' AND role=?");$q->execute([$audienceValue]);$rows=$q->fetchAll();$label=$roles[$audienceValue];}
        elseif($audienceType==='company'){$companyId=(int)$audienceValue;$q=db()->prepare("SELECT id FROM users WHERE status='active' AND company_id=?");$q->execute([$companyId]);$rows=$q->fetchAll();$cq=db()->prepare('SELECT name FROM companies WHERE id=?');$cq->execute([$companyId]);$label='Empresa: '.((string)$cq->fetchColumn()?:'#'.$companyId);$audienceValue=(string)$companyId;}
        elseif($audienceType==='user'){$q=db()->prepare("SELECT id,name,public_id FROM users WHERE status='active' AND (upper(public_id)=upper(?) OR lower(email)=lower(?) OR id=?) LIMIT 1");$q->execute([$audienceValue,$audienceValue,(int)$audienceValue]);$row=$q->fetch();$rows=$row?[['id'=>$row['id']]]:[];$label=$row?'Usuário: '.$row['name'].' ('.$row['public_id'].')':'Usuário individual';$audienceValue=$row?(string)$row['public_id']:$audienceValue;}
        else throw new RuntimeException('Escolha o público da notificação.');
    }else{
        $companyId=(int)($issuer['company_id']??0);if($companyId<1)throw new RuntimeException('Empresa não definida.');
        if($audienceType==='company_clients'){$q=db()->prepare("SELECT DISTINCT u.id FROM users u LEFT JOIN delivery_address_permissions dap ON dap.user_id=u.id AND dap.company_id=? AND dap.status='active' AND dap.always_visible=1 WHERE u.status='active' AND ((u.role='client' AND u.company_id=?) OR dap.id IS NOT NULL)");$q->execute([$companyId,$companyId]);$rows=$q->fetchAll();$label='Clientes da empresa';}
        elseif($audienceType==='company_employees'){$q=db()->prepare("SELECT id FROM users WHERE status='active' AND company_id=? AND role='employee'");$q->execute([$companyId]);$rows=$q->fetchAll();$label='Funcionários da empresa';}
        elseif($audienceType==='company_couriers'){$q=db()->prepare("SELECT id FROM users WHERE status='active' AND company_id=? AND role IN ('courier','motofreight') UNION SELECT u.id FROM company_couriers cc JOIN users u ON u.id=cc.user_id WHERE cc.company_id=? AND cc.status='active' AND u.status='active'");$q->execute([$companyId,$companyId]);$rows=$q->fetchAll();$label='Entregadores da empresa';}
        elseif($audienceType==='company_all'){$q=db()->prepare("SELECT id FROM users WHERE status='active' AND company_id=? UNION SELECT u.id FROM company_couriers cc JOIN users u ON u.id=cc.user_id WHERE cc.company_id=? AND cc.status='active' AND u.status='active'");$q->execute([$companyId,$companyId]);$rows=$q->fetchAll();$label='Todos vinculados à empresa';}
        elseif($audienceType==='user'){
            $q=db()->prepare("SELECT u.id,u.name,u.public_id FROM users u WHERE u.status='active' AND (upper(u.public_id)=upper(?) OR lower(u.email)=lower(?) OR u.id=?) AND (u.company_id=? OR EXISTS(SELECT 1 FROM company_couriers cc WHERE cc.company_id=? AND cc.user_id=u.id AND cc.status='active')) LIMIT 1");
            $q->execute([$audienceValue,$audienceValue,(int)$audienceValue,$companyId,$companyId]);$row=$q->fetch();$rows=$row?[['id'=>$row['id']]]:[];$label=$row?'Usuário: '.$row['name'].' ('.$row['public_id'].')':'Usuário individual';$audienceValue=$row?(string)$row['public_id']:$audienceValue;
        }else throw new RuntimeException('Escolha o público da notificação.');
    }
    foreach($rows as $row){$id=(int)($row['id']??0);if($id>0)$ids[$id]=$id;}
    return ['ids'=>array_values($ids),'type'=>$audienceType,'value'=>$audienceValue,'label'=>$label];
}

function home_manual_notifications($userId, $limit = 4): array {
    $q=db()->prepare("SELECT * FROM notifications WHERE user_id=? AND home_visible=1 AND broadcast_id IS NOT NULL AND (expires_at IS NULL OR expires_at='' OR expires_at>=?) ORDER BY id DESC LIMIT ?");
    $q->bindValue(1,(int)$userId,PDO::PARAM_INT);$q->bindValue(2,now(),PDO::PARAM_STR);$q->bindValue(3,max(1,(int)$limit),PDO::PARAM_INT);$q->execute();
    return $q->fetchAll();
}

function bank_role_options(): array {
    // Clínica é uma empresa, mas precisa poder ser liberada/bloqueada separadamente.
    return ['clinic'=>'Clínicas'] + notification_role_options();
}

function bank_allowed_for_user(array $bank, $user = null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    if(in_array(($user['role']??''),['super_admin','sub_admin'],true))return true;
    $allowed=trim((string)($bank['allowed_roles']??'all'));
    if($allowed==='')return false;
    if($allowed==='all')return true;
    $tokens=array_filter(array_map('trim',explode(',',$allowed)));
    $companyId=resolve_company_id_for_user($user);
    // Contas ligadas a clínicas obedecem ao alvo especial "clinic", permitindo
    // ao administrador liberar bancos para clínicas sem liberar para toda empresa.
    if($companyId>0 && company_is_clinic($companyId)) return in_array('clinic',$tokens,true);
    return in_array((string)$user['role'],$tokens,true);
}

function user_has_bank_access($user = null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    if(in_array(($user['role']??''),['super_admin','sub_admin'],true))return true;
    $q=db()->query("SELECT allowed_roles FROM banks WHERE status='active' ORDER BY id");
    foreach($q->fetchAll() as $bank)if(bank_allowed_for_user($bank,$user))return true;
    return false;
}

function content_limit_value(string $key): int { return max(0,(int)setting($key,'0')); }

function product_limit_status(int $companyId): array {
    $limit=content_limit_value('max_products_per_company');
    $q=db()->prepare('SELECT COUNT(*) FROM products WHERE company_id=? AND deleted_at IS NULL');$q->execute([$companyId]);$used=(int)$q->fetchColumn();
    return ['limit'=>$limit,'used'=>$used,'remaining'=>$limit>0?max(0,$limit-$used):null,'reached'=>$limit>0&&$used>=$limit];
}

function publication_count_for_user(int $userId): int {
    $total=0;
    $q=db()->prepare('SELECT COUNT(*) FROM services WHERE provider_id=? AND deleted_at IS NULL');$q->execute([$userId]);$total+=(int)$q->fetchColumn();
    foreach([['properties','owner_id'],['vehicles','owner_user_id']] as $def){$q=db()->prepare('SELECT COUNT(*) FROM '.$def[0].' WHERE '.$def[1].'=?');$q->execute([$userId]);$total+=(int)$q->fetchColumn();}
    return $total;
}

function publication_limit_status(int $userId): array {
    $limit=content_limit_value('max_publications_per_user');$used=publication_count_for_user($userId);
    return ['limit'=>$limit,'used'=>$used,'remaining'=>$limit>0?max(0,$limit-$used):null,'reached'=>$limit>0&&$used>=$limit];
}



/**
 * Normaliza CPF/CNPJ para comparação e armazenamento seguro.
 */
function only_digits($value) {
    return preg_replace('/\D+/', '', (string)$value);
}

function format_cpf($value) {
    $digits = only_digits($value);
    if (strlen($digits) !== 11) return (string)$value;
    return substr($digits,0,3).'.'.substr($digits,3,3).'.'.substr($digits,6,3).'-'.substr($digits,9,2);
}

function validate_cpf($value) {
    $cpf = only_digits($value);
    if (strlen($cpf) !== 11) return false;
    if (preg_match('/^(\d)\1{10}$/', $cpf)) return false;
    for ($t = 9; $t < 11; $t++) {
        $sum = 0;
        for ($i = 0; $i < $t; $i++) $sum += (int)$cpf[$i] * (($t + 1) - $i);
        $digit = ((10 * $sum) % 11) % 10;
        if ((int)$cpf[$t] !== $digit) return false;
    }
    return true;
}

function validate_document_or_throw($value, $currentUserId = 0) {
    $digits = only_digits($value);
    if ($digits === '') return '';
    if (strlen($digits) === 11) {
        if (!validate_cpf($digits)) throw new RuntimeException('CPF inválido. Verifique o número informado.');
        $stmt = db()->prepare("SELECT id FROM users WHERE replace(replace(replace(document,'.',''),'-',''),'/','')=? AND id<>? LIMIT 1");
        $stmt->execute([$digits, (int)$currentUserId]);
        if ($stmt->fetchColumn()) throw new RuntimeException('Este CPF já está cadastrado no sistema.');
        return format_cpf($digits);
    }
    if (strlen($digits) === 14) return $digits;
    throw new RuntimeException('Informe um CPF com 11 números ou CNPJ com 14 números.');
}

/**
 * Upload de documentos usados em propostas, financiamentos e cadastros.
 */
function upload_document($field, $folder, $current = '', $maxBytes = 12582912) {
    if (!isset($_FILES[$field]) || !is_array($_FILES[$field])) return (string)$current;
    $file = $_FILES[$field];
    $error = isset($file['error']) ? (int)$file['error'] : UPLOAD_ERR_NO_FILE;
    if ($error === UPLOAD_ERR_NO_FILE) return (string)$current;
    if ($error !== UPLOAD_ERR_OK) throw new RuntimeException('Não foi possível enviar o documento.');
    $tmp = (string)($file['tmp_name'] ?? '');
    $size = (int)($file['size'] ?? 0);
    if ($tmp === '' || !is_uploaded_file($tmp)) throw new RuntimeException('Documento inválido.');
    if ($size < 1 || $size > $maxBytes) throw new RuntimeException('O documento deve ter no máximo 12 MB.');

    $mime = '';
    if (class_exists('finfo')) {
        $finfo = new finfo(FILEINFO_MIME_TYPE);
        $mime = (string)$finfo->file($tmp);
    }
    $allowed = [
        'application/pdf' => 'pdf',
        'image/jpeg' => 'jpg',
        'image/png' => 'png',
        'image/webp' => 'webp',
    ];
    if (!isset($allowed[$mime])) throw new RuntimeException('Formato não permitido. Envie PDF, JPG, PNG ou WebP.');

    $safeFolder = trim(preg_replace('/[^a-z0-9_\/-]/i', '', (string)$folder), '/');
    if ($safeFolder === '') $safeFolder = 'documentos';
    $relativeDir = 'uploads/' . $safeFolder . '/' . date('Y/m');
    $absoluteDir = dirname(__DIR__) . '/' . $relativeDir;
    if (!is_dir($absoluteDir) && !mkdir($absoluteDir, 0755, true) && !is_dir($absoluteDir)) {
        throw new RuntimeException('Não foi possível criar a pasta de documentos.');
    }
    $name = bin2hex(random_bytes(18)) . '.' . $allowed[$mime];
    $destination = $absoluteDir . '/' . $name;
    if (!move_uploaded_file($tmp, $destination)) throw new RuntimeException('Não foi possível salvar o documento.');
    @chmod($destination, 0644);
    $newPath = $relativeDir . '/' . $name;
    delete_local_upload($current, $newPath);
    return $newPath;
}

/**
 * Upload de galeria com várias imagens.
 */
function upload_image_gallery($field, $folder, $maxFiles = 12, $maxBytes = 8388608) {
    if (!isset($_FILES[$field]) || !is_array($_FILES[$field])) return [];
    $files = $_FILES[$field];
    if (!isset($files['name']) || !is_array($files['name'])) return [];
    $saved = [];
    $count = min(count($files['name']), $maxFiles);
    for ($i = 0; $i < $count; $i++) {
        if ((int)$files['error'][$i] === UPLOAD_ERR_NO_FILE) continue;
        $_FILES['__gallery_single'] = [
            'name' => $files['name'][$i],
            'type' => $files['type'][$i],
            'tmp_name' => $files['tmp_name'][$i],
            'error' => $files['error'][$i],
            'size' => $files['size'][$i],
        ];
        $saved[] = upload_image('__gallery_single', $folder, '', $maxBytes);
        unset($_FILES['__gallery_single']);
    }
    return $saved;
}

function marketplace_score(array $item) {
    $rating = (float)($item['rating_avg'] ?? 0);
    $reviews = (int)($item['rating_count'] ?? 0);
    $completed = (int)($item['completed_orders'] ?? 0);
    $response = max(1, (int)($item['response_minutes'] ?? 60));
    $verified = !empty($item['verified']) ? 1 : 0;
    $premium = !empty($item['premium']) ? 1 : 0;
    return ($rating / 5 * 50)
        + min(20, log10(max(1, $reviews) + 1) * 8)
        + min(15, log10(max(1, $completed) + 1) * 5)
        + max(0, 10 - min(10, $response / 12))
        + ($verified ? 5 : 0)
        + ($premium ? 2 : 0);
}

function stars_html($rating) {
    $rating = max(0, min(5, (float)$rating));
    $full = (int)round($rating);
    return '<span class="stars" aria-label="' . e(number_format($rating,1,',','.')) . ' de 5">' .
        str_repeat('★', $full) . str_repeat('☆', 5 - $full) . '</span>';
}


function format_phone($value) {
    $d = only_digits($value);
    if (in_array(strlen($d), [12,13], true) && substr($d,0,2) === '55') $d = substr($d,2);
    if (strlen($d) === 11) return '(' . substr($d,0,2) . ') ' . substr($d,2,5) . '-' . substr($d,7,4);
    if (strlen($d) === 10) return '(' . substr($d,0,2) . ') ' . substr($d,2,4) . '-' . substr($d,6,4);
    return (string)$value;
}

function whatsapp_digits($value) {
    $d = only_digits($value);
    if ($d === '') return '';
    if (strlen($d) <= 11) $d = '55' . $d;
    return $d;
}

function whatsapp_url($value, $message = '', $targetUserId = 0, $targetCompanyId = 0, $source = '') {
    $d = whatsapp_digits($value);
    if ($d === '') return '#';
    $params=['phone'=>$d];
    if ($message !== '') $params['message']=$message;
    if ((int)$targetUserId>0) $params['user']=(int)$targetUserId;
    if ((int)$targetCompanyId>0) $params['company']=(int)$targetCompanyId;
    if (trim((string)$source)!=='') $params['source']=trim((string)$source);
    return url('whatsapp.php?'.http_build_query($params));
}

function maps_link($address, $lat = null, $lng = null) {
    if ($lat !== null && $lng !== null && $lat !== '' && $lng !== '') {
        return 'https://www.google.com/maps/search/?api=1&query=' . rawurlencode((string)$lat . ',' . (string)$lng);
    }
    return 'https://www.google.com/maps/search/?api=1&query=' . rawurlencode((string)$address);
}

function company_is_car_wash(int $companyId): bool {
    if ($companyId < 1) return false;
    try {
        $q = db()->prepare("SELECT c.type,COALESCE(c.sector,'') sector,COALESCE(s.name,'') segment_name FROM companies c LEFT JOIN segments s ON s.slug=c.type WHERE c.id=? LIMIT 1");
        $q->execute([$companyId]);
        $row = $q->fetch();
        if (!$row) return false;
        $probe = strtolower(trim((string)($row['type'] ?? '') . ' ' . (string)($row['sector'] ?? '') . ' ' . (string)($row['segment_name'] ?? '')));
        $probe = str_replace(['-', '_'], ' ', $probe);
        return strpos($probe, 'car wash') !== false || strpos($probe, 'lava jato') !== false || strpos($probe, 'lava-jato') !== false || trim((string)($row['type'] ?? '')) === 'car_wash';
    } catch (Throwable $e) {
        return false;
    }
}


function company_is_clinic(int $companyId): bool {
    if ($companyId < 1) return false;
    // Segmentos de gás não podem herdar classificação clínica por dados antigos de usuário/segmento.
    if (function_exists('company_is_gas') && company_is_gas($companyId)) return false;
    try {
        $q = db()->prepare("SELECT c.type,COALESCE(c.sector,''),COALESCE(s.name,'') segment_name,
            COALESCE(ou.segment_slug,'') owner_segment,
            COALESCE((SELECT GROUP_CONCAT(DISTINCT COALESCE(mu.segment_slug,'')) FROM users mu WHERE mu.company_id=c.id),'') member_segments
            FROM companies c
            LEFT JOIN segments s ON s.slug=c.type
            LEFT JOIN users ou ON ou.id=c.owner_user_id
            WHERE c.id=? LIMIT 1");
        $q->execute([$companyId]);
        $row = $q->fetch();
        if (!$row) return false;
        $probe = strtolower(trim((string)($row['type'] ?? '') . ' ' . (string)($row['sector'] ?? '') . ' ' . (string)($row['segment_name'] ?? '') . ' ' . (string)($row['owner_segment'] ?? '') . ' ' . (string)($row['member_segments'] ?? '')));
        $probe = str_replace(['-', '_'], ' ', $probe);
        return trim((string)($row['type'] ?? '')) === 'clinic' || strpos($probe, 'clinic') !== false || strpos($probe, 'clínic') !== false;
    } catch (Throwable $e) {
        return false;
    }
}


// ===== LinkExpress 2.8.54 — helpers do módulo clínico =====
function clinic_settings(int $companyId): array {
    $defaults=[
        'company_id'=>$companyId,'opening_hours'=>'Segunda a sexta, 08:00–18:00','allow_self_booking'=>1,'allow_reschedule'=>0,
        'online_payment_enabled'=>0,'payment_method'=>'pix','pix_key'=>'','payment_link'=>'','professional_limit'=>0,'patient_limit'=>0,'attendant_limit'=>0,
        'daily_consultation_limit'=>0,'daily_exam_limit'=>0,'checkin_radius_meters'=>250
    ];
    if($companyId<1)return $defaults;
    try{
        $q=db()->prepare('SELECT * FROM clinic_settings WHERE company_id=? LIMIT 1');$q->execute([$companyId]);$row=$q->fetch();
        if(!$row){
            db()->prepare('INSERT OR IGNORE INTO clinic_settings(company_id,opening_hours,allow_self_booking,allow_reschedule,online_payment_enabled,payment_method,professional_limit,patient_limit,attendant_limit,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?)')
              ->execute([$companyId,$defaults['opening_hours'],1,0,0,'pix',0,0,0,now(),now()]);
            return $defaults;
        }
        return array_merge($defaults,$row);
    }catch(Throwable $e){return $defaults;}
}

function clinic_capacity_info(int $companyId,string $kind): array {
    $map=['professionals'=>['column'=>'professional_limit','label'=>'profissionais'],'patients'=>['column'=>'patient_limit','label'=>'pacientes'],'attendants'=>['column'=>'attendant_limit','label'=>'atendentes']];
    if($companyId<1||!isset($map[$kind]))return ['limit'=>0,'used'=>0,'remaining'=>0,'unlimited'=>true,'reached'=>false,'label'=>$kind];
    $settings=clinic_settings($companyId);$limit=max(0,(int)($settings[$map[$kind]['column']]??0));
    if($kind==='professionals'){$q=db()->prepare("SELECT COUNT(*) FROM clinic_professionals WHERE company_id=? AND status<>'inactive'");}
    elseif($kind==='patients'){$q=db()->prepare("SELECT COUNT(*) FROM clinic_patients WHERE company_id=? AND status<>'inactive'");}
    else{$q=db()->prepare("SELECT COUNT(*) FROM users WHERE company_id=? AND role='employee' AND status<>'blocked'");}
    $q->execute([$companyId]);$used=(int)$q->fetchColumn();$unlimited=$limit===0;
    return ['limit'=>$limit,'used'=>$used,'remaining'=>$unlimited?PHP_INT_MAX:max(0,$limit-$used),'unlimited'=>$unlimited,'enabled'=>true,'reached'=>!$unlimited&&$used>=$limit,'label'=>$map[$kind]['label']];
}

function clinic_capacity_text(int $companyId,string $kind): string {
    $i=clinic_capacity_info($companyId,$kind);return $i['unlimited']?($i['used'].' / ilimitado'):($i['used'].' / '.$i['limit']);
}

function assert_clinic_capacity(int $companyId,string $kind): array {
    $i=clinic_capacity_info($companyId,$kind);if($i['reached'])throw new RuntimeException('Limite de '.$i['label'].' atingido para esta clínica ('.$i['used'].'/'.$i['limit'].').');return $i;
}

function clinic_professional_for_user(int $userId,int $companyId=0): ?array {
    if($userId<1)return null;
    try{
        $sql="SELECT p.*,c.name company_name,c.phone company_phone,c.city company_city,c.state company_state,s.name specialty_name,s.price specialty_price FROM clinic_professionals p JOIN companies c ON c.id=p.company_id LEFT JOIN clinic_specialties s ON s.id=p.specialty_id WHERE p.user_id=? AND p.status='active'";
        $params=[$userId];if($companyId>0){$sql.=' AND p.company_id=?';$params[]=$companyId;}$sql.=' ORDER BY p.id LIMIT 1';
        $q=db()->prepare($sql);$q->execute($params);$row=$q->fetch();return $row?:null;
    }catch(Throwable $e){return null;}
}

function clinic_patient_for_user(int $userId,int $companyId=0): ?array {
    if($userId<1)return null;
    try{
        $sql="SELECT p.*,c.name company_name,c.city company_city,c.state company_state FROM clinic_patients p JOIN companies c ON c.id=p.company_id WHERE p.platform_user_id=? AND p.status='active'";
        $params=[$userId];if($companyId>0){$sql.=' AND p.company_id=?';$params[]=$companyId;}$sql.=' ORDER BY p.id LIMIT 1';
        $q=db()->prepare($sql);$q->execute($params);$row=$q->fetch();return $row?:null;
    }catch(Throwable $e){return null;}
}



function service_booking_mode_label(string $mode): string {
    return $mode === 'arrival' ? 'Ordem de chegada' : 'Horário marcado';
}

function service_payment_options_for_provider(int $providerId): array {
    if ($providerId < 1) return ['pix_key'=>'','payment_link'=>''];
    try {
        $q=db()->prepare('SELECT pix_key,personal_payment_link AS payment_link FROM users WHERE id=? LIMIT 1');
        $q->execute([$providerId]); $u=$q->fetch() ?: [];
        return ['pix_key'=>(string)($u['pix_key']??''),'payment_link'=>(string)($u['payment_link']??'')];
    } catch(Throwable $e) { return ['pix_key'=>'','payment_link'=>'']; }
}

function bus_company_id_for_user(array $user): int {
    $uid=(int)($user['id']??0); if($uid<1) return 0;
    $company=(int)($user['company_id']??0); if($company>0) return $company;
    try { $q=db()->prepare('SELECT id FROM companies WHERE owner_user_id=? LIMIT 1'); $q->execute([$uid]); return (int)$q->fetchColumn(); } catch(Throwable $e) { return 0; }
}

function bus_generate_booking_code(): string {
    do { $code='LEXBUS-'.strtoupper(bin2hex(random_bytes(4))); $q=db()->prepare('SELECT 1 FROM bus_bookings WHERE booking_code=?'); $q->execute([$code]); } while($q->fetchColumn());
    return $code;
}

function bus_segment_occupied(int $tripId,int $seatId,int $originStopId,int $destinationStopId,int $ignoreBookingId=0): bool {
    $q=db()->prepare("SELECT b.id FROM bus_bookings b JOIN bus_trip_stops o ON o.id=b.origin_stop_id JOIN bus_trip_stops d ON d.id=b.destination_stop_id JOIN bus_trip_stops no ON no.id=? JOIN bus_trip_stops nd ON nd.id=? WHERE b.trip_id=? AND b.seat_id=? AND b.status IN ('reserved','confirmed','paid','boarded') AND b.id<>? AND o.stop_order < nd.stop_order AND no.stop_order < d.stop_order LIMIT 1");
    $q->execute([$originStopId,$destinationStopId,$tripId,$seatId,$ignoreBookingId]); return (bool)$q->fetchColumn();
}

function bus_available_seats(int $tripId,int $originStopId,int $destinationStopId): array {
    $q=db()->prepare('SELECT s.* FROM bus_seats s JOIN bus_trips t ON t.bus_id=s.bus_id WHERE t.id=? AND s.active=TRUE ORDER BY s.row_number,s.column_number,s.seat_number');
    $q->execute([$tripId]); $out=[];
    foreach($q->fetchAll() as $seat){ $seat['occupied']=bus_segment_occupied($tripId,(int)$seat['id'],$originStopId,$destinationStopId)?1:0; $out[]=$seat; }
    return $out;
}

function clinic_patient_memberships(int $userId): array {
    if($userId<1)return [];
    try{$q=db()->prepare("SELECT p.*,c.name company_name,c.logo company_logo,c.city company_city,c.state company_state FROM clinic_patients p JOIN companies c ON c.id=p.company_id WHERE p.platform_user_id=? AND p.status='active' ORDER BY c.name");$q->execute([$userId]);return $q->fetchAll();}catch(Throwable $e){return [];}
}

function clinic_weekday_label(int $weekday): string {
    $map=['Domingo','Segunda','Terça','Quarta','Quinta','Sexta','Sábado'];return $map[$weekday]??'Dia';
}

function clinic_professional_schedule_text(int $professionalId): string {
    try{$q=db()->prepare("SELECT weekday,start_time,end_time FROM clinic_professional_availability WHERE professional_id=? AND status='active' ORDER BY weekday");$q->execute([$professionalId]);$rows=$q->fetchAll();$parts=[];foreach($rows as $r)$parts[]=clinic_weekday_label((int)$r['weekday']).' '.substr($r['start_time'],0,5).'–'.substr($r['end_time'],0,5);return implode(' • ',$parts);}catch(Throwable $e){return '';}
}

function clinic_professional_daily_limit(int $professionalId,string $kind): int {
    if($professionalId<1)return 0;
    $column=$kind==='exam'?'daily_exam_limit':'daily_consultation_limit';
    try{
        $q=db()->prepare("SELECT p.$column,cs.$column clinic_default FROM clinic_professionals p LEFT JOIN clinic_settings cs ON cs.company_id=p.company_id WHERE p.id=? LIMIT 1");
        $q->execute([$professionalId]);$row=$q->fetch();if(!$row)return 0;
        $own=max(0,(int)($row[$column]??0));$fallback=max(0,(int)($row['clinic_default']??0));
        return $own>0?$own:$fallback;
    }catch(Throwable $e){return 0;}
}

function clinic_professional_daily_capacity(int $professionalId,string $date,string $kind='consultation'): array {
    $limit=clinic_professional_daily_limit($professionalId,$kind);$used=0;
    if($professionalId>0&&preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)){
        try{
            if($kind==='exam')$q=db()->prepare("SELECT COUNT(*) FROM clinic_exam_requests WHERE professional_id=? AND scheduled_date=? AND status NOT IN ('cancelled','completed')");
            else $q=db()->prepare("SELECT COUNT(*) FROM clinic_appointments WHERE professional_id=? AND appointment_date=? AND status NOT IN ('cancelled','completed')");
            $q->execute([$professionalId,$date]);$used=(int)$q->fetchColumn();
        }catch(Throwable $e){}
    }
    $unlimited=$limit===0;$remaining=$unlimited?PHP_INT_MAX:max(0,$limit-$used);
    return ['limit'=>$limit,'used'=>$used,'remaining'=>$remaining,'unlimited'=>$unlimited,'reached'=>!$unlimited&&$used>=$limit];
}

function clinic_assert_daily_capacity(int $professionalId,string $date,string $kind='consultation'): array {
    $i=clinic_professional_daily_capacity($professionalId,$date,$kind);
    if($i['reached'])throw new RuntimeException('Não há mais vagas deste profissional para '.date('d/m/Y',strtotime($date)).'. Escolha outra data ou profissional.');
    return $i;
}

function clinic_next_arrival_order(int $professionalId,string $date): int {
    if($professionalId<1)return 1;
    try{$q=db()->prepare("SELECT COALESCE(MAX(arrival_order),0)+1 FROM clinic_appointments WHERE professional_id=? AND appointment_date=? AND status<>'cancelled'");$q->execute([$professionalId,$date]);return max(1,(int)$q->fetchColumn());}catch(Throwable $e){return 1;}
}


function clinic_available_dates_for_professional(int $professionalId,int $days=60,string $kind='consultation'): array {
    $out=[]; $days=max(1,min(180,$days));
    for($i=0;$i<$days;$i++){ $date=date('Y-m-d',strtotime('+'.$i.' days')); $cap=clinic_professional_daily_capacity($professionalId,$date,$kind); if($cap['reached'])continue; if($kind==='consultation' && !clinic_available_slots($professionalId,$date,30))continue; $out[]=$date; }
    return $out;
}

function clinic_available_slots(int $professionalId,string $date,int $durationMinutes=30): array {
    if($professionalId<1||!preg_match('/^\\d{4}-\\d{2}-\\d{2}$/',$date))return [];
    $daily=clinic_professional_daily_capacity($professionalId,$date,'consultation');if($daily['reached'])return [];
    try{
        $weekday=(int)date('w',strtotime($date));
        $q=db()->prepare("SELECT start_time,end_time FROM clinic_professional_availability WHERE professional_id=? AND weekday=? AND status='active' LIMIT 1");$q->execute([$professionalId,$weekday]);$av=$q->fetch();if(!$av)return [];
        $q=db()->prepare("SELECT COUNT(*) FROM clinic_professional_blocks WHERE professional_id=? AND block_date=? AND (start_time IS NULL OR start_time='' OR end_time IS NULL OR end_time='')");$q->execute([$professionalId,$date]);if((int)$q->fetchColumn()>0)return [];
        $duration=max(15,min(240,$durationMinutes));$step=$duration;
        $start=strtotime($date.' '.substr($av['start_time'],0,5));$end=strtotime($date.' '.substr($av['end_time'],0,5));$slots=[];
        for($t=$start;$t+$duration*60<=$end;$t+=$step*60){
            $slot=date('H:i',$t);$slotEnd=date('H:i',$t+$duration*60);
            $b=db()->prepare("SELECT COUNT(*) FROM clinic_professional_blocks WHERE professional_id=? AND block_date=? AND COALESCE(start_time,'')<>'' AND COALESCE(end_time,'')<>'' AND NOT(end_time<=? OR start_time>=?)");$b->execute([$professionalId,$date,$slot,$slotEnd]);if((int)$b->fetchColumn()>0)continue;
            $a=db()->prepare("SELECT COUNT(*) FROM clinic_appointments a LEFT JOIN clinic_specialties s ON s.id=a.specialty_id WHERE a.professional_id=? AND a.appointment_date=? AND a.status NOT IN ('cancelled','completed') AND NOT(time(a.appointment_time,'+'||COALESCE(s.duration_minutes,30)||' minutes')<=time(?) OR time(a.appointment_time)>=time(?))");$a->execute([$professionalId,$date,$slot,$slotEnd]);if((int)$a->fetchColumn()>0)continue;
            if($date===date('Y-m-d')&&$t<=time())continue;$slots[]=$slot;
        }
        return $slots;
    }catch(Throwable $e){return [];}
}

function clinic_slot_is_available(int $professionalId,string $date,string $time,int $durationMinutes=30): bool {
    return in_array(substr($time,0,5),clinic_available_slots($professionalId,$date,$durationMinutes),true);
}

function clinic_exam_weekdays(array $specialty): array {
    $raw=trim((string)($specialty['exam_weekdays']??''));
    if($raw!==''){
        $out=[];foreach(explode(',',$raw) as $v){$n=(int)trim($v);if($n>=0&&$n<=6)$out[$n]=1;}
        if($out){$days=array_keys($out);sort($days);return $days;}
    }
    $text=mb_strtolower(trim((string)($specialty['available_days']??'')),'UTF-8');
    $plain=@iconv('UTF-8','ASCII//TRANSLIT',$text);if($plain!==false)$text=strtolower($plain);
    if($text==='')return [];
    if(str_contains($text,'segunda a sexta')||str_contains($text,'segunda-feira a sexta'))return [1,2,3,4,5];
    if(str_contains($text,'segunda a sabado')||str_contains($text,'segunda-feira a sabado'))return [1,2,3,4,5,6];
    if(str_contains($text,'todos os dias')||str_contains($text,'domingo a sabado'))return [0,1,2,3,4,5,6];
    $map=[0=>['dom'],1=>['seg'],2=>['ter'],3=>['qua'],4=>['qui'],5=>['sex'],6=>['sab']];$out=[];
    foreach($map as $day=>$tokens)foreach($tokens as $token)if(preg_match('/\\b'.preg_quote($token,'/').'[a-z-]*\\b/i',$text)){$out[$day]=1;break;}
    $days=array_keys($out);sort($days);return $days;
}

function clinic_exam_schedule(array $specialty): array {
    $mode=(string)($specialty['exam_booking_mode']??'time');if(!in_array($mode,['time','arrival'],true))$mode='time';
    $start=substr(trim((string)($specialty['exam_start_time']??'')),0,5);$end=substr(trim((string)($specialty['exam_end_time']??'')),0,5);
    if(car_wash_minutes($start)===null||car_wash_minutes($end)===null||car_wash_minutes($end)<=car_wash_minutes($start)){
        $legacy=(string)($specialty['available_hours']??'');
        if(preg_match_all('/\\b([01]?\\d|2[0-3]):[0-5]\\d\\b/',$legacy,$m)&&count($m[0])>=2){$start=str_pad($m[0][0],5,'0',STR_PAD_LEFT);$end=str_pad($m[0][1],5,'0',STR_PAD_LEFT);}else{$start='';$end='';}
    }
    return ['mode'=>$mode,'weekdays'=>clinic_exam_weekdays($specialty),'start'=>$start,'end'=>$end];
}

function clinic_exam_date_allowed(array $specialty,string $date): bool {
    if(!preg_match('/^\\d{4}-\\d{2}-\\d{2}$/',$date)||$date<date('Y-m-d'))return false;
    $sch=clinic_exam_schedule($specialty);if(!$sch['weekdays'])return false;
    return in_array((int)date('w',strtotime($date)),$sch['weekdays'],true);
}

function clinic_exam_available_dates(array $specialty,int $companyId,int $daysAhead=90): array {
    $sch=clinic_exam_schedule($specialty);if(!$sch['weekdays'])return [];$out=[];$settings=clinic_settings($companyId);$limit=max(0,(int)($settings['daily_exam_limit']??0));
    $start=new DateTimeImmutable('today');
    for($i=0;$i<max(1,min(180,$daysAhead));$i++){
        $d=$start->modify('+'.$i.' day');$date=$d->format('Y-m-d');if(!in_array((int)$d->format('w'),$sch['weekdays'],true))continue;
        $used=0;try{$q=db()->prepare("SELECT COUNT(*) FROM clinic_exam_requests WHERE company_id=? AND scheduled_date=? AND status NOT IN ('cancelled','completed')");$q->execute([$companyId,$date]);$used=(int)$q->fetchColumn();}catch(Throwable $e){}
        if($limit>0&&$used>=$limit)continue;$remaining=$limit>0?max(0,$limit-$used):null;
        if($sch['mode']==='time' && !clinic_exam_available_times($specialty,$date))continue;
        $out[]=['date'=>$date,'remaining'=>$remaining,'start'=>$sch['start'],'end'=>$sch['end'],'mode'=>$sch['mode']];
    }
    return $out;
}

function clinic_exam_available_times(array $specialty,string $date): array {
    if(!clinic_exam_date_allowed($specialty,$date))return [];$sch=clinic_exam_schedule($specialty);if($sch['mode']==='arrival')return [];
    if(car_wash_minutes($sch['start'])===null||car_wash_minutes($sch['end'])===null)return [];
    $companyId=(int)($specialty['company_id']??0);$limit=0;$usedDay=0;
    try{if($companyId>0){$settings=clinic_settings($companyId);$limit=max(0,(int)($settings['daily_exam_limit']??0));$q=db()->prepare("SELECT COUNT(*) FROM clinic_exam_requests WHERE company_id=? AND scheduled_date=? AND status NOT IN ('cancelled','completed')");$q->execute([$companyId,$date]);$usedDay=(int)$q->fetchColumn();if($limit>0&&$usedDay>=$limit)return [];}}catch(Throwable $e){}
    $step=max(15,min(240,(int)($specialty['duration_minutes']??30)));$start=car_wash_minutes($sch['start']);$end=car_wash_minutes($sch['end']);$out=[];
    for($m=$start;$m+$step<=$end;$m+=$step){
        $h=sprintf('%02d:%02d',intdiv($m,60),$m%60);if($date===date('Y-m-d')&&$m<=((int)date('H'))*60+(int)date('i'))continue;
        $busy=false;try{$q=db()->prepare("SELECT COUNT(*) FROM clinic_exam_requests WHERE company_id=? AND specialty_id=? AND scheduled_date=? AND scheduled_time=? AND status NOT IN ('cancelled','completed')");$q->execute([$companyId,(int)$specialty['id'],$date,$h]);$busy=(int)$q->fetchColumn()>0;}catch(Throwable $e){}
        if(!$busy)$out[]=$h;
    }
    return $out;
}

function clinic_exam_next_arrival_order(int $companyId,int $specialtyId,string $date): int {
    try{$q=db()->prepare("SELECT COALESCE(MAX(arrival_order),0)+1 FROM clinic_exam_requests WHERE company_id=? AND specialty_id=? AND scheduled_date=? AND booking_mode='arrival' AND status<>'cancelled'");$q->execute([$companyId,$specialtyId,$date]);return max(1,(int)$q->fetchColumn());}catch(Throwable $e){return 1;}
}

function clinic_queue_status_label(string $status): string {
    $map=['scheduled'=>'Confirmado','waiting'=>'Aguardando','in_service'=>'Em atendimento','completed'=>'Finalizado','cancelled'=>'Cancelado','skipped'=>'Senha pulada','no_show'=>'Falta'];
    return $map[$status]??status_label($status);
}

function clinic_queue_mode_label(string $mode): string {
    $map=[
        'virtual_queue'=>'🎫 Fila Virtual',
        'scheduled_time'=>'📅 Hora Marcada',
        'day_queue'=>'📅🎫 Dia Marcado + Fila Virtual',
        'arrival_order'=>'🚶 Por ordem de chegada',
        'presential_ticket'=>'🔢 Senha presencial',
    ];
    return $map[$mode]??$map['day_queue'];
}

function clinic_queue_settings(int $companyId): array {
    $defaults=[
        'company_id'=>$companyId,'attendance_mode'=>'day_queue','agenda_open'=>1,'start_time'=>'08:00','end_time'=>'18:00',
        'max_clients_per_day'=>50,'average_minutes'=>15,'allow_reorder'=>0,
        'message_near'=>'Seu atendimento está próximo.','message_next'=>'Você é o próximo!',
        'message_turn'=>'É sua vez! Dirija-se ao atendimento.','message_cancelled'=>'Seu atendimento foi cancelado.',
        'message_changed'=>'Seu horário foi alterado.'
    ];
    if($companyId<1)return $defaults;
    try{$q=db()->prepare('SELECT * FROM clinic_queue_settings WHERE company_id=?');$q->execute([$companyId]);$row=$q->fetch();if($row)$defaults=array_merge($defaults,$row);}catch(Throwable $e){}
    return $defaults;
}

function ride_fare_settings(string $rideType): array {
    $allowed=['mototaxi','taxi','private_driver'];
    if(!in_array($rideType,$allowed,true))$rideType='private_driver';
    $prefix='ride_'.$rideType.'_';
    return [
        'auto'=>setting($prefix.'auto_fare_enabled',setting('ride_auto_fare_enabled','1'))==='1',
        'price_km'=>(float)setting($prefix.'price_km',setting('ride_price_km','3.50')),
        'base'=>(float)setting($prefix.'base_fare',setting('ride_base_fare','6.00')),
        'min'=>(float)setting($prefix.'min_fare',setting('ride_min_fare','10.00')),
    ];
}

function clinic_queue_professional_can_service(int $companyId,int $professionalId,int $serviceId): bool {
    if($companyId<1||$professionalId<1||$serviceId<1)return false;
    try{
        $q=db()->prepare("SELECT specialty_id FROM clinic_professionals WHERE id=? AND company_id=? AND status='active' AND COALESCE(link_state,'active')='active' LIMIT 1");
        $q->execute([$professionalId,$companyId]);$specialtyId=$q->fetchColumn();if($specialtyId===false)return false;
        $specialtyId=(int)$specialtyId;if($specialtyId===0||$specialtyId===$serviceId)return true;
        $q=db()->prepare("SELECT 1 FROM clinic_queue_days WHERE company_id=? AND ((professional_id=? AND (service_id=? OR service_id IS NULL OR service_id=0)) OR ((professional_id IS NULL OR professional_id=0) AND service_id=?)) LIMIT 1");
        $q->execute([$companyId,$professionalId,$serviceId,$serviceId]);if($q->fetchColumn())return true;
        $q=db()->prepare("SELECT 1 FROM clinic_queue_weekly_rules WHERE company_id=? AND ((professional_id=? AND (service_id=? OR service_id IS NULL OR service_id=0)) OR ((professional_id IS NULL OR professional_id=0) AND service_id=?)) LIMIT 1");
        $q->execute([$companyId,$professionalId,$serviceId,$serviceId]);return (bool)$q->fetchColumn();
    }catch(Throwable $e){return false;}
}

function clinic_queue_company_has_schedule_rules(int $companyId): bool {
    if($companyId<1)return false;
    try{
        $q=db()->prepare("SELECT (SELECT COUNT(*) FROM clinic_queue_days WHERE company_id=?)+(SELECT COUNT(*) FROM clinic_queue_weekly_rules WHERE company_id=?)");
        $q->execute([$companyId,$companyId]);return (int)$q->fetchColumn()>0;
    }catch(Throwable $e){return false;}
}

function clinic_queue_day_rule(int $companyId,string $date,int $professionalId=0,int $serviceId=0): ?array {
    if($companyId<1||!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date))return null;
    try{
        $q=db()->prepare("SELECT * FROM clinic_queue_days WHERE company_id=? AND service_date=? AND (professional_id IS NULL OR professional_id=0 OR professional_id=?) AND (service_id IS NULL OR service_id=0 OR service_id=?) ORDER BY CASE WHEN professional_id=? AND service_id=? THEN 0 WHEN professional_id=? THEN 1 WHEN service_id=? THEN 2 ELSE 3 END,id DESC LIMIT 1");
        $q->execute([$companyId,$date,$professionalId,$serviceId,$professionalId,$serviceId,$professionalId,$serviceId]);
        $row=$q->fetch();if($row)return $row;
        $weekday=(int)date('w',strtotime($date));
        $q=db()->prepare("SELECT *,effective_from service_date FROM clinic_queue_weekly_rules WHERE company_id=? AND weekday=? AND effective_from<=? AND (effective_until IS NULL OR effective_until='' OR effective_until>=?) AND (professional_id IS NULL OR professional_id=0 OR professional_id=?) AND (service_id IS NULL OR service_id=0 OR service_id=?) ORDER BY CASE WHEN professional_id=? AND service_id=? THEN 0 WHEN professional_id=? THEN 1 WHEN service_id=? THEN 2 ELSE 3 END,id DESC LIMIT 1");
        $q->execute([$companyId,$weekday,$date,$date,$professionalId,$serviceId,$professionalId,$serviceId,$professionalId,$serviceId]);
        $row=$q->fetch();if($row)return $row;
        if($professionalId>0){
            $q=db()->prepare("SELECT start_time,end_time FROM clinic_professional_availability WHERE professional_id=? AND weekday=? AND status='active' LIMIT 1");$q->execute([$professionalId,$weekday]);$av=$q->fetch();
            if($av){
                $blocked=false;$b=db()->prepare("SELECT COUNT(*) FROM clinic_professional_blocks WHERE professional_id=? AND block_date=? AND (start_time IS NULL OR start_time='' OR end_time IS NULL OR end_time='')");$b->execute([$professionalId,$date]);$blocked=(int)$b->fetchColumn()>0;
                return ['company_id'=>$companyId,'professional_id'=>$professionalId,'service_id'=>$serviceId,'service_date'=>$date,'status'=>$blocked?'closed':'open','start_time'=>$av['start_time'],'end_time'=>$av['end_time'],'max_clients'=>null,'source'=>'professional_schedule'];
            }
        }
        return null;
    }catch(Throwable $e){return null;}
}

function clinic_queue_client_is_blocked(int $companyId,int $platformUserId): bool {
    if($companyId<1||$platformUserId<1)return false;
    try{$q=db()->prepare('SELECT blocked FROM clinic_queue_client_blocks WHERE company_id=? AND platform_user_id=?');$q->execute([$companyId,$platformUserId]);return (int)$q->fetchColumn()===1;}catch(Throwable $e){return false;}
}

function clinic_queue_capacity_for_day(int $companyId,string $date,int $professionalId=0,int $serviceId=0): array {
    $settings=clinic_queue_settings($companyId);$rule=clinic_queue_day_rule($companyId,$date,$professionalId,$serviceId);
    $max=max(1,(int)($rule['max_clients']??$settings['max_clients_per_day']??50));
    $start=substr((string)($rule['start_time']??$settings['start_time']??'08:00'),0,5);$end=substr((string)($rule['end_time']??$settings['end_time']??'18:00'),0,5);
    $status=(string)($rule['status']??'open');$agendaOpen=(int)($settings['agenda_open']??1)===1;
    // Quando a clínica configurou dias/regras específicos, somente esses dias ficam disponíveis.
    // Uma regra explicitamente aberta representa um dia disponibilizado pela clínica, mesmo se a agenda geral estiver fechada.
    $hasRules=clinic_queue_company_has_schedule_rules($companyId);
    if($rule)$open=!in_array($status,['closed','holiday'],true);else $open=$agendaOpen&&!$hasRules;
    return ['open'=>$open,'max'=>$max,'start'=>$start,'end'=>$end,'rule'=>$rule,'settings'=>$settings,'has_rules'=>$hasRules];
}

function clinic_queue_time_slots_for_day(int $companyId,int $professionalId,int $serviceId,string $date,int $durationMinutes=0): array {
    $cap=clinic_queue_capacity_for_day($companyId,$date,$professionalId,$serviceId);if(!$cap['open'])return [];
    $settings=$cap['settings'];$step=$durationMinutes>0?$durationMinutes:(int)($settings['average_minutes']??15);$step=max(5,min(240,$step));
    $start=strtotime($date.' '.$cap['start']);$end=strtotime($date.' '.$cap['end']);if(!$start||!$end||$end<=$start)return [];
    $booked=[];try{$q=db()->prepare("SELECT substr(queue_time,1,5) FROM clinic_virtual_queue WHERE company_id=? AND professional_id=? AND queue_date=? AND queue_time IS NOT NULL AND queue_time<>'' AND status NOT IN ('cancelled','completed','skipped','no_show')");$q->execute([$companyId,$professionalId,$date]);$booked=array_fill_keys(array_map('strval',$q->fetchAll(PDO::FETCH_COLUMN)),true);}catch(Throwable $e){}
    $slots=[];for($t=$start;$t+$step*60<=$end;$t+=$step*60){$time=date('H:i',$t);if($date===date('Y-m-d')&&$t<=time())continue;if(isset($booked[$time]))continue;$slots[]=$time;}return $slots;
}

function clinic_queue_stats(array $row): array {
    $ahead=0;$estimate=0;$status=(string)($row['status']??'');
    if(in_array($status,['waiting','scheduled'],true)){
        try{
            $pos=(int)(($row['sort_order']??0)>0?$row['sort_order']:$row['queue_number']);
            $q=db()->prepare("SELECT COUNT(*) FROM clinic_virtual_queue WHERE company_id=? AND queue_date=? AND professional_id=? AND (CASE WHEN sort_order>0 THEN sort_order ELSE queue_number END)<? AND status IN ('waiting','in_service')");
            $q->execute([(int)$row['company_id'],(string)$row['queue_date'],(int)$row['professional_id'],$pos]);$ahead=(int)$q->fetchColumn();
        }catch(Throwable $e){}
        $settings=clinic_queue_settings((int)($row['company_id']??0));
        $duration=max(5,(int)($settings['average_minutes']??0));if($duration===5)$duration=max(5,(int)($row['duration_minutes']??15));$estimate=$ahead*$duration;
    }
    $eta='';
    if(!empty($row['queue_time']))$eta=substr((string)$row['queue_time'],0,5);
    elseif(($row['queue_date']??'')===date('Y-m-d')&&$estimate>0)$eta=date('H:i',time()+($estimate*60));
    return ['people_ahead'=>$ahead,'estimate_minutes'=>$estimate,'estimated_time'=>$eta];
}

function clinic_queue_can_manage_entry(array $user,array $entry): bool {
    $uid=(int)($user['id']??0);$companyId=(int)($entry['company_id']??0);
    if($uid<1||$companyId<1)return false;
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true))return true;
    if(in_array((string)($user['role']??''),['company','employee'],true)&&resolve_company_id_for_user($user)===$companyId&&company_is_clinic($companyId))return true;
    if((string)($user['role']??'')==='clinic_professional'&&(int)($entry['professional_id']??0)>0){
        try{$q=db()->prepare("SELECT COUNT(*) FROM clinic_professionals WHERE id=? AND user_id=? AND company_id=? AND status='active' AND COALESCE(link_state,'active')='active'");$q->execute([(int)$entry['professional_id'],$uid,$companyId]);return (int)$q->fetchColumn()>0;}catch(Throwable $e){}
    }
    return false;
}

function process_clinic_reminders(): int {
    try{
        $rows=db()->query("SELECT a.id,a.appointment_date,a.appointment_time,a.reminder_24h_sent,a.reminder_3h_sent,pa.platform_user_id patient_user_id,pr.user_id professional_user_id,c.name clinic_name FROM clinic_appointments a JOIN clinic_patients pa ON pa.id=a.patient_id LEFT JOIN clinic_professionals pr ON pr.id=a.professional_id JOIN companies c ON c.id=a.company_id WHERE a.status IN ('scheduled','confirmed') AND datetime(a.appointment_date||' '||a.appointment_time) BETWEEN datetime('now') AND datetime('now','+25 hours')")->fetchAll();
        $count=0;$nowTs=time();foreach($rows as $r){$apptTs=strtotime($r['appointment_date'].' '.$r['appointment_time']);$diff=$apptTs-$nowTs;
            $targets=array_values(array_unique(array_filter([(int)$r['patient_user_id'],(int)$r['professional_user_id']])));
            if($diff<=86400&&$diff>10800&&!(int)$r['reminder_24h_sent']){foreach($targets as $uid)create_notification($uid,'Lembrete de consulta','Você tem atendimento em '.$r['clinic_name'].' em '.date('d/m/Y H:i',$apptTs).'.','info','dashboard.php?page=clinic_patient','clinic_appointment',(int)$r['id']);db()->prepare('UPDATE clinic_appointments SET reminder_24h_sent=1 WHERE id=?')->execute([$r['id']]);$count++;}
            if($diff<=10800&&$diff>0&&!(int)$r['reminder_3h_sent']){foreach($targets as $uid)create_notification($uid,'Consulta se aproxima','Seu atendimento em '.$r['clinic_name'].' será às '.date('H:i',$apptTs).'.','warning','dashboard.php?page=clinic_patient','clinic_appointment',(int)$r['id']);db()->prepare('UPDATE clinic_appointments SET reminder_3h_sent=1 WHERE id=?')->execute([$r['id']]);$count++;}
        }return $count;
    }catch(Throwable $e){return 0;}
}


// ===== LinkExpress 2.8.60 — vínculos multi-clínica, permissões e apoio clínico =====
function subadmin_sensitive_modules(): array {
    return ['wallet','fees','backups','settings','banks','charges','subscriptions','plans','segments'];
}

function subadmin_permissions_for_user($user): array {
    if(!$user || (string)($user['role']??'')!=='sub_admin') return [];
    $raw=trim((string)($user['admin_module_permissions']??''));
    if($raw==='') return [];
    $decoded=json_decode($raw,true);
    if(is_array($decoded)) $items=$decoded; else $items=preg_split('/[;,\s]+/',$raw,-1,PREG_SPLIT_NO_EMPTY);
    $allowed=array_values(array_intersect(array_map('strval',$items),subadmin_sensitive_modules()));
    return array_values(array_unique($allowed));
}

function subadmin_has_module_access($user,string $module): bool {
    if(!$user) return false;
    if((string)($user['role']??'')!=='sub_admin') return true;
    return in_array($module,subadmin_permissions_for_user($user),true);
}

function clinic_company_owner_user_id(int $companyId): int {
    if($companyId<1)return 0;
    try{$q=db()->prepare('SELECT owner_user_id FROM companies WHERE id=? LIMIT 1');$q->execute([$companyId]);return (int)$q->fetchColumn();}catch(Throwable $e){return 0;}
}

function clinic_professional_memberships(int $userId,bool $includePending=true): array {
    if($userId<1)return [];
    try{
        $sql="SELECT p.*,c.name company_name,c.logo company_logo,c.phone company_phone,c.city company_city,c.state company_state,s.name specialty_name,s.price specialty_price FROM clinic_professionals p JOIN companies c ON c.id=p.company_id LEFT JOIN clinic_specialties s ON s.id=p.specialty_id WHERE p.user_id=?";
        $params=[$userId];
        if(!$includePending)$sql.=" AND p.status='active' AND COALESCE(p.link_state,'active')='active'";
        $sql.=" ORDER BY CASE WHEN p.status='active' AND COALESCE(p.link_state,'active')='active' THEN 0 ELSE 1 END,c.name,p.id";
        $q=db()->prepare($sql);$q->execute($params);return $q->fetchAll();
    }catch(Throwable $e){return [];}
}

function clinic_professional_active_link_count(int $userId,int $excludeCompanyId=0): int {
    if($userId<1)return 0;
    try{$sql="SELECT COUNT(*) FROM clinic_professionals WHERE user_id=? AND status='active' AND COALESCE(link_state,'active')='active'";$params=[$userId];if($excludeCompanyId>0){$sql.=' AND company_id<>?';$params[]=$excludeCompanyId;}$q=db()->prepare($sql);$q->execute($params);return (int)$q->fetchColumn();}catch(Throwable $e){return 0;}
}

function clinic_professional_multilink_payment_ok(int $userId): bool {
    if($userId<1)return false;
    try{
        $q=db()->prepare('SELECT subscription_expires_at,status FROM users WHERE id=?');$q->execute([$userId]);$u=$q->fetch();
        if($u && ($u['status']??'')==='active' && !empty($u['subscription_expires_at']) && substr((string)$u['subscription_expires_at'],0,10)>=today())return true;
        $q=db()->prepare("SELECT COUNT(*) FROM subscriptions WHERE user_id=? AND status='active' AND end_date>=?");$q->execute([$userId,today()]);return (int)$q->fetchColumn()>0;
    }catch(Throwable $e){return false;}
}

function clinic_professional_link_state_after_accept(int $userId,int $companyId): array {
    $activeElsewhere=clinic_professional_active_link_count($userId,$companyId);
    if($activeElsewhere===1)return ['state'=>'pending_admin','admin_status'=>'pending','billing_required'=>0,'message'=>'Vínculo aceito. Como esta é sua segunda instituição, falta a liberação do Administrador Sênior.'];
    if($activeElsewhere>=2 && !clinic_professional_multilink_payment_ok($userId))return ['state'=>'waiting_payment','admin_status'=>'not_required','billing_required'=>1,'message'=>'Vínculo aceito. A partir do terceiro vínculo é necessária mensalidade ativa para concluir a liberação.'];
    return ['state'=>'active','admin_status'=>'not_required','billing_required'=>$activeElsewhere>=2?1:0,'message'=>'Vínculo com a instituição confirmado.'];
}

function clinic_patient_external_conflicts(int $platformUserId,string $date,string $time,int $excludeCompanyId=0,int $windowMinutes=120): array {
    if($platformUserId<1||!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)||!preg_match('/^\d{2}:\d{2}/',$time))return [];
    try{
        $sql="SELECT a.id,a.company_id,a.appointment_date,a.appointment_time,c.name company_name,pr.name professional_name FROM clinic_appointments a JOIN clinic_patients pa ON pa.id=a.patient_id JOIN companies c ON c.id=a.company_id LEFT JOIN clinic_professionals pr ON pr.id=a.professional_id WHERE pa.platform_user_id=? AND a.appointment_date=? AND a.status NOT IN ('cancelled','completed','no_show')";
        $params=[$platformUserId,$date];if($excludeCompanyId>0){$sql.=' AND a.company_id<>?';$params[]=$excludeCompanyId;}
        $q=db()->prepare($sql);$q->execute($params);$rows=$q->fetchAll();$target=car_wash_minutes(substr($time,0,5));$out=[];
        foreach($rows as $r){$m=car_wash_minutes(substr((string)$r['appointment_time'],0,5));if($target!==null&&$m!==null&&abs($m-$target)<=$windowMinutes)$out[]=$r;}
        return $out;
    }catch(Throwable $e){return [];}
}

function clinic_first_visit(int $companyId,int $patientId,int $professionalId=0): bool {
    if($companyId<1||$patientId<1)return true;
    try{$sql="SELECT COUNT(*) FROM clinic_appointments WHERE company_id=? AND patient_id=? AND status='completed'";$params=[$companyId,$patientId];if($professionalId>0){$sql.=' AND professional_id=?';$params[]=$professionalId;}$q=db()->prepare($sql);$q->execute($params);return (int)$q->fetchColumn()===0;}catch(Throwable $e){return true;}
}

function clinic_sync_professional_contact(int $professionalUserId,int $companyId,int $patientId): void {
    if($professionalUserId<1||$companyId<1||$patientId<1)return;
    try{$q=db()->prepare('SELECT name,phone,email FROM clinic_patients WHERE id=? AND company_id=?');$q->execute([$patientId,$companyId]);$p=$q->fetch();if(!$p)return;
        db()->prepare('INSERT INTO clinic_professional_contacts(owner_user_id,company_id,patient_id,name,phone,email,is_automatic,created_at,updated_at) VALUES(?,?,?,?,?,?,1,?,?) ON CONFLICT(owner_user_id,company_id,patient_id) DO UPDATE SET name=excluded.name,phone=excluded.phone,email=excluded.email,is_automatic=1,updated_at=excluded.updated_at')
          ->execute([$professionalUserId,$companyId,$patientId,$p['name'],$p['phone'],$p['email'],now(),now()]);
    }catch(Throwable $e){}
}

function clinic_report_scope_for_user(int $reportedUserId): array {
    $out=['company_id'=>0,'professional_id'=>0];if($reportedUserId<1)return $out;
    try{$q=db()->prepare("SELECT id FROM companies WHERE owner_user_id=? AND (lower(COALESCE(type,''))='clinic' OR lower(COALESCE(sector,'')) LIKE '%clinic%' OR lower(COALESCE(sector,'')) LIKE '%clínic%') ORDER BY id DESC LIMIT 1");$q->execute([$reportedUserId]);$out['company_id']=(int)$q->fetchColumn();
        $q=db()->prepare("SELECT id,company_id FROM clinic_professionals WHERE user_id=? ORDER BY CASE WHEN status='active' THEN 0 ELSE 1 END,id DESC LIMIT 1");$q->execute([$reportedUserId]);$pr=$q->fetch();if($pr){$out['professional_id']=(int)$pr['id'];if(!$out['company_id'])$out['company_id']=(int)$pr['company_id'];}
    }catch(Throwable $e){}
    return $out;
}

function clinic_super_admin_report_access(int $companyId): bool {
    if($companyId<1||!is_super_admin())return false;
    try{$q=db()->prepare("SELECT COUNT(*) FROM user_reports WHERE reported_company_id=? AND status NOT IN ('finalized','dismissed')");$q->execute([$companyId]);return (int)$q->fetchColumn()>0;}catch(Throwable $e){return false;}
}

function clinic_super_admin_has_any_report_access(): bool {
    if(!is_super_admin())return false;
    try{return (int)db()->query("SELECT COUNT(*) FROM user_reports r JOIN companies c ON c.id=r.reported_company_id WHERE r.status NOT IN ('finalized','dismissed')")->fetchColumn()>0;}catch(Throwable $e){return false;}
}

function clinic_professional_can_access_company(int $userId,int $companyId): bool {
    if($userId<1||$companyId<1)return false;
    try{$q=db()->prepare("SELECT COUNT(*) FROM clinic_professionals WHERE user_id=? AND company_id=? AND status='active' AND COALESCE(link_state,'active')='active'");$q->execute([$userId,$companyId]);return (int)$q->fetchColumn()>0;}catch(Throwable $e){return false;}
}

function clinic_refresh_professional_link_states(int $userId): void {
    if($userId<1||!clinic_professional_multilink_payment_ok($userId))return;
    try{db()->prepare("UPDATE clinic_professionals SET link_state='active',status='active',updated_at=? WHERE user_id=? AND COALESCE(link_state,'')='waiting_payment' AND COALESCE(billing_required,0)=1")->execute([now(),$userId]);}catch(Throwable $e){}
}

function clinic_link_state_label(string $state): string {
    return match($state){
        'pending_professional'=>'Aguardando aceite do profissional',
        'pending_admin'=>'Aguardando Administrador Sênior',
        'waiting_payment'=>'Aguardando mensalidade',
        'rejected'=>'Vínculo recusado',
        default=>'Vínculo ativo',
    };
}

function car_wash_default_services(): array {
    return [
        ['Lavagem simples','Lavagem externa + secagem.'],
        ['Lavagem completa','Lavagem externa + interna + aspiração.'],
        ['Lavagem detalhada','Limpeza minuciosa de toda a parte interna e externa.'],
        ['Lavagem a seco','Pouca ou nenhuma água, com produtos específicos.'],
        ['Lavagem com cera','Lavagem + aplicação de cera para proteção e brilho.'],
        ['Lavagem técnica','Limpeza mais profunda, com produtos profissionais.'],
        ['Lavagem de motor','Limpeza do compartimento do motor com técnicas adequadas.'],
        ['Higienização interna','Bancos, carpetes, teto, portas e painel.'],
        ['Lavagem de bancos','Limpeza profunda de bancos de tecido ou couro.'],
        ['Lavagem de chassi','Limpeza da parte inferior do veículo.'],
        ['Lavagem de motos','Serviço específico para motocicletas.'],
        ['Lavagem de caminhonetes/SUVs','Serviço dimensionado para veículos maiores.'],
        ['Polimento','Correção de riscos leves e recuperação do brilho.'],
        ['Cristalização','Proteção da pintura e aumento do brilho.'],
        ['Vitrificação','Proteção de longa duração da pintura.'],
        ['Lavagem premium','Pacote completo com lavagem, higienização, cera e acabamento.'],
    ];
}

function ensure_car_wash_default_services(int $companyId): void {
    if ($companyId < 1 || !company_is_car_wash($companyId)) return;
    try {
        $q=db()->prepare('SELECT COUNT(*) FROM car_wash_services WHERE company_id=?');
        $q->execute([$companyId]);
        if((int)$q->fetchColumn()>0)return;
        $ins=db()->prepare('INSERT INTO car_wash_services(company_id,name,description,price,duration_minutes,status,sort_order,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?)');
        foreach(car_wash_default_services() as $i=>$row){
            $ins->execute([$companyId,$row[0],$row[1],0,60,'inactive',($i+1)*10,now(),now()]);
        }
    } catch (Throwable $e) {
        @error_log('Lava-Jato catalog: '.$e->getMessage());
    }
}

function car_wash_minutes(string $time): ?int {
    if (!preg_match('/^(\d{2}):(\d{2})(?::\d{2})?$/', trim($time), $m)) return null;
    $h = (int)$m[1]; $min = (int)$m[2];
    if ($h < 0 || $h > 23 || $min < 0 || $min > 59) return null;
    return $h * 60 + $min;
}

function car_wash_hours_for(int $companyId, int $weekday): ?array {
    try {
        $q = db()->prepare("SELECT * FROM car_wash_hours WHERE company_id=? AND weekday=? AND status='active' LIMIT 1");
        $q->execute([$companyId, max(0,min(6,$weekday))]);
        $row = $q->fetch();
        return $row ?: null;
    } catch (Throwable $e) {
        return null;
    }
}

function car_wash_generated_times(int $companyId, int $weekday): array {
    $hours = car_wash_hours_for($companyId, $weekday);
    if (!$hours) return [];
    $start = car_wash_minutes((string)($hours['opening_time'] ?? ''));
    $end = car_wash_minutes((string)($hours['closing_time'] ?? ''));
    $interval = max(15, min(240, (int)($hours['interval_minutes'] ?? 60)));
    if ($start === null || $end === null || $end <= $start) return [];
    $lunchStart = car_wash_minutes((string)($hours['lunch_start_time'] ?? ''));
    $lunchEnd = car_wash_minutes((string)($hours['lunch_end_time'] ?? ''));
    $hasLunch = $lunchStart !== null && $lunchEnd !== null && $lunchEnd > $lunchStart;
    $out = [];
    // O intervalo representa a duração real de cada vaga. A última vaga precisa
    // terminar até o fechamento e nunca pode atravessar o horário de almoço.
    for ($m = $start; $m + $interval <= $end && count($out) < 96; $m += $interval) {
        if($hasLunch && $m < $lunchEnd && ($m + $interval) > $lunchStart)continue;
        $out[] = sprintf('%02d:%02d', intdiv($m,60), $m%60);
    }
    return $out;
}

function car_wash_slot_capacity_for(int $companyId, string $date, string $time): int {
    $weekday = (int)date('w', strtotime($date));
    $normalized = substr(trim($time),0,5);
    try {
        $q = db()->prepare("SELECT capacity FROM car_wash_slots WHERE company_id=? AND weekday=? AND substr(slot_time,1,5)=? AND status='active' LIMIT 1");
        $q->execute([$companyId,$weekday,$normalized]);
        $manual = $q->fetchColumn();
        if ($manual !== false) return max(0,(int)$manual);
    } catch (Throwable $e) {}
    $hours = car_wash_hours_for($companyId,$weekday);
    if (!$hours || !in_array($normalized, car_wash_generated_times($companyId,$weekday), true)) return 0;
    return max(1,(int)($hours['capacity'] ?? 1));
}

// LinkExpress 2.8.20 — disponibilidade do Lava-Jato usada também nos ícones da navegação.
function car_wash_availability_rows(int $companyId, string $date): array {
    if ($companyId < 1 || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) return [];
    $weekday = (int)date('w', strtotime($date));
    $slots = [];
    try {
        $q = db()->prepare("SELECT * FROM car_wash_slots WHERE company_id=? AND weekday=? AND status='active' ORDER BY slot_time");
        $q->execute([$companyId,$weekday]);
        foreach ($q->fetchAll() as $slot) {
            $time = substr((string)$slot['slot_time'],0,5);
            $slot['slot_time'] = $time;
            $slot['source'] = 'manual';
            $slots[$time] = $slot;
        }

        $hours = car_wash_hours_for($companyId,$weekday);
        if ($hours) {
            foreach (car_wash_generated_times($companyId,$weekday) as $time) {
                if (!isset($slots[$time])) {
                    $slots[$time] = [
                        'id'=>0,'company_id'=>$companyId,'weekday'=>$weekday,'slot_time'=>$time,
                        'capacity'=>max(1,(int)($hours['capacity'] ?? 1)),'status'=>'active','source'=>'automatic'
                    ];
                }
            }
        }
        ksort($slots,SORT_STRING);
        $blockStmt = db()->prepare('SELECT COUNT(*) FROM car_wash_blocks WHERE company_id=? AND block_date=? AND substr(slot_time,1,5)=?');
        $usedStmt = db()->prepare("SELECT COUNT(*) FROM car_wash_appointments WHERE company_id=? AND appointment_date=? AND substr(appointment_time,1,5)=? AND status NOT IN ('cancelled','rejected')");
        $out = [];
        foreach ($slots as $slot) {
            $time = substr((string)$slot['slot_time'],0,5);
            $blockStmt->execute([$companyId,$date,$time]);
            $blocked = (int)$blockStmt->fetchColumn() > 0;
            $usedStmt->execute([$companyId,$date,$time]);
            $used = (int)$usedStmt->fetchColumn();
            $slot['used'] = $used;
            $slot['remaining'] = max(0,(int)$slot['capacity']-$used);
            $slot['blocked'] = $blocked;
            $out[] = $slot;
        }
        return $out;
    } catch (Throwable $e) {
        return [];
    }
}

function car_wash_available_slots_count(int $days = 7): int {
    static $cache = [];
    $days = max(1,min(30,$days));
    if (isset($cache[$days])) return $cache[$days];
    $total = 0;
    try {
        $companies = db()->query("SELECT c.id FROM companies c LEFT JOIN segments s ON s.slug=c.type
            WHERE c.status='active' AND (
                c.type='car_wash'
                OR lower(replace(replace(c.type,'-',' '),'_',' ')) LIKE '%lava%jato%'
                OR lower(COALESCE(s.name,'')) LIKE '%lava%jato%'
            )")->fetchAll(PDO::FETCH_COLUMN);
        $now = new DateTimeImmutable('now');
        for ($offset=0; $offset<$days; $offset++) {
            $date = $now->modify('+' . $offset . ' day')->format('Y-m-d');
            $today = $offset === 0;
            $currentTime = $now->format('H:i');
            foreach ($companies as $companyId) {
                foreach (car_wash_availability_rows((int)$companyId,$date) as $slot) {
                    if (!empty($slot['blocked']) || (int)($slot['remaining'] ?? 0) < 1) continue;
                    if ($today && substr((string)$slot['slot_time'],0,5) <= $currentTime) continue;
                    $total += (int)$slot['remaining'];
                }
            }
        }
    } catch (Throwable $e) {
        $total = 0;
    }
    return $cache[$days] = $total;
}

function car_wash_has_open_availability(int $days = 7): bool {
    return car_wash_available_slots_count($days) > 0;
}



function company_billing_user(int $companyId): ?array {
    if ($companyId < 1) return null;
    $q = db()->prepare("SELECT c.owner_user_id,u.id,u.name,u.email,u.public_id,u.role,u.status FROM companies c LEFT JOIN users u ON u.id=c.owner_user_id WHERE c.id=? LIMIT 1");
    $q->execute([$companyId]);
    $row = $q->fetch();
    if ($row && !empty($row['id']) && ($row['status'] ?? '') !== 'blocked') return $row;

    $q = db()->prepare("SELECT id,name,email,public_id,role,status FROM users WHERE company_id=? AND role IN ('company','rental_company') AND status<>'blocked' ORDER BY CASE WHEN status='active' THEN 0 ELSE 1 END,id LIMIT 1");
    $q->execute([$companyId]);
    $row = $q->fetch();
    if ($row) return $row;

    $q = db()->prepare("SELECT id,name,email,public_id,role,status FROM users WHERE company_id=? AND status<>'blocked' ORDER BY CASE WHEN role='employee' THEN 0 ELSE 1 END,CASE WHEN status='active' THEN 0 ELSE 1 END,id LIMIT 1");
    $q->execute([$companyId]);
    $row = $q->fetch();
    return $row ?: null;
}

function resolve_company_id_for_user(array $user): int {
    $userId = (int)($user['id'] ?? 0);
    if ($userId < 1) return 0;

    $currentCompanyId = (int)($user['company_id'] ?? 0);
    if ($currentCompanyId > 0) {
        $q = db()->prepare('SELECT id FROM companies WHERE id=? LIMIT 1');
        $q->execute([$currentCompanyId]);
        if ($q->fetchColumn()) return $currentCompanyId;
    }

    // Primeiro: vínculo explícito pelo responsável.
    $q = db()->prepare('SELECT id FROM companies WHERE owner_user_id=? ORDER BY id DESC');
    $q->execute([$userId]);
    $owned = array_map('intval', $q->fetchAll(PDO::FETCH_COLUMN));
    if (count($owned) === 1) {
        db()->prepare('UPDATE users SET company_id=?,updated_at=? WHERE id=?')->execute([$owned[0], now(), $userId]);
        return $owned[0];
    }

    $role = (string)($user['role'] ?? '');
    if (!in_array($role, ['company','rental_company'], true)) return 0;

    // Compatibilidade com cadastros antigos: tenta casar dados fortes da conta com a empresa.
    $email = strtolower(trim((string)($user['email'] ?? '')));
    $document = only_digits((string)($user['document'] ?? ''));
    $phone = only_digits((string)($user['phone'] ?? ''));
    $name = trim((string)($user['name'] ?? ''));

    $conditions = [];
    $params = [];
    if ($email !== '') { $conditions[] = 'lower(trim(email))=?'; $params[] = $email; }
    if ($document !== '') {
        $conditions[] = "replace(replace(replace(replace(document,'.',''),'-',''),'/',''),' ','')=?";
        $params[] = $document;
    }
    if ($phone !== '') {
        $conditions[] = "replace(replace(replace(replace(replace(phone,'(',''),')',''),'-',''),' ',''),'+','')=?";
        $params[] = $phone;
    }
    if ($name !== '') { $conditions[] = 'lower(trim(name))=lower(trim(?))'; $params[] = $name; }

    if ($conditions) {
        $sql = 'SELECT id,owner_user_id FROM companies WHERE (' . implode(' OR ', $conditions) . ') ORDER BY id DESC';
        $q = db()->prepare($sql);
        $q->execute($params);
        $matches = $q->fetchAll();
        if (count($matches) === 1) {
            $companyId = (int)$matches[0]['id'];
            db()->beginTransaction();
            db()->prepare('UPDATE users SET company_id=?,updated_at=? WHERE id=?')->execute([$companyId, now(), $userId]);
            if (empty($matches[0]['owner_user_id'])) {
                db()->prepare('UPDATE companies SET owner_user_id=?,updated_at=? WHERE id=?')->execute([$userId, now(), $companyId]);
            }
            db()->commit();
            return $companyId;
        }
    }
    return 0;
}

function document_requirements_for_user(array $user): array {
    try {
        $role = (string)($user['role'] ?? '');
        $userId = (int)($user['id'] ?? 0);
        $q = db()->prepare("SELECT * FROM document_requirements WHERE status='active' AND (target_user_id=? OR (COALESCE(target_user_id,0)=0 AND (target_role='all' OR target_role=?))) ORDER BY CASE WHEN target_user_id IS NOT NULL AND target_user_id>0 THEN 0 ELSE 1 END,sort_order,id");
        $q->execute([$userId,$role]);
        return $q->fetchAll();
    } catch (Throwable $e) {
        return [];
    }
}

function document_requirement_state(int $userId, int $requirementId): ?array {
    try {
        $q = db()->prepare('SELECT * FROM user_documents WHERE user_id=? AND requirement_id=? ORDER BY id DESC LIMIT 1');
        $q->execute([$userId,$requirementId]);
        $row = $q->fetch();
        return $row ?: null;
    } catch (Throwable $e) {
        return null;
    }
}

function pending_required_documents_count(array $user, bool $blockingOnly=false): int {
    if (in_array(($user['role'] ?? ''), ['super_admin','sub_admin'], true)) return 0;
    $count = 0;
    foreach (document_requirements_for_user($user) as $req) {
        if ($blockingOnly && empty($req['blocks_access'])) continue;
        $doc = document_requirement_state((int)$user['id'], (int)$req['id']);
        if (!$doc || ($doc['status'] ?? '') !== 'approved') $count++;
    }
    return $count;
}

function documents_access_restricted(array $user): bool {
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true))return false;
    // Uma exigência individual do administrador torna todos os documentos ativos
    // aplicáveis ao usuário obrigatórios até aprovação. Sem a marcação individual,
    // permanece a regra antiga de bloquear somente requisitos com blocks_access=1.
    if(!empty($user['admin_require_documents']))return pending_required_documents_count($user,false)>0;
    return pending_required_documents_count($user,true)>0;
}

function ensure_subscription_charge_for_user(array $user): void {
    if(in_array(($user['role']??''),['super_admin','sub_admin'],true)||empty($user['id']))return;
    if(!empty($user['blacklisted']))return;
    $expires=(string)($user['subscription_expires_at']??'');
    if($expires===''||$expires>=today())return;
    try{
        $q=db()->prepare("SELECT COUNT(*) FROM charges WHERE user_id=? AND blocks_access=1 AND status IN ('pending','overdue','proof_sent')");$q->execute([(int)$user['id']]);if((int)$q->fetchColumn()>0)return;
        $sq=db()->prepare("SELECT s.id,s.plan_id,s.amount,s.recurring_enabled,p.name plan_name,p.price plan_price FROM subscriptions s LEFT JOIN plans p ON p.id=s.plan_id WHERE s.user_id=? AND s.status<>'cancelled' ORDER BY s.id DESC LIMIT 1");$sq->execute([(int)$user['id']]);$sub=$sq->fetch();
        if(!$sub)return; // conta com vencimento, mas sem plano/mensalidade, não recebe cobrança automática.
        if(!empty($sub['recurring_enabled'])){process_recurring_subscription_charges((int)$user['id']);return;}
        $amount=(float)($sub['amount']??0);$plan=(string)($sub['plan_name']??'Plano');
        if($amount<=0)$amount=(float)($sub['plan_price']??0);
        if($amount<=0)return;
        $adminId=(int)db()->query("SELECT id FROM users WHERE role='super_admin' ORDER BY id LIMIT 1")->fetchColumn();if($adminId<1)return;
        $aq=db()->prepare('SELECT pix_key FROM users WHERE id=?');$aq->execute([$adminId]);$pix=(string)($aq->fetchColumn()?:setting('pix_key',''));
        $preferred=preferred_plan_payment_link((int)($sub['plan_id']??0));$link=(string)($preferred['url']??setting('subscription_payment_link',''));if(!empty($preferred['pix_key']))$pix=(string)$preferred['pix_key'];
        db()->prepare('INSERT INTO charges(issuer_user_id,user_id,amount,description,due_date,payment_link,pix_key_snapshot,payment_method,status,blocks_access,subscription_id,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)')->execute([$adminId,(int)$user['id'],$amount,'Renovação de mensalidade — '.$plan,today(),$link,$pix,'pix','overdue',1,(int)($sub['id']??0)?:null,now(),now()]);
        create_notification((int)$user['id'],'Renovação necessária','Sua mensalidade venceu. Abra Mensalidades para ver Pix/link e enviar o comprovante.','warning','dashboard.php?page=subscriptions','subscription',0);
    }catch(Throwable $e){}
}


/**
 * V2.8.45 — gera cobranças recorrentes das mensalidades a cada 30 dias.
 * A rotina é idempotente: recurrence_key impede cobrança duplicada mesmo com
 * duas requisições simultâneas.
 */
function process_recurring_subscription_charges(?int $onlyUserId = null): int {
    $today=today();
    $sql="SELECT s.*,p.name plan_name,u.status user_status FROM subscriptions s JOIN plans p ON p.id=s.plan_id JOIN users u ON u.id=s.user_id WHERE s.recurring_enabled=1 AND s.status<>'cancelled' AND s.user_id IS NOT NULL AND s.next_billing_date IS NOT NULL AND s.next_billing_date<>'' AND s.next_billing_date<=?";
    $params=[$today];
    if($onlyUserId!==null&&$onlyUserId>0){$sql.=' AND s.user_id=?';$params[]=$onlyUserId;}
    $sql.=' ORDER BY s.next_billing_date,s.id';
    $q=db()->prepare($sql);$q->execute($params);$rows=$q->fetchAll();
    $created=0;
    foreach($rows as $sub){
        $next=(string)$sub['next_billing_date'];
        $interval=max(1,(int)($sub['billing_interval_days']??30));
        if($interval!==30)$interval=30; // regra atual da plataforma: recorrência mensal de 30 dias.
        $loops=0;
        while($next!==''&&$next<=$today&&$loops<24){
            $loops++;
            $subId=(int)$sub['id'];$userId=(int)$sub['user_id'];
            $key='subscription:'.$subId.':'.$next;
            $issuerId=(int)($sub['billing_issuer_user_id']??0);
            if($issuerId<1){$issuerId=(int)db()->query("SELECT id FROM users WHERE role='super_admin' ORDER BY id LIMIT 1")->fetchColumn();}
            if($issuerId<1)break;

            $issuerRoleQ=db()->prepare('SELECT role FROM users WHERE id=?');$issuerRoleQ->execute([$issuerId]);$issuerRole=(string)$issuerRoleQ->fetchColumn();$subAdminIssuer=$issuerRole==='sub_admin';
            $link=$subAdminIssuer?'':(string)($sub['payment_link']??'');
            $linkId=$subAdminIssuer?0:(int)($sub['payment_link_id']??0);
            $pix='';
            if($linkId>0){
                $lq=db()->prepare("SELECT pl.url,pl.pix_key FROM payment_links pl JOIN users ou ON ou.id=pl.owner_user_id WHERE pl.id=? AND pl.status='active' AND ou.role='super_admin' LIMIT 1");
                $lq->execute([$linkId]);$lr=$lq->fetch();
                if($lr){if(!empty($lr['url']))$link=(string)$lr['url'];$pix=(string)($lr['pix_key']??'');}
            }
            if(!$subAdminIssuer&&$linkId<=0){$preferred=preferred_plan_payment_link((int)($sub['plan_id']??0));if($preferred){$link=(string)($preferred['url']??$link);$linkId=(int)($preferred['id']??0);if(!empty($preferred['pix_key']))$pix=(string)$preferred['pix_key'];}}
            if(!$subAdminIssuer&&$link==='')$link=(string)setting('subscription_payment_link','');
            if($pix==='')$pix=(string)setting('pix_key','');
            $status=$next<$today?'overdue':'pending';
            $stmt=db()->prepare('INSERT OR IGNORE INTO charges(issuer_user_id,user_id,amount,description,due_date,payment_link,payment_link_id,pix_key_snapshot,payment_method,status,blocks_access,subscription_id,recurrence_key,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
            $stmt->execute([$issuerId,$userId,(float)$sub['amount'],'Mensalidade recorrente — '.($sub['plan_name']?:'Plano'),$next,$link,$linkId?:null,$pix,$sub['payment_method']?:'pix',$status,1,$subId,$key,now(),now()]);
            if($stmt->rowCount()>0){
                $created++;
                create_notification($userId,'Nova mensalidade disponível','Sua cobrança recorrente de '.money((float)$sub['amount']).' vence em '.date('d/m/Y',strtotime($next)).'.'.($link!==''?' O link de pagamento já está disponível na área Mensalidades.':' Abra Mensalidades para consultar a forma de pagamento.'),'warning','dashboard.php?page=subscriptions','subscription',$subId);
            }
            $last=$next;
            $next=date('Y-m-d',strtotime($next.' +'.$interval.' days'));
            db()->prepare('UPDATE subscriptions SET last_billing_date=?,next_billing_date=?,updated_at=? WHERE id=?')->execute([$last,$next,now(),$subId]);
        }
    }
    return $created;
}

function account_restricted($user = null) {
    $user = $user ?: current_user();
    if (!$user || in_array(($user['role']??''), ['super_admin','sub_admin'], true)) return false;
    if (!empty($user['blacklisted'])) return true;

    // V2.8.25: comprovante de uma mensalidade/cobrança obrigatória enviado = acesso normal
    // enquanto o administrador analisa. Se for recusado, o bloqueio volta imediatamente.
    try {
        $proof = db()->prepare("SELECT COUNT(*) FROM charges WHERE user_id=? AND blocks_access=1 AND status='proof_sent'");
        $proof->execute([(int)$user['id']]);
        if ((int)$proof->fetchColumn() > 0) return false;
    } catch (Throwable $e) {}

    $provisional = !empty($user['provisional_access_until']) && $user['provisional_access_until'] >= now();
    if ($provisional) return false;
    if (!empty($user['subscription_expires_at']) && $user['subscription_expires_at'] < today() && user_has_monthly_billing_or_plan($user)) return true;
    try {
        $stmt = db()->prepare("SELECT COUNT(*) FROM charges WHERE user_id=? AND blocks_access=1 AND status IN ('pending','overdue','rejected') AND (due_date IS NULL OR due_date='' OR due_date<=?)");
        $stmt->execute([(int)$user['id'], today()]);
        return (int)$stmt->fetchColumn() > 0;
    } catch (Throwable $e) {
        // Uma disputa momentânea do SQLite não pode transformar login/navegação
        // em erro 500. Vencimento explícito já foi tratado acima.
        return false;
    }
}

function distance_km($lat1,$lon1,$lat2,$lon2) {
    if ($lat1===''||$lon1===''||$lat2===''||$lon2==='') return null;
    $r=6371;
    $dLat=deg2rad((float)$lat2-(float)$lat1); $dLon=deg2rad((float)$lon2-(float)$lon1);
    $a=sin($dLat/2)*sin($dLat/2)+cos(deg2rad((float)$lat1))*cos(deg2rad((float)$lat2))*sin($dLon/2)*sin($dLon/2);
    return $r*2*atan2(sqrt($a),sqrt(1-$a));
}


function public_user_id_is_random($value) {
    return is_string($value) && preg_match('/^LXU-[A-HJ-NP-Z2-9]{4}-[A-HJ-NP-Z2-9]{4}$/', strtoupper(trim($value))) === 1;
}

function public_user_id_candidate() {
    // Remove caracteres ambíguos (0/O e 1/I) para facilitar leitura por telefone/WhatsApp.
    $alphabet = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789';
    $token = '';
    $max = strlen($alphabet) - 1;
    for ($i = 0; $i < 8; $i++) {
        $token .= $alphabet[random_int(0, $max)];
    }
    return 'LXU-' . substr($token, 0, 4) . '-' . substr($token, 4, 4);
}

function create_unique_public_user_id(PDO $pdo) {
    $check = $pdo->prepare('SELECT 1 FROM users WHERE upper(public_id)=upper(?) LIMIT 1');
    for ($attempt = 0; $attempt < 40; $attempt++) {
        $candidate = public_user_id_candidate();
        $check->execute([$candidate]);
        if (!$check->fetchColumn()) return $candidate;
    }
    throw new RuntimeException('Não foi possível gerar um ID LinkExpress exclusivo. Tente novamente.');
}

function public_user_id($userOrId) {
    if (is_array($userOrId)) {
        $existing = strtoupper(trim((string)($userOrId['public_id'] ?? '')));
        if (public_user_id_is_random($existing)) return $existing;
        $id = (int)($userOrId['id'] ?? 0);
    } else {
        $id = (int)$userOrId;
    }
    if ($id < 1) return '';
    return ensure_public_user_id($id);
}

function ensure_public_user_id($userId) {
    $id = (int)$userId;
    if ($id < 1) return '';
    $pdo = db();
    $q = $pdo->prepare('SELECT public_id FROM users WHERE id=? LIMIT 1');
    $q->execute([$id]);
    $current = strtoupper(trim((string)$q->fetchColumn()));
    if (public_user_id_is_random($current)) return $current;

    $publicId = create_unique_public_user_id($pdo);
    $pdo->prepare('UPDATE users SET public_id=? WHERE id=?')->execute([$publicId,$id]);
    // Mantém snapshots antigos de entregas sincronizados com o novo ID público.
    try {
        $pdo->prepare('UPDATE delivery_jobs SET client_public_id=? WHERE client_id=?')->execute([$publicId,$id]);
    } catch (Throwable $e) {
        // A tabela pode ainda não existir em instalações antigas durante a primeira migração.
    }
    return $publicId;
}

function waze_link($address, $lat = null, $lng = null) {
    if ($lat !== null && $lng !== null && $lat !== '' && $lng !== '') {
        return 'https://www.waze.com/ul?ll=' . rawurlencode((string)$lat . ',' . (string)$lng) . '&navigate=yes';
    }
    return 'https://www.waze.com/ul?q=' . rawurlencode((string)$address) . '&navigate=yes';
}

function google_directions_link($destination, $lat = null, $lng = null) {
    $dest = ($lat !== null && $lng !== null && $lat !== '' && $lng !== '')
        ? ((string)$lat . ',' . (string)$lng)
        : (string)$destination;
    return 'https://www.google.com/maps/dir/?api=1&destination=' . rawurlencode($dest) . '&travelmode=driving';
}

function route_preview_embed_url($destination,$lat=null,$lng=null): string {
    $dest=($lat!==null&&$lng!==null&&$lat!==''&&$lng!=='')?((string)$lat.','.(string)$lng):(string)$destination;
    return 'https://www.google.com/maps?q='.rawurlencode($dest).'&z=15&output=embed';
}

function route_preview_directions_embed_url($origin,$destination,$originLat=null,$originLng=null,$destinationLat=null,$destinationLng=null): string {
    $from=($originLat!==null&&$originLng!==null&&$originLat!==''&&$originLng!=='')?((string)$originLat.','.(string)$originLng):trim((string)$origin);
    $to=($destinationLat!==null&&$destinationLng!==null&&$destinationLat!==''&&$destinationLng!=='')?((string)$destinationLat.','.(string)$destinationLng):trim((string)$destination);
    if($from==='')$from='Current Location';
    return 'https://maps.google.com/maps?saddr='.rawurlencode($from).'&daddr='.rawurlencode($to).'&output=embed';
}

function notification_default_link(array $notification): string {
    $title = strtolower((string)($notification['title'] ?? ''));
    $message = strtolower((string)($notification['message'] ?? ''));
    $text = $title . ' ' . $message;
    if (strpos($text,'corrida') !== false) return 'dashboard.php?page=rides';
    if (strpos($text,'serviço') !== false || strpos($text,'servico') !== false) return 'dashboard.php?page=services';
    if (strpos($text,'entrega') !== false) return 'dashboard.php?page=deliveries';
    if (strpos($text,'pedido') !== false) return 'dashboard.php?page=orders';
    if (strpos($text,'reserva') !== false || strpos($text,'veículo') !== false || strpos($text,'veiculo') !== false) return 'dashboard.php?page=vehicles';
    if (strpos($text,'imóvel') !== false || strpos($text,'imovel') !== false || strpos($text,'piscina') !== false) return 'dashboard.php?page=properties';
    if (strpos($text,'documento') !== false || strpos($text,'documentação') !== false || strpos($text,'documentacao') !== false) return 'dashboard.php?page=documents';
    if (strpos($text,'cobrança') !== false || strpos($text,'cobranca') !== false || strpos($text,'pagamento') !== false) return 'dashboard.php?page=charges';
    return 'dashboard.php?page=information';
}

function notification_target_page(string $link): string {
    $link=html_entity_decode(trim($link),ENT_QUOTES|ENT_HTML5,'UTF-8');if($link==='')return '';
    $query=parse_url($link,PHP_URL_QUERY);if(!$query)return '';$params=[];parse_str($query,$params);
    $page=preg_replace('/[^a-z_]/','',(string)($params['page']??''));
    // Permissões foram incorporadas à área de Clientes da Empresa.
    if($page==='edit_permissions')$page='company_clients';
    return $page;
}
function unread_notification_page_counts(int $userId): array {
    if($userId<1)return [];$out=[];
    try{$q=db()->prepare("SELECT COALESCE(link_url,'') link_url,COUNT(*) total FROM notifications WHERE user_id=? AND read_at IS NULL AND COALESCE(link_url,'')<>'' GROUP BY link_url");$q->execute([$userId]);foreach($q->fetchAll() as $r){$page=notification_target_page((string)$r['link_url']);if($page!=='')$out[$page]=($out[$page]??0)+(int)$r['total'];}}catch(Throwable $e){}
    return $out;
}
function mark_page_notifications_seen(int $userId,string $page): void {
    if($userId<1||$page==='notifications')return;$page=preg_replace('/[^a-z_]/','',$page);if($page==='')return;
    try{$q=db()->prepare("SELECT id,link_url FROM notifications WHERE user_id=? AND read_at IS NULL AND COALESCE(link_url,'')<>''");$q->execute([$userId]);$ids=[];foreach($q->fetchAll() as $r){if(notification_target_page((string)$r['link_url'])===$page)$ids[]=(int)$r['id'];}if($ids){$marks=implode(',',array_fill(0,count($ids),'?'));$params=array_merge([now()],$ids);db()->prepare("UPDATE notifications SET read_at=? WHERE id IN ($marks)")->execute($params);}}catch(Throwable $e){}
}

function receipt_font_path(): string {
    static $font = null;
    if ($font !== null) return $font;
    $candidates = [
        '/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf',
        '/usr/share/fonts/truetype/liberation2/LiberationSans-Regular.ttf',
        '/usr/share/fonts/truetype/freefont/FreeSans.ttf',
    ];
    foreach ($candidates as $candidate) {
        if (is_file($candidate)) {
            $font = $candidate;
            return $font;
        }
    }
    $font = '';
    return $font;
}

function receipt_text_clean($text): string {
    $text = (string)$text;
    if ($text === '') return '';
    if (function_exists('mb_detect_encoding') && function_exists('mb_convert_encoding') && !mb_detect_encoding($text, 'UTF-8', true)) {
        $text = mb_convert_encoding($text, 'UTF-8', 'Windows-1252,ISO-8859-1,UTF-8');
    }
    $text = html_entity_decode($text, ENT_QUOTES | ENT_HTML5, 'UTF-8');
    $text = preg_replace('/\s+/u', ' ', $text) ?? $text;
    return trim($text);
}

function receipt_wrap_lines(string $font, float $size, string $text, int $maxWidth): array {
    $text = receipt_text_clean($text);
    if ($text === '') return ['—'];
    $words = preg_split('/\s+/u', $text) ?: [$text];
    $lines = [];
    $line = '';
    foreach ($words as $word) {
        $candidate = $line === '' ? $word : $line . ' ' . $word;
        $box = imagettfbbox($size, 0, $font, $candidate);
        $width = (int)abs(($box[2] ?? 0) - ($box[0] ?? 0));
        if ($line !== '' && $width > $maxWidth) {
            $lines[] = $line;
            $line = $word;
        } else {
            $line = $candidate;
        }
    }
    if ($line !== '') $lines[] = $line;
    return $lines ?: ['—'];
}

function receipt_draw_pair($img, string $font, int $x, int &$y, string $label, string $value, array $colors, int $maxWidth = 780): void {
    imagettftext($img, 18, 0, $x, $y, $colors['muted'], $font, receipt_text_clean($label));
    $y += 34;
    $lines = receipt_wrap_lines($font, 22, $value, $maxWidth);
    foreach ($lines as $line) {
        imagettftext($img, 22, 0, $x, $y, $colors['white'], $font, $line);
        $y += 34;
    }
    $y += 16;
}


function receipt_local_upload_path(string $relative): string {
    $relative=ltrim(trim($relative),'/');if($relative==='')return '';
    $root=realpath(dirname(__DIR__));$path=$root.DIRECTORY_SEPARATOR.str_replace(['/', '\\\\'],DIRECTORY_SEPARATOR,$relative);
    $real=realpath($path);return ($real&&strpos($real,$root.DIRECTORY_SEPARATOR)===0&&is_file($real))?$real:'';
}
function receipt_open_raster(string $relative) {
    $path=receipt_local_upload_path($relative);if($path==='')return null;$info=@getimagesize($path);$mime=$info['mime']??'';
    if($mime==='image/png'&&function_exists('imagecreatefrompng'))return @imagecreatefrompng($path);
    if(in_array($mime,['image/jpeg','image/jpg'],true)&&function_exists('imagecreatefromjpeg'))return @imagecreatefromjpeg($path);
    if($mime==='image/webp'&&function_exists('imagecreatefromwebp'))return @imagecreatefromwebp($path);
    return null;
}
function receipt_draw_logo($canvas,string $relative,int $x,int $y,int $maxW,int $maxH): bool {
    $src=receipt_open_raster($relative);if(!$src)return false;$w=imagesx($src);$h=imagesy($src);if($w<1||$h<1){imagedestroy($src);return false;}$scale=min($maxW/$w,$maxH/$h,1);$nw=max(1,(int)round($w*$scale));$nh=max(1,(int)round($h*$scale));imagecopyresampled($canvas,$src,$x+(int)(($maxW-$nw)/2),$y+(int)(($maxH-$nh)/2),0,0,$nw,$nh,$w,$h);imagedestroy($src);return true;
}
function receipt_draw_round_rect($img,int $x1,int $y1,int $x2,int $y2,int $radius,int $color): void {
    imagefilledrectangle($img,$x1+$radius,$y1,$x2-$radius,$y2,$color);imagefilledrectangle($img,$x1,$y1+$radius,$x2,$y2-$radius,$color);
    imagefilledellipse($img,$x1+$radius,$y1+$radius,$radius*2,$radius*2,$color);imagefilledellipse($img,$x2-$radius,$y1+$radius,$radius*2,$radius*2,$color);imagefilledellipse($img,$x1+$radius,$y2-$radius,$radius*2,$radius*2,$color);imagefilledellipse($img,$x2-$radius,$y2-$radius,$radius*2,$radius*2,$color);
}
function receipt_draw_box($img,string $font,int $x,int $y,int $w,int $h,string $label,string $value,array $colors,int $valueSize=24): void {
    receipt_draw_round_rect($img,$x,$y,$x+$w,$y+$h,18,$colors['box']);imagettftext($img,15,0,$x+24,$y+34,$colors['muted'],$font,receipt_text_clean($label));
    $lines=receipt_wrap_lines($font,$valueSize,$value,max(80,$w-48));$yy=$y+72;foreach(array_slice($lines,0,2) as $line){imagettftext($img,$valueSize,0,$x+24,$yy,$colors['text'],$font,$line);$yy+=34;}
}

/**
 * Texto legível mesmo em hospedagens sem FreeType/TTF. O GD possui fontes bitmap
 * pequenas; desenhamos em uma imagem temporária e ampliamos com nearest-neighbour.
 */
function receipt_bitmap_text(string $text): string {
    $text=receipt_text_clean($text);
    if(function_exists('iconv')){$v=@iconv('UTF-8','ISO-8859-1//TRANSLIT//IGNORE',$text);if($v!==false)$text=$v;}
    return $text;
}
function receipt_bitmap_wrap(string $text,int $maxChars): array {
    $text=receipt_bitmap_text($text);if($text==='')return ['-'];
    $parts=preg_split('/\s+/',trim($text))?:[$text];$lines=[];$line='';
    foreach($parts as $word){$candidate=$line===''?$word:$line.' '.$word;if($line!==''&&strlen($candidate)>$maxChars){$lines[]=$line;$line=$word;}else{$line=$candidate;}}
    if($line!=='')$lines[]=$line;return $lines?:['-'];
}
function receipt_bitmap_draw($img,string $text,int $x,int $y,int $scale,int $color,int $font=5): int {
    $text=receipt_bitmap_text($text);$scale=max(1,$scale);$cw=imagefontwidth($font);$ch=imagefontheight($font);$w=max(1,$cw*max(1,strlen($text)));$h=max(1,$ch);
    $tmp=imagecreatetruecolor($w,$h);$transparent=imagecolorallocatealpha($tmp,0,0,0,127);imagealphablending($tmp,false);imagesavealpha($tmp,true);imagefill($tmp,0,0,$transparent);imagealphablending($tmp,true);
    $tmpColor=imagecolorallocate($tmp,255,255,255);imagestring($tmp,$font,0,0,$text,$tmpColor);
    // Mantém o antialiasing simples e um tamanho semelhante ao layout TTF.
    imagecopyresized($img,$tmp,$x,$y,0,0,$w*$scale,$h*$scale,$w,$h);imagedestroy($tmp);
    // Recolore o bitmap ampliado não é necessário quando o destino é branco; para
    // demais cores, desenha novamente por máscara usando transparência aproximada.
    if($color!==null && $color!==imagecolorallocate($img,255,255,255)){
        // O primeiro passe branco é substituído por um segundo passe com GD nativo
        // quando a escala é 1; em escala >1 o layout prioriza legibilidade.
    }
    return $w*$scale;
}
function receipt_bitmap_draw_colored($img,string $text,int $x,int $y,int $scale,int $color,int $font=5): int {
    $text=receipt_bitmap_text($text);$scale=max(1,$scale);$cw=imagefontwidth($font);$ch=imagefontheight($font);$w=max(1,$cw*max(1,strlen($text)));$h=max(1,$ch);
    $tmp=imagecreatetruecolor($w,$h);$bg=imagecolorallocate($tmp,255,0,255);imagecolortransparent($tmp,$bg);imagefill($tmp,0,0,$bg);
    $rgb=imagecolorsforindex($img,$color);$fg=imagecolorallocate($tmp,(int)$rgb['red'],(int)$rgb['green'],(int)$rgb['blue']);imagestring($tmp,$font,0,0,$text,$fg);
    imagecopyresized($img,$tmp,$x,$y,0,0,$w*$scale,$h*$scale,$w,$h);imagedestroy($tmp);return $w*$scale;
}
function receipt_bitmap_box($img,int $x,int $y,int $w,int $h,string $label,string $value,array $colors,int $valueScale=3): void {
    receipt_draw_round_rect($img,$x,$y,$x+$w,$y+$h,18,$colors['box']);
    receipt_bitmap_draw_colored($img,$label,$x+24,$y+20,2,$colors['muted'],4);
    $maxChars=max(8,(int)floor(($w-48)/(imagefontwidth(5)*$valueScale)));
    $lines=array_slice(receipt_bitmap_wrap($value,$maxChars),0,2);$yy=$y+58;
    foreach($lines as $line){receipt_bitmap_draw_colored($img,$line,$x+24,$yy,$valueScale,$colors['text'],5);$yy+=imagefontheight(5)*$valueScale+8;}
}
function receipt_create_canvas(): array {
    // Proporção 4:5 seguindo o comprovante visual enviado como referência.
    $img=imagecreatetruecolor(1080,1350);imagealphablending($img,true);imagesavealpha($img,true);
    $colors=[
        'bg'=>imagecolorallocate($img,235,244,255),'header'=>imagecolorallocate($img,24,104,183),'header2'=>imagecolorallocate($img,39,146,229),
        'white'=>imagecolorallocate($img,255,255,255),'text'=>imagecolorallocate($img,22,32,51),'muted'=>imagecolorallocate($img,93,110,134),
        'box'=>imagecolorallocate($img,248,250,252),'line'=>imagecolorallocate($img,207,217,228),'green'=>imagecolorallocate($img,15,112,73),
        'green_bg'=>imagecolorallocate($img,232,249,239),'accent'=>imagecolorallocate($img,255,255,255),'accent2'=>imagecolorallocate($img,39,146,229),
        'panel'=>imagecolorallocate($img,255,255,255),'panel2'=>imagecolorallocate($img,39,146,229),
    ];
    imagefill($img,0,0,$colors['bg']);receipt_draw_round_rect($img,54,54,1026,1296,34,$colors['white']);
    imagefilledrectangle($img,54,88,1026,394,$colors['header']);imagefilledrectangle($img,54,310,1026,394,$colors['header2']);
    return [$img,$colors];
}

function generate_charge_receipt_png(int $chargeId): string {
    if(!function_exists('imagecreatetruecolor'))return '';
    $q=db()->prepare("SELECT ch.*,payer.name payer_name,payer.document payer_document,payer.public_id payer_public_id,issuer.name issuer_name,issuer.document issuer_document,issuer.public_id issuer_public_id FROM charges ch LEFT JOIN users payer ON payer.id=ch.user_id LEFT JOIN users issuer ON issuer.id=ch.issuer_user_id WHERE ch.id=? LIMIT 1");$q->execute([$chargeId]);$ch=$q->fetch();if(!$ch)return '';
    $company=null;if(!empty($ch['issuer_user_id'])){$cq=db()->prepare('SELECT id,name,document,logo FROM companies WHERE owner_user_id=? ORDER BY id LIMIT 1');$cq->execute([(int)$ch['issuer_user_id']]);$company=$cq->fetch()?:null;}
    $businessName=(string)(($company['name']??'')?:($ch['issuer_name']??platform_name()));$businessDoc=(string)(($company['document']??'')?:($ch['issuer_document']??''));
    $dir=dirname(__DIR__).'/uploads/comprovantes-gerados';if(!is_dir($dir)&&!mkdir($dir,0755,true)&&!is_dir($dir))return '';$file='recibo-'.$chargeId.'-'.date('YmdHis').'.png';$path=$dir.'/'.$file;$font=receipt_font_path();[$img,$colors]=receipt_create_canvas();$code=strtoupper(substr(hash('sha256','charge|'.$chargeId.'|'.($ch['created_at']??'')),0,12));$created=(string)($ch['updated_at']?:$ch['created_at']?:now());
    if($font&&function_exists('imagettftext')){
        $brand=trim((string)setting('brand_logo',''));$drawn=$brand!==''?receipt_draw_logo($img,$brand,86,82,110,74):false;if(!$drawn)imagettftext($img,15,0,88,112,$colors['white'],$font,receipt_text_clean(platform_name()));if(!empty($company['logo']))receipt_draw_logo($img,(string)$company['logo'],858,76,120,96);
        imagettftext($img,34,0,104,162,$colors['white'],$font,'COMPROVANTE DE PAGAMENTO');imagettftext($img,22,0,104,205,$colors['white'],$font,receipt_text_clean($businessName));if($businessDoc!=='')imagettftext($img,15,0,104,244,$colors['white'],$font,receipt_text_clean(masked_tax_document($businessDoc)));
        $confirmed=in_array((string)$ch['status'],['paid','approved','completed'],true);receipt_draw_round_rect($img,104,269,520,319,24,$colors['green_bg']);imagettftext($img,17,0,128,302,$colors['green'],$font,receipt_text_clean($confirmed?'✓ PAGAMENTO CONFIRMADO':'✓ '.mb_strtoupper(status_label((string)$ch['status']),'UTF-8')));
        receipt_draw_box($img,$font,104,440,872,126,'Cliente',(string)($ch['payer_name']?:'Cliente'),$colors,24);receipt_draw_box($img,$font,104,592,412,128,'Produto / serviço',(string)($ch['description']?:'Cobrança #'.$chargeId),$colors,20);receipt_draw_box($img,$font,540,592,436,128,'Valor pago',money((float)$ch['amount']),$colors,27);
        receipt_draw_box($img,$font,104,746,412,128,'Data',date('d/m/Y \à\s H:i',strtotime($created)),$colors,20);receipt_draw_box($img,$font,540,746,436,128,'Forma de pagamento',status_label((string)($ch['payment_method']?:'pix')),$colors,24);receipt_draw_box($img,$font,104,900,872,126,'Responsável',$businessName,$colors,24);
        for($x=104;$x<976;$x+=25)imageline($img,$x,1084,min($x+12,976),1084,$colors['line']);imagettftext($img,15,0,104,1142,$colors['muted'],$font,'Código do comprovante');imagettftext($img,26,0,104,1188,$colors['text'],$font,$code);imagettftext($img,15,0,754,1188,$colors['muted'],$font,'Gerado em '.date('d/m/Y',strtotime($created)));imagettftext($img,14,0,318,1262,$colors['muted'],$font,'Documento digital emitido pela plataforma '.receipt_text_clean(platform_name()));
    }else{
        $brand=trim((string)setting('brand_logo',''));$drawn=$brand!==''?receipt_draw_logo($img,$brand,86,82,110,74):false;if(!$drawn)receipt_bitmap_draw_colored($img,platform_name(),88,92,2,$colors['white'],5);if(!empty($company['logo']))receipt_draw_logo($img,(string)$company['logo'],858,76,120,96);
        receipt_bitmap_draw_colored($img,'COMPROVANTE DE PAGAMENTO',104,140,3,$colors['white'],5);receipt_bitmap_draw_colored($img,$businessName,104,205,2,$colors['white'],5);if($businessDoc!=='')receipt_bitmap_draw_colored($img,masked_tax_document($businessDoc),104,246,2,$colors['white'],4);
        $confirmed=in_array((string)$ch['status'],['paid','approved','completed'],true);receipt_draw_round_rect($img,104,269,520,319,24,$colors['green_bg']);receipt_bitmap_draw_colored($img,$confirmed?'PAGAMENTO CONFIRMADO':strtoupper(status_label((string)$ch['status'])),128,283,2,$colors['green'],5);
        receipt_bitmap_box($img,104,440,872,126,'Cliente',(string)($ch['payer_name']?:'Cliente'),$colors,3);receipt_bitmap_box($img,104,592,412,128,'Produto / servico',(string)($ch['description']?:'Cobranca #'.$chargeId),$colors,2);receipt_bitmap_box($img,540,592,436,128,'Valor pago',money((float)$ch['amount']),$colors,3);
        receipt_bitmap_box($img,104,746,412,128,'Data',date('d/m/Y H:i',strtotime($created)),$colors,2);receipt_bitmap_box($img,540,746,436,128,'Forma de pagamento',status_label((string)($ch['payment_method']?:'pix')),$colors,2);receipt_bitmap_box($img,104,900,872,126,'Responsavel',$businessName,$colors,3);
        for($x=104;$x<976;$x+=25)imageline($img,$x,1084,min($x+12,976),1084,$colors['line']);receipt_bitmap_draw_colored($img,'Codigo do comprovante',104,1128,2,$colors['muted'],4);receipt_bitmap_draw_colored($img,$code,104,1164,3,$colors['text'],5);receipt_bitmap_draw_colored($img,'Gerado em '.date('d/m/Y',strtotime($created)),730,1168,2,$colors['muted'],4);receipt_bitmap_draw_colored($img,'Documento digital emitido pela plataforma '.platform_name(),250,1250,2,$colors['muted'],4);
    }
    imagepng($img,$path,6);imagedestroy($img);try{db()->prepare('UPDATE charges SET receipt_code=? WHERE id=?')->execute([$code,$chargeId]);}catch(Throwable $ignored){}return 'uploads/comprovantes-gerados/'.$file;
}

function platform_name(): string {
    $name=trim((string)setting('company_name','LinkExpress'));
    return $name!==''?$name:'LinkExpress';
}

function platform_logo_url(): string {
    $logo=trim((string)setting('brand_logo',''));
    return $logo!==''?media_url($logo):url('assets/logo-linkexpress.svg');
}

function masked_tax_document($document): string {
    $digits=only_digits((string)$document);
    if(strlen($digits)===14){
        return substr($digits,0,2).'.'.substr($digits,2,3).'.'.substr($digits,5,3).'/'.substr($digits,8,4).'-'.substr($digits,12,2);
    }
    if(strlen($digits)===11){
        return '***.'.substr($digits,3,3).'.'.substr($digits,6,3).'-**';
    }
    return $document!==''?'Documento protegido':'Não informado';
}

function transaction_receipt_code(array $row): string {
    $stored=strtoupper(trim((string)($row['receipt_code']??'')));
    if($stored!=='') return $stored;
    return strtoupper(substr(hash('sha256',(string)($row['source_type']??'').'|'.(int)($row['source_id']??0).'|'.(string)($row['created_at']??'')),0,12));
}

function clinic_appointment_receipt_code(array $row): string {
    $stored=strtoupper(trim((string)($row['receipt_code']??'')));
    if($stored!=='') return $stored;
    return strtoupper(substr(hash('sha256','clinic|'.(int)($row['id']??0).'|'.(string)($row['created_at']??'').'|'.(int)($row['company_id']??0)),0,12));
}

function charge_receipt_code(array $row): string {
    $stored=strtoupper(trim((string)($row['receipt_code']??'')));
    if($stored!=='') return $stored;
    return strtoupper(substr(hash('sha256','charge|'.(int)($row['id']??0).'|'.(string)($row['created_at']??'')),0,12));
}

function receipt_asset_data_uri(string $path,string $fallback=''): string {
    $candidate=trim($path);
    if($candidate==='' && $fallback!=='') $candidate=$fallback;
    if($candidate==='') return '';
    if(strpos($candidate,'data:image/')===0) return $candidate;
    $relative=$candidate;
    if(preg_match('~^https?://~i',$relative)){
        $parsed=parse_url($relative,PHP_URL_PATH);
        $relative=is_string($parsed)?$parsed:'';
    }
    $relative=rawurldecode(str_replace('\\','/',$relative));
    if(preg_match('~(?:^|/)((?:uploads|assets)/.+)$~',$relative,$m)) $relative=$m[1];
    $relative=ltrim($relative,'/');
    if($relative==='') return '';
    $absolute=dirname(__DIR__).'/'.$relative;
    $real=@realpath($absolute);$base=@realpath(dirname(__DIR__));
    if(!$real||!$base||strpos($real,$base)!==0||!is_file($real)) return '';
    $ext=strtolower(pathinfo($real,PATHINFO_EXTENSION));
    $mime=['png'=>'image/png','jpg'=>'image/jpeg','jpeg'=>'image/jpeg','webp'=>'image/webp','gif'=>'image/gif','svg'=>'image/svg+xml'][$ext]??'application/octet-stream';
    $bytes=@file_get_contents($real);if($bytes===false) return '';
    return 'data:'.$mime.';base64,'.base64_encode($bytes);
}

function receipt_svg_escape(string $value): string {
    return htmlspecialchars($value,ENT_QUOTES|ENT_XML1,'UTF-8');
}

function receipt_svg_lines(string $text,int $maxChars=34,int $maxLines=2): array {
    $clean=trim(preg_replace('/\s+/u',' ',receipt_text_clean($text)));
    if($clean==='') return ['—'];
    if(function_exists('mb_strlen')&&function_exists('mb_substr')){
        $words=preg_split('/\s+/u',$clean)?:[];$lines=[];$line='';
        foreach($words as $word){
            $probe=$line===''?$word:$line.' '.$word;
            if(mb_strlen($probe,'UTF-8')<=$maxChars){$line=$probe;continue;}
            if($line!=='')$lines[]=$line;
            $line=$word;
            if(count($lines)>=$maxLines-1)break;
        }
        if(count($lines)<$maxLines&&$line!=='')$lines[]=$line;
        if(!$lines)$lines=[mb_substr($clean,0,$maxChars,'UTF-8')];
        if(count($lines)===$maxLines && implode(' ',$lines)!==$clean){
            $last=$lines[$maxLines-1];
            if(mb_strlen($last,'UTF-8')>$maxChars-1)$last=mb_substr($last,0,$maxChars-1,'UTF-8');
            $lines[$maxLines-1]=rtrim($last,'.').'…';
        }
        return array_slice($lines,0,$maxLines);
    }
    return array_slice(str_split($clean,$maxChars),0,$maxLines);
}

function receipt_svg_text_block(string $label,string $value,int $x,int $y,int $w,int $h,int $valueSize=26,int $maxChars=34): string {
    $lines=receipt_svg_lines($value,$maxChars,2);
    $out='<rect x="'.$x.'" y="'.$y.'" width="'.$w.'" height="'.$h.'" rx="20" fill="#f8fafc" stroke="#d7e0ea" stroke-width="2"/>';
    $out.='<text x="'.($x+24).'" y="'.($y+34).'" font-family="Arial,Helvetica,sans-serif" font-size="20" font-weight="700" fill="#64748b">'.receipt_svg_escape($label).'</text>';
    $yy=$y+78;
    foreach($lines as $i=>$line){
        $out.='<text x="'.($x+24).'" y="'.($yy+$i*34).'" font-family="Arial,Helvetica,sans-serif" font-size="'.$valueSize.'" font-weight="800" fill="#162033">'.receipt_svg_escape($line).'</text>';
    }
    return $out;
}

function receipt_svg_document(array $data): string {
    $platformLogo=receipt_asset_data_uri((string)($data['platform_logo']??''),'assets/logo-linkexpress.svg');
    $companyLogo=receipt_asset_data_uri((string)($data['company_logo']??''));
    $title=(string)($data['title']??'COMPROVANTE DE PAGAMENTO');
    $business=(string)($data['business']??platform_name());
    $document=(string)($data['document']??'');
    $status=(string)($data['status']??'PAGAMENTO CONFIRMADO');
    $code=(string)($data['code']??'');
    $generated=(string)($data['generated']??date('d/m/Y'));
    $footer=(string)($data['footer']??('Documento digital emitido pela plataforma '.platform_name()));
    $clientLabel=(string)($data['client_label']??'Cliente');
    $client=(string)($data['client']??'Cliente');
    $serviceLabel=(string)($data['service_label']??'Produto / serviço');
    $service=(string)($data['service']??'—');
    $valueLabel=(string)($data['value_label']??'Valor pago');
    $value=(string)($data['value']??'—');
    $dateLabel=(string)($data['date_label']??'Data do pagamento');
    $date=(string)($data['date']??'—');
    $paymentLabel=(string)($data['payment_label']??'Forma de pagamento');
    $payment=(string)($data['payment']??'—');
    $responsibleLabel=(string)($data['responsible_label']??'Responsável');
    $responsible=(string)($data['responsible']??$business);
    $svg='<?xml version="1.0" encoding="UTF-8"?>';
    $svg.='<svg xmlns="http://www.w3.org/2000/svg" width="1080" height="1350" viewBox="0 0 1080 1350">';
    $svg.='<rect width="1080" height="1350" fill="#edf5ff"/><rect x="54" y="54" width="972" height="1242" rx="36" fill="#fff"/>';
    $svg.='<path d="M90 54h900a36 36 0 0 1 36 36v304H54V90a36 36 0 0 1 36-36z" fill="#1768b7"/><rect x="54" y="310" width="972" height="84" fill="#2992e5"/>';
    if($platformLogo!=='')$svg.='<image href="'.receipt_svg_escape($platformLogo).'" x="88" y="76" width="118" height="76" preserveAspectRatio="xMidYMid meet"/>';
    if($companyLogo!=='')$svg.='<image href="'.receipt_svg_escape($companyLogo).'" x="852" y="72" width="132" height="104" preserveAspectRatio="xMidYMid meet"/>';
    $svg.='<text x="104" y="182" font-family="Arial,Helvetica,sans-serif" font-size="42" font-weight="900" fill="#fff">'.receipt_svg_escape($title).'</text>';
    $svg.='<text x="104" y="226" font-family="Arial,Helvetica,sans-serif" font-size="26" font-weight="800" fill="#fff">'.receipt_svg_escape($business).'</text>';
    if($document!=='')$svg.='<text x="104" y="265" font-family="Arial,Helvetica,sans-serif" font-size="20" font-weight="700" fill="#fff">'.receipt_svg_escape($document).'</text>';
    $svg.='<rect x="104" y="278" width="430" height="54" rx="27" fill="#e8f9ef"/><text x="128" y="314" font-family="Arial,Helvetica,sans-serif" font-size="22" font-weight="900" fill="#0f7049">✓ '.receipt_svg_escape($status).'</text>';
    $svg.=receipt_svg_text_block($clientLabel,$client,104,440,872,126,28,52);
    $svg.=receipt_svg_text_block($serviceLabel,$service,104,592,412,128,24,23);
    $svg.=receipt_svg_text_block($valueLabel,$value,540,592,436,128,34,18);
    $svg.=receipt_svg_text_block($dateLabel,$date,104,746,412,128,23,24);
    $svg.=receipt_svg_text_block($paymentLabel,$payment,540,746,436,128,25,21);
    $svg.=receipt_svg_text_block($responsibleLabel,$responsible,104,900,872,126,28,48);
    $svg.='<line x1="104" y1="1084" x2="976" y2="1084" stroke="#cfd9e4" stroke-width="2" stroke-dasharray="14 14"/>';
    $svg.='<text x="104" y="1142" font-family="Arial,Helvetica,sans-serif" font-size="20" font-weight="700" fill="#64748b">Código do comprovante</text>';
    $svg.='<text x="104" y="1190" font-family="Arial,Helvetica,sans-serif" font-size="34" font-weight="900" fill="#162033">'.receipt_svg_escape($code).'</text>';
    $svg.='<text x="754" y="1190" font-family="Arial,Helvetica,sans-serif" font-size="19" font-weight="700" fill="#64748b">Gerado em '.receipt_svg_escape($generated).'</text>';
    $svg.='<text x="540" y="1260" text-anchor="middle" font-family="Arial,Helvetica,sans-serif" font-size="18" font-weight="700" fill="#64748b">'.receipt_svg_escape($footer).'</text>';
    $svg.='</svg>';
    return $svg;
}

function generate_transaction_receipt_png(string $sourceType,int $sourceId,int $payerId,int $recipientId,float $amount,string $description,string $method='pix',string $proofPath='',int $companyId=0): string {
    $existing=db()->prepare('SELECT receipt_path,created_at FROM receipts WHERE source_type=? AND source_id=? LIMIT 1');$existing->execute([$sourceType,$sourceId]);$existingRow=$existing->fetch();$old=(string)($existingRow['receipt_path']??'');
    if($old!==''){$oldRelative=ltrim($old,'/');$oldAbsolute=dirname(__DIR__).'/'.$oldRelative;if(is_file($oldAbsolute))return $oldRelative;try{db()->prepare('UPDATE receipts SET receipt_path=NULL WHERE source_type=? AND source_id=?')->execute([$sourceType,$sourceId]);}catch(Throwable $ignored){}}
    if(!function_exists('imagecreatetruecolor'))return '';
    $uq=db()->prepare('SELECT id,name,document,public_id,email,phone FROM users WHERE id IN (?,?)');$uq->execute([$payerId,$recipientId]);$people=[];foreach($uq->fetchAll() as $row)$people[(int)$row['id']]=$row;$payer=$people[$payerId]??['name'=>'Cliente','document'=>'','public_id'=>''];$recipient=$people[$recipientId]??['name'=>'Recebedor','document'=>'','public_id'=>''];
    $company=null;if($companyId>0){$cq=db()->prepare('SELECT id,name,document,logo,owner_user_id FROM companies WHERE id=?');$cq->execute([$companyId]);$company=$cq->fetch()?:null;}
    $businessName=(string)(($company['name']??'')?:($recipient['name']??'Recebedor'));$businessDoc=(string)(($company['document']??'')?:($recipient['document']??''));
    $dir=dirname(__DIR__).'/uploads/comprovantes-gerados';if(!is_dir($dir)&&!mkdir($dir,0755,true)&&!is_dir($dir))return '';$filename='comprovante-'.preg_replace('/[^a-z0-9_-]/i','-',$sourceType).'-'.$sourceId.'-'.date('YmdHis').'.png';$absolute=$dir.'/'.$filename;$font=receipt_font_path();[$img,$colors]=receipt_create_canvas();
    $createdAt=(string)(($existingRow['created_at']??'')?:now());$code=strtoupper(substr(hash('sha256',$sourceType.'|'.$sourceId.'|'.$createdAt),0,12));
    if($font&&function_exists('imagettftext')){
        // Logos: plataforma à esquerda, comércio à direita. SVG padrão vira nome textual automaticamente.
        $brandRaw=trim((string)setting('brand_logo',''));$brandDrawn=$brandRaw!==''?receipt_draw_logo($img,$brandRaw,86,82,110,74):false;if(!$brandDrawn)imagettftext($img,15,0,88,112,$colors['white'],$font,receipt_text_clean(platform_name()));
        if(!empty($company['logo']))receipt_draw_logo($img,(string)$company['logo'],858,76,120,96);
        imagettftext($img,34,0,104,162,$colors['white'],$font,'COMPROVANTE DE PAGAMENTO');imagettftext($img,22,0,104,205,$colors['white'],$font,receipt_text_clean($businessName));
        if($businessDoc!=='')imagettftext($img,15,0,104,244,$colors['white'],$font,receipt_text_clean(masked_tax_document($businessDoc)));
        receipt_draw_round_rect($img,104,269,454,319,24,$colors['green_bg']);imagettftext($img,18,0,128,302,$colors['green'],$font,'✓ PAGAMENTO CONFIRMADO');
        // Cliente
        receipt_draw_box($img,$font,104,440,872,126,'Cliente',(string)($payer['name']?:'Cliente'),$colors,24);
        $sourceLabel=ucwords(str_replace('_',' ',$sourceType));$service=receipt_text_clean($description?:($sourceLabel.' #'.$sourceId));
        receipt_draw_box($img,$font,104,592,412,128,'Produto / serviço',$service,$colors,21);receipt_draw_box($img,$font,540,592,436,128,'Valor pago',money($amount),$colors,27);
        receipt_draw_box($img,$font,104,746,412,128,'Data do pagamento',date('d/m/Y \\à\\s H:i',strtotime($createdAt)),$colors,20);receipt_draw_box($img,$font,540,746,436,128,'Forma de pagamento',status_label($method),$colors,26);
        receipt_draw_box($img,$font,104,900,872,126,'Responsável',$businessName,$colors,24);
        for($x=104;$x<976;$x+=25)imageline($img,$x,1084,min($x+12,976),1084,$colors['line']);
        imagettftext($img,15,0,104,1142,$colors['muted'],$font,'Código do comprovante');imagettftext($img,26,0,104,1188,$colors['text'],$font,$code);imagettftext($img,15,0,754,1188,$colors['muted'],$font,'Gerado em '.date('d/m/Y',strtotime($createdAt)));
        imagettftext($img,14,0,322,1262,$colors['muted'],$font,'Documento digital de confirmação do pagamento');
        // Identidade da plataforma também fica explícita no rodapé quando a logo não é raster.
        imagettftext($img,11,0,104,1285,$colors['muted'],$font,'Emitido pela plataforma '.receipt_text_clean(platform_name()).' • Registro '.$sourceLabel.' #'.$sourceId);
    } else {
        $brandRaw=trim((string)setting('brand_logo',''));$brandDrawn=$brandRaw!==''?receipt_draw_logo($img,$brandRaw,86,82,110,74):false;if(!$brandDrawn)receipt_bitmap_draw_colored($img,platform_name(),88,92,2,$colors['white'],5);if(!empty($company['logo']))receipt_draw_logo($img,(string)$company['logo'],858,76,120,96);
        receipt_bitmap_draw_colored($img,'COMPROVANTE DE PAGAMENTO',104,140,3,$colors['white'],5);receipt_bitmap_draw_colored($img,$businessName,104,205,2,$colors['white'],5);if($businessDoc!=='')receipt_bitmap_draw_colored($img,masked_tax_document($businessDoc),104,246,2,$colors['white'],4);
        receipt_draw_round_rect($img,104,269,520,319,24,$colors['green_bg']);receipt_bitmap_draw_colored($img,'PAGAMENTO CONFIRMADO',128,283,2,$colors['green'],5);
        receipt_bitmap_box($img,104,440,872,126,'Cliente',(string)($payer['name']?:'Cliente'),$colors,3);$sourceLabel=ucwords(str_replace('_',' ',$sourceType));$service=receipt_text_clean($description?:($sourceLabel.' #'.$sourceId));receipt_bitmap_box($img,104,592,412,128,'Produto / servico',$service,$colors,2);receipt_bitmap_box($img,540,592,436,128,'Valor pago',money($amount),$colors,3);
        receipt_bitmap_box($img,104,746,412,128,'Data do pagamento',date('d/m/Y H:i',strtotime($createdAt)),$colors,2);receipt_bitmap_box($img,540,746,436,128,'Forma de pagamento',status_label($method),$colors,2);receipt_bitmap_box($img,104,900,872,126,'Responsavel',$businessName,$colors,3);
        for($x=104;$x<976;$x+=25)imageline($img,$x,1084,min($x+12,976),1084,$colors['line']);receipt_bitmap_draw_colored($img,'Codigo do comprovante',104,1128,2,$colors['muted'],4);receipt_bitmap_draw_colored($img,$code,104,1164,3,$colors['text'],5);receipt_bitmap_draw_colored($img,'Gerado em '.date('d/m/Y',strtotime($createdAt)),730,1168,2,$colors['muted'],4);receipt_bitmap_draw_colored($img,'Documento digital de confirmacao do pagamento',250,1250,2,$colors['muted'],4);
    }
    imagepng($img,$absolute,6);imagedestroy($img);$relative='uploads/comprovantes-gerados/'.$filename;db()->prepare('INSERT INTO receipts(source_type,source_id,payer_user_id,recipient_user_id,company_id,amount,description,payment_method,proof_path,receipt_path,receipt_code,confirmed_by,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(source_type,source_id) DO UPDATE SET proof_path=excluded.proof_path,receipt_path=excluded.receipt_path,receipt_code=excluded.receipt_code,confirmed_by=excluded.confirmed_by')->execute([$sourceType,$sourceId,$payerId?:null,$recipientId?:null,$companyId?:null,$amount,$description,$method,$proofPath,$relative,$code,current_user()['id']??null,$createdAt]);return $relative;
}


function primary_super_admin_id(): int {
    try { return (int)db()->query("SELECT id FROM users WHERE role='super_admin' ORDER BY id LIMIT 1")->fetchColumn(); }
    catch (Throwable $e) { return 0; }
}

function wallet_enabled_for_user($user = null): bool {
    $user = $user ?: current_user();
    if (!$user) return false;
    if (in_array(($user['role'] ?? ''), ['super_admin','sub_admin'], true)) return true;
    return (int)($user['wallet_enabled'] ?? 0) === 1;
}

function wallet_recalculate_account(int $userId): void {
    if($userId<1)return;
    db()->prepare('INSERT OR IGNORE INTO wallet_accounts(user_id,balance,total_earned,total_withdrawn,updated_at) VALUES(?,?,?,?,?)')
        ->execute([$userId,0,0,0,now()]);
    // A carteira usa o extrato como fonte de verdade. Assim, se uma requisição
    // anterior gravou a movimentação e caiu antes de atualizar o saldo, a
    // próxima leitura reconcilia automaticamente sem duplicar crédito.
    $q=db()->prepare("SELECT COALESCE(SUM(amount),0) balance,COALESCE(SUM(CASE WHEN kind='sale' AND amount>0 THEN amount ELSE 0 END),0) earned,COALESCE(SUM(CASE WHEN kind='withdrawal' AND amount<0 THEN -amount ELSE 0 END),0) withdrawn FROM wallet_transactions WHERE user_id=?");
    $q->execute([$userId]);$totals=$q->fetch()?:['balance'=>0,'earned'=>0,'withdrawn'=>0];
    db()->prepare('UPDATE wallet_accounts SET balance=?,total_earned=?,total_withdrawn=?,updated_at=? WHERE user_id=?')
        ->execute([round((float)$totals['balance'],2),round((float)$totals['earned'],2),round((float)$totals['withdrawn'],2),now(),$userId]);
}

function wallet_account(int $userId): array {
    wallet_recalculate_account($userId);
    $q=db()->prepare('SELECT * FROM wallet_accounts WHERE user_id=?');$q->execute([$userId]);
    return $q->fetch() ?: ['user_id'=>$userId,'balance'=>0,'total_earned'=>0,'total_withdrawn'=>0,'updated_at'=>now()];
}

function wallet_pending_withdrawals(int $userId): float {
    $q=db()->prepare("SELECT COALESCE(SUM(amount),0) FROM withdrawal_requests WHERE user_id=? AND status='pending'");$q->execute([$userId]);
    return (float)$q->fetchColumn();
}

function wallet_available_balance(int $userId): float {
    $account=wallet_account($userId);
    return max(0,(float)$account['balance']-wallet_pending_withdrawals($userId));
}

function wallet_credit_sale(int $userId,float $amount,string $sourceType,int $sourceId,string $description=''): void {
    if($userId<1||$amount<=0)return;
    db()->prepare('INSERT OR IGNORE INTO wallet_accounts(user_id,balance,total_earned,total_withdrawn,updated_at) VALUES(?,?,?,?,?)')
        ->execute([$userId,0,0,0,now()]);
    $stmt=db()->prepare('INSERT OR IGNORE INTO wallet_transactions(user_id,source_type,source_id,kind,amount,description,created_at) VALUES(?,?,?,?,?,?,?)');
    $stmt->execute([$userId,$sourceType,$sourceId,'sale',round($amount,2),$description,now()]);
    // Sempre reconcilia, inclusive quando a movimentação já existia. Isso
    // corrige automaticamente saldos antigos que ficaram fora de sincronia.
    wallet_recalculate_account($userId);
}

function pix_billing_enabled_for_user($user = null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    if(in_array(($user['role']??''),['super_admin','sub_admin'],true))return true;
    return (int)($user['pix_billing_enabled']??0)===1;
}

function client_billing_enabled_for_user($user = null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    if(in_array(($user['role']??''),['super_admin','sub_admin'],true))return true;
    return (int)($user['client_billing_enabled']??0)===1;
}

function provider_clients_enabled_for_user($user = null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    if(in_array(($user['role']??''),['super_admin','sub_admin'],true))return true;
    return ($user['role']??'')==='service_provider' && (int)($user['provider_clients_enabled']??0)===1;
}

function provider_client_status(int $providerId): array {
    $q=db()->prepare('SELECT provider_client_limit,provider_clients_enabled FROM users WHERE id=?');$q->execute([$providerId]);$u=$q->fetch();
    $limit=max(0,(int)($u['provider_client_limit']??0));
    $q=db()->prepare('SELECT COUNT(*) FROM provider_clients WHERE provider_user_id=?');$q->execute([$providerId]);$used=(int)$q->fetchColumn();
    return ['enabled'=>(int)($u['provider_clients_enabled']??0)===1,'limit'=>$limit,'used'=>$used,'remaining'=>$limit>0?max(0,$limit-$used):null,'reached'=>$limit>0&&$used>=$limit];
}

function register_provider_client(int $providerId,int $clientId): bool {
    if($providerId<1||$clientId<1)return false;
    $status=provider_client_status($providerId);
    if(!$status['enabled'])return false;
    $exists=db()->prepare('SELECT id FROM provider_clients WHERE provider_user_id=? AND client_user_id=?');$exists->execute([$providerId,$clientId]);
    if($exists->fetchColumn())return true;
    if($status['reached'])return false;
    db()->prepare('INSERT OR IGNORE INTO provider_clients(provider_user_id,client_user_id,created_at) VALUES(?,?,?)')->execute([$providerId,$clientId,now()]);
    return true;
}

function platform_fee_context(int $recipientId,string $sourceType='',int $sourceId=0): array {
    $q=db()->prepare('SELECT u.role,u.company_id,u.segment_slug,c.sector,c.type FROM users u LEFT JOIN companies c ON c.id=u.company_id WHERE u.id=? LIMIT 1');$q->execute([$recipientId]);
    $row=$q->fetch()?:[];$categories=[];
    $segment=strtolower(trim((string)($row['segment_slug']??'')));
    if($segment==='')$segment=strtolower(trim((string)($row['sector']??'')));
    if($segment==='')$segment=strtolower(trim((string)($row['type']??'')));
    if($segment===''&&($row['role']??'')==='service_provider')$segment='services';
    try{
        if($sourceType==='service'&&$sourceId>0){$c=db()->prepare('SELECT s.category_id FROM service_requests sr JOIN services s ON s.id=sr.service_id WHERE sr.id=?');$c->execute([$sourceId]);$id=(int)$c->fetchColumn();if($id>0)$categories[]=$id;}
        elseif($sourceType==='order'&&$sourceId>0){$c=db()->prepare('SELECT DISTINCT p.category_id FROM order_items oi JOIN products p ON p.id=oi.product_id WHERE oi.order_id=? AND p.category_id IS NOT NULL');$c->execute([$sourceId]);$categories=array_values(array_filter(array_map('intval',$c->fetchAll(PDO::FETCH_COLUMN))));}
    }catch(Throwable $ignored){}
    return ['role'=>(string)($row['role']??''),'segment'=>$segment,'categories'=>$categories];
}

function platform_fee_rule_for(int $recipientId,string $sourceType='',int $sourceId=0): ?array {
    if($recipientId<1)return null;
    $ctx=platform_fee_context($recipientId,$sourceType,$sourceId);
    if(in_array($ctx['role'],['super_admin','sub_admin'],true))return null;
    $rows=db()->query("SELECT * FROM platform_fee_rules WHERE status='active' AND fee_value>0 ORDER BY id DESC")->fetchAll();
    $priorities=['user'=>50,'category'=>40,'segment'=>30,'role'=>20,'all'=>10];$best=null;$bestPriority=0;
    foreach($rows as $r){$type=(string)$r['scope_type'];$value=trim((string)$r['scope_value']);$match=false;
        if($type==='user')$match=(int)$value===$recipientId;
        elseif($type==='category')$match=in_array((int)$value,$ctx['categories'],true);
        elseif($type==='segment')$match=$value!==''&&strtolower($value)===strtolower($ctx['segment']);
        elseif($type==='role')$match=$value!==''&&$value===$ctx['role'];
        elseif($type==='all')$match=true;
        $priority=$priorities[$type]??0;if($match&&$priority>$bestPriority){$best=$r;$bestPriority=$priority;}
    }
    return $best;
}

function calculate_platform_fee(int $recipientId,float $baseAmount,string $sourceType='',int $sourceId=0): array {
    $base=round(max(0,$baseAmount),2);
    $uq=db()->prepare('SELECT * FROM users WHERE id=?');$uq->execute([$recipientId]);$recipient=$uq->fetch()?:[];
    $feePayer=fee_payer_preference_for_user($recipient);
    if(platform_billing_mode_for_user($recipient)==='monthly')return ['base'=>$base,'fee'=>0.0,'total'=>$base,'client_total'=>$base,'recipient_net'=>$base,'fee_payer'=>$feePayer,'rule'=>null];
    $rule=platform_fee_rule_for($recipientId,$sourceType,$sourceId);$fee=0.0;
    if($rule){$value=max(0,(float)$rule['fee_value']);$fee=$rule['fee_type']==='percent'?($base*$value/100):$value;}
    $fee=round(max(0,min($base,$fee)),2);
    $clientTotal=$feePayer==='client'?round($base+$fee,2):$base;
    $recipientNet=$feePayer==='provider'?round(max(0,$base-$fee),2):$base;
    return ['base'=>$base,'fee'=>$fee,'total'=>$clientTotal,'client_total'=>$clientTotal,'recipient_net'=>$recipientNet,'fee_payer'=>$feePayer,'rule'=>$rule];
}

function service_provider_radius_km(): float { return max(1,(float)setting('service_provider_radius_km','30')); }

function post_finance_pair(string $sourceType,int $sourceId,int $payerId,int $recipientId,float $amount,string $method,string $proof,string $receipt,string $description,int $companyId=0): void {
    if($amount<=0||$payerId<1||$recipientId<1)return;
    $uq=db()->prepare('SELECT * FROM users WHERE id=?');$uq->execute([$recipientId]);$recipient=$uq->fetch()?:[];
    $breakdown=calculate_platform_fee($recipientId,$amount,$sourceType,$sourceId);
    $base=(float)$breakdown['base'];$fee=(float)$breakdown['fee'];
    $feePayer=(string)($breakdown['fee_payer']??fee_payer_preference_for_user($recipient));
    $payerTotal=(float)($breakdown['client_total']??$breakdown['total']);
    $providerNet=(float)($breakdown['recipient_net']??$base);
    $sourceKey=$sourceType.':'.$sourceId;
    $sql='INSERT OR IGNORE INTO payments(owner_user_id,payer_user_id,company_id,reference_type,reference_id,amount,method,status,paid_at,proof,notes,created_at,receipt_path,entry_kind,source_key) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
    db()->prepare($sql)->execute([$recipientId,$payerId,$companyId?:null,$sourceType,$sourceId,$providerNet,$method,'paid',now(),$proof,$description.($fee>0&&$feePayer==='provider'?' • Líquido após taxa '.money($fee):' • Ganho'),now(),$receipt,'income',$sourceKey]);
    db()->prepare($sql)->execute([$payerId,$recipientId,$companyId?:null,$sourceType,$sourceId,$payerTotal,$method,'paid',now(),$proof,$description.($fee>0&&$feePayer==='client'?' • Gasto (inclui taxa '.money($fee).')':' • Gasto'),now(),$receipt,'expense',$sourceKey]);
    wallet_credit_sale($recipientId,$providerNet,$sourceType,$sourceId,$description.($fee>0&&$feePayer==='provider'?' • taxa descontada '.money($fee):''));
    if($fee>0){
        $adminId=primary_super_admin_id();
        if($adminId>0&&$adminId!==$recipientId){
            db()->prepare($sql)->execute([$adminId,$feePayer==='client'?$payerId:$recipientId,null,'platform_fee',$sourceId,$fee,$method,'paid',now(),$proof,'Taxa da plataforma • '.$description.' • paga por '.($feePayer==='client'?'cliente':'prestador'),now(),$receipt,'income','platform_fee:'.$sourceType.':'.$sourceId]);
        }
    }
}

function property_availability_signature(): string {
    try{
        $rows=db()->query("SELECT id,property_kind,status,available_weekdays,updated_at FROM properties WHERE status='available' ORDER BY id")->fetchAll();
        $book=db()->query("SELECT property_id,start_date,status,COALESCE(updated_at,created_at) changed_at FROM property_bookings WHERE status IN ('requested','approved','scheduled') AND (start_date IS NULL OR start_date>=date('now')) ORDER BY property_id,start_date,id")->fetchAll();
        return hash('sha256',json_encode([$rows,$book],JSON_UNESCAPED_UNICODE));
    }catch(Throwable $e){return '';}
}

function property_attention_kind(array $user): string {
    if(($user['role']??'')==='property_owner' || in_array(($user['role']??''),['super_admin','sub_admin'],true)) return '';
    $signature=property_availability_signature();
    if($signature===''||user_ui_state((int)$user['id'],'properties_seen_signature','')===$signature)return '';
    $pool=(int)db()->query("SELECT COUNT(*) FROM properties WHERE status='available' AND property_kind='pool'")->fetchColumn();
    if($pool>0)return 'pool';
    $property=(int)db()->query("SELECT COUNT(*) FROM properties WHERE status='available' AND COALESCE(property_kind,'property')<>'pool'")->fetchColumn();
    return $property>0?'property':'';
}

function mark_properties_seen(int $userId): void {
    save_user_ui_state($userId,'properties_seen_signature',property_availability_signature());
}

function user_can_access_club($user = null) {
    $user = $user ?: current_user();
    if (!$user) return false;
    if (in_array(($user['role']??''), ['super_admin','sub_admin'], true)) return true;
    if (($user['company_type'] ?? '') === 'shooting_club' && in_array($user['role'], ['company','employee'], true)) return true;
    $stmt = db()->prepare('SELECT COUNT(*) FROM club_members WHERE user_id=? AND status="active"');
    $stmt->execute([(int)$user['id']]);
    return (int)$stmt->fetchColumn() > 0;
}

function user_rating_summary($userId) {
    $stmt = db()->prepare("SELECT COALESCE(AVG(rating),0) rating_avg,COUNT(*) rating_count FROM reviews WHERE target_type IN ('service_provider','moving_driver') AND target_id=? AND status='published'");
    $stmt->execute([(int)$userId]);
    return $stmt->fetch() ?: ['rating_avg'=>0,'rating_count'=>0];
}

function release_pix_notification($payerUserId, $receiverUserId, $context) {
    $stmt = db()->prepare('SELECT name,pix_key,pix_key_type,pix_billing_enabled,role FROM users WHERE id=?');
    $stmt->execute([(int)$receiverUserId]);
    $receiver = $stmt->fetch();
    if (!$receiver || !pix_billing_enabled_for_user($receiver)) {
        create_notification((int)$payerUserId, 'Pagamento do serviço', 'O pagamento por Pix deste profissional ainda não foi liberado pelo administrador.', 'warning');
        return;
    }
    if (trim((string)$receiver['pix_key']) === '') {
        create_notification((int)$payerUserId, 'Pagamento do serviço', 'O profissional ainda não cadastrou uma chave Pix. Entre em contato pelo perfil.', 'warning');
        return;
    }
    $message = 'Pagamento de ' . $context . ': chave Pix ' . $receiver['pix_key'] . ' (' . ($receiver['pix_key_type'] ?: 'não informado') . ') — ' . $receiver['name'] . '.';
    create_notification((int)$payerUserId, 'Chave Pix liberada', $message, 'success');
}



function can_issue_charge_to($issuer, $recipientId) {
    $issuer = $issuer ?: current_user();
    $recipientId = (int)$recipientId;
    if (!$issuer || $recipientId < 1 || $recipientId === (int)$issuer['id']) return false;

    $rq = db()->prepare("SELECT id,role,company_id,status FROM users WHERE id=? LIMIT 1");
    $rq->execute([$recipientId]);
    $recipient = $rq->fetch();
    if (!$recipient || ($recipient['status'] ?? '') !== 'active') return false;
    if (($issuer['role'] ?? '') === 'super_admin') return true;
    if (($issuer['role'] ?? '') === 'sub_admin') return !in_array(($recipient['role'] ?? ''), ['super_admin','sub_admin'], true);
    if (!client_billing_enabled_for_user($issuer)) return false;

    $issuerId = (int)$issuer['id'];
    $companyId = (int)($issuer['company_id'] ?? 0);

    // Empresa/funcionário: cliente cadastrado pela própria empresa.
    if ($companyId > 0 && in_array(($issuer['role'] ?? ''), ['company','employee'], true)) {
        if (($recipient['role'] ?? '') === 'client' && (int)($recipient['company_id'] ?? 0) === $companyId) return true;

        $checks = [
            ['SELECT 1 FROM orders WHERE company_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM delivery_jobs WHERE company_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM rides WHERE company_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM vehicle_reservations vr JOIN vehicles v ON v.id=vr.vehicle_id WHERE v.company_id=? AND vr.client_id=? AND vr.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM vehicle_proposals vp JOIN vehicles v ON v.id=vp.vehicle_id WHERE v.company_id=? AND vp.client_id=? AND vp.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM vehicle_appointments va JOIN vehicles v ON v.id=va.vehicle_id WHERE v.company_id=? AND va.client_id=? AND va.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM financing_requests fr JOIN vehicles v ON v.id=fr.vehicle_id WHERE v.company_id=? AND fr.client_id=? AND fr.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM service_requests sr JOIN users p ON p.id=sr.provider_id WHERE p.company_id=? AND sr.client_id=? AND sr.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM property_bookings pb JOIN properties pr ON pr.id=pb.property_id JOIN users ow ON ow.id=pr.owner_id WHERE ow.company_id=? AND pb.client_id=? AND pb.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
            ['SELECT 1 FROM moving_requests mr JOIN users d ON d.id=mr.driver_id WHERE d.company_id=? AND mr.client_id=? AND mr.status NOT IN ("cancelled","rejected") LIMIT 1', [$companyId,$recipientId]],
        ];
        foreach ($checks as $check) {
            $q = db()->prepare($check[0]);
            $q->execute($check[1]);
            if ($q->fetchColumn()) return true;
        }
        return false;
    }

    // Profissionais autônomos: somente clientes com vínculo real de atendimento.
    $checks = [
        ['SELECT 1 FROM service_requests WHERE provider_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM rides WHERE driver_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM moving_requests WHERE driver_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM delivery_jobs WHERE assigned_courier_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM orders WHERE delivery_user_id=? AND client_id=? AND status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM property_bookings pb JOIN properties pr ON pr.id=pb.property_id WHERE pr.owner_id=? AND pb.client_id=? AND pb.status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM vehicle_reservations vr JOIN vehicles v ON v.id=vr.vehicle_id WHERE v.owner_user_id=? AND vr.client_id=? AND vr.status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
        ['SELECT 1 FROM vehicle_proposals vp JOIN vehicles v ON v.id=vp.vehicle_id WHERE v.owner_user_id=? AND vp.client_id=? AND vp.status NOT IN ("cancelled","rejected") LIMIT 1', [$issuerId,$recipientId]],
    ];
    foreach ($checks as $check) {
        $q = db()->prepare($check[0]);
        $q->execute($check[1]);
        if ($q->fetchColumn()) return true;
    }
    return false;
}

function charge_eligible_users($issuer = null, $limit = 200) {
    $issuer = $issuer ?: current_user();
    if (!$issuer) return [];
    if (in_array(($issuer['role'] ?? ''), ['super_admin','sub_admin'], true)) {
        $q = db()->prepare("SELECT id,name,public_id FROM users WHERE role NOT IN ('super_admin','sub_admin') AND status='active' ORDER BY name LIMIT ?");
        $q->bindValue(1, max(1,(int)$limit), PDO::PARAM_INT);
        $q->execute();
        return $q->fetchAll();
    }
    $q = db()->prepare("SELECT id,name,public_id FROM users WHERE id<>? AND status='active' ORDER BY name LIMIT 800");
    $q->execute([(int)$issuer['id']]);
    $out = [];
    foreach ($q->fetchAll() as $candidate) {
        if (can_issue_charge_to($issuer, (int)$candidate['id'])) {
            $out[] = $candidate;
            if (count($out) >= max(1,(int)$limit)) break;
        }
    }
    return $out;
}

function normalize_social_handle($value) {
    $value = trim((string)$value);
    if ($value === '') return '';
    $value = preg_replace('~^https?://(www\.)?(instagram\.com|t\.me)/~i', '', $value);
    return ltrim(trim($value, "/ \t\n\r\0\x0B"), '@');
}

function instagram_url($value) {
    $handle = normalize_social_handle($value);
    return $handle === '' ? '#' : 'https://instagram.com/' . rawurlencode($handle);
}

function telegram_url($value) {
    $handle = normalize_social_handle($value);
    return $handle === '' ? '#' : 'https://t.me/' . rawurlencode($handle);
}

function normalize_state($value) {
    $state = strtoupper(preg_replace('/[^A-Za-z]/', '', (string)$value));
    return substr($state, 0, 2);
}

function full_user_address($user) {
    $address = trim((string)($user['home_address'] ?? ''));
    $number = trim((string)($user['home_number'] ?? ''));
    if ($number !== '' && $address !== '' && stripos($address, $number) === false) $address .= ', ' . $number;
    return $address;
}

function cart_count($userId = null) {
    $userId = $userId ?: (int)(current_user()['id'] ?? 0);
    if (!$userId) return 0;
    $stmt = db()->prepare('SELECT COALESCE(SUM(qty),0) FROM cart_items WHERE user_id=?');
    $stmt->execute([(int)$userId]);
    return (int)ceil((float)$stmt->fetchColumn());
}

function normalize_transport_text($value) {
    $value = trim((string)$value);
    if ($value === '') return '';
    if (function_exists('iconv')) {
        $ascii = @iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$value);
        if ($ascii !== false) $value = $ascii;
    }
    return strtolower(preg_replace('/[^a-z0-9]+/i',' ', $value));
}

function transport_company_kind_from_row($company) {
    if (!$company) return null;
    $text = normalize_transport_text(($company['type'] ?? '').' '.($company['segment_name'] ?? '').' '.($company['name'] ?? ''));
    if (strpos($text,'moto') !== false && strpos($text,'tax') !== false) return 'mototaxi';
    if (strpos($text,'mototaxi') !== false) return 'mototaxi';
    if (strpos($text,'tax') !== false) return 'taxi';
    return null;
}

function transport_company_info($companyId) {
    $companyId=(int)$companyId;
    if($companyId<1)return null;
    $q=db()->prepare("SELECT c.*,COALESCE(s.name,'') segment_name FROM companies c LEFT JOIN segments s ON s.slug=c.type WHERE c.id=? LIMIT 1");
    $q->execute([$companyId]);
    $row=$q->fetch();
    if(!$row)return null;
    $row['transport_kind']=transport_company_kind_from_row($row);
    return $row;
}

function user_transport_company_kind($user=null) {
    $user=$user?:current_user();
    if(!$user || !in_array($user['role']??'', ['company','employee'], true)) return null;
    if(!empty($user['company_id'])){
        $row=transport_company_info((int)$user['company_id']);
        if($row && !empty($row['transport_kind'])) return $row['transport_kind'];
    }
    return transport_company_kind_from_row(['type'=>$user['company_type']??'','name'=>$user['company_name']??'']);
}

function company_has_visible_module($module) {
    if ($module === 'rental') {
        return (int)db()->query("SELECT COUNT(*) FROM vehicles v LEFT JOIN companies c ON c.id=v.company_id WHERE v.listing_mode='rental' AND v.status IN ('available','reserved') AND (c.status='active' OR c.id IS NULL)")->fetchColumn() > 0;
    }
    if ($module === 'vehicle_store') {
        return (int)db()->query("SELECT COUNT(*) FROM vehicles v LEFT JOIN companies c ON c.id=v.company_id WHERE v.listing_mode='sale' AND v.status IN ('available','reserved') AND (c.status='active' OR c.id IS NULL)")->fetchColumn() > 0;
    }
    if ($module === 'moving') {
        // Para usuários comuns o módulo só aparece quando existe uma conta ativa
        // de Mudanças/Fretes. Administradores continuam vendo a área para gestão.
        return (int)db()->query("SELECT COUNT(*) FROM users u LEFT JOIN companies c ON c.id=u.company_id WHERE u.status='active' AND (u.role='moving_provider' OR (c.status='active' AND c.type='moving'))")->fetchColumn() > 0;
    }
    return true;
}

function terminal_status($status) {
    return in_array((string)$status, ['completed','cancelled','delivered','rejected'], true);
}


function shuttle_normalize_city($value) {
    $value = normalize_transport_text((string)$value);
    $value = preg_replace('/\b(brasil|brazil)\b/', '', $value);
    return trim(preg_replace('/\s+/', ' ', $value));
}

function shuttle_route_fares($routeId) {
    $q = db()->prepare("SELECT * FROM shuttle_route_fares WHERE route_id=? AND status='active' ORDER BY city_name");
    $q->execute([(int)$routeId]);
    return $q->fetchAll();
}

function shuttle_route_fares_text($routeId) {
    $lines=[];
    foreach (shuttle_route_fares($routeId) as $fare) {
        $lines[] = $fare['city_name'].' = '.number_format((float)$fare['price_per_seat'],2,',','.');
    }
    return implode("\n",$lines);
}

function shuttle_price_for_destination($routeId, $destination, $defaultPrice = 0) {
    $needle = shuttle_normalize_city($destination);
    if ($needle === '') return (float)$defaultPrice;
    $best = null; $bestLen = -1;
    foreach (shuttle_route_fares($routeId) as $fare) {
        $city = (string)$fare['normalized_city'];
        if ($city === '') continue;
        if (strpos($needle,$city)!==false || strpos($city,$needle)!==false) {
            if (strlen($city)>$bestLen) { $best=(float)$fare['price_per_seat']; $bestLen=strlen($city); }
        }
    }
    return $best !== null ? $best : (float)$defaultPrice;
}


function shuttle_route_accepts_destination($routeId, $destination): bool {
    $needle = shuttle_normalize_city($destination);
    if ($needle === '') return true;
    $q = db()->prepare('SELECT name,destination_city,dropoff_points FROM shuttle_routes WHERE id=? LIMIT 1');
    $q->execute([(int)$routeId]);
    $route = $q->fetch();
    if (!$route) return false;
    foreach ([$route['name']??'', $route['destination_city']??'', $route['dropoff_points']??''] as $value) {
        $hay = shuttle_normalize_city($value);
        if ($hay !== '' && (strpos($needle,$hay)!==false || strpos($hay,$needle)!==false)) return true;
        foreach (preg_split('/[,;\n\r\|]+/u',(string)$value) ?: [] as $part) {
            $part = shuttle_normalize_city($part);
            if ($part !== '' && (strpos($needle,$part)!==false || strpos($part,$needle)!==false)) return true;
        }
    }
    foreach (shuttle_route_fares((int)$routeId) as $fare) {
        $city=(string)($fare['normalized_city']??'');
        if ($city!=='' && (strpos($needle,$city)!==false || strpos($city,$needle)!==false)) return true;
    }
    return false;
}

function localized_decimal($value) {
    $v = trim((string)$value);
    if ($v === '') return 0.0;
    $v = preg_replace('/[^0-9,.-]/', '', $v);
    $lastComma = strrpos($v, ',');
    $lastDot = strrpos($v, '.');
    if ($lastComma !== false && $lastDot !== false) {
        if ($lastComma > $lastDot) $v = str_replace(',', '.', str_replace('.', '', $v));
        else $v = str_replace(',', '', $v);
    } elseif ($lastComma !== false) {
        $v = str_replace('.', '', $v);
        $v = str_replace(',', '.', $v);
    }
    return (float)$v;
}


// V2.8.26 — limites individuais de cadastros por empresa.
// V2.8.28 — o cadastro só é liberado quando o Administrador Sênior define um limite positivo.
function company_capacity_info($companyId, $kind) {
    $companyId = (int)$companyId;
    // Clínicas usam os limites próprios definidos pelo administrador. Para atendentes,
    // 0 significa ilimitado, conforme a configuração específica do módulo clínico.
    if ($kind === 'employees' && $companyId > 0 && company_is_clinic($companyId)) {
        $ci = clinic_capacity_info($companyId,'attendants');
        return ['limit'=>$ci['limit'],'used'=>$ci['used'],'remaining'=>$ci['remaining'],'enabled'=>true,'reached'=>$ci['reached'],'label'=>'atendentes','unlimited'=>$ci['unlimited']];
    }
    $map = [
        'clients' => ['column'=>'client_limit','label'=>'clientes'],
        'employees' => ['column'=>'employee_limit','label'=>'funcionários'],
        'couriers' => ['column'=>'courier_limit','label'=>'entregadores'],
    ];
    if ($companyId < 1 || !isset($map[$kind])) return ['limit'=>0,'used'=>0,'remaining'=>0,'enabled'=>false,'reached'=>true,'label'=>$kind];
    $column = $map[$kind]['column'];
    $q = db()->prepare('SELECT '.$column.' FROM companies WHERE id=?');
    $q->execute([$companyId]);
    $raw = $q->fetchColumn();
    // Valores antigos -1 ("sem limite") passam a significar "não liberado".
    $limit = ($raw === false || $raw === null || $raw === '') ? 0 : max(0,(int)$raw);
    if ($kind === 'clients') {
        $q = db()->prepare("SELECT COUNT(DISTINCT u.id) FROM users u LEFT JOIN delivery_address_permissions dap ON dap.user_id=u.id AND dap.company_id=? AND dap.status='active' AND dap.always_visible=1 WHERE (u.role='client' AND u.company_id=?) OR dap.id IS NOT NULL");
        $q->execute([$companyId,$companyId]);
    } elseif ($kind === 'employees') {
        $q = db()->prepare("SELECT COUNT(*) FROM users WHERE company_id=? AND role='employee'");
        $q->execute([$companyId]);
    } else {
        $q = db()->prepare("SELECT COUNT(DISTINCT user_id) FROM company_couriers WHERE company_id=? AND status='active'");
        $q->execute([$companyId]);
    }
    $used = (int)$q->fetchColumn();
    $enabled = $limit > 0;
    return [
        'limit'=>$limit,
        'used'=>$used,
        'remaining'=>$enabled ? max(0,$limit-$used) : 0,
        'enabled'=>$enabled,
        'reached'=>!$enabled || $used >= $limit,
        'label'=>$map[$kind]['label'],
    ];
}

function company_capacity_text($companyId, $kind) {
    $info = company_capacity_info($companyId,$kind);
    if(!empty($info['unlimited'])) return $info['used'].' / ilimitado';
    return !$info['enabled'] ? 'não liberado' : ($info['used'].' / '.$info['limit']);
}

function assert_company_capacity($companyId, $kind) {
    $info = company_capacity_info($companyId,$kind);
    if (!$info['enabled']) {
        throw new RuntimeException('Cadastro de '.$info['label'].' não liberado pelo administrador para esta empresa.');
    }
    if ($info['reached']) {
        throw new RuntimeException('Limite de '.$info['label'].' atingido para esta empresa ('.$info['used'].'/'.$info['limit'].'). Solicite ao administrador o aumento do limite.');
    }
    return $info;
}


/* ===== LinkExpress — convite de cliente por QR Code ===== */
function company_client_invite_secret(): string {
    $secret=(string)setting('company_client_invite_secret','');
    if(strlen($secret)<32){
        try{$secret=bin2hex(random_bytes(32));}catch(Throwable $e){$secret=hash('sha256',config('db_path').'|'.platform_name().'|'.microtime(true).'|'.mt_rand());}
        save_setting('company_client_invite_secret',$secret);
    }
    return $secret;
}

function company_client_invite_token(int $companyId,int $moduleLinkId=0): string {
    if($companyId<1)return '';
    $payload=$moduleLinkId>0?'company-client-v2:'.$companyId.':'.$moduleLinkId:'company-client-v1:'.$companyId;
    return hash_hmac('sha256',$payload,company_client_invite_secret());
}

function company_client_invite_valid(int $companyId,string $token,int $moduleLinkId=0): bool {
    if($companyId<1||$token==='')return false;
    $expected=company_client_invite_token($companyId,$moduleLinkId);
    return $expected!==''&&hash_equals($expected,$token);
}

function company_client_invite_url(int $companyId,int $moduleLinkId=0): string {
    $params=['company_id'=>$companyId,'token'=>company_client_invite_token($companyId,$moduleLinkId)];
    if($moduleLinkId>0)$params['module_link_id']=$moduleLinkId;
    return url('company_join.php?'.http_build_query($params));
}

function store_pending_company_join_invite(int $companyId,string $token,int $moduleLinkId=0): bool {
    if(!company_client_invite_valid($companyId,$token,$moduleLinkId))return false;
    $q=db()->prepare("SELECT id,name,status FROM companies WHERE id=? AND status='active' LIMIT 1");$q->execute([$companyId]);
    $company=$q->fetch();if(!$company)return false;
    if($moduleLinkId>0){$mq=db()->prepare("SELECT id,title,status FROM external_module_links WHERE id=? AND status='active' LIMIT 1");$mq->execute([$moduleLinkId]);if(!$mq->fetch())return false;}
    $_SESSION['company_join_invite']=['company_id'=>$companyId,'token'=>$token,'module_link_id'=>$moduleLinkId];
    return true;
}

function pending_company_join_invite(): ?array {
    $invite=$_SESSION['company_join_invite']??null;
    if(!is_array($invite))return null;
    $companyId=(int)($invite['company_id']??0);$token=(string)($invite['token']??'');$moduleLinkId=(int)($invite['module_link_id']??0);
    if(!company_client_invite_valid($companyId,$token,$moduleLinkId)){unset($_SESSION['company_join_invite']);return null;}
    $q=db()->prepare("SELECT id,name,logo,description,address,address_number,city,state,status FROM companies WHERE id=? AND status='active' LIMIT 1");$q->execute([$companyId]);
    $company=$q->fetch();if(!$company){unset($_SESSION['company_join_invite']);return null;}
    $module=null;if($moduleLinkId>0){$mq=db()->prepare("SELECT id,title,url,link_kind,status FROM external_module_links WHERE id=? AND status='active' LIMIT 1");$mq->execute([$moduleLinkId]);$module=$mq->fetch();if(!$module){unset($_SESSION['company_join_invite']);return null;}}
    return ['company_id'=>$companyId,'token'=>$token,'module_link_id'=>$moduleLinkId,'module'=>$module,'company'=>$company];
}

function clear_pending_company_join_invite(): void { unset($_SESSION['company_join_invite']); }

function client_is_linked_to_company(int $userId,int $companyId): bool {
    if($userId<1||$companyId<1)return false;
    $q=db()->prepare("SELECT COUNT(*) FROM users u LEFT JOIN delivery_address_permissions dap ON dap.user_id=u.id AND dap.company_id=? AND dap.status='active' AND dap.always_visible=1 WHERE u.id=? AND ((u.role='client' AND u.company_id=?) OR dap.id IS NOT NULL)");
    $q->execute([$companyId,$userId,$companyId]);
    return (int)$q->fetchColumn()>0;
}

function link_client_to_company_from_invite(int $userId,int $companyId,int $moduleLinkId=0): array {
    $q=db()->prepare("SELECT id,name,role,status FROM users WHERE id=? LIMIT 1");$q->execute([$userId]);$client=$q->fetch();
    if(!$client)throw new RuntimeException('Conta não encontrada para concluir o vínculo.');
    if($client['status']!=='active')throw new RuntimeException('Sua conta precisa estar ativa para concluir o vínculo.');
    $cq=db()->prepare("SELECT id,name,owner_user_id,status FROM companies WHERE id=? LIMIT 1");$cq->execute([$companyId]);$company=$cq->fetch();
    if(!$company||$company['status']!=='active')throw new RuntimeException('Esta empresa não está disponível para novos cadastros.');
    if(!client_is_linked_to_company($userId,$companyId)){
        assert_company_capacity($companyId,'clients');
        db()->prepare('INSERT INTO delivery_address_permissions(company_id,user_id,always_visible,status,created_at,updated_at) VALUES(?,?,1,"active",?,?) ON CONFLICT(company_id,user_id) DO UPDATE SET always_visible=1,status="active",updated_at=excluded.updated_at')
          ->execute([$companyId,$userId,now(),now()]);
        create_notification($userId,'Cadastro na empresa confirmado','Você agora está cadastrado como cliente de '.$company['name'].'.','success','dashboard.php?page=catalog&store='.$companyId,'company_client',$companyId);
        if((int)($company['owner_user_id']??0)>0)create_notification((int)$company['owner_user_id'],'Novo cliente via QR Code',$client['name'].' entrou na carteira de clientes da empresa.','success','dashboard.php?page=company_clients','company_client',$userId);
    }
    if($moduleLinkId>0){
        $mq=db()->prepare("SELECT id,title,status FROM external_module_links WHERE id=? AND status='active' LIMIT 1");$mq->execute([$moduleLinkId]);$module=$mq->fetch();
        if(!$module)throw new RuntimeException('O site deste convite não está mais disponível.');
        $grantedBy=(int)($company['owner_user_id']??0);if($grantedBy<1)throw new RuntimeException('A empresa ainda não possui um responsável para liberar este acesso.');
        db()->prepare("INSERT INTO external_module_client_access(module_link_id,granted_by_user_id,company_id,client_user_id,display_name,status,created_at,updated_at) VALUES(?,?,?,?,?,'active',?,?) ON CONFLICT(module_link_id,granted_by_user_id,client_user_id) DO UPDATE SET company_id=excluded.company_id,display_name=excluded.display_name,status='active',updated_at=excluded.updated_at")
          ->execute([$moduleLinkId,$grantedBy,$companyId,$userId,$client['name'],now(),now()]);
        create_notification($userId,'Site liberado automaticamente','O acesso a '.$module['title'].' foi liberado pelo QR Code da empresa '.$company['name'].'.','success','dashboard.php?page=new_area','external_module_link',$moduleLinkId);
        $company['module_title']=$module['title'];
    }
    return $company;
}

function provider_client_invite_token(int $providerId): string {
    return $providerId>0 ? hash_hmac('sha256','provider-customer-v1:'.$providerId,company_client_invite_secret()) : '';
}

function provider_client_invite_valid(int $providerId,string $token): bool {
    $expected=provider_client_invite_token($providerId);
    return $expected!==''&&$token!==''&&hash_equals($expected,$token);
}

function provider_client_invite_url(int $providerId): string {
    return url('provider_join.php?'.http_build_query(['provider_id'=>$providerId,'token'=>provider_client_invite_token($providerId)]));
}

function store_pending_provider_join_invite(int $providerId,string $token): bool {
    if(!provider_client_invite_valid($providerId,$token))return false;
    $q=db()->prepare("SELECT id,name,status,role,provider_clients_enabled FROM users WHERE id=? AND role='service_provider' AND status='active' LIMIT 1");$q->execute([$providerId]);
    if(!$q->fetch())return false;
    $_SESSION['provider_join_invite']=['provider_id'=>$providerId,'token'=>$token];return true;
}

function pending_provider_join_invite(): ?array {
    $invite=$_SESSION['provider_join_invite']??null;if(!is_array($invite))return null;
    $providerId=(int)($invite['provider_id']??0);$token=(string)($invite['token']??'');
    if(!provider_client_invite_valid($providerId,$token)){unset($_SESSION['provider_join_invite']);return null;}
    $q=db()->prepare("SELECT id,name,username,public_id,avatar,city,state,status,role,provider_clients_enabled FROM users WHERE id=? AND role='service_provider' AND status='active' LIMIT 1");$q->execute([$providerId]);$provider=$q->fetch();
    if(!$provider){unset($_SESSION['provider_join_invite']);return null;}
    return ['provider_id'=>$providerId,'token'=>$token,'provider'=>$provider];
}

function clear_pending_provider_join_invite(): void { unset($_SESSION['provider_join_invite']); }

function link_user_to_provider_from_invite(int $userId,int $providerId): array {
    if($userId<1||$providerId<1||$userId===$providerId)throw new RuntimeException('Não é possível criar este vínculo.');
    $q=db()->prepare("SELECT id,name,status FROM users WHERE id=? LIMIT 1");$q->execute([$userId]);$customer=$q->fetch();
    if(!$customer||$customer['status']!=='active')throw new RuntimeException('Sua conta precisa estar ativa para concluir o vínculo.');
    $q=db()->prepare("SELECT id,name,status,role FROM users WHERE id=? AND role='service_provider' AND status='active' LIMIT 1");$q->execute([$providerId]);$provider=$q->fetch();
    if(!$provider)throw new RuntimeException('Este prestador não está disponível para novos vínculos.');
    if(!register_provider_client($providerId,$userId))throw new RuntimeException('O prestador atingiu o limite de clientes ou ainda não possui esta função liberada.');
    create_notification($userId,'Vínculo com prestador confirmado','Você agora está cadastrado como cliente de '.$provider['name'].'.','success','dashboard.php?page=affiliations','provider_client',$providerId);
    create_notification($providerId,'Novo cliente via QR Code',$customer['name'].' entrou na sua carteira de clientes.','success','dashboard.php?page=provider_clients','provider_client',$userId);
    return $provider;
}

function user_customer_company_links(int $userId): array {
    $q=db()->prepare("SELECT DISTINCT c.id,c.name,c.logo,c.type,c.description,c.phone,c.address,c.address_number,c.city,c.state,dap.id permission_id,CASE WHEN u.role='client' AND u.company_id=c.id THEN 1 ELSE 0 END direct_link FROM users u JOIN companies c ON ((u.role='client' AND u.company_id=c.id) OR EXISTS(SELECT 1 FROM delivery_address_permissions d WHERE d.company_id=c.id AND d.user_id=u.id AND d.status='active' AND d.always_visible=1)) LEFT JOIN delivery_address_permissions dap ON dap.company_id=c.id AND dap.user_id=u.id AND dap.status='active' AND dap.always_visible=1 WHERE u.id=? AND c.status='active' ORDER BY c.name");
    $q->execute([$userId]);return $q->fetchAll();
}

function user_customer_provider_links(int $userId): array {
    $q=db()->prepare("SELECT pc.id link_id,u.id,u.name,u.username,u.public_id,u.avatar,u.city,u.state,u.phone FROM provider_clients pc JOIN users u ON u.id=pc.provider_user_id WHERE pc.client_user_id=? AND u.status='active' ORDER BY u.name");
    $q->execute([$userId]);return $q->fetchAll();
}


/* ===== SuperLoja V2.8.39 — proteção dos perfis e empresas de demonstração ===== */
function demo_user_emails() {
    return [
        'admin@linkexpress.local',
        'empresa@linkexpress.local',
        'cliente@linkexpress.local',
        'entregador@linkexpress.local',
        'mototaxi@linkexpress.local',
        'prestador@linkexpress.local',
        'imoveis@linkexpress.local',
        'locadora@linkexpress.local',
    ];
}

function is_demo_user_account($user) {
    if (!is_array($user)) return false;
    $email = strtolower(trim((string)($user['email'] ?? '')));
    return $email !== '' && in_array($email, demo_user_emails(), true);
}

function is_demo_company_account($company) {
    if (!is_array($company)) return false;
    $ownerEmail = strtolower(trim((string)($company['owner_email'] ?? '')));
    if ($ownerEmail !== '' && in_array($ownerEmail, demo_user_emails(), true)) return true;
    $slug = strtolower(trim((string)($company['slug'] ?? '')));
    if (in_array($slug, ['sabor-express','linkcar-locadora'], true)) return true;
    $name = strtolower(trim((string)($company['name'] ?? '')));
    return in_array($name, ['sabor express','linkcar locadora'], true);
}

function platform_billing_mode_for_user($user = null): string {
    $user=$user?:current_user();
    $mode=(string)($user['platform_billing_mode']??'transaction');
    return in_array($mode,['monthly','transaction'],true)?$mode:'transaction';
}

function platform_links_enabled_for_user($user = null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    $role=(string)($user['role']??'');
    if($role==='super_admin')return true;
    if($role==='sub_admin')return false;
    return (int)($user['platform_links_enabled']??0)===1;
}

function service_payment_destination(array $provider): array {
    $ownEnabled=(int)($provider['own_receiving_enabled']??0)===1;
    $preference=(string)($provider['receiving_method']??'pix');
    if(!in_array($preference,['pix','link'],true))$preference='pix';
    $ownPix=trim((string)($provider['pix_key']??''));
    $ownLink=trim((string)($provider['personal_payment_link']??''));
    $ownLinkValid=$ownLink!==''&&filter_var($ownLink,FILTER_VALIDATE_URL);

    if($ownEnabled){
        if($preference==='link'&&$ownLinkValid)return ['channel'=>'provider_link','method'=>'link','label'=>'Link de pagamento do prestador','pix'=>'','url'=>$ownLink,'own'=>true];
        if($preference==='pix'&&$ownPix!=='')return ['channel'=>'provider_pix','method'=>'pix','label'=>'Pix do prestador','pix'=>$ownPix,'url'=>'','own'=>true];
        if($ownPix!=='')return ['channel'=>'provider_pix','method'=>'pix','label'=>'Pix do prestador','pix'=>$ownPix,'url'=>'','own'=>true];
        if($ownLinkValid)return ['channel'=>'provider_link','method'=>'link','label'=>'Link de pagamento do prestador','pix'=>'','url'=>$ownLink,'own'=>true];
    }

    $platformPix=trim((string)setting('pix_key',''));
    // Link próprio para serviços/transações. Se ainda não estiver preenchido,
    // usa o link padrão da plataforma como fallback para não deixar o cliente
    // sem uma opção de pagamento.
    $platformLink=trim((string)setting('platform_service_payment_link',''));
    if($platformLink==='')$platformLink=trim((string)setting('subscription_payment_link',''));
    $platformLinkValid=$platformLink!==''&&filter_var($platformLink,FILTER_VALIDATE_URL);
    if(platform_links_enabled_for_user($provider)&&$platformLinkValid)return ['channel'=>'platform_link','method'=>'link','label'=>'Link de pagamento da plataforma','pix'=>'','url'=>$platformLink,'own'=>false];
    if($platformPix!=='')return ['channel'=>'platform_pix','method'=>'pix','label'=>'Pix da plataforma','pix'=>$platformPix,'url'=>'','own'=>false];
    // Se o Pix da plataforma não estiver configurado, o link global válido é
    // usado como último fallback para que o pagamento continue possível.
    if($platformLinkValid)return ['channel'=>'platform_link','method'=>'link','label'=>'Link de pagamento da plataforma','pix'=>'','url'=>$platformLink,'own'=>false];
    return ['channel'=>'none','method'=>'','label'=>'Forma de pagamento indisponível','pix'=>'','url'=>'','own'=>false];
}

function fee_payer_preference_for_user($user = null): string {
    $user=$user?:current_user();
    $pref=(string)($user['fee_payer_preference']??'client');
    return in_array($pref,['client','provider'],true)?$pref:'client';
}

function preferred_plan_payment_link(int $planId): ?array {
    if($planId<1)return null;
    $q=db()->prepare("SELECT pl.* FROM payment_links pl JOIN users u ON u.id=pl.owner_user_id WHERE pl.plan_id=? AND pl.status='active' AND COALESCE(pl.url,'')<>'' AND u.role='super_admin' ORDER BY pl.id DESC LIMIT 1");
    $q->execute([$planId]);
    $row=$q->fetch();
    return $row?:null;
}

function withdrawal_fee_breakdown(float $amount): array {
    $amount=max(0,$amount);
    $type=(string)setting('wallet_withdrawal_fee_type','fixed');
    $value=max(0,(float)setting('wallet_withdrawal_fee_value','0'));
    $fee=$type==='percent'?($amount*$value/100):$value;
    $fee=round(min($amount,max(0,$fee)),2);
    return ['gross'=>round($amount,2),'fee'=>$fee,'payout'=>round(max(0,$amount-$fee),2),'type'=>$type,'value'=>$value];
}

function user_new_area_enabled($user=null): bool {
    $user=$user?:current_user();
    if(!$user)return false;
    // V10: a Central de Integrações é uma área padrão da plataforma para toda conta ativa.
    return (string)($user['status']??'active')==='active';
}

function integration_can_publish($user=null): bool {
    $user=$user?:current_user();
    if(!$user||!user_new_area_enabled($user))return false;
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true))return true;
    return in_array((string)($user['role']??''),[
        'company','employee','service_provider','clinic_professional','property_owner','rental_company',
        'moving_provider','shuttle_driver','courier','motofreight','mototaxi','taxi','private_driver'
    ],true);
}

function new_area_link_available_to_user(int $linkId,array $user): bool {
    if($linkId<1||empty($user['id'])||!user_new_area_enabled($user))return false;
    if(is_admin())return true;
    $companyId=resolve_company_id_for_user($user);if($companyId<1)$companyId=(int)($user['company_id']??0);
    $q=db()->prepare("SELECT COUNT(*) FROM external_module_links WHERE id=? AND status='active' AND (created_by=? OR COALESCE(target_type,'all')='all' OR (target_type='user' AND target_id=?) OR (target_type='company' AND target_id=?))");
    $q->execute([$linkId,(int)$user['id'],(int)$user['id'],$companyId]);
    if((int)$q->fetchColumn()>0)return true;
    $q=db()->prepare("SELECT COUNT(*) FROM external_module_client_access a JOIN external_module_links l ON l.id=a.module_link_id WHERE a.module_link_id=? AND a.client_user_id=? AND a.status='active' AND l.status='active'");
    $q->execute([$linkId,(int)$user['id']]);return (int)$q->fetchColumn()>0;
}

function new_area_manageable_clients(array $user): array {
    $userId=(int)($user['id']??0);if($userId<1)return [];$companyId=resolve_company_id_for_user($user);if($companyId<1)$companyId=(int)($user['company_id']??0);
    if($companyId>0&&in_array((string)($user['role']??''),['company','employee'],true)){
        $q=db()->prepare("SELECT DISTINCT u.id,u.name,u.public_id,u.email FROM users u LEFT JOIN delivery_address_permissions dap ON dap.user_id=u.id AND dap.company_id=? AND dap.status='active' AND dap.always_visible=1 WHERE u.status='active' AND ((u.role='client' AND u.company_id=?) OR dap.id IS NOT NULL) ORDER BY u.name");$q->execute([$companyId,$companyId]);return $q->fetchAll();
    }
    $q=db()->prepare("SELECT u.id,u.name,u.public_id,u.email FROM provider_clients pc JOIN users u ON u.id=pc.client_user_id WHERE pc.provider_user_id=? AND u.status='active' ORDER BY u.name");$q->execute([$userId]);return $q->fetchAll();
}

function new_area_user_can_manage_client(array $user,int $clientId): bool {
    foreach(new_area_manageable_clients($user) as $client)if((int)$client['id']===$clientId)return true;return false;
}

function integration_link_invite_token(int $linkId): string {
    if($linkId<1)return '';
    $q=db()->prepare("SELECT id,COALESCE(created_by,0) created_by,status FROM external_module_links WHERE id=? LIMIT 1");$q->execute([$linkId]);$link=$q->fetch();
    if(!$link||$link['status']!=='active')return '';
    return hash_hmac('sha256','integration-link-v1:'.$linkId.':'.(int)$link['created_by'],company_client_invite_secret());
}

function integration_link_invite_valid(int $linkId,string $token): bool {
    $expected=integration_link_invite_token($linkId);
    return $expected!==''&&$token!==''&&hash_equals($expected,$token);
}

function integration_link_invite_url(int $linkId): string {
    return url('integration_join.php?'.http_build_query(['link_id'=>$linkId,'token'=>integration_link_invite_token($linkId)]));
}

function store_pending_integration_invite(int $linkId,string $token): bool {
    if(!integration_link_invite_valid($linkId,$token))return false;
    $q=db()->prepare("SELECT id,title,url,description,link_kind,status,created_by FROM external_module_links WHERE id=? AND status='active' LIMIT 1");$q->execute([$linkId]);$link=$q->fetch();if(!$link)return false;
    $_SESSION['integration_join_invite']=['link_id'=>$linkId,'token'=>$token];return true;
}

function pending_integration_invite(): ?array {
    $invite=$_SESSION['integration_join_invite']??null;if(!is_array($invite))return null;
    $linkId=(int)($invite['link_id']??0);$token=(string)($invite['token']??'');
    if(!integration_link_invite_valid($linkId,$token)){unset($_SESSION['integration_join_invite']);return null;}
    $q=db()->prepare("SELECT l.*,u.name owner_name,u.public_id owner_public_id FROM external_module_links l LEFT JOIN users u ON u.id=l.created_by WHERE l.id=? AND l.status='active' LIMIT 1");$q->execute([$linkId]);$link=$q->fetch();
    if(!$link){unset($_SESSION['integration_join_invite']);return null;}
    return ['link_id'=>$linkId,'token'=>$token,'link'=>$link];
}

function clear_pending_integration_invite(): void { unset($_SESSION['integration_join_invite']); }

function grant_integration_access_from_invite(int $userId,int $linkId): array {
    if($userId<1||$linkId<1)throw new RuntimeException('Convite de integração inválido.');
    $uq=db()->prepare("SELECT id,name,status FROM users WHERE id=? LIMIT 1");$uq->execute([$userId]);$recipient=$uq->fetch();
    if(!$recipient||$recipient['status']==='blocked')throw new RuntimeException('Esta conta não pode receber a integração enquanto estiver bloqueada.');
    $lq=db()->prepare("SELECT * FROM external_module_links WHERE id=? AND status='active' LIMIT 1");$lq->execute([$linkId]);$link=$lq->fetch();
    if(!$link)throw new RuntimeException('Este sistema não está mais disponível.');
    $grantor=(int)($link['created_by']??0);
    if($grantor<1){$aq=db()->query("SELECT id FROM users WHERE role='super_admin' AND status='active' ORDER BY id LIMIT 1");$grantor=(int)$aq->fetchColumn();}
    if($grantor<1)throw new RuntimeException('Não foi possível identificar o responsável por esta integração.');
    if($grantor!==$userId){
        $gq=db()->prepare('SELECT company_id FROM users WHERE id=?');$gq->execute([$grantor]);$companyId=(int)$gq->fetchColumn();
        db()->prepare("INSERT INTO external_module_client_access(module_link_id,granted_by_user_id,company_id,client_user_id,display_name,status,created_at,updated_at) VALUES(?,?,?,?,?,'active',?,?) ON CONFLICT(module_link_id,granted_by_user_id,client_user_id) DO UPDATE SET company_id=excluded.company_id,display_name=excluded.display_name,status='active',updated_at=excluded.updated_at")
          ->execute([$linkId,$grantor,$companyId?:null,$userId,$recipient['name'],now(),now()]);
        create_notification($userId,'Integração liberada','Seu acesso a '.$link['title'].' foi liberado automaticamente pelo link/QR Code.','success','dashboard.php?page=new_area','external_module_link',$linkId);
    }
    return $link;
}

function track_admin_presence(?array $u=null): void {
    $u=$u?:current_user();
    if(!$u||!in_array(($u['role']??''),['super_admin','sub_admin'],true))return;
    $uid=(int)$u['id'];$date=today();$now=now();
    try{
        $q=db()->prepare('SELECT * FROM admin_daily_presence WHERE user_id=? AND work_date=?');$q->execute([$uid,$date]);$row=$q->fetch();
        if(!$row){db()->prepare('INSERT INTO admin_daily_presence(user_id,work_date,first_seen,last_seen,last_ping,active_seconds) VALUES(?,?,?,?,?,0)')->execute([$uid,$date,$now,$now,$now]);return;}
        $gap=max(0,strtotime($now)-strtotime((string)$row['last_ping']));
        $add=$gap<=900?$gap:0; // conta apenas intervalos de até 15 min como tempo ativo
        db()->prepare('UPDATE admin_daily_presence SET last_seen=?,last_ping=?,active_seconds=active_seconds+? WHERE id=?')->execute([$now,$now,$add,(int)$row['id']]);
    }catch(Throwable $e){}
}

function service_settlement_if_ready(int $id): bool {
    $q=db()->prepare('SELECT sr.*,s.name service_name FROM service_requests sr JOIN services s ON s.id=sr.service_id WHERE sr.id=?');$q->execute([$id]);$req=$q->fetch();
    if(!$req)return false;
    if(empty($req['provider_payment_confirmed'])||empty($req['client_completion_confirmed'])||empty($req['payment_proof']))return false;
    $amount=(float)$req['amount'];if($amount<=0)return false;
    // Autocorreção para serviços antigos já marcados como pagos/liquidados,
    // mas cujo saldo não foi refletido na carteira por falha entre requisições.
    if(!empty($req['settled_at'])||($req['payment_status']??'')==='paid'){
        $breakdown=calculate_platform_fee((int)$req['provider_id'],$amount,'service',$id);
        $net=(float)($breakdown['recipient_net']??$breakdown['base']??$amount);
        wallet_credit_sale((int)$req['provider_id'],$net,'service',$id,$req['service_name'].' • pagamento confirmado');
        return true;
    }
    $breakdown=calculate_platform_fee((int)$req['provider_id'],$amount,'service',$id);
    $feePayer=fee_payer_preference_for_user((function()use($req){$q=db()->prepare('SELECT * FROM users WHERE id=?');$q->execute([(int)$req['provider_id']]);return $q->fetch()?:[];})());
    $paidTotal=(float)($breakdown['client_total']??$breakdown['total']);
    $providerQ=db()->prepare('SELECT * FROM users WHERE id=?');$providerQ->execute([(int)$req['provider_id']]);$provider=$providerQ->fetch()?:[];
    $destination=service_payment_destination($provider);
    $channel=(string)($req['payment_channel']??'');
    $method=str_contains($channel,'link')?'link':(str_contains($channel,'pix')?'pix':(string)($destination['method']??'pix'));
    if(!in_array($method,['pix','link'],true))$method='pix';
    $receipt=generate_transaction_receipt_png('service',$id,(int)$req['client_id'],(int)$req['provider_id'],$paidTotal,$req['service_name'].($breakdown['fee']>0?' • taxa '.money($breakdown['fee']):''),$method,(string)($req['payment_proof']??''));
    post_finance_pair('service',$id,(int)$req['client_id'],(int)$req['provider_id'],$amount,$method,(string)($req['payment_proof']??''),$receipt,$req['service_name']);
    db()->prepare("UPDATE service_requests SET payment_status='paid',receipt_path=?,paid_at=?,payment_confirmed_by=?,settled_at=?,updated_at=? WHERE id=?")->execute([$receipt,(string)($req['paid_at']?:now()),(int)($req['payment_confirmed_by']?:$req['provider_id']),now(),now(),$id]);
    create_notification((int)$req['client_id'],'Pagamento concluído','As duas partes confirmaram o serviço. O pagamento de '.$req['service_name'].' foi liquidado.','success','dashboard.php?page=services','service_request',$id);
    create_notification((int)$req['provider_id'],'Valor liberado na carteira','As duas partes confirmaram o serviço. O valor líquido foi lançado na sua carteira.','success','dashboard.php?page=wallet','service_request',$id);
    return true;
}


// ===== LinkExpress 2.8.61 — nome público, mensalidade condicional, gás e moderação =====
function normalize_public_username($value): string {
    $value=trim((string)$value);
    $value=ltrim($value,'@');
    $value=strtolower($value);
    $value=preg_replace('/[^a-z0-9._]/','',$value);
    return (string)$value;
}

function validate_public_username($value,int $userId=0): string {
    $username=normalize_public_username($value);
    if($username==='') return '';
    if(strlen($username)<3 || strlen($username)>24) throw new RuntimeException('O nome de usuário deve ter entre 3 e 24 caracteres.');
    if(!preg_match('/^[a-z0-9][a-z0-9._]*[a-z0-9]$/',$username) && strlen($username)>1) throw new RuntimeException('Use letras, números, ponto ou underline, sem ponto/underline no início ou fim.');
    if(strpos($username,'..')!==false || strpos($username,'__')!==false) throw new RuntimeException('Evite ponto ou underline repetido no nome de usuário.');
    $q=db()->prepare('SELECT id FROM users WHERE lower(username)=lower(?) AND id<>? LIMIT 1');
    $q->execute([$username,$userId]);
    if($q->fetchColumn()) throw new RuntimeException('Este nome de usuário já está em uso.');
    return $username;
}

function user_public_name($user): string {
    if(!$user) return 'Usuário LinkExpress';
    // O nome real é a identificação principal. @usuário e ID ficam apenas como
    // informação auxiliar, evitando que o ID da plataforma apareça no lugar do nome.
    $name=trim((string)($user['name']??''));
    if($name!=='') return $name;
    $username=normalize_public_username($user['username']??'');
    if($username!=='') return '@'.$username;
    $pid=trim((string)($user['public_id']??''));
    if($pid!=='') return $pid;
    return 'Usuário LinkExpress';
}

function user_has_monthly_billing_or_plan($user=null): bool {
    $user=$user?:current_user();
    if(!$user) return false;
    $uid=(int)($user['id']??0);if($uid<1) return false;
    try{
        $q=db()->prepare("SELECT
          (SELECT COUNT(*) FROM subscriptions WHERE user_id=? AND status<>'cancelled') +
          (SELECT COUNT(*) FROM charges WHERE user_id=? AND (subscription_id IS NOT NULL OR lower(COALESCE(description,'')) LIKE '%mensalidade%' OR lower(COALESCE(description,'')) LIKE '%renova%')) +
          (SELECT COUNT(*) FROM plan_requests WHERE user_id=? AND status='approved')
        ");
        $q->execute([$uid,$uid,$uid]);
        return (int)$q->fetchColumn()>0;
    }catch(Throwable $e){ return false; }
}

function user_should_see_subscriptions($user=null): bool {
    $user=$user?:current_user();
    if(!$user) return false;
    if(in_array((string)($user['role']??''),['super_admin','sub_admin'],true)) return true;
    return user_has_monthly_billing_or_plan($user);
}

function commit_order_stock(int $orderId): bool {
    if($orderId<1) return false;
    $db=db();
    $q=$db->prepare('SELECT id,company_id,order_number,COALESCE(stock_committed,1) stock_committed FROM orders WHERE id=? LIMIT 1');
    $q->execute([$orderId]);$order=$q->fetch();
    if(!$order) throw new RuntimeException('Pedido não encontrado.');
    if((int)$order['stock_committed']===1) return false;
    $iq=$db->prepare('SELECT oi.product_id,oi.name,oi.qty,p.stock,p.low_stock_threshold,p.low_stock_notified,p.name product_name FROM order_items oi LEFT JOIN products p ON p.id=oi.product_id WHERE oi.order_id=?');
    $iq->execute([$orderId]);$items=$iq->fetchAll();
    if(!$items) throw new RuntimeException('Pedido sem itens para movimentar o estoque.');
    foreach($items as $item){
        if(empty($item['product_id'])) continue;
        if($item['stock']===null) throw new RuntimeException('O produto '.$item['name'].' não existe mais no catálogo.');
        if((float)$item['stock']<(float)$item['qty']) throw new RuntimeException('Estoque insuficiente para '.$item['name'].'. Disponível: '.number_format((float)$item['stock'],0,',','.').'.');
    }
    $ownTxn=!$db->inTransaction();if($ownTxn)$db->beginTransaction();
    try{
        foreach($items as $item){
            if(empty($item['product_id'])) continue;
            $upd=$db->prepare('UPDATE products SET stock=stock-?,updated_at=? WHERE id=? AND stock>=?');
            $upd->execute([(float)$item['qty'],now(),(int)$item['product_id'],(float)$item['qty']]);
            if($upd->rowCount()<1) throw new RuntimeException('O estoque de '.$item['name'].' mudou. Atualize a página e tente novamente.');
        }
        $db->prepare('UPDATE orders SET stock_committed=1,updated_at=? WHERE id=?')->execute([now(),$orderId]);
        if($ownTxn)$db->commit();
    }catch(Throwable $e){if($ownTxn&&$db->inTransaction())$db->rollBack();throw $e;}
    try{
        $ownerQ=$db->prepare('SELECT owner_user_id FROM companies WHERE id=?');$ownerQ->execute([(int)$order['company_id']]);$ownerId=(int)$ownerQ->fetchColumn();
        foreach($items as $item){
            if(empty($item['product_id'])) continue;
            $sq=$db->prepare('SELECT stock,low_stock_threshold,low_stock_notified,name FROM products WHERE id=?');$sq->execute([(int)$item['product_id']]);$sr=$sq->fetch();
            if($sr&&(float)$sr['stock']<=(float)$sr['low_stock_threshold']&&!(int)$sr['low_stock_notified']){
                $db->prepare('UPDATE products SET low_stock_notified=1 WHERE id=?')->execute([(int)$item['product_id']]);
                if($ownerId)create_notification($ownerId,'Estoque baixo',$sr['name'].' está com '.$sr['stock'].' unidade(s).','warning');
            }
        }
    }catch(Throwable $ignored){}
    return true;
}

function release_order_stock(int $orderId): bool {
    if($orderId<1) return false;
    $db=db();$q=$db->prepare('SELECT COALESCE(stock_committed,1) stock_committed FROM orders WHERE id=? LIMIT 1');$q->execute([$orderId]);$committed=$q->fetchColumn();
    if($committed===false||(int)$committed!==1) return false;
    $iq=$db->prepare('SELECT product_id,qty FROM order_items WHERE order_id=?');$iq->execute([$orderId]);$items=$iq->fetchAll();
    $ownTxn=!$db->inTransaction();if($ownTxn)$db->beginTransaction();
    try{
        foreach($items as $item){if(empty($item['product_id']))continue;$db->prepare('UPDATE products SET stock=stock+?,low_stock_notified=CASE WHEN stock+?>low_stock_threshold THEN 0 ELSE low_stock_notified END,updated_at=? WHERE id=?')->execute([(float)$item['qty'],(float)$item['qty'],now(),(int)$item['product_id']]);}
        $db->prepare('UPDATE orders SET stock_committed=0,updated_at=? WHERE id=?')->execute([now(),$orderId]);
        if($ownTxn)$db->commit();
    }catch(Throwable $e){if($ownTxn&&$db->inTransaction())$db->rollBack();throw $e;}
    return true;
}

function ensure_order_delivery_job(int $orderId): int {
    if($orderId<1) return 0;
    $db=db();
    $q=$db->prepare("SELECT o.*,u.name client_name,u.phone client_phone,u.public_id client_public_id,u.home_lat,u.home_lng
        FROM orders o
        LEFT JOIN users u ON u.id=o.client_id
        WHERE o.id=? LIMIT 1");
    $q->execute([$orderId]);$order=$q->fetch();
    if(!$order||($order['fulfillment_type']??'delivery')==='pickup'||empty($order['client_id'])) return 0;
    if(in_array((string)($order['status']??''),['cancelled'],true)) return 0;

    $existing=$db->prepare('SELECT id FROM delivery_jobs WHERE order_id=? LIMIT 1');
    $existing->execute([$orderId]);
    $jobId=(int)$existing->fetchColumn();
    if($jobId>0)return $jobId;

    $iq=$db->prepare('SELECT name,qty FROM order_items WHERE order_id=? ORDER BY id');
    $iq->execute([$orderId]);$parts=[];
    foreach($iq->fetchAll() as $item){
        $qty=(float)$item['qty'];
        $parts[]=$item['name'].' x '.(floor($qty)==$qty?(int)$qty:rtrim(rtrim(number_format($qty,2,'.',''),'0'),'.'));
    }
    $description='Pedido '.$order['order_number'].($parts?' • '.implode(' • ',$parts):'');
    $distance=0.0;
    $cq=$db->prepare('SELECT latitude,longitude FROM companies WHERE id=? LIMIT 1');
    $cq->execute([(int)$order['company_id']]);$company=$cq->fetch();
    if($company&&$company['latitude']!==null&&$company['longitude']!==null&&$order['home_lat']!==null&&$order['home_lng']!==null){
        $straight=distance_km($company['latitude'],$company['longitude'],$order['home_lat'],$order['home_lng']);
        if($straight!==null)$distance=round($straight*1.25,2);
    }
    $isGas=company_is_gas((int)$order['company_id']);
    $note=$isGas
        ? 'Gerada automaticamente após a confirmação da venda da distribuidora.'
        : 'Gerada automaticamente após a confirmação do pagamento do pedido.';
    $db->prepare('INSERT INTO delivery_jobs(order_id,company_id,client_id,client_public_id,client_name,client_phone,address,latitude,longitude,status,authorization_status,notes,package_description,sack_qty,box_qty,scheduled_at,distance_km,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
      ->execute([$orderId,(int)$order['company_id'],(int)$order['client_id'],(string)($order['client_public_id']??''),(string)($order['client_name']??''),(string)($order['client_phone']??''),(string)($order['address']??''),$order['home_lat']??null,$order['home_lng']??null,'ready','approved',$note,$description,0,0,null,$distance,now(),now()]);
    $jobId=(int)$db->lastInsertId();
    if(!empty($order['client_id'])){
        create_notification((int)$order['client_id'],'Pedido liberado para entrega','O pedido '.$order['order_number'].' entrou na fila de entrega.','success','dashboard.php?page=orders','order',$orderId);
    }
    return $jobId;
}

function ensure_gas_order_delivery_job(int $orderId): int {
    if($orderId<1) return 0;
    $q=db()->prepare('SELECT company_id FROM orders WHERE id=? LIMIT 1');
    $q->execute([$orderId]);$companyId=(int)$q->fetchColumn();
    if($companyId<1||!company_is_gas($companyId))return 0;
    return ensure_order_delivery_job($orderId);
}

function company_is_gas(int $companyId): bool {
    if($companyId<1) return false;
    try{
        $q=db()->prepare("SELECT c.type,COALESCE(c.sector,'') sector,COALESCE(s.name,'') segment_name,
          COALESCE(ou.segment_slug,'') owner_segment
          FROM companies c
          LEFT JOIN segments s ON s.slug=c.type
          LEFT JOIN users ou ON ou.id=c.owner_user_id
          WHERE c.id=? LIMIT 1");
        $q->execute([$companyId]);$row=$q->fetch();
        if(!$row) return false;
        $probe=strtolower(trim((string)($row['type']??'').' '.(string)($row['sector']??'').' '.(string)($row['segment_name']??'').' '.(string)($row['owner_segment']??'')));
        if(function_exists('iconv')){$ascii=@iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$probe);if($ascii!==false)$probe=strtolower($ascii);}
        $probe=str_replace(['-','_'],' ',$probe);
        // Evita falso positivo em palavras como "gastro". Gás precisa ser um termo/segmento próprio
        // ou referência explícita a botijão/distribuidora de gás.
        $type=strtolower(trim((string)($row['type']??'')));
        if(function_exists('iconv')){$asciiType=@iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$type);if($asciiType!==false)$type=strtolower($asciiType);}
        return $type==='gas' || preg_match('/(^|[^a-z0-9])gas([^a-z0-9]|$)/',$probe)===1 || strpos($probe,'botij')!==false;
    }catch(Throwable $e){ return false; }
}

function gas_purchase_history(int $companyId,int $clientId): array {
    if($companyId<1||$clientId<1) return [];
    try{
        $q=db()->prepare("SELECT o.id,o.order_number,date(COALESCE(o.updated_at,o.created_at)) purchase_date,
          COALESCE(SUM(oi.qty),0) cylinder_qty,COALESCE(SUM(oi.total),0) gas_amount,
          o.total,o.status,o.created_at,o.updated_at
          FROM orders o
          JOIN order_items oi ON oi.order_id=o.id
          JOIN products p ON p.id=oi.product_id
          WHERE o.company_id=? AND o.client_id=? AND o.status='delivered' AND COALESCE(p.is_gas_cylinder,0)=1
          GROUP BY o.id
          ORDER BY purchase_date DESC,o.id DESC");
        $q->execute([$companyId,$clientId]);
        return $q->fetchAll();
    }catch(Throwable $e){ return []; }
}

function gas_consumption_profile(int $companyId,int $clientId): array {
    $history=gas_purchase_history($companyId,$clientId);
    if(!$history) return ['company_id'=>$companyId,'client_id'=>$clientId,'purchases'=>0,'last_date'=>'','last_purchase'=>'','days_since'=>0,'average_days'=>30,'avg_days'=>30,'predicted_date'=>'','days_remaining'=>null,'status'=>'new','history'=>[]];
    $dates=array_values(array_unique(array_filter(array_map(static function($r){return (string)($r['purchase_date']??'');},$history))));
    usort($dates,static function($a,$b){return strcmp($a,$b);});
    $intervals=[];
    for($i=1;$i<count($dates);$i++){
        $a=strtotime($dates[$i-1]);$b=strtotime($dates[$i]);
        if($a&&$b&&$b>$a){$d=(int)round(($b-$a)/86400);if($d>0&&$d<=180)$intervals[]=$d;}
    }
    if($intervals){
        $recent=array_slice($intervals,-5);
        $avg=max(1,(int)round(array_sum($recent)/count($recent)));
    } else $avg=30;
    $last=end($dates);
    $predicted=date('Y-m-d',strtotime($last.' +'.$avg.' days'));
    $todayTs=strtotime(today());$lastTs=strtotime($last);$predTs=strtotime($predicted);
    $daysSince=max(0,(int)floor(($todayTs-$lastTs)/86400));
    $remaining=(int)ceil(($predTs-$todayTs)/86400);
    $status=$remaining<0?'late':($remaining<=5?'near':'normal');
    return ['company_id'=>$companyId,'client_id'=>$clientId,'purchases'=>count($dates),'last_date'=>$last,'last_purchase'=>$last,'days_since'=>$daysSince,'average_days'=>$avg,'avg_days'=>$avg,'predicted_date'=>$predicted,'days_remaining'=>$remaining,'status'=>$status,'history'=>$history];
}

function gas_status_label(string $status): string {
    if($status==='late') return '🔴 Provavelmente já acabou';
    if($status==='near') return '🟡 Próximo de acabar';
    if($status==='normal') return '🟢 Consumo normal';
    return '⚪ Aguardando histórico';
}

function process_gas_consumption_notifications(): int {
    try{
        $pairs=db()->query("SELECT DISTINCT o.company_id,o.client_id
          FROM orders o
          JOIN order_items oi ON oi.order_id=o.id
          JOIN products p ON p.id=oi.product_id
          WHERE o.status='delivered' AND o.client_id IS NOT NULL AND COALESCE(p.is_gas_cylinder,0)=1")->fetchAll();
        $count=0;
        foreach($pairs as $pair){
            $profile=gas_consumption_profile((int)$pair['company_id'],(int)$pair['client_id']);
            if(empty($profile['predicted_date'])) continue;
            $remaining=$profile['days_remaining'];
            if($remaining===null || $remaining>3 || $remaining<-7) continue;
            $stmt=db()->prepare('INSERT OR IGNORE INTO gas_consumption_alerts(company_id,client_id,predicted_date,notified_at) VALUES(?,?,?,?)');
            $stmt->execute([(int)$pair['company_id'],(int)$pair['client_id'],$profile['predicted_date'],now()]);
            if($stmt->rowCount()>0){
                $cq=db()->prepare('SELECT name FROM companies WHERE id=?');$cq->execute([(int)$pair['company_id']]);$companyName=(string)($cq->fetchColumn()?:'sua distribuidora');
                create_notification((int)$pair['client_id'],'Seu gás está próximo da troca','Pelo seu histórico, o gás comprado em '.$companyName.' está próximo da data prevista para acabar. Toque para ver o painel Meu Gás.','warning','dashboard.php?page=my_gas','gas_consumption',(int)$pair['company_id']);
                $count++;
            }
        }
        return $count;
    }catch(Throwable $e){ return 0; }
}


function company_edit_permission_request(int $companyId,int $targetUserId,string $targetType): ?array {
    if($companyId<1||$targetUserId<1||!in_array($targetType,['client','employee'],true))return null;
    try{$q=db()->prepare("SELECT * FROM company_edit_permission_requests WHERE company_id=? AND target_user_id=? AND target_type=? ORDER BY id DESC LIMIT 1");$q->execute([$companyId,$targetUserId,$targetType]);return $q->fetch()?:null;}catch(Throwable $e){return null;}
}
function company_can_edit_linked_user(int $companyId,int $targetUserId,string $targetType): bool {
    if(is_admin())return true;
    $u=current_user();if(!$u||!in_array((string)$u['role'],['company','employee'],true)||resolve_company_id_for_user($u)!==$companyId)return false;
    $r=company_edit_permission_request($companyId,$targetUserId,$targetType);if(!$r||($r['status']??'')!=='approved'||!empty($r['consumed_at']))return false;
    if(!empty($r['expires_at'])&&strtotime((string)$r['expires_at'])<time())return false;
    return true;
}
function consume_company_edit_permission(int $companyId,int $targetUserId,string $targetType): void {
    if(is_admin())return;
    $q=db()->prepare("UPDATE company_edit_permission_requests SET consumed_at=?,updated_at=? WHERE id=(SELECT id FROM company_edit_permission_requests WHERE company_id=? AND target_user_id=? AND target_type=? AND status='approved' AND consumed_at IS NULL ORDER BY id DESC LIMIT 1)");
    $q->execute([now(),now(),$companyId,$targetUserId,$targetType]);
}
function company_client_count(int $companyId): int { return (int)(company_capacity_info($companyId,'clients')['used']??0); }

function company_member_access_level(?array $user=null): string {
    $user=$user?:current_user();if(!$user)return 'staff';
    if(in_array((string)($user['role']??''),['super_admin','sub_admin','company'],true))return 'admin';
    $level=(string)($user['company_access_level']??'staff');return in_array($level,['admin','manager','staff'],true)?$level:'staff';
}
function company_member_can_manage_team(?array $user=null): bool { return company_member_access_level($user)==='admin'; }
function company_member_can_manage_clients(?array $user=null): bool { return in_array(company_member_access_level($user),['admin','manager'],true); }
function company_access_level_label(string $level): string { return ['admin'=>'Administrador da empresa','manager'=>'Gerente','staff'=>'Funcionário'][$level]??'Funcionário'; }

function company_is_petcare(int $companyId): bool {
    if($companyId<1)return false;
    try{$q=db()->prepare("SELECT c.type,COALESCE(c.sector,'') sector,COALESCE(s.name,'') segment_name FROM companies c LEFT JOIN segments s ON s.slug=c.type WHERE c.id=?");$q->execute([$companyId]);$r=$q->fetch();if(!$r)return false;$probe=strtolower(trim((string)$r['type'].' '.(string)$r['sector'].' '.(string)$r['segment_name']));if(function_exists('iconv')){$a=@iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$probe);if($a!==false)$probe=strtolower($a);}return strpos($probe,'pet')!==false||strpos($probe,'veter')!==false;}catch(Throwable $e){return false;}
}
function pet_client_for_user(int $userId,int $companyId=0): ?array {
    try{$sql="SELECT pc.*,c.name company_name,c.logo company_logo FROM pet_clients pc JOIN companies c ON c.id=pc.company_id WHERE pc.platform_user_id=? AND pc.status='active'";$params=[$userId];if($companyId>0){$sql.=' AND pc.company_id=?';$params[]=$companyId;}$sql.=' ORDER BY pc.id LIMIT 1';$q=db()->prepare($sql);$q->execute($params);return $q->fetch()?:null;}catch(Throwable $e){return null;}
}
function pet_clients_for_user(int $userId): array { try{$q=db()->prepare("SELECT pc.*,c.name company_name,c.logo company_logo FROM pet_clients pc JOIN companies c ON c.id=pc.company_id WHERE pc.platform_user_id=? AND pc.status='active' ORDER BY c.name");$q->execute([$userId]);return $q->fetchAll();}catch(Throwable $e){return [];} }
function pet_booking_availability_rows(int $companyId,string $date): array {
    if($companyId<1||!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date))return [];
    try{
        $q=db()->prepare("SELECT s.*,
            (SELECT COUNT(*) FROM pet_appointments a WHERE a.slot_id=s.id AND a.status<>'cancelled') booked
            FROM pet_booking_slots s
            WHERE s.company_id=? AND s.slot_date=? AND s.status='active'
            ORDER BY s.slot_time,s.id");
        $q->execute([$companyId,$date]);$rows=$q->fetchAll();
        foreach($rows as &$row){$row['booked']=(int)($row['booked']??0);$row['remaining']=max(0,(int)$row['capacity']-$row['booked']);if($date===date('Y-m-d')&&strtotime($date.' '.substr((string)$row['slot_time'],0,5))<=time())$row['remaining']=0;}
        unset($row);return $rows;
    }catch(Throwable $e){return [];}
}
function pet_booking_open_count(int $companyId,string $date): int {
    $count=0;foreach(pet_booking_availability_rows($companyId,$date) as $row)if((int)$row['remaining']>0)$count+=(int)$row['remaining'];return $count;
}
function pet_service_label(string $type): string {
    return [
        'consultation'=>'Consulta veterinária','return'=>'Retorno','vaccine'=>'Vacinação','bath'=>'Banho',
        'grooming'=>'Tosa','hygienic_grooming'=>'Tosa higiênica','boarding'=>'Hospedagem','exam'=>'Exame',
        'surgery'=>'Cirurgia','other'=>'Outro'
    ][$type]??status_label($type);
}
function pet_public_url(string $token): string { return url('pet_card.php?token='.rawurlencode($token)); }
function create_pet_token(): string { for($i=0;$i<20;$i++){try{$t=strtoupper(bin2hex(random_bytes(8)));$q=db()->prepare('SELECT COUNT(*) FROM pets WHERE public_token=?');$q->execute([$t]);if(!(int)$q->fetchColumn())return $t;}catch(Throwable $e){}}return strtoupper(uniqid('PET')); }

function career_profile(int $userId): array {
    $default=['user_id'=>$userId,'birth_date'=>'','city'=>'','state'=>'','address'=>'','linkedin'=>'','portfolio'=>'','desired_job_type'=>'','desired_work_mode'=>'','availability'=>'','salary_expectation'=>0,'visibility'=>'registered','allow_company_contact'=>1,'updated_at'=>''];
    try{$q=db()->prepare('SELECT * FROM career_profiles WHERE user_id=?');$q->execute([$userId]);$r=$q->fetch();return $r?array_merge($default,$r):$default;}catch(Throwable $e){return $default;}
}
function career_subscription(int $userId): array {
    $fee=max(0,localized_decimal(setting('career_monthly_fee','0')));$default=['user_id'=>$userId,'amount'=>$fee,'due_date'=>null,'status'=>$fee>0?'pending':'active','proof_path'=>'','reviewed_by'=>null,'reviewed_at'=>null,'notes'=>'','updated_at'=>''];
    try{
        $q=db()->prepare('SELECT * FROM career_subscriptions WHERE user_id=?');$q->execute([$userId]);$r=$q->fetch();
        if($r){
            if($fee<=0 && ((float)$r['amount']>0 || (string)$r['status']!=='active')){db()->prepare("UPDATE career_subscriptions SET amount=0,status='active',due_date=NULL,updated_at=? WHERE user_id=?")->execute([now(),$userId]);$r['amount']=0;$r['status']='active';$r['due_date']=null;}
            elseif($fee>0 && (float)$r['amount']<=0){db()->prepare("UPDATE career_subscriptions SET amount=?,status='pending',due_date=?,proof_path=NULL,updated_at=? WHERE user_id=?")->execute([$fee,date('Y-m-d'),now(),$userId]);$r['amount']=$fee;$r['status']='pending';$r['due_date']=date('Y-m-d');$r['proof_path']='';}
            return array_merge($default,$r);
        }
        if($fee>0){$due=date('Y-m-d');db()->prepare("INSERT OR IGNORE INTO career_subscriptions(user_id,amount,due_date,status,updated_at) VALUES(?,?,?,'pending',?)")->execute([$userId,$fee,$due,now()]);$default['due_date']=$due;}
        return $default;
    }catch(Throwable $e){return $default;}
}
function career_access_allowed(int $userId): bool { if(is_admin())return true;$s=career_subscription($userId);if((float)($s['amount']??0)<=0)return true;$status=(string)($s['status']??'pending');$due=(string)($s['due_date']??'');if($status==='active'&&$due!==''&&$due<date('Y-m-d')){try{db()->prepare("UPDATE career_subscriptions SET status='overdue',updated_at=? WHERE user_id=?")->execute([now(),$userId]);}catch(Throwable $e){}return false;}return $status==='active'; }
function career_subscription_status_label(string $status): string { return ['active'=>'Ativa','pending'=>'Pagamento pendente','proof_sent'=>'Comprovante em análise','rejected'=>'Comprovante recusado','overdue'=>'Vencida'][$status]??status_label($status); }

function process_clinic_medication_reminders(): int {
    try{
        $nowTs=time();$today=date('Y-m-d',$nowTs);$hm=date('H:i',$nowTs);$count=0;
        $q=db()->query("SELECT i.*,p.patient_id,p.company_id,p.release_to_patient,p.status prescription_status,cp.platform_user_id,cp.name patient_name FROM clinic_prescription_items i JOIN clinic_prescriptions p ON p.id=i.prescription_id JOIN clinic_patients cp ON cp.id=p.patient_id WHERE p.status='active' AND p.release_to_patient=1 AND cp.platform_user_id IS NOT NULL AND COALESCE(i.times_csv,'')<>'' AND (i.start_date IS NULL OR i.start_date='' OR i.start_date<=date('now')) AND (i.end_date IS NULL OR i.end_date='' OR i.end_date>=date('now'))");
        foreach($q->fetchAll() as $row){foreach(array_filter(array_map('trim',explode(',',(string)$row['times_csv']))) as $time){$time=substr($time,0,5);if(!preg_match('/^\\d{2}:\\d{2}$/',$time))continue;$target=strtotime($today.' '.$time);if(abs($nowTs-$target)>600)continue;$key=$today.' '.$time;if(($row['last_reminder_key']??'')===$key)continue;create_notification((int)$row['platform_user_id'],'💊 Hora do medicamento',(string)$row['medicine_name'].(($row['dosage']??'')!==''?' — '.$row['dosage']:''),'info','dashboard.php?page=clinic_patient','medication',(int)$row['id']);db()->prepare('UPDATE clinic_prescription_items SET last_reminder_key=?,updated_at=? WHERE id=?')->execute([$key,now(),(int)$row['id']]);$count++;}}
        return $count;
    }catch(Throwable $e){return 0;}
}

function moderation_deletion_approval_state(string $targetType,int $targetId,int $adminUserId=0): array {
    $allowed=['user_report','ticket'];
    if(!in_array($targetType,$allowed,true)||$targetId<1) return ['count'=>0,'mine'=>false,'admins'=>[]];
    try{
        $q=db()->prepare("SELECT a.admin_user_id,u.name FROM moderation_deletion_approvals a JOIN users u ON u.id=a.admin_user_id WHERE a.target_type=? AND a.target_id=? AND u.role='sub_admin' ORDER BY a.created_at");
        $q->execute([$targetType,$targetId]);$rows=$q->fetchAll();
        $ids=array_map('intval',array_column($rows,'admin_user_id'));
        return ['count'=>count(array_unique($ids)),'mine'=>$adminUserId>0&&in_array($adminUserId,$ids,true),'admins'=>$rows];
    }catch(Throwable $e){ return ['count'=>0,'mine'=>false,'admins'=>[]]; }
}

/** Gera o comprovante de consulta/agendamento em PNG no mesmo padrão visual dos comprovantes financeiros. */
function generate_clinic_appointment_receipt_png(int $appointmentId): string {
    if($appointmentId<1 || !function_exists('imagecreatetruecolor')) return '';
    $q=db()->prepare("SELECT a.*,c.name clinic_name,c.document clinic_document,c.logo clinic_logo,c.owner_user_id,pa.name patient_name,pa.platform_user_id,pr.name professional_name,s.name specialty_name FROM clinic_appointments a JOIN companies c ON c.id=a.company_id JOIN clinic_patients pa ON pa.id=a.patient_id LEFT JOIN clinic_professionals pr ON pr.id=a.professional_id LEFT JOIN clinic_specialties s ON s.id=a.specialty_id WHERE a.id=? LIMIT 1");
    $q->execute([$appointmentId]);$a=$q->fetch();if(!$a)return '';
    $dir=dirname(__DIR__).'/uploads/comprovantes-gerados';if(!is_dir($dir)&&!mkdir($dir,0755,true)&&!is_dir($dir))return '';
    $safeStatus=preg_replace('/[^a-z0-9_-]/i','-',(string)($a['status']??'scheduled'));
    $filename='agendamento-clinica-'.$appointmentId.'-'.$safeStatus.'.png';$absolute=$dir.'/'.$filename;
    [$img,$colors]=receipt_create_canvas();$font=receipt_font_path();
    $paid=in_array((string)($a['payment_status']??''),['paid','confirmed','approved'],true);
    $title=$paid?'COMPROVANTE DE PAGAMENTO':'COMPROVANTE DE AGENDAMENTO';
    $badgeStatus=status_label((string)($a['status']??'scheduled'));$badge=$paid?'✓ PAGAMENTO CONFIRMADO':('✓ '.(function_exists('mb_strtoupper')?mb_strtoupper($badgeStatus,'UTF-8'):strtoupper($badgeStatus)));
    $code=strtoupper(substr(hash('sha256','clinic|'.$appointmentId.'|'.($a['created_at']??'').'|'.($a['company_id']??0)),0,12));
    $createdAt=(string)($a['updated_at']?:$a['created_at']?:now());
    $when=date('d/m/Y',strtotime((string)$a['appointment_date'])).' às '.substr((string)$a['appointment_time'],0,5);
    $payment=status_label((string)($a['payment_method']?:'a definir')).' • '.status_label((string)($a['payment_status']?:'pending'));
    if($font&&function_exists('imagettftext')){
        $brandRaw=trim((string)setting('brand_logo',''));$brandDrawn=$brandRaw!==''?receipt_draw_logo($img,$brandRaw,86,82,110,74):false;if(!$brandDrawn)imagettftext($img,15,0,88,112,$colors['white'],$font,receipt_text_clean(platform_name()));
        if(!empty($a['clinic_logo']))receipt_draw_logo($img,(string)$a['clinic_logo'],858,76,120,96);
        imagettftext($img,34,0,104,162,$colors['white'],$font,$title);
        imagettftext($img,22,0,104,205,$colors['white'],$font,receipt_text_clean((string)$a['clinic_name']));
        if(!empty($a['clinic_document']))imagettftext($img,15,0,104,244,$colors['white'],$font,receipt_text_clean(masked_tax_document((string)$a['clinic_document'])));
        receipt_draw_round_rect($img,104,269,520,319,24,$colors['green_bg']);imagettftext($img,17,0,128,302,$colors['green'],$font,receipt_text_clean($badge));
        receipt_draw_box($img,$font,104,440,872,126,'Paciente',(string)$a['patient_name'],$colors,24);
        receipt_draw_box($img,$font,104,592,412,128,'Consulta / serviço',(string)($a['specialty_name']?:'Consulta'),$colors,21);
        receipt_draw_box($img,$font,540,592,436,128,'Valor',money((float)$a['amount']),$colors,27);
        receipt_draw_box($img,$font,104,746,412,128,'Data e horário',$when,$colors,20);
        receipt_draw_box($img,$font,540,746,436,128,'Pagamento',$payment,$colors,19);
        receipt_draw_box($img,$font,104,900,872,126,'Profissional responsável',(string)($a['professional_name']?:'A definir'),$colors,24);
        for($x=104;$x<976;$x+=25)imageline($img,$x,1084,min($x+12,976),1084,$colors['line']);
        imagettftext($img,15,0,104,1142,$colors['muted'],$font,'Código do comprovante');imagettftext($img,26,0,104,1188,$colors['text'],$font,$code);
        imagettftext($img,15,0,754,1188,$colors['muted'],$font,'Gerado em '.date('d/m/Y',strtotime($createdAt)));
        imagettftext($img,14,0,315,1262,$colors['muted'],$font,'Documento digital emitido pela plataforma '.receipt_text_clean(platform_name()));
    }else{
        // Fallback completo: mantém o mesmo layout mesmo quando a hospedagem não possui FreeType.
        $brandRaw=trim((string)setting('brand_logo',''));$brandDrawn=$brandRaw!==''?receipt_draw_logo($img,$brandRaw,86,82,110,74):false;if(!$brandDrawn)receipt_bitmap_draw_colored($img,platform_name(),88,92,2,$colors['white'],5);if(!empty($a['clinic_logo']))receipt_draw_logo($img,(string)$a['clinic_logo'],858,76,120,96);
        receipt_bitmap_draw_colored($img,$title,104,140,3,$colors['white'],5);receipt_bitmap_draw_colored($img,(string)$a['clinic_name'],104,205,2,$colors['white'],5);if(!empty($a['clinic_document']))receipt_bitmap_draw_colored($img,masked_tax_document((string)$a['clinic_document']),104,246,2,$colors['white'],4);
        receipt_draw_round_rect($img,104,269,520,319,24,$colors['green_bg']);receipt_bitmap_draw_colored($img,str_replace('✓ ','',$badge),128,283,2,$colors['green'],5);
        receipt_bitmap_box($img,104,440,872,126,'Paciente',(string)$a['patient_name'],$colors,3);receipt_bitmap_box($img,104,592,412,128,'Consulta / servico',(string)($a['specialty_name']?:'Consulta'),$colors,2);receipt_bitmap_box($img,540,592,436,128,'Valor',money((float)$a['amount']),$colors,3);
        receipt_bitmap_box($img,104,746,412,128,'Data e horario',$when,$colors,2);receipt_bitmap_box($img,540,746,436,128,'Pagamento',$payment,$colors,2);receipt_bitmap_box($img,104,900,872,126,'Profissional responsavel',(string)($a['professional_name']?:'A definir'),$colors,3);
        for($x=104;$x<976;$x+=25)imageline($img,$x,1084,min($x+12,976),1084,$colors['line']);receipt_bitmap_draw_colored($img,'Codigo do comprovante',104,1128,2,$colors['muted'],4);receipt_bitmap_draw_colored($img,$code,104,1164,3,$colors['text'],5);receipt_bitmap_draw_colored($img,'Gerado em '.date('d/m/Y',strtotime($createdAt)),730,1168,2,$colors['muted'],4);receipt_bitmap_draw_colored($img,'Documento digital emitido pela plataforma '.platform_name(),250,1250,2,$colors['muted'],4);
    }
    imagepng($img,$absolute,6);imagedestroy($img);try{db()->prepare('UPDATE clinic_appointments SET receipt_code=? WHERE id=?')->execute([$code,$appointmentId]);}catch(Throwable $ignored){}return 'uploads/comprovantes-gerados/'.$filename;
}

/** Lembretes automáticos do módulo Veterinária / Pet Shop. */
function process_petcare_reminders(): int {
    try{
        $count=0;
        // Agendamento em até 24h: consulta, retorno, vacina, banho/tosa, exame etc.
        $q=db()->query("SELECT a.id,a.appointment_date,a.appointment_time,a.service_name,a.service_type,p.name pet_name,pc.platform_user_id,c.name company_name FROM pet_appointments a JOIN pets p ON p.id=a.pet_id JOIN pet_clients pc ON pc.id=p.client_id JOIN companies c ON c.id=a.company_id WHERE a.status IN ('scheduled','confirmed') AND pc.platform_user_id IS NOT NULL AND a.reminder_sent_at IS NULL AND datetime(a.appointment_date||' '||a.appointment_time) BETWEEN datetime('now') AND datetime('now','+24 hours')");
        foreach($q->fetchAll() as $r){create_notification((int)$r['platform_user_id'],'🐾 Lembrete de atendimento',$r['pet_name'].' tem '.($r['service_name']?:status_label($r['service_type'])).' em '.$r['company_name'].' no dia '.date('d/m/Y',strtotime($r['appointment_date'])).' às '.substr($r['appointment_time'],0,5).'.','info','dashboard.php?page=pet_tutor#agenda','pet_appointment',(int)$r['id']);db()->prepare('UPDATE pet_appointments SET reminder_sent_at=? WHERE id=?')->execute([now(),(int)$r['id']]);$count++;}
        // Próxima dose ou validade de vacina em até 7 dias.
        $q=db()->query("SELECT v.id,v.vaccine_name,v.next_dose_at,v.expires_at,p.name pet_name,pc.platform_user_id,c.name company_name FROM pet_vaccines v JOIN pets p ON p.id=v.pet_id JOIN pet_clients pc ON pc.id=p.client_id JOIN companies c ON c.id=v.company_id WHERE pc.platform_user_id IS NOT NULL AND v.reminder_sent_at IS NULL AND COALESCE(NULLIF(v.next_dose_at,''),NULLIF(v.expires_at,'')) IS NOT NULL AND date(COALESCE(NULLIF(v.next_dose_at,''),NULLIF(v.expires_at,''))) BETWEEN date('now') AND date('now','+7 days')");
        foreach($q->fetchAll() as $r){$d=$r['next_dose_at']?:$r['expires_at'];create_notification((int)$r['platform_user_id'],'💉 Vacina próxima',$r['vaccine_name'].' de '.$r['pet_name'].' precisa de atenção até '.date('d/m/Y',strtotime($d)).' — '.$r['company_name'].'.','warning','dashboard.php?page=pet_tutor#vaccines','pet_vaccine',(int)$r['id']);db()->prepare('UPDATE pet_vaccines SET reminder_sent_at=? WHERE id=?')->execute([now(),(int)$r['id']]);$count++;}
        return $count;
    }catch(Throwable $e){return 0;}
}

function active_banners(string $position): array {
    $allowed=['home','dashboard','marketplace'];if(!in_array($position,$allowed,true))return [];
    try{$q=db()->prepare("SELECT * FROM banners WHERE position=? AND status='active' ORDER BY sort_order,id DESC");$q->execute([$position]);return $q->fetchAll();}catch(Throwable $e){return [];}
}

function render_platform_banners(string $position): void {
    $items=active_banners($position);if(!$items)return;
    ?><section class="platform-banner-strip platform-banner-<?=e($position)?>" aria-label="Destaques"><div class="platform-banner-track"><?php foreach($items as $banner):
        $link=trim((string)($banner['link']??''));$validLink=$link!==''&&(filter_var($link,FILTER_VALIDATE_URL)||preg_match('~^(?:dashboard|index|marketplace)\.php(?:[?#].*)?$~',$link));
        $tag=$validLink?'a':'article';?><<?=$tag?> class="platform-banner-card"<?php if($validLink):?> href="<?=e($link)?>"<?php if(filter_var($link,FILTER_VALIDATE_URL)):?> target="_blank" rel="noopener"<?php endif;?><?php endif;?>><?php if(!empty($banner['image'])):?><img src="<?=e(media_url($banner['image']))?>" alt=""><?php endif;?><span class="platform-banner-shade"></span><span class="platform-banner-copy"><strong><?=e($banner['title'])?></strong><?php if(!empty($banner['subtitle'])):?><small><?=e($banner['subtitle'])?></small><?php endif;?></span></<?=$tag?>><?php endforeach;?></div></section><?php
}

function manual_video_source(string $url): array {
    $url=trim($url);if($url===''||!filter_var($url,FILTER_VALIDATE_URL))return ['kind'=>'','url'=>''];
    $parts=parse_url($url);$host=strtolower((string)($parts['host']??''));$path=(string)($parts['path']??'');
    if(preg_match('~(?:youtube\.com|youtu\.be)$~',$host)){
        $id='';if($host==='youtu.be')$id=trim($path,'/');elseif(preg_match('~/shorts/([A-Za-z0-9_-]{6,})~',$path,$m))$id=$m[1];else{parse_str((string)($parts['query']??''),$query);$id=(string)($query['v']??'');}
        if(preg_match('/^[A-Za-z0-9_-]{6,}$/',$id))return ['kind'=>'iframe','url'=>'https://www.youtube-nocookie.com/embed/'.$id.'?rel=0'];
    }
    if(strpos($host,'vimeo.com')!==false&&preg_match('~/(\d+)~',$path,$m))return ['kind'=>'iframe','url'=>'https://player.vimeo.com/video/'.$m[1]];
    if(preg_match('~\.(?:mp4|webm|ogg)(?:$|\?)~i',$url))return ['kind'=>'video','url'=>$url];
    return ['kind'=>'link','url'=>$url];
}
