/*======================================================================
  isql_example.c - Using the Internet SQL (ISQL) engine from a C program.

  Pure C. Links the ISQL C API (isqlc.h) against isqldll.dll (or the static
  library). Demonstrates the connection lifecycle and a broad set of SQL
  commands: DDL, INSERT/SELECT/WHERE/UPDATE/DELETE, LIKE, ORDER BY, aggregates,
  indexes, and the storage-vector commands (VINSERT / VECTOR).

  Build:  examples\build_example.bat        (see doc\HOWTO_BUILD_C_APP.md)
  Run:    isql_example.exe  <path-to-NewIsqlHome>
          e.g.  isql_example.exe  C:\NewIsqlHome

  The engine runs fully in-process (no server, no network) against the
  database directory <home>\db.
 ======================================================================*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "isql.h"          /* ISQL C API umbrella (pure C) */

static KINT64 g_obj = 0;      /* current ISQL object (statement handle)   */
static int  g_pass = 0, g_fail = 0;

/* Run one SQL statement; returns the engine command code (>0 on success). */
static KINT64 sql(const char *stmt) { return ISQL_Exec(g_obj, (char*)stmt); }

/* Tally a check and print the engine status text on failure. */
static void check(int ok, const char *what)
{
    if (ok) { g_pass++; printf("  [ok]   %s\n", what); }
    else    { g_fail++; printf("  [FAIL] %s   (status: %s)\n", what, ISQL_Status(g_obj)); }
}

/* Count rows returned by the most recent SELECT (using the cursor API). */
static int count_rows(void) { int n = 0; while (ISQL_Next(g_obj)) n++; return n; }

