CREATE OR REPLACE algorithm=MERGE VIEW forecast_requests AS
	SELECT
		request.*,
		date_cancelled AS forecast_date_cancelled,
		(CASE WHEN TIME(date_issued) < 730 THEN
				DATE_SUB(date_issued, INTERVAL 1 DAY)
			ELSE
				date_issued
		END) as forecast_date_issued,
		job_type.include_issued,
		job_type.include_cancelled,
		(
			status.forecast AND job_category.forecast AND job_type.forecast
		) as forecast,
		COALESCE(fm_4s.ophours, fm_3s.ophours, fm_2s.ophours, fm_1s.ophours) as ophours
	FROM
		requests request
		JOIN operating_areas op_area
			ON op_area.id = request.operating_area_id
		JOIN statuses status
			ON (
				status.id = request.status_id
			)
		JOIN job_categories job_category
			ON (
				job_category.id = request.job_category_id
			)
		JOIN job_types job_type
			ON (
				job_type.id = request.job_type_id
			)
		LEFT JOIN forecast_mapping fm_4s
			ON (
				fm_4s.region_id = op_area.op_region_id
				AND fm_4s.job_category_id = request.job_category_id
				AND fm_4s.job_type_id = request.job_type_id
				AND fm_4s.zone_id = request.zone_id
			)
		LEFT JOIN forecast_mapping fm_3s
			ON (
				fm_3s.region_id = op_area.op_region_id
				AND fm_3s.job_category_id = request.job_category_id
				AND fm_3s.job_type_id = request.job_type_id
				AND fm_3s.zone_id IS NULL
			)
		LEFT JOIN forecast_mapping fm_2s
			ON (
				fm_2s.region_id = op_area.op_region_id
				AND fm_2s.job_category_id = request.job_category_id
				AND fm_2s.job_type_id IS NULL
				AND fm_2s.zone_id IS NULL
			)
		LEFT JOIN forecast_mapping fm_1s
			ON (
				fm_1s.region_id = op_area.op_region_id
				AND fm_1s.job_category_id IS NULL
				AND fm_1s.job_type_id IS NULL
				AND fm_1s.zone_id IS NULL
			);