Recipe 2 — Queryables & CQL2 Reference

# Import required libraries
import json
import pandas as pd
import numpy as np
from pystac_client import Client
from datetime import datetime, timedelta
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import requests
from typing import List, Dict, Any, Optional
import warnings
import os
from getpass import getpass

warnings.filterwarnings('ignore')

# Display settings
pd.set_option('display.max_rows', 30)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', 60)
pd.set_option('display.width', None)

# Plotting style
sns.set_style('whitegrid')
plt.rcParams['figure.figsize'] = (14, 6)

print("All libraries imported successfully!")

# Connect to Montandon STAC API with Authentication
STAC_API_URL = "https://montandon-eoapi-stage.ifrc.org/stac"

# Get authentication token
# Option 1: From environment variable (recommended for automation)
api_token = os.getenv('MONTANDON_API_TOKEN')

# Option 2: Prompt user for token if not in environment
if api_token is None:
    print("=" * 70)
    print("AUTHENTICATION REQUIRED")
    print("=" * 70)
    print("\nThe Montandon STAC API requires a Bearer Token for authentication.")
    print("\nHow to get your token:")
    print("  1. Visit: https://goadmin-stage.ifrc.org/")
    print("  2. Log in with your IFRC credentials")
    print("  3. Generate an API token from your account settings")
    print("\nAlternatively, set the MONTANDON_API_TOKEN environment variable:")
    print("  PowerShell: $env:MONTANDON_API_TOKEN = 'your_token_here'")
    print("  Bash: export MONTANDON_API_TOKEN='your_token_here'")
    print("\n" + "=" * 70)
    
    # Prompt for token (hidden input)
    api_token = getpass("Enter your Montandon API Token: ")
    
    if not api_token or api_token.strip() == "":
        raise ValueError("API token is required to access the Montandon STAC API")

# Create authentication headers
auth_headers = {"Authorization": f"Bearer {api_token}"}

# Connect to STAC API with authentication
try:
    client = Client.open(STAC_API_URL, headers=auth_headers)
    print(f"\n✓ Connected to: {STAC_API_URL}")
    print(f"✓ API Title: {client.title}")
    print(f"✓ Authentication: Bearer Token (OpenID Connect)")
    print(f"✓ Auth Provider: https://goadmin-stage.ifrc.org/o/.well-known/openid-configuration")
except Exception as e:
    print(f"\n✗ Authentication failed: {e}")
    print("\nPlease check:")
    print("  1. Your token is valid and not expired")
    print("  2. You have the correct permissions")
    print("  3. The API endpoint is accessible")
    raise

# Helper function to handle search with fallback to direct HTTP request
def search_stac(filter_dict=None, collections=None, 
                max_items=100, filter_lang="cql2-json"):
    """
    Search the STAC API with automatic fallback to direct HTTP requests.
    This handles the /stac/ endpoint routing issue.
    """
    try:
        # Try PySTAC client search
        search = client.search(
            collections=collections,
            filter=filter_dict,
            filter_lang=filter_lang,
            max_items=max_items
        )
        return list(search.items())
    except Exception as e:
        # Fallback to direct HTTP POST request
        search_url = f"{STAC_API_URL}/search"
        search_payload = {
            "filter_lang": filter_lang,
            "limit": max_items
        }
        
        if filter_dict:
            search_payload["filter"] = filter_dict
        if collections:
            search_payload["collections"] = collections
        
        response = requests.post(search_url, json=search_payload, headers=auth_headers)
        
        if response.status_code == 200:
            search_results = response.json()
            # Convert features to simple objects
            items = []
            for feature in search_results.get('features', []):
                item = type('Item', (), {
                    'id': feature.get('id'),
                    'collection_id': feature.get('collection'),
                    'properties': feature.get('properties', {}),
                    'geometry': feature.get('geometry'),
                    'bbox': feature.get('bbox'),
                    'assets': feature.get('assets', {})
                })()
                items.append(item)
            return items
        else:
            print(f"Search failed: {response.status_code} - {response.text}")
            return []

print("✓ Search helper function defined")

# Fetch the queryables schema from the API (with authentication)
queryables_url = f"{STAC_API_URL}/queryables"
response = requests.get(queryables_url, headers=auth_headers)

