204 lines
9.6 KiB
Python
204 lines
9.6 KiB
Python
# app/models.py
|
|
import enum
|
|
import secrets
|
|
from datetime import datetime, timedelta, timezone
|
|
|
|
from sqlalchemy import (
|
|
Column,
|
|
Integer,
|
|
String,
|
|
DateTime,
|
|
ForeignKey,
|
|
Boolean,
|
|
Enum as SAEnum,
|
|
UniqueConstraint,
|
|
Index,
|
|
DDL,
|
|
event,
|
|
delete,
|
|
func,
|
|
text as sa_text,
|
|
Text, # <-- Add Text for description
|
|
Numeric, # <-- Add Numeric for price
|
|
ARRAY
|
|
)
|
|
from sqlalchemy.orm import relationship
|
|
|
|
from .database import Base
|
|
|
|
# --- Enums ---
|
|
class UserRoleEnum(enum.Enum):
|
|
owner = "owner"
|
|
member = "member"
|
|
|
|
class SplitTypeEnum(enum.Enum):
|
|
equal = "equal"
|
|
# Add other types later if needed (e.g., custom, percentage)
|
|
# custom = "custom"
|
|
|
|
class SettlementActivityTypeEnum(enum.Enum):
|
|
marked_paid = "marked_paid"
|
|
marked_unpaid = "marked_unpaid"
|
|
# Add other activity types later if needed
|
|
|
|
# --- User Model ---
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
email = Column(String, unique=True, index=True, nullable=False)
|
|
password_hash = Column(String, nullable=False)
|
|
name = Column(String, index=True, nullable=True)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
|
|
# --- Relationships ---
|
|
created_groups = relationship("Group", back_populates="creator")
|
|
group_associations = relationship("UserGroup", back_populates="user", cascade="all, delete-orphan")
|
|
created_invites = relationship("Invite", back_populates="creator")
|
|
|
|
# --- NEW Relationships for Lists/Items ---
|
|
created_lists = relationship("List", foreign_keys="List.created_by_id", back_populates="creator") # Link List.created_by_id -> User
|
|
added_items = relationship("Item", foreign_keys="Item.added_by_id", back_populates="added_by_user") # Link Item.added_by_id -> User
|
|
completed_items = relationship("Item", foreign_keys="Item.completed_by_id", back_populates="completed_by_user") # Link Item.completed_by_id -> User
|
|
# --- End NEW Relationships ---
|
|
|
|
|
|
# --- Group Model ---
|
|
class Group(Base):
|
|
__tablename__ = "groups"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, index=True, nullable=False)
|
|
created_by_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
|
|
# --- Relationships ---
|
|
creator = relationship("User", back_populates="created_groups")
|
|
member_associations = relationship("UserGroup", back_populates="group", cascade="all, delete-orphan")
|
|
invites = relationship("Invite", back_populates="group", cascade="all, delete-orphan")
|
|
|
|
# --- NEW Relationship for Lists ---
|
|
lists = relationship("List", back_populates="group", cascade="all, delete-orphan") # Link List.group_id -> Group
|
|
# --- End NEW Relationship ---
|
|
|
|
|
|
# --- UserGroup Association Model ---
|
|
class UserGroup(Base):
|
|
__tablename__ = "user_groups"
|
|
__table_args__ = (UniqueConstraint('user_id', 'group_id', name='uq_user_group'),)
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
|
|
group_id = Column(Integer, ForeignKey("groups.id", ondelete="CASCADE"), nullable=False)
|
|
role = Column(SAEnum(UserRoleEnum, name="userroleenum", create_type=True), nullable=False, default=UserRoleEnum.member)
|
|
joined_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
|
|
user = relationship("User", back_populates="group_associations")
|
|
group = relationship("Group", back_populates="member_associations")
|
|
|
|
|
|
# --- Invite Model ---
|
|
class Invite(Base):
|
|
__tablename__ = "invites"
|
|
__table_args__ = (
|
|
Index('ix_invites_active_code', 'code', unique=True, postgresql_where=sa_text('is_active = true')),
|
|
)
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
code = Column(String, unique=False, index=True, nullable=False, default=lambda: secrets.token_urlsafe(16))
|
|
group_id = Column(Integer, ForeignKey("groups.id", ondelete="CASCADE"), nullable=False)
|
|
created_by_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
expires_at = Column(DateTime(timezone=True), nullable=False, default=lambda: datetime.now(timezone.utc) + timedelta(days=7))
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
|
|
group = relationship("Group", back_populates="invites")
|
|
creator = relationship("User", back_populates="created_invites")
|
|
|
|
|
|
# === NEW: List Model ===
|
|
class List(Base):
|
|
__tablename__ = "lists"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, index=True, nullable=False)
|
|
description = Column(Text, nullable=True)
|
|
created_by_id = Column(Integer, ForeignKey("users.id"), nullable=False) # Who created this list
|
|
group_id = Column(Integer, ForeignKey("groups.id"), nullable=True) # Which group it belongs to (NULL if personal)
|
|
is_complete = Column(Boolean, default=False, nullable=False)
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)
|
|
|
|
# --- Relationships ---
|
|
creator = relationship("User", back_populates="created_lists") # Link to User.created_lists
|
|
group = relationship("Group", back_populates="lists") # Link to Group.lists
|
|
items = relationship("Item", back_populates="list", cascade="all, delete-orphan", order_by="Item.created_at") # Link to Item.list, cascade deletes
|
|
|
|
# === NEW: Item Model ===
|
|
class Item(Base):
|
|
__tablename__ = "items"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
list_id = Column(Integer, ForeignKey("lists.id", ondelete="CASCADE"), nullable=False) # Belongs to which list
|
|
name = Column(String, index=True, nullable=False)
|
|
quantity = Column(String, nullable=True) # Flexible quantity (e.g., "1", "2 lbs", "a bunch")
|
|
is_complete = Column(Boolean, default=False, nullable=False)
|
|
price = Column(Numeric(10, 2), nullable=True) # For cost splitting later (e.g., 12345678.99)
|
|
price_added_by_id = Column(Integer, ForeignKey("users.id"), nullable=True)
|
|
price_added_at = Column(DateTime(timezone=True), nullable=True)
|
|
added_by_id = Column(Integer, ForeignKey("users.id"), nullable=False) # Who added this item
|
|
completed_by_id = Column(Integer, ForeignKey("users.id"), nullable=True) # Who marked it complete
|
|
created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)
|
|
|
|
# --- Relationships ---
|
|
list = relationship("List", back_populates="items") # Link to List.items
|
|
added_by_user = relationship("User", foreign_keys=[added_by_id], back_populates="added_items") # Link to User.added_items
|
|
completed_by_user = relationship("User", foreign_keys=[completed_by_id], back_populates="completed_items") # Link to User.completed_items
|
|
|
|
# === NEW: ExpenseRecord Model ===
|
|
class ExpenseRecord(Base):
|
|
__tablename__ = "expense_records"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
list_id = Column(Integer, ForeignKey("lists.id"), index=True, nullable=False)
|
|
calculated_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
calculated_by_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
total_amount = Column(Numeric(10, 2), nullable=False)
|
|
participants = Column(ARRAY(Integer), nullable=False)
|
|
split_type = Column(SAEnum(SplitTypeEnum, name="splittypeenum", create_type=True), nullable=False, default=SplitTypeEnum.equal)
|
|
is_settled = Column(Boolean, default=False, nullable=False)
|
|
|
|
# Relationships
|
|
list = relationship("List")
|
|
calculator = relationship("User")
|
|
shares = relationship("ExpenseShare", back_populates="expense_record", cascade="all, delete-orphan")
|
|
settlement_activities = relationship("SettlementActivity", back_populates="expense_record", cascade="all, delete-orphan")
|
|
|
|
class ExpenseShare(Base):
|
|
__tablename__ = "expense_shares"
|
|
__table_args__ = (UniqueConstraint('expense_record_id', 'user_id', name='uq_expense_share_user'),)
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
expense_record_id = Column(Integer, ForeignKey("expense_records.id", ondelete="CASCADE"), index=True, nullable=False)
|
|
user_id = Column(Integer, ForeignKey("users.id"), index=True, nullable=False)
|
|
amount_owed = Column(Numeric(10, 2), nullable=False)
|
|
is_paid = Column(Boolean, default=False, nullable=False)
|
|
|
|
# Relationships
|
|
expense_record = relationship("ExpenseRecord", back_populates="shares")
|
|
user = relationship("User")
|
|
|
|
|
|
class SettlementActivity(Base):
|
|
__tablename__ = "settlement_activities"
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
expense_record_id = Column(Integer, ForeignKey("expense_records.id", ondelete="CASCADE"), index=True, nullable=False)
|
|
payer_user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
affected_user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
activity_type = Column(SAEnum(SettlementActivityTypeEnum, name="settlementactivitytypeenum", create_type=True), nullable=False)
|
|
timestamp = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
|
|
|
|
# Relationships
|
|
expense_record = relationship("ExpenseRecord", back_populates="settlement_activities")
|
|
payer = relationship("User", foreign_keys=[payer_user_id])
|
|
affected_user = relationship("User", foreign_keys=[affected_user_id]) |