SET @report_date = '2018-08-20';

WITH RECURSIVE 
		report AS (
			SELECT
				YEAR(@report_date) 							AS year,
				DATE(DATE_FORMAT(@report_date, '%Y-01-01'))	AS report_start,
				DATE(@report_date)							AS report_end
		),

		_py AS (
			SELECT
				YEAR(report.report_start - INTERVAL 1 YEAR)					AS year,
				MONTH(report.report_start - INTERVAL 1 YEAR)				AS month,
				report.report_start - INTERVAL 1 YEAR						AS period_start,
				report.report_start - INTERVAL 1 YEAR + INTERVAL 1 MONTH	AS period_end
				FROM report
			UNION ALL
			SELECT
				-- Add all the periods until the report end date
				YEAR(_py.period_start + INTERVAL 1 MONTH)			AS year,
				MONTH(_py.period_start + INTERVAL 1 MONTH)			AS month,
				_py.period_start + INTERVAL 1 MONTH					AS period_start,
				CASE
					WHEN report.report_end - INTERVAL 1 YEAR < _py.period_start + INTERVAL 2 MONTH
				  		THEN	report.report_end - INTERVAL 1 YEAR + INTERVAL 1 DAY
					ELSE 	_py.period_start + INTERVAL 2 MONTH
				END 												AS period_end
				FROM _py
				JOIN report
				WHERE _py.period_start + INTERVAL 1 MONTH < report.report_end - INTERVAL 1 YEAR
		),
		_cy AS (
			SELECT
				YEAR(report.report_start)				AS year,
				MONTH(report.report_start)				AS month,
				report.report_start						AS period_start,
				report.report_start + INTERVAL 1 MONTH	AS period_end
				FROM report
			UNION ALL
			SELECT
				-- Add all the periods until the report end date
				YEAR(_cy.period_start + INTERVAL 1 MONTH)			AS year,
				MONTH(_cy.period_start + INTERVAL 1 MONTH)			AS month,
				_cy.period_start + INTERVAL 1 MONTH					AS period_start,
				CASE
					WHEN report.report_end < _cy.period_start + INTERVAL 2 MONTH
				  		THEN	report.report_end + INTERVAL 1 DAY
					ELSE 	_cy.period_start + INTERVAL 2 MONTH
				END													AS period_end
				FROM _cy
				JOIN report
				WHERE _cy.period_start + INTERVAL 1 MONTH < report.report_end
		),
		erp_orders AS (
			-- This coalesces invoice and payment transaction date because we recognize wholesale analysis2 when paid for
			SELECT
					o.entity_id												AS order_id,
					COALESCE(MAX(t.created_at), MAX(i.created_at))			AS created_at
				FROM	erp.sales_flat_order o
				JOIN	erp.sales_flat_invoice i
					ON i.order_id = o.entity_id AND i.store_id = 3 AND i.state = 2
				LEFT JOIN   erp.sales_payment_transaction t
					ON t.order_id = o.entity_id
				LEFT JOIN 	erp.frauddetection_order_assessment f -- Fraud Filter
					ON f.order_id = o.entity_id
				WHERE  		(f.severity != 9 OR f.severity IS NULL)
					AND		o.store_id = 3
					AND		o.status != 'canceled'
				GROUP BY order_id
		),

		erp_cost_by_order AS (
			SELECT
					order_id					AS order_id,
					MAX(o.created_at)			AS created_at,
					SUM(cpf.cost * ii.qty)		AS cost,
					SUM(cpf.price * ii.qty)		AS price
				FROM erp_orders o
				JOIN erp.sales_flat_invoice i USING (order_id)
				JOIN erp.sales_flat_invoice_item ii ON i.entity_id = ii.parent_id
				JOIN erp.catalog_product_flat_3 AS cpf ON cpf.entity_id = ii.product_id AND cpf.type_id = 'simple'
				GROUP BY order_id
		),

		erp_analysis2_by_order AS (
			-- This coalesces invoice and payment transaction date because we recognize wholesale analysis2 when paid for
			SELECT
					order_id									AS order_id,
					MAX(o.created_at)							AS created_at,
					MAX(i.grand_total)							AS analysis2
				FROM		erp_orders o 
				JOIN		erp.sales_flat_invoice i USING (order_id)
				GROUP BY order_id
		),

		cy_erp_analysis2 AS (
			SELECT 
					year			AS year,
					month			AS month,
					SUM(i.analysis2)	AS analysis2
				FROM _cy
				JOIN erp_analysis2_by_order i ON i.created_at BETWEEN _cy.period_start AND _cy.period_end
				GROUP BY year, month
		),
		py_erp_analysis2 AS (
			SELECT 
					year			AS year,
					month			AS month,
					SUM(i.analysis2)	AS analysis2
				FROM _py
				JOIN erp_analysis2_by_order i ON i.created_at BETWEEN _py.period_start AND _py.period_end
				GROUP BY year, month
		),

		historic_posted AS (
			SELECT
					year										AS year,
					month										AS month,
					ws_rev										AS analysis2
				FROM	monthly_wholesale_rev_historic
				WHERE	updated = 'yes'
		),

		analysis2_month AS (
			SELECT
					_cy.year,
					_cy.month,
					IF (cy_historic_posted.analysis2 IS NOT NULL AND py_historic_posted.analysis2 IS NOT NULL, cy_historic_posted.analysis2, cy_erp_analysis2.analysis2) AS analysis2,
					IF (cy_historic_posted.analysis2 IS NOT NULL AND py_historic_posted.analysis2 IS NOT NULL, py_historic_posted.analysis2, py_erp_analysis2.analysis2) AS py_analysis2,
					IF (cy_historic_posted.analysis2 IS NOT NULL AND py_historic_posted.analysis2 IS NOT NULL, 'posted', 'erp') AS analysis2_source
				FROM 		_cy
				JOIN		_py
					ON _py.period_start = _cy.period_start - INTERVAL 1 YEAR -- Juliane: Is "_py.month = _cy.month AND _py.year = _cy.year - 1" easier to read?
				LEFT JOIN	cy_erp_analysis2 ON cy_erp_analysis2.month = _cy.month AND cy_erp_analysis2.year = _cy.year
				LEFT JOIN	historic_posted AS cy_historic_posted ON cy_historic_posted.month = _cy.month AND cy_historic_posted.year = _cy.year
				LEFT JOIN	py_erp_analysis2 ON py_erp_analysis2.month = _py.month AND py_erp_analysis2.year = _py.year
				LEFT JOIN	historic_posted AS py_historic_posted ON py_historic_posted.month = _py.month AND py_historic_posted.year = _py.year
		),
		analysis2_year AS (
			SELECT
				year																							AS year,
				SUM(analysis2_month.analysis2)																		AS analysis2,
				SUM(analysis2_month.py_analysis2)																	AS py_analysis2,
				ROUND(((analysis2_month.analysis2 - analysis2_month.py_analysis2) / analysis2_month.py_analysis2) * 100, 2)	AS yoy
				FROM _cy
				JOIN analysis2_month USING (year, month)
				GROUP BY year
		),

		analysis1_month AS (
			SELECT  
	  				year			AS year,
					month			AS month,
					SUM(ws_analysis1)	AS analysis1
				FROM	_cy
				JOIN	2018_daily_analysis1 ON analysis1_date BETWEEN _cy.period_start AND _cy.period_end
				GROUP BY year, month
		),
		analysis1_year AS (
			SELECT
					year																							AS year,
					SUM(analysis1_month.analysis1)																		AS analysis1
				FROM _cy
				JOIN analysis1_month USING (year, month)
				GROUP BY year
		),

		analysis3 AS (
			SELECT
					year						AS year,
					ROUND(AVG(i.analysis2), 2)	AS analysis3,
					COUNT(*)					AS orders
				FROM _cy
				JOIN erp_analysis2_by_order i ON i.created_at BETWEEN _cy.period_start AND _cy.period_end
				GROUP BY year
		),

		analysis4 AS (
			SELECT
					year													AS year,
					ROUND((1 - (SUM(c.cost) / SUM(c.price))) * 100, 2)		AS product_analysis4
				FROM _cy
				JOIN erp_cost_by_order c ON c.created_at BETWEEN _cy.period_start AND _cy.period_end
				GROUP BY year
		)

		SELECT 
				@report_date																					AS as_of_date,
				report.year																						AS year,
				analysis2_year.analysis2																			AS wholesale_rev,
				ROUND(((analysis2_year.analysis2 - analysis2_year.py_analysis2) / analysis2_year.py_analysis2) * 100, 2)	AS yoy,
				analysis1_year.analysis1																				AS analysis1,
				ROUND(((analysis2_year.analysis2 / analysis1_year.analysis1) - 1) * 100, 2)								AS percent_of_analysis1,
				analysis3.analysis3																							AS analysis3,
				analysis3.orders	 																					AS orders,
				analysis4.product_analysis4																			AS product_analysis4
			FROM report
			JOIN analysis2_year USING (year)
			JOIN analysis1_year USING (year)
			JOIN analysis3 USING (year)
			JOIN analysis4 USING (year)
	;
