src/database/models.py

from datetime import datetime, timezone, time, date as dt_date
from sqlmodel import SQLModel, Field, Relationship
from sqlalchemy import Column, DateTime, text, Numeric, func
from enum import Enum
from sqlalchemy.dialects.postgresql import JSONB
from typing import Optional
from decimal import Decimal

class ActivityCategory(str, Enum):
    prayer = "prayer"
    meal = "meal"
    habit = "habit"
    exercise = "exercise"
    productivity = "productivity"

class TransactionCategory(str, Enum):
    INCOME = "income"
    EXPENSE = "expense"

class User(SQLModel, table=True):
    __tablename__ = "users"

    id: int = Field(default=None, primary_key=True)

    phone_number: str = Field(index=True, unique=True)
    email: str = Field(index=True, unique=True)
    hashed_password: str
    name: Optional[str] = None

    timezone: str = "Asia/Karachi"

    created_at: datetime = Field(
        default_factory=lambda: datetime.now(timezone.utc),
        sa_column=Column(DateTime(timezone=True), server_default=text("CURRENT_TIMESTAMP"), nullable=False)
    )
    updated_at: datetime = Field(
    default_factory=lambda: datetime.now(timezone.utc),
    sa_column=Column(
        DateTime(timezone=True), 
        server_default=func.now(), 
        onupdate=func.now(), # SQLAlchemy will now handle this dynamically
        nullable=False
    ))
    activity_entries: list["ActivityEntry"] = Relationship(back_populates="user")
    goals: list["Goal"] = Relationship(back_populates="user")
    day_logs: list["DayLog"] = Relationship(back_populates="user")
    wallet: "Wallet" = Relationship(back_populates="user")
    transactions: list["Transaction"] = Relationship(back_populates="user")

class Goal(SQLModel, table=True):
    __tablename__ = "goals"

    id: int = Field(default=None, primary_key=True)

    user_id: int = Field(
        foreign_key="users.id",
        index=True
    )


    date: dt_date = Field(default_factory=dt_date.today, index=True)
    title: str = Field(index=True)
    description: Optional[str] = None
    total_productivity_hours: Optional[float] = Field(default=0.0)

    active_status: Optional[bool] = Field(default=None)

    activity_entries: list["ActivityEntry"] = Relationship(back_populates="goal")
    user: "User" = Relationship(back_populates="goals")


class DayLog(SQLModel, table=True):
    __tablename__ = "day_logs"

    id: int = Field(default=None, primary_key=True)

    user_id: int = Field(
        foreign_key="users.id",
        index=True
    )

    date: dt_date = Field(default_factory=dt_date.today, index=True)
    bed_time: Optional[time] = None
    sleep_time: Optional[time] = None
    wake_time: Optional[time] = None
    sleep_duration_hours: Optional[float] = None
    total_productivity_min: Optional[float] = Field(default=0.0)
    total_calories: Optional[int] = None
    notes: Optional[str] = None

    user: "User" = Relationship(back_populates="day_logs")
    activity_entries: list["ActivityEntry"] = Relationship(
        back_populates="day_log"
    )
    transactions: list["Transaction"] = Relationship(back_populates="day_log")

class ActivityEntry(SQLModel, table=True):
    __tablename__ = "activity_entries"

    id: int = Field(default=None, primary_key=True)
    user_id: int = Field(
        foreign_key="users.id",
        index=True
    )
    day_log_id: int = Field(
        foreign_key="day_logs.id",
        index=True
    )
    goal_id: Optional[int] = Field(
        default=None,
        foreign_key="goals.id",
        index=True
    )
    goal_name: Optional[str] = None
    date: dt_date = Field(default_factory=dt_date.today, index=True)
    category: ActivityCategory = Field(index=True)
    activity_type_name: str = Field(index=True)
    note: Optional[str] = None

    #for dynamic fields like location, quantity_text, what_i_did, result_text, jamaat,
    #sets, reps, on_time, duration_min, occurred_at, active_status etc.
    details: dict = Field(
        default_factory=dict,
        sa_column=Column(JSONB, server_default='{}')
    )
    user: "User" = Relationship(back_populates="activity_entries")
    goal: Optional["Goal"] = Relationship(back_populates="activity_entries")
    day_log: "DayLog" = Relationship(
        back_populates="activity_entries"
    )

class Wallet(SQLModel, table=True):
    """Stores the running total/balance for the user."""
    __tablename__ = "wallets"

    id: int = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="users.id", index=True, unique=True)
    
    # Using Numeric/Decimal for precise money math
    balance: Decimal = Field(default=Decimal("0.00"), sa_column=Column(Numeric(10, 2)))
    
    updated_at: datetime = Field(
        default_factory=lambda: datetime.now(timezone.utc),
        sa_column=Column(DateTime(timezone=True), server_default=text("CURRENT_TIMESTAMP"), onupdate=text("CURRENT_TIMESTAMP"))
    )

    user: "User" = Relationship(back_populates="wallet")
    transactions: list["Transaction"] = Relationship(back_populates="wallet")

class Transaction(SQLModel, table=True):
    """Records every addition (income) and subtraction (expense)."""
    __tablename__ = "transactions"

    id: int = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="users.id", index=True) 
    day_log_id: int = Field(foreign_key="day_logs.id", index=True)
    wallet_id: int = Field(foreign_key="wallets.id", index=True)

    date: dt_date = Field(default_factory=dt_date.today, index=True)
    category: TransactionCategory = Field(index=True)
    amount: Decimal = Field(sa_column=Column(Numeric(10, 2)))
    note: Optional[str] = None
    
    # JSONB for the dynamic fields (source, type, litre, dayMode, what_eat)
    details: dict = Field(
        default_factory=dict,
        sa_column=Column(JSONB, server_default='{}')
    )
    # ADDED: Relationships to map back to your other tables
    user: "User" = Relationship(back_populates="transactions")
    day_log: "DayLog" = Relationship(back_populates="transactions")
    wallet: "Wallet" = Relationship(back_populates="transactions")