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

#include <qregexp.h>
#include <qtextstream.h>

/***********************************************************************/
/* QHACCSQLDBPLUGIN                                                    */
/*   an abstract class that provides the format of the functions that  */
/*   will need to be satisfied by plugin DB drivers                    */
/*                                                                     */
/***********************************************************************/
const QHaccSQLDBInfo QHaccSQLDBPlugin::pinfo;

QString QHaccSQLDBPlugin::table( Table t ){
	//if( t==XTRANS ) return QC::TABLENAMES[QC::TRANT]+QString( " t, " )+
	//									QC::TABLENAMES[QC::SPLTT]+QString( " s" );
	if( t==XTRANS ) return QC::TABLENAMES[QC::SPLTT]+QString( " s, " )+
										QC::TABLENAMES[QC::TRANT]+QString( " t" );
	else return QC::TABLENAMES[( int )t];
}

int QHaccSQLDBPlugin::table( const QString& t ){
	for( int i=0; i<QC::NUMTABLES; i++ ) if( QC::TABLENAMES[i]==t.lower() )
		return i;
	return -1;
}

QHaccSQLDBPlugin::QHaccSQLDBPlugin(){}
QHaccSQLDBPlugin::~QHaccSQLDBPlugin(){}

bool QHaccSQLDBPlugin::load( QString& ){ return true; }
bool QHaccSQLDBPlugin::save( QString& ){ return true; }

QString QHaccSQLDBPlugin::iconv( Table t, const TableRow& r ) const {
	// convert a TableRow into a string suitable for insertions into a database

	QString ret("(" );

	ret.append( sqlField( r.get( 0 ), Utils::tctype( t, 0 ) ) );
	for( int i=1; i<Utils::tcols( t ); i++ ){
		ret.append( "," );
		ret.append( sqlField( r[i], Utils::tctype( t, i ) ) );
	}
	return ret.append( ")" );
}

QString QHaccSQLDBPlugin::sqlField( const TableSelect& ts, ColType ct ) const {
	int type;
	PosVal pv;
	ts.getAll( pv, type );
	return ts.sqlSel()+sqlField( pv.getv(), ct );
}


int QHaccSQLDBPlugin::add( Table t, const TableRow& r ){
	if( t==XTRANS ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERROPER, str ) )
			*str<<"cannot insert into XTRANS"<<endl;
		return QHaccResultSet::UNKNOWN;
	}

	QString insert="insert into ";
	insert.append( table( t ) );
	insert.append( "(" );
	int i=0;
	insert.append( Utils::tcname( t, i++ ) );
	for( ; i<Utils::tcols( t ) ; i++ ){
		insert.append( "," );
		insert.append( Utils::tcname( t, i ) );
	}
	insert.append( ") values " );
	insert.append( iconv( t, r ) );

	return run( insert );
}

void QHaccSQLDBPlugin::updateWhere( Table t, const TableSelect& ts,
																		const TableUpdate& tu ){
	if( t==XTRANS ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERROPER, str ) )
			*str<<"cannot update XTRANS"<<endl;
		return;
	}

	int pos=0;
	int type=0;
	TableCol col;
	PosVal tpv;
	ts.getAll( tpv, type );
	tpv.get( pos, col );

	QString stmt=QString( "update "+table( t )+" set " );
	for( uint i=0; i<tu.cnt(); i++ ){
		if( i>0 ) stmt.append( ", " );
		PosVal pv=tu[i];
		stmt.append( Utils::tcname( t, pv.getp() ) );
		stmt.append( "="+sqlField( pv.getv(), Utils::tctype( t, pv.getp() ) ) );
	}
	if( ts.sqlValid() ){
		stmt.append( " where " );
		stmt.append( Utils::tcname( t, pos )+
								 sqlField( ts, Utils::tctype( t, pos ) ) );
	}
	run( stmt );
}

