core/models.py - master - vorobei/erp

core/models.py
955 строк42 KB
vorobei
feat(core): интеграция модели БД, email-сервиса и админ-интерфейса
02 июл 2026, 13:38
02 июл 2026, 13:38cdf3188
# core/models.py
from core.db import db
from datetime import datetime, date
import json


# ===== МОДЕЛИ ПОЛЬЗОВАТЕЛЕЙ И РОЛЕЙ =====
class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String(100), unique=True, nullable=False)
    password_hash = db.Column(db.String(200), nullable=False)
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
    is_protected = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    role = db.relationship('Role', backref='users')


class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
    description = db.Column(db.String(200))


class Permission(db.Model):
    __tablename__ = 'permissions'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    description = db.Column(db.String(200))


class RolePermission(db.Model):
    __tablename__ = 'role_permissions'
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), primary_key=True)
    permission_id = db.Column(db.Integer, db.ForeignKey('permissions.id'), primary_key=True)


# ===== МОДЕЛИ СЧЕТОВ =====
class Invoice(db.Model):
    __tablename__ = 'invoices'
    id = db.Column(db.Integer, primary_key=True)
    external_id = db.Column(db.String(100), unique=True, nullable=True)
    number = db.Column(db.String(50), unique=True, nullable=False)
    customer = db.Column(db.String(200))
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    additional_info = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)


class InvoiceItem(db.Model):
    __tablename__ = 'invoice_items'
    id = db.Column(db.Integer, primary_key=True)
    external_id = db.Column(db.String(100), unique=True, nullable=True)
    invoice_id = db.Column(db.Integer, db.ForeignKey('invoices.id'), nullable=False)
    line_number = db.Column(db.Integer, nullable=False)
    product_name = db.Column(db.String(200), nullable=False)
    drawing = db.Column(db.String(100))
    concrete_class = db.Column(db.String(20))
    length_meters = db.Column(db.Float)
    width_meters = db.Column(db.Float)
    volume_m3 = db.Column(db.Float)
    frost_resistance = db.Column(db.String(20))
    water_resistance = db.Column(db.String(20))
    planned_quantity = db.Column(db.Float, nullable=False, default=0)
    quantity_produced = db.Column(db.Float, default=0)
    surface_category = db.Column(db.String(10))
    release_strength_percent = db.Column(db.Integer, default=90)
    additional_info = db.Column(db.Text)
    bridge_inspector = db.Column(db.String(200))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    invoice = db.relationship('Invoice', backref='items', lazy=True)

    @property
    def remaining(self):
        return max(0, (self.planned_quantity or 0) - (self.quantity_produced or 0))


# ===== МОДЕЛИ ПРОИЗВОДСТВА =====
class WorkCenter(db.Model):
    __tablename__ = 'work_centers'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    description = db.Column(db.String(200))
    order_index = db.Column(db.Integer, default=0)


class ProductionTask(db.Model):
    __tablename__ = 'production_tasks'
    id = db.Column(db.Integer, primary_key=True)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), unique=True, nullable=False)
    status = db.Column(db.String(20), default='planned')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    invoice_item = db.relationship('InvoiceItem', backref='production_task', lazy=True)
    schedule = db.relationship('ProductionScheduleItem', backref='task', lazy=True, cascade='all, delete-orphan')

    @property
    def remaining(self):
        return max(0, (self.invoice_item.planned_quantity or 0) - (self.invoice_item.quantity_produced or 0))


