from datetime import datetime
from typing import Optional

from sqlalchemy import BigInteger, DateTime, Integer, String, func
from sqlalchemy.orm import Mapped, mapped_column

from app.db.base import Base


class ActivityEvent(Base):
    """Small usage log that feeds /analyze: downloads and searches (so we learn what students look for)."""

    __tablename__ = "activity_events"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(BigInteger, index=True)
    kind: Mapped[str] = mapped_column(String(12), index=True)  # download | search | inline | find
    ref: Mapped[str] = mapped_column(String(120))  # material id, or the search words
    n: Mapped[int] = mapped_column(Integer, default=0)  # for searches: how many results were found
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)


class ChannelPost(Base):
    """A post in the main channel (seen live, or added by forwarding it to the bot)."""

    __tablename__ = "channel_posts"

    chat_id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=False)
    message_id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=False)
    posted_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
    preview: Mapped[Optional[str]] = mapped_column(String(300))
    tags: Mapped[Optional[str]] = mapped_column(String(120))  # ?start= tags found in the post, comma separated
    reactions: Mapped[Optional[int]] = mapped_column(Integer)  # None = unknown