/////////////////////////////////////////////////////////////////////////////
// Name:        pgdatabase.h
// Author:      Stefan Liersch
// Contact:		stefan.liersch@ufz.de stefan.liersch@googlemail.com
// 
// Date:			2007-02-05
// last modified:	2007-06-23
/////////////////////////////////////////////////////////////////////////////

//*********************************************************************
// ToDo
//---------------------------------------------------------------------
// - PGhostaddr is not yet implemented and has not been tested so far
//	 the current version works only with localhost !!!
// - remove symbols automatically from user defined table names that are not accepted by PostgreSQL
// - Functions, such as "InsertIntoTable()" should always return an error message 
//*********************************************************************

#ifndef HEADER_INCLUDED__pgdatabase_H
#define HEADER_INCLUDED__pgdatabase_H

//---------------------------------------------------------
#include "wx/arrstr.h"
#include "wx/string.h"
#include "wx/datetime.h"
#include "libpq-fe.h"

#include "sqltable.h"
#include "saga_api/simplefeature_api.h"

#include "saga_api/table.h"
//---------------------------------------------------------

//---------------------------------------------------------
// SQL commands
//---------------------------------------------------------
const std::string sql_getGeometryTables = "SELECT * FROM GEOMETRY_COLUMNS ORDER BY f_table_name";

const std::string sql_getTables = "SELECT relname FROM pg_class WHERE relkind='r'"
			 "and relname !~ '^pg_' and relname !~ '^pga_'"
			 "and relname !~ '^pgadmin_' and relname !~'^sql_'"
			 "and relname !~ 'spatial_ref_sys'"
			 "and relname !~ 'geometry_columns'"
			 "ORDER BY pg_class.relname";

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

class Cpgdatabase
{
public:
	Cpgdatabase		(void);
	Cpgdatabase		(char *pghost, char *pgport, char *pgoptions, char *pgtty,
					 char *dbname, char *user, char *password);

	~Cpgdatabase	(void);

	bool			b_conn;  // true if connection to database was successfull
	bool			b_trans; // true if transaction was successfull
	
	//-----------------------------------------------------
	// PUBLIC FUNCTIONS
	//-----------------------------------------------------
	bool			ConnectToDB(string pghost, string pgport,
								string pgoptions, string pgtty,
								string dbname, string user, string password);

	// make a connection to the database using PQconnectdb
	bool			ConnectToDB(const char* connectInfo);

	void			ExitDB();

	//--------------------
	// Table Functions
	//--------------------
	int				GetNumberCols(const char *tablename);
	int				GetNumberRows(const char *tablename);

	bool			GetMinMaxValue(const char* tablename, const char* colname, double& min, double& max);
	bool			GetMinMaxValue(const char* tablename, const char* colname, int& min, int& max);

	// reads a PostgreSQL table
	bool			CheckAccessibility(std::string tablename);
	bool			ReadTable(Csqltable *table);
	bool			Get_QueryResult(std::string query, Csqltable* table);
	bool			GetSelection(Csqltable *table, wxString sql);
	//bool			ReadSpatialTable(CSimpleFeature *SimpleFeature);

	// returns a string containing all user tables in database
	string			GetDBTables();
	wxArrayString	GetDBTablesArray();
	string			GetDBGeometryTables();
	wxArrayString	GetDBGeometryTablesArray();

	bool			CreateTable(Csqltable *SQLTable);

	bool			DropTable(const char *tablename);

	bool			InsertIntoTable(const char *tablename, const char *record);
	string			Get_SQL_InsertInto(const char *tablename, const char *record);

	//--------------------
	// Helper
	//--------------------
	std::string		Get_PrimaryKeyColumnName(const char* tablename);
	int				_GetPrimaryKeyColumnNumber(string FeatureName);
	std::string		_GetPrimaryKeyColumnName(string FeatureName, int pk_col_number);

	std::string		_Get_GeometryColumnName(std::string FeatureName);

	std::string		Cut_FileNameExtension(const char* filename);

