app/models.py

"""
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)