///////////////////////////////////////////////////////////
// Author:			Stefan Liersch
// Contact:			stefan.liersch@googlemail.com,
//					stefan.liersch@ufz.de
//
// Date:			2007-06-20
// last modified:	2007-06-22
//
//---------------------------------------------------------
// DESCRIPTION
//---------------------------------------------------------
//
//---------------------------------------------------------
// ToDo
//---------------------------------------------------------
//	-	an alternative to wxArrayString aError
//
///////////////////////////////////////////////////////////

#include "simplefeature_api.h"
#include "convert_sl.h"
#include <fstream>

/////////////////////////////////////////////////////////////////////////////
//
// Copy a SAGA Shapefile as SimpleFeature to a PostgreSQL/PostGIS database
//
/////////////////////////////////////////////////////////////////////////////
bool CSG_SimpleFeature::Create_SF_From_Shape(PGconn *conn, CSG_Shapes *pShapes, CSG_geometry_columns *geo_cols,
											 const char* ShapeName,
											 bool b_create_pk, int pk_col_number)
{
	//---------------------------------------------------------------
	// Create output file as sql-script to create the shapefile
	// as SimpleFeature
	//---------------------------------------------------------------
	string sql_filename = ShapeName;
	sql_filename += ".sql";
	std::ofstream file(sql_filename.c_str());
	//---------------------------------------------------------------

	//---------------------------------------------------------------
	// Assign Geometry Type (POINT, LINESTRING, POLYGON... from Shapetype
	//---------------------------------------------------------------
	_AssignGeometryType(pShapes, *geo_cols);
	//---------------------------------------------------------------

	//---------------------------------------------------------------
	// Create SQL-Query: CREATE TABLE...
	//---------------------------------------------------------------
	string ErrorMessage="";
	if (b_create_pk)
	{
		int		pk_col_number = 0;			// existing column which acts as primary key
	}
	bool	b_allsuccessful = true;			// remains true if all sql commands are successful
	// Create Query
	string	SQL_Create = _Get_SQL_Create(conn, pShapes, ShapeName, b_create_pk, pk_col_number);
	file << SQL_Create << std::endl; // write to SQL-Script

	if (_ExecSQL(conn, SQL_Create.c_str(), ErrorMessage))
	{
		//-----------------------------------------------------------
		// Create SQL-Query: SELECT AddGeometryColumn
		//-----------------------------------------------------------
		string SQL_SELECT_GEOM = _Get_SQL_SELECT_GEOM(pShapes, ShapeName, geo_cols);
		file << SQL_SELECT_GEOM << std::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 (_ExecSQL(conn,SQL_SELECT_GEOM.c_str(), ErrorMessage))
		//{
			_ExecSQL(conn,SQL_SELECT_GEOM.c_str(), ErrorMessage);
			SG_UI_Msg_Add(ErrorMessage.c_str(),true);

			//-------------------------------------------------------
			// Drop constraints enforce_geotype
			//-------------------------------------------------------
			// This is necessary if the shape contains shapes with
			// more than one part (e.g. MULTIPOLYGON).
			string SQL_DROP_ENFORCE_GEOTYPE = _Get_SQL_DropEnforce(ShapeName, geo_cols->geom);
			_ExecSQL(conn,SQL_DROP_ENFORCE_GEOTYPE.c_str(), ErrorMessage);
			SG_UI_Msg_Add(ErrorMessage.c_str(), true);
			//-------------------------------------------------------

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

				if (_ExecSQL(conn, SQL_INSERT.c_str(),ErrorMessage))
				{
					//return(true);
				} else {
					SG_UI_Msg_Add(ErrorMessage.c_str(),true);
					b_allsuccessful = false;
					//aError.Add(wxString::Format("%s %d: %s","Feature",feature,ErrorMessage));
				}
			}
			SG_UI_Process_Set_Ready();
			//-------------------------------------------------------

		//} if (SQL_SELECT_GEOM
	} // end if SQL_Create

	file.close(); // sql script file

	return(true);
}


