import uuid
from datetime import datetime, timezone
from sqlalchemy import Column, String, DateTime, Numeric, ForeignKey, UniqueConstraint
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from app.core.database import Base

def now_utc():
    return datetime.now(timezone.utc)

class RmPurchaseRequest(Base):
    """Header for generated Indents."""
    __tablename__ = 'rm_purchase_request'
    request_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    request_number = Column(String(50), unique=True)
    status = Column(String(30), default='PENDING') # PENDING, APPROVED, PO_CREATED, REJECTED
    requested_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True) 
    vendor_id = Column(UUID(as_uuid=True), ForeignKey('vendor_master.vendor_id'), nullable=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)

    details = relationship("RmPurchaseRequestDetail", back_populates="request", cascade="all, delete-orphan")

class RmPurchaseRequestDetail(Base):
    """Line items for the Indent."""
    __tablename__ = 'rm_purchase_request_detail'
    request_detail_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    request_id = Column(UUID(as_uuid=True), ForeignKey('rm_purchase_request.request_id'))
    rm_id = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'))
    shortage_qty = Column(Numeric(14,3))
    affected_chassis = Column(JSONB, default=list) 
    reason = Column(String(200))

    request = relationship("RmPurchaseRequest", back_populates="details")
    rm = relationship("RmMaster", lazy="joined")

    @property
    def rm_name(self):
        return self.rm.name if self.rm else None


class ChassisBomSnapshot(Base):
    """
    Stores the final exploded and validated BOM for a specific chassis after it passes Procurement Check.
    Replaces the old QuoteBomSnapshot logic since BOMs are now generated dynamically per-chassis
    from the DealerOrderVehicle features + BomMaster.
    """
    __tablename__ = 'chassis_bom_snapshot'
    __table_args__ = (
        UniqueConstraint('chassis_id', 'rm_id', name='uq_chassis_bom_snapshot'),  # DESIGN-01 FIX
    )
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id', ondelete='CASCADE'), nullable=False)
    rm_id = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    quantity = Column(Numeric(14,3), nullable=False)
    reserved_qty = Column(Numeric(14,3), nullable=False, default=0)
    consumed_qty = Column(Numeric(14,3), nullable=False, default=0)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=True)
    category = Column(String(50), nullable=True)  # 'Base BOM', 'Feature: <name>', 'Ad-hoc', etc.
    created_at = Column(DateTime(timezone=True), default=now_utc)