# MCP Client Demo


<!-- WARNING: THIS FILE WAS AUTOGENERATED! DO NOT EDIT! -->

``` python
import json
import pandas as pd
from IPython.display import display, Markdown, HTML

from mcp_ski_resort.core import (
    extract_text, try_parse_json,
    parse_agent_response, parse_analyst_response, parse_search_response,
    result_set_to_dataframe,
)
from mcp_ski_resort.mcp_client import (
    MCPClient, MCPToolbox, mcp_tools_list, mcp_call, display_mcp_result,
)
```

## 1. Discover Available Tools

``` python
tools = mcp_tools_list()

rows = []
for t in tools:
    params = list(t.get('inputSchema', {}).get('properties', {}).keys())
    rows.append({
        'Tool': t['name'],
        'Params': ', '.join(params),
        'Description': t.get('description', '')[:80],
    })
display(HTML(f'<h3 style="color:#60a5fa;">{len(tools)} MCP Tools Available</h3>'))
display(pd.DataFrame(rows))
```

<h3 style="color:#60a5fa;">10 MCP Tools Available</h3>

<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
&#10;    .dataframe tbody tr th {
        vertical-align: top;
    }
&#10;    .dataframe thead th {
        text-align: right;
    }
</style>

<table class="dataframe" data-quarto-postprocess="true" data-border="1">
<thead>
<tr style="text-align: right;">
<th data-quarto-table-cell-role="th"></th>
<th data-quarto-table-cell-role="th">Tool</th>
<th data-quarto-table-cell-role="th">Params</th>
<th data-quarto-table-cell-role="th">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td data-quarto-table-cell-role="th">0</td>
<td>daily-summary-analyst</td>
<td>message</td>
<td>Executive daily summary analyst - ask natural ...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">1</td>
<td>revenue-analyst</td>
<td>message</td>
<td>Revenue analyst covering ticket sales, rental ...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">2</td>
<td>operations-analyst</td>
<td>message</td>
<td>Operations analyst for lift scans, wait times,...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">3</td>
<td>resort-executive-agent</td>
<td>text</td>
<td>Executive BI partner with access to all resort...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">4</td>
<td>ski-ops-assistant-agent</td>
<td>text</td>
<td>Operations-focused assistant for lift supervis...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">5</td>
<td>feedback-search</td>
<td>query, columns, filter, limit</td>
<td>Semantic search over guest feedback entries. S...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">6</td>
<td>incident-search</td>
<td>query, columns, filter, limit</td>
<td>Semantic search over safety incidents. Search ...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">7</td>
<td>execute-sql</td>
<td>sql</td>
<td>Execute read-only SQL queries against the AM_S...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">8</td>
<td>resort-kpi-summary</td>
<td>p_season</td>
<td>Returns a comprehensive JSON summary of resort...</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">9</td>
<td>weather-impact-report</td>
<td>p_start_date, p_end_date</td>
<td>Analyzes how weather conditions impact resort ...</td>
</tr>
</tbody>
</table>

</div>

## 2. Cortex Agent (`CORTEX_AGENT_RUN`)

Agents return structured responses with answer text, SQL queries, result
sets, and charts.

``` python
agent_resp = mcp_call("resort-executive-agent", {
    "text": "Give me a quick executive summary of the 2024-2025 season: revenue, visits, and satisfaction."
})

parsed = parse_agent_response(agent_resp)
if parsed.get("answer"):
    display(HTML('<h4 style="color:#60a5fa;">Executive Agent: Season Summary</h4>'))
    display(Markdown(parsed["answer"][:3000]))
    for i, sql in enumerate(parsed.get("sql_queries", [])):
        display(HTML(
            f'<details><summary style="cursor:pointer;font-weight:600;color:#94a3b8;">SQL {i+1}</summary>'
            f'<pre style="padding:12px;background:#0f172a;color:#e2e8f0;border:1px solid #334155;'
            f'border-radius:8px;font-size:13px;">{sql}</pre></details>'
        ))
    for i, rs in enumerate(parsed.get("result_sets", [])):
        df = result_set_to_dataframe(rs)
        if not df.empty:
            display(HTML(f'<p style="font-weight:600;color:#d1d5db;">Result {i+1} ({len(df)} rows)</p>'))
            display(df.head(10))
else:
    display_mcp_result(agent_resp, "Executive Agent")
```

