-- Fantasy42 Analytics Dashboard Queries
-- For Grafana Cloud with D1 SQLite source

-- ==================================================
-- DASHBOARD PANEL 1: REVENUE OVERVIEW
-- ==================================================

-- Daily Revenue & Hold Percentage (Last 30 days)
-- Query A: Main revenue metrics
SELECT
  strftime('%Y-%m-%d', datetime(timestamp/1000, 'unixepoch')) as date,
  SUM(bet_amount) as total_handle,
  SUM(CASE WHEN graded_result = 'win' THEN bet_amount * (odds - 1) - bet_amount ELSE -bet_amount END) as net_win,
  ROUND(
    CASE
      WHEN SUM(bet_amount) > 0
      THEN (SUM(CASE WHEN graded_result = 'win' THEN bet_amount * (odds - 1) - bet_amount ELSE -bet_amount END) / SUM(bet_amount)) * 100
      ELSE 0
    END,
    2
  ) as hold_percentage
FROM live_bets
WHERE timestamp >= (strftime('%s', 'now', '-30 days') * 1000)
  AND agent_id = '${agent_id}'
  AND graded_result IS NOT NULL
GROUP BY strftime('%Y-%m-%d', datetime(timestamp/1000, 'unixepoch'))
ORDER BY date DESC;

-- ==================================================
-- DASHBOARD PANEL 2: TOP CUSTOMERS BY VOLUME
-- ==================================================

-- Query B: Customer volume leaderboard
SELECT
  customer_id,
  SUM(total_amount) as total_volume,
  SUM(total_bets) as bet_count,
  ROUND(SUM(total_amount) / SUM(total_bets), 2) as avg_bet_size,
  ROUND((SUM(win_amount) / NULLIF(SUM(total_amount), 0)) * 100, 2) as win_rate_pct,
  MAX(date) as last_bet_date
FROM customer_volume
WHERE agent_id = '${agent_id}'
  AND total_amount > 0
GROUP BY customer_id
ORDER BY total_volume DESC
LIMIT 10;

-- ==================================================
-- DASHBOARD PANEL 3: SPORT PERFORMANCE BREAKDOWN
-- ==================================================

-- Query C: Performance by sport type
SELECT
  sport_type as sport,
  COUNT(*) as total_bets,
  SUM(bet_amount) as total_handle,
  SUM(CASE WHEN graded_result = 'win' THEN bet_amount * (odds - 1) - bet_amount ELSE -bet_amount END) as net_pnl,
  ROUND(
    CASE
      WHEN SUM(bet_amount) > 0
      THEN (SUM(CASE WHEN graded_result = 'win' THEN bet_amount * (odds - 1) - bet_amount ELSE -bet_amount END) / SUM(bet_amount)) * 100
      ELSE 0
    END,
    2
  ) as hold_percentage,
  ROUND(AVG(bet_amount), 2) as avg_bet_size
FROM live_bets
WHERE timestamp >= (strftime('%s', 'now', '-30 days') * 1000)
  AND agent_id = '${agent_id}'
  AND graded_result IS NOT NULL
  AND sport_type != 'Unknown'
GROUP BY sport_type
ORDER BY total_handle DESC;

-- ==================================================
-- DASHBOARD PANEL 4: HOURLY VOLUME HEATMAP
-- ==================================================

-- Query D: Hourly betting patterns
SELECT
  strftime('%w', datetime(timestamp/1000, 'unixepoch')) as day_of_week,
  strftime('%H', datetime(timestamp/1000, 'unixepoch')) as hour,
  COUNT(*) as bet_count,
  SUM(bet_amount) as total_volume,
  ROUND(AVG(bet_amount), 2) as avg_bet
FROM live_bets
WHERE timestamp >= (strftime('%s', 'now', '-7 days') * 1000)
  AND agent_id = '${agent_id}'
