#include "shared_grade_book_db.h"

SCALE_LIST *global_scale_list = 0;
CATEGORY_LIST *global_cat_list = 0;
ITEM_LIST *global_item_list = 0;
LETTER_GRADE_LIST *global_letter_grades =0;
GRADE_LIST *global_grade_list = 0;
GRADE_LIST *global_grade_temp_list = 0;

char global_a_letter[MAX_GRADE_LEN +1];
double global_min = 0;
double  global_max = 0;
double global_average = 0;


static int callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
    SCALE *ptr = 0;

    ptr = (SCALE *)malloc(sizeof(SCALE));
    if(!ptr)
       cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db callback func");
    
    ptr->id = atoi(p_fields[0]);
    strncpy(ptr->grade, p_fields[1], MAX_GRADE_LEN +1);
    ptr->start = atof(p_fields[2]);
    ptr->end = atof(p_fields[3]);
    ptr->count = 0;
    ptr->next = 0;

    if(!global_scale_list->head)
	 global_scale_list->head = ptr;
    else
	 global_scale_list->current->next = ptr;
    global_scale_list->current = ptr;
    return 0;
}

int get_scales(CONFIG_STRUCT *conf, const char *my_db)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_scale_list = (SCALE_LIST *)malloc(sizeof(SCALE_LIST));
   global_scale_list->head = 0;
   global_scale_list->current = 0;

   ret = sqlite3_exec(db, 
		   "select * from Scales where grade != 'XXXXX'",
		   callback,
		   &nrecs,
		   &errmsg);

  if(errmsg)
  {
      sqlite3_free(errmsg);
      sqlite3_close(db);
      return 0;

  } 
  sqlite3_close(db);
  return 1;

}

static int category_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
    CATEGORY *ptr = 0;

    ptr = (CATEGORY *)malloc(sizeof(CATEGORY));
    if(!ptr)
       cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db category callback func");
    
    ptr->cat_id = atoi(p_fields[0]);
    strncpy(ptr->cat_name, p_fields[1], MAX_CAT_LEN +1);
    ptr->weight = atof(p_fields[2]);
    ptr->drop_lowest = atoi(p_fields[3]);
    ptr->number_members = 0;
    ptr->number_graded_items = 0;
    ptr->next = 0;

    if(!global_cat_list->head)
	 global_cat_list->head = ptr;
    else
	 global_cat_list->current->next = ptr;
    global_cat_list->current = ptr;
    return 0;
}

static int category_member_count(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
	global_cat_list->current->number_members = atoi(p_fields[0]);
	return 0;
}

static int grades_entered_count(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
	global_cat_list->current->number_graded_items = atoi(p_fields[0]);
	return 0;
}

static int grades_hided_count(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
	global_cat_list->current->hided_items = atoi(p_fields[0]);
	return 0;
}


int get_categories(CONFIG_STRUCT *conf, const char *my_db)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_cat_list = (CATEGORY_LIST *)malloc(sizeof(CATEGORY_LIST));
   global_cat_list->head = 0;
   global_cat_list->current = 0;

   ret = sqlite3_exec(db, 
		   "select * from Categories where catName != 'XXXXXXXXXXXX' order by catId",
		   category_callback,
		   &nrecs,
		   &errmsg);

  if(errmsg)
  {
      sqlite3_free(errmsg);
      sqlite3_close(db);
      return 0;

  } 
  if(global_cat_list && global_cat_list->head)
  {
     for(global_cat_list->current = global_cat_list->head; global_cat_list->current; global_cat_list->current = global_cat_list->current->next)
     {
          stmt = sqlite3_mprintf("select count(*) from Items where catId=%d", global_cat_list->current->cat_id);
          if(stmt)
	  {
             ret = sqlite3_exec(db, 
		   stmt,
		   category_member_count,
		   &nrecs,
		   &errmsg);
	     if(errmsg)
	     {
                 sqlite3_free(stmt);
                 sqlite3_free(errmsg);
                 sqlite3_close(db);
                 return 0;
	     }
             sqlite3_free(stmt);

	  }	  
     } 
  }
  if(global_cat_list && global_cat_list->head)
  {
     for(global_cat_list->current = global_cat_list->head; global_cat_list->current; global_cat_list->current = global_cat_list->current->next)
     {
          stmt = sqlite3_mprintf("select count(*) from Items where catId=%d and gradesEntered='yes'", global_cat_list->current->cat_id);
          if(stmt)
	  {
             ret = sqlite3_exec(db, 
		   stmt,
		   grades_entered_count,
		   &nrecs,
		   &errmsg);
	     if(errmsg)
	     {
                 sqlite3_free(stmt);
                 sqlite3_free(errmsg);
                 sqlite3_close(db);
                 return 0;
	     }
             sqlite3_free(stmt);

	  }	  
     } 
  }

  if(global_cat_list && global_cat_list->head)
  {
     for(global_cat_list->current = global_cat_list->head; global_cat_list->current; global_cat_list->current = global_cat_list->current->next)
     {
          stmt = sqlite3_mprintf("select count(*) from Items where catId=%d and hide='yes'", global_cat_list->current->cat_id);
          if(stmt)
	  {
             ret = sqlite3_exec(db, 
		   stmt,
		   grades_hided_count,
		   &nrecs,
		   &errmsg);
	     if(errmsg)
	     {
                 sqlite3_free(stmt);
                 sqlite3_free(errmsg);
                 sqlite3_close(db);
                 return 0;
	     }
             sqlite3_free(stmt);

	  }	  
     } 
  }

  sqlite3_close(db);
  return 1;

}

