WITH RECURSIVE date_ranges(range_from, range_to) AS (
    VALUES (
        '2018-11-19T05:00:00+09:00'::TIMESTAMP WITH TIME ZONE,
        '2018-12-19T04:59:00+09:00'::TIMESTAMP WITH TIME ZONE
    )
    UNION
        SELECT
            range_from + interval '1 day',
            range_to + interval '1 day'
        FROM date_ranges
        WHERE range_from < '2018-12-20T04:59:00+09:00'
)
SELECT
	TO_CHAR(date_ranges.range_from at time zone 'Asia/Tokyo', 'YYYY/MM/DD') AS date_resv,
	COALESCE(SUM(number_resv_by_call), 0) AS number_resv_by_call,
	COALESCE(SUM(number_resv_on_web), 0) AS number_resv_on_web,
	COALESCE(SUM(number_resv_using_cash), 0) AS number_resv_using_cash,
	COALESCE(SUM(number_resv_using_point), 0) AS number_resv_using_point,
	COALESCE(SUM(revenue_resv_by_phone), 0) AS revenue_resv_by_phone,
	COALESCE(SUM(revenue_resv_on_web + delay_option_total_cash + delay_option_total_points), 0) AS revenue_resv_on_web,
	COALESCE(SUM(revenue_resv_using_cash + delay_option_total_cash), 0) AS revenue_resv_using_cash,
	COALESCE(SUM(revenue_resv_using_point + delay_option_total_points), 0) AS revenue_resv_using_point,
	COALESCE(SUM(total_revenue + delay_option_total_cash + delay_option_total_points), 0) AS total_revenue,
	COALESCE(SUM(total_web_cash_service_fee), 0) AS total_web_cash_service_fee,
	COALESCE(SUM(revenue_resv_cancelled), 0) AS revenue_resv_cancelled,
	COALESCE(SUM(total_cancel_fee), 0) AS total_cancel_fee,
	deleted_at
FROM date_ranges
LEFT JOIN (
	SELECT
		store_id,
		deleted_at,
		confirm_start_at AS time_at,
		(CASE WHEN created_by='store' THEN 1 ELSE 0 END) AS number_resv_by_call,
		(CASE WHEN created_by='store' THEN 0 ELSE 1 END) AS number_resv_on_web,
		(CASE WHEN payment_method='cash' THEN 1 ELSE 0 END) AS number_resv_using_cash,
		(CASE WHEN payment_method='point' THEN 1 ELSE 0 END) AS number_resv_using_point,
		(CASE WHEN created_by='store' THEN (total_cash + total_points - delay_option_total_cash - delay_option_total_points) ELSE 0 END) AS revenue_resv_by_phone,
		(CASE WHEN created_by='store' THEN 0 ELSE (total_cash + total_points - delay_option_total_cash - delay_option_total_points) END) AS revenue_resv_on_web,
		(CASE WHEN payment_method='cash' THEN (total_cash - delay_option_total_cash) ELSE 0 END) AS revenue_resv_using_cash,
		(CASE WHEN payment_method='point' THEN (total_points - delay_option_total_points) ELSE 0 END) AS revenue_resv_using_point,
		(total_cash + total_points - delay_option_total_cash - delay_option_total_points) AS total_revenue,
		(CASE WHEN payment_method='cash' AND created_by!='store' THEN web_service_fee ELSE 0 END) AS total_web_cash_service_fee,
		0 AS revenue_resv_cancelled,
		0 AS total_cancel_fee,
		0 AS delay_option_total_cash,
		0 AS delay_option_total_points
	FROM reservations
	WHERE
		confirm_start_at IS NOT NULL
		AND store_id = 56854
	UNION
	SELECT
		reservations.store_id,
		reservations.deleted_at,
		reservation_delay_options.approved_at AS time_at,
		0 AS number_resv_by_call,
		0 AS number_resv_on_web,
		0 AS number_resv_using_cash,
		0 AS number_resv_using_point,
		0 AS revenue_resv_by_phone,
		0 AS revenue_resv_on_web,
		0 AS revenue_resv_using_cash,
		0 AS revenue_resv_using_point,
		0 AS total_revenue,
		0 AS total_web_cash_service_fee,
		0 AS revenue_resv_cancelled,
		0 AS total_cancel_fee,
		(CASE WHEN reservation_delay_options.payment_method='cash' THEN reservation_delay_options.total_price ELSE 0 END) AS delay_option_total_cash,
		(CASE WHEN reservation_delay_options.payment_method='point' THEN reservation_delay_options.total_price ELSE 0 END) AS delay_option_total_points
	FROM reservations
	LEFT JOIN reservation_delay_options
	    ON reservations.id = reservation_delay_options.reservation_id
			AND reservation_delay_options.deleted_at IS NULL
			AND reservation_delay_options.status = 'approved'
			AND reservation_delay_options.approved_at IS NOT NULL
	WHERE
        reservations.confirm_start_at IS NOT NULL
        AND reservations.cancelled_at IS NULL
		AND reservations.store_id = 56854
	UNION
	SELECT
		store_id,
		deleted_at,
		cancelled_at AS time_at,
		0 AS number_resv_by_call,
		0 AS number_resv_on_web,
		0 AS number_resv_using_cash,
		0 AS number_resv_using_point,
		0 AS revenue_resv_by_phone,
		0 AS revenue_resv_on_web,
		0 AS revenue_resv_using_cash,
		0 AS revenue_resv_using_point,
		0 AS total_revenue,
		0 AS total_web_cash_service_fee,
		(CASE WHEN cancelled_at IS NOT NULL THEN total_cash + total_points ELSE 0 END) AS revenue_resv_cancelled,
		(CASE WHEN cancelled_at IS NOT NULL THEN cancel_fee ELSE 0 END) AS total_cancel_fee,
		0 AS delay_option_total_cash,
		0 AS delay_option_total_points
	FROM reservations
	WHERE
        reservations.cancel_fee > 0
        AND reservations.cancelled_at IS NOT NULL
		AND store_id = 56854
) AS reservations
ON
	to_timestamp(time_at) AT TIME ZONE 'Asia/Tokyo' >= date_ranges.range_from
	AND to_timestamp(time_at) AT TIME ZONE 'Asia/Tokyo' < date_ranges.range_to
GROUP BY date_ranges.range_from, reservations.deleted_at
ORDER BY date_ranges.range_from