#include "menu_db.h"
#include "convert_sl.h"

#include "saga_gui/saga_frame.h"
#include "saga_gui/helper.h"			// using function MSG_General_Add()
#define MSG_ADD(s)	MSG_General_Add(s, true, true);

#include <fstream>

//*********************************************************************
// ToDo
//---------------------------------------------------------------------
// - Depending on the database encoding settings, there can occur problems
//		by exporting a shapefile to PostGIS SimpleFeature. In my case the
//		db-settings where UTF8 and the sql-statement, that was produced by
//		function Get_SQL_INSERT_INTO(...) was in LATIN1. Converting the
//		same shapefile to a LATIN1 encoded database was no problem.
//		In any case an output file ('shapename'.sql) will be produced which contains the
//		SQL-statements that are necessary to create the SimpleFeature.
//		http://lists.maptools.org/pipermail/gdal-dev/2005-December/007384.html
//		http://www.wilcoxd.com/blog/unicode-wxwidgets-and-i.html
// - A LINE or POLYGON Shape can be composed of one or more parts. If such 
//		a shape is composed of more than one parts, it should be exported
//		as MULTILINE or MULTIPOLYGON.
//*********************************************************************


/////////////////////////////////////////////////////////////////////////////
//
// Converting a SimpleFeature from a PostgrSQL database to SAGA Shapefile
//
/////////////////////////////////////////////////////////////////////////////
bool Cmenu_db::Create_Shape_From_SF(CSG_Shapes *pShapes)
{
	CSG_Table_Record	*pRecord;

	//-----------------------------------------------------------------
	// Set SHAPE_TYPE
	// GeoType is defined in simple_feature.h as SF_TGeometry
	//-----------------------------------------------------------------
	switch (m_pSimpleFeature->GetGeoType())
	{
		default: 
			return(false);
		// Multi-features are not yet implemented
		case 1: pShapes->Create(SHAPE_TYPE_Points,	m_pSimpleFeature->GetFeatureName().c_str()); break;
		case 2: pShapes->Create(SHAPE_TYPE_Line,	m_pSimpleFeature->GetFeatureName().c_str()); break;
		case 3: pShapes->Create(SHAPE_TYPE_Polygon,	m_pSimpleFeature->GetFeatureName().c_str()); break;
		case 4: pShapes->Create(SHAPE_TYPE_Points,	m_pSimpleFeature->GetFeatureName().c_str()); break;
		case 5: pShapes->Create(SHAPE_TYPE_Line,	m_pSimpleFeature->GetFeatureName().c_str()); break;
		case 6: pShapes->Create(SHAPE_TYPE_Polygon,	m_pSimpleFeature->GetFeatureName().c_str()); break;
		//case 7: // SF_GeometryCollection not yet implemented
	} // end switch
	//-----------------------------------------------------------------

	//-----------------------------------------------------------------
	// Create Geometries
	// Add Simple Feature 'features' as 'parts' to shapefile
	//-----------------------------------------------------------------
	int nFeatures = m_pSimpleFeature->GetNumFeatures();
	MSG_ADD("Create Shapes...");
	for (int feature = 0; feature < nFeatures && SG_UI_Process_Set_Progress(double(feature), double(nFeatures)) ; feature++)
	{
		pShapes->Add_Shape();
		// Add coordinates to parts
		for (unsigned int point = 0; point < m_pSimpleFeature->GetNumPointsFeature(feature); point++)
			pShapes->Get_Shape(feature)->Add_Point(m_pSimpleFeature->Get_X_Value(feature,point),
												   m_pSimpleFeature->Get_Y_Value(feature,point),
												   feature);
	}

	//-----------------------------------------------------------------
	// Add field names and types to shapefile attribute table
	//-----------------------------------------------------------------
	string s;
	MSG_ADD("Create Attribute Table...");
	for (int x = 0; x < m_pSimpleFeature->Get_ncols(); x++)
	{
		if (x != m_pSimpleFeature->GetGeometryColumnNumber())
			pShapes->Get_Table().Add_Field(m_pSimpleFeature->GetFieldName(x).c_str(),
											g_pSAGA_Frame->db->SQLFieldTypeToSAGAFieldtype(m_pSimpleFeature->GetFieldTypeInt(x))
											,x);
	}

	//-----------------------------------------------------------------
	// Add data to attribute table
	// NOTE: Before new records can be added to the attribute table,
	// the corresponding shapes (features) must exist. Here the geometry
	// features have been added with Add_Shape() above.
	//-----------------------------------------------------------------
	for (int row = 0; row < m_pSimpleFeature->Get_nrows(); row++)
	{
		pShapes->Get_Table().Add_Record();
		pRecord = pShapes->Get_Table().Get_Record(row);
		for (int col = 0; col < m_pSimpleFeature->Get_ncols(); col++)
		{
			// Ignore geometry column
			if (col != m_pSimpleFeature->GetGeometryColumnNumber())
			{
				if (g_pSAGA_Frame->db->SQLFieldTypeToSAGAFieldtype(m_pSimpleFeature->GetFieldTypeInt(col))
					== TABLE_FIELDTYPE_Int)
 					pRecord->Set_Value(col, convert_sl::StringToInt(m_pSimpleFeature->GetValue(col,row)));
				
				if (g_pSAGA_Frame->db->SQLFieldTypeToSAGAFieldtype(m_pSimpleFeature->GetFieldTypeInt(col))
					== TABLE_FIELDTYPE_Double)
					pRecord->Set_Value(col, convert_sl::StringToDouble(m_pSimpleFeature->GetValue(col,row)));
				
				if (g_pSAGA_Frame->db->SQLFieldTypeToSAGAFieldtype(m_pSimpleFeature->GetFieldTypeInt(col))
					== TABLE_FIELDTYPE_String)
					pRecord->Set_Value(col,m_pSimpleFeature->GetValue(col,row).c_str());
			}
		}
	}
	//-----------------------------------------------------------------

	return(true);
}