static int item_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
    ITEM *ptr = 0;

    ptr = (ITEM *)malloc(sizeof(ITEM));
    if(!ptr)
       cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db category callback func");
    
    ptr->item_id = atoi(p_fields[0]);
    strncpy(ptr->item_name, p_fields[1], MAX_ITEM_LEN +1);
    ptr->points = atof(p_fields[2]);
    ptr->due_date = atoi(p_fields[3]);
    strncpy(ptr->extra_credit, p_fields[4], MAX_EXTRA_CREDIT_LEN +1);
    strncpy(ptr->is_letter_grade, p_fields[5], MAX_BOOLEN_LEN +1);
    strncpy(ptr->grade_entered, p_fields[6], MAX_BOOLEN_LEN +1);
    strncpy(ptr->hide, p_fields[7], MAX_BOOLEN_LEN +1);
    ptr->cat_id = atoi(p_fields[8]);
    ptr->grades = 0;
    ptr->letters = 0;
    *(ptr->min) = '\0';
    *(ptr->max) = '\0';
    *(ptr->avg) = '\0';
    ptr->next = 0;

    if(!global_item_list->head)
	 global_item_list->head = ptr;
    else
	 global_item_list->current->next = ptr;
    global_item_list->current = ptr;
    return 0;
}

int get_cat_members(CONFIG_STRUCT *conf, int cat_id, const char *my_db)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_item_list = (ITEM_LIST *)malloc(sizeof(ITEM_LIST));
   global_item_list->head = 0;
   global_item_list->current = 0;
   
   
   stmt = sqlite3_mprintf("select *  from Items where catId=%d", cat_id);
   if(stmt)
   {
        ret = sqlite3_exec(db, stmt,item_callback,&nrecs,&errmsg);
   

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
            return 0;
	}
     sqlite3_free(stmt);
  } 
  sqlite3_close(db);
  return 1;
}

int get_items(CONFIG_STRUCT *conf, const char *my_db, int desc)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_item_list = (ITEM_LIST *)malloc(sizeof(ITEM_LIST));
   global_item_list->head = 0;
   global_item_list->current = 0;

   if(desc == 1)
   ret = sqlite3_exec(db, 
		   "select * from Items where itemName != 'XXXXXXXXXXXX' order by catId, dueDate desc",
		   item_callback,
		   &nrecs,
		   &errmsg);
   else
   ret = sqlite3_exec(db, 
		   "select * from Items where itemName != 'XXXXXXXXXXXX' order by catId, dueDate asc",
		   item_callback,
		   &nrecs,
		   &errmsg);


  if(errmsg)
  {
      sqlite3_free(errmsg);
      sqlite3_close(db);
      return 0;

  } 
  sqlite3_close(db);
  return 1;
}

int get_one_item(CONFIG_STRUCT *conf, int item_id, const char *my_db)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_item_list = (ITEM_LIST *)malloc(sizeof(ITEM_LIST));
   global_item_list->head = 0;
   global_item_list->current = 0;

   stmt = sqlite3_mprintf("select *  from Items where itemId=%d", item_id);
   if(stmt)
   {
         ret = sqlite3_exec(db, 
		   stmt,
		   item_callback,
		   &nrecs,
		   &errmsg);

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
            return 0;
	}
     sqlite3_free(stmt);

  } 
  sqlite3_close(db);
  return 1;
}

