from sqlalchemy import Column, Integer, String, Float, ForeignKey, DateTime, Text, Boolean, Index
from sqlalchemy.orm import relationship
from datetime import datetime
import uuid

from app.core.database import Base



    # ...existing code...


class ManufacturingCost(Base):
    """Manufacturing Cost model with batch tracking"""
    __tablename__ = "manufacturing_costs"

    id = Column(Integer, primary_key=True, index=True)
    date = Column(DateTime, default=datetime.utcnow)
    product_id = Column(String, ForeignKey("products.id"))
    production_order_id = Column(
        String,
        ForeignKey("production_orders.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )
    batch_number = Column(String(50), index=True)
    batch_size = Column(Float)

    # Batch lot tracking (enterprise feature)
    lot_start_date = Column(DateTime, nullable=True)
    lot_end_date = Column(DateTime, nullable=True)

    # Cost components
    material_cost = Column(Float, default=0.0)
    labor_cost = Column(Float, default=0.0)
    overhead_cost = Column(Float, default=0.0)
    total_cost = Column(Float, default=0.0)
    unit_cost = Column(Float, default=0.0)

    # Quality management
    quality_check_status = Column(String(50), default="pending")  # pending/pass/fail/conditional
    quality_notes = Column(Text, nullable=True)
    quality_checked_by = Column(String, ForeignKey("users.id"), nullable=True)
    quality_check_date = Column(DateTime, nullable=True)

    notes = Column(Text, nullable=True)
    status = Column(String(20), default="draft")  # draft, in_progress, completed

    # Relationships
    product = relationship("Product", back_populates="manufacturing_costs")
    material_entries = relationship("MaterialCostEntry", back_populates="manufacturing_cost", cascade="all, delete-orphan")
    labor_entries = relationship("LaborCostEntry", back_populates="manufacturing_cost", cascade="all, delete-orphan")
    overhead_entries = relationship("OverheadCostEntry", back_populates="manufacturing_cost", cascade="all, delete-orphan")
    variance = relationship("ManufacturingVariance", back_populates="manufacturing_cost", cascade="all, delete-orphan")


class ManufacturingCostStandard(Base):
    """Standard costs for variance analysis"""
    __tablename__ = "manufacturing_cost_standards"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    product_id = Column(String, ForeignKey("products.id", ondelete="CASCADE"), nullable=False, index=True)

    # Standard cost components
    standard_material_cost = Column(Float, default=0.0)
    standard_labor_cost = Column(Float, default=0.0)
    standard_overhead_cost = Column(Float, default=0.0)

    # When this standard is effective
    effective_date = Column(DateTime, nullable=False, default=datetime.utcnow)
    notes = Column(Text, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    product = relationship("Product", back_populates="cost_standards")


class ManufacturingVariance(Base):
    """Variance tracking for cost control (actual vs standard)"""
    __tablename__ = "manufacturing_variance"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    manufacturing_cost_id = Column(Integer, ForeignKey("manufacturing_costs.id", ondelete="CASCADE"), nullable=False)
    product_id = Column(String, ForeignKey("products.id"), nullable=False, index=True)
    batch_number = Column(String(50), nullable=False, index=True)

    # Cost comparison
    standard_cost = Column(Float, default=0.0)
    actual_cost = Column(Float, default=0.0)
    variance_amount = Column(Float, default=0.0)
    variance_percentage = Column(Float, default=0.0)

    # Variance breakdown
    material_variance = Column(Float, default=0.0)
    labor_variance = Column(Float, default=0.0)
    overhead_variance = Column(Float, default=0.0)

    # Analysis
    variance_type = Column(String(20), default="neutral")  # favorable/unfavorable/neutral
    notes = Column(Text, nullable=True)

    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    manufacturing_cost = relationship("ManufacturingCost", back_populates="variance")
    product = relationship("Product", back_populates="variances")


class MaterialCostEntry(Base):
    """Material cost entry for manufacturing"""
    __tablename__ = "material_cost_entries"

    id = Column(Integer, primary_key=True, index=True)
    manufacturing_cost_id = Column(Integer, ForeignKey("manufacturing_costs.id"))
    material_id = Column(String, ForeignKey("products.id"))
    quantity = Column(Float)
    unit_cost = Column(Float)
    total_cost = Column(Float)
    description = Column(Text, nullable=True)

    # Relationships
    manufacturing_cost = relationship("ManufacturingCost", back_populates="material_entries")
    material = relationship("Product")


class LaborCostEntry(Base):
    """Labor cost entry for manufacturing"""
    __tablename__ = "labor_cost_entries"

    id = Column(Integer, primary_key=True, index=True)
    manufacturing_cost_id = Column(Integer, ForeignKey("manufacturing_costs.id"))
    hours = Column(Float)
    rate = Column(Float)
    total_cost = Column(Float)
    description = Column(Text, nullable=True)

    # Relationships
    manufacturing_cost = relationship("ManufacturingCost", back_populates="labor_entries")


class OverheadCostEntry(Base):
    """Overhead cost entry for manufacturing"""
    __tablename__ = "overhead_cost_entries"

    id = Column(Integer, primary_key=True, index=True)
    manufacturing_cost_id = Column(Integer, ForeignKey("manufacturing_costs.id"))
    name = Column(String(100))
    amount = Column(Float)
    description = Column(Text, nullable=True)

    # Relationships
    manufacturing_cost = relationship("ManufacturingCost", back_populates="overhead_entries")