/*
 *  ADP (Another Data Processor) www.adp.la
 *  Copyright (C) 2010 Katsuhisa Ohfuji <katsuhisa@ohfuji.name>
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License version 2 as
 *  published by the Free Software Foundation.
 *
 *  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., 51 Franklin Street, Fifth Floor, Boston,
 *  MA 02110-1301, USA.
 */

#ifndef ADP_BUILTIN_DB_H
#define ADP_BUILTIN_DB_H

struct ExecContext_DB_Base : public ExecContextBase {
	// db connector
	kz_odbc					*con;
	kz_stmt					stmt;			/* stmt SQL文に対応した結果が入る */

	ExecContext_DB_Base(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContextBase(p_, f_, pred_, l) {}	// ????

	virtual void cleanup() { ExecContextBase::cleanup(); stmt.freestmt();}

	bool setODBC() {
		const PObject *constr = get<PObject>(0);
		if ( !constr->c_str() ) return false;
		con = db_connect(constr->c_str());
		stmt.setODBC(con);
		return true;
	}

	void setParameter(const PObject *parameter) {
		if ( parameter == &pnil ) {
			stmt, kz_odbc_null;
		} else if ( parameter->c_str() != 0 ) {
			stmt, parameter->c_str();
		} else if ( parameter->c_double() ) {
			stmt,  *parameter->c_double();
		} else if ( parameter->c_integer32(this) ) {
			stmt,  *parameter->c_integer32(this);
		} else if ( parameter->c_integer() ) {
			stmt,  *parameter->c_integer();
		} else {
			stmt, kz_odbc_null;
		}
	}

	void setParameterList(const PObject *parameters) {
		if ( typeid(*parameters) == typeid(PList) ) {
			while ( typeid(*parameters) == typeid(PList) ) {
				const PList *l = dynamic_cast<const PList*>(parameters);
				if ( l->lval() == &pnil && l->rval() == &pnil ) return;
				const PObject *lval = l->lval();
				setParameter(lval);
				parameters = l->rval();
			}
		} else if ( typeid(*parameters) == typeid(PArray) ) {
			const PArray *pa = dynamic_cast<const PArray*>(parameters);
			for ( size_t i = 0; i < pa->size(); i++ ) {
				setParameter((*pa)[i]);
			}
		}
	}

	void setParameterArray(vector<const PObject*> &parameters) {
		for ( vector<const PObject*>::iterator i = parameters.begin(); i < parameters.end(); i++ ) {
			setParameter(*i);
		}
	}

	bool checkError(PException &excp) {
		string msg;
		msg.clear();
		if ( stmt.iserr() ) {
			kz_string_array	errs = stmt.errors();
			kz_string_array::iterator j;
			for ( j = errs.begin(); j < errs.end(); j++ ) {
				msg += *j;
				msg += "\n";
			}
			stmt.errclear();
		}
		if ( con->iserr() ) {
			kz_string_array	errs = con->errors();
			kz_string_array::iterator j;
			for ( j = errs.begin(); j < errs.end(); j++ ) {
				msg += *j;
				msg += "\n";
			}
			con->errclear();
		}
		if ( !msg.empty() ) {
			return RERR(excp,msg);
		}
		return true;
	}

	bool checkConError() {
		if ( con->iserr() ) return false;
		return true;
	}

	bool prtError() {
		if ( stmt.iserr() ) return false;
		return true;
	}

	bool prtConError() {
		if ( con->iserr() ) {
			kz_string_array	errs = con->errors();
			kz_string_array::iterator j;
			for ( j = errs.begin(); j < errs.end(); j++ ) {
				cout << *j << endl;
			}
			return false;
		}
		return true;
	}

	bool setResult(int vidx, PException &excp) {
		//kz_string_array result = stmt.next();
		bool result = stmt.fetch();
		vector< describe_col > &colinfo = stmt.cinfo();
		if ( !checkError(excp) ) return false;
		if ( !result ) return false;

		PArray *alloc_ary = 0;

		if ( !size_check_and_alloc_array(colinfo.size() + vidx, excp, &alloc_ary) ) return false;
		kz_string_array::iterator i;
		bool nflg;
		for ( size_t i = 0; i < colinfo.size(); i++, vidx++ ) {
			switch ( colinfo[i].typ ) {
				case SQL_INTEGER :
				case SQL_SMALLINT :
					int intval;
					intval = 0;
					stmt.get( i+1,intval,nflg);
					if ( !nflg ) {
						const PInteger *po = pmm.newPInteger(pobjs,intval);
						if ( !unify<PInteger>( vidx, po, excp, &colinfo[i].name, alloc_ary) ) return false;
					} else {
						if ( !unify<PNil>( vidx, &pnil, excp, &colinfo[i].name, alloc_ary) ) return false;
					}
					break;
				case SQL_FLOAT :
				case SQL_REAL :
				case SQL_DOUBLE :
					double dblval;
					stmt.get( i+1,dblval,nflg);
					if ( !nflg ) {
						const PDouble *po = pmm.newPDouble(pobjs,dblval);
						if ( !unify<PDouble>( vidx, po, excp, &colinfo[i].name, alloc_ary) ) return false;
					} else {
						if ( !unify<PNil>( vidx, &pnil, excp, &colinfo[i].name, alloc_ary) ) return false;
					}
					break;
				case SQL_NUMERIC :
				case SQL_DECIMAL :
				default :
					string strval;
					stmt.get( i+1, strval,nflg);
					if ( !nflg ) {
						const PString *po = pmm.newPString(pobjs,strval);
						if ( !unify<PString>( vidx, po, excp, &colinfo[i].name, alloc_ary) ) return false;
					} else {
						if ( !unify<PNil>( vidx, &pnil, excp, &colinfo[i].name, alloc_ary) ) return false;
					}
					break;
			}
		}
		return true;
	}

	bool setResultHash(size_t idx, PException &excp) {
		string_array result = stmt.next();
		if ( !checkError(excp) ) return false;
		if ( result.empty() ) return false;
		vector< describe_col > &colinfo = stmt.cinfo();
		const PVeriable *v = dynamic_cast<const PVeriable*>((*pred)[idx]);
		if ( !v ) return false;
		if ( colinfo.size() != result.size() ) return INTERNAL_ERROR_sql_hash_result(excp);	// internal error

		PObject *list = &pnil;
		for (size_t i = colinfo.size() - 1; i < colinfo.size(); i-- ) {
			PString *pn = pmm.newPString(pobjs,colinfo[i].name);
			PString *pv = pmm.newPString(pobjs,result[i]);
			PList *vlist = pmm.newPList(pobjs, pn, pv);
			PList *nlist = pmm.newPList(pobjs, vlist, list);
			list = nlist;
		}
		(*gl)[v->idx] = list;
		return true;
	}

	void makColValues(const PArray *param, const PArray *filter, kz_resultset_array &cols, vector<string> &col_names, vector<const PObject *> &col_values) {
		for ( kz_resultset_array::iterator i = cols.begin(); i < cols.end(); i++ ) {
			const string &key = (*i)["COLUMN_NAME"];
			if ( filter != 0 && filter->find_value(key) != -1 ) continue;
			if ( filter == 0 && strcmp( key.c_str(), "ID") == 0 ) continue;
			const PObject *col_value = (*param)[key];
			if ( col_value != &pnil ) {
				col_names.push_back(key);
				col_values.push_back(col_value);
			}
		}
	};

	bool addWhereAND( string &sql, const PArray *param, const PArray *filter, vector<const PObject *> &col_values, PException &excp) {
		string fstr;
		if ( filter != 0 ) {
			if ( filter->size() != 0 ) {
				// filterで指定されたparamのハッシュをWhere句にする
				for ( size_t i = 0; i < filter->size(); i++ ) {
					const PObject *col_name = (*filter)[i];
					if ( col_name->c_str() == 0 ) RERR_argment_type( excp, 3);
					const PObject *col_value = (*param)[col_name->c_str()];
					col_values.push_back(col_value);
					if ( i != 0 ) {
						fstr += " AND ";
					}
					fstr += col_name->c_str();
					fstr += " = ? ";
				}
			} else {
				// paramのハッシュを全てWhere句にする
				bool first = true;
				if ( param == 0 ) return false;
				for ( PHashArrayMap::const_iterator ki = param->hamap.begin(); ki != param->hamap.end(); ki++ ) {
					col_values.push_back( (*param)[ki->second] );
					if ( !first ) {
						fstr += " AND ";
					} else {
						first = false;
					}
					fstr += ki->first;
					fstr += " = ? ";
				}
			}
		} else {
			// IDのparamのハッシュをWhere句にする
			const PObject *col_value = (*param)["ID"];
			if ( col_value == 0 || col_value == &pnil ) return false;
			col_values.push_back( col_value);
			fstr += " ID = ? ";
		}
		if ( !fstr.empty() ) {
			sql += " WHERE " + fstr;
		} else if ( !filter || filter->size() != 0) {
			return false;	// Where句なしはfilterが空配列{}のときのみ許される。select/deleteは上から空配列が渡される
		}
		return true;
	};
};

struct ExecContext_Select : public ExecContext_DB_Base {
	PArray	filter; // ダミーの空配列
	int		ridx;
	ExecContext_Select(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l),ridx(0) {}

