drop schema if exists materialized_test cascade;
create extension if not exists btree_gist;
create schema materialized_test;

create table materialized_test.buying_list ( 
	buying_list_id uuid not null primary key default gen_random_uuid(),
	buying_list_description text not null
);

--represents brands or categories of products.  Simple hierarchy (each node can belong to one and only one parent node)
create table materialized_test.product_group (
	product_group_id uuid not null primary key default gen_random_uuid(),
	parent_product_group_id uuid null references materialized_test.product_group (product_group_id), --simple hierarchy
	group_code text not null,
	description text not null
);

create table materialized_test.product (
	product_id uuid not null primary key default gen_random_uuid(),
	sku text not null,
	description text not null,
	active boolean not null default true,-- soft delete
	CONSTRAINT product_unique_sku UNIQUE (sku)
);

create table materialized_test.product_group_product (
	product_group_id uuid not null references materialized_test.product_group(product_group_id),
	product_id uuid not null references materialized_test.product(product_id),
	constraint product_group_product_pkey primary key (product_group_id, product_id)
);

 

CREATE OR REPLACE FUNCTION materialized_test.value_exists(VARIADIC _values anyarray)
  RETURNS integer AS
$BODY$
  SELECT sum(CASE WHEN val IS NOT NULL THEN 1 ELSE 0 END)::int FROM unnest(_values) tmp(val);
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

--stores product_groups and products on the buying list
-- a descendent of a group can be specified on the same buying list as the parent group
-- a product within a group can be specified on the same buying list as that group
CREATE TABLE materialized_test.buying_list_item (  
	buying_list_item_id uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid() ,
	buying_list_id uuid NOT NULL REFERENCES materialized_test.buying_list (buying_list_id),
	product_id uuid NULL REFERENCES materialized_test.product (product_id),
	product_group_id uuid NULL REFERENCES materialized_test.product_group (product_group_id),
	buying_list_item_price NUMERIC,
	buying_list_item_description TEXT,
	buying_list_item_effective_date DATE,
	buying_list_item_special BOOLEAN,
	CONSTRAINT buying_list_item_one_not_null CHECK (materialized_test.value_exists(VARIADIC ARRAY[product_id, product_group_id]) = 1)
);

--"materialized" table to provide view of data to 
CREATE TABLE materialized_test.buying_list_product_materialized (
	buying_list_item_id uuid not null,
	buying_list_id uuid not null,
	product_id uuid not null,
	buying_list_item_price numeric  not null,
	buying_list_item_description text  not null,
	buying_list_item_effective_date date  not null,
	buying_list_item_special boolean not null,
	CONSTRAINT buying_list_product_materialized_pkey PRIMARY KEY (buying_list_id, product_id)

);


CREATE OR REPLACE VIEW materialized_test.product_group_hierarchy AS 
 WITH RECURSIVE groupings_list(original_product_group_id, parent_product_group_id, product_group_id) AS (
         SELECT pg.product_group_id AS original_grouping_id,
            pg.parent_product_group_id,
            pg.product_group_id,
            0 AS level
           FROM materialized_test.product_group pg
        UNION ALL
         SELECT gl.original_product_group_id,
            pg2.parent_product_group_id,
            pg2.product_group_id,
            gl.level + 1
           FROM groupings_list gl
             JOIN materialized_test.product_group pg2 ON pg2.parent_product_group_id = gl.product_group_id
        )
 SELECT groupings_list.original_product_group_id,
    groupings_list.parent_product_group_id,
    groupings_list.product_group_id,
    groupings_list.level
   FROM groupings_list;
 
--p_active_products_only is a client-level setting all lists for one client will either enforce this or not
CREATE OR REPLACE FUNCTION materialized_test.refresh_buying_list_product_materialized(p_buying_list_ids uuid[])
  RETURNS void AS
$BODY$
BEGIN


CREATE TEMPORARY TABLE t_result AS
SELECT DISTINCT ON (bli.buying_list_id, p.product_id) 
	bli.buying_list_item_id,
	bli.buying_list_id,
	p.product_id,
	bli.buying_list_item_price,
	bli.buying_list_item_description,
	bli.buying_list_item_effective_date,
	bli.buying_list_item_special