static int letter_grade_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
    LETTER_GRADE *ptr = 0;

    ptr = (LETTER_GRADE *)malloc(sizeof(LETTER_GRADE));
    if(!ptr)
       cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db letter grade callback func");
    
    ptr->letter_id = atoi(p_fields[0]);
    strncpy(ptr->letter, p_fields[1], MAX_GRADE_LEN +1);
    ptr->points = atof(p_fields[2]);
    ptr->item_id = atoi(p_fields[3]);
    ptr->count = 0;
    ptr->next = 0;

    if(!global_letter_grades->head)
	 global_letter_grades->head = ptr;
    else
	 global_letter_grades->current->next = ptr;
    global_letter_grades->current = ptr;
    return 0;
}

int get_letter_grades(CONFIG_STRUCT *conf, int item_id, const char *my_db)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_letter_grades = (LETTER_GRADE_LIST *)malloc(sizeof(LETTER_GRADE_LIST));
   global_letter_grades->head = 0;
   global_letter_grades->current = 0;

   stmt = sqlite3_mprintf("select *  from letterGrades where itemId=%d order by points desc", item_id);
   if(stmt)
   {
         ret = sqlite3_exec(db, 
		   stmt,
		   letter_grade_callback,
		   &nrecs,
		   &errmsg);

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
            return 0;
	}
       sqlite3_free(stmt);
   }
   return 1;
}
 
static int grade_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
    GRADE *ptr = 0;

    ptr = (GRADE *)malloc(sizeof(GRADE));
    if(!ptr)
       cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db grade callback func");
    
    strncpy(ptr->username, p_fields[0], MAX_USERNAME +1);
    ptr->item_id = atoi(p_fields[1]);
    ptr->score = atof(p_fields[2]);
    strncpy(ptr->excuse, p_fields[3], MAX_BOOLEN_LEN +1);
    strncpy(ptr->pending, p_fields[4], MAX_BOOLEN_LEN +1);
    
    ptr->comment = malloc_str(p_fields[5]);
    ptr->letter_id = atoi(p_fields[6]);
    ptr->timestamp = atoi(p_fields[7]);
    strncpy(ptr->is_letter_grade, p_fields[8], MAX_BOOLEN_LEN +1);
    strncpy(ptr->drop, "no", MAX_BOOLEN_LEN +1);
    ptr->next = 0;

    if(!global_grade_list->head)
	 global_grade_list->head = ptr;
    else
	 global_grade_list->current->next = ptr;
    global_grade_list->current = ptr;
    return 0;
}



int get_current_grade_list(CONFIG_STRUCT *conf, int item_id, const char *mydb)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_grade_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST));
   global_grade_list->head = 0;
   global_grade_list->current = 0;

   stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.comments, G.letterId, G.timestamp, isLetterGrade from Grades G, Items, (select max(timestamp) as maxDate, username, itemId from Grades where itemId=%d group by username, itemId) M where G.username = M.username and G.timestamp = M.maxDate and G.itemId=Items.itemId", item_id);
   if(stmt)
   {
         ret = sqlite3_exec(db, stmt,grade_callback,&nrecs,&errmsg);

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
	    cs_critical_error(ERR_GRADES_DB, "get current grade list");
            return 0;
	}
       sqlite3_free(stmt);
   }

   sqlite3_close(db);
   return 1;
}
  
int get_user_score_logs(CONFIG_STRUCT *conf, int item_id, const char *username, const char *mydb)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_grade_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST));
   global_grade_list->head = 0;
   global_grade_list->current = 0;

   stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.comments, G.letterId, G.timestamp, isLetterGrade from Grades G, Items where G.itemId=%d and G.itemId=Items.itemId and G.username='%s'", item_id, username);
   if(stmt)
   {
         ret = sqlite3_exec(db, stmt,grade_callback,&nrecs,&errmsg);

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
	    cs_critical_error(ERR_GRADES_DB, "get current grade list");
            return 0;
	}
       sqlite3_free(stmt);
   }

   sqlite3_close(db);
   return 1;
}


