WIP: occurrence_key model before refactoring to persistent projections

1 files changed, 45 insertions, 3 deletions

diff --git a/database.cpp b/database.cpp
index 5f9d99c..4041344 100644
--- a/database.cpp
+++ b/database.cpp
@@ -91,13 +91,37 @@ bool Database::createTables() {
     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) "
-                 "VALUES (:date, :amount, :account, :category, :description, :type, :recurring_id, :sort_order)");
+    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);
@@ -106,6 +130,10 @@ bool Database::addTransaction(const Transaction &transaction) {
     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();
@@ -117,7 +145,9 @@ bool Database::addTransaction(const Transaction &transaction) {
 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 WHERE id=:id");
+                 "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);
@@ -127,6 +157,11 @@ bool Database::updateTransaction(const Transaction &transaction) {
     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();
@@ -307,6 +342,13 @@ Transaction Database::queryToTransaction(QSqlQuery &query) {
     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;
 }