app/models/progress.py

# app/models/progress.py
"""Progress / daily plan database model — production-hardened."""

from datetime import datetime, timezone

from sqlalchemy import (
    Column, Integer, String, DateTime, Float, Text,
    Boolean, JSON, ForeignKey, Index,
)
from sqlalchemy.orm import relationship

from app.core.database import Base


class Progress(Base):
    __tablename__ = "progress"

    id      = Column(Integer, primary_key=True, index=True)

    # ── FK relationships ───────────────────────────────────────────────────
    user_id = Column(
        String(100),
        ForeignKey("users.user_id", ondelete="CASCADE"),
        index=True,
        nullable=False,
    )
    goal_id = Column(
        Integer,
        ForeignKey("goals.id", ondelete="SET NULL"),
        index=True,
        nullable=True,
    )

    date = Column(String(20), index=True, nullable=False)   # ISO date YYYY-MM-DD

    # ── Plan content ───────────────────────────────────────────────────────
    module = Column(String(255), nullable=True)
    topic  = Column(String(255), nullable=True)
    tasks  = Column(JSON, default=list)

    # ── Behavioral snapshot at plan creation ───────────────────────────────
    mood              = Column(Float, default=0.5)
    fatigue           = Column(Float, default=0.0)
    action_label      = Column(String(50), default="Unknown")
    intensity         = Column(Float, default=0.0)
    raw_action_vector = Column(Text, default="[]")

    # ── Completion ─────────────────────────────────────────────────────────
    completed            = Column(Boolean, default=False, nullable=False)
    actual_duration_min  = Column(Integer, nullable=True)
    satisfaction         = Column(Float, nullable=True)    # 0.0–1.0 user-reported
    notes                = Column(Text, default="")

    # ── Timestamps ─────────────────────────────────────────────────────────
    created_at   = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    completed_at = Column(DateTime, nullable=True)

    # ── Composite indexes for hot query patterns ────────────────────────────
    __table_args__ = (
        # Most-used: get today's plan for a user
        Index("ix_progress_user_date", "user_id", "date"),
        # Stats queries: completed tasks per user per goal
        Index("ix_progress_user_goal_completed", "user_id", "goal_id", "completed"),
    )

    def __repr__(self) -> str:
        return f"<Progress {self.user_id} {self.date} completed={self.completed}>"