---

-- DDL
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    phone       VARCHAR(20),
    email       VARCHAR(100)
);

CREATE TABLE products (
    product_id     SERIAL PRIMARY KEY,
    name           VARCHAR(150) NOT NULL,
    price          DECIMAL(10,2) NOT NULL,
    unit           VARCHAR(20)  NOT NULL,
    stock_quantity INT DEFAULT 0,
    category_id    INT REFERENCES categories(category_id),
    supplier_id    INT REFERENCES suppliers(supplier_id)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    full_name   VARCHAR(150) NOT NULL,
    position    VARCHAR(50)  NOT NULL,
    phone       VARCHAR(20)
);

CREATE TABLE sales (
    sale_id      SERIAL PRIMARY KEY,
    sale_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    employee_id  INT REFERENCES employees(employee_id),
    total_amount DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE sale_items (
    sale_item_id  SERIAL PRIMARY KEY,
    sale_id       INT REFERENCES sales(sale_id) ON DELETE CASCADE,
    product_id    INT REFERENCES products(product_id),
    quantity      INT NOT NULL,
    price_at_sale DECIMAL(10,2) NOT NULL
);