-- Optimal Exit Level Analysis
-- Run this to determine data-driven SL/TP settings
-- Execute: docker exec trading-bot-postgres psql -U postgres -d trading_bot_v4 -f /path/to/this/file.sql

\echo '=========================================='
\echo '1. MFE/MAE DISTRIBUTION ANALYSIS'
\echo 'Where do trades actually move?'
\echo '=========================================='

SELECT 
  direction,
  COUNT(*) as total_trades,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_best_profit,
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "maxFavorableExcursion")::numeric, 2) as q25_mfe,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY "maxFavorableExcursion")::numeric, 2) as median_mfe,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY "maxFavorableExcursion")::numeric, 2) as q75_mfe,
  ROUND(MAX("maxFavorableExcursion")::numeric, 2) as max_mfe,
  ROUND(AVG("maxAdverseExcursion")::numeric, 2) as avg_worst_loss,
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "maxAdverseExcursion")::numeric, 2) as q25_mae,
  ROUND(MIN("maxAdverseExcursion")::numeric, 2) as min_mae
FROM "Trade"
WHERE "exitReason" IS NOT NULL 
  AND "maxFavorableExcursion" IS NOT NULL
  AND "maxAdverseExcursion" IS NOT NULL
GROUP BY direction;

\echo ''
\echo 'Interpretation:'
\echo '- median_mfe = Where 50% of trades profit reaches (set TP2 here)'
\echo '- q75_mfe = Where top 25% reaches (runner territory)'
\echo '- q25_mae = Where 25% of worst losses occur (set SL here + buffer)'
\echo ''

\echo '=========================================='
\echo '2. QUALITY SCORE vs PERFORMANCE'
\echo 'Do high quality signals move further?'
\echo '=========================================='

