"""库存管理数据模型"""
from sqlalchemy import Column, BigInteger, String, Integer, Text, TIMESTAMP, ForeignKey, Boolean, Numeric, Date
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.core.database import Base


class MaterialCategory(Base):
    __tablename__ = "material_categories"

    id = Column(BigInteger, primary_key=True, index=True)
    name = Column(String(100), nullable=False)
    code = Column(String(50), unique=True, nullable=False)
    description = Column(Text)
    created_at = Column(TIMESTAMP, server_default=func.now())

    materials = relationship("Material", back_populates="category")


class Material(Base):
    __tablename__ = "materials"

    id = Column(BigInteger, primary_key=True, index=True)
    category_id = Column(BigInteger, ForeignKey("material_categories.id"))
    name = Column(String(200), nullable=False)
    model = Column(String(100))
    specification = Column(Text)
    brand = Column(String(100))
    unit = Column(String(20), default="个")
    safe_quantity = Column(Integer, default=0)
    notes = Column(Text)
    created_at = Column(TIMESTAMP, server_default=func.now())
    updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.now())

    category = relationship("MaterialCategory", back_populates="materials")
    batches = relationship("InventoryBatch", back_populates="material")
    serial_devices = relationship("SerialDevice", back_populates="material")


class InventoryBatch(Base):
    __tablename__ = "inventory_batches"

    id = Column(BigInteger, primary_key=True, index=True)
    material_id = Column(BigInteger, ForeignKey("materials.id"))
    batch_no = Column(String(50), nullable=False)
    quantity = Column(Integer, nullable=False)
    available_quantity = Column(Integer, nullable=False, default=0)
    supplier = Column(String(200))
    purchase_date = Column(Date)
    purchase_price = Column(Numeric(10, 2))
    expiry_date = Column(Date)
    location = Column(String(100))
    status = Column(String(20), default="in_stock")  # in_stock, reserved, out_of_stock
    created_at = Column(TIMESTAMP, server_default=func.now())

    material = relationship("Material", back_populates="batches")
    serial_devices = relationship("SerialDevice", back_populates="batch")


class SerialDevice(Base):
    __tablename__ = "serial_devices"

    id = Column(BigInteger, primary_key=True, index=True)
    material_id = Column(BigInteger, ForeignKey("materials.id"))
    batch_id = Column(BigInteger, ForeignKey("inventory_batches.id"))
    serial_no = Column(String(100), unique=True, nullable=False)
    mac_address = Column(String(17), index=True)
    asset_no = Column(String(50))
    status = Column(String(20), default="in_stock", index=True)
    # in_stock, allocated, installed, in_use, returned, repairing, scrapped
    current_location = Column(String(200))
    installed_info = Column(JSONB)
    onu_device_id = Column(BigInteger, ForeignKey("onu_devices.id"), nullable=True)
    notes = Column(Text)
    created_at = Column(TIMESTAMP, server_default=func.now())
    updated_at = Column(TIMESTAMP, server_default=func.now(), onupdate=func.now())

    material = relationship("Material", back_populates="serial_devices")
    batch = relationship("InventoryBatch", back_populates="serial_devices")


class InventoryTransaction(Base):
    __tablename__ = "inventory_transactions"

    id = Column(BigInteger, primary_key=True, index=True)
    transaction_no = Column(String(50), unique=True, nullable=False)
    transaction_type = Column(String(20), nullable=False)
    # purchase_in, allocate_out, return_in, scrap_out, adjust
    material_id = Column(BigInteger, ForeignKey("materials.id"))
    batch_id = Column(BigInteger, ForeignKey("inventory_batches.id"), nullable=True)
    serial_device_id = Column(BigInteger, ForeignKey("serial_devices.id"), nullable=True)
    quantity = Column(Integer, nullable=False)
    from_status = Column(String(20))
    to_status = Column(String(20))
    operator_id = Column(BigInteger, ForeignKey("users.id"))
    project_name = Column(String(200))
    installation_info = Column(JSONB)
    notes = Column(Text)
    created_at = Column(TIMESTAMP, server_default=func.now())


class InventoryCheck(Base):
    __tablename__ = "inventory_checks"

    id = Column(BigInteger, primary_key=True, index=True)
    check_no = Column(String(50), unique=True, nullable=False)
    check_date = Column(Date, nullable=False)
    checker_id = Column(BigInteger, ForeignKey("users.id"))
    material_id = Column(BigInteger, ForeignKey("materials.id"))
    batch_id = Column(BigInteger, ForeignKey("inventory_batches.id"), nullable=True)
    book_quantity = Column(Integer)
    actual_quantity = Column(Integer)
    difference = Column(Integer)
    reason = Column(Text)
    adjusted = Column(Boolean, default=False)
    created_at = Column(TIMESTAMP, server_default=func.now())