-- Signal Quality Version Analysis
-- Compare performance between different scoring logic versions

-- 1. Count trades by version
SELECT 
  COALESCE("signalQualityVersion", 'v1/null') as version,
  COUNT(*) as trade_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 1) as percentage
FROM "Trade"
WHERE "exitReason" IS NOT NULL
GROUP BY "signalQualityVersion"
ORDER BY version;

-- 2. Performance by version
SELECT 
  COALESCE("signalQualityVersion", 'v1/null') as version,
  COUNT(*) as trades,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(SUM("realizedPnL")::numeric, 2) as total_pnl,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate,
  ROUND(AVG("signalQualityScore")::numeric, 1) as avg_quality_score,
  ROUND(AVG("maxFavorableExcursion")::numeric, 2) as avg_mfe,
  ROUND(AVG("maxAdverseExcursion")::numeric, 2) as avg_mae
FROM "Trade"
WHERE "exitReason" IS NOT NULL AND "exitReason" NOT LIKE '%CLEANUP%'
GROUP BY "signalQualityVersion"
ORDER BY version;

-- 3. Version breakdown by exit reason
SELECT 
  COALESCE("signalQualityVersion", 'v1/null') as version,
  "exitReason",
  COUNT(*) as count,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl
FROM "Trade"
WHERE "exitReason" IS NOT NULL AND "exitReason" NOT LIKE '%CLEANUP%'
GROUP BY "signalQualityVersion", "exitReason"
ORDER BY version, count DESC;

-- 4. Quality score distribution by version
SELECT 
  COALESCE("signalQualityVersion", 'v1/null') as version,
  CASE 
    WHEN "signalQualityScore" >= 80 THEN '80-100 (High)'
    WHEN "signalQualityScore" >= 70 THEN '70-79 (Good)'
    WHEN "signalQualityScore" >= 60 THEN '60-69 (Pass)'
    ELSE '< 60 (Block)'
  END as score_range,
  COUNT(*) as trades,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate
FROM "Trade"
WHERE "exitReason" IS NOT NULL AND "signalQualityScore" IS NOT NULL
GROUP BY "signalQualityVersion", score_range
ORDER BY version, score_range DESC;

-- 5. Extreme position entries by version (< 15% or > 85%)
SELECT 
  COALESCE("signalQualityVersion", 'v1/null') as version,
  direction,
  COUNT(*) as trades,
  ROUND(AVG("pricePositionAtEntry")::numeric, 1) as avg_price_pos,
  ROUND(AVG("adxAtEntry")::numeric, 1) as avg_adx,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate
FROM "Trade"
WHERE "exitReason" IS NOT NULL 
  AND "pricePositionAtEntry" IS NOT NULL
  AND ("pricePositionAtEntry" < 15 OR "pricePositionAtEntry" > 85)
GROUP BY "signalQualityVersion", direction
ORDER BY version, direction;

-- 6. Recent v3 trades (new logic)
SELECT 
  "createdAt",
  symbol,
  direction,
  "entryPrice",
  "exitPrice",
  "exitReason",
  ROUND("realizedPnL"::numeric, 2) as pnl,
  "signalQualityScore" as score,
  ROUND("adxAtEntry"::numeric, 1) as adx,
  ROUND("pricePositionAtEntry"::numeric, 1) as price_pos
FROM "Trade"
WHERE "signalQualityVersion" = 'v3'
  AND "exitReason" IS NOT NULL
ORDER BY "createdAt" DESC
LIMIT 20;

-- 7. Compare v3 vs pre-v3 on extreme positions
WITH version_groups AS (
  SELECT 
    CASE WHEN "signalQualityVersion" = 'v3' THEN 'v3 (NEW)' ELSE 'pre-v3 (OLD)' END as version_group,
    *
  FROM "Trade"
  WHERE "exitReason" IS NOT NULL 
    AND "pricePositionAtEntry" IS NOT NULL
    AND ("pricePositionAtEntry" < 15 OR "pricePositionAtEntry" > 85)
    AND "adxAtEntry" IS NOT NULL
)
SELECT 
  version_group,
  COUNT(*) as trades,
  ROUND(AVG("adxAtEntry")::numeric, 1) as avg_adx,
  COUNT(*) FILTER (WHERE "adxAtEntry" < 18) as weak_adx_count,
  ROUND(AVG("realizedPnL")::numeric, 2) as avg_pnl,
  ROUND(SUM("realizedPnL")::numeric, 2) as total_pnl,
  ROUND(100.0 * SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) / COUNT(*)::numeric, 1) as win_rate
FROM version_groups
GROUP BY version_group
ORDER BY version_group DESC;

-- 8. Daily performance by version (last 7 days)
SELECT 
  DATE("createdAt") as trade_date,
  COALESCE("signalQualityVersion", 'v1/null') as version,
  COUNT(*) as trades,
  ROUND(SUM("realizedPnL")::numeric, 2) as daily_pnl
FROM "Trade"
WHERE "exitReason" IS NOT NULL
  AND "createdAt" >= NOW() - INTERVAL '7 days'
GROUP BY trade_date, "signalQualityVersion"
ORDER BY trade_date DESC, version;