/*  
    Compile the program with the following command:
       gcc $(mysql_config --cflags) db.c $(mysql_config --libs)
    When running:
       ./a.out
*/

#include <mysql.h>
#include <stdio.h>

/* Globals since its C */

/* The mysql connection */
MYSQL *conn;

/* The mysql results set */
MYSQL_RES *res;

/* The row we are processing from the results set */
MYSQL_ROW row;

/* The database server */
char *server = "nellans.org";

/* The user we connect as */
char *user = "utah";

/* The password for this user */
char *password = "arch";

/* The database we connect to within the server */
char *database = "eoddata_v1";

/* Connect to the server and database within the server */
void connectToDatabase() {

	/* Initialize our connection without connecting to any server */
	conn = mysql_init(NULL);

	/* Connect to database */
	if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
		fprintf(stderr, "%s\n", mysql_error(conn));
	}

	printf("Connection Initialized\n\n");
}

/* Returns the tables available in this database */
void showTables() {

	/* send SQL query */
	if (mysql_query(conn, "show tables")) {
		fprintf(stderr, "%s\n", mysql_error(conn));

	}

	/* Gets the results set */
	res = mysql_use_result(conn);

	printf("Tables in this database...\n");

	/* Keep selecting rows from the results set until we've run out */
	while ((row = mysql_fetch_row(res)) != NULL) {
		printf("%s \n", row[0]);
	}

	printf("\n");

}

/* Returns all unique stock tickers listed on the NYSE and NASDAQ that are in the database*/
void getTickers() {

	/* send SQL query */
	if (mysql_query(
			conn,
			"select ticker, exchange from HistoricalEquity where snapshotDate = '2008-01-02' and (exchange='NYSE' or exchange='NASDAQ') group by (ticker)")) {
		fprintf(stderr, "%s\n", mysql_error(conn));

	}

	/* Gets the results set */
	res = mysql_use_result(conn);

	printf("Ticker, Exchange\n");

	/* Keep selecting rows from the results set until we've run out */
	while ((row = mysql_fetch_row(res)) != NULL) {
		printf("%s, %s\n", row[0], row[1]);
	}

	printf("\n");
}

/* Returns the data for a single stock */
void getSingleStockData() {

	/* send SQL query */
	if (mysql_query(
			conn,
			"select ticker, exchange, date, close, volume from HistoricalEquity where snapshotDate = '2008-01-02' and ticker='AAPL' order by date desc")) {
		fprintf(stderr, "%s\n", mysql_error(conn));

	}

	/* Gets the results set */
	res = mysql_use_result(conn);

	printf("Ticker, Exchange, Date, Close, Volume\n");

	/* Keep selecting rows from the results set until we've run out */
	while ((row = mysql_fetch_row(res)) != NULL) {
		printf("%s, %s, %s, %s, %s\n", row[0], row[1], row[2], row[3], row[4]);
	}
}

/* Close the connection when we're done */
void closeConnection() {

	/* close connection */
	mysql_free_result(res);
	mysql_close(conn);
}

/* main */
int main() {

	// initialize our database connection
	connectToDatabase();

	// show the tables available in the database
	showTables();

	// returns a set of tickers
	getTickers();

	// gets data for a single ticker
	getSingleStockData();

	// close the connection to the database when complete
	closeConnection();
}