package com.iemr.flw.repo.identity;


import com.iemr.flw.domain.identity.*;
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.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
import java.util.stream.Stream;

@Repository
public interface BeneficiaryRepo extends JpaRepository<RMNCHBeneficiaryDetailsRmnch, Long> {

    @Query(nativeQuery = true, value = " select UserName from db_iemr.m_user where  UserID = :userId and deleted is false ")
    String getUserName(@Param("userId") Integer userId);

    Optional<RMNCHBeneficiaryDetailsRmnch> findById(Long benID);

    List<RMNCHBeneficiaryDetailsRmnch> findByHouseoldId(Long houseHoldId);
    List<RMNCHBeneficiaryDetailsRmnch> findByBenficieryid(Long benID);

    @Query(value = "SELECT beneficiaryRegID FROM db_identity.i_beneficiarydetails_rmnch WHERE BeneficiaryId = :benId", nativeQuery = true)
    Long getBenRegIdFromBenId(@Param("benId") Long benId);

    @Query(nativeQuery = true, value = " SELECT userid FROM db_iemr.m_user WHERE UserName = :userName ")
    Integer getUserIDByUserName(@Param("userName") String userName);


    @Query("SELECT t FROM RMNCHMBeneficiaryaddress t " +
            "WHERE t.createdDate BETWEEN :fromDate AND :toDate " +
            "AND t.createdBy = :userName")
    Page<RMNCHMBeneficiaryaddress> getBenDataWithinDates(
            @Param("userName") String userName,
            @Param("fromDate") Timestamp fromDate,
            @Param("toDate") Timestamp toDate,
            Pageable pageable);

    @Query(value = " SELECT t FROM RMNCHMBeneficiaryaddress t WHERE t.createdBy = :userName ")
    Page<RMNCHMBeneficiaryaddress> getBenDataByUser(@Param("userName") String userName, Pageable pageable);

    // BenAddressId can have more than one mapping row (confirmed live: address-ID collisions
    // between old/test registrations and real ones). Order by PK desc, same "latest wins"
    // convention already used for findByBenRegIdFromMapping, so callers picking .get(0) get
    // the newest mapping deterministically instead of whichever row the DB returns first.
    @Query(value = " SELECT t FROM RMNCHMBeneficiarymapping t WHERE t.benAddressId = :addressID ORDER BY t.benMapId DESC")
    List<RMNCHMBeneficiarymapping> getByAddressID(@Param("addressID") BigInteger addressID);

    @Query(value = " SELECT t FROM RMNCHMBeneficiarymapping t WHERE t.benRegId = :BenRegId")
    RMNCHMBeneficiarymapping getById(@Param("BenRegId") BigInteger BenRegId);

    // NOTE: these 5 lookups take the row's real primary key (BenDetailsId/BenAccountID/
    // BenImageId/BenAddressId/BenContactsId, sourced from RMNCHMBeneficiarymapping) as their
    // argument. They used to query WHERE t.id = :vanSerialNo — but each entity's "id" field is
    // mapped to the VanSerialNo column (a per-van sync counter that repeats across different
    // vans), not the real @Id primary key. Passing a real PK value into a VanSerialNo comparison
    // either matched the wrong row by coincidence, or — whenever that VanSerialNo happened to be
    // shared by another row (common; confirmed live via IncorrectResultSizeDataAccessException:
    // "Query did not return a unique result: 2 results were returned") — threw outright. Fixed to
    // query the actual primary key column on each entity instead.
    @Query(value = " SELECT t FROM RMNCHMBeneficiarydetail t WHERE t.beneficiaryDetailsId = :benDetailsId")
    RMNCHMBeneficiarydetail getDetailsById(@Param("benDetailsId") BigInteger benDetailsId);

    @Query(value = " SELECT t FROM RMNCHMBeneficiaryAccount t WHERE t.benAccountID = :benAccountId")
    RMNCHMBeneficiaryAccount getAccountById(@Param("benAccountId") BigInteger benAccountId);

