src/app/models/inventory.py

"""
INVENTORY Models - 3-Tier Inventory Management

FLOW:
1. ProjectInventory: Client delivers equipment to main office
2. ProjectInventoryDistribution: Equipment distributed to regional hubs
3. InventoryAssignment: Individual units issued to field agents
4. InventoryTransfer: Agent-to-agent transfers (peer-to-peer)

Examples:
- TOOLS: Issued to worker → Used → Returned to hub
- EQUIPMENT: Issued to worker → Installed at customer site → Not returned
- CONSUMABLES: Issued to worker → Used up → No return
- PPE: Issued to worker → Long-term assignment
- TRANSFERS: Agent A → Agent B (direct peer-to-peer)
"""
from sqlalchemy import Column, String, Boolean, Integer, Text, Date, DateTime, Numeric, ForeignKey, CheckConstraint, UniqueConstraint, Index
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from datetime import datetime, timezone
from decimal import Decimal
from app.models.base import BaseModel
from app.models.enums import InventoryItemType, EquipmentStatus, TransferStatus


class ProjectInventory(BaseModel):
    """
    Main Office Inventory - Equipment received from clients
    
    First tier: Tracks equipment batches received at main office.
    Client delivers equipment (ONTs, routers, cables, tools) for project.
    Main office distributes to regional hubs via ProjectInventoryDistribution.
    
    Features:
    - Item classification (tool, equipment, consumable, ppe)
    - Quantity tracking (received, distributed, at_office)
    - Serial number management (JSONB array)
    - Valuation (unit_cost, total_cost)
    - Optimistic locking (version field)
    """
    __tablename__ = "project_inventory"

    # Relationships
    project_id = Column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False)
    
    # Item Classification
    item_type = Column(String, nullable=False)  # InventoryItemType enum
    equipment_type = Column(Text, nullable=False)  # 'ONT', 'Router', 'Cable', 'Drill', etc.
    equipment_name = Column(Text, nullable=False)  # Specific model/name
    description = Column(Text)
    
    # Quantity Tracking (main office level)
    quantity_received = Column(Numeric(10, 2), nullable=False)
    quantity_distributed = Column(Numeric(10, 2), default=0)
    # quantity_at_office computed in database: quantity_received - quantity_distributed
    
    # Unit of Measurement
    unit = Column(Text, default="pieces")  # 'pieces', 'meters', 'boxes', 'sets'
    
    # Serial Numbers (if applicable)
    has_serial_numbers = Column(Boolean, default=False)
    serial_numbers = Column(JSONB)  # Array of serials with status
    
    # Receiving Details
    received_date = Column(Date, nullable=False)
    received_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    delivery_note_reference = Column(Text)
    
    # Valuation
    unit_cost = Column(Numeric(12, 2))
    total_cost = Column(Numeric(12, 2))
    currency = Column(Text, default="KES")
    
    # Status
    status = Column(String, default="received")  # EquipmentStatus enum
    is_active = Column(Boolean, default=True)
    
    # Metadata
    notes = Column(Text)
    
    # Optimistic Locking
    version = Column(Integer, default=1, nullable=False)
    
    # Relationships
    project = relationship("Project", back_populates="inventory")
    received_by = relationship("User", foreign_keys=[received_by_user_id])
    distributions = relationship("ProjectInventoryDistribution", back_populates="inventory", lazy="dynamic")
    
    # Table constraints
    __table_args__ = (
        CheckConstraint("quantity_received >= 0 AND quantity_distributed >= 0", name="chk_positive_quantities"),
        CheckConstraint("quantity_distributed <= quantity_received", name="chk_valid_distributed"),
        CheckConstraint("(unit_cost IS NULL OR unit_cost >= 0) AND (total_cost IS NULL OR total_cost >= 0)", name="chk_positive_costs"),
        Index("idx_project_inventory_project", "project_id", "item_type", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_project_inventory_status", "status", "is_active", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_project_inventory_version", "id", "version", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_project_inventory_type", "equipment_type", "is_active"),
    )
    
    @property
    def quantity_at_office(self) -> Decimal:
        """Calculate quantity remaining at main office"""
        return Decimal(str(self.quantity_received)) - Decimal(str(self.quantity_distributed))
    
    @property
    def is_fully_distributed(self) -> bool:
        """Check if all inventory distributed to regional hubs"""
        return self.quantity_at_office <= 0
    
    @property
    def distribution_percentage(self) -> float:
        """Calculate percentage of inventory distributed"""
        if not self.quantity_received or self.quantity_received == 0:
            return 0.0
        return float((self.quantity_distributed / self.quantity_received) * 100)
    
    def __repr__(self):
        return f"<ProjectInventory(id={self.id}, type={self.equipment_type}, received={self.quantity_received}, at_office={self.quantity_at_office})>"


class ProjectInventoryDistribution(BaseModel):
    """
    Regional Hub Inventory - Distribution from main office
    
    Second tier: Tracks inventory allocated to regional hubs.
    Main office distributes equipment batches to regional hubs.
    Field agents collect equipment from their assigned regional hub.
    
    Features:
    - Quantity tracking (allocated, issued, installed, returned, lost, damaged, available)
    - Serial number subset tracking (from parent ProjectInventory)
    - Regional hub assignment
    - Optimistic locking
    """
    __tablename__ = "project_inventory_distribution"
    
    # Relationships
    project_inventory_id = Column(UUID(as_uuid=True), ForeignKey("project_inventory.id", ondelete="CASCADE"), nullable=False)
    project_region_id = Column(UUID(as_uuid=True), ForeignKey("project_regions.id", ondelete="CASCADE"), nullable=False)
    
    # Quantity Tracking (regional hub level)
    quantity_allocated = Column(Numeric(10, 2), nullable=False)  # Sent to this hub
    quantity_issued = Column(Numeric(10, 2), default=0)  # Issued to field agents
    quantity_installed = Column(Numeric(10, 2), default=0)  # Installed (equipment only)
    quantity_returned = Column(Numeric(10, 2), default=0)  # Returned (tools only)
    quantity_lost = Column(Numeric(10, 2), default=0)
    quantity_damaged = Column(Numeric(10, 2), default=0)
    # quantity_available computed in database: quantity_allocated - quantity_issued + quantity_returned
    
    # Distribution Details
    allocated_date = Column(Date, nullable=False)
    allocated_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    received_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))  # Regional manager
    
    # Serial Numbers (subset from parent)
    serial_numbers = Column(JSONB)  # Array of serials at this hub
    
    # Status
    is_active = Column(Boolean, default=True)
    
    # Metadata
    notes = Column(Text)
    
    # Optimistic Locking
    version = Column(Integer, default=1, nullable=False)
    
    # Relationships
    inventory = relationship("ProjectInventory", back_populates="distributions")
    region = relationship("ProjectRegion", back_populates="inventory_distributions")
    allocated_by = relationship("User", foreign_keys=[allocated_by_user_id])
    received_by = relationship("User", foreign_keys=[received_by_user_id])
    assignments = relationship("InventoryAssignment", back_populates="distribution", lazy="dynamic")
    
    # Table constraints
    __table_args__ = (
        CheckConstraint(
            "quantity_allocated >= 0 AND quantity_issued >= 0 AND quantity_installed >= 0 AND quantity_returned >= 0 AND quantity_lost >= 0 AND quantity_damaged >= 0",
            name="chk_positive_distribution_quantities"
        ),
        CheckConstraint("quantity_issued <= quantity_allocated + quantity_returned", name="chk_valid_issued_distribution"),
        Index("idx_inventory_distribution_inventory", "project_inventory_id", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_distribution_region", "project_region_id", "is_active", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_distribution_version", "id", "version", postgresql_where=(Column("deleted_at").is_(None))),
    )
    
    @property
    def quantity_available(self) -> Decimal:
        """Calculate quantity available at this regional hub"""
        return Decimal(str(self.quantity_allocated)) - Decimal(str(self.quantity_issued)) + Decimal(str(self.quantity_returned))
    
    @property
    def is_fully_issued(self) -> bool:
        """Check if all inventory issued to field agents"""
        return self.quantity_available <= 0
    
    @property
    def utilization_percentage(self) -> float:
        """Calculate percentage of inventory issued"""
        if not self.quantity_allocated or self.quantity_allocated == 0:
            return 0.0
        return float((self.quantity_issued / self.quantity_allocated) * 100)
    
    def __repr__(self):
        return f"<ProjectInventoryDistribution(id={self.id}, region={self.project_region_id}, allocated={self.quantity_allocated}, available={self.quantity_available})>"


