02_materialized_test_data.sql

-- Base test data
INSERT INTO materialized_test.product (sku, description, active) 
VALUES 
('111', 'Widget2', TRUE),
('112', 'Widget', TRUE),
('121', 'Whatsit2', TRUE),
('122', 'Whatsit', TRUE),
('131', 'Foo', TRUE),
('132', 'Foo+', TRUE),
('133', 'Foo++', TRUE),
('113', 'Discontinued Widget', FALSE),
('123', 'Discontinued Whatsit', FALSE),
('134', 'Discontinued Foo', FALSE);

INSERT INTO materialized_test.fee_type (fee_type_description)
VALUES('Late Fee'),('Stupid Tax'),('Ugly Babies Fund Donation');

INSERT INTO materialized_test.product_group (group_code, description)
VALUES ('1', 'Merchandise');

--insert children with parent's id as their parent
INSERT INTO materialized_test.product_group (parent_product_group_id, group_code, description)
SELECT product_group_id, '11','Widgets' FROM materialized_test.product_group UNION ALL
SELECT product_group_id, '12','Whatsits' FROM materialized_test.product_group UNION ALL
SELECT product_group_id, '13','Foos' FROM materialized_test.product_group;

--add products to the child groups 
INSERT INTO materialized_test.product_group_product (product_group_id, product_id)
SELECT product_group.product_group_id, product.product_id
FROM materialized_test.product_group
INNER JOIN materialized_test.product ON product.sku ILIKE '11%'
WHERE product_group.group_code = '11'
UNION ALL
SELECT product_group.product_group_id, product.product_id
FROM materialized_test.product_group
INNER JOIN materialized_test.product ON product.sku ILIKE '12%'
WHERE product_group.group_code = '12'
UNION ALL
SELECT product_group.product_group_id, product.product_id
FROM materialized_test.product_group
INNER JOIN materialized_test.product ON product.sku ILIKE '13%'
WHERE product_group.group_code = '13';


-- Buying list test data
INSERT INTO materialized_test.buying_list (buying_list_description) 
VALUES ('Default List');

INSERT INTO materialized_test.buying_list_item (
	buying_list_id,
	product_group_id,
	buying_list_item_price,
	buying_list_item_description,
	buying_list_item_effective_date,
	buying_list_item_special
)
SELECT buying_list.buying_list_id,
       product_group.product_group_id,
       100.00,
       'Merchandising Price',
       '2020-01-01'::date,
       FALSE
FROM materialized_test.buying_list
INNER JOIN materialized_test.product_group ON product_group.group_code = '1'
UNION ALL
SELECT buying_list.buying_list_id,
       product_group.product_group_id,
       90.00,
       'Widgets Bulk Price',
       '2020-04-01'::date,
       FALSE
FROM materialized_test.buying_list
INNER JOIN materialized_test.product_group ON product_group.group_code = '11'
UNION ALL
SELECT buying_list.buying_list_id,
       product_group.product_group_id,
       95.00,
       'Whatsits Bulk Price',
       '2020-04-01'::date,
       FALSE 
FROM materialized_test.buying_list
INNER JOIN materialized_test.product_group ON product_group.group_code = '12';


-- We will add 2 of the products in group 13 directly to the buying_list with their own special pricing
-- and one product each from groups 11 and 12 with their own pricing.
INSERT INTO materialized_test.buying_list_item (
	buying_list_id,
	product_id,
	buying_list_item_price,
	buying_list_item_description,
	buying_list_item_effective_date,
	buying_list_item_special
)
SELECT 	buying_list.buying_list_id,
	product.product_id,
	80.00,
	product.description,
	'2020-01-01'::date,
	FALSE
FROM materialized_test.buying_list
INNER JOIN materialized_test.product ON product.sku IN ('132','133')
UNION ALL
SELECT 	buying_list.buying_list_id,
	product.product_id,
	80.00,
	product.description,
	'2020-01-01'::date,
	TRUE
FROM materialized_test.buying_list
INNER JOIN materialized_test.product ON product.sku IN ('111','122');


--Products should be priced on the buying list as follows:
--'111'  80 - product-specific pricing on the list
--'112'  90 - inherited from group 11
--'121'  95 - inherited from group 2
--'122'  80 - product-specific pricing on the list
--'131' 100 - inherited from Group 1 as 13 is not on the list
--'132'  80 - product-specific pricing on the list
--'133'  80 - product-specific pricing on the list
--'113'  should not appear, active = false
--'123'  should not appear, active = false
--'134'  should not appear, active = false
update materialized_test.buying_list_item
set buying_list_item_price = 75.00
where product_group_id IN ( 
	select product_group_id
	from materialized_test.product_group 
	where product_group.group_code = '12'); 


update materialized_test.buying_list_item
set buying_list_item_price = 85.00
where product_id IN ( 
	select product_id
	from materialized_test.product 
	where product.sku = '111'); 


update materialized_test.buying_list_item
set buying_list_item_price = 85.00
where product_id IN ( 
	select product_id
	from materialized_test.product 
	where product.sku = '111'); 

--Move product from one group to another and back
DELETE 
FROM materialized_test.product_group_product
WHERE product_id = (SELECT product_id FROM materialized_test.product WHERE sku = '112');

