telegram-bot-for-manipulate-orders/app/database/models.py

from sqlalchemy import Column, Integer, String, Date, ForeignKey, func, Null, BIGINT
from sqlalchemy.dialects.postgresql import ENUM
from sqlalchemy.orm import relationship, DeclarativeBase

status_enum = ENUM('Выполнено', 'В процессе', 'Создано', 'Ожидание комплектующих', name='status')
job_title = ENUM('Начальник цеха', 'Мастер', 'Сборщик', name='job_title')


class Base(DeclarativeBase):
    pass


class Worker(Base):
    """
    id           SERIAL PRIMARY KEY,
    telegram_id  INTEGER UNIQUE NOT NULL,
    name         VARCHAR        NOT NULL,
    email        VARCHAR(50),
    phone_number VARCHAR(20)    NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP
    """
    __tablename__ = "workers"

    id = Column(Integer, primary_key=True, autoincrement=True)
    telegram_id = Column(BIGINT, unique=True, nullable=False)
    name = Column(String, nullable=False)
    email = Column(String, nullable=True)
    phone_number = Column(String, default=None)
    job_title = Column(job_title, default='Сборщик'),
    created_at = Column(Date, server_default=func.now())
    updated_at = Column(Date, onupdate=func.now())


class Order(Base):
    """
    id                SERIAL PRIMARY KEY,
    name              VARCHAR,
    worker_id         INTEGER REFERENCES workers (id),
    status_id         status    DEFAULT 'Создано',
    counterparty      VARCHAR(50),
    customer          VARCHAR NOT NULL,
    commencement_work DATE,
    end_work          DATE,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description       VARCHAR   DEFAULT NULL
    """
    __tablename__ = "orders"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    worker_id = Column(BIGINT, ForeignKey('workers.telegram_id'), nullable=False)
    status_id = Column(status_enum)
    counterparty = Column(String)
    customer = Column(String, nullable=False)
    commencement_work = Column(Date, nullable=True)
    end_work = Column(Date, nullable=True)
    created_at = Column(Date, server_default=func.now())
    description = Column(String, default=Null)
    user = relationship("Worker", backref="orders")


class Component(Base):
    """
    id          SERIAL PRIMARY KEY,
    name        VARCHAR NOT NULL,
    description VARCHAR NULL
    """
    __tablename__ = "components"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String, default=Null)


class OrderComponent(Base):
    """
    id           SERIAL PRIMARY KEY,
    order_id     INTEGER REFERENCES orders (id),
    component_id INTEGER REFERENCES components (id),
    quantity     INTEGER DEFAULT 1
    """
    __tablename__ = "order_components"

    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'))
    component_id = Column(Integer, ForeignKey('components.id'))
    quantity = Column(Integer, default=1)

    order = relationship("Order", backref="order_components")
    component = relationship("Component", backref="order_components")