from sqlalchemy import Column, Text, Integer, ForeignKey, UniqueConstraint
from app.core.database import Base


class UserAccount(Base):
    __tablename__ = "user_account"

    id = Column(Text, primary_key=True)
    username = Column(Text, unique=True, nullable=False)
    display_name = Column(Text, nullable=False)
    password_hash = Column(Text, nullable=False)
    role = Column(Text, nullable=False)  # 'admin' | 'creator'
    is_active = Column(Integer, default=1)
    created_at = Column(Text, nullable=False)
    updated_at = Column(Text, nullable=False)


class Session(Base):
    __tablename__ = "session"

    id = Column(Text, primary_key=True)  # session token
    user_id = Column(Text, ForeignKey("user_account.id"), nullable=False)
    created_at = Column(Text, nullable=False)
    expires_at = Column(Text, nullable=False)


class ProjectRegistry(Base):
    __tablename__ = "project_registry"

    id = Column(Text, primary_key=True)
    name = Column(Text, nullable=False)
    name_en = Column(Text, nullable=True)
    description = Column(Text, default="")
    status = Column(Text, default="active")  # 'active' | 'archived' | 'deleted'
    created_by = Column(Text, ForeignKey("user_account.id"), nullable=False)
    planning_doc_text = Column(Text, nullable=True)
    created_at = Column(Text, nullable=False)
    updated_at = Column(Text, nullable=False)


class ProjectMember(Base):
    __tablename__ = "project_member"

    id = Column(Text, primary_key=True)
    project_id = Column(Text, ForeignKey("project_registry.id"), nullable=False)
    user_id = Column(Text, ForeignKey("user_account.id"), nullable=False)
    role = Column(Text, nullable=False)  # 'owner' | 'member'
    added_by = Column(Text, ForeignKey("user_account.id"), nullable=True)
    created_at = Column(Text, nullable=False)

    __table_args__ = (UniqueConstraint("project_id", "user_id"),)