/************************* * * * * * * * * * * * * ***************************
    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 "sqliteplugin.h"
#include "qhacctablerows.h"
#include "qhaccutils.h"
#include "resultset.h"

#include <qregexp.h>

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

const SQLiteInfo SQLiteDBPlugin::pinfo;

/* read and write to a SQLite database */
SQLiteDBPlugin::SQLiteDBPlugin() : QHaccSQLDBPlugin(){ conn=0; }
SQLiteDBPlugin::~SQLiteDBPlugin(){
	if( conn )
#ifdef SQLITE3
		sqlite3_close( conn ); 
#else
		sqlite_close( conn ); 
#endif

}

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

bool SQLiteDBPlugin::connect( QHacc *, const QString& home, QString& err ){
	// make the db connection	
	atoms=0; // we start with no atoms pending

#ifdef SQLITE3
	int eroor=sqlite3_open( home, &conn );
	if( eroor!=SQLITE_OK ){
		err=QString( sqlite3_errmsg( conn ) );
 		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ) *str<<err<<endl;
		return false;
	}
#else
	char * er=0;
	conn=sqlite_open( home, 0, &er );
	if( conn==0 ){
		err=QString( er );
 		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ) *str<<er<<endl;
		free( er );
		return false;
	}
#endif

	return true;
}

void SQLiteDBPlugin::startLoad( Table, uint ){ setAtom( BEGIN ); }
void SQLiteDBPlugin::stopLoad( Table ){ setAtom( COMMIT ); }

void SQLiteDBPlugin::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" );
	}
}

int SQLiteDBPlugin::callback( void * rs , int argc, char **argv, 
															char ** ){

	QHaccResultSet * rslt=( QHaccResultSet * )rs;

	QString ins=( argv[0] ? argv[0] : "" );
	if( rslt->coltype( 0 )==CTDATE ) ins=convdate( ins );

  for( int i=1; i<argc; i++ ){
		QString data=( argv[i] ? argv[i] : "" );
		if( rslt->coltype( i )==CTDATE ) data=convdate( data );
		ins.append( QC::TCSEP+data );
	}
	rslt->loadRow( ins );
	return 0;
}

auto_ptr<QHaccResultSet> SQLiteDBPlugin::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;
	
	ret->startLoad();
	char * err=0;
#ifdef SQLITE3
	if( sqlite3_exec( conn, stmt, &SQLiteDBPlugin::callback, ret.get(),
										&err )!=SQLITE_OK ){
#else
	if( sqlite_exec( conn, stmt, &SQLiteDBPlugin::callback, ret.get(),
									 &err )!=SQLITE_OK ){
#endif
 		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ) *str<<err<<endl;
		free( err );
	}
	// else the callback will fill in our resultset for us
	ret->stopLoad();
	rows=ret->rows();
	return ret;
}

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

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

	char * err=0;
#ifdef SQLITE3
	if( sqlite3_exec( conn, stmt, 0, 0, &err )!=SQLITE_OK ){
#else
	if( sqlite_exec( conn, stmt, 0, 0, &err )!=SQLITE_OK ){
#endif
 		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ) *str<<err<<endl;
		free( err );
		return QHaccResultSet::UNKNOWN;
	}
	return QHaccResultSet::VALID;
}

uint SQLiteDBPlugin::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 SQLiteDBPlugin::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 SQLiteDBPlugin::sqlField( const TableCol& tc, ColType ct ) const{
	if( ct==CTDATE ) return "'"+tc.getd().toString( "yyyyMMdd" )+"'";
	else return QHaccSQLDBPlugin::sqlField( tc, ct );
}

QString SQLiteDBPlugin::convdate( const QString& datestr ){
	// convert datestr from the database format to one that TableCol recognizes
	// that is, from YYYYMMDD to YYYY-MM-DD
	//return datestr.mid( 4, 2 )+"/"+datestr.right( 2 )+"/"+datestr.left( 4 );
	return datestr.left( 4 )+"-"+datestr.mid( 4, 2 )+"-"+datestr.right( 2 );
}

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

QString SQLiteDBPlugin::screate( const QString& dbname ){
	QString ret="sqlite "+dbname+";";

	ret.append( "\ncreate table journals( \n  id integer primary key, \n  name varchar(30), \n  description varchar(100) );" );

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

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

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

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

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

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

	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;
}

SQLiteInfo::SQLiteInfo(){
	description="SQLite Database";
	stubby="SQLL";
	atom=true;
}