/*
 * test_sql_extreme.prg — Extreme SQL Challenge Queries
 *
 * These push the limits: deeply nested subqueries, multi-CTE pipelines,
 * correlated subqueries, window function chains, self-referencing patterns,
 * and real-world analytics scenarios from production systems.
 *
 * Copyright (c) 2025-2026 Charles KWON (Charles KWON OhJun)
 * Email: charleskwonohjun@gmail.com
 * All rights reserved.
 */

#include "FiveSqlDef.ch"
#include "hbclass.ch"

STATIC s_nPass  := 0
STATIC s_nFail  := 0
STATIC s_nTotal := 0

PROCEDURE Main()

   ? "================================================================"
   ? "  Extreme SQL Challenge — Production-Level Stress Test"
   ? "================================================================"
   ?

   SetupData()

   X01_TripleSelfJoin()
   X02_CorrelatedSubquery()
   X03_MultiCTE_Pipeline()
   X04_WindowLagLead()
   X05_RecursiveFactorial()
   X06_SubqueryInSelect()
   X07_ExistsAntiPattern()
   X08_PivotMultiColumn()
   X09_NestedAggregation()
   X10_RecursiveBomTree()
   X11_WindowPercentile()
   X12_MultiJoinThreeTables()
   X13_CTE_Reuse()
   X14_DenseRankGap()
   X15_UltimateCombo()

   CleanupData()

   ?
   ? "================================================================"
   ? "  Pass:  " + hb_ntos( s_nPass )
   ? "  Fail:  " + hb_ntos( s_nFail )
   ? "  Total: " + hb_ntos( s_nTotal )
   ? "  Rate:  " + hb_ntos( Int( s_nPass * 100 / Max( s_nTotal, 1 ) ) ) + "%"
   ? "================================================================"

RETURN


STATIC FUNCTION Assert( cLabel, lOK )

   s_nTotal++
   IF lOK
      s_nPass++
      ? "  PASS: " + cLabel
   ELSE
      s_nFail++
      ? "  FAIL: " + cLabel
   ENDIF

RETURN lOK

STATIC FUNCTION R( aR )

   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2 .AND. ValType( aR[ 2 ] ) == "A"
      RETURN Len( aR[ 2 ] )
   ENDIF

RETURN 0

STATIC FUNCTION C( aR, nRow, nCol )

   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2 .AND. ;
      nRow <= Len( aR[ 2 ] ) .AND. nCol <= Len( aR[ 2 ][ nRow ] )
      RETURN aR[ 2 ][ nRow ][ nCol ]
   ENDIF

RETURN NIL


