import uuid
from datetime import datetime

from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base


def new_uuid() -> str:
    return str(uuid.uuid4())


class Job(Base):
    __tablename__ = "jobs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    linkedin_url: Mapped[str] = mapped_column(String(500), default="")
    title: Mapped[str] = mapped_column(String(300))
    company: Mapped[str] = mapped_column(String(200), default="")
    subject: Mapped[str] = mapped_column(String(80), default="other")
    location: Mapped[str] = mapped_column(String(200), default="")
    description: Mapped[str] = mapped_column(Text, default="")
    description_clean: Mapped[str] = mapped_column(Text, default="")
    extra_info: Mapped[str] = mapped_column(Text, default="")
    ral_min: Mapped[int | None] = mapped_column(Integer, nullable=True)
    ral_max: Mapped[int | None] = mapped_column(Integer, nullable=True)
    ral_exact: Mapped[int | None] = mapped_column(Integer, nullable=True)
    work_type: Mapped[str] = mapped_column(String(20), default="hybrid")
    difficulty: Mapped[int] = mapped_column(Integer, default=1)
    status: Mapped[str] = mapped_column(String(20), default="ready")
    fetch_error: Mapped[str] = mapped_column(Text, default="")
    source: Mapped[str] = mapped_column(String(40), default="seed")
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)


class FetchTask(Base):
    __tablename__ = "fetch_tasks"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    url: Mapped[str] = mapped_column(String(500))
    status: Mapped[str] = mapped_column(String(20), default="queued")
    error: Mapped[str] = mapped_column(Text, default="")
    job_id: Mapped[int | None] = mapped_column(ForeignKey("jobs.id"), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    started_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    job: Mapped[Job | None] = relationship()


class GameSession(Base):
    __tablename__ = "game_sessions"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=new_uuid)
    host_token: Mapped[str] = mapped_column(String(36), default=new_uuid)
    max_players: Mapped[int] = mapped_column(Integer, default=8)
    num_rounds: Mapped[int] = mapped_column(Integer, default=8)
    extra_guesses: Mapped[bool] = mapped_column(Boolean, default=False)
    hide_company: Mapped[bool] = mapped_column(Boolean, default=False)
    hide_subject: Mapped[bool] = mapped_column(Boolean, default=False)
    status: Mapped[str] = mapped_column(String(20), default="lobby")
    current_round: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

    players: Mapped[list["Player"]] = relationship(back_populates="session", cascade="all, delete-orphan")
    rounds: Mapped[list["Round"]] = relationship(back_populates="session", cascade="all, delete-orphan")


class Player(Base):
    __tablename__ = "players"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    session_id: Mapped[str] = mapped_column(ForeignKey("game_sessions.id"))
    name: Mapped[str] = mapped_column(String(40))
    score: Mapped[int] = mapped_column(Integer, default=0)
    connected: Mapped[bool] = mapped_column(Boolean, default=True)
    is_host: Mapped[bool] = mapped_column(Boolean, default=False)
    token: Mapped[str] = mapped_column(String(36), default=new_uuid)

    session: Mapped[GameSession] = relationship(back_populates="players")
    answers: Mapped[list["Answer"]] = relationship(back_populates="player", cascade="all, delete-orphan")


class Round(Base):
    __tablename__ = "rounds"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    session_id: Mapped[str] = mapped_column(ForeignKey("game_sessions.id"))
    job_id: Mapped[int] = mapped_column(ForeignKey("jobs.id"))
    round_number: Mapped[int] = mapped_column(Integer)
    is_bonus: Mapped[bool] = mapped_column(Boolean, default=False)
    options_json: Mapped[str] = mapped_column(Text)
    correct_option: Mapped[str] = mapped_column(String(80))
    status: Mapped[str] = mapped_column(String(20), default="open")

    session: Mapped[GameSession] = relationship(back_populates="rounds")
    job: Mapped[Job] = relationship()
    answers: Mapped[list["Answer"]] = relationship(back_populates="round", cascade="all, delete-orphan")


class Answer(Base):
    __tablename__ = "answers"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    round_id: Mapped[int] = mapped_column(ForeignKey("rounds.id"))
    player_id: Mapped[int] = mapped_column(ForeignKey("players.id"))
    choice: Mapped[str] = mapped_column(String(80))
    precise_guess: Mapped[int | None] = mapped_column(Integer, nullable=True)
    work_type_guess: Mapped[str] = mapped_column(String(20), default="")
    points: Mapped[int] = mapped_column(Integer, default=0)

    round: Mapped[Round] = relationship(back_populates="answers")
    player: Mapped[Player] = relationship(back_populates="answers")