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");
    query.exec("ALTER TABLE transactions ADD COLUMN expected_balance REAL DEFAULT 0");
    query.exec("ALTER TABLE transactions ADD COLUMN calculated_balance REAL DEFAULT 0");
    
    // Add projection_end_date to recurring_rules (default to 1 year from now)
    query.exec("ALTER TABLE recurring_rules ADD COLUMN projection_end_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, expected_balance, calculated_balance) "
                 "VALUES (:date, :amount, :account, :category, :description, :type, :recurring_id, :sort_order, "
                 ":reconciled, :occurrence_key, :expected_amount, :expected_date, :expected_balance, :calculated_balance)");
    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);
    
    QString typeStr = "estimated";
    if (transaction.type == TransactionType::Actual) typeStr = "actual";
    else if (transaction.type == TransactionType::Reconciliation) typeStr = "reconciliation";
    query.bindValue(":type", typeStr);
    
    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(":expected_balance", transaction.expectedBalance);
    query.bindValue(":calculated_balance", transaction.calculatedBalance);
    
    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, expected_balance=:expected_balance, calculated_balance=:calculated_balance 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);
    
    QString typeStr = "estimated";
    if (transaction.type == TransactionType::Actual) typeStr = "actual";
    else if (transaction.type == TransactionType::Reconciliation) typeStr = "reconciliation";
    query.bindValue(":type", typeStr);
    
    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(":expected_balance", transaction.expectedBalance);
    query.bindValue(":calculated_balance", transaction.calculatedBalance);
    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, projection_end_date) "
                 "VALUES (:name, :frequency, :start_date, :end_date, :amount, :account, "
                 ":category, :description, :day_of_week, :day_of_month, :occurrences, :sort_order, :projection_end_date)");
    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);
    query.bindValue(":projection_end_date", rule.projectionEndDate.isValid() ? rule.projectionEndDate.toString(Qt::ISODate) : QVariant());
    
    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, "
                 "projection_end_date=:projection_end_date 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);
    query.bindValue(":projection_end_date", rule.projectionEndDate.isValid() ? rule.projectionEndDate.toString(Qt::ISODate) : QVariant());
    
    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();
    
    QString typeStr = query.value("type").toString();
    if (typeStr == "actual") t.type = TransactionType::Actual;
    else if (typeStr == "reconciliation") t.type = TransactionType::Reconciliation;
    else t.type = 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);
    }
    t.expectedBalance = query.value("expected_balance").toDouble();
    t.calculatedBalance = query.value("calculated_balance").toDouble();
    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();
    QString projEndDateStr = query.value("projection_end_date").toString();
    if (!projEndDateStr.isEmpty()) {
        r.projectionEndDate = QDate::fromString(projEndDateStr, Qt::ISODate);
    }
    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;
}

void Database::deleteProjectionsForRule(int ruleId) {
    QSqlQuery query;
    // Delete estimated transactions for this rule only where no actual exists for that date
    query.prepare("DELETE FROM transactions WHERE recurring_id = :rule_id AND type = 'estimated' "
                  "AND NOT EXISTS (SELECT 1 FROM transactions t2 WHERE t2.recurring_id = :rule_id AND t2.date = transactions.date AND t2.type = 'actual')");
    query.bindValue(":rule_id", ruleId);
    query.exec();
}

