// Rick van der Zwet
// Databases opdracht 3
// SN: 0433373


//c libaries
#include <cstdio>
#include <string>

//oracle sql libaries
#include <sqlcpr.h>
#include <sqlca.h>




// Declare error handling function.-
void sql_error(char *msg)
{
  char err_msg[128];
  size_t buf_len, msg_len;

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  
  // Print the error message:
  cout << msg << endl;
  buf_len = sizeof (err_msg);
  sqlglm(err_msg, &buf_len, &msg_len);
  cout << msg_len << "*" << err_msg << endl;

  // Make sure the database is consistent:
  EXEC SQL ROLLBACK RELEASE;

  // Stop the program.
  exit(EXIT_FAILURE);
} //end sql_error



//
// clear input field
void digestline(void) {
  scanf("%*[^\n]");   /* Skip to the End of the Line */
  scanf("%*1[\n]");   /* Skip One Newline */
} //end digestline




void opdr1() {
  printf("Uitvoer opdracht 1...\n");
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  EXEC SQL BEGIN DECLARE SECTION;
    struct
    {
      char Sname[20];
    } result;
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL DECLARE names_1 CURSOR FOR
  SELECT Sname
  FROM Sportsman S, Coaches C
  WHERE S.Scountry = 'Norway' AND S.Sage > 25 AND S.Sid = C.Sid;

  EXEC SQL OPEN names_1;
  EXEC SQL WHENEVER NOT FOUND DO break;

  printf("NAAM\n");
  printf("----\n");
  for (;;)
  {
    EXEC SQL FETCH names_1
    INTO :result;
    printf("%s\n", result.Sname);
  }
  EXEC SQL CLOSE names_1;
}



void opdr2() {
  printf("Uitvoer opdracht 2...\n");
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  EXEC SQL BEGIN DECLARE SECTION;
    struct
    {
      char Cname[20];
    } result;
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL DECLARE names_2 CURSOR FOR
  SELECT Cname
  FROM Coach C, Coaches Cs, Sportsman S, (SELECT MIN(C2.Cage) AS minimum
                                          FROM Coach C2 ) Age
  WHERE C.Cage != Age.minimum AND C.Ccountry = S.Scountry AND C.Cid = Cs.Cid AND Cs.Sid = S.Sid;
  
  EXEC SQL OPEN names_2;
  EXEC SQL WHENEVER NOT FOUND DO break;

  printf("NAAM\n");
  printf("----\n");
  for (;;)
  {
    EXEC SQL FETCH names_2
    INTO :result;
    printf("%s\n", result.Cname);
  }

  EXEC SQL CLOSE names_2;
}

void opdr3() {
  printf("Uitvoer opdracht 3...\n");
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  EXEC SQL BEGIN DECLARE SECTION;
    struct
    {
      char Sname[20];
    } result;
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL DECLARE names_3 CURSOR FOR
  SELECT Sname
  FROM Sportsman S
  WHERE RTRIM(S.Sname) LIKE 'K%t%e';
  
  EXEC SQL OPEN names_3;
  EXEC SQL WHENEVER NOT FOUND DO break;

  printf("NAAM\n");
  printf("----\n");
  for (;;)
  {
    EXEC SQL FETCH names_3
    INTO :result;
    printf("%s\n", result.Sname);
  }

  EXEC SQL CLOSE names_3;
}


void opdr4() {
  printf("Uitvoer opdracht 4...\n");
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  EXEC SQL BEGIN DECLARE SECTION;
    struct
    {
      char Sname[20];
      char Cname[20];
    } result;
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL DECLARE names_4 CURSOR FOR
  SELECT S.Sname, C.Cname
  FROM Sportsman S, Coaches Cs, Coach C
  WHERE Cs.Cid = C.Cid AND Cs.Sid = S.Sid AND ABS(S.Sage - C.Cage) < 4;
  
  EXEC SQL OPEN names_4;
  EXEC SQL WHENEVER NOT FOUND DO break;

  printf("SPORTSMAN , COACH\n");
  printf("-----------------\n");
  for (;;)
  {
    EXEC SQL FETCH names_4
    INTO :result;
    printf("%s , %s\n", result.Sname, result.Cname);
  }
}


void opdr5() {
  printf("Uitvoer opdracht 5...\n");
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
  EXEC SQL BEGIN DECLARE SECTION;
    struct
    {
      char Cname[20];
    } result;
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL DECLARE names_5 CURSOR FOR
  SELECT C.Cname
  FROM  Coach C
  WHERE C.Ccountry = 'Kazakhstan' AND C.Cid = (SELECT T.Cid
                                       FROM Coaches T
                                       WHERE T.Cid = C.Cid
                                       GROUP BY T.Cid
                                       HAVING COUNT(*) > 3 );
  
  EXEC SQL OPEN names_5;
  EXEC SQL WHENEVER NOT FOUND DO break;

  printf("COACH\n");
  printf("-----\n");
  for (;;)
  {
    EXEC SQL FETCH names_5
    INTO :result;
    printf("%s\n", result.Cname);
  }
}


// Main module
// 
int main() {

  char username[10] = "";
  char password[10] = "";
  char connect[20] = "";
  int choice, args = 0;
  
  EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR connect_str[20];
    struct
    {
      char tname[20];
    } tables;
  EXEC SQL END DECLARE SECTION;
  
  printf("Please enter username: ");
  scanf("%s",username);
  printf ("Please enter password: ");
  scanf("%s",password);
  strcpy(connect, username);
  strcat(connect, "/");
  strcat(connect, password);
  strcat(connect, "@ONW");
  printf("Using %s", connect);

  strcpy((char *) connect_str.arr, connect);
  connect_str.len = (unsigned short) strlen((char *) connect_str.arr);

  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

  EXEC SQL CONNECT :connect_str;

  do
  {
    printf("1: Names of Sportsmen, Norway, older 25, at least one coach\n");
    printf("2: Names of coaches, sportsman own country, not youngest coach\n");
    printf("3: Names of all sportsman, name start with 'K', includes 't', ends with 'e'\n");
    printf("4: Names of sportsman and coach, 'team', age differences 4 year\n");
    printf("5: Names of coaches, from Kazakhstan, coach over 3 persons\n");
    printf("\n");
    printf("9: quit\n");
    printf("Please enter your choice: ");
    if ( scanf("%d", &choice) > 0 ) {
      switch( choice ) {
        case 1:
          opdr1();
          break;
        case 2:
          opdr2();
          break;
        case 3:
          opdr3();
          break;
        case 4:
          opdr4();
          break;
        case 5:
          opdr5();
          break;
        case 9:
          printf("Goodbye..\n");
          break;
        default:
          printf("Sorry %d ain't a good number\n", choice);
      }
    }
    else
    {
      printf("Sorry input not valid\n");
      choice = 0;
    }
    digestline();
  } while ( choice != 9);

  //close connection
  EXEC SQL COMMIT WORK RELEASE;
  exit(0);
}