SET @tp := 0.03;   /* +0.35% */

SET @sl := -0.03;  /* -0.25% */



/* ---- signal + TP/SL backtest ---- */

WITH

signals AS (

  SELECT

    f.coin,

    f.minute_time AS end_of_6m,

    f.close_price AS buy_price,

    f.total_gain_6,

    f.pct_range_30m,

    f.sma30_1m,

    f.positives_6,

    f.is_green

  FROM prices_07_2025_feat f

  WHERE f.positives_6 >= 5

    AND f.is_green = 0                                  /* last candle red (pullback) */

    AND f.total_gain_6 BETWEEN 0.8 AND 1.4             /* tighter 6m band (%) */

    AND f.pct_range_30m BETWEEN 1.0 AND 3.0            /* 30m volatility band (%) */

    AND f.close_price >= f.sma30_1m * 1.001            /* stronger trend filter */

    AND MINUTE(f.minute_time) NOT IN (0,1,2,3,4)       /* skip first 5 minutes each hour */

),

dedup AS (

  SELECT

    s.*,

    LAG(s.end_of_6m) OVER (PARTITION BY s.coin ORDER BY s.end_of_6m) AS prev_hit

  FROM signals s

),

entries AS (

  /* edge detect + 60m cooldown */

  SELECT

    d.coin,

    d.end_of_6m AS buy_time,

    d.buy_price,

    d.buy_price * (1 + @tp) AS tp_price,

    d.buy_price * (1 + @sl) AS sl_price

  FROM dedup d

  WHERE d.prev_hit IS NULL

     OR d.prev_hit <= d.end_of_6m - INTERVAL 60 MINUTE

),

fut AS (

  /* all future bars up to +15m for every entry */

  SELECT

    e.coin,

    e.buy_time,

    e.buy_price,

    e.tp_price,

    e.sl_price,

    p.minute_time,

    p.close_price

  FROM entries e

  JOIN prices_07_2025 p

    ON p.coin = e.coin

   AND p.minute_time >= e.buy_time

   AND p.minute_time <= e.buy_time + INTERVAL 15 MINUTE

),

first_hits AS (

  /* earliest TP time and earliest SL time per entry */

  SELECT

    coin,

    buy_time,

    buy_price,

    tp_price,

    sl_price,

    MIN(CASE WHEN close_price >= tp_price THEN minute_time END) AS tp_time,

    MIN(CASE WHEN close_price <= sl_price THEN minute_time END) AS sl_time

  FROM fut

  GROUP BY coin, buy_time, buy_price, tp_price, sl_price

),

exits AS (

  /* decide which happened first; if none, exit at +15m close */

  SELECT

    fh.coin,

    fh.buy_time,

    fh.buy_price,

    CASE

      WHEN fh.tp_time IS NOT NULL

           AND (fh.sl_time IS NULL OR fh.tp_time <= fh.sl_time) THEN 'TP'

      WHEN fh.sl_time IS NOT NULL

           AND (fh.tp_time IS NULL OR fh.sl_time < fh.tp_time) THEN 'SL'

      ELSE 'TIME'

    END AS exit_type,

    CASE

      WHEN fh.tp_time IS NOT NULL

           AND (fh.sl_time IS NULL OR fh.tp_time <= fh.sl_time) THEN fh.tp_time

      WHEN fh.sl_time IS NOT NULL

           AND (fh.tp_time IS NULL OR fh.sl_time < fh.tp_time) THEN fh.sl_time

      ELSE fh.buy_time + INTERVAL 15 MINUTE

    END AS exit_time

  FROM first_hits fh

),

trades AS (

  /* price at exit_time (for TP/SL we take that minute’s close) */

  SELECT

    x.coin,

    x.buy_time,

    x.buy_price,

    x.exit_type,

    x.exit_time,

    pexit.close_price AS sell_price,

    ROUND((pexit.close_price - x.buy_price) / x.buy_price * 100, 4) AS profit_pct

  FROM exits x

  JOIN prices_07_2025 pexit

    ON pexit.coin = x.coin

   AND pexit.minute_time = x.exit_time

)

SELECT

  ROUND(SUM(profit_pct), 3)                                       AS total_profit_pct,

  ROUND(AVG(profit_pct), 4)                                       AS avg_profit_pct,

  COUNT(*)                                                        AS total_trades,

  SUM(profit_pct > 0)                                             AS winning_trades,

  SUM(profit_pct < 0)                                             AS losing_trades,

  ROUND(SUM(profit_pct > 0) / COUNT(*) * 100, 2)                  AS win_rate_pct,

  SUM(exit_type = 'TP')                                           AS tp_hits,

  SUM(exit_type = 'SL')                                           AS sl_hits,

  SUM(exit_type = 'TIME')                                         AS time_exits

FROM trades;



