#include "F77Radtran.h"
#include "GATS_Utilities.hpp"
#include "DatabaseHandles.h"
#include "GATS_DB.h"
#include "insert_profile_db.h"

#include "fortranfunctions.h"

#include <cassert>
#include <iostream>
#include <cmath>

using namespace GATS_Utilities;
using namespace gatsDBpp;


void insert_profile_db ( int *ievent, int *ml, 
	float *z,   float **qmix,     float **evar, float *NO_den, float *NO_evar,
        int *nl,    int *ngas,        int *igas,    int *igasdo,  
	int *ngdo,  int *id_product,  int *id_evar, int *db_id_NO, int *db_id_NO_err,
	float *zmax,float *zmin,      float *ztop,  float *zbot,   float *re,
        float *POUT,   int *npt,      float *zr,    float *pr,     float *tr,
	float **qmix_r,int *ngr,      int *igr,     int *nr  )
{

	static std::vector<float> alts;
	static int mcel;
	static int irfn=0;
	static int iplat = 0;
	static int c__1 = 1;
	static float wave= 1000.;
//static const int cycle=2;
	std::string sql;

	std::string tablename("L2_Data");
	DatabaseHandles* handles = GetDatabaseHandles();
 	GATS_DBptr handle = handles->Get("Level2");
	assert( handle );
	std::cout << "Zmax, Zmin, Ztop, Zbot = " << *zmax <<", " << *zmin <<", " << *ztop <<", " << *zbot << std::endl; 
//std::cout << "The lowest altitude is " << *std::min_element(zbot,zbot + *ngdo-1) << '\n';
//std::cout << "The highest altitude is "  << *std::max_element(ztop,ztop + *ngdo-1) << '\n';

/*
    Get altitudes once
*/
	if(alts.empty() ) {
	    sql = " select Altitude from Altitude_Keys order by AltKey";
	    GATS_DB_Results R = handle->Query(sql.c_str() );
	    for(unsigned i=0; i< R.size(); ++i) {
			alts.push_back( R[i][0].asFloat() );
	    }
	    mcel = (int)alts.size();
	}

	for(int igd=0; igd < *ngdo; ++igd) {

		if(id_product[igd] == 0) continue;

		sql = "delete from " + tablename + " where "
			+ "ID in ("+ConvertToString( id_product[igd] )+","
			+ ConvertToString( id_evar[igd] )
			+") AND Event = " + ConvertToString( *ievent); 
		handle->Query(sql.c_str() );

		if(igasdo[igd] < 0) {   // temperature retrieval store p and T
			int press_id = (id_product[igd] == 11) ? 10 : -id_product[igd];
	 		sql = "delete from " + tablename + " where "
				+ "ID= "+ConvertToString( press_id )+" AND Event = " 
				+ ConvertToString( *ievent) ;
			handle->Query(sql.c_str() );
			
			std::vector<float> gt(2* *nr, 0.0), zout(mcel),pout(mcel),
                            tout(mcel),grout(2*mcel);
			float tearth;

			::kp_mpath__(nr, &mcel, npt,  &irfn,
		   	   &c__1,    &mcel,	&press_id,	 re,
			   &mcel,  nr,  &wave,	&iplat,
			   &alts[0],  zr,  pr, tr,
               &gt[0],  &zout[0], &pout[0], &tout[0],
			   &grout[0], &tearth, POUT );

/*
for(unsigned ir = 0; ir < *nr; ++ir)
	std::cout << tr[ir] << " " << pr[ir] << std::endl;
std::cout << "&" << std::endl;

for(unsigned ir = 0; ir < mcel; ++ir)
        std::cout << tout[ir] << " " << pout[ir] << std::endl;

exit(23);
*/
			for(int ir=0; ir< mcel; ++ir) {
				if( zout[ir] > std::fabs(ztop[igd]) || zout[ir] < std::fabs(zbot[igd]) ) continue;
	 			std::vector < std::pair< std::string, ColData> > dataRow; 
   	  	 		dataRow.push_back( std::pair<std::string, ColData>("ID",
				  ColData( id_product[igd] )  ) );
     	 		dataRow.push_back( std::pair<std::string, ColData>("Event",
				  ColData( *ievent)  ) );
     	 		dataRow.push_back( std::pair<std::string, ColData>("AltKey",
				  ColData( ir  ) ) );
     	 		dataRow.push_back( std::pair<std::string, ColData>("Value",
  				 ColData( tout[ir]  ) ) );  
     	 		handle->InsertIntoTable(tablename.c_str(), dataRow );

	 			dataRow.clear() ; 
   	  	 		dataRow.push_back( std::pair<std::string, ColData>("ID",
			  	  ColData( press_id )  ) );
     	 		dataRow.push_back( std::pair<std::string, ColData>("Event",
				  ColData( *ievent)  ) );
     	 		dataRow.push_back( std::pair<std::string, ColData>("AltKey",
				  ColData( ir  ) ) );
     	 		dataRow.push_back( std::pair<std::string, ColData>("Value",
  			  	  ColData(  pout[ir] ) ) );  
     	 		handle->InsertIntoTable(tablename.c_str(), dataRow );
			}

		} else {  // a species or extinction
			int* p = std::find(igas,igas+ *ngas, igasdo[igd] );
			int ik = std::distance(igas, p);
			assert( ik < *ngas);
			std::vector<float> profile(mcel);

                        if(igasdo[igd] == 444) {
                         ::liner_(z,  (float*)qmix, &alts[0] , &profile[0], nl, &mcel);
                        } else {
		         ::liner_(z,  &qmix[ik][0], &alts[0] , &profile[0], nl, &mcel);
                        }
			for(int ir=0; ir< mcel; ++ir ) {
				if( alts[ir] > std::fabs(ztop[igd]) || alts[ir] < std::fabs(zbot[igd]) ) continue;
	 			std::vector < std::pair< std::string, ColData> > dataRow; 
   	  	 		dataRow.push_back( std::pair<std::string, ColData>("ID",
					ColData( id_product[igd] )  ) );
     	 		        dataRow.push_back( std::pair<std::string, ColData>("Event",
					ColData( *ievent)  ) );
     	 		        dataRow.push_back( std::pair<std::string, ColData>("AltKey",
					ColData( ir  ) ) );
     	 		        dataRow.push_back( std::pair<std::string, ColData>("Value",
  					ColData( profile[ir] ) ) );  
     	 		        handle->InsertIntoTable(tablename.c_str(), dataRow );
			}
		}
 
// If NO, also output # density
		std::vector<float> profile(mcel);
                if(igasdo[igd] == 800){
                        std::cout << "-> Inserting NO_den " << std::endl;                
   		        sql = "delete from " + tablename + " where "
 			+ "ID in ("+ConvertToString( *db_id_NO )+","
			+ ConvertToString( *db_id_NO_err )
			+") AND Event = " + ConvertToString( *ievent); 
		        handle->Query(sql.c_str() );
//			std::vector<float> profile(mcel);
 		        ::liner_(z,  &NO_den[0], &alts[0] , &profile[0], nl, &mcel);

			for(int ir=0; ir< mcel; ++ir ) {
			    if( alts[ir] > std::fabs(ztop[igd]) || alts[ir] < std::fabs(zbot[igd]) ) continue;
	 		    std::vector < std::pair< std::string, ColData> > dataRow; 
   	  	 	    dataRow.push_back( std::pair<std::string, ColData>("ID",
					ColData( *db_id_NO )  ) );
     	 		    dataRow.push_back( std::pair<std::string, ColData>("Event",
					ColData( *ievent)  ) );
     	 		    dataRow.push_back( std::pair<std::string, ColData>("AltKey",
					ColData( ir  ) ) );
     	 		    dataRow.push_back( std::pair<std::string, ColData>("Value",
  					ColData( profile[ir] ) ) );  
     	 		    handle->InsertIntoTable(tablename.c_str(), dataRow );
			}
// Also Insert extended pressure, temperature, and O3 arrays
                        int pres_ext_id = 110;
                        int temp_ext_id = 111;
                        int O3_ext_id = 112;
			int* p = std::find(igr,igr+ *ngr, 300 );
			int ik = std::distance(igr, p);
			assert( ik < *ngr);
		        ::liner_(zr,  &qmix_r[ik][0], &alts[0] , &profile[0], nr, &mcel);
		        sql = "delete from " + tablename + " where "
		        	+ "ID= "+ConvertToString( temp_ext_id )+" AND Event = "
                                + ConvertToString( *ievent); 
		        handle->Query(sql.c_str() );

	 		sql = "delete from " + tablename + " where "
				+ "ID= "+ConvertToString( pres_ext_id )+" AND Event = " 
				+ ConvertToString( *ievent);
			handle->Query(sql.c_str() );
			
	 		sql = "delete from " + tablename + " where "
				+ "ID= "+ConvertToString( O3_ext_id )+" AND Event = " 
				+ ConvertToString( *ievent);
			handle->Query(sql.c_str() );
			
			std::vector<float> gt(2* *nr, 0.0), zout(mcel),pout(mcel),
                            tout(mcel),grout(2*mcel);
			float tearth;

			::kp_mpath__(nr, &mcel, npt,  &irfn,
		   	   &c__1,    &mcel,	&pres_ext_id,	 re,
			   &mcel,  nr,  &wave,	&iplat,
			   &alts[0],  zr,  pr, tr,
                           &gt[0],  &zout[0], &pout[0], &tout[0],
			   &grout[0], &tearth, POUT );

			for(int ir=0; ir< mcel; ++ir) {
			     if( zout[ir] > std::fabs( *zmax) || zout[ir] < std::fabs( *zmin) ) continue;
	 		     std::vector < std::pair< std::string, ColData> > dataRow; 
   	  	 	     dataRow.push_back( std::pair<std::string, ColData>("ID",
			       ColData( temp_ext_id )  ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("Event",
			       ColData( *ievent)  ) );
     	   		     dataRow.push_back( std::pair<std::string, ColData>("AltKey",
			       ColData( ir  ) ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("Value",
  			       ColData( tout[ir]  ) ) );  
     	 		     handle->InsertIntoTable(tablename.c_str(), dataRow );

	 		     dataRow.clear() ; 
   	  	 	     dataRow.push_back( std::pair<std::string, ColData>("ID",
			       ColData( pres_ext_id )  ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("Event",
			       ColData( *ievent)  ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("AltKey",
			       ColData( ir  ) ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("Value",
  			       ColData(  pout[ir] ) ) );  
     	 		     handle->InsertIntoTable(tablename.c_str(), dataRow );

	 		     dataRow.clear() ; 
   	  	 	     dataRow.push_back( std::pair<std::string, ColData>("ID",
			       ColData( O3_ext_id )  ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("Event",
			       ColData( *ievent)  ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("AltKey",
			       ColData( ir  ) ) );
     	 		     dataRow.push_back( std::pair<std::string, ColData>("Value",
  			       ColData(  profile[ir] ) ) );  
     	 		     handle->InsertIntoTable(tablename.c_str(), dataRow );
			}

                }
		if(id_evar[igd] == 0) continue ;   // don't write out standard deviations if  id_evar is 0;

                ::liner_(z,  &evar[igd][0], &alts[0], &profile[0], nl, &mcel);

		for(int ir=0; ir< mcel; ++ir ) {
			if( alts[ir] > std::fabs(ztop[igd]) || alts[ir] < std::fabs(zbot[igd]) ) continue;
	 		std::vector < std::pair< std::string, ColData> > dataRow; 
   	   		dataRow.push_back( std::pair<std::string, ColData>("ID",
				ColData( id_evar[igd] )  ) );
     	 	        dataRow.push_back( std::pair<std::string, ColData>("Event",
				ColData( *ievent)  ) );
     	 	        dataRow.push_back( std::pair<std::string, ColData>("AltKey",
				ColData( ir ) ) );
     	 	        dataRow.push_back( std::pair<std::string, ColData>("Value",
                                ColData(  profile[ir] )  ) );
     	 	        handle->InsertIntoTable(tablename.c_str(), dataRow );
		}
 
                if(igasdo[igd] == 800){
//	        	std::vector<float> profile(mcel);
                        ::liner_(z,  &NO_evar[0], &alts[0], &profile[0], nl, &mcel);
 
		        for(int ir=0; ir< mcel; ++ir ) {
		            if( alts[ir] > std::fabs(ztop[igd]) || alts[ir] < std::fabs(zbot[igd]) ) continue;
	 	            std::vector < std::pair< std::string, ColData> > dataRow; 
   	   	            dataRow.push_back( std::pair<std::string, ColData>("ID",
				ColData( *db_id_NO_err )  ) );
     	 	            dataRow.push_back( std::pair<std::string, ColData>("Event",
				ColData( *ievent)  ) );
     	 	            dataRow.push_back( std::pair<std::string, ColData>("AltKey",
				ColData( ir ) ) );
     	 	            dataRow.push_back( std::pair<std::string, ColData>("Value",
                                ColData(  profile[ir] )  ) );
     	 	            handle->InsertIntoTable(tablename.c_str(), dataRow );
                        }

		}
	}
}