database.cpp

/**
 * @file database.cpp
 * @brief Implementation of the database to store user's, expenses, categories and all the data that will be used by the application .
 *
 * Handles the backend of insertion, deletion, updation and getting the data
 *
 * @author Abdullah Ahmad, Shourya Nundy, Armaan Sharma, Darsh Vimal Shah
 */


#include "database.h"
#include <QSqlQuery>
#include <QCryptographicHash>
#include <QCoreApplication>

QSqlDatabase DatabaseManager::db;
int DatabaseManager::currentUserId = -1;

/**
 * @brief Initializes the SQLite database and creates all required tables.
 * @return true if initialization and table creation succeed.
 */
bool DatabaseManager::initializeDatabase()
{
    QString dbPath = QCoreApplication::applicationDirPath() + "/budgetx.db";
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbPath);

    if (!db.open()) {
        qDebug() << "Error: Unable to open database: " << db.lastError().text();
        return false;
    }

    QSqlQuery query(db);

    // Users
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            email TEXT UNIQUE,
            password TEXT
        )
    )")) return false;
// Create all necessary tables using IF NOT EXISTS
    // Income
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS income (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            date TEXT,
            amount REAL,
            source TEXT,
            description TEXT,
            recurring TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    )")) return false;

    // Expenses
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS expenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            date TEXT,
            amount REAL,
            category TEXT,
            description TEXT,
            recurring TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    )")) return false;

    // Savings
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS saving_goals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            goalName TEXT,
            targetAmount REAL,
            currentAmount REAL,
            priority INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    )")) return false;

    // Stocks
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS stocks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            date TEXT,
            stock_name TEXT,
            purchase_price REAL,
            quantity INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    )")) return false;

    // Cryptocurrencies
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS cryptocurrencies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            date TEXT,
            crypto_name TEXT,
            purchase_price REAL,
            quantity REAL,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
    )")) return false;

    // Monthly Budgets
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS monthly_budgets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            month TEXT,
            total_amount REAL,
            UNIQUE(user_id, month)
        )
    )")) return false;

    // Category Budgets
    if (!query.exec(R"(
        CREATE TABLE IF NOT EXISTS category_budgets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            month TEXT,
            category TEXT,
            amount REAL,
            UNIQUE(user_id, month, category)
        )
    )")) return false;

    qDebug() << "Database initialized successfully!";
    return true;
}

QSqlDatabase& DatabaseManager::getDatabase() {
    return db;
}

bool DatabaseManager::registerUser(const QString &username, const QString &email, const QString &password)
{
    if (doesUserExist(username)) {
        qDebug() << "User already exists: " << username;
        return false;
    }

    QString hashedPassword = QString(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256).toHex());

    QSqlQuery query(db);
    query.prepare("INSERT INTO users (username, email, password) VALUES (:username, :email, :password)");
    query.bindValue(":username", username);
    query.bindValue(":email", email);
    query.bindValue(":password", hashedPassword);
    return query.exec();
}

bool DatabaseManager::validateUser(const QString &username, const QString &password)
{
    QSqlQuery query(db);
    query.prepare("SELECT id, password FROM users WHERE username = :username");
    query.bindValue(":username", username);
    if (query.exec() && query.next()) {
        QString storedHash = query.value(1).toString();
        QString enteredHash = QString(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256).toHex());

        if (storedHash == enteredHash) {
            currentUserId = query.value(0).toInt();
            return true;
        }
    }
    return false;
}

bool DatabaseManager::doesUserExist(const QString &username)
{
    QSqlQuery query(db);
    query.prepare("SELECT id FROM users WHERE username = :username");
    query.bindValue(":username", username);
    return query.exec() && query.next();
}

int DatabaseManager::getUserId(const QString &username)
{
    QSqlQuery query(db);
    query.prepare("SELECT id FROM users WHERE username = :username");
    query.bindValue(":username", username);
    if (query.exec() && query.next()) {
        return query.value(0).toInt();
    }
    return -1;
}

QString DatabaseManager::getEmailForCurrentUser()
{
    QSqlQuery query(db);
    query.prepare("SELECT email FROM users WHERE id = :id");
    query.bindValue(":id", currentUserId);
    if (query.exec() && query.next()) {
        return query.value(0).toString();
    }
    return QString();
}

bool DatabaseManager::updatePassword(const QString &newPassword)
{
    QString newHash = QString(QCryptographicHash::hash(newPassword.toUtf8(), QCryptographicHash::Sha256).toHex());
    QSqlQuery query(db);
    query.prepare("UPDATE users SET password = :pwd WHERE id = :id");
    query.bindValue(":pwd", newHash);
    query.bindValue(":id", currentUserId);
    return query.exec();
}

bool DatabaseManager::verifyPassword(const QString &currentPassword)
{
    QSqlQuery query(db);
    query.prepare("SELECT password FROM users WHERE id = :id");
    query.bindValue(":id", currentUserId);
    if (query.exec() && query.next()) {
        QString storedHash = query.value(0).toString();
        QString enteredHash = QString(QCryptographicHash::hash(currentPassword.toUtf8(), QCryptographicHash::Sha256).toHex());
        return storedHash == enteredHash;
    }
    return false;
}