oimwiodev
d1160673a7
Self-hosted P&L tracking app with component-level pricing. Offers: Atlas/Atlas+/Rif/Rif+ with granular cost breakdown. API + MCP + multi-user auth.
97 lines
3.3 KiB
Python
97 lines
3.3 KiB
Python
"""SQLAlchemy models: User, Service, ServiceComponent, Transaction."""
|
|
|
|
from datetime import datetime, timezone
|
|
|
|
from sqlalchemy import (
|
|
Column,
|
|
Integer,
|
|
String,
|
|
Float,
|
|
Boolean,
|
|
DateTime,
|
|
ForeignKey,
|
|
)
|
|
from sqlalchemy.orm import relationship
|
|
|
|
from database import Base
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
username = Column(String, unique=True, index=True, nullable=False)
|
|
password_hash = Column(String, nullable=False)
|
|
display_name = Column(String, nullable=False)
|
|
role = Column(String, nullable=False, default="viewer") # admin | viewer
|
|
|
|
transactions = relationship("Transaction", back_populates="creator")
|
|
|
|
|
|
class Service(Base):
|
|
__tablename__ = "services"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String, unique=True, nullable=False)
|
|
description = Column(String, default="")
|
|
sell_price = Column(Float, nullable=False) # MAD/month — auto-computed from components
|
|
cost_price = Column(Float, nullable=False) # MAD/month — auto-computed from components
|
|
active = Column(Boolean, default=True)
|
|
|
|
transactions = relationship("Transaction", back_populates="service")
|
|
components = relationship(
|
|
"ServiceComponent",
|
|
back_populates="service",
|
|
cascade="all, delete-orphan",
|
|
order_by="ServiceComponent.id",
|
|
)
|
|
|
|
def recompute_prices(self):
|
|
"""Update sell_price and cost_price from component totals."""
|
|
self.sell_price = sum(
|
|
(c.unit_sell * c.quantity) for c in self.components
|
|
) if self.components else 0.0
|
|
self.cost_price = sum(
|
|
(c.unit_cost * c.quantity) for c in self.components
|
|
) if self.components else 0.0
|
|
|
|
|
|
class ServiceComponent(Base):
|
|
"""A cost/sell component of a service offer (e.g. RAM, HDD, Transport)."""
|
|
__tablename__ = "service_components"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
service_id = Column(Integer, ForeignKey("services.id"), nullable=False)
|
|
name = Column(String, nullable=False)
|
|
unit_cost = Column(Float, nullable=False, default=0.0) # what we pay / unit
|
|
unit_sell = Column(Float, nullable=False, default=0.0) # what we charge / unit
|
|
quantity = Column(Integer, nullable=False, default=1)
|
|
notes = Column(String, default="")
|
|
|
|
service = relationship("Service", back_populates="components")
|
|
|
|
|
|
class Transaction(Base):
|
|
__tablename__ = "transactions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
service_id = Column(Integer, ForeignKey("services.id"), nullable=False)
|
|
quantity = Column(Integer, nullable=False, default=1)
|
|
month = Column(String, nullable=False) # YYYY-MM format
|
|
notes = Column(String, default="")
|
|
created_by = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
|
|
|
|
service = relationship("Service", back_populates="transactions")
|
|
creator = relationship("User", back_populates="transactions")
|
|
|
|
@property
|
|
def revenue(self) -> float:
|
|
"""Calculated: quantity * service.sell_price"""
|
|
return self.quantity * self.service.sell_price
|
|
|
|
@property
|
|
def cost(self) -> float:
|
|
"""Calculated: quantity * service.cost_price"""
|
|
return self.quantity * self.service.cost_price
|