if response.status_code == 200:
    queryables = response.json()
    print("Queryables Schema Retrieved Successfully!\n")
    print(f"Schema ID: {queryables.get('$id', 'N/A')}")
    print(f"Title: {queryables.get('title', 'N/A')}")
    print(f"Description: {queryables.get('description', 'N/A')}")
elif response.status_code == 401:
    print(f"Authentication failed (401 Unauthorized)")
    print("Your token may be invalid or expired. Please check your credentials.")
else:
    print(f"Failed to fetch queryables: {response.status_code}")
    print(f"Response: {response.text}")

# List all available queryable properties
properties = queryables.get('properties', {})

print(f"Total Queryable Properties: {len(properties)}\n")
print("="*80)

# Categorize queryables
core_queryables = []
monty_core = []
hazard_detail = []
impact_detail = []
other = []

for prop_name, prop_info in properties.items():
    if prop_name.startswith('monty:hazard_detail'):
        hazard_detail.append((prop_name, prop_info))
    elif prop_name.startswith('monty:impact_detail'):
        impact_detail.append((prop_name, prop_info))
    elif prop_name.startswith('monty:'):
        monty_core.append((prop_name, prop_info))
    elif prop_name in ['id', 'collection', 'datetime', 'geometry', 'roles']:
        core_queryables.append((prop_name, prop_info))
    else:
        other.append((prop_name, prop_info))

print("STAC Core Queryables:")
for name, info in core_queryables:
    print(f"  - {name}: {info.get('type', 'N/A')} | {info.get('description', 'N/A')[:50]}...")

print("\nMonty Core Queryables:")
for name, info in monty_core:
    print(f"  - {name}: {info.get('type', 'N/A')}")

print("\nHazard Detail Queryables:")
for name, info in hazard_detail:
    print(f"  - {name}: {info.get('type', 'N/A')}")

print("\nImpact Detail Queryables:")
for name, info in impact_detail:
    print(f"  - {name}: {info.get('type', 'N/A')}")

# Search for recent events across all event collections
# We filter by collection names instead of the 'roles' property

event_collections = ["gdacs-events", "glide-events", "emdat-events", "desinventar-events", "gfd-events"]

