ChocAnDB.cpp

#include "ChocAn.h"

//member constructor for member struct
Member::Member() : mem_id(0), mem_name(""), address(""), city(""), state(""), zip(""), status("")
{
}

//parameterized member constructor for easy use of making a new member object
Member::Member(int id, const string & name, const string & address, const string & city, const string & state, const string & zip, const string & status)
	: mem_id(id), mem_name(name), address(address), city(city), state(state), zip(zip), status(status)
{
}

//provider constructor for provider struct
Provider::Provider() : prov_id(0), prov_name(""), address(""), city(""), state(""), zip("")
{
}

//parameterized provider constructor for easy use of making new provider objects
Provider::Provider(int id, const string & name, const string & address, const string & city, const string & state, const string & zip)
	: prov_id(id), prov_name(name), address(address), city(city), state(state), zip(zip)
{
}

//service constructor for service struct
Service::Service() : code(0), name(""), fee(0.0)
{
}

//parameterized service constructor for easy use of making new service objects
Service::Service(int code, const string & name, double fee) : code(code), name(name), fee(fee)
{
}

//Database constructor
ChocAnDB::ChocAnDB(): db(nullptr), path("ChocAn.db")
{
}

//Database destructor
ChocAnDB::~ChocAnDB()
{
	close();
}

//helper function to make executing sql statements easier
bool ChocAnDB::exec_sql(const string & sql)
{
	char * err_msg = nullptr;
	int err = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &err_msg);

	//if the return from the built in exec function isn't the OK macro prints the error and returns
	if(err != SQLITE_OK)
	{
		cerr << "SQL error: " << err_msg << endl;
		sqlite3_free(err_msg);
		return false;
	}
	return true;
}

//table initializer for building the tables in the database
bool ChocAnDB::initialize()
{
	int err = sqlite3_open(path.c_str(), &db);
	if(err != SQLITE_OK)
	{//if the database fails to open print the error and return
		cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
		return false;
	}

	//strings with the sql statements to make the required database schema
	string create_members = R"(
		CREATE TABLE IF NOT EXISTS Members (
			Member_ID INTEGER PRIMARY KEY,
			Name TEXT NOT NULL,
			Street_Address TEXT,
			City TEXT,
			State TEXT,
			Zip_Code TEXT,
			Status TEXT DEFAULT 'active'
		);
	)";

	string create_providers = R"(
		CREATE TABLE IF NOT EXISTS Providers (
			Provider_ID INTEGER PRIMARY KEY,
			Name TEXT NOT NULL,
			Street_Address TEXT,
			City TEXT,
			State TEXT,
			Zip_Code TEXT
		);
	)";

	string create_services = R"(
		CREATE TABLE IF NOT EXISTS Services (
			Service_Code INTEGER PRIMARY KEY,
			Service_Name TEXT NOT NULL,
			Fee REAL NOT NULL
		);
	)";

	string create_records = R"(
		CREATE TABLE IF NOT EXISTS ServiceRecords (
			Record_ID INTEGER PRIMARY KEY AUTOINCREMENT,
			Current_Date TEXT NOT NULL,
			Service_Date TEXT NOT NULL,
			Provider_ID INTEGER NOT NULL,
			Member_ID INTEGER NOT NULL,
			Service_Code INTEGER NOT NULL,
			Comments TEXT,
			Fee REAL NOT NULL,
			FOREIGN KEY (Provider_ID) REFERENCES Providers(Provider_ID),
			FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
			FOREIGN KEY (Service_Code) REFERENCES Services(Service_Code)
		);
	)";

	//return runs all the sql statements creating the tables
	return exec_sql(create_members) && exec_sql(create_providers) && exec_sql(create_services) && exec_sql(create_records);
}

bool ChocAnDB::close()
{//closes the database and sets the pointer to null
	if(db)
	{
		sqlite3_close(db);
		db = nullptr;
		return true;
	}
	return false;
}