void Database::regenerateProjectionsForRule(const RecurringRule &rule) {
    // Delete ALL existing projections for this rule (not just future ones)
    deleteProjectionsForRule(rule.id);
    
    // Get projection months setting (default 3)
    int projectionMonths = getSetting("projection_months", "3").toInt();
    
    // Determine end date for projections
    QDate endDate = rule.projectionEndDate.isValid() ? 
                    rule.projectionEndDate : 
                    QDate::currentDate().addMonths(projectionMonths);
    
    // Start from the rule's start date (not today)
    QDate currentDate = rule.startDate;
    
    // Align to proper day based on frequency
    if (rule.frequency == RecurrenceFrequency::Weekly || rule.frequency == RecurrenceFrequency::BiWeekly) {
        while (currentDate <= endDate && currentDate.dayOfWeek() != rule.dayOfWeek) {
            currentDate = currentDate.addDays(1);
        }
    } else if (rule.frequency == RecurrenceFrequency::Monthly) {
        int targetDay = qMin(rule.dayOfMonth, currentDate.daysInMonth());
        currentDate = QDate(currentDate.year(), currentDate.month(), targetDay);
    }
    
    int count = 0;
    while (currentDate <= endDate) {
        if (rule.occurrences != -1 && count >= rule.occurrences) {
            break;
        }
        
        if (rule.endDate.isValid() && currentDate > rule.endDate) {
            break;
        }
        
        // Generate occurrence key for this date
        QString occurrenceKey;
        if (rule.frequency == RecurrenceFrequency::Daily) {
            occurrenceKey = currentDate.toString("yyyy-MM-dd");
        } else if (rule.frequency == RecurrenceFrequency::Weekly || rule.frequency == RecurrenceFrequency::BiWeekly) {
            occurrenceKey = QString("%1-W%2").arg(currentDate.year()).arg(currentDate.weekNumber(), 2, 10, QChar('0'));
        } else if (rule.frequency == RecurrenceFrequency::Monthly) {
            occurrenceKey = currentDate.toString("yyyy-MM");
        } else if (rule.frequency == RecurrenceFrequency::Yearly) {
            occurrenceKey = QString::number(currentDate.year());
        }
        
        // Check if this occurrence already has a transaction (actual or reconciled)
        QSqlQuery checkQuery(db);
        checkQuery.prepare("SELECT COUNT(*) FROM transactions WHERE recurring_id = :rid AND occurrence_key = :okey AND (type = 'actual' OR reconciled = 1)");
        checkQuery.bindValue(":rid", rule.id);
        checkQuery.bindValue(":okey", occurrenceKey);
        
        bool hasTransaction = false;
        if (checkQuery.exec() && checkQuery.next()) {
            hasTransaction = checkQuery.value(0).toInt() > 0;
        }
        
        // Only create projection if this occurrence hasn't been fulfilled
        if (!hasTransaction) {
            Transaction t;
            t.date = currentDate;
            t.amount = rule.amount;
            t.account = rule.account;
            t.category = rule.category;
            t.description = rule.description;
            t.type = TransactionType::Estimated;
            t.recurringId = rule.id;
            t.reconciled = false;
            t.sortOrder = rule.sortOrder;
            t.occurrenceKey = occurrenceKey;
            
            addTransaction(t);
        }
        count++;
        
        // Calculate next occurrence
        switch (rule.frequency) {
            case RecurrenceFrequency::Daily:
                currentDate = currentDate.addDays(1);
                break;
            case RecurrenceFrequency::Weekly:
                currentDate = currentDate.addDays(7);
                break;
            case RecurrenceFrequency::BiWeekly:
                currentDate = currentDate.addDays(14);
                break;
            case RecurrenceFrequency::Monthly: {
                currentDate = currentDate.addMonths(1);
                int targetDay = qMin(rule.dayOfMonth, currentDate.daysInMonth());
                currentDate = QDate(currentDate.year(), currentDate.month(), targetDay);
                break;
            }
            case RecurrenceFrequency::Yearly:
                currentDate = currentDate.addYears(1);
                break;
            default:
                currentDate = endDate.addDays(1); // Exit loop
                break;
        }
    }
}

void Database::regenerateAllProjections() {
    // Delete ALL existing projections
    QSqlQuery deleteQuery(db);
    deleteQuery.prepare("DELETE FROM transactions WHERE type = 'estimated'");
    if (!deleteQuery.exec()) {
        errorMsg = deleteQuery.lastError().text();
        return;
    }
    
    // Regenerate projections for all recurring rules
    QList<RecurringRule> rules = getAllRecurringRules();
    for (const RecurringRule &rule : rules) {
        regenerateProjectionsForRule(rule);
    }
}