<?php

namespace Modules\PortalApi\Support;

use Illuminate\Support\Carbon;
use Modules\PortalApi\Entities\PortalOwner;
use Modules\VetClinic\Entities\VcAppointment;
use Modules\VetClinic\Entities\VcAppointmentType;
use Modules\VetClinic\Entities\VcPet;
use Modules\VetClinic\Entities\VcPetCondition;
use Modules\VetClinic\Entities\VcVaccination;

/**
 * Reads from the real VetClinic tables, scoped to a single portal owner
 * (and therefore a single business_id + contact_id).
 *
 * Each method returns the SAME shape PortalFixtures returns, so the
 * PWA contract doesn't change. PortalController picks between this
 * class and PortalFixtures based on whether the request resolved to a
 * real authenticated owner.
 */
class PortalDataRepository
{
    public function __construct(private PortalOwner $owner) {}

    /* ─── Helpers ───────────────────────────────────────────── */

    private function business(): int { return (int) $this->owner->business_id; }
    private function contact(): ?int { return $this->owner->contact_id ? (int) $this->owner->contact_id : null; }

    /** Base pets query — only THIS owner's pets, only living. */
    private function petsQuery()
    {
        return VcPet::query()
            ->where('business_id', $this->business())
            ->when($this->contact(), fn($q) => $q->where('contact_id', $this->contact()))
            ->where(function ($q) { $q->where('deceased', false)->orWhereNull('deceased'); });
    }

    /** Map a VcPet to the PWA pet-summary shape. */
    public function petSummary(VcPet $p): array
    {
        $vaxCount     = $p->vaccinations()->count();
        $overdue      = $p->vaccinations()->whereNotNull('next_due_date')->whereDate('next_due_date', '<',  now())->count();
        $dueSoon      = $p->vaccinations()->whereNotNull('next_due_date')->whereBetween('next_due_date', [now(), now()->copy()->addDays(30)])->count();

        if ($overdue > 0) {
            $statusLabel = $overdue . ' vax overdue';
            $statusTone  = 'urgent';
        } elseif ($dueSoon > 0) {
            $statusLabel = $dueSoon . ' due soon';
            $statusTone  = 'warning';
        } else {
            $statusLabel = $vaxCount ? 'Up to date' : 'No vaccines';
            $statusTone  = $vaxCount ? 'ok' : 'warning';
        }

        return [
            'id'           => $p->id,
            'name'         => $p->petname,
            'species'      => strtolower($p->species ?? 'other'),
            'age_label'    => $this->ageLabel($p),
            'dob'          => $p->dob ? Carbon::parse($p->dob)->toDateString() : null,
            'photo_url'    => $p->photo_url,
            'status_label' => $statusLabel,
            'status_tone'  => $statusTone,
        ];
    }

    private function ageLabel(VcPet $p): string
    {
        $species = ucfirst($p->species ?? '—');
        if (! $p->dob) return $species;
        $years = (int) Carbon::parse($p->dob)->diffInYears(now());
        return $species . ' · ' . ($years === 0 ? '<1y' : "{$years}y");
    }

    /* ─── Owner profile (/me) ───────────────────────────────── */

    public function me(): array
    {
        return [
            'id'    => $this->owner->id,
            'name'  => $this->owner->name,
            'phone' => $this->owner->phone,
            'email' => $this->owner->email,
        ];
    }

    /* ─── Pets ──────────────────────────────────────────────── */

    public function pets(): array
    {
        return $this->petsQuery()->orderBy('petname')->get()
            ->map(fn($p) => $this->petSummary($p))->all();
    }

    public function pet(int $id): ?array
    {
        $pet = $this->petsQuery()->where('id', $id)->first();
        if (! $pet) return null;

        $vaxRows  = $this->vaxRecords($pet);
        $weights  = $this->weightHistory($pet);
        $cond     = $this->conditions($pet);
        $visits   = $this->recentVisits($pet);
        $allergies = $this->allergies($pet);

        $statsCount = [
            'visits'     => $pet->appointments()->count(),
            'vaccines'   => $pet->vaccinations()->count(),
            'conditions' => $pet->conditions()->where('status', 'active')->count(),
        ];

        $preferredId = $pet->effectivePreferredDoctorId();
        $preferredDoctor = $preferredId
            ? \Modules\VetClinic\Entities\VcDoctor::find($preferredId)
            : null;

        return array_merge($this->petSummary($pet), [
            'breed'             => $pet->breed,
            'gender'            => strtolower($pet->gender ?? 'unknown'),
            'neutered'          => (bool) $pet->neutered,
            'dob_label'         => $pet->dob ? Carbon::parse($pet->dob)->format('d M Y') : null,
            'microchip_no'      => $pet->microchip_no,
            'last_weight_kg'    => $pet->weight_kg ? (float) $pet->weight_kg : null,
            'allergies'         => $allergies,
            'vaccinations'      => $vaxRows,
            'weight_history'    => $weights,
            'conditions'        => $cond,
            'recent_visits'     => $visits,
            'stats'             => $statsCount,
            'preferred_doctor'  => $preferredDoctor ? [
                'id'             => $preferredDoctor->id,
                'name'           => $preferredDoctor->name,
                'specialization' => $preferredDoctor->specialization,
                // Where the preference came from — pet-level override or
                // owner-level fallback. UI uses this to show a hint.
                'source'         => $pet->preferred_doctor_id ? 'pet' : 'owner',
            ] : null,
        ]);
    }

    private function vaxRecords(VcPet $pet): array
    {
        return $pet->vaccinations()->with('vaccineType')->orderByDesc('administered_date')->take(20)->get()
            ->map(function (VcVaccination $v) {
                $next = $v->next_due_date ? Carbon::parse($v->next_due_date) : null;
                $status = 'ok';
                if ($next) {
                    if ($next->isPast()) $status = 'overdue';
                    elseif ($next->lte(now()->addDays(30))) $status = 'due_soon';
                }
                return [
                    'id'              => $v->id,
                    'vaccine_name'    => optional($v->vaccineType)->name ?: 'Vaccine',
                    'given_at'        => optional($v->administered_date)->toDateString(),
                    'given_label'     => optional($v->administered_date)->format('d M Y'),
                    'doctor_name'     => $v->administered_by ?: '—',
                    'next_due_at'     => $next?->toDateString(),
                    'next_due_label'  => $next?->format('M Y'),
                    'status'          => $status,
                ];
            })->all();
    }