FROM materialized_test.buying_list_item bli 
LEFT JOIN materialized_test.product_group_hierarchy pgh 
ON pgh.original_product_group_id = bli.product_group_id 
AND NOT EXISTS ( --group from this group is not already explicitly on the list
	SELECT 1
	FROM materialized_test.buying_list_item bli2
	WHERE true
	AND bli2.buying_list_id = bli.buying_list_id 
	AND pgh.product_group_id = bli2.product_group_id 
	AND bli2.product_group_id <> bli.product_group_id
)
LEFT JOIN materialized_test.product_group pg
ON pgh.product_group_id = pg.product_group_id
LEFT JOIN materialized_test.product_group_product pgp 
ON pg.product_group_id  = pgp.product_group_id
AND NOT EXISTS ( --product in this group is not already explicitly on the list
	SELECT 1
	FROM materialized_test.buying_list_item bli3
	WHERE true
	AND bli3.buying_list_id = bli.buying_list_id 
	AND pgp.product_id = bli.product_id
)
INNER JOIN materialized_test.product p 
ON p.product_id = COALESCE(bli.product_id, pgp.product_id)
WHERE p.active = true
AND bli.buying_list_id IN (SELECT unnest(p_buying_list_ids))
ORDER BY bli.buying_list_id, p.product_id, pgh.level desc;

ANALYZE t_result;

DELETE 
FROM materialized_test.buying_list_product_materialized blpm
WHERE blpm.buying_list_id IN (SELECT unnest(p_buying_list_ids))
AND NOT EXISTS (
	SELECT 1
	FROM t_result res
	WHERE res = blpm
);

INSERT INTO materialized_test.buying_list_product_materialized
( 
	buying_list_item_id,
	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_item_id,
        buying_list_id,
	product_id,
	buying_list_item_price,
	buying_list_item_description,
	buying_list_item_effective_date,
	buying_list_item_special
FROM t_result
ON CONFLICT DO NOTHING;


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION materialized_test.buying_list_item_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
BEGIN
	IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
	THEN
			SELECT array_agg(DISTINCT buying_list_id)
			INTO v_buying_list_ids
			FROM new_table;

	END IF;	
	
	IF (TG_OP = 'DELETE')
	THEN
			SELECT array_agg(DISTINCT buying_list_id)
			INTO v_buying_list_ids
			FROM old_table;

	END IF;

	PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);

	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 

CREATE TRIGGER buying_list_item_statement_trigger_d
  AFTER DELETE ON materialized_test.buying_list_item
  REFERENCING OLD TABLE AS old_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.buying_list_item_statement_refresh_trigger();

CREATE TRIGGER buying_list_item_statement_trigger_i
  AFTER INSERT ON materialized_test.buying_list_item
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.buying_list_item_statement_refresh_trigger();

CREATE TRIGGER buying_list_item_statement_trigger_u
  AFTER UPDATE ON materialized_test.buying_list_item
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.buying_list_item_statement_refresh_trigger();
  

CREATE OR REPLACE FUNCTION materialized_test.product_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
v_product_ids uuid[];
BEGIN
	IF (TG_OP = 'UPDATE')
	THEN
		SELECT array_agg(product_id)
		INTO v_product_ids
		FROM new_table;
		
		SELECT array_agg(DISTINCT x.buying_list_id)
		INTO v_buying_list_ids
		FROM (
			SELECT bli.buying_list_id
			FROM buying_list_item bli
			WHERE true
			AND bli.product_id = ANY (v_product_ids)
			
			UNION ALL
			
			SELECT bli2.buying_list_id
			FROM materialized_test.buying_list_item bli2
			WHERE true
			AND EXISTS (
				SELECT 1
				FROM materialized_test.product_group_product pgp
				INNER JOIN materialized_test.product_group_hierarchy pgh
				ON pgp.product_group_id = pgh.product_group_id
				WHERE true
				AND bli2.product_group_id = pgh.original_product_group_id
				AND pgp.product_id = ANY (v_product_ids)
			)
		) x;
	END IF;

	PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--Only need to trigger on updates, newly inserted products cannot be on a buying list and to hard delete a product
-- requires deleting all of its dependencies
CREATE TRIGGER product_statement_trigger_u
  AFTER UPDATE ON materialized_test.product
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.product_statement_refresh_trigger();