    @Query(value = " SELECT t FROM RMNCHMBeneficiaryImage t WHERE t.benImageId = :benImageId")
    RMNCHMBeneficiaryImage getImageById(@Param("benImageId") Long benImageId);

    @Query(value = " SELECT t FROM RMNCHMBeneficiaryaddress t WHERE t.benAddressID = :benAddressId")
    RMNCHMBeneficiaryaddress getAddressById(@Param("benAddressId") BigInteger benAddressId);

    @Query(value = " SELECT t FROM RMNCHMBeneficiarycontact t WHERE t.benContactsID = :benContactsId")
    RMNCHMBeneficiarycontact getContactById(@Param("benContactsId") BigInteger benContactsId);

    @Query(value = " SELECT t.beneficiaryID FROM RMNCHMBeneficiaryregidmapping t  WHERE t.benRegId = :benRegID ")
    BigInteger getBenIdFromRegID(@Param("benRegID") Long benRegID);

    // BenRegId has no unique constraint on i_beneficiarydetails_rmnch — a beneficiary can have
    // more than one row (confirmed live: duplicates, some created well after the original with
    // a household link the older row never got). Order by PK desc so callers picking .get(0)
    // get the latest row, not whichever one the DB happens to return first.
    @Query(value = " SELECT t FROM RMNCHBeneficiaryDetailsRmnch t WHERE t.BenRegId =:benRegID ORDER BY t.beneficiaryDetails_RmnchId DESC ")
    List<RMNCHBeneficiaryDetailsRmnch> getDetailsByRegID(@Param("benRegID") Long benRegID);

    @Query(value = " SELECT t FROM RMNCHBornBirthDetails t WHERE t.BenRegId =:benRegID ")
    RMNCHBornBirthDetails getBornBirthByRegID(@Param("benRegID") Long benRegID);

    @Query(" SELECT t.benRegId FROM RMNCHMBeneficiaryregidmapping t  WHERE t.beneficiaryID = :benID ")
    Long getRegIDFromBenId(@Param("benID") Long benID);


    @Query(nativeQuery = true, value = " SELECT HealthIdNumber,HealthID  FROM db_iemr.m_benhealthidmapping WHERE BeneficiaryRegID = :benRegId ")
	Object[] getBenHealthIdNumber(@Param("benRegId") BigInteger benRegId);

    @Query(nativeQuery = true, value = " SELECT HealthIdNumber,HealthID  FROM db_iemr.m_benhealthidmapping WHERE HealthIdNumber = :HealthIdNumber ")
    Object[] getHealthIdNumber(@Param("HealthIdNumber") String  HealthIdNumber);
    
    @Query(nativeQuery = true, value = " SELECT HealthID,HealthIdNumber,isNewAbha FROM db_iemr.t_healthid WHERE HealthIdNumber = :healthIdNumber ")
    ArrayList<Object[]> getBenHealthDetails(@Param("healthIdNumber") String healthIdNumber);

    // Same rows FHIR-API getBenhealthID reads, so the worklist can fill healthId/healthIdNumber without an HTTP call
    @Query(nativeQuery = true, value = " SELECT HealthID, HealthIdNumber FROM db_iemr.m_benhealthidmapping WHERE BeneficiaryRegID = :benRegId ")
    List<Object[]> getHealthIdMappingRows(@Param("benRegId") Long benRegId);

    @Query(nativeQuery = true, value = " SELECT HealthId FROM db_iemr.t_healthid WHERE HealthIdNumber = :healthIdNumber ")
    List<String> getHealthIdByNumber(@Param("healthIdNumber") String healthIdNumber);

    // benRegId has no unique constraint on i_beneficiarymapping — a beneficiary can have
    // more than one mapping row, so this must return a list, not assume a single result.
    @Query("SELECT b FROM RMNCHMBeneficiarymapping b WHERE b.benRegId = :benRegId ORDER BY b.benMapId DESC")
    List<RMNCHMBeneficiarymapping> findByBenRegIdFromMapping(@Param("benRegId") BigInteger benRegId);


