#include "rssSql.h"

bool createChannelTable(Database &db)
{
	Query q(db);
	return q.execute( \
	"CREATE TABLE channelTable ( \
	channelID INTEGER PRIMARY KEY autoincrement, \
	title VARCHAR(40), \
	pubDate DATE, \
	link VARCHAR(1024), \
	description VARCHAR(1024), \
	author VARCHAR(128),\
	status INT,\
	cando bit(32)\
	)" );
}

bool createItemTable(Database &db)
{
	Query q(db);
	return q.execute("\
	CREATE TABLE itemTable ( \
	itemID INTEGER PRIMARY KEY autoincrement,  \
	channelID INTEGER, \
	title VARCHAR(40), \
	pubDate DATE,  \
	link VARCHAR(1024),  \
	mediaURL VARCHAR(1024), \
	length INT, \
	duration INT, \
	description VARCHAR(1024), \
	author VARCHAR(128), \
	status INT,\
	cando bit(32)\
	)" );
}

#define APPEND_NAME_IF(info, x)  if(info->x) {strcpy(ptr, #x); ptr += sizeof(#x)-1; *ptr=','; ptr++; } 
#define APPEND_NAME(x)  { strcpy(ptr, #x); ptr += sizeof(#x)-1; *ptr=','; ptr++; } 

#define APPEND_VALUE_IF(info, x)  if(info->x) { *ptr = '\''; strcpy(ptr+1, info->x); \
                                ptr += 1 + strlen(info->x); *ptr = '\''; ptr++; *ptr =','; *ptr++; } 
#define APPEND_VALUE(info, x)  { _itoa(info->##x, ptr, 10); ptr += strlen(ptr); *ptr=','; ptr++;} 

int  insertChannel(Database &db, ChannelInfo *channelInfo)
{
	Query q(db);
	char command[4096];
	char *ptr = command;

	// check whether the channel already exist in channelTable or not
	sprintf(ptr, "SELECT channelID, title, link FROM channelTable WHERE title = '%s' AND link = '%s'", channelInfo->title, channelInfo->link);
	q.get_result(ptr);
	
	int rows = q.num_rows(); // get number of rows of result
	int channelID;
	if (rows > 0) { // exist already 		
		q.fetch_row();
		{
			channelID = q.getval();
			const char* title = q.getstr();
			const char* link = q.getstr();
			printf("channelID %d exist %s: %s\n", channelID, title, link);
		}

	}
	q.free_result();
	if (rows > 0)
		return channelID;

	// execute the insert 
	ptr = command;
	strcpy(ptr, "INSERT INTO channelTable ("); ptr += strlen(ptr);

	APPEND_NAME_IF(channelInfo, title);
	APPEND_NAME_IF(channelInfo, pubDate);
	APPEND_NAME_IF(channelInfo, link);
	APPEND_NAME_IF(channelInfo, description);
	APPEND_NAME_IF(channelInfo, author);		

	APPEND_NAME(status);
	APPEND_NAME(cando);
	*(ptr-1) = ')';

	strcpy(ptr, " VALUES("); 
	ptr += strlen(ptr);

	APPEND_VALUE_IF(channelInfo, title);
	APPEND_VALUE_IF(channelInfo, pubDate);
	APPEND_VALUE_IF(channelInfo, link);
	APPEND_VALUE_IF(channelInfo, description);
	APPEND_VALUE_IF(channelInfo, author);
	APPEND_VALUE(channelInfo, status);
	APPEND_VALUE(channelInfo, cando);
	*(ptr-1) = ')';
	*ptr = 0;
	q.execute(command);
	return q.insert_id();
}


int insertItem(Database &db, ItemInfo *itemInfo, int channelID, ChannelInfo *channelInfo)
{
	Query q(db);
	char command[4096];
	char *ptr = command;

	if (channelID < 0) {
		// insert channel first
		if (channelInfo != NULL) {
			channelID = insertChannel(db, channelInfo);
		}
	}
	itemInfo->channelID = channelID;

	strcpy(ptr, "INSERT INTO itemTable ("); ptr += strlen(ptr);

	APPEND_NAME_IF(itemInfo, title);
	APPEND_NAME_IF(itemInfo, pubDate);
	APPEND_NAME_IF(itemInfo, link);
	APPEND_NAME_IF(itemInfo, description);
	APPEND_NAME_IF(itemInfo, author);
	
	APPEND_NAME_IF(itemInfo, mediaURL);	

	APPEND_NAME(channelID);
	APPEND_NAME(length);
	APPEND_NAME(duration);
	APPEND_NAME(status);
	APPEND_NAME(cando);
	*(ptr-1) = ')';

	strcpy(ptr, " VALUES("); 
	ptr += strlen(ptr);

	APPEND_VALUE_IF(itemInfo, title);
	APPEND_VALUE_IF(itemInfo, pubDate);
	APPEND_VALUE_IF(itemInfo, link);
	APPEND_VALUE_IF(itemInfo, description);
	APPEND_VALUE_IF(itemInfo, author);

	APPEND_VALUE_IF(itemInfo, mediaURL);
	
	APPEND_VALUE(itemInfo, channelID);
	APPEND_VALUE(itemInfo, length);
	APPEND_VALUE(itemInfo, duration);
	APPEND_VALUE(itemInfo, status);
	APPEND_VALUE(itemInfo, cando);
	*(ptr-1) = ')';
	*ptr = 0;
	printf("%s\n", command);
	q.execute(command);
	//printf("insertID = %d", (long) q.insert_id());

	return q.insert_id();
}


