///////////////////////////////////////////////////////////
// File:			pg_soil_salinity_report.cpp
//
// Author:			Stefan Liersch
// Contact:			stefan.liersch@googlemail.com,
//					stefan.liersch@ufz.de
//
// Date:			2008-09-29
// last modified:	2008-09-29
//
//---------------------------------------------------------
// DESCRIPTION
//---------------------------------------------------------
//
//---------------------------------------------------------
// ToDo
//---------------------------------------------------------
// 
//
///////////////////////////////////////////////////////////

//---------------------------------------------------------------------

#include <iostream>

#include "saga_api/saga_api.h"
#include "saga_gui/SAGA_Frame.h"
#include "saga_gui/wksp_data_manager.h" // adding items to SAGA Workspace *g_pData

#include "saga_gui/convert_sl.h"

#include "pg_soil_salinity_report.h"
#include "pgsql/pgdatabase.h"
#include "pgsql/sqltable.h"

//---------------------------------------------------------------------

CSoilSalinityReport::CSoilSalinityReport(CSoilSalinityMap *pSSMap)
{
	m_pSSMap = pSSMap;

	f.open("report.txt");

	m_counter = 0;
}

CSoilSalinityReport::~CSoilSalinityReport()
{
}


//---------------------------------------------------------------------
void CSoilSalinityReport::Write_Report()
{
	int			nSelected			= m_pSSMap->Get_SimpleFeature()->Get_Selection_Count();
	int			minYear				= 0;
	int			maxYear				= 0;	// min and max year (all selected features)
	int			nYears				= 0;
	int			fn_Salinity_v		= m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity_v();
	int			fn_Salinity_l		= m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity_l();
	int			fn_Salinity_e		= m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity_e();
	double		salinity_field_v	= 0.0;
	double		salinity_field_l	= 0.0;
	double		salinity_field_e	= 0.0;
	double		salinity_diff_area_v= 0.0;
	double		salinity_diff_area_l= 0.0;
	double		salinity_diff_area_e= 0.0;
	CSG_String	sql;
	Csqltable*	pSQLTable;
	vector_i	v_FIDs;	// used to store ID (FID) of selected features
	vector_d	v_salinity;
	v_FIDs.resize(nSelected);

	// get selected features
	_Get_Selected_FIDs(nSelected, v_FIDs);	


	if ( g_pSAGA_Frame->db->GetMinMaxValue(m_pSSMap->Get_AttributeTableName().c_str(),m_pSSMap->Get_AttrFieldName()->Get_FieldName(2).c_str(),minYear,maxYear) ) {

		f << "Min: " << minYear << " Max: " << maxYear << std::endl;
		nYears = maxYear - minYear + 1;
		f << "nYears: " << nYears << std::endl;
	}

	v_salinity.resize(nYears);

	_Create_SAGA_SalinityTable(minYear, maxYear);

	double	smin = 1.1;
	double	smax = -0.1;
	double	smean = 0.0;
	double	ssum = 0.0;
	int		nvalid = 0;
	double	firstvalid = -1.0;
	double	lastvalid = -1.0;
	double	strend = 0.0;

	for (int feature = 0; feature < nSelected; feature++) {
		for (int year = minYear; year < nYears+minYear; year++) {
			v_salinity[year-minYear] = _Get_Salinity(_Get_SQL_Year_FID(year,v_FIDs[feature]));

			f << "year: " << year << " feature: " << v_FIDs[feature];
			f << " salinity: " << v_salinity[year-minYear] << std::endl;

			if ( v_salinity[year-minYear] > smax ) smax = v_salinity[year-minYear];
			if ( v_salinity[year-minYear] >= 0 && v_salinity[year-minYear] < smin ) smin = v_salinity[year-minYear];
			if ( v_salinity[year-minYear] >= 0 && v_salinity[year-minYear] <= 1.0) {
				ssum += v_salinity[year-minYear];
				nvalid++;
				if (firstvalid == -1.0) firstvalid = v_salinity[year-minYear];
				lastvalid = v_salinity[year-minYear];
			}
		}
		smean = ssum / nvalid;
		strend = lastvalid - firstvalid;
		
		_Add_Column_SalinityTable(feature,v_FIDs[feature], v_salinity, smin, smax, smean, strend);

		smin = 1.1;
		smax = -0.1;
		ssum = 0.0;
		smean = 0.0;
		nvalid = 0;
		firstvalid = -1.0;
		lastvalid = -1.0;
	}


	//pSQLTable = new Csqltable(m_pSSMap->Get_AttributeTableName().c_str());

	//if ( g_pSAGA_Frame->db->Get_QueryResult(_Get_SQL_SelectAll(v_FIDs[0]).c_str(), pSQLTable) ) {

	//	// Create report table and put to SAGA Workspace
	//	_Create_SAGA_ReportTable();

	//	_Create_SAGA_SalinityTable(nSelected);

	//	for (int field = 0; field < nSelected; field++) {

	//		sql = _Get_SQL_SelectAll(v_FIDs[field]);

	//		// load attribute table from db into pSQLTable
	//		g_pSAGA_Frame->db->Get_QueryResult(sql.c_str(), pSQLTable);

	//		// calculate salinity differences / trends
	//		if ( pSQLTable->Get_nrows() > 0 ) {

	//			// Write to SAGA report table
	//			_Write_SAGA_ReportTable(pSQLTable);

	//			salinity_field_v = convert_sl::StringToDouble( pSQLTable->GetValue(fn_Salinity_v,0) ) - 
	//				 convert_sl::StringToDouble( pSQLTable->GetValue(fn_Salinity_v,pSQLTable->Get_nrows()-1) ) ;

	//			salinity_field_l = convert_sl::StringToDouble( pSQLTable->GetValue(fn_Salinity_l,0) ) - 
	//				 convert_sl::StringToDouble( pSQLTable->GetValue(fn_Salinity_l,pSQLTable->Get_nrows()-1) ) ;

	//			salinity_field_e = convert_sl::StringToDouble( pSQLTable->GetValue(fn_Salinity_e,0) ) - 
	//				 convert_sl::StringToDouble( pSQLTable->GetValue(fn_Salinity_e,pSQLTable->Get_nrows()-1) ) ;

	//			
	//			salinity_diff_area_v += salinity_field_v;
	//			salinity_diff_area_l += salinity_field_l;
	//			salinity_diff_area_e += salinity_field_e;

	//			_Write_SAGA_FieldSummary(salinity_field_v,salinity_field_l,salinity_field_e, "Differences");
	//			m_pReportTable->Add_Record();
	//			m_counter++;
	//		}
	//	}
	//	m_pReportTable->Add_Record();
	//	m_counter++;
	//	m_pReportTable->Add_Record();
	//	m_pReportTable->Set_Value(m_counter,0,"Total Area");
	//	m_counter++;
	//	_Write_SAGA_FieldSummary(salinity_diff_area_v,salinity_diff_area_l,salinity_diff_area_e, "Diff tot.");

	//} // end if 

	//delete pSQLTable;
}
//---------------------------------------------------------------------