//function to add a member to the database
bool ChocAnDB::addMember(const Member & member)
{
	sqlite3_stmt * stmt;
	//sql statement to add the member
	string sql = "INSERT INTO Members (Member_ID, Name, Street_Address, City, State, Zip_Code, Status) VALUES (?, ?, ?, ?, ?, ?, ?);";
	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if(sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << endl;
		return false;
	}
	
	//bind to replace the ? in the sql statement with the passed in member data
	sqlite3_bind_int(stmt, 1, member.mem_id);
	sqlite3_bind_text(stmt, 2, member.mem_name.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 3, member.address.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 4, member.city.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 5, member.state.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 6, member.zip.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 7, member.status.c_str(), -1, SQLITE_TRANSIENT);

	//updates sql and checks the result and frees the statment
	int result = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	return result == SQLITE_DONE;
}

//function to update a member
bool ChocAnDB::updateMember(const Member & member)
{
	sqlite3_stmt * stmt;
	//sql command updates one member
	string sql = "UPDATE Members SET Name=?, Street_Address=?, City=?, State=?, Zip_Code=?, Status=? WHERE Member_ID=?;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if(sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{ 
		cerr << "Failed to prepare statement (updateMember): " << sqlite3_errmsg(db) << endl;
		return false;
	}

	//each bind replaces one ? in the sql script
	sqlite3_bind_text(stmt, 1, member.mem_name.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 2, member.address.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 3, member.city.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 4, member.state.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 5, member.zip.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 6, member.status.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(stmt, 7, member.mem_id);

	//updates sql and checks the result and frees the statment
	int result = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	return result == SQLITE_DONE;
}

//function to delete a member
bool ChocAnDB::deleteMember(int mem_id)
{
	sqlite3_stmt * stmt;
	//delete one member
	string sql = "DELETE FROM Members WHERE Member_ID=?;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement (deleteMember): " << sqlite3_errmsg(db) << endl;
		return false;
	}

	//replaces one ? in the sql script
	sqlite3_bind_int(stmt, 1, mem_id);

	//if the member existed it is now deleted
	//return true only when successful
	int result = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	return result == SQLITE_DONE;
}

//function to get a member
Member * ChocAnDB::getMember(int mem_id)
{
	sqlite3_stmt * stmt;
	//selects all member fields for the member id
	string sql = "SELECT * FROM Members WHERE Member_ID=?;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement (getMember): " << sqlite3_errmsg(db) << endl;
		return nullptr;
	}

	//look up specific member
	sqlite3_bind_int(stmt, 1, mem_id);

	//when a result was returned fill the member and return
	if (sqlite3_step(stmt) == SQLITE_ROW)
	{
		Member * member = new Member(
				sqlite3_column_int(stmt, 0),
				(const char*) sqlite3_column_text(stmt, 1),
				(const char*) sqlite3_column_text(stmt, 2),
				(const char*) sqlite3_column_text(stmt, 3),
				(const char*) sqlite3_column_text(stmt, 4),
				(const char*) sqlite3_column_text(stmt, 5),
				(const char*) sqlite3_column_text(stmt, 6));

		sqlite3_finalize(stmt);
		return member;
	}

	//free statement and return null if not found
	sqlite3_finalize(stmt);
	return nullptr;
}

//function to get all members
vector<Member> ChocAnDB::getAllMembers()
{
	vector<Member> members;
  sqlite3_stmt* stmt;
	//gets all rows from the table
  string sql = "SELECT * FROM Members;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement (getAllMembers): " << sqlite3_errmsg(db) << endl;
		return members;
	}

	//loops throught the rows pushing each member into the vector
	while(sqlite3_step(stmt) == SQLITE_ROW)
	{
		members.push_back(Member(
				sqlite3_column_int(stmt, 0),
				(const char*) sqlite3_column_text(stmt, 1),
				(const char*) sqlite3_column_text(stmt, 2),
				(const char*) sqlite3_column_text(stmt, 3),
				(const char*) sqlite3_column_text(stmt, 4),
				(const char*) sqlite3_column_text(stmt, 5),
				(const char*) sqlite3_column_text(stmt, 6)));
	}

	//frees the statement and returns the vector
	sqlite3_finalize(stmt);
  return members;
}