GROUP BY strftime('%w', datetime(timestamp/1000, 'unixepoch')), strftime('%H', datetime(timestamp/1000, 'unixepoch'))
ORDER BY day_of_week, hour;

-- ==================================================
-- DASHBOARD PANEL 5: RISK EXPOSURE GAUGES
-- ==================================================

-- Query E: Risk metrics summary
SELECT
  COUNT(DISTINCT customer_id) as active_customers,
  SUM(CASE WHEN status = 'active' THEN bet_amount ELSE 0 END) as current_exposure,
  ROUND(AVG(
    CASE WHEN graded_result = 'win' THEN bet_amount * odds ELSE 0 END
  ), 2) as avg_potential_payout,
  MAX(bet_amount) as largest_single_bet,
  SUM(CASE WHEN status = 'active' THEN bet_amount ELSE 0 END) / COUNT(DISTINCT customer_id) as exposure_per_customer
FROM live_bets
WHERE agent_id = '${agent_id}'
  AND timestamp >= (strftime('%s', 'now', '-1 day') * 1000);

-- ==================================================
-- DASHBOARD PANEL 6: TREND ANALYSIS CHART
-- ==================================================

-- Query F: 7-day volume trend
SELECT
  strftime('%Y-%m-%d', datetime(timestamp/1000, 'unixepoch')) as date,
  COUNT(*) as bets_placed,
  SUM(bet_amount) as daily_volume,
  COUNT(DISTINCT customer_id) as unique_customers,
  ROUND(SUM(bet_amount) / COUNT(DISTINCT customer_id), 2) as volume_per_customer
FROM live_bets
WHERE timestamp >= (strftime('%s', 'now', '-7 days') * 1000)
  AND agent_id = '${agent_id}'
GROUP BY strftime('%Y-%m-%d', datetime(timestamp/1000, 'unixepoch'))
ORDER BY date ASC;

-- ==================================================
-- DASHBOARD PANEL 7: ALERTS & THRESHOLDS
-- ==================================================

-- Query G: Unusual activity alerts
WITH daily_stats AS (
  SELECT
    strftime('%Y-%m-%d', datetime(timestamp/1000, 'unixepoch')) as date,
    COUNT(*) as daily_bets,
    SUM(bet_amount) as daily_volume,
    COUNT(DISTINCT customer_id) as daily_customers
  FROM live_bets
  WHERE timestamp >= (strftime('%s', 'now', '-30 days') * 1000)
    AND agent_id = '${agent_id}'
  GROUP BY strftime('%Y-%m-%d', datetime(timestamp/1000, 'unixepoch'))
),
stats_agg AS (
  SELECT
    AVG(daily_bets) as avg_daily_bets,
    STDDEV(daily_bets) as stddev_bets,
    AVG(daily_volume) as avg_daily_volume,
    STDDEV(daily_volume) as stddev_volume,
    AVG(daily_customers) as avg_daily_customers,
    STDDEV(daily_customers) as stddev_customers
  FROM daily_stats
)
SELECT
  d.*,
  ROUND((d.daily_bets - s.avg_daily_bets) / NULLIF(s.stddev_bets, 0), 2) as bets_z_score,
  ROUND((d.daily_volume - s.avg_daily_volume) / NULLIF(s.stddev_volume, 0), 2) as volume_z_score,
  ROUND((d.daily_customers - s.avg_daily_customers) / NULLIF(s.stddev_customers, 0), 2) as customers_z_score,
  CASE
    WHEN ABS((d.daily_bets - s.avg_daily_bets) / NULLIF(s.stddev_bets, 0)) > 2 THEN 'UNUSUAL_BET_ACTIVITY'
    WHEN ABS((d.daily_volume - s.avg_daily_volume) / NULLIF(s.stddev_volume, 0)) > 2 THEN 'UNUSUAL_VOLUME'
    WHEN ABS((d.daily_customers - s.avg_daily_customers) / NULLIF(s.stddev_customers, 0)) > 2 THEN 'UNUSUAL_CUSTOMER_ACTIVITY'
    ELSE 'NORMAL'
  END as alert_status
