<commit_before>-- Deprecate unused view to instead use products_last_purchased
DROP VIEW products_current_price;

CREATE VIEW products_last_purchased
AS
select
	1 AS id, -- Dummy, LessQL needs an id column
	sl.product_id,
	sl.amount,
	sl.best_before_date,
	sl.purchased_date,
	sl.price,
	sl.qu_factor_purchase_to_stock,
	sl.location_id,
	sl.shopping_location_id
	FROM stock_log sl
	JOIN (
			SELECT
				s1.product_id,
				MAX(s1.id) max_stock_id
				FROM stock_log s1
				JOIN (
						SELECT
							s.product_id,
							MAX(s.purchased_date) max_purchased_date
						FROM stock_log s
						WHERE undone = 0
							AND transaction_type in ('purchase', 'stock-edit-new', 'inventory-correction')
						GROUP BY s.product_id) sp2
					ON s1.product_id = sp2.product_id
					AND s1.purchased_date = sp2.max_purchased_date
			WHERE undone = 0
				AND transaction_type in ('purchase', 'stock-edit-new', 'inventory-correction')
			GROUP BY s1.product_id) sp3
		ON sl.product_id = sp3.product_id
		AND sl.id = sp3.max_stock_id;

CREATE VIEW products_average_price
AS
SELECT
	1 AS id, -- Dummy, LessQL needs an id column
	p.id AS product_id,
	CASE WHEN s.product_id IS NOT NULL THEN round(sum(s.amount * s.price) / sum(s.amount), 2) ELSE NULL END as price
FROM products p
LEFT JOIN stock s
	ON p.id = s.product_id
GROUP BY p.id;

CREATE VIEW products_oldest_stock_unit_price
AS
-- Find oldest best_before_date then oldest purchased_date then make sure to return one stock row using max
SELECT
	1 AS id, -- Dummy, LessQL needs an id column
	p.id AS product_id,
	s.amount,
	s.best_before_date,
	s.purchased_date,
	s.price,
	s.qu_factor_purchase_to_stock,
	s.location_id,
	s.shopping_location_id
	FROM products p
	LEFT JOIN stock s
		ON p.id = s.product_id
	LEFT JOIN (
			SELECT<commit_msg>Make sure that the view products_last_purchased always returns a row per product, also for not in stock items (references #801)<commit_after>-- Deprecate unused view to instead use products_last_purchased
DROP VIEW products_current_price;

CREATE VIEW products_last_purchased
AS
SELECT
	1 AS id, -- Dummy, LessQL needs an id column
	p.id AS product_id,
	sl.amount,
	sl.best_before_date,
	sl.purchased_date,
	sl.price,
	sl.qu_factor_purchase_to_stock,
	sl.location_id,
	sl.shopping_location_id
	FROM products p
	LEFT JOIN stock_log sl
		ON p.id = sl.product_id
	LEFT JOIN (
			SELECT
				s1.product_id,
				MAX(s1.id) max_stock_id
				FROM stock_log s1
				JOIN (
						SELECT
							s.product_id,
							MAX(s.purchased_date) max_purchased_date
						FROM stock_log s
						WHERE undone = 0
							AND transaction_type in ('purchase', 'stock-edit-new', 'inventory-correction')
						GROUP BY s.product_id) sp2
					ON s1.product_id = sp2.product_id
					AND s1.purchased_date = sp2.max_purchased_date
			WHERE undone = 0
				AND transaction_type in ('purchase', 'stock-edit-new', 'inventory-correction')
			GROUP BY s1.product_id) sp3
	ON sl.product_id = sp3.product_id
	AND sl.id = sp3.max_stock_id;

CREATE VIEW products_average_price
AS
SELECT
	1 AS id, -- Dummy, LessQL needs an id column
	p.id AS product_id,
	CASE WHEN s.product_id IS NOT NULL THEN round(sum(s.amount * s.price) / sum(s.amount), 2) ELSE NULL END as price
FROM products p
LEFT JOIN stock s
	ON p.id = s.product_id
GROUP BY p.id;

CREATE VIEW products_oldest_stock_unit_price
AS
-- Find oldest best_before_date then oldest purchased_date then make sure to return one stock row using max
SELECT
	1 AS id, -- Dummy, LessQL needs an id column
	p.id AS product_id,
	s.amount,
	s.best_before_date,
	s.purchased_date,
	s.price,
	s.qu_factor_purchase_to_stock,
	s.location_id,
	s.shopping_location_id
	FROM products p
	LEFT JOIN stock s
		ON p.id = s.product_id
	LEFT JOIN (
			SELECT
{'fraction_non_alphanumeric': 0.07219538378958669, 'fraction_numerical': 0.010466988727858293, 'mean_word_length': 3.6355721393034828, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5768116', 'n_tokens_mistral': 1538, 'n_tokens_neox': 1369, 'n_words': 406}