BEGIN TRANSACTION; CREATE VIEW IF NOT EXISTS stock AS SELECT id, name, category, amount FROM products WHERE deprecated = 0 OR amount != 0; CREATE VIEW IF NOT EXISTS purchaseprices AS SELECT product, SUM(price * amount) / SUM(amount) AS price FROM restock GROUP BY product; CREATE VIEW IF NOT EXISTS invoice AS SELECT user, timestamp, id AS productid, name AS productname, CASE WHEN user < 0 THEN (SELECT SUM(price * amount) / SUM(amount) FROM restock WHERE restock.product = id AND restock.timestamp <= sales.timestamp ) else (SELECT CASE WHEN user=0 THEN guestprice else memberprice END FROM prices WHERE product = id AND valid_from <= timestamp ORDER BY valid_from DESC LIMIT 1) END AS price FROM sales INNER JOIN products ON sales.product = products.id ORDER BY timestamp; CREATE VIEW IF NOT EXISTS current_cashbox_status AS SELECT ( ( SELECT SUM( ( SELECT guestprice FROM prices WHERE product = s.product AND valid_from <= s.timestamp ORDER BY valid_from DESC LIMIT 1 ) ) FROM sales s WHERE user = 0 ) + ( SELECT SUM(amount) FROM cashbox_diff ) ) AS amount; COMMIT;