clinicpro/src/Doctor/Repository/DoctorRepository.php

<?php

namespace App\Doctor\Repository;

use App\Appointment\Entity\WeeklySchedule;
use App\Auth\Entity\User;
use App\Clinic\Entity\Clinic;
use App\Doctor\Entity\Doctor;
use App\Doctor\Entity\DoctorAddress;
use App\Specialty\Entity\Specialty;
use App\Specialty\Repository\SpecialtyRepository;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\Tools\Pagination\Paginator;
use Doctrine\Persistence\ManagerRegistry;

class DoctorRepository extends ServiceEntityRepository
{
    public function __construct(
        ManagerRegistry $registry,
        private readonly SpecialtyRepository $specialtyRepo,
    ) {
        parent::__construct($registry, Doctor::class);
    }

    /**
     * شرطِ «این پزشک دست‌کم یکی از این تخصص‌ها را دارد» به‌شکل زیرکوئری مستقل.
     *
     * هر فیلترِ مربوط به تخصص alias خودش را می‌گیرد. اگر همه روی یک alias بنشینند،
     * DQL مجبور می‌شود یک ردیفِ join همهٔ شرط‌ها را با هم ارضا کند و ترکیبِ دو فیلتر
     * بی‌صدا نتیجه را تنگ می‌کند.
     */
    private function hasAnySpecialty(string $alias, string $param): string
    {
        return sprintf(
            'EXISTS(SELECT %1$s.id FROM %2$s %1$s WHERE %1$s MEMBER OF d.specialties AND %1$s.id IN (:%3$s))',
            $alias,
            Specialty::class,
            $param,
        );
    }

    public function findByUuid(string $uuid): ?Doctor
    {
        return $this->findOneBy(['uuid' => $uuid]);
    }

    public function findByUser(User $user): ?Doctor
    {
        return $this->findOneBy(['user' => $user]);
    }

    public function findOneByMobile(string $mobile): ?Doctor
    {
        return $this->createQueryBuilder('d')
            ->join('d.user', 'u')
            ->where('u.mobileNumber = :mobile')
            ->setParameter('mobile', $mobile)
            ->setMaxResults(1)
            ->getQuery()
            ->getOneOrNullResult();
    }

