-- Backtest Q≥95 + Instant Reversal + HTF + 5-Candle Exit Strategy
-- Compares current strategy results with proposed new filters

-- Configuration
\set STARTING_CAPITAL 97.55
\set Q_THRESHOLD 95
\set HTF_Q_THRESHOLD 85
\set TIME_EXIT_CANDLES 5
\set TIME_EXIT_MFE_THRESHOLD 30.00

-- Full backtest with all filters
WITH trades_with_htf AS (
  SELECT 
    t.*,
    EXTRACT(EPOCH FROM (t."exitTime" - t."entryTime"))/300 AS candles_in_trade,
    (
      SELECT direction 
      FROM "BlockedSignal" 
      WHERE timeframe = '15' 
        AND "createdAt" <= t."entryTime" 
      ORDER BY "createdAt" DESC 
      LIMIT 1
    ) AS htf_direction
  FROM "Trade" t
  WHERE t.timeframe = '5'
    AND t."signalSource" != 'manual'
    AND t.status = 'closed'
    AND t."exitReason" IS NOT NULL
    AND t."entryTime" >= '2024-11-19'
),

trades_with_filters AS (
  SELECT 
    t.*,
    -- HTF blocked flag
    CASE 
      WHEN t.direction = t.htf_direction 
        AND t."signalQualityScore" < :HTF_Q_THRESHOLD
      THEN 1 
      ELSE 0 
    END AS htf_blocked,
    
    -- Instant reversal flag (SL within 1 candle)
    CASE 
      WHEN t."exitReason" = 'SL' 
        AND t.candles_in_trade <= 1
      THEN 1 
      ELSE 0 
    END AS instant_reversal,
    
    -- Quality check
    CASE 
      WHEN t."signalQualityScore" >= :Q_THRESHOLD THEN 1 
      ELSE 0 
    END AS quality_pass,
    
    -- Simulated PnL with time exit rule
    CASE 
      -- Natural good exits: keep realized PnL
      WHEN t."exitReason" IN ('TP1', 'TP2', 'TRAILING_SL') 
        THEN CAST(t."realizedPnL" AS NUMERIC(10,2))
      
      -- High MFE achieved: keep realized PnL  
      WHEN CAST(t."maxFavorableExcursion" AS NUMERIC(10,2)) >= :TIME_EXIT_MFE_THRESHOLD
        THEN CAST(t."realizedPnL" AS NUMERIC(10,2))
      
      -- Exited within time window: keep realized PnL
      WHEN t.candles_in_trade <= :TIME_EXIT_CANDLES
        THEN CAST(t."realizedPnL" AS NUMERIC(10,2))
      
      -- Simulate time exit: 50% of MFE or MAE (whichever better)
      ELSE GREATEST(
        CAST(t."maxFavorableExcursion" AS NUMERIC(10,2)) * 0.5,
        CAST(t."maxAdverseExcursion" AS NUMERIC(10,2))
      )
    END AS simulated_pnl,
    
    -- Exit reason for new strategy
    CASE 
      WHEN t."exitReason" IN ('TP1', 'TP2', 'TRAILING_SL') 
        THEN t."exitReason"
      WHEN CAST(t."maxFavorableExcursion" AS NUMERIC(10,2)) >= :TIME_EXIT_MFE_THRESHOLD
        THEN t."exitReason"
      WHEN t.candles_in_trade <= :TIME_EXIT_CANDLES
        THEN t."exitReason"
      ELSE 'TIME_EXIT_5_CANDLE'
    END AS simulated_exit_reason
    
  FROM trades_with_htf t
),

-- Original strategy (all trades)
original_strategy AS (
  SELECT 
    COUNT(*) AS trades,
    SUM(CASE WHEN "realizedPnL" > 0 THEN 1 ELSE 0 END) AS wins,
    CAST(SUM("realizedPnL") AS NUMERIC(10,2)) AS total_pnl,
    CAST(AVG("realizedPnL") AS NUMERIC(10,2)) AS avg_pnl,
    MIN("entryTime") AS start_date,
    MAX("entryTime") AS end_date,
    EXTRACT(days FROM (MAX("entryTime") - MIN("entryTime"))) AS days
  FROM trades_with_filters
),

