#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdbool.h>
#include <libpq-fe.h>
#include <unistd.h>
#define MAX 1000
#define MEDIUM 200
#define MIN 50

enum COUNTER_ARGS{
  CREATE_TABLE = 2
};

void printTuples(PGresult *result){
  int m, n;
  int nRows = PQntuples(result);
  int nFields = PQnfields(result);
  printf("  Number of rows returned   = %d\n", nRows);
  printf("  Number of fields returned = %d\n\n", nFields);
  for (m = 0; m < nRows; m++){
    for (n = 0; n < nFields; n++){
      printf("%16s = %24s;\n", PQfname(result, n), PQgetvalue(result, m, n));
    }
    printf("\n");
  }
}

void doSQL(PGconn *conn, char *command){
  PGresult *result;
  printf("> %s\n", command);
  printf("\n_____________________________________\n");
  result = PQexec(conn, command);
  printf("  Status is     : %s\n", PQresStatus(PQresultStatus(result)));
  printf("  Rows affected : %s\n", PQcmdTuples(result));
  printf("  Result message: %s\n", PQresultErrorMessage(result));
  switch (PQresultStatus(result)){
  case PGRES_TUPLES_OK:
    printTuples(result);
    break;
  }
  printf("_____________________________________\n\n");
  PQclear(result);
}

char *getField(char *line, int num){
  char *tok;
  for (tok = strtok(line, ";"); tok && *tok; tok = strtok(NULL, ";\n")){
    if (!--num){
      return tok;
    }
  }
  return NULL;
}

void dbExist(char *dbname, char *result){
  char command[MEDIUM] = { "\0" };
  sprintf(command, "psql -l | cut -d '|' -f1 | grep -w %s > .exist_test", dbname);
  system(command);
  FILE *test = fopen(".exist_test", "r");
  if (test != NULL){
    fseek(test, 0, SEEK_END);
    int length = ftell(test);
    if (length == 0){
      sprintf(result, "createdb %s 2>/dev/null", dbname);
      system(result);
      printf("\nDatabase \"%s\" was created.\n", dbname);
    }
  }
  fclose(test);
  system("rm .exist_test");
}

void createConnection(int argc, char *dbname, char *result){
  char createdb[MIN] = { "\0" };
  char username[MIN] = { "\0" };
  char *password;
  printf("\tLog in\n");
  printf("______________________________\n");
  if (argc == 2){
    printf("DBname:   ");
    scanf("%s", dbname);
  }
  printf("Username: ");
  scanf("%s", username);
  password  = getpass("Password: ");
  printf("______________________________\n\n");
  dbExist(dbname, createdb);
  sprintf(result, "host=localhost port=5432 dbname=%s user=%s password=%s", dbname, username, password);
}

bool showLog(PGconn *conn){
  if (PQstatus(conn) == CONNECTION_OK){
    printf("\nConnection made!\n");
    printf("______________________________\n");
    printf("PGDBNAME   = %s\n",PQdb(conn));
    printf("PGUSER     = %s\n",PQuser(conn));
    printf("PGPASSWORD = ");
    int passLength = strlen(PQpass(conn));
    while (passLength--){
      printf("*");
    };
    printf("\n");
    printf("PGHOST     = %s\n",PQhost(conn));
    printf("PGPORT     = %s\n",PQport(conn));
    printf("______________________________\n\n");
    return true;
  }
  else{
    printf("Connection failed!\n%s\n", PQerrorMessage(conn));
    return false;
  }
}

void crTlName(char *source, char *result){
  strncat(result, source, strlen(source) - 4);
}

void dropTl(char *tblName, char *result){
  sprintf(result, "DROP TABLE %s;", tblName);
}

int countColumns(FILE *file){
  char line[MAX] = { "\0" };
  fseek(file, 0, 0);
  fgets(line, MAX, file);
  char *tmp = strdup(line);
  int i, counter = 1;
  for (i = 0; i < strlen(tmp); i++){
    if (tmp[i] == ';'){
      counter++;
    }
  }
  free(tmp);
  return counter;
}

