from sqlalchemy import Column, Integer, String, Float, Text, ForeignKey, DateTime, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
try:
    from src.utils.db import Base
except ImportError:
    from utils.db import Base


class Company(Base):
    """Company model to store company information"""
    __tablename__ = "companies"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, unique=True, nullable=False, index=True)  # External company ID
    company_name = Column(String(255), nullable=False)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    def __repr__(self):
        return f"<Company(id={self.id}, company_id={self.company_id}, name='{self.company_name}')>"


# inventory table
class Product(Base):
    """Product model to store product information"""
    __tablename__ = "products"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    product_id = Column(Integer, unique=True, nullable=False, index=True)  # External product ID
    product_name = Column(String(500), nullable=False)
    short_name = Column(String(255), nullable=True)
    description = Column(Text, nullable=True)
    brand_name = Column(String(255), nullable=True)
    fk_product_category_id = Column(Integer, ForeignKey("categories.category_id"), nullable=True)
    
    # Product flags
    eligible_for_return = Column(Boolean, default=False)
    display_on_pos = Column(Boolean, default=False)
    display_on_online_store = Column(Boolean, default=False)
    is_perishable = Column(Boolean, default=False)
    
    # Image information (from first image)
    image_path = Column(String(1000), nullable=True)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    category = relationship("Category", back_populates="products")
    product_vendors = relationship("ProductVendor", back_populates="product", cascade="all, delete-orphan")
    product_locations = relationship("ProductLocation", back_populates="product", cascade="all, delete-orphan")
    product_prices = relationship("ProductPrice", back_populates="product", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Product(id={self.id}, product_id={self.product_id}, name='{self.product_name}')>"


class Category(Base):
    """Category model to store product category information"""
    __tablename__ = "categories"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    category_id = Column(Integer, unique=True, nullable=False, index=True)  # External category ID (fkProductCategoryId from API)
    category_name = Column(String(255), nullable=False)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    products = relationship("Product", back_populates="category")

    def __repr__(self):
        return f"<Category(id={self.id}, category_id={self.category_id}, name='{self.category_name}')>"


class ProductPrice(Base):
    """Product prices table for location-wise pricing"""
    __tablename__ = "product_prices"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    product_price_id = Column(Integer, unique=True, nullable=False, index=True)  # External ID from API
    product_id = Column(Integer, ForeignKey("products.product_id"), nullable=False, index=True)
    location_id = Column(Integer, ForeignKey("locations.location_id"), nullable=False, index=True)
    
    # Pricing fields from API
    cost_price_per_unit = Column(Float, nullable=True)
    markup_value = Column(Float, nullable=True)
    margin_value = Column(Float, nullable=True)
    retail_price_excl_tax = Column(Float, nullable=True)
    compare_at_price = Column(Float, nullable=True, default=0)
    markup_type_name = Column(String(50), nullable=True)
    margin_type_name = Column(String(50), nullable=True)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    product = relationship("Product", back_populates="product_prices")
    location = relationship("Location")

    def __repr__(self):
        return f"<ProductPrice(id={self.id}, product_id={self.product_id}, location_id={self.location_id})>"


class Location(Base):
    """Location model to store location information"""
    __tablename__ = "locations"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    location_id = Column(Integer, unique=True, nullable=False, index=True)  # External location ID
    location_name = Column(String(255), nullable=False)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    product_locations = relationship("ProductLocation", back_populates="location", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Location(id={self.id}, location_id={self.location_id}, name='{self.location_name}')>"


class Vendor(Base):
    """Vendor model to store vendor information"""
    __tablename__ = "vendors"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    vendor_id = Column(Integer, unique=True, nullable=False, index=True)  # External vendor ID
    vendor_name = Column(String(255), nullable=False)
    vendor_code = Column(String(100), nullable=True)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    product_vendors = relationship("ProductVendor", back_populates="vendor", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Vendor(id={self.id}, vendor_id={self.vendor_id}, name='{self.vendor_name}')>"


class ProductVendor(Base):
    """Many-to-many relationship between products and vendors"""
    __tablename__ = "product_vendors"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    product_id = Column(Integer, ForeignKey("products.product_id"), nullable=False)
    vendor_id = Column(Integer, ForeignKey("vendors.vendor_id"), nullable=False)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    product = relationship("Product", back_populates="product_vendors")
    vendor = relationship("Vendor", back_populates="product_vendors")

    def __repr__(self):
        return f"<ProductVendor(product_id={self.product_id}, vendor_id={self.vendor_id})>"


class ProductLocation(Base):
    """Many-to-many relationship between products and locations"""
    __tablename__ = "product_locations"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.company_id"), nullable=False, index=True)
    product_id = Column(Integer, ForeignKey("products.product_id"), nullable=False)
    location_id = Column(Integer, ForeignKey("locations.location_id"), nullable=False)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    # Relationships
    company = relationship("Company", foreign_keys=[company_id])
    product = relationship("Product", back_populates="product_locations")
    location = relationship("Location", back_populates="product_locations")

    def __repr__(self):
        return f"<ProductLocation(product_id={self.product_id}, location_id={self.location_id})>"