    private function weightHistory(VcPet $pet): array
    {
        // Pull weight measurements from appointments (where vets typically record).
        return $pet->appointments()
            ->whereNotNull('weight_kg')
            ->orderBy('appointment_date')
            ->take(12)
            ->get(['appointment_date', 'weight_kg'])
            ->map(fn ($a) => [
                'date'       => optional($a->appointment_date)->toDateString(),
                'date_label' => optional($a->appointment_date)->format('M y'),
                'kg'         => (float) $a->weight_kg,
            ])
            ->filter(fn ($r) => $r['date'] !== null)
            ->values()
            ->all();
    }

    private function conditions(VcPet $pet): array
    {
        return $pet->conditions()->with('condition')->orderByDesc('id')->take(10)->get()
            ->map(fn(VcPetCondition $c) => [
                'id'           => $c->id,
                'name'         => optional($c->condition)->name ?: 'Condition',
                'status'       => $c->status ?: 'active',
                'since_label'  => $c->diagnosed_date
                    ? 'since ' . Carbon::parse($c->diagnosed_date)->format('M Y')
                    : null,
            ])->all();
    }

    private function allergies(VcPet $pet): array
    {
        // Allergies live in vc_pet_conditions where the linked
        // vc_medical_conditions.category = 'allergy' (or name like 'Allerg%').
        return $pet->conditions()
            ->whereHas('condition', function ($q) {
                $q->where('category', 'allergy')
                  ->orWhere('name', 'like', 'Allerg%');
            })
            ->where('status', 'active')
            ->with('condition')
            ->get()
            ->map(fn (VcPetCondition $c) => optional($c->condition)->name)
            ->filter()
            ->map(fn ($n) => trim((string) $n))
            ->values()
            ->all();
    }

    private function recentVisits(VcPet $pet): array
    {
        // No appointmentType relation defined on VcAppointment — the type
        // name is stored denormalized on the row's 'type' column.
        return $pet->appointments()->orderByDesc('appointment_date')->take(5)->get()
            ->map(function (VcAppointment $a) {
                $doctor = null;
                try { $doctor = $a->doctor; } catch (\Throwable $e) { /* relation may not exist */ }
                return [
                    'id'          => $a->id,
                    'date_label'  => optional($a->appointment_date)->format('d M Y')
                                     . ($a->appointment_time ? ' · ' . substr($a->appointment_time, 0, 5) : ''),
                    'type'        => $a->type ?: 'Visit',
                    'doctor_name' => optional($doctor)->name ?? '—',
                    'status'      => $this->appointmentStatusKey($a->status),
                ];
            })->all();
    }

    /* ─── Vax card ──────────────────────────────────────────── */