	virtual bool first(PException &excp) { 
		if ( pred->size() < 4 ) return RERR_argment_number(excp, 4);

		if ( !setODBC() ) return false; 
		const PArray *param = get<PArray>(1);
		const PObject *table = get<PObject>(2);
		if ( !table->c_str() ) return false;
		const PEVeriable *v = get<PEVeriable>(3);
		const PArray *a = get<PArray>(3);

		// テーブル名のチェック `が入っていたらエラー、インジェクション対策）
		if ( strchr( table->c_str(), table_quote_e) != 0 ) return RERR_DB_Cannot_Used_Quote_Char(excp);
		string sql("SELECT ");
		// SQLの組み立て
		ridx = 3;
		if ( pred->size() == 4 && v && v->isargs() ) {
			sql += "* ";
		} else if ( pred->size() == 5 && a ) {
			ridx = 4;
			for ( size_t i = 0; i < a->size(); i++ ) {
				if ( (*a)[i]->c_str() == 0 ) return RERR_argment_type( excp, 3);
				sql += (*a)[i]->c_str();
				if ( i != a->size() - 1 ) {
					sql += ", ";
				}
			}
		} else {
			for ( size_t i = 3; i < pred->size(); i++ ) {
				const PObject *item = (*pred)[i];
				if ( typeid(*item) != typeid(PVeriable) ) return RERR_argment_type( excp, i);
				sql += dynamic_cast<const PVeriable*>(item)->getName();
				if ( i != pred->size() - 1) {
					sql += ", ";
				}
			}
		}
		sql += " FROM ";
		sql += table_quote_s;
		sql += table->c_str();
		sql += table_quote_e; // テーブル名をクオートで囲む(インジェクション対策）

		// カラムと値のマップの作成（実際は別変数）
		vector<const PObject *> col_values;
		if ( !addWhereAND( sql, param, &filter, col_values, excp) ) return RERR_argment_type( excp, 1);

		if ( this->pred->opt.debug ) fputs( sql.c_str(), stderr);

		stmt, sql.c_str();
		setParameterArray(col_values);
		stmt, endsql;

		return backtrack(excp);
	};