void CSoilSalinityReport::_Get_Selected_FIDs(int nSelected, vector_i& v_FIDs)
{
	// This function is not working if function:
	// pTable->Get_Record(i)->is_Selected() is being used.
	// Thus, Get_Shape(i)->is_Selected(i) is used instead.
	// Maybe the reason is in not sufficiently implemented CSG_SimpleFeatures?!

	CSG_Table	pTable			= m_pSSMap->Get_SimpleFeature()->Get_Table();
	int			FID_FieldNumber	= _Get_FID_FieldNumber();
	double		temp			= 0.0;
	int			index			= 0;
	
	if ( FID_FieldNumber >= 0 ) {
		for (int i = 0; i < m_pSSMap->Get_SimpleFeature()->Get_Count(); i++) {

			if ( m_pSSMap->Get_SimpleFeature()->Get_Shape(i)->is_Selected() ) {
				pTable.Get_Value(i, FID_FieldNumber, temp);
				v_FIDs[index] = int(temp);
				index++;
			}
		}
	} else {
		f << "Couldn't find field 'fid'" << std::endl;
	}
}
//---------------------------------------------------------------------

int CSoilSalinityReport::_Get_FID_FieldNumber()
{
	std::string fieldName;
	for (int field = 0; field < m_pSSMap->Get_SimpleFeature()->Get_Table().Get_Field_Count(); field++) {
		fieldName = m_pSSMap->Get_SimpleFeature()->Get_Table().Get_Field_Name(field);
		if ( fieldName.compare(m_pSSMap->Get_AttrFieldName()->Get_FieldName(1)) == 0 ) return (field);
	}
	return(-1);
}

