path: root/database.cpp

#include "database.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>

Database::Database() {
    db = QSqlDatabase::addDatabase("QSQLITE");
}

Database::~Database() {
    if (db.isOpen()) {
        db.close();
    }
}

bool Database::open(const QString &path) {
    db.setDatabaseName(path);
    if (!db.open()) {
        errorMsg = db.lastError().text();
        return false;
    }
    return createTables();
}

bool Database::createTables() {
    QSqlQuery query;
    
    // Create transactions table
    QString createTransactions = R"(
        CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            amount REAL NOT NULL,
            account TEXT NOT NULL,
            category TEXT,
            description TEXT,
            type TEXT NOT NULL,
            recurring_id INTEGER DEFAULT -1,
            sort_order INTEGER DEFAULT 0
        )
    )";
    
    if (!query.exec(createTransactions)) {
        errorMsg = query.lastError().text();
        return false;
    }
    
    // Create recurring_rules table
    QString createRecurring = R"(
        CREATE TABLE IF NOT EXISTS recurring_rules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            frequency TEXT NOT NULL,
            start_date TEXT NOT NULL,
            end_date TEXT,
            amount REAL NOT NULL,
            account TEXT NOT NULL,
            category TEXT,
            description TEXT,
            day_of_week INTEGER DEFAULT -1,
            day_of_month INTEGER DEFAULT -1,
            occurrences INTEGER DEFAULT -1,
            sort_order INTEGER DEFAULT 0
        )
    )";
    
    if (!query.exec(createRecurring)) {
        errorMsg = query.lastError().text();
        return false;
    }
    
    // Create settings table
    QString createSettings = R"(
        CREATE TABLE IF NOT EXISTS settings (
            setting_name TEXT PRIMARY KEY,
            setting_value TEXT
        )
    )";
    
    if (!query.exec(createSettings)) {
        errorMsg = query.lastError().text();
        return false;
    }
    
    // Migrate existing tables - add category column if missing
    query.exec("ALTER TABLE transactions ADD COLUMN category TEXT");
    query.exec("ALTER TABLE recurring_rules ADD COLUMN category TEXT");
    
    // Migrate existing tables - add sort_order column if missing
    query.exec("ALTER TABLE transactions ADD COLUMN sort_order INTEGER DEFAULT 0");
    query.exec("ALTER TABLE recurring_rules ADD COLUMN sort_order INTEGER DEFAULT 0");
    
    // Migrate existing tables - add reconciliation columns
    query.exec("ALTER TABLE transactions ADD COLUMN reconciled INTEGER DEFAULT 0");
    query.exec("ALTER TABLE transactions ADD COLUMN occurrence_key TEXT");
    query.exec("ALTER TABLE transactions ADD COLUMN expected_amount REAL");
    query.exec("ALTER TABLE transactions ADD COLUMN expected_date TEXT");
    
    // Create reconciliation_checkpoints table
    QString createCheckpoints = R"(
        CREATE TABLE IF NOT EXISTS reconciliation_checkpoints (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account TEXT NOT NULL,
            reconciled_through_date TEXT NOT NULL,
            reconciled_at TEXT DEFAULT CURRENT_TIMESTAMP,
            notes TEXT
        )
    )";
    
    if (!query.exec(createCheckpoints)) {
        errorMsg = query.lastError().text();
        return false;
    }
    
    return true;
}

