DROP TABLE IF EXISTS Specifications;
DROP TABLE IF EXISTS Order_items;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Materials;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Customers;

CREATE TABLE Customers (
    id       VARCHAR(20)  PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    inn      VARCHAR(20),
    addres   VARCHAR(100),
    phone    VARCHAR(20),
    salesman BOOLEAN      NOT NULL DEFAULT FALSE,
    buyer    BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE Products (
    id   INT          PRIMARY KEY, -- AUTO_INCREMENT
    name VARCHAR(100),
    unit VARCHAR(10)
);

CREATE TABLE Materials (
    id    INT          PRIMARY KEY, -- AUTO_INCREMENT
    name  VARCHAR(100),
    unit  VARCHAR(10),
    price DECIMAL(10,2)
);

CREATE TABLE Orders (
    id          INT         PRIMARY KEY, -- AUTO_INCREMENT
    order_date  DATE,
    customer_id VARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

CREATE TABLE Order_items (
    id         INT PRIMARY KEY, -- AUTO_INCREMENT
    quantity   INT,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES Orders(id),
    FOREIGN KEY (product_id) REFERENCES Products(id)
);

CREATE TABLE Specifications (
    id          INT PRIMARY KEY, -- AUTO_INCREMENT
    name        VARCHAR(100),
    quantity    FLOAT,
    product_id  INT NOT NULL,
    material_id INT NOT NULL,
    FOREIGN KEY (product_id)  REFERENCES Products(id),
    FOREIGN KEY (material_id) REFERENCES Materials(id)
);