class InventoryAssignment(BaseModel):
    """
    Field Agent Assignments - Individual units issued to workers
    
    Third tier: Tracks individual inventory units issued to field agents.
    Each assignment = ONE unit/box with unique identifier.
    
    FLOW:
    - TOOLS: Worker collects → Uses → Returns to hub
    - EQUIPMENT: Worker collects → Installs at customer site → Not returned
    - CONSUMABLES: Worker collects → Uses up → No return
    - PPE: Worker collects → Long-term assignment
    
    Features:
    - Unit/box identity tracking (serial numbers or unique IDs)
    - Timeline tracking (issued, returned, installed, consumed)
    - Return condition tracking (for tools)
    - Ticket linkage (for equipment/consumables)
    - Optimistic locking
    - Unique constraint: One unit identifier per distribution
    """
    __tablename__ = "inventory_assignments"
    
    # Relationships
    project_inventory_distribution_id = Column(UUID(as_uuid=True), ForeignKey("project_inventory_distribution.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False)  # Field agent
    ticket_id = Column(UUID(as_uuid=True), ForeignKey("tickets.id", ondelete="SET NULL"))
    
    # Unit/Box Identity (ONE unit per assignment)
    unit_identifier = Column(Text, nullable=False)  # Serial number, box number, or unique ID
    unit_contents = Column(JSONB)  # OPTIONAL: Array of items inside box/unit
    
    # Timeline
    issued_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    issued_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    
    # Return/Usage (mutually exclusive based on item type)
    returned_at = Column(DateTime(timezone=True))  # For tools only
    return_condition = Column(Text)  # 'good', 'damaged', 'lost', 'worn'
    returned_to_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    return_notes = Column(Text)
    is_returned = Column(Boolean, default=False)
    
    installed_at = Column(DateTime(timezone=True))  # For equipment only
    consumed_at = Column(DateTime(timezone=True))  # For consumables only
    
    # Status
    status = Column(String, default="issued")  # EquipmentStatus enum
    
    # Notes
    notes = Column(Text)
    additional_metadata = Column(JSONB, default={})
    
    # Transfer tracking
    transferred_from_assignment_id = Column(UUID(as_uuid=True), ForeignKey("inventory_assignments.id", ondelete="SET NULL"))
    is_transferred = Column(Boolean, default=False)
    transferred_at = Column(DateTime(timezone=True))
    
    # Optimistic Locking
    version = Column(Integer, default=1, nullable=False)
    
    # Relationships
    distribution = relationship("ProjectInventoryDistribution", back_populates="assignments")
    user = relationship("User", foreign_keys=[user_id])
    issued_by = relationship("User", foreign_keys=[issued_by_user_id])
    returned_to = relationship("User", foreign_keys=[returned_to_user_id])
    ticket = relationship("Ticket")
    transferred_from = relationship("InventoryAssignment", foreign_keys=[transferred_from_assignment_id], remote_side="InventoryAssignment.id")
    
    # Table constraints
    __table_args__ = (
        Index("idx_inventory_assignments_distribution", "project_inventory_distribution_id", "status", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_user", "user_id", "status", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_user_issued", "user_id", "issued_at", postgresql_where=(Column("status") == "issued") & (Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_ticket", "ticket_id", postgresql_where=(Column("ticket_id").isnot(None)) & (Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_unit", "unit_identifier", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_status", "status", "issued_at", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_version", "id", "version", postgresql_where=(Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_unreturned", "user_id", "is_returned", "issued_at", postgresql_where=(Column("is_returned") == False) & (Column("deleted_at").is_(None))),
        Index("idx_inventory_assignments_transferred", "is_transferred", "transferred_at", postgresql_where=(Column("is_transferred") == True) & (Column("deleted_at").is_(None))),
        UniqueConstraint("project_inventory_distribution_id", "unit_identifier", name="idx_inventory_assignments_unique_unit"),
    )
    
    @property
    def is_active(self) -> bool:
        """Check if assignment is still active (not returned/installed/consumed)"""
        return not (self.returned_at or self.installed_at or self.consumed_at)
    
    @property
    def days_issued(self) -> int:
        """Calculate days since issuance"""
        if not self.issued_at:
            return 0
        end_time = self.returned_at or self.installed_at or self.consumed_at or datetime.now(timezone.utc)
        return (end_time - self.issued_at).days
    
    def can_return(self) -> bool:
        """Check if this assignment can be returned"""
        return not (self.returned_at or self.installed_at or self.consumed_at)
    
    def __repr__(self):
        return f"<InventoryAssignment(id={self.id}, unit={self.unit_identifier}, user={self.user_id}, status={self.status})>"