<h4 style="color:#60a5fa;">Executive Agent: Season Summary</h4>

## 2024-2025 Season Executive Summary

**Season Performance (Nov 1, 2024 – Apr 30, 2025)**

### Revenue: $7.23M Total

- **Food & Beverage:** $3.09M (43% of total, 216,944 transactions)
- **Ticket Sales:** $2.62M (36% of total, 24,041 tickets sold)
- **Rentals:** $1.51M (21% of total, 27,770 transactions)

### Visitation: 103,597 Total Visits

- **Unique Visitors:** 7,803 guests
- **Average Visits per Guest:** 13.3 visits
- **Pass Holder Share:** 76.8% (79,556 visits)
- **Day Ticket Visits:** 24,041

### Guest Satisfaction

- **Net Promoter Score (NPS):** 7.2 out of 10
- **Average Rating:** 3.4 out of 5.0
- **Total Feedback Collected:** 1,553 responses

------------------------------------------------------------------------

**Key Takeaways:** - Strong pass holder loyalty driving repeat
visitation with nearly 77% of visits from passholders - F&B has emerged
as the largest revenue stream, representing significant ancillary
spending opportunity - Guest satisfaction scores are moderate,
suggesting opportunity for experience improvements to drive higher
ratings and NPS

## 3. Cortex Analyst (`CORTEX_ANALYST_TEXT_TO_SQL`)

Natural-language SQL generation over structured data — returns SQL +
result sets.

``` python
analyst_resp = mcp_call("daily-summary-analyst", {
    "message": "How many total visits and unique visitors in the 2024-2025 season?"
})

display(HTML('<h4 style="color:#60a5fa;">Analyst: Season Visitation</h4>'))
display_mcp_result(analyst_resp)
```

<h4 style="color:#60a5fa;">Analyst: Season Visitation</h4>

This is our interpretation of your question:

How many total visits and unique visitors in the 2024-2025 ski season?

<details><summary style="cursor:pointer;font-weight:600;color:#9ca3af;">SQL</summary><pre style="padding:12px;background:#1e293b;color:#e2e8f0;border-radius:8px;font-size:13px;">WITH __fact_pass_usage AS (
  SELECT
    customer_key,
    date_key,
    usage_key
  FROM AM_SKI_RESORT.MARTS.FACT_PASS_USAGE
), __dim_date AS (
  SELECT
    date_key,
    ski_season
  FROM AM_SKI_RESORT.MARTS.DIM_DATE
), __fact_pass_usage AS (
  SELECT
    customer_key,
    date_key,
    usage_key
  FROM __fact_pass_usage
), __dim_date AS (
  SELECT
    date_key,
    ski_season
  FROM __dim_date
)
SELECT
  d.ski_season,
  COUNT(pu.usage_key) AS total_visits,
  COUNT(DISTINCT pu.customer_key) AS unique_visitors
FROM __fact_pass_usage AS pu
LEFT OUTER JOIN __dim_date AS d
  ON pu.date_key = d.date_key
WHERE
  d.ski_season = &#x27;2024-2025&#x27;
GROUP BY
  d.ski_season
ORDER BY
  d.ski_season DESC NULLS LAST
 -- Generated by Cortex Analyst (request_id: ba506c71-07e0-4159-9794-0c973d14929c)
;</pre></details>

## 4. Cortex Search (`CORTEX_SEARCH_SERVICE_QUERY`)

Semantic search over unstructured text — feedback and incidents.

