from typing import List
from sqlalchemy import ForeignKey, String, create_engine
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship,
    sessionmaker,
)
from sqlalchemy.orm.session import Session
from sqlalchemy.types import Boolean

class Base(DeclarativeBase):
    pass


class Image(Base):
    __tablename__ = "image"
    id: Mapped[int] = mapped_column(primary_key=True)
    ia_name: Mapped[str] = mapped_column(String(255))
    file_name: Mapped[str] = mapped_column(String(255))
    description: Mapped[str] = mapped_column(String(1000))
    category_id: Mapped[int] = mapped_column(ForeignKey("category.id"))
    category: Mapped["Category"] = relationship(
        back_populates="images",
        cascade="all, delete-orphan",
        single_parent=True,
    )

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"


class Category(Base):
    __tablename__ = "category"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255))
    dir_name: Mapped[str] = mapped_column(String(255))
    hay_fotos: Mapped[bool] = mapped_column(Boolean, default=True)
    hay_tamanos: Mapped[bool] = mapped_column(Boolean, default=True)
    images: Mapped[List["Image"]] = relationship(
        back_populates="category", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"Category(id={self.id!r}, name={self.name!r})"


class Database:
    def __init__(self):
        self.engine = create_engine('sqlite+pysqlite:///./db.sqlite3', echo=True)
        self.session = sessionmaker[Session](self.engine)

        Base.metadata.create_all(self.engine)

    def add_category(self, name: str, dir_name: str, hay_fotos: bool = True, hay_tamanos: bool = True):
        try:
            with self.session() as session:
                category = Category(name=name, dir_name=dir_name, hay_fotos=hay_fotos, hay_tamanos=hay_tamanos)
                session.add(category)
                session.commit()
        except Exception as e:
            print(f"Error adding category: {e}")
            raise e
    
    def get_category(self, dir_name: str):
        try:
            with self.session() as session:
                return session.query(Category).filter(Category.dir_name == dir_name).first()
        except Exception as e:
            print(f"Error getting category: {e}")
            raise e


    def add_image(self, ia_name: str, file_name: str, description: str, category_id: int):
        try:
            with self.session() as session:
                image = Image(ia_name=ia_name, file_name=file_name, description=description, category_id=category_id)
                session.add(image)
                session.commit()
        except Exception as e:
            print(f"Error adding image: {e}")
            raise e
    
    def last_record(self):
        try:
            with self.session() as session:
                return session.new
        except Exception as e:
            print(f"Error getting last record: {e}")
            raise e

    def get_all(self, model: str, type: str = "all"):
        try:
            with self.session() as session:
                if model == "Category":
                    if type == "all":
                        return session.query(Category).all()
                    elif type == "pictures":
                        return session.query(Category).filter(Category.hay_fotos).all()
                    elif type == "size":
                        return session.query(Category).filter(Category.hay_tamanos).all()
                    else:
                        raise ValueError(f"Invalid type: {type}")
        except Exception as e:
            print(f"Error getting all {model} records: {e}")
            raise e
    
    def count_images(self, category_id: int):
        try:
            with self.session() as session:
                return session.query(Image).filter(Image.category_id == category_id).count()
        except Exception as e:
            print(f"Error counting images: {e}")
            raise e
    
    def image_exists(self, file_name: str):
        try:
            with self.session() as session:
                image = session.query(Image).filter(Image.file_name == file_name).first()
                if image is not None:
                    return True
                else:
                    return False
        except Exception as e:
            print(f"Error checking if image exists: {e}")
            raise e