/* useradd.c
 *
 * This program adds a user to the sthumb database.
 * Specifically, this program will add the following
 * user inputed values for the following fields:
 *   -lastname
 *   -firstname
 *   -time_in
 *   -time_out
 *
 * Kentaro Innes
 *
 * To run: Type adduser <characteristic file name>
 */

#include <stdio.h>
#include "libpq-fe.h"

#define SIZE 160            /* max size of sql command string */
#define DBNAME "ss99"       /* name of database */
#define TBLNAME "user2"     /* name of table in database */

int get_user_info(char *lname, char *fname, char *in, char *out)
{
  char line[40];
  char reply[26];

  printf("User Information\n");
  printf("----------------\n");
  printf("Enter lastname =>");
  fgets(line, sizeof(line), stdin);
  sscanf(line, "%s", lname);
  printf("\n");
  printf("Enter firstname =>");
  fgets(line, sizeof(line), stdin);
  sscanf(line, "%s", fname);
  printf("\n");
  printf("Enter time from which user may enter =>");
  fgets(line, sizeof(line), stdin);
  sscanf(line, "%s", in);
  printf("\n");
  printf("Enter time from which user may not enter =>");
  fgets(line, sizeof(line), stdin);
  sscanf(line, "%s", out);
  printf("\n");

return(0);
}
  
int build_string(char *lname, char *fname, char *in, char *out, char *filename, char *answer)
{
  FILE *filep = NULL;

  char string[4];

  int slength;

  /* Open file and prepare to read */

  filep = fopen(filename, "r"); 
                
  if (filep == NULL) {
    fprintf(stderr, "error opening input file %s\n", filename); 
    exit(1); 
  }

  fprintf(stdout, "line 30\n");

  /* Read ints and write them to string */
  
  if (fscanf(filep, "%s", &string) != EOF){
    fprintf(stdout, "line 39\n");
    sprintf(answer, "INSERT INTO %s VALUES ('%s','%s','%s','%s','{%s", TBLNAME,  lname, fname, in, out, string);
    fprintf(stdout, "%s\n", answer);
  }

  while (fscanf(filep, "%s", &string) != EOF){
    strcat(answer, ",");
    strcat(answer, string);
    fprintf(stdout, "%s\n", answer);
  }
  
  strcat(answer, "}')");
  fprintf(stdout, "%s\n", answer);
  slength = strlen(answer);
  fprintf(stdout, "length: %d characters.\n", slength);

  fclose(filep);

  /* Return exit */
  
  return(0);
} 

void exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(1);
}


main(int argc, char *argv[])
{
	char		*pghost,
			*pgport,
			*pgoptions,
			*pgtty;
	char		*dbName;
	char		user_id[4],
	                lname[26],
	                fname[26],
	                time_in[9],
	                time_out[9],
	                sql_comm[160];
	char            filename[26];
	int		nFields;
	int		c,
			i,
	                length,
	                check, 
			j;
	
	/* File Debug */
		
	PGconn		*conn;
	PGresult	*res;
	
	/* Begin by assigning values taken from command line agruments to variables */
	
	printf ("%d\n", argc);

	if (argc < 1)
	{
	  printf("Usuage: adduser <characteristics filename>\n");
	  exit (0);
	}

	strcpy(filename, argv[1]);

	/* verify command line arguments */
	fprintf(stdout, "arg[1] = %s", filename);

	/* Get value for lastname, firstname, time_in, time_out */
	get_user_info(lname, fname, time_in, time_out);

	/* build sql command string */
	check = build_string(lname,fname,time_in,time_out,filename,sql_comm);

	if (check != 0)
	  exit(1);

	/*
	 * Set the parameters for a backend connection if the parameters
	 * are NULL, then the system will try to use reasonable defaults
	 * by looking up environment variables or, failing that, using
	 * hardwired constraints
	 */
	pghost = NULL;		/* hostname of the backend server */
	pgport = NULL;		/* port of the backend server */
	pgoptions = NULL;	/* special options to start up the backend server */
	
	pgtty = NULL;		/* debugging tty for the backend server */
	
	dbName = DBNAME;
	
	/* Make a connection to the database */
	conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
	
	/*
	 * Check to see that the backend connection was made sucessfully
	 */
	 if (PQstatus(conn) == CONNECTION_BAD)
		{
			fprintf (stderr, "Connection to database '%s' failed.\n", dbName);
			fprintf (stderr, "%s", PQerrorMessage(conn));
			exit_nicely(conn);
		}
		
	/* debug = fopen("/temp/trace.out", "W");
	   PQtrace(conn, debug); */
	   
	/* Start a transaction block */
	res = PQexec(conn, "BEGIN");
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "BEGIN command failed\n");
			PQclear(res);
			exit_nicely(conn);
		}
	
	/* should PQclear PQresult when it is not longer needed to avoid memory leaks */
	PQclear(res);
	
	/*
	 * Inserting new user into db
	 */
	res = PQexec(conn, sql_comm);
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "INSERT command failed\n");
			PQclear(res);
			exit_nicely(conn);
		}
	PQclear(res);

	/*
	 * Fetch instances from Users, a table in the SS99 database
	 */
	printf("fetching instances\n");
	sprintf(sql_comm, "DECLARE mycursor CURSOR FOR select * from %s", TBLNAME);
	res = PQexec(conn, sql_comm);
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "DECLARE CURSOR command failed\n");
			PQclear(res);
			exit_nicely(conn);
		}
	PQclear(res);
	res = PQexec(conn, "FETCH ALL in mycursor");
	if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
		{
			fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
			PQclear(res);
			exit_nicely(conn);
		}
		
	/*
	 * First, print out the attribute names
	 */
	nFields = PQnfields(res);
	for (i = 0; i < nFields; i++)
		printf("%-15s", PQfname(res, i));
	printf("\n\n");
	
	/*
	 * Next, print out the instance
	 */
	for (i = 0; i < PQntuples(res); i++)
	  {
	    for (j = 0; j < nFields; j++)
	      {
		printf("%-15s", PQgetvalue(res, i, j));
	      }
	    printf("\n");
	  }
	PQclear(res);
	
	/* Close the cursor */
	res = PQexec(conn, "CLOSE mycursor");
	PQclear(res);
	
	/* Commit the transaction */
	res = PQexec(conn, "COMMIT");
	PQclear(res);
	
	/* Close the connection to the database and cleanup */
	PQfinish(conn);
	
	/* fclose (debug) */
}