FROM daily_stats d
CROSS JOIN stats_agg s
ORDER BY d.date DESC
LIMIT 1;

-- ==================================================
-- DASHBOARD PANEL 8: SYSTEM HEALTH METRICS
-- ==================================================

-- Query H: System performance monitoring
SELECT
  strftime('%Y-%m-%d %H:%M', datetime(timestamp/1000, 'unixepoch')) as time_bucket,
  metric_type,
  AVG(CASE WHEN value_numeric IS NOT NULL THEN value_numeric ELSE 0 END) as avg_value,
  MAX(CASE WHEN value_numeric IS NOT NULL THEN value_numeric ELSE 0 END) as max_value,
  COUNT(*) as sample_count
FROM system_metrics
WHERE timestamp >= (strftime('%s', 'now', '-24 hours') * 1000)
  AND agent_id = '${agent_id}'
GROUP BY strftime('%Y-%m-%d %H:%M', datetime(timestamp/1000, 'unixepoch')), metric_type
ORDER BY time_bucket DESC;

-- ==================================================
-- DASHBOARD VARIABLES
-- ==================================================

-- For Grafana dashboard variables:
-- ${agent_id} - Agent ID selector (default: first available)
-- ${time_range} - Time range picker (default: last 30 days)
-- ${sport_filter} - Sport type multi-select (default: all)

-- To get available agent IDs for variable:
SELECT DISTINCT agent_id FROM live_bets ORDER BY agent_id;

-- To get available sport types for variable:
SELECT DISTINCT sport_type as __text, sport_type as __value
FROM live_bets
WHERE sport_type != 'Unknown'
ORDER BY sport_type;

-- ==================================================
-- RAW FEED ANALYSIS DASHBOARD QUERIES
-- ==================================================

-- ==================================================
-- RAW FEED DASHBOARD PANEL 1: PARSE SUCCESS RATES
-- ==================================================

-- Query I: ShortDesc parsing success rates over time
SELECT
  strftime('%Y-%m-%d %H:00:00', datetime(rf.timestamp/1000, 'unixepoch')) as hour,
  COUNT(*) as total_feeds,
  COUNT(CASE WHEN rf.processing_status = 'parsed' THEN 1 END) as parsed_feeds,
  COUNT(pb.id) as parsed_bets,
  ROUND(AVG(pb.confidence_score), 3) as avg_confidence,
  COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) as high_confidence_bets,
  ROUND((COUNT(CASE WHEN rf.processing_status = 'parsed' THEN 1 END) * 1.0 / COUNT(*)) * 100, 2) as parse_success_rate
FROM raw_live_feed rf
LEFT JOIN parsed_bets pb ON rf.id = pb.raw_feed_id
WHERE rf.timestamp >= (strftime('%s', 'now', '-24 hours') * 1000)
GROUP BY strftime('%Y-%m-%d %H:00:00', datetime(rf.timestamp/1000, 'unixepoch'))
ORDER BY hour;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 2: TOP SHORTDESC PATTERNS
-- ==================================================

-- Query J: Most common ShortDesc patterns and their success rates
SELECT
  substr(pb.short_desc_raw, 1, instr(pb.short_desc_raw, ' - ') - 1) as pattern_prefix,
  COUNT(*) as occurrences,
  COUNT(DISTINCT rf.id) as unique_feeds,
  ROUND(AVG(pb.confidence_score), 3) as avg_confidence,
  COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) as high_confidence_count,
  ROUND((COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) * 1.0 / COUNT(*)) * 100, 2) as high_confidence_rate,
  pb.bet_type as most_common_bet_type,
  pb.sport_type as most_common_sport