	virtual bool backtrack(PException &excp) { 
		return setResult(ridx, excp);
	};
};


struct ExecContext_SQL : public ExecContext_DB_Base {
	ExecContext_SQL(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l) {}
	virtual bool first(PException &excp) { 
		if ( pred->size() < 3 ) return RERR_argment_number(excp, 3);
		if ( !setODBC() ) return false; 
		const PObject *sql = get<PObject>(1);
		if ( !sql->c_str() ) return false;
		const PObject *parameters = get<PObject>(2);

		if ( this->pred->opt.debug ) fputs( sql->c_str(), stderr);

		stmt, sql->c_str();
		setParameterList(parameters);
		stmt, endsql;
		if ( pred->size() == 3 ) {
			return checkError(excp);
		} else {
			return backtrack(excp);
		}
	};

	virtual bool backtrack(PException &excp) { 
		if ( pred->size() == 3 ) {
			return false;
		} else {
			return setResult(3, excp);
		}
	};
};

struct ExecContext_Insert : public ExecContext_DB_Base {
	ExecContext_Insert(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l) {}
	bool insert( const PArray *param, const PObject *table, PException &excp) {
		kz_stmt	col(con);
		kz_resultset_array cols = col.columns(table->c_str());
		
		int colcnt = 0;
		string sql("INSERT INTO ");

		sql += table_quote_s;
		sql += table->c_str();
		sql += table_quote_e; // テーブル名をクオートで囲む(インジェクション対策）
		sql += " (";

		// カラムと値のマップの作成（実際は別変数）
		vector<string> col_names;
		vector<const PObject *> col_values;
		for ( kz_resultset_array::iterator i = cols.begin(); i < cols.end(); i++ ) {
			const string key = (*i)["COLUMN_NAME"];
			const PObject *col_value = (*param)[key];
			if ( col_value != &pnil ) {
				col_names.push_back(key);
				col_values.push_back(col_value);
			}
		}

		for ( vector<string>::iterator i = col_names.begin(); i < col_names.end(); i++ ) {
			if ( i != col_names.begin() ) {
				sql += ",";
			}
			sql += *i;
		}
		sql += ") Values ( ";
		sql += "?";
		for ( size_t i = 1; i < col_names.size(); i++ ) {
			sql += ", ?";
		}
		sql += ") ";

		if ( this->pred->opt.debug ) fputs( sql.c_str(), stderr);

		stmt, sql.c_str();
		setParameterArray(col_values);
		stmt, endsql;

		return checkError(excp);
	};
	