//function to add a provider
bool ChocAnDB::addProvider(const Provider & provider)
{
	sqlite3_stmt * stmt;
	//sql statement to add the provider
	string sql = "INSERT INTO Providers (Provider_ID, Name, Street_Address, City, State, Zip_Code) VALUES (?, ?, ?, ?, ?, ?);";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if(sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement (addProvider): " << sqlite3_errmsg(db) << endl;
		return false;
	}

	//each bind replaces one ? in the sql script
	sqlite3_bind_int(stmt, 1, provider.prov_id);
	sqlite3_bind_text(stmt, 2, provider.prov_name.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 3, provider.address.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 4, provider.city.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 5, provider.state.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 6, provider.zip.c_str(), -1, SQLITE_TRANSIENT);

	//updates sql and checks the result and frees the statment
	int result = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	return result == SQLITE_DONE;
}

bool ChocAnDB::updateProvider(const Provider & provider)
{
	sqlite3_stmt * stmt;
	//sql command updates one provider
	string sql = "UPDATE Providers SET Name = ?, Street_Address = ?, City = ?, State = ?, Zip_Code = ? WHERE Provider_ID = ?;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
        cerr << "Failed to prepare statement (updateProvider): " << sqlite3_errmsg(db) << endl;
        return false;
    }

	//each bind replaces one ? is the sql script
	sqlite3_bind_text(stmt, 1, provider.prov_name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, provider.address.c_str(),   -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, provider.city.c_str(),      -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, provider.state.c_str(),     -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, provider.zip.c_str(),       -1, SQLITE_TRANSIENT);
    sqlite3_bind_int (stmt, 6, provider.prov_id);

	//updates sql and checks the result and frees the statment
	int result = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return (result == SQLITE_DONE);
}

bool ChocAnDB::deleteProvider(int prov_id)
{
	sqlite3_stmt *stmt;
	//deletes one provider
    string sql = "DELETE FROM Providers WHERE Provider_ID = ?;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
    {
        cerr << "Failed to prepare statement (deleteProvider): " << sqlite3_errmsg(db) << endl;
        return false;
    }

	//replaces ? with the actual provider id
    sqlite3_bind_int(stmt, 1, prov_id);

	//if the provider existed it is now deleted
	//return true only when successful
    int result = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return (result == SQLITE_DONE);
}

Provider * ChocAnDB::getProvider(int prov_id)
{
	sqlite3_stmt *stmt;
	//selects all relevant provider fields
    string sql = "SELECT Provider_ID, Name, Street_Address, City, State, Zip_Code FROM Providers WHERE Provider_ID = ?;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
    {
        cerr << "Failed to prepare statement (getProvider): " << sqlite3_errmsg(db) << endl;
        return nullptr;
    }

	//look up the specific provider
    sqlite3_bind_int(stmt, 1, prov_id);

    int result = sqlite3_step(stmt);
    Provider *provider = nullptr;

	//SQLITE_ROW means a result was returned
    if (result == SQLITE_ROW)
    {
		//filled a new provider object
        provider = new Provider();
        provider->prov_id   = sqlite3_column_int(stmt, 0);

		//get text from the row and SQKite returns unsigned 
		//char * so we cast to string
        const unsigned char *name  = sqlite3_column_text(stmt, 1);
        const unsigned char *addr  = sqlite3_column_text(stmt, 2);
        const unsigned char *city  = sqlite3_column_text(stmt, 3);
        const unsigned char *state = sqlite3_column_text(stmt, 4);
        const unsigned char *zip   = sqlite3_column_text(stmt, 5);

        provider->prov_name = name  ? reinterpret_cast<const char*>(name)  : "";
        provider->address   = addr  ? reinterpret_cast<const char*>(addr)  : "";
        provider->city      = city  ? reinterpret_cast<const char*>(city)  : "";
        provider->state     = state ? reinterpret_cast<const char*>(state) : "";
        provider->zip       = zip   ? reinterpret_cast<const char*>(zip)   : "";
    }

	//free and return the provider or nullptr
    sqlite3_finalize(stmt);
    return provider;
}

