Spaces:
Running
Running
File size: 10,154 Bytes
996fcf9 b2253b1 996fcf9 c5de9ee 996fcf9 c5de9ee 996fcf9 b2253b1 996fcf9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 | """
Database models for the Producer Intake application.
Two categories of tables:
1. AUTH tables — users, roles, organisations, sessions
2. CONFIG tables — commission rules, bell ringers, dropdowns, constants
(Admin-editable via the Admin Panel)
3. DATA tables — producers, clients (refreshed daily from BigQuery)
4. FORM tables — submitted forms, approvals
"""
from datetime import datetime, timezone
from sqlalchemy import (
Column, Integer, String, Float, Boolean, Text, DateTime,
ForeignKey, UniqueConstraint, JSON, create_engine
)
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
Base = declarative_base()
# =====================================================================
# 1. AUTH TABLES
# =====================================================================
class Organisation(Base):
__tablename__ = "organisations"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(200), unique=True, nullable=False)
is_active = Column(Boolean, default=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
users = relationship("User", back_populates="organisation")
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, autoincrement=True)
email = Column(String(255), unique=True, nullable=False, index=True)
hashed_password = Column(String(255), nullable=False)
full_name = Column(String(200), nullable=False)
role = Column(String(20), nullable=False, default="user") # user | admin | superadmin
org_id = Column(Integer, ForeignKey("organisations.id"), nullable=True)
is_active = Column(Boolean, default=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
reset_token = Column(String(255), nullable=True)
reset_token_exp = Column(DateTime, nullable=True)
organisation = relationship("Organisation", back_populates="users")
# =====================================================================
# 2. CONFIG TABLES (Admin-editable)
# =====================================================================
class CommissionAgreement(Base):
"""
Each row is one agreement (e.g. 'Standard Agreement').
The year-tier details live in CommissionTier.
"""
__tablename__ = "commission_agreements"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(200), unique=True, nullable=False)
p1_suffix = Column(Integer, nullable=True) # 1 or 2 or NULL
p2_suffix = Column(Integer, nullable=True) # 1 or 2 or NULL
ce_as_producer = Column(Boolean, default=False)
display_order = Column(Integer, default=0) # controls dropdown order
is_active = Column(Boolean, default=True)
tiers = relationship("CommissionTier", back_populates="agreement",
cascade="all, delete-orphan", order_by="CommissionTier.sort_order")
class CommissionTier(Base):
"""
Year-tier rows for each agreement.
producer_role is 'P1' or 'P2'.
"""
__tablename__ = "commission_tiers"
id = Column(Integer, primary_key=True, autoincrement=True)
agreement_id = Column(Integer, ForeignKey("commission_agreements.id"), nullable=False)
producer_role = Column(String(2), nullable=False) # 'P1' or 'P2'
years_label = Column(String(50), nullable=False) # 'Year 1', 'All Years', etc.
commission = Column(Float, nullable=True) # NULL = ended
credit = Column(Float, nullable=True) # NULL = ended
agreement_label = Column(String(200), nullable=False) # display name for this tier
note = Column(Text, nullable=True)
flag = Column(Text, nullable=True) # warning text
sort_order = Column(Integer, default=0)
agreement = relationship("CommissionAgreement", back_populates="tiers")
class FixedConstant(Base):
"""
Key-value store for fixed percentages that rarely change.
e.g. CE_COMM=2, CE_CREDIT=0, CC_COMM=5, CE_PROD_COMM=10, etc.
"""
__tablename__ = "fixed_constants"
id = Column(Integer, primary_key=True, autoincrement=True)
key = Column(String(50), unique=True, nullable=False)
value = Column(Float, nullable=False)
description = Column(String(200), nullable=True)
class BellRingerThreshold(Base):
"""Each row is one market_segment + dept_code combination."""
__tablename__ = "bell_ringer_thresholds"
id = Column(Integer, primary_key=True, autoincrement=True)
market_segment = Column(String(50), nullable=False) # 'Middle Market', 'Emerging Markets'
dept_code = Column(String(10), nullable=False) # 'CL', 'EB', 'PL'
threshold = Column(Float, nullable=False)
__table_args__ = (
UniqueConstraint("market_segment", "dept_code", name="uq_bell_seg_dept"),
)
class DropdownOption(Base):
"""
Generic table for admin-managed dropdown values.
category = 'association' | 'department' | 'market_segment' | 'employee'
"""
__tablename__ = "dropdown_options"
id = Column(Integer, primary_key=True, autoincrement=True)
category = Column(String(50), nullable=False, index=True)
label = Column(String(200), nullable=False) # display text
value = Column(String(100), nullable=False) # internal value
display_order = Column(Integer, default=0)
is_active = Column(Boolean, default=True)
__table_args__ = (
UniqueConstraint("category", "value", name="uq_dropdown_cat_val"),
)
class EmailRoutingRule(Base):
"""
Configurable email routing for completed forms.
condition: 'always' | 'bell_ringer' | 'dept_cl' | 'dept_bd' | 'submitter'
"""
__tablename__ = "email_routing_rules"
id = Column(Integer, primary_key=True, autoincrement=True)
condition = Column(String(50), nullable=False)
email = Column(String(255), nullable=False)
description = Column(String(200), nullable=True)
is_active = Column(Boolean, default=True)
# =====================================================================
# 3. DATA TABLES (Refreshed daily from BigQuery)
# =====================================================================
class Producer(Base):
"""Loaded from Team Mapping BigQuery table."""
__tablename__ = "producers"
id = Column(Integer, primary_key=True, autoincrement=True)
code = Column(String(20), nullable=False) # full code e.g. RENCH1
prefix = Column(String(20), nullable=False, index=True) # e.g. RENCH
suffix = Column(Integer, nullable=False) # 1 or 2
name = Column(String(200), nullable=False) # display name
is_active = Column(Boolean, default=True)
email = Column(String(255), nullable=True)
class Client(Base):
"""Loaded from ClientExtract BigQuery table."""
__tablename__ = "clients"
id = Column(Integer, primary_key=True, autoincrement=True)
lookup_code = Column(String(50), nullable=False, index=True)
name = Column(String(300), nullable=False)
is_active = Column(Boolean, default=True)
# =====================================================================
# 4. FORM TABLES (Submissions & Approvals — Phase 4)
# =====================================================================
class IntakeSubmission(Base):
"""A form submission — can be a draft or a submitted form."""
__tablename__ = "intake_submissions"
id = Column(Integer, primary_key=True, autoincrement=True)
submitted_by = Column(Integer, ForeignKey("users.id"), nullable=False)
submitted_at = Column(DateTime, nullable=True) # NULL for drafts
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc),
onupdate=lambda: datetime.now(timezone.utc))
status = Column(String(20), default="draft") # draft | pending_approval | approved | rejected
title = Column(String(300), nullable=True) # auto-generated label for dashboard
# Form data stored as JSON blob for flexibility
form_data = Column(JSON, nullable=False)
# Computed summary stored for the PDF
summary_data = Column(JSON, nullable=True)
bell_ringer_triggered = Column(Boolean, default=False)
dept_code = Column(String(10), nullable=True)
approvals = relationship("Approval", back_populates="submission",
cascade="all, delete-orphan")
submitter = relationship("User")
class Approval(Base):
"""Each person on the account must approve."""
__tablename__ = "approvals"
id = Column(Integer, primary_key=True, autoincrement=True)
submission_id = Column(Integer, ForeignKey("intake_submissions.id"), nullable=False)
approver_name = Column(String(200), nullable=False)
approver_email = Column(String(255), nullable=True)
producer_code = Column(String(20), nullable=True)
role_label = Column(String(50), nullable=False) # 'Producer 1', 'CE', etc.
status = Column(String(20), default="pending") # pending | approved | rejected
responded_at = Column(DateTime, nullable=True)
reminder_sent = Column(DateTime, nullable=True)
approval_token = Column(String(64), unique=True, nullable=True, index=True)
submission = relationship("IntakeSubmission", back_populates="approvals")
# =====================================================================
# DB ENGINE & SESSION FACTORY
# =====================================================================
def get_engine(db_url: str = "sqlite:///./data/app.db"):
return create_engine(db_url, connect_args={"check_same_thread": False})
def create_tables(engine):
Base.metadata.create_all(bind=engine)
def get_session_factory(engine):
return sessionmaker(autocommit=False, autoflush=False, bind=engine)
|