from sqlalchemy import Column, Integer, String, Float, DateTime, Text, Table, ForeignKey
from sqlalchemy.orm import relationship

from app.db.database import Base
from datetime import datetime


# Association table for books and categories (many-to-many)
book_category_association = Table(
    "book_category",
    Base.metadata,
    Column("book_id", Integer, ForeignKey("books.id"), primary_key=True),
    Column("category_id", Integer, ForeignKey("categories.id"), primary_key=True),
)

# Association table for books and authors (many-to-many)
book_author_association = Table(
    "book_author",
    Base.metadata,
    Column("book_id", Integer, ForeignKey("books.id"), primary_key=True),
    Column("author_id", Integer, ForeignKey("authors.id"), primary_key=True),
)


class Book(Base):
    __tablename__ = "books"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True, nullable=False)
    isbn = Column(String, unique=True, index=True, nullable=False)
    description = Column(Text)
    price = Column(Float, nullable=False)
    cover_image_url = Column(String)
    publication_date = Column(DateTime)
    publisher = Column(String)
    language = Column(String)
    page_count = Column(Integer)
    stock_quantity = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    authors = relationship(
        "Author", secondary=book_author_association, back_populates="books"
    )
    categories = relationship(
        "Category", secondary=book_category_association, back_populates="books"
    )
    order_items = relationship("OrderItem", back_populates="book")


class Author(Base):
    __tablename__ = "authors"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True, nullable=False)
    biography = Column(Text)
    birthdate = Column(DateTime)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    books = relationship(
        "Book", secondary=book_author_association, back_populates="authors"
    )


class Category(Base):
    __tablename__ = "categories"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, index=True, nullable=False)
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    books = relationship(
        "Book", secondary=book_category_association, back_populates="categories"
    )