#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "/usr/include/postgresql/libpq-fe.h"





#define QUIT -1
#define MAIN_MENU 0
#define SELECT_MENU 1
#define INSERT_MENU 2
#define UPDATE_MENU 3
#define DELETE_MENU 4
#define MAX_DB_STATEMENT_BUFFER_LENGTH 5000
#define MAX_COMMAND_BUFFER_LENGTH 25
#define MAX_STUDENT_FIRST_NAME_LENGTH 100
#define MAX_STUDENT_LAST_NAME_LENGTH 100
#define MAX_STUDENT_ID_LENGTH 25
#define TRUE 1
#define FALSE 0




int get_command (void);
void remove_newline_from_string (char *);
int is_valid_non_negative_integer (char *);





int main (int argc, char *argv[])
{
  PGconn   *db_connection;
  PGresult *db_result;
  char db_statement[MAX_DB_STATEMENT_BUFFER_LENGTH], command_buffer[MAX_COMMAND_BUFFER_LENGTH], student_first_name[MAX_STUDENT_FIRST_NAME_LENGTH], student_last_name[MAX_STUDENT_LAST_NAME_LENGTH];
  char student_id[MAX_STUDENT_ID_LENGTH], *postgres_safe_student_first_name, *postgres_safe_student_last_name;
  const char *parameter_values[2];
  int parameter_lengths[2];
  int parameter_formats[2];
  int menu, command, row, num_rows;

  db_connection = PQconnectdb("host = 'localhost' dbname = 'cs161' user = 'postgres' password = 'YOUR_POSTGRES_PASSWORD'");
  if (PQstatus(db_connection) != CONNECTION_OK)
  {
    printf ("Connection to database failed: %s", PQerrorMessage(db_connection));
    PQfinish (db_connection);
    exit (EXIT_FAILURE);
  }
  menu = MAIN_MENU;
  while (menu != QUIT)
  {
    switch (menu)
    {
      case MAIN_MENU:
        printf ("\n\nMain menu:\n");
        printf ("1 : Select\n");
        printf ("2 : Insert\n");
        printf ("3 : Update\n");
        printf ("4 : Delete\n");
        printf ("q : QUIT\n");
        printf ("Enter a command: ");
        command = get_command();
        switch (command)
        {
          case -1:
            menu = QUIT;
          break;
          case 0:
            printf ("Invalid command\n");
          break;
          case 1:
            menu = SELECT_MENU;
          break;
          case 2:
            menu = INSERT_MENU;
          break;
          case 3:
            menu = UPDATE_MENU;
          break;
          case 4:
            menu = DELETE_MENU;
          break;
          default:
          break;
        }
      break;
      case SELECT_MENU:
        printf ("\n\nSELECT menu:\n");
        printf ("1 : Select all students sorted by student_id ascending\n");
        printf ("2 : Select all students sorted by student_id descending\n");
        printf ("3 : Select all students sorted by last_name ascending\n");
        printf ("4 : Select all students sorted by last_name descending\n");
        printf ("5 : Select all students sorted by first_name ascending\n");
        printf ("6 : Select all students sorted by first_name descending\n");
        printf ("7 : Return to main menu\n");
        printf ("q : Quit\n");
        printf ("Enter a command: ");
        command = get_command();
        switch (command)
        {
          case -1:
            menu = QUIT;
          break;
          case 0:
            printf ("Invalid command\n");
          break;
          case 1:
            strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.student_id ASC", MAX_DB_STATEMENT_BUFFER_LENGTH); 
          break;
          case 2:
            strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.student_id DESC", MAX_DB_STATEMENT_BUFFER_LENGTH); 
          break;
          case 3:
            strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.last_name ASC", MAX_DB_STATEMENT_BUFFER_LENGTH); 
          break;
          case 4:
            strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.last_name DESC", MAX_DB_STATEMENT_BUFFER_LENGTH); 
          break;
          case 5:
            strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.first_name ASC", MAX_DB_STATEMENT_BUFFER_LENGTH); 
          break;
          case 6:
            strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.first_name DESC", MAX_DB_STATEMENT_BUFFER_LENGTH); 
          break;
          case 7:
            menu = MAIN_MENU;
          break;
          default:
          break;
        }
        if ((command >= 1) && (command <= 6))
        {
          db_result = PQexec (db_connection, &db_statement[0]);
          if (PQresultStatus(db_result) == PGRES_TUPLES_OK)
          {
            num_rows = PQntuples(db_result);
            printf ("\n");
            printf ("+-----------+------------+-----------+\n");
            printf ("|Student ID | First name | Last name |\n");
            printf ("+-----------+------------+-----------+\n");
            if (num_rows == 0)
            {
              printf ("database is empty");
            }
            else
            {
              for (row = 0; row < num_rows; row++)
              {
                printf ("%s %s %s\n", PQgetvalue (db_result, row, 0), PQgetvalue (db_result, row, 1), PQgetvalue (db_result, row, 2));
              }
            }  
            PQclear (db_result);
          }
        }
      break;
      case INSERT_MENU:
        printf ("\n\nINSERT menu:\n");
        printf ("1 : Add new student\n");
        printf ("2 : Return to main menu\n");
        printf ("q : Quit\n");
        printf ("Enter a command: ");
        command = get_command();
        switch (command)
        {
          case -1:
            menu = QUIT;
          break;
          case 0:
            printf ("Invalid command\n");
          break;
          case 1:
            printf ("Enter student's first name: ");
            if (fgets(&student_first_name[0], MAX_STUDENT_FIRST_NAME_LENGTH-1, stdin) != NULL)
            {
              printf ("Enter student's last name: ");
              if (fgets(&student_last_name[0], MAX_STUDENT_LAST_NAME_LENGTH-1, stdin) != NULL)
              {
                remove_newline_from_string (&student_first_name[0]);
                remove_newline_from_string (&student_last_name[0]);
                parameter_values[0] = &student_first_name[0];
                parameter_values[1] = &student_last_name[0];
                strncpy (&db_statement[0], "INSERT INTO students (first_name, last_name) VALUES ($1, $2) RETURNING student_id", MAX_DB_STATEMENT_BUFFER_LENGTH); 
                db_result = PQexecParams (db_connection, db_statement, 2, NULL, parameter_values, NULL, NULL, 0);
                if (PQresultStatus(db_result) != PGRES_TUPLES_OK)
                {
                  printf ("Postgres INSERT error: %s\n", PQerrorMessage(db_connection));
                }
                else
                {
                  if (PQntuples(db_result) == 1)
                  {
                    if (strlen(PQgetvalue(db_result, 0, 0)) < MAX_STUDENT_ID_LENGTH-1)
                    {
                      strncpy (&student_id[0], PQgetvalue(db_result, 0, 0), MAX_STUDENT_ID_LENGTH-1);
                      printf ("Student ID %s: assigned to %s %s\n\n", &student_id[0], &student_first_name[0], &student_last_name[0]);
                    }
                  }
                  PQclear(db_result);
                }
              }
            }
          break;
          case 2:
            menu = MAIN_MENU;
          break;
          default:
          break;
        }
      break;
      case UPDATE_MENU:
        printf ("\n\nUPDATE menu:\n");
        printf ("1 : Update a student's first name specified by student_id.\n");
        printf ("2 : Update a student's last name specified by student_id.\n");
        printf ("3 : Return to main menu\n");
        printf ("q : Quit\n");
        printf ("Enter a command: ");
        command = get_command();
        switch (command)
        {
          case -1:
            menu = QUIT;
          break;
          case 0:
            printf ("Invalid command\n");
          break;
          case 1:
            printf ("\nEnter student ID: ");
            if (fgets(&student_id[0], MAX_STUDENT_ID_LENGTH-1, stdin) != NULL)
            {
              remove_newline_from_string (&student_id[0]);
              if (is_valid_non_negative_integer (&student_id[0]))
              {
                printf ("Enter student's new first name: ");
                if (fgets(&student_first_name[0], MAX_STUDENT_FIRST_NAME_LENGTH-1, stdin) != NULL)
                {
                  remove_newline_from_string (&student_first_name[0]);
                  parameter_values[0] = &student_first_name[0];
                  parameter_values[1] = &student_id[0];
                  strncpy (&db_statement[0], "UPDATE students SET first_name = $1 WHERE student_id = $2", MAX_DB_STATEMENT_BUFFER_LENGTH); 
                  db_result = PQexecParams (db_connection, db_statement, 2, NULL, parameter_values, NULL, NULL, 0);
                  if (PQresultStatus(db_result) != PGRES_COMMAND_OK)
                  {
                    printf("Postgres UPDATE error: %s\n", PQerrorMessage(db_connection));
                  }
                  PQclear(db_result);
                }
              }
            }
          break;
          case 2:
            printf ("\nEnter student ID: ");
            if (fgets(&student_id[0], MAX_STUDENT_ID_LENGTH-1, stdin) != NULL)
            {
              remove_newline_from_string (&student_id[0]);
              if (is_valid_non_negative_integer (&student_id[0]))
              {
                printf ("Enter student's new last name: ");
                if (fgets(&student_last_name[0], MAX_STUDENT_LAST_NAME_LENGTH-1, stdin) != NULL)
                {
                  remove_newline_from_string (&student_last_name[0]);
                  parameter_values[0] = &student_last_name[0];
                  parameter_values[1] = &student_id[0];
                  strncpy (&db_statement[0], "UPDATE students SET last_name = $1 WHERE student_id = $2", MAX_DB_STATEMENT_BUFFER_LENGTH); 
                  db_result = PQexecParams (db_connection, db_statement, 2, NULL, parameter_values, NULL, NULL, 0);
                  if (PQresultStatus(db_result) != PGRES_COMMAND_OK)
                  {
                    printf("Postgres UPDATE error: %s\n", PQerrorMessage(db_connection));
                  }
                  PQclear(db_result);
                }
              }
            }
          break;
          case 3:
            menu = MAIN_MENU;
          break;
          default:
          break;
        }
      break;
      case DELETE_MENU:
        printf ("\n\nDELETE menu:\n");
        printf ("1 : Delete a student record from the students table specified by student id number\n");
        printf ("2 : Return to main menu\n");
        printf ("q : Quit\n");
        printf ("Enter a command: ");
        command = get_command();
        switch (command)
        {
          case -1:
            menu = QUIT;
          break;
          case 0:
            printf ("Invalid command\n");
          break;
          case 1:
            printf ("\nEnter student ID: ");
            if (fgets(&student_id[0], MAX_STUDENT_ID_LENGTH-1, stdin) != NULL)
            {
              remove_newline_from_string (&student_id[0]);
              if (is_valid_non_negative_integer (&student_id[0]))
              {
                parameter_values[0] = &student_id[0];
                strncpy (&db_statement[0], "DELETE FROM students WHERE student_id = $1", MAX_DB_STATEMENT_BUFFER_LENGTH); 
                db_result = PQexecParams (db_connection, db_statement, 1, NULL, parameter_values, NULL, NULL, 0);
                if (PQresultStatus(db_result) != PGRES_COMMAND_OK)
                {
                  printf("Postgres DELETE error: %s\n", PQerrorMessage(db_connection));
                }
                PQclear(db_result);
              }
              else
              {
                printf ("\nThat is not a valid student ID. Please enter a non-negative integer number for student_id.\n\n");
              }
            }
          break;
          case 2:
            menu = MAIN_MENU;
          break;
          default:
          break;
        }
      break;
      default:
      break;
    }
  }
  db_result = PQexec (db_connection, "CLOSE myportal");
  PQclear (db_result);
  PQfinish (db_connection);
  exit (EXIT_SUCCESS);
}





