hi ron

#include "DatabaseAccess.h"

int tempNum = 0;	// Functions will save things such as ID numbers to communicate with other functions
std::list<Album> m_albums;
std::list<User> m_users;
std::list<Picture> m_pictures;
std::list<int> m_tags;

// --------------------- MY  FUNCTIONS ---------------------

/*
	Function will get an SQL statement as well as a callback function, and executes the statement.
	Returns whether the execution was sucessful.
*/
bool DatabaseAccess::executeSQL(const std::string sqlStatement, int(*func)(void *, int, char **, char **))
{
	char *errMessage = nullptr;
	int res = sqlite3_exec(db, sqlStatement.c_str(), func, nullptr, &errMessage);
	if (res != SQLITE_OK) {
		std::cout << errMessage << std::endl;
		return false;
	}
	return true;
}

/*
	Callback funcion that gets an ID number from its caller and saves it to a global variable.
	Note: If it gets more than one value, or no values, the function will save ERROR_NUM (-1) to the global variable.
*/
int saveToGlobal(void *data, int argc, char **argv, char **azColName)
{
	if (argc != 1)
		tempNum = ERROR_NUM;
	else tempNum = atoi(argv[0]);
	return 0;
}

/*
	Callback function that gets a table of users and prints it.
*/
int callbackUsersPrinter(void *data, int argc, char **argv, char **azColName)
{
	if (argc < 2)
		return 1;
	for (int i = 0; i < argc; i += 2)
		std::cout << "    - @" << argv[i] << " - " << argv[i + 1] << std::endl;
	return 0;
}

/*
	Callback function that gets a table of albums and prints it.
*/
int callbackAlbumsPrinter(void *data, int argc, char **argv, char **azColName)
{
	if (argc < 3)
		return 1;
	std::cout << "    - @" << argv[0] << " - " << argv[1] << std::endl;
	return 0;
}

/*
	Callback funcion that gets a user from its caller and saves it to a global (list) variable, and returns true.
	Note: If it gets no values, the function will save ERROR_NUM (-1) to the global variable and return false.
*/
int saveUser(void *data, int argc, char **argv, char **azColName)
{
	if (argc < 2)
	{
		tempNum = ERROR_NUM;
		return 1;
	}
	tempNum = 0;
	m_users.push_back(User(atoi(argv[0]), argv[1]));
	return 0;
}

/*
	Callback funcion that gets a tag from its caller and saves it to a global (list) variable, and returns true.
	Note: If it gets no values, the function will save ERROR_NUM (-1) to the global variable and return false.
*/
int saveTag(void *data, int argc, char **argv, char **azColName)
{
	if (argc < 1)
	{
		tempNum = ERROR_NUM;
		return 1;
	}
	tempNum = 0;
	m_tags.push_back(atoi(argv[0]));
	return 0;
}

/*
	Callback funcion that gets a picture from its caller and saves it to a global (list) variable, and returns true.
	Note: If it gets no values, the function will save ERROR_NUM (-1) to the global variable and return false.
*/
int savePicture(void *data, int argc, char **argv, char **azColName)
{
	if (argc < 4)
	{
		tempNum = ERROR_NUM;
		return 1;
	}
	tempNum = 0;
	Picture p = Picture(atoi(argv[0]), argv[1], argv[2], argv[3]);
	m_pictures.push_back(p);
	return 0;
}

/*
	Callback funcion that gets an album from its caller and saves it to a global (vector) variable, and returns true.
	Note: If it gets no values, the function will save ERROR_NUM (-1) to the global variable and return false.
*/
int saveAlbum(void *data, int argc, char **argv, char **azColName)
{
	if (argc < 3)
	{
		tempNum = ERROR_NUM;
		return 1;
	}
	tempNum = 0;
	Album temp = Album(atoi(argv[2]), argv[0], argv[1]);
	m_albums.push_back(temp);
	return 0;
}

/*
	Function gets an album name and returns its ID number.
*/
int DatabaseAccess::getUserIdByName(const std::string & userName)
{
	std::string sql = "select id from users where name='" + userName + "';";
	executeSQL(sql, saveToGlobal);
	if (tempNum == ERROR_NUM)
		throw "No user with name \"" + userName + "\" found.";
	return tempNum;
}

