# include "dvopsps.h"
# define DEBUG 0

int insert_objects_dvopsps_catalog (Catalog *catalog, char *basename, MYSQL *mysql) {

  off_t i;
  int missingID = 0;

  IOBuffer ave_buffer, sec_buffer;
  ave_buffer.Nalloc = 0;
  sec_buffer.Nalloc = 0;

  Average *average = catalog->average;
  SecFilt *secfilt = catalog->secfilt;
  int Nsecfilt = catalog->Nsecfilt;

  off_t found = 0;

  char *cleanname = strcreate (basename);
  for (i = 0; i < strlen(cleanname); i++) {
    if (cleanname[i] == '.') cleanname[i] = '_';
    if (cleanname[i] == '/') cleanname[i] = '_';
  }
  insert_objects_mysql_create_tables (cleanname, mysql);

  INITTIME;
  insert_objects_mysql_init (&ave_buffer, &sec_buffer, cleanname);
  int Ninsert = 0;

  // NOTE for testing, just do a few objects
  if (!mysql) {
    catalog[0].Naverage = 5;
  }
  for (i = 0; i < catalog[0].Naverage; i++) {

    if (average[i].extID == 0) {
      missingID ++;
    }

    // XXX check return status
    insert_objects_mysql_value (&ave_buffer, &sec_buffer, &average[i], &secfilt[i*Nsecfilt], Nsecfilt);

    // fprintf (stderr, "%f : %d %d %d\n", average[i].ChiSqPM, isinf(average[i].ChiSqPM), isnan(average[i].ChiSqPM), isfinite(average[i].ChiSqPM));

    // Average is a bigger table, but Nsecfilt*secfilt might be bigger, so check both
    if ((ave_buffer.Nbuffer > MAX_BUFFER) || (sec_buffer.Nbuffer > MAX_BUFFER)) {
      insert_objects_mysql_commit (&ave_buffer, &sec_buffer, mysql);
      if (DEBUG) fprintf (stderr, "inserted %d rows\n", Ninsert);
      Ninsert = 0;
      if (0) {
	FlushIOBuffer (&ave_buffer);
	FlushIOBuffer (&sec_buffer);
      } else {
	ave_buffer.Nbuffer = 0;
	bzero (ave_buffer.buffer, ave_buffer.Nalloc);
	sec_buffer.Nbuffer = 0;
	bzero (sec_buffer.buffer, sec_buffer.Nalloc);
      }
      insert_objects_mysql_init (&ave_buffer, &sec_buffer, cleanname);
    }
    Ninsert ++;
    found ++;
  }
  insert_objects_mysql_commit (&ave_buffer, &sec_buffer, mysql);
  if (VERBOSE) fprintf (stderr, "inserted "OFF_T_FMT" average, "OFF_T_FMT" secfilt\n", catalog[0].Naverage, Nsecfilt*catalog[0].Naverage);
  FreeIOBuffer (&ave_buffer);
  FreeIOBuffer (&sec_buffer);
  free (cleanname);

  MARKTIME("-- inserted "OFF_T_FMT" objects in %f sec, skipped %d without IDs\n", found, dtime, missingID);
  return (TRUE);
}

