import uuid
from datetime import datetime, date
from sqlalchemy import String, DateTime, Date, Float, Integer, Text, ForeignKey, func
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database import Base


class SeedLot(Base):
    __tablename__ = "seed_lots"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    lot_number: Mapped[str] = mapped_column(String(50), unique=True)
    plant_id: Mapped[str] = mapped_column(ForeignKey("plants.id"))
    supplier_id: Mapped[str | None] = mapped_column(ForeignKey("suppliers.id"), nullable=True)
    quantity: Mapped[int] = mapped_column(Integer, default=0)
    unit: Mapped[str] = mapped_column(String(20), default="grams")
    purchase_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    expiry_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    cost_per_unit: Mapped[float] = mapped_column(Float, default=0.0)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    batches = relationship("ProductionBatch", back_populates="seed_lot")


class ProductionBatch(Base):
    __tablename__ = "production_batches"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    batch_number: Mapped[str] = mapped_column(String(30), unique=True)
    plant_id: Mapped[str] = mapped_column(ForeignKey("plants.id"))
    seed_lot_id: Mapped[str | None] = mapped_column(ForeignKey("seed_lots.id"), nullable=True)
    sowing_date: Mapped[date] = mapped_column(Date)
    expected_ready_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    sown_quantity: Mapped[int] = mapped_column(Integer, default=0)
    current_quantity: Mapped[int] = mapped_column(Integer, default=0)
    status: Mapped[str] = mapped_column(String(30), default="sowing")
    tray_number: Mapped[str | None] = mapped_column(String(50), nullable=True)
    location: Mapped[str | None] = mapped_column(String(100), nullable=True)
    employee_id: Mapped[str | None] = mapped_column(ForeignKey("employees.id"), nullable=True)
    branch_id: Mapped[str | None] = mapped_column(ForeignKey("branches.id"), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), onupdate=func.now())
    plant = relationship("Plant", back_populates="batches")
    seed_lot = relationship("SeedLot", back_populates="batches")
    germination_records = relationship("GerminationRecord", back_populates="batch", cascade="all, delete-orphan")
    transplant_records = relationship("TransplantRecord", back_populates="batch", cascade="all, delete-orphan")


class GerminationRecord(Base):
    __tablename__ = "germination_records"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    batch_id: Mapped[str] = mapped_column(ForeignKey("production_batches.id"))
    date: Mapped[date] = mapped_column(Date)
    germinated_quantity: Mapped[int] = mapped_column(Integer, default=0)
    success_rate: Mapped[float] = mapped_column(Float, default=0.0)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    batch = relationship("ProductionBatch", back_populates="germination_records")


class TransplantRecord(Base):
    __tablename__ = "transplant_records"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    batch_id: Mapped[str] = mapped_column(ForeignKey("production_batches.id"))
    date: Mapped[date] = mapped_column(Date)
    from_container: Mapped[str] = mapped_column(String(50))
    to_container: Mapped[str] = mapped_column(String(50))
    quantity: Mapped[int] = mapped_column(Integer, default=0)
    employee_id: Mapped[str | None] = mapped_column(ForeignKey("employees.id"), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    batch = relationship("ProductionBatch", back_populates="transplant_records")