/*
	Function gets an album name and returns its ID number.
*/
int DatabaseAccess::getAlbumIdByName(const std::string & albumName)
{
	std::string sql = "select id from albums where name='" + albumName + "';";
	executeSQL(sql, saveToGlobal);
	if (tempNum == ERROR_NUM)
		throw "No album with name \"" + albumName + "\" found.";
	return tempNum;
}

/*
	Function gets a picture name and the ID of the picture's album, and returns its ID number.
*/
int DatabaseAccess::getPictureIdByName(const std::string & pictureName, int albumId)
{
	std::string sql = "select id from pictures where name='" + pictureName + "' and album_id=" + std::to_string(albumId) + ";";
	executeSQL(sql, saveToGlobal);
	if (tempNum == ERROR_NUM)
		throw "No picture with name \"" + pictureName + "\" and album ID " + std::to_string(albumId) + " found.";
	return tempNum;
}

// ------------------------ QUERIES ------------------------

/*
	Function opens a database from and existing .sqlite file with path DB_FILE_NAME.
	Returns whether database was opened successfully.
*/
bool DatabaseAccess::open()
{
	int file_exist = _access(DB_FILE_NAME, 0);
	int res = sqlite3_open(DB_FILE_NAME, &db);
	if (res != SQLITE_OK) {
		db = nullptr;
		std::cout << "Failed to open Database" << std::endl;
		return -1;
	}
	if (file_exist != 0) {
		std::cout << "Database does not exist" << std::endl;
		return false;
	}
	return true;
}

/*
	Function closes the database.
*/
void DatabaseAccess::close()
{
	sqlite3_close(db);
	db = nullptr;
}

/*
	Function clears the memory of all objects created by this class.
*/
void DatabaseAccess::clear()
{
	m_users.clear();
	m_albums.clear();
	m_pictures.clear();
	m_tags.clear();
}

/*
	Function returns the user which has been tagged the most.
*/
User DatabaseAccess::getTopTaggedUser()
{
	std::string sql = "select user_id from tags group by user_id order by count(*) desc limit 1;";
	executeSQL(sql, saveToGlobal);
	return getUser(tempNum);
}

/*
	Function returns the picture which has been tagged the most.
*/
Picture DatabaseAccess::getTopTaggedPicture()
{
	std::string sql = "select picture_id from tags group by picture_id order by count(*) desc limit 1;";
	executeSQL(sql, saveToGlobal);
	return getPicture(tempNum);
}

/*
	Function gets a user and returns a list of all pictures tagged to the user.
*/
std::list<Picture> DatabaseAccess::getTaggedPicturesOfUser(const User & user)
{
	std::string sql = "select id, name, location, creation_date from pictures inner join tags on tags.picture_id=pictures.id where user_id=" + std::to_string(user.getId()) + ';';
	m_pictures.clear();
	executeSQL(sql, savePicture);
	for (Picture& p : m_pictures)
		fillPicture(p);
	return m_pictures;
}

/*
	Function gets a picture ID and returns a list of all the user IDs tagged in the picture.
*/
std::list<int> DatabaseAccess::getTagsInPicture(int pictureId)
{
	std::string sql = "select user_id from tags where picture_id=" + std::to_string(pictureId) + ';';
	m_tags.clear();
	executeSQL(sql, saveTag);
	return m_tags;
}

// --------------------- ALBUM RELATED ---------------------

/*
	This function does nothing, and is just here to fulfil the requirements of the IDataAccess interface.
*/
void DatabaseAccess::closeAlbum(Album & pAlbum) { }

/*
	Function prints a list of all albums currently in the database.
*/
void DatabaseAccess::printAlbums()
{
	std::cout << "Album list:" << std::endl << "-----------" << std::endl;
	for (const Album& album : getAlbums())
		std::cout << std::setw(5) << "* " << album;
}

/*
	Function returns a list of all albums currently in the database.
*/
const std::list<Album> DatabaseAccess::getAlbums()
{
	std::string sql = "select name, creation_date, user_id from albums;";
	m_albums.clear();
	executeSQL(sql, saveAlbum);
	for (Album a : m_albums)
		fillAlbum(a);
	return m_albums;
}

