Untitled

SET @dateFrom := '2022-04-12';
SET @dateUntil := '2022-04-13';

select
	avs.id,
	str_to_date(concat(calendar_table.d, ' ', avs.timeslot_start), '%Y-%m-%d %H:%i') as date_start,
	date_add(
		str_to_date(concat(calendar_table.d, ' ', avs.timeslot_end), '%Y-%m-%d %H:%i'),
		interval avs.timeslot_interval minute
	) as date_end,
	avc.capacity as finetuned_capacity,
	avs.schedule_capacity,
	(
		case
			when
				s.shadow_parent_id = 0
					then
						max_number_of_people_per_booking
			else
				(
					select 
						max_number_of_people_per_booking
					from 
						services
					where 
						services.id = s.shadow_parent_id
				)
		end
	) as service_capacity
from
	availability_schedule as avs
inner join 
	calendar_table on calendar_table.weekday = if (avs.day_of_week = 7, 0, avs.day_of_week)
left join 
	availability_capacity as avc on avc.availability_schedule_id = avs.id and avc.schedule_date = calendar_table.d
inner 
	join services as s on s.id = (if (avc.id is null, avs.service_id, avc.service_id))
left join
	availability_schedule_staffs on availability_schedule_staffs.availability_schedule_id = avs.id
left join
	availability_schedule_resources on availability_schedule_resources.availability_schedule_id = avs.id
inner join
	business on business.id = avs.business_id
inner join
	locations on locations.business_id = business.id
where
	avs.deleted_at is null
	and avs.shadow_enabled = 1
	and (calendar_table.d >= @dateFrom and calendar_table.d <= @dateUntil)
	and avs.id not in (
		-- Remove dates where the schedule is closed
		select 
			availability_schedule_id
		from 
			availability_schedule_closed
		where 
			availability_schedule_id = avs.id 
			and availability_schedule_closed.schedule_date = calendar_table.d
			and (
				availability_schedule_closed.is_schedule_closed = 1 
				or availability_schedule_closed.is_schedule_disabled = 1
			)
	)
	and (
		-- Remove dates where the staff has a blockout
		case
			when
				availability_schedule_staffs.staff_id is null
					then 1
			else
				(
					select
						count(1)
					from
						staff_blockout_dates
					where
						staff_blockout_dates.blockout_date = calendar_table.d
						and staff_blockout_dates.staff_id = availability_schedule_staffs.staff_id
				) = 0
		end
	)
	and (
		-- Remove dates where the resource has a blockout
		case
			when
				availability_schedule_resources.resource_id is null
					then 1
			else
				(
					select
						count(1)
					from
						resource_blockout_dates
					where
						resource_blockout_dates.blockout_date = calendar_table.d
						and resource_blockout_dates.resource_id = availability_schedule_resources.resource_id
				) = 0
		end
	
	and (
		-- Remove dates where the locaiton has a blockout
		select
			count(1)
		from
			opening_hours_blockout_dates
		where
			opening_hours_blockout_dates.blockout_date = calendar_table.d
			and opening_hours_blockout_dates.location_id = locations.id
	) = 0
	and availability_schedule_staffs.deleted_at is null
	and availability_schedule_resources.deleted_at is null
	and s.id = 52318
	and locations.id = 6730