/*
 * bench_sql.prg — FiveSql2 SQL execution benchmark
 * Measures actual query execution time across various SQL patterns.
 *
 * Copyright (c) 2025-2026 Charles KWON (Charles KWON OhJun)
 * All rights reserved.
 */

#include "FiveSqlDef.ch"

#define ITERS  1000

PROCEDURE Main()

   LOCAL aR, t0, t1, i, nRows

   ErrorBlock( {|e| QOut( "TRAP: " + e:description + " " + e:operation ), Break(e) } )

   ? "================================================================"
   ? "  FiveSql2 SQL Execution Benchmark"
   ? "  Iterations per query: " + hb_ntos( ITERS )
   ? "================================================================"
   ?

   /* Setup: create test tables */
   SetupBenchData()

   /* Opt in to workarea cache — repeated DML against the same table
    * skips dbUseArea/dbCloseArea syscalls.  Disabled at cleanup. */
   SqlWACacheEnable()

   ? "--- SELECT Benchmarks ---"

   /* B1: Simple SELECT * (full scan) */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT * FROM bench_emp" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B1_SELECT_STAR", t1 - t0, nRows )

   /* B2: SELECT with WHERE (filter) */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT name, salary FROM bench_emp WHERE salary > 50000" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B2_WHERE_FILTER", t1 - t0, nRows )

   /* B3: SELECT with ORDER BY */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT name, salary FROM bench_emp ORDER BY salary DESC" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B3_ORDER_BY", t1 - t0, 0 )

   /* B4: GROUP BY + HAVING */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT dept, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM bench_emp GROUP BY dept HAVING COUNT(*) > 1" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B4_GROUP_HAVING", t1 - t0, nRows )

   /* B5: DISTINCT */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT DISTINCT dept FROM bench_emp" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B5_DISTINCT", t1 - t0, nRows )

   ? ""
   ? "--- JOIN Benchmarks ---"

   /* B6: INNER JOIN */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT e.name, o.product, o.amount FROM bench_emp e JOIN bench_ord o ON e.id = o.emp_id" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B6_INNER_JOIN", t1 - t0, nRows )

   ? ""
   ? "--- CTE Benchmarks ---"

   /* B7: Simple CTE */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "WITH top_emp AS (SELECT name, salary FROM bench_emp WHERE salary > 60000) SELECT * FROM top_emp ORDER BY salary DESC" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B7_CTE_SIMPLE", t1 - t0, 0 )

   /* B8: RECURSIVE CTE (sequence 1..20) */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 20) SELECT n FROM seq" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B8_RECURSIVE_CTE", t1 - t0, nRows )

   ? ""
   ? "--- Window Function Benchmarks ---"

   /* B9: ROW_NUMBER() */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM bench_emp" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B9_ROW_NUMBER", t1 - t0, 0 )

   /* B10: RANK() PARTITION BY */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM bench_emp" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B10_RANK_PART", t1 - t0, 0 )

   /* B11: SUM() OVER PARTITION */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT name, dept, salary, SUM(salary) OVER (PARTITION BY dept) AS dept_total FROM bench_emp" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B11_SUM_OVER", t1 - t0, 0 )

   ? ""
   ? "--- DML Benchmarks ---"

   /* B12: INSERT (1000 inserts) */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      five_SQL( "INSERT INTO bench_tmp (id, val) VALUES (" + hb_ntos( i ) + ", 'test')" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B12_INSERT", t1 - t0, ITERS )

   /* B13: UPDATE */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      five_SQL( "UPDATE bench_emp SET salary = salary + 1 WHERE id = 1" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B13_UPDATE", t1 - t0, 0 )

   ? ""
   ? "--- Aggregate Benchmarks ---"

   /* B14: COUNT(*) */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "SELECT COUNT(*) AS cnt FROM bench_emp" )
   NEXT
   t1 := hb_MilliSeconds()
   R( "B14_COUNT", t1 - t0, 0 )

   /* B15: Complex: CTE + Window + WHERE */
   t0 := hb_MilliSeconds()
   FOR i := 1 TO ITERS
      aR := five_SQL( "WITH dept_stats AS (SELECT dept, AVG(salary) AS avg_sal FROM bench_emp GROUP BY dept) SELECT e.name, e.salary, d.avg_sal, ROW_NUMBER() OVER (PARTITION BY e.dept ORDER BY e.salary DESC) AS rn FROM bench_emp e JOIN dept_stats d ON e.dept = d.dept WHERE e.salary > d.avg_sal" )
   NEXT
   t1 := hb_MilliSeconds()
   nRows := 0
   IF ValType( aR ) == "A" .AND. Len( aR ) >= 2
      nRows := Len( aR[ 2 ] )
   ENDIF
   R( "B15_CTE_WIN_JOIN", t1 - t0, nRows )

   ? ""
   ? "================================================================"
   ? "  Benchmark Complete"
   ? "================================================================"

   /* Cleanup — dbCloseAll flushes + closes every workarea. */
   SqlWACacheDisable()
   dbCloseAll()
   FErase( "bench_emp.dbf" )
   FErase( "bench_ord.dbf" )
   FErase( "bench_tmp.dbf" )
   FErase( "__cte_top_emp.dbf" )
   FErase( "__cte_dept_stats.dbf" )
   FErase( "__cte_seq.dbf" )

RETURN


STATIC FUNCTION SetupBenchData()

   LOCAL i, aDepts, nIdx

   aDepts := { "Engineering", "Sales", "Marketing", "HR", "Finance" }

   /* bench_emp: 100 employees */
   IF hb_FileExists( "bench_emp.dbf" )
      FErase( "bench_emp.dbf" )
   ENDIF
   dbCreate( "bench_emp.dbf", { ;
      { "ID",     "N", 10, 0 }, ;
      { "NAME",   "C", 30, 0 }, ;
      { "DEPT",   "C", 20, 0 }, ;
      { "SALARY", "N", 12, 2 } ;
   } )
   USE bench_emp.dbf NEW EXCLUSIVE
   FOR i := 1 TO 100
      dbAppend()
      FieldPut( 1, i )
      FieldPut( 2, "Employee_" + PadL( hb_ntos( i ), 3, "0" ) )
      nIdx := ( ( i - 1 ) % 5 ) + 1
      FieldPut( 3, aDepts[ nIdx ] )
      FieldPut( 4, 30000 + i * 500 )
   NEXT
   dbCommit()
   CLOSE bench_emp

   /* bench_ord: 200 orders */
   IF hb_FileExists( "bench_ord.dbf" )
      FErase( "bench_ord.dbf" )
   ENDIF
   dbCreate( "bench_ord.dbf", { ;
      { "ID",      "N", 10, 0 }, ;
      { "EMP_ID",  "N", 10, 0 }, ;
      { "PRODUCT", "C", 30, 0 }, ;
      { "AMOUNT",  "N", 12, 2 } ;
   } )
   USE bench_ord.dbf NEW EXCLUSIVE
   FOR i := 1 TO 200
      dbAppend()
      FieldPut( 1, i )
      FieldPut( 2, ( ( i - 1 ) % 100 ) + 1 )
      FieldPut( 3, "Product_" + PadL( hb_ntos( i % 20 ), 3, "0" ) )
      FieldPut( 4, 100 + i * 10 )
   NEXT
   dbCommit()
   CLOSE bench_ord

   /* bench_tmp: empty table for INSERT bench */
   IF hb_FileExists( "bench_tmp.dbf" )
      FErase( "bench_tmp.dbf" )
   ENDIF
   dbCreate( "bench_tmp.dbf", { ;
      { "ID",  "N", 10, 0 }, ;
      { "VAL", "C", 20, 0 } ;
   } )

RETURN NIL


STATIC FUNCTION R( cLabel, nMs, nRows )

   LOCAL cRow := ""
   IF nRows > 0
      cRow := " rows=" + hb_ntos( nRows )
   ENDIF
   ? "  " + PadR( cLabel, 20 ) + Str( nMs, 8 ) + " ms  (" + ;
     Str( nMs * 1000 / ITERS, 8, 1 ) + " us/query)" + cRow

RETURN NIL