#include <stdio.h>
#include <sqlite3.h>


sqlite3 *db;

static int 
callback (void *data, int argc, char **argv, char **azColName)
{
  	int i;
  	for(i=0; i<argc; i++) {
    		printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  	}
  	printf("--DONE \n");
  	return 0;
}

static int
select_stmt (const char* stmt) {
  	char *errmsg;
  	int   ret;
  	int   nrecs = 0;

  	ret = sqlite3_exec(db, stmt, callback, &nrecs, &errmsg);

  	if(ret!=SQLITE_OK) {
    		printf("Error in select statement %s [%s].\n", stmt, errmsg);
  	} else {
    		printf("\n   %d records returned.\n", nrecs);
  	}

	return ret;
}

static int
sql_stmt(const char* stmt) {
  	char *errmsg;
  	int   ret;

  	ret = sqlite3_exec(db, stmt, 0, 0, &errmsg);

  	if(ret != SQLITE_OK) {
    		printf("Error in statement: %s [%s].\n", stmt, errmsg);
		exit (1);
 	}

	return ret;
}

#define CREATE_STMT "CREATE TABLE IF NOT EXISTS %s (uid TEXT, gflags INTEGER, isize INTEGER, dsent INTEGER, dreceived INTEGER, jsubject TEXT, ffrom TEXT, tto TEXT, cc TEXT, mlist TEXT, part TEXT, userflags TEXT, usertags TEXT, bdata TEXT)"

static int
create_table (const char *tablename)
{
	char *cmd = malloc (sizeof(CREATE_STMT)+20);
	sprintf(cmd, CREATE_STMT, tablename);
	sql_stmt (cmd);
}

int sort_uid (void *foo, int len, void * data1, int len2, void *data2)
{
	//printf("%s \n%s\n\n", data1, data2);
	int a1 = atoi (data1);
	int a2 = atoi (data2);
	return a1 < a2;
}

int int_uid (void *foo, int len, void * data1, int len2, void *data2)
{
	printf("%d \n%d\n\n", data1, data2);
	int a1 = data1;
	int a2 = data2;
	return a1 < a2;
}


int sort_cmp (void *foo, int len, void * data1, int len2, void *data2)
{
	printf("%s \n%s\n\n", data1, data2);
	int a1 = atoi (data1);
	int a2 = atoi (data2);
	return a1 == a2 ? 0 : a1 < a2 ? -1 : 1;
}

int main(int argc, char **argv) {
	char *zErrMsg = 0;
	int rc;

//  	rc = sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE , NULL);
	rc = sqlite3_open("test.db", &db);

  	if( rc ) {
    		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    		sqlite3_close(db);
    		exit(1);
  	}

	sqlite3_create_collation(db, "uidcmp", SQLITE_UTF8,  NULL, sort_cmp);
	sqlite3_create_collation(db, "uidsort", SQLITE_UTF8,  NULL, sort_uid);
	sqlite3_create_collation(db, "intsort", SQLITE_UTF8,  NULL, int_uid);

	create_table ("table1");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('5120', 100, 123, '5120nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('6103', 100, 13, '6103nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('3194', 100, 12, '3194nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('8130', 100, 1231, '8130nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('9102', 100, 5123, '9102nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('3112', 100, 1223, '3112nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('1102', 100, 3123, '1102nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('214', 100, 7123, '214nice subject', 'mlistbinary')");
	sql_stmt ("INSERT INTO table1 (uid, gflags, isize, jsubject, mlist) VALUES ('3161', 0, 199123, '3161nice subject', '123mlistbinary')");
	select_stmt ("select uid from table1 order by isize collate uidsort");

	//select_stmt ("select uid from table1 where uid collate uidsort order by uid collate uidsort");
	//select_stmt ("select * from table1 where uid == 5120 collate uidcmp");

	printf("------\n");
	//select_stmt ("select count(isize) from table1");
  	sqlite3_close(db);

  	return 0;
}