``` python
feedback_resp = mcp_call("feedback-search", {
    "query": "long lift lines and crowded conditions",
    "columns": ["feedback_text", "rating", "sentiment", "category"],
    "limit": 10,
})

feedback_df = parse_search_response(feedback_resp)
display(HTML(f'<h4 style="color:#60a5fa;">Feedback Search: "long lift lines" ({len(feedback_df)} results)</h4>'))
if not feedback_df.empty:
    display(feedback_df[["feedback_text", "rating", "sentiment", "category"]].head(10))
```

<h4 style="color:#60a5fa;">Feedback Search: "long lift lines" (10 results)</h4>

<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
&#10;    .dataframe tbody tr th {
        vertical-align: top;
    }
&#10;    .dataframe thead th {
        text-align: right;
    }
</style>

<table class="dataframe" data-quarto-postprocess="true" data-border="1">
<thead>
<tr style="text-align: right;">
<th data-quarto-table-cell-role="th"></th>
<th data-quarto-table-cell-role="th">feedback_text</th>
<th data-quarto-table-cell-role="th">rating</th>
<th data-quarto-table-cell-role="th">sentiment</th>
<th data-quarto-table-cell-role="th">category</th>
</tr>
</thead>
<tbody>
<tr>
<td data-quarto-table-cell-role="th">0</td>
<td>Great experience</td>
<td>5</td>
<td>positive</td>
<td>Snow_Conditions</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">1</td>
<td>Great experience</td>
<td>4</td>
<td>positive</td>
<td>Lessons</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">2</td>
<td>Great experience</td>
<td>5</td>
<td>positive</td>
<td>Parking</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">3</td>
<td>Great experience</td>
<td>4</td>
<td>positive</td>
<td>Lifts</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">4</td>
<td>Great experience</td>
<td>4</td>
<td>positive</td>
<td>Food_Service</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">5</td>
<td>Great experience</td>
<td>5</td>
<td>positive</td>
<td>Lifts</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">6</td>
<td>Great experience</td>
<td>4</td>
<td>positive</td>
<td>Lifts</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">7</td>
<td>Great experience</td>
<td>4</td>
<td>positive</td>
<td>Rentals</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">8</td>
<td>Great experience</td>
<td>4</td>
<td>positive</td>
<td>Food_Service</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">9</td>
<td>Great experience</td>
<td>5</td>
<td>positive</td>
<td>Facilities</td>
</tr>
</tbody>
</table>

</div>

## 5. Execute SQL (`SYSTEM_EXECUTE_SQL`)

Run ad-hoc SQL queries against the resort database.

``` python
sql_resp = mcp_call("execute-sql", {
    "sql": """
        SELECT
            l.lift_name,
            l.lift_type,
            COUNT(*) AS total_scans,
            ROUND(AVG(s.wait_time_minutes), 1) AS avg_wait
        FROM AM_SKI_RESORT.MARTS.FACT_LIFT_SCANS s
        JOIN AM_SKI_RESORT.MARTS.DIM_LIFT l ON s.lift_key = l.lift_key
        JOIN AM_SKI_RESORT.MARTS.DIM_DATE d ON s.date_key = d.date_key
        WHERE d.ski_season = '2024-2025'
        GROUP BY l.lift_name, l.lift_type
        ORDER BY avg_wait DESC
        LIMIT 10
    """
})

display(HTML('<h4 style="color:#60a5fa;">SQL: Top 10 Lifts by Wait Time (2024-2025)</h4>'))
display_mcp_result(sql_resp)
```

<h4 style="color:#60a5fa;">SQL: Top 10 Lifts by Wait Time (2024-2025)</h4>

<p style="color:#9ca3af;font-size:12px;">Query ID: 01c3c2a9-0208-8521-004d-de0710f4be8e</p>

<p style="font-weight:600;color:#d1d5db;">Result (10 rows)</p>

<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
&#10;    .dataframe tbody tr th {
        vertical-align: top;
    }
&#10;    .dataframe thead th {
        text-align: right;
    }
</style>

