#include <iostream>	// for test output only
#include <fstream>	// for test output only
using std::ofstream;// for test output only
using std::endl;	// for test output only


#include "pgdatabase.h"

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

///////////////////////////////////////////////////////////////////////
//
//								CONSTRUCTOR
//
///////////////////////////////////////////////////////////////////////

Cpgdatabase::Cpgdatabase()
{
}

Cpgdatabase::Cpgdatabase(char *pghost, char *pgport,
						 char *pgoptions, char *pgtty,
						 char *dbname, char *user, char *password)
{
	// make a connection to the database
	conn = PQsetdbLogin(pghost,pgport,pgoptions,pgtty,dbname,user,password);
	if (PQstatus(conn) != CONNECTION_OK)
	{
		PQfinish(conn);
		b_conn = false;
	} else {
		b_conn = true;
	}
}
// DESTRUCTOR
Cpgdatabase::~Cpgdatabase()
{
	PQfinish(conn);
}

///////////////////////////////////////////////////////////////////////
//
//						PUBLIC MEMBER FUNCTIONS
//
///////////////////////////////////////////////////////////////////////

//---------------------------------------------------------------------
bool Cpgdatabase::ConnectToDB(string pghost, string pgport,
					   string pgoptions, string pgtty,
					   string dbname, string user, string password)
{
	try
	{
		// make a connection to the database
		conn = PQsetdbLogin(pghost.c_str(),pgport.c_str(),
							pgoptions.c_str(),pgtty.c_str(),
							dbname.c_str(),user.c_str(),password.c_str());
	}
	catch(...)
	{
		return(false);
	}

	if (PQstatus(conn) != CONNECTION_OK)
	{
		PQfinish(conn);
		b_conn = false;
		return(false);
	} else {
		b_conn = true;
		return(true);
	}
}

//---------------------------------------------------------------------
void Cpgdatabase::ExitDB()
{
	PQfinish(conn);
}
//---------------------------------------------------------------------