INSERT INTO materialized_test.product_group_product(product_group_id, product_id)
SELECT product_group_id, product_id
FROM materialized_test.product
INNER JOIN materialized_test.product_group
ON product_group.group_code = '12'
AND product.sku = '112';

DELETE 
FROM materialized_test.product_group_product
WHERE product_id = (SELECT product_id FROM materialized_test.product WHERE sku = '112');

INSERT INTO materialized_test.product_group_product(product_group_id, product_id)
SELECT product_group_id, product_id
FROM materialized_test.product
INNER JOIN materialized_test.product_group
ON product_group.group_code = '11'
AND product.sku = '112';


-- Begin testing invoice data --

--Invoice Totals test data
INSERT INTO materialized_test.invoice(invoice_description, invoice_date, invoice_total)
VALUES 
('Test Invoice 1','2020-01-01',150),
('Test Invoice 2','2020-01-02',200),
('Test Invoice 3','2020-01-03',250),
('Test Invoice 4','2020-01-04',300),
('Test Invoice 5','2020-01-05',350),
('Test Invoice 6','2020-01-06',400),
('Test Invoice 7','2020-01-07',450);
 

INSERT INTO materialized_test.invoice_product_detail (
	invoice_id,
	product_id,
	invoiced_price,
	invoiced_quantity,
	paid_price,
	paid_quantity
)
SELECT invoice_id,
       product_id,
       10.00,
       invoice_total / 20,
       0,
       0
FROM materialized_test.invoice
INNER JOIN materialized_test.product ON product.sku NOT LIKE '13%'
WHERE invoice.invoice_description = 'Test Invoice 1'
ORDER BY invoice_id;


INSERT INTO materialized_test.invoice_fee_detail (
	invoice_id,
	fee_type_id,
	invoiced_fee,
	paid_fee
)
SELECT invoice_id,
	fee_type_id,
	invoice.invoice_total / 2,
	0
FROM  materialized_test.invoice
INNER JOIN materialized_test.fee_type ON fee_type_description = 'Stupid Tax'
WHERE invoice.invoice_description = 'Test Invoice 2'
ORDER BY invoice_id;



INSERT INTO materialized_test.invoice_fee_detail (
	invoice_id,
	fee_type_id,
	invoiced_fee,
	paid_fee
)
SELECT invoice_id,
	fee_type_id,
	invoice.invoice_total / 2,
	0
FROM  materialized_test.invoice
INNER JOIN materialized_test.fee_type ON fee_type_description = 'Late Fee'
WHERE invoice.invoice_description = 'Test Invoice 2'
ORDER BY invoice_id;


INSERT INTO materialized_test.invoice_fee_detail (
	invoice_id,
	fee_type_id,
	invoiced_fee,
	paid_fee
)
SELECT invoice_id,
	fee_type_id,
	invoice.invoice_total / 2,
	0
FROM  materialized_test.invoice
INNER JOIN materialized_test.fee_type ON fee_type_description = 'Stupid Tax'
WHERE invoice.invoice_description = 'Test Invoice 3'
ORDER BY invoice_id;

INSERT INTO materialized_test.invoice_product_detail (
	invoice_id,
	product_id,
	invoiced_price,
	invoiced_quantity,
	paid_price,
	paid_quantity
)
SELECT invoice_id,
       product_id,
       (invoice.invoice_total / 2) / 6,
       2,
       0,
       0
FROM materialized_test.invoice
INNER JOIN materialized_test.product ON product.sku LIKE '13%'
WHERE invoice.invoice_description = 'Test Invoice 3'
ORDER BY invoice_id;

INSERT INTO materialized_test.invoice_product_detail (
	invoice_id,
	product_id,
	invoiced_price,
	invoiced_quantity,
	paid_price,
	paid_quantity
)
SELECT invoice_id,
       product_id,
       (invoice.invoice_total / 4) / 4,
       4,
       0,
       0
FROM materialized_test.invoice
INNER JOIN materialized_test.product ON product.sku LIKE '13%'
WHERE invoice.invoice_description = 'Test Invoice 4'
ORDER BY invoice_id;


UPDATE materialized_test.invoice_product_detail
SET invoiced_price = 1, invoiced_quantity = 31.25
FROM materialized_test.invoice
WHERE invoice.invoice_id = invoice_product_detail.invoice_id
AND invoice.invoice_description = 'Test Invoice 3';


UPDATE materialized_test.invoice_product_detail
SET paid_price = invoiced_price, paid_quantity = invoiced_quantity
FROM materialized_test.invoice
WHERE invoice.invoice_id = invoice_product_detail.invoice_id
AND invoice.invoice_description = 'Test Invoice 3';


-- BUYING LIST TEST QUERY
-- Ensure nothing is different in the check view and materialized view 
SELECT * FROM materialized_test.buying_list_product_materialized except SELECT * FROM materialized_test.check_buying_list_product_materialized
UNION ALL 
SELECT * FROM materialized_test.check_buying_list_product_materialized except SELECT * FROM materialized_test.buying_list_product_materialized;

-- INVOICE TEST QUERY
-- Ensure nothing is different in the check view and materialized view
SELECT * FROM materialized_test.invoice_totals_materialized EXCEPT SELECT * FROM materialized_test.check_invoice_totals_materialized UNION ALL
SELECT * FROM materialized_test.check_invoice_totals_materialized EXCEPT SELECT * FROM materialized_test.invoice_totals_materialized;