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


//---------------------------------------------------------------------
#include "soil_salinity_map.h"
#include "pgsql/pg_menu_db.h"
//#include "pgsql/pgdatabase.h"

#include "saga_gui/saga_frame.h"		// location of class Cpgdatabase
										// using global *g_pSAGA_Frame pointer
#include "saga_gui/helper.h"			// using function MSG_General_Add()
#define MSG_ADD(s)	MSG_General_Add(s, true, true);

#include <fstream>
//---------------------------------------------------------------------


//---------------------------------------------------------------------
// use this constructor to load a soil salinity map
CSoilSalinityMap::CSoilSalinityMap(CSG_SimpleFeature* pSimpleFeature)
{
	m_pSimpleFeature	= pSimpleFeature;

	m_pSimpleFeature->Set_Category(SF_CATEGORY_SoilSalinityMap);

	m_MapName			= pSimpleFeature->Get_FeatureName();

	_Assign_TableNames(m_MapName);

	m_PK_colname = "PK_ID";

	m_nSoilSalinityVariables = 14;

	_Resize_Weights(m_nSoilSalinityVariables);

	if ( _Load_Weights() ) {
	}
}

//---------------------------------------------------------------------
// use this constructor to create a soil salinity map
// on the basis of a shapefile
CSoilSalinityMap::CSoilSalinityMap(CSG_Shapes *pShapes)
{
	if ( _Export_Shape_As_SoilSalinityMap(pShapes) ) {

		_Assign_TableNames(m_MapName);

		m_PK_colname = "PK_ID";

		_Create_External_Attributes();
	}
}
//---------------------------------------------------------------------


CSoilSalinityMap::~CSoilSalinityMap()
{
}



//---------------------------------------------------------------------
//
//	PUBLIC FUNCTIONS
//
//---------------------------------------------------------------------
bool CSoilSalinityMap::Set_Default_Weights()
{
	// it seems to be more conventional not to save
	// the weights automatically by loading the defaults
	// in the weight dialog.
	// only if button 'save' is pressed the settings are saved

	//m_v_weights[0] = m_Default_Weights.SoilColourLocal;
	//m_v_weights[1] = m_Default_Weights.nLeaching;
	//m_v_weights[2] = m_Default_Weights.WQ_Leaching;
	//m_v_weights[3] = m_Default_Weights.WQ_Irrigation;
	//m_v_weights[4] = m_Default_Weights.WVolumeIrrigation;
	//m_v_weights[5] = m_Default_Weights.Evenness;
	//m_v_weights[6] = m_Default_Weights.Morphology;
	//m_v_weights[7] = m_Default_Weights.SoilTexture;
	//m_v_weights[8] = m_Default_Weights.PlantGrowth;
	//m_v_weights[9] = m_Default_Weights.GWSalinity;
	//m_v_weights[10] = m_Default_Weights.GWLevel;
	//m_v_weights[11] = m_Default_Weights.nChannels;
	//m_v_weights[12] = m_Default_Weights.Distance;
	//m_v_weights[13] = m_Default_Weights.Maintanance;

	//if ( Update_Weights(m_v_weights) ) {
	//	return(true);
	//} else {
	//	return(false);
	//}
	return(true);
}
//---------------------------------------------------------------------
bool CSoilSalinityMap::Update_Weights(vector_i v_weights)
{
	_Delete_TableContent_Weights();

	std::ostringstream sql;

	sql << "INSERT INTO " << Get_WeightsTableName() << "(";
	sql << "sc_l, nleaching, wq_l, ";
	sql << "wq_i, wv_i, even, morph, ";
	sql << "s_t, pg, gw_s, gw_l, ";
	sql << "nchannels, distance, maintanance) ";
	sql << "VALUES(";
	for (int i = 0; i < m_nSoilSalinityVariables-1; i++) {
		sql << v_weights[i] << ", ";
	}
	sql << v_weights[m_nSoilSalinityVariables-1] << ")";

	if ( g_pSAGA_Frame->db->ExecSQL_wx(sql.str().c_str()) ) {
		for (int i = 0; i < m_nSoilSalinityVariables; i++) {
			m_v_weights[i] = v_weights[i];
		}
		MSG_ADD("Update weights ok");
		return(true);
	} else {
		MSG_ADD("Update weights NOT successful");
		return(false);
	}
}
//---------------------------------------------------------------------

bool CSoilSalinityMap::_Delete_TableContent_Weights()
{
	std::ostringstream sql;

	sql << "DELETE FROM " << Get_WeightsTableName();

	if ( g_pSAGA_Frame->db->ExecSQL_wx(sql.str().c_str()) )
		return(true);
	else
		return(false);
}

//---------------------------------------------------------------------
//
//	PRIVATE FUNCTIONS
//
//---------------------------------------------------------------------

void CSoilSalinityMap::_Resize_Weights(int size)
{
	m_v_weights.resize(size);
}
//---------------------------------------------------------------------

