/*
 * test_sql1999_hard.prg — 10 Complex SQL:1999 Stress Tests
 *
 * Advanced queries combining multiple SQL:1999 features:
 * Recursive CTE, Window Functions, Subqueries, Aggregation, JOIN, MERGE
 *
 * Copyright (c) 2025-2026 Charles KWON (Charles KWON OhJun)
 * Email: charleskwonohjun@gmail.com
 * All rights reserved.
 */

#include "dbstruct.ch"
#include "FiveSqlDef.ch"

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

PROCEDURE Main()

   ? "================================================================"
   ? "  SQL:1999 Complex Stress Tests (10 tests)"
   ? "================================================================"
   ?

   SetupData()

   Test01_RecursiveCTE_WithLevels()
   Test02_WindowRank_TopN_PerDept()
   Test03_CTE_MultiJoin_Aggregate()
   Test04_RecursiveFibonacci_Window()
   Test05_NestedCTE_WindowLag()
   Test06_CTE_Subquery_Having()
   Test07_RecursivePowerSet()
   Test08_Window_RunningTotal_Partition()
   Test09_MultiCTE_CrossJoin_Window()
   Test10_Recursive_Hierarchy_Depth_Salary()

   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 Rows( aR )

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

RETURN 0


STATIC FUNCTION CellVal( aR, nRow, nCol )

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

RETURN NIL