# Optional: Add time filter for recent events
recent_events_filter = {
    "op": "t_intersects",
    "args": [
        {"property": "datetime"},
        {"interval": ["2024-01-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
    ]
}

recent_events = search_stac(
    collections=event_collections,
    filter_dict=recent_events_filter,
    max_items=10
)

print(f"Found {len(recent_events)} recent events (limited to 10)\n")

for item in recent_events[:5]:

    print(f"ID: {item.id}")   

    print(f"  Collection: {item.collection_id}")    
    print(f"  Title: {item.properties.get('title', 'N/A')[:60]}...")
    print(f"  Roles: {item.properties.get('roles', [])}")

# Example 1: Find all events in Japan using a_contains
japan_filter = {
    "op": "a_contains",
    "args": [
        {"property": "monty:country_codes"},
        "JPN"
    ]
}

japan_events = search_stac(
    collections=["gdacs-events", "glide-events"],
    filter_dict=japan_filter,
    max_items=20
)

print(f"Events in Japan (JPN): {len(japan_events)}\n")

for item in japan_events[:5]:
    print(f"- {item.properties.get('title', 'N/A')[:70]}")
    print(f"  Hazard Codes: {item.properties.get('monty:hazard_codes', [])}")

# Example 2: Find all flood-related events using 
# a_overlaps with multiple hazard codes
# Flood codes across different classification systems:
# - FL (GDACS), MH0600 (UNDRR-ISC), nat-hyd-flo-flo (EMDAT)

flood_codes = ["FL", "MH0600", "nat-hyd-flo-flo"]

flood_filter = {
    "op": "a_overlaps",
    "args": [
        {"property": "monty:hazard_codes"},
        flood_codes
    ]
}

flood_events = search_stac(
    collections=["gdacs-events", "glide-events", "emdat-events"],
    filter_dict=flood_filter,
    max_items=30
)
print(f"Flood-related events found: {len(flood_events)}\n")

# Count by collection
collections_count = {}
for item in flood_events:
    coll = item.collection_id
    collections_count[coll] = collections_count.get(coll, 0) + 1

print("Events by Collection:")
for coll, count in sorted(collections_count.items()):
    print(f"  {coll}: {count}")

# Find hazards with notable severity values
# Note: severity_value means different things for different hazard types:
# - Earthquakes: Magnitude (e.g., 5.0, 6.5, 7.2)
# - Cyclones: Wind speed (e.g., 150 km/h, 200 km/h)
# - Floods: Water level or discharge rate
# We'll fetch hazards by specifying hazard collections

hazard_collections = ["gdacs-hazards", "emdat-hazards", "gfd-hazards", "usgs-hazards"]

hazards_filter = {
    "op": ">",
    "args": [{"property": "monty:hazard_detail.severity_value"}, 5]
}

all_hazards = search_stac(
    collections=hazard_collections,
    filter_dict=hazards_filter,
    max_items=50
)
print(f"Total hazards with severity values: {len(all_hazards)}\n")

# Categorize hazards by cluster/type
hazard_data = []
for item in all_hazards:
    hazard_detail = item.properties.get('monty:hazard_detail', {})
    cluster = hazard_detail.get('cluster', 'N/A')
    severity = hazard_detail.get('severity_value', 'N/A')
    unit = hazard_detail.get('severity_unit', 'N/A')
    
    hazard_data.append({
        'ID': item.id[:30],
        'Collection': item.collection_id,
        'Title': item.properties.get('title', 'N/A')[:40],
        'Hazard_Type': cluster,
        'Severity': severity,
        'Unit': unit
    })

if hazard_data:
    df_hazards = pd.DataFrame(hazard_data)
    
    # Display summary by hazard type
    print("="*70)
    print("HAZARDS BY TYPE:")
    print("="*70)
    
    # Group by hazard type
    for hazard_type in df_hazards['Hazard_Type'].unique():
        if hazard_type == 'N/A':
            continue
        type_df = df_hazards[df_hazards['Hazard_Type'] == hazard_type]
        print(f"\n{hazard_type.upper()} ({len(type_df)} records):")
        print(f"  Severity range: {type_df['Severity'].min():.2f} - {type_df['Severity'].max():.2f} {type_df['Unit'].iloc[0]}")
        print(f"  Mean severity: {type_df['Severity'].mean():.2f} {type_df['Unit'].iloc[0]}")
    
    print("\n" + "="*70)
    print("SAMPLE HAZARDS:")
    print("="*70)
    display(df_hazards.head(15))

# Find impacts with deaths reported
# Filter by impact collections instead of roles property

impact_collections = ["desinventar-impacts", "gdacs-impacts", "emdat-impacts", "idmc-gidd-impacts", "idmc-idu-impacts"]

death_impacts_filter = {
    "op": "and",
    "args": [
        {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "death"]},
        {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 0]}
    ]
}

death_impacts = search_stac(
    collections=impact_collections,
    filter_dict=death_impacts_filter,
    max_items=50
)
print(f"Impact records with deaths: {len(death_impacts)}\n")

# Compile impact data
impact_data = []
for item in death_impacts[:20]:
    impact_detail = item.properties.get('monty:impact_detail', {})
    impact_data.append({
        'ID': item.id[:25],
        'Collection': item.collection_id,
        'Country': ', '.join(item.properties.get('monty:country_codes', [])[:2]),
        'Category': impact_detail.get('category', 'N/A'),
        'Type': impact_detail.get('type', 'N/A'),
        'Value': impact_detail.get('value', 'N/A'),
        'Estimate': impact_detail.get('estimate_type', 'N/A')
    })

if impact_data:
    df_impacts = pd.DataFrame(impact_data)
    display(df_impacts)

# Find significant displacement impacts (> 1000 people)
# Filter by impact collections

impact_collections = ["desinventar-impacts", "gdacs-impacts", "emdat-impacts", "idmc-gidd-impacts", "idmc-idu-impacts"]

displacement_filter = {
    "op": "and",
    "args": [
        {
            "op": "or",
            "args": [
                {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "displaced_internal"]},
                {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "displaced_total"]},
                {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "displaced_external"]}
            ]
        },
        {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 1000]}
    ]
}

