/*********************************
 * Jorge Ceballos
 * CS 430
 * HW1
 * Q2
 **********************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oraca.h>

void myTrim(char* );
/*** header file for ORACLE definitions ***/
exec sql include sqlca;

/*** Variable Declaration Block ***/
exec sql begin declare section;
  char qAorB[10], condA[20];
  char login[20], password[20];
  float pcPrinter;
  float minSpeed = 5.0;
  char maker[12];
  char pType[12];
  int model;
  int pcNum;
  int printerNum;
  int colorNum = 0;
  float tempPrice;
  float speed;
  float ram;
  float hd;
  float screen;
  float price;
  char color[1];
  char printerType[10];
  char SQLSTATE[6];
exec sql end declare section;

int print_sql_state(char *code){
  printf("\nSQLSTATE=%s\n",code);
}

int main()
{
  /*** error handler ***/
  exec sql whenever sqlerror goto report_error;
  exec sql whenever not found goto notfound;

  /*** Prompt user for login name and password ***/
  printf("Username=");
  gets(login);
  printf("Password=");
  char *pass_string = malloc(100);
  pass_string=getpass("");
  strcpy(password, pass_string);

  /*** Connect to DB ***/
  exec sql connect :login
     identified by :password;

  /*** cursor declaration ***/
  exec sql declare maker_type cursor for
     select maker, type, model from Product;

  /*** Prompt user for question ***/
  printf("\nFor Q2.a Type = a, \nFor Q2.b Type = b");
  gets(qAorB);

  if(strcmp(qAorB, "a") == 0) {
    printf("\nChoose Maker");
    scanf("%s",&condA);
    exec sql open maker_type;
    /*** TRAVERSE RESULT SET WITH CURSOR ***/
    while(1) {
      EXEC SQL
	   FETCH maker_type into :maker, :pType, :model;

      myTrim(pType);
      myTrim(maker);
      if(strcmp(pType, "PC") == 0 && strcmp(maker, condA) == 0) {
	   EXEC SQL
	    SELECT speed, ram, hd, price
	    INTO :speed, :ram, :hd, :price
	    FROM PC
            WHERE model = :model;
	   printf("\nModel # = %d, Product type = %s, Speed = %f, Ram = %f, Hd = %f, Price = %f", model, pType, speed, ram, hd, price);
      }
      if(strcmp(pType, "laptop") == 0 && strcmp(maker, condA) == 0) {
	   EXEC SQL
	    SELECT speed, ram, hd, screen, price
	    INTO :speed, :ram, :hd, :screen, :price
	    FROM Laptop
            WHERE model = :model;
	   printf("\nModel # = %d, Product type = %s, Speed = %f, Ram = %f, Hd = %f, Screen = %f Price = %f", model, pType, speed, ram, hd, screen, price);
      }
      if(strcmp(pType, "printer") == 0 && strcmp(maker, condA) == 0) {
	   EXEC SQL
	    SELECT color, type, price
	    INTO :color, :printerType, :price
	    FROM Printer
            WHERE model = :model;
	   printf("\nModel # = %d, Product type = %s, Color = %s, Type = %s, Price = %f", model, pType, color, printerType, price);
      }
    }
  }
  if(strcmp(qAorB, "b") == 0) {
    printf("\nEnter Max Budget = ");
    scanf("%f",&pcPrinter);
    printf("\nEnter Min PC speed = ");
    scanf("%f",&minSpeed);
    float result = pcPrinter;
    float pc = 0;
    float printer = 0;
    int i;
    int j;
    char hasColor[20];

    /*** cursor declaration ***/
    exec sql declare pc_crs cursor for
      select price from PC where speed >= :minSpeed;

    printf("\n1 = %f.......", minSpeed);

    EXEC SQL
        SELECT COUNT(*) INTO :pcNum FROM PC WHERE speed >= :minSpeed;
    printf("\n2.......pcNum = %d", pcNum);
    while(1)  {
      printf("\n3.......");

    /*** cursor declaration ***/
      exec sql declare colorP_crs cursor for
        select price from Printer where color = 't';
      printf("\n4.......");
      EXEC SQL
        SELECT COUNT(*) INTO :printerNum FROM Printer;

      printf("\n5.......printerNum = %d", printerNum);

      exec sql declare printer_crs cursor for
       select price from Printer;
      printf("\n6.......");
      EXEC SQL
        SELECT COUNT(*) INTO :colorNum FROM Printer WHERE color = 't';
      printf("\n7.......colorNum = %d", colorNum);

      EXEC SQL
        FETCH pc_crs INTO :price;
      printf("\n8.......problem?");

      if(colorNum > 0) {
      printf("\n9.......");
	strcpy(hasColor, "Yes");
        printf("\n10.......");
	for(i = 0 ; i < colorNum; i++) {
        printf("\n11.......");
	   EXEC SQL
             FETCH colorP_crs INTO :tempPrice;

           if((tempPrice + price) <= result) {
             result = price + tempPrice;
	     pc = price;
	     printer = tempPrice;
	   }
        }
      }else if(colorNum == 0 && (pc == 0 || printer == 0)) {
	 strcpy(hasColor, "No");
         for(i = 0 ; i < printerNum; i++) {
           EXEC SQL
             FETCH printer_crs INTO :tempPrice;

           if((tempPrice + price) <= result) {
             result = price + tempPrice;
             pc = price;
             printer = tempPrice;
           }
        }
      }
    }
    printf("\nCheapest System (pc + printer): \nColor Printer = %s, \nPC = %f, \nPrinter = %f, \nTotal price = %f", hasColor, pc, printer, result);
  }
notfound:
  /* END OF DATA */
  printf("\nEND OF DATA");
  print_sql_state(SQLSTATE);
  exec sql close maker_type;
  exec sql close pc_crs;
  exec sql close printer_crs;
  return 0;
report_error:
  printf("\nERROR OCCURRED");
  print_sql_state(SQLSTATE);
  return 1;
}

void myTrim(char* str) {

 	int i = strlen(str) - 1;

	while(i >= 0) {
                if(str[i] == '\0') continue;
                if(str[i] == 32) str[i] = '\0';
                i--;
        }

}