""" Admin panel SQLAlchemy models. All tables managed from the admin dashboard live here: - CommunityPost → Community posts - Reward → Rewards catalogue - ContentPage → Privacy Policy / Terms & Conditions - Faq → FAQ entries """ from __future__ import annotations import enum from datetime import datetime from typing import TYPE_CHECKING from sqlalchemy import Boolean, DateTime, Enum, ForeignKey, Integer, String, Text, UniqueConstraint, func from sqlalchemy.orm import Mapped, mapped_column, relationship from app.database import Base if TYPE_CHECKING: from app.models import User # --------------------------------------------------------------------------- # Community # --------------------------------------------------------------------------- class CommunityPost(Base): """Admin-managed community feed posts.""" __tablename__ = "community_posts" id: Mapped[int] = mapped_column(primary_key=True, index=True) title: Mapped[str] = mapped_column(String(255), nullable=False, default="") post_type: Mapped[str] = mapped_column(String(64), nullable=False) content: Mapped[str] = mapped_column(Text, nullable=False) media_path: Mapped[str | None] = mapped_column(String(512), nullable=True) media_content_type: Mapped[str | None] = mapped_column(String(128), nullable=True) created_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) created_by: Mapped["User | None"] = relationship("User", foreign_keys=[created_by_id]) class CommunityPostLike(Base): """A member like on a community post.""" __tablename__ = "community_post_likes" __table_args__ = ( UniqueConstraint("post_id", "user_id", name="uq_community_post_likes_post_user"), ) id: Mapped[int] = mapped_column(primary_key=True, index=True) post_id: Mapped[int] = mapped_column(ForeignKey("community_posts.id", ondelete="CASCADE"), nullable=False, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) post: Mapped["CommunityPost"] = relationship("CommunityPost") user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) class CommunityPostComment(Base): """A member comment on a community post.""" __tablename__ = "community_post_comments" id: Mapped[int] = mapped_column(primary_key=True, index=True) post_id: Mapped[int] = mapped_column(ForeignKey("community_posts.id", ondelete="CASCADE"), nullable=False, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) comment: Mapped[str] = mapped_column(Text, nullable=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) post: Mapped["CommunityPost"] = relationship("CommunityPost") user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) class CommunityPostShare(Base): """A member share action for a community post and target platform.""" __tablename__ = "community_post_shares" id: Mapped[int] = mapped_column(primary_key=True, index=True) post_id: Mapped[int] = mapped_column(ForeignKey("community_posts.id", ondelete="CASCADE"), nullable=False, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) platform: Mapped[str] = mapped_column(String(32), nullable=False, index=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) post: Mapped["CommunityPost"] = relationship("CommunityPost") user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) # --------------------------------------------------------------------------- # Rewards # --------------------------------------------------------------------------- class Reward(Base): """Rewards members can claim with points.""" __tablename__ = "rewards" id: Mapped[int] = mapped_column(primary_key=True, index=True) name: Mapped[str] = mapped_column(String(255), nullable=False) description: Mapped[str] = mapped_column(Text, nullable=False, default="") points_required: Mapped[int] = mapped_column(Integer, nullable=False) category: Mapped[str] = mapped_column(String(64), nullable=False) media_path: Mapped[str | None] = mapped_column(String(512), nullable=True) media_content_type: Mapped[str | None] = mapped_column(String(128), nullable=True) created_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) created_by: Mapped["User | None"] = relationship("User", foreign_keys=[created_by_id]) class RewardClaim(Base): """Reward claim requests submitted by members for admin approval.""" __tablename__ = "reward_claims" id: Mapped[int] = mapped_column(primary_key=True, index=True) reward_id: Mapped[int | None] = mapped_column(ForeignKey("rewards.id", ondelete="SET NULL"), nullable=True, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) product_name: Mapped[str] = mapped_column(String(255), nullable=False) user_name: Mapped[str] = mapped_column(String(255), nullable=False, default="") email: Mapped[str] = mapped_column(String(255), nullable=False, index=True) phone_number: Mapped[str | None] = mapped_column(String(32), nullable=True) location: Mapped[str | None] = mapped_column(String(255), nullable=True) total_points: Mapped[int] = mapped_column(Integer, nullable=False) remaining_points: Mapped[int | None] = mapped_column(Integer, nullable=True) status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending", index=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) reward: Mapped["Reward | None"] = relationship("Reward") user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) class MemberSessionProgress(Base): """Admin-assigned points, feedback, and completion state per member session.""" __tablename__ = "member_session_progress" id: Mapped[int] = mapped_column(primary_key=True, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) week_id: Mapped[int | None] = mapped_column(Integer, nullable=True) week_name: Mapped[str | None] = mapped_column(String(255), nullable=True) week_index: Mapped[int | None] = mapped_column(Integer, nullable=True) session_id: Mapped[int | None] = mapped_column(Integer, nullable=True) session_name: Mapped[str | None] = mapped_column(String(255), nullable=True) session_number: Mapped[int | None] = mapped_column(Integer, nullable=True) assigned_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False) feedback: Mapped[str | None] = mapped_column(Text, nullable=True) feedback_notified: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) image_path: Mapped[str | None] = mapped_column(String(512), nullable=True) is_completed: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) assigned_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) assigned_by: Mapped["User | None"] = relationship("User", foreign_keys=[assigned_by_id]) images: Mapped[list["MemberSessionProgressImage"]] = relationship( "MemberSessionProgressImage", cascade="all, delete-orphan", order_by="MemberSessionProgressImage.created_at", ) class MemberInactiveFollowUp(Base): """Members inactive for N days — shown in admin Need Follow-Up tab.""" __tablename__ = "member_inactive_followups" id: Mapped[int] = mapped_column(primary_key=True, index=True) user_id: Mapped[int] = mapped_column( ForeignKey("users.id", ondelete="CASCADE"), nullable=False, unique=True, index=True, ) last_active_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) days_inactive: Mapped[int] = mapped_column(Integer, default=0, nullable=False, index=True) follow_up_sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) class MemberSessionProgressImage(Base): """Uploaded images attached to one MemberSessionProgress row (supports multiple images).""" __tablename__ = "member_session_progress_images" id: Mapped[int] = mapped_column(primary_key=True, index=True) progress_id: Mapped[int] = mapped_column( ForeignKey("member_session_progress.id", ondelete="CASCADE"), nullable=False, index=True, ) image_path: Mapped[str] = mapped_column(String(512), nullable=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) progress: Mapped["MemberSessionProgress"] = relationship("MemberSessionProgress", foreign_keys=[progress_id]) # --------------------------------------------------------------------------- # Member points summary # --------------------------------------------------------------------------- class MemberPoints(Base): """Per-member cached points summary (assigned and available). This table stores the total assigned points and the current available points after accounting for claims/reservations. It's updated whenever an admin assigns points via the member progress endpoints. """ __tablename__ = "member_points" id: Mapped[int] = mapped_column(primary_key=True, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) assigned_points_total: Mapped[int] = mapped_column(Integer, default=0, nullable=False) available_points: Mapped[int] = mapped_column(Integer, default=0, nullable=False) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) # --------------------------------------------------------------------------- # Notifications # --------------------------------------------------------------------------- class MemberNotificationType(str, enum.Enum): announcement = "announcement" reward = "reward" feedback = "feedback" system = "system" achievement = "achievement" class AdminNotificationType(str, enum.Enum): reward_claim = "reward_claim" track_submit = "track_submit" community_like = "community_like" community_comment = "community_comment" community_share = "community_share" class AdminNotification(Base): """Activity alerts for admins when members claim rewards, submit tracks, or engage on community.""" __tablename__ = "admin_notifications" id: Mapped[int] = mapped_column(primary_key=True, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) notification_type: Mapped[str] = mapped_column(String(64), nullable=False, index=True) title: Mapped[str] = mapped_column(String(255), nullable=False) message: Mapped[str] = mapped_column(Text, nullable=False) reference_type: Mapped[str | None] = mapped_column(String(64), nullable=True) reference_id: Mapped[int | None] = mapped_column(Integer, nullable=True) is_read: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) class MemberNotification(Base): """Notifications sent to members from the admin Community panel.""" __tablename__ = "member_notifications" id: Mapped[int] = mapped_column(primary_key=True, index=True) user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) notification_type: Mapped[str] = mapped_column(String(64), nullable=False, index=True) title: Mapped[str] = mapped_column(String(255), nullable=False) message: Mapped[str] = mapped_column(Text, nullable=False) is_read: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) admin_cleared: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) sent_by_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) user: Mapped["User"] = relationship("User", foreign_keys=[user_id]) sent_by: Mapped["User | None"] = relationship("User", foreign_keys=[sent_by_id]) # --------------------------------------------------------------------------- # Content management (Privacy / Terms / FAQ) # --------------------------------------------------------------------------- class ContentPageSlug(str, enum.Enum): privacy_policy = "privacy_policy" terms_conditions = "terms_conditions" class FaqStatus(str, enum.Enum): completed = "completed" draft = "draft" class ContentPage(Base): """Static legal pages (Privacy Policy, Terms & Conditions).""" __tablename__ = "content_pages" id: Mapped[int] = mapped_column(primary_key=True) slug: Mapped[ContentPageSlug] = mapped_column( Enum(ContentPageSlug, values_callable=lambda x: [e.value for e in x], native_enum=False), unique=True, nullable=False, ) title: Mapped[str] = mapped_column(String(255), nullable=False) body: Mapped[str] = mapped_column(Text, nullable=False, default="") updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) class ContactMessage(Base): """Landing-page contact form submissions.""" __tablename__ = "contact_messages" id: Mapped[int] = mapped_column(primary_key=True, index=True) full_name: Mapped[str] = mapped_column(String(255), nullable=False) email: Mapped[str] = mapped_column(String(255), nullable=False, index=True) phone_number: Mapped[str | None] = mapped_column(String(32), nullable=True) subject: Mapped[str] = mapped_column(String(255), nullable=False) message: Mapped[str] = mapped_column(Text, nullable=False) email_sent: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) class Faq(Base): """FAQ question / answer entries.""" __tablename__ = "faqs" id: Mapped[int] = mapped_column(primary_key=True, index=True) question: Mapped[str] = mapped_column(String(512), nullable=False) answer: Mapped[str] = mapped_column(Text, nullable=False) status: Mapped[FaqStatus] = mapped_column( Enum(FaqStatus, values_callable=lambda x: [e.value for e in x], native_enum=False), nullable=False, default=FaqStatus.completed, ) view_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) __all__ = [ "CommunityPost", "Reward", "RewardClaim", "MemberSessionProgress", "MemberInactiveFollowUp", "MemberSessionProgressImage", "AdminNotificationType", "AdminNotification", "MemberNotificationType", "MemberNotification", "ContactMessage", "ContentPageSlug", "FaqStatus", "ContentPage", "Faq", ]