displacement_impacts = search_stac(
    collections=impact_collections,
    filter_dict=displacement_filter,
    max_items=50
)
print(f"Significant displacement impacts (>1000 people): {len(displacement_impacts)}\n")

# Analyze by country
country_displacements = {}
for item in displacement_impacts:
    countries = item.properties.get('monty:country_codes', [])
    value = item.properties.get('monty:impact_detail', {}).get('value', 0)
    for country in countries:
        if country not in country_displacements:
            country_displacements[country] = {'count': 0, 'total': 0}
        country_displacements[country]['count'] += 1
        country_displacements[country]['total'] += value if value else 0

# Sort by total displacement
sorted_countries = sorted(country_displacements.items(), 
                          key=lambda x: x[1]['total'], reverse=True)[:10]

print("Top 10 Countries by Displacement:\n")
for country, data in sorted_countries:
    print(f"  {country}: {data['total']:,.0f} displaced ({data['count']} records)")

# Complex Query: Find all flood impacts in South/Southeast Asia in 2024
# with significant affected population (>100 people)

asian_countries = ["IND", "BGD", "PAK", "NPL", "LKA", "MMR", "THA", "VNM", "PHL", "IDN", "MYS"]
flood_codes = ["FL", "MH0600", "nat-hyd-flo-flo"]
impact_collections = ["desinventar-impacts", "gdacs-impacts", "emdat-impacts", "idmc-gidd-impacts", "idmc-idu-impacts"]