bool Database::addTransaction(const Transaction &transaction) {
    QSqlQuery query;
    query.prepare("INSERT INTO transactions (date, amount, account, category, description, type, recurring_id, sort_order, "
                 "reconciled, occurrence_key, expected_amount, expected_date) "
                 "VALUES (:date, :amount, :account, :category, :description, :type, :recurring_id, :sort_order, "
                 ":reconciled, :occurrence_key, :expected_amount, :expected_date)");
    query.bindValue(":date", transaction.date.toString(Qt::ISODate));
    query.bindValue(":amount", transaction.amount);
    query.bindValue(":account", transaction.account);
    query.bindValue(":category", transaction.category);
    query.bindValue(":description", transaction.description);
    query.bindValue(":type", transaction.type == TransactionType::Actual ? "actual" : "estimated");
    query.bindValue(":recurring_id", transaction.recurringId);
    query.bindValue(":sort_order", transaction.sortOrder);
    query.bindValue(":reconciled", transaction.reconciled ? 1 : 0);
    query.bindValue(":occurrence_key", transaction.occurrenceKey);
    query.bindValue(":expected_amount", transaction.expectedAmount > 0 ? transaction.expectedAmount : QVariant());
    query.bindValue(":expected_date", transaction.expectedDate.isValid() ? transaction.expectedDate.toString(Qt::ISODate) : QVariant());
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

bool Database::updateTransaction(const Transaction &transaction) {
    QSqlQuery query;
    query.prepare("UPDATE transactions SET date=:date, amount=:amount, account=:account, category=:category, "
                 "description=:description, type=:type, recurring_id=:recurring_id, sort_order=:sort_order, "
                 "reconciled=:reconciled, occurrence_key=:occurrence_key, expected_amount=:expected_amount, "
                 "expected_date=:expected_date WHERE id=:id");
    query.bindValue(":id", transaction.id);
    query.bindValue(":date", transaction.date.toString(Qt::ISODate));
    query.bindValue(":amount", transaction.amount);
    query.bindValue(":account", transaction.account);
    query.bindValue(":category", transaction.category);
    query.bindValue(":description", transaction.description);
    query.bindValue(":type", transaction.type == TransactionType::Actual ? "actual" : "estimated");
    query.bindValue(":recurring_id", transaction.recurringId);
    query.bindValue(":sort_order", transaction.sortOrder);
    query.bindValue(":reconciled", transaction.reconciled ? 1 : 0);
    query.bindValue(":occurrence_key", transaction.occurrenceKey);
    query.bindValue(":expected_amount", transaction.expectedAmount > 0 ? transaction.expectedAmount : QVariant());
    query.bindValue(":expected_date", transaction.expectedDate.isValid() ? transaction.expectedDate.toString(Qt::ISODate) : QVariant());
    query.bindValue(":sort_order", transaction.sortOrder);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

bool Database::deleteTransaction(int id) {
    QSqlQuery query;
    query.prepare("DELETE FROM transactions WHERE id=:id");
    query.bindValue(":id", id);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

QList<Transaction> Database::getTransactions(const QDate &startDate, const QDate &endDate) {
    QList<Transaction> transactions;
    QSqlQuery query;
    query.prepare("SELECT * FROM transactions WHERE date >= :start AND date <= :end ORDER BY date");
    query.bindValue(":start", startDate.toString(Qt::ISODate));
    query.bindValue(":end", endDate.toString(Qt::ISODate));
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return transactions;
    }
    
    while (query.next()) {
        transactions.append(queryToTransaction(query));
    }
    return transactions;
}

QList<Transaction> Database::getAllTransactions() {
    QList<Transaction> transactions;
    QSqlQuery query("SELECT * FROM transactions ORDER BY date");
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return transactions;
    }
    
    while (query.next()) {
        transactions.append(queryToTransaction(query));
    }
    return transactions;
}

bool Database::addRecurringRule(const RecurringRule &rule) {
    QSqlQuery query;
    query.prepare("INSERT INTO recurring_rules (name, frequency, start_date, end_date, amount, "
                 "account, category, description, day_of_week, day_of_month, occurrences, sort_order) "
                 "VALUES (:name, :frequency, :start_date, :end_date, :amount, :account, "
                 ":category, :description, :day_of_week, :day_of_month, :occurrences, :sort_order)");
    query.bindValue(":name", rule.name);
    
    QString freq;
    switch (rule.frequency) {
        case RecurrenceFrequency::Daily: freq = "daily"; break;
        case RecurrenceFrequency::Weekly: freq = "weekly"; break;
        case RecurrenceFrequency::BiWeekly: freq = "biweekly"; break;
        case RecurrenceFrequency::Monthly: freq = "monthly"; break;
        case RecurrenceFrequency::Yearly: freq = "yearly"; break;
        default: freq = "none"; break;
    }
    query.bindValue(":frequency", freq);
    query.bindValue(":start_date", rule.startDate.toString(Qt::ISODate));
    query.bindValue(":end_date", rule.endDate.isValid() ? rule.endDate.toString(Qt::ISODate) : QVariant());
    query.bindValue(":amount", rule.amount);
    query.bindValue(":account", rule.account);
    query.bindValue(":category", rule.category);
    query.bindValue(":description", rule.description);
    query.bindValue(":day_of_week", rule.dayOfWeek);
    query.bindValue(":day_of_month", rule.dayOfMonth);
    query.bindValue(":occurrences", rule.occurrences);
    query.bindValue(":sort_order", rule.sortOrder);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

bool Database::updateRecurringRule(const RecurringRule &rule) {
    QSqlQuery query;
    query.prepare("UPDATE recurring_rules SET name=:name, frequency=:frequency, start_date=:start_date, "
                 "end_date=:end_date, amount=:amount, account=:account, category=:category, description=:description, "
                 "day_of_week=:day_of_week, day_of_month=:day_of_month, occurrences=:occurrences, sort_order=:sort_order WHERE id=:id");
    query.bindValue(":id", rule.id);
    query.bindValue(":name", rule.name);
    
    QString freq;
    switch (rule.frequency) {
        case RecurrenceFrequency::Daily: freq = "daily"; break;
        case RecurrenceFrequency::Weekly: freq = "weekly"; break;
        case RecurrenceFrequency::BiWeekly: freq = "biweekly"; break;
        case RecurrenceFrequency::Monthly: freq = "monthly"; break;
        case RecurrenceFrequency::Yearly: freq = "yearly"; break;
        default: freq = "none"; break;
    }
    query.bindValue(":frequency", freq);
    query.bindValue(":start_date", rule.startDate.toString(Qt::ISODate));
    query.bindValue(":end_date", rule.endDate.isValid() ? rule.endDate.toString(Qt::ISODate) : QVariant());
    query.bindValue(":amount", rule.amount);
    query.bindValue(":account", rule.account);
    query.bindValue(":category", rule.category);
    query.bindValue(":description", rule.description);
    query.bindValue(":day_of_week", rule.dayOfWeek);
    query.bindValue(":day_of_month", rule.dayOfMonth);
    query.bindValue(":occurrences", rule.occurrences);
    query.bindValue(":sort_order", rule.sortOrder);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

bool Database::deleteRecurringRule(int id) {
    QSqlQuery query;
    query.prepare("DELETE FROM recurring_rules WHERE id=:id");
    query.bindValue(":id", id);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

QList<RecurringRule> Database::getAllRecurringRules() {
    QList<RecurringRule> rules;
    QSqlQuery query("SELECT * FROM recurring_rules");
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return rules;
    }
    
    while (query.next()) {
        rules.append(queryToRecurringRule(query));
    }
    return rules;
}

bool Database::convertToActual(int transactionId, double actualAmount) {
    QSqlQuery query;
    query.prepare("UPDATE transactions SET type='actual', amount=:amount WHERE id=:id");
    query.bindValue(":amount", actualAmount);
    query.bindValue(":id", transactionId);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}

QString Database::lastError() const {
    return errorMsg;
}

Transaction Database::queryToTransaction(QSqlQuery &query) {
    Transaction t;
    t.id = query.value("id").toInt();
    t.date = QDate::fromString(query.value("date").toString(), Qt::ISODate);
    t.amount = query.value("amount").toDouble();
    t.account = query.value("account").toString();
    t.category = query.value("category").toString();
    t.description = query.value("description").toString();
    t.type = query.value("type").toString() == "actual" ? TransactionType::Actual : TransactionType::Estimated;
    t.recurringId = query.value("recurring_id").toInt();
    t.sortOrder = query.value("sort_order").toInt();
    t.reconciled = query.value("reconciled").toInt() == 1;
    t.occurrenceKey = query.value("occurrence_key").toString();
    t.expectedAmount = query.value("expected_amount").toDouble();
    QString expectedDateStr = query.value("expected_date").toString();
    if (!expectedDateStr.isEmpty()) {
        t.expectedDate = QDate::fromString(expectedDateStr, Qt::ISODate);
    }
    return t;
}

RecurringRule Database::queryToRecurringRule(QSqlQuery &query) {
    RecurringRule r;
    r.id = query.value("id").toInt();
    r.name = query.value("name").toString();
    
    QString freq = query.value("frequency").toString();
    if (freq == "daily") r.frequency = RecurrenceFrequency::Daily;
    else if (freq == "weekly") r.frequency = RecurrenceFrequency::Weekly;
    else if (freq == "biweekly") r.frequency = RecurrenceFrequency::BiWeekly;
    else if (freq == "monthly") r.frequency = RecurrenceFrequency::Monthly;
    else if (freq == "yearly") r.frequency = RecurrenceFrequency::Yearly;
    else r.frequency = RecurrenceFrequency::None;
    
    r.startDate = QDate::fromString(query.value("start_date").toString(), Qt::ISODate);
    QString endDateStr = query.value("end_date").toString();
    if (!endDateStr.isEmpty()) {
        r.endDate = QDate::fromString(endDateStr, Qt::ISODate);
    }
    r.amount = query.value("amount").toDouble();
    r.account = query.value("account").toString();
    r.category = query.value("category").toString();
    r.description = query.value("description").toString();
    r.dayOfWeek = query.value("day_of_week").toInt();
    r.dayOfMonth = query.value("day_of_month").toInt();
    r.occurrences = query.value("occurrences").toInt();
    r.sortOrder = query.value("sort_order").toInt();
    return r;
}

QString Database::getSetting(const QString &name, const QString &defaultValue) {
    QSqlQuery query;
    query.prepare("SELECT setting_value FROM settings WHERE setting_name = :name");
    query.bindValue(":name", name);
    
    if (query.exec() && query.next()) {
        return query.value(0).toString();
    }
    return defaultValue;
}

bool Database::setSetting(const QString &name, const QString &value) {
    QSqlQuery query;
    query.prepare("INSERT OR REPLACE INTO settings (setting_name, setting_value) VALUES (:name, :value)");
    query.bindValue(":name", name);
    query.bindValue(":value", value);
    
    if (!query.exec()) {
        errorMsg = query.lastError().text();
        return false;
    }
    return true;
}