void QHaccSQLDBPlugin::updateWhere( Table t, const TableSelect& ts,
																		const TableRow& replace ){
	if( t==XTRANS ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERROPER, str ) )
			*str<<"cannot update XTRANS"<<endl;
		return;
	}

	PosVal pv;
	int pos, check;
	TableCol equals;
	ts.getAll( pv, check );
	pv.get( pos, equals );

	QString stmt="update "+table( t )+" set ";
	int i=0;
	stmt.append( Utils::tcname( t, i ) );
	stmt.append( "="+sqlField( replace.get( i ), Utils::tctype( t, i ) ) );
	for( i=1; i<Utils::tcols( t ); i++ ){
		stmt.append( ", " );
		stmt.append( Utils::tcname( t, i ) );
		stmt.append( "="+sqlField( replace.get( i ), Utils::tctype( t, i ) ) );
	}
	stmt.append( " where " );
	stmt.append( Utils::tcname( t, pos ) );
	stmt.append( "="+sqlField( equals, Utils::tctype( t, pos ) ) );

	run( stmt );
}

auto_ptr<QHaccResultSet> QHaccSQLDBPlugin::getWhere( Table t,
																										 const TableGet& mtg,
																										 vector<TableSelect> arr, 
																										 uint& rr ){
	QString stmt="select ";
	uint tgsz=mtg.cnt();
	TableGet tg=mtg;

	const bool XT=( t==XTRANS );
	if( tgsz==0 ){		// return all fields
		tgsz=Utils::tcols( t );
		vector<int> cols;
		for( int i=0; i<( int )tgsz; i++ ) cols.push_back( i );
		tg=TableGet( cols );
	}

	vector<ColType> types;
	for( uint i=0; i<tgsz; i++ ){
		if( i>0 ) stmt.append( "," );
		int tgcol=tg[i];
		QString name=Utils::tcname( t, tgcol );
		ColType type=Utils::tctype( t, tgcol );

		types.push_back( type );

		if( tg.getMod( i )==TableGet::UQ ) stmt.append( "distinct " );
		stmt.append( selField( name, type ) );
	}

	stmt.append( " from " );
	stmt.append( table( t ) );

	bool gotone=false;
	uint arsz=arr.size();
	for( uint i=0; i<arsz; i++ ){
		if( arr[i].sqlValid() ){
			if( gotone ) stmt.append( " and " );
			else{
				gotone=true;
				stmt.append( " where " );
			}

			int pos=0;
			int type=0;
			TableCol col;
			PosVal pv;
			arr[i].getAll( pv, type );
			pv.get( pos, col );
			stmt.append( Utils::tcname( t, pos )+
									 sqlField( arr[i], Utils::tctype( t, pos ) ) );
		}
	}
	
	if( XT ){
		if( gotone ) stmt.append( " and " );
		else stmt.append( " where " );
		stmt.append( QString( "t." )+Utils::tcname( TRANSACTIONS, QC::TID )+
								 QString( "=s." )+Utils::tcname( SPLITS, QC::STID ) );
	}



	return sel( stmt, types, rr );
}

auto_ptr<QHaccResultSet> QHaccSQLDBPlugin::getWhere( Table t,
																										 vector<TableSelect> ts,
																										 uint& rr ){
	return getWhere( t, TableGet(), ts, rr );
}

auto_ptr<QHaccResultSet> QHaccSQLDBPlugin::getWhere( Table t,
																										 const TableSelect& ts, 
																										 uint& rr ){
	
	return getWhere( t, TableGet(), vector<TableSelect>( 1, ts ), rr );
}

void QHaccSQLDBPlugin::deleteWhere( Table t, const TableSelect& ts ){
	if( t==XTRANS ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERROPER, str ) )
			*str<<"cannot delete from XTRANS"<<endl;
		return;
	}

	int pos=0;
	int type=0;
	TableCol col;
	PosVal pv;
	ts.getAll( pv, type );
	pv.get( pos, col );
	QString stmt="delete from "+table( t );
	if( ts.sqlValid() ){
		stmt.append( " where " );
		stmt.append( Utils::tcname( t, pos )+
								 sqlField( ts, Utils::tctype( t, pos ) ) );
	}
	run( stmt );
}

