package com.gnx.telemedicine.repository;

import com.gnx.telemedicine.model.Appointment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalTime;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, UUID> {

    @Query("SELECT DISTINCT a FROM Appointment a " +
           "LEFT JOIN FETCH a.patient p " +
           "LEFT JOIN FETCH p.user " +
           "LEFT JOIN FETCH a.doctor d " +
           "LEFT JOIN FETCH d.user " +
           "WHERE a.id = :id")
    Optional<Appointment> findById(@Param("id") UUID id);

    @Query("SELECT DISTINCT a FROM Appointment a " +
           "LEFT JOIN FETCH a.patient p " +
           "LEFT JOIN FETCH p.user " +
           "LEFT JOIN FETCH a.doctor d " +
           "LEFT JOIN FETCH d.user " +
           "WHERE a.doctor.id = :doctorId " +
           "AND (a.deletedByDoctor IS NULL OR a.deletedByDoctor = false)")
    List<Appointment> findByDoctorId(UUID doctorId);

    @Query("SELECT DISTINCT a FROM Appointment a " +
           "LEFT JOIN FETCH a.patient p " +
           "LEFT JOIN FETCH p.user " +
           "LEFT JOIN FETCH a.doctor d " +
           "LEFT JOIN FETCH d.user " +
           "WHERE a.patient.id = :patientId " +
           "AND (a.deletedByPatient IS NULL OR a.deletedByPatient = false)")
    List<Appointment> findByPatientId(UUID patientId);

    @Query(value = "SELECT * FROM appointments a WHERE a.doctor_id = CAST(:doctorId AS UUID) " +
           "AND a.scheduled_date = CAST(:date AS DATE) " +
           "AND a.status != 'CANCELLED' " +
           "AND (a.deleted_by_doctor IS NULL OR a.deleted_by_doctor = false) " +
           "AND (a.deleted_by_patient IS NULL OR a.deleted_by_patient = false) " +
           "AND ((a.scheduled_time < CAST(:endTime AS TIME) AND " +
           "(a.scheduled_time + (a.duration_minutes || ' minutes')::INTERVAL) > CAST(:startTime AS TIME)))",
           nativeQuery = true)
    List<Appointment> findOverlappingAppointments(
            @Param("doctorId") UUID doctorId,
            @Param("date") LocalDate date,
            @Param("startTime") LocalTime startTime,
            @Param("endTime") LocalTime endTime
    );

    @Query("SELECT a FROM Appointment a WHERE a.doctor.id = :doctorId " +
           "AND a.scheduledDate = :date " +
           "AND a.status != 'CANCELLED' " +
           "AND (a.deletedByDoctor IS NULL OR a.deletedByDoctor = false) " +
           "AND (a.deletedByPatient IS NULL OR a.deletedByPatient = false)")
    List<Appointment> findByDoctorIdAndDate(
            @Param("doctorId") UUID doctorId,
            @Param("date") LocalDate date
    );

    @Query("SELECT a FROM Appointment a WHERE a.patient.id = :patientId " +
           "AND a.scheduledDate = :date " +
           "AND a.status != 'CANCELLED' " +
           "AND (a.deletedByPatient IS NULL OR a.deletedByPatient = false) " +
           "AND (a.deletedByDoctor IS NULL OR a.deletedByDoctor = false)")
    List<Appointment> findByPatientIdAndDate(
            @Param("patientId") UUID patientId,
            @Param("date") LocalDate date
    );

    @Query("SELECT DISTINCT a FROM Appointment a " +
           "LEFT JOIN FETCH a.patient p " +
           "LEFT JOIN FETCH p.user " +
           "LEFT JOIN FETCH a.doctor d " +
           "LEFT JOIN FETCH d.user")
    List<Appointment> findAllWithRelations();
    
    // Find all appointments between a specific doctor and patient
    @Query("SELECT a FROM Appointment a WHERE a.doctor.id = :doctorId AND a.patient.id = :patientId " +
           "AND (a.deletedByDoctor IS NULL OR a.deletedByDoctor = false) " +
           "AND (a.deletedByPatient IS NULL OR a.deletedByPatient = false)")
    List<Appointment> findByDoctorIdAndPatientId(@Param("doctorId") UUID doctorId, @Param("patientId") UUID patientId);
}