static int a_letter_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
     strncpy(global_a_letter, p_fields[0], MAX_GRADE_LEN +1);
     return 0;
}


int update_letter_log_list(CONFIG_STRUCT *conf,  int item_id, const char *mydb, GRADE_LIST *list)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      cs_critical_error(ERR_GRADES_DB, "get  grade list");
   if(list && list->head)
   {
      for(list->current = list->head; list->current; list->current = list->current->next)
      {
	 stmt = sqlite3_mprintf("select letter from LetterGrades where itemId=%d and letterId=%d", item_id, list->current->letter_id);
         if(stmt)
	 {
            ret = sqlite3_exec(db, stmt, a_letter_callback,&nrecs,&errmsg);
	    if(errmsg)
	    {
		sqlite3_free(stmt);
		sqlite3_free(errmsg);
		sqlite3_close(db);
                cs_critical_error(ERR_GRADES_DB, "get a letter grade error");
		
		
	    }

	    strncpy(list->current->letter, global_a_letter, MAX_GRADE_LEN +1);

	    sqlite3_free(stmt);
	     
	 }
      }

   }
   sqlite3_close(db);
   return 1;

}

static int grade_temp_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name)
{
    GRADE *ptr = 0;

    ptr = (GRADE *)malloc(sizeof(GRADE));
    if(!ptr)
       cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db grade callback func");
    
    strncpy(ptr->username, p_fields[0], MAX_USERNAME +1);
    ptr->item_id = atoi(p_fields[1]);
    ptr->score = atof(p_fields[2]);
    strncpy(ptr->excuse, p_fields[3], MAX_BOOLEN_LEN +1);
    strncpy(ptr->pending, p_fields[4], MAX_BOOLEN_LEN +1);
    ptr->letter_id = atoi(p_fields[5]);
    ptr->timestamp = atoi(p_fields[6]);
    strncpy(ptr->drop, "no", MAX_BOOLEN_LEN +1);
    if(strlen(p_fields[7]))
       ptr->comment = malloc_str(p_fields[7]);
    else
       ptr->comment =0;
    ptr->next = 0;

    if(!global_grade_temp_list->head)
	 global_grade_temp_list->head = ptr;
    else
	 global_grade_temp_list->current->next = ptr;
    global_grade_temp_list->current = ptr;
    return 0;
}

int get_one_item_grade_list(CONFIG_STRUCT *conf, int item_id, const char *mydb)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_grade_temp_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST));
   global_grade_temp_list->head = 0;
   global_grade_temp_list->current = 0;

   stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.letterId , G.timestamp, G.comments from Grades G, (select max(timestamp) as maxDate, username, itemId from Grades where itemId=%d group by username, itemId) M where G.username = M.username and G.timestamp = M.maxDate", item_id);
   if(stmt)
   {
         ret = sqlite3_exec(db, stmt,grade_temp_callback,&nrecs,&errmsg);

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
	    cs_critical_error(ERR_GRADES_DB, "get one item  grade list");
            return 0;
	}
       sqlite3_free(stmt);
   }
   sqlite3_close(db);
   return 1;
}


int get_student_one_item_grade(CONFIG_STRUCT *conf, int item_id, const char *username, const char *mydb)
{
   int ret, nrecs = 0;
   char *errmsg = 0;
   sqlite3 *db;
   char db_name[MAX_PATH +1];
   char *stmt =0;

   snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb);  
   if(!file_exists(db_name))
       return 0;

   ret = sqlite3_open(db_name, &db);
   if(ret)
      return 0;
   global_grade_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST));
   global_grade_list->head = 0;
   global_grade_list->current = 0;

   stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.comments, G.letterId, G.timestamp, isLetterGrade from Grades G, Items, (select max(timestamp) as maxDate, username, itemId from Grades where itemId=%d group by username, itemId) M where G.username='%s' and G.username = M.username and G.timestamp = M.maxDate and G.itemId=Items.itemId", item_id, username);
   if(stmt)
   {
         ret = sqlite3_exec(db, stmt,grade_callback,&nrecs,&errmsg);

        if(errmsg)
        {
            sqlite3_free(errmsg);
            sqlite3_free(stmt);
            sqlite3_close(db);
	    cs_critical_error(ERR_GRADES_DB, "get current grade list");
            return 0;
	}
       sqlite3_free(stmt);
   }

   sqlite3_close(db);
   return 1;
}

   
