#include <string.h>
#include <stdio.h>
#include <ctype.h>
#include <sys/stat.h>
#include <sys/types.h>
 
#include <mysql.h>
//#include "dbsql.h"
//#include "db.h"

//extern db_init dbs;
//extern glb_enviroment;

int convAI(char *text){
int i=0,l,c, m = 1;

	c = strlen(text);
	while(c--){
		l = *(text+c);
		l = l - 48;
		l = l * m;
		m = m * 10;		
		i = i + l;	
		}
	return i;
}

int db_connect_db(){
	if (!(mysql_connect(&dbs.mysql,enviroment.host,enviroment.user,enviroment.passwd))){
		fprintf(stderr,"DB connect error:%s\n",mysql_error(&dbs.mysql));
		return -1;
		}
	return 0;
}
int db_disconnect_db(){
	mysql_close(&dbs.mysql);
	return 0;
}
int db_open_db(char *dbname){
	if (mysql_select_db(&dbs.mysql, dbname)) {
		fprintf(stderr,"DB open DB error:%d\n",mysql_error(&dbs.mysql));
		return -1;
		}
	return 0;
}
int db_use(){	//uses resources just to be able to do a query rigth after a previous query
MYSQL_RES *res;
	res = mysql_use_result(&dbs.mysql);
	mysql_free_result(res);
}

db_map *db_load_maps(int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol;
db_map *mp,*tmp;

	*noo = 0;
	mysql_query(&dbs.mysql,"SELECT name, parent FROM map_cloud");
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(mp = (db_map *) malloc((unsigned)(rol*sizeof(db_map))))) return 0;
	tmp = mp;
	while(row = mysql_fetch_row(res)){
                strcpy(mp->name, row[0]);
                strcpy(mp->parent, row[1]);
                mp++;
                }
        mysql_free_result(res);
        return(tmp);
}
db_map *db_read_map(db_map *map, int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol;
char line[200];
db_map *mp,*tmp;

	*noo = 0;
	sprintf(line,"SELECT name, parent FROM map_cloud WHERE parent='%s'",map->parent);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(mp = (db_map *) malloc((unsigned)(rol*sizeof(db_map))))) return 0;
	tmp = mp;
	while(row = mysql_fetch_row(res)){
                strcpy(mp->name, row[0]);
                strcpy(mp->parent, row[1]);
                mp++;
                }
        mysql_free_result(res);
        return(tmp);
}

int db_parent_map(db_map *map){
MYSQL_RES *res;
MYSQL_ROW row;
char line[200];

	if(!strcmp(map->parent,"-")) return 0;
	fprintf(stderr,"(%s)\n",map->parent);
	sprintf(line,"SELECT name, parent FROM map_cloud WHERE name='%s'",map->parent);	// step back in hierarchy
	if(! mysql_query(&dbs.mysql,line)) return 0;
	res = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(res);
	strcpy(map->name, row[0]);
	strcpy(map->parent, row[1]);
	mysql_free_result(res);
	return 1;
}

int db_add_map(db_map *mp){
char line[200];

	sprintf(line,"INSERT INTO map_cloud VALUES ('%s','%s')",mp->name,mp->parent);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}

int db_del_map(db_map *mp){
char line[200];

	sprintf(line,"DELETE FROM map_cloud WHERE name = '%s'",mp->name);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}

db_site *db_load_sites(int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol;
db_site *st,*tmp;

	*noo = 0;
	mysql_query(&dbs.mysql,"SELECT camid, name, descr FROM site_cloud");
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(st = (db_site *) malloc((unsigned)(rol * sizeof(db_site))))) return 0;
	tmp = st;
	while(row = mysql_fetch_row(res)){
		st->camid = convAI(row[0]);
                strcpy(st->name, row[1]);
                strcpy(st->descr, row[2]);
                st++;
                }
        mysql_free_result(res);
        return(tmp);
}