vector<Provider> ChocAnDB::getAllProviders()
{
	vector<Provider> providers;
    sqlite3_stmt *stmt;

	//fetches all rows
    string sql = "SELECT Provider_ID, Name, Street_Address, City, State, Zip_Code FROM Providers;";

	//converts the sql string into a compiled statement that SQLite can execute
	//if preparation fails the function returns false
    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
    {
        cerr << "Failed to prepare statement (getAllProviders): " << sqlite3_errmsg(db) << endl;
        return providers;
    }

	//loop through all rows
    while (true)
    {
        int result = sqlite3_step(stmt);
		//SQLITE_ROW mean theres another result now
        if (result == SQLITE_ROW)
        {
			//build a Provider p for each one and append it to the vector
            Provider p;
            p.prov_id = sqlite3_column_int(stmt, 0);

            const unsigned char *name  = sqlite3_column_text(stmt, 1);
            const unsigned char *addr  = sqlite3_column_text(stmt, 2);
            const unsigned char *city  = sqlite3_column_text(stmt, 3);
            const unsigned char *state = sqlite3_column_text(stmt, 4);
            const unsigned char *zip   = sqlite3_column_text(stmt, 5);

            p.prov_name = name  ? reinterpret_cast<const char*>(name)  : "";
            p.address   = addr  ? reinterpret_cast<const char*>(addr)  : "";
            p.city      = city  ? reinterpret_cast<const char*>(city)  : "";
            p.state     = state ? reinterpret_cast<const char*>(state) : "";
            p.zip       = zip   ? reinterpret_cast<const char*>(zip)   : "";

            providers.push_back(p);
        }
        else if (result == SQLITE_DONE)
		//SQLITE_DONE means reached the end
        {
            break;
        }
        else
        {
            cerr << "Error stepping through getAllProviders: " << sqlite3_errmsg(db) << endl;
            break;
        }
    }

	//all providers returned in a vector
    sqlite3_finalize(stmt);
    return providers;
}