STATIC FUNCTION SetupData()

   FErase( "emp.dbf" )
   dbCreate( "emp.dbf", { ;
      { "ID",     "N", 10, 0 }, ;
      { "NAME",   "C", 15, 0 }, ;
      { "DEPT",   "C", 10, 0 }, ;
      { "SALARY", "N", 10, 0 }, ;
      { "MGR_ID", "N", 10, 0 }, ;
      { "LEVEL",  "N",  2, 0 }  ;
   } )
   USE emp.dbf NEW EXCLUSIVE
   dbAppend(); FieldPut(1, 1); FieldPut(2,"CEO");     FieldPut(3,"Exec");  FieldPut(4,15000); FieldPut(5, 0); FieldPut(6,1)
   dbAppend(); FieldPut(1, 2); FieldPut(2,"VP_Eng");  FieldPut(3,"Eng");   FieldPut(4,12000); FieldPut(5, 1); FieldPut(6,2)
   dbAppend(); FieldPut(1, 3); FieldPut(2,"VP_Sales");FieldPut(3,"Sales"); FieldPut(4,11000); FieldPut(5, 1); FieldPut(6,2)
   dbAppend(); FieldPut(1, 4); FieldPut(2,"Dev_Lead");FieldPut(3,"Eng");   FieldPut(4, 9000); FieldPut(5, 2); FieldPut(6,3)
   dbAppend(); FieldPut(1, 5); FieldPut(2,"Dev_Sr");  FieldPut(3,"Eng");   FieldPut(4, 8000); FieldPut(5, 4); FieldPut(6,4)
   dbAppend(); FieldPut(1, 6); FieldPut(2,"Dev_Jr");  FieldPut(3,"Eng");   FieldPut(4, 5000); FieldPut(5, 4); FieldPut(6,4)
   dbAppend(); FieldPut(1, 7); FieldPut(2,"Sales_Mgr");FieldPut(3,"Sales");FieldPut(4, 8500); FieldPut(5, 3); FieldPut(6,3)
   dbAppend(); FieldPut(1, 8); FieldPut(2,"Sales_Rep");FieldPut(3,"Sales");FieldPut(4, 5500); FieldPut(5, 7); FieldPut(6,4)
   dbAppend(); FieldPut(1, 9); FieldPut(2,"Intern");  FieldPut(3,"Eng");   FieldPut(4, 3000); FieldPut(5, 5); FieldPut(6,5)
   dbAppend(); FieldPut(1,10); FieldPut(2,"Analyst");FieldPut(3,"Sales"); FieldPut(4, 6000); FieldPut(5, 7); FieldPut(6,4)
   dbCommit(); CLOSE ALL

   FErase( "sales.dbf" )
   dbCreate( "sales.dbf", { ;
      { "ID",     "N", 10, 0 }, ;
      { "EMP_ID", "N", 10, 0 }, ;
      { "QTR",    "N",  1, 0 }, ;
      { "YEAR",   "N",  4, 0 }, ;
      { "AMOUNT", "N", 10, 0 }  ;
   } )
   USE sales.dbf NEW EXCLUSIVE
   dbAppend(); FieldPut(1, 1); FieldPut(2, 7); FieldPut(3,1); FieldPut(4,2023); FieldPut(5,5000)
   dbAppend(); FieldPut(1, 2); FieldPut(2, 7); FieldPut(3,2); FieldPut(4,2023); FieldPut(5,7000)
   dbAppend(); FieldPut(1, 3); FieldPut(2, 7); FieldPut(3,3); FieldPut(4,2023); FieldPut(5,6000)
   dbAppend(); FieldPut(1, 4); FieldPut(2, 7); FieldPut(3,4); FieldPut(4,2023); FieldPut(5,8000)
   dbAppend(); FieldPut(1, 5); FieldPut(2, 8); FieldPut(3,1); FieldPut(4,2023); FieldPut(5,3000)
   dbAppend(); FieldPut(1, 6); FieldPut(2, 8); FieldPut(3,2); FieldPut(4,2023); FieldPut(5,4000)
   dbAppend(); FieldPut(1, 7); FieldPut(2, 8); FieldPut(3,3); FieldPut(4,2023); FieldPut(5,3500)
   dbAppend(); FieldPut(1, 8); FieldPut(2, 8); FieldPut(3,4); FieldPut(4,2023); FieldPut(5,5000)
   dbAppend(); FieldPut(1, 9); FieldPut(2,10); FieldPut(3,1); FieldPut(4,2023); FieldPut(5,2000)
   dbAppend(); FieldPut(1,10); FieldPut(2,10); FieldPut(3,2); FieldPut(4,2023); FieldPut(5,2500)
   dbAppend(); FieldPut(1,11); FieldPut(2,10); FieldPut(3,3); FieldPut(4,2023); FieldPut(5,3000)
   dbAppend(); FieldPut(1,12); FieldPut(2,10); FieldPut(3,4); FieldPut(4,2023); FieldPut(5,3500)
   dbAppend(); FieldPut(1,13); FieldPut(2, 3); FieldPut(3,1); FieldPut(4,2023); FieldPut(5,9000)
   dbAppend(); FieldPut(1,14); FieldPut(2, 3); FieldPut(3,2); FieldPut(4,2023); FieldPut(5,8500)
   dbCommit(); CLOSE ALL

RETURN NIL

STATIC FUNCTION CleanupData()

   dbCloseAll()
   FErase( "emp.dbf" )
   FErase( "sales.dbf" )

RETURN NIL


