880 lines
39 KiB
Python
880 lines
39 KiB
Python
# ================================================================
|
|
# services/production/app/models/production.py
|
|
# ================================================================
|
|
"""
|
|
Production models for the production service
|
|
"""
|
|
|
|
from sqlalchemy import Column, String, Integer, Float, DateTime, Boolean, Text, JSON, Enum as SQLEnum
|
|
from sqlalchemy.dialects.postgresql import UUID
|
|
from sqlalchemy.orm import deferred
|
|
from sqlalchemy.sql import func
|
|
from datetime import datetime, timezone
|
|
from typing import Dict, Any, Optional
|
|
import uuid
|
|
import enum
|
|
|
|
from shared.database.base import Base
|
|
|
|
|
|
class ProductionStatus(str, enum.Enum):
|
|
"""Production batch status enumeration"""
|
|
PENDING = "PENDING"
|
|
IN_PROGRESS = "IN_PROGRESS"
|
|
COMPLETED = "COMPLETED"
|
|
CANCELLED = "CANCELLED"
|
|
ON_HOLD = "ON_HOLD"
|
|
QUALITY_CHECK = "QUALITY_CHECK"
|
|
FAILED = "FAILED"
|
|
|
|
|
|
class ProductionPriority(str, enum.Enum):
|
|
"""Production priority levels"""
|
|
LOW = "LOW"
|
|
MEDIUM = "MEDIUM"
|
|
HIGH = "HIGH"
|
|
URGENT = "URGENT"
|
|
|
|
|
|
class EquipmentStatus(str, enum.Enum):
|
|
"""Equipment status enumeration"""
|
|
OPERATIONAL = "OPERATIONAL"
|
|
MAINTENANCE = "MAINTENANCE"
|
|
DOWN = "DOWN"
|
|
WARNING = "WARNING"
|
|
|
|
|
|
class ProcessStage(str, enum.Enum):
|
|
"""Production process stages where quality checks can occur"""
|
|
MIXING = "mixing"
|
|
PROOFING = "proofing"
|
|
SHAPING = "shaping"
|
|
BAKING = "baking"
|
|
COOLING = "cooling"
|
|
PACKAGING = "packaging"
|
|
FINISHING = "finishing"
|
|
|
|
|
|
class EquipmentType(str, enum.Enum):
|
|
"""Equipment type enumeration"""
|
|
OVEN = "oven"
|
|
MIXER = "mixer"
|
|
PROOFER = "proofer"
|
|
FREEZER = "freezer"
|
|
PACKAGING = "packaging"
|
|
OTHER = "other"
|
|
|
|
|
|
class ProductionBatch(Base):
|
|
"""Production batch model for tracking individual production runs"""
|
|
__tablename__ = "production_batches"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
batch_number = Column(String(50), nullable=False, unique=True, index=True)
|
|
|
|
# Product and recipe information
|
|
product_id = Column(UUID(as_uuid=True), nullable=False, index=True) # Reference to inventory/recipes
|
|
product_name = Column(String(255), nullable=False)
|
|
recipe_id = Column(UUID(as_uuid=True), nullable=True)
|
|
|
|
# Production planning
|
|
planned_start_time = Column(DateTime(timezone=True), nullable=False)
|
|
planned_end_time = Column(DateTime(timezone=True), nullable=False)
|
|
planned_quantity = Column(Float, nullable=False)
|
|
planned_duration_minutes = Column(Integer, nullable=False)
|
|
|
|
# Actual production tracking
|
|
actual_start_time = Column(DateTime(timezone=True), nullable=True)
|
|
actual_end_time = Column(DateTime(timezone=True), nullable=True)
|
|
actual_quantity = Column(Float, nullable=True)
|
|
actual_duration_minutes = Column(Integer, nullable=True)
|
|
|
|
# Status and priority
|
|
status = Column(SQLEnum(ProductionStatus), nullable=False, default=ProductionStatus.PENDING, index=True)
|
|
priority = Column(SQLEnum(ProductionPriority), nullable=False, default=ProductionPriority.MEDIUM)
|
|
|
|
# Process stage tracking
|
|
current_process_stage = Column(SQLEnum(ProcessStage), nullable=True, index=True)
|
|
process_stage_history = Column(JSON, nullable=True) # Track stage transitions with timestamps
|
|
pending_quality_checks = Column(JSON, nullable=True) # Required quality checks for current stage
|
|
completed_quality_checks = Column(JSON, nullable=True) # Completed quality checks by stage
|
|
|
|
# Cost tracking
|
|
estimated_cost = Column(Float, nullable=True)
|
|
actual_cost = Column(Float, nullable=True)
|
|
labor_cost = Column(Float, nullable=True)
|
|
material_cost = Column(Float, nullable=True)
|
|
overhead_cost = Column(Float, nullable=True)
|
|
|
|
# Quality metrics
|
|
yield_percentage = Column(Float, nullable=True) # actual/planned quantity
|
|
quality_score = Column(Float, nullable=True)
|
|
waste_quantity = Column(Float, nullable=True)
|
|
defect_quantity = Column(Float, nullable=True)
|
|
waste_defect_type = Column(String(100), nullable=True) # Type of defect causing waste (burnt, misshapen, underproofed, temperature_issues, expired)
|
|
|
|
# Equipment and resources
|
|
equipment_used = Column(JSON, nullable=True) # List of equipment IDs
|
|
staff_assigned = Column(JSON, nullable=True) # List of staff IDs
|
|
station_id = Column(String(50), nullable=True)
|
|
|
|
# Business context
|
|
order_id = Column(UUID(as_uuid=True), nullable=True) # Associated customer order
|
|
forecast_id = Column(UUID(as_uuid=True), nullable=True) # Associated demand forecast
|
|
is_rush_order = Column(Boolean, default=False)
|
|
is_special_recipe = Column(Boolean, default=False)
|
|
is_ai_assisted = Column(Boolean, default=False) # Whether batch used AI forecasting/optimization
|
|
|
|
# Notes and tracking
|
|
production_notes = Column(Text, nullable=True)
|
|
quality_notes = Column(Text, nullable=True)
|
|
delay_reason = Column(String(255), nullable=True)
|
|
cancellation_reason = Column(String(255), nullable=True)
|
|
|
|
# JTBD Dashboard: Structured reasoning data for i18n support
|
|
# Backend stores structured data, frontend translates using i18n
|
|
reasoning_data = Column(JSON, nullable=True) # Structured reasoning data for multilingual support
|
|
# reasoning_data structure (see shared/schemas/reasoning_types.py):
|
|
# {
|
|
# "type": "forecast_demand" | "customer_order" | "stock_replenishment" | etc.,
|
|
# "parameters": {
|
|
# "product_name": "Croissant",
|
|
# "predicted_demand": 500,
|
|
# "current_stock": 120,
|
|
# "production_needed": 380,
|
|
# "confidence_score": 87
|
|
# },
|
|
# "urgency": {
|
|
# "level": "normal",
|
|
# "ready_by_time": "08:00",
|
|
# "customer_commitment": false
|
|
# },
|
|
# "metadata": {
|
|
# "trigger_source": "orchestrator_auto",
|
|
# "forecast_id": "uuid-here",
|
|
# "ai_assisted": true
|
|
# }
|
|
# }
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
completed_at = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"batch_number": self.batch_number,
|
|
"product_id": str(self.product_id),
|
|
"product_name": self.product_name,
|
|
"recipe_id": str(self.recipe_id) if self.recipe_id else None,
|
|
"planned_start_time": self.planned_start_time.isoformat() if self.planned_start_time else None,
|
|
"planned_end_time": self.planned_end_time.isoformat() if self.planned_end_time else None,
|
|
"planned_quantity": self.planned_quantity,
|
|
"planned_duration_minutes": self.planned_duration_minutes,
|
|
"actual_start_time": self.actual_start_time.isoformat() if self.actual_start_time else None,
|
|
"actual_end_time": self.actual_end_time.isoformat() if self.actual_end_time else None,
|
|
"actual_quantity": self.actual_quantity,
|
|
"actual_duration_minutes": self.actual_duration_minutes,
|
|
"status": self.status.value if self.status else None,
|
|
"priority": self.priority.value if self.priority else None,
|
|
"estimated_cost": self.estimated_cost,
|
|
"actual_cost": self.actual_cost,
|
|
"labor_cost": self.labor_cost,
|
|
"material_cost": self.material_cost,
|
|
"overhead_cost": self.overhead_cost,
|
|
"yield_percentage": self.yield_percentage,
|
|
"quality_score": self.quality_score,
|
|
"waste_quantity": self.waste_quantity,
|
|
"defect_quantity": self.defect_quantity,
|
|
"waste_defect_type": self.waste_defect_type,
|
|
"equipment_used": self.equipment_used,
|
|
"staff_assigned": self.staff_assigned,
|
|
"station_id": self.station_id,
|
|
"order_id": str(self.order_id) if self.order_id else None,
|
|
"forecast_id": str(self.forecast_id) if self.forecast_id else None,
|
|
"is_rush_order": self.is_rush_order,
|
|
"is_special_recipe": self.is_special_recipe,
|
|
"is_ai_assisted": self.is_ai_assisted,
|
|
"production_notes": self.production_notes,
|
|
"quality_notes": self.quality_notes,
|
|
"delay_reason": self.delay_reason,
|
|
"cancellation_reason": self.cancellation_reason,
|
|
"reasoning_data": self.reasoning_data,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
"completed_at": self.completed_at.isoformat() if self.completed_at else None,
|
|
}
|
|
|
|
|
|
class ProductionSchedule(Base):
|
|
"""Production schedule model for planning and tracking daily production"""
|
|
__tablename__ = "production_schedules"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
|
|
# Schedule information
|
|
schedule_date = Column(DateTime(timezone=True), nullable=False, index=True)
|
|
shift_start = Column(DateTime(timezone=True), nullable=False)
|
|
shift_end = Column(DateTime(timezone=True), nullable=False)
|
|
|
|
# Capacity planning
|
|
total_capacity_hours = Column(Float, nullable=False)
|
|
planned_capacity_hours = Column(Float, nullable=False)
|
|
actual_capacity_hours = Column(Float, nullable=True)
|
|
overtime_hours = Column(Float, nullable=True, default=0.0)
|
|
|
|
# Staff and equipment
|
|
staff_count = Column(Integer, nullable=False)
|
|
equipment_capacity = Column(JSON, nullable=True) # Equipment availability
|
|
station_assignments = Column(JSON, nullable=True) # Station schedules
|
|
|
|
# Production metrics
|
|
total_batches_planned = Column(Integer, nullable=False, default=0)
|
|
total_batches_completed = Column(Integer, nullable=True, default=0)
|
|
total_quantity_planned = Column(Float, nullable=False, default=0.0)
|
|
total_quantity_produced = Column(Float, nullable=True, default=0.0)
|
|
|
|
# Status tracking
|
|
is_finalized = Column(Boolean, default=False)
|
|
is_active = Column(Boolean, default=True)
|
|
|
|
# Performance metrics
|
|
efficiency_percentage = Column(Float, nullable=True)
|
|
utilization_percentage = Column(Float, nullable=True)
|
|
on_time_completion_rate = Column(Float, nullable=True)
|
|
|
|
# Notes and adjustments
|
|
schedule_notes = Column(Text, nullable=True)
|
|
schedule_adjustments = Column(JSON, nullable=True) # Track changes made
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
finalized_at = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"schedule_date": self.schedule_date.isoformat() if self.schedule_date else None,
|
|
"shift_start": self.shift_start.isoformat() if self.shift_start else None,
|
|
"shift_end": self.shift_end.isoformat() if self.shift_end else None,
|
|
"total_capacity_hours": self.total_capacity_hours,
|
|
"planned_capacity_hours": self.planned_capacity_hours,
|
|
"actual_capacity_hours": self.actual_capacity_hours,
|
|
"overtime_hours": self.overtime_hours,
|
|
"staff_count": self.staff_count,
|
|
"equipment_capacity": self.equipment_capacity,
|
|
"station_assignments": self.station_assignments,
|
|
"total_batches_planned": self.total_batches_planned,
|
|
"total_batches_completed": self.total_batches_completed,
|
|
"total_quantity_planned": self.total_quantity_planned,
|
|
"total_quantity_produced": self.total_quantity_produced,
|
|
"is_finalized": self.is_finalized,
|
|
"is_active": self.is_active,
|
|
"efficiency_percentage": self.efficiency_percentage,
|
|
"utilization_percentage": self.utilization_percentage,
|
|
"on_time_completion_rate": self.on_time_completion_rate,
|
|
"schedule_notes": self.schedule_notes,
|
|
"schedule_adjustments": self.schedule_adjustments,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
"finalized_at": self.finalized_at.isoformat() if self.finalized_at else None,
|
|
}
|
|
|
|
|
|
class ProductionCapacity(Base):
|
|
"""Production capacity model for tracking equipment and resource availability"""
|
|
__tablename__ = "production_capacity"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
|
|
# Capacity definition
|
|
resource_type = Column(String(50), nullable=False) # equipment, staff, station
|
|
resource_id = Column(String(100), nullable=False)
|
|
resource_name = Column(String(255), nullable=False)
|
|
|
|
# Time period
|
|
date = Column(DateTime(timezone=True), nullable=False, index=True)
|
|
start_time = Column(DateTime(timezone=True), nullable=False)
|
|
end_time = Column(DateTime(timezone=True), nullable=False)
|
|
|
|
# Capacity metrics
|
|
total_capacity_units = Column(Float, nullable=False) # Total available capacity
|
|
allocated_capacity_units = Column(Float, nullable=False, default=0.0)
|
|
remaining_capacity_units = Column(Float, nullable=False)
|
|
|
|
# Status
|
|
is_available = Column(Boolean, default=True)
|
|
is_maintenance = Column(Boolean, default=False)
|
|
is_reserved = Column(Boolean, default=False)
|
|
|
|
# Equipment specific
|
|
equipment_type = Column(String(100), nullable=True)
|
|
max_batch_size = Column(Float, nullable=True)
|
|
min_batch_size = Column(Float, nullable=True)
|
|
setup_time_minutes = Column(Integer, nullable=True)
|
|
cleanup_time_minutes = Column(Integer, nullable=True)
|
|
|
|
# Performance tracking
|
|
efficiency_rating = Column(Float, nullable=True)
|
|
maintenance_status = Column(String(50), nullable=True)
|
|
last_maintenance_date = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
# Notes
|
|
notes = Column(Text, nullable=True)
|
|
restrictions = Column(JSON, nullable=True) # Product type restrictions, etc.
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"resource_type": self.resource_type,
|
|
"resource_id": self.resource_id,
|
|
"resource_name": self.resource_name,
|
|
"date": self.date.isoformat() if self.date else None,
|
|
"start_time": self.start_time.isoformat() if self.start_time else None,
|
|
"end_time": self.end_time.isoformat() if self.end_time else None,
|
|
"total_capacity_units": self.total_capacity_units,
|
|
"allocated_capacity_units": self.allocated_capacity_units,
|
|
"remaining_capacity_units": self.remaining_capacity_units,
|
|
"is_available": self.is_available,
|
|
"is_maintenance": self.is_maintenance,
|
|
"is_reserved": self.is_reserved,
|
|
"equipment_type": self.equipment_type,
|
|
"max_batch_size": self.max_batch_size,
|
|
"min_batch_size": self.min_batch_size,
|
|
"setup_time_minutes": self.setup_time_minutes,
|
|
"cleanup_time_minutes": self.cleanup_time_minutes,
|
|
"efficiency_rating": self.efficiency_rating,
|
|
"maintenance_status": self.maintenance_status,
|
|
"last_maintenance_date": self.last_maintenance_date.isoformat() if self.last_maintenance_date else None,
|
|
"notes": self.notes,
|
|
"restrictions": self.restrictions,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
}
|
|
|
|
|
|
class QualityCheckTemplate(Base):
|
|
"""Quality check templates for tenant-specific quality standards"""
|
|
__tablename__ = "quality_check_templates"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
|
|
# Template identification
|
|
name = Column(String(255), nullable=False)
|
|
template_code = Column(String(100), nullable=True, index=True)
|
|
check_type = Column(String(50), nullable=False) # visual, measurement, temperature, weight, boolean
|
|
category = Column(String(100), nullable=True) # appearance, structure, texture, etc.
|
|
|
|
# Template configuration
|
|
description = Column(Text, nullable=True)
|
|
instructions = Column(Text, nullable=True)
|
|
parameters = Column(JSON, nullable=True) # Dynamic check parameters
|
|
thresholds = Column(JSON, nullable=True) # Pass/fail criteria
|
|
scoring_criteria = Column(JSON, nullable=True) # Scoring methodology
|
|
|
|
# Configurability settings
|
|
is_active = Column(Boolean, default=True)
|
|
is_required = Column(Boolean, default=False)
|
|
is_critical = Column(Boolean, default=False) # Critical failures block production
|
|
weight = Column(Float, default=1.0) # Weight in overall quality score
|
|
|
|
# Measurement specifications
|
|
min_value = Column(Float, nullable=True)
|
|
max_value = Column(Float, nullable=True)
|
|
target_value = Column(Float, nullable=True)
|
|
unit = Column(String(20), nullable=True)
|
|
tolerance_percentage = Column(Float, nullable=True)
|
|
|
|
# Process stage applicability
|
|
applicable_stages = Column(JSON, nullable=True) # List of ProcessStage values
|
|
|
|
# Metadata
|
|
created_by = Column(UUID(as_uuid=True), nullable=False)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"name": self.name,
|
|
"template_code": self.template_code,
|
|
"check_type": self.check_type,
|
|
"category": self.category,
|
|
"description": self.description,
|
|
"instructions": self.instructions,
|
|
"parameters": self.parameters,
|
|
"thresholds": self.thresholds,
|
|
"scoring_criteria": self.scoring_criteria,
|
|
"is_active": self.is_active,
|
|
"is_required": self.is_required,
|
|
"is_critical": self.is_critical,
|
|
"weight": self.weight,
|
|
"min_value": self.min_value,
|
|
"max_value": self.max_value,
|
|
"target_value": self.target_value,
|
|
"unit": self.unit,
|
|
"tolerance_percentage": self.tolerance_percentage,
|
|
"applicable_stages": self.applicable_stages,
|
|
"created_by": str(self.created_by),
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
}
|
|
|
|
|
|
class QualityCheck(Base):
|
|
"""Quality check model for tracking production quality metrics with stage support"""
|
|
__tablename__ = "quality_checks"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
batch_id = Column(UUID(as_uuid=True), nullable=False, index=True) # FK to ProductionBatch
|
|
template_id = Column(UUID(as_uuid=True), nullable=True, index=True) # FK to QualityCheckTemplate
|
|
|
|
# Check information
|
|
check_type = Column(String(50), nullable=False) # visual, weight, temperature, etc.
|
|
process_stage = Column(SQLEnum(ProcessStage), nullable=True, index=True) # Stage when check was performed
|
|
check_time = Column(DateTime(timezone=True), nullable=False)
|
|
checker_id = Column(String(100), nullable=True) # Staff member who performed check
|
|
|
|
# Quality metrics
|
|
quality_score = Column(Float, nullable=False) # 1-10 scale
|
|
pass_fail = Column(Boolean, nullable=False)
|
|
defect_count = Column(Integer, nullable=False, default=0)
|
|
defect_types = Column(JSON, nullable=True) # List of defect categories
|
|
|
|
# Measurements
|
|
measured_weight = Column(Float, nullable=True)
|
|
measured_temperature = Column(Float, nullable=True)
|
|
measured_moisture = Column(Float, nullable=True)
|
|
measured_dimensions = Column(JSON, nullable=True)
|
|
stage_specific_data = Column(JSON, nullable=True) # Stage-specific measurements
|
|
|
|
# Standards comparison
|
|
target_weight = Column(Float, nullable=True)
|
|
target_temperature = Column(Float, nullable=True)
|
|
target_moisture = Column(Float, nullable=True)
|
|
tolerance_percentage = Column(Float, nullable=True)
|
|
|
|
# Results
|
|
within_tolerance = Column(Boolean, nullable=True)
|
|
corrective_action_needed = Column(Boolean, default=False)
|
|
corrective_actions = Column(JSON, nullable=True)
|
|
|
|
# Template-based results
|
|
template_results = Column(JSON, nullable=True) # Results from template-based checks
|
|
criteria_scores = Column(JSON, nullable=True) # Individual criteria scores
|
|
|
|
# Notes and documentation
|
|
check_notes = Column(Text, nullable=True)
|
|
photos_urls = Column(JSON, nullable=True) # URLs to quality check photos
|
|
certificate_url = Column(String(500), nullable=True)
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"batch_id": str(self.batch_id),
|
|
"check_type": self.check_type,
|
|
"check_time": self.check_time.isoformat() if self.check_time else None,
|
|
"checker_id": self.checker_id,
|
|
"quality_score": self.quality_score,
|
|
"pass_fail": self.pass_fail,
|
|
"defect_count": self.defect_count,
|
|
"defect_types": self.defect_types,
|
|
"measured_weight": self.measured_weight,
|
|
"measured_temperature": self.measured_temperature,
|
|
"measured_moisture": self.measured_moisture,
|
|
"measured_dimensions": self.measured_dimensions,
|
|
"target_weight": self.target_weight,
|
|
"target_temperature": self.target_temperature,
|
|
"target_moisture": self.target_moisture,
|
|
"tolerance_percentage": self.tolerance_percentage,
|
|
"within_tolerance": self.within_tolerance,
|
|
"corrective_action_needed": self.corrective_action_needed,
|
|
"corrective_actions": self.corrective_actions,
|
|
"check_notes": self.check_notes,
|
|
"photos_urls": self.photos_urls,
|
|
"certificate_url": self.certificate_url,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
}
|
|
|
|
|
|
class IoTProtocol(str, enum.Enum):
|
|
"""IoT protocol enumeration"""
|
|
REST_API = "rest_api"
|
|
OPC_UA = "opc_ua"
|
|
MQTT = "mqtt"
|
|
MODBUS = "modbus"
|
|
CUSTOM = "custom"
|
|
|
|
|
|
class IoTConnectionStatus(str, enum.Enum):
|
|
"""IoT connection status enumeration"""
|
|
CONNECTED = "connected"
|
|
DISCONNECTED = "disconnected"
|
|
ERROR = "error"
|
|
UNKNOWN = "unknown"
|
|
|
|
|
|
class Equipment(Base):
|
|
"""Equipment model for tracking production equipment"""
|
|
__tablename__ = "equipment"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
|
|
# Equipment identification
|
|
name = Column(String(255), nullable=False)
|
|
type = Column(SQLEnum(EquipmentType), nullable=False)
|
|
model = Column(String(100), nullable=True)
|
|
serial_number = Column(String(100), nullable=True)
|
|
location = Column(String(255), nullable=True)
|
|
manufacturer = Column(String(100), nullable=True)
|
|
firmware_version = Column(String(50), nullable=True)
|
|
|
|
# Status tracking
|
|
status = Column(SQLEnum(EquipmentStatus), nullable=False, default=EquipmentStatus.OPERATIONAL)
|
|
|
|
# Dates
|
|
install_date = Column(DateTime(timezone=True), nullable=True)
|
|
last_maintenance_date = Column(DateTime(timezone=True), nullable=True)
|
|
next_maintenance_date = Column(DateTime(timezone=True), nullable=True)
|
|
maintenance_interval_days = Column(Integer, nullable=True) # Maintenance interval in days
|
|
|
|
# Performance metrics
|
|
efficiency_percentage = Column(Float, nullable=True) # Current efficiency
|
|
uptime_percentage = Column(Float, nullable=True) # Overall equipment effectiveness
|
|
energy_usage_kwh = Column(Float, nullable=True) # Current energy usage
|
|
|
|
# Specifications
|
|
power_kw = Column(Float, nullable=True) # Power in kilowatts
|
|
capacity = Column(Float, nullable=True) # Capacity (units depend on equipment type)
|
|
weight_kg = Column(Float, nullable=True) # Weight in kilograms
|
|
|
|
# Temperature monitoring
|
|
current_temperature = Column(Float, nullable=True) # Current temperature reading
|
|
target_temperature = Column(Float, nullable=True) # Target temperature
|
|
|
|
# IoT Connectivity
|
|
iot_enabled = Column(Boolean, default=False, nullable=False)
|
|
iot_protocol = Column(String(50), nullable=True) # rest_api, opc_ua, mqtt, modbus, custom
|
|
iot_endpoint = Column(String(500), nullable=True) # URL or IP address
|
|
iot_port = Column(Integer, nullable=True) # Connection port
|
|
iot_credentials = Column(JSON, nullable=True) # Encrypted credentials (API keys, tokens, username/password)
|
|
iot_connection_status = Column(String(50), nullable=True) # connected, disconnected, error, unknown
|
|
iot_last_connected = Column(DateTime(timezone=True), nullable=True)
|
|
iot_config = Column(JSON, nullable=True) # Additional configuration (polling interval, specific endpoints, etc.)
|
|
|
|
# Real-time monitoring
|
|
supports_realtime = Column(Boolean, default=False, nullable=False)
|
|
poll_interval_seconds = Column(Integer, nullable=True) # How often to poll for data
|
|
|
|
# Sensor capabilities
|
|
temperature_zones = Column(Integer, nullable=True) # Number of temperature zones
|
|
supports_humidity = Column(Boolean, default=False, nullable=False)
|
|
supports_energy_monitoring = Column(Boolean, default=False, nullable=False)
|
|
supports_remote_control = Column(Boolean, default=False, nullable=False)
|
|
|
|
# Status
|
|
is_active = Column(Boolean, default=True)
|
|
|
|
# Notes
|
|
notes = Column(Text, nullable=True)
|
|
|
|
# Support contact information
|
|
support_contact = Column(JSON, nullable=True)
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"name": self.name,
|
|
"type": self.type.value if self.type else None,
|
|
"model": self.model,
|
|
"serial_number": self.serial_number,
|
|
"location": self.location,
|
|
"manufacturer": self.manufacturer,
|
|
"firmware_version": self.firmware_version,
|
|
"status": self.status.value if self.status else None,
|
|
"install_date": self.install_date.isoformat() if self.install_date else None,
|
|
"last_maintenance_date": self.last_maintenance_date.isoformat() if self.last_maintenance_date else None,
|
|
"next_maintenance_date": self.next_maintenance_date.isoformat() if self.next_maintenance_date else None,
|
|
"maintenance_interval_days": self.maintenance_interval_days,
|
|
"efficiency_percentage": self.efficiency_percentage,
|
|
"uptime_percentage": self.uptime_percentage,
|
|
"energy_usage_kwh": self.energy_usage_kwh,
|
|
"power_kw": self.power_kw,
|
|
"capacity": self.capacity,
|
|
"weight_kg": self.weight_kg,
|
|
"current_temperature": self.current_temperature,
|
|
"target_temperature": self.target_temperature,
|
|
"iot_enabled": self.iot_enabled,
|
|
"iot_protocol": self.iot_protocol,
|
|
"iot_endpoint": self.iot_endpoint,
|
|
"iot_port": self.iot_port,
|
|
"iot_connection_status": self.iot_connection_status,
|
|
"iot_last_connected": self.iot_last_connected.isoformat() if self.iot_last_connected else None,
|
|
"iot_config": self.iot_config,
|
|
"supports_realtime": self.supports_realtime,
|
|
"poll_interval_seconds": self.poll_interval_seconds,
|
|
"temperature_zones": self.temperature_zones,
|
|
"supports_humidity": self.supports_humidity,
|
|
"supports_energy_monitoring": self.supports_energy_monitoring,
|
|
"supports_remote_control": self.supports_remote_control,
|
|
"is_active": self.is_active,
|
|
"notes": self.notes,
|
|
"support_contact": self.support_contact,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
}
|
|
|
|
|
|
class EquipmentSensorReading(Base):
|
|
"""Equipment sensor reading model for time-series IoT data"""
|
|
__tablename__ = "equipment_sensor_readings"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
equipment_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
batch_id = Column(UUID(as_uuid=True), nullable=True, index=True)
|
|
|
|
# Timestamp
|
|
reading_time = Column(DateTime(timezone=True), nullable=False, index=True)
|
|
|
|
# Temperature readings (support multiple zones)
|
|
temperature = Column(Float, nullable=True)
|
|
temperature_zones = Column(JSON, nullable=True) # {"zone1": 180, "zone2": 200, "zone3": 185}
|
|
target_temperature = Column(Float, nullable=True)
|
|
|
|
# Humidity
|
|
humidity = Column(Float, nullable=True)
|
|
target_humidity = Column(Float, nullable=True)
|
|
|
|
# Energy monitoring
|
|
energy_consumption_kwh = Column(Float, nullable=True)
|
|
power_current_kw = Column(Float, nullable=True)
|
|
|
|
# Equipment status
|
|
operational_status = Column(String(50), nullable=True) # running, idle, warming_up, cooling_down
|
|
cycle_stage = Column(String(100), nullable=True) # preheating, baking, cooling
|
|
cycle_progress_percentage = Column(Float, nullable=True)
|
|
time_remaining_minutes = Column(Integer, nullable=True)
|
|
|
|
# Process parameters
|
|
motor_speed_rpm = Column(Float, nullable=True)
|
|
door_status = Column(String(20), nullable=True) # open, closed
|
|
steam_level = Column(Float, nullable=True)
|
|
|
|
# Quality indicators
|
|
product_weight_kg = Column(Float, nullable=True)
|
|
moisture_content = Column(Float, nullable=True)
|
|
|
|
# Additional sensor data (flexible JSON for manufacturer-specific metrics)
|
|
additional_sensors = Column(JSON, nullable=True)
|
|
|
|
# Data quality
|
|
data_quality_score = Column(Float, nullable=True)
|
|
is_anomaly = Column(Boolean, default=False, nullable=False)
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"equipment_id": str(self.equipment_id),
|
|
"batch_id": str(self.batch_id) if self.batch_id else None,
|
|
"reading_time": self.reading_time.isoformat() if self.reading_time else None,
|
|
"temperature": self.temperature,
|
|
"temperature_zones": self.temperature_zones,
|
|
"target_temperature": self.target_temperature,
|
|
"humidity": self.humidity,
|
|
"target_humidity": self.target_humidity,
|
|
"energy_consumption_kwh": self.energy_consumption_kwh,
|
|
"power_current_kw": self.power_current_kw,
|
|
"operational_status": self.operational_status,
|
|
"cycle_stage": self.cycle_stage,
|
|
"cycle_progress_percentage": self.cycle_progress_percentage,
|
|
"time_remaining_minutes": self.time_remaining_minutes,
|
|
"motor_speed_rpm": self.motor_speed_rpm,
|
|
"door_status": self.door_status,
|
|
"steam_level": self.steam_level,
|
|
"product_weight_kg": self.product_weight_kg,
|
|
"moisture_content": self.moisture_content,
|
|
"additional_sensors": self.additional_sensors,
|
|
"data_quality_score": self.data_quality_score,
|
|
"is_anomaly": self.is_anomaly,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
}
|
|
|
|
|
|
class EquipmentConnectionLog(Base):
|
|
"""Equipment connection log for tracking IoT connectivity"""
|
|
__tablename__ = "equipment_connection_logs"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
equipment_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
|
|
# Connection event
|
|
event_type = Column(String(50), nullable=False) # connected, disconnected, error, timeout
|
|
event_time = Column(DateTime(timezone=True), nullable=False, index=True)
|
|
|
|
# Connection details
|
|
connection_status = Column(String(50), nullable=False)
|
|
protocol_used = Column(String(50), nullable=True)
|
|
endpoint = Column(String(500), nullable=True)
|
|
|
|
# Error tracking
|
|
error_message = Column(Text, nullable=True)
|
|
error_code = Column(String(50), nullable=True)
|
|
|
|
# Performance metrics
|
|
response_time_ms = Column(Integer, nullable=True)
|
|
data_points_received = Column(Integer, nullable=True)
|
|
|
|
# Additional details
|
|
additional_data = Column(JSON, nullable=True)
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"equipment_id": str(self.equipment_id),
|
|
"event_type": self.event_type,
|
|
"event_time": self.event_time.isoformat() if self.event_time else None,
|
|
"connection_status": self.connection_status,
|
|
"protocol_used": self.protocol_used,
|
|
"endpoint": self.endpoint,
|
|
"error_message": self.error_message,
|
|
"error_code": self.error_code,
|
|
"response_time_ms": self.response_time_ms,
|
|
"data_points_received": self.data_points_received,
|
|
"additional_data": self.additional_data,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
}
|
|
|
|
|
|
class EquipmentIoTAlert(Base):
|
|
"""Equipment IoT alert model for real-time equipment alerts"""
|
|
__tablename__ = "equipment_iot_alerts"
|
|
|
|
# Primary identification
|
|
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
|
|
tenant_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
equipment_id = Column(UUID(as_uuid=True), nullable=False, index=True)
|
|
batch_id = Column(UUID(as_uuid=True), nullable=True, index=True)
|
|
|
|
# Alert information
|
|
alert_type = Column(String(50), nullable=False) # temperature_deviation, connection_lost, equipment_error
|
|
severity = Column(String(20), nullable=False) # info, warning, critical
|
|
alert_time = Column(DateTime(timezone=True), nullable=False, index=True)
|
|
|
|
# Alert details
|
|
title = Column(String(255), nullable=False)
|
|
message = Column(Text, nullable=False)
|
|
sensor_reading_id = Column(UUID(as_uuid=True), nullable=True)
|
|
|
|
# Threshold information
|
|
threshold_value = Column(Float, nullable=True)
|
|
actual_value = Column(Float, nullable=True)
|
|
deviation_percentage = Column(Float, nullable=True)
|
|
|
|
# Status tracking
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
is_acknowledged = Column(Boolean, default=False, nullable=False)
|
|
acknowledged_by = Column(UUID(as_uuid=True), nullable=True)
|
|
acknowledged_at = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
is_resolved = Column(Boolean, default=False, nullable=False)
|
|
resolved_by = Column(UUID(as_uuid=True), nullable=True)
|
|
resolved_at = Column(DateTime(timezone=True), nullable=True)
|
|
resolution_notes = Column(Text, nullable=True)
|
|
|
|
# Automated response
|
|
auto_resolved = Column(Boolean, default=False, nullable=False)
|
|
corrective_action_taken = Column(String(255), nullable=True)
|
|
|
|
# Additional data
|
|
additional_data = Column(JSON, nullable=True)
|
|
|
|
# Timestamps
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
|
|
|
def to_dict(self) -> Dict[str, Any]:
|
|
"""Convert to dictionary following shared pattern"""
|
|
return {
|
|
"id": str(self.id),
|
|
"tenant_id": str(self.tenant_id),
|
|
"equipment_id": str(self.equipment_id),
|
|
"batch_id": str(self.batch_id) if self.batch_id else None,
|
|
"alert_type": self.alert_type,
|
|
"severity": self.severity,
|
|
"alert_time": self.alert_time.isoformat() if self.alert_time else None,
|
|
"title": self.title,
|
|
"message": self.message,
|
|
"sensor_reading_id": str(self.sensor_reading_id) if self.sensor_reading_id else None,
|
|
"threshold_value": self.threshold_value,
|
|
"actual_value": self.actual_value,
|
|
"deviation_percentage": self.deviation_percentage,
|
|
"is_active": self.is_active,
|
|
"is_acknowledged": self.is_acknowledged,
|
|
"acknowledged_by": str(self.acknowledged_by) if self.acknowledged_by else None,
|
|
"acknowledged_at": self.acknowledged_at.isoformat() if self.acknowledged_at else None,
|
|
"is_resolved": self.is_resolved,
|
|
"resolved_by": str(self.resolved_by) if self.resolved_by else None,
|
|
"resolved_at": self.resolved_at.isoformat() if self.resolved_at else None,
|
|
"resolution_notes": self.resolution_notes,
|
|
"auto_resolved": self.auto_resolved,
|
|
"corrective_action_taken": self.corrective_action_taken,
|
|
"additional_data": self.additional_data,
|
|
"created_at": self.created_at.isoformat() if self.created_at else None,
|
|
"updated_at": self.updated_at.isoformat() if self.updated_at else None,
|
|
}
|
|
|
|
|