/////////////////////////////////////////////////////////////////////////////
//
// Copy a SAGA Shapefile as SimpleFeature to a PostgreSQL/PostGIS database
//
/////////////////////////////////////////////////////////////////////////////
bool Cmenu_db::Create_SF_From_Shape(CSG_Shapes *pShapes)
{
	//---------------------------------------------------------------
	AssignGeoAttributes(pShapes);

	//---------------------------------------------------------------
	// cut off the file extension from Shapefile name
	string ShapeName = "";
	ShapeName.insert(0, pShapes->Get_Name());
	ShapeName = Get_Reduced_ShapeName(ShapeName);

	//---------------------------------------------------------------
	// Create output file as sql-script to create the shapefile
	// as SimpleFeature
	string sql_filename = ShapeName + ".sql";
	std::ofstream file(sql_filename.c_str());

	//---------------------------------------------------------------
	// Create SQL-Query: CREATE TABLE...
	wxString ErrorMessage="";
	bool	b_create_pk = true;	// create a row containing the primary key
	int		pk_col = 0;			// existing column which acts as primary key
	bool	b_allsuccessful = true;
	// Create Query
	string	SQL_Create = Get_SQL_Create(pShapes, ShapeName, b_create_pk, pk_col);
	file << SQL_Create << endl; // write to SQL-Script
	
	if (g_pSAGA_Frame->db->ExecSQL_wx1(SQL_Create.c_str(), ErrorMessage))
	{
		MSG_ADD("Table created...");
		//-----------------------------------------------------------
		// Create SQL-Query: SELECT AddGeometryColumn
		string SQL_SELECT_GEOM = Get_SQL_SELECT_GEOM(pShapes, ShapeName, GeoAttributes);
		file << SQL_SELECT_GEOM << endl; // write to SQL-Script
		// If the geometry column already exist, this sql query will produce an
		// error message, although the command can be successful.
		//if (g_pSAGA_Frame->db->ExecSQL_wx1(SQL_SELECT_GEOM.c_str(),ErrorMessage))
		//{
			g_pSAGA_Frame->db->ExecSQL_wx1(SQL_SELECT_GEOM.c_str(), ErrorMessage);
			MSG_ADD(wxString::Format("%s %s", "AddGeometryColumn", ErrorMessage.c_str()));

			//-------------------------------------------------------
			// Create SQL-Query: INSERT INTO...
			string SQL_INSERT ="";
			int nFeatures = pShapes->Get_Count();
			for (int feature = 0; feature < nFeatures && SG_UI_Process_Set_Progress(double(feature), double(nFeatures-1)); feature++)
			{
				SQL_INSERT = Get_SQL_INSERT_INTO(pShapes, ShapeName, feature,
												 b_create_pk, pk_col,
												 GeoAttributes);
				file << SQL_INSERT << endl; // write to SQL-Script

				if (g_pSAGA_Frame->db->ExecSQL_wx1(SQL_INSERT.c_str(),ErrorMessage))
				{
					//return(true);
				} else {
					MSG_ADD(wxString::Format("%s: %d: %s", "Feature", feature, ErrorMessage.c_str()));
					b_allsuccessful = false;
				}
			}
			//-------------------------------------------------------

		//} else { // end if SQL_SELECT_GEOM
		//	MSG_ADD(ErrorMessage.c_str());
		//	return(false);
		//}
	} else { // end if CREATE TABLE
		MSG_ADD(ErrorMessage.c_str());
		file.close();
		return(false);
	}
	if (b_allsuccessful)
		MSG_ADD("Export was successful");
	file.close();
	return(true);
}
//---------------------------------------------------------------------------

