DROP TABLE IF EXISTS ProductionItems CASCADE;
DROP TABLE IF EXISTS OrderItems CASCADE;
DROP TABLE IF EXISTS Specification CASCADE;
DROP TABLE IF EXISTS Production CASCADE;
DROP TABLE IF EXISTS Orders CASCADE;
DROP TABLE IF EXISTS Products CASCADE;
DROP TABLE IF EXISTS Materials CASCADE;
DROP TABLE IF EXISTS Customers CASCADE;
DROP TABLE IF EXISTS Units CASCADE;

CREATE TABLE Units (
    unit_id  SERIAL PRIMARY KEY,
    name     VARCHAR(50) NOT NULL
);

CREATE TABLE Customers (
    customer_id  SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    inn          VARCHAR(20),
    address      VARCHAR(200),
    phone        VARCHAR(20),
    is_supplier  BOOLEAN DEFAULT FALSE,
    is_buyer     BOOLEAN DEFAULT FALSE
);

CREATE TABLE Products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    unit_id     INT NOT NULL REFERENCES Units(unit_id),
    price       NUMERIC(10,2) NOT NULL
);

CREATE TABLE Materials (
    material_id  SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    unit_id      INT NOT NULL REFERENCES Units(unit_id),
    price        NUMERIC(10,2) NOT NULL
);

CREATE TABLE Specification (
    spec_id      SERIAL PRIMARY KEY,
    product_id   INT NOT NULL REFERENCES Products(product_id),
    material_id  INT NOT NULL REFERENCES Materials(material_id),
    quantity     NUMERIC(10,4) NOT NULL
);

CREATE TABLE Orders (
    order_id     SERIAL PRIMARY KEY,
    number       VARCHAR(20) NOT NULL,
    date         DATE NOT NULL,
    customer_id  INT NOT NULL REFERENCES Customers(customer_id),
    total_amount NUMERIC(12,2) DEFAULT 0
);

CREATE TABLE OrderItems (
    item_id     SERIAL PRIMARY KEY,
    order_id    INT NOT NULL REFERENCES Orders(order_id),
    product_id  INT NOT NULL REFERENCES Products(product_id),
    quantity    INT NOT NULL,
    price       NUMERIC(10,2) NOT NULL,
    amount      NUMERIC(12,2) NOT NULL
);

CREATE TABLE Production (
    production_id  SERIAL PRIMARY KEY,
    number         VARCHAR(20) NOT NULL,
    date           DATE NOT NULL,
    total_amount   NUMERIC(12,2) DEFAULT 0
);

CREATE TABLE ProductionItems (
    item_id        SERIAL PRIMARY KEY,
    production_id  INT NOT NULL REFERENCES Production(production_id),
    product_id     INT NOT NULL REFERENCES Products(product_id),
    quantity       INT NOT NULL
);