    @Query("SELECT d FROM RMNCHMBeneficiarydetail d WHERE d.beneficiaryDetailsId = :beneficiaryDetailsId")
    RMNCHMBeneficiarydetail findByBeneficiaryDetailsId(@Param("beneficiaryDetailsId") BigInteger beneficiaryDetailsId);

    @Query("SELECT d FROM RMNCHMBeneficiarydetail d WHERE d.BenRegId = :benRegID")
    RMNCHMBeneficiarydetail getDetailByBenRegID(@Param("benRegID") BigInteger benRegID);


    // BeneficiaryRepo — replaces 3 separate queries per beneficiary
    @Query(value = """
    SELECT 
        ibd.BeneficiaryId    AS benId,
        bd.firstName         AS firstName,
        bd.lastName          AS lastName
    FROM db_identity.i_beneficiarydetails_rmnch ibd
    JOIN db_identity.i_beneficiarymapping bm 
        ON bm.BenRegId = ibd.BeneficiaryRegID
    JOIN db_identity.i_beneficiarydetails bd 
        ON bd.beneficiaryDetailsId = bm.BenDetailsId
    WHERE ibd.BeneficiaryId IN :benIds
    """, nativeQuery = true)
    List<Object[]> findBenNamesByBenIds(@Param("benIds") List<Long> benIds);

// BeneficiaryRepo — replaces the per-row getBenIdFromRegID call
@Query("SELECT b.BenRegId, b.benficieryid " +
        "FROM RMNCHBeneficiaryDetailsRmnch b " +
        "WHERE b.BenRegId IN :regIds AND b.benficieryid IS NOT NULL")
List<Object[]> getBenIdsFromRegIDs(@Param("regIds") List<Long> regIds);

    // Village worklist (Stop TB path) — paginated at the DB level via LIMIT/OFFSET,
    // replacing the old approach of loading every flow row for the whole village into
    // memory and slicing it in Java. Cost is now O(pageSize) instead of O(village size)
    // on every page request, including page 0. See BeneficiaryServiceImpl.getBenData().
    @Query(value = """
        SELECT bm.BenAddressId
        FROM db_iemr.i_ben_flow_outreach bfs
        JOIN db_identity.i_beneficiarymapping bm ON bm.BenRegId = bfs.beneficiary_reg_id
        WHERE bfs.providerServiceMapID = :psmId AND bfs.villageID = :villageId AND bfs.deleted = 0
        ORDER BY bfs.registrationDate DESC
        LIMIT :pageSize OFFSET :offset
        """, nativeQuery = true)
    List<BigInteger> getVillageWorklistAddressIds(
            @Param("psmId") Integer psmId,
            @Param("villageId") Integer villageId,
            @Param("pageSize") int pageSize,
            @Param("offset") int offset);

    @Query(value = """
        SELECT COUNT(*)
        FROM db_iemr.i_ben_flow_outreach bfs
        JOIN db_identity.i_beneficiarymapping bm ON bm.BenRegId = bfs.beneficiary_reg_id
        WHERE bfs.providerServiceMapID = :psmId AND bfs.villageID = :villageId AND bfs.deleted = 0
        """, nativeQuery = true)
    long countVillageWorklist(@Param("psmId") Integer psmId, @Param("villageId") Integer villageId);

    @Query("SELECT t FROM RMNCHMBeneficiaryaddress t WHERE t.benAddressID IN :ids")
    List<RMNCHMBeneficiaryaddress> findAddressesByIds(@Param("ids") List<BigInteger> ids);

    @Query("SELECT b.BenRegId, b.benficieryid " +
            "FROM RMNCHBeneficiaryDetailsRmnch b " +
            "WHERE b.BenRegId IN :regIds AND b.benficieryid IS NOT NULL")
    List<Object[]> getBenIdsFromRegIds(@Param("regIds") List<Long> regIds);
}