void Cmenu_db::AssignGeoAttributes(CSG_Shapes *pShapes)
{
	ESG_Shape_Type shapetype = pShapes->Get_Type();
	GeoAttributes.geometry_columnt_name = "geom";
	GeoAttributes.geometry_dimension = 2;
	switch(shapetype)
	{
		case SHAPE_TYPE_Undefined : GeoAttributes.geometry_type = "POINT"; break;
		case SHAPE_TYPE_Point	: GeoAttributes.geometry_type = "POINT"; break;
		case SHAPE_TYPE_Points	: GeoAttributes.geometry_type = "POINT"; break;
		case SHAPE_TYPE_Line	: GeoAttributes.geometry_type = "LINESTRING"; break;
		case SHAPE_TYPE_Polygon	: GeoAttributes.geometry_type = "POLYGON"; break;
		default : GeoAttributes.geometry_type = "POINT"; 
	}
	GeoAttributes.SRID = 31462;
}
//---------------------------------------------------------------------------

string Cmenu_db::Get_Reduced_ShapeName(string &ShapeName)
{
	unsigned int pos = 0;
	for (pos = 0; ShapeName[pos] != '0'; pos++)
	{
		if (ShapeName[pos] == '.')
			return(ShapeName.substr(0,pos));
	}
	return(ShapeName);
}

//---------------------------------------------------------------------------
string Cmenu_db::Get_SQL_Create(CSG_Shapes *pShapes, string ShapeName, bool b_create_pk, int pk_col)
{
	// !!!!!!!!!!!!!!!
	// the possibility to select a column which acts as primary key is missing
	// Create SQL-Query 'CREATE TABLE...'
	string CREATE = "";
	if (b_create_pk) // if column with primary key must be created
	{
		string SEQUENCE_Name="pk_id_seq_";
		SEQUENCE_Name += ShapeName;
		// If a sequence already exist, it must be dropped before
		string DROP_SEQ = "DROP SEQUENCE " + SEQUENCE_Name;
		g_pSAGA_Frame->db->ExecSQL_wx(DROP_SEQ.c_str());
		// Create sequence
		CREATE += "CREATE SEQUENCE ";
		CREATE += SEQUENCE_Name;
		CREATE += "; \n";
	}
	CREATE += "CREATE TABLE ";
	CREATE += ShapeName;
	string DROP_TABLE = "DROP TABLE " + ShapeName;
	g_pSAGA_Frame->db->ExecSQL_wx(DROP_TABLE.c_str());
	CREATE += "(";
	if (b_create_pk) // if column with primary key must be created
		CREATE += "pk_id serial primary key,";

	for (int col = 0; col < pShapes->Get_Table().Get_Field_Count(); col++)
	{
		CREATE.insert(CREATE.length(), pShapes->Get_Table().Get_Field_Name(col));
		CREATE += " ";
		if (col == pk_col && !b_create_pk) 
			CREATE += "INT4 primary key";
		else
			CREATE += Get_FieldTypeFromSAGA(pShapes->Get_Table().Get_Field_Type(col));
		if (col < pShapes->Get_Table().Get_Field_Count()-1)
			CREATE += ", ";
		else CREATE += ");";
	}

	return(CREATE);
}
//---------------------------------------------------------------------------