asia_flood_impacts_filter = {
    "op": "and",
    "args": [
        # Flood-related hazard codes
        {
            "op": "a_overlaps",
            "args": [
                {"property": "monty:hazard_codes"},
                flood_codes
            ]
        },
        # In Asian countries
        {
            "op": "a_overlaps",
            "args": [
                {"property": "monty:country_codes"},
                asian_countries
            ]
        },
        # Category is people-related
        {"op": "=", "args": [{"property": "monty:impact_detail.category"}, "people"]},
        # Value > 100
        {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 100]},
        # In 2024
        {
            "op": "t_intersects",
            "args": [
                {"property": "datetime"},
                {"interval": ["2024-01-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
            ]
        }
    ]
}

asia_flood_impacts = search_stac(
    collections=impact_collections,
    filter_dict=asia_flood_impacts_filter,
    max_items=100
)
print(f"Flood impacts in South/Southeast Asia (2024): {len(asia_flood_impacts)}\n")

# Analyze results
if asia_flood_impacts:
    impact_summary = []
    for item in asia_flood_impacts:
        impact_detail = item.properties.get('monty:impact_detail', {})
        impact_summary.append({
            'Country': ', '.join(item.properties.get('monty:country_codes', [])),
            'Date': item.properties.get('datetime', 'N/A')[:10],
            'Type': impact_detail.get('type', 'N/A'),
            'Value': impact_detail.get('value', 0),
            'Collection': item.collection_id
        })
    
    df_summary = pd.DataFrame(impact_summary)
    
    # Summary by country and type
    print(df_summary.groupby(['Country', 'Type'])['Value'].agg(['count', 'sum']).reset_index())
    print(df_summary.groupby(['Country', 'Type'])['Value'].agg(['count', 'sum']).reset_index())

# Complex Query: Find correlated events, hazards, and impacts for a specific disaster
# Let's find a recent correlation ID first

event_collections = ["gdacs-events", "glide-events", "emdat-events", "desinventar-events", "gfd-events"]

recent_events_filter = {
    "op": "t_intersects",
    "args": [
        {"property": "datetime"},
        {"interval": ["2024-10-01T00:00:00Z", "2024-11-30T23:59:59Z"]}
    ]
}

recent_events = search_stac(
    collections=event_collections,
    filter_dict=recent_events_filter,
    max_items=10
)
print(f"Recent reference events (Oct-Nov 2024): {len(recent_events)}\n")

# Select one correlation ID to explore
if recent_events:
    sample_event = recent_events[0]
    corr_id = sample_event.properties.get('monty:corr_id')
    print(f"Selected Event: {sample_event.properties.get('title', 'N/A')}")
    print(f"Correlation ID: {corr_id}")
    print(f"Countries: {sample_event.properties.get('monty:country_codes', [])}")
    print(f"Hazard Codes: {sample_event.properties.get('monty:hazard_codes', [])}")

# Now find ALL related items using the correlation ID
if recent_events and corr_id:
    corr_filter = {
        "op": "=",
        "args": [
            {"property": "monty:corr_id"},
            corr_id
        ]
    }
    
    correlated_items = search_stac(
        filter_dict=corr_filter,
        max_items=100
    )
    print(f"\nAll items with correlation ID '{corr_id}': {len(correlated_items)}\n")
    
    # Categorize by roles
    events = []
    hazards = []
    impacts = []
    
    for item in correlated_items:
        roles = item.properties.get('roles', [])
        if 'event' in roles:
            events.append(item)
        elif 'hazard' in roles:
            hazards.append(item)
        elif 'impact' in roles:
            impacts.append(item)
    
    print(f"Events: {len(events)}")
    print(f"Hazards: {len(hazards)}")
    print(f"Impacts: {len(impacts)}")
    
    # Display impact summary
    if impacts:
        print("\n" + "="*60)
        print("IMPACT SUMMARY:")
        print("="*60)
        for impact in impacts[:10]:
            detail = impact.properties.get('monty:impact_detail', {})
            print(f"\n  {impact.collection_id}:")
            print(f"    Category: {detail.get('category', 'N/A')}")
            print(f"    Type: {detail.get('type', 'N/A')}")
            print(f"    Value: {detail.get('value', 'N/A')} {detail.get('unit', '')}")

# Find all cyclone events in the last 6 months
cyclone_codes = ["TC", "MH0306", "nat-met-sto-tro"]
event_collections = ["gdacs-events", "glide-events", "emdat-events", "gfd-events"]

end_date = datetime.now()
start_date = end_date - timedelta(days=180)

cyclone_filter = {
    "op": "and",
    "args": [
        {
            "op": "a_overlaps",
            "args": [
                {"property": "monty:hazard_codes"},
                cyclone_codes
            ]
        },
        {
            "op": "t_intersects",
            "args": [
                {"property": "datetime"},
                {
                    "interval": [
                        start_date.strftime("%Y-%m-%dT00:00:00Z"),
                        end_date.strftime("%Y-%m-%dT23:59:59Z")
                    ]
                }
            ]
        }
    ]
}

cyclones = search_stac(
    collections=event_collections,
    filter_dict=cyclone_filter,
    max_items=50
)
print(f"Cyclone events in last 6 months: {len(cyclones)}\n")

# Display cyclone data
cyclone_data = []
for item in cyclones:
    cyclone_data.append({
        'Date': item.properties.get('datetime', 'N/A')[:10],
        'Title': item.properties.get('title', 'N/A')[:50],
        'Countries': ', '.join(item.properties.get('monty:country_codes', [])[:3]),
        'Collection': item.collection_id
    })

if cyclone_data:
    df_cyclones = pd.DataFrame(cyclone_data)
    df_cyclones = df_cyclones.sort_values('Date', ascending=False)
    display(df_cyclones)

# Analysis 1: Deaths by Hazard Type (Recent Records)
# Query deaths from major hazard types in 2024

impact_collections = ["desinventar-impacts", "gdacs-impacts", "emdat-impacts"]

# Define major hazard types with their codes
hazard_types = {
    "Flood": ["FL", "MH0600", "nat-hyd-flo-flo"],
    "Earthquake": ["EQ", "GH0101", "nat-geo-ear-gro"],
    "Cyclone": ["TC", "MH0306", "nat-met-sto-tro"],
    "Drought": ["DR", "MH0401", "nat-cli-dro-dro"]
}

death_by_hazard = {}

for hazard_name, codes in hazard_types.items():
    death_filter = {
        "op": "and",
        "args": [
            {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "death"]},
            {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 0]},
            {"op": "a_overlaps", "args": [{"property": "monty:hazard_codes"}, codes]},
            {
                "op": "t_intersects",
                "args": [
                    {"property": "datetime"},
                    {"interval": ["2024-01-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
                ]
            }
        ]
    }
    
    results = search_stac(
        collections=impact_collections,
        filter_dict=death_filter,
        max_items=50
    )
    
    total_deaths = sum(
        item.properties.get('monty:impact_detail', {}).get('value', 0) or 0 
        for item in results
    )
    death_by_hazard[hazard_name] = {
        'records': len(results),
        'total_deaths': total_deaths
    }
    print(f"{hazard_name}: {len(results)} records, {total_deaths:,.0f} deaths reported")

print(f"\n✓ Deaths by hazard type analysis complete")

# Visualize Deaths by Hazard Type
if death_by_hazard:
    hazards = list(death_by_hazard.keys())
    deaths = [death_by_hazard[h]['total_deaths'] for h in hazards]
    records = [death_by_hazard[h]['records'] for h in hazards]
    
    fig = go.Figure()
    fig.add_trace(go.Bar(
        x=hazards, 
        y=deaths,
        text=[f"{d:,.0f}" for d in deaths],
        textposition='outside',
        marker_color=['#3498db', '#e74c3c', '#9b59b6', '#f39c12']
    ))
    
    fig.update_layout(
        title="Deaths Reported by Hazard Type (2024)",
        xaxis_title="Hazard Type",
        yaxis_title="Total Deaths Reported",
        height=550,
        showlegend=False
    )
    fig.show()

# Analysis 2: Displacement by Country (Top 10)
# Focus on internally displaced persons (IDPs) - a key humanitarian metric

displacement_filter = {
    "op": "and",
    "args": [
        {
            "op": "or",
            "args": [
                {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "displaced_internal"]},
                {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "displaced_total"]}
            ]
        },
        {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 100]},
        {
            "op": "t_intersects",
            "args": [
                {"property": "datetime"},
                {"interval": ["2024-01-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
            ]
        }
    ]
}