-- New strategy (Q>=95 + filters)
new_strategy AS (
  SELECT 
    COUNT(*) AS trades,
    SUM(CASE WHEN simulated_pnl > 0 THEN 1 ELSE 0 END) AS wins,
    CAST(SUM(simulated_pnl) AS NUMERIC(10,2)) AS total_pnl,
    CAST(AVG(simulated_pnl) AS NUMERIC(10,2)) AS avg_pnl
  FROM trades_with_filters
  WHERE quality_pass = 1
    AND htf_blocked = 0
    AND instant_reversal = 0
),

-- Filter breakdown
filter_stats AS (
  SELECT 
    COUNT(*) FILTER (WHERE "signalQualityScore" < :Q_THRESHOLD) AS blocked_by_q,
    COUNT(*) FILTER (WHERE htf_blocked = 1 AND "signalQualityScore" >= :Q_THRESHOLD) AS blocked_by_htf,
    COUNT(*) FILTER (WHERE instant_reversal = 1 AND htf_blocked = 0 AND "signalQualityScore" >= :Q_THRESHOLD) AS blocked_by_instant_reversal
  FROM trades_with_filters
)

-- Main report
SELECT 
  '================================================================================' AS separator_1
UNION ALL
SELECT 'BACKTEST REPORT: Q≥95 + Instant Reversal + HTF + 5-Candle Exit'
UNION ALL
SELECT '================================================================================'
UNION ALL
SELECT ''
UNION ALL
SELECT 'Period: ' || TO_CHAR(o.start_date, 'YYYY-MM-DD') || ' to ' || TO_CHAR(o.end_date, 'YYYY-MM-DD') || ' (' || o.days || ' days)'
FROM original_strategy o
UNION ALL
SELECT 'Starting Capital: $' || :STARTING_CAPITAL
UNION ALL
SELECT ''
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'ORIGINAL STRATEGY (Current Live)'
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'Trades:          ' || o.trades FROM original_strategy o
UNION ALL
SELECT 'Wins:            ' || o.wins || ' (' || ROUND(o.wins::numeric / NULLIF(o.trades,0) * 100, 1) || '%)' FROM original_strategy o
UNION ALL
SELECT 'Total PnL:       $' || o.total_pnl FROM original_strategy o
UNION ALL
SELECT 'Ending Capital:  $' || CAST(:STARTING_CAPITAL + o.total_pnl AS NUMERIC(10,2)) FROM original_strategy o
UNION ALL
SELECT 'Total Return:    ' || ROUND(o.total_pnl / :STARTING_CAPITAL * 100, 2) || '%' FROM original_strategy o
UNION ALL
SELECT 'Daily Return:    ' || ROUND(o.total_pnl / :STARTING_CAPITAL / NULLIF(o.days,0) * 100, 3) || '%' FROM original_strategy o
UNION ALL
SELECT ''
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'NEW STRATEGY (Q≥95 + Filters)'
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'Trades:          ' || n.trades FROM new_strategy n
UNION ALL
SELECT 'Wins:            ' || n.wins || ' (' || ROUND(n.wins::numeric / NULLIF(n.trades,0) * 100, 1) || '%)' FROM new_strategy n
UNION ALL
SELECT 'Total PnL:       $' || n.total_pnl FROM new_strategy n
UNION ALL
SELECT 'Ending Capital:  $' || CAST(:STARTING_CAPITAL + n.total_pnl AS NUMERIC(10,2)) FROM new_strategy n
UNION ALL
SELECT 'Total Return:    ' || ROUND(n.total_pnl / :STARTING_CAPITAL * 100, 2) || '%' FROM new_strategy n
UNION ALL
SELECT 'Daily Return:    ' || ROUND(n.total_pnl / :STARTING_CAPITAL / (SELECT days FROM original_strategy) * 100, 3) || '%' FROM new_strategy n
UNION ALL
SELECT ''
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'FILTER IMPACT'
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'Blocked by Q<' || :Q_THRESHOLD || ':           ' || f.blocked_by_q || ' trades' FROM filter_stats f
UNION ALL
SELECT 'Blocked by HTF:                      ' || f.blocked_by_htf || ' trades' FROM filter_stats f
UNION ALL
SELECT 'Blocked by Instant Reversal:         ' || f.blocked_by_instant_reversal || ' trades' FROM filter_stats f
UNION ALL
SELECT 'Total Filtered Out:                  ' || (o.trades - n.trades) || ' trades' FROM original_strategy o, new_strategy n
UNION ALL
SELECT ''
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'COMPARISON'
UNION ALL
SELECT '--------------------------------------------------------------------------------'
UNION ALL
SELECT 'PnL Change:      $' || CAST(n.total_pnl - o.total_pnl AS NUMERIC(10,2)) || ' (' || 
       ROUND((n.total_pnl - o.total_pnl) / :STARTING_CAPITAL * 100, 2) || '%)' 