void createHeaders(FILE *file, char *result){
  char line[MAX] = { "\0" };
  char headers[MAX] = "(";
  fseek(file, 0, 0);
  fgets(line, MAX, file);
  char *tmp = strdup(line);
  int i = 1, counter = countColumns(file);
  while (counter--){
    tmp = strdup(line);
    strcat(headers, getField(tmp, i));
    strcat(headers, ", ");
    i++;
  }
  strncat(result, headers, strlen(headers) - 2);
  strcat(result, ")");
  free(tmp);
}

void createTbl(FILE *file, char *tblName, char *result){
  char line[MAX] = { "\0" };
  char columns[MAX] = { "\0" };
  fgets(line, MAX, file);
  char *tmp = strdup(line);
  int i = 1, counter = countColumns(file);
  while (counter--){
    tmp = strdup(line);
    strcat(columns, "\t");
    strcat(columns, getField(tmp, i));
    strcat(columns, " VARCHAR(20),\n");
    i++;
  }
  sprintf(result, "CREATE TABLE %s(\n\tid SERIAL,\n", tblName);
  strncat(result, columns, strlen(columns) - 2);
  strcat(result, "\n);");
  free(tmp);
}

void alterTbl(char *tblName, char *columnName, int size, char *result){
  sprintf(result, "ALTER TABLE %s ALTER COLUMN %s TYPE VARCHAR(%d);", tblName, columnName, size);
}

void insert(PGconn *conn, FILE *file, char *tblName){
  char line[MAX] = { "\0" };
  char headers[MEDIUM] = { "\0" };
  createHeaders(file, headers);
  int i = 1, j = 0, counter = countColumns(file);
  fseek(file, 0, 0);
  char head[MEDIUM] = { "\0" };
  fgets(head, MEDIUM, file);
  int maxLength[counter + 1];
  for (i = 1; i <= (counter + 1); i++){
    maxLength[i] = 20;
  }
  while (fgets(line, MAX, file)){
    char insertTbl[MAX] = "INSERT INTO ";
    strcat(insertTbl, tblName);
    strcat(insertTbl, headers);
    strcat(insertTbl, " VALUES\n(");
    char *tmp;
    i = 1;
    j = counter;
    while (j--){
      tmp = strdup(line);
      strcat(insertTbl, "'");
      strcat(insertTbl, getField(tmp, i));
      tmp = strdup(line);
      if (strlen(getField(tmp, i)) > maxLength[i]){
        tmp = strdup(line);
        maxLength[i] = strlen(getField(tmp, i));
        char alterTblCommand[MEDIUM];
        tmp = strdup(head);
        alterTbl(tblName, (char*) getField(tmp, i),  maxLength[i], alterTblCommand);
        doSQL(conn, alterTblCommand);
      }
      strcat(insertTbl, "'");
      if (j != 0) strcat(insertTbl, ",");
      i++;
    }
    strcat(insertTbl, ");");
    doSQL(conn, insertTbl);
  }
}

void selectTbl(char *tblName, char *result){
  sprintf(result, "SELECT * FROM %s;", tblName);
}

int sortBy(int orderBy){
  printf("Order by: 1-id | 2-name :: ");
  scanf("%d", &orderBy);
  printf("\n");
  return orderBy;
}

int main(int argc, char *argv[]){
  FILE *file = fopen(argv[1], "r");
  char connectionInfo[MEDIUM] = { "\0" };
  createConnection(argc, argv[1], connectionInfo);
  PGconn *conn = PQconnectdb(connectionInfo);
  if (!showLog(conn)){
    return 1;
  };
  char tblName[MIN] = { "\0" };
  char dropTblCommand[MEDIUM] = { "\0" };
  char createTblCommand[MAX] = { "\0" };
  char selectTblCommand[MEDIUM] = { "\0" };
  int orderBy = 1;
  switch(argc){
    case CREATE_TABLE:
      crTlName(argv[1], tblName);
      dropTl(tblName, dropTblCommand);
      createTbl(file, tblName, createTblCommand);
      selectTbl(tblName, selectTblCommand);
      doSQL(conn, dropTblCommand);
      doSQL(conn, createTblCommand);
      insert(conn, file, tblName);
      doSQL(conn, selectTblCommand);
      break;
    default:
      orderBy = sortBy(orderBy);
      }
      PQfinish(conn);
  return 0;
}