displacement_results = search_stac(
    collections=["desinventar-impacts", "gdacs-impacts", "emdat-impacts", "idmc-gidd-impacts"],
    filter_dict=displacement_filter,
    max_items=100
)

print(f"Found {len(displacement_results)} displacement records\n")

# Aggregate by country
country_displacement = {}
for item in displacement_results:
    countries = item.properties.get('monty:country_codes', [])
    value = item.properties.get('monty:impact_detail', {}).get('value', 0) or 0
    for country in countries[:1]:  # Primary country only
        if country not in country_displacement:
            country_displacement[country] = 0
        country_displacement[country] += value

# Sort and get top 10
top_countries = sorted(country_displacement.items(), key=lambda x: x[1], reverse=True)[:10]

print("Top 10 Countries by Displacement (2024):")
for country, displaced in top_countries:
    print(f"  {country}: {displaced:,.0f} people displaced")

# Visualize Displacement by Country
if top_countries:
    countries = [c[0] for c in top_countries]
    displaced = [c[1] for c in top_countries]
    
    fig = go.Figure()
    fig.add_trace(go.Bar(
        x=countries,
        y=displaced,
        text=[f"{d:,.0f}" for d in displaced],
        textposition='outside',
        marker_color='#27ae60'
    ))
    
    fig.update_layout(
        title="Top 10 Countries by Displacement (2024)",
        xaxis_title="Country (ISO3)",
        yaxis_title="People Displaced",
        height=550
    )
    fig.show()

# Analysis 3: Recent High-Impact Flood Events with Deaths
# Focused query combining hazard type + impact type + recent time

flood_death_filter = {
    "op": "and",
    "args": [
        {"op": "a_overlaps", "args": [{"property": "monty:hazard_codes"}, ["FL", "MH0600", "nat-hyd-flo-flo"]]},
        {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "death"]},
        {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 10]},
        {
            "op": "t_intersects",
            "args": [
                {"property": "datetime"},
                {"interval": ["2024-06-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
            ]
        }
    ]
}

flood_deaths = search_stac(
    collections=["desinventar-impacts", "gdacs-impacts", "emdat-impacts"],
    filter_dict=flood_death_filter,
    max_items=20
)

print(f"Recent Flood Events with Significant Deaths (>10):\n")
print(f"{'Country':<10} {'Date':<12} {'Deaths':>10} {'Collection':<20}")
print("-" * 55)

