d:\dsa_PROJ\include\database\userDao.h

#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) {
        if (db == nullptr) {
            std::cout << "Error: Database connection is not open." << std::endl;
            return;
        }

        std::string query = "SELECT COUNT(*) FROM USERS";
        sqlite3_stmt* statement;

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

        rc = sqlite3_step(statement);
        int count;

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

        sqlite3_finalize(statement);
        std::string query_insert;

        if(count == 0) {
            query_insert = "INSERT INTO USERS (UserId, UserName, Password) VALUES (0, '" + username + "', '" + userpassword + "')";
        } else {          
            query_insert = "INSERT INTO USERS (UserId, UserName, Password) VALUES ((SELECT COALESCE(MAX(UserId), 0) + 1 FROM USERS), '" + username + "', '" + userpassword + "')";
        }
       
        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;
        }

        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);
    }

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

        std::string query = "SELECT COUNT(*) FROM USERS WHERE UserName = ' " + 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;
        }

        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;
    }

      // Getter for the database connection
    static sqlite3* getDBConnection() {
        return db;
    }

    // Setter for the database connection
    static void setDBConnection(sqlite3* newDB) {
        db = newDB;
    }
};

sqlite3* UserDAO::db = nullptr;

#endif