//---------------------------------------------------------------------------
string CSG_SimpleFeature::_Get_SQL_Create(PGconn *conn, CSG_Shapes *pShapes, const char* ShapeName, bool b_create_pk, int pk_col_number)
{
	string errorMessage = "";
	// 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;
		_ExecSQL(conn,DROP_SEQ.c_str(),errorMessage);//(DROP_SEQ.c_str());
		// Create sequence
		CREATE += "CREATE SEQUENCE ";
		CREATE += SEQUENCE_Name;
		CREATE += "; \n";
	}

	string DROP_TABLE = "DROP TABLE ";
	DROP_TABLE += ShapeName;
	_ExecSQL(conn,DROP_TABLE.c_str(),errorMessage);//g_pSAGA_Frame->db->ExecSQL_wx(DROP_TABLE.c_str());

	CREATE += "CREATE TABLE ";
	CREATE += ShapeName;
	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 (!b_create_pk && col == pk_col_number) 
			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 CSG_SimpleFeature::_Get_SQL_SELECT_GEOM(CSG_Shapes *pShapes, const char* ShapeName, CSG_geometry_columns *geo_cols)
{
	// Create SQL-Query 'SELECT AddGeometryColumn for Single Features'
	string SELECT = "SELECT AddGeometryColumn('";
	SELECT += geo_cols->TableSchema;
	SELECT += "', '";
	SELECT += ShapeName;
	SELECT += "' ,'";
	SELECT += geo_cols->geom; //GeoAttributes.geometry_column_name_single;
	SELECT += "', ";
	SELECT += convert_sl::Int2String(geo_cols->SRID); //.insert(SELECT.length(), GeoAttributes.SRID); // += SRID;
	SELECT += ", '";
	SELECT += geo_cols->GeoType_asString; //GeoAttributes.geometry_type;
	SELECT += "', ";
	SELECT += convert_sl::Int2String(geo_cols->CoordDimension); //GeoAttributes.geometry_dimension); //.insert(SELECT.length(), GeoAttributes.geometry_dimension); // += geometry_dimension;
	SELECT += "); \n";

	return(SELECT);
}
//---------------------------------------------------------------------------
void CSG_SimpleFeature::_AssignGeometryType(CSG_Shapes *pShapes, CSG_geometry_columns &geo_cols)
{
	ESG_Shape_Type shapetype = pShapes->Get_Type();
	switch(shapetype)
	{
		case SHAPE_TYPE_Undefined : geo_cols.GeoType_asString = "POINT"; break;
		case SHAPE_TYPE_Point	: geo_cols.GeoType_asString = "POINT"; break;
		case SHAPE_TYPE_Points	: geo_cols.GeoType_asString = "POINT"; break;
		case SHAPE_TYPE_Line	: geo_cols.GeoType_asString = "LINESTRING"; break;
		case SHAPE_TYPE_Polygon	: geo_cols.GeoType_asString = "POLYGON"; break;
		default : geo_cols.GeoType_asString = "POINT"; 
	}
}
//---------------------------------------------------------------------------
string CSG_SimpleFeature::_Get_SQL_DropEnforce(string ShapeName, string constraint_name)
{
	string SQL = "ALTER TABLE ";
	SQL += ShapeName;
	SQL += " DROP CONSTRAINT enforce_geotype_";
	SQL += constraint_name;
	return(SQL);
}
//---------------------------------------------------------------------------