STATIC FUNCTION SetupData()

   /* employees: 10 rows with hierarchy */
   FErase( "employees.dbf" )
   dbCreate( "employees.dbf", { ;
      { "ID",       "N", 10, 0 }, ;
      { "NAME",     "C", 30, 0 }, ;
      { "DEPT",     "C", 20, 0 }, ;
      { "SALARY",   "N", 12, 2 }, ;
      { "MGR_ID",   "N", 10, 0 } ;
   } )
   USE employees.dbf NEW EXCLUSIVE
   dbAppend() ; FieldPut(1,  1) ; FieldPut(2, "Alice")   ; FieldPut(3, "Engineering") ; FieldPut(4, 8000) ; FieldPut(5, 0)
   dbAppend() ; FieldPut(1,  2) ; FieldPut(2, "Bob")     ; FieldPut(3, "Engineering") ; FieldPut(4, 7000) ; FieldPut(5, 1)
   dbAppend() ; FieldPut(1,  3) ; FieldPut(2, "Charlie") ; FieldPut(3, "Engineering") ; FieldPut(4, 6000) ; FieldPut(5, 1)
   dbAppend() ; FieldPut(1,  4) ; FieldPut(2, "Diana")   ; FieldPut(3, "Sales")       ; FieldPut(4, 7500) ; FieldPut(5, 0)
   dbAppend() ; FieldPut(1,  5) ; FieldPut(2, "Eve")     ; FieldPut(3, "Sales")       ; FieldPut(4, 5000) ; FieldPut(5, 4)
   dbAppend() ; FieldPut(1,  6) ; FieldPut(2, "Frank")   ; FieldPut(3, "Sales")       ; FieldPut(4, 4500) ; FieldPut(5, 4)
   dbAppend() ; FieldPut(1,  7) ; FieldPut(2, "Grace")   ; FieldPut(3, "Marketing")   ; FieldPut(4, 6500) ; FieldPut(5, 0)
   dbAppend() ; FieldPut(1,  8) ; FieldPut(2, "Henry")   ; FieldPut(3, "Marketing")   ; FieldPut(4, 5500) ; FieldPut(5, 7)
   dbAppend() ; FieldPut(1,  9) ; FieldPut(2, "Ivy")     ; FieldPut(3, "HR")          ; FieldPut(4, 6000) ; FieldPut(5, 0)
   dbAppend() ; FieldPut(1, 10) ; FieldPut(2, "Jack")    ; FieldPut(3, "HR")          ; FieldPut(4, 5000) ; FieldPut(5, 9)
   dbCommit()
   CLOSE ALL

   /* orders: 15 rows */
   FErase( "orders.dbf" )
   dbCreate( "orders.dbf", { ;
      { "ID",         "N", 10, 0 }, ;
      { "EMP_ID",     "N", 10, 0 }, ;
      { "PRODUCT",    "C", 30, 0 }, ;
      { "AMOUNT",     "N", 12, 2 }, ;
      { "ORDER_DATE", "C", 10, 0 } ;
   } )
   USE orders.dbf NEW EXCLUSIVE
   dbAppend() ; FieldPut(1,  1) ; FieldPut(2, 1) ; FieldPut(3, "Laptop")   ; FieldPut(4, 2500)
   dbAppend() ; FieldPut(1,  2) ; FieldPut(2, 1) ; FieldPut(3, "Monitor")  ; FieldPut(4, 800)
   dbAppend() ; FieldPut(1,  3) ; FieldPut(2, 2) ; FieldPut(3, "Keyboard") ; FieldPut(4, 150)
   dbAppend() ; FieldPut(1,  4) ; FieldPut(2, 3) ; FieldPut(3, "Mouse")    ; FieldPut(4, 100)
   dbAppend() ; FieldPut(1,  5) ; FieldPut(2, 4) ; FieldPut(3, "Printer")  ; FieldPut(4, 1200)
   dbAppend() ; FieldPut(1,  6) ; FieldPut(2, 4) ; FieldPut(3, "Scanner")  ; FieldPut(4, 500)
   dbAppend() ; FieldPut(1,  7) ; FieldPut(2, 5) ; FieldPut(3, "Tablet")   ; FieldPut(4, 900)
   dbAppend() ; FieldPut(1,  8) ; FieldPut(2, 6) ; FieldPut(3, "Phone")    ; FieldPut(4, 1100)
   dbAppend() ; FieldPut(1,  9) ; FieldPut(2, 7) ; FieldPut(3, "Camera")   ; FieldPut(4, 3000)
   dbAppend() ; FieldPut(1, 10) ; FieldPut(2, 7) ; FieldPut(3, "Lens")     ; FieldPut(4, 1500)
   dbAppend() ; FieldPut(1, 11) ; FieldPut(2, 8) ; FieldPut(3, "Headset")  ; FieldPut(4, 250)
   dbAppend() ; FieldPut(1, 12) ; FieldPut(2, 9) ; FieldPut(3, "Desk")     ; FieldPut(4, 800)
   dbAppend() ; FieldPut(1, 13) ; FieldPut(2, 9) ; FieldPut(3, "Chair")    ; FieldPut(4, 600)
   dbAppend() ; FieldPut(1, 14) ; FieldPut(2,10) ; FieldPut(3, "Lamp")     ; FieldPut(4, 200)
   dbAppend() ; FieldPut(1, 15) ; FieldPut(2, 2) ; FieldPut(3, "Webcam")   ; FieldPut(4, 350)
   dbCommit()
   CLOSE ALL

RETURN NIL


STATIC FUNCTION CleanupData()

   dbCloseAll()
   FErase( "employees.dbf" )
   FErase( "orders.dbf" )
   FErase( "__cte_org.dbf" )
   FErase( "__cte_nums.dbf" )
   FErase( "__cte_fib.dbf" )
   FErase( "__cte_ranked.dbf" )
   FErase( "__cte_dept_stats.dbf" )
   FErase( "__cte_order_totals.dbf" )
   FErase( "__cte_top_emps.dbf" )
   FErase( "__cte_dept_summary.dbf" )
   FErase( "__cte_emp_orders.dbf" )
   FErase( "__cte_powers.dbf" )

RETURN NIL


/* ====================================================================
 * Test 1: Recursive CTE with JOIN — org hierarchy with level numbers
 * Combines: WITH RECURSIVE, JOIN, ORDER BY
 * ==================================================================== */
