from __future__ import annotations import uuid from datetime import datetime, date, time from sqlalchemy import Column, String, Float, Integer, Text, Boolean, Date, Time, DateTime, ForeignKey, func from sqlalchemy.orm import relationship from app.database import Base class Treatment(Base): __tablename__ = "treatments" id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4())) treatment_number = Column(String(50), unique=True, nullable=False) batch_id = Column(String(36), ForeignKey("production_batches.id"), nullable=False) variety_id = Column(String(36), ForeignKey("plant_varieties.id"), nullable=True) block_bed = Column(String(100), nullable=True) treatment_type = Column(String(50), nullable=False) # irrigation, fertilizer, pesticide, growth_regulator, pruning, weeding, transplanting, inspection, corrective treatment_date = Column(Date, nullable=False, server_default=func.now()) treatment_time = Column(Time, nullable=True) growth_stage = Column(String(50), nullable=True) employee_id = Column(String(36), ForeignKey("employees.id"), nullable=True) status = Column(String(20), default="completed") # completed, scheduled, cancelled notes = Column(Text, nullable=True) cost = Column(Float, default=0.0) created_by = Column(String(36), ForeignKey("users.id"), nullable=True) created_at = Column(DateTime, server_default=func.now()) updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now()) batch = relationship("ProductionBatch", backref="treatments") variety = relationship("PlantVariety") employee = relationship("Employee") class TreatmentDetail(Base): __tablename__ = "treatment_details" id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4())) treatment_id = Column(String(36), ForeignKey("treatments.id"), nullable=False) # Irrigation watering_method = Column(String(50), nullable=True) # drip, sprinkler, manual duration_minutes = Column(Float, nullable=True) quantity_liters = Column(Float, nullable=True) # Fertilizer fertilizer_name = Column(String(100), nullable=True) npk_ratio = Column(String(20), nullable=True) dose = Column(String(50), nullable=True) unit = Column(String(20), nullable=True) # g, kg, ml, L application_method = Column(String(50), nullable=True) next_application_date = Column(Date, nullable=True) # Pesticide/Fungicide product_name = Column(String(100), nullable=True) active_ingredient = Column(String(100), nullable=True) target_pest_disease = Column(String(100), nullable=True) dosage = Column(String(50), nullable=True) spray_method = Column(String(50), nullable=True) weather_conditions = Column(String(100), nullable=True) operator = Column(String(100), nullable=True) # Growth Regulator hormone_name = Column(String(100), nullable=True) purpose = Column(String(100), nullable=True) expected_effect = Column(String(200), nullable=True) # Pruning pruning_reason = Column(String(100), nullable=True) # Weeding weeding_method = Column(String(20), nullable=True) # manual, chemical area_treated = Column(String(50), nullable=True) time_spent_minutes = Column(Float, nullable=True) # Transplanting from_tray = Column(String(50), nullable=True) to_polybag_pot = Column(String(50), nullable=True) quantity_transplanted = Column(Integer, nullable=True) survival_count = Column(Integer, nullable=True) # Inspection health_score = Column(Float, nullable=True) pest_observed = Column(String(200), nullable=True) disease_observed = Column(String(200), nullable=True) growth_status = Column(String(100), nullable=True) photo_urls = Column(Text, nullable=True) # JSON array of URLs # Corrective corrective_type = Column(String(50), nullable=True) # wilt, root_rot, nutrient_deficiency, pest_control, replanting treatment = relationship("Treatment", backref="details") class HealthRecord(Base): __tablename__ = "health_records" id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4())) batch_id = Column(String(36), ForeignKey("production_batches.id"), nullable=False) record_date = Column(Date, nullable=False, server_default=func.now()) plant_height_cm = Column(Float, nullable=True) leaf_count = Column(Integer, nullable=True) stem_thickness_mm = Column(Float, nullable=True) plant_color = Column(String(50), nullable=True) root_status = Column(String(50), nullable=True) moisture_level = Column(Float, nullable=True) health_score = Column(Float, nullable=True) mortality_rate = Column(Float, nullable=True) notes = Column(Text, nullable=True) recorded_by = Column(String(36), ForeignKey("users.id"), nullable=True) created_at = Column(DateTime, server_default=func.now()) batch = relationship("ProductionBatch", backref="health_records") class DiseasePestRecord(Base): __tablename__ = "disease_pest_records" id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4())) batch_id = Column(String(36), ForeignKey("production_batches.id"), nullable=False) record_date = Column(Date, nullable=False, server_default=func.now()) name = Column(String(100), nullable=False) type = Column(String(20), nullable=False) # disease, pest severity = Column(String(20), default="low") # low, medium, high symptoms = Column(Text, nullable=True) treatment_applied = Column(Text, nullable=True) product_used = Column(String(100), nullable=True) follow_up_date = Column(Date, nullable=True) before_photo = Column(String(500), nullable=True) after_photo = Column(String(500), nullable=True) status = Column(String(20), default="active") # active, resolved recorded_by = Column(String(36), ForeignKey("users.id"), nullable=True) created_at = Column(DateTime, server_default=func.now()) batch = relationship("ProductionBatch", backref="disease_pest_records") class TreatmentSchedule(Base): __tablename__ = "treatment_schedules" id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4())) batch_id = Column(String(36), ForeignKey("production_batches.id"), nullable=False) treatment_type = Column(String(50), nullable=False) frequency_days = Column(Integer, nullable=False) # every N days next_due_date = Column(Date, nullable=False) employee_id = Column(String(36), ForeignKey("employees.id"), nullable=True) notes = Column(Text, nullable=True) is_active = Column(Boolean, default=True) created_by = Column(String(36), ForeignKey("users.id"), nullable=True) created_at = Column(DateTime, server_default=func.now()) updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now()) batch = relationship("ProductionBatch", backref="treatment_schedules") employee = relationship("Employee")