string CSG_SimpleFeature::_Get_SQL_INSERT_INTO(CSG_Shapes *pShapes, const char* ShapeName,
									 unsigned int feature, bool b_create_pk, int pk_col_number,
									 CSG_geometry_columns *geo_cols)
{
	string geotype = geo_cols->GeoType_asString;
	// 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('";
	for (int iPart = 0; iPart < pShapes->Get_Shape(feature)->Get_Part_Count(); iPart++)
	{
		if (pShapes->Get_Shape(feature)->Get_Part_Count() == 1 &&
			iPart == 0)
		{
			INSERT += geo_cols->GeoType_asString; //GeoAttributes.geometry_type;
			if (geo_cols->GeoType_asString == "POINT" ||
				geo_cols->GeoType_asString == "LINESTRING")
				INSERT += "(";
			else //if geometry_type == POLYGON
				INSERT += "((";
		}

		if (pShapes->Get_Shape(feature)->Get_Part_Count() > 1 &&
			iPart == 0)
		{
			//_Modify_GeometryType(GeoAttributes);
			geotype = geo_cols->GeoType_asString;
			//geo_cols->GeoType_asString = _Modify_GeometryType(geo_cols->GeoType_asString);
			geotype = _Modify_GeometryType(geotype);
			INSERT += geotype;
			if (geotype == "MULTIPOINT")
				INSERT += "(";
			if (geotype == "MULTILINESTRING")
				INSERT += "((";
			if (geotype == "MULTIPOLYGON")
				INSERT += "(((";
		}

		for (int point = 0; point < pShapes->Get_Shape(feature)->Get_Point_Count(iPart); 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 (geotype == "POINT" ||
				geotype == "LINESTRING")
			{
				if (point < pShapes->Get_Shape(feature)->Get_Point_Count(iPart)-1)
					INSERT += ",";
				else
					INSERT += ")', ";
			}
			if (geotype == "POLYGON")
			{
				if (point < pShapes->Get_Shape(feature)->Get_Point_Count(iPart)-1)
					INSERT += ",";
				else
					INSERT += "))', ";
			}
			if (geotype == "MULTIPOLYGON")
			{
				if (point < pShapes->Get_Shape(feature)->Get_Point_Count(iPart)-1)
					INSERT += ",";
				else
					if (iPart < pShapes->Get_Shape(feature)->Get_Part_Count() -1 )
						INSERT += ")),((";
					else
						INSERT += ")))', ";
			}
		}
	} // end for (iPart...

	INSERT += convert_sl::Int2String(geo_cols->SRID);//GeoAttributes.SRID);
	INSERT += "));";

	return(INSERT);
}
//---------------------------------------------------------------------------
bool CSG_SimpleFeature::_is_numeric(TSG_Table_Field_Type SG_FT)
{
	switch (SG_FT)
	{
	case TABLE_FIELDTYPE_None :
		return(false); break;
	case TABLE_FIELDTYPE_Char :
		return(false); break;
	case TABLE_FIELDTYPE_Short :
		return(true); break;
	case TABLE_FIELDTYPE_Int :
		return(true); break;
	case TABLE_FIELDTYPE_Long :
		return(true); break;
	case TABLE_FIELDTYPE_Float :
		return(true); break;
	case TABLE_FIELDTYPE_Double :
		return(true); break;
	case TABLE_FIELDTYPE_String :
		return(false); break;
	case TABLE_FIELDTYPE_Color :
		return(false); break;

	default: return(false); 
	} // end switch
}
//---------------------------------------------------------------------------
string CSG_SimpleFeature::_Modify_GeometryType(string &geotype)
{
	if (geotype == "POINT")			return("MULTIPOINT");
	if (geotype == "LINESTRING")	return("MULTILINESTRING");
	if (geotype == "POLYGON")		return("MULTIPOLYGON");
	return("");
}
//---------------------------------------------------------------------------

string CSG_SimpleFeature::_Get_FieldTypeFromSAGA(TSG_Table_Field_Type SG_ft)
{
	switch(SG_ft)
	{
	case TABLE_FIELDTYPE_None :
		return("UNKNOWN"); break;
	case TABLE_FIELDTYPE_Char :
		return("VARCHAR(1)"); break;
	case TABLE_FIELDTYPE_Short :
		return("INT4"); break;
	case TABLE_FIELDTYPE_Int :
		return("INT8"); break;
	case TABLE_FIELDTYPE_Long :
		return("INT8"); break;
	case TABLE_FIELDTYPE_Float :
		return("FLOAT4"); break;
	case TABLE_FIELDTYPE_Double :
		return("FLOAT8"); break;
	case TABLE_FIELDTYPE_String :
		return("VARCHAR"); break;
	case TABLE_FIELDTYPE_Color :
		return("UNKNOWN"); break;

	default: return("UNKNOWN");
	}
}
// ------------------------------------------------------------------------

//---------------------------------------------------------------------
bool CSG_SimpleFeature::_ExecSQL(PGconn *conn, string SQL_Query, string &errorMessage)
{
	// executes any SQL query in the currently connected database
	// using the global connection variable 'conn'
	PGresult *res;
	try
	{
		res = PQexec(conn, SQL_Query.c_str());
	}
	catch(...)
	{
		return(false);
	}
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		errorMessage = PQresultErrorMessage(res);
		PQclear(res);
		return(false);
	} else {
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------
