from sqlalchemy import (
    Integer,
    ForeignKey,
    Text,
    Float,
    DateTime,
    Boolean,
    String,
    Table,
    Column,
)
from sqlalchemy.orm import relationship, Mapped, mapped_column
from typing import List, Optional, TYPE_CHECKING
from sqlalchemy.sql import func
from src.apps.base.models.base import Base
from sqlalchemy.ext.hybrid import hybrid_property
from src.apps.files.schemas.file_common import FileResponseSchema
from datetime import datetime

if TYPE_CHECKING:
    from src.apps.product_categories.models.product_category import ProductCategory

product_attachments_map = Table(
    "products_files",
    Base.metadata,
    Column("product_id", Integer, ForeignKey("products.id")),
    Column("file_id", Integer, ForeignKey("files.id")),
)

product_notes_map = Table(
    "products_notes",
    Base.metadata,
    Column("product_id", Integer, ForeignKey("products.id")),
    Column("note_id", Integer, ForeignKey("notes.id")),
)


class Product(Base):
    """
    Product Model: ORM class for Product Entity
    """

    __tablename__ = "products"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True, autoincrement=True)
    name: Mapped[str] = mapped_column(Text)
    sku: Mapped[str] = mapped_column(Text, unique=True)
    code: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    slug: Mapped[str] = mapped_column(Text, unique=True)
    unit_price: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    sale_price: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    is_new: Mapped[bool] = mapped_column(Boolean, default=False)
    new_until: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    item_type: Mapped[str] = mapped_column(String(50), default="product")
    is_purchase: Mapped[bool] = mapped_column(Boolean, default=False)
    purchase_msrp: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    purchase_manufacture_part_number: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    purchase_supplier_sku: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    purchase_description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    calculated_purchase_tax_rate: Mapped[float] = mapped_column(Float, default=0.00)
    is_sell: Mapped[bool] = mapped_column(Boolean, default=False)
    sell_msrp: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    sell_manufacture_part_number: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    sell_supplier_sku: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    sell_description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    calculated_sell_tax_rate: Mapped[float] = mapped_column(Float, default=0.00)
    length: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    height: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    width: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    weight: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    depth: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True, onupdate=func.now())
    deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    # TODO: Uncomment when TaxRate model is created
    # purchase_tax_rate_id: Mapped[Optional[int]] = mapped_column(Integer, ForeignKey("tax_rates.id"), nullable=True)
    # purchase_tax_rate: Mapped[Optional["TaxRate"]] = relationship(
    #     "TaxRate", foreign_keys=[purchase_tax_rate_id], lazy="joined", uselist=False
    # )

    # sell_tax_rate_id: Mapped[Optional[int]] = mapped_column(Integer, ForeignKey("tax_rates.id"), nullable=True)
    # sell_tax_rate: Mapped[Optional["TaxRate"]] = relationship(
    #     "TaxRate",
    #     foreign_keys=[sell_tax_rate_id],
    #     lazy="joined",
    #     uselist=False,
    #     primaryjoin="and_(Product.sell_tax_rate_id == TaxRate.id, TaxRate.is_active == True)",
    # )

    category_id: Mapped[Optional[int]] = mapped_column(Integer, ForeignKey("products_category.id"), nullable=True)
    category: Mapped[Optional["ProductCategory"]] = relationship(
        "ProductCategory", foreign_keys=[category_id], lazy="joined", uselist=False
    )

    line_items: Mapped[List["PaymentRequestLineItems"]] = relationship("PaymentRequestLineItems", back_populates="product")
    invoice_line_items: Mapped[List["InvoiceLineItems"]] = relationship("InvoiceLineItems", back_populates="product")

    merchant_id: Mapped[Optional[int]] = mapped_column(Integer, ForeignKey("merchants.id"), nullable=True)
    merchant: Mapped[Optional["Merchant"]] = relationship("Merchant", foreign_keys=[merchant_id], uselist=False)

    attachments: Mapped[List["File"]] = relationship("File", secondary=product_attachments_map)

    notes: Mapped[List["Note"]] = relationship("Note", secondary=product_notes_map)

    @hybrid_property
    def thumbnail(self) -> Optional[FileResponseSchema]:
        if self.attachments:
            thumbnail_data = list(
                filter(lambda x: x.file_type == "thumbnail", self.attachments)
            )
            return thumbnail_data[0] if thumbnail_data else None
        return None

    @hybrid_property
    def product_attachments(self) -> Optional[List[FileResponseSchema]]:
        if self.attachments:
            attachment = list(
                filter(lambda x: x.file_type != "thumbnail", self.attachments)
            )
            return attachment
        return None