USE is_invoice;

DROP TABLE IF EXISTS customer, orders, products, order_products;

create table customer(
    id int  primary key,
    name varchar(80) not null default 'Anton Hofmann',
    address varchar(80) not null default 'address n/a'
);

CREATE TABLE orders (
    id INT,
    user_id INT,
    order_date DATE,

    PRIMARY KEY(id),
    FOREIGN KEY (user_id) REFERENCES customer(id)
    );

CREATE TABLE products (
    id INT,
    descr VARCHAR(80) NOT NULL,
    price FLOAT NOT NULL,

    PRIMARY KEY(id)
    );

CREATE TABLE order_products (
    pos INT NOT NULL,
    order_id INT,
    user_id INT,
    product_id INT,
    amount INT,

    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (user_id) REFERENCES customer(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
    );

INSERT INTO customer VALUES (1, "Person 1", "Kolpinghaus");
INSERT INTO customer VALUES (2, "Person 2", "Kolpinghaus");
INSERT INTO customer VALUES (3, "Person 3", "Kolpinghaus");

INSERT INTO products VALUES (1, "Drucker MSP45", 1000);
INSERT INTO products VALUES (2, "Bildschirm VGA", 500);

INSERT INTO orders VALUES (1, 1, "1970-01-01");
INSERT INTO orders VALUES (2, 2, "1870-02-02");

INSERT INTO order_products VALUES (1, 1, 1, 1, 5);
INSERT INTO order_products VALUES (2, 1, 1, 2, 100);
INSERT INTO order_products VALUES (2, 1, 1, 1, 4);

-- Get Product-ID of most sold product

SELECT product_id, sum(amount)
FROM order_products
GROUP BY product_id
ORDER BY sum(amount) desc;