//---------------------------------------------------------------------
CSG_String CSoilSalinityReport::_Get_SQL_SelectAll(int fid)
{
	std::ostringstream s;
	CSG_String sql;

	s << "SELECT * FROM " << m_pSSMap->Get_AttributeTableName();
	s << " WHERE fid = " << fid << " ORDER BY fid, yr ASC";

	sql = s.str().c_str();
	return(sql);
}
//---------------------------------------------------------------------

CSG_String CSoilSalinityReport::_Get_SQL_Year_FID(int year, int fid)
{
	std::ostringstream s;
	CSG_String sql;

	s << "SELECT " << m_pSSMap->Get_AttrFieldName()->Get_FieldName(2) << ", "; // column year
	s << m_pSSMap->Get_AttrFieldName()->Get_FieldName(5); // column salinity_v
	s << " FROM " << m_pSSMap->Get_AttributeTableName();
	s << " WHERE fid = " << fid;
	s << " AND " << m_pSSMap->Get_AttrFieldName()->Get_FieldName(2) << " = " << year;

	sql = s.str().c_str();
	return(sql);
}
//---------------------------------------------------------------------
double CSoilSalinityReport::_Get_Salinity(CSG_String sql)
{
	double salinity = -1.0;
	PGresult* res = g_pSAGA_Frame->db->ExecSQL_wx2(sql.c_str());
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		PQclear(res);
		return(salinity);
	} else {
		if (PQntuples(res) > 0 ) {
			salinity = convert_sl::StringToDouble(PQgetvalue(res,0,1));
		} else {
			salinity = -1.0;
		}
		PQclear(res);
		return(salinity);
	}
}
//---------------------------------------------------------------------

void CSoilSalinityReport::_Create_SAGA_ReportTable()
{
	m_pReportTable = SG_Create_Table();
	m_pReportTable->Set_Name("report");

	m_pReportTable->Add_Field("_", TABLE_FIELDTYPE_String, 0);
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(1).c_str(), TABLE_FIELDTYPE_Int, 1);	// FID
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(2).c_str(), TABLE_FIELDTYPE_Int, 2);	// Year
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(4).c_str(), TABLE_FIELDTYPE_String, 3);	// salinity
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(5).c_str(), TABLE_FIELDTYPE_Float, 4);	// salinity_v
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(6).c_str(), TABLE_FIELDTYPE_Float, 5);	// salinity_l
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(7).c_str(), TABLE_FIELDTYPE_Float, 6);	// salinity_e
	m_pReportTable->Add_Field(m_pSSMap->Get_AttrFieldName()->Get_FieldName(11).c_str(), TABLE_FIELDTYPE_String, 7);// plant

	// add table to SAGA workspace
	g_pData->Add(m_pReportTable);

}
//---------------------------------------------------------------------

