WITH latest_profile AS (
  SELECT MAX(snapshot_date) AS snapshot_date
  FROM a_share_profile_snapshot
  WHERE snapshot_date <= '2026-07-04'
),
universe AS (
  SELECT
    p.symbol,
    p.stock_name,
    p.industry_l1,
    p.industry_l2,
    p.concepts_raw,
    p.main_business_raw
  FROM a_share_profile_snapshot p
  JOIN latest_profile lp ON lp.snapshot_date = p.snapshot_date
  WHERE p.status = 'OK'
    AND ((p.industry_l1 LIKE '%机器人%' ESCAPE '\\' OR p.industry_l2 LIKE '%机器人%' ESCAPE '\\' OR p.concepts_raw LIKE '%机器人%' ESCAPE '\\' OR p.main_business_raw LIKE '%机器人%' ESCAPE '\\' OR p.stock_name LIKE '%机器人%' ESCAPE '\\') OR (p.industry_l1 LIKE '%人形机器人%' ESCAPE '\\' OR p.industry_l2 LIKE '%人形机器人%' ESCAPE '\\' OR p.concepts_raw LIKE '%人形机器人%' ESCAPE '\\' OR p.main_business_raw LIKE '%人形机器人%' ESCAPE '\\' OR p.stock_name LIKE '%人形机器人%' ESCAPE '\\') OR (p.industry_l1 LIKE '%工业机器人%' ESCAPE '\\' OR p.industry_l2 LIKE '%工业机器人%' ESCAPE '\\' OR p.concepts_raw LIKE '%工业机器人%' ESCAPE '\\' OR p.main_business_raw LIKE '%工业机器人%' ESCAPE '\\' OR p.stock_name LIKE '%工业机器人%' ESCAPE '\\') OR (p.industry_l1 LIKE '%机器视觉%' ESCAPE '\\' OR p.industry_l2 LIKE '%机器视觉%' ESCAPE '\\' OR p.concepts_raw LIKE '%机器视觉%' ESCAPE '\\' OR p.main_business_raw LIKE '%机器视觉%' ESCAPE '\\' OR p.stock_name LIKE '%机器视觉%' ESCAPE '\\') OR (p.industry_l1 LIKE '%减速器%' ESCAPE '\\' OR p.industry_l2 LIKE '%减速器%' ESCAPE '\\' OR p.concepts_raw LIKE '%减速器%' ESCAPE '\\' OR p.main_business_raw LIKE '%减速器%' ESCAPE '\\' OR p.stock_name LIKE '%减速器%' ESCAPE '\\') OR (p.industry_l1 LIKE '%伺服%' ESCAPE '\\' OR p.industry_l2 LIKE '%伺服%' ESCAPE '\\' OR p.concepts_raw LIKE '%伺服%' ESCAPE '\\' OR p.main_business_raw LIKE '%伺服%' ESCAPE '\\' OR p.stock_name LIKE '%伺服%' ESCAPE '\\') OR (p.industry_l1 LIKE '%丝杠%' ESCAPE '\\' OR p.industry_l2 LIKE '%丝杠%' ESCAPE '\\' OR p.concepts_raw LIKE '%丝杠%' ESCAPE '\\' OR p.main_business_raw LIKE '%丝杠%' ESCAPE '\\' OR p.stock_name LIKE '%丝杠%' ESCAPE '\\') OR (p.industry_l1 LIKE '%灵巧手%' ESCAPE '\\' OR p.industry_l2 LIKE '%灵巧手%' ESCAPE '\\' OR p.concepts_raw LIKE '%灵巧手%' ESCAPE '\\' OR p.main_business_raw LIKE '%灵巧手%' ESCAPE '\\' OR p.stock_name LIKE '%灵巧手%' ESCAPE '\\') OR (p.industry_l1 LIKE '%传感器%' ESCAPE '\\' OR p.industry_l2 LIKE '%传感器%' ESCAPE '\\' OR p.concepts_raw LIKE '%传感器%' ESCAPE '\\' OR p.main_business_raw LIKE '%传感器%' ESCAPE '\\' OR p.stock_name LIKE '%传感器%' ESCAPE '\\') OR (p.industry_l1 LIKE '%具身智能%' ESCAPE '\\' OR p.industry_l2 LIKE '%具身智能%' ESCAPE '\\' OR p.concepts_raw LIKE '%具身智能%' ESCAPE '\\' OR p.main_business_raw LIKE '%具身智能%' ESCAPE '\\' OR p.stock_name LIKE '%具身智能%' ESCAPE '\\'))
),
price_with_prev AS (
  SELECT
    d.symbol,
    d.trade_date,
    d.open_price,
    d.high_price,
    d.low_price,
    d.close_price,
    d.volume,
    d.amount,
    LAG(d.close_price) OVER (PARTITION BY d.symbol ORDER BY d.trade_date) AS prev_close,
    AVG(d.amount) OVER (
      PARTITION BY d.symbol
      ORDER BY d.trade_date
      ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING
    ) AS avg_amount_20
  FROM a_share_daily_price d
  JOIN universe u ON u.symbol = d.symbol
  WHERE d.trade_date BETWEEN DATE_SUB('2026-04-04', INTERVAL 30 DAY) AND '2026-07-04'
),
window_rows AS (
  SELECT
    u.stock_name,
    u.industry_l1,
    u.industry_l2,
    u.concepts_raw,
    u.main_business_raw,
    p.*,
    CASE
      WHEN p.prev_close IS NULL OR p.prev_close = 0 THEN NULL
      ELSE (p.close_price / p.prev_close - 1) * 100
    END AS daily_return_pct,
    CASE
      WHEN p.avg_amount_20 IS NULL OR p.avg_amount_20 = 0 THEN NULL
      ELSE p.amount / p.avg_amount_20
    END AS amount_ratio_20
  FROM price_with_prev p
  JOIN universe u ON u.symbol = p.symbol
  WHERE p.trade_date BETWEEN '2026-04-04' AND '2026-07-04'
),
ranked AS (
  SELECT
    w.*,
    ROW_NUMBER() OVER (PARTITION BY w.symbol ORDER BY w.trade_date) AS rn_first,
    ROW_NUMBER() OVER (PARTITION BY w.symbol ORDER BY w.trade_date DESC) AS rn_last
  FROM window_rows w
),
agg AS (
  SELECT
    symbol,
    MAX(stock_name) AS company_name,
    MAX(industry_l1) AS industry_l1,
    MAX(industry_l2) AS industry_l2,
    MAX(concepts_raw) AS concepts_raw,
    MAX(main_business_raw) AS main_business_raw,
    MIN(trade_date) AS window_start,
    MAX(trade_date) AS window_end,
    MAX(CASE WHEN rn_first = 1 THEN close_price END) AS first_close,
    MAX(CASE WHEN rn_last = 1 THEN close_price END) AS last_close,
    COUNT(*) AS trading_days,
    SUM(CASE WHEN daily_return_pct >= 9.5 THEN 1 ELSE 0 END) AS strong_up_days,
    MAX(daily_return_pct) AS max_daily_return_pct,
    MAX(amount_ratio_20) AS max_amount_ratio_20,
    MAX(amount) AS max_amount
  FROM ranked
  GROUP BY symbol
),
scored AS (
  SELECT
    *,
    CASE
      WHEN first_close IS NULL OR first_close = 0 THEN NULL
      ELSE (last_close / first_close - 1) * 100
    END AS period_return_pct
  FROM agg
)
SELECT
  'SCAN-ANA-ROBOT-MARKET-GAP-20260704-001' AS scan_id,
  '机器人案例' AS industry_case,
  symbol,
  company_name,
  industry_l1,
  industry_l2,
  window_start,
  window_end,
  trading_days,
  ROUND(first_close, 4) AS first_close,
  ROUND(last_close, 4) AS last_close,
  ROUND(period_return_pct, 4) AS period_return_pct,
  strong_up_days,
  ROUND(max_daily_return_pct, 4) AS max_daily_return_pct,
  ROUND(max_amount_ratio_20, 4) AS max_amount_ratio_20,
  CASE
    WHEN period_return_pct >= 20.0 OR strong_up_days >= 1 OR max_amount_ratio_20 >= 2.0
    THEN 'STRONG_MANIFESTATION'
    ELSE 'WEAK_OR_NORMAL'
  END AS manifestation_type,
  'REVIEW_REQUIRED' AS scope_type,
  'REVIEW_REQUIRED' AS suspected_missing_track,
  'MARKET_REVERSE_GAP_SCAN' AS gap_type,
  CASE
    WHEN period_return_pct >= 20.0 OR strong_up_days >= 1 OR max_amount_ratio_20 >= 2.0
    THEN 1
    ELSE 0
  END AS supplement_required_flag,
  'Review core business against industry scheme before adding to evidence queue' AS supplement_question,
  'DRAFT_FOR_REVIEW' AS review_status,
  LEFT(concepts_raw, 500) AS concepts_excerpt,
  LEFT(main_business_raw, 500) AS main_business_excerpt
FROM scored
WHERE period_return_pct >= 20.0
   OR strong_up_days >= 1
   OR max_amount_ratio_20 >= 2.0
ORDER BY supplement_required_flag DESC, period_return_pct DESC, strong_up_days DESC, max_amount_ratio_20 DESC;