/* deluser.c
 *
 * This program deletes a user from the users table in the ss99 database.
 *
 * Kentaro Innes
 *
 * To run: Type deluser(<user_id>)
 */

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

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

int build_string(char *id, char *answer)
{
  int slength;

  /* Write SQL Command */
  
  fprintf(stdout, "line 23\n");
  sprintf(answer, "DELETE FROM %s WHERE user_id = '%s'", TBLNAME, id);
  fprintf(stdout, "%s\n", answer);
 
  slength = strlen(answer);
  fprintf(stdout, "length: %d characters.\n", slength);
  fprintf(stdout, "line 31\n");

  /* 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],
	                sql_comm[160];
	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 */

	if (argc < 2)
	{
	fprintf(stdout, "Usuage: deluser <user_id>\n");
	exit (0);
	}

	strcpy(user_id, argv[1]);

	/* verify command line arguments */

	fprintf(stdout, "arg[1] = %s", user_id);

	/* build sql command string */

	check = build_string(user_id,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 sthumb 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) */
	}