<table class="dataframe" data-quarto-postprocess="true" data-border="1">
<thead>
<tr style="text-align: right;">
<th data-quarto-table-cell-role="th"></th>
<th data-quarto-table-cell-role="th">LIFT_NAME</th>
<th data-quarto-table-cell-role="th">LIFT_TYPE</th>
<th data-quarto-table-cell-role="th">TOTAL_SCANS</th>
<th data-quarto-table-cell-role="th">AVG_WAIT</th>
</tr>
</thead>
<tbody>
<tr>
<td data-quarto-table-cell-role="th">0</td>
<td>Mid Mountain</td>
<td>Chair - 4 Pack</td>
<td>144314</td>
<td>4.3</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">1</td>
<td>Family Fun</td>
<td>Chair - 4 Pack</td>
<td>134416</td>
<td>4.1</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">2</td>
<td>Backcountry Access</td>
<td>Chair - 2 Pack</td>
<td>76932</td>
<td>3.6</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">3</td>
<td>Blue Sky</td>
<td>Chair - 4 Pack</td>
<td>115527</td>
<td>3.6</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">4</td>
<td>Cruiser</td>
<td>Chair - 6 Pack</td>
<td>134463</td>
<td>3.5</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">5</td>
<td>Terrain Park Express</td>
<td>Chair - 4 Pack</td>
<td>86626</td>
<td>3.4</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">6</td>
<td>Sunshine</td>
<td>Chair - 4 Pack</td>
<td>105470</td>
<td>3.4</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">7</td>
<td>South Ridge</td>
<td>Chair - 4 Pack</td>
<td>105050</td>
<td>3.4</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">8</td>
<td>Eagle Ridge</td>
<td>Chair - 6 Pack</td>
<td>124945</td>
<td>3.3</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">9</td>
<td>Learners Lift</td>
<td>Chair - 3 Pack</td>
<td>48021</td>
<td>3.2</td>
</tr>
</tbody>
</table>

</div>

## 6. KPI Summary (`GENERIC` UDF)

Call the `resort-kpi-summary` UDF and render a dashboard card layout.

``` python
kpi_resp = mcp_call("resort-kpi-summary", {"p_season": "2024-2025"})

kpi_data = try_parse_json(extract_text(kpi_resp))

if kpi_data:
    display(HTML('<h4 style="color:#60a5fa;">KPI Summary: 2024-2025 Season</h4>'))

    vis = kpi_data.get("visitation", {})
    rev = kpi_data.get("revenue", {})
    ops = kpi_data.get("operations", {})
    sat = kpi_data.get("satisfaction", {})
    safety = kpi_data.get("safety", {})

    cards = [
        ("Total Visits", f"{vis.get('total_visits', 0):,}", ""),
        ("Unique Visitors", f"{vis.get('unique_visitors', 0):,}", ""),
        ("Ticket Revenue", f"${rev.get('ticket_revenue', 0):,.0f}", f"{rev.get('ticket_count', 0):,} tickets"),
        ("Avg Wait", f"{ops.get('avg_wait_minutes', 0):.1f} min", f"Max: {ops.get('max_wait_minutes', 0)} min"),
        ("NPS Score", f"{sat.get('avg_nps', 0):.1f}", f"Rating: {sat.get('avg_rating', 0):.1f}/5"),
        ("Incidents", f"{safety.get('total_incidents', 0)}", f"Response: {safety.get('avg_patrol_response_min', 0):.0f} min"),
    ]

    card_html = '<div style="display:flex;flex-wrap:wrap;gap:12px;margin:12px 0;">'
    for title, value, subtitle in cards:
        card_html += (
            f'<div style="flex:1;min-width:150px;padding:16px;background:#1e293b;'
            f'border:1px solid #334155;border-radius:12px;text-align:center;">'
            f'<div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">{title}</div>'
            f'<div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">{value}</div>'
            f'<div style="font-size:11px;color:#64748b;">{subtitle}</div></div>'
        )
    card_html += '</div>'
    display(HTML(card_html))
else:
    display_mcp_result(kpi_resp, "KPI Summary")
```

<h4 style="color:#60a5fa;">KPI Summary: 2024-2025 Season</h4>