SELECT 
  CASE 
    WHEN "signalQualityScore" >= 80 THEN 'High (80-100)'
    WHEN "signalQualityScore" >= 70 THEN 'Medium (70-79)'
    ELSE 'Low (60-69)'
  END as quality_tier,
  COUNT(*) as trades,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_mfe,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate,
  -- How many went beyond current TP1 (+0.4%)?
  ROUND(100.0 * SUM(CASE WHEN "maxFavorableExcursion" > 0.4 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as pct_exceeded_tp1,
  -- How many went beyond current TP2 (+0.7%)?
  ROUND(100.0 * SUM(CASE WHEN "maxFavorableExcursion" > 0.7 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as pct_exceeded_tp2,
  -- How many reached runner territory (2%+)?
  ROUND(100.0 * SUM(CASE WHEN "maxFavorableExcursion" > 2.0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as pct_runner_potential
FROM "Trade"
WHERE "signalQualityScore" IS NOT NULL 
  AND "exitReason" IS NOT NULL
  AND "maxFavorableExcursion" IS NOT NULL
GROUP BY quality_tier
ORDER BY quality_tier;

\echo ''
\echo 'Interpretation:'
\echo '- If High Quality has higher pct_exceeded_tp2 → Use quality-based tiers'
\echo '- If pct_runner_potential > 40% → Runners make sense'
\echo '- If pct_runner_potential < 20% → Quick exits better'
\echo ''

\echo '=========================================='
\echo '3. RUNNER POTENTIAL BY EXIT REASON'
\echo 'Are we leaving money on the table?'
\echo '=========================================='

SELECT 
  direction,
  "exitReason",
  COUNT(*) as count,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_mfe,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(AVG(("maxFavorableExcursion" - ABS(("exitPrice" - "entryPrice") / "entryPrice" * 100)))::numeric, 2) as avg_mfe_vs_exit_gap,
  SUM(CASE WHEN "maxFavorableExcursion" > 1.5 THEN 1 ELSE 0 END) as moved_beyond_1_5pct,
  SUM(CASE WHEN "maxFavorableExcursion" > 2.0 THEN 1 ELSE 0 END) as moved_beyond_2pct,
  SUM(CASE WHEN "maxFavorableExcursion" > 3.0 THEN 1 ELSE 0 END) as moved_beyond_3pct
FROM "Trade"
WHERE "exitReason" IS NOT NULL 
  AND "maxFavorableExcursion" IS NOT NULL
  AND "exitPrice" IS NOT NULL
GROUP BY direction, "exitReason"
ORDER BY direction, count DESC;

\echo ''
\echo 'Interpretation:'
\echo '- avg_mfe_vs_exit_gap = How much profit left on table'
\echo '- If TP1/TP2 exits have large gap → Move targets wider'
\echo '- If SL exits have positive MFE → SL too tight'
\echo ''

\echo '=========================================='
\echo '4. ATR CORRELATION WITH MOVEMENT'
\echo 'Does volatility predict move size?'
\echo '=========================================='

SELECT 
  CASE 
    WHEN atr < 0.3 THEN 'Low (<0.3%)'
    WHEN atr < 0.6 THEN 'Medium (0.3-0.6%)'
    ELSE 'High (>0.6%)'
  END as atr_bucket,
  COUNT(*) as trades,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_mfe,
  ROUND(AVG(ABS("maxAdverseExcursion"))::numeric, 2) as avg_mae_abs,
  ROUND(AVG(atr)::numeric, 3) as avg_atr,
  -- MFE to ATR ratio (how many ATRs did price move?)
  ROUND(AVG("maxFavorableExcursion" / atr)::numeric, 1) as mfe_to_atr_ratio
FROM "Trade"
WHERE atr IS NOT NULL 
  AND atr > 0
  AND "exitReason" IS NOT NULL
  AND "maxFavorableExcursion" IS NOT NULL
GROUP BY atr_bucket
ORDER BY avg_atr;

\echo ''
\echo 'Interpretation:'
\echo '- mfe_to_atr_ratio = How many ATRs price typically moves'
\echo '- If ratio > 3 → Price moves 3x ATR, use ATR-based targets'
\echo '- If ratio ~1-2 → Fixed targets may work better'
\echo ''

\echo '=========================================='
\echo '5. DIRECTION-SPECIFIC PERFORMANCE'
\echo 'Should longs vs shorts have different exits?'
\echo '=========================================='

SELECT 
  direction,
  COUNT(*) as trades,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_mfe,
  ROUND(AVG("maxAdverseExcursion")::numeric, 2) as avg_mae,
  ROUND(AVG("holdTimeSeconds")::numeric / 60, 1) as avg_hold_minutes,
  -- How many hit TP vs SL?
  SUM(CASE WHEN "exitReason" IN ('TP1', 'TP2') THEN 1 ELSE 0 END) as tp_exits,
  SUM(CASE WHEN "exitReason" IN ('SL', 'SOFT_SL', 'HARD_SL') THEN 1 ELSE 0 END) as sl_exits
FROM "Trade"
WHERE "exitReason" IS NOT NULL
  AND "maxFavorableExcursion" IS NOT NULL
GROUP BY direction;

\echo ''
\echo 'Interpretation:'
\echo '- If one direction has much higher MFE → Give it wider TP2'
\echo '- If one direction has worse MAE → Tighten its SL'
\echo '- Different avg_hold_minutes → May need different trailing stops'
\echo ''

\echo '=========================================='
\echo '6. CURRENT TP/SL HIT ANALYSIS'
\echo 'How often do current levels trigger?'
\echo '=========================================='

SELECT 
  CASE 
    WHEN "maxFavorableExcursion" < 0.4 THEN 'Never reached TP1'
    WHEN "maxFavorableExcursion" >= 0.4 AND "maxFavorableExcursion" < 0.7 THEN 'Reached TP1 only'
    WHEN "maxFavorableExcursion" >= 0.7 AND "maxFavorableExcursion" < 2.0 THEN 'Reached TP2'
    ELSE 'Runner territory (2%+)'
  END as price_action,
  COUNT(*) as trades,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER ()::numeric, 1) as pct_of_total,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_mfe,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl
FROM "Trade"
WHERE "exitReason" IS NOT NULL
  AND "maxFavorableExcursion" IS NOT NULL
GROUP BY price_action
ORDER BY avg_mfe;

\echo ''
\echo 'Interpretation:'
\echo '- If most trades in "Never reached TP1" → TP1 too wide'
\echo '- If most trades in "Runner territory" → We are exiting too early'
\echo '- Ideal: ~60% reach TP1, ~30% reach TP2, ~10% runners'
\echo ''

\echo '=========================================='
\echo '7. RECENT PERFORMANCE TREND'
\echo 'Is strategy improving over time?'
\echo '=========================================='

SELECT 
  DATE("createdAt") as trade_date,
  COUNT(*) as trades,
  ROUND(AVG("signalQualityScore")::numeric, 1) as avg_score,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate,
  ROUND(SUM("realizedPnL")::numeric, 2) as daily_pnl
FROM "Trade"
WHERE "exitReason" IS NOT NULL
  AND "createdAt" > NOW() - INTERVAL '30 days'
GROUP BY trade_date
ORDER BY trade_date DESC
LIMIT 10;

\echo ''
\echo 'Interpretation:'
\echo '- Rising avg_score + rising win_rate → Quality scoring working'
\echo '- Declining daily_pnl despite good win_rate → Exits may need adjustment'
\echo ''

\echo '=========================================='
\echo 'SUMMARY RECOMMENDATIONS'
\echo '=========================================='

\echo 'Based on the analysis above, consider:'
\echo ''
\echo '1. TP1 Setting:'
\echo '   - Should be at ~25th percentile of MFE (where most trades reach)'
\echo '   - Currently: +0.4%'
\echo ''
\echo '2. TP2 Setting:'
\echo '   - Should be at ~median MFE (where 50% reach)'
\echo '   - Currently: +0.7% (with ATR-based dynamic up to 3%)'
\echo ''
\echo '3. Runner Strategy:'
\echo '   - Only use if >40% trades reach "Runner territory"'
\echo '   - Currently: 25% position with ATR trailing'
\echo ''
\echo '4. Stop Loss:'
\echo '   - Should be at ~25th percentile of MAE (protect from worst 25%)'
\echo '   - Currently: -1.5% soft, -2.5% hard'
\echo ''
\echo '5. Quality-Based Tiers:'
\echo '   - Only implement if High Quality tier has significantly better metrics'
\echo '   - Check pct_runner_potential difference across tiers'
\echo ''