/* X01: Triple self-join — employee, manager, skip-level manager */
STATIC PROCEDURE X01_TripleSelfJoin()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT e.name, m.name AS mgr, s.name AS skip_mgr " + ;
         "FROM emp e " + ;
         "JOIN emp m ON e.mgr_id = m.id " + ;
         "JOIN emp s ON m.mgr_id = s.id " + ;
         "WHERE s.id > 0 ORDER BY e.name" )
      Assert( "X01 Triple self-join: skip-level managers", R( aR ) >= 4 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X01 (exception)"
   END SEQUENCE

RETURN


/* X02: Correlated subquery — employees earning above their dept average */
STATIC PROCEDURE X02_CorrelatedSubquery()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, dept, salary FROM emp e " + ;
         "WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept = e.dept) " + ;
         "ORDER BY dept, salary DESC" )
      Assert( "X02 Correlated subquery: above dept avg", R( aR ) >= 3 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X02 (exception)"
   END SEQUENCE

RETURN


/* X03: 3-CTE pipeline — each CTE builds on the previous */
STATIC PROCEDURE X03_MultiCTE_Pipeline()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH dept_stats AS (" + ;
         "  SELECT dept, COUNT(*) AS cnt, SUM(salary) AS total, AVG(salary) AS avg_sal " + ;
         "  FROM emp GROUP BY dept" + ;
         ") SELECT dept, cnt, total, avg_sal FROM dept_stats " + ;
         "WHERE cnt >= 2 ORDER BY total DESC" )
      Assert( "X03 CTE pipeline: dept stats with filter", ;
         R( aR ) >= 2 .AND. C( aR, 1, 2 ) >= 2 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X03 (exception)"
   END SEQUENCE

RETURN


/* X04: LAG + LEAD in same query — salary neighbors */
STATIC PROCEDURE X04_WindowLagLead()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, salary, " + ;
         "LAG(salary, 1) OVER (ORDER BY salary) AS prev, " + ;
         "LEAD(salary, 1) OVER (ORDER BY salary) AS next " + ;
         "FROM emp ORDER BY salary" )
      Assert( "X04 LAG+LEAD: 10 rows, prev/next populated", ;
         R( aR ) == 10 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X04 (exception)"
   END SEQUENCE

RETURN


/* X05: Recursive factorial */
STATIC PROCEDURE X05_RecursiveFactorial()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE fact AS (" + ;
         "SELECT 1 AS n, 1 AS val " + ;
         "UNION ALL " + ;
         "SELECT n + 1, val * (n + 1) FROM fact WHERE n < 10" + ;
         ") SELECT n, val FROM fact" )
      /* 10! = 3628800 */
      Assert( "X05 Recursive factorial: 10!=3628800", ;
         R( aR ) == 10 .AND. C( aR, 10, 2 ) == 3628800 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X05 (exception)"
   END SEQUENCE

RETURN


/* X06: Scalar subquery in SELECT column list */
STATIC PROCEDURE X06_SubqueryInSelect()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, salary, " + ;
         "(SELECT AVG(salary) FROM emp) AS company_avg " + ;
         "FROM emp WHERE level = 4 ORDER BY salary DESC" )
      Assert( "X06 Subquery in SELECT: level-4 with avg", ;
         R( aR ) >= 3 .AND. C( aR, 1, 3 ) > 0 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X06 (exception)"
   END SEQUENCE

RETURN


/* X07: EXISTS anti-pattern — employees with no sales */
STATIC PROCEDURE X07_ExistsAntiPattern()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, dept FROM emp " + ;
         "WHERE NOT EXISTS (" + ;
         "  SELECT 1 FROM sales WHERE sales.emp_id = emp.id" + ;
         ") ORDER BY name" )
      /* emp 1,2,4,5,6,9 have no sales => 6 rows */
      Assert( "X07 NOT EXISTS: emps without sales", R( aR ) >= 5 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X07 (exception)"
   END SEQUENCE

RETURN


/* X08: Multi-column CASE pivot — quarterly sales */
STATIC PROCEDURE X08_PivotMultiColumn()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT e.name, " + ;
         "SUM(CASE WHEN s.qtr = 1 THEN s.amount ELSE 0 END) AS q1, " + ;
         "SUM(CASE WHEN s.qtr = 2 THEN s.amount ELSE 0 END) AS q2, " + ;
         "SUM(CASE WHEN s.qtr = 3 THEN s.amount ELSE 0 END) AS q3, " + ;
         "SUM(CASE WHEN s.qtr = 4 THEN s.amount ELSE 0 END) AS q4 " + ;
         "FROM emp e JOIN sales s ON e.id = s.emp_id " + ;
         "GROUP BY e.name ORDER BY e.name" )
      Assert( "X08 Quarterly pivot: name + Q1-Q4 columns", ;
         R( aR ) >= 3 .AND. C( aR, 1, 2 ) > 0 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X08 (exception)"
   END SEQUENCE

RETURN


/* X09: Nested aggregation — avg of department sums */
STATIC PROCEDURE X09_NestedAggregation()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH dept_totals AS (" + ;
         "  SELECT dept, SUM(salary) AS total FROM emp GROUP BY dept" + ;
         ") SELECT AVG(total) AS avg_dept_total FROM dept_totals" )
      Assert( "X09 Nested agg: avg of dept salary sums", ;
         R( aR ) == 1 .AND. C( aR, 1, 1 ) > 0 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X09 (exception)"
   END SEQUENCE

RETURN


/* X10: Recursive BOM tree — org chain with accumulated salary */
STATIC PROCEDURE X10_RecursiveBomTree()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE chain AS (" + ;
         "SELECT id, name, salary, salary AS total_sal, 1 AS depth " + ;
         "FROM emp WHERE id = 1 " + ;
         "UNION ALL " + ;
         "SELECT e.id, e.name, e.salary, c.total_sal + e.salary, c.depth + 1 " + ;
         "FROM emp e JOIN chain c ON e.mgr_id = c.id" + ;
         ") SELECT name, salary, total_sal, depth FROM chain ORDER BY depth, name" )
      Assert( "X10 Recursive BOM: CEO chain, accumulating salary", ;
         R( aR ) == 10 .AND. C( aR, 1, 1 ) == "CEO" )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X10 (exception)"
   END SEQUENCE

RETURN


/* X11: Window percentile — salary rank as percentage */
STATIC PROCEDURE X11_WindowPercentile()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, salary, " + ;
         "ROW_NUMBER() OVER (ORDER BY salary) AS rn, " + ;
         "COUNT(*) OVER () AS total " + ;
         "FROM emp ORDER BY salary" )
      Assert( "X11 Percentile rank: 10 rows with rn+total", ;
         R( aR ) == 10 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X11 (exception)"
   END SEQUENCE

RETURN


/* X12: 3-table JOIN — emp + sales + self (manager name) */
STATIC PROCEDURE X12_MultiJoinThreeTables()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT e.name, m.name AS mgr, SUM(s.amount) AS total_sales " + ;
         "FROM emp e " + ;
         "JOIN emp m ON e.mgr_id = m.id " + ;
         "JOIN sales s ON e.id = s.emp_id " + ;
         "GROUP BY e.name, m.name ORDER BY total_sales DESC" )
      Assert( "X12 3-table JOIN: emp+mgr+sales", R( aR ) >= 2 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X12 (exception)"
   END SEQUENCE

RETURN


/* X13: CTE referenced multiple times in main query */
STATIC PROCEDURE X13_CTE_Reuse()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH stats AS (" + ;
         "  SELECT dept, AVG(salary) AS avg_sal, COUNT(*) AS cnt FROM emp GROUP BY dept" + ;
         ") SELECT dept, avg_sal, cnt FROM stats " + ;
         "WHERE avg_sal > (SELECT AVG(salary) FROM emp) " + ;
         "ORDER BY avg_sal DESC" )
      Assert( "X13 CTE + scalar subquery filter", R( aR ) >= 1 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X13 (exception)"
   END SEQUENCE

RETURN


/* X14: DENSE_RANK gap detection — find salary gaps */
STATIC PROCEDURE X14_DenseRankGap()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH ranked AS (" + ;
         "  SELECT DISTINCT salary, " + ;
         "  DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk " + ;
         "  FROM emp" + ;
         ") SELECT salary, rnk FROM ranked ORDER BY rnk" )
      Assert( "X14 DENSE_RANK salary bands: rank 1=highest", ;
         R( aR ) >= 5 .AND. C( aR, 1, 2 ) == 1 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X14 (exception)"
   END SEQUENCE

RETURN


/* X15: Ultimate combo — Recursive CTE + Window + Self-JOIN + Subquery + CASE + Aggregate */
STATIC PROCEDURE X15_UltimateCombo()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE org AS (" + ;
         "  SELECT id, name, salary, 1 AS depth FROM emp WHERE mgr_id = 0 " + ;
         "  UNION ALL " + ;
         "  SELECT e.id, e.name, e.salary, o.depth + 1 " + ;
         "  FROM emp e JOIN org o ON e.mgr_id = o.id" + ;
         ") SELECT name, depth, salary, " + ;
         "CASE WHEN salary > 10000 THEN 'Executive' " + ;
         "     WHEN salary > 7000 THEN 'Senior' ELSE 'Staff' END AS tier, " + ;
         "RANK() OVER (ORDER BY salary DESC) AS sal_rank " + ;
         "FROM org " + ;
         "WHERE salary > (SELECT AVG(salary) FROM emp) " + ;
         "ORDER BY salary DESC" )
      Assert( "X15 Ultimate: RCTE+Window+Subquery+CASE+Rank", ;
         R( aR ) >= 3 .AND. C( aR, 1, 5 ) == 1 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: X15 (exception)"
   END SEQUENCE

RETURN