int insert_objects_mysql_create_tables (char *basename, MYSQL *mysql) {

  int status;
  IOBuffer buffer;
  InitIOBuffer (&buffer, 1024);
  // MYSQL_RES *result;

  PrintIOBuffer (&buffer, "DROP TABLE %s_cpt\n", basename);
  status = mysql_query(mysql, buffer.buffer); 
  if (status) {
    fprintf (stderr, "failed to drop table:\n");
    fprintf (stderr, "%s\n", mysql_error(mysql));
  }
  buffer.Nbuffer = 0;
  bzero (buffer.buffer, buffer.Nalloc);

  PrintIOBuffer (&buffer, "CREATE TABLE %s_cpt ("
		 "RA DOUBLE,  "          
		 "DEC_ DOUBLE,  "         
		 "RA_ERR FLOAT,  "      
		 "DEC_ERR FLOAT,  "     
		 "U_RA FLOAT,  "        
		 "U_DEC FLOAT,  "       
		 "V_RA_ERR FLOAT,  "    
		 "V_DEC_ERR FLOAT,  "   
		 "PAR FLOAT,  "         
		 "PAR_ERR FLOAT,  "     
		 "CHISQ_POS FLOAT,  "   
		 "CHISQ_PM FLOAT,  "    
		 "CHISQ_PAP FLOAT,  "   
		 "MEAN_EPOCH INT,  "  
		 "TIME_RANGE INT,  "  
		 "PSF_QF FLOAT, "      
		 "PSF_QF_PERF FLOAT,  " 
		 "STARGAL_SEP FLOAT,  " 
		 "NUMBER_POS SMALLINT,  "  
		 "NMEASURE SMALLINT,  "    
		 "NMISSING SMALLINT,  "    
		 "NEXTEND SMALLINT,  "     
		 "OFF_MEASURE INT,  " 
		 "OFF_MISSING INT,  " 
		 "OFF_EXTEND INT,  "  
		 "FLAGS INT,  "       
		 "PHOTFLAGS_U INT,  " 
		 "PHOTFLAGS_L INT,  " 
		 "OBJ_ID INT,  "      
		 "CAT_ID INT,  "      
		 "EXT_ID BIGINT"
		 ")\n", basename);

  if (DEBUG) fprintf (stderr, "%s\n", buffer.buffer);
  status = mysql_query(mysql, buffer.buffer); 
  if (status) {
    fprintf (stderr, "failed to create table:\n");
    fprintf (stderr, "%s\n", mysql_error(mysql));
    fprintf (stderr, "Nbuffer: %d\n", buffer.Nbuffer);
  }
  // result = mysql_store_result (mysql);
  // mysql_free_result (result);

  buffer.Nbuffer = 0;
  bzero (buffer.buffer, buffer.Nalloc);

  PrintIOBuffer (&buffer, "DROP TABLE %s_cps\n", basename);
  status = mysql_query(mysql, buffer.buffer); 
  if (status) {
    fprintf (stderr, "failed to drop table:\n");
    fprintf (stderr, "%s\n", mysql_error(mysql));
  }
  buffer.Nbuffer = 0;
  bzero (buffer.buffer, buffer.Nalloc);

  PrintIOBuffer (&buffer, "CREATE TABLE %s_cps ("
		 "MAG FLOAT,  "             
		 "MAG_AP FLOAT,  "             
		 "MAG_KRON FLOAT,  "        
		 "MAG_KRON_ERR FLOAT,  "    
		 "MAG_ERR FLOAT,  "         
		 "MAG_CHI FLOAT,  "         
		 "STACK_PSF_MAG FLOAT,  "        
		 "STACK_PSF_MAG_ERR FLOAT,  "    
		 "STACK_KRON_MAG FLOAT,  "       
		 "STACK_KRON_MAG_ERR FLOAT,  "   
		 "FLAGS INT,  "           
		 "NCODE SMALLINT,  "           
		 "NUSED SMALLINT,  "           
		 "MAG_20 SMALLINT,  "          
		 "MAG_80 SMALLINT,  "          
		 "UBERCAL_DIST SMALLINT,  "    
		 "MAG_STDEV SMALLINT,  "       
		 "STACK_DETECT_ID BIGINT" 
		 ")\n", basename);

 // 		 "FLUX_PSF FLOAT,  "        
 //  		 "FLUX_PSF_ERR FLOAT,  "    
 //  		 "FLUX_KRON FLOAT,  "       
 //  		 "FLUX_KRON_ERR FLOAT,  "   

  if (DEBUG) fprintf (stderr, "%s\n", buffer.buffer);
  status = mysql_query(mysql, buffer.buffer); 
  if (status) {
    fprintf (stderr, "failed to create table:\n");
    fprintf (stderr, "%s\n", mysql_error(mysql));
    fprintf (stderr, "Nbuffer: %d\n", buffer.Nbuffer);
  }
  // result = mysql_store_result (mysql);
  // mysql_free_result (result);

  FreeIOBuffer (&buffer);
  return TRUE;
}