flood_death_data = []
for item in flood_deaths[:15]:
    detail = item.properties.get('monty:impact_detail', {})
    country = ', '.join(item.properties.get('monty:country_codes', [])[:1]) or 'N/A'
    date = item.properties.get('datetime', 'N/A')[:10]
    deaths = detail.get('value', 0) or 0
    collection = item.collection_id
    
    print(f"{country:<10} {date:<12} {deaths:>10,.0f} {collection:<20}")
    flood_death_data.append({'Country': country, 'Date': date, 'Deaths': deaths})

# Analysis 4: Earthquake Impact Comparison (Deaths vs Injured)
# Compare death and injury impacts from earthquakes

earthquake_codes = ["EQ", "GH0101", "nat-geo-ear-gro"]
earthquake_impacts = {}

for impact_type in ["death", "injured"]:
    eq_filter = {
        "op": "and",
        "args": [
            {"op": "a_overlaps", "args": [{"property": "monty:hazard_codes"}, earthquake_codes]},
            {"op": "=", "args": [{"property": "monty:impact_detail.type"}, impact_type]},
            {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 0]},
            {
                "op": "t_intersects",
                "args": [
                    {"property": "datetime"},
                    {"interval": ["2024-01-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
                ]
            }
        ]
    }
    
    results = search_stac(
        collections=["desinventar-impacts", "gdacs-impacts", "emdat-impacts"],
        filter_dict=eq_filter,
        max_items=50
    )
    
    total = sum(item.properties.get('monty:impact_detail', {}).get('value', 0) or 0 for item in results)
    earthquake_impacts[impact_type] = {'records': len(results), 'total': total}
    print(f"Earthquake {impact_type}: {len(results)} records, {total:,.0f} people")

# Simple bar comparison
if earthquake_impacts:
    fig = go.Figure()
    fig.add_trace(go.Bar(
        x=['Deaths', 'Injured'],
        y=[earthquake_impacts.get('death', {}).get('total', 0), 
           earthquake_impacts.get('injured', {}).get('total', 0)],
        marker_color=['#e74c3c', '#f39c12'],
        text=[f"{earthquake_impacts.get('death', {}).get('total', 0):,.0f}",
              f"{earthquake_impacts.get('injured', {}).get('total', 0):,.0f}"],
        textposition='outside'
    ))
    
    fig.update_layout(
        title="Earthquake Impacts: Deaths vs Injured (2024)",
        yaxis_title="Number of People",
        height=550,
        margin=dict(t=50, b=50, l=50, r=50)
    )
    fig.show()

# Analysis 5: Recent Cyclone Events with Affected Population
# Query affected_total for tropical cyclones

cyclone_codes = ["TC", "MH0306", "nat-met-sto-tro"]

cyclone_affected_filter = {
    "op": "and",
    "args": [
        {"op": "a_overlaps", "args": [{"property": "monty:hazard_codes"}, cyclone_codes]},
        {"op": "=", "args": [{"property": "monty:impact_detail.type"}, "affected_total"]},
        {"op": ">", "args": [{"property": "monty:impact_detail.value"}, 1000]},
        {
            "op": "t_intersects",
            "args": [
                {"property": "datetime"},
                {"interval": ["2024-01-01T00:00:00Z", "2024-12-31T23:59:59Z"]}
            ]
        }
    ]
}

cyclone_affected = search_stac(
    collections=["desinventar-impacts", "gdacs-impacts", "emdat-impacts"],
    filter_dict=cyclone_affected_filter,
    max_items=30
)

print(f"Cyclone Events with Significant Affected Population (>1000):\n")

cyclone_data = []
for item in cyclone_affected[:10]:
    detail = item.properties.get('monty:impact_detail', {})
    country = ', '.join(item.properties.get('monty:country_codes', [])[:1]) or 'N/A'
    date = item.properties.get('datetime', 'N/A')[:10]
    affected = detail.get('value', 0) or 0
    
    cyclone_data.append({
        'Country': country,
        'Date': date,
        'Affected': affected,
        'Collection': item.collection_id
    })

if cyclone_data:
    df_cyclone = pd.DataFrame(cyclone_data)
    df_cyclone = df_cyclone.sort_values('Affected', ascending=False)
    display(df_cyclone)