/*
	Function gets a user and returns all albums which are owned by that user.
*/
const std::list<Album> DatabaseAccess::getAlbumsOfUser(const User & user)
{
	std::string sql = "select name, creation_date, user_id from albums where user_id='" + std::to_string(user.getId()) + "';";
	m_albums.clear();
	executeSQL(sql, saveAlbum);
	for (Album a : m_albums)
		fillAlbum(a);
	return m_albums;
	//return std::list<Album>(m_albums.begin(), m_albums.end());
}

/*
	Function gets an album and adds it to the database.
*/
void DatabaseAccess::createAlbum(const Album & album)
{
	std::string sql = "insert into albums (name, creation_date, user_id) values ('" + album.getName() + "','" + album.getCreationDate() + "'," + std::to_string(album.getOwnerId()) + ");";
	executeSQL(sql, nullptr);
}

/*
	Function deletes an album by its name and its owner's user ID.
*/
void DatabaseAccess::deleteAlbum(const std::string & albumName, int userId)
{
	for (Picture p : openAlbum(albumName).getPictures())
		removePictureFromAlbumByName(albumName, p.getName());
	std::string sql = "delete from albums where user_id=" + std::to_string(userId) + " and name='" + albumName + "';";
	executeSQL(sql, nullptr);
}

/*
	Function gets an album name and a user ID, and returns whether an album with those parameters exists in the database.
*/
bool DatabaseAccess::doesAlbumExists(const std::string & albumName, int userId)
{
	std::string sql = "select count (*) from albums where name='" + albumName + "' and user_id=" + std::to_string(userId) + ';';
	executeSQL(sql, saveToGlobal);
	return tempNum != 0;
}

/*
	Function gets an album name and returns an object representing the corresponding album.
*/
Album DatabaseAccess::openAlbum(const std::string & albumName)
{
	std::string sql = "select name, creation_date, user_id from albums where name='" + albumName + "';";
	m_albums.clear();
	executeSQL(sql, saveAlbum);
	Album a = m_albums.back();
	fillAlbum(a);
	return a;
}

/*
	Function gets a reference to an Album object and fills it with the pictures it should have according to the database.
*/
void DatabaseAccess::fillAlbum(Album& a)
{
	std::string sql = "select * from pictures where album_id=" + std::to_string(getAlbumIdByName(a.getName())) + ";";
	m_pictures.clear();
	executeSQL(sql, savePicture);
	for (Picture p : m_pictures)
	{
		fillPicture(p);
		a.addPicture(p);
	}
}

// -------------------- PICTURE RELATED --------------------

/*
	Function gets an album name and a picture, and adds said picture to the album.
*/
void DatabaseAccess::addPictureToAlbumByName(const std::string & albumName, const Picture & picture)
{
	int albumId = getAlbumIdByName(albumName);
	std::string sql = "insert into pictures (name, location, creation_date, album_id) values ('" + picture.getName() + "','" + picture.getPath() + "','" + picture.getCreationDate() + "'," + std::to_string(albumId) + ");";
	executeSQL(sql, nullptr);
}

/*
	Function gets an album name and a picture name, and deletes said pictures.
*/
void DatabaseAccess::removePictureFromAlbumByName(const std::string & albumName, const std::string & pictureName)
{
	int albumId = getAlbumIdByName(albumName);
	for (int i : getTagsInPicture(getPictureIdByName(pictureName, albumId)))
		untagUserInPicture(albumName, pictureName, i);
	std::string sql = "delete from pictures where album_id='" + std::to_string(albumId) + "' and name='" + pictureName + "';";
	executeSQL(sql, nullptr);
}

/*
	Function creates a new tag based on the album's name, the picture's name, and the user's ID.
*/
void DatabaseAccess::tagUserInPicture(const std::string & albumName, const std::string & pictureName, int userId)
{
	int albumId = getAlbumIdByName(albumName);
	int pictureId = getPictureIdByName(pictureName, albumId);
	std::string sql = "insert into tags (picture_id, user_id) values (" + std::to_string(pictureId) + ',' + std::to_string(userId) + ");";
	executeSQL(sql, nullptr);
}