    public function vaxCard(int $petId, string $verifyUrl, string $clinic, string $ownerName): ?array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return null;
        return [
            'pet_id'        => $pet->id,
            'pet_name'      => $pet->petname,
            'pet_species'   => strtolower($pet->species ?? 'other'),
            'owner_name'    => $ownerName,
            'clinic_name'   => $clinic,
            'microchip_no'  => $pet->microchip_no,
            'vaccinations'  => $this->vaxRecords($pet),
            'verify_url'    => $verifyUrl,
            'issued_at'     => now()->toIso8601String(),
        ];
    }

    /* ─── Home ──────────────────────────────────────────────── */

    public function home(): array
    {
        $pets = $this->petsQuery()->orderBy('petname')->get();

        // Reminders: overdue + due-soon vaccinations across all this owner's pets
        $reminders = [];
        foreach ($pets as $pet) {
            $rows = $pet->vaccinations()
                ->whereNotNull('next_due_date')
                ->whereDate('next_due_date', '<=', now()->copy()->addDays(30))
                ->with('vaccineType')
                ->get();
            foreach ($rows as $v) {
                $due = Carbon::parse($v->next_due_date);
                $isOverdue = $due->isPast();
                $reminders[] = [
                    'id'        => $v->id,
                    'pet_id'    => $pet->id,
                    'pet_name'  => $pet->petname,
                    'type'      => 'vaccination',
                    'title'     => $pet->petname . "'s " . (optional($v->vaccineType)->name ?: 'vaccine') . ' is due',
                    'due_label' => $due->diffForHumans(now(), ['parts' => 1]),
                    'due_at'    => $due->toIso8601String(),
                    'severity'  => $isOverdue ? 'urgent' : 'warning',
                ];
            }
        }
        // Show at most 3, newest first.
        usort($reminders, fn($a, $b) => $a['due_at'] <=> $b['due_at']);
        $reminders = array_slice($reminders, 0, 3);

        // Next appointment
        $next = null;
        $nextRow = VcAppointment::with(['doctor'])
            ->where('business_id', $this->business())
            ->when($this->contact(), fn($q) => $q->where('contact_id', $this->contact()))
            ->whereDate('appointment_date', '>=', now())
            ->whereIn('status', ['pending','confirmed','waiting'])
            ->orderBy('appointment_date')
            ->orderBy('appointment_time')
            ->first();
        if ($nextRow) {
            $next = [
                'id'          => $nextRow->id,
                'date_label'  => Carbon::parse($nextRow->appointment_date)->format('D M j')
                                 . ' · ' . substr((string) $nextRow->appointment_time, 0, 5),
                'type'        => $nextRow->type ?: 'Visit',
                'pet_name'    => optional($nextRow->pet)->petname ?? '',
                'doctor_name' => optional($nextRow->doctor)->name ?? '',
            ];
        }

        return [
            'greeting_name'    => $this->owner->name,
            'date_label'       => now()->format('l, F j'),
            'reminders'        => $reminders,
            'next_appointment' => $next,
            'pets'             => $pets->map(fn($p) => $this->petSummary($p))->all(),
        ];
    }

    /* ─── Appointment listing ───────────────────────────────── */

    public function appointments(): array
    {
        $base = VcAppointment::with(['pet', 'doctor'])
            ->where('business_id', $this->business())
            ->when($this->contact(), fn($q) => $q->where('contact_id', $this->contact()));

        $upcoming = (clone $base)
            ->whereDate('appointment_date', '>=', now()->toDateString())
            ->whereIn('status', ['pending','confirmed','waiting','in_consultation'])
            ->orderBy('appointment_date')->orderBy('appointment_time')->take(20)->get();

        $past = (clone $base)
            ->where(function ($q) {
                $q->whereDate('appointment_date', '<', now()->toDateString())
                  ->orWhereIn('status', ['completed','cancelled','no_show']);
            })
            ->orderByDesc('appointment_date')->orderByDesc('appointment_time')->take(50)->get();

        return [
            'upcoming' => $upcoming->map(fn($a) => $this->appointmentRow($a))->all(),
            'past'     => $past->map(fn($a)     => $this->appointmentRow($a))->all(),
        ];
    }

    private function appointmentRow(VcAppointment $a): array
    {
        $dt = Carbon::parse(($a->appointment_date?->toDateString() ?? '') . ' ' . ($a->appointment_time ?? '00:00:00'));
        $statusKey = $this->appointmentStatusKey($a->status);
        return [
            'id'            => $a->id,
            'ref_no'        => $a->ref_no,
            'date_iso'      => $dt->toIso8601String(),
            'date_label'    => $dt->format('D M j · H:i'),
            'type'          => $a->type ?: 'Visit',
            'pet_name'      => optional($a->pet)->petname ?? '',
            'pet_species'   => strtolower(optional($a->pet)->species ?? 'other'),
            'doctor_name'   => optional($a->doctor)->name ?? '',
            'status'        => $statusKey,
            'status_label'  => ucfirst(str_replace('_', ' ', $statusKey)),
        ];
    }

    private function appointmentStatusKey(?string $s): string
    {
        $map = [
            'pending'         => 'scheduled',
            'confirmed'       => 'confirmed',
            'waiting'         => 'in_consultation',
            'in_consultation' => 'in_consultation',
            'completed'       => 'completed',
            'cancelled'       => 'cancelled',
            'no_show'         => 'no_show',
        ];
        return $map[$s] ?? 'scheduled';
    }

    /* ─── Boarding & admissions ─────────────────────────────── */

    /**
     * Active + recent admissions (hospitalization, observation, boarding)
     * for this owner's pets. Returns a flat shape the PWA can render
     * without joining tables. Gracefully no-ops when the VetAdmission
     * module isn't installed.
     */
    public function admissions(): array
    {
        if (! class_exists(\Modules\VetAdmission\Entities\VaAdmission::class)
            || ! \Schema::hasTable('va_admissions')) {
            return ['active' => [], 'past' => []];
        }

        $base = \Modules\VetAdmission\Entities\VaAdmission::query()
            ->with(['pet:id,petname,species,date_of_birth', 'ward:id,name,type', 'admittingDoctor:id,name', 'attendingDoctor:id,name'])
            ->where('business_id', $this->business())
            ->when($this->contact(), fn($q) => $q->where('contact_id', $this->contact()));

        $active = (clone $base)
            ->where('status', 'admitted')
            ->orderByDesc('admitted_at')->take(10)->get();

        $past = (clone $base)
            ->where('status', 'discharged')
            ->orderByDesc('discharged_at')->take(20)->get();

        return [
            'active' => $active->map(fn($a) => $this->admissionRow($a))->all(),
            'past'   => $past->map(fn($a)   => $this->admissionRow($a))->all(),
        ];
    }

    private function admissionRow(\Modules\VetAdmission\Entities\VaAdmission $a): array
    {
        $admittedAt   = $a->admitted_at   ? Carbon::parse($a->admitted_at)   : null;
        $expectedOut  = $a->expected_discharge_at ? Carbon::parse($a->expected_discharge_at) : null;
        $dischargedAt = $a->discharged_at ? Carbon::parse($a->discharged_at) : null;
        $endRef       = $dischargedAt ?: Carbon::now();
        $stayDays     = $admittedAt ? max(1, $admittedAt->diffInDays($endRef) + 1) : 0;

        // Latest vital if any (gracefully degrades if the table doesn't exist)
        $latestVital = null;
        if (\Schema::hasTable('va_admission_vitals')) {
            $v = \DB::table('va_admission_vitals')->where('admission_id', $a->id)
                    ->orderByDesc('recorded_at')->first();
            if ($v) {
                $bits = array_filter([
                    $v->temperature  ? 'T ' . rtrim(rtrim((string)$v->temperature, '0'), '.')  . '°C' : null,
                    $v->heart_rate   ? 'HR ' . (int) $v->heart_rate  : null,
                    $v->resp_rate    ? 'RR ' . (int) $v->resp_rate   : null,
                ]);
                if ($bits) $latestVital = [
                    'summary'     => implode(' · ', $bits),
                    'recorded_at' => $v->recorded_at,
                ];
            }
        }

        return [
            'id'              => $a->id,
            'ref_no'          => $a->admission_no,
            'type'            => $a->type,       // hospitalization | observation | boarding
            'type_label'      => $this->admissionTypeLabel($a->type),
            'status'          => $a->status,     // admitted | discharged
            'reason'          => $a->reason,
            'diagnosis'       => $a->diagnosis,
            'pet_id'          => (int) $a->pet_id,
            'pet_name'        => optional($a->pet)->petname ?? '',
            'pet_species'     => strtolower(optional($a->pet)->species ?? 'other'),
            'ward_name'       => optional($a->ward)->name ?? null,
            'admitting_doctor'=> optional($a->admittingDoctor)->name,
            'attending_doctor'=> optional($a->attendingDoctor)->name,
            'admitted_at'     => $admittedAt?->toIso8601String(),
            'admitted_label'  => $admittedAt?->format('D M j · H:i'),
            'expected_out'    => $expectedOut?->toIso8601String(),
            'expected_label'  => $expectedOut?->format('D M j'),
            'discharged_at'   => $dischargedAt?->toIso8601String(),
            'discharged_label'=> $dischargedAt?->format('D M j · H:i'),
            'stay_days'       => $stayDays,
            'daily_rate'      => $a->daily_rate ? (float) $a->daily_rate : null,
            'deposit'         => $a->deposit_amount ? (float) $a->deposit_amount : null,
            'discharge_summary' => $a->discharge_summary,
            'latest_vital'    => $latestVital,
        ];
    }

    private function admissionTypeLabel(?string $t): string
    {
        return [
            'hospitalization' => 'Hospitalisation',
            'observation'     => 'Observation',
            'boarding'        => 'Boarding',
        ][$t] ?? ucfirst((string) $t);
    }

    /* ─── Owner profile ─────────────────────────────────────── */

    public function updateProfile(array $data): array
    {
        $allowed = array_intersect_key($data, array_flip(['name','phone','email']));
        if (empty($allowed)) return ['error' => 'nothing_to_update'];
        $this->owner->fill($allowed)->save();
        // Mirror to the ERP contact row so clinic admin sees the change.
        if ($this->contact()) {
            $contactUpdate = array_intersect_key($allowed, array_flip(['email']));
            if (isset($allowed['name']))  $contactUpdate['name']   = $allowed['name'];
            if (isset($allowed['phone'])) $contactUpdate['mobile'] = $allowed['phone'];
            if ($contactUpdate) \DB::table('contacts')->where('id', $this->contact())->update($contactUpdate);
        }
        return ['ok' => true, 'me' => $this->me()];
    }

    public function deleteAccount(): array
    {
        // Soft-disable rather than hard-delete: keeps clinical records intact
        // for regulatory retention, revokes all sessions, drops push subs.
        $this->owner->is_active = false;
        $this->owner->save();
        \DB::table('portal_owner_tokens')->where('owner_id', $this->owner->id)->delete();
        \DB::table('portal_push_subscriptions')->where('owner_id', $this->owner->id)->delete();
        return ['ok' => true];
    }

    /* ─── Pet photo upload ───────────────────────────────────── */

    public function setPetPhoto(int $petId, string $absolutePath, string $publicUrl): array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return ['error' => 'pet_not_found'];
        $pet->photo_path = $publicUrl;
        $pet->save();
        return ['ok' => true, 'photo_url' => $publicUrl];
    }

    /* ─── Weight log ─────────────────────────────────────────── */

    public function weights(int $petId): array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return ['entries' => [], 'unit' => 'kg'];
        $rows = \DB::table('vc_pet_weights')
            ->where('business_id', $this->business())
            ->where('pet_id', $pet->id)
            ->orderByDesc('recorded_at')
            ->limit(100)
            ->get(['id','weight_kg','recorded_at','source','notes']);
        return [
            'entries' => $rows->map(fn ($r) => [
                'id'           => $r->id,
                'weight_kg'    => (float) $r->weight_kg,
                'recorded_at'  => $r->recorded_at,
                'date_label'   => Carbon::parse($r->recorded_at)->format('D M j'),
                'source'       => $r->source,
                'notes'        => $r->notes,
            ])->all(),
            'unit' => 'kg',
            'current' => $rows->first() ? (float) $rows->first()->weight_kg : ((float) ($pet->weight_kg ?? 0)),
        ];
    }

    public function addWeight(int $petId, float $weightKg, ?string $notes = null): array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return ['error' => 'pet_not_found'];
        if ($weightKg <= 0 || $weightKg > 200) return ['error' => 'out_of_range'];

        $id = \DB::table('vc_pet_weights')->insertGetId([
            'business_id'  => $this->business(),
            'pet_id'       => $pet->id,
            'weight_kg'    => $weightKg,
            'recorded_at'  => now(),
            'source'       => 'owner',
            'notes'        => $notes,
            'created_at'   => now(),
            'updated_at'   => now(),
        ]);
        // Also update the pet's "current weight" cache.
        $pet->weight_kg = $weightKg;
        $pet->save();
        return ['ok' => true, 'id' => $id];
    }

    /* ─── Symptom diary ──────────────────────────────────────── */

    public function symptoms(int $petId): array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return ['entries' => []];
        $rows = \DB::table('vc_pet_symptoms')
            ->where('business_id', $this->business())
            ->where('pet_id', $pet->id)
            ->orderByDesc('recorded_at')
            ->limit(50)
            ->get();
        return [
            'entries' => $rows->map(fn ($r) => [
                'id'           => $r->id,
                'recorded_at'  => $r->recorded_at,
                'date_label'   => Carbon::parse($r->recorded_at)->format('D M j · H:i'),
                'body'         => $r->body,
                'severity'     => $r->severity,
                'photo_paths'  => json_decode((string) $r->photo_paths, true) ?: [],
                'status'       => $r->status,
                'reply_body'   => $r->reply_body,
                'replied_at'   => $r->replied_at,
            ])->all(),
        ];
    }

    public function addSymptom(int $petId, string $body, string $severity, array $photoUrls = []): array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return ['error' => 'pet_not_found'];
        if (! in_array($severity, ['low','moderate','high','urgent'], true)) $severity = 'low';

        $id = \DB::table('vc_pet_symptoms')->insertGetId([
            'business_id'  => $this->business(),
            'pet_id'       => $pet->id,
            'contact_id'   => $this->contact(),
            'recorded_at'  => now(),
            'body'         => $body,
            'severity'     => $severity,
            'photo_paths'  => json_encode($photoUrls),
            'status'       => 'open',
            'created_at'   => now(),
            'updated_at'   => now(),
        ]);
        return ['ok' => true, 'id' => $id];
    }

    /* ─── Medical reports + SOAP notes ───────────────────────── */

    public function medicalReports(): array
    {
        $reports = \DB::table('vc_medical_reports as r')
            ->leftJoin('vc_pets as p',     'p.id', '=', 'r.pet_id')
            ->leftJoin('vc_doctors as d',  'd.id', '=', 'r.doctor_id')
            ->where('r.business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('r.contact_id', $this->contact()))
            ->orderByDesc('r.created_at')
            ->limit(50)
            ->get([
                'r.id','r.report_type','r.report_title','r.findings','r.created_at',
                'p.id as pet_id','p.petname as pet_name','p.species as pet_species',
                'd.name as doctor_name',
            ]);
        return $reports->map(fn ($r) => [
            'id'           => $r->id,
            'type'         => $r->report_type,
            'title'        => $r->report_title ?: ucfirst((string) $r->report_type),
            'pet_id'       => $r->pet_id,
            'pet_name'     => $r->pet_name,
            'pet_species'  => strtolower((string) $r->pet_species),
            'doctor_name'  => $r->doctor_name,
            'date_label'   => Carbon::parse($r->created_at)->format('D M j, Y'),
            'preview'      => \Illuminate\Support\Str::limit((string) $r->findings, 140),
        ])->all();
    }

    public function medicalReport(int $id): ?array
    {
        $r = \DB::table('vc_medical_reports as r')
            ->leftJoin('vc_pets as p',    'p.id', '=', 'r.pet_id')
            ->leftJoin('vc_doctors as d', 'd.id', '=', 'r.doctor_id')
            ->where('r.id', $id)
            ->where('r.business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('r.contact_id', $this->contact()))
            ->first([
                'r.id','r.report_type','r.report_title','r.findings','r.attachments','r.created_at',
                'p.id as pet_id','p.petname as pet_name','p.species as pet_species',
                'd.name as doctor_name',
            ]);
        if (! $r) return null;
        $attachments = json_decode((string) $r->attachments, true) ?: [];
        return [
            'id'          => $r->id,
            'type'        => $r->report_type,
            'title'       => $r->report_title ?: ucfirst((string) $r->report_type),
            'findings'    => $r->findings,
            'attachments' => $attachments,
            'pet_id'      => $r->pet_id,
            'pet_name'    => $r->pet_name,
            'pet_species' => strtolower((string) $r->pet_species),
            'doctor_name' => $r->doctor_name,
            'date_label'  => Carbon::parse($r->created_at)->format('D M j, Y'),
        ];
    }

    /** SOAP notes from a completed appointment — what the vet actually wrote. */
    public function appointmentSoap(int $aptId): ?array
    {
        $a = VcAppointment::with(['pet','doctor'])
            ->where('id', $aptId)
            ->where('business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('contact_id', $this->contact()))
            ->first();
        if (! $a) return null;
        return [
            'id'             => $a->id,
            'ref_no'         => $a->ref_no,
            'date_label'     => Carbon::parse($a->appointment_date)->setTimeFromTimeString($a->appointment_time ?? '00:00:00')->format('D M j, Y · H:i'),
            'type'           => $a->type ?: 'Visit',
            'pet_name'       => optional($a->pet)->petname ?? '',
            'pet_species'    => strtolower(optional($a->pet)->species ?? 'other'),
            'doctor_name'    => optional($a->doctor)->name ?? '',
            'soap' => [
                'subjective'    => $a->soap_subjective,
                'objective'     => $a->soap_objective,
                'assessment'    => $a->soap_assessment,
                'plan'          => $a->soap_plan,
            ],
            'vitals' => array_filter([
                'weight_kg'        => $a->weight_kg ? (float) $a->weight_kg : null,
                'temperature'      => $a->temperature ? (float) $a->temperature : null,
                'heart_rate'       => $a->heart_rate ? (int) $a->heart_rate : null,
                'respiratory_rate' => $a->respiratory_rate ? (int) $a->respiratory_rate : null,
            ]),
            'notes' => $a->doctor_notes,
        ];
    }

    /* ─── Prescriptions ──────────────────────────────────────── */

    public function prescriptions(): array
    {
        $rxs = \DB::table('vc_prescriptions as rx')
            ->leftJoin('vc_pets as p',    'p.id', '=', 'rx.pet_id')
            ->leftJoin('vc_doctors as d', 'd.id', '=', 'rx.doctor_id')
            ->where('rx.business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('rx.contact_id', $this->contact()))
            ->orderByDesc('rx.prescribed_date')
            ->limit(50)
            ->get([
                'rx.id','rx.ref_no','rx.prescribed_date','rx.valid_until','rx.status','rx.diagnosis',
                'p.id as pet_id','p.petname as pet_name','p.species as pet_species',
                'd.name as doctor_name',
            ]);
        $byId = $rxs->pluck('id')->all();
        $items = \DB::table('vc_prescription_items')
            ->whereIn('prescription_id', $byId)
            ->get(['prescription_id','medicine_name','dosage','frequency','duration_days','instructions'])
            ->groupBy('prescription_id');

        return $rxs->map(fn ($r) => [
            'id'           => $r->id,
            'ref_no'       => $r->ref_no,
            'pet_id'       => $r->pet_id,
            'pet_name'     => $r->pet_name,
            'pet_species'  => strtolower((string) $r->pet_species),
            'doctor_name'  => $r->doctor_name,
            'date_label'   => $r->prescribed_date ? Carbon::parse($r->prescribed_date)->format('D M j, Y') : '—',
            'valid_until'  => $r->valid_until ? Carbon::parse($r->valid_until)->format('D M j, Y') : null,
            'status'       => $r->status,
            'diagnosis'    => $r->diagnosis,
            'items'        => ($items[$r->id] ?? collect())->map(fn ($i) => [
                'medicine'    => $i->medicine_name,
                'dosage'      => $i->dosage,
                'frequency'   => $i->frequency,
                'duration'    => $i->duration_days ? $i->duration_days . ' day' . ($i->duration_days === 1 ? '' : 's') : null,
                'instructions'=> $i->instructions,
            ])->values()->all(),
        ])->all();
    }

    /** Refill = a portal message + telehealth-style request to the clinic. */
    public function requestRefill(int $prescriptionId, ?string $notes = null): array
    {
        $rx = \DB::table('vc_prescriptions')
            ->where('id', $prescriptionId)
            ->where('business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('contact_id', $this->contact()))
            ->first();
        if (! $rx) return ['error' => 'prescription_not_found'];

        \DB::table('portal_messages')->insert([
            'business_id'    => $this->business(),
            'owner_id'       => $this->owner->id,
            'sender'         => 'owner',
            'sender_user_id' => null,
            'body'           => 'Refill request for prescription ' . ($rx->ref_no ?? '#' . $rx->id)
                              . ($notes ? "\n\nNote: " . $notes : ''),
            'attachments'    => null,
            'reply_to_id'    => null,
            'created_at'     => now(),
            'updated_at'     => now(),
        ]);
        return ['ok' => true];
    }

    /* ─── Lab results ────────────────────────────────────────── */

    public function labSessions(): array
    {
        if (! \Schema::hasTable('lab_test_sessions')) return [];
        $rows = \DB::table('lab_test_sessions')
            ->where('business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('contact_id', $this->contact()))
            ->orderByDesc('test_date')->orderByDesc('test_time')
            ->limit(50)
            ->get(['id','sample_no','vendor','test_date','test_time','species','sample_type','vc_pet_id']);
        $pets = \DB::table('vc_pets')->whereIn('id', $rows->pluck('vc_pet_id')->filter())
            ->get(['id','petname','species'])->keyBy('id');
        return $rows->map(fn ($r) => [
            'id'          => $r->id,
            'sample_no'   => $r->sample_no,
            'vendor'      => $r->vendor,
            'sample_type' => $r->sample_type,
            'date_label'  => $r->test_date ? Carbon::parse($r->test_date . ' ' . ($r->test_time ?? '00:00'))->format('D M j · H:i') : '—',
            'pet_id'      => $r->vc_pet_id,
            'pet_name'    => optional($pets[$r->vc_pet_id] ?? null)->petname,
            'pet_species' => strtolower((string) (optional($pets[$r->vc_pet_id] ?? null)->species ?? '')),
        ])->all();
    }

    public function labSession(int $sessionId): ?array
    {
        $session = \DB::table('lab_test_sessions')
            ->where('id', $sessionId)
            ->where('business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('contact_id', $this->contact()))
            ->first();
        if (! $session) return null;
        $results = \DB::table('lab_test_results')
            ->where('test_session_id', $sessionId)
            ->orderBy('analyte_name')
            ->get(['analyte_name','value','unit','ref_lower','ref_upper','is_abnormal','flags']);
        $pet = $session->vc_pet_id ? \DB::table('vc_pets')->where('id', $session->vc_pet_id)->first(['id','petname','species']) : null;
        return [
            'id'          => $session->id,
            'sample_no'   => $session->sample_no,
            'vendor'      => $session->vendor,
            'date_label'  => $session->test_date ? Carbon::parse($session->test_date . ' ' . ($session->test_time ?? '00:00'))->format('D M j, Y · H:i') : '—',
            'sample_type' => $session->sample_type,
            'pet_name'    => $pet->petname ?? '',
            'pet_species' => strtolower((string) ($pet->species ?? '')),
            'results'     => $results->map(fn ($r) => [
                'analyte'     => $r->analyte_name,
                'value'       => $r->value,
                'unit'        => $r->unit,
                'ref_lower'   => $r->ref_lower,
                'ref_upper'   => $r->ref_upper,
                'is_abnormal' => (bool) $r->is_abnormal,
                'flag'        => $r->flags,
            ])->all(),
        ];
    }

    /* ─── Invoices ──────────────────────────────────────────── */

    public function invoices(): array
    {
        $rows = \DB::table('transactions')
            ->where('business_id', $this->business())
            ->where('type', 'sell')
            ->where('status', 'final')
            ->when($this->contact(), fn ($q) => $q->where('contact_id', $this->contact()))
            ->orderByDesc('transaction_date')
            ->limit(50)
            ->get(['id','invoice_no','transaction_date','final_total','total_amount_recovered','payment_status']);
        return $rows->map(fn ($r) => [
            'id'              => $r->id,
            'invoice_no'      => $r->invoice_no,
            'date_label'      => $r->transaction_date ? Carbon::parse($r->transaction_date)->format('D M j, Y') : '—',
            'total'           => (float) $r->final_total,
            'paid'            => (float) ($r->total_amount_recovered ?? 0),
            'balance'         => max(0, (float) $r->final_total - (float) ($r->total_amount_recovered ?? 0)),
            'payment_status'  => $r->payment_status,
        ])->all();
    }

    /* ─── Inbox messaging ───────────────────────────────────── */

    public function messages(): array
    {
        $rows = \DB::table('portal_messages')
            ->where('business_id', $this->business())
            ->where('owner_id', $this->owner->id)
            ->orderBy('created_at')
            ->limit(200)
            ->get(['id','sender','body','attachments','read_at','created_at']);
        return $rows->map(fn ($r) => [
            'id'         => $r->id,
            'sender'     => $r->sender,
            'body'       => $r->body,
            'attachments'=> json_decode((string) $r->attachments, true) ?: [],
            'is_read'    => ! is_null($r->read_at),
            'created_at' => $r->created_at,
            'date_label' => Carbon::parse($r->created_at)->diffForHumans(),
        ])->all();
    }

    public function sendMessage(string $body, array $attachments = []): array
    {
        if (trim($body) === '') return ['error' => 'empty'];
        $id = \DB::table('portal_messages')->insertGetId([
            'business_id' => $this->business(),
            'owner_id'    => $this->owner->id,
            'sender'      => 'owner',
            'body'        => $body,
            'attachments' => $attachments ? json_encode($attachments) : null,
            'created_at'  => now(),
            'updated_at'  => now(),
        ]);
        return ['ok' => true, 'id' => $id];
    }

    public function markMessagesRead(): array
    {
        \DB::table('portal_messages')
            ->where('business_id', $this->business())
            ->where('owner_id', $this->owner->id)
            ->where('sender', 'clinic')
            ->whereNull('read_at')
            ->update(['read_at' => now()]);
        return ['ok' => true];
    }

    /* ─── Telehealth ────────────────────────────────────────── */

    public function submitTelehealth(int $petId, string $question, array $photos = []): array
    {
        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) return ['error' => 'pet_not_found'];
        $id = \DB::table('portal_telehealth_requests')->insertGetId([
            'business_id' => $this->business(),
            'owner_id'    => $this->owner->id,
            'pet_id'      => $pet->id,
            'question'    => $question,
            'photo_paths' => $photos ? json_encode($photos) : null,
            'status'      => 'pending',
            'created_at'  => now(),
            'updated_at'  => now(),
        ]);
        return ['ok' => true, 'id' => $id];
    }

    public function telehealthRequests(): array
    {
        $rows = \DB::table('portal_telehealth_requests as t')
            ->leftJoin('vc_pets as p', 'p.id', '=', 't.pet_id')
            ->where('t.business_id', $this->business())
            ->where('t.owner_id', $this->owner->id)
            ->orderByDesc('t.created_at')
            ->limit(50)
            ->get(['t.id','t.question','t.photo_paths','t.status','t.reply_body','t.replied_at','t.created_at',
                   'p.id as pet_id','p.petname as pet_name','p.species as pet_species']);
        return $rows->map(fn ($r) => [
            'id'          => $r->id,
            'question'    => $r->question,
            'photos'      => json_decode((string) $r->photo_paths, true) ?: [],
            'status'      => $r->status,
            'reply'       => $r->reply_body,
            'replied_at'  => $r->replied_at,
            'pet_name'    => $r->pet_name,
            'pet_species' => strtolower((string) $r->pet_species),
            'date_label'  => Carbon::parse($r->created_at)->diffForHumans(),
        ])->all();
    }

    /* ─── Reviews ───────────────────────────────────────────── */

    public function submitReview(int $appointmentId, int $rating, ?string $comments = null): array
    {
        $apt = VcAppointment::query()
            ->where('id', $appointmentId)
            ->where('business_id', $this->business())
            ->when($this->contact(), fn ($q) => $q->where('contact_id', $this->contact()))
            ->first();
        if (! $apt) return ['error' => 'appointment_not_found'];
        if ($apt->status !== 'completed') return ['error' => 'not_reviewable'];

        \DB::table('portal_reviews')->updateOrInsert(
            ['appointment_id' => $apt->id, 'owner_id' => $this->owner->id],
            [
                'business_id' => $this->business(),
                'doctor_id'   => $apt->doctor_id,
                'rating'      => max(1, min(5, $rating)),
                'comments'    => $comments,
                'created_at'  => now(),
                'updated_at'  => now(),
            ]
        );
        return ['ok' => true];
    }

    public function reviewFor(int $appointmentId): ?array
    {
        $r = \DB::table('portal_reviews')
            ->where('appointment_id', $appointmentId)
            ->where('owner_id', $this->owner->id)
            ->first(['rating','comments']);
        return $r ? ['rating' => (int) $r->rating, 'comments' => $r->comments] : null;
    }

    /* ─── Notification history ──────────────────────────────── */

    public function notifications(): array
    {
        $rows = \DB::table('portal_notifications')
            ->where('business_id', $this->business())
            ->where('owner_id', $this->owner->id)
            ->orderByDesc('created_at')->limit(80)
            ->get(['id','category','title','body','url','read_at','created_at']);
        return $rows->map(fn ($r) => [
            'id'         => $r->id,
            'category'   => $r->category,
            'title'      => $r->title,
            'body'       => $r->body,
            'url'        => $r->url,
            'is_read'    => ! is_null($r->read_at),
            'date_label' => Carbon::parse($r->created_at)->diffForHumans(),
        ])->all();
    }

    public function markNotificationsRead(): array
    {
        \DB::table('portal_notifications')
            ->where('business_id', $this->business())
            ->where('owner_id', $this->owner->id)
            ->whereNull('read_at')
            ->update(['read_at' => now()]);
        return ['ok' => true];
    }

    /* ─── Search ────────────────────────────────────────────── */

    public function search(string $q): array
    {
        $q = trim($q);
        if (mb_strlen($q) < 2) return ['pets' => [], 'appointments' => [], 'prescriptions' => [], 'invoices' => []];
        $like = '%' . $q . '%';

        $pets = $this->petsQuery()
            ->where(function ($w) use ($like) {
                $w->where('petname', 'like', $like)->orWhere('breed', 'like', $like);
            })
            ->limit(10)->get(['id','petname','species','breed']);

        $apts = VcAppointment::where('business_id', $this->business())
            ->when($this->contact(), fn ($x) => $x->where('contact_id', $this->contact()))
            ->where(function ($w) use ($like) { $w->where('ref_no', 'like', $like)->orWhere('type', 'like', $like); })
            ->orderByDesc('appointment_date')->limit(10)->get(['id','ref_no','type','appointment_date','status']);

        $rxs = \DB::table('vc_prescriptions')
            ->where('business_id', $this->business())
            ->when($this->contact(), fn ($x) => $x->where('contact_id', $this->contact()))
            ->where(function ($w) use ($like) { $w->where('ref_no', 'like', $like)->orWhere('diagnosis', 'like', $like); })
            ->orderByDesc('prescribed_date')->limit(10)->get(['id','ref_no','diagnosis','prescribed_date']);

        $invs = \DB::table('transactions')
            ->where('business_id', $this->business())->where('type', 'sell')
            ->when($this->contact(), fn ($x) => $x->where('contact_id', $this->contact()))
            ->where('invoice_no', 'like', $like)
            ->orderByDesc('transaction_date')->limit(10)
            ->get(['id','invoice_no','transaction_date','final_total','payment_status']);

        return [
            'pets'          => $pets->map(fn ($p) => ['id' => $p->id, 'name' => $p->petname, 'species' => strtolower($p->species ?? 'other'), 'breed' => $p->breed])->all(),
            'appointments'  => $apts->map(fn ($a) => ['id' => $a->id, 'ref_no' => $a->ref_no, 'type' => $a->type, 'date' => $a->appointment_date, 'status' => $a->status])->all(),
            'prescriptions' => $rxs->map(fn ($r) => ['id' => $r->id, 'ref_no' => $r->ref_no, 'diagnosis' => $r->diagnosis, 'date' => $r->prescribed_date])->all(),
            'invoices'      => $invs->map(fn ($i) => ['id' => $i->id, 'invoice_no' => $i->invoice_no, 'date' => $i->transaction_date, 'total' => (float) $i->final_total, 'payment_status' => $i->payment_status])->all(),
        ];
    }

    /* ─── Reasons (appointment types) ───────────────────────── */

    public function reasons(): array
    {
        // Include both this business's custom types AND the platform-wide
        // defaults (business_id IS NULL — seeded by AppointmentTypesSeeder).
        $rows = VcAppointmentType::where(function ($q) {
                $q->where('business_id', $this->business())
                  ->orWhereNull('business_id');
            })
            ->where(function ($q) {
                $q->where('status', 1)->orWhere('status', 'active');
            })
            ->orderBy('sort_order')
            ->orderBy('name')
            ->get();

        return $rows->map(fn ($t) => [
            'id'           => $t->id,
            'label'        => $t->name,
            'icon'         => $this->iconForType($t->icon ?? '', $t->name ?? ''),
            'duration_min' => 30,
        ])->all();
    }

    /**
     * Translate a VetClinic icon (FontAwesome class name) into the emoji
     * the PWA expects. Falls back on name keywords, then to a paw.
     */
    private function iconForType(string $faIcon, string $name): string
    {
        $key = strtolower(str_replace(['fa-', 'fas '], ['', ''], $faIcon));
        $byFa = [
            'stethoscope' => '🩺', 'syringe'  => '💉', 'scalpel'  => '🏥',
            'cut'         => '✂️', 'tooth'    => '🦷', 'band-aid' => '🩹',
            'x-ray'       => '📷', 'vial'     => '🧪', 'redo'     => '🔁',
            'ambulance'   => '🚑', 'heart'    => '❤️', 'eye'      => '👁️',
            'bone'        => '🦴', 'microchip'=> '🔌',
        ];
        if (isset($byFa[$key])) return $byFa[$key];

        // Last resort: try keywords in the type name
        $low = strtolower($name);
        foreach ([
            'vaccin' => '💉', 'dental' => '🦷', 'surgery'  => '🏥',
            'groom'  => '✂️', 'lab'    => '🧪', 'x-ray'    => '📷',
            'imag'   => '📷', 'emerg'  => '🚨', 'follow'   => '🔁',
            'derm'   => '🩹', 'chip'   => '🔌', 'spay'     => '✂️',
            'neuter' => '✂️', 'eye'    => '👁️', 'ortho'   => '🦴',
            'wellness' => '🩺', 'check' => '🩺',
        ] as $needle => $emoji) {
            if (str_contains($low, $needle)) return $emoji;
        }
        return '🐾';
    }

    /* ─── Booking ───────────────────────────────────────────── */

    public function book(array $draft): array
    {
        $petId   = (int) ($draft['pet_id'] ?? 0);
        // Accept the new multi-select reason_ids array OR the legacy
        // single reason_id, so older clients keep working.
        $typeIds = array_values(array_filter(
            (array) ($draft['reason_ids'] ?? []),
            fn ($v) => $v !== null && $v !== ''
        ));
        if (empty($typeIds) && isset($draft['reason_id'])) {
            $typeIds = [(int) $draft['reason_id']];
        }
        $typeIds = array_map('intval', $typeIds);
        $startIs = $draft['slot_start'] ?? null;

        $pet = $this->petsQuery()->where('id', $petId)->first();
        if (! $pet) {
            return ['error' => 'pet_not_found'];
        }
        $dt = $startIs ? Carbon::parse($startIs) : now()->addDay()->setTime(10, 0);

        // Apply pet's preferred doctor by default (falls back to owner's
        // preferred via VcPet::effectivePreferredDoctorId). Owner can
        // override per-booking by passing doctor_id (used when accepting
        // an "alternative" because preferred wasn't available).
        $preferredId = $pet->effectivePreferredDoctorId();
        $doctorId    = ! empty($draft['doctor_id'])
            ? (int) $draft['doctor_id']
            : $preferredId;
        $locationId  = ! empty($draft['location_id']) ? (int) $draft['location_id'] : null;

        // Combine selected types into the display name + sum duration.
        $typeNames = [];
        if (! empty($typeIds)) {
            $rows = VcAppointmentType::whereIn('id', $typeIds)->get(['id','name']);
            foreach ($rows as $t) $typeNames[] = $t->name;
        }
        $duration = max(30, 30 * count($typeIds));

        $apt = VcAppointment::create([
            'business_id'      => $this->business(),
            'contact_id'       => $pet->contact_id,
            'pet_id'           => $pet->id,
            'doctor_id'        => $doctorId,
            'location_id'      => $locationId,
            'ref_no'           => 'APT-' . strtoupper(substr(uniqid(), -6)),
            'appointment_date' => $dt->toDateString(),
            'appointment_time' => $dt->format('H:i:s'),
            'duration_minutes' => $duration,
            'status'           => 'pending',
            'type'             => implode(', ', $typeNames) ?: null,
            'type_ids'         => $typeIds ?: null,
        ]);

        // Audit: if the booked doctor differs from preferred (owner picked
        // an alternative because preferred wasn't free), log it so ERP
        // reports show the switch.
        if ($preferredId && $doctorId && $doctorId !== $preferredId) {
            \DB::table('vc_doctor_change_requests')->insert([
                'business_id'         => $this->business(),
                'appointment_id'      => $apt->id,
                'pet_id'              => $pet->id,
                'contact_id'          => $pet->contact_id,
                'requested_by'        => 0,
                'current_doctor_id'   => $preferredId,
                'requested_doctor_id' => $doctorId,
                'scope'               => 'appointment',
                'reason'              => 'Owner picked alternative at booking (portal)',
                'status'              => 'approved',
                'reviewed_at'         => now(),
                'created_at'          => now(),
                'updated_at'          => now(),
            ]);
        }

        return [
            'appointment_id' => $apt->id,
            'ref_no'         => $apt->ref_no,
            'date_label'     => $dt->format('D M j · H:i'),
            'pet_name'       => $pet->petname,
            'doctor_name'    => $doctorId
                ? (optional(\Modules\VetClinic\Entities\VcDoctor::find($doctorId))->name ?? '—')
                : '—',
        ];
    }

    /**
     * Owner-initiated cancel of an UPCOMING appointment. Only the
     * appointment's owner can cancel it, and only while it's still
     * pending/confirmed (no point cancelling completed/in-progress ones).
     *
     * Returns ['ok' => true] on success, ['error' => reason] on rejection.
     */
    public function cancelAppointment(int $id): array
    {
        $apt = VcAppointment::query()
            ->where('id', $id)
            ->where('business_id', $this->business())
            ->when($this->contact(), fn($q) => $q->where('contact_id', $this->contact()))
            ->first();

        if (! $apt)                                  return ['error' => 'not_found'];
        if (in_array($apt->status, ['completed','cancelled','no_show'], true)) {
            return ['error' => 'not_cancellable'];
        }

        // Clinic-side audit so the staff knows owner initiated the cancel.
        $apt->status = 'cancelled';
        $apt->cancelled_at = now();
        $apt->cancel_reason = 'Cancelled by owner via portal';
        $apt->save();

        return ['ok' => true, 'appointment_id' => $apt->id];
    }
}
