CREATE TABLE category(
    id SERIAL PRIMARY KEY,
    name VARCHAR (50) not null,
    description TEXT
);

CREATE TABLE tax (
    id SERIAL PRIMARY KEY,
    value INT
)

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    buy_price MONEY,
    sell_price MONEY NOT NULL,
    quantity INT,
    description TEXT,
    FOREIGN KEY (tax_id) REFERENCES tax (id),
    FOREIGN KEY (category_id) REFERENCES category (id)
);

CREATE TABLE client(
    id SERIAL PRIMARY KEY,
    firstname VARCHAR (100) NOT NULL,
    lastname VARCHAR (100) NOT NULL,
    email VARCHAR (100) NOT NULL,
);

CREATE TABLE adress(
    id SERIAL PRIMARY KEY,
    active BOOLEAN DEFAULT true,
    city VARCHAR (100) NOT NULL,
    postcode VARCHAR (6) NOT NULL,
    street VARCHAR (100),
    number VARCHAR (100),
    FOREIGN KEY (client_id) REFERENCES client (id)
);

CREATE TABLE order (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES client (id),
);

CREATE TABLE order_product (
    order_id int REFERENCES order (id),
    product_id int REFERENCES product (product_id),
    amount numeric NOT NULL DEFAULT 1,
    CONSTRAINT order_product_pkey PRIMARY KEY (order_id, product_id)  -- explicit pk
);