<?php

namespace App\Controllers;

use App\Models\ActivityLogModel;
use App\Models\UserModel;
use App\Models\DoctorModel;
use App\Models\DoctorSpecializationModel;
use App\Models\PatientModel;
use App\Models\AppointmentModel;
use App\Models\DoctorBookingModel;
// ...existing code...
use App\Models\SpecializationModel;

class Admin extends BaseController
{
    private function formatFullName(?string $firstName, ?string $lastName, string $fallback = ''): string
    {
        $fullName = trim(trim((string) $firstName) . ' ' . trim((string) $lastName));

        return $fullName !== '' ? $fullName : $fallback;
    }

    private function generateAccountPassword(int $length = 12): string
    {
        $alphabet = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789!@#$%';
        $maxIndex = strlen($alphabet) - 1;
        $password = '';

        for ($i = 0; $i < $length; $i++) {
            $password .= $alphabet[random_int(0, $maxIndex)];
        }

        return $password;
    }

    private function parseSpecializations($specializationInput): array
    {
        $specializations = [];

        if (is_array($specializationInput)) {
            foreach ($specializationInput as $item) {
                $item = trim((string) $item);
                if ($item !== '' && ! in_array($item, $specializations, true)) {
                    $specializations[] = $item;
                }
            }
        } else {
            $single = trim((string) $specializationInput);
            if ($single !== '') {
                $specializations[] = $single;
            }
        }

        return $specializations;
    }

    private function buildExperienceString(?int $years, ?int $months): ?string
    {
        $experienceParts = [];

        if ($years !== null && $years > 0) {
            $experienceParts[] = $years . ' year' . ($years === 1 ? '' : 's');
        }

        if ($months !== null && $months > 0) {
            $experienceParts[] = $months . ' month' . ($months === 1 ? '' : 's');
        }

        return $experienceParts !== [] ? implode(' ', $experienceParts) : null;
    }

    private function parseExperienceString(?string $experience): array
    {
        $experience = trim((string) $experience);
        $years      = 0;
        $months     = 0;

        if ($experience !== '' && preg_match('/(\d+)\s+year/i', $experience, $yearMatch)) {
            $years = (int) $yearMatch[1];
        }

        if ($experience !== '' && preg_match('/(\d+)\s+month/i', $experience, $monthMatch)) {
            $months = (int) $monthMatch[1];
        }

        return [
            'experience_years'  => $years,
            'experience_months' => $months,
        ];
    }

    private function normalizeAppointmentTime(string $time): ?string
    {
        $time = trim($time);

        if (preg_match('/^\d{2}:\d{2}$/', $time)) {
            return $time . ':00';
        }

        return preg_match('/^\d{2}:\d{2}:\d{2}$/', $time) ? $time : null;
    }

    private function isPastAppointmentDateTime(string $date, string $time): bool
    {
        $timezone = new \DateTimeZone(config('App')->appTimezone);
        $appointmentDateTime = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $date . ' ' . $time, $timezone);

        if (! $appointmentDateTime || $appointmentDateTime->format('Y-m-d H:i:s') !== $date . ' ' . $time) {
            return true;
        }

