/*======================================================================
  isql_shell.c - Interactive command-line SQL shell for ISQL, on the DLL.

  A small REPL that links isqldll.dll (via isqlc.h) and lets you type SQL
  interactively against a local database. Type  help  to list commands,
  quit  or  exit  to leave.

  Build:  examples\build_shell.bat           (see doc\HOWTO_BUILD_C_APP.md)
  Run:    isql_shell.exe  <path-to-NewIsqlHome>
          e.g.  isql_shell.exe  C:\NewIsqlHome
          (isqldll.dll must be on the PATH or beside isql_shell.exe)
 ======================================================================*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "isql.h"

#define CMD_BUFSIZE 65536
#define COL_WIDTH   18

static KINT64 g_obj = 0;

static void print_help(void)
{
    printf(
"\nISQL commands (end each statement with ';'):\n"
"\n  Databases\n"
"    use <db>;                                  select a database (default: 'default')\n"
"\n  Tables (DDL)\n"
"    create table <t>(<col> <type>[, ...]);     types: number(n) char(n) storage\n"
"    drop table <t>;\n"
"    describe <t>;                              show a table's columns\n"
"    create index <ix> on <t>(<col>);\n"
"    drop index <ix> on <t>;\n"
"\n  Rows (DML)\n"
"    insert into <t> values(<v>[, ...]);\n"
"    select <cols|*> from <t> [where <cond>] [order by <col>];\n"
"    update <t> set <col>=<v> [where <cond>];\n"
"    delete from <t> [where <cond>];\n"
"    where operators:  =  <>  <  >  <=  >=  like 'p%'   and / or\n"
"    aggregates:  count(*)  sum(c)  avg(c)  min(c)  max(c)\n"
"\n  Storage vectors (AI / nearest-neighbour)\n"
"    vinsert <vcol> into <t> values '<f,f,...>' [, <col>=<v> ...];\n"
"    vector  <vcol> from <t> where query='<f,f,...>' and maxdistance=<d>;\n"
"\n  Users / access (multi-user deployments)\n"
"    add user <name>,<password>,<a|m|o>;        a=admin m=manager o=ordinary\n"
"    drop user <name>;\n"
"    login <name>,<password>;\n"
"\n  Shell\n"
"    help;      show this list\n"
"    quit;      exit          (also: exit)\n"
"\n");
}

/* Print a result set (any statement that produced fields). */
static void print_results(void)
{
    int nf = ISQL_FieldCount(g_obj);
    int f, rows = 0;
    if (nf <= 0) return;

    for (f = 1; f <= nf; f++) {
        char *name = ISQL_FieldName(g_obj, f);
        printf("%-*s", COL_WIDTH, name ? name : "?");
    }
    printf("\n");
    for (f = 1; f <= nf; f++) { int i; for (i = 0; i < COL_WIDTH-1; i++) putchar('-'); putchar(' '); }
    printf("\n");

    while (ISQL_Next(g_obj)) {
        for (f = 1; f <= nf; f++) {
            char *v = ISQL_DataByFno(g_obj, f);
            printf("%-*s", COL_WIDTH, v ? v : "");
        }
        printf("\n");
        rows++;
    }
    printf("(%d row%s)\n", rows, rows == 1 ? "" : "s");
}

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

    printf("InternetSQL shell (DLL) - database home: %s\\db\n", home);
    printf("Type 'help;' for commands, 'quit;' to exit.\n\n");

    inst = ISQL_CreateInternetSqlInstance();
    if (!inst) { fprintf(stderr, "CreateInternetSqlInstance failed\n"); return 1; }
    ISQL_NoNetworkMode(inst);
    ISQL_DisableMessage(inst);
    startid = ISQL_StartInternetSql(inst, (char*)home, (char*)"", (char*)"");
    if (!startid) { fprintf(stderr, "StartInternetSql failed on '%s'\n", home); return 1; }
    g_obj = ISQL_NewObject(startid);
    if (!g_obj) { fprintf(stderr, "NewObject failed\n"); return 1; }

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

    for (;;) {
        int len;
        const char *status;

        printf("isql> ");
        fflush(stdout);
        if (!fgets(cmd, CMD_BUFSIZE, stdin)) break;     /* EOF */

        len = (int)strlen(cmd);
        while (len > 0 && (cmd[len-1]=='\n'||cmd[len-1]=='\r'||cmd[len-1]==' '||cmd[len-1]=='\t'))
            cmd[--len] = '\0';
        if (len == 0) continue;

        if (_stricmp(cmd,"quit")==0 || _stricmp(cmd,"exit")==0 ||
            _stricmp(cmd,"quit;")==0|| _stricmp(cmd,"exit;")==0) break;
        if (_stricmp(cmd,"help")==0 || _stricmp(cmd,"help;")==0) { print_help(); continue; }

        if (cmd[len-1] != ';') { cmd[len]=';'; cmd[len+1]='\0'; }   /* auto-terminate */

        ISQL_Exec(g_obj, cmd);
        /* Print a result grid only for row-returning statements. */
        if (_strnicmp(cmd,"select",6)==0 || _strnicmp(cmd,"vector",6)==0 ||
            _strnicmp(cmd,"describe",8)==0 || _strnicmp(cmd,"list",4)==0)
            print_results();

        status = ISQL_Status(g_obj);
        if (status && status[0]) printf("%s\n", status);
    }

    ISQL_FreeObject(g_obj);
    ISQL_StopInternetSql(startid);
    printf("\nBye.\n");
    return 0;
}