void CSoilSalinityReport::_Create_SAGA_SalinityTable(int minYear, int maxYear)
{
	int rec_counter = 0;
	m_pSalinityTable = SG_Create_Table();
	m_pSalinityTable->Set_Name("report_salinity");

	m_pSalinityTable->Add_Field("Year", TABLE_FIELDTYPE_String, 0);

	for (int year = minYear; year < maxYear+1; year++) {
		m_pSalinityTable->Add_Record();
		m_pSalinityTable->Set_Value(year-minYear,0,convert_sl::Int2String(year).c_str());
		rec_counter++;
	}
	m_pSalinityTable->Add_Record();
	m_pSalinityTable->Set_Value(rec_counter,0,"Mean"); 
	m_pSalinityTable->Add_Record();
	rec_counter++;
	m_pSalinityTable->Set_Value(rec_counter,0,"Min");
	m_pSalinityTable->Add_Record();
	rec_counter++;
	m_pSalinityTable->Set_Value(rec_counter,0,"Max");
	m_pSalinityTable->Add_Record();
	rec_counter++;
	m_pSalinityTable->Set_Value(rec_counter,0,"Diff_max_min");
	m_pSalinityTable->Add_Record();
	rec_counter++;
	m_pSalinityTable->Set_Value(rec_counter,0,"Trend");

	g_pData->Add(m_pSalinityTable);
}
//---------------------------------------------------------------------
void CSoilSalinityReport::_Add_Column_SalinityTable(int field, int feature, vector_d& salinity,
													double min, double max, double mean, double trend)
{
	std::ostringstream s;
	s << "Field " << feature;
	m_pSalinityTable->Add_Field(s.str().c_str(), TABLE_FIELDTYPE_Double, field+1);

	unsigned int row = 0;
	for (row = 0; row < salinity.size(); row++) {
		m_pSalinityTable->Set_Value(row,field+1,salinity[row]);
	}
	m_pSalinityTable->Set_Value(row,field+1,mean);	row++;
	m_pSalinityTable->Set_Value(row,field+1,min);	row++;
	m_pSalinityTable->Set_Value(row,field+1,max);	row++;
	m_pSalinityTable->Set_Value(row,field+1,max-min); row++;
	m_pSalinityTable->Set_Value(row,field+1,trend);
}

//---------------------------------------------------------------------
void CSoilSalinityReport::_Write_SAGA_ReportTable(Csqltable* pSQLTable)
{
	std::ostringstream field;
	field << "Field " << pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_FID(),0);

	for (int row = 0; row < pSQLTable->Get_nrows(); row++) {
		m_pReportTable->Add_Record();
		if ( row == 0 ) m_pReportTable->Set_Value(m_counter,0,field.str().c_str());
		m_pReportTable->Set_Value(m_counter,1,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_FID(),row).c_str());
		m_pReportTable->Set_Value(m_counter,2,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_yr(),row).c_str());
		m_pReportTable->Set_Value(m_counter,3,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity(),row).c_str());
		m_pReportTable->Set_Value(m_counter,4,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity_v(),row).c_str());
		m_pReportTable->Set_Value(m_counter,5,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity_l(),row).c_str());
		m_pReportTable->Set_Value(m_counter,6,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_salinity_e(),row).c_str());
		m_pReportTable->Set_Value(m_counter,7,pSQLTable->GetValue(m_pSSMap->Get_AttrFieldName()->Get_FieldNumber_plant(),row).c_str());
		m_counter++;
	}

}
//---------------------------------------------------------------------

void CSoilSalinityReport::_Write_SAGA_FieldSummary(double sal_v, double sal_l, double sal_e, const char* tit)
{
	m_pReportTable->Add_Record();
	m_pReportTable->Set_Value(m_counter,0,tit);
	m_pReportTable->Set_Value(m_counter,4,sal_v);
	m_pReportTable->Set_Value(m_counter,5,sal_l);
	m_pReportTable->Set_Value(m_counter,6,sal_e);
	m_counter++;
}
//---------------------------------------------------------------------

//SQL
//select * from _attr_ssm_virtual_wua_fields
//where fid = 1
//or fid = 2
//order by fid, yr ASC