<div style="display:flex;flex-wrap:wrap;gap:12px;margin:12px 0;"><div style="flex:1;min-width:150px;padding:16px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">Total Visits</div><div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">103,597</div><div style="font-size:11px;color:#64748b;"></div></div><div style="flex:1;min-width:150px;padding:16px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">Unique Visitors</div><div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">7,803</div><div style="font-size:11px;color:#64748b;"></div></div><div style="flex:1;min-width:150px;padding:16px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">Ticket Revenue</div><div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">$2,619,109</div><div style="font-size:11px;color:#64748b;">24,041 tickets</div></div><div style="flex:1;min-width:150px;padding:16px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">Avg Wait</div><div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">3.4 min</div><div style="font-size:11px;color:#64748b;">Max: 28 min</div></div><div style="flex:1;min-width:150px;padding:16px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">NPS Score</div><div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">7.2</div><div style="font-size:11px;color:#64748b;">Rating: 3.4/5</div></div><div style="flex:1;min-width:150px;padding:16px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:12px;color:#94a3b8;text-transform:uppercase;">Incidents</div><div style="font-size:28px;font-weight:700;color:#38bdf8;margin:4px 0;">528</div><div style="font-size:11px;color:#64748b;">Response: 9 min</div></div></div>

## 7. Weather Impact (`GENERIC` UDF)

``` python
weather_resp = mcp_call("weather-impact-report", {
    "p_start_date": "2024-11-01",
    "p_end_date": "2025-04-30",
})

weather_data = try_parse_json(extract_text(weather_resp))

if weather_data:
    display(HTML('<h4 style="color:#60a5fa;">Weather Impact: 2024-2025 Season</h4>'))

    summary = weather_data.get("weather_summary", {})
    powder = weather_data.get("powder_day_impact", {})
    conditions = weather_data.get("visitation_by_condition", [])

    summary_html = (
        f'<div style="padding:12px;background:#0f2918;border:1px solid #166534;border-radius:8px;margin:8px 0;color:#4ade80;">'
        f'<b>Weather Summary:</b> {summary.get("total_days", 0)} days analyzed | '
        f'Avg Temp: {summary.get("avg_temperature", 0):.0f}\u00b0F | '
        f'Total Snowfall: {summary.get("total_snowfall_inches", 0):.0f}" | '
        f'Powder Days: {powder.get("powder_day_count", 0)}'
        f'</div>'
    )
    display(HTML(summary_html))

    if conditions:
        display(pd.DataFrame(conditions))
else:
    display_mcp_result(weather_resp, "Weather Impact")
```

<h4 style="color:#60a5fa;">Weather Impact: 2024-2025 Season</h4>

<div style="padding:12px;background:#0f2918;border:1px solid #166534;border-radius:8px;margin:8px 0;color:#4ade80;"><b>Weather Summary:</b> 181 days analyzed | Avg Temp: 0°F | Total Snowfall: 0" | Powder Days: 0</div>

<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
&#10;    .dataframe tbody tr th {
        vertical-align: top;
    }
&#10;    .dataframe thead th {
        text-align: right;
    }
</style>

<table class="dataframe" data-quarto-postprocess="true" data-border="1">
<thead>
<tr style="text-align: right;">
<th data-quarto-table-cell-role="th"></th>
<th data-quarto-table-cell-role="th">avg_daily_visits</th>
<th data-quarto-table-cell-role="th">avg_hours</th>
<th data-quarto-table-cell-role="th">snow_condition</th>
<th data-quarto-table-cell-role="th">visit_days</th>
</tr>
</thead>
<tbody>
<tr>
<td data-quarto-table-cell-role="th">0</td>
<td>777</td>
<td>6.8</td>
<td>Excellent</td>
<td>59</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">1</td>
<td>703</td>
<td>6.6</td>
<td>Good</td>
<td>62</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">2</td>
<td>236</td>
<td>6.3</td>
<td>Fair</td>
<td>60</td>
</tr>
</tbody>
</table>

</div>

## 8. Season Report Pipeline

Combine multiple MCP tools into a single executive report.