int main(int argc, char **argv)
{
    const char *home = (argc > 1) ? argv[1] : "C:\\NewIsqlHome";
    KINT64 inst, startid;

    printf("ISQL C example - database home: %s\\db\n\n", home);

    /*---------- 1. Connect (in-process, no network) ----------*/
    inst = ISQL_CreateInternetSqlInstance();
    if (!inst) { printf("ERROR: CreateInternetSqlInstance\n"); return 2; }
    ISQL_NoNetworkMode(inst);                       /* standalone embedded  */
    ISQL_DisableMessage(inst);                       /* no GUI popups        */
    startid = ISQL_StartInternetSql(inst, (char*)home, (char*)"", (char*)"");
    if (!startid) { printf("ERROR: StartInternetSql on '%s'\n", home); return 2; }
    g_obj = ISQL_NewObject(startid);
    if (!g_obj) { printf("ERROR: NewObject\n"); return 2; }

    ISQL_Exec(g_obj, (char*)"use default;");         /* select the database  */
    ISQL_SetOwnerAccess(g_obj);                      /* embedded owner access */

    /*---------- 2. DDL ----------*/
    printf("-- DDL --\n");
    sql("drop table employees;");                    /* ignore if absent     */
    check(sql("create table employees("
              "id number(5), name char(24), dept char(12), salary number(9));") > 0,
          "CREATE TABLE employees");

    /*---------- 3. INSERT ----------*/
    printf("-- INSERT --\n");
    check(sql("insert into employees values(1,'Alice','Engineering',95000);") > 0, "INSERT row 1");
    check(sql("insert into employees values(2,'Bob','Sales',72000);")         > 0, "INSERT row 2");
    check(sql("insert into employees values(3,'Carol','Engineering',88000);") > 0, "INSERT row 3");
    check(sql("insert into employees values(4,'Dan','Sales',63000);")         > 0, "INSERT row 4");
    check(sql("insert into employees values(5,'Eve','Research',110000);")     > 0, "INSERT row 5");

    /*---------- 4. SELECT + iterate ----------*/
    printf("-- SELECT (iterate with ISQL_Next / ISQL_Data) --\n");
    sql("select * from employees;");
    {
        int n = 0;
        while (ISQL_Next(g_obj)) {
            n++;
            printf("     id=%s name=%s dept=%s salary=%s\n",
                   ISQL_Data(g_obj,(char*)"id"),   ISQL_Data(g_obj,(char*)"name"),
                   ISQL_Data(g_obj,(char*)"dept"), ISQL_Data(g_obj,(char*)"salary"));
        }
        check(n == 5, "SELECT * returned 5 rows");
    }

    /*---------- 5. WHERE ----------*/
    printf("-- WHERE --\n");
    sql("select * from employees where dept='Engineering';");
    check(count_rows() == 2, "WHERE dept='Engineering' -> 2 rows");
    sql("select * from employees where salary>80000;");
    check(count_rows() == 3, "WHERE salary>80000 -> 3 rows");

    /*---------- 6. LIKE ----------*/
    printf("-- LIKE --\n");
    sql("select * from employees where name like 'A%';");
    check(count_rows() == 1, "WHERE name LIKE 'A%' -> 1 row");

    /*---------- 7. ORDER BY ----------*/
    printf("-- ORDER BY --\n");
    sql("select * from employees order by salary;");
    {
        int n = 0; long first = -1, last = -1;
        while (ISQL_Next(g_obj)) { long s = atol(ISQL_Data(g_obj,(char*)"salary")); if (n==0) first=s; last=s; n++; }
        check(n == 5 && first <= last, "ORDER BY salary ascending");
    }

    /*---------- 8. Aggregates ----------*/
    printf("-- Aggregates --\n");
    check(sql("select count(*) from employees;") > 0, "COUNT(*)");
    check(sql("select sum(salary) from employees;") > 0, "SUM(salary)");
    check(sql("select avg(salary) from employees;") > 0, "AVG(salary)");
    check(sql("select min(salary) from employees;") > 0, "MIN(salary)");
    check(sql("select max(salary) from employees;") > 0, "MAX(salary)");

    /*---------- 9. UPDATE ----------*/
    printf("-- UPDATE --\n");
    check(sql("update employees set salary=99000 where id=1;") > 0, "UPDATE Alice salary");
    sql("select * from employees where id=1;");
    { int ok=0; while (ISQL_Next(g_obj)) if (atol(ISQL_Data(g_obj,(char*)"salary"))==99000) ok=1; check(ok, "UPDATE took effect"); }

    /*---------- 10. DELETE ----------*/
    printf("-- DELETE --\n");
    check(sql("delete from employees where id=4;") > 0, "DELETE id=4");
    sql("select * from employees;");
    check(count_rows() == 4, "4 rows remain after DELETE");

    /*---------- 11. INDEX ----------*/
    printf("-- INDEX --\n");
    {
        long r = sql("create index idx_dept on employees(dept);");
        /* pass if created now, or if it already exists from a previous run */
        int ok = (r > 0) || (strstr(ISQL_Status(g_obj), "already exists") != NULL);
        check(ok, "CREATE INDEX idx_dept (create-or-exists)");
    }
    sql("select * from employees where dept='Sales';");
    check(count_rows() == 1, "indexed WHERE dept='Sales' -> 1 row");

    /*---------- 12. Storage vectors: VINSERT / VECTOR ----------*/
    printf("-- VECTOR (storage-float nearest neighbour) --\n");
    sql("drop table vectors;");
    check(sql("create table vectors(id char(8), vec storage);") > 0, "CREATE TABLE vectors(... storage)");
    check(sql("vinsert vec into vectors values '1,0,0' , id='a';") > 0, "VINSERT a=[1,0,0]");
    check(sql("vinsert vec into vectors values '0,1,0' , id='b';") > 0, "VINSERT b=[0,1,0]");
    check(sql("vinsert vec into vectors values '0,0,1' , id='c';") > 0, "VINSERT c=[0,0,1]");
    sql("vector vec from vectors where query='1,0,0' and maxdistance=0.2;");
    {
        int n = 0; char id0[64]; id0[0] = 0;
        while (ISQL_Next(g_obj)) { if (n==0) { char *d=ISQL_Data(g_obj,(char*)"id"); strncpy(id0,d?d:"",63); id0[63]=0; } n++; }
        check(n == 1 && id0[0] == 'a', "VECTOR nearest to [1,0,0] is 'a'");
    }

    /*---------- 13. DROP ----------*/
    printf("-- DROP --\n");
    sql("drop index idx_dept on employees;");        /* clean up the index first */
    check(sql("drop table vectors;")   > 0, "DROP TABLE vectors");
    check(sql("drop table employees;") > 0, "DROP TABLE employees");

    /*---------- 14. Cleanup ----------*/
    ISQL_FreeObject(g_obj);
    ISQL_StopInternetSql(startid);

    printf("\n============================================\n");
    printf("ISQL example: %d passed, %d failed\n", g_pass, g_fail);
    printf("============================================\n");
    return g_fail == 0 ? 0 : 1;
}
