/************************* * * * * * * * * * * * * ***************************
    Copyright (c) 1999-2005 Ryan Bobko
                       ryan@ostrich-emulators.com

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.     
************************** * * * * * * * * * * * * **************************/

#include "psqlplugin.h"
#include "resultset.h"
#include "qhaccutils.h"
#include "qhacctablerows.h"

#include <qregexp.h>

// "factory" function calls
extern "C" {
	QHaccPlugin * create(){	return new PSQLDBPlugin; }
	void destroy( PSQLDBPlugin * p ){ delete p; }
}

const PSQLDBInfo PSQLDBPlugin::pinfo;

/* read and write to a PSQL database */
PSQLDBPlugin::PSQLDBPlugin() : QHaccSQLDBPlugin(){ conn=0; }
PSQLDBPlugin::~PSQLDBPlugin(){ PQfinish( conn ); }
 
bool PSQLDBPlugin::connect( QHacc *, const QString& home, QString& ){
	// make the db connection
	// FIXME: parse home for more connection info
	atoms=0; // start with no atoms pending

	conn=PQsetdb( 0, 0, 0, 0, home );
	if( PQstatus( conn )==CONNECTION_BAD ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ){
			*str<<"no connection to database: "<<PQerrorMessage( conn )<<endl;
		}
		return false;
	}	
	return true;
}

auto_ptr<QHaccResultSet> PSQLDBPlugin::sel( const QString& stmt,
																						vector<ColType> types,
																						uint& rows ){
	std::ostream * str=0;

	const uint CS=types.size();
	ColType * cols=new ColType[CS];
	for( uint i=0; i<CS; i++ ) cols[i]=types[i];
	auto_ptr<QHaccResultSet> ret( new QHaccResultSet( CS, cols ) );
	delete []cols;

	if( Utils::debug( Utils::CURIOSITY, str ) )
		*str<<"stmt is: ->"<<stmt<<"<-"<<endl;
	
	PGresult * res=PQexec( conn, stmt );
	if( !res || PQresultStatus( res )!=PGRES_TUPLES_OK ){
		rows=0;
		if( Utils::error( Utils::ERRFATAL, str ) ){
			*str<<"error retrieving rows from database"<<endl;
		}
	}
	else{
		const QString TCSEP=QC::TCSEP;
		rows=PQntuples( res );

		ret->startLoad( rows );
		for( uint i=0; i<rows; i++ ){
			QString ins=PQgetvalue( res, i, 0 );
			for( uint j=1; j<CS; j++ ) ins+=TCSEP+PQgetvalue( res, i, j );
			ret->loadRow( ins );
		}
		ret->stopLoad();
	}
	PQclear( res );

	return ret;
}

int PSQLDBPlugin::run( const QString& stmt ){
	std::ostream * str=0;

	if( Utils::debug( Utils::CURIOSITY, str ) )
		*str<<"stmt is: ->"<<stmt<<"<-"<<endl;

	PGresult * res=PQexec( conn, stmt );
	bool ok=( PQresultStatus( res )==PGRES_COMMAND_OK );
	if( !ok ){
		if( Utils::error( Utils::ERRFATAL, str ) ){
			*str<<"database error: "<<PQerrorMessage( conn )<<endl;
		}
	}
	PQclear( res );

	return ( ok ? QHaccResultSet::VALID : QHaccResultSet::UNKNOWN );
}

uint PSQLDBPlugin::cnt( Table t ){
	uint r=0;
	vector<ColType> v( 1, CTUINT );
	auto_ptr<QHaccResultSet> j=sel( "select count(*) from "+table( t ), v, r );
	return j->at( 0 ).getu( 0 );
}

QString PSQLDBPlugin::minmax( Table t, int col, bool max ){
	QString selector( max ? "max" : "min" );
	return "select "+selector+"("+
		selField( Utils::tcname( t, col ),
							Utils::tctype( t, col ) )+") from "+table( t );
}

