userdao_newest

#ifndef USERDAO_H
#define USERDAO_H

#include <iostream>
#include <sqlite3.h>
#include "classes/User.h"

class UserDAO {
private:
    // Static member variable for the shared database connection
    static sqlite3 *db;

public:
    // Constructor and destructor to open and close the database connection
    UserDAO() {
        if (db == nullptr) {
            int rc = sqlite3_open("D:\\dsa_PROJ\\resources\\database.db", &db);
            if (rc != SQLITE_OK) {
                std::cout << "Error opening the database." << std::endl;
                sqlite3_close(db);
                db = nullptr; // Set db to nullptr to avoid using an invalid connection
            }
        }
    }

    ~UserDAO() {
        if (db != nullptr) {
            sqlite3_close(db);
            db = nullptr;
        }
    }

    void insert(std::string username, std::string userpassword) {
    try {
    if (db == nullptr) {
        std::cout << "Error: Database connection is not open." << std::endl;
        return;
    }

    std::string count_query = "SELECT COUNT(*) FROM USERS";
    sqlite3_stmt* count_statement;

    int rc = sqlite3_prepare_v2(db, count_query.c_str(), -1, &count_statement, nullptr);
    if (rc != SQLITE_OK) {
        std::cout << "Error preparing count statement." << std::endl;
        return;
    }

    rc = sqlite3_step(count_statement);
    int count;

    if (rc == SQLITE_ROW) {
        count = sqlite3_column_int(count_statement, 0);
    } else if (rc != SQLITE_DONE) {
        std::cout << "Error executing the count query." << std::endl;
    }

    sqlite3_finalize(count_statement);

    std::string query_insert;

    if (count == 0) {
        query_insert = "INSERT INTO USERS (UserId, UserName, Password) VALUES (0, ?, ?)";
    } else {
        query_insert = "INSERT INTO USERS (UserId, UserName, Password) VALUES ((SELECT COALESCE(MAX(UserId), 0) + 1 FROM USERS), ?, ?)";
    }

    sqlite3_stmt* statement_insert;
    rc = sqlite3_prepare_v2(db, query_insert.c_str(), -1, &statement_insert, nullptr);
    if (rc != SQLITE_OK) {
        std::cout << "Error preparing insert statement." << std::endl;
        return;
    }

    // Bind parameters
    rc = sqlite3_bind_text(statement_insert, 1, username.c_str(), -1, SQLITE_STATIC);
    rc = sqlite3_bind_text(statement_insert, 2, userpassword.c_str(), -1, SQLITE_STATIC);

    rc = sqlite3_step(statement_insert);

    if (rc != SQLITE_DONE) {
        std::cout << "Error executing insert statement: " << sqlite3_errmsg(db) << std::endl;
    } else {
        std::cout << "User inserted successfully." << std::endl;
    }

    sqlite3_finalize(statement_insert);
} catch (const std::exception& e) {
    std::cout << "Exception: " << e.what() << std::endl;
}

}

    bool is_unique(std::string username) {
       try {
        if (db == nullptr) {
            std::cout << "Error: Database connection is not open." << std::endl;
            return false;
        }

        std::string query = "SELECT COUNT(*) FROM USERS WHERE UserName = ?";
        sqlite3_stmt* stmt_check;

        int rc = sqlite3_prepare_v2(db, query.c_str(), -1, &stmt_check, nullptr);
        if (rc != SQLITE_OK) {
            std::cout << "Error preparing check statement for unique." << std::endl;
            return false;
        }

        // Bind parameter
        rc = sqlite3_bind_text(stmt_check, 1, username.c_str(), -1, SQLITE_STATIC);

        rc = sqlite3_step(stmt_check);
        if (rc == SQLITE_ROW) {
            int count = sqlite3_column_int(stmt_check, 0);

            if (count == 0) {
                std::cout << "Username is unique." << std::endl;
                return true;
            } else {
                std::cout << "Username is not unique." << std::endl;
                return false;
            }
        } else if (rc != SQLITE_DONE) {
            std::cout << "Error executing check query for unique." << std::endl;
            return false;
        }

        sqlite3_finalize(stmt_check);
        return false;
    } catch (const std::exception& e) {
        std::cout << "Exception: " << e.what() << std::endl;
        return false;
        }
    }

vector<User> selectAllFromTable(void) {
    vector<User> users;
    try {
        if (db == nullptr) {
            cout << "Error: Database is not opened." << endl;
            return users;  // Return an empty vector since there's an Error
        }

        string query = "SELECT * FROM USERS";
        sqlite3_stmt* statement;

        if (sqlite3_prepare_v2(db, query.c_str(), -1, &statement, nullptr) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int retrieveduserID = sqlite3_column_int(statement, 0);
                const char* usernameCStr = reinterpret_cast<const char*>(sqlite3_column_text(statement, 1));
                std::string retrievedUsername(usernameCStr != nullptr ? usernameCStr : "");
                const char* passwordCStr = reinterpret_cast<const char*>(sqlite3_column_text(statement, 2));
                std::string retrievedPassword(passwordCStr != nullptr ? passwordCStr : "");

                users.push_back(User(retrieveduserID, retrievedUsername, retrievedPassword));
            }

            sqlite3_finalize(statement);
        } else {
            cout << "Error: could not prepare select query." << endl;
            return users;  // Return an empty vector since there's an error
        }
    } catch (const std::exception& e) {
        std::cout << "Exception: " << e.what() << std::endl;
    }

    return users;
}

};
    sqlite3* UserDAO::db = nullptr;



#endif