/*
	Function deletes a tag based on the album's name, the picture's name, and the user's ID.
*/
void DatabaseAccess::untagUserInPicture(const std::string & albumName, const std::string & pictureName, int userId)
{
	int albumId = getAlbumIdByName(albumName);
	int pictureId = getPictureIdByName(pictureName, albumId);
	std::string sql = "delete from tags where picture_id=" + std::to_string(pictureId) + " and user_id=" + std::to_string(userId) + ";";
	executeSQL(sql, nullptr);
}

/*
	Function gets a picture ID and returns an object representing the corresponding picture.
*/
Picture DatabaseAccess::getPicture(int pictureId)
{
	std::string sql = "select * from pictures where id=" + std::to_string(pictureId) + ';';
	m_pictures.clear();
	executeSQL(sql, savePicture);
	Picture p = m_pictures.back();
	fillPicture(p);
	return p;
}

/*
	Function gets a reference to a Picture object and fills it with the tags it should have according to the database.
*/
void DatabaseAccess::fillPicture(Picture& p)
{
	for (int i : getTagsInPicture(p.getId()))
		p.tagUser(i);
}

// --------------------- USER  RELATED ---------------------

/*
	Function prints all users in the database.
*/
void DatabaseAccess::printUsers()
{
	std::string sql = "select * from users;";
	executeSQL(sql, callbackUsersPrinter);
}

/*
	Function gets a user ID and returns an object representing the corresponding user.
*/
User DatabaseAccess::getUser(int userId)
{
	std::string sql = "select * from users where id=" + std::to_string(userId) + ';';
	executeSQL(sql, saveUser);
	return m_users.back();
}

/*
	Function gets a user and adds it to the database.
	Note: Only the name is kept, the id of the user may change.
*/
void DatabaseAccess::createUser(User & user)
{
	std::string sql = "insert into users (name) values ('" + user.getName() + "');";
	executeSQL(sql, nullptr);
	user.setId(getUserIdByName(user.getName()));
}

/*
	Function gets a user and deletes it from the database.
	Note: Deletion is based on ID alone.
*/
void DatabaseAccess::deleteUser(const User & user)
{
	for (Album a : getAlbumsOfUser(user))
		deleteAlbum(a.getName(), user.getId());
	std::string sql = "delete from users where id=" + std::to_string(user.getId()) + ";";
	executeSQL(sql, nullptr);
}

/*
	Function gets a user ID and returns whether there exists a user with that ID in the database.
*/
bool DatabaseAccess::doesUserExists(int userId)
{
	std::string sql = "select count (*) from users where id=" + std::to_string(userId) + ';';
	executeSQL(sql, saveToGlobal);
	return tempNum != 0;
}

// -------------------- USER STATISTICS --------------------

/*
	Function gets a user and returns the number of albums which are owned by the user.
*/
int DatabaseAccess::countAlbumsOwnedOfUser(const User & user)
{
	std::string sql = "select count (*) from albums where user_id=" + std::to_string(user.getId()) + ';';
	executeSQL(sql, saveToGlobal);
	return tempNum;
}

/*
	Function gets a user and returns the number of albums which are tagged to the user.
*/
int DatabaseAccess::countAlbumsTaggedOfUser(const User & user)
{
	std::string sql = "select count(distinct pictures.album_id) from tags inner join pictures on tags.picture_id = pictures.id where user_id=" + std::to_string(user.getId()) + ';';;
	executeSQL(sql, saveToGlobal);
	return tempNum;
}

/*
	Function gets a user and returns the number of tags which are tagged to the user.
*/
int DatabaseAccess::countTagsOfUser(const User & user)
{
	std::string sql = "select count (*) from tags where user_id=" + std::to_string(user.getId()) + ';';
	executeSQL(sql, saveToGlobal);
	return tempNum;
}

/*
	Function gets a user, and returns the average number of tags that user has per album.
*/
float DatabaseAccess::averageTagsPerAlbumOfUser(const User & user)
{
	std::string sql = "select count(*) from tags where user_id=" + std::to_string(user.getId()) + ';';
	executeSQL(sql, saveToGlobal);
	float numTags = tempNum;
	sql = "select count(*) from albums";
	executeSQL(sql, saveToGlobal);
	float numAlbums = tempNum;
	return numTags / numAlbums;
}