DROP FUNCTION IF EXISTS public."udf_FetchPatientsFilter_For_Appointment"(character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION public."udf_FetchPatientsFilter_For_Appointment"(
	filter character varying DEFAULT NULL::text,
	"patientMobile" character varying DEFAULT NULL::text,
	"uMRNo" character varying DEFAULT NULL::text,
	"patientName" character varying DEFAULT NULL::text)
    RETURNS TABLE("PatientId" integer, "Salutation" character varying, "FirstName" text, "MiddleName" text, "LastName" text, "FullName" text, "DateOfBirth" date, "Age" smallint, "Gender" character, "UMRNo" character varying, "Email" character varying, "Mobile" character varying, "CountryId" integer, "ProfileImageUrl" text,"TempPatient" boolean, "HowDidYouKnowId" integer, "Active" boolean, "PaymentStatus" boolean, "ThumbnailUrl" text, "AppointmentDate" timestamp without time zone, "isActiveAppointmentExists" boolean, "isActiveAdmissionExists" boolean) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN

return query

with cts as (
select row_number() over(partition by a."PatientId" order by  a."AppointmentId" desc, a."AppointmentDate" desc )"Max" ,a."PatientId",a."AppointmentId" from "Appointment" a
join  "Patient" pat  on  a."PatientId"	=pat."PatientId"
where a."Active"=true 	 
	and case when "filter" is null then 1=1 else  TRIM(UPPER(pat."FullName")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(pat."UMRNo")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(pat."Mobile")) ilike'%'|| "filter"||'%'  end
	and	case when "uMRNo" is null then 1=1 else  pat."UMRNo"  ilike'%'|| "uMRNo"||'%' end
	and	case when "patientMobile" is null then 1=1 else  pat."Mobile"  ilike'%'|| "patientMobile"||'%' end
	and	case when "patientName" is null then 1=1 else  pat."FullName"  ilike'%'|| "patientName"||'%' end
)
, maxapt as (
select a.* from "Appointment" a
join cts on a."AppointmentId"=cts."AppointmentId"
where "Max"=1)
,ctsadmission as (	
select distinct  max(a."AdmissionId") over(partition by a."PatientId"   )"MaxAdmissionId" ,a."PatientId",
	a."AppointmentId" from "Admission" a
join  "Patient" pat  on  a."PatientId"	=pat."PatientId"
where a."Active"=true 	 
	
	and case when "filter" is null then 1=1 else  TRIM(UPPER(pat."FullName")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(pat."UMRNo")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(pat."Mobile")) ilike'%'|| "filter"||'%'  end
	and	case when "uMRNo" is null then 1=1 else  pat."UMRNo"  ilike'%'|| "uMRNo"||'%' end
	and	case when "patientMobile" is null then 1=1 else  pat."Mobile"  ilike'%'|| "patientMobile"||'%' end
	and	case when "patientName" is null then 1=1 else  pat."FullName"  ilike'%'|| "patientName"||'%' end
)

, maxadmision as (
	select a.* from "Admission" a
join ctsadmission cts on a."AdmissionId"=cts."MaxAdmissionId"
	)

SELECT DISTINCT pat."PatientId" ,
    pat."Salutation"  ,
    pat."FirstName"  ,
    pat."MiddleName"  ,
    pat."LastName"  ,
    pat."FullName"  ,
    pat."DateOfBirth" ,
    pat."Age"  ,
    pat."Gender"  ,
    pat."UMRNo"  ,
    pat."Email"  ,
    pat."Mobile" ,
    pat."CountryId"  ,
    pat."ProfileImageUrl"  ,
	pat."TempPatient",
	pat."HowDidYouKnowId",
   -- pat."ThumbnailUrl"  ,
    pat."Active"  ,
	pat."PaymentStatus",
(CASE WHEN pat."ThumbnailUrl" IS NOT NULL THEN CONCAT('https://hims-qa.s3.amazonaws.com/', pat."Guid", '/', pat."ThumbnailUrl") ELSE NULL END) AS "ThumbnailUrl"
,(max(apt."AppointmentDate")over(partition by apt."PatientId")::text ||' ' || max(apt."AppointmentTime")over(partition by apt."PatientId",apt."AppointmentDate")::text)::timestamp as "AppointmentDate",
(case when (max(apt."AppointmentDate")over(partition by apt."PatientId")::text ||' ' || max(apt."AppointmentTime")over(partition by apt."PatientId")::text)::timestamp > now() at time zone 'UTC-5:30' then true else false end)"isActiveAppointmentExists"
,(case when max(adm."AdmissionId")over(partition by adm."PatientId") is  null  then false 
	   when max(adm."AdmissionId")over(partition by adm."PatientId") is not null and max(dc."DischargeId")over(partition by pat."PatientId") is  null  then true 
	   when max(adm."AdmissionId")over(partition by adm."PatientId") is not null and max(dc."DischargeId")over(partition by pat."PatientId") is not null  then false end
	  )"isActiveAdmissionExists"
FROM "Patient" pat  
left join maxapt apt on apt."PatientId" = pat."PatientId" and apt."Status" <> 'C' and apt."Active"=true
left join maxadmision adm on adm."PatientId" = pat."PatientId" and adm."Active"=true
left join "Discharge" dc on dc."AdmissionId" = adm."AdmissionId"
 WHERE  pat."Active" IS TRUE 
 
and case when "filter" is null then 1=1 else  TRIM(UPPER(pat."FullName")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(pat."UMRNo")) ilike'%'|| "filter"||'%' OR  TRIM(UPPER(pat."Mobile")) ilike'%'|| "filter"||'%'  end
and	case when "uMRNo" is null then 1=1 else  pat."UMRNo"  ilike'%'|| "uMRNo"||'%' end
and	case when "patientMobile" is null then 1=1 else  pat."Mobile"  ilike'%'|| "patientMobile"||'%' end
and	case when "patientName" is null then 1=1 else  pat."FullName"  ilike'%'|| "patientName"||'%' end
;

END
$BODY$;

ALTER FUNCTION public."udf_FetchPatientsFilter_For_Appointment"(character varying, character varying, character varying, character varying)
    OWNER TO postgres;
