#ifdef _MSC_VER
#include <winsqlite/winsqlite3.h>
#else
// On UNIX systems, make sure libsqlite3 is installed
// Then compile and link the sqlite3 library with your source code
// $ c++ labsqlite.cpp -lsqlite3
#include <sqlite3.h>
#endif
#include <iostream>
#include <iomanip>
#include <string>

#pragma comment(lib, "winsqlite3.lib")

class PreparedStmt {
public:
	// Initialize the prepared statement and reset it prepare it for use
	PreparedStmt(sqlite3_stmt* stmt) : stmt(stmt) { reset(); }

	// Finalize the prepared statement upon completion
	~PreparedStmt() { if (stmt) finalize(); }

	// Overload the bool conversion operator to return whether the query can be used
	operator bool() const { return (stmt != nullptr && readyToStep); }

	// Finalize the prepared statement which makes it unusable for any future use.
	void finalize() {
		if (!stmt) {
			std::cerr << "the program is trying to finalize an already finalized prepared statement!\n";
			return;
		}
		sqlite3_finalize(stmt);
		stmt = nullptr;
	}

	// Reset the statement so that the SQL query can be executed again.
	bool reset() {
		if (!stmt) return false;
		if (stmt) readyToStep = true;
		if (firstRun) {
			firstRun = true;
			return true;
		}
		sqlite3_reset(stmt);
		return readyToStep;
	}

	// Execute the statement -- returns true if a row of information is ready to be processed
	bool step() {
		if (!readyToStep) return false;
		int rc = sqlite3_step(stmt);
		if (rc == SQLITE_ROW) return true;
		// If an error occured or step returned DONE, we need to call reset next time
		readyToStep = false;
		if (rc == SQLITE_DONE) return true;
		return false;
	}

	// Return an integer from column index. Index is 0-based.
	int columnInt(int index) {
		if (!stmt) return 0;
		return sqlite3_column_int(stmt, index);
	}

	// Return an integer from column index. Index is 0-based.
	long long columnInt64(int index) {
		if (!stmt) return 0;
		return sqlite3_column_int64(stmt, index);
	}

	// Return an double from column index. Index is 0-based.
	double columnReal(int index) {
		if (!stmt) return 0.0;
		return sqlite3_column_double(stmt, index);
	}

	// Return an string from column index. Index is 0-based.
	std::string columnText(int index) {
		std::string text;
		if (!stmt) return text;
		const char* textptr = (const char*)sqlite3_column_text(stmt, index);
		if (textptr) text = textptr;
		return text;
	}

	// Sets query element to an integer. Index is 1-based.
	void bindInt(int index, int value) {
		sqlite3_bind_int(stmt, index, value);
	}

	// Sets query element to an integer. Index is 1-based.
	void bindInt64(int index, long long value) {
		sqlite3_bind_int64(stmt, index, value);
	}

	// Sets query element to a double. Index is 1-based.
	void bindReal(int index, double value) {
		sqlite3_bind_double(stmt, index, value);
	}

	// Sets query element to a string. Index is 1-based.
	void bindText(int index, const std::string& value) {
		sqlite3_bind_text(stmt, index, value.c_str(), (int)value.size(), nullptr);
	}

private:
	bool firstRun{ true };
	bool readyToStep{ false };
	sqlite3_stmt* stmt{ nullptr };
};


class Database {
public:
	Database();
	~Database();

	// Overload the bool conversion operator to return whether the database can be used
	operator bool() { return db_ != nullptr; }

	// Open the database file. If database = ":memory:", creates a memory only database. If readonly is true, the file database is loaded as readonly.
	bool open(const std::string& database, bool readonly = false);

	// Close the database file
	void close();

	// Construct a PreparedStmt
	PreparedStmt prepare(const std::string& sql);

	// Execute a one off SQL query -- the internal callback is used to process the rows
	bool exec(const std::string& sql);

	// get last row inserted
	long long lastRowID();

private:
	// Handle an error by SQLite
	bool _handleError();

	// Callback just prints out the rows and columns for now
	static int SQLITE_CALLBACK _callback(void* pDatabase, int argc, char** argv, char** colname);

	sqlite3* db_{ nullptr };
};


Database::Database() {}


Database::~Database() { close(); }


bool Database::open(const std::string& database, bool readonly) {
	int flags = 0;
	if (database == ":memory:") {
		flags |= SQLITE_OPEN_MEMORY;
	}
	
	if (readonly) {
		flags |= SQLITE_OPEN_READONLY;
	}
	else {
		flags |= SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
	}
	sqlite3_open_v2(database.c_str(), &db_, flags, nullptr);
	return _handleError();
}


void Database::close() {
	if (!db_) return;

	int rc = sqlite3_close_v2(db_);
	if (rc) _handleError();
}


bool Database::exec(const std::string& sql) {
	if (!db_) throw std::runtime_error("Database not open");
	char* errorMessage{ nullptr };
	sqlite3_exec(db_, sql.c_str(), Database::_callback, (void*)this, &errorMessage);
	if (errorMessage) {
		std::cerr << "SQLIte3 Error: " << errorMessage << "\n";
		sqlite3_free(errorMessage);
		return false;
	}
	return true;
}


long long Database::lastRowID() {
	return sqlite3_last_insert_rowid(db_);
}