bool QHaccSQLDBPlugin::load( Table t, const QHaccResultSet * model ){
	if( t==XTRANS ){
		std::ostream * str=0;
		if( Utils::error( Utils::ERROPER, str ) )
			*str<<"cannot update XTRANS"<<endl;
		return false;
	}

	bool retb=true;

	const uint mrows=model->rows();
	startLoad( t, mrows );
	for( uint i=0; i<mrows; i++ ){
		const TableRow& r=model->at( i );

		QString ret("insert into "+table( t )+" values (" );
		ret.append( sqlField( r.get( 0 ), Utils::tctype( t, 0 ) ) );
		for( int i=1; i<Utils::tcols( t ); i++ ){
			ret.append( "," );
			ret.append( sqlField( r.get( i ), Utils::tctype( t, i ) ) );
		}
		ret.append( ")" );
		
		int runcheck=run( ret );
		retb=( retb && ( 0==runcheck ) );
	}
	stopLoad( t );
	return retb;
}

TableCol QHaccSQLDBPlugin::minmax( const QString& query, ColType ct ){
  uint r=0;
  vector<ColType> v( 1, ct );
  auto_ptr<QHaccResultSet> j=sel( query, v, r );
	return ( r>0 ? j->at( 0 )[0] : TableCol() );	
}

TableCol QHaccSQLDBPlugin::min( Table t, int col ){
	return minmax( minmax( t, col, false ), Utils::tctype( t, col ) );
}

TableCol QHaccSQLDBPlugin::max( Table t, int col ){
	return minmax( minmax( t, col, true ), Utils::tctype( t, col ) );
}

bool QHaccSQLDBPlugin::dirty() const { return false; }
void QHaccSQLDBPlugin::startLoad( Table, uint ){ setAtom( BEGIN ); }
void QHaccSQLDBPlugin::stopLoad( Table ){ setAtom( COMMIT ); }
bool QHaccSQLDBPlugin::imprt( QHaccResultSet * tbls ){
	bool ret=true;
	// order the inserts so that RI is maintained. this is easy
	// so long as we order the inserts by APID and not AID
	const uint ROWS=tbls[QC::ACCTT].rows();
	QHaccResultSet tbl( QC::ACOLS, QC::ACOLTYPES, ROWS );
	QHaccTableIndex idx( &tbls[QC::ACCTT], QC::APID, CTUINT, QC::AID, CTUINT );

	for( uint j=0; j<ROWS; j++ )	tbl+=tbls[QC::ACCTT][idx[j]];
	tbls[QC::ACCTT]=tbl;
	
	setAtom( BEGIN, "loader" );
	for( int i=0; i<QC::NUMTABLES; i++ ){
		for( uint j=0; j<tbls[i].rows(); j++ ) add( ( Table )i, tbls[i][j] );
	}
	setAtom( COMMIT, "loader" );
	return ret;
}

bool QHaccSQLDBPlugin::exprt( QHaccResultSet * tbls ){
	for( int i=0; i<QC::NUMTABLES; i++ ){
		uint rr=0;
		auto_ptr<QHaccResultSet> rslt=getWhere( ( Table )i, TableSelect(), rr );
		tbls[i]=QHaccResultSet( *rslt );
	}
	return true;
}

void QHaccSQLDBPlugin::setAtom( AtomicOp, QString ){}

QString QHaccSQLDBPlugin::selField( const QString& fname, ColType ) const{ 
	return fname;
}

QString QHaccSQLDBPlugin::sqlField( const TableCol& tc, ColType ct ) const {
	QString ret=tc.gets();
	if( !( ct==CTUINT || ct==CTINT || ct==CTFLOAT ) ) {
		ret.replace( QRegExp( "'" ), "''" );
		ret.prepend( "'" );
		ret.append( "'" );
	}
	return ret;
}

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

QHaccSQLDBInfo::QHaccSQLDBInfo(){
	atom=raw=false;
	guisel=true;
	targ=DATABASE;
	piprefs.reset( new QHaccResultSet( QC::IPICOLS, QC::IPICOLTYPES ) );
}

QHaccSQLDBInfo::~QHaccSQLDBInfo(){}