	virtual bool first(PException &excp) { 
		if ( pred->size() < 3 ) return RERR_argment_number(excp, 3);

		if ( !setODBC() ) return false; 

		const PArray *param = get<PArray>(1);
		if ( param == 0 ) return RERR_argment_type( excp, 1);

		const PObject *table = get<PObject>(2);
		if ( !table->c_str() ) return false;
		// テーブル名のチェック `が入っていたらエラー、インジェクション対策）
		if ( strchr( table->c_str(), table_quote_e) != 0 ) return RERR_DB_Cannot_Used_Quote_Char(excp);

		return insert( param, table, excp);

	};

	virtual bool backtrack(PException &excp) { 
		return false;
	};
};

struct ExecContext_Update : public ExecContext_Insert {
	ExecContext_Update(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_Insert(p_, f_, pred_, l) {}

	bool update( const PArray *param, const PObject *table, const PArray *filter, PException &excp) {
		kz_stmt	col(con);
		kz_resultset_array cols = col.columns(table->c_str());
		
		int colcnt = 0;
		string sql("UPDATE ");
		sql += table_quote_s;
		sql += table->c_str();
		sql += table_quote_e; // テーブル名をクオートで囲む(インジェクション対策）
		sql += " SET ";

		// カラムと値のマップの作成（実際は別変数）
		vector<string> col_names;
		vector<const PObject *> col_values;
		makColValues(param, filter, cols, col_names, col_values);

		for ( vector<string>::iterator i = col_names.begin(); i < col_names.end(); i++ ) {
			if ( i != col_names.begin() ) {
				sql += ",";
			}
			sql += *i;
			sql += " = ? ";
		}

		if ( !addWhereAND( sql, param, filter, col_values, excp) ) return RERR_argment_type( excp, 3);

		if ( this->pred->opt.debug ) fputs( sql.c_str(), stderr);

		stmt, sql.c_str();
		setParameterArray(col_values);
		stmt, endsql;

		return checkError(excp);
	};

	virtual bool first(PException &excp) { 
		if ( pred->size() < 3 ) return RERR_argment_number(excp, 3);

		if ( !setODBC() ) return false; 

		const PArray *param = get<PArray>(1);
		if ( param == 0 ) return RERR_argment_type( excp, 1);

		const PObject *table = get<PObject>(2);
		if ( !table->c_str() ) return false;
		// テーブル名のチェック `が入っていたらエラー、インジェクション対策）
		if ( strchr( table->c_str(), table_quote_e) != 0 ) return RERR_DB_Cannot_Used_Quote_Char(excp);

		// Where句
		const PArray *filter = 0;
		if ( pred->size() > 3 ) {
			filter = get<PArray>(3);
			if ( filter == 0 ) return RERR_argment_type( excp, 3);
		}
		
		return update( param, table, filter, excp);
	};

	virtual bool backtrack(PException &excp) { 
		return false;
	};
};


struct ExecContext_Save : public ExecContext_Update {
	ExecContext_Save(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_Update(p_, f_, pred_, l) {}
	virtual bool first(PException &excp) { 
		if ( pred->size() < 3 ) return RERR_argment_number(excp, 3);

		if ( !setODBC() ) return false; 

		const PArray *param = get<PArray>(1);
		if ( param == 0 ) return RERR_argment_type( excp, 1);

		const PObject *table = get<PObject>(2);
		if ( !table->c_str() ) return false;
		// テーブル名のチェック `が入っていたらエラー、インジェクション対策）
		if ( strchr( table->c_str(), table_quote_e) != 0 ) return RERR_DB_Cannot_Used_Quote_Char(excp);

		// Where句
		const PArray *filter = 0;
		if ( pred->size() > 3 ) {
			filter = get<PArray>(3);
			if ( filter == 0 ) return RERR_argment_type( excp, 3);
		}

		string sql("SELECT COUNT(*) FROM ");
		sql += table_quote_s;
		sql += table->c_str();
		sql += table_quote_e; // テーブル名をクオートで囲む(インジェクション対策）

		// カラムと値のマップの作成（実際は別変数）
		vector<const PObject *> col_values;
		// Where句の作成（filterが無い場合、該当するIDで検索を行い、それがあればUpdate 無ければ Insertを行う）
		if ( !addWhereAND( sql, param, filter, col_values, excp) ) {
			return insert( param, table, excp);	
		}

		if ( this->pred->opt.debug ) fputs( sql.c_str(), stderr);

		stmt, sql.c_str();
		setParameterArray(col_values);
		stmt, endsql;

		int cnum;
		bool nflg;
		cnum = 1;
		stmt.fetch();
		stmt.get( 1, cnum, nflg);
		if ( cnum == 0 ) {
			return insert( param, table, excp);	
		} else {
			return update( param, table, filter, excp);
		
		}
		return checkError(excp);
	};

	virtual bool backtrack(PException &excp) { 
		return false;
	};
};


struct ExecContext_Delete : public ExecContext_Update {
	PArray	filter; // ダミーの空配列
	ExecContext_Delete(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_Update(p_, f_, pred_, l) {}

	bool dbdelete( const PArray *param, const PObject *table, PException &excp) {
		kz_stmt	col(con);
		kz_resultset_array cols = col.columns(table->c_str());
		
		int colcnt = 0;
		string sql("DELETE FROM ");
		sql += table_quote_s;
		sql += table->c_str();
		sql += table_quote_e; // テーブル名をクオートで囲む(インジェクション対策）
		sql += " ";

		// カラムと値のマップの作成（実際は別変数）
		vector<string> col_names;
		vector<const PObject *> col_values;
		makColValues(param, &filter, cols, col_names, col_values);

		if ( !addWhereAND( sql, param, &filter, col_values, excp) ) return RERR_argment_type( excp, 3);

		if ( this->pred->opt.debug ) fputs( sql.c_str(), stderr);

		stmt, sql.c_str();
		setParameterArray(col_values);
		stmt, endsql;

		return checkError(excp);
	};

	virtual bool first(PException &excp) { 
		if ( pred->size() != 3 ) return RERR_argment_number(excp, 3);

		if ( !setODBC() ) return false; 

		const PArray *param = get<PArray>(1);
		if ( param == 0 ) return RERR_argment_type( excp, 1);

		const PObject *table = get<PObject>(2);
		if ( !table->c_str() ) return false;
		// テーブル名のチェック `が入っていたらエラー、インジェクション対策）
		if ( strchr( table->c_str(), table_quote_e) != 0 ) return RERR_DB_Cannot_Used_Quote_Char(excp);

		return dbdelete( param, table, excp);
	};

	virtual bool backtrack(PException &excp) { 
		return false;
	};
};



struct ExecContext_Commit : public ExecContext_DB_Base {
	ExecContext_Commit(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l) {}
	virtual bool first(PException &excp) { 
		if ( pred->size() != 1 ) return RERR_argment_number(excp, 1);
		if ( !setODBC() ) return false; 
		con->Commit();
		return checkConError();
	};

