package queries

const CREATE_ORDER = `
INSERT INTO order_record(user_id, orderer, date_due, date_placed) VALUES ($1, $2, $3, $4);
`

const SET_ORDER_FILLED = `
UPDATE order_record SET filled = $1 WHERE id = $2
`

const SET_ORDER_DELIVERED = `
UPDATE order_record SET delivered = $1 WHERE id = $2
`

const SET_ORDER_PAID = `
UPDATE order_record SET amount_paid = $1 WHERE id = $2
`

const GET_ORDER_TOTAL_AND_BALANCE = `
SELECT
	orec.id,
	order_total,
	order_total - orec.amount_paid AS balance
FROM
	order_record orec
	INNER JOIN (
		SELECT 
			orec.id,
			SUM(quantity * price) AS order_total
		FROM
			order_record orec
			INNER JOIN order_item oi ON oi.order_id = orec.id
			INNER JOIN item i ON oi.item_id = i.id
				AND oi.order_id = $1
			INNER JOIN item_price_history iph ON iph.item_id = i.id
				AND iph.valid_from <= oi.created_at
				AND (iph.valid_to IS NULL OR iph.valid_to > oi.created_at)
		GROUP BY orec.id
	) totals ON orec.id = totals.id
`

const GET_TOTAL_ORDER_FROM_ORDER_INFORMATION = `
WITH order_bill AS (
	SELECT
		orec.id,
		COALESCE(order_total, 0) AS order_total,
		COALESCE(order_total - orec.amount_paid, 0) AS balance
	FROM
		order_record orec
		INNER JOIN (
			SELECT 
				orec.id,
				SUM(quantity * price) AS order_total
			FROM
				order_record orec
				LEFT JOIN order_item oi ON oi.order_id = orec.id
				LEFT JOIN item i ON oi.item_id = i.id
				LEFT JOIN item_price_history iph ON iph.item_id = i.id
					AND iph.valid_from <= oi.created_at
					AND (iph.valid_to IS NULL OR iph.valid_to > oi.created_at)
			WHERE 
				orec.user_id = $1
				AND orec.orderer = $2
				AND orec.date_placed = $3
			GROUP BY orec.id
		) totals ON orec.id = totals.id
)

SELECT
	orec.id,
	user_id,
	orderer,
	date_due,
	date_placed,
	amount_paid,
	order_total,
	balance,
	filled,
	delivered
FROM
	order_record orec
	INNER JOIN order_bill ON order_bill.id = orec.id;
	`

const GET_TOTAL_ORDER_FROM_ORDER_ID = `
WITH order_bill AS (
SELECT
	orec.id,
	COALESCE(order_total, 0) AS order_total,
	COALESCE(order_total - orec.amount_paid, 0) AS balance
FROM
	order_record orec
	INNER JOIN (
		SELECT 
			orec.id,
			SUM(quantity * price) AS order_total
		FROM
			order_record orec
			LEFT JOIN order_item oi ON oi.order_id = orec.id
			LEFT JOIN item i ON oi.item_id = i.id
			LEFT JOIN item_price_history iph ON iph.item_id = i.id
				AND iph.valid_from <= oi.created_at
				AND (iph.valid_to IS NULL OR iph.valid_to > oi.created_at)
		WHERE orec.id = $1
		GROUP BY orec.id
	) totals ON orec.id = totals.id
)

SELECT
	orec.id,
	user_id,
	orderer,
	date_due,
	date_placed,
	amount_paid,
	order_total,
	balance,
	filled,
	delivered
FROM
	order_record orec
	INNER JOIN order_bill ON order_bill.id = orec.id;
`

const GET_ORDER_TABLE = `
WITH order_bill AS (
SELECT
	orec.id,
	COALESCE(order_total, 0) AS order_total,
	COALESCE(order_total - orec.amount_paid, 0) AS balance
FROM
	order_record orec
	INNER JOIN (
		SELECT 
			orec.id,
			SUM(quantity * price) AS order_total
		FROM
			order_record orec
			LEFT JOIN order_item oi ON oi.order_id = orec.id
			LEFT JOIN item i ON oi.item_id = i.id
			LEFT JOIN item_price_history iph ON iph.item_id = i.id
				AND iph.valid_from <= oi.created_at
				AND (iph.valid_to IS NULL OR iph.valid_to > oi.created_at)
		GROUP BY orec.id
	) totals ON orec.id = totals.id
)

SELECT
	orec.id,
	user_id,
	orderer,
	date_due,
	date_placed,
	amount_paid,
	order_total,
	balance,
	filled,
	delivered
FROM
	order_record orec
	INNER JOIN order_bill ON order_bill.id = orec.id
ORDER BY date_due DESC
OFFSET $1
LIMIT $2;
`

const UPDATE_ORDER_FILLED = `UPDATE order_record
SET filled = (
	SELECT order_filled
	FROM order_record orec
	INNER JOIN (SELECT
		orec.id,
		COALESCE(EVERY(made = quantity), false) AS order_filled
	FROM
		order_record orec
		LEFT JOIN order_item oi ON oi.order_id = orec.id
	GROUP BY
		orec.id) orec_result ON orec.id = orec_result.id
			AND orec.id = $1
) WHERE order_record.id = $1
`