bool CSoilSalinityMap::_Load_Weights()
{
	std::ostringstream	sql;
	PGresult*			res;

	if ( g_pSAGA_Frame->db->CheckAccessibility(Get_WeightsTableName().c_str()) ) {

		sql << "SELECT * FROM " << Get_WeightsTableName() << " LIMIT 1";

		res = PQexec(g_pSAGA_Frame->db->GetConn(),sql.str().c_str());
		
		if ( PQntuples(res) > 0 && PQnfields(res) > 0 )
		{
			for (int i = 0; i < m_nSoilSalinityVariables; i++) {
				m_v_weights[i] = convert_sl::StringToInt(PQgetvalue(res,0,i));
			}
			
			PQclear(res);
			return(true);
		} else {
			PQclear(res);
			return(false);
		}
	} else {
		return(false);
	}
}
//---------------------------------------------------------------------

bool CSoilSalinityMap::_Export_Shape_As_SoilSalinityMap(CSG_Shapes *pShapes)
{
	// create temporary shapefile
	CSG_Shapes* pShapesTemp = new CSG_Shapes(*pShapes);
	
	//pShapesTemp->Set_Name(m_FeatureName.c_str());

	// adding primary key column and 
	// specific soil salinity attributes
	_Add_Specific_Attributes(pShapesTemp);
	
	// add id's to primary key column
	_Add_IDs_To_PK_Field(pShapesTemp);

	// export new shape to PostgreSQL database
	// assign m_MapName
	Cmenu_db menu_db;
	if ( menu_db._On_DB_ExportAsSimpleFeature(pShapesTemp, m_MapName, true) ) {

		// create attribute and parameter settings tables in PostgreSQL
		//Create_External_Attributes();
		delete pShapesTemp;
		return(true);
	} else {
		delete pShapesTemp;
		return(false);
	}
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Create_External_Attributes()
{
	//-----------------------------------------------------------------
	//	SOIL SALINITY ASSESSMENT ATTRIBUTES
	//-----------------------------------------------------------------
	_Create_AttributeTable();

	//-----------------------------------------------------------------
	//	SOIL SALINITY parameter settings
	//-----------------------------------------------------------------
	_Create_SettingsTable();


	//-----------------------------------------------------------------
	//	SOIL SALINITY PARAMETER WEIGHTS
	//-----------------------------------------------------------------
	_Create_WeightsTable();
	_Insert_DefaultWeights();
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Create_AttributeTable()
{
	std::ostringstream sql;

	if ( g_pSAGA_Frame->db->CheckAccessibility(m_AttributeTableName.c_str()) ) {
		g_pSAGA_Frame->db->DropTable(m_AttributeTableName.c_str());
	}

	sql << "CREATE TABLE " << m_AttributeTableName << "(";
	sql << m_PK_colname << " serial primary key, "; // ID primary key
	sql << "FID int4 NOT NULL, Yr int4, Mnth varchar(3), Salinity varchar, Salinity_v float4, ";
	sql << "Salinity_l float4, Salinity_e float4, ";
	sql << "MS_LOW float4, MS_MEDIUM float4, MS_HIGH float4, Plant varchar)";
	MSG_ADD(sql.str().c_str());

	g_pSAGA_Frame->db->ExecSQL_wx(sql.str().c_str());
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Create_SettingsTable()
{
	std::ostringstream sql;

	if ( g_pSAGA_Frame->db->CheckAccessibility(m_SettingsTableName.c_str()) ) {
		g_pSAGA_Frame->db->DropTable(m_SettingsTableName.c_str());
	}

	sql << "CREATE TABLE " << m_SettingsTableName << "(FK_ID int4 REFERENCES "; // FK_ID = foreign key
	sql << m_AttributeTableName;	//	= references primary key of attribute table
	sql << " (PK_ID) ON DELETE CASCADE, ";
	sql << "FID int4 NOT NULL, b_sc_l int2, sc_l int4, ";	// FID, soil colour (local knowledge)
	sql << "b_nleaching int2, nleaching int4, b_wq_l int2, wq_l int4, ";	// number of leaching, water quality leaching
	sql << "b_wq_i int2, wq_i int4, b_wv_i int2, wv_i int4, ";			// water quality irrigation; volume irrigation
	sql << "b_even int2, even int4, b_morph int2, morph int4, ";		// evenness, morphology
	sql << "b_s_t int2, s_t int4, b_pg int2, pg int4, ";			// soil texture, plant growth
	sql << "b_gw_s int2, gw_s int4, b_gw_l int2, gw_l int4, ";		// ground water salinity, ground water level
	sql << "b_nchannels int2, nchannels int4, b_distance int2, distance int4, ";	// number of channels, distance to nearest channel
	sql << "b_maintanance int2, maintanance int4)";				// maintanance of drainage network
	MSG_ADD(sql.str().c_str());

	g_pSAGA_Frame->db->ExecSQL_wx(sql.str().c_str());
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Create_WeightsTable()
{
	std::ostringstream sql;

	if ( g_pSAGA_Frame->db->CheckAccessibility(m_WeightsTableName.c_str()) ) {
		g_pSAGA_Frame->db->DropTable(m_WeightsTableName.c_str());
	}

	sql << "CREATE TABLE " << m_WeightsTableName <<"(";
	sql << "sc_l int4 NOT NULL, ";
	sql << "nleaching int4 NOT NULL, ";
	sql << "wq_l int4 NOT NULL, ";	
	sql << "wq_i int4 NOT NULL, "; 
	sql << "wv_i int4 NOT NULL, ";
	sql << "even int4 NOT NULL, ";
	sql << "morph int4 NOT NULL, ";
	sql << "s_t int4 NOT NULL, ";
	sql << "pg int4 NOT NULL, ";
	sql << "gw_s int4 NOT NULL, ";
	sql << "gw_l int4 NOT NULL, ";
	sql << "nchannels int4 NOT NULL, ";
	sql << "distance int4 NOT NULL, ";
	sql << "maintanance int4 NOT NULL, ";
	sql << m_PK_colname << " serial primary key)";		// ID primary key
	MSG_ADD(sql.str().c_str());

	g_pSAGA_Frame->db->ExecSQL_wx(sql.str().c_str());
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Insert_DefaultWeights()
{
	std::ostringstream sql;
	
	sql << "INSERT INTO " << m_WeightsTableName << " (";
	sql << "sc_l, nleaching, wq_l, ";
	sql << "wq_i, wv_i, even, morph, ";
	sql << "s_t, pg, gw_s, gw_l, ";
	sql << "nchannels, distance, maintanance) ";
	sql << "VALUES (";
	sql << m_Default_Weights.SoilColourLocal << ", " << m_Default_Weights.nLeaching << ", ";
	sql << m_Default_Weights.WQ_Leaching << ", " << m_Default_Weights.WQ_Irrigation << ", ";
	sql << m_Default_Weights.WVolumeIrrigation << ", " << m_Default_Weights.Evenness << ", ";
	sql << m_Default_Weights.Morphology << ", ";
	sql << m_Default_Weights.SoilTexture << ", ";
	sql << m_Default_Weights.PlantGrowth << ", ";
	sql << m_Default_Weights.GWSalinity << ", " << m_Default_Weights.GWLevel << ", ";
	sql << m_Default_Weights.nChannels << ", ";
	sql << m_Default_Weights.Distance << ", " << m_Default_Weights.Maintanance << ")";
	MSG_ADD(sql.str().c_str());

	g_pSAGA_Frame->db->ExecSQL_wx(sql.str().c_str());
}

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

void CSoilSalinityMap::_Assign_TableNames(CSG_String m_MapName)
{
	m_AttributeTableName = "_attr_";
	m_AttributeTableName.Append(m_MapName);

	m_SettingsTableName = "_settings_";
	m_SettingsTableName.Append(m_MapName);

	m_WeightsTableName = "_weights_";
	m_WeightsTableName.Append(m_MapName);
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Add_Specific_Attributes(CSG_Shapes* pShapesTemp)
{
	// Add a column at the first position in the shape file attribute table
	// that serves as primary key in the database
	pShapesTemp->Get_Table().Add_Field("FID",TABLE_FIELDTYPE_Int,0);
	// add soil salinity specific columns
	pShapesTemp->Get_Table().Add_Field("Plant",TABLE_FIELDTYPE_String,1);
	pShapesTemp->Get_Table().Add_Field("MS_HIGH",TABLE_FIELDTYPE_Float,1);
	pShapesTemp->Get_Table().Add_Field("MS_MEDIUM",TABLE_FIELDTYPE_Float,1);
	pShapesTemp->Get_Table().Add_Field("MS_LOW",TABLE_FIELDTYPE_Float,1);
	pShapesTemp->Get_Table().Add_Field("Salinity_e",TABLE_FIELDTYPE_Float,1);
	pShapesTemp->Get_Table().Add_Field("Salinity_l",TABLE_FIELDTYPE_Float,1);
	pShapesTemp->Get_Table().Add_Field("Salinity_v",TABLE_FIELDTYPE_Float,1);
	pShapesTemp->Get_Table().Add_Field("Salinity",TABLE_FIELDTYPE_String,1);
	pShapesTemp->Get_Table().Add_Field("Mnth",TABLE_FIELDTYPE_String,1);
	pShapesTemp->Get_Table().Add_Field("Yr",TABLE_FIELDTYPE_Int,1);
}
//---------------------------------------------------------------------

void CSoilSalinityMap::_Add_IDs_To_PK_Field(CSG_Shapes* pShapesTemp)
{
	// add numbers to primary key column FID
	CSG_Table_Record	*pRecord;
	CSG_String str;
	for (int i = 0; i < pShapesTemp->Get_Count(); i++) {
		pRecord = pShapesTemp->Get_Table().Get_Record(i);
		str.Append(convert_sl::Int2String(i+1).c_str());
		pRecord->Set_Value(0,str.c_str());
		str.Clear();
	}
}
//---------------------------------------------------------------------
