CREATE TABLE product_types(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE products(
    id INTEGER PRIMARY KEY,
    type_id INTEGER NOT NULL REFERENCES product_types(id),
    height INTEGER NOT NULL,
    width INTEGER NOT NULL,
    depth INTEGER NOT NULL,
    max_storage_duration INTERVAL NULL
);

CREATE TYPE PARTNER_TYPE AS ENUM('SUPPLIER', 'ISSUER');

CREATE TABLE partners(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    tax_number TEXT NOT NULL,
    type PARTNER_TYPE NOT NULL,
    address TEXT NOT NULL
);

CREATE TABLE partner_contacts(
    id INTEGER PRIMARY KEY,
    partner_id INTEGER NOT NULL REFERENCES partners(id),
    surname TEXT NOT NULL,
    name TEXT NOT NULL,
    position TEXT NOT NULL,
    phone TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE storage_slots(
    id INTEGER PRIMARY KEY,
    location TEXT NOT NULL,
    height INTEGER NOT NULL,
    width INTEGER NOT NULL,
    depth INTEGER NOT NULL
);

CREATE TABLE product_storage_statues(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE product_slots(
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id),
    slot_id INTEGER NOT NULL REFERENCES storage_slots(id),
    amount INTEGER NOT NULL CHECK (amount > 0),
    status_id INTEGER NOT NULL REFERENCES product_storage_statues(id),
    placement_time TIMESTAMP NOT NULL
);

CREATE TYPE OPERATION_TYPE AS ENUM('SUPPLY', 'ISSUE');

CREATE TABLE operation_statues(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE operations(
    id INTEGER PRIMARY KEY,
    partner_id INTEGER NOT NULL REFERENCES partners(id),
    responsible_id INTEGER NOT NULL REFERENCES partner_contacts(id),
    type OPERATION_TYPE NOT NULL,
    status_id INTEGER NOT NULL REFERENCES operation_statues(id),
    date_created TIMESTAMP NOT NULL,
    date_finished TIMESTAMP NULL
);

CREATE TABLE operation_products(
    id INTEGER PRIMARY KEY,
    operation_id INTEGER NOT NULL REFERENCES operations(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    amount INTEGER NOT NULL CHECK (amount > 0)
);