STATIC PROCEDURE Test01_RecursiveCTE_WithLevels()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE org AS (" + ;
         "SELECT id, name, 1 AS lvl FROM employees WHERE mgr_id = 0 " + ;
         "UNION ALL " + ;
         "SELECT e.id, e.name, o.lvl + 1 FROM employees e JOIN org o ON e.mgr_id = o.id" + ;
         ") SELECT name, lvl FROM org ORDER BY lvl, name" )
      Assert( "1. Recursive CTE + JOIN: org hierarchy 10 rows, lvl 1 first", ;
         Rows( aR ) == 10 .AND. CellVal( aR, 1, 2 ) == 1 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 1. Recursive CTE org hierarchy (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 2: Window RANK — Top 2 earners per department
 * Combines: CTE, ROW_NUMBER() OVER PARTITION BY, WHERE on rank
 * ==================================================================== */
STATIC PROCEDURE Test02_WindowRank_TopN_PerDept()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH ranked AS (" + ;
         "SELECT name, dept, salary, " + ;
         "ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn " + ;
         "FROM employees" + ;
         ") SELECT name, dept, salary FROM ranked WHERE rn <= 2 ORDER BY dept, salary DESC" )
      /* 4 depts, 2 each = 8 rows */
      Assert( "2. Window RANK top 2/dept: 8 rows", ;
         Rows( aR ) == 8 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 2. Window RANK top 2/dept (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 3: CTE + Multi-table JOIN + Aggregate
 * Combines: CTE with GROUP BY SUM, JOIN back to employees, WHERE filter
 * ==================================================================== */
STATIC PROCEDURE Test03_CTE_MultiJoin_Aggregate()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH order_totals AS (" + ;
         "SELECT emp_id, SUM(amount) AS total, COUNT(*) AS cnt " + ;
         "FROM orders GROUP BY emp_id" + ;
         ") SELECT e.name, e.dept, t.total, t.cnt " + ;
         "FROM employees e JOIN order_totals t ON e.id = t.emp_id " + ;
         "WHERE t.total > 500 ORDER BY t.total DESC" )
      Assert( "3. CTE+JOIN+Agg: employees with orders>500", ;
         Rows( aR ) >= 5 .AND. CellVal( aR, 1, 3 ) >= 500 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 3. CTE+JOIN+Agg (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 4: Recursive Fibonacci + Window to add row numbers
 * Combines: WITH RECURSIVE (3 columns), ROW_NUMBER() on CTE result
 * ==================================================================== */
STATIC PROCEDURE Test04_RecursiveFibonacci_Window()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE fib AS (" + ;
         "SELECT 1 AS n, 1 AS a, 0 AS b " + ;
         "UNION ALL " + ;
         "SELECT n + 1, a + b, a FROM fib WHERE n < 10" + ;
         ") SELECT n, a FROM fib" )
      /* 10 rows: n=1..10, a = 1,1,2,3,5,8,13,21,34,55 */
      Assert( "4. Recursive Fibonacci: 10 rows, fib(10)=55", ;
         Rows( aR ) == 10 .AND. CellVal( aR, 10, 2 ) == 55 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 4. Recursive Fibonacci (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 5: Nested CTE + Window LAG — salary change from previous
 * Combines: CTE producing sorted list, LAG() on that list
 * ==================================================================== */
STATIC PROCEDURE Test05_NestedCTE_WindowLag()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, salary, " + ;
         "LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary " + ;
         "FROM employees ORDER BY salary DESC" )
      /* First row's prev_salary should be NULL/0 (no previous) */
      Assert( "5. Window LAG: 10 rows, first has no prev", ;
         Rows( aR ) == 10 .AND. ;
         ( CellVal( aR, 1, 3 ) == NIL .OR. CellVal( aR, 1, 3 ) == 0 ) )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 5. Window LAG (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 6: CTE + Subquery in WHERE + Aggregation
 * Combines: CTE, subquery, COUNT, GROUP BY
 * ==================================================================== */
STATIC PROCEDURE Test06_CTE_Subquery_Having()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT dept, COUNT(*) AS cnt, AVG(salary) AS avg_sal " + ;
         "FROM employees " + ;
         "WHERE id IN (SELECT emp_id FROM orders) " + ;
         "GROUP BY dept ORDER BY avg_sal DESC" )
      Assert( "6. Subquery+GROUP BY: depts of employees with orders", ;
         Rows( aR ) >= 2 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 6. Subquery+GROUP BY (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 7: Recursive powers of 2 with termination
 * Combines: WITH RECURSIVE, computed columns
 * ==================================================================== */
STATIC PROCEDURE Test07_RecursivePowerSet()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE powers AS (" + ;
         "SELECT 0 AS n, 1 AS val " + ;
         "UNION ALL " + ;
         "SELECT n + 1, val * 2 FROM powers WHERE n < 15" + ;
         ") SELECT n, val FROM powers" )
      /* 16 rows: 2^0=1, 2^1=2, ..., 2^15=32768 */
      Assert( "7. Recursive powers of 2: 16 rows, 2^15=32768", ;
         Rows( aR ) == 16 .AND. CellVal( aR, 16, 2 ) == 32768 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 7. Recursive powers (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 8: Window running total partitioned by dept
 * Combines: SUM() OVER PARTITION BY ... ORDER BY
 * ==================================================================== */
STATIC PROCEDURE Test08_Window_RunningTotal_Partition()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "SELECT name, dept, salary, " + ;
         "SUM(salary) OVER (PARTITION BY dept ORDER BY salary) AS running_total " + ;
         "FROM employees ORDER BY dept, salary" )
      Assert( "8. Running SUM by dept: 10 rows", ;
         Rows( aR ) == 10 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 8. Window running total (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 9: Multiple CTEs + Window DENSE_RANK
 * Combines: 2 CTEs, DENSE_RANK, ORDER BY rank
 * ==================================================================== */
STATIC PROCEDURE Test09_MultiCTE_CrossJoin_Window()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH dept_stats AS (" + ;
         "SELECT dept, COUNT(*) AS cnt, SUM(salary) AS total " + ;
         "FROM employees GROUP BY dept" + ;
         ") SELECT dept, cnt, total, " + ;
         "DENSE_RANK() OVER (ORDER BY total DESC) AS rnk " + ;
         "FROM dept_stats ORDER BY rnk" )
      /* 4 departments ranked by total salary */
      Assert( "9. Multi-CTE + DENSE_RANK: 4 depts ranked", ;
         Rows( aR ) == 4 .AND. CellVal( aR, 1, 4 ) == 1 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 9. Multi-CTE + DENSE_RANK (exception)"
   END SEQUENCE

RETURN


/* ====================================================================
 * Test 10: Recursive org hierarchy + department salary totals
 * Combines: WITH RECURSIVE + JOIN, computed depth, final aggregation
 * ==================================================================== */
STATIC PROCEDURE Test10_Recursive_Hierarchy_Depth_Salary()

   LOCAL aR

   dbCloseAll()
   BEGIN SEQUENCE
      aR := five_SQL( ;
         "WITH RECURSIVE org AS (" + ;
         "SELECT id, name, salary, 1 AS depth FROM employees WHERE mgr_id = 0 " + ;
         "UNION ALL " + ;
         "SELECT e.id, e.name, e.salary, o.depth + 1 " + ;
         "FROM employees e JOIN org o ON e.mgr_id = o.id" + ;
         ") SELECT name, salary, depth FROM org ORDER BY depth, name" )
      /* 10 employees, depth 1 = top-level managers (4), depth 2+ = reports */
      Assert( "10. Recursive hierarchy+salary: 10 rows, depth 1 first", ;
         Rows( aR ) == 10 .AND. CellVal( aR, 1, 3 ) == 1 .AND. ;
         CellVal( aR, 10, 3 ) >= 2 )
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 10. Recursive hierarchy+salary (exception)"
   END SEQUENCE

RETURN