// Basic SQL tests - Section 4 (SQL-92) focused
#include "dbstruct.ch"

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

PROCEDURE Main()

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

   ? "================================================================"
   ? "  FiveSql Basic SQL Test Suite"
   ? "================================================================"
   ?

   BEGIN SEQUENCE
      SetupData()
   RECOVER
      ? "FATAL: SetupData() failed -- aborting"
      QUIT
   END SEQUENCE

   ? "--- Basic SELECT Tests ---"
   TestBasicSelect()

   ? ""
   ? "--- Section 4: SQL-92 Full Features ---"
   TestSQL92Full()

   ?
   ? "================================================================"
   ? "  Results:", LTrim(Str(s_nPass)), "/", LTrim(Str(s_nTotal)), "passed"
   ? "================================================================"

   CleanupData()
RETURN


STATIC PROCEDURE SetupData()
   LOCAL aFields

   // employees table
   aFields := { ;
      {"NAME",   "C", 30, 0}, ;
      {"DEPT",   "C", 20, 0}, ;
      {"SALARY", "N", 10, 2}, ;
      {"HIRE",   "D",  8, 0} }
   dbCreate("employees", aFields)
   USE "employees" NEW EXCLUSIVE
   APPEND BLANK ; REPLACE NAME WITH "Alice",  DEPT WITH "Engineering", SALARY WITH 8000
   APPEND BLANK ; REPLACE NAME WITH "Bob",    DEPT WITH "Sales",       SALARY WITH 5500
   APPEND BLANK ; REPLACE NAME WITH "Carol",  DEPT WITH "Engineering", SALARY WITH 7200
   APPEND BLANK ; REPLACE NAME WITH "Dave",   DEPT WITH "Marketing",   SALARY WITH 4800
   APPEND BLANK ; REPLACE NAME WITH "Eve",    DEPT WITH "Sales",       SALARY WITH 6100
   APPEND BLANK ; REPLACE NAME WITH "Frank",  DEPT WITH "Engineering", SALARY WITH 9200
   APPEND BLANK ; REPLACE NAME WITH "Grace",  DEPT WITH "Marketing",   SALARY WITH 5300
   APPEND BLANK ; REPLACE NAME WITH "Hank",   DEPT WITH "Sales",       SALARY WITH 4200
   CLOSE ALL

   // products table
   aFields := { ;
      {"PNAME",  "C", 30, 0}, ;
      {"PRICE",  "N", 10, 2}, ;
      {"CATEG",  "C", 20, 0}, ;
      {"STOCK",  "N",  6, 0} }
   dbCreate("products", aFields)
   USE "products" NEW EXCLUSIVE
   APPEND BLANK ; REPLACE PNAME WITH "Laptop",  PRICE WITH 1200, CATEG WITH "Electronics", STOCK WITH 50
   APPEND BLANK ; REPLACE PNAME WITH "Phone",   PRICE WITH 800,  CATEG WITH "Electronics", STOCK WITH 200
   APPEND BLANK ; REPLACE PNAME WITH "Desk",    PRICE WITH 350,  CATEG WITH "Furniture",   STOCK WITH 30
   APPEND BLANK ; REPLACE PNAME WITH "Chair",   PRICE WITH 150,  CATEG WITH "Furniture",   STOCK WITH 100
   APPEND BLANK ; REPLACE PNAME WITH "Monitor", PRICE WITH 400,  CATEG WITH "Electronics", STOCK WITH 75
   CLOSE ALL

RETURN