bool ChocAnDB::addService(const Service & service)
{
	sqlite3_stmt * stmt;
	string sql = "INSERT INTO Services (Service_Code, Service_Name, Fee) VALUES (?, ?, ?);";
	
	if(sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << endl;
		return false;
	}
	
	sqlite3_bind_int(stmt, 1, service.code);
	sqlite3_bind_text(stmt, 2, service.name.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_double(stmt, 3, service.fee);
	
	int rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	
	return rc == SQLITE_DONE;
}

bool ChocAnDB::updateService(const Service & service)
{
	sqlite3_stmt * stmt;
	string sql = "UPDATE Services SET Service_Name=?, Fee=? WHERE Service_Code=?;";
	
	if(sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << endl;
		return false;
	}
	
	sqlite3_bind_text(stmt, 1, service.name.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_double(stmt, 2, service.fee);
	sqlite3_bind_int(stmt, 3, service.code);
	
	int rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);
	
	return rc == SQLITE_DONE;
}

bool ChocAnDB::deleteService(int service_code)
{
	sqlite3_stmt * stmt;
	string sql = "DELETE FROM Services WHERE Service_Code=?;";

	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
		return false;

	sqlite3_bind_int(stmt, 1, service_code);
	int rc = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	return rc == SQLITE_DONE;
}

Service * ChocAnDB::getService(int service_code)
{
	sqlite3_stmt * stmt;
	string sql = "SELECT * FROM Services WHERE Service_Code=?;";

	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
		return nullptr;

	sqlite3_bind_int(stmt, 1, service_code);

	if (sqlite3_step(stmt) == SQLITE_ROW)
	{
		Service * service = new Service(
				sqlite3_column_int(stmt, 0),
				(const char*) sqlite3_column_text(stmt, 1),
				sqlite3_column_double(stmt, 2));

		sqlite3_finalize(stmt);
		return service;
	}

	sqlite3_finalize(stmt);
	return nullptr;
}

vector<Service> ChocAnDB::getAllServices()
{
	vector<Service> services;
    sqlite3_stmt* stmt;
    string sql = "SELECT * FROM Services;";

	if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
		return services;

	while(sqlite3_step(stmt) == SQLITE_ROW)
	{
		services.push_back(Service(
				sqlite3_column_int(stmt, 0),
				(const char*) sqlite3_column_text(stmt, 1),
				sqlite3_column_double(stmt, 2)));
	}

	sqlite3_finalize(stmt);
    return services;
}

bool ChocAnDB::addServiceRecord(ServiceRecord & record)
{
	sqlite3_stmt * stmt;
	string sql = "INSERT INTO ServiceRecords (Current_Date, Service_Date, Provider_ID, Member_ID, Service_Code, Comments, Fee) VALUES (?, ?, ?, ?, ?, ?, ?);";

	if(sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK)
	{
		cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << endl;
		return false;
	}

	sqlite3_bind_text(stmt, 1, record.current_date.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(stmt, 2, record.service_date.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_int(stmt, 3, record.prov_id);
	sqlite3_bind_int(stmt, 4, record.mem_id);
	sqlite3_bind_int(stmt, 5, record.service_code);
	sqlite3_bind_text(stmt, 6, record.comments.c_str(), -1, SQLITE_TRANSIENT);
	sqlite3_bind_double(stmt, 7, record.fee);

	int rc = sqlite3_step(stmt);

	if (rc == SQLITE_DONE)
		record.rec_id = sqlite3_last_insert_rowid(db);

	sqlite3_finalize(stmt);
	return rc == SQLITE_DONE;
}

vector<ServiceRecord> ChocAnDB::getServiceRecordsByMember(int mem_id, const string & startDate, const string & endDate)
{
	vector<ServiceRecord> records;
    sqlite3_stmt *stmt = nullptr;

    // Base query: all records for a member
    string sql =
        "SELECT Record_ID, Current_Date, Service_Date, Provider_ID, "
        "Member_ID, Service_Code, Comments, Fee "
        "FROM ServiceRecords WHERE Member_ID = ?";

    // If both dates are non-empty, filter by date range
    bool useDateRange = !startDate.empty() && !endDate.empty();
    if (useDateRange) {
        sql += " AND Service_Date BETWEEN ? AND ?";
    }

    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        cerr << "Failed to prepare statement (getServiceRecordsByMember): "
             << sqlite3_errmsg(db) << endl;
        return records;
    }

    int bindIndex = 1;
    sqlite3_bind_int(stmt, bindIndex++, mem_id);

    if (useDateRange) {
        sqlite3_bind_text(stmt, bindIndex++, startDate.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, bindIndex++, endDate.c_str(),   -1, SQLITE_TRANSIENT);
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        ServiceRecord rec;
        rec.rec_id       = sqlite3_column_int(stmt, 0);

        const unsigned char *current = sqlite3_column_text(stmt, 1);
        const unsigned char *service = sqlite3_column_text(stmt, 2);
        rec.current_date  = current ? reinterpret_cast<const char*>(current) : "";
        rec.service_date  = service ? reinterpret_cast<const char*>(service) : "";

        rec.prov_id       = sqlite3_column_int(stmt, 3);
        rec.mem_id        = sqlite3_column_int(stmt, 4);
        rec.service_code  = sqlite3_column_int(stmt, 5);

        const unsigned char *comm   = sqlite3_column_text(stmt, 6);
        rec.comments      = comm ? reinterpret_cast<const char*>(comm) : "";

        rec.fee           = sqlite3_column_double(stmt, 7);

        records.push_back(rec);
    }

    sqlite3_finalize(stmt);
    return records;

}

vector<ServiceRecord> ChocAnDB::getServiceRecordsByProvider(int prov_id, const string & startDate, const string & endDate)
{
	vector<ServiceRecord> records;
    sqlite3_stmt *stmt = nullptr;

    string sql =
        "SELECT Record_ID, Current_Date, Service_Date, Provider_ID, "
        "Member_ID, Service_Code, Comments, Fee "
        "FROM ServiceRecords WHERE Provider_ID = ?";

    bool useDateRange = !startDate.empty() && !endDate.empty();
    if (useDateRange) {
        sql += " AND Service_Date BETWEEN ? AND ?";
    }

    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        cerr << "Failed to prepare statement (getServiceRecordsByProvider): "
             << sqlite3_errmsg(db) << endl;
        return records;
    }

    int bindIndex = 1;
    sqlite3_bind_int(stmt, bindIndex++, prov_id);

    if (useDateRange) {
        sqlite3_bind_text(stmt, bindIndex++, startDate.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, bindIndex++, endDate.c_str(),   -1, SQLITE_TRANSIENT);
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        ServiceRecord rec;
        rec.rec_id       = sqlite3_column_int(stmt, 0);

        const unsigned char *current = sqlite3_column_text(stmt, 1);
        const unsigned char *service = sqlite3_column_text(stmt, 2);
        rec.current_date  = current ? reinterpret_cast<const char*>(current) : "";
        rec.service_date  = service ? reinterpret_cast<const char*>(service) : "";

        rec.prov_id       = sqlite3_column_int(stmt, 3);
        rec.mem_id        = sqlite3_column_int(stmt, 4);
        rec.service_code  = sqlite3_column_int(stmt, 5);

        const unsigned char *comm   = sqlite3_column_text(stmt, 6);
        rec.comments      = comm ? reinterpret_cast<const char*>(comm) : "";

        rec.fee           = sqlite3_column_double(stmt, 7);

        records.push_back(rec);
    }

    sqlite3_finalize(stmt);
    return records;
}

vector<ServiceRecord> ChocAnDB::getAllServiceRecords(const string & startDate, const string & endDate)
{
	 vector<ServiceRecord> records;
    sqlite3_stmt *stmt = nullptr;

    string sql =
        "SELECT Record_ID, Current_Date, Service_Date, Provider_ID, "
        "Member_ID, Service_Code, Comments, Fee "
        "FROM ServiceRecords";

    bool useDateRange = !startDate.empty() && !endDate.empty();
    if (useDateRange) {
        sql += " WHERE Service_Date BETWEEN ? AND ?";
    }

    if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        cerr << "Failed to prepare statement (getAllServiceRecords): "
             << sqlite3_errmsg(db) << endl;
        return records;
    }

    int bindIndex = 1;
    if (useDateRange) {
        sqlite3_bind_text(stmt, bindIndex++, startDate.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, bindIndex++, endDate.c_str(),   -1, SQLITE_TRANSIENT);
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
        ServiceRecord rec;
        rec.rec_id       = sqlite3_column_int(stmt, 0);

        const unsigned char *current = sqlite3_column_text(stmt, 1);
        const unsigned char *service = sqlite3_column_text(stmt, 2);
        rec.current_date  = current ? reinterpret_cast<const char*>(current) : "";
        rec.service_date  = service ? reinterpret_cast<const char*>(service) : "";

        rec.prov_id       = sqlite3_column_int(stmt, 3);
        rec.mem_id        = sqlite3_column_int(stmt, 4);
        rec.service_code  = sqlite3_column_int(stmt, 5);

        const unsigned char *comm   = sqlite3_column_text(stmt, 6);
        rec.comments      = comm ? reinterpret_cast<const char*>(comm) : "";

        rec.fee           = sqlite3_column_double(stmt, 7);

        records.push_back(rec);
    }

    sqlite3_finalize(stmt);
    return records;
}