from database import Base
from sqlalchemy import Column, Integer, String, TIMESTAMP, Boolean, text, ForeignKey, UniqueConstraint, Numeric, Float
from sqlalchemy.dialects.postgresql import MONEY as Money, ARRAY
from sqlalchemy.orm import relationship



# Inventory Table
class Inventory(Base):
    __tablename__ = "inventory"

    inventory_id = Column(String, primary_key=True, index=True)
    product_id = Column(String, ForeignKey("products.product_id"), nullable=False)
    warehouse_id = Column(String, ForeignKey("warehouses.warehouse_id"), nullable=False)
    q_on_hand = Column(Integer, nullable=False)
    q_reserved = Column(Integer, nullable=False, default=0)
    last_update = Column(TIMESTAMP(timezone=True))
    condition = Column(String, nullable=True)
    sku = Column(String, nullable=True)
    q_in_transit = Column(Integer, nullable=True, default=0)
    reorder_point = Column(Integer, nullable=True)
    location = Column(String, nullable=True)
    safety_stock = Column(Integer, nullable=True)

    product = relationship("Product", back_populates="inventories")
    warehouse = relationship("Warehouse", back_populates="inventories")

    __table_args__ = (
        UniqueConstraint("product_id", "warehouse_id", name="inventory_product_id_warehouse_id_key"),
    )

# Products Table
class Product(Base):
    __tablename__ = "products"

    product_id = Column(String, primary_key=True, index=True)
    product_name = Column(String, nullable=False)
    sku = Column(String, unique=True, nullable=False)
    category = Column(String, nullable=True)
    base_price = Column(Numeric(10, 2), nullable=False)
    model_number = Column(String, nullable=True)
    description = Column(String, nullable=True)
    weight = Column(Float, nullable=True)
    dimensions = Column(String, nullable=True)
    brand = Column(String, nullable=True)
    status = Column(String, nullable=True)

    inventories = relationship("Inventory", back_populates="product")
    # order_items = relationship("OrderItem", back_populates="product")
    # stock_ledgers = relationship("StockLedger", back_populates="product")
    # stock_reservations = relationship("StockReservation", back_populates="product")
    spare_parts = relationship("SparePart", back_populates="product")


# Warehouses Table
class Warehouse(Base):
    __tablename__ = "warehouses"

    warehouse_id = Column(String, primary_key=True, index=True)
    warehouse_name = Column(String, nullable=False)
    city = Column(String, nullable=True)
    state = Column(String, nullable=True)
    country = Column(String, nullable=True)
    latitude = Column(Float, nullable=True)
    longitude = Column(Float, nullable=True)
    capacity = Column(Integer, nullable=True)
    operating_hours = Column(String, nullable=True)
    manager = Column(String, nullable=True)

    inventories = relationship("Inventory", back_populates="warehouse")
    # stock_ledgers = relationship("StockLedger", back_populates="warehouse")
    # stock_reservations = relationship("StockReservation", back_populates="warehouse")


class OrderItem(Base):
    __tablename__ = "order_items"

    order_item_id = Column(Integer, primary_key=True, index=True)
    order_id = Column(String, ForeignKey("orders.order_id"), nullable=False)
    product_id = Column(String, ForeignKey("products.product_id"), nullable=False)
    sku = Column(String, nullable=False)
    price = Column(Numeric(10, 2), nullable=False)
    qty = Column(Integer, nullable=False)
    order_date = Column(TIMESTAMP, nullable=False)
    order = relationship("Order", back_populates="items")
    product = relationship("Product")
    created_at = Column(TIMESTAMP(timezone=True), server_default=text("now()"))

# Orders Table
class Order(Base):
    __tablename__ = "orders"

    order_id = Column(String, primary_key=True, index=True)
    order_date = Column(TIMESTAMP, nullable=True)
    customer_id = Column(String, nullable=False)
    customer_name = Column(String, nullable=False)
    customer_location = Column(String, nullable=True)
    order_total = Column(Numeric(10, 2), nullable=False)
    items = relationship("OrderItem", back_populates="order", cascade="all, delete-orphan")
    assigned_warehouse_id = Column(String, nullable=True)
    status = Column(String, nullable=False)
    payment_method = Column(String, nullable=True)
    shipping_address = Column(String, nullable=True)
    estimated_delivery = Column(TIMESTAMP, nullable=True)
    
    

# Spare Parts Table
class SparePart(Base):
    __tablename__ = "spare_parts"

    part_id = Column(String, primary_key=True, index=True)
    main_product_id = Column(String, ForeignKey("products.product_id"))
    main_product_model = Column(String, nullable=False)
    part_name = Column(String, nullable=False)
    part_sku = Column(String, unique=True, nullable=False)
    part_price = Column(Numeric(10, 2), nullable=False)
    availability = Column(String, nullable=False)
    estimate_shipment_days = Column(Integer, nullable=False)
    supplier = Column(String, nullable=False)
    part_number = Column(String, nullable=False)
    compatibility_notes = Column(String, nullable=True)

    product = relationship("Product", back_populates="spare_parts")
    
    



# class StockLedger(Base):
#     __tablename__ = "stock_ledger"

#     ledger_id = Column(Integer, primary_key=True, index=True)
#     product_id = Column(Integer, ForeignKey("products.product_id"))
#     warehouse_id = Column(Integer, ForeignKey("warehouses.warehouse_id"))
#     event_type = Column(String, nullable=False)
#     quantity = Column(Integer, nullable=False)
#     timestamp = Column(TIMESTAMP(timezone=True), server_default=text("now()"))

#     product = relationship("Product", back_populates="stock_ledgers")
#     warehouse = relationship("Warehouse")
    
# class StockReservation(Base):
#     __tablename__ = "stock_reservations"

#     reservation_id = Column(Integer, primary_key=True, index=True)
#     product_id = Column(Integer, ForeignKey("products.product_id"))
#     warehouse_id = Column(Integer, ForeignKey("warehouses.warehouse_id"))
#     status = Column(String, nullable=False)
#     reserved_quantity = Column(Integer, nullable=False)
#     created_at = Column(TIMESTAMP(timezone=True), server_default=text("now()"))

#     product = relationship("Product", back_populates="stock_reservations")
#     warehouse = relationship("Warehouse")

# class Post(Base):
#     __tablename__ = "posts"

#     id = Column(Integer, primary_key=True, index=True)
#     title = Column(String, nullable=False)
#     content = Column(String, nullable=False)
#     published = Column(Boolean, server_default="TRUE")
#     created_at = Column(TIMESTAMP(timezone=True), server_default=text("now()"))