	virtual bool backtrack(PException &excp) { 
		return false;
	};
};

struct ExecContext_Rollback : public ExecContext_DB_Base {
	ExecContext_Rollback(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l) {}
	virtual bool first(PException &excp) { 
		if ( pred->size() != 1 ) return RERR_argment_number(excp, 1);
		if ( !setODBC() ) return false; 
		con->Rollback();
		return checkConError();
	};

	virtual bool backtrack(PException &excp) { 
		return false;
	};
};

struct ExecContext_Tables : public ExecContext_DB_Base {
	ExecContext_Tables(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l) {}
	virtual bool first(PException &excp) { 
		if ( pred->size() < 2 ) return RERR_argment_number(excp, 2);

		if ( !setODBC() ) return false; 

		stmt.tables_();

		return backtrack(excp);
	};

	virtual bool backtrack(PException &excp) { 
		return setResult(1, excp);
	};
};

struct ExecContext_Columns : public ExecContext_DB_Base {
	ExecContext_Columns(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContext_DB_Base(p_, f_, pred_, l) {}
	virtual bool first(PException &excp) { 
		if ( pred->size() < 3 ) return RERR_argment_number(excp, 3);

		if ( !setODBC() ) return false; 
		const PObject *table = get<PObject>(1);
		if ( !table->c_str() ) return false;

		stmt.columns_(table->c_str());

		return backtrack(excp);
	};

	virtual bool backtrack(PException &excp) { 
		return setResult(2, excp);
	};
};


struct ExecContext_SetTableQuote : public ExecContextRoot {
	ExecContext_SetTableQuote(ExecContext *p_, void *f_, const PPredicate *pred_, VLocal *l) : ExecContextRoot(p_, f_, pred_, l) {}

	virtual bool first(PException &excp) { 
		if ( pred->size() != 1 && pred->size() != 2 ) return RERR_argment_number(excp, 1);
		const char *quote_s = cnv_charp(0);
		const char *quote_e = quote_s;
		if ( pred->size() > 1 ) {
			quote_e = cnv_charp(1);
		}
		table_quote_s = *quote_s;
		table_quote_e = *quote_e;
		return true;
	}
};

#endif