FROM original_strategy o, new_strategy n
UNION ALL
SELECT 'Trade Count:     ' || (n.trades - o.trades) || ' (' || n.trades || ' vs ' || o.trades || ')' 
FROM original_strategy o, new_strategy n
UNION ALL
SELECT 'Hit Rate Change: ' || ROUND((n.wins::numeric / NULLIF(n.trades,0) - o.wins::numeric / NULLIF(o.trades,0)) * 100, 1) || '%' 
FROM original_strategy o, new_strategy n
UNION ALL
SELECT 'Avg PnL/Trade:   $' || n.avg_pnl FROM new_strategy n WHERE n.trades > 0
UNION ALL
SELECT ''
UNION ALL
SELECT '================================================================================'
UNION ALL
SELECT ''
UNION ALL
SELECT 'RECOMMENDATION:'
UNION ALL
(
  SELECT CASE
    WHEN n.total_pnl > 0 AND (n.wins::numeric / NULLIF(n.trades,0)) >= 0.40 AND n.trades >= 10 THEN
      E'✓ NEW STRATEGY LOOKS PROMISING\n' ||
      '  - Positive PnL ($' || n.total_pnl || E')\n' ||
      '  - Decent hit rate (' || ROUND(n.wins::numeric / NULLIF(n.trades,0) * 100, 1) || E'%)\n' ||
      '  - Sufficient trades (' || n.trades || E')\n' ||
      '  → Consider implementing with shadow mode first'
    WHEN n.total_pnl > o.total_pnl THEN
      E'⚠ NEW STRATEGY SHOWS IMPROVEMENT BUT NEEDS MORE DATA\n' ||
      '  - Better PnL ($' || CAST(n.total_pnl - o.total_pnl AS NUMERIC(10,2)) || E')\n' ||
      '  - Only ' || n.trades || E' trades (need ≥100 for confidence)\n' ||
      '  → Run shadow mode for 4-6 weeks before live deployment'
    ELSE
      E'✗ NEW STRATEGY DOES NOT IMPROVE RESULTS\n' ||
      '  - PnL worse by $' || CAST(o.total_pnl - n.total_pnl AS NUMERIC(10,2)) || E'\n' ||
      '  → Do not deploy; revisit filter thresholds'
  END
  FROM original_strategy o, new_strategy n
)
UNION ALL
SELECT '================================================================================'
;

-- Sample qualified trades
\echo ''
\echo 'SAMPLE QUALIFIED TRADES (First 10):'
\echo '--------------------------------------------------------------------------------'

SELECT 
  ROW_NUMBER() OVER (ORDER BY "entryTime") AS "#",
  TO_CHAR("entryTime", 'YYYY-MM-DD') AS date,
  UPPER(direction) AS dir,
  "signalQualityScore" AS q,
  simulated_exit_reason AS exit_reason,
  '$' || CAST(simulated_pnl AS NUMERIC(7,2)) AS pnl
FROM trades_with_filters
WHERE quality_pass = 1
  AND htf_blocked = 0
  AND instant_reversal = 0
ORDER BY "entryTime"
LIMIT 10;