CREATE OR REPLACE FUNCTION materialized_test.product_group_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
BEGIN
	IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
	THEN
		SELECT array_agg(DISTINCT bli.buying_list_id)
		INTO v_buying_list_ids
		FROM materialized_test.buying_list_item bli
		INNER JOIN materialized_test.product_group_hierarchy pgh
		ON bli.product_group_id = pgh.original_product_group_id
		WHERE true
		AND pgh.product_group_id IN (
			SELECT product_group_id
			FROM new_table
		);
	END IF;
	IF (TG_OP = 'DELETE')
	THEN
		SELECT array_agg(DISTINCT buying_list_id)
		INTO v_buying_list_ids
		FROM materialized_test.buying_list_item bli
		INNER JOIN materialized_test.product_group_hierarchy  pgh
		ON bli.product_group_id_id =  pgh.original_product_group_id_id
		WHERE true
		AND  pgh.product_group_id IN (
			SELECT product_group_id
			FROM old_table
		);
	END IF;

	PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--Only need to trigger on updates, newly inserted product_groups cannot be on a buying list and to hard delete a product
-- requires deleting all of its dependencies
CREATE TRIGGER product_group_statement_trigger_u
  AFTER UPDATE ON materialized_test.product_group
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.product_group_statement_refresh_trigger();
  

CREATE OR REPLACE FUNCTION materialized_test.product_group_product_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
BEGIN
	IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
	THEN
		SELECT array_agg(DISTINCT bli.buying_list_id)
		INTO v_buying_list_ids
		FROM materialized_test.buying_list_item bli
		INNER JOIN materialized_test.product_group_hierarchy pgh
		ON bli.product_group_id = pgh.original_product_group_id
		WHERE true
		AND EXISTS ( 
			SELECT 1
			FROM new_table
			WHERE new_table.product_group_id = pgh.product_group_id
		);
	END IF;
	IF (TG_OP = 'DELETE')
	THEN
		SELECT array_agg(DISTINCT bli.buying_list_id)
		INTO v_buying_list_ids
		FROM materialized_test.buying_list_item bli
		INNER JOIN materialized_test.product_group_hierarchy pgh
		ON bli.product_group_id =  pgh.original_product_group_id
		WHERE true
		AND EXISTS ( 
			SELECT 1
			FROM old_table
			WHERE old_table.product_group_id = pgh.product_group_id
		);
	END IF;

	PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER product_group_product_statement_trigger_i
  AFTER INSERT ON materialized_test.product_group_product
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.product_group_product_statement_refresh_trigger();

CREATE TRIGGER product_group_product_statement_trigger_d
  AFTER DELETE ON materialized_test.product_group_product
  REFERENCING OLD TABLE AS old_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.product_group_product_statement_refresh_trigger();

CREATE TRIGGER product_group_product_statement_trigger_u
  AFTER UPDATE ON materialized_test.product_group_product
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.product_group_product_statement_refresh_trigger();
  




CREATE VIEW materialized_test.check_buying_list_product_materialized 
AS
SELECT DISTINCT ON (bli.buying_list_id, p.product_id) 
	bli.buying_list_item_id,
	bli.buying_list_id,
	p.product_id,
	bli.buying_list_item_price,
	bli.buying_list_item_description,
	bli.buying_list_item_effective_date,
	bli.buying_list_item_special
FROM materialized_test.buying_list_item bli 
LEFT JOIN materialized_test.product_group_hierarchy pgh 
ON pgh.original_product_group_id = bli.product_group_id 
AND NOT EXISTS ( --group from this group is not already explicitly on the list
	SELECT 1
	FROM materialized_test.buying_list_item bli2
	WHERE true
	AND bli2.buying_list_id = bli.buying_list_id 
	AND pgh.product_group_id = bli2.product_group_id 
	AND bli2.product_group_id <> bli.product_group_id
)
LEFT JOIN materialized_test.product_group pg
ON pgh.product_group_id = pg.product_group_id
LEFT JOIN materialized_test.product_group_product pgp 
ON pg.product_group_id  = pgp.product_group_id
AND NOT EXISTS ( --product in this group is not already explicitly on the list
	SELECT 1
	FROM materialized_test.buying_list_item bli3
	WHERE true
	AND bli3.buying_list_id = bli.buying_list_id 
	AND pgp.product_id = bli.product_id
)
INNER JOIN materialized_test.product p 
ON p.product_id = COALESCE(bli.product_id, pgp.product_id)
WHERE p.active = true
ORDER BY bli.buying_list_id, p.product_id, pgh.level desc;



-- Scenario 2 - denormalization of totals of price * quantity type and simple fee-type values from child 
-- records for a given entity stored in two different child tables