PreparedStmt Database::prepare(const std::string& sql) {
	if (!db_) throw std::runtime_error("Database not open");
	sqlite3_stmt* stmt;
	int rc = sqlite3_prepare_v3(db_, sql.c_str(), sql.size(), 0, &stmt, nullptr);
	_handleError();
	return PreparedStmt(stmt);
}


int SQLITE_CALLBACK Database::_callback(void* pDatabase, int argc, char** argv, char** colname) {
	Database* db = (Database*)pDatabase;

	for (int i = 0; i < argc; i++) {
		if (!argv[i]) std::cout << "  --  NULL  --  ";
		else std::cout << std::setw(16) << argv[i] << " ";
	}
	std::cout << "\n";
	// returning non-0 results in SQLITE_ABORT
	return 0;
}


bool Database::_handleError() {
	if (sqlite3_errcode(db_) != SQLITE_OK) {
		std::cerr << "SQLite error: " << sqlite3_errmsg(db_) << "\n";
		return false;
	}
	return true;
}


bool UpdateRecord(Database& db, long long rowID, const std::string firstName, const std::string lastName) {
	if (!rowID) return false;
	{auto stmt = db.prepare("UPDATE Customers SET FirstName=?2 WHERE ID=?1");
	stmt.bindInt64(1, rowID);
	stmt.bindText(2, firstName);
	if (!stmt.step()) return false;
	}
	{auto stmt = db.prepare("UPDATE Customers SET LastName=?2 WHERE ID=?1");
	stmt.bindInt64(1, rowID);
	stmt.bindText(2, lastName);
	if (!stmt.step()) return false;
	}
	return true;
}


long long CreateRecord(Database& db, const std::string firstName, const std::string lastName) {
	// Divide this into two steps
	// Create the row (atomic operation)
	// Update the row
	long long rowID = 0;
	if (db.exec("INSERT INTO Customers (ID) VALUES (NULL)")) {
		rowID = db.lastRowID();
		UpdateRecord(db, rowID, firstName, lastName);
	}
	return rowID;
}


// Returns 0 if record does not exist
long long FindRecord(Database& db, const std::string firstName, const std::string lastName) {
	auto stmt = db.prepare("SELECT id FROM customers WHERE firstName LIKE ? AND lastName LIKE ?");
	if (!stmt) return 0;
	stmt.bindText(1, firstName);
	stmt.bindText(2, lastName);
	if (stmt.step()) {
		return stmt.columnInt64(0);
	}
	return 0;
}


bool DeleteRecord(Database& db, long long rowID) {
	auto stmt = db.prepare("DELETE FROM Customers WHERE ID=?");
	stmt.bindInt64(1, rowID);
	return stmt.step();
}


// Returns the number of records printed
int PrintRecords(Database& db, int first, int count) {
	// LIMIT is 0 based, so
	// - first=0 and count=25 means print the first 25 records
	// - first=25 and count=25 means print the next 25 records
	auto stmt = db.prepare("SELECT * FROM customers LIMIT ?1,?2");
	if (!stmt) return 0;
	stmt.bindInt(1, first);
	stmt.bindInt(2, count);
	std::cout << "      ID       FIRST NAME        LAST NAME\n";
	std::cout << "------------------------------------------\n";
	int rowCount = 0;
	while (stmt.step()) {
		rowCount++;
		std::cout << std::setw(8) << stmt.columnText(0) << " ";
		std::cout << std::setw(16) << stmt.columnText(1) << " ";
		std::cout << std::setw(16) << stmt.columnText(2) << "\n";
	}
	std::cout << "------------------------------------------\n";
	return rowCount;
}


int main(int argc, char** argv) {
	Database db;
	if (!db.open(":memory:")) return -1;

	if (!db.exec("SELECT COUNT(*) FROM Customers")) {
		db.exec("CREATE TABLE \"Customers\" ("
				"	\"ID\"	INTEGER PRIMARY KEY AUTOINCREMENT,"
				"	\"FirstName\"	TEXT,"
				"	\"LastName\"	TEXT"
				")"
		);
		db.exec("INSERT INTO Customers (ID,FirstName,LastName) VALUES (NULL,'Jane','Austen')");
		db.exec("INSERT INTO Customers (ID,FirstName,LastName) VALUES (NULL,'Charles','Dickens')");
		db.exec("INSERT INTO Customers (ID,FirstName,LastName) VALUES (NULL,'Frank','Herbert')");
	}

	long long rowID{ 0 };
	if (rowID = FindRecord(db, "Jane", "Austen")) {
		std::cout << "Jane Austen is ID " << rowID << "\n";
		rowID = 0;
	}

	if (rowID = FindRecord(db, "George", "Elliot")) {
		std::cout << "George Elliot is ID " << rowID << "\n";
	}
	else {
		rowID = CreateRecord(db, "George", "Elliot");
		std::cout << "Created George Elliot record with ID " << rowID << "\n";
	}

	rowID = CreateRecord(db, "Mark", "Twain");

	std::cout << "\n";
	PrintRecords(db, 0, 25);

	// Change Mark Twain to Samuel Clemens
	if (rowID) {
		std::cout << "Updating Mark Twain\n";
		UpdateRecord(db, rowID, "Samuel", "Clemens");
	}

	std::cout << "\n";
	PrintRecords(db, 0, 25);

	// Delete Mark Twain
	if (rowID) DeleteRecord(db, rowID);

	return 0;
}