from sqlalchemy import BigInteger, ForeignKey, Index, Integer, String, UniqueConstraint
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.models.base import Base, TimestampMixin, TenantMixin


class ProductAttachmentType(Base, TenantMixin, TimestampMixin):
    __tablename__ = "product_attachment_types"
    __table_args__ = (
        UniqueConstraint("tenant_id", "code", name="uk_product_attachment_type_code"),
        Index("ix_product_attachment_type_tenant_active", "tenant_id", "is_active"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(80), nullable=False)
    label: Mapped[str] = mapped_column(String(160), nullable=False)
    description: Mapped[str | None] = mapped_column(String(500), nullable=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)

    attachments = relationship("ProductAttachment", back_populates="type", lazy="noload")


class ProductAttachment(Base, TenantMixin, TimestampMixin):
    __tablename__ = "product_attachments"
    __table_args__ = (
        Index("ix_product_attachment_product", "tenant_id", "product_id", "sort_order"),
        Index("ix_product_attachment_type", "tenant_id", "type_id", "is_active"),
        {
            "mysql_engine": "InnoDB",
            "mysql_charset": "utf8mb4",
            "mysql_collate": "utf8mb4_unicode_ci",
        },
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    product_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("products.id", ondelete="CASCADE"), nullable=False)
    type_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("product_attachment_types.id", ondelete="RESTRICT"), nullable=False)
    title: Mapped[str | None] = mapped_column(String(200), nullable=True)
    file_url: Mapped[str] = mapped_column(String(500), nullable=False)
    mime_type: Mapped[str | None] = mapped_column(String(120), nullable=True)
    file_size: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[int] = mapped_column(TINYINT(1), nullable=False, default=1)

    type = relationship("ProductAttachmentType", back_populates="attachments", lazy="joined")