from datetime import datetime
from typing import List, Optional
from sqlalchemy import String, Text, ForeignKey, Table, Column
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database import Base

task_tags = Table(
    "task_tags",
    Base.metadata,
    Column("task_id", ForeignKey("tasks.id", ondelete="CASCADE"), primary_key=True),
    Column("tag_id",  ForeignKey("tags.id",  ondelete="CASCADE"), primary_key=True),
)


class User(Base):
    __tablename__ = "users"

    id:            Mapped[int]      = mapped_column(primary_key=True)
    username:      Mapped[str]      = mapped_column(String(50),  unique=True, index=True)
    email:         Mapped[str]      = mapped_column(String(255), unique=True, index=True)
    password_hash: Mapped[str]      = mapped_column(String(255))
    role:          Mapped[str]      = mapped_column(String(20),  default="user")
    is_active:     Mapped[bool]     = mapped_column(default=True)
    created_at:    Mapped[datetime] = mapped_column(default=datetime.utcnow)

    projects:       Mapped[List["Project"]] = relationship(back_populates="owner",    cascade="all, delete-orphan")
    assigned_tasks: Mapped[List["Task"]]    = relationship(back_populates="assignee", foreign_keys="Task.assignee_id")
    comments:       Mapped[List["Comment"]] = relationship(back_populates="author",   cascade="all, delete-orphan")


class Project(Base):
    __tablename__ = "projects"

    id:          Mapped[int]           = mapped_column(primary_key=True)
    title:       Mapped[str]           = mapped_column(String(200))
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    owner_id:    Mapped[int]           = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    status:      Mapped[str]           = mapped_column(String(20), default="active")
    created_at:  Mapped[datetime]      = mapped_column(default=datetime.utcnow)

    owner: Mapped["User"]       = relationship(back_populates="projects")
    tasks: Mapped[List["Task"]] = relationship(back_populates="project", cascade="all, delete-orphan")


class Task(Base):
    __tablename__ = "tasks"

    id:          Mapped[int]           = mapped_column(primary_key=True)
    title:       Mapped[str]           = mapped_column(String(200))
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    project_id:  Mapped[int]           = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    assignee_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    status:      Mapped[str]           = mapped_column(String(20), default="todo")
    priority:    Mapped[str]           = mapped_column(String(20), default="medium")
    due_date:    Mapped[Optional[datetime]] = mapped_column(nullable=True)
    created_at:  Mapped[datetime]      = mapped_column(default=datetime.utcnow)

    project:  Mapped["Project"]         = relationship(back_populates="tasks")
    assignee: Mapped[Optional["User"]]  = relationship(back_populates="assigned_tasks", foreign_keys=[assignee_id])
    comments: Mapped[List["Comment"]]   = relationship(back_populates="task", cascade="all, delete-orphan")
    tags:     Mapped[List["Tag"]]       = relationship(secondary=task_tags, back_populates="tasks")


class Tag(Base):
    __tablename__ = "tags"

    id:    Mapped[int] = mapped_column(primary_key=True)
    name:  Mapped[str] = mapped_column(String(50), unique=True)
    color: Mapped[str] = mapped_column(String(7), default="#3776ab")

    tasks: Mapped[List["Task"]] = relationship(secondary=task_tags, back_populates="tags")


class Comment(Base):
    __tablename__ = "comments"

    id:         Mapped[int]      = mapped_column(primary_key=True)
    task_id:    Mapped[int]      = mapped_column(ForeignKey("tasks.id",  ondelete="CASCADE"))
    author_id:  Mapped[int]      = mapped_column(ForeignKey("users.id",  ondelete="CASCADE"))
    content:    Mapped[str]      = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)

    task:   Mapped["Task"] = relationship(back_populates="comments")
    author: Mapped["User"] = relationship(back_populates="comments")