pacientes que poderiam ter se agendado pela condicao de idade mas até hoje não se agendaram

lista_3_sem_agendamento.sql

with imunizados as (
select
	d.patient_id
from
	doses d
left join vaccines v on
	v.id = d.vaccine_id
where
	(
	d.sequence_number = 1
		and v.name in ('Janssen')
)
	or (
	d.sequence_number = 2
		and v.name not in ('Janssen')
)
),
condicao_idade as (
select
	c.min_age
from
	conditions c
left join conditions_groups cg on
	cg.condition_id = c.id
where
	cg is null
	and c.min_age is not null
	and c.can_schedule is true
	and c.start_at < now()
order by
	c.min_age asc
limit 1
)
select
	p.id,
	p.name,
	p.phone,
	p.other_phone,
	date_part('year', age(p.birthday))::int as "idade"
from
	patients p
left join doses d on
	d.patient_id = p.id
left join appointments a on
	a.patient_id = p.id
where
	p.id not in (
	select
		patient_id
	from
		imunizados)
	-- nunca se agendou
	and a is null
	-- não tem nenhuma dose registrada (obviamente ja que nunca se agendou, mas re-verificando por seguranca)
	and d is null
	and date_part('year', age(p.birthday))::int >= (
	select
		min_age
	from
		condicao_idade)
order by
	date_part('year', age(p.birthday))::int desc