CREATE TABLE materialized_test.fee_type (
	fee_type_id uuid not null primary key default gen_random_uuid(),
	fee_type_description text not null
	--fee types have other attributes that aren't important in this use case
);

CREATE TABLE materialized_test.invoice (
	invoice_id uuid not null primary key default gen_random_uuid(),
	invoice_description text not null,
	invoice_date date not null,
	invoice_total numeric not null --denormalized total of all invoiced prices and invoiced fees for this invoice.  users cannot submit the invoice through the application until this matches the detail
	--invoices have other attributes that aren't important for this use case
);

--a invoice can have many prices for the same or different products at man different quantities.  The paid price can be for some quantity of the invoiced amount and can be different than the invoiced price.
CREATE TABLE materialized_test.invoice_product_detail (
	invoice_product_id uuid not null primary key default gen_random_uuid(),
	invoice_id uuid not null references materialized_test.invoice(invoice_id),
	product_id uuid not null references materialized_test.product(product_id),
	invoiced_price numeric not null,
	invoiced_quantity numeric not null,
	paid_price numeric not null,
	paid_quantity numeric not null
);

--an invoice can have many fees of different (or the same fee_type_id) applied
CREATE TABLE materialized_test.invoice_fee_detail (
	invoice_fee_id uuid not null primary key default gen_random_uuid(),
	invoice_id uuid not null references materialized_test.invoice(invoice_id),
	fee_type_id uuid not null references materialized_test.fee_type(fee_type_id),
	invoiced_fee numeric not null,
	paid_fee numeric not null
);

--table which stores rolled up totals from both the child tables of invoice and sums of those
-- to display at the invoice level.  
--shorted is the difference between invoiced and paid
CREATE TABLE materialized_test.invoice_totals_materialized (
	invoice_id uuid not null primary key references materialized_test.invoice(invoice_id),
	product_count numeric not null,
	fee_count numeric not null,
	invoiced_product_total numeric not null,
	paid_product_total numeric not null,
	invoiced_fee_total numeric not null,
	paid_fee_total numeric not null,
	invoiced_total numeric not null,
	paid_total numeric not null,
	balanced boolean not null default false
);


CREATE OR REPLACE FUNCTION materialized_test.invoice_statement_trigger()
  RETURNS trigger AS
$BODY$
DECLARE v_invoice_ids uuid[];
BEGIN
	IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
	THEN	
		SELECT array_agg(DISTINCT invoice_id)
		INTO v_invoice_ids
		FROM new_table;

	END IF;
	
	IF (TG_OP = 'DELETE')
	THEN
		SELECT array_agg(DISTINCT invoice_id)
		INTO v_invoice_ids
		FROM old_table;
	END IF;

	PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);
	
	RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION materialized_test.invoice_product_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
	v_invoice_ids uuid[]; 
BEGIN

		IF (TG_OP = 'INSERT')
		THEN
			SELECT array_agg(DISTINCT invoice_id)
			INTO v_invoice_ids 
			FROM new_table;
		END IF;

		IF (TG_OP = 'UPDATE')
		THEN

			SELECT array_agg(DISTINCT invoice_ids)
			INTO v_invoice_ids
			FROM (
				--We unnrest the invoice_ids in the concatenated arrays to de-dup
				SELECT unnest(invoice_ids) as invoice_ids 
				FROM (
					--no need for distinct keyword here as we'll do it once above
					SELECT array_agg(old_table.invoice_id) || array_agg(new_table.invoice_id) as invoice_ids
					FROM new_table
					INNER JOIN old_table
					USING (invoice_product_id)
					WHERE TRUE
					AND
					(
						   old_table.invoiced_quantity <> new_table.invoiced_quantity
						OR old_table.invoiced_price <> new_table.invoiced_price
						OR old_table.paid_quantity <> new_table.paid_quantity
						OR old_table.paid_price <> new_table.paid_price
						OR old_table.invoice_id <> new_table.invoice_id
					)
				) AS x
			) AS y;

			
		END IF;

		IF (TG_OP = 'DELETE')
		THEN

			SELECT array_agg(DISTINCT invoice_id) 
			INTO v_invoice_ids 
			FROM old_table;
		END IF;
		
	PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);

	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION materialized_test.invoice_fee_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
	v_invoice_ids uuid[];
