"""Reproducible, wholly synthetic portfolio for the public showcase."""
import calendar
import random
from datetime import date, timedelta

BROKERS = [
    "Summit Ridge Lending", "Atlas Home Finance", "Cedarpoint Mortgage",
    "Horizon Lending Partners", "Lighthouse Home Capital", "Evergreen Financial",
    "Nova Mortgage Group", "Westbridge Lending"
]
PROGRAMS = ["Conventional", "FHA", "VA", "Jumbo"]
CHANNELS = ["Wholesale", "Correspondent", "Retail"]
STAGES = ["Loan Open", "Underwriting", "Approved", "Clear to Close", "Docs Out"]


def shift_month(today, delta):
    month_index = today.year * 12 + today.month - 1 + delta
    year, month_zero = divmod(month_index, 12)
    month = month_zero + 1
    return date(year, month, min(today.day, calendar.monthrange(year, month)[1]))


def seed_demo_data(get_db):
    conn = get_db()
    if conn.execute("SELECT COUNT(*) FROM loans").fetchone()[0]:
        conn.close()
        return

    rng = random.Random(4821)
    today = date.today()
    loans = []

    def add_loan(number, status, registered, funded=None, locked=None):
        channel = rng.choices(CHANNELS, weights=[50, 31, 19])[0]
        program = rng.choices(PROGRAMS, weights=[49, 23, 17, 11])[0]
        broker = rng.choice(BROKERS)
        amount = rng.randrange(230000, 960000, 5000)
        uw = registered + timedelta(days=rng.randint(2, 8))
        approved = uw + timedelta(days=rng.randint(3, 9))
        gross = round(amount * rng.uniform(0.009, 0.023), 2) if funded else None
        loans.append((
            f"DEMO-{number:05d}", "Sample", f"Borrower {number:03d}", program,
            channel, status, rng.choice(["Purchase", "Refinance"]), "Primary Residence",
            amount, round(rng.uniform(5.65, 7.35), 3), rng.randint(62, 94),
            rng.randint(655, 795), broker, rng.choice(["Taylor", "Morgan", "Casey", "Jordan"]),
            rng.choice(["Avery", "Quinn", "Harper"]), rng.choice(["Reese", "Parker", "Rowan"]),
            registered.isoformat(), uw.isoformat() if (funded or status != "Loan Open") else None,
            approved.isoformat() if (funded or status in STAGES[2:]) else None,
            (approved + timedelta(days=2)).isoformat() if (funded or status in STAGES[3:]) else None,
            (approved + timedelta(days=3)).isoformat() if funded else None,
            (approved + timedelta(days=5)).isoformat() if funded else None,
            funded.isoformat() if funded else None,
            funded.isoformat() if funded else None,
            locked.isoformat() if locked else None,
            "Demo Investor", gross, round(gross * 0.72, 2) if gross is not None else None
        ))

    serial = 1
    # Closed production across 12 calendar months, including the current one.
    for months_ago in range(11, -1, -1):
        month = shift_month(today.replace(day=1), -months_ago)
        last_day = calendar.monthrange(month.year, month.month)[1]
        if months_ago == 0:
            last_day = today.day
        count = rng.randint(12, 21) if months_ago else max(5, today.day // 2)
        for _ in range(count):
            day = rng.randint(1, max(1, last_day))
            funded = month.replace(day=day)
            registered = funded - timedelta(days=rng.randint(17, 36))
            locked = registered + timedelta(days=rng.randint(1, 8))
            add_loan(serial, "Funded", registered, funded, locked)
            serial += 1

    # A realistic live pipeline with risk, aging and upcoming lock-expiry samples.
    for stage, count in zip(STAGES, [16, 18, 14, 11, 9]):
        for _ in range(count):
            age = rng.randint(2, 48)
            registered = today - timedelta(days=age)
            locked = None
            if rng.random() < 0.78:
                locked = today - timedelta(days=rng.randint(3, 48))
            add_loan(serial, stage, registered, locked=locked)
            serial += 1

    for index, name in enumerate(BROKERS, start=1):
        conn.execute(
            "INSERT OR IGNORE INTO broker_companies (name, nmls_id, city, state, status) VALUES (?, ?, ?, ?, ?)",
            (name, f"DEMO-{index:03d}", rng.choice(["Los Angeles", "Pasadena", "Irvine", "Ventura"]), "CA", "Active")
        )

    conn.executemany("""
        INSERT INTO loans (
            loan_number, borrower_last, borrower_first, loan_program,
            loan_channel, loan_status, loan_purpose, property_purpose,
            loan_amount, note_rate, ltv, credit_score, broker_company,
            account_executive, underwriter, processor, registered_date,
            underwriting_date, approved_date, clear_to_close_date,
            docs_out_date, docs_back_date, scheduled_funding, funded_date,
            rate_locked_date, investor_name, gross_profit, net_profit
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    """, loans)
    conn.commit()
    conn.close()
