package com.nebula.erp.sales.repository;

import com.nebula.erp.sales.model.Sales;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface SalesRepository extends JpaRepository<Sales, Long> {

      @Query("SELECT s FROM Sales s WHERE s.prescription_id = :prescriptionId")
      Sales findSaleByPrescriptionId(@Param("prescriptionId") String prescriptionId);

      @Query("SELECT s FROM Sales s WHERE s.tenant = :tenantName")
      Page<Sales> findAllByTenant(@Param("tenantName") String tenantName, Pageable pageable);

      @Query("SELECT s FROM Sales s WHERE s.tenant = :tenant AND (LOWER(s.customEncounterId) LIKE :keyword OR LOWER(s.patient_name) LIKE :keyword OR LOWER(s.customer_name) LIKE :keyword OR LOWER(s.customer_mobile) LIKE :keyword OR LOWER(s.payment_status) LIKE :keyword)")
      Page<Sales> searchAllSales(@Param("tenant") String tenant,
                  @Param("keyword") String keyword,
                  Pageable pageable);

      @Query("SELECT DISTINCT s FROM Sales s JOIN s.sales_items si WHERE s.tenant = :tenant AND si.type = 'product' AND (LOWER(s.customEncounterId) LIKE :keyword OR LOWER(s.patient_name) LIKE :keyword OR LOWER(s.customer_name) LIKE :keyword OR LOWER(s.customer_mobile) LIKE :keyword OR LOWER(s.payment_status) LIKE :keyword)")
      Page<Sales> searchProductSales(@Param("tenant") String tenant,
                  @Param("keyword") String keyword,
                  Pageable pageable);

      @Query("SELECT DISTINCT s FROM Sales s JOIN s.sales_items si WHERE s.tenant = :tenant AND si.type IN ('service','package','bed') AND (LOWER(s.customEncounterId) LIKE :keyword OR LOWER(s.patient_name) LIKE :keyword OR LOWER(s.customer_name) LIKE :keyword OR LOWER(s.customer_mobile) LIKE :keyword OR LOWER(s.payment_status) LIKE :keyword)")
      Page<Sales> searchCombinedSales(@Param("tenant") String tenant,
                  @Param("keyword") String keyword,
                  Pageable pageable);

      @Query("SELECT s FROM Sales s WHERE s.tenant = :tenant AND ((:id IS NOT NULL AND s.id = :id) OR (:amount IS NOT NULL AND s.total_amount >= :amount AND s.total_amount < :amount + 1))")
      Page<Sales> searchAllSalesByNumbers(@Param("tenant") String tenant,
                  @Param("id") Long id,
                  @Param("amount") Double amount,
                  Pageable pageable);

      @Query("SELECT DISTINCT s FROM Sales s JOIN s.sales_items si WHERE s.tenant = :tenant AND si.type = 'product' AND ((:id IS NOT NULL AND s.id = :id) OR (:amount IS NOT NULL AND s.total_amount >= :amount AND s.total_amount < :amount + 1))")
      Page<Sales> searchProductSalesByNumbers(@Param("tenant") String tenant,
                  @Param("id") Long id,
                  @Param("amount") Double amount,
                  Pageable pageable);

      @Query("SELECT DISTINCT s FROM Sales s JOIN s.sales_items si WHERE s.tenant = :tenant AND si.type IN ('service','package','bed') AND ((:id IS NOT NULL AND s.id = :id) OR (:amount IS NOT NULL AND s.total_amount >= :amount AND s.total_amount < :amount + 1))")
      Page<Sales> searchCombinedSalesByNumbers(@Param("tenant") String tenant,
                  @Param("id") Long id,
                  @Param("amount") Double amount,
                  Pageable pageable);

      @Query("SELECT s FROM Sales s WHERE s.patient_id = :patientId AND s.tenant = :tenantName")
      List<Sales> findAllByPatientIdAndTenant(@Param("patientId") String patientId,
                  @Param("tenantName") String tenantName);

      @Query("SELECT s FROM Sales s WHERE s.id = :id AND s.tenant = :tenantName")
      Optional<Sales> findByIdAndTenant(@Param("id") Long id,
                  @Param("tenantName") String tenantName);

      @Query("SELECT COUNT(s) FROM Sales s WHERE s.tenant = :tenantName")
      Integer findCountByTenant(@Param("tenantName") String tenantName);

      @Query("SELECT DISTINCT s FROM Sales s JOIN s.sales_items si WHERE s.tenant = :tenantName AND si.type IN ('service','package','bed')")
      Page<Sales> findCombinedSales(@Param("tenantName") String tenantName,
                  Pageable pageable);

      @Query("SELECT DISTINCT s FROM Sales s JOIN s.sales_items si WHERE s.tenant = :tenantName AND si.type = 'product'")
      Page<Sales> findProductSales(@Param("tenantName") String tenantName,
                  Pageable pageable);

      @Query("SELECT s FROM Sales s WHERE s.encounter_id = :encounterId AND s.tenant = :tenantName ORDER BY s.created_at DESC")
      List<Sales> findByEncounterIdAndTenantOrderByCreatedAtDesc(@Param("encounterId") String encounterId,
                  @Param("tenantName") String tenantName);

      @Query("SELECT s FROM Sales s WHERE s.encounter_id = :encounterId AND s.tenant = :tenant ORDER BY s.created_at DESC")
      List<Sales> findAllByEncounterIdAndTenant(@Param("encounterId") String encounterId,
                  @Param("tenant") String tenant);

      @Query("SELECT COALESCE(SUM(s.total_amount),0) FROM Sales s WHERE s.tenant = :tenant AND EXISTS (SELECT 1 FROM SalesItem si WHERE si.sales = s AND si.type IN ('service','package','bed'))")
      Double sumHospitalSales(@Param("tenant") String tenant);

      @Query("SELECT COALESCE(SUM(s.total_amount),0) FROM Sales s WHERE s.tenant = :tenant AND NOT EXISTS (SELECT 1 FROM SalesItem si WHERE si.sales = s AND si.type IN ('service','package','bed'))")
      Double sumPharmacySales(@Param("tenant") String tenant);

      @Query("SELECT s FROM Sales s WHERE s.patient_id = :patientId AND s.tenant = :tenant AND s.payment_status IN ('UNPAID','PARTIAL') AND s.id <> :currentSaleId ORDER BY s.created_at ASC")
      List<Sales> findPendingSalesForSettlement(@Param("patientId") String patientId,
                  @Param("tenant") String tenant,
                  @Param("currentSaleId") Long currentSaleId);

}