int db_read_site(db_site *st){
MYSQL_RES *res;
MYSQL_ROW row;
char line[200];

	sprintf(line,"SELECT camid, descr from site_cloud WHERE name = '%s'",st->name); 
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(res);
	st->camid = convAI(row[0]);
	strcpy(st->descr,row[1]);
	mysql_free_result(res);
	return 1;	
}
int db_add_site(db_site *st){
MYSQL_RES *result;
MYSQL_ROW row;
char line[200];
	if ( mysql_query(&dbs.mysql, "SELECT camid FROM glb_conf") == -1) return 0;
	result = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(result);
	st->camid = convAI(row[0]);
	mysql_free_result(result);

	sprintf(line,"UPDATE glb_conf SET camid = %d", ++st->camid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;

	sprintf(line,"INSERT INTO site_cloud VALUES (%d,'%s','%s')",st->camid,st->name,st->descr);
	if (!mysql_query(&dbs.mysql,line)) return 0;
	return 1;	
}

int db_del_site(db_site *st){
char line[200];
	sprintf(line,"DELETE FROM site_cloud WHERE name = '%s'",st->name);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}

int db_add_qs(db_qs *qs){
MYSQL_RES *res;
char line[200];

	sprintf(line,"SELECT dnid FROM qs_cloud WHERE dnid = %d",qs->dnid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	if(mysql_num_rows(res)) return 0;				//exist

	sprintf(line,"INSERT INTO qs_cloud VALUES (%d,%d,'%s')",qs->dnid,qs->camid,qs->map);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;
}

int db_del_qs(db_qs *qs){
char line[200];

	sprintf(line,"DELETE FROM qs_cloud WHERE dnid = %d",qs->dnid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}

int db_del_qsm(db_qs *qs){
char line[200];

	sprintf(line,"DELETE FROM qs_cloud WHERE map='%s'",qs->map);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}

db_qs *db_load_qsm(db_qs *qsm, int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol;
char line[200];
db_qs *qs,*tmp;

	*noo = 0;
	sprintf(line,"SELECT dnid, camid FROM qs_cloud WHERE map='%s'",qsm->map);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(qs = (db_qs *) malloc((unsigned)(rol * sizeof(db_qs))))) return 0;
	tmp = qs;
	while(row = mysql_fetch_row(res)){
		qs->dnid = convAI(row[0]);
		qs->camid = convAI(row[1]);
                qs++;
                }
        mysql_free_result(res);
        return(tmp);
}

/*
db_gs *db_load_gs(db_gs *gs, int *noo){
int rol;
MYSQL_RES *res;
MYSQL_ROW row;
char line[200];
db_gs *ts,*tmp;

	*noo = 0;
	sprintf(line,"SELECT dnid, ip, rcom, wcom, stid, sysname, descr FROM gs_cloud WHERE camid=%d", gs->camid);
	if ( mysql_query(&dbs.mysql, line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(ts = (db_gs *) malloc((unsigned)(rol*(sizeof(db_gs)))))) return 0;
	tmp = ts;
	while(row = mysql_fetch_row(res)){
                ts->camid = gs->camid;
                ts->dnid = convAI(row[0]);
                strcpy(ts->ip, row[1]);
                strcpy(ts->rcom, row[2]);
                strcpy(ts->wcom, row[3]);
                ts->stid = convAI(row[4]);
                strcpy(ts->sysname, row[5]);
                strcpy(ts->descr, row[6]);
                ts++;
                }
        mysql_free_result(res);
        return(tmp);
}
*/

/* Fill *host from host table assuming value _rowid */
int db_read_host(db_host *host){
MYSQL_RES *result;
MYSQL_ROW row;
char line[200];

	sprintf(line,"SELECT created, modified, dns_name, name, description, contact, tags FROM host WHERE _rowid = %d",host->_rowid);
	if ( mysql_query(&dbs.mysql, line) == -1) return -1;
	result = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(result);
	if (!mysql_num_rows(result)) {mysql_free_result(result);return 1;}		// Zero touched 

	strcpy(host->created,row[0]);
	strcpy(host->modified,row[1]);
	strcpy(host->dns_name,row[2]);
	strcpy(host->name,row[3]);
	strcpy(host->description,row[4]);
	strcpy(host->contact,row[5]);
	strcpy(host->tags,row[6]);

	mysql_free_result(result);
	return 0;
}

/* Fill *host from host table assuming value name */
int db_ipread_host(db_host *host){
MYSQL_RES *result;
MYSQL_ROW row;
char line[200];

	sprintf(line,"SELECT _rowid, created, modified, dns_name, description, contact, tags FROM host WHERE name = '%s'",host->name);
	if ( mysql_query(&dbs.mysql, line) == -1) return -1;
	result = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(result);
	if (!mysql_num_rows(result)) {mysql_free_result(result);return 1;}		// Zero touched 

	host->_rowid = convAI(row[0]);
	strcpy(host->created,row[1]);
	strcpy(host->modified,row[2]);
	strcpy(host->dns_name,row[3]);
	strcpy(host->description,row[4]);
	strcpy(host->contact,row[5]);
	strcpy(host->tags,row[6]);

	mysql_free_result(result);
	return 0;
}

/* Translate IP (host.name) to dns_name from host table */
int db_ip2name_read_host(db_host *host){
MYSQL_RES *result;
MYSQL_ROW row;
char line[200];

	fprintf(stderr,"db: translating (%s)\n", host->name);

	sprintf(line,"SELECT dns_name FROM host WHERE name = '%s'", host->name);
	if ( mysql_query(&dbs.mysql, line) == -1){
		fprintf(stderr,"db: transaction error\n");
		return -1;
	}
	result = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(result);
	if (!mysql_num_rows(result)){
		mysql_free_result(result);
		fprintf(stderr,"db: zero touched\n");
		return 1;
	}		// Zero touched 

	strcpy(host->dns_name,row[0]);

	mysql_free_result(result);
	return 0;
}

/*
int db_ipread_gs(db_gs *gs){
MYSQL_RES *result;
MYSQL_ROW row;
char line[200];
int i;

	sprintf(line,"SELECT dnid, rcom, sysname, descr FROM gs_cloud WHERE camid=%d ip='%s'",gs->camid, gs->ip);
	if ( i = mysql_query(&dbs.mysql, line) == -1) return 0;
	result = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(result);
	i = mysql_num_rows(result);
	if (!i) { mysql_free_result(result);return 0;}		// Zero touched 
	

	gs->dnid = convAI(row[0]);
	strcpy(gs->rcom,row[1]);
	strcpy(gs->sysname,row[2]);
	strcpy(gs->descr,row[3]);

	mysql_free_result(result);
	return 1;
}
*/

/*
int db_add_gs(db_gs *gs){
MYSQL_RES *result;
MYSQL_ROW row;
char line[200];
int i;

	//if(!db_read_gs(gs)) return 0;		//check if exist

	if ( mysql_query(&dbs.mysql, "SELECT dnid FROM glb_conf") == -1) return 0;
	result = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(result);
	gs->dnid = convAI(row[0]);
	mysql_free_result(result);
	sprintf(line,"SELECT dnid FROM gs_cloud WHERE ip='%s' AND camid=%d", gs->ip, gs->camid);
	if ( i = mysql_query(&dbs.mysql,line) == -1) return 0;
	result = mysql_store_result(&dbs.mysql);
	i = mysql_num_rows(result);
	if(i){	row = mysql_fetch_row(result);
		gs->dnid = convAI(row[0]);
		mysql_free_result(result);
		return 0;
		}
	mysql_free_result(result);

	sprintf(line,"UPDATE glb_conf SET dnid = %d", ++gs->dnid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	sprintf(line,"INSERT INTO gs_cloud VALUES (%d,%d,'%s','%s','%s',%d,%d,%d,'%s','%s')",gs->dnid,gs->camid,gs->ip,gs->rcom,gs->wcom,gs->stid,gs->oa,gs->oae,gs->sysname,gs->descr);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}
int db_open_gs(MYSQL_RES **result, db_gs *gs){
char line[200];

	sprintf(line,"SELECT dnid, ip, rcom, sysname, descr FROM gs_cloud WHERE camid=%d", gs->camid);
	if ( mysql_query(&dbs.mysql, line) == -1) return 0;
	*result = mysql_store_result(&dbs.mysql);
	return 1;
}
int db_open_next_gs(MYSQL_RES **result, db_gs *gs){
MYSQL_ROW row;

	if (!(row = mysql_fetch_row(*result))) return 0;
	gs->dnid = convAI(row[0]);
	strcpy(gs->ip,row[1]);
	strcpy(gs->rcom,row[2]);
	strcpy(gs->sysname,row[3]);
	strcpy(gs->descr,row[4]);
	return 1;
}

int db_del_gs(db_gs *gs){
char line[200];
	sprintf(line,"DELETE FROM gs_cloud WHERE dnid=%d AND camid=%d",gs->dnid, gs->camid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}
*/

int db_del_gn(db_gn *gn){
char line[200];
	sprintf(line,"DELETE FROM gn_cloud WHERE camid=%d AND dnid=%d AND idx=%d",gn->camid, gn->dnid, gn->idx);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;	
}

int db_quickread_event(char *name, db_event *ev){
MYSQL_RES *res;
MYSQL_ROW row;
char line[200];

	sprintf(line,"SELECT eid FROM events WHERE name='%s'",name);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(res);
	ev->eid = convAI(row[0]);
        mysql_free_result(res);
	return 1;
}

db_event *db_load_events(int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol=0;
db_event *ev,*tmp;

	*noo = 0;
	if (mysql_query(&dbs.mysql,"SELECT eid, edate, name, map, descr, dfile, dii, sid FROM events") == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(ev = (db_event *) malloc((unsigned)(rol * sizeof(db_event))))) return 0;
	tmp = ev;
	while(row = mysql_fetch_row(res)){
		ev->eid = convAI(row[0]);
                strcpy(ev->edate, row[1]);
                strcpy(ev->name, row[2]);
                strcpy(ev->map, row[3]);
                strcpy(ev->descr, row[4]);
                strcpy(ev->dfile, row[5]);
		ev->dii = convAI(row[6]);
		ev->sid = convAI(row[7]);
                ev++;
                }
        mysql_free_result(res);
        return(tmp);
}

int db_del_event(int eid){
char line[200];

	sprintf(line,"DELETE FROM events WHERE eid=%d",eid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;
}

int db_add_poll(db_poll *po){
MYSQL_RES *res;
char line[200];
unsigned long int n;

	sprintf(line,"SELECT eid, camid, dnid, idx FROM poll_cloud WHERE eid=%d AND camid=%d AND dnid=%d AND idx=%d",po->eid,po->camid,po->dnid,po->idx);
	mysql_query(&dbs.mysql,line); 
	res = mysql_store_result(&dbs.mysql);
	n = mysql_affected_rows(&dbs.mysql);
	mysql_free_result(res);
	switch(n){ 
		case 0:
			sprintf(line,"INSERT INTO poll_cloud VALUES (%d,%d,%d,%d)",po->eid,po->camid,po->dnid,po->idx);
			mysql_query(&dbs.mysql,line);
			db_use();
		}
	return 0;
}
int db_del_pollevent(int eid){
char line[200];

	sprintf(line,"DELETE FROM poll_cloud WHERE eid=%d",eid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;
}
int db_del_poll(db_poll *po){
char line[200];

	sprintf(line,"DELETE FROM poll_cloud WHERE eid=%d AND dnid=%d",po->eid,po->dnid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;
}
db_poll *db_load_poll(int eid,int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol;
char line[200];
db_poll *ev,*tmp;

	*noo = 0;
	sprintf(line,"SELECT camid, dnid, idx FROM poll_cloud WHERE eid = %d", eid);
	if ( mysql_query(&dbs.mysql, line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(ev = (db_poll *) malloc((unsigned)(rol * sizeof(db_poll))))) return 0;
	tmp = ev;
	while(row = mysql_fetch_row(res)){
		ev->camid = convAI(row[0]);
		ev->dnid = convAI(row[1]);
		ev->idx = convAI(row[2]);
                ev++;
                }
        mysql_free_result(res);
	return tmp;
}
int db_del_mib(int sid){
char line[200];

	sprintf(line,"DELETE FROM mib_cloud WHERE sid = %d", sid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;
}

int db_add_mib(db_mibs *mib){
char line[200];
MYSQL_RES *res;
MYSQL_ROW row;
int i;

	sprintf(line,"SELECT sid FROM mib_cloud WHERE string = '%s'", mib->string);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	i = mysql_num_rows(res);
	mysql_free_result(res);
	if(i) return 0;			// it already exists

	if ( mysql_query(&dbs.mysql, "SELECT sid FROM glb_conf") == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(res);
	mib->sid = convAI(row[0]);
	mysql_free_result(res);
	sprintf(line,"UPDATE glb_conf SET sid = %d", ++mib->sid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;

	sprintf(line,"INSERT INTO mib_cloud VALUES(%d,%d,'%s','%s','%s')", mib->sid, mib->dii, mib->name, mib->descr, mib->string);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	return 1;
}

int db_read_mib(db_mibs *mib, int sid){
char line[200];
MYSQL_RES *res;
MYSQL_ROW row;

	sprintf(line,"SELECT dii, name, string, descr FROM mib_cloud WHERE sid = %d",sid);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	row = mysql_fetch_row(res);
	mib->sid = sid;
	mib->dii = convAI(row[0]);
	strcpy(mib->name, row[1]);
	strcpy(mib->string, row[2]);
	strcpy(mib->descr, row[3]);
	mysql_free_result(res);
	return 0;
}

db_mibs *db_load_mibs(int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
int rol;
db_mibs *mb, *tmp;

	*noo = 0;
	if ( mysql_query(&dbs.mysql, "SELECT sid, dii, name, string, descr FROM mib_cloud") == -1) return 0;
	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!(mb = (db_mibs *) malloc((unsigned)(rol * sizeof(db_mibs))))) return 0;
	tmp = mb;
	while(row = mysql_fetch_row(res)){
		mb->sid = convAI(row[0]);
		mb->dii = convAI(row[1]);
                strcpy(mb->name,row[2]);
                strcpy(mb->string,row[3]);
                strcpy(mb->descr,row[4]);
                mb++;
                }
        mysql_free_result(res);
        return tmp;
}

int db_add_record( db_rec *rec){
MYSQL_RES *result;
char line[200];

	sprintf(line,"INSERT INTO dat_cloud VALUES(%d,%d,%d,'%s','%s')",rec->eid, rec->dnid, rec->idx, rec->rdate, rec->raw);
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;
	result = mysql_store_result(&dbs.mysql);
	mysql_free_result(result);
	return 0;
}

db_rec *db_load_record( db_event *ev, int *noo){
MYSQL_RES *res;
MYSQL_ROW row;
char line[200];
int rol;		// e is used to detect if int is used as data result
db_rec *rec, *tmp;

	*noo = 0;
	sprintf(line,"SELECT eid, dnid, idx, rdate raw FROM dat_cloud ORDER BY dnid DESC, idx DESC");
	if ( mysql_query(&dbs.mysql,line) == -1) return 0;

	res = mysql_store_result(&dbs.mysql);
	rol = mysql_num_rows(res);
	*noo = rol;
	if (!( rec = (db_rec *) malloc((unsigned)(rol * sizeof(db_rec))))) return 0;
	tmp = rec;
	while(row = mysql_fetch_row(res)){
		rec->eid = convAI(row[0]);
		rec->dnid = convAI(row[1]);
		rec->idx = convAI(row[2]);
		strcpy(rec->rdate,row[3]);
		strcpy(rec->raw,row[4]);
		rec++;
	}
	mysql_free_result(res);
	return tmp;
}