QString PSQLDBPlugin::selField( const QString& fieldname, ColType t ) const {
	if( t==CTBOOL ) return "CASE WHEN "+fieldname+" THEN 'Y' else 'N' END";
	else if ( t==CTDATE ) return "to_char("+fieldname+", 'YYYY-MM-DD')";
	else return fieldname;
}

QString PSQLDBPlugin::sqlField( const TableCol & tc, ColType ct ) const {
	QString ret=tc.gets();
	if( ct==CTBOOL ) ret=( tc.getb() ? "true" : "false" );
	else{
		ret.replace( QRegExp( "'" ), "''" );		
		ret.prepend( "'" );
		ret.append( "'" );
	}
	return ret;
}

bool PSQLDBPlugin::atomizer() const{ return true; }
void PSQLDBPlugin::setAtom( AtomicOp op, QString ){
  if( op==COMMIT ){
    // if we still have atoms to commit, don't do anything.
    // if this was our last atom, commit everything
    if( atoms==1 ) run( "COMMIT" );
		atoms--;
		if( atoms<0 ) atoms=0;
  }
  else if( op==ROLLBACK ){
    // if we're rolling back, roll back everything
    run( "ROLLBACK" );
    atoms=0;
  }
  else{
    // if this is our first atom, start it. Subsequent atoms won't do anything
    atoms++;
    if( atoms==1 ) run( "BEGIN" );
  }
}

const PluginInfo& PSQLDBPlugin::info() const { return pinfo; }

QString PSQLDBPlugin::create( const QString& dbname ) const {
	return screate( dbname );
}

QString PSQLDBPlugin::screate( const QString& dbname ){
	QString ret="createdb "+dbname;

	ret.append( "\ncreate table journals( \n  id int primary key, \n  name text, \n  description text );" );

	ret.append( "\ncreate table accounts(\n  id int primary key, \n  name text, \n  obal text, \n  cbal text, \n  rbal text, \n  budget text, \n  transnums text, \n  defaultnum text, \n  num text, \n  instname text, \n  instaddr1 text, \n  instaddr2 text, \n  instcity text, \n  inststate text, \n  instzip text, \n  instphone text, \n  instfax text, \n  instemail text, \n  instcontact text, \n  instnotes text, \n  category bool, \n  taxed bool, \n  type int CHECK( type>-1 AND type<5 ), \n  pid int references accounts(id), \n  ameta text);" );

	ret.append( "\ncreate table transactions( \n  tid int primary key, \n  num text, \n  payee text, \n  memo text, \n  date date, \n  journalid int references journals(id), \n  type int, \n  tmeta text, \n  tvoid bool );" );

	ret.append( "\ncreate table splits( \n  sid int primary key, \n  stid int references transactions(tid), \n  acctid int references accounts(id), \n  sum text, \n  reco int, \n  recodate date, \n  smeta text, \n  staxable bool );" );

	ret.append( "\ncreate table namedtrans( \n  id int primary key, \n  name text, \n  tid int references transactions(tid), \n  acctid int references accounts(id), \n  shortcut text );" );

	ret.append( "\ncreate table jobs( \n  id int primary key, \n  lastrun date, \n  frequency int, \n  whattorun text );" );

	ret.append( "\ncreate table preferences( \n  pref text primary key, \n  value text );" );


	ret.append( "\ncreate index tdidx on transactions(date);" );
	ret.append( "\ncreate index tpidx on transactions(payee);" );

	ret.append( "\ncreate index stidx on splits(stid);" );
	ret.append( "\ncreate index saidx on splits(acctid);" );

	ret.append( "\ncreate index anidx on accounts(name);" );

	ret.append( "\ncreate index nnidx on namedtrans(name);" );
	ret.append( "\ncreate index nacct on namedtrans(acctid);" );
	ret.append( "\ncreate index ntidx on namedtrans(tid);" );

	return ret;
}

PSQLDBInfo::PSQLDBInfo() : QHaccSQLDBInfo(){
	atom=true;
	description="PostgreSQL Database";
	stubby="PSQL";
}

PSQLDBInfo::~PSQLDBInfo(){}