FROM parsed_bets pb
JOIN raw_live_feed rf ON pb.raw_feed_id = rf.id
WHERE rf.timestamp >= (strftime('%s', 'now', '-7 days') * 1000)
GROUP BY substr(pb.short_desc_raw, 1, instr(pb.short_desc_raw, ' - ') - 1)
ORDER BY occurrences DESC
LIMIT 10;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 3: SPORT TYPE PARSING ACCURACY
-- ==================================================

-- Query K: Sport type parsing accuracy by detected patterns
SELECT
  CASE
    WHEN pb.sport_type IS NOT NULL AND pb.sport_type != 'Unknown' THEN pb.sport_type
    ELSE 'Unknown/Null'
  END as sport_type,
  COUNT(*) as total_bets,
  COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) as high_confidence_bets,
  ROUND((COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) * 1.0 / COUNT(*)) * 100, 2) as high_confidence_rate,
  ROUND(AVG(pb.amount), 2) as avg_bet_size,
  COUNT(DISTINCT pb.customer_id) as unique_customers
FROM parsed_bets pb
JOIN raw_live_feed rf ON pb.raw_feed_id = rf.id
WHERE rf.timestamp >= (strftime('%s', 'now', '-30 days') * 1000)
GROUP BY CASE
  WHEN pb.sport_type IS NOT NULL AND pb.sport_type != 'Unknown' THEN pb.sport_type
  ELSE 'Unknown/Null'
END
ORDER BY total_bets DESC;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 4: BET TYPE EXTRACTION SUCCESS
-- ==================================================

-- Query L: Bet type extraction success and distribution
SELECT
  COALESCE(pb.bet_type, 'NULL') as bet_type,
  COUNT(*) as total_bets,
  COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) as high_confidence_bets,
  ROUND((COUNT(CASE WHEN pb.confidence_score >= 0.8 THEN 1 END) * 1.0 / COUNT(*)) * 100, 2) as success_rate,
  ROUND(AVG(pb.amount), 2) as avg_amount,
  ROUND(AVG(pb.odds), 2) as avg_odds
FROM parsed_bets pb
JOIN raw_live_feed rf ON pb.raw_feed_id = rf.id
WHERE rf.timestamp >= (strftime('%s', 'now', '-30 days') * 1000)
GROUP BY pb.bet_type
ORDER BY total_bets DESC;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 5: PROCESSING ERRORS HEATMAP
-- ==================================================

-- Query M: Processing errors by hour and error type
SELECT
  strftime('%Y-%m-%d %H:00:00', datetime(timestamp/1000, 'unixepoch')) as hour,
  error_type,
  processing_step,
  COUNT(*) as error_count,
  GROUP_CONCAT(DISTINCT substr(error_message, 1, 50)) as sample_errors
FROM feed_processing_errors
WHERE timestamp >= (strftime('%s', 'now', '-7 days') * 1000)
GROUP BY strftime('%Y-%m-%d %H:00:00', datetime(timestamp/1000, 'unixepoch')), error_type, processing_step
ORDER BY hour, error_count DESC;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 6: RAW vs PARSED DATA COMPARISON
-- ==================================================

-- Query N: Compare raw feed ingestion rates vs successful parsing
SELECT
  strftime('%Y-%m-%d %H:00:00', datetime(rf.timestamp/1000, 'unixepoch')) as hour,
  COUNT(DISTINCT rf.id) as feeds_ingested,
  COUNT(DISTINCT CASE WHEN rf.processing_status = 'parsed' THEN rf.id END) as feeds_parsed,
  COUNT(pb.id) as bets_parsed,
  SUM(rf.response_size) as total_raw_bytes,
  ROUND(AVG(rf.response_size), 0) as avg_feed_size,
  ROUND((COUNT(DISTINCT CASE WHEN rf.processing_status = 'parsed' THEN rf.id END) * 1.0 / COUNT(DISTINCT rf.id)) * 100, 2) as parse_success_rate
