Create table "HWCPatient" ("HWCPatientId" serial primary key, "HWCName" varchar(250), "Description" text, "LocationId" int references "Location"("LocationId"),
 "Active" boolean default true,"CreatedBy" int references "Account"("AccountId"),"CreatedDate" timestamp without time zone,"ModifiedBy" int references "Account"("AccountId"),
 "ModifiedDate" timestamp without time zone);

insert into "LogType"("LogTypeId","LogTypeName","Active") values(50,'HWCPatients',true);

alter table "Patient" add column "HWCPatientId" int references "HWCPatient"("HWCPatientId");

Alter table "HWCPatient" add column "RowColor" text;

------------------
 drop function "udf_FetchPatients_For_QuickSchedule"(character varying,character varying,character varying);
 

CREATE OR REPLACE FUNCTION public."udf_FetchPatients_For_QuickSchedule"(filter character varying DEFAULT NULL::text, "patientMobile" 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, "Active" boolean, "ThumbnailUrl" text, "AppointmentDate" timestamp without time zone, 
 "isActiveAppointmentExists" boolean, "isActiveAdmissionExists" boolean,"HWCName" varchar,"HWCPatientId" int,"Description" text,"RowColor" text)
 LANGUAGE plpgsql
AS $function$
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 '%' OR pat."UMRNo"  ilike'%'  OR pat."Mobile" ILIKE '%')|| "filter"||'%' end
	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 "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 '%' OR pat."UMRNo"  ilike'%'  OR pat."Mobile" ILIKE '%') || "filter"||'%' end
	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 "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."ThumbnailUrl"  ,
    pat."Active"  ,
(CASE WHEN pat."ThumbnailUrl" IS NOT NULL THEN CONCAT('https://hims-devv.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", HWC."HWCName",HWC."HWCPatientId", HWC."Description", HWC."RowColor"
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"
left join "HWCPatient"  HWC on HWC."HWCPatientId" = pat."HWCPatientId"
 WHERE  pat."Active" IS TRUE 
--and	case when "filter" is null then 1=1 else (TRIM(UPPER(pat."FullName")) ILIKE '%' OR pat."UMRNo"  ilike'%'  OR pat."Mobile" ILIKE '%')|| "filter"||'%' end
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 "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
$function$
;

-------------