int insert_objects_mysql_init (IOBuffer *ave_buffer, IOBuffer *sec_buffer, char *basename) {

  if (ave_buffer->Nalloc == 0) {
    InitIOBuffer (ave_buffer, 1024);
  }

  PrintIOBuffer (ave_buffer, "INSERT INTO %s_cpt ("
		 "RA, "          
		 "DEC_, "         
		 "RA_ERR, "      
		 "DEC_ERR, "     
		 "U_RA, "        
		 "U_DEC, "       
		 "V_RA_ERR, "    
		 "V_DEC_ERR, "   
		 "PAR, "         
		 "PAR_ERR, "     
		 "CHISQ_POS, "   
		 "CHISQ_PM, "    
		 "CHISQ_PAP, "   
		 "MEAN_EPOCH, "  
		 "TIME_RANGE, "  
		 "PSF_QF, "      
		 "PSF_QF_PERF, " 
		 "STARGAL_SEP, " 
		 "NUMBER_POS, "  
		 "NMEASURE, "    
		 "NMISSING, "    
		 "NEXTEND, "     
		 "OFF_MEASURE, " 
		 "OFF_MISSING, " 
		 "OFF_EXTEND, "  
		 "FLAGS, "       
		 "PHOTFLAGS_U, " 
		 "PHOTFLAGS_L, " 
		 "OBJ_ID, "      
		 "CAT_ID, "      
		 "EXT_ID"
		 ") VALUES \n", basename);

  if (sec_buffer->Nalloc == 0) {
    InitIOBuffer (sec_buffer, 1024);
  }

  PrintIOBuffer (sec_buffer, "INSERT INTO %s_cps ("
		 "MAG, "             
		 "MAG_AP, "             
		 "MAG_KRON, "        
		 "MAG_KRON_ERR, "    
		 "MAG_ERR, "         
		 "MAG_CHI, "         
		 "STACK_PSF_MAG, "        
		 "STACK_PSF_MAG_ERR, "    
		 "STACK_KRON_MAG, "       
		 "STACK_KRON_MAG_ERR, "   
		 "FLAGS, "           
		 "NCODE, "           
		 "NUSED, "           
		 "MAG_20, "          
		 "MAG_80, "          
		 "UBERCAL_DIST, "    
		 "MAG_STDEV, "       
		 "STACK_DETECT_ID " 
		 ") VALUES \n", basename);
  return TRUE;
}

# define PRINT_FLOAT(BUFFER,FIELD,FORMAT)		  \
  if (isinf(FIELD) || isnan(FIELD)) PrintIOBuffer (BUFFER, "NULL, ");	\
  else PrintIOBuffer (BUFFER, FORMAT, FIELD); 