        return $appointmentDateTime < new \DateTimeImmutable('now', $timezone);
    }

    private function doctorHasAvailabilityAt(int $doctorId, string $date, string $time): bool
    {
        try {
            $dayNumber = (int) (new \DateTimeImmutable($date))->format('N');
        } catch (\Exception $e) {
            return false;
        }

        return \Config\Database::connect()
            ->table('doctor_availability')
            ->where('doctor_id', $doctorId)
            ->where('day_number', $dayNumber)
            ->where('status', 1)
            ->where('start_time <=', $time)
            ->where('end_time >', $time)
            ->countAllResults() > 0;
    }

    private function normalizeServiceIds($services): array
    {
        if (is_string($services)) {
            $decoded = json_decode($services, true);
            $services = is_array($decoded) ? $decoded : [$services];
        }

        if (! is_array($services)) {
            return [
                'ids'            => [],
                'has_duplicates' => false,
            ];
        }

        $ids = array_values(array_filter(array_map('intval', $services), static fn ($id) => $id > 0));

        return [
            'ids'            => array_values(array_unique($ids)),
            'has_duplicates' => count($ids) !== count(array_unique($ids)),
        ];
    }

    private function getPatientFormData(int $userId): ?array
    {
        $userModel    = new UserModel();
        $patientModel = new PatientModel();

        $user    = $userModel->find($userId);
        $patient = $patientModel->findByUserId($userId);

        if (! $user || ! $patient) {
            return null;
        }

        return [
            'patient'     => $patient,
            'user'        => $user,
            'first_name'  => $user['first_name'] ?? '',
            'last_name'   => $user['last_name'] ?? '',
        ];
    }

    private function validateDobInput(): bool
    {
        $dob = trim((string) $this->request->getPost('dob'));

        if ($dob === '') {
            return true;
        }

        $date = \DateTime::createFromFormat('Y-m-d', $dob);
        $errors = \DateTime::getLastErrors();

        if (! $date || ($errors['warning_count'] ?? 0) > 0 || ($errors['error_count'] ?? 0) > 0 || $date->format('Y-m-d') !== $dob) {
            return false;
        }

        return $dob <= date('Y-m-d');
    }

    private function getDoctorFormData(int $userId): ?array
    {
        $userModel                 = new UserModel();
        $doctorModel               = new DoctorModel();
        $specializationModel       = new SpecializationModel();
        $doctorSpecializationModel = new DoctorSpecializationModel();

        $user   = $userModel->find($userId);
        $doctor = $doctorModel->findByUserId($userId);

        if (! $user || ! $doctor) {
            return null;
        }

        $selectedSpecializations = $doctorSpecializationModel->getNamesForDoctor((int) $doctor['id']);

        if ($selectedSpecializations === []) {
            $selectedSpecializations = $this->parseSpecializations($doctor['specialization'] ?? []);
        }

        return array_merge($this->parseExperienceString($doctor['experience'] ?? null), [
            'doctor'                  => $doctor,
            'user'                    => $user,
            'first_name'              => $user['first_name'] ?? '',
            'last_name'               => $user['last_name'] ?? '',
            'specializationOptions'   => $specializationModel->getOptionNames(),
            'selectedSpecializations' => $selectedSpecializations,
        ]);
    }

    public function dashboard()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $doctorModel      = new DoctorModel();
        $patientModel     = new PatientModel();
        $appointmentModel = new AppointmentModel();
        $userModel        = new UserModel();
        $adminId          = (int) session()->get('id');
        $adminUser        = $userModel->find($adminId);

        $data['totalDoctors']      = $doctorModel->countAll();
        $data['totalPatients']     = $patientModel->countAll();
        $data['totalAppointments'] = $appointmentModel->countAll();
        $data['activeToday']       = $appointmentModel->countForDate(date('Y-m-d'));
        $data['recentActivity']    = $appointmentModel->getRecentActivity(6);
        $data['latestDoctors']     = $doctorModel->getLatestDoctors(5);
        $data['latestPatients']    = $patientModel->getLatestPatients(5);
        $data['adminName']  = $this->formatFullName($adminUser['first_name'] ?? '', $adminUser['last_name'] ?? '', 'Administrator');
        $data['adminEmail'] = $adminUser['email'] ?? '';

        return view('admin/dashboard', $data);
    }

    public function doctors()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        return view('admin/doctors');
    }

    public function deleteDoctor($id)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $id = (int) $id;
        if ($id < 1) {
            return redirect()->to(site_url('admin/doctors'));
        }

        $userModel                 = new UserModel();
        $doctorModel               = new DoctorModel();
        $appointmentModel          = new AppointmentModel();
        $doctorSpecializationModel = new DoctorSpecializationModel();

        $doctor = $doctorModel->findByUserId($id);
        if ($doctor) {
            $appointmentModel->deleteByDoctorId((int) $doctor['id']);
            $doctorSpecializationModel->deleteByDoctorId((int) $doctor['id']);
            $doctorModel->delete($doctor['id']);
        }

        $userModel->delete($id);

        return redirect()->to(site_url('admin/doctors'));
    }

    public function patients()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $specializationModel = new SpecializationModel();
        $specializations = $specializationModel->getOptionNames();

        return view('admin/patients', [
            'specializations' => $specializations,
        ]);
    }

    public function deletePatient($id)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $id = (int) $id;
        if ($id < 1) {
            return redirect()->to(site_url('admin/patients'));
        }

        $userModel        = new UserModel();
        $patientModel     = new PatientModel();
        $appointmentModel = new AppointmentModel();

        $patient = $patientModel->findByUserId($id);
        if ($patient) {
            $appointmentModel->deleteByPatientId((int) $patient['id']);
            $patientModel->delete($patient['id']);
        }

        $userModel->delete($id);

        return redirect()->to(site_url('admin/patients'));
    }

    public function appointments()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $appointmentModel       = new AppointmentModel();
        $data['appointments']   = $appointmentModel->getAdminAppointments();

        return view('admin/appointments', $data);
    }

    public function appointmentPreview()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $patientId = (int) $this->request->getGet('patient_id');
        $rawServices = $this->request->getGet('services');
        $preferredDoctorId = (int) ($this->request->getGet('doctor_id') ?? 0);
        $preferredDoctorName = trim((string) ($this->request->getGet('doctor_name') ?? ''));
        $requestedDate = (string) ($this->request->getGet('date') ?? '');
        $requestedTime = (string) ($this->request->getGet('time') ?? '');

        $serviceData = $this->normalizeServiceIds($rawServices);
        $serviceIds = $serviceData['ids'];

        $patient = null;
        if ($patientId > 0) {
            $patient = \Config\Database::connect()
                ->table('patients p')
                ->select("
                    p.id as patient_id,
                    p.user_id,
                    p.phone,
                    p.dob,
                    p.gender as patient_gender,
                    u.first_name,
                    u.last_name,
                    u.email,
                    u.gender as user_gender,
                    u.status,
                    CASE
                        WHEN u.formatted_user_id IS NULL
                             OR u.formatted_user_id = ''
                             OR u.formatted_user_id LIKE 'PHY%'
                        THEN CONCAT('PT', LPAD(u.id, 7, '0'))
                        ELSE u.formatted_user_id
                    END as formatted_user_id
                ")
                ->join('users u', 'u.id = p.user_id', 'left')
                ->where('p.id', $patientId)
                ->get()
                ->getRowArray();
        }

        $serviceMap = [];
        $specializationModel = new SpecializationModel();
        foreach ($specializationModel->findAll() as $specialization) {
            $id = (int) ($specialization['id'] ?? 0);
            $name = trim((string) ($specialization['name'] ?? ''));
            if ($id > 0 && $name !== '') {
                $serviceMap[$id] = $name;
            }
        }

        $serviceNames = [];
        foreach ($serviceIds as $serviceId) {
            if (isset($serviceMap[$serviceId])) {
                $serviceNames[] = $serviceMap[$serviceId];
            }
        }

        if ($preferredDoctorName === '' && $preferredDoctorId > 0) {
            $preferredDoctorName = \Config\Database::connect()
                ->table('doctors d')
                ->select("TRIM(CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,''))) AS doctor_name")
                ->join('users u', 'u.id = d.user_id', 'left')
                ->where('d.id', $preferredDoctorId)
                ->get()
                ->getRowArray()['doctor_name'] ?? '';
            $preferredDoctorName = trim((string) $preferredDoctorName);
        }

        return view('admin/appointment_preview', [
            'patient'        => $patient,
            'patient_id'     => $patientId,
            'services'       => $serviceIds,
            'service_names'  => array_values(array_unique($serviceNames)),
            'date'           => $requestedDate,
            'time'           => $requestedTime,
            'doctor'         => $preferredDoctorName,
            'preview_id'     => 0,
        ]);
    }

    public function deleteAppointment($id)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $id = (int) $id;
        if ($id < 1) {
            return redirect()->to(site_url('admin/appointments'))->with('error', 'Invalid appointment.');
        }

        $appointmentModel = new AppointmentModel();
        $bookingModel = new DoctorBookingModel();
        $appointment = $appointmentModel->find($id);

        if (! $appointment) {
            return redirect()->to(site_url('admin/appointments'))->with('error', 'Appointment not found.');
        }

        // Update booking status if exists
        $bookingModel->where('appointment_id', $id)->set(['status' => 'cancelled'])->update();

        // ...existing code...
        $previewModel = new \App\Models\AppointmentPreviewModel();
        $previewModel
            ->where('patient_id', (int) ($appointment['patient_id'] ?? 0))
            ->where('assigned_doctor_id', (int) ($appointment['doctor_id'] ?? 0))
            ->where('requested_date', $appointment['appointment_date'] ?? null)
            ->where('requested_time', $appointment['appointment_time'] ?? null)
            ->set([
                'status' => 'cancelled',
            ])
            ->update();

        $appointmentModel->delete($id);

        $logModel = new ActivityLogModel();
        $logModel->log('delete_appointment', 'Admin deleted appointment ID ' . $id, 'appointment', $id);

        return redirect()->to(site_url('admin/appointments'))->with('success', 'Appointment cleared successfully.');
    }

    public function updateAppointmentStatus()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $id = (int) $this->request->getPost('appointment_id');
        $status = $this->request->getPost('status');

        if ($id < 1 || empty($status)) {
            return $this->response->setJSON(['success' => false, 'message' => 'Invalid data']);
        }

        $appointmentModel = new AppointmentModel();
        $bookingModel = new DoctorBookingModel();
        
        $appointment = $appointmentModel->find($id);
        if (!$appointment) {
            return $this->response->setJSON(['success' => false, 'message' => 'Appointment not found']);
        }

        $status = AppointmentModel::normalizeStatus($status);
        
        $db = \Config\Database::connect();
        $db->transStart();

        $appointmentModel->update($id, ['status' => $status]);
        
        // Sync with doctor_bookings table
        $bookingModel->where('appointment_id', $id)->set(['status' => $status])->update();

        $db->transComplete();

        if ($db->transStatus() === false) {
            return $this->response->setJSON(['success' => false, 'message' => 'Failed to update status']);
        }

        $logModel = new ActivityLogModel();
        $logModel->log('update_appointment_status', "Admin updated appointment ID {$id} status to {$status}", 'appointment', $id);

        return $this->response->setJSON(['success' => true, 'message' => 'Status updated successfully']);
    }

    public function createAppointment()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        // Check if this is a JSON request or form request
        $isJson = $this->request->isAJAX() && stripos($this->request->getHeaderLine('Content-Type'), 'json') !== false;
        
        if ($isJson) {
            return $this->createAppointmentFromJson();
        }

        // Get the appointment_datetime and split it
        $appointmentDateTime = $this->request->getPost('appointment_datetime');
        
        if(!$appointmentDateTime){
            return redirect()->back()
                ->withInput()
                ->with('error','Appointment date and time are required');
        }

        // Split the datetime-local format (YYYY-MM-DDTHH:mm)
        $parts = explode('T', $appointmentDateTime);
        $appointmentDate = $parts[0] ?? null;
        $appointmentTime = isset($parts[1]) ? $this->normalizeAppointmentTime($parts[1]) : null;

        if(!$appointmentDate || !$appointmentTime){
            return redirect()->back()
                ->withInput()
                ->with('error','Invalid appointment date and time format');
        }

        if ($this->isPastAppointmentDateTime($appointmentDate, $appointmentTime)) {
            return redirect()->back()
                ->withInput()
                ->with('error', 'Past date or time booking is not allowed.');
        }

        $appointmentModel = new AppointmentModel();

        $serviceInput = $this->request->getPost('services');
        $serviceData = $this->normalizeServiceIds($serviceInput);
        if($serviceData['ids'] === []){
            return redirect()->back()
                ->withInput()
                ->with('error','Please select at least one service');
        }

        if ($serviceData['has_duplicates']) {
            return redirect()->back()
                ->withInput()
                ->with('error', 'Duplicate services are not allowed. Please select each service only once.');
        }

        $patientId = (int)$this->request->getPost('patient_id');
        $doctorId = $this->request->getPost('doctor_id') ? (int)$this->request->getPost('doctor_id') : null;
        $note = $this->request->getPost('note');

        $data = [
            'patient_id' => $patientId,
            'doctor_id'  => $doctorId,
            'services' => json_encode($serviceData['ids']),
            'created_by' => session()->get('id'),
            'appointment_date' => $appointmentDate,
            'appointment_time' => $appointmentTime,
            'note' => !empty($note) ? trim($note) : null
        ];

        // Only check for slot availability if doctor is selected
        if($doctorId){
            if (! $this->doctorHasAvailabilityAt($doctorId, $appointmentDate, $appointmentTime)) {
                return redirect()->back()
                    ->withInput()
                    ->with('error', 'Doctor is not available at the selected time.');
            }

            $taken = $appointmentModel
                ->where('doctor_id',$doctorId)
                ->where('appointment_date',$appointmentDate)
                ->where('appointment_time',$appointmentTime)
                ->whereIn('status',['pending','approved'])
                ->first();

            if($taken){
                return redirect()->back()
                    ->withInput()
                    ->with('error','Slot already booked');
            }
        }

        if($appointmentModel->insert($data)){
            // Log activity
            $activityLogModel = new ActivityLogModel();
            $activityLogModel->log(
                'appointment_created',
                'Appointment created for patient ID ' . $data['patient_id'] . ' with services ' . $data['services'],
                null,
                null,
                session()->get('id')
            );

            return redirect()
                ->to(site_url('admin/appointments'))
                ->with('success','Appointment created successfully');
        } else {
            return redirect()->back()
                ->withInput()
                ->with('error','Failed to create appointment');
        }
    }

    private function createAppointmentFromJson()
    {
        $json = $this->request->getJSON();
        
        $patientId = (int) ($json->patient_id ?? 0);
        $doctorId = (int) ($json->doctor_id ?? 0);
        $appointmentDate = (string) ($json->appointment_date ?? '');
        $appointmentTime = $this->normalizeAppointmentTime((string) ($json->appointment_time ?? ''));
        $note = (string) ($json->note ?? '');
        $serviceData = $this->normalizeServiceIds($json->services ?? []);

        if (!$patientId || !$doctorId || !$appointmentDate || !$appointmentTime) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Invalid appointment data'
            ]);
        }

        if ($this->isPastAppointmentDateTime($appointmentDate, $appointmentTime)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Past date or time booking is not allowed'
            ]);
        }

        if ($serviceData['has_duplicates']) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Duplicate services are not allowed'
            ]);
        }

        $appointmentModel = new AppointmentModel();
        $bookingModel = new DoctorBookingModel();
        $patientModel = new PatientModel();
        $doctorModel = new DoctorModel();

        // Verify patient exists
        $patient = $patientModel->find($patientId);
        if (!$patient) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Patient not found'
            ]);
        }

        // Verify doctor exists
        $doctor = $doctorModel->find($doctorId);
        if (!$doctor) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Doctor not found'
            ]);
        }

        if (! $this->doctorHasAvailabilityAt($doctorId, $appointmentDate, $appointmentTime)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Doctor is not available at the selected time'
            ]);
        }

        // Check if doctor is already booked using the source of truth (doctor_bookings table)
        if ($bookingModel->isDoctorBooked($doctorId, $appointmentDate, $appointmentTime)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Doctor already booked for this time'
            ]);
        }

        $data = [
            'patient_id' => $patientId,
            'doctor_id' => $doctorId,
            'appointment_date' => $appointmentDate,
            'appointment_time' => $appointmentTime,
            'status' => 'assigned',
            'note' => !empty($note) ? trim($note) : null
        ];

        if ($serviceData['ids'] !== []) {
            $data['services'] = json_encode($serviceData['ids']);
        }

        if ($appointmentModel->insert($data)) {
            $appointmentId = (int) $appointmentModel->getInsertID();
            
            // Sync with doctor_bookings table
            $bookingModel->syncBooking($appointmentId, $doctorId, $appointmentDate, $appointmentTime, 'assigned', (int) session()->get('id'));
            $previewId = (int) ($json->preview_id ?? 0);

            if ($previewId > 0) {
                $doctorUser = $doctor['user_id'] ? (new UserModel())->find((int) $doctor['user_id']) : null;
                $assignedDoctorFormattedId = $doctorUser && ! empty($doctorUser['formatted_user_id'])
                    ? $doctorUser['formatted_user_id']
                    : 'PHY' . str_pad((string) ($doctor['user_id'] ?: $doctorId), 7, '0', STR_PAD_LEFT);

                // ...existing code...
                $previewModel->update($previewId, [
                    'assigned_doctor_id'            => $doctorId,
                    'assigned_doctor_formatted_id'  => $assignedDoctorFormattedId,
                    'status'                        => 'booked',
                ]);
            }

            // Log activity
            $activityLogModel = new ActivityLogModel();
            $activityLogModel->log(
                'appointment_created',
                'Appointment created for patient ID ' . $patientId . ' with doctor ID ' . $doctorId,
                'appointment',
                $appointmentId,
                session()->get('id')
            );

            return $this->response->setJSON([
                'success' => true,
                'message' => 'Appointment created successfully'
            ]);
        } else {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'Failed to create appointment'
            ]);
        }
    }

    public function addDoctor()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $specializationModel = new SpecializationModel();

        return view('admin/add_doctor', [
            'specializationOptions' => $specializationModel->getOptionNames(),
            'isEdit'                => false,
        ]);
    }

   public function addPatient($patientId = null)
{
    if ($r = $this->requireRole('admin')) {
        return $r;
    }

    return view('admin/add_patient', [
        'isEdit'     => false,
        'patient_id' => $patientId
    ]);
}

    public function editDoctor($encryptedId)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }
        $id = decrypt_id($encryptedId);
        if (! ctype_digit((string) $id)) {
            return redirect()->to(site_url('admin/doctors'))->with('error', 'Invalid doctor link.');
        }
        $doctorData = $this->getDoctorFormData((int) $id);
        if (! $doctorData) {
            return redirect()->to(site_url('admin/doctors'))->with('error', 'Doctor not found.');
        }

        return view('admin/add_doctor', array_merge($doctorData, [
            'isEdit' => true,
        ]));
    }

    public function editPatient($encryptedId)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $id = decrypt_id($encryptedId);
        if (! ctype_digit((string) $id)) {
            return redirect()->to(site_url('admin/patients'))->with('error', 'Invalid patient link.');
        }

        $patientData = $this->getPatientFormData((int) $id);
        if (! $patientData) {
            return redirect()->to(site_url('admin/patients'))->with('error', 'Patient not found.');
        }

        return view('admin/add_patient', array_merge($patientData, [
            'isEdit' => true,
        ]));
    }

    public function storeDoctor()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $rules = [
            'first_name'        => 'required|min_length[2]|max_length[50]|alpha_space',
            'last_name'         => 'required|min_length[2]|max_length[50]|alpha_space',
            'email'             => 'required|valid_email|is_unique[users.email]',
            'gender'            => 'required|in_list[Male,Female,Other]',
            'experience_years'  => 'permit_empty|integer|greater_than_equal_to[0]|less_than_equal_to[60]',
            'experience_months' => 'permit_empty|integer|greater_than_equal_to[0]|less_than_equal_to[11]',
            'fees'              => 'permit_empty|decimal',
        ];
        
        if (! $this->validate($rules)) {
            return redirect()->back()->withInput();
        }

        $userModel   = new UserModel();
        $doctorModel = new DoctorModel();
        $specializationModel = new SpecializationModel();
        $doctorSpecializationModel = new DoctorSpecializationModel();
        $db          = \Config\Database::connect();

        $firstName = trim((string) $this->request->getPost('first_name'));
        $lastName  = trim((string) $this->request->getPost('last_name'));
        $specializationInput = $this->request->getPost('specialization');

        $specializations = $this->parseSpecializations($specializationInput);

        if ($specializations === []) {
            return redirect()->back()->withInput()->with('error', 'Please select at least one specialization.');
        }

        $specializationValue = implode(', ', $specializations);
        if (strlen($specializationValue) > 191) {
            return redirect()->back()->withInput()->with('error', 'Selected specializations are too long. Please reduce them.');
        }

        $yearsRaw  = $this->request->getPost('experience_years');
        $monthsRaw = $this->request->getPost('experience_months');
        $years     = ($yearsRaw === '' || $yearsRaw === null) ? 0 : (int) $yearsRaw;
        $months    = ($monthsRaw === '' || $monthsRaw === null) ? 0 : (int) $monthsRaw;

        $experience = $this->buildExperienceString($years, $months);
        $generatedPassword = $this->generateAccountPassword();

        $db->transStart();

        $userData = [
            'first_name' => $firstName,
            'last_name'  => $lastName,
            'email'      => trim((string) $this->request->getPost('email')),
            'gender'     => trim((string) $this->request->getPost('gender')),
            'password'   => password_hash($generatedPassword, PASSWORD_DEFAULT),
            'role'       => 'doctor',
            'status'     => 'active',
        ];

        if (! $userModel->skipValidation(true)->insert($userData)) {
            $db->transRollback();

            return redirect()->back()->withInput()->with('error', 'Could not create doctor login account.');
        }

        $userId = (int) $userModel->getInsertID();

        $doctorRow = [
            'user_id'        => $userId,
            'specialization' => $specializationValue,
            'experience'     => $experience,
            'fees'           => $this->request->getPost('fees') !== '' && $this->request->getPost('fees') !== null
                ? $this->request->getPost('fees')
                : null,
        ];

        if (! $doctorModel->skipValidation(true)->insert($doctorRow)) {
            $db->transRollback();

            return redirect()->back()->withInput()->with('error', 'Could not create doctor profile.');
        }

        $doctorId           = (int) $doctorModel->getInsertID();
        $specializationMap  = $specializationModel->ensureNamesExist($specializations);
        $specializationIds  = array_values($specializationMap);
        $doctorSpecializationModel->syncDoctorSpecializations($doctorId, $specializationIds, (int) session()->get('id'));

        $db->transComplete();

        if (! $db->transStatus()) {
            return redirect()->back()->withInput()->with('error', 'Transaction failed.');
        }

        $logModel = new ActivityLogModel();
        $logModel->log('create_doctor', "Admin created doctor {$firstName} {$lastName}", 'user', $userId);

        return redirect()->to(site_url('admin/doctors'))->with('success', 'Doctor account created. Generated password: ' . $generatedPassword);
    }

    public function storePatient()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $rules = [
            'first_name' => 'required|min_length[2]|max_length[50]|alpha_space',
            'last_name'  => 'required|min_length[2]|max_length[50]|alpha_space',
            'email'      => 'required|valid_email|is_unique[users.email]',
            'phone'      => 'required|min_length[10]|max_length[15]',
            'dob'        => 'permit_empty|valid_date[Y-m-d]',
            'gender'     => 'permit_empty|in_list[male,female,other]',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput();
        }

        if (! $this->validateDobInput()) {
            return redirect()->back()->withInput()->with('error', 'Please enter a valid date of birth that is not in the future.');
        }

        $userModel    = new UserModel();
        $patientModel = new PatientModel();
        $db           = \Config\Database::connect();

        $firstName         = trim((string) $this->request->getPost('first_name'));
        $lastName          = trim((string) $this->request->getPost('last_name'));
        $generatedPassword = $this->generateAccountPassword();
        $dobRaw            = trim((string) $this->request->getPost('dob'));

        $db->transStart();

        $userData = [
            'first_name' => $firstName,
            'last_name'  => $lastName,
            'email'      => trim((string) $this->request->getPost('email')),
            'password'   => password_hash($generatedPassword, PASSWORD_DEFAULT),
            'role'       => 'patient',
            'status'     => 'active',
        ];

        if (! $userModel->skipValidation(true)->insert($userData)) {
            $db->transRollback();

            return redirect()->back()->withInput()->with('error', 'Could not create patient login account.');
        }

        $userId = (int) $userModel->getInsertID();

        $patientRow = [
            'user_id' => $userId,
            'dob'     => $dobRaw !== '' ? $dobRaw : null,
            'gender'  => $this->request->getPost('gender') !== '' ? $this->request->getPost('gender') : null,
            'phone'   => trim((string) $this->request->getPost('phone')),
        ];

        if (! $patientModel->skipValidation(true)->insert($patientRow)) {
            $db->transRollback();

            return redirect()->back()->withInput()->with('error', 'Could not create patient profile.');
        }

        $db->transComplete();

        if (! $db->transStatus()) {
            return redirect()->back()->withInput()->with('error', 'Transaction failed.');
        }

        $logModel = new ActivityLogModel();
        $logModel->log('create_patient', "Admin created patient {$firstName} {$lastName}", 'user', $userId);

        return redirect()->to(site_url('admin/patients'))->with('success', 'Patient account created. Generated password: ' . $generatedPassword);
    }

    public function updateDoctor($encryptedId)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }
        $id = decrypt_id($encryptedId);
        if (! ctype_digit((string) $id)) {
            return redirect()->to(site_url('admin/doctors'))->with('error', 'Invalid doctor link.');
        }
        $userId      = (int) $id;
        $doctorData  = $this->getDoctorFormData($userId);
        if (! $doctorData) {
            return redirect()->to(site_url('admin/doctors'))->with('error', 'Doctor not found.');
        }

        $rules = [
            'first_name'        => 'required|min_length[2]|max_length[50]|alpha_space',
            'last_name'         => 'required|min_length[2]|max_length[50]|alpha_space',
            'email'             => 'required|valid_email|is_unique[users.email,id,' . $userId . ']',
            'gender'            => 'required|in_list[Male,Female,Other]',
            'experience_months' => 'required|integer|greater_than_equal_to[0]|less_than_equal_to[11]',
            'experience_years'  => 'required|integer|greater_than_equal_to[0]|less_than_equal_to[60]',
            'fees'              => 'permit_empty|decimal',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput();
        }

        $userModel                 = new UserModel();
        $doctorModel               = new DoctorModel();
        $specializationModel       = new SpecializationModel();
        $doctorSpecializationModel = new DoctorSpecializationModel();
        $db                        = \Config\Database::connect();

        $firstName             = trim((string) $this->request->getPost('first_name'));
        $lastName              = trim((string) $this->request->getPost('last_name'));
        $specializationInput   = $this->request->getPost('specialization');
        $specializations       = $this->parseSpecializations($specializationInput);

        if ($specializations === []) {
            return redirect()->back()->withInput()->with('error', 'Please select at least one specialization.');
        }

        $specializationValue = implode(', ', $specializations);
        if (strlen($specializationValue) > 191) {
            return redirect()->back()->withInput()->with('error', 'Selected specializations are too long. Please reduce them.');
        }

        $yearsRaw   = $this->request->getPost('experience_years');
        $monthsRaw  = $this->request->getPost('experience_months');
        $years      = ($yearsRaw === '' || $yearsRaw === null) ? 0 : (int) $yearsRaw;
        $months     = ($monthsRaw === '' || $monthsRaw === null) ? 0 : (int) $monthsRaw;
        $experience = $this->buildExperienceString($years, $months);

        $db->transStart();

        $userModel->update($userId, [
            'first_name' => $firstName,
            'last_name'  => $lastName,
            'email'      => trim((string) $this->request->getPost('email')),
            'gender'     => trim((string) $this->request->getPost('gender')),
        ]);

        $doctorModel->update($doctorData['doctor']['id'], [
            'specialization' => $specializationValue,
            'experience'     => $experience,
            'fees'           => $this->request->getPost('fees') !== '' && $this->request->getPost('fees') !== null
                ? $this->request->getPost('fees')
                : null,
        ]);

        $specializationMap = $specializationModel->ensureNamesExist($specializations);
        $doctorSpecializationModel->syncDoctorSpecializations((int) $doctorData['doctor']['id'], array_values($specializationMap), (int) session()->get('id'));

        $db->transComplete();

        if (! $db->transStatus()) {
            return redirect()->back()->withInput()->with('error', 'Could not update doctor.');
        }

        $logModel = new ActivityLogModel();
        $logModel->log('update_doctor', "Admin updated doctor {$firstName} {$lastName}", 'user', $userId);

        return redirect()->to(site_url('admin/doctors'))->with('success', 'Doctor updated successfully.');
    }

    public function updatePatient($encryptedId)
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $id = decrypt_id($encryptedId);
        if (! ctype_digit((string) $id)) {
            return redirect()->to(site_url('admin/patients'))->with('error', 'Invalid patient link.');
        }

        $userId      = (int) $id;
        $patientData = $this->getPatientFormData($userId);
        if (! $patientData) {
            return redirect()->to(site_url('admin/patients'))->with('error', 'Patient not found.');
        }

        $rules = [
            'first_name' => 'required|min_length[2]|max_length[50]|alpha_space',
            'last_name'  => 'required|min_length[2]|max_length[50]|alpha_space',
            'email'      => 'required|valid_email|is_unique[users.email,id,' . $userId . ']',
            'phone'      => 'required|min_length[10]|max_length[15]',
            'dob'        => 'permit_empty|valid_date[Y-m-d]',
            'gender'     => 'permit_empty|in_list[male,female,other]',
        ];

        if (! $this->validate($rules)) {
            return redirect()->back()->withInput();
        }

        if (! $this->validateDobInput()) {
            return redirect()->back()->withInput()->with('error', 'Please enter a valid date of birth that is not in the future.');
        }

        $userModel    = new UserModel();
        $patientModel = new PatientModel();
        $db           = \Config\Database::connect();

        $firstName = trim((string) $this->request->getPost('first_name'));
        $lastName  = trim((string) $this->request->getPost('last_name'));
        $dobRaw    = trim((string) $this->request->getPost('dob'));

        $db->transStart();

        $userModel->update($userId, [
            'first_name' => $firstName,
            'last_name'  => $lastName,
            'email'      => trim((string) $this->request->getPost('email')),
        ]);

        $patientModel->update($patientData['patient']['id'], [
            'dob'    => $dobRaw !== '' ? $dobRaw : null,
            'gender' => $this->request->getPost('gender') !== '' ? $this->request->getPost('gender') : null,
            'phone'  => trim((string) $this->request->getPost('phone')),
        ]);

        $db->transComplete();

        if (! $db->transStatus()) {
            return redirect()->back()->withInput()->with('error', 'Could not update patient.');
        }

        $logModel = new ActivityLogModel();
        $logModel->log('update_patient', "Admin updated patient {$firstName} {$lastName}", 'user', $userId);

        return redirect()->to(site_url('admin/patients'))->with('success', 'Patient updated successfully.');
    }
    public function checkEmail()
    {
        $email      = (string) $this->request->getPost('email');
        $excludeId  = (int) $this->request->getPost('exclude_id');

        $userModel = new UserModel();

        return $this->response->setJSON([
            'exists' => $userModel->emailExistsExcept($email, $excludeId > 0 ? $excludeId : null)
        ]);
    }
    public function getDoctors()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        helper('encryption');

        $doctorModel = new DoctorModel();
        $doctors     = $doctorModel->getAdminDoctorList('doctor_id', 'asc');
        $payload     = [];

        foreach ($doctors as $doc) {
            $payload[] = [
                'doctor_id'         => (int) ($doc->doctor_id ?? 0),
                'user_id'           => (int) ($doc->user_id ?? 0),
                'formatted_user_id' => $doc->formatted_user_id ?? null,
                'name'              => $doc->name ?? null,
                'email'             => $doc->email ?? null,
                'specialization'    => $doc->specialization ?? null,
                'experience'        => $doc->experience ?? null,
                'fees'              => $doc->fees ?? null,
                'status'            => $doc->status ?? null,
                'edit_token'        => encrypt_id($doc->user_id ?? 0),
            ];
        }

        return $this->response->setJSON([
            'data' => $payload,
        ]);
    }

    public function getPatients()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        helper('encryption');

        $patientModel = new PatientModel();
        $patients     = $patientModel->getAdminPatientList('patient_id', 'asc');
        $payload      = [];

        foreach ($patients as $patient) {
            $payload[] = [
                'user_id'           => (int) ($patient->user_id ?? 0),
                'formatted_user_id' => $patient->formatted_user_id ?? null,
                'name'              => $patient->name ?? null,
                'email'             => $patient->email ?? null,
                'phone'             => $patient->phone ?? null,
                'status'            => $patient->status ?? null,
                'edit_token'        => encrypt_id($patient->user_id ?? 0),
            ];
        }

        return $this->response->setJSON([
            'data' => $payload,
        ]);
    }
    public function getAvailableDoctorsForPatient()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        helper('encryption');

        if (! $this->request->isAJAX()) {
            return $this->response->setStatusCode(403);
        }

        $date = $this->request->getPost('date');
        $time = $this->request->getPost('time');
        $previewId = (int) $this->request->getPost('preview_id');
        $specializationIds = $this->request->getPost('specialization_id');

        if (is_string($specializationIds)) {
            $decoded = json_decode($specializationIds, true);
            if (is_array($decoded)) {
                $specializationIds = $decoded;
            } elseif ($specializationIds !== '') {
                $specializationIds = preg_split('/\s*,\s*/', $specializationIds, -1, PREG_SPLIT_NO_EMPTY);
            } else {
                $specializationIds = [];
            }
        }

        if (! is_array($specializationIds) || empty($specializationIds)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'No specialization selected',
                'data'    => [],
                'doctors' => [],
            ]);
        }

        $specializationIds = array_values(array_filter(array_map('intval', $specializationIds), fn ($id) => $id > 0));

        if (empty($specializationIds)) {
            return $this->response->setJSON([
                'success' => false,
                'message' => 'No valid specialization selected',
                'data'    => [],
                'doctors' => [],
            ]);
        }

        $dayNumber = null;
        $requestedTime = $this->normalizeAppointmentTime((string) $time);
        if (! empty($date)) {
            try {
                $dateObj = new \DateTime($date);
                $dayNumber = (int) $dateObj->format('N');
            } catch (\Exception $e) {
                $dayNumber = null;
            }
        }

        $db = \Config\Database::connect();

        // Get doctors with that specialization
        $builder = $db->table('doctors d');

        $builder->select("\n            d.id,\n            d.user_id,\n            d.experience,\n            CONCAT(u.first_name,' ',u.last_name) as name,\n            u.gender\n            ");
        $builder->join('users u', 'u.id=d.user_id');
        $builder->join('doctor_specializations ds', 'ds.doctor_id=d.id');
        $builder->whereIn('ds.specialization_id', $specializationIds);
        $builder->groupBy(['d.id', 'd.user_id', 'd.experience', 'u.first_name', 'u.last_name', 'u.gender']);
        $builder->orderBy('u.first_name', 'ASC');

        $doctors = $builder->get()->getResultArray();

        $appointmentModel = new AppointmentModel();
        $bookingModel     = new DoctorBookingModel();
        $availableDoctors = [];

        foreach ($doctors as $doctor) {

            // Format doctor ID as PHY + 7-digit user_id (from users table)
            $formattedDoctorId = null;
            if (!empty($doctor['user_id'])) {
                $formattedDoctorId = 'PHY' . str_pad($doctor['user_id'], 7, '0', STR_PAD_LEFT);
            } else {
                $formattedDoctorId = 'PHY' . str_pad($doctor['id'], 7, '0', STR_PAD_LEFT);
            }
            $doctor['formatted_doctor_id'] = $formattedDoctorId;
            $doctor['doctor_edit_token'] = encrypt_id($doctor['user_id'] ?? 0);

            $doctor['is_available'] = false;
            $doctor['status'] = 'No Slots';
            $doctor['time_slots'] = [];
            $doctor['available_days'] = [];
            $doctor['available_days_text'] = 'No availability';

            $availableDayRows = $db->table('doctor_availability da')
                ->distinct()
                ->select('da.day_number, days.day_name')
                ->join('days', 'days.day_number = da.day_number', 'left')
                ->where('da.doctor_id', $doctor['id'])
                ->where('da.status', 1)
                ->orderBy('da.day_number', 'ASC')
                ->get()
                ->getResultArray();

            foreach ($availableDayRows as $availableDay) {
                $availableDayNumber = (int) ($availableDay['day_number'] ?? 0);
                $dayName = trim((string) ($availableDay['day_name'] ?? ''));

                if ($dayName === '') {
                    $dayName = [
                        1 => 'Monday',
                        2 => 'Tuesday',
                        3 => 'Wednesday',
                        4 => 'Thursday',
                        5 => 'Friday',
                        6 => 'Saturday',
                        7 => 'Sunday',
                    ][$availableDayNumber] ?? '';
                }

                if ($dayName !== '') {
                    $doctor['available_days'][] = $dayName;
                }
            }

            $doctor['available_days'] = array_values(array_unique($doctor['available_days']));
            if ($doctor['available_days'] !== []) {
                $doctor['available_days_text'] = implode(', ', $doctor['available_days']);
            }

            // Fetch ALL availability slots for this doctor across ALL days
            $allSlotsBuilder = $db->table('doctor_availability da')
                ->select('da.start_time, da.end_time, da.day_number, days.day_name')
                ->join('days', 'days.day_number = da.day_number', 'left')
                ->where('da.doctor_id', $doctor['id'])
                ->where('da.status', 1)
                ->orderBy('da.day_number', 'ASC')
                ->orderBy('da.start_time', 'ASC');

            $allSlots = $allSlotsBuilder->get()->getResultArray();
            $doctor['all_slots'] = [];

            foreach ($allSlots as $slot) {
                $dayName = trim((string) ($slot['day_name'] ?? ''));
                if ($dayName === '') {
                    $dayName = [
                        1 => 'Monday', 2 => 'Tuesday', 3 => 'Wednesday',
                        4 => 'Thursday', 5 => 'Friday', 6 => 'Saturday', 7 => 'Sunday',
                    ][$slot['day_number']] ?? 'Unknown';
                }

                $doctor['all_slots'][] = [
                    'day' => $dayName,
                    'start_time' => $slot['start_time'],
                    'end_time' => $slot['end_time'],
                    'start_time_formatted' => date('h:i A', strtotime($slot['start_time'])),
                    'end_time_formatted' => date('h:i A', strtotime($slot['end_time'])),
                ];
            }

            // Fetch availability slots for this doctor on the SPECIFIED day and time for booking status
            if ($dayNumber !== null) {
                $availabilityBuilder = $db->table('doctor_availability')
                    ->where('doctor_id', $doctor['id'])
                    ->where('day_number', $dayNumber)
                    ->where('status', 1)
                    ->orderBy('start_time', 'ASC');

                if ($requestedTime !== null) {
                    $availabilityBuilder
                        ->where('start_time <=', $requestedTime)
                        ->where('end_time >', $requestedTime);
                }

                $availability = $availabilityBuilder->get()->getResultArray();

                if ($availability) {
                    foreach ($availability as $slot) {
                        $doctor['time_slots'][] = [
                            'start_time' => $slot['start_time'],
                            'end_time' => $slot['end_time'],
                            'start_time_formatted' => date('h:i A', strtotime($slot['start_time'])),
                            'end_time_formatted' => date('h:i A', strtotime($slot['end_time'])),
                        ];
                    }
                }
            }

            $doctor['is_available'] = $doctor['time_slots'] !== [];
            
            if ($doctor['is_available']) {
                $doctor['status'] = 'Available';
            } else {
                $doctor['status'] = 'Not Available';
            }

            if ($doctor['is_available'] && $requestedTime !== null && ! empty($date)) {
                if ($bookingModel->isDoctorBooked($doctor['id'], $date, $requestedTime)) {
                    $doctor['is_available'] = false;
                    $doctor['status'] = 'Booked';
                }
            }

            $availableDoctors[] = $doctor;
        }

        // Fetch specializations for all available doctors
        if (!empty($availableDoctors)) {
            foreach ($availableDoctors as &$doc) {
                $doc['specializations'] = $db->table('doctor_specializations ds')
                    ->select('s.name, s.bg_color, s.text_color')
                    ->join('specializations s', 's.id = ds.specialization_id')
                    ->where('ds.doctor_id', $doc['id'])
                    ->get()
                    ->getResultArray();
            }
        }

        if ($previewId > 0) {
            $availableDaysSnapshot = [];
            $availableSlotsSnapshot = [];

            foreach ($availableDoctors as $doctor) {
                $doctorId = (int) ($doctor['id'] ?? 0);
                if ($doctorId < 1) {
                    continue;
                }

                $snapshotKey = (string) ($doctor['formatted_doctor_id'] ?? $doctorId);
                $availableDaysSnapshot[$snapshotKey] = [
                    'doctor_id' => $doctorId,
                    'name'      => $doctor['name'] ?? null,
                    'days'      => $doctor['available_days'] ?? [],
                ];
                $availableSlotsSnapshot[$snapshotKey] = [
                    'doctor_id' => $doctorId,
                    'name'      => $doctor['name'] ?? null,
                    'slots'     => $doctor['time_slots'] ?? [],
                ];
            }

            // ...existing code...
            $previewModel->update($previewId, [
                'available_days_snapshot'   => $availableDaysSnapshot,
                'available_slots_snapshot'  => $availableSlotsSnapshot,
                'status'                    => 'viewed',
            ]);
        }

        return $this->response->setJSON([
            'success'    => true,
            'message'    => 'Doctors loaded successfully',
            'day_number' => $dayNumber,
            'data'       => $availableDoctors,
            'doctors'    => $availableDoctors,
        ]);
    }

    public function checkDoctorStatus()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        $doctorId = (int) $this->request->getPost('doctor_id');
        $date = $this->request->getPost('date');
        $time = $this->request->getPost('time');

        if ($doctorId <= 0 || empty($date) || empty($time)) {
            return $this->response->setJSON(['status' => 'Available']);
        }

        $requestedTime = $this->normalizeAppointmentTime((string) $time);
        
        $bookingModel = new DoctorBookingModel();
        $isBooked = $bookingModel->isDoctorBooked($doctorId, $date, $requestedTime);

        return $this->response->setJSON([
            'status' => $isBooked ? 'Booked' : 'Available'
        ]);
    }

    public function getSpecializations()
    {
        if ($r = $this->requireRole('admin')) {
            return $r;
        }

        if (!$this->request->isAJAX()) {
            return $this->response->setStatusCode(403);
        }

        $specializationModel = new SpecializationModel();
        $specializations = $specializationModel->orderBy('name', 'ASC')->findAll();

        return $this->response->setJSON([
            'success' => true,
            'specializations' => $specializations
        ]);
    }
    public function appointmentForm($patientId)
{
    if ($r = $this->requireRole('admin')) {
        return $r;
    }

    $patientModel = new PatientModel();
    $userModel    = new UserModel();

    $patient = $patientModel->findByUserId((int)$patientId);

    if (!$patient) {
        return redirect()
            ->to(site_url('admin/patients'))
            ->with('error','Patient not found');
    }

    $user = $userModel->find((int)$patientId);

    $formattedPatientId =
    (!empty($user['formatted_user_id']) &&
     strpos($user['formatted_user_id'],'PHY') !== 0)

    ? $user['formatted_user_id']

    : 'PT' . str_pad($user['id'],7,'0',STR_PAD_LEFT);

    return view('admin/add_appointment',[
        'patient'            => $patient,
        'user'               => $user,
        'formattedPatientId' => $formattedPatientId
    ]);
}
public function doctorSearch()
{
    if ($r = $this->requireRole('admin')) {
        return $r;
    }

    $doctorModel         = new \App\Models\DoctorModel();
    $availabilityModel   = new \App\Models\AvailabilityModel();
    $specializationModel = new \App\Models\SpecializationModel();

    $specializations = $specializationModel->select('name')->orderBy('name', 'ASC')->findAll();

    $doctors = $doctorModel
        ->select('doctors.id, doctors.user_id, doctors.specialization, doctors.experience, doctors.fees, users.first_name, users.last_name')
        ->join('users', 'users.id = doctors.user_id')
        ->findAll();
    $dayNumberToNextDate = [];
    for ($i = 1; $i <= 7; $i++) {
        $todayDayNum = (int) date('N');
        $daysAhead   = ($i - $todayDayNum + 7) % 7;
        $dayNumberToNextDate[$i] = date('Y-m-d', strtotime("+{$daysAhead} days"));
    }

    $doctorsData = [];
    foreach ($doctors as $doctor) {
        $db = \Config\Database::connect();
        $doctorSpecializations = $db->table('doctor_specializations ds')
        ->select('s.name,s.bg_color,s.text_color')
        ->join('specializations s','s.id=ds.specialization_id')
        ->where('ds.doctor_id',$doctor['id'])
        ->get()
        ->getResultArray();

        // Create fresh instance for each doctor to avoid query builder state issues
        $availabilityModel = new \App\Models\AvailabilityModel();
        
        $availability = $availabilityModel
            ->where('doctor_id', $doctor['id'])
            ->where('status', 1)
            ->orderBy('day_number', 'ASC')
            ->orderBy('start_time', 'ASC')
            ->findAll();

        // Log for debugging
        log_message('info', "Doctor: {$doctor['first_name']} {$doctor['last_name']} (ID: {$doctor['id']}) - Availability records: " . count($availability));

        $timeSlots        = [];
        $availabilityByDate = [];

        foreach ($availability as $avail) {
            $dayNum = (int) $avail['day_number'];

            // ✅ CORRECT: map the weekday number to the next real calendar date
            $date = $dayNumberToNextDate[$dayNum]
                    ?? date('Y-m-d', strtotime("+{$dayNum} days")); // fallback

            // Human-readable label e.g. "Mon, Apr 28"
            $dateLabel = date('D, M j', strtotime($date));

            // Safely format the start time
            $startTimeFormatted = false;
            if (!empty($avail['start_time'])) {

                $parsed = strtotime($avail['start_time']);

                if ($parsed !== false && !empty($avail['end_time'])) {

                    $endParsed = strtotime($avail['end_time']);

                    if($endParsed !== false){

                        $timeSlots[] = [
                            'day'   => date('D', strtotime($date)),
                            'start' => date('g:ia', $parsed),
                            'end'   => date('g:ia', $endParsed)
                        ];

                    }
                }
            }

            if (!isset($availabilityByDate[$date])) {
                $availabilityByDate[$date] = [
                    'label'  => $dateLabel,
                    'day'    => $dayNum,
                    'slots'  => [],
                ];
            }

            // Only add slot if times were parsed successfully
            if ($parsed !== false && !empty($avail['end_time'])) {
                $endParsed = strtotime($avail['end_time']);
                if ($endParsed !== false) {
                    $availabilityByDate[$date]['slots'][] = [
                        'start'    => date('h:i A', $parsed),
                        'end'      => date('h:i A', $endParsed),
                        'start_24' => date('H:i', $parsed),
                        'end_24'   => date('H:i', $endParsed),
                    ];
                }
            }
        }

        // Sort by date ascending so the nearest day shows first
        ksort($availabilityByDate);

        $doctorsData[] = [
            'id'               => $doctor['id'],
            'first_name'       => $doctor['first_name'],
            'last_name'        => $doctor['last_name'],
            'specializations' => $doctorSpecializations,
            'experience'       => $doctor['experience'],
            'fees'             => $doctor['fees'],
            'timeSlots'        => array_slice($timeSlots, 0, 4),
            'extraSlots'       => array_slice($timeSlots, 4),
            'availabilityByDate' => $availabilityByDate,
            'hasAvailability'  => !empty($availability), // explicit flag for the view
            'avatar'           => strtoupper(
                                    substr($doctor['first_name'] ?? '', 0, 1) .
                                    substr($doctor['last_name']  ?? '', 0, 1)
                                  ),
        ];
    }

    return view('admin/search_doctors', [
        'doctors'         => $doctorsData,
        'specializations' => $specializations,
    ]);
} 
    public function appointmentsData()
    {
        $appointmentModel    = new \App\Models\AppointmentModel();
        $specializationModel = new \App\Models\SpecializationModel();

        $appointments = $appointmentModel->getAdminAppointments(); // better than findAll()
        $specializations = $specializationModel->findAll();
        $serviceMap = [];

        foreach ($specializations as $specialization) {
            $serviceMap[(int) ($specialization['id'] ?? 0)] = trim((string) ($specialization['name'] ?? ''));
        }

        $data = [];

        foreach ($appointments as $a) {
            $serviceIds = json_decode((string) ($a->services ?? '[]'), true);
            $serviceNames = [];
            $doctorName = trim((string) ($a->doctor_name ?? ''));

            if (is_array($serviceIds)) {
                foreach ($serviceIds as $serviceId) {
                    $serviceId = (int) $serviceId;
                    if ($serviceId > 0 && ! empty($serviceMap[$serviceId])) {
                        $serviceNames[] = $serviceMap[$serviceId];
                    }
                }
            }

            $data[] = [
                'id' => $a->id,
                'sl_no' => 'APT' . str_pad((string) ($a->id ?? 0), 7, '0', STR_PAD_LEFT),
                'patient_id' => $a->patient_id,
                'services' => $serviceIds,
                'patient_name' => $a->patient_name,
                'service_request' => $serviceNames !== [] ? implode(', ', array_unique($serviceNames)) : 'Not Specified',
                'doctor_id' => (int) ($a->doctor_id ?? 0),
                'doctor_name' => $doctorName !== '' ? $doctorName : 'Not Assigned',
                'appointment_date' => $a->appointment_date,
                'appointment_time' => $a->appointment_time,
                'status' => $a->status ?: 'pending'
            ];
        }

        return $this->response->setJSON(['data' => $data]);
    }
    // ...existing code...
}