from sqlalchemy import Column, Integer, Text, ForeignKey, DateTime, create_engine
from sqlalchemy.orm import DeclarativeBase, relationship


class Base(DeclarativeBase):
    pass


class Actress(Base):
    __tablename__ = "actress"

    id = Column(Integer, primary_key=True)
    name = Column(Text, unique=True, nullable=False)
    name_kana = Column(Text)
    height = Column(Integer)
    bust = Column(Integer)
    waist = Column(Integer)
    hip = Column(Integer)
    profile_url = Column(Text)

    aliases = relationship("ActressAlias", back_populates="actress", cascade="all, delete-orphan")


class ActressAlias(Base):
    __tablename__ = "actress_alias"

    id = Column(Integer, primary_key=True)
    actress_id = Column(Integer, ForeignKey("actress.id"), nullable=False)
    alias_name = Column(Text, unique=True, nullable=False)
    alias_kana = Column(Text)
    dmm_actress_id = Column(Integer)
    note = Column(Text)

    actress = relationship("Actress", back_populates="aliases")


class Label(Base):
    __tablename__ = "label"

    id = Column(Integer, primary_key=True)
    name = Column(Text, unique=True, nullable=False)
    type = Column(Text, nullable=False)
    wiki_url = Column(Text)
    parent_id = Column(Integer, ForeignKey("label.id"))

    children = relationship("Label", back_populates="parent", cascade="all, delete-orphan")
    parent = relationship("Label", back_populates="children", remote_side=[id])


class Video(Base):
    __tablename__ = "video"

    id = Column(Integer, primary_key=True)
    dvd_id = Column(Text, unique=True, nullable=False)
    prefix = Column(Text)
    title = Column(Text)
    release_date = Column(Text)
    cover_url = Column(Text)
    dmm_url = Column(Text)
    label_id = Column(Integer, ForeignKey("label.id"))

    label = relationship("Label")


class VideoActress(Base):
    __tablename__ = "video_actress"

    video_id = Column(Integer, ForeignKey("video.id"), primary_key=True)
    actress_id = Column(Integer, ForeignKey("actress.id"), primary_key=True)
    role_name = Column(Text)


class CrawlStatus(Base):
    __tablename__ = "crawl_status"

    page_name = Column(Text, primary_key=True)
    page_display_name = Column(Text)
    page_type = Column(Text)
    page_url = Column(Text)
    last_checked = Column(DateTime)
    last_updated = Column(Text)
    http_status = Column(Integer)
    error_msg = Column(Text)