#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdbool.h>
#include <libpq-fe.h>
#include <unistd.h>
#define BIG 1000
#define SMALL 200

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;
  tok = strtok(line, ";");

  for (tok ; ;){
	if (!--num){
     return tok;
    }
         tok = strtok(NULL, ";\n");

    if (tok == NULL) {
     return "null";
	}
  }
}


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


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[BIG] = { "\0" };
  fseek(file, 0, 0);
  fgets(line, BIG, 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[BIG] = { "\0" };
  char headers[BIG] = "(";
  fseek(file, 0, 0);
  fgets(line, BIG, 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[BIG] = { "\0" };
  char columns[BIG] = { "\0" };
  fgets(line, BIG, 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[BIG] = { "\0" };
  char headers[SMALL] = { "\0" };
  createHeaders(file, headers);
  int i = 1, j = 0, counter = countColumns(file);
  fseek(file, 0, 0);
  char head[SMALL] = { "\0" };
  fgets(head, SMALL, file);
  int maxLength[counter + 1];
  for (i = 1; i <= (counter + 1); i++){
    maxLength[i] = 20;
  }
  while (fgets(line, BIG, file)){
    char insertTbl[BIG] = "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[SMALL];
        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 main(int argc, char *argv[]){
  FILE *file = fopen(argv[1], "r");
  char connectionInfo[SMALL] = { "\0" };
  createConnection(argc, argv[1], connectionInfo);
  PGconn *conn = PQconnectdb(connectionInfo);

  char tblName[SMALL] = { "\0" };
  char dropTblCommand[SMALL] = { "\0" };
  char createTblCommand[BIG] = { "\0" };
  char selectTblCommand[SMALL] = { "\0" };
  int orderBy = 1;
      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);

      PQfinish(conn);
  return 0;
}