``` python
SEASON = "2024-2025"
report = {}

display(HTML(f'<h2 style="text-align:center;color:#7dd3fc;">Alpine Mountain Ski Resort \u2014 {SEASON} Season Report</h2><hr style="border-color:#334155;"/>'))

print(f"[1/5] Fetching KPIs via resort-kpi-summary...")
kpi = mcp_call("resort-kpi-summary", {"p_season": SEASON})
report["kpi"] = try_parse_json(extract_text(kpi))

print(f"[2/5] Revenue breakdown via revenue-analyst...")
rev = mcp_call("revenue-analyst", {
    "message": f"What is the total ticket revenue, rental revenue, and food & beverage revenue for the {SEASON} season?"
})
report["revenue"] = parse_analyst_response(rev)

print(f"[3/5] Operations via operations-analyst...")
ops = mcp_call("operations-analyst", {
    "message": f"What are the top 5 busiest lifts by total scans in the {SEASON} season?"
})
report["operations"] = parse_analyst_response(ops)

print(f"[4/5] Guest sentiment via feedback-search...")
neg = mcp_call("feedback-search", {
    "query": "worst experience terrible disappointing",
    "columns": ["feedback_text", "rating", "sentiment", "category"],
    "limit": 5,
})
report["negative_feedback"] = parse_search_response(neg)

print(f"[5/5] Season comparison via resort-executive-agent...")
comparison = mcp_call("resort-executive-agent", {
    "text": f"Compare the 2023-2024 season vs {SEASON}: total visits, revenue, NPS. Which season performed better?"
})
report["comparison"] = parse_agent_response(comparison)

print("\nAll data collected!")
```

<h2 style="text-align:center;color:#7dd3fc;">Alpine Mountain Ski Resort — 2024-2025 Season Report</h2><hr style="border-color:#334155;"/>

    [1/5] Fetching KPIs via resort-kpi-summary...
    [2/5] Revenue breakdown via revenue-analyst...
    [3/5] Operations via operations-analyst...
    [4/5] Guest sentiment via feedback-search...
    [5/5] Season comparison via resort-executive-agent...

    All data collected!

``` python
display(HTML(f'<h2 style="text-align:center;color:#7dd3fc;">Alpine Mountain \u2014 {SEASON} Executive Report</h2>'))

if report.get("kpi"):
    kpi = report["kpi"]
    vis = kpi.get("visitation", {})
    rev_kpi = kpi.get("revenue", {})
    ops_kpi = kpi.get("operations", {})
    sat = kpi.get("satisfaction", {})
    safety_kpi = kpi.get("safety", {})

    metrics = [
        ("Total Visits", f"{vis.get('total_visits', 0):,}"),
        ("Unique Visitors", f"{vis.get('unique_visitors', 0):,}"),
        ("Ticket Revenue", f"${rev_kpi.get('ticket_revenue', 0):,.0f}"),
        ("Avg Ticket Price", f"${rev_kpi.get('avg_ticket_price', 0):.2f}"),
        ("Total Lift Scans", f"{ops_kpi.get('total_lift_scans', 0):,}"),
        ("Avg Wait Time", f"{ops_kpi.get('avg_wait_minutes', 0):.1f} min"),
        ("NPS Score", f"{sat.get('avg_nps', 0):.1f}"),
        ("Avg Rating", f"{sat.get('avg_rating', 0):.2f}/5"),
        ("Safety Incidents", f"{safety_kpi.get('total_incidents', 0)}"),
        ("Patrol Response", f"{safety_kpi.get('avg_patrol_response_min', 0):.0f} min"),
    ]
    cards_html = '<div style="display:flex;flex-wrap:wrap;gap:10px;margin:16px 0;">'
    for title, value in metrics:
        cards_html += (
            f'<div style="flex:1;min-width:130px;padding:14px;background:#1e293b;'
            f'border:1px solid #334155;border-radius:12px;text-align:center;">'
            f'<div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">{title}</div>'
            f'<div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">{value}</div></div>'
        )
    cards_html += '</div>'
    display(HTML(cards_html))

for section, label in [("revenue", "Revenue Breakdown"), ("operations", "Top Lifts")]:
    data = report.get(section, {})
    if data.get("result_sets"):
        display(HTML(f'<h4 style="color:#60a5fa;">{label}</h4>'))
        for rs in data["result_sets"]:
            df = result_set_to_dataframe(rs)
            if not df.empty:
                display(df.head(10))
    elif data.get("text"):
        display(HTML(f'<h4 style="color:#60a5fa;">{label}</h4>'))
        display(Markdown(data["text"][:2000]))

if not report.get("negative_feedback", pd.DataFrame()).empty:
    display(HTML('<h4 style="color:#f87171;">Worst Guest Feedback</h4>'))
    display(report["negative_feedback"].head(5))

comp = report.get("comparison", {})
if comp.get("answer"):
    display(HTML('<h4 style="color:#60a5fa;">Season Comparison</h4>'))
    display(Markdown(comp["answer"][:3000]))
    for rs in comp.get("result_sets", []):
        df = result_set_to_dataframe(rs)
        if not df.empty:
            display(df.head(10))
```