BEGIN

	IF (TG_OP = 'INSERT')
	THEN
		SELECT array_agg(DISTINCT invoice_id)
		INTO v_invoice_ids
		FROM new_table;

	END IF;	
	
	IF (TG_OP = 'UPDATE')
	THEN

		SELECT array_agg(DISTINCT invoice_id)
		INTO v_invoice_ids
		FROM (
			SELECT unnest(invoice_ids) as invoice_ids 
			FROM 
			(
				--no need for distinct keyword here as we'll do it once above
				SELECT array_agg(old_table.invoice_id) || array_agg(new_table.invoice_id) as invoice_ids
				FROM new_table
				INNER JOIN old_table
				USING (invoice_fee_id)
				WHERE TRUE
				AND (old_table.invoiced_fee <> new_table.invoiced_fee
				    OR old_table.paid_fee <> new_table.paid_fee
				    OR old_table.invoice_id <> new_table.invoice_id)
			) AS x
		) AS y;

	END IF;	
	
	IF (TG_OP = 'DELETE')
	THEN
		SELECT array_agg(DISTINCT invoice_id)
		INTO v_invoice_ids
		FROM old_table;
	END IF;	

	PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);	

	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



CREATE OR REPLACE FUNCTION materialized_test.refresh_invoice_totals_materialized(p_invoice_ids uuid[])
  RETURNS void AS
