/************************* * * * * * * * * * * * * ***************************
    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 "mysqlplugin.h"
#include "qhaccutils.h"
#include "qhacctable.h"

#include <qregexp.h>

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

const MySQLDBInfo MySQLDBPlugin::pinfo;

/* read and write to a MySQL database */
MySQLDBPlugin::MySQLDBPlugin() : QHaccSQLDBPlugin(){ conn=0; }
MySQLDBPlugin::~MySQLDBPlugin(){ if( conn ) mysql_close( conn ); }
 
bool MySQLDBPlugin::connect( QHacc *, const QString& home, QString& err ){
	// make the db connection


	// -f MYSQL:DB:HOST:USER:PASSWD to connect, but the MYSQL: is stripped
	QString strs[5];
	Utils::parser( home, ":", 0, strs, 4 );

	conn=mysql_init( conn );
	mysql_options( conn, MYSQL_READ_DEFAULT_GROUP, "qhacc" );
	if( !mysql_real_connect( conn, strs[1], strs[2], strs[3], strs[0], 
													 0, NULL, 0 ) ){
		err=mysql_error( conn );
		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ) *str<<err<<endl;
		return false;
	}
	return true;
}

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

auto_ptr<QHaccResultSet> MySQLDBPlugin::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;

	rows=0;
	if( !mysql_real_query( conn, stmt, stmt.length() ) ){
		MYSQL_RES * res=mysql_store_result( conn );
		MYSQL_ROW row;
		const QString TCSEP=QC::TCSEP;
		ret->startLoad();
		while( ( row=mysql_fetch_row( res ) ) ){
			QString ins=row[0];
			for( uint i=1; i<CS; i++ ) ins+=TCSEP+row[i];
			ret->loadRow( ins );
			rows++;
		}
		ret->stopLoad();
		mysql_free_result( res );
	}
	return ret;
}

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

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

	int ok=QHaccResultSet::VALID;
	if( mysql_real_query( conn, stmt, stmt.length() ) ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERRFATAL, str ) ) *str<<mysql_error( conn )<<endl;
		ok=QHaccResultSet::UNKNOWN;
	}
	return ok;
}

uint MySQLDBPlugin::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 MySQLDBPlugin::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 MySQLDBPlugin::selField( const QString& fieldname, ColType t ) const {
	if( t==CTBOOL ) return "if("+fieldname+"=0, 'N', 'Y')";
	else if ( t==CTDATE ) return "date_format("+fieldname+", '%Y-%m-%d')";
	else return fieldname;
}

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

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

QString MySQLDBPlugin::screate( const QString& dbname ){
	QString ret="create database "+dbname+";\nuse "+dbname+";";

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

	ret.append( "\ncreate table accounts(\n  id int primary key, \n  name text, \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 text, \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 int primary key, \n  num varchar(20), \n  payee varchar(50), \n  memo text, \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 int 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), staxable bool );" );

	ret.append( "\ncreate table namedtrans( \n  id int 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 int primary key, \n  lastrun date, \n  frequency int, \n  whattorun text );" );

	ret.append( "\ncreate table preferences( \n  pref varchar(30) 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(50));" );

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

MySQLDBInfo::MySQLDBInfo() : QHaccSQLDBInfo(){
	description="MySQL Database";
	stubby="MYSQL";
}

MySQLDBInfo::~MySQLDBInfo(){}