class ProductionLine(db.Model):
    __tablename__ = 'production_lines'
    id = db.Column(db.Integer, primary_key=True)
    work_center_id = db.Column(db.Integer, db.ForeignKey('work_centers.id'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    length_meters = db.Column(db.Float, nullable=False)
    order_index = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    work_center = db.relationship('WorkCenter', backref='production_lines')
    rivers = db.relationship('ProductionRiver', back_populates='line', cascade='all, delete-orphan')
    schedule_items = db.relationship('ProductionScheduleItem', back_populates='line', lazy=True)


class ProductionRiver(db.Model):
    __tablename__ = 'production_rivers'
    id = db.Column(db.Integer, primary_key=True)
    line_id = db.Column(db.Integer, db.ForeignKey('production_lines.id'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    order_index = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    line = db.relationship('ProductionLine', back_populates='rivers')
    schedule_items = db.relationship('ProductionScheduleItem', back_populates='river', lazy=True)


class ProductionScheduleItem(db.Model):
    __tablename__ = 'production_schedule_items'
    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.Integer, db.ForeignKey('production_tasks.id'), nullable=False)
    planned_date = db.Column(db.Date, nullable=False)
    planned_quantity = db.Column(db.Integer, default=0)
    actual_quantity = db.Column(db.Integer, default=0)
    shift = db.Column(db.Integer, default=1, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    work_center_id = db.Column(db.Integer, db.ForeignKey('work_centers.id'), nullable=False)
    line_id = db.Column(db.Integer, db.ForeignKey('production_lines.id'), nullable=False)
    river_id = db.Column(db.Integer, db.ForeignKey('production_rivers.id'), nullable=False)
    area_items_json = db.Column(db.Text, nullable=True)
    
    work_center = db.relationship('WorkCenter', backref='schedule_items')
    line = db.relationship('ProductionLine', back_populates='schedule_items')
    river = db.relationship('ProductionRiver', back_populates='schedule_items')
    events = db.relationship('ProductionEvent', backref='schedule_item', lazy=True, cascade='all, delete-orphan')
    sync_status = db.relationship('SyncStatus', backref='schedule_item', uselist=False, cascade='all, delete-orphan')

    def get_area_items(self):
        if self.area_items_json:
            try:
                return json.loads(self.area_items_json)
            except:
                return []
        return []

    def set_area_items(self, items):
        self.area_items_json = json.dumps(items) if items else None


class ProductionEvent(db.Model):
    __tablename__ = 'production_events'
    id = db.Column(db.Integer, primary_key=True)
    schedule_item_id = db.Column(db.Integer, db.ForeignKey('production_schedule_items.id'), nullable=False)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    event_type = db.Column(db.String(50), default='produced')
    shift = db.Column(db.Integer)
    operator_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    confirmed_at = db.Column(db.DateTime, default=datetime.utcnow)
    has_deviation = db.Column(db.Boolean, default=False)
    deviation_reason = db.Column(db.String(50))
    deviation_note = db.Column(db.Text)
    start_time = db.Column(db.DateTime)
    end_time = db.Column(db.DateTime)
    team_name = db.Column(db.String(100))
    
    invoice_item = db.relationship('InvoiceItem', backref='production_events')
    operator = db.relationship('User', foreign_keys=[operator_id])
    
    __table_args__ = (
        db.Index('idx_production_events_schedule_item', 'schedule_item_id'),
        db.Index('idx_production_events_invoice_item', 'invoice_item_id'),
        db.Index('idx_production_events_confirmed_at', 'confirmed_at'),
        db.Index('idx_production_events_has_deviation', 'has_deviation'),
    )
    
    def to_dict(self):
        return {
            'id': self.id,
            'schedule_item_id': self.schedule_item_id,
            'invoice_item_id': self.invoice_item_id,
            'quantity': self.quantity,
            'event_type': self.event_type,
            'shift': self.shift,
            'operator_id': self.operator_id,
            'operator_name': self.operator.full_name if self.operator else None,
            'confirmed_at': self.confirmed_at.isoformat() if self.confirmed_at else None,
            'has_deviation': self.has_deviation,
            'deviation_reason': self.deviation_reason,
            'deviation_reason_label': self.get_deviation_reason_label(),
            'deviation_note': self.deviation_note,
            'start_time': self.start_time.isoformat() if self.start_time else None,
            'end_time': self.end_time.isoformat() if self.end_time else None,
            'team_name': self.team_name,
        }
    
    def get_deviation_reason_label(self):
        labels = {
            'materials': 'Нехватка материалов',
            'equipment': 'Поломка оборудования',
            'team': 'Отсутствие бригады',
            'quality': 'Низкое качество (переделка)',
            'priority': 'Смена приоритетов (другой заказ)',
            'weather': 'Погодные условия',
            'other': 'Другое',
        }
        return labels.get(self.deviation_reason, self.deviation_reason or '—')


class SyncStatus(db.Model):
    __tablename__ = 'sync_status'
    id = db.Column(db.Integer, primary_key=True)
    schedule_item_id = db.Column(db.Integer, db.ForeignKey('production_schedule_items.id'), unique=True, nullable=False)
    planned_quantity = db.Column(db.Float, default=0)
    produced_quantity = db.Column(db.Float, default=0)
    accepted_quantity = db.Column(db.Float, default=0)
    rejected_quantity = db.Column(db.Float, default=0)
    sync_gap = db.Column(db.Float, default=0)
    quality_loss = db.Column(db.Float, default=0)
    efficiency = db.Column(db.Float, default=0)
    status = db.Column(db.String(20), default='planned')
    last_event_at = db.Column(db.DateTime)
    last_sync_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    __table_args__ = (
        db.Index('idx_sync_status_status', 'status'),
        db.Index('idx_sync_status_efficiency', 'efficiency'),
    )
    
    def update_from_events(self):
        events = ProductionEvent.query.filter_by(schedule_item_id=self.schedule_item_id).all()
        
        self.produced_quantity = sum(e.quantity for e in events if e.event_type == 'produced')
        self.accepted_quantity = sum(e.quantity for e in events if e.event_type == 'accepted')
        self.rejected_quantity = sum(e.quantity for e in events if e.event_type == 'rejected')
        
        if self.schedule_item:
            self.planned_quantity = self.schedule_item.planned_quantity or 0
        
        self.sync_gap = self.planned_quantity - self.produced_quantity
        self.efficiency = (self.produced_quantity / self.planned_quantity * 100) if self.planned_quantity > 0 else 0
        self.quality_loss = (self.rejected_quantity / self.produced_quantity * 100) if self.produced_quantity > 0 else 0
        
        if self.produced_quantity >= self.planned_quantity and self.planned_quantity > 0:
            self.status = 'completed'
        elif self.produced_quantity > 0:
            self.status = 'in_progress'
        else:
            self.status = 'planned'
        
        if self.schedule_item and self.schedule_item.planned_date < date.today() and self.status != 'completed':
            self.status = 'delayed'
        
        if self.status == 'in_progress' and self.produced_quantity > self.planned_quantity * 0.5:
            self.status = 'partially_completed'
        
        if events:
            self.last_event_at = max(e.confirmed_at for e in events)
        
        self.last_sync_at = datetime.utcnow()
        db.session.commit()
    
    def to_dict(self):
        return {
            'id': self.id,
            'schedule_item_id': self.schedule_item_id,
            'planned_quantity': self.planned_quantity,
            'produced_quantity': self.produced_quantity,
            'accepted_quantity': self.accepted_quantity,
            'rejected_quantity': self.rejected_quantity,
            'sync_gap': self.sync_gap,
            'quality_loss': round(self.quality_loss, 1),
            'efficiency': round(self.efficiency, 1),
            'status': self.status,
            'status_label': self.get_status_label(),
            'last_event_at': self.last_event_at.isoformat() if self.last_event_at else None,
            'last_sync_at': self.last_sync_at.isoformat() if self.last_sync_at else None,
        }
    
    def get_status_label(self):
        labels = {
            'planned': 'Запланировано',
            'in_progress': 'В работе',
            'completed': 'Выполнено',
            'delayed': 'Задерживается',
            'partially_completed': 'Частично выполнено',
        }
        return labels.get(self.status, self.status)


# ===== МОДЕЛИ КОНТРОЛЯ КАЧЕСТВА =====
class DefectReport(db.Model):
    __tablename__ = 'defect_reports'
    id = db.Column(db.Integer, primary_key=True)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), nullable=False)
    production_task_id = db.Column(db.Integer, db.ForeignKey('production_tasks.id'), nullable=True)
    production_event_id = db.Column(db.Integer, db.ForeignKey('production_events.id'), nullable=True)
    batch_number = db.Column(db.String(100), nullable=False)
    product_article = db.Column(db.String(200), nullable=False)
    quantity = db.Column(db.Integer, default=1)
    inspection_date = db.Column(db.String(30))
    inspector_name = db.Column(db.String(100), nullable=False)
    result = db.Column(db.String(30))
    defects_json = db.Column(db.Text)
    notes = db.Column(db.Text)
    photos_json = db.Column(db.Text)
    production_date = db.Column(db.String(20))
    work_center_id = db.Column(db.Integer, db.ForeignKey('work_centers.id'))
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    invoice_item = db.relationship('InvoiceItem', backref='defect_reports')
    production_task = db.relationship('ProductionTask', backref='defect_reports')
    production_event = db.relationship('ProductionEvent', backref='defect_reports')


class OperationalControl(db.Model):
    __tablename__ = 'operational_control'
    id = db.Column(db.Integer, primary_key=True)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), nullable=False)
    production_task_id = db.Column(db.Integer, db.ForeignKey('production_tasks.id'), nullable=True)
    batch_number = db.Column(db.String(100), nullable=False)
    product_article = db.Column(db.String(200), nullable=False)
    quantity = db.Column(db.Integer, default=1)
    inspection_date = db.Column(db.String(30))
    inspector_name = db.Column(db.String(100), nullable=False)
    result = db.Column(db.String(30))
    defects_json = db.Column(db.Text)
    notes = db.Column(db.Text)
    photos_json = db.Column(db.Text)
    work_center_id = db.Column(db.Integer, db.ForeignKey('work_centers.id'))
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    invoice_item = db.relationship('InvoiceItem', backref='operational_controls')
    production_task = db.relationship('ProductionTask', backref='operational_controls')