STATIC PROCEDURE TestBasicSelect()
   LOCAL aR

   // Test 1: SELECT *
   BEGIN SEQUENCE
      aR := five_SQL("SELECT * FROM employees")
      Assert("Basic SELECT *", Rows(aR) == 8)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: Basic SELECT * (exception)"
   END SEQUENCE

   // Test 2: SELECT with WHERE
   BEGIN SEQUENCE
      aR := five_SQL("SELECT name, salary FROM employees WHERE salary > 6000")
      Assert("SELECT WHERE salary>6000", Rows(aR) == 4)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: SELECT WHERE (exception)"
   END SEQUENCE

   // Test 3: SELECT with ORDER BY
   BEGIN SEQUENCE
      aR := five_SQL("SELECT name, salary FROM employees ORDER BY salary DESC")
      Assert("SELECT ORDER BY DESC", Rows(aR) == 8 .AND. CellVal(aR,1,2) >= CellVal(aR,2,2))
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: SELECT ORDER BY (exception)"
   END SEQUENCE

   // Test 4: Aggregate COUNT
   BEGIN SEQUENCE
      aR := five_SQL("SELECT COUNT(*) FROM employees")
      Assert("SELECT COUNT(*)", Rows(aR) == 1 .AND. CellVal(aR,1,1) == 8)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: COUNT(*) (exception)"
   END SEQUENCE

   // Test 5: GROUP BY with HAVING
   BEGIN SEQUENCE
      aR := five_SQL("SELECT dept, COUNT(*) AS cnt FROM employees GROUP BY dept HAVING COUNT(*) > 2")
      Assert("GROUP BY HAVING cnt>2", Rows(aR) >= 1)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: GROUP BY HAVING (exception)"
   END SEQUENCE

   // Test 6: DISTINCT
   BEGIN SEQUENCE
      aR := five_SQL("SELECT DISTINCT dept FROM employees")
      Assert("SELECT DISTINCT dept", Rows(aR) == 3)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: DISTINCT (exception)"
   END SEQUENCE

   // Test 7: LIKE
   BEGIN SEQUENCE
      aR := five_SQL("SELECT name FROM employees WHERE name LIKE 'A%'")
      Assert("SELECT LIKE 'A%'", Rows(aR) == 1)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: LIKE (exception)"
   END SEQUENCE

   // Test 8: BETWEEN
   BEGIN SEQUENCE
      aR := five_SQL("SELECT name FROM employees WHERE salary BETWEEN 5000 AND 7000")
      Assert("SELECT BETWEEN", Rows(aR) >= 2)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: BETWEEN (exception)"
   END SEQUENCE

   // Test 9: IN
   BEGIN SEQUENCE
      aR := five_SQL("SELECT name FROM employees WHERE dept IN ('Sales','Marketing')")
      Assert("SELECT IN", Rows(aR) == 5)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: IN (exception)"
   END SEQUENCE

   // Test 10: INSERT
   BEGIN SEQUENCE
      five_SQL("INSERT INTO employees (name, dept, salary) VALUES ('Test', 'QA', 3000)")
      aR := five_SQL("SELECT * FROM employees WHERE dept = 'QA'")
      Assert("INSERT VALUES", Rows(aR) == 1)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: INSERT (exception)"
   END SEQUENCE

   // Test 11: UPDATE
   BEGIN SEQUENCE
      five_SQL("UPDATE employees SET salary = 3500 WHERE name = 'Test'")
      aR := five_SQL("SELECT salary FROM employees WHERE name = 'Test'")
      Assert("UPDATE SET", Rows(aR) == 1 .AND. CellVal(aR,1,1) == 3500)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: UPDATE (exception)"
   END SEQUENCE

   // Test 12: DELETE
   BEGIN SEQUENCE
      five_SQL("DELETE FROM employees WHERE name = 'Test'")
      aR := five_SQL("SELECT * FROM employees WHERE name = 'Test'")
      Assert("DELETE FROM", Rows(aR) == 0)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: DELETE (exception)"
   END SEQUENCE

RETURN


STATIC PROCEDURE TestSQL92Full()
   LOCAL aR

   // CHECK constraint
   BEGIN SEQUENCE
      five_SQL("CREATE TABLE ck_tbl (val N(6,0) CHECK val > 0)")
      five_SQL("INSERT INTO ck_tbl (val) VALUES (10)")
      aR := five_SQL("SELECT * FROM ck_tbl")
      Assert("4a CHECK insert ok", Rows(aR) == 1)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 4a CHECK (exception)"
   END SEQUENCE

   // UNIQUE constraint
   BEGIN SEQUENCE
      five_SQL("CREATE TABLE uq_tbl (code C(10) UNIQUE)")
      five_SQL("INSERT INTO uq_tbl (code) VALUES ('ABC')")
      five_SQL("INSERT INTO uq_tbl (code) VALUES ('DEF')")
      aR := five_SQL("SELECT * FROM uq_tbl")
      Assert("4b UNIQUE insert ok", Rows(aR) == 2)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 4b UNIQUE (exception)"
   END SEQUENCE

   // TRUNCATE
   BEGIN SEQUENCE
      five_SQL("CREATE TABLE trunc_tbl (id N(4,0))")
      five_SQL("INSERT INTO trunc_tbl (id) VALUES (1)")
      five_SQL("INSERT INTO trunc_tbl (id) VALUES (2)")
      five_SQL("TRUNCATE TABLE trunc_tbl")
      aR := five_SQL("SELECT * FROM trunc_tbl")
      Assert("4c TRUNCATE", Rows(aR) == 0)
   RECOVER
      s_nTotal++ ; s_nFail++ ; ? "  FAIL: 4c TRUNCATE (exception)"
   END SEQUENCE

RETURN


STATIC PROCEDURE CleanupData()
   CLOSE ALL
   FErase("employees.dbf")
   FErase("products.dbf")
   FErase("ck_tbl.dbf")
   FErase("uq_tbl.dbf")
   FErase("trunc_tbl.dbf")
RETURN


// Helper functions
STATIC FUNCTION Rows( aResult )
   IF ValType(aResult) == "A" .AND. Len(aResult) >= 2
      RETURN Len(aResult[2])
   ENDIF
RETURN 0

STATIC FUNCTION CellVal( aResult, nRow, nCol )
   IF ValType(aResult) == "A" .AND. Len(aResult) >= 2
      IF nRow <= Len(aResult[2]) .AND. nCol <= Len(aResult[2][nRow])
         RETURN aResult[2][nRow][nCol]
      ENDIF
   ENDIF
RETURN NIL

STATIC PROCEDURE Assert( cTest, lCond )
   s_nTotal++
   IF lCond
      s_nPass++
      ? "  PASS:", cTest
   ELSE
      s_nFail++
      ? "  FAIL:", cTest
   ENDIF
RETURN