from typing import List
from sqlalchemy import ForeignKey
from sqlalchemy import Table, Column
from sqlalchemy import String, Integer, Boolean
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass

#Many to many relationship
consoles_extensions = Table(
    " consoles_extensions",
    Base.metadata,
    Column("console_id", ForeignKey("console.id")),
    Column("extension_id", ForeignKey("rom_file_extensions.id")),
)

class Console(Base):
    __tablename__ = 'console'
    id : Mapped[int] = mapped_column(primary_key=True)
    name : Mapped[str] = mapped_column(String(400))
    core : Mapped[str] = mapped_column(String(400))
    playable = Mapped[bool]

    #Many to many relationship
    rom_files_extensions : Mapped[List['RomFileExtensions']] = relationship(secondary=consoles_extensions, back_populates='consoles')

    #One to many relationship
    rom_files : Mapped[List['RomFile']] = relationship(back_populates='console')

    def __init__(self,name ,core ,playable, rom_files_extensions):
        self.name = name
        self.core = core
        self.playable = playable
        self.rom_files_extensions = rom_files_extensions

    def __repr__(self):
        return f"<Console(name={self.name},core={self.core}, playable={self.playable})>"

class RomFileExtensions(Base):
    __tablename__ = 'rom_file_extensions'
    id : Mapped[int] = mapped_column(primary_key=True)
    name : Mapped[str] = mapped_column(String(400))

    #Many to many relationship
    consoles: Mapped[List[Console]] = relationship(secondary=consoles_extensions, back_populates='rom_files_extensions')
    
    #One to many relationship
    roms : Mapped[List['RomFile']] = relationship(back_populates='extension')

    def __init__(self, name, consoles):
        self.name = name
        self.consoles = consoles

    def __repr__(self):
        return f"<RomFileExtensions(name={self.name})>"

rom_language = Table(
    "rom_language",
    Base.metadata,
    Column("language_id", ForeignKey("language.id")),
    Column("rom_id", ForeignKey("rom_file.id")),
)

class Language(Base):
    __tablename__ = 'language'
    id : Mapped[int] = mapped_column(primary_key=True)
    name : Mapped[str] = mapped_column(String(400))

    #Many to many relationship
    roms : Mapped[List['RomFile']] = relationship(secondary=rom_language, back_populates='languages')

    def __init__(self, name, roms):
        self.name = name
        self.roms = roms
    
    def __repr__(self):
        return f"<Language(name={self.name})>"

#Many to many relationship
rom_category = Table(
    "rom_category",
    Base.metadata,
    Column("category_id", ForeignKey("category.id")),
    Column("rom_id", ForeignKey("rom_file.id")),
)

class Category(Base):
    __tablename__ = 'category'
    id : Mapped[int] = mapped_column(primary_key=True)
    name : Mapped[str] = mapped_column(String(400))

    #Many to many relationship
    roms : Mapped[List['RomFile']] = relationship(secondary=rom_category, back_populates='categories')

    def __init__(self, name, roms):
        self.name = name
        self.roms = roms

    def __repr__(self):
        return f"<Category(name={self.name})>"
    
class RomFile(Base):
    __tablename__ = 'rom_file'
    id : Mapped[int] = mapped_column(primary_key=True)
    name : Mapped[str] = mapped_column(String(400))
    path : Mapped[str] = mapped_column(String(400))

    #Many to many relationship
    categories : Mapped[List[Category]] = relationship(secondary=rom_category, back_populates='roms')

    #Many to many relationship
    languages : Mapped[List[Language]] = relationship(secondary=rom_language, back_populates='roms')

    #One to many relationship
    extension : Mapped[RomFileExtensions] = relationship(back_populates="roms")
    extension_id : Mapped[int] = mapped_column(ForeignKey('rom_file_extensions.id'))

    #One to many relationship
    console : Mapped[Console] = relationship(back_populates="rom_files")
    console_id : Mapped[int] = mapped_column(ForeignKey('console.id'))

    def __init__(self, name, path, categories, languages, extension, console):
        self.name = name
        self.path = path
        self.categories = categories
        self.languages = languages
        self.extension = extension
        self.console = console

    def __repr__(self):
        return f"<RomFile(name={self.name}, path={self.path})>"