<h2 style="text-align:center;color:#7dd3fc;">Alpine Mountain — 2024-2025 Executive Report</h2>

<div style="display:flex;flex-wrap:wrap;gap:10px;margin:16px 0;"><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Total Visits</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">103,597</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Unique Visitors</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">7,803</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Ticket Revenue</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">$2,619,109</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Avg Ticket Price</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">$108.94</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Total Lift Scans</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">1,623,967</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Avg Wait Time</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">3.4 min</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">NPS Score</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">7.2</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Avg Rating</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">3.43/5</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Safety Incidents</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">528</div></div><div style="flex:1;min-width:130px;padding:14px;background:#1e293b;border:1px solid #334155;border-radius:12px;text-align:center;"><div style="font-size:11px;color:#94a3b8;text-transform:uppercase;letter-spacing:0.5px;">Patrol Response</div><div style="font-size:24px;font-weight:700;color:#38bdf8;margin:4px 0;">9 min</div></div></div>

<h4 style="color:#f87171;">Worst Guest Feedback</h4>

<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
&#10;    .dataframe tbody tr th {
        vertical-align: top;
    }
&#10;    .dataframe thead th {
        text-align: right;
    }
</style>

<table class="dataframe" data-quarto-postprocess="true" data-border="1">
<thead>
<tr style="text-align: right;">
<th data-quarto-table-cell-role="th"></th>
<th data-quarto-table-cell-role="th">sentiment</th>
<th data-quarto-table-cell-role="th">@scores</th>
<th data-quarto-table-cell-role="th">rating</th>
<th data-quarto-table-cell-role="th">category</th>
<th data-quarto-table-cell-role="th">feedback_text</th>
</tr>
</thead>
<tbody>
<tr>
<td data-quarto-table-cell-role="th">0</td>
<td>negative</td>
<td>{'text_match': 1.5123662e-07, 'cosine_similari...</td>
<td>1</td>
<td>Rentals</td>
<td>Poor experience</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">1</td>
<td>negative</td>
<td>{'text_match': 1.5123662e-07, 'cosine_similari...</td>
<td>3</td>
<td>Rentals</td>
<td>Poor experience</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">2</td>
<td>negative</td>
<td>{'text_match': 1.5123662e-07, 'cosine_similari...</td>
<td>1</td>
<td>Lifts</td>
<td>Poor experience</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">3</td>
<td>negative</td>
<td>{'text_match': 1.5123662e-07, 'cosine_similari...</td>
<td>1</td>
<td>Food_Service</td>
<td>Poor experience</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">4</td>
<td>negative</td>
<td>{'text_match': 1.5123662e-07, 'cosine_similari...</td>
<td>1</td>
<td>Lifts</td>
<td>Poor experience</td>
</tr>
</tbody>
</table>

</div>

<h4 style="color:#60a5fa;">Season Comparison</h4>

## Season Performance Comparison: 2023-2024 vs. 2024-2025

The **2024-2025 season performed slightly better operationally**, with
modest gains in visits and revenue, though customer satisfaction saw a
marginal decline.

### Key Findings

**Visits:** The 2024-2025 season attracted **103,597 total visits**, up
896 visits (+0.87%) from the prior season’s 102,701. Both seasons had
comparable data completeness with 181-182 operating days.

**Revenue:** Total revenue reached **$7,227,690** in 2024-2025, an
increase of $50,331 (+0.70%) over 2023-2024’s $7,177,359. All three
revenue streams grew: - Food & Beverage: $3,094,287 (+$33,458, +1.09%) -
Tickets: $2,619,109 (+$6,222, +0.24%) - Rentals: $1,514,294 (+$10,651,
+0.71%)

**NPS (Customer Satisfaction):** Average NPS declined slightly to
**7.24** in 2024-2025 from 7.29 in 2023-2024 (-0.05 points, -0.69%),
despite receiving more feedback responses (1,553 vs. 1,530).

### Strategic Observations

- **Revenue per visit declined slightly**: Revenue grew at 0.70% while
  visits grew at 0.87%, indicating lower yield per guest
- **F&B drove revenue growth**: Food & beverage accounted for 66% of the
  revenue increase, suggesting strong ancillary spending
- **Satisfaction-growth disconnect**: The resort grew visits and revenue
  but at the expense of customer experience, signaling potential
  capacity or service quality concerns

**Bottom Line:** 2024-2025 edges out 2023-2024 on volume and revenue
metrics, but the NPS decline warrants attention to maintain long-term
guest loyalty and pricing power.

## 9. Using MCPToolbox and MCPClient Directly

`MCPToolbox` auto-resolves the default session. For full control,
construct `MCPClient` with an explicit URL and headers factory.

``` python
toolbox = MCPToolbox()
print(toolbox)
print(f"Tools: {toolbox.tool_names}")

result = toolbox.call("incident-search", {"query": "avalanche near-miss"})
display_mcp_result(result, label="Incident Search")

from mcp_ski_resort.core import default_session, get_headers
s = default_session()
client = MCPClient(
    url=f"{s.host}/api/v2/databases/{s.database}/schemas/{s.mcp_schema}/mcp-servers/{s.mcp_server_name}",
    headers_factory=get_headers,
)
raw = client.request("tools/list", {})
print(f"Raw JSON-RPC keys: {list(raw.keys())}")
```

    MCPToolbox(tools=?, url='https://trb65519.snowflakecomputing.com/api/v2/databases/AM_SKI_RESORT/schemas/MCP_SERVERS/mcp-servers/ski_resort_mcp')
    Tools: ['daily-summary-analyst', 'revenue-analyst', 'operations-analyst', 'resort-executive-agent', 'ski-ops-assistant-agent', 'feedback-search', 'incident-search', 'execute-sql', 'resort-kpi-summary', 'weather-impact-report']

<h4 style="color:#60a5fa;margin:12px 0 4px;">Incident Search</h4>

<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
&#10;    .dataframe tbody tr th {
        vertical-align: top;
    }
&#10;    .dataframe thead th {
        text-align: right;
    }
</style>

<table class="dataframe" data-quarto-postprocess="true" data-border="1">
<thead>
<tr style="text-align: right;">
<th data-quarto-table-cell-role="th"></th>
<th data-quarto-table-cell-role="th">@scores</th>
<th data-quarto-table-cell-role="th">DESCRIPTION</th>
</tr>
</thead>
<tbody>
<tr>
<td data-quarto-table-cell-role="th">0</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">1</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">2</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">3</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">4</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">5</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">6</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">7</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost Skier incident</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">8</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost_Skier incident on 2025-03-16</td>
</tr>
<tr>
<td data-quarto-table-cell-role="th">9</td>
<td>{'text_match': 0.0, 'cosine_similarity': 0.444...</td>
<td>Lost_Skier incident on 2025-02-10</td>
</tr>
</tbody>
</table>

</div>

    Raw JSON-RPC keys: ['jsonrpc', 'id', 'result']