# ===== МОДЕЛИ СКЛАДА ГОТОВОЙ ПРОДУКЦИИ =====
class StockBatch(db.Model):
    __tablename__ = 'stock_batches'
    id = db.Column(db.Integer, primary_key=True)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), nullable=False)
    product_name = db.Column(db.String(200), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    remaining_quantity = db.Column(db.Float, nullable=False)
    production_date = db.Column(db.Date, nullable=False)
    receipt_date = db.Column(db.Date, nullable=False)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    invoice_item = db.relationship('InvoiceItem', backref='batches')
    user = db.relationship('User', backref='stock_batches')
    movements = db.relationship('StockMovement', backref='batch', lazy=True)


class StockMovement(db.Model):
    __tablename__ = 'stock_movements'
    id = db.Column(db.Integer, primary_key=True)
    batch_id = db.Column(db.Integer, db.ForeignKey('stock_batches.id'), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    movement_type = db.Column(db.String(20), nullable=False)
    reference_id = db.Column(db.Integer)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    user = db.relationship('User', backref='stock_movements')


class Shipment(db.Model):
    __tablename__ = 'shipments'
    id = db.Column(db.Integer, primary_key=True)
    shipment_date = db.Column(db.Date, nullable=False, default=date.today)
    customer = db.Column(db.String(200))
    vehicle_number = db.Column(db.String(50))
    driver_name = db.Column(db.String(100))
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    items = db.relationship('ShipmentItem', backref='shipment', cascade='all, delete-orphan')
    user = db.relationship('User', backref='shipments')


class ShipmentItem(db.Model):
    __tablename__ = 'shipment_items'
    id = db.Column(db.Integer, primary_key=True)
    shipment_id = db.Column(db.Integer, db.ForeignKey('shipments.id'), nullable=False)
    batch_id = db.Column(db.Integer, db.ForeignKey('stock_batches.id'), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    
    batch = db.relationship('StockBatch', backref='shipment_items')


class RejectWarehouse(db.Model):
    __tablename__ = 'reject_warehouse'
    id = db.Column(db.Integer, primary_key=True)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), nullable=True)
    product_name = db.Column(db.String(200), nullable=False)
    quantity = db.Column(db.Float, nullable=False)
    notes = db.Column(db.Text)
    defect_report_id = db.Column(db.Integer, db.ForeignKey('defect_reports.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    invoice_item = db.relationship('InvoiceItem', backref='reject_items')


# ===== МОДЕЛИ ПРОТОКОЛОВ (ЛАБОРАТОРИЯ) =====
class RebarTest(db.Model):
    __tablename__ = 'rebar_tests'
    id = db.Column(db.Integer, primary_key=True)
    protocol_number = db.Column(db.String(50), nullable=False)
    test_date = db.Column(db.Date, nullable=False)
    steel_class = db.Column(db.String(50), nullable=False)
    diameter = db.Column(db.Float, nullable=False)
    batch_number = db.Column(db.String(100))
    manufacturer = db.Column(db.String(200))
    certificate_number = db.Column(db.String(100))
    yield_strength = db.Column(db.Float)
    tensile_strength = db.Column(db.Float)
    elongation = db.Column(db.Float)
    conclusion = db.Column(db.String(50), default='accepted')
    notes = db.Column(db.Text)
    gost_standard = db.Column(db.String(50), default='ГОСТ 34028-2016')
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    creator = db.relationship('User', backref='rebar_tests', lazy=True)

    def get_requirements(self):
        requirements = {
            'А240': {'yield': 240, 'tensile': 360, 'elongation': 9},
            'А400': {'yield': 390, 'tensile': 590, 'elongation': 16},
            'А500С': {'yield': 500, 'tensile': 600, 'elongation': 14},
            'А600': {'yield': 600, 'tensile': 700, 'elongation': 10},
            'А800': {'yield': 800, 'tensile': 900, 'elongation': 8},
        }
        return requirements.get(self.steel_class, {})


class ConcreteTest(db.Model):
    __tablename__ = 'concrete_tests'
    id = db.Column(db.Integer, primary_key=True)
    protocol_number = db.Column(db.String(50), nullable=False)
    test_date = db.Column(db.Date, nullable=False)
    concrete_class = db.Column(db.String(20), nullable=False)
    sample_type = db.Column(db.String(50))
    batch_number = db.Column(db.String(100))
    manufacturer = db.Column(db.String(200))
    required_strength = db.Column(db.Float)
    actual_strength = db.Column(db.Float)
    sample_count = db.Column(db.Integer, default=3)
    strength_values = db.Column(db.Text)
    density = db.Column(db.Float)
    water_cement_ratio = db.Column(db.Float)
    conclusion = db.Column(db.String(50), default='accepted')
    notes = db.Column(db.Text)
    gost_standard = db.Column(db.String(50), default='ГОСТ 10180-2012')
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    creator = db.relationship('User', backref='concrete_tests', lazy=True)

    def get_requirements(self):
        requirements = {
            'B15': {'strength': 15.0},
            'B20': {'strength': 20.0},
            'B22.5': {'strength': 22.5},
            'B25': {'strength': 25.0},
            'B30': {'strength': 30.0},
            'B35': {'strength': 35.0},
            'B40': {'strength': 40.0},
        }
        return requirements.get(self.concrete_class, {'strength': 0})

    def get_strength_values_list(self):
        return json.loads(self.strength_values) if self.strength_values else []


class ConcreteCubeTest(db.Model):
    __tablename__ = 'concrete_cube_tests'
    id = db.Column(db.Integer, primary_key=True)
    test_date = db.Column(db.Date, nullable=False)
    manufacture_date = db.Column(db.Date, nullable=False)
    age_days = db.Column(db.Integer, nullable=False)
    concrete_class = db.Column(db.String(50), nullable=False)
    cube1_length = db.Column(db.Float, nullable=False, default=100)
    cube1_width = db.Column(db.Float, nullable=False, default=100)
    cube1_height = db.Column(db.Float, nullable=False, default=100)
    cube1_mass = db.Column(db.Float, nullable=False)
    cube1_load = db.Column(db.Float, nullable=False)
    cube2_length = db.Column(db.Float, nullable=False, default=100)
    cube2_width = db.Column(db.Float, nullable=False, default=100)
    cube2_height = db.Column(db.Float, nullable=False, default=100)
    cube2_mass = db.Column(db.Float, nullable=False)
    cube2_load = db.Column(db.Float, nullable=False)
    strength_mpa = db.Column(db.Float, nullable=False)
    strength_percent = db.Column(db.Float, nullable=True)
    density_kgm3 = db.Column(db.Float, nullable=True)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    notes = db.Column(db.Text, nullable=True)
    creator = db.relationship('User', backref='cube_tests')

    def to_dict(self):
        return {
            'id': self.id,
            'test_date': self.test_date.strftime('%Y-%m-%d'),
            'manufacture_date': self.manufacture_date.strftime('%Y-%m-%d'),
            'age_days': self.age_days,
            'concrete_class': self.concrete_class,
            'cube1': {
                'length': self.cube1_length,
                'width': self.cube1_width,
                'height': self.cube1_height,
                'mass': self.cube1_mass,
                'load': self.cube1_load
            },
            'cube2': {
                'length': self.cube2_length,
                'width': self.cube2_width,
                'height': self.cube2_height,
                'mass': self.cube2_mass,
                'load': self.cube2_load
            },
            'strength_mpa': self.strength_mpa,
            'strength_percent': self.strength_percent,
            'density_kgm3': self.density_kgm3,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'notes': self.notes
        }


# ===== МОДЕЛИ БСУ =====
class BsuBatch(db.Model):
    __tablename__ = 'bsu_batches'
    id = db.Column(db.Integer, primary_key=True)
    invoice_number = db.Column(db.String(50))
    work_center_name = db.Column(db.String(100))
    work_center_id = db.Column(db.Integer, db.ForeignKey('work_centers.id'))
    concrete_class = db.Column(db.String(20))
    volume_m3 = db.Column(db.Float, default=0)
    start_time = db.Column(db.String(10))
    end_time = db.Column(db.String(10))
    report_date = db.Column(db.Date, nullable=False)
    imported_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    work_center = db.relationship('WorkCenter', backref='bsu_batches')
    user = db.relationship('User', backref='bsu_batches')


class BsuDailyComparison(db.Model):
    __tablename__ = 'bsu_daily_comparisons'
    id = db.Column(db.Integer, primary_key=True)
    report_date = db.Column(db.Date, nullable=False)
    work_center_id = db.Column(db.Integer, db.ForeignKey('work_centers.id'))
    concrete_class = db.Column(db.String(20), nullable=False)
    bsu_volume = db.Column(db.Float, default=0)
    production_volume = db.Column(db.Float, default=0)
    difference = db.Column(db.Float, default=0)
    first_batch_time = db.Column(db.String(10))
    last_batch_time = db.Column(db.String(10))
    batches_count = db.Column(db.Integer, default=0)
    batches_json = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    work_center = db.relationship('WorkCenter', backref='bsu_comparisons')

    def to_dict(self):
        return {
            'id': self.id,
            'report_date': self.report_date.strftime('%d.%m.%Y'),
            'work_center': self.work_center.name if self.work_center else '—',
            'concrete_class': self.concrete_class,
            'bsu_volume': round(self.bsu_volume, 2),
            'production_volume': round(self.production_volume, 2),
            'difference': round(self.difference, 2),
            'first_batch_time': self.first_batch_time or '—',
            'last_batch_time': self.last_batch_time or '—',
            'batches_count': self.batches_count
        }


# ===== МОДЕЛИ СКЛАДА СИЗ =====
class Employee(db.Model):
    __tablename__ = 'employees'
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String(200), nullable=False)
    personnel_number = db.Column(db.String(50))
    position = db.Column(db.String(100))
    department = db.Column(db.String(100))
    hire_date = db.Column(db.String(20))
    badge_number = db.Column(db.String(50), unique=True)
    active = db.Column(db.Boolean, default=True)


class Item(db.Model):
    __tablename__ = 'items'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    type = db.Column(db.String(20))
    unit = db.Column(db.String(20), default='шт.')
    wear_period = db.Column(db.Integer)
    description = db.Column(db.Text)


class ItemNorm(db.Model):
    __tablename__ = 'item_norms'
    id = db.Column(db.Integer, primary_key=True)
    position = db.Column(db.String(100), nullable=False)
    item_id = db.Column(db.Integer, db.ForeignKey('items.id'), nullable=False)
    quantity = db.Column(db.Float, default=1)
    period_months = db.Column(db.Integer)


class IssuedItem(db.Model):
    __tablename__ = 'issued_items'
    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'), nullable=False)
    item_id = db.Column(db.Integer, db.ForeignKey('items.id'), nullable=False)
    quantity = db.Column(db.Float, default=1)
    date_issued = db.Column(db.String(20), nullable=False)
    date_expire = db.Column(db.String(20))
    date_returned = db.Column(db.String(20))
    status = db.Column(db.String(20), default='issued')
    notes = db.Column(db.Text)


# ===== МОДЕЛЬ ПАСПОРТА КАЧЕСТВА =====
class QualityPassport(db.Model):
    __tablename__ = 'quality_passports'
    id = db.Column(db.Integer, primary_key=True)
    invoice_item_id = db.Column(db.Integer, db.ForeignKey('invoice_items.id'), nullable=True)
    shipment_id = db.Column(db.Integer, db.ForeignKey('shipments.id'), nullable=True)
    doc_number = db.Column(db.String(50))
    doc_date = db.Column(db.String(20))
    product_name = db.Column(db.String(200))
    drawing_series = db.Column(db.String(100))
    concrete_class = db.Column(db.String(20))
    release_strength = db.Column(db.String(50))
    actual_strength = db.Column(db.String(50))
    surface_category = db.Column(db.String(20))
    weight = db.Column(db.String(20))
    frost = db.Column(db.String(20))
    waterproof = db.Column(db.String(20))
    additional_info = db.Column(db.Text)
    logo_type = db.Column(db.String(10), default='psk')
    items_list = db.Column(db.Text)
    required_strength = db.Column(db.String(20))
    responsible = db.Column(db.String(100))
    account = db.Column(db.String(100))
    quantity = db.Column(db.Integer, default=0)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    invoice_item = db.relationship('InvoiceItem', backref='quality_passports')
    shipment = db.relationship('Shipment', backref='passports')
    creator = db.relationship('User', backref='quality_passports')

    @property
    def weight_value(self):
        try:
            return float(self.weight) if self.weight else 0
        except ValueError:
            return 0

    @property
    def volume_value(self):
        return self.weight_value / 2.5 if self.weight_value else 0


# ===== МОДЕЛЬ ДОКУМЕНТАЛЬНОГО ЖУРНАЛА =====
class DocumentLog(db.Model):
    __tablename__ = 'document_log'
    id = db.Column(db.Integer, primary_key=True)
    doc_type = db.Column(db.String(50), nullable=False)
    doc_id = db.Column(db.Integer, nullable=True)
    title = db.Column(db.String(200), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    batch_number = db.Column(db.String(100))
    product_name = db.Column(db.String(200))
    customer_order = db.Column(db.String(200))
    employee_name = db.Column(db.String(200))
    result = db.Column(db.String(30))
    
    user = db.relationship('User', backref='documents', lazy=True)


# ===== МОДЕЛЬ ИСТОРИИ ИМПОРТОВ =====
class ImportHistory(db.Model):
    """История импортов Excel-файлов"""
    __tablename__ = 'import_history'
    
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(255), nullable=False)
    file_size = db.Column(db.Integer, nullable=True)
    created_invoices = db.Column(db.Integer, default=0)
    created_items = db.Column(db.Integer, default=0)
    updated_invoices = db.Column(db.Integer, default=0)
    updated_items = db.Column(db.Integer, default=0)
    errors_count = db.Column(db.Integer, default=0)
    errors_text = db.Column(db.Text, nullable=True)
    status = db.Column(db.String(20), default='success')
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    user = db.relationship('User', backref='imports', lazy=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'filename': self.filename,
            'file_size': self.file_size,
            'created_invoices': self.created_invoices,
            'created_items': self.created_items,
            'updated_invoices': self.updated_invoices,
            'updated_items': self.updated_items,
            'errors_count': self.errors_count,
            'errors_text': self.errors_text[:200] if self.errors_text else '',
            'status': self.status,
            'user_name': self.user.full_name if self.user else '—',
            'created_at': self.created_at.strftime('%d.%m.%Y %H:%M') if self.created_at else ''
        }


# ===== МОДЕЛЬ ИНВЕНТАРИЗАЦИИ АРМАТУРЫ =====
class ArmatureInventory(db.Model):
    """Инвентаризация арматуры"""
    __tablename__ = 'armature_inventory'
    
    id = db.Column(db.Integer, primary_key=True)
    inventory_date = db.Column(db.Date, nullable=False)
    diameter = db.Column(db.String(20), nullable=False)
    steel_class = db.Column(db.String(20), default='')
    batch_number = db.Column(db.String(100), default='')
    manufacturer = db.Column(db.String(200), default='')
    
    incoming_quantity = db.Column(db.Float, default=0)
    consumed_quantity = db.Column(db.Float, default=0)
    remaining_quantity = db.Column(db.Float, default=0)
    
    incoming_weight = db.Column(db.Float, default=0)
    consumed_weight = db.Column(db.Float, default=0)
    remaining_weight = db.Column(db.Float, default=0)
    
    notes = db.Column(db.Text, default='')
    
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    creator = db.relationship('User', backref='armature_inventories', lazy=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'inventory_date': self.inventory_date.strftime('%Y-%m-%d') if self.inventory_date else '',
            'diameter': self.diameter,
            'steel_class': self.steel_class,
            'batch_number': self.batch_number,
            'manufacturer': self.manufacturer,
            'incoming_quantity': self.incoming_quantity,
            'incoming_weight': round(self.incoming_weight, 2),
            'consumed_quantity': self.consumed_quantity,
            'consumed_weight': round(self.consumed_weight, 2),
            'remaining_quantity': self.remaining_quantity,
            'remaining_weight': round(self.remaining_weight, 2),
            'notes': self.notes,
            'created_by': self.created_by,
            'creator_name': self.creator.full_name if self.creator else '—',
            'created_at': self.created_at.strftime('%d.%m.%Y %H:%M') if self.created_at else '',
            'updated_at': self.updated_at.strftime('%d.%m.%Y %H:%M') if self.updated_at else ''
        }


# ===== МОДЕЛИ ДЛЯ ЭЛЕКТРОННОЙ ПОЧТЫ =====
class EmailAccount(db.Model):
    __tablename__ = 'email_accounts'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    from_email = db.Column(db.String(200), nullable=False)
    
    smtp_server = db.Column(db.String(200), nullable=False)
    smtp_port = db.Column(db.Integer, default=587)
    smtp_username = db.Column(db.String(200))
    smtp_password = db.Column(db.String(200))
    use_tls = db.Column(db.Boolean, default=True)
    
    imap_server = db.Column(db.String(200))
    imap_port = db.Column(db.Integer, default=993)
    imap_username = db.Column(db.String(200))
    imap_password = db.Column(db.String(200))
    
    is_active = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    messages = db.relationship('EmailMessage', backref='account', lazy=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'from_email': self.from_email,
            'smtp_server': self.smtp_server,
            'smtp_port': self.smtp_port,
            'smtp_username': self.smtp_username,
            'use_tls': self.use_tls,
            'imap_server': self.imap_server,
            'imap_port': self.imap_port,
            'imap_username': self.imap_username,
            'is_active': self.is_active,
            'created_at': self.created_at.strftime('%d.%m.%Y %H:%M') if self.created_at else ''
        }


class EmailMessage(db.Model):
    __tablename__ = 'email_messages'
    
    id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(db.Integer, db.ForeignKey('email_accounts.id'))
    
    from_email = db.Column(db.String(200))
    to_emails = db.Column(db.Text)
    subject = db.Column(db.String(500))
    body = db.Column(db.Text)
    html_body = db.Column(db.Text)
    
    direction = db.Column(db.String(20), default='incoming')
    status = db.Column(db.String(20), default='received')
    
    processed = db.Column(db.Boolean, default=False)
    processed_at = db.Column(db.DateTime)
    
    received_at = db.Column(db.DateTime)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    is_deleted = db.Column(db.Boolean, default=False)
    deleted_at = db.Column(db.DateTime)
    
    attachments = db.relationship('EmailAttachment', backref='message', lazy=True, cascade='all, delete-orphan')
    
    def to_dict(self):
        return {
            'id': self.id,
            'from_email': self.from_email,
            'to_emails': self.to_emails,
            'subject': self.subject,
            'direction': self.direction,
            'status': self.status,
            'processed': self.processed,
            'processed_at': self.processed_at.strftime('%d.%m.%Y %H:%M') if self.processed_at else '',
            'received_at': self.received_at.strftime('%d.%m.%Y %H:%M') if self.received_at else '',
            'created_at': self.created_at.strftime('%d.%m.%Y %H:%M') if self.created_at else '',
            'attachments_count': len(self.attachments) if self.attachments else 0,
            'is_deleted': self.is_deleted
        }


class EmailAttachment(db.Model):
    __tablename__ = 'email_attachments'
    
    id = db.Column(db.Integer, primary_key=True)
    message_id = db.Column(db.Integer, db.ForeignKey('email_messages.id'))
    
    filename = db.Column(db.String(255), nullable=False)
    content = db.Column(db.LargeBinary)
    content_type = db.Column(db.String(100))
    size = db.Column(db.Integer)
    
    processed = db.Column(db.Boolean, default=False)
    processed_at = db.Column(db.DateTime)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def to_dict(self):
        return {
            'id': self.id,
            'filename': self.filename,
            'content_type': self.content_type,
            'size': self.size,
            'processed': self.processed,
            'created_at': self.created_at.strftime('%d.%m.%Y %H:%M') if self.created_at else ''
        }


class EmailConfig(db.Model):
    __tablename__ = 'email_configs'
    
    id = db.Column(db.Integer, primary_key=True)
    config_type = db.Column(db.String(50), nullable=False)
    recipients = db.Column(db.Text)
    is_active = db.Column(db.Boolean, default=False)
    last_sent = db.Column(db.DateTime)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        return {
            'id': self.id,
            'config_type': self.config_type,
            'recipients': self.recipients,
            'is_active': self.is_active,
            'last_sent': self.last_sent.strftime('%d.%m.%Y %H:%M') if self.last_sent else '',
            'created_at': self.created_at.strftime('%d.%m.%Y %H:%M') if self.created_at else ''
        }


# ===== НАСТРОЙКИ СИСТЕМЫ =====
class SystemSettings(db.Model):
    """Глобальные настройки системы"""
    __tablename__ = 'system_settings'
    
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(100), unique=True, nullable=False)
    value = db.Column(db.Text, nullable=True)
    description = db.Column(db.String(200))
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    @staticmethod
    def get(key, default=None):
        """Получить значение настройки"""
        setting = SystemSettings.query.filter_by(key=key).first()
        if setting:
            return setting.value
        return default
    
    @staticmethod
    def set(key, value):
        """Установить значение настройки"""
        setting = SystemSettings.query.filter_by(key=key).first()
        if setting:
            setting.value = str(value)
        else:
            setting = SystemSettings(key=key, value=str(value))
            db.session.add(setting)
        db.session.commit()
        return setting