$BODY$
  with cte as (
    SELECT invoice.invoice_id,
	COALESCE(invoice_product.product_count, 0::bigint)::integer AS product_count,
	COALESCE(invoice_fee.fee_count, 0::bigint)::integer AS fee_count,	
	ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric), 2) AS invoiced_product_total,
	ROUND(COALESCE(invoice_product.paid_amount, 0::numeric), 2) AS paid_product_total,
	ROUND(COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_fee_total,
	ROUND(COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_fee_total,
	ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_total,
	ROUND(COALESCE(invoice_product.paid_amount, 0::numeric) + COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_total,
	(invoice.invoice_total::double precision <-> (ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2))::double precision < 0.01::double precision) AS balanced 
 
   FROM materialized_test.invoice
     LEFT JOIN ( 
		SELECT invoice_fee_detail.invoice_id,
		count(invoice_fee_detail.invoice_fee_id) AS fee_count,
		sum(invoice_fee_detail.invoiced_fee) AS invoiced_amount,
		sum(invoice_fee_detail.paid_fee) AS paid_amount
		FROM materialized_test.invoice_fee_detail
		WHERE invoice_fee_detail.invoice_id IN (SELECT unnest(p_invoice_ids))
		GROUP BY invoice_fee_detail.invoice_id
     ) AS invoice_fee 
     ON invoice_fee.invoice_id = invoice.invoice_id
     LEFT JOIN ( 
		SELECT invoice_product_detail.invoice_id,
		count(invoice_product_detail.invoice_product_id) as product_count,
		sum(invoice_product_detail.invoiced_quantity * invoice_product_detail.invoiced_price) as invoiced_amount,
		sum(invoice_product_detail.paid_quantity * invoice_product_detail.paid_price) as paid_amount
		FROM materialized_test.invoice_product_detail 
		WHERE invoice_product_detail.invoice_id IN (SELECT unnest(p_invoice_ids))
		GROUP BY invoice_product_detail.invoice_id
     ) AS invoice_product 
     ON invoice_product.invoice_id = invoice.invoice_id
   WHERE invoice.invoice_id IN (SELECT unnest(p_invoice_ids))
  )
  INSERT INTO materialized_test.invoice_totals_materialized (
 	invoice_id,
 	product_count,
 	fee_count,
 	invoiced_product_total,
 	paid_product_total,
 	invoiced_fee_total,
 	paid_fee_total,
 	invoiced_total,
 	paid_total,
 	balanced
  )
  SELECT invoice_id,
 	product_count,
 	fee_count,
 	invoiced_product_total,
 	paid_product_total,
 	invoiced_fee_total,
 	paid_fee_total,
 	invoiced_total,
 	paid_total,
 	balanced 
  FROM cte
  ON CONFLICT (invoice_id) DO UPDATE SET 	
	  product_count = EXCLUDED.product_count
	, fee_count = EXCLUDED.fee_count
	, invoiced_product_total = EXCLUDED.invoiced_product_total
	, paid_product_total = EXCLUDED.paid_product_total
	, invoiced_fee_total = EXCLUDED.invoiced_fee_total
	, paid_fee_total = EXCLUDED.paid_fee_total
	, invoiced_total = EXCLUDED.invoiced_total
	, paid_total = EXCLUDED.paid_total
	, balanced = EXCLUDED.balanced;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;



CREATE OR REPLACE VIEW materialized_test.check_invoice_totals_materialized AS 
with cte as (
    SELECT invoice.invoice_id,
	COALESCE(invoice_product.product_count, 0::bigint)::integer AS product_count,
	COALESCE(invoice_fee.fee_count, 0::bigint)::integer AS fee_count,	
	ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric), 2) AS invoiced_product_total,
	ROUND(COALESCE(invoice_product.paid_amount, 0::numeric), 2) AS paid_product_total,
	ROUND(COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_fee_total,
	ROUND(COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_fee_total,
	ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_total,
	ROUND(COALESCE(invoice_product.paid_amount, 0::numeric) + COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_total,
	(invoice.invoice_total::double precision <-> (ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2))::double precision < 0.01::double precision) AS balanced 
 
   FROM materialized_test.invoice
     LEFT JOIN ( 
		SELECT invoice_fee_detail.invoice_id,
		count(invoice_fee_detail.invoice_fee_id) AS fee_count,
		sum(invoice_fee_detail.invoiced_fee) AS invoiced_amount,
		sum(invoice_fee_detail.paid_fee) AS paid_amount		 
		FROM materialized_test.invoice_fee_detail
		WHERE true
		GROUP BY invoice_fee_detail.invoice_id
     ) AS invoice_fee 
     ON invoice_fee.invoice_id = invoice.invoice_id
     LEFT JOIN ( 
		SELECT invoice_product_detail.invoice_id,
		count(invoice_product_detail.invoice_product_id) as product_count,
		sum(invoice_product_detail.invoiced_quantity * invoice_product_detail.invoiced_price) as invoiced_amount,
		sum(invoice_product_detail.paid_quantity * invoice_product_detail.paid_price) as paid_amount
		FROM materialized_test.invoice_product_detail 
		WHERE true
		GROUP BY invoice_product_detail.invoice_id
     ) AS invoice_product 
     ON invoice_product.invoice_id = invoice.invoice_id
   WHERE true
  )
  SELECT invoice_id,
 	product_count,
 	fee_count,
 	invoiced_product_total,
 	paid_product_total,
 	invoiced_fee_total,
 	paid_fee_total,
 	invoiced_total,
 	paid_total,
 	balanced 
  FROM cte;

   

CREATE TRIGGER invoice_product_detail_statement_trigger_d
  AFTER DELETE
  ON materialized_test.invoice_product_detail
  REFERENCING OLD TABLE AS old_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_product_statement_refresh_trigger();
 

CREATE TRIGGER invoice_product_detail_statement_trigger_i
  AFTER INSERT
  ON materialized_test.invoice_product_detail
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_product_statement_refresh_trigger();
 

CREATE TRIGGER invoice_product_detail_statement_trigger_u
  AFTER UPDATE ON materialized_test.invoice_product_detail
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_product_statement_refresh_trigger();  


CREATE TRIGGER invoice_fee_detail_statement_trigger_d
  AFTER DELETE
  ON materialized_test.invoice_fee_detail
  REFERENCING OLD TABLE AS old_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_fee_statement_refresh_trigger();
 

CREATE TRIGGER invoice_fee_detail_statement_trigger_i
  AFTER INSERT
  ON materialized_test.invoice_fee_detail
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_fee_statement_refresh_trigger();
 

CREATE TRIGGER invoice_fee_detail_statement_trigger_u
  AFTER UPDATE ON materialized_test.invoice_fee_detail
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_fee_statement_refresh_trigger(); 
  
CREATE OR REPLACE FUNCTION materialized_test.invoice_statement_refresh_trigger()
  RETURNS trigger AS
$BODY$
DECLARE v_invoice_ids uuid[];
BEGIN
	IF (TG_OP = 'INSERT')
	THEN
		SELECT array_agg(invoice_id)
		INTO v_invoice_ids
		FROM new_table;
	END IF;
	
	PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);
	
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER invoice_statement_trigger_d
  AFTER DELETE ON materialized_test.invoice
  REFERENCING OLD TABLE AS old_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_statement_refresh_trigger();
 

CREATE TRIGGER invoice_statement_trigger_i
  AFTER INSERT ON materialized_test.invoice
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_statement_refresh_trigger();
 

CREATE TRIGGER invoice_statement_trigger_u
  AFTER UPDATE ON materialized_test.invoice
  REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE materialized_test.invoice_statement_refresh_trigger(); 

