-- Your SQL goes here
CREATE TABLE products (
       id INTEGER PRIMARY KEY NOT NULL,
       name TEXT UNIQUE NOT NULL,
       price REAL NOT NULL,
       owner TEXT NOT NULL,
       created_at TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL,
       updated_at TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE orders (
       id INTEGER PRIMARY KEY NOT NULL,
       created_at TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL,
       updated_at TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE orders_products (
       order_id INTEGER NOT NULL,
       product_id INTEGER NOT NULL,
       quantity INTEGER NOT NULL,
       FOREIGN KEY (order_id) REFERENCES orders(id),
       FOREIGN KEY (product_id) REFERENCES products(id),
       PRIMARY KEY (order_id, product_id)
);

INSERT INTO products (name, price, owner)
VALUES
	("Pfadiburger", 5.1, "Pfadfinder"),
	("Kuchen", 2, "Sportunion"),
	("Apfelsaft", 1.5, "Sportunion"),
	("Käsekrainer", 4, "Pfadfinder"),
	("Süßes", 0.2, "Pfadfinder"),
	("Capuccino", 3.5, "Deluke"),
	("Espresso", 2.5, "Deluke");

INSERT INTO orders DEFAULT VALUES;
INSERT INTO orders_products (order_id, product_id, quantity)
VALUES
	(1, 1, 3),
	(1, 2, 1),
	(1, 5, 1);

INSERT INTO orders DEFAULT VALUES;
INSERT INTO orders_products (order_id, product_id, quantity)
VALUES
	(2, 4, 2),
	(2, 6, 2),
	(2, 2, 1);

CREATE VIEW totals_per_owner_and_order AS
SELECT id, owner, SUM(total_price) AS total FROM (
    SELECT o.id, p.owner, p.price*op.quantity AS total_price FROM orders AS o
    INNER JOIN orders_products AS op ON o.id = op.order_id
    INNER JOIN products AS p ON p.id = op.product_id
  )
GROUP BY id, owner;