int get_command (void)
{
  char command_buffer[MAX_COMMAND_BUFFER_LENGTH];
  int command;

  command = 0;
  if (fgets(&command_buffer[0], MAX_COMMAND_BUFFER_LENGTH-1, stdin) != NULL)
  {
    if (command_buffer[0] == 'q')
    {
      command = -1;
    }
    else
    {
      if ((command_buffer[0] >= '1') && (command_buffer[0] <= '9'))
      {
        command = command_buffer[0] - '0';
      }
    }
  }
  return command;
}






void remove_newline_from_string (char *input_string)
{
  char *byte;

  if (input_string != NULL)
  {
    if ((byte = strchr(input_string, '\n')) != NULL)
    {
      *byte = '\0';
    }
  }
}





int is_valid_non_negative_integer (char *input_string)
{
  char *byte;
  int valid_non_negative_integer;

  if (strlen(input_string) == 0)
  {
    valid_non_negative_integer = FALSE;
  }
  else
  {
    valid_non_negative_integer = TRUE;
  }
  byte = input_string;
  while ((*byte != '\0') && valid_non_negative_integer)
  {
    if ((*byte < '0') || (*byte > '9'))
    {
      valid_non_negative_integer = FALSE;
    }
    else
    {
      byte++;
    }
  }
  return valid_non_negative_integer;
}