app/seed_db.py

"""
Seed the database with all commission rules, constants, dropdown options,
bell ringer thresholds, and email routing rules from the original notebook.

Run once on first deploy:  python -m app.seed_db
Run again to reset:        python -m app.seed_db --reset
"""

import sys
from app.models import (
    Base, get_engine, get_session_factory,
    Organisation, User, CommissionAgreement, CommissionTier,
    FixedConstant, BellRingerThreshold, DropdownOption, EmailRoutingRule,
)
from passlib.context import CryptContext

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")


def seed(session, reset=False):
    """Populate all config tables."""

    if reset:
        for tbl in reversed(Base.metadata.sorted_tables):
            session.execute(tbl.delete())
        session.commit()
        print("🗑  All tables cleared.")

    # ── 1. COMMISSION AGREEMENTS ─────────────────────────────
    agreements_data = [
        {
            "name": "Standard Agreement", "p1_suffix": 1, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 30, "credit": 100, "agr": "Standard Agreement"},
            ]
        },
        {
            "name": "Standard Agreement PL", "p1_suffix": 1, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 16, "credit": 100, "agr": "Standard Agreement PL"},
            ]
        },
        {
            "name": "PhD Lead / PhD Agreement", "p1_suffix": 1, "p2_suffix": 2,
            "tiers": [
                {"role": "P1", "years": "Year 1",  "comm": 10, "credit": 100, "agr": "PhD Lead Agreement"},
                {"role": "P1", "years": "Year 2",  "comm": 15, "credit": 100, "agr": "PhD Lead Agreement"},
                {"role": "P1", "years": "Year 3+", "comm": 30, "credit": 100, "agr": "Standard Agreement",
                 "note": "Transitions to Standard Agreement"},
                {"role": "P2", "years": "Year 1",  "comm": 20, "credit": 0,   "agr": "PhD Agreement"},
                {"role": "P2", "years": "Year 2",  "comm": 15, "credit": 0,   "agr": "PhD Agreement",
                 "flag": "Commission 15% with Production Credit 0% — confirm intentional"},
                {"role": "P2", "years": "Year 3+", "comm": None, "credit": None, "agr": "Ended"},
            ]
        },
        {
            "name": "Partner Agreement", "p1_suffix": 1, "p2_suffix": 1,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 15, "credit": 50, "agr": "Partner Agreement"},
                {"role": "P2", "years": "All Years", "comm": 15, "credit": 50, "agr": "Partner Agreement"},
            ]
        },
        {
            "name": "Closer Agreement", "p1_suffix": 1, "p2_suffix": 2,
            "tiers": [
                {"role": "P1", "years": "Year 1",  "comm": 20, "credit": 100, "agr": "Referral Agreement"},
                {"role": "P1", "years": "Year 2+", "comm": 30, "credit": 100, "agr": "Standard Agreement",
                 "note": "Transitions to Standard Agreement"},
                {"role": "P2", "years": "Year 1",  "comm": 10, "credit": 0,   "agr": "Closer Agreement"},
                {"role": "P2", "years": "Year 2+", "comm": None, "credit": None, "agr": "Ended"},
            ]
        },
        {
            "name": "Referral / Origination Fee Agreement", "p1_suffix": 1, "p2_suffix": 2,
            "tiers": [
                {"role": "P1", "years": "Year 1 & Year 2", "comm": 20, "credit": 100, "agr": "Referral Agreement"},
                {"role": "P1", "years": "Year 3+",  "comm": 30, "credit": 100, "agr": "Standard Agreement",
                 "note": "Transitions to Standard Agreement"},
                {"role": "P2", "years": "Year 1 & Year 2", "comm": 10, "credit": 0,   "agr": "Origination Fee Agreement"},
                {"role": "P2", "years": "Year 3+",  "comm": None, "credit": None, "agr": "Ended"},
            ]
        },
        {
            "name": "Bond Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 30, "credit": 100, "agr": "Bond Agreement"},
            ]
        },
        {
            "name": "Acquisition Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 20, "credit": 100, "agr": "Acquisition Agreement"},
            ]
        },
        {
            "name": "Ceded Accounts Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 20, "credit": 100, "agr": "Ceded Accounts Agreement"},
            ]
        },
        {
            "name": "Ceded Accounts - CE as Producer", "p1_suffix": None, "p2_suffix": None,
            "ce_as_producer": True,
            "tiers": []
        },
        {
            "name": "Emerging Markets - House Standard", "p1_suffix": 1, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 100, "credit": 100, "agr": "House Standard Agreement",
                 "flag": "Commission 100% — every other standard is 30%. Verify with source."},
            ]
        },
        {
            "name": "Emerging Markets Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 0, "credit": 100, "agr": "Emerging Markets Agreement"},
            ]
        },
        {
            "name": "Life Origination Fee Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 20, "credit": 100, "agr": "Life Origination Fee Agreement"},
            ]
        },
        {
            "name": "Gebhardt Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "Year 1-3", "comm": 0, "credit": 100, "agr": "Gebhardt Agreement",
                 "flag": "Source had conflicting credit values (0% and 100%) — resolved to 100%."},
            ]
        },
        {
            "name": "Beacham Agreement", "p1_suffix": 2, "p2_suffix": None,
            "tiers": [
                {"role": "P1", "years": "All Years", "comm": 30, "credit": 100, "agr": "Beacham Agreement"},
            ]
        },
    ]

    for i, ad in enumerate(agreements_data):
        agr = CommissionAgreement(
            name=ad["name"],
            p1_suffix=ad["p1_suffix"],
            p2_suffix=ad["p2_suffix"],
            ce_as_producer=ad.get("ce_as_producer", False),
            display_order=i,
        )
        session.add(agr)
        session.flush()  # get the id

        for j, td in enumerate(ad["tiers"]):
            tier = CommissionTier(
                agreement_id=agr.id,
                producer_role=td["role"],
                years_label=td["years"],
                commission=td["comm"],
                credit=td["credit"],
                agreement_label=td["agr"],
                note=td.get("note"),
                flag=td.get("flag"),
                sort_order=j,
            )
            session.add(tier)

    print(f"✅ {len(agreements_data)} commission agreements seeded.")

    # ── 2. FIXED CONSTANTS ───────────────────────────────────
    constants = [
        ("CE_COMM",        2,   "Client Executive commission % (standard)"),
        ("CE_CREDIT",      0,   "Client Executive production credit % (standard)"),
        ("CE_SUFFIX",      1,   "Client Executive code suffix (standard)"),
        ("CE_PROD_COMM",   10,  "CE commission % when acting as producer"),
        ("CE_PROD_CREDIT", 100, "CE production credit % when acting as producer"),
        ("CE_PROD_SUFFIX", 2,   "CE code suffix when acting as producer"),
        ("CC_COMM",        5,   "Complex Claims commission %"),
        ("CC_CREDIT",      0,   "Complex Claims production credit %"),
    ]
    for key, val, desc in constants:
        session.add(FixedConstant(key=key, value=val, description=desc))
    print(f"✅ {len(constants)} fixed constants seeded.")

    # ── 3. BELL RINGER THRESHOLDS (department-only) ─────────
    bell_ringers = [
        ("—", "CL",  75000),
        ("—", "CON",  7500),
        ("—", "EB",  75000),
        ("—", "PL",   4500),
        ("—", "VAN", 10000),
    ]
    for seg, dept, thresh in bell_ringers:
        session.add(BellRingerThreshold(market_segment=seg, dept_code=dept, threshold=thresh))
    print(f"✅ {len(bell_ringers)} bell ringer thresholds seeded.")

    # ── 4. DROPDOWN OPTIONS ──────────────────────────────────
    associations = ["N/A", "AiN Group", "EDGE (USAV) Group", "GUAC", "PSA", "UAC"]
    for i, a in enumerate(associations):
        session.add(DropdownOption(category="association", label=a, value=a, display_order=i))

    departments = [
        ("BD - Bond", "BD"), ("CL - Commercial Lines", "CL"),
        ("CON - Connect", "CON"), ("EB - Employee Benefits", "EB"),
        ("PL - Personal Lines", "PL"), ("VAN - Vanguard", "VAN"), ("LIF - Life", "LIF"),
    ]
    for i, (lbl, val) in enumerate(departments):
        session.add(DropdownOption(category="department", label=lbl, value=val, display_order=i))

    employees = [
        "Sarah Johnson", "Mike Thompson", "Karen Lee", "James Wilson",
        "Beth Johnston", "Beth Potter", "Terrell Wallace", "Stephanie Pena",
        "Jason Goodwyn", "Steve Gebhardt", "Wade Beacham", "Mike Parsa",
        "Frank Herron", "David Chen", "Lisa Martinez", "Robert Brown",
        "Jennifer Davis", "Amanda Taylor", "Christopher Moore", "Michelle Anderson",
    ]
    for i, e in enumerate(employees):
        session.add(DropdownOption(category="employee", label=e, value=e, display_order=i))

    print(f"✅ Dropdown options seeded ({len(associations)} associations, "
          f"{len(departments)} departments, {len(employees)} employees).")

    # ── 5. EMAIL ROUTING RULES ───────────────────────────────
    email_rules = [
        ("always",      "accounting@snellingswalters.com",  "Accounting — always receives final PDF"),
        ("always",      "payroll@snellingswalters.com",     "Payroll — always receives final PDF"),
        ("submitter",   "__SUBMITTER__",                    "Form submitter — always receives final PDF"),
        ("bell_ringer", "spena@snellingswalters.com",       "Engagement — only when bell ringer triggers"),
        ("dept_cl",     "twallace@snellingswalters.com",    "T. Wallace — when dept is CL"),
        ("dept_bd",     "twallace@snellingswalters.com",    "T. Wallace — when dept is Bond"),
    ]
    for cond, email, desc in email_rules:
        session.add(EmailRoutingRule(condition=cond, email=email, description=desc))
    print(f"✅ {len(email_rules)} email routing rules seeded.")

    # ── 6. DEFAULT ORG & USERS ─────────────────────────────────
    org = Organisation(name="Snellings Walters")
    session.add(org)
    session.flush()

    import os
    default_pass = os.getenv("DEFAULT_USER_PASSWORD", "Check@123")

    # All users seeded on every fresh DB init so they survive container rebuilds.
    # Passwords can be changed via /change-password or admin reset.
    seed_users = [
        {"email": "ai@snellingswalters.com",        "name": "System Admin",     "role": "superadmin"},
        {"email": "pmisra@snellingswalters.com",     "name": "Pramod Misra",     "role": "superadmin"},
        {"email": "jgoodwyn@snellingswalters.com",   "name": "Jason Goodwyn",    "role": "admin"},
        {"email": "bjohnston@snellingswalters.com",  "name": "Beth Johnston",     "role": "admin"},
        {"email": "bpotter@snellingswalters.com",    "name": "Beth Potter",       "role": "admin"},
        {"email": "twallace@snellingswalters.com",   "name": "Terrell Wallace",   "role": "user"},
        {"email": "spena@snellingswalters.com",      "name": "Stephanie Pena",    "role": "user"},
    ]

    for u in seed_users:
        pw = os.getenv(f"USER_PASSWORD_{u['email'].split('@')[0].upper()}", default_pass)
        session.add(User(
            email=u["email"],
            hashed_password=pwd_context.hash(pw),
            full_name=u["name"],
            role=u["role"],
            org_id=org.id,
        ))
    print(f"✅ Organisation and {len(seed_users)} users seeded.")

    session.commit()
    print("\n🎉 Database seeding complete!")


if __name__ == "__main__":
    reset = "--reset" in sys.argv
    engine = get_engine("sqlite:///./data/app.db")
    Base.metadata.create_all(bind=engine)
    Session = get_session_factory(engine)
    with Session() as session:
        seed(session, reset=reset)