int Cpgdatabase::GetNumberCols(const char *tablename)
{
	int ncols = 0;
	// start transaction
	if (StartTransaction())
	{
		b_trans = true;
		if (FetchRows(tablename) && FetchAll())
		{
			ncols = PQnfields(res);
			PQclear(res);
		} else {
			ncols = 0;
			PQclear(res);
		}
		// close cursor (FetchRows(...))
		Cpgdatabase::CloseCursor();
	} else {
		b_trans = false;
	}
	// Commit transaction
	Cpgdatabase::CommitTransaction();
	return(ncols);

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

int Cpgdatabase::GetNumberRows(const char *tablename)
{
	int nrows = 0;
	// start transaction
	if (StartTransaction())
	{
		b_trans = true;
		if (FetchRows(tablename) && FetchAll())
		{
			nrows = PQntuples(res);
			PQclear(res);
		} else {
			nrows = 0;
			PQclear(res);
		}
		// close cursor (FetchRows(...))
		Cpgdatabase::CloseCursor();
	} else {
		b_trans = false;
	}
	// Commit transaction
	Cpgdatabase::CommitTransaction();
	return(nrows);
}
//---------------------------------------------------------------------

bool Cpgdatabase::ReadTable(Csqltable *SQLTable)
{
	int ncols = 0;
	int nrows = 0;
	int intval = 0;
	int i;

	// build SQL query
	char SQL_Query[255] = { "SELECT * FROM " };
	strcat (SQL_Query, SQLTable->GetTableName().c_str());
	
	try
	{			
		// send SELECT * FROM tablename query
		res = PQexec (conn, SQL_Query);
	}
	catch(...)
	{
		return(false);
	}
	// check whether SQL query was successfull or not
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		//PQfinish (conn);
		return(false);
	} else {
		ncols = PQnfields(res);
		nrows = PQntuples(res);

		// assign values to incoming Csqltable 'table'

		// assign field names
		for (i = 0; i < ncols; i++)
			SQLTable->SetFieldName(PQfname(res,i), i);

		// assign field types
		for (i = 0; i < ncols; i++)
		{
			SQLTable->SetFieldType(GetFieldTypeString(res,i),i);
			SQLTable->SetFieldTypeInt(PQftype(res,i),i);
		}

		// assign field values to table matrix
		for (int col = 0; col < SQLTable->Get_ncols(); col++)
		{
			for (int row = 0; row <SQLTable->Get_nrows(); row++)
				SQLTable->SetValue(PQgetvalue(res,row,col),col,row);
		}
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

/*
bool Cpgdatabase::ReadSpatialTable(CSimpleFeature *SimpleFeature)
{
	const int FIELDTYPE_GEOMETRY = 16632;
	int i, x, y;

	char SELECT[255] = { "SELECT * FROM "};
	strcat(SELECT, SimpleFeature->GetTableName().c_str());
	res = PQexec(conn, SELECT);
	// check whether SQL query was successfull or not
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		// error
		PQclear(res);
	} else
	{
		// assign field types to fieldtypeInt[]
		for (x = 0; x < PQnfields(res); x++)
			SimpleFeature->SetFieldTypeInt(PQftype(res,x),i);
	}

	// build SQL query
	char SQL_Query[4096] = { "SELECT " };
	for (i = 0; i < SimpleFeature->Get_ncols(); i++)
	{
		// searching for field type "geometry" OID = 16632)
		if (SimpleFeature->GetFieldTypeInt(i) != FIELDTYPE_GEOMETRY)
		{
			SimpleFeature->SetFieldName(PQfname(res,i),i);
			strcat (SQL_Query, SimpleFeature->GetFieldName(i).c_str());
			strcat (SQL_Query, ", ");
		} else
		{
			//SimpleFeature->fieldname[i] = PQfname(res,i);
			SimpleFeature->SetFieldName(PQfname(res,i),i);
			strcat(SQL_Query, "AsText(");
			strcat(SQL_Query, SimpleFeature->GetFieldName(i).c_str());
			strcat(SQL_Query, ") AS ");
			strcat(SQL_Query, SimpleFeature->GetFieldName(i).c_str());
			strcat(SQL_Query, " FROM ");
			strcat(SQL_Query, SimpleFeature->GetTableName().c_str());
		}
	}

	// send SELECT * FROM tablename query
	res = PQexec (conn, SQL_Query);

	// check whether SQL query was successfull or not
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		return(false);
	} else
	{
		// Assign table values to SQLTable
		for (x = 0; x < PQnfields(res); x++)
		{
			SimpleFeature->SetValue(PQgetvalue(res,0,x),x,0);
			MSG_ADD(SimpleFeature->GetValue(x,0).c_str());
		}
		PQclear (res);
	}

	return(true);
}
*/

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

string Cpgdatabase::GetDBTables()
{
	// this function returns a string containing all user table names
	// in the current database (default system tables not included)
	
	string str; // storing table names for Add.Choice

	// creating the SQL query
	char *s ="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";
	
	// sending SQL query to database
	res = PQexec(conn, s);
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		PQfinish (conn);
		return("");
	} else {
		// print table names to string str
        for (int y = 0; y < PQntuples(res); y++)
		{
			str.append(PQgetvalue(res,y,0));
			str.append("|");
		}
		PQclear(res);
		return(str);
	}
}
//---------------------------------------------------------------------

string Cpgdatabase::GetDBGeometryTables()
{
	string str; // storing table names for Add.Choice

	// creating the SQL query
	char *s ="SELECT * FROM GEOMETRY_COLUMNS "
			 "ORDER BY f_table_name";
	
	// sending SQL query to database
	res = PQexec(conn, s);
	if (!res || PQresultStatus (res) != PGRES_TUPLES_OK)
	{
		PQclear (res);
		PQfinish (conn);
		return("");
	} else {
		// print table names to string str
        for (int y = 0; y < PQntuples(res); y++)
		{
			str.append(PQgetvalue(res,y,2));
			str.append("|");
		}
		PQclear(res);
		return(str);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::CreateTable(Csqltable *SQLTable)
{
	// creating the SQL command
	char SQL_Query[4096] = "CREATE TABLE ";
	strcat(SQL_Query, SQLTable->GetTableName().c_str()); //tablename);
	strcat(SQL_Query, " (");

	for (int i = 0; i < SQLTable->Get_ncols(); i++)
	{
		strcat(SQL_Query, SQLTable->GetFieldName(i).c_str()); //fname[i].c_str());
		strcat(SQL_Query, " ");
		strcat(SQL_Query, SQLTable->GetFieldType(i).c_str()); //ftype[i].c_str());
		if (i <  SQLTable->Get_ncols() -1) strcat(SQL_Query, ", ");
		if (i == SQLTable->Get_ncols() -1) strcat(SQL_Query,")");
	}
	
	MSG_ADD(SQL_Query);

	if (ExecSQL(SQL_Query))
	{
		//cout << "\n Create Table successfull..." << endl;
		return(true);
	} else{
		//cout << "\n Create Table not successfull..." << endl;
		return(false);
	}
}

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

bool Cpgdatabase::DropTable(const char *tablename)
{
	try
	{
		// creating the SQL command
		char SQL_Query[4096] = "DROP TABLE ";
		strcat(SQL_Query, tablename);
		if (!ExecSQL(SQL_Query)) return(false);
	}
	catch(...)
	{
		return(false);
	}
	return(true);
}
//---------------------------------------------------------------------

bool Cpgdatabase::InsertIntoTable(const char *tablename, const char *record)
{
	try
	{
	// creating the SQL command
	char SQL_Query[4096] = "INSERT INTO ";
	strcat(SQL_Query, tablename);
	strcat(SQL_Query, " VALUES(");
	strcat(SQL_Query, record);
	strcat(SQL_Query, ")");
	if (!ExecSQL(SQL_Query)) return(false);
	}
	catch(...)
	{
		return(false);
	}
	return(true);
}
//---------------------------------------------------------------------

//---------------------------------------------------------------------
//				GET FUNCTIONS
//---------------------------------------------------------------------

char *Cpgdatabase::GetDBName()
{
	return(PQdb(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetUser()
{
	return(PQuser(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetPW()
{
	return(PQpass(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetHost()
{
	if (PQhost(conn) !=NULL)
	{
		return(PQhost(conn));
	} else {
		return("Hostname returned: NULL");
	}
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetPort()
{
	return(PQport(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetTTY()
{
	return(PQtty(conn));
}
//---------------------------------------------------------------------

char *Cpgdatabase::GetOptions()
{
	return(PQoptions(conn));
}
//---------------------------------------------------------------------

PGconn *Cpgdatabase::GetConn()
{
	return(conn);
}
//---------------------------------------------------------------------

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

///////////////////////////////////////////////////////////////////////
//
//						PRIVATE MEMBER FUNCTIONS
//
///////////////////////////////////////////////////////////////////////

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

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

bool Cpgdatabase::StartTransaction()
{
	// Start transaction block
	res = PQexec(conn, "BEGIN");
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		PQfinish(conn);
		return(false);
	} else {
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

void Cpgdatabase::CommitTransaction()
{
	// COMMIT Transaction
	res = PQexec(conn, "COMMIT");
	PQclear(res);
}
//---------------------------------------------------------------------

bool Cpgdatabase::FetchRows(const char *tablename)
{
	// Tablename must not be longer than 25 characters !!!
	char DECLARE[75] = "DECLARE mycursor BINARY CURSOR FOR SELECT * FROM ";
	// copy tablename to the end of of the DECLARE command
	strncpy(DECLARE + 49, tablename, 25);

	// fetch rows from the database
	res = PQexec(conn, DECLARE);
	if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		PQclear(res);
		PQfinish(conn);
		return(false);
	} else {
		PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

bool Cpgdatabase::FetchAll()
{
	// !!! If FetchAll was successfull PQclear(res) will not be executed
	// !!! The user must do this manually in the calling program
	res = PQexec(conn, "FETCH ALL in mycursor");
	if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		PQclear(res);
		PQfinish(conn);
		return(false);
	} else {
		//PQclear(res);
		return(true);
	}
}
//---------------------------------------------------------------------

void Cpgdatabase::CloseCursor()
{
	// close cursor (FetchRows(...))
	res = PQexec(conn, "CLOSE mycursor");
	PQclear(res);
}

string Cpgdatabase::GetFieldTypeString(PGresult *res, int i)
{
	// You find the Oid number in the PostgreSQL installation directory:
	// src/include/catalog/pg_type
	int Oid;
	Oid = PQftype(res,i);
	switch(Oid)
	{
	case 16 : return("BOOL");
		break;
	case 25 : return("TEXT");
		break;
	case 20 : return("INT8");
		break;
	case 23 : return("INT4");
		break;
	case 700 : return("FLOAT4");
		break;
	case 701 : return("FLOAT8");
		break;
	case 702 : return("ABSTIME");
		break;
	case 703 : return("RELTIME");
		break;
	case 704 : return("TINTERVAL");
		break;
	case 705 : return("UNKNOWN");
		break;
	case 1043 : return("VARCHAR");
		break;
	case 1082 : return("DATE");
		break;
	case 1083 : return("TIME");
		break;
	case 2275 : return("CSTRING");
		break;
	default: return("undefined");
	}
}
