import uuid
from datetime import datetime, timezone

from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.dialects.postgresql import ARRAY, UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base


def utcnow():
    return datetime.now(timezone.utc)


class Activity(Base):
    __tablename__ = "activities"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    title: Mapped[str] = mapped_column(String(300), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    category: Mapped[str] = mapped_column(String(50), nullable=False)
    # course | reading | video | hackathon | workshop | tool_usage | certification | contribution | agent_creation
    platform: Mapped[str | None] = mapped_column(String(100))
    # udemy | linkedin_learning | viva | coursera | internal | github | genspark | claude | m365_copilot | stream | custom
    external_url: Mapped[str | None] = mapped_column(Text)
    external_id: Mapped[str | None] = mapped_column(String(255))
    xp_value: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    xp_open_pct: Mapped[int] = mapped_column(Integer, default=100)
    # % of XP for just opening content. 100 = no quiz needed. 30 = 30% on open, 70% on quiz pass
    estimated_duration_minutes: Mapped[int | None] = mapped_column(Integer)
    difficulty: Mapped[str] = mapped_column(String(20), default="beginner")
    is_mandatory: Mapped[bool] = mapped_column(Boolean, default=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    is_recurring: Mapped[bool] = mapped_column(Boolean, default=False)
    recurrence_period: Mapped[str | None] = mapped_column(String(20))  # monthly | weekly
    trust_tier: Mapped[int] = mapped_column(Integer, default=1)  # 1=API, 2=admin-import, 3=self-reported
    thumbnail_url: Mapped[str | None] = mapped_column(Text)
    tags: Mapped[list[str] | None] = mapped_column(ARRAY(String))
    quiz_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True))
    created_by: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)

    completions: Mapped[list["ActivityCompletion"]] = relationship(back_populates="activity")


class ActivityCompletion(Base):
    __tablename__ = "activity_completions"

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    activity_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("activities.id", ondelete="CASCADE"), nullable=False)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="not_started")
    # not_started | in_progress | completed | verified | pending_verification | flagged
    progress_pct: Mapped[int] = mapped_column(Integer, default=0)
    started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    verified_by: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"))
    verification_note: Mapped[str | None] = mapped_column(Text)
    evidence_url: Mapped[str | None] = mapped_column(Text)
    xp_awarded: Mapped[int] = mapped_column(Integer, default=0)
    source: Mapped[str] = mapped_column(String(50), default="manual")
    # manual | api_sync | excel_import | self_reported | content_tracker | quiz
    source_detail: Mapped[str | None] = mapped_column(String(200))
    trust_tier: Mapped[int] = mapped_column(Integer, default=3)
    # 1=API-verified, 2=admin-imported, 3=self-reported
    period_key: Mapped[str | None] = mapped_column(String(20))
    # For recurring activities: "2025-03" for monthly
    flagged_reason: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)

    user: Mapped["User"] = relationship(back_populates="completions", foreign_keys=[user_id])
    activity: Mapped[Activity] = relationship(back_populates="completions")

    __table_args__ = (
        UniqueConstraint("user_id", "activity_id", "period_key", name="uq_completion_user_activity_period"),
    )


from app.models.user import User  # noqa: E402