int insert_objects_mysql_value (IOBuffer *ave_buffer, IOBuffer *sec_buffer, Average *average, SecFilt *secfilt, int Nsecfilt) {

  int i;

// XXX this bit could/should be autocoded...
  PrintIOBuffer (ave_buffer, " (");
  PRINT_FLOAT(ave_buffer, average->R,  	     "%.8f, ");  // 0.036 mas precision 
  PRINT_FLOAT(ave_buffer, average->D,  	     "%.8f, ");  // 0.036 mas precision 
  PRINT_FLOAT(ave_buffer, average->dR, 	     "%.5f, ");  // 0.010 mas precision
  PRINT_FLOAT(ave_buffer, average->dD, 	     "%.5f, ");  // 0.010 mas precision
  PRINT_FLOAT(ave_buffer, average->uR, 	     "%.5f, ");  // 0.010 mas/yr precision
  PRINT_FLOAT(ave_buffer, average->uD, 	     "%.5f, ");
  PRINT_FLOAT(ave_buffer, average->duR,      "%.5f, ");
  PRINT_FLOAT(ave_buffer, average->duD,      "%.5f, ");
  PRINT_FLOAT(ave_buffer, average->P,        "%.5f, ");
  PRINT_FLOAT(ave_buffer, average->dP,       "%.5f, ");
  PRINT_FLOAT(ave_buffer, average->ChiSqAve, "%.4f, ");
  PRINT_FLOAT(ave_buffer, average->ChiSqPM,  "%.4f, ");
  PRINT_FLOAT(ave_buffer, average->ChiSqPar, "%.4f, ");
  PrintIOBuffer (ave_buffer, "%d, ",  average->Tmean);          
  PrintIOBuffer (ave_buffer, "%d, ",  average->Trange);         
  PRINT_FLOAT(ave_buffer, average->psfQF,     "%.3f, ");
  PRINT_FLOAT(ave_buffer, average->psfQFperf, "%.3f, ");
  PRINT_FLOAT(ave_buffer, average->stargal,   "%.3f, ");
  PrintIOBuffer (ave_buffer, "%hu, ", average->Npos);           
  PrintIOBuffer (ave_buffer, "%hu, ", average->Nmeasure);       
  PrintIOBuffer (ave_buffer, "%hu, ", average->Nmissing);       
  PrintIOBuffer (ave_buffer, "%hu, ", average->Nextend);        
  PrintIOBuffer (ave_buffer, "%d, ",  average->measureOffset);  
  PrintIOBuffer (ave_buffer, "%d, ",  average->missingOffset);  
  PrintIOBuffer (ave_buffer, "%d, ",  0); // was : average->extendOffset);   
  PrintIOBuffer (ave_buffer, "%u, ",  average->flags);          
  PrintIOBuffer (ave_buffer, "%u, ",  average->photFlagsUpper); 
  PrintIOBuffer (ave_buffer, "%u, ",  average->photFlagsLower); 
  PrintIOBuffer (ave_buffer, "%u, ",  average->objID);          
  PrintIOBuffer (ave_buffer, "%u, ",  average->catID);          
  PrintIOBuffer (ave_buffer, "%lu",   average->extID);          
  PrintIOBuffer (ave_buffer, "),\n");

  for (i = 0; i < Nsecfilt; i++) {
    float meanPSFMag    = -999.0;
    float meanPSFMagErr = -999.0;
    if (isfinite(secfilt->dM) && isfinite(secfilt->M)) {
      meanPSFMag = secfilt->M;
      meanPSFMagErr = secfilt->dM;
    }

    float meanKronMag    = -999.0;
    float meanKronMagErr = -999.0;
    if (isfinite(secfilt->dMkron) && isfinite(secfilt->Mkron) && (secfilt->dMkron < 0.333)) {
      meanKronMag = secfilt->Mkron;
      meanKronMagErr = secfilt->dMkron;
    }

    float meanApMag    = -999.0;
    if (isfinite(secfilt->Map)) {
      meanApMag = secfilt->Map;
    }

    float stackPSFMag    = -999.0;
    float stackPSFMagErr = -999.0;
    if (isfinite(secfilt->dFpsfStk) && isfinite(secfilt->FpsfStk) && (secfilt->dFpsfStk > 0.0)) {
      float SN = secfilt->FpsfStk / secfilt->dFpsfStk;
      if (SN > 3.0) {
	stackPSFMag = -2.5 * log10(secfilt->FpsfStk) + 8.9;
	stackPSFMagErr = secfilt->dFpsfStk / secfilt->FpsfStk;
      }
    }

    float stackKronMag    = -999.0;
    float stackKronMagErr = -999.0;
    if (isfinite(secfilt->dFkronStk) && isfinite(secfilt->FkronStk) && (secfilt->dFkronStk > 0.0)) {
      float SN = secfilt->FkronStk / secfilt->dFkronStk;
      if (SN > 3.0) {
	stackKronMag = -2.5 * log10(secfilt->FkronStk) + 8.9;
	stackKronMagErr = secfilt->dFkronStk / secfilt->FkronStk;
      }
    }

    PrintIOBuffer (sec_buffer, " (");
    PRINT_FLOAT(sec_buffer, meanPSFMag,      "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, meanApMag,       "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, meanKronMag,     "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, meanKronMagErr,  "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, meanPSFMagErr,   "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, secfilt->Mchisq, "%.6f, ");
    PRINT_FLOAT(sec_buffer, stackPSFMag,     "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, stackPSFMagErr,  "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, stackKronMag,    "%.6f, "); // umag precision
    PRINT_FLOAT(sec_buffer, stackKronMagErr, "%.6f, "); // 0.36 umag precision
    PrintIOBuffer (sec_buffer, "%u,   ", secfilt->flags);       
    PrintIOBuffer (sec_buffer, "%hd,  ", secfilt->Ncode);        
    PrintIOBuffer (sec_buffer, "%hd,  ", secfilt->Nused);        
    PrintIOBuffer (sec_buffer, "%.6f, ", secfilt->Mmin);         
    PrintIOBuffer (sec_buffer, "%.6f, ", secfilt->Mmax);         
    PrintIOBuffer (sec_buffer, "%hd, ", secfilt->ubercalDist);  
    PrintIOBuffer (sec_buffer, "%.6f, ", secfilt->Mstdev);       
    PrintIOBuffer (sec_buffer, "%lu  ", secfilt->stackDetectID);
    PrintIOBuffer (sec_buffer, "),\n");
    secfilt ++;
  }
  return TRUE;
}
    
int insert_objects_mysql_commit (IOBuffer *ave_buffer, IOBuffer *sec_buffer, MYSQL *mysql) {

  MYSQL_RES *result;

  // check that the last two chars are ,\n and replace with ;\n
  if (!strcmp(&ave_buffer->buffer[ave_buffer->Nbuffer-2], ",\n")) {
    ave_buffer->buffer[ave_buffer->Nbuffer-2] = ';';
  } else {
    fprintf (stderr, "invalid sql?\n");
    return FALSE;
  }

  // check that the last two chars are ,\n and replace with ;\n
  if (!strcmp(&sec_buffer->buffer[sec_buffer->Nbuffer-2], ",\n")) {
    sec_buffer->buffer[sec_buffer->Nbuffer-2] = ';';
  } else {
    fprintf (stderr, "invalid sql?\n");
    return FALSE;
  }

  // XXX check return status
  if (mysql) {
    int status;
    if (DEBUG) fprintf (stderr, "%s\n", ave_buffer->buffer);
    status = mysql_query(mysql, ave_buffer->buffer); 
    if (status) {
      fprintf (stderr, "error with insert:\n");
      fprintf (stderr, "%s\n", mysql_error(mysql));
      fprintf (stderr, "Nbuffer: %d\n", ave_buffer->Nbuffer);
    }
    result = mysql_store_result (mysql);
    if (result) mysql_free_result (result);

    if (DEBUG) fprintf (stderr, "%s\n", sec_buffer->buffer);
    status = mysql_query(mysql, sec_buffer->buffer); 
    if (status) {
      fprintf (stderr, "error with insert:\n");
      fprintf (stderr, "%s\n", mysql_error(mysql));
      fprintf (stderr, "Nbuffer: %d\n", sec_buffer->Nbuffer);
    }
    result = mysql_store_result (mysql);
    if (result) mysql_free_result (result);
  } else {
    fprintf (stderr, "%s\n", sec_buffer->buffer);
  }

  return TRUE;
}