string Cmenu_db::Get_SQL_SELECT_GEOM(CSG_Shapes *pShapes, string ShapeName, TGeometryAttributes GeoAttributes)
{
	// Create SQL-Query 'SELECT AddGeometryColumn...'
	string SELECT = "SELECT AddGeometryColumn('public', '";
	SELECT += ShapeName;
	SELECT += "' ,'";
	SELECT += GeoAttributes.geometry_columnt_name;
	SELECT += "', ";
	SELECT += convert_sl::Int2String(GeoAttributes.SRID); //.insert(SELECT.length(), GeoAttributes.SRID); // += SRID;
	SELECT += ", '";
	SELECT += GeoAttributes.geometry_type;
	SELECT += "', ";
	SELECT += convert_sl::Int2String(GeoAttributes.geometry_dimension); //.insert(SELECT.length(), GeoAttributes.geometry_dimension); // += geometry_dimension;
	SELECT += ");";

	return(SELECT);
}
//---------------------------------------------------------------------------

string Cmenu_db::Get_SQL_INSERT_INTO(CSG_Shapes *pShapes, string ShapeName,
									 int feature, bool b_create_pk, int pk_col,
									 TGeometryAttributes GeoAttributes)
{
	// Create SQL-Query 'INSERT INTO...'
	CSG_String val;
	string INSERT = "INSERT INTO ";
	INSERT += ShapeName;
	INSERT += " VALUES(";
	// Add values from attribute table
	if (b_create_pk)
		INSERT += "nextval('pk_id_seq'), ";
	for (int col = 0; col < pShapes->Get_Table().Get_Field_Count(); col++)
	{
		if (!is_numeric(pShapes->Get_Table().Get_Field_Type(col)))
		{
			INSERT += "'";
			if (pShapes->Get_Table().Get_Value(feature,col,val))
			{
				INSERT += val;
				INSERT += "', ";
			}
		} else {
			if (pShapes->Get_Table().Get_Value(feature, col, val))
			{
				INSERT += val;
				INSERT += ", ";
			}
		}// end if		
	}

	// Convert ShapeFile geometry to SimpleFeature
	INSERT += "GeometryFromText('";
	INSERT += GeoAttributes.geometry_type;
	if (GeoAttributes.geometry_type == "POINT" ||
		GeoAttributes.geometry_type == "LINESTRING")
		INSERT += "(";
	else
		INSERT += "((";
	for (int iPart = 0; iPart < pShapes->Get_Shape(feature)->Get_Part_Count(); iPart++)
	{
		for (int point = 0; point < pShapes->Get_Shape(feature)->Get_Point_Count(); point++)
		{
			INSERT += convert_sl::Double2String(pShapes->Get_Shape(feature)->Get_Point(point, iPart).x);
			INSERT += " ";
			INSERT += convert_sl::Double2String(pShapes->Get_Shape(feature)->Get_Point(point, iPart).y);
			if (GeoAttributes.geometry_type == "POINT" ||
				GeoAttributes.geometry_type == "LINESTRING")
			{
				if (point < pShapes->Get_Shape(feature)->Get_Point_Count()-1)
					INSERT += ",";
				else
					INSERT += ")', ";
			} else { //if geometry_type == POLYGON
				if (point < pShapes->Get_Shape(feature)->Get_Point_Count()-1)
					INSERT += ",";
				else
					INSERT += "))',";
			}
		}
	}
/*
	for (int point = 0; point < pShapes->Get_Shape(feature)->Get_Point_Count(); point++)
	{
		INSERT += convert_sl::Double2String(pShapes->Get_Shape(feature)->Get_Point(point, feature).x);
		INSERT += " ";
		INSERT += convert_sl::Double2String(pShapes->Get_Shape(feature)->Get_Point(point, feature).y);
		if (GeoAttributes.geometry_type == "POINT" ||
			GeoAttributes.geometry_type == "LINESTRING")
		{
			if (point < pShapes->Get_Shape(feature)->Get_Point_Count()-1)
				INSERT += ",";
			else
				INSERT += ")', ";
		} else { //if geometry_type == POLYGON
			if (point < pShapes->Get_Shape(feature)->Get_Point_Count()-1)
				INSERT += ",";
			else
				INSERT += "))',";
		}

/*
		if (point <= pShapes->Get_Shape(feature)->Get_Point_Count()-1)
		{
			if (GeoAttributes.geometry_type == "POINT" ||
				GeoAttributes.geometry_type == "LINESTRING")
				INSERT += ")',";
			else
				INSERT += "))',";
		} else {
			INSERT += ",";
		}
	}	*/
	INSERT += convert_sl::Int2String(GeoAttributes.SRID);
	INSERT += "));";

	return(INSERT);
}