FROM raw_live_feed rf
LEFT JOIN parsed_bets pb ON rf.id = pb.raw_feed_id
WHERE rf.timestamp >= (strftime('%s', 'now', '-7 days') * 1000)
GROUP BY strftime('%Y-%m-%d %H:00:00', datetime(rf.timestamp/1000, 'unixepoch'))
ORDER BY hour;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 7: CONSUMER CONFIDENCE DISTRIBUTION
-- ==================================================

-- Query O: Distribution of parsing confidence scores
SELECT
  ROUND(pb.confidence_score * 10) / 10 as confidence_bucket,
  COUNT(*) as bet_count,
  ROUND((COUNT(*) * 1.0 / (SELECT COUNT(*) FROM parsed_bets WHERE confidence_score IS NOT NULL)) * 100, 2) as percentage,
  ROUND(AVG(pb.amount), 2) as avg_bet_size,
  COUNT(DISTINCT pb.customer_id) as unique_customers
FROM parsed_bets pb
JOIN raw_live_feed rf ON pb.raw_feed_id = rf.id
WHERE rf.timestamp >= (strftime('%s', 'now', '-30 days') * 1000)
  AND pb.confidence_score IS NOT NULL
GROUP BY ROUND(pb.confidence_score * 10) / 10
ORDER BY confidence_bucket DESC;

-- ==================================================
-- RAW FEED DASHBOARD PANEL 8: FEED FRESHNESS AND FREQUENCY
-- ==================================================

-- Query P: Feed health metrics - freshness and update frequency
SELECT
  strftime('%Y-%m-%d', datetime(rf.timestamp/1000, 'unixepoch')) as date,
  COUNT(DISTINCT rf.id) as total_feeds,
  COUNT(DISTINCT strftime('%Y-%m-%d %H', datetime(rf.timestamp/1000, 'unixepoch'))) as active_hours,
  ROUND(AVG(julianday(datetime(rf.timestamp/1000, 'unixepoch')) - julianday(LAG(datetime(rf.timestamp/1000, 'unixepoch')) OVER (ORDER BY rf.timestamp))), 4) as avg_feed_interval_hours,
  MIN(rf.timestamp) as oldest_feed,
  MAX(rf.timestamp) as newest_feed,
  ROUND((MAX(rf.timestamp) - MIN(rf.timestamp)) / (1000 * 60 * 60), 2) as feed_span_hours,
  ROUND(COUNT(DISTINCT rf.id) * 1.0 / COUNT(DISTINCT strftime('%Y-%m-%d %H', datetime(rf.timestamp/1000, 'unixepoch'))), 2) as feeds_per_active_hour
FROM raw_live_feed rf
WHERE rf.timestamp >= (strftime('%s', 'now', '-14 days') * 1000)
GROUP BY strftime('%Y-%m-%d', datetime(rf.timestamp/1000, 'unixepoch'))
ORDER BY date DESC;

-- ==================================================
-- RAW FEED VARIABLES FOR DASHBOARDS
-- ==================================================

-- For raw feed dashboard variables:
-- ${agent_id} - Agent ID selector (same as analytics dashboard)
-- ${time_range} - Time range picker (default: last 24 hours for raw feed)
-- ${confidence_threshold} - Confidence score filter (default: 0.0)
-- ${sport_filter} - Sport type filter (from parsed_bets table)

-- Available agent IDs for raw feed dashboard:
SELECT DISTINCT agent_id FROM raw_live_feed ORDER BY agent_id;

-- Available sport types from parsed bets:
SELECT DISTINCT sport_type as __text, sport_type as __value
FROM parsed_bets
WHERE sport_type IS NOT NULL AND sport_type != 'Unknown'
ORDER BY sport_type;

-- Available bet types from parsed bets:
SELECT DISTINCT bet_type as __text, COALESCE(bet_type, 'Unknown') as __value
FROM parsed_bets
ORDER BY bet_type;
