# AUX: InvoiceItem net & gross
DROP VIEW IF EXISTS invoice_item_total_view;
CREATE VIEW invoice_item_total_view AS
SELECT
  B.id AS id,
  B.invoice_id AS invoice_id,
  (B.unitary_cost * B.quantity) AS base,
  (B.unitary_cost * B.quantity) * (B.discount / 100) AS discount,
  (B.unitary_cost * B.quantity) * (100 - B.discount) / 100 AS net,
  SUM(T.value)/100 AS tax_factor,
  ((B.unitary_cost * B.quantity) * (100 - B.discount) / 100 ) * (1 + (SUM(T.value)/100)) AS gross
FROM
  invoice_item AS B
  INNER JOIN
  invoice_item_tax AS T
  ON B.id = T.invoice_item_id
GROUP BY
  B.invoice_id,B.id;

# AUX: Invoice net & gross
DROP VIEW IF EXISTS invoice_total;
CREATE VIEW invoice_total AS
SELECT
  A.id AS invoice_id,
  SUM(B.base) AS base,
  SUM(B.discount) AS discount,
  SUM(B.net) AS net,
  SUM(B.net * B.tax_factor) AS taxes,
  SUM(B.gross) AS gross,
  (SELECT SUM(payment.amount) FROM payment where invoice_id=A.id) as paid,
  (SELECT (SUM(B.gross) - SUM(payment.amount)) FROM payment where invoice_id=A.id) as due
FROM
  invoice AS A
  INNER JOIN
  invoice_item_total_view AS B
  ON A.id = B.invoice_id
GROUP BY
  A.id;