	int				get_lastval(CSG_String sequencename);
	int				_Get_last_serial(CSG_String tablename, CSG_String pk_colname);
	//--------------------
	// SQL Commands
	// Declarations in pgsql_settings.cpp
	//--------------------
	bool			SetDate_ISO();
	bool			SetDate_SQL();
	bool			SetDate_Postgres();
	bool			SetDate_German();
	bool			SetDate_European();
	bool			SetDate_NonEuropean();

	bool			CreateUser_normal(string username, string password);

	bool			TableExists(const char* table);

	// Executes an user defined SQL command
	bool			ExecSQL_str(string SQL_Query);
	bool			ExecSQL_wx(wxString SQL_Query);
	bool			ExecSQL_wx1(wxString SQL_Query, wxString &error);
	bool			ExecSQL_wx1(PGresult *res, wxString SQL_Query, wxString &error);
	PGresult		*ExecSQL_wx2(wxString SQL);
	bool			get_ResultStatus(PGresult* res);

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

	//--------------------
	// SQL Date / TimeSeries
	// Declaration in 'pgsql_date.cpp'
	//--------------------
	wxDateTime		Init_wxDateTime();
	int				GetFirstYear(const char* tablename, const char* date_col);
	int				GetFirstMonth(const char* tablename, const char* date_col);
	int				GetFirstDay(const char* tablename, const char* date_col);
	int				GetLastYear(const char* tablename, const char* date_col);
	int				GetLastMonth(const char* tablename, const char* date_col);
	int				GetLastDay(const char* tablename, const char* date_col);
	// The day and year in wxDateTime can be assigned by integer values,
	// but unfortunately it is not possible to assign the month like this.
	bool			AssignMonthByInt(wxDateTime& date, int month);

	// GetMonthly returns monthly "avg, sum, min, or max" values of a time series.
	// If year > 0, the requested value of a specific month in a specific year will be returned.
	// If year < 0, the requested value of a specific month of the entire time series will be returned.
	double			GetMonthly(const char* method, const char* tablename, const char* date_col, const char* val_column, int month, int year);

	//--------------------
	// Spatial Queries
	// Declaration in 'pgsql_spatial_queries.cpp'
	//--------------------
	wxString		Get_SQL_SpatialRelation(wxString command,
											CSG_SimpleFeature *feature1, CSG_SimpleFeature *feature2,
											wxString condition_value);

	//--------------------
	// Connection Parameters
	//--------------------
	char			*GetDBName();	// returns database name
	char			*GetUser();		// returns user name
	char			*GetPW();		// returns password
	char			*GetHost();		// returns host name
	char			*GetPort();		// returns port
	char			*GetTTY();		// returns tty for debugging 
	char			*GetOptions();	// returns the connection configuration options

	PGconn			*GetConn();		// returns PGconn

	// returns SAGA TABLE_FIELDTYPE as string
	TSG_Table_Field_Type	SQLFieldTypeToSAGAFieldtype(int oid);

private:
	const char		*conninfo;
	PGconn			*conn;
	PGresult		*res;

	char			*pghost,
					*pghostaddr,
					*pgport,
					*pgoptions,
					*pgtty,
					*dbname,
					*user,
					*password;

	Csqltable		*SQLTable;

	//-----------------------------------------------------
	// PRIVATE FUNCTIONS
	//-----------------------------------------------------
	bool			ExecSQL(const char *SQL_Query);

	bool			StartTransaction();
	void			CommitTransaction();

	void			CloseCursor();

	// returns the 'field type' as string
	string			GetFieldTypeString(PGresult *res, int i);


}; // end class pgdatabase

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


///////////////////////////////////////////////////////////
//
//	Class CSQL_Record
//
///////////////////////////////////////////////////////////
/*
class CSQL_Record
{
public:
	CSQL_Record();
	CSQL_Record(wxArrayString FieldType, wxArrayString Value);
	~CSQL_Record();

	wxString			GetFieldType(int i);
	wxString			GetValue(int i);
	bool				IsNumeric(int i);
	
private:
	wxArrayString		m_FieldType;
	wxArrayString		m_Value;
	bool				*m_pIsNumeric;

	void				_AssignIsNumeric();
};
*/
#endif /* #ifndef HEADER_INCLUDED__pgdatabase */