    public function findWithFilters(array $filters): array
    {
        $page  = max(1, (int) ($filters['page'] ?? 1));
        $limit = min(50, max(1, (int) ($filters['limit'] ?? 10)));
        $sort  = strtoupper($filters['sort'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';

        // Location comes from the doctor's own address (doctor_addresses), plus a
        // fallback to the clinic address for doctors listed under a clinic.
        $qb = $this->createQueryBuilder('d')
            ->leftJoin('d.specialties', 's')
            ->leftJoin(DoctorAddress::class, 'da', Join::WITH, 'da.doctor = d')
            ->distinct();

        if (!empty($filters['state_id'])) {
            $stateId   = (int) $filters['state_id'];
            $clinicIds = $this->doctorIdsViaClinicLocation('province', $stateId);
            $orX = $qb->expr()->orX('IDENTITY(da.province) = :state');
            if ($clinicIds) {
                $orX->add('d.id IN (:stateClinicDoctorIds)');
                $qb->setParameter('stateClinicDoctorIds', $clinicIds);
            }
            $qb->andWhere($orX)->setParameter('state', $stateId);
        }
        if (!empty($filters['city_id'])) {
            $cityId    = (int) $filters['city_id'];
            $clinicIds = $this->doctorIdsViaClinicLocation('city', $cityId);
            $orX = $qb->expr()->orX('IDENTITY(da.city) = :city');
            if ($clinicIds) {
                $orX->add('d.id IN (:cityClinicDoctorIds)');
                $qb->setParameter('cityClinicDoctorIds', $clinicIds);
            }
            $qb->andWhere($orX)->setParameter('city', $cityId);
        }
        // «این تخصص» یعنی خودش و همهٔ زیرشاخه‌هایش. تا امروز این فقط به‌خاطر عارضهٔ
        // جانبیِ expandWithAncestors هنگام ذخیره کار می‌کرد؛ پزشکی که از مسیر دیگری
        // (مثلاً import دسته‌ای) وارد شود آن والدِ denormalize‌شده را ندارد.
        //
        // زیرکوئری جداست و از alias مشترک `s` استفاده نمی‌کند: آن alias فیلتر نام
        // تخصص را هم حمل می‌کند، و دو شرط روی یک alias یعنی یک ردیفِ join باید هر دو
        // را با هم ارضا کند — پزشکی که با تخصص A فیلتر را پاس می‌کند و نام تخصص B را
        // دارد بی‌صدا حذف می‌شد.
        if (!empty($filters['specialty_id'])) {
            $qb->andWhere($this->hasAnySpecialty('sf', 'specialtyIds'))
               ->setParameter(
                   'specialtyIds',
                   $this->specialtyRepo->expandWithDescendants([(int) $filters['specialty_id']])
               );
        }
        // Scope دامنه‌ی نماینده‌ی سراسری (تزریق‌شده توسط DomainContextResolver در کنترلر).
        if (!empty($filters['representation_id'])) {
            $qb->andWhere('d.representationId = :repId')->setParameter('repId', (int) $filters['representation_id']);
        }
        if (!empty($filters['gender'])) {
            $qb->andWhere('d.gender = :gender')->setParameter('gender', $filters['gender']);
        }
        if (!empty($filters['degree'])) {
            $qb->andWhere('d.degree = :degree')->setParameter('degree', $filters['degree']);
        }
        // کادر جستجوی سایت عمومی یک فیلد بیشتر ندارد و کاربر در آن هم نام پزشک تایپ
        // می‌کند و هم نام تخصص. alias جداگانه می‌گیرد تا با فیلتر specialty_id روی یک
        // ردیفِ join گره نخورد؛ وگرنه ترکیبِ دو فیلتر بی‌صدا نتیجه را تنگ می‌کرد.
        if (!empty($filters['name'])) {
            $qb->andWhere(
                $qb->expr()->orX(
                    'd.name LIKE :name',
                    sprintf(
                        'EXISTS(SELECT sn.id FROM %s sn WHERE sn MEMBER OF d.specialties AND sn.name LIKE :name)',
                        Specialty::class,
                    ),
                )
            )->setParameter('name', '%' . $filters['name'] . '%');
        }
        // "دارای نوبت" = appointment flag on AND a weekly schedule exists with
        // online booking not disabled AND at least one active session — same
        // definition as the `active` field in Doctor::toListArray(), so
        // filter/sort match what the doctor card shows.
        $bookable = fn(string $alias): string => sprintf(
            'd.activeDoctorAppointment = true AND EXISTS(SELECT %1$s.id FROM %2$s %1$s WHERE %1$s.doctor = d'
            . ' AND (JSON_EXTRACT(%1$s.setting, \'$.meta.online_booking_enabled\') IS NULL OR JSON_EXTRACT(%1$s.setting, \'$.meta.online_booking_enabled\') != \'false\')'
            . ' AND JSON_CONTAINS(JSON_EXTRACT(%1$s.setting, \'$**.sessions[*].active\'), \'true\') = 1)',
            $alias,
            WeeklySchedule::class
        );

        if (isset($filters['active'])) {
            if ((bool) $filters['active']) {
                $qb->andWhere($bookable('wsf'));
            } else {
                // Legacy admin escape hatch: active=0 → flag explicitly off.
                $qb->andWhere('d.activeDoctorAppointment = false');
            }
        } else {
            // Public listing default: deactivated doctors (admin toggled the
            // active flag off) must never surface on the public site, even
            // without an explicit `active` filter. Bookability is a separate,
            // stricter concern handled by `active=1`.
            $qb->andWhere('d.activeDoctorAppointment = true');
        }

        // Bookable doctors always rank above non-bookable ones.
        $qb->addSelect('(CASE WHEN ' . $bookable('wss') . ' THEN 1 ELSE 0 END) AS HIDDEN bookableRank')
           ->orderBy('bookableRank', 'DESC')
           ->addOrderBy('d.doctorRate', $sort);

        $total   = (new Paginator($qb))->count();
        $results = $qb->setFirstResult(($page - 1) * $limit)
                      ->setMaxResults($limit)
                      ->getQuery()
                      ->getResult();

        return [
            'items'       => $results,
            'total'       => $total,
            'page'        => $page,
            'limit'       => $limit,
            'totalPages'  => (int) ceil($total / $limit),
        ];
    }

    /**
     * IDs of doctors who belong to a clinic whose own address (DoctorAddress with
     * doctor IS NULL) is in the given city/province. Used so doctors without a
     * direct address still surface under their clinic's location, without a
     * per-row correlated subquery on the main search.
     *
     * @return int[]
     */
    /**
     * شهر/استان دسته‌ای پزشکان برای پاسخ لیست — دو کوئری ثابت، نه یکی به‌ازای هر پزشک.
     *
     * همان قاعده‌ای که فیلتر city_id/state_id در findWithFilters اعمال می‌کند اینجا هم
     * برقرار است: اول آدرس شخصی خود پزشک، و اگر نداشت آدرس کلینیکی که عضو آن است
     * (آدرس کلینیک ردیفی از DoctorAddress با doctor IS NULL و clinicId پرشده است).
     * بدون این fallback، پزشکی که فقط از طریق کلینیک مکان دارد در فیلتر city_id
     * می‌آمد ولی در پاسخ شهرش خالی بود.
     *
     * @param Doctor[] $doctors
     * @return array<int, array{city: ?array, province: ?array}>
     */
    public function findLocationsByDoctors(array $doctors): array
    {
        $ids = array_values(array_filter(array_map(fn(Doctor $d) => $d->getId(), $doctors)));
        if (!$ids) {
            return [];
        }

        $locationFields = [
            'c.id AS cityId', 'c.uuid AS cityUuid', 'c.name AS cityName',
            'p.id AS provinceId', 'p.uuid AS provinceUuid', 'p.name AS provinceName',
        ];

        $ownRows = $this->getEntityManager()->createQueryBuilder()
            ->select('IDENTITY(da.doctor) AS doctorId', ...$locationFields)
            ->from(DoctorAddress::class, 'da')
            ->join('da.city', 'c')
            ->leftJoin('da.province', 'p')
            ->where('da.doctor IN (:ids)')
            ->setParameter('ids', $ids)
            ->getQuery()
            ->getArrayResult();

        $map = $this->indexLocationRows($ownRows, []);

        $missing = array_values(array_diff($ids, array_keys($map)));
        if ($missing) {
            $clinicRows = $this->getEntityManager()->createQueryBuilder()
                ->select('cd.id AS doctorId', ...$locationFields)
                ->from(Clinic::class, 'cl')
                ->join('cl.doctors', 'cd')
                ->join(DoctorAddress::class, 'ca', Join::WITH, 'ca.clinicId = cl.id AND ca.doctor IS NULL')
                ->join('ca.city', 'c')
                ->leftJoin('ca.province', 'p')
                ->where('cd.id IN (:ids)')
                ->setParameter('ids', $missing)
                ->getQuery()
                ->getArrayResult();

            $map = $this->indexLocationRows($clinicRows, $map);
        }

        return $map;
    }

    /** اولین مکانِ هر پزشک برنده است — پزشک چند-مطبی یک شهر اصلی می‌گیرد. */
    private function indexLocationRows(array $rows, array $map): array
    {
        foreach ($rows as $row) {
            $doctorId = (int) $row['doctorId'];
            if (isset($map[$doctorId])) {
                continue;
            }
            $map[$doctorId] = [
                'city' => [
                    'uuid'   => $row['cityUuid'],
                    'id'     => (string) $row['cityId'],
                    'name'   => $row['cityName'],
                    'parent' => $row['provinceId'] !== null ? (string) $row['provinceId'] : null,
                ],
                'province' => $row['provinceId'] !== null ? [
                    'uuid' => $row['provinceUuid'],
                    'id'   => (string) $row['provinceId'],
                    'name' => $row['provinceName'],
                ] : null,
            ];
        }

        return $map;
    }

    private function doctorIdsViaClinicLocation(string $field, int $locationId): array
    {
        $column = $field === 'city' ? 'ca.city' : 'ca.province';

        $rows = $this->getEntityManager()->createQueryBuilder()
            ->select('cd.id AS doctorId')
            ->from(Clinic::class, 'cl')
            ->join('cl.doctors', 'cd')
            ->join(DoctorAddress::class, 'ca', Join::WITH, 'ca.clinicId = cl.id AND ca.doctor IS NULL')
            ->where(sprintf('IDENTITY(%s) = :loc', $column))
            ->setParameter('loc', $locationId)
            ->getQuery()
            ->getScalarResult();

        return array_values(array_unique(array_map('intval', array_column($rows, 'doctorId'))));
    }

    public function findByClinicWithFilters(int $clinicId, array $filters): array
    {
        $page  = max(1, (int) ($filters['page'] ?? 1));
        $limit = min(50, max(1, (int) ($filters['limit'] ?? 10)));
        $sort  = strtoupper($filters['sort'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';

        // Doctor has no inverse 'clinics' relation; the ManyToMany is owned by
        // Clinic.doctors. Join Clinic and match its doctors collection to d.
        $qb = $this->createQueryBuilder('d')
            ->innerJoin(Clinic::class, 'c', Join::WITH, 'd MEMBER OF c.doctors')
            ->leftJoin('d.specialties', 's')
            ->where('c.id = :clinicId')
            ->setParameter('clinicId', $clinicId)
            ->distinct();

        if (!empty($filters['specialty'])) {
            $qb->andWhere('s.id = :specialty')->setParameter('specialty', (int) $filters['specialty']);
        }
        if (!empty($filters['gender'])) {
            $qb->andWhere('d.gender = :gender')->setParameter('gender', $filters['gender']);
        }
        if (!empty($filters['degree'])) {
            $qb->andWhere('d.degree = :degree')->setParameter('degree', $filters['degree']);
        }
        if (!empty($filters['name'])) {
            $qb->andWhere('d.name LIKE :name')->setParameter('name', '%' . $filters['name'] . '%');
        }
        if (isset($filters['active'])) {
            $qb->andWhere('d.activeDoctorAppointment = :active')
               ->setParameter('active', (bool) $filters['active']);
        }

        // Hydrate specialties in the same query so toListArray() doesn't lazy-load
        // them per doctor (N+1). fetchJoinCollection keeps LIMIT paginating by
        // doctor, not by joined rows.
        $qb->addSelect('s')
           ->orderBy('d.doctorRate', $sort)
           ->setFirstResult(($page - 1) * $limit)
           ->setMaxResults($limit);

        $paginator = new Paginator($qb, fetchJoinCollection: true);
        $total     = count($paginator);
        $results   = iterator_to_array($paginator);

        return [
            'items'      => $results,
            'total'      => $total,
            'page'       => $page,
            'limit'      => $limit,
            'totalPages' => (int) ceil($total / $limit),
        ];
    }

    public function save(Doctor $doctor, bool $flush = true): void
    {
        $this->getEntityManager()->persist($doctor);
        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    public function remove(Doctor $doctor, bool $flush = true): void
    {
        $this->getEntityManager()->remove($doctor);
        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }
}