# Load Google Sheets data
client_master = load_sheet(url, 'Client_Master')
products = load_sheet(url, 'Products')
holdings = load_sheet(url, 'Holdings')

# Validate required columns exist
required_columns = {
    'Client_Master': ['Client_ID', 'Client_Name', 'Total_Portfolio', 'Cash_Balance', 'Cash_Yield'],
    'Products': ['Client_ID', 'Product_Type', 'Current_Value', 'Surrender_End_Date', 'Cap_Rate'],
    'Holdings': ['Client_ID', 'Current_Value', 'Percent_of_Portfolio']
}

# Report any missing data

revenue = product['Current_Value'] * 0.05  # 5% commission

Client: {client_name}
Type: FIA Replacement Opportunity
Details: {product_name} FIA with ${current_value:,} approaching surrender end in {months} months. Current cap rate of {cap_rate} is below market average of 5.5-6.5%.
Revenue Estimate: ${revenue:,}
Action: Schedule review meeting to discuss current product performance and illustrate replacement options with higher crediting rates.

revenue = client['Cash_Balance'] * 0.01  # 1% AUM fee

Client: {client_name}
Type: Cash Repositioning Opportunity
Details: ${cash_balance:,} in cash earning {cash_yield}. Money market funds currently yielding 4.5-5.5%.
Revenue Estimate: ${revenue:,} annually
Action: Quick call to reposition to higher-yielding money market or short-term treasury fund.

# Assume 30% of concentrated position can be repositioned
reposition_amount = position['Current_Value'] * 0.30
# Alternative investment typically 5% upfront
revenue = reposition_amount * 0.05

Client: {client_name}
Type: Concentration Risk / Diversification
Details: {ticker} position of ${position_value:,} represents {percentage}% of portfolio. Significant single-stock concentration risk.
Revenue Estimate: ${revenue:,}
Action: Schedule portfolio review to discuss diversification into alternative investments or sector diversification.

# Collect all opportunities
all_opportunities = []
all_opportunities.extend(fia_opportunities)
all_opportunities.extend(cash_opportunities)
all_opportunities.extend(concentration_opportunities)

# Deduplicate: One opportunity per client (keep highest revenue)
client_best_opp = {}
for opp in all_opportunities:
    client_id = opp['client_id']
    if client_id not in client_best_opp:
        client_best_opp[client_id] = opp
    elif opp['revenue'] > client_best_opp[client_id]['revenue']:
        client_best_opp[client_id] = opp

# Sort by revenue (highest first)
sorted_opportunities = sorted(
    client_best_opp.values(),
    key=lambda x: x['revenue'],
    reverse=True
)

═══════════════════════════════════════════════════
  OPPORTUNITYIQ MVP SCAN RESULTS
═══════════════════════════════════════════════════

Generated: {current_date_time}
Clients Scanned: {total_clients}
Opportunities Found: {total_opportunities}
Total Revenue Potential: ${total_revenue:,}

───────────────────────────────────────────────────

OPPORTUNITY #1 - ${revenue:,}

Client: {client_name} ({client_id})
Type: {scenario_name}

Details:
{detailed_description}

Recommended Action:
{next_steps}

───────────────────────────────────────────────────

[Repeat for each opportunity]

───────────────────────────────────────────────────

SUMMARY BY TYPE:
• FIA Replacement: {fia_count} opportunities - ${fia_total:,}
• Cash Repositioning: {cash_count} opportunities - ${cash_total:,}
• Concentration/Diversification: {conc_count} opportunities - ${conc_total:,}

PRIORITY ACTIONS:
1. High Priority (>$5,000): {high_priority_count} opportunities
2. Medium Priority ($2,000-$5,000): {medium_priority_count} opportunities
3. Quick Wins (<$2,000): {quick_win_count} opportunities

NEXT STEPS:
□ Review each opportunity for accuracy
□ Prioritize top 3-5 for immediate outreach
□ Note any false positives to refine criteria
□ Decide: Expand to more scenarios or scale client base?

# Missing data
if 'Surrender_End_Date' not in products.columns:
    print("⚠️ Warning: Surrender_End_Date not found in Products sheet. Skipping FIA scenario.")

# Invalid dates
try:
    surrender_date = pd.to_datetime(product['Surrender_End_Date'])
except:
    print(f"⚠️ Invalid date format for client {client_id}, skipping this product")

# Empty dataframes
if holdings.empty:
    print("⚠️ No holdings data found. Skipping concentration scenario.")

# Division by zero
if total_portfolio > 0:
    percent = position_value / total_portfolio
else:
    percent = 0

OPPORTUNITY #1 - $24,350

Client: John Smith (C001)
Type: FIA Replacement Opportunity

Details:
Allianz 222 FIA with $487,000 approaching surrender end in 7 months. Current cap rate of 4.5% is significantly below current market rates of 5.8-6.5%. Client has held product for 6.5 years through low-rate environment.

Recommended Action:
1. Schedule 45-minute review meeting within next 2 weeks
2. Run side-by-side illustration: Current product vs. best available replacement
3. Review surrender schedule details to confirm no penalties
4. Prepare 1035 exchange paperwork if client approves
5. Expected timeline: 30-60 days to completion

Revenue Estimate: $24,350 (5% commission on $487,000)
Confidence: High

OPPORTUNITY #3 - $1,500

Client: Mary Johnson (C002)
Type: Cash Repositioning Opportunity

Details:
$150,000 in cash earning 0.5% in traditional savings account. Money market funds currently yielding 4.5-5.5%. Client is losing approximately $6,000 annually in opportunity cost. No upcoming liquidity needs identified.

Recommended Action:
1. Quick 15-minute call to client
2. Explain opportunity cost (earning $750/year vs. $7,500/year)
3. Recommend repositioning to money market fund
4. Execute trade same day if approved
5. No-brainer quick win

Revenue Estimate: $1,500 annually (1% AUM fee)
Confidence: High

COMMISSION_RATES = {
    'FIA_replacement': 0.05,      # Default: 5%
    'Life_insurance': 0.01,       # Default: 1% of face value
    'AUM_fee': 0.01,              # Default: 1%
    'Alternative_product': 0.05,  # Default: 5%
}

THRESHOLDS = {
    'cash_minimum': 50000,                    # Minimum cash balance
    'cash_yield_threshold': 3.0,              # Yield below which to flag
    'fia_cap_rate_threshold': 5.5,            # Cap rate below market
    'concentration_percentage': 20,            # Portfolio concentration %
    'concentration_minimum_portfolio': 500000, # Minimum portfolio for concentration
}

URGENCY_WEIGHTS = {
    'Immediate': 1.3,
    'Near-term': 1.2,
    'Time-sensitive': 1.3,
    'Strategic': 1.0,
}