#include "cashflow.h"
#include "ui_cashflow.h"
#include "settingsdialog.h"
#include "multiselectcombobox.h"
#include "importdialog.h"
#include <QMessageBox>
#include <QDebug>
#include <QDir>
#include <QStandardPaths>
#include <QFontDialog>
#include <QLocale>
#include <QFileDialog>
#include <QApplication>
#include <QKeyEvent>
#include <QClipboard>
#include <QFile>
#include <QTextStream>
#include <QFileInfo>
#include <QSignalBlocker>
#include <limits>

CashFlow::CashFlow(QWidget *parent, const QString &filePath)
    : QMainWindow(parent)
    , ui(new Ui::CashFlow)
    , currentTransactionId(-1)
    , currentRecurringId(-1)
    , startingBalance(0.0)
    , currentAmountFont("Courier New", 10)
    , weekStartDay(1) // Default to Monday
    , chartView(nullptr)
    , chart(nullptr)
{
    ui->setupUi(this);
    
    // Initialize database
    database = new Database();
    
    // Determine which database to open
    QString dbPath;
    if (!filePath.isEmpty()) {
        dbPath = filePath;
    } else {
        QString defaultDir = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
        QDir().mkpath(defaultDir);
        dbPath = defaultDir + "/default.cashflo.sqlite";
    }
    
    setupConnections();
    
    // Initialize chart view
    chart = new QChart();
    chart->setTitle("Balance Over Time");
    chart->setAnimationOptions(QChart::SeriesAnimations);
    
    chartView = new QChartView(chart);
    chartView->setRenderHint(QPainter::Antialiasing);
    chartView->setRubberBand(QChartView::RectangleRubberBand); // Enable zoom with mouse drag
    chartView->installEventFilter(this); // Install event filter for mouse wheel zoom
    
    // Add chart view to the chart tab layout
    QVBoxLayout *chartLayout = ui->chartsTab->findChild<QVBoxLayout*>("chartViewLayout");
    if (chartLayout) {
        chartLayout->addWidget(chartView);
    }
    
    // Initialize chart date range to match main date range
    ui->chartDateFromEdit->setDate(QDate::currentDate().addMonths(-1));
    ui->chartDateToEdit->setDate(QDate::currentDate().addMonths(3));
    
    if (!openDatabase(dbPath)) {
        QMessageBox::critical(this, "Database Error", "Failed to open database: " + database->lastError());
        return;
    }
    
    // Regenerate all projections on startup to ensure they're current
    database->regenerateAllProjections();
    // No need to call refreshView here - onJumpToToday already does it
}

CashFlow::~CashFlow()
{
    delete database;
    delete ui;
}

void CashFlow::keyPressEvent(QKeyEvent *event)
{
    // Handle Escape to deselect transaction and clear form
    if (event->key() == Qt::Key_Escape) {
        QTableWidget *table = ui->transactionTable;
        if (table->hasFocus() && !table->selectedItems().isEmpty()) {
            table->clearSelection();
            clearTransactionEntry();
            event->accept();
            return;
        }
    }
    
    // Handle Ctrl+C to copy selected column
    if (event->key() == Qt::Key_C && event->modifiers() == Qt::ControlModifier) {
        QTableWidget *table = nullptr;
        
        // Check which table has focus
        if (ui->transactionTable->hasFocus()) {
            table = ui->transactionTable;
        } else if (ui->recurringTable->hasFocus()) {
            table = ui->recurringTable;
        }
        
        if (table) {
            QList<QTableWidgetItem*> selected = table->selectedItems();
            
            if (selected.isEmpty()) {
                QMainWindow::keyPressEvent(event);
                return;
            }
            
            // Get all unique columns and rows that have selected items
            QSet<int> selectedColumns;
            QSet<int> selectedRows;
            for (QTableWidgetItem *item : selected) {
                selectedColumns.insert(item->column());
                selectedRows.insert(item->row());
            }
            
            // Copy data from selected columns and rows
            // If multiple columns selected, arrange them side-by-side with tabs between columns
            QList<int> sortedColumns = selectedColumns.values();
            std::sort(sortedColumns.begin(), sortedColumns.end());
            
            QList<int> sortedRows = selectedRows.values();
            std::sort(sortedRows.begin(), sortedRows.end());
            
            // Build clipboard text row by row
            QString clipboardText;
            
            // Header row
            QStringList headerRow;
            for (int col : sortedColumns) {
                QString headerText = table->horizontalHeaderItem(col) 
                                   ? table->horizontalHeaderItem(col)->text() 
                                   : QString("Column %1").arg(col);
                headerRow.append(headerText);
            }
            clipboardText += headerRow.join("\t") + "\n";
            
            // Data rows (only selected rows)
            for (int row : sortedRows) {
                QStringList rowData;
                for (int col : sortedColumns) {
                    QTableWidgetItem *item = table->item(row, col);
                    rowData.append(item ? item->text() : "");
                }
                clipboardText += rowData.join("\t") + "\n";
            }
            
            QApplication::clipboard()->setText(clipboardText);
            event->accept();
            return;
        }
    }
    
    QMainWindow::keyPressEvent(event);
}

bool CashFlow::eventFilter(QObject *watched, QEvent *event)
{
    if (watched == chartView && event->type() == QEvent::Wheel) {
        QWheelEvent *wheelEvent = static_cast<QWheelEvent*>(event);
        
        if (chart->axes().isEmpty()) {
            return QMainWindow::eventFilter(watched, event);
        }
        
        // Zoom factor based on wheel delta
        double factor = wheelEvent->angleDelta().y() > 0 ? 0.9 : 1.1;
        
        // Zoom on both axes
        chart->zoom(factor);
        
        return true;
    }
    
    return QMainWindow::eventFilter(watched, event);
}

void CashFlow::setupConnections() {
    // File menu
    connect(ui->actionNew, &QAction::triggered, this, &CashFlow::onNewFile);
    connect(ui->actionOpen, &QAction::triggered, this, &CashFlow::onOpenFile);
    connect(ui->actionSaveAs, &QAction::triggered, this, &CashFlow::onSaveAs);
    connect(ui->actionImportCSV, &QAction::triggered, this, &CashFlow::onImportCSV);
    connect(ui->actionExportCSV, &QAction::triggered, this, &CashFlow::onExportCSV);
    connect(ui->actionQuit, &QAction::triggered, this, &CashFlow::onQuit);
    
    // Settings menu
    connect(ui->actionPreferences, &QAction::triggered, this, &CashFlow::onPreferences);
    
    // Transaction tab
    connect(ui->dateFromEdit, &QDateEdit::dateChanged, this, &CashFlow::onDateRangeChanged);
    connect(ui->dateToEdit, &QDateEdit::dateChanged, this, &CashFlow::onDateRangeChanged);
    connect(ui->periodCombo, QOverload<int>::of(&QComboBox::currentIndexChanged), this, &CashFlow::onPeriodChanged);
    connect(ui->accountFilterCombo, &MultiSelectComboBox::selectionChanged, this, &CashFlow::onDateRangeChanged);
    connect(ui->recurringFilterCombo, QOverload<int>::of(&QComboBox::currentIndexChanged), this, &CashFlow::onDateRangeChanged);
    connect(ui->showAccountBalancesCheck, &QCheckBox::stateChanged, this, &CashFlow::onDateRangeChanged);
    connect(ui->searchEdit, &QLineEdit::textChanged, this, &CashFlow::onSearchTextChanged);
    connect(ui->collapseAllBtn, &QPushButton::clicked, this, &CashFlow::onCollapseAll);
    connect(ui->expandAllBtn, &QPushButton::clicked, this, &CashFlow::onExpandAll);
    connect(ui->jumpTodayBtn, &QPushButton::clicked, this, &CashFlow::onJumpToToday);
    connect(ui->transactionTable, &QTableWidget::cellDoubleClicked, this, &CashFlow::onTransactionTableDoubleClicked);
    
    // Auto-save period and show balances settings
    connect(ui->periodCombo, QOverload<int>::of(&QComboBox::currentIndexChanged), this, [this]() {
        database->setSetting("default_period", QString::number(ui->periodCombo->currentIndex()));
    });
    connect(ui->showAccountBalancesCheck, &QCheckBox::stateChanged, this, [this]() {
        database->setSetting("show_account_balances", QString::number(ui->showAccountBalancesCheck->isChecked() ? 1 : 0));
    });
    
    connect(ui->transactionTable, &QTableWidget::itemSelectionChanged, this, &CashFlow::onTransactionSelected);
    connect(ui->saveBtn, &QPushButton::clicked, this, &CashFlow::onSaveTransaction);
    connect(ui->newBtn, &QPushButton::clicked, this, &CashFlow::onNewTransaction);
    connect(ui->deleteBtn, &QPushButton::clicked, this, &CashFlow::onDeleteTransaction);
    connect(ui->adjustmentBtn, &QPushButton::clicked, this, &CashFlow::onCreateAdjustment);
    
    // Transaction type change - grey out fields for reconciliation
    connect(ui->entryTypeCombo, QOverload<int>::of(&QComboBox::currentIndexChanged), this, [this](int index) {
        bool isReconciliation = (index == 2); // Reconciliation is index 2
        ui->entryCategoryCombo->setEnabled(!isReconciliation);
        ui->entryRecurringCombo->setEnabled(!isReconciliation);
        ui->entryOccurrenceEdit->setEnabled(!isReconciliation);
        ui->adjustmentBtn->setVisible(false); // Hide until we load a reconciliation
    });
    
    // Transaction entry recurring rule linking
    connect(ui->entryRecurringCombo, QOverload<int>::of(&QComboBox::currentIndexChanged), this, &CashFlow::onRecurringRuleChanged);
    connect(ui->entryDateEdit, &QDateEdit::dateChanged, this, &CashFlow::onTransactionDateChanged);
    
    // Set up Delete key shortcut for transaction table
    ui->deleteBtn->setShortcut(Qt::Key_Delete);
    
    // Color-code amount inputs
    connect(ui->entryAmountSpin, QOverload<double>::of(&QDoubleSpinBox::valueChanged), this, &CashFlow::updateAmountColors);
    connect(ui->recurringAmountSpin, QOverload<double>::of(&QDoubleSpinBox::valueChanged), this, &CashFlow::updateAmountColors);
    
    // Recurring tab
    connect(ui->recurringTable, &QTableWidget::itemSelectionChanged, this, &CashFlow::onRecurringSelected);
    connect(ui->saveRecurringBtn, &QPushButton::clicked, this, &CashFlow::onSaveRecurring);
    connect(ui->newRecurringBtn, &QPushButton::clicked, this, &CashFlow::onNewRecurring);
    connect(ui->deleteRecurringBtn, &QPushButton::clicked, this, &CashFlow::onDeleteRecurring);
    
    // Set up Delete key shortcut for recurring table
    ui->deleteRecurringBtn->setShortcut(QKeySequence(Qt::CTRL | Qt::Key_Delete));
    
    // Charts tab
    connect(ui->chartDateFromEdit, &QDateEdit::dateChanged, this, &CashFlow::refreshCharts);
    connect(ui->chartDateToEdit, &QDateEdit::dateChanged, this, &CashFlow::refreshCharts);
}

void CashFlow::refreshView() {
    // Populate account filter dropdown with unique accounts
    // Block signals to prevent recursive refresh
    ui->accountFilterCombo->blockSignals(true);
    
    QStringList previouslySelected = ui->accountFilterCombo->getSelectedItems();
    ui->accountFilterCombo->clear();
    
    // Get unique account names and categories
    QSet<QString> accounts;
    QSet<QString> categories;
    for (const Transaction &t : database->getAllTransactions()) {
        if (!t.account.isEmpty()) accounts.insert(t.account);
        if (!t.category.isEmpty() && t.category != "Adjustment") categories.insert(t.category);
    }
    for (const RecurringRule &r : database->getAllRecurringRules()) {
        if (!r.account.isEmpty()) accounts.insert(r.account);
        if (!r.category.isEmpty() && r.category != "Adjustment") categories.insert(r.category);
    }
    
    QStringList sortedAccounts = accounts.values();
    sortedAccounts.sort();
    ui->accountFilterCombo->addItems(sortedAccounts);
    
    // Restore previous selection if possible, otherwise select all
    if (!previouslySelected.isEmpty()) {
        QStringList validSelection;
        for (const QString &account : previouslySelected) {
            if (sortedAccounts.contains(account)) {
                validSelection.append(account);
            }
        }
        if (!validSelection.isEmpty()) {
            ui->accountFilterCombo->setSelectedItems(validSelection);
        }
    }
    
    ui->accountFilterCombo->blockSignals(false);
    ui->entryAccountCombo->blockSignals(true);
    QString currentAccount = ui->entryAccountCombo->currentText();
    ui->entryAccountCombo->clear();
    ui->entryAccountCombo->addItems(sortedAccounts);
    ui->entryAccountCombo->setCurrentText(currentAccount);
    ui->entryAccountCombo->blockSignals(false);
    
    // Populate entry form category combo
    ui->entryCategoryCombo->blockSignals(true);
    QString currentCategory = ui->entryCategoryCombo->currentText();
    ui->entryCategoryCombo->clear();
    QStringList sortedCategories = categories.values();
    sortedCategories.sort();
    ui->entryCategoryCombo->addItems(sortedCategories);
    ui->entryCategoryCombo->setCurrentText(currentCategory);
    ui->entryCategoryCombo->blockSignals(false);
    
    // Populate recurring rules combo
    populateRecurringRulesCombo();
    
    // Populate recurring rule account and category combos
    ui->recurringAccountCombo->blockSignals(true);
    ui->recurringAccountCombo->clear();
    ui->recurringAccountCombo->addItems(sortedAccounts);
    ui->recurringAccountCombo->blockSignals(false);
    
    ui->recurringCategoryCombo->blockSignals(true);
    ui->recurringCategoryCombo->clear();
    ui->recurringCategoryCombo->addItems(sortedCategories);
    ui->recurringCategoryCombo->blockSignals(false);
    
    refreshTransactionTable();
    refreshRecurringTable();
    refreshCharts();
    calculateAndDisplayBalance();
}

void CashFlow::refreshTransactionTable() {
    QList<Transaction> allTransactions = getAllTransactionsInRange();
    
    ui->transactionTable->setRowCount(0);
    
    // Calculate starting balance from all transactions before the date range
    QDate startDate = ui->dateFromEdit->date();
    QStringList selectedAccounts = ui->accountFilterCombo->getSelectedItems();
    bool showAllAccounts = ui->accountFilterCombo->allSelected();
    
    // Get transactions before the visible range
    QList<Transaction> priorTransactions = database->getTransactions(QDate(1900, 1, 1), startDate.addDays(-1));
    
    // Filter by selected accounts if applicable
    if (!showAllAccounts && !selectedAccounts.isEmpty()) {
        QList<Transaction> filtered;
        for (const Transaction &t : priorTransactions) {
            if (selectedAccounts.contains(t.account)) {
                filtered.append(t);
            }
        }
        priorTransactions = filtered;
    }
    
    // Calculate starting balance and per-account balances from prior transactions
    double calculatedStartingBalance = 0.0;
    QMap<QString, double> accountBalances; // Track per-account balances
    
    for (const Transaction &t : priorTransactions) {
        calculatedStartingBalance += t.amount;
        accountBalances[t.account] += t.amount;
    }
    
    // Determine if we're showing per-account columns
    bool showAccountColumns = ui->showAccountBalancesCheck->isChecked();
    
    // Get list of all unique accounts in the date range
    QStringList accountList;
    if (showAccountColumns) {
        QSet<QString> uniqueAccounts;
        for (const Transaction &t : allTransactions) {
            if (!t.account.isEmpty()) {
                uniqueAccounts.insert(t.account);
            }
        }
        accountList = uniqueAccounts.values();
        std::sort(accountList.begin(), accountList.end());
        
        // Ensure all accounts in accountList are initialized in accountBalances map
        for (const QString &account : accountList) {
            if (!accountBalances.contains(account)) {
                accountBalances[account] = 0.0;
            }
        }
    }
    
    // Set up columns: Date | Amount | Balance | [Per-account columns] | Account | Category | Description | Type
    int baseColumnCount = 3; // Date, Amount, Balance
    int accountColumnsCount = accountList.size() * 2; // 2 per account (Amount + Balance)
    int totalColumns = baseColumnCount + accountColumnsCount + 5; // + Account, Category, Description, Type, Recurring
    ui->transactionTable->setColumnCount(totalColumns);
    
    QStringList headers = {"Date", "Amount", "Balance"};
    for (const QString &account : accountList) {
        headers.append(account + " Amount");
        headers.append(account + " Balance");
    }
    headers << "Account" << "Category" << "Description" << "Type" << "Recurring";
    ui->transactionTable->setHorizontalHeaderLabels(headers);
    
    // Enable column reordering by dragging headers
    ui->transactionTable->horizontalHeader()->setSectionsMovable(true);
    
    double runningBalance = calculatedStartingBalance;
    QDate currentPeriodEnd;
    QString periodLabel;
    int periodCount = 1;
    
    // Determine period type
    PeriodType periodType = static_cast<PeriodType>(ui->periodCombo->currentIndex());
    
    // Get first period end date
    if (!allTransactions.isEmpty()) {
        currentPeriodEnd = getPeriodEnd(allTransactions.first().date, periodType);
        periodLabel = getPeriodLabel(allTransactions.first().date, periodType, periodCount);
    }
    
    // Track which period we're in for collapse tracking
    int currentPeriodId = 0;
    bool inCollapsedPeriod = false;
    
    for (const Transaction &t : allTransactions) {
        // Check if we've crossed into a new period
        if (t.date > currentPeriodEnd) {
            // Insert period end row
            currentPeriodId = periodCount; // Use period count as ID
            insertPeriodEndRow(periodLabel, runningBalance, accountBalances, currentPeriodId);
            
            // Check if new period will be collapsed
            inCollapsedPeriod = false;
            
            // Move to next period
            periodCount++;
            currentPeriodEnd = getPeriodEnd(t.date, periodType);
            periodLabel = getPeriodLabel(t.date, periodType, periodCount);
        }
        
        // Check if current period will be collapsed
        if (periodCount > 0 && collapsedPeriods[periodType].contains(periodCount)) {
            inCollapsedPeriod = true;
        }
        
        // Skip inserting transaction row if we're in a collapsed period
        if (inCollapsedPeriod) {
            // Still update balances for correct totals
            runningBalance += t.amount;
            accountBalances[t.account] += t.amount;
            continue;
        }
        
        // Update balances
        runningBalance += t.amount;
        accountBalances[t.account] += t.amount;
        
        // Insert transaction row
        int row = ui->transactionTable->rowCount();
        ui->transactionTable->insertRow(row);
        
        // Store ID in first column's data for retrieval later
        QTableWidgetItem *dateItem = new QTableWidgetItem(t.date.toString("MM/dd/yy"));
        dateItem->setData(Qt::UserRole, t.id);
        dateItem->setData(Qt::UserRole + 2, t.date.toString(Qt::ISODate));
        dateItem->setFlags(dateItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, 0, dateItem);
        
        // For reconciliation rows, show expected balance in amount column, variance in balance column
        double displayAmount = t.amount;
        double displayBalance = runningBalance;
        
        if (t.type == TransactionType::Reconciliation) {
            displayAmount = t.expectedBalance;  // Show what bank says
            displayBalance = t.expectedBalance - t.calculatedBalance; // Show variance
        }
        
        // Format amount with color, right-align, monospace
        QTableWidgetItem *amountItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(displayAmount)));
        amountItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
        amountItem->setFont(currentAmountFont);
        amountItem->setFlags(amountItem->flags() & ~Qt::ItemIsEditable);
        if (displayAmount < 0) {
            amountItem->setForeground(QColor(200, 0, 0));
        } else {
            amountItem->setForeground(QColor(0, 150, 0));
        }
        ui->transactionTable->setItem(row, 1, amountItem);
        
        // Format balance with right-align, monospace
        QTableWidgetItem *balanceItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(displayBalance)));
        balanceItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
        balanceItem->setFont(currentAmountFont);
        balanceItem->setFlags(balanceItem->flags() & ~Qt::ItemIsEditable);
        if (displayBalance < 0) {
            balanceItem->setForeground(QColor(200, 0, 0));
        }
        ui->transactionTable->setItem(row, 2, balanceItem);
        
        // Populate per-account amount and balance columns if enabled
        if (showAccountColumns) {
            for (int i = 0; i < accountList.size(); i++) {
                int amountCol = baseColumnCount + (i * 2);     // Amount column
                int balanceCol = baseColumnCount + (i * 2) + 1; // Balance column
                
                if (accountList[i] == t.account) {
                    // This transaction belongs to this account - show amount and balance normally
                    
                    // Amount column
                    QTableWidgetItem *accountAmtItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(t.amount)));
                    accountAmtItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    accountAmtItem->setFont(currentAmountFont);
                    accountAmtItem->setFlags(accountAmtItem->flags() & ~Qt::ItemIsEditable);
                    if (t.amount < 0) {
                        accountAmtItem->setForeground(QColor(200, 0, 0));
                    } else {
                        accountAmtItem->setForeground(QColor(0, 150, 0));
                    }
                    ui->transactionTable->setItem(row, amountCol, accountAmtItem);
                    
                    // Balance column
                    QTableWidgetItem *accountBalItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(accountBalances[t.account])));
                    accountBalItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    accountBalItem->setFont(currentAmountFont);
                    accountBalItem->setFlags(accountBalItem->flags() & ~Qt::ItemIsEditable);
                    if (accountBalances[t.account] < 0) {
                        accountBalItem->setForeground(QColor(200, 0, 0));
                    }
                    ui->transactionTable->setItem(row, balanceCol, accountBalItem);
                } else {
                    // Other accounts - show balance in grey (no amount change)
                    QTableWidgetItem *emptyAmtItem = new QTableWidgetItem("");
                    emptyAmtItem->setFlags(emptyAmtItem->flags() & ~Qt::ItemIsEditable);
                    ui->transactionTable->setItem(row, amountCol, emptyAmtItem);
                    
                    // Show the current balance for this account in grey
                    QTableWidgetItem *greyBalItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(accountBalances[accountList[i]])));
                    greyBalItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
                    greyBalItem->setFont(currentAmountFont);
                    greyBalItem->setFlags(greyBalItem->flags() & ~Qt::ItemIsEditable);
                    greyBalItem->setForeground(QColor(150, 150, 150)); // Grey color for unchanged accounts
                    ui->transactionTable->setItem(row, balanceCol, greyBalItem);
                }
            }
        }
        
        // Account, Category, Description, Type, Recurring come after per-account columns
        int accountCol = baseColumnCount + accountColumnsCount;
        int categoryCol = accountCol + 1;
        int descCol = categoryCol + 1;
        int typeCol = descCol + 1;
        int recurringCol = typeCol + 1;
        
        QTableWidgetItem *accountItem = new QTableWidgetItem(t.account);
        accountItem->setTextAlignment(Qt::AlignCenter | Qt::AlignVCenter);
        accountItem->setFlags(accountItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, accountCol, accountItem);
        
        QTableWidgetItem *categoryItem = new QTableWidgetItem(t.category);
        categoryItem->setFlags(categoryItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, categoryCol, categoryItem);
        
        // For reconciliation, show calculated status in description
        QString displayDescription = t.description;
        if (t.type == TransactionType::Reconciliation) {
            double variance = t.expectedBalance - t.calculatedBalance;
            QString status = (qAbs(variance) < 0.01) ? "Balanced" : QString("Out of balance by $%1").arg(formatCurrency(variance));
            displayDescription = t.description.isEmpty() ? status : QString("%1 - %2").arg(t.description, status);
        }
        
        QTableWidgetItem *descItem = new QTableWidgetItem(displayDescription);
        descItem->setFlags(descItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, descCol, descItem);
        
        QString typeLabel = "Estimated";
        if (t.type == TransactionType::Actual) typeLabel = "Actual";
        else if (t.type == TransactionType::Reconciliation) typeLabel = "Reconciliation";
        
        QTableWidgetItem *typeItem = new QTableWidgetItem(typeLabel);
        typeItem->setFlags(typeItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, typeCol, typeItem);
        
        // Recurring rule name (if linked to a recurring rule)
        QString recurringName = "";
        if (t.recurringId != -1) {
            QList<RecurringRule> rules = database->getAllRecurringRules();
            for (const RecurringRule &rule : rules) {
                if (rule.id == t.recurringId) {
                    recurringName = rule.name;
                    break;
                }
            }
        }
        QTableWidgetItem *recurringItem = new QTableWidgetItem(recurringName);
        recurringItem->setFlags(recurringItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, recurringCol, recurringItem);
        
        // Color code: Actual=green, Estimated=yellow, Reconciliation=red if mismatch, blue if balanced
        QColor rowColor;
        if (t.type == TransactionType::Reconciliation) {
            double variance = qAbs(t.expectedBalance - t.calculatedBalance);
            rowColor = (variance < 0.01) ? QColor(200, 220, 255) : QColor(255, 200, 200);
        } else {
            rowColor = t.type == TransactionType::Actual ? QColor(200, 255, 200) : QColor(255, 255, 200);
        }
        
        for (int col = 0; col < totalColumns; col++) {
            if (ui->transactionTable->item(row, col)) {
                ui->transactionTable->item(row, col)->setBackground(rowColor);
            }
        }
    }
    
    // Insert final period end row
    if (!allTransactions.isEmpty()) {
        currentPeriodId = periodCount;
        insertPeriodEndRow(periodLabel, runningBalance, accountBalances, currentPeriodId);
    }
    
    ui->transactionTable->resizeColumnsToContents();
    
    // Set minimum and optimal widths for specific columns
    ui->transactionTable->setColumnWidth(0, 100);  // Date
    ui->transactionTable->setColumnWidth(1, 100);  // Amount
    ui->transactionTable->setColumnWidth(2, 100);  // Balance
    
    // Set widths for per-account amount and balance columns
    if (showAccountColumns) {
        for (int i = 0; i < accountList.size(); i++) {
            ui->transactionTable->setColumnWidth(baseColumnCount + (i * 2), 100);     // Account Amount
            ui->transactionTable->setColumnWidth(baseColumnCount + (i * 2) + 1, 100); // Account Balance
        }
    }
    
    // Account, Category, Description, Type come after per-account columns
    int accountCol = baseColumnCount + accountColumnsCount;
    ui->transactionTable->setColumnWidth(accountCol, 120);      // Account
    ui->transactionTable->setColumnWidth(accountCol + 1, 120);  // Category
    ui->transactionTable->setColumnWidth(accountCol + 2, 250);  // Description
    ui->transactionTable->setColumnWidth(accountCol + 3, 80);   // Type
    ui->transactionTable->horizontalHeader()->setStretchLastSection(false);
}

QDate CashFlow::getPeriodEnd(const QDate &date, PeriodType periodType) {
    switch (periodType) {
        case Daily:
            return date;
        case Weekly: {
            // End on day before week start day
            int weekEndDay = (weekStartDay == 1) ? 7 : weekStartDay - 1;
            int currentDay = date.dayOfWeek();
            int daysUntilWeekEnd = (weekEndDay - currentDay + 7) % 7;
            if (daysUntilWeekEnd == 0 && currentDay != weekEndDay) {
                daysUntilWeekEnd = 7; // If we're past the end, go to next week's end
            }
            return date.addDays(daysUntilWeekEnd);
        }
        case Monthly:
            return QDate(date.year(), date.month(), date.daysInMonth());
        case Quarterly: {
            int quarter = (date.month() - 1) / 3;
            int lastMonthOfQuarter = (quarter + 1) * 3;
            QDate lastDayOfQuarter(date.year(), lastMonthOfQuarter, 1);
            return QDate(date.year(), lastMonthOfQuarter, lastDayOfQuarter.daysInMonth());
        }
    }
    return date;
}

QString CashFlow::getPeriodLabel(const QDate &date, PeriodType periodType, int count) {
    QDate periodStart = getPeriodStart(date, periodType);
    QDate periodEnd = getPeriodEnd(date, periodType);
    
    QString dateRange = QString("%1 - %2").arg(periodStart.toString("MM/dd")).arg(periodEnd.toString("MM/dd"));
    
    switch (periodType) {
        case Daily:
            return QString("DAY %1 %2 END").arg(count).arg(date.toString("MM/dd"));
        case Weekly:
            return QString("WEEK %1 END %2").arg(count).arg(dateRange);
        case Monthly:
            return QString("%1 END %2").arg(date.toString("MMMM yyyy").toUpper()).arg(dateRange);
        case Quarterly:
            return QString("Q%1 %2 END %3").arg((date.month() - 1) / 3 + 1).arg(date.year()).arg(dateRange);
    }
    return "";
}

QDate CashFlow::getPeriodStart(const QDate &date, PeriodType periodType) {
    switch (periodType) {
        case Daily:
            return date;
        case Weekly: {
            // Start on configured week start day (1=Monday, 7=Sunday)
            int currentDay = date.dayOfWeek(); // 1=Monday, 7=Sunday
            int daysFromWeekStart = (currentDay - weekStartDay + 7) % 7;
            return date.addDays(-daysFromWeekStart);
        }
        case Monthly:
            return QDate(date.year(), date.month(), 1);
        case Quarterly: {
            int quarter = (date.month() - 1) / 3;
            int firstMonthOfQuarter = quarter * 3 + 1;
            return QDate(date.year(), firstMonthOfQuarter, 1);
        }
    }
    return date;
}

void CashFlow::insertPeriodEndRow(const QString &label, double balance, const QMap<QString, double> &accountBalances, int periodId) {
    int row = ui->transactionTable->rowCount();
    ui->transactionTable->insertRow(row);
    
    bool showAccountColumns = ui->showAccountBalancesCheck->isChecked();
    
    // Get account list (same as in refreshTransactionTable)
    QStringList accountList;
    if (showAccountColumns) {
        accountList = accountBalances.keys();
        std::sort(accountList.begin(), accountList.end());
    }
    
    int baseColumnCount = 3;
    int accountColumnsCount = showAccountColumns ? (accountList.size() * 2) : 0;
    int totalColumns = ui->transactionTable->columnCount();
    
    // Date column: show period label - SPAN across Date and Amount columns
    PeriodType periodType = static_cast<PeriodType>(ui->periodCombo->currentIndex());
    bool isCollapsed = collapsedPeriods[periodType].contains(periodId);
    QString displayLabel = label;
    if (isCollapsed) {
        displayLabel = "▶ " + label;  // Right arrow for collapsed
    } else {
        displayLabel = "▼ " + label;  // Down arrow for expanded
    }
    
    QTableWidgetItem *dateItem = new QTableWidgetItem(displayLabel);
    dateItem->setFont(QFont("Arial", 11, QFont::Bold));
    dateItem->setBackground(QColor(180, 180, 180));
    dateItem->setForeground(Qt::black);
    dateItem->setTextAlignment(Qt::AlignLeft | Qt::AlignVCenter);
    dateItem->setFlags(dateItem->flags() & ~Qt::ItemIsEditable);
    dateItem->setData(Qt::UserRole, -1); // Mark as period end row
    dateItem->setData(Qt::UserRole + 1, periodId); // Store period ID
    ui->transactionTable->setItem(row, 0, dateItem);
    
    // Span the Date cell across Date and Amount columns (columns 0 and 1)
    ui->transactionTable->setSpan(row, 0, 1, 2);
    
    // Column 1 (Amount) is hidden by the span, but set it to empty to avoid copy issues
    QTableWidgetItem *hiddenAmountItem = new QTableWidgetItem("");
    hiddenAmountItem->setBackground(QColor(180, 180, 180));
    hiddenAmountItem->setFlags(hiddenAmountItem->flags() & ~Qt::ItemIsEditable);
    ui->transactionTable->setItem(row, 1, hiddenAmountItem);
    
    // Balance column: grand total
    QTableWidgetItem *balanceItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(balance)));
    balanceItem->setFont(QFont("Arial", 11, QFont::Bold));
    balanceItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
    balanceItem->setBackground(QColor(180, 180, 180));
    balanceItem->setForeground(balance < 0 ? QColor(200, 0, 0) : QColor(0, 100, 0));
    balanceItem->setFlags(balanceItem->flags() & ~Qt::ItemIsEditable);
    ui->transactionTable->setItem(row, 2, balanceItem);
    
    // Per-account columns
    if (showAccountColumns) {
        for (int i = 0; i < accountList.size(); i++) {
            int amountCol = baseColumnCount + (i * 2);
            int balanceCol = baseColumnCount + (i * 2) + 1;
            
            // Amount column: empty
            QTableWidgetItem *acctAmtItem = new QTableWidgetItem("");
            acctAmtItem->setBackground(QColor(180, 180, 180));
            acctAmtItem->setFlags(acctAmtItem->flags() & ~Qt::ItemIsEditable);
            ui->transactionTable->setItem(row, amountCol, acctAmtItem);
            
            // Balance column: account balance
            double acctBalance = accountBalances.value(accountList[i], 0.0);
            QTableWidgetItem *acctBalItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(acctBalance)));
            acctBalItem->setFont(QFont("Arial", 11, QFont::Bold));
            acctBalItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
            acctBalItem->setBackground(QColor(180, 180, 180));
            acctBalItem->setForeground(acctBalance < 0 ? QColor(200, 0, 0) : QColor(0, 100, 0));
            acctBalItem->setFlags(acctBalItem->flags() & ~Qt::ItemIsEditable);
            ui->transactionTable->setItem(row, balanceCol, acctBalItem);
        }
    }
    
    // Remaining columns (Account, Category, Description, Type): empty
    int accountCol = baseColumnCount + accountColumnsCount;
    for (int col = accountCol; col < totalColumns; col++) {
        QTableWidgetItem *emptyItem = new QTableWidgetItem("");
        emptyItem->setBackground(QColor(180, 180, 180));
        emptyItem->setFlags(emptyItem->flags() & ~Qt::ItemIsEditable);
        ui->transactionTable->setItem(row, col, emptyItem);
    }
    
    // Make the row taller
    ui->transactionTable->setRowHeight(row, 30);
}

void CashFlow::refreshRecurringTable() {
    QList<RecurringRule> rules = database->getAllRecurringRules();
    
    ui->recurringTable->setRowCount(0);
    ui->recurringTable->setColumnCount(9);
    ui->recurringTable->setHorizontalHeaderLabels({"ID", "Name", "Frequency", "Schedule", "Amount", "Account", "Category", "Start Date", "End Date"});
    ui->recurringTable->setEditTriggers(QAbstractItemView::NoEditTriggers);
    
    for (const RecurringRule &r : rules) {
        int row = ui->recurringTable->rowCount();
        ui->recurringTable->insertRow(row);
        
        QTableWidgetItem *idItem = new QTableWidgetItem(QString::number(r.id));
        idItem->setData(Qt::UserRole, r.id); // Store numeric value for sorting
        ui->recurringTable->setItem(row, 0, idItem);
        ui->recurringTable->setItem(row, 1, new QTableWidgetItem(r.name));
        
        QString freqStr;
        switch (r.frequency) {
            case RecurrenceFrequency::Daily: freqStr = "Daily"; break;
            case RecurrenceFrequency::Weekly: freqStr = "Weekly"; break;
            case RecurrenceFrequency::BiWeekly: freqStr = "Bi-Weekly"; break;
            case RecurrenceFrequency::Monthly: freqStr = "Monthly"; break;
            case RecurrenceFrequency::Yearly: freqStr = "Yearly"; break;
            default: freqStr = "None"; break;
        }
        ui->recurringTable->setItem(row, 2, new QTableWidgetItem(freqStr));
        
        // Schedule column - human-readable description
        QString scheduleStr;
        if (r.frequency == RecurrenceFrequency::Weekly || r.frequency == RecurrenceFrequency::BiWeekly) {
            if (r.dayOfWeek >= 1 && r.dayOfWeek <= 7) {
                QStringList dayNames = {"Mondays", "Tuesdays", "Wednesdays", "Thursdays", "Fridays", "Saturdays", "Sundays"};
                scheduleStr = dayNames[r.dayOfWeek - 1];
            }
        } else if (r.frequency == RecurrenceFrequency::Monthly) {
            if (r.dayOfMonth >= 1 && r.dayOfMonth <= 31) {
                QString suffix;
                if (r.dayOfMonth == 1 || r.dayOfMonth == 21 || r.dayOfMonth == 31) suffix = "st";
                else if (r.dayOfMonth == 2 || r.dayOfMonth == 22) suffix = "nd";
                else if (r.dayOfMonth == 3 || r.dayOfMonth == 23) suffix = "rd";
                else suffix = "th";
                scheduleStr = QString("on the %1%2").arg(r.dayOfMonth).arg(suffix);
            }
        } else if (r.frequency == RecurrenceFrequency::Yearly) {
            scheduleStr = r.startDate.toString("MMM d");
        }
        ui->recurringTable->setItem(row, 3, new QTableWidgetItem(scheduleStr));
        
        // Amount with color coding, right-align, monospace
        QTableWidgetItem *amountItem = new QTableWidgetItem(QString("$%1").arg(formatCurrency(r.amount)));
        amountItem->setTextAlignment(Qt::AlignRight | Qt::AlignVCenter);
        amountItem->setFont(currentAmountFont);
        amountItem->setData(Qt::UserRole, r.amount); // Store numeric value for sorting
        if (r.amount < 0) {
            amountItem->setForeground(QColor(200, 0, 0)); // Red for debits
        } else {
            amountItem->setForeground(QColor(0, 150, 0)); // Green for credits
        }
        ui->recurringTable->setItem(row, 4, amountItem);
        
        ui->recurringTable->setItem(row, 5, new QTableWidgetItem(r.account));
        ui->recurringTable->setItem(row, 6, new QTableWidgetItem(r.category));
        ui->recurringTable->setItem(row, 7, new QTableWidgetItem(r.startDate.toString("yyyy-MM-dd")));
        
        // End Date column
        QString endDateStr = r.endDate.isValid() ? r.endDate.toString("yyyy-MM-dd") : "";
        ui->recurringTable->setItem(row, 8, new QTableWidgetItem(endDateStr));
    }
    
    ui->recurringTable->resizeColumnsToContents();
}

void CashFlow::refreshCharts() {
    if (!chart || !chartView) return;
    
    // Clear existing series and axes
    chart->removeAllSeries();
    
    // Remove all existing axes
    for (QAbstractAxis *axis : chart->axes()) {
        chart->removeAxis(axis);
        delete axis;
    }
    
    // Get chart-specific date range
    QDate chartStartDate = ui->chartDateFromEdit->date();
    QDate chartEndDate = ui->chartDateToEdit->date();
    
    // Get all transactions (not filtered by main date range)
    QList<Transaction> allTransactions = database->getTransactions(chartStartDate, chartEndDate);
    
    // Apply account filter from main view
    QStringList selectedAccounts = ui->accountFilterCombo->getSelectedItems();
    bool showAllAccounts = ui->accountFilterCombo->allSelected();
    
    // Filter transactions by selected accounts
    if (!showAllAccounts && !selectedAccounts.isEmpty()) {
        QList<Transaction> filtered;
        for (const Transaction &t : allTransactions) {
            if (selectedAccounts.contains(t.account)) {
                filtered.append(t);
            }
        }
        allTransactions = filtered;
    }
    
    // Sort by date
    std::sort(allTransactions.begin(), allTransactions.end(), 
              [](const Transaction &a, const Transaction &b) {
                  if (a.date != b.date) return a.date < b.date;
                  if (a.sortOrder != b.sortOrder) return a.sortOrder < b.sortOrder;
                  return a.amount > b.amount;
              });
    
    if (allTransactions.isEmpty()) {
        chart->setTitle("Balance Over Time (No Data)");
        return;
    }
    
    // Get unique accounts
    QSet<QString> accountSet;
    for (const Transaction &t : allTransactions) {
        if (!t.account.isEmpty()) {
            accountSet.insert(t.account);
        }
    }
    QStringList accounts = accountSet.values();
    std::sort(accounts.begin(), accounts.end());
    
    // Filter to only selected accounts
    if (!showAllAccounts && !selectedAccounts.isEmpty()) {
        accounts = selectedAccounts;
    }
    
    // Create a series for each account
    QMap<QString, QLineSeries*> seriesMap;
    QMap<QString, double> accountBalances;
    
    for (const QString &account : accounts) {
        QLineSeries *series = new QLineSeries();
        series->setName(account);
        seriesMap[account] = series;
        accountBalances[account] = 0.0;
        
        // Add starting point
        series->append(QDateTime(chartStartDate, QTime(0, 0)).toMSecsSinceEpoch(), 0.0);
    }
    
    // Also create total balance series if showing multiple accounts
    QLineSeries *totalSeries = nullptr;
    double totalBalance = startingBalance;
    if (showAllAccounts && accounts.size() > 1) {
        totalSeries = new QLineSeries();
        totalSeries->setName("Total");
        totalSeries->append(QDateTime(chartStartDate, QTime(0, 0)).toMSecsSinceEpoch(), totalBalance);
    }
    
    // Process transactions and update balances
    for (const Transaction &t : allTransactions) {
        if (t.type == TransactionType::Reconciliation) continue;
        
        // Update account balance
        if (seriesMap.contains(t.account)) {
            accountBalances[t.account] += t.amount;
            seriesMap[t.account]->append(QDateTime(t.date, QTime(0, 0)).toMSecsSinceEpoch(), 
                                         accountBalances[t.account]);
        }
        
        // Update total balance
        if (totalSeries) {
            totalBalance += t.amount;
            totalSeries->append(QDateTime(t.date, QTime(0, 0)).toMSecsSinceEpoch(), totalBalance);
        }
    }
    
    // Add all series to chart
    for (QLineSeries *series : seriesMap.values()) {
        chart->addSeries(series);
    }
    if (totalSeries) {
        chart->addSeries(totalSeries);
        QPen pen = totalSeries->pen();
        pen.setWidth(3);
        totalSeries->setPen(pen);
    }
    
    // Create axes
    QDateTimeAxis *axisX = new QDateTimeAxis;
    axisX->setFormat("MMM dd");
    axisX->setTitleText("Date");
    axisX->setTickCount(10);
    axisX->setRange(QDateTime(chartStartDate, QTime(0, 0)), QDateTime(chartEndDate, QTime(23, 59)));
    chart->addAxis(axisX, Qt::AlignBottom);
    
    // Find min/max values across all series
    double minBalance = 0.0;
    double maxBalance = 0.0;
    bool firstValue = true;
    
    for (QLineSeries *series : seriesMap.values()) {
        for (const QPointF &point : series->points()) {
            if (firstValue) {
                minBalance = maxBalance = point.y();
                firstValue = false;
            } else {
                minBalance = qMin(minBalance, point.y());
                maxBalance = qMax(maxBalance, point.y());
            }
        }
    }
    if (totalSeries) {
        for (const QPointF &point : totalSeries->points()) {
            if (firstValue) {
                minBalance = maxBalance = point.y();
                firstValue = false;
            } else {
                minBalance = qMin(minBalance, point.y());
                maxBalance = qMax(maxBalance, point.y());
            }
        }
    }
    
    // Add 10% padding to the range
    double range = maxBalance - minBalance;
    double padding = range * 0.1;
    if (range < 0.01) padding = 100; // Minimum padding if range is tiny
    
    QValueAxis *axisY = new QValueAxis;
    axisY->setTitleText("Balance ($)");
    axisY->setLabelFormat("$%.2f");
    axisY->setRange(minBalance - padding, maxBalance + padding);
    chart->addAxis(axisY, Qt::AlignLeft);
    
    // Attach axes to all series
    for (QLineSeries *series : seriesMap.values()) {
        series->attachAxis(axisX);
        series->attachAxis(axisY);
        
        // Enable hover events for tooltips
        series->setPointsVisible(false);
        connect(series, &QLineSeries::hovered, this, [this, series](const QPointF &point, bool state) {
            if (state) {
                QDateTime dateTime = QDateTime::fromMSecsSinceEpoch(point.x());
                QString tooltip = QString("%1\n%2\n$%3")
                    .arg(series->name())
                    .arg(dateTime.date().toString("MMM dd, yyyy"))
                    .arg(formatCurrency(point.y()));
                chart->setToolTip(tooltip);
            }
        });
    }
    if (totalSeries) {
        totalSeries->attachAxis(axisX);
        totalSeries->attachAxis(axisY);
        
        // Enable hover events for total series
        totalSeries->setPointsVisible(false);
        connect(totalSeries, &QLineSeries::hovered, this, [this, totalSeries](const QPointF &point, bool state) {
            if (state) {
                QDateTime dateTime = QDateTime::fromMSecsSinceEpoch(point.x());
                QString tooltip = QString("%1\n%2\n$%3")
                    .arg(totalSeries->name())
                    .arg(dateTime.date().toString("MMM dd, yyyy"))
                    .arg(formatCurrency(point.y()));
                chart->setToolTip(tooltip);
            }
        });
    }
    
    // Set chart title
    QString title;
    if (showAllAccounts) {
        title = "Balance Over Time (All Accounts)";
    } else if (selectedAccounts.size() == 1) {
        title = QString("Balance Over Time (%1)").arg(selectedAccounts.first());
    } else {
        title = QString("Balance Over Time (%1 Accounts)").arg(selectedAccounts.size());
    }
    chart->setTitle(title);
    
    chart->legend()->setVisible(true);
    chart->legend()->setAlignment(Qt::AlignBottom);
}

void CashFlow::calculateAndDisplayBalance() {
    QList<Transaction> allTransactions = getAllTransactionsInRange();
    
    // Calculate starting balance from all transactions before the date range
    QDate startDate = ui->dateFromEdit->date();
    QStringList selectedAccounts = ui->accountFilterCombo->getSelectedItems();
    bool showAllAccounts = ui->accountFilterCombo->allSelected();
    
    // Get transactions before the visible range
    QList<Transaction> priorTransactions = database->getTransactions(QDate(1900, 1, 1), startDate.addDays(-1));
    
    // Filter by selected accounts if applicable
    if (!showAllAccounts && !selectedAccounts.isEmpty()) {
        QList<Transaction> filtered;
        for (const Transaction &t : priorTransactions) {
            if (selectedAccounts.contains(t.account)) {
                filtered.append(t);
            }
        }
        priorTransactions = filtered;
    }
    
    // Calculate starting balance from prior transactions
    double calculatedStartingBalance = 0.0;
    for (const Transaction &t : priorTransactions) {
        calculatedStartingBalance += t.amount;
    }
    
    double endBalance = calculatedStartingBalance;
    for (const Transaction &t : allTransactions) {
        endBalance += t.amount;
    }
    
    ui->startBalanceLabel->setText(QString("Starting Balance: $%1").arg(formatCurrency(calculatedStartingBalance)));
    ui->endBalanceLabel->setText(QString("Ending Balance: $%1").arg(formatCurrency(endBalance)));
    
    // Color code the ending balance
    if (endBalance < 0) {
        ui->endBalanceLabel->setStyleSheet("font-weight: bold; font-size: 12pt; color: red;");
    } else if (endBalance < 1000) {
        ui->endBalanceLabel->setStyleSheet("font-weight: bold; font-size: 12pt; color: orange;");
    } else {
        ui->endBalanceLabel->setStyleSheet("font-weight: bold; font-size: 12pt; color: green;");
    }
}

QList<Transaction> CashFlow::getAllTransactionsInRange() {
    QDate startDate = ui->dateFromEdit->date();
    QDate endDate = ui->dateToEdit->date();
    QStringList selectedAccounts = ui->accountFilterCombo->getSelectedItems();
    int recurringFilter = ui->recurringFilterCombo->currentData().toInt();
    QString searchText = ui->searchEdit->text().trimmed().toLower();
    
    // Get all transactions from database (includes both actual and projected)
    QList<Transaction> allTransactions = database->getTransactions(startDate, endDate);
    
    // Filter by selected accounts
    if (!selectedAccounts.isEmpty() && !ui->accountFilterCombo->allSelected()) {
        QList<Transaction> filtered;
        for (const Transaction &t : allTransactions) {
            if (selectedAccounts.contains(t.account)) {
                filtered.append(t);
            }
        }
        allTransactions = filtered;
    }
    
    // Filter by recurring rule if selected
    if (recurringFilter != -1) {
        QList<Transaction> filtered;
        for (const Transaction &t : allTransactions) {
            if (t.recurringId == recurringFilter) {
                filtered.append(t);
            }
        }
        allTransactions = filtered;
    }
    
    // Filter by search text if present
    if (!searchText.isEmpty()) {
        QList<Transaction> filtered;
        for (const Transaction &t : allTransactions) {
            if (t.account.toLower().contains(searchText) ||
                t.category.toLower().contains(searchText) ||
                t.description.toLower().contains(searchText) ||
                QString::number(t.amount).contains(searchText)) {
                filtered.append(t);
            }
        }
        allTransactions = filtered;
    }
    
    // Sort by date, then reconciliation always last, then by sort_order, then credits before debits
    std::sort(allTransactions.begin(), allTransactions.end(), 
              [](const Transaction &a, const Transaction &b) {
                  if (a.date != b.date) return a.date < b.date;
                  
                  // Reconciliation transactions always sort LAST on their date
                  if (a.type == TransactionType::Reconciliation && b.type != TransactionType::Reconciliation) return false;
                  if (a.type != TransactionType::Reconciliation && b.type == TransactionType::Reconciliation) return true;
                  
                  if (a.sortOrder != b.sortOrder) return a.sortOrder < b.sortOrder;
                  // Credits (positive amounts) before debits (negative amounts)
                  return a.amount > b.amount;
              });
    
    return allTransactions;
}

void CashFlow::onDateRangeChanged() {
    // Save date range to settings
    database->setSetting("date_from", ui->dateFromEdit->date().toString(Qt::ISODate));
    database->setSetting("date_to", ui->dateToEdit->date().toString(Qt::ISODate));
    
    refreshView();
}

void CashFlow::onJumpToToday() {
    QDate today = QDate::currentDate();
    QDate currentFrom = ui->dateFromEdit->date();
    QDate currentTo = ui->dateToEdit->date();
    bool rangeChanged = false;

    if (today < currentFrom || today > currentTo) {
        PeriodType periodType = static_cast<PeriodType>(ui->periodCombo->currentIndex());
        QDate periodStart = getPeriodStart(today, periodType);
        QDate periodEnd = getPeriodEnd(today, periodType);

        QSignalBlocker blockFrom(ui->dateFromEdit);
        QSignalBlocker blockTo(ui->dateToEdit);

        ui->dateFromEdit->setDate(periodStart);
        ui->dateToEdit->setDate(periodEnd);
        rangeChanged = true;
    }

    if (rangeChanged) {
        onDateRangeChanged();
    }

    jumpToDateInTable(today);
}

void CashFlow::jumpToDateInTable(const QDate &date) {
    qDebug() << "[JumpToToday] Target date:" << date.toString(Qt::ISODate);
    int rowCount = ui->transactionTable->rowCount();
    int targetRow = -1;
    int bestDistance = std::numeric_limits<int>::max();

    for (int row = 0; row < rowCount; ++row) {
        QTableWidgetItem *dateItem = ui->transactionTable->item(row, 0);
        if (!dateItem) continue;
        if (dateItem->data(Qt::UserRole).toInt() == -1) continue; // Skip period rows

        QString rawDateText = dateItem->text();
        QString isoDateText = dateItem->data(Qt::UserRole + 2).toString();
        QDate rowDate = QDate::fromString(isoDateText, Qt::ISODate);
        if (!rowDate.isValid()) {
            qDebug() << "[JumpToToday] Skipping row" << row << "invalid date text:" << rawDateText;
            continue;
        }

        int distance = qAbs(rowDate.daysTo(date));
        if (distance < bestDistance) {
            bestDistance = distance;
            targetRow = row;
            qDebug() << "[JumpToToday] Candidate row" << row
                     << "date" << rowDate.toString(Qt::ISODate)
                     << "distance" << distance;
            if (distance == 0) {
                break;
            }
        }
    }

    if (targetRow != -1) {
        qDebug() << "[JumpToToday] Selected row" << targetRow
                 << "distance" << bestDistance;
        ui->transactionTable->setCurrentCell(targetRow, 0);
        ui->transactionTable->scrollToItem(ui->transactionTable->item(targetRow, 0), QAbstractItemView::PositionAtCenter);
    } else {
        qDebug() << "[JumpToToday] No valid transaction rows found.";
    }
}

void CashFlow::onPeriodChanged() {
    refreshView();
}

void CashFlow::onTransactionSelected() {
    QList<QTableWidgetItem*> selected = ui->transactionTable->selectedItems();
    if (selected.isEmpty()) {
        // No selection - clear the form to create new transaction
        clearTransactionEntry();
        return;
    }
    
    int row = selected[0]->row();
    int id = ui->transactionTable->item(row, 0)->data(Qt::UserRole).toInt();
    
    // Load from the current in-range transaction list (includes projections)
    QList<Transaction> allTrans = getAllTransactionsInRange();
    for (const Transaction &t : allTrans) {
        if (t.id == id) {
            currentTransactionId = id;
            loadTransactionToEntry(t);
            QString typeLabel = (t.type == TransactionType::Estimated) ? "Estimated" : "Actual";
            ui->entryStatusLabel->setText(QString("Editing ID: %1 (%2)").arg(id).arg(typeLabel));
            return;
        }
    }
}

void CashFlow::onSaveTransaction() {
    // Skip validation if this is a new empty transaction being auto-saved
    bool isEmptyNew = (currentTransactionId == -1 && 
                       ui->entryAccountCombo->currentText().isEmpty() &&
                       ui->entryAmountSpin->value() == 0.0);
    
    if (!isEmptyNew) {
        // Validate required fields
        if (ui->entryAccountCombo->currentText().isEmpty()) {
            QMessageBox::warning(this, "Required Field", "Account is required.");
            return;
        }
        if (ui->entryAmountSpin->value() == 0.0) {
            QMessageBox::warning(this, "Required Field", "Amount cannot be zero.");
            return;
        }
    }
    
    Transaction t;
    t.id = currentTransactionId;
    t.date = ui->entryDateEdit->date();
    t.amount = ui->entryAmountSpin->value();
    t.account = ui->entryAccountCombo->currentText();
    t.category = ui->entryCategoryCombo->currentText();
    t.description = ui->entryDescriptionEdit->text();
    
    QString typeText = ui->entryTypeCombo->currentText();
    if (typeText == "Actual") t.type = TransactionType::Actual;
    else if (typeText == "Reconciliation") t.type = TransactionType::Reconciliation;
    else t.type = TransactionType::Estimated;
    
    // If this is a reconciliation checkpoint, calculate the variance
    if (t.type == TransactionType::Reconciliation) {
        // amount field holds the expected balance from bank
        t.expectedBalance = t.amount;
        // Calculate what we think the balance should be
        t.calculatedBalance = calculateBalanceUpTo(t.date, t.account);
        // Don't overwrite user's description - it's saved as-is
        // Category is irrelevant for reconciliation
        t.category = "";
        t.amount = 0; // Reconciliation rows don't affect balance
    }
    
    // Check if user manually linked to a recurring rule
    int manualRuleId = ui->entryRecurringCombo->currentData().toInt();
    QString manualOccurrenceKey = ui->entryOccurrenceEdit->text().trimmed();
    
    if (manualRuleId != -1 && !manualOccurrenceKey.isEmpty()) {
        // User manually linked to recurring rule
        t.recurringId = manualRuleId;
        t.occurrenceKey = manualOccurrenceKey;
        t.reconciled = true;
        // Try to get expected amount from the rule
        QList<RecurringRule> rules = database->getAllRecurringRules();
        for (const RecurringRule &rule : rules) {
            if (rule.id == manualRuleId) {
                t.expectedAmount = rule.amount;
                break;
            }
        }
    } else if (currentTransactionId != -1) {
        // Editing existing transaction - preserve existing reconciliation fields
        QList<Transaction> allTrans = database->getAllTransactions();
        for (const Transaction &existing : allTrans) {
            if (existing.id == currentTransactionId) {
                t.recurringId = existing.recurringId;
                t.occurrenceKey = existing.occurrenceKey;
                t.expectedAmount = existing.expectedAmount;
                t.expectedDate = existing.expectedDate;
                t.reconciled = existing.reconciled;
                
                // If user manually edited an Estimated transaction from a recurring rule, mark as reconciled
                // This prevents regeneration from overwriting the edited values
                if (existing.type == TransactionType::Estimated && 
                    existing.recurringId != -1 && 
                    (t.amount != existing.amount || 
                     t.date != existing.date || 
                     t.account != existing.account ||
                     t.category != existing.category ||
                     t.description != existing.description)) {
                    // User modified the transaction - keep the link but mark as reconciled
                    t.reconciled = true; // Mark as reconciled so it won't be regenerated
                }
                
                // If user is converting Estimated to Actual, mark as reconciled
                if (existing.type == TransactionType::Estimated && t.type == TransactionType::Actual) {
                    t.reconciled = true;
                    if (t.expectedAmount == 0) {
                        t.expectedAmount = existing.amount; // Store original projected amount
                    }
                    if (!t.expectedDate.isValid()) {
                        t.expectedDate = existing.date; // Store original projected date
                    }
                }
                break;
            }
        }
    } else {
        // New manual transaction with no recurring link
        t.recurringId = -1;
        t.reconciled = false;
    }
    
    bool success;
    if (currentTransactionId == -1) {
        // New transaction
        success = database->addTransaction(t);
        if (success) {
            // Get the new ID and update currentTransactionId
            QList<Transaction> allTrans = database->getAllTransactions();
            if (!allTrans.isEmpty()) {
                currentTransactionId = allTrans.last().id;
            }
        }
    } else {
        // Update existing
        success = database->updateTransaction(t);
    }
    
    if (success) {
        // Recalculate all reconciliation checkpoints since balances may have changed
        recalculateAllReconciliations();
        
        ui->entryStatusLabel->setText("Saved!");
        refreshView();
    } else {
        QMessageBox::critical(this, "Error", "Failed to save: " + database->lastError());
    }
}

void CashFlow::onNewTransaction() {
    clearTransactionEntry();
    ui->entryDateEdit->setDate(QDate::currentDate());
    ui->entryDateEdit->setFocus();
}

void CashFlow::onDeleteTransaction() {
    if (currentTransactionId == -1) {
        QMessageBox::warning(this, "No Selection", "Please select a transaction to delete.");
        return;
    }
    
    // Remember current row position before delete
    int currentRow = ui->transactionTable->currentRow();
    
    if (QMessageBox::question(this, "Confirm Delete", 
        QString("Delete transaction ID %1?").arg(currentTransactionId)) == QMessageBox::Yes) {
        if (database->deleteTransaction(currentTransactionId)) {
            // Recalculate all reconciliation checkpoints since balances may have changed
            recalculateAllReconciliations();
            clearTransactionEntry();
            refreshView();
            
            // Restore selection to next row (or previous if we deleted the last row)
            int rowCount = ui->transactionTable->rowCount();
            if (rowCount > 0) {
                int newRow = qMin(currentRow, rowCount - 1);
                ui->transactionTable->selectRow(newRow);
            }
        } else {
            QMessageBox::critical(this, "Error", "Failed to delete: " + database->lastError());
        }
    }
}

void CashFlow::onCreateAdjustment() {
    // Get the current reconciliation details
    if (currentTransactionId == -1) return;
    
    QList<Transaction> allTrans = database->getAllTransactions();
    Transaction recon;
    bool found = false;
    
    for (const Transaction &t : allTrans) {
        if (t.id == currentTransactionId && t.type == TransactionType::Reconciliation) {
            recon = t;
            found = true;
            break;
        }
    }
    
    if (!found) return;
    
    double variance = recon.expectedBalance - recon.calculatedBalance;
    
    QString msg = QString("Create an adjustment transaction for $%1 to match the bank balance?\n\n"
                         "Bank says: $%2\n"
                         "We calculate: $%3\n"
                         "Adjustment needed: $%4")
                         .arg(formatCurrency(qAbs(variance)))
                         .arg(formatCurrency(recon.expectedBalance))
                         .arg(formatCurrency(recon.calculatedBalance))
                         .arg(formatCurrency(variance));
    
    if (QMessageBox::question(this, "Create Adjustment", msg) == QMessageBox::Yes) {
        // Create adjustment transaction on the same date, same account
        Transaction adj;
        adj.id = -1;
        adj.date = recon.date;
        adj.amount = variance;
        adj.account = recon.account;
        adj.category = "Adjustment";
        adj.description = "Balance adjustment to match bank statement";
        adj.type = TransactionType::Actual;
        adj.recurringId = -1;
        adj.reconciled = false;
        adj.sortOrder = 0; // Doesn't matter - reconciliation always sorts last
        
        if (database->addTransaction(adj)) {
            // Recalculate reconciliations and refresh
            recalculateAllReconciliations();
            refreshView();
            QMessageBox::information(this, "Success", "Adjustment transaction created.");
            ui->adjustmentBtn->setVisible(false); // Hide button since we're now balanced
        } else {
            QMessageBox::critical(this, "Error", "Failed to create adjustment: " + database->lastError());
        }
    }
}

void CashFlow::onRecurringSelected() {
    QList<QTableWidgetItem*> selected = ui->recurringTable->selectedItems();
    if (selected.isEmpty()) {
        return;
    }
    
    int row = selected[0]->row();
    int id = ui->recurringTable->item(row, 0)->text().toInt();
    
    QList<RecurringRule> rules = database->getAllRecurringRules();
    for (const RecurringRule &r : rules) {
        if (r.id == id) {
            loadRecurringToEntry(r);
            currentRecurringId = id;
            return;
        }
    }
}

void CashFlow::onSaveRecurring() {
    RecurringRule r;
    r.id = currentRecurringId;
    r.name = ui->recurringNameEdit->text();
    r.startDate = ui->recurringStartDateEdit->date();
    r.endDate = ui->recurringEndDateEdit->date();
    // If end date is the minimum date (special value), treat as null
    if (r.endDate == ui->recurringEndDateEdit->minimumDate()) {
        r.endDate = QDate();
    }
    r.amount = ui->recurringAmountSpin->value();
    r.account = ui->recurringAccountCombo->currentText();
    r.category = ui->recurringCategoryCombo->currentText();
    r.description = ui->recurringDescriptionEdit->text();
    r.occurrences = -1; // Default to infinite
    
    QString freqStr = ui->recurringFrequencyCombo->currentText();
    if (freqStr == "Daily") {
        r.frequency = RecurrenceFrequency::Daily;
        r.dayOfWeek = -1;
        r.dayOfMonth = -1;
    }
    else if (freqStr == "Weekly") {
        r.frequency = RecurrenceFrequency::Weekly;
        r.dayOfWeek = r.startDate.dayOfWeek(); // Use the day of week from start date
        r.dayOfMonth = -1;
    }
    else if (freqStr == "Bi-Weekly") {
        r.frequency = RecurrenceFrequency::BiWeekly;
        r.dayOfWeek = r.startDate.dayOfWeek(); // Use the day of week from start date
        r.dayOfMonth = -1;
    }
    else if (freqStr == "Monthly") {
        r.frequency = RecurrenceFrequency::Monthly;
        r.dayOfWeek = -1;
        r.dayOfMonth = r.startDate.day(); // Use the day of month from start date
    }
    else if (freqStr == "Yearly") {
        r.frequency = RecurrenceFrequency::Yearly;
        r.dayOfWeek = -1;
        r.dayOfMonth = r.startDate.day();
    }
    
    bool success;
    if (currentRecurringId == -1) {
        success = database->addRecurringRule(r);
        if (success) {
            // Get the newly created rule ID to regenerate projections
            QList<RecurringRule> rules = database->getAllRecurringRules();
            if (!rules.isEmpty()) {
                RecurringRule newRule = rules.last();
                database->regenerateProjectionsForRule(newRule);
            }
        }
    } else {
        success = database->updateRecurringRule(r);
        if (success) {
            // Regenerate projections for updated rule
            database->regenerateProjectionsForRule(r);
        }
    }
    
    if (success) {
        refreshView();
        QMessageBox::information(this, "Success", "Recurring rule saved. Projections updated automatically.");
    } else {
        QMessageBox::critical(this, "Error", "Failed to save: " + database->lastError());
    }
}

void CashFlow::onNewRecurring() {
    clearRecurringEntry();
    ui->recurringStartDateEdit->setDate(QDate::currentDate());
    ui->recurringNameEdit->setFocus();
}

void CashFlow::onDeleteRecurring() {
    if (currentRecurringId == -1) {
        QMessageBox::warning(this, "No Selection", "Please select a recurring rule to delete.");
        return;
    }
    
    if (QMessageBox::question(this, "Confirm Delete", 
        "Delete this recurring rule and all its future projections?") == QMessageBox::Yes) {
        // Delete future projections first
        database->deleteProjectionsForRule(currentRecurringId);
        // Then delete the rule
        if (database->deleteRecurringRule(currentRecurringId)) {
            clearRecurringEntry();
            refreshView();
        } else {
            QMessageBox::critical(this, "Error", "Failed to delete: " + database->lastError());
        }
    }
}

void CashFlow::clearTransactionEntry() {
    currentTransactionId = -1;
    ui->entryDateEdit->setDate(QDate::currentDate());
    ui->entryAmountSpin->setValue(0.0);
    ui->entryAccountCombo->setCurrentText("");
    ui->entryCategoryCombo->setCurrentText("");
    ui->entryDescriptionEdit->clear();
    ui->entryTypeCombo->setCurrentIndex(0);
    ui->entryRecurringCombo->setCurrentIndex(0); // (None)
    ui->entryOccurrenceEdit->clear();
    ui->entryOccurrenceEdit->setEnabled(false);
    ui->entryStatusLabel->setText("(New transaction)");
    updateAmountColors();
}

void CashFlow::loadTransactionToEntry(const Transaction &t) {
    // Block signals to prevent auto-save while loading
    ui->entryDateEdit->blockSignals(true);
    ui->entryAmountSpin->blockSignals(true);
    ui->entryAccountCombo->blockSignals(true);
    ui->entryCategoryCombo->blockSignals(true);
    ui->entryDescriptionEdit->blockSignals(true);
    ui->entryTypeCombo->blockSignals(true);
    ui->entryRecurringCombo->blockSignals(true);
    ui->entryOccurrenceEdit->blockSignals(true);
    
    ui->entryDateEdit->setDate(t.date);
    
    // For reconciliation rows, load the expected balance into amount field
    double loadAmount = (t.type == TransactionType::Reconciliation) ? t.expectedBalance : t.amount;
    ui->entryAmountSpin->setValue(loadAmount);
    
    ui->entryAccountCombo->setCurrentText(t.account);
    ui->entryCategoryCombo->setCurrentText(t.category);
    ui->entryDescriptionEdit->setText(t.description);
    
    int typeIndex = 0; // Estimated
    if (t.type == TransactionType::Actual) typeIndex = 1;
    else if (t.type == TransactionType::Reconciliation) typeIndex = 2;
    ui->entryTypeCombo->setCurrentIndex(typeIndex);
    
    // Set recurring rule link if present
    if (t.recurringId != -1) {
        // Find and select the rule in combo
        for (int i = 0; i < ui->entryRecurringCombo->count(); i++) {
            if (ui->entryRecurringCombo->itemData(i).toInt() == t.recurringId) {
                ui->entryRecurringCombo->setCurrentIndex(i);
                break;
            }
        }
        ui->entryOccurrenceEdit->setText(t.occurrenceKey);
        ui->entryOccurrenceEdit->setEnabled(true);
    } else {
        ui->entryRecurringCombo->setCurrentIndex(0); // (None)
        ui->entryOccurrenceEdit->clear();
        ui->entryOccurrenceEdit->setEnabled(false);
    }
    
    ui->entryDateEdit->blockSignals(false);
    ui->entryAmountSpin->blockSignals(false);
    ui->entryAccountCombo->blockSignals(false);
    ui->entryCategoryCombo->blockSignals(false);
    ui->entryDescriptionEdit->blockSignals(false);
    ui->entryTypeCombo->blockSignals(false);
    ui->entryRecurringCombo->blockSignals(false);
    ui->entryOccurrenceEdit->blockSignals(false);
    
    // Show adjustment button if this is a reconciliation that's out of balance
    if (t.type == TransactionType::Reconciliation) {
        double variance = qAbs(t.expectedBalance - t.calculatedBalance);
        ui->adjustmentBtn->setVisible(variance >= 0.01);
    } else {
        ui->adjustmentBtn->setVisible(false);
    }
    
    updateAmountColors();
}

void CashFlow::clearRecurringEntry() {
    currentRecurringId = -1;
    ui->recurringNameEdit->clear();
    ui->recurringStartDateEdit->setDate(QDate::currentDate());
    ui->recurringEndDateEdit->setDate(ui->recurringEndDateEdit->minimumDate()); // Set to special value (Never)
    ui->recurringAmountSpin->setValue(0.0);
    ui->recurringAccountCombo->setCurrentText("");
    ui->recurringCategoryCombo->setCurrentText("");
    ui->recurringDescriptionEdit->clear();
    ui->recurringFrequencyCombo->setCurrentIndex(3); // Default to Monthly
    updateAmountColors();
}

void CashFlow::loadRecurringToEntry(const RecurringRule &r) {
    ui->recurringNameEdit->setText(r.name);
    ui->recurringStartDateEdit->setDate(r.startDate);
    if (r.endDate.isValid()) {
        ui->recurringEndDateEdit->setDate(r.endDate);
    } else {
        ui->recurringEndDateEdit->setDate(ui->recurringEndDateEdit->minimumDate()); // Show "Never"
    }
    ui->recurringAmountSpin->setValue(r.amount);
    ui->recurringAccountCombo->setCurrentText(r.account);
    ui->recurringCategoryCombo->setCurrentText(r.category);
    ui->recurringDescriptionEdit->setText(r.description);
    
    int freqIndex = 3; // Default monthly
    switch (r.frequency) {
        case RecurrenceFrequency::Daily: freqIndex = 0; break;
        case RecurrenceFrequency::Weekly: freqIndex = 1; break;
        case RecurrenceFrequency::BiWeekly: freqIndex = 2; break;
        case RecurrenceFrequency::Monthly: freqIndex = 3; break;
        case RecurrenceFrequency::Yearly: freqIndex = 4; break;
        default: break;
    }
    ui->recurringFrequencyCombo->setCurrentIndex(freqIndex);
    updateAmountColors();
}

void CashFlow::updateAmountColors() {
    // Color code transaction amount
    if (ui->entryAmountSpin->value() < 0) {
        ui->entryAmountSpin->setStyleSheet("QDoubleSpinBox { color: rgb(200, 0, 0); font-weight: bold; }");
    } else if (ui->entryAmountSpin->value() > 0) {
        ui->entryAmountSpin->setStyleSheet("QDoubleSpinBox { color: rgb(0, 150, 0); font-weight: bold; }");
    } else {
        ui->entryAmountSpin->setStyleSheet("");
    }
    
    // Color code recurring amount
    if (ui->recurringAmountSpin->value() < 0) {
        ui->recurringAmountSpin->setStyleSheet("QDoubleSpinBox { color: rgb(200, 0, 0); font-weight: bold; }");
    } else if (ui->recurringAmountSpin->value() > 0) {
        ui->recurringAmountSpin->setStyleSheet("QDoubleSpinBox { color: rgb(0, 150, 0); font-weight: bold; }");
    } else {
        ui->recurringAmountSpin->setStyleSheet("");
    }
}

void CashFlow::loadSettings() {
    // Load settings from database
    QString fontFamily = database->getSetting("amount_font", "Courier New");
    int fontSize = database->getSetting("amount_font_size", "10").toInt();
    int defaultPeriod = database->getSetting("default_period", "2").toInt();
    bool showAccountBalances = database->getSetting("show_account_balances", "0").toInt();
    weekStartDay = database->getSetting("week_start_day", "1").toInt();
    
    // Load date range
    QDate today = QDate::currentDate();
    QString dateFromStr = database->getSetting("date_from", "");
    QString dateToStr = database->getSetting("date_to", "");
    
    QDate dateFrom = dateFromStr.isEmpty() ? QDate(today.year(), today.month(), 1) : QDate::fromString(dateFromStr, Qt::ISODate);
    QDate dateTo = dateToStr.isEmpty() ? today.addMonths(3) : QDate::fromString(dateToStr, Qt::ISODate);
    
    // Apply to member variables and main UI
    currentAmountFont = QFont(fontFamily, fontSize);
    ui->periodCombo->setCurrentIndex(defaultPeriod);
    ui->showAccountBalancesCheck->setChecked(showAccountBalances);
    ui->dateFromEdit->setDate(dateFrom);
    ui->dateToEdit->setDate(dateTo);
}

QString CashFlow::formatCurrency(double amount) const {
    QLocale locale;
    return locale.toString(amount, 'f', 2);
}

double CashFlow::calculateBalanceUpTo(const QDate &date, const QString &account) {
    QList<Transaction> allTrans = database->getAllTransactions();
    double balance = 0.0;
    
    // Sort by date to ensure proper ordering
    std::sort(allTrans.begin(), allTrans.end(), [](const Transaction &a, const Transaction &b) {
        if (a.date != b.date) return a.date < b.date;
        return a.sortOrder < b.sortOrder;
    });
    
    for (const Transaction &t : allTrans) {
        if (t.date > date) continue;
        if (!account.isEmpty() && t.account != account) continue;
        if (t.type == TransactionType::Reconciliation) continue; // Don't count reconciliation rows
        
        balance += t.amount;
    }
    
    return balance;
}

void CashFlow::recalculateAllReconciliations() {
    QList<Transaction> allTrans = database->getAllTransactions();
    
    for (Transaction &t : allTrans) {
        if (t.type == TransactionType::Reconciliation) {
            // Recalculate the calculated balance
            t.calculatedBalance = calculateBalanceUpTo(t.date, t.account);
            // Update in database
            database->updateTransaction(t);
        }
    }
}

bool CashFlow::openDatabase(const QString &filePath) {
    if (database->open(filePath)) {
        currentFilePath = filePath;
        QFileInfo fileInfo(filePath);
        setWindowTitle(QString("CashFlo - %1").arg(fileInfo.fileName()));
        
        loadSettings();
        
        clearTransactionEntry();
        clearRecurringEntry();
        onJumpToToday();
        
        return true;
    }
    return false;
}

void CashFlow::onNewFile() {
    QString defaultDir = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QDir().mkpath(defaultDir);
    
    QString fileName = QFileDialog::getSaveFileName(
        this,
        "New CashFlo File",
        defaultDir,
        "CashFlo Files (*.cashflo.sqlite);;All Files (*)"
    );
    
    if (fileName.isEmpty()) {
        return;
    }
    
    // Ensure .cashflo.sqlite extension
    if (!fileName.endsWith(".cashflo.sqlite", Qt::CaseInsensitive)) {
        fileName += ".cashflo.sqlite";
    }
    
    // Remove file if it exists
    if (QFile::exists(fileName)) {
        QFile::remove(fileName);
    }
    
    // Close current database
    delete database;
    database = new Database();
    
    if (!openDatabase(fileName)) {
        QMessageBox::critical(this, "Error", "Failed to create new file: " + database->lastError());
    }
}

void CashFlow::onOpenFile() {
    QString defaultDir = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    
    QString fileName = QFileDialog::getOpenFileName(
        this,
        "Open CashFlo File",
        defaultDir,
        "CashFlo Files (*.cashflo.sqlite);;All Files (*)"
    );
    
    if (fileName.isEmpty()) {
        return;
    }
    
    // Close current database
    delete database;
    database = new Database();
    
    if (!openDatabase(fileName)) {
        QMessageBox::critical(this, "Error", "Failed to open file: " + database->lastError());
    }
}

void CashFlow::onSaveAs() {
    if (currentFilePath.isEmpty()) {
        QMessageBox::warning(this, "No File Open", "Please open or create a file first.");
        return;
    }
    
    QString defaultDir = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    QDir().mkpath(defaultDir);
    
    QString fileName = QFileDialog::getSaveFileName(
        this,
        "Save As",
        defaultDir,
        "CashFlo Files (*.cashflo.sqlite);;All Files (*)"
    );
    
    if (fileName.isEmpty()) {
        return;
    }
    
    // Ensure .cashflo.sqlite extension
    if (!fileName.endsWith(".cashflo.sqlite", Qt::CaseInsensitive)) {
        fileName += ".cashflo.sqlite";
    }
    
    // Don't allow overwriting the current file
    if (QFileInfo(fileName).canonicalFilePath() == QFileInfo(currentFilePath).canonicalFilePath()) {
        QMessageBox::warning(this, "Invalid Operation", "Cannot save as the same file. Use a different name.");
        return;
    }
    
    // Store the current file path
    QString oldFilePath = currentFilePath;
    
    // Close database to unlock the file
    delete database;
    database = new Database();
    
    // Copy the file
    if (QFile::exists(fileName)) {
        QFile::remove(fileName);
    }
    
    if (!QFile::copy(oldFilePath, fileName)) {
        QMessageBox::critical(this, "Error", "Failed to copy file.");
        // Reopen the original file
        openDatabase(oldFilePath);
        return;
    }
    
    // Open the new copy
    if (!openDatabase(fileName)) {
        QMessageBox::critical(this, "Error", "Failed to open copied file: " + database->lastError());
        // Try to reopen the original
        openDatabase(oldFilePath);
    } else {
        QMessageBox::information(this, "Success", "File saved successfully.");
    }
}

void CashFlow::onExportCSV() {
    QString defaultDir = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    
    QString fileName = QFileDialog::getSaveFileName(
        this,
        "Export to CSV",
        defaultDir,
        "CSV Files (*.csv);;All Files (*)"
    );
    
    if (fileName.isEmpty()) {
        return;
    }
    
    // Ensure .csv extension
    if (!fileName.endsWith(".csv", Qt::CaseInsensitive)) {
        fileName += ".csv";
    }
    
    QFile file(fileName);
    if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
        QMessageBox::critical(this, "Error", "Failed to open file for writing.");
        return;
    }
    
    QTextStream out(&file);
    
    // Write headers
    int columnCount = ui->transactionTable->columnCount();
    QStringList headers;
    for (int col = 0; col < columnCount; ++col) {
        QString header = ui->transactionTable->horizontalHeaderItem(col)->text();
        // Escape quotes and wrap in quotes if needed
        if (header.contains(',') || header.contains('"') || header.contains('\n')) {
            header.replace("\"", "\"\"");
            header = "\"" + header + "\"";
        }
        headers.append(header);
    }
    out << headers.join(",") << "\n";
    
    // Write rows
    int rowCount = ui->transactionTable->rowCount();
    for (int row = 0; row < rowCount; ++row) {
        QStringList values;
        for (int col = 0; col < columnCount; ++col) {
            QString value;
            QTableWidgetItem *item = ui->transactionTable->item(row, col);
            if (item) {
                value = item->text();
            }
            
            // Escape quotes and wrap in quotes if needed
            if (value.contains(',') || value.contains('"') || value.contains('\n')) {
                value.replace("\"", "\"\"");
                value = "\"" + value + "\"";
            }
            values.append(value);
        }
        out << values.join(",") << "\n";
    }
    
    file.close();
    QMessageBox::information(this, "Success", QString("Exported %1 rows to CSV.").arg(rowCount));
}

void CashFlow::onImportCSV() {
    ImportDialog dialog(database, this);
    if (dialog.exec() == QDialog::Accepted) {
        QList<Transaction> imported = dialog.getImportedTransactions();
        for (const Transaction &t : imported) {
            database->addTransaction(t);
        }
        refreshView();
        QMessageBox::information(this, "Success", QString("Imported %1 transactions.").arg(imported.size()));
    }
}

void CashFlow::onQuit() {
    QApplication::quit();
}

void CashFlow::onPreferences() {
    SettingsDialog dialog(database, this);
    if (dialog.exec() == QDialog::Accepted) {
        // Reload settings
        currentAmountFont = dialog.getCurrentAmountFont();
        weekStartDay = dialog.getWeekStartDay();
        loadSettings();
        refreshView();
    }
}

void CashFlow::onSearchTextChanged() {
    refreshTransactionTable();
}

void CashFlow::onCollapseAll() {
    // Mark all periods as collapsed for current period type
    PeriodType periodType = static_cast<PeriodType>(ui->periodCombo->currentIndex());
    int rowCount = ui->transactionTable->rowCount();
    for (int row = 0; row < rowCount; ++row) {
        QTableWidgetItem *item = ui->transactionTable->item(row, 0);
        if (item && item->data(Qt::UserRole).toInt() == -1) {
            // This is a period end row
            int periodId = item->data(Qt::UserRole + 1).toInt();
            collapsedPeriods[periodType].insert(periodId);
        }
    }
    refreshTransactionTable();
}

void CashFlow::onExpandAll() {
    PeriodType periodType = static_cast<PeriodType>(ui->periodCombo->currentIndex());
    collapsedPeriods[periodType].clear();
    refreshTransactionTable();
}

void CashFlow::onTransactionTableDoubleClicked(int row, int column) {
    Q_UNUSED(column);
    QTableWidgetItem *item = ui->transactionTable->item(row, 0);
    if (item && item->data(Qt::UserRole).toInt() == -1) {
        // This is a period end row - toggle collapse state
        PeriodType periodType = static_cast<PeriodType>(ui->periodCombo->currentIndex());
        int periodId = item->data(Qt::UserRole + 1).toInt();
        if (collapsedPeriods[periodType].contains(periodId)) {
            collapsedPeriods[periodType].remove(periodId);
        } else {
            collapsedPeriods[periodType].insert(periodId);
        }
        refreshTransactionTable();
    }
}

void CashFlow::populateRecurringRulesCombo() {
    ui->entryRecurringCombo->clear();
    ui->entryRecurringCombo->addItem("(None)", -1);
    
    // Also populate the recurring filter combo
    ui->recurringFilterCombo->blockSignals(true);
    QString currentRecurringFilter = ui->recurringFilterCombo->currentText();
    ui->recurringFilterCombo->clear();
    ui->recurringFilterCombo->addItem("All Transactions", -1);
    
    QList<RecurringRule> rules = database->getAllRecurringRules();
    for (const RecurringRule &rule : rules) {
        QString label = QString("%1 (%2)").arg(rule.name).arg(
            rule.frequency == RecurrenceFrequency::Daily ? "Daily" :
            rule.frequency == RecurrenceFrequency::Weekly ? "Weekly" :
            rule.frequency == RecurrenceFrequency::BiWeekly ? "Bi-weekly" :
            rule.frequency == RecurrenceFrequency::Monthly ? "Monthly" :
            rule.frequency == RecurrenceFrequency::Yearly ? "Yearly" : "Unknown"
        );
        ui->entryRecurringCombo->addItem(label, rule.id);
        ui->recurringFilterCombo->addItem(label, rule.id);
    }
    
    // Restore previous recurring filter selection if possible
    int filterIndex = ui->recurringFilterCombo->findText(currentRecurringFilter);
    if (filterIndex >= 0) {
        ui->recurringFilterCombo->setCurrentIndex(filterIndex);
    }
    ui->recurringFilterCombo->blockSignals(false);
}

QString CashFlow::generateOccurrenceKey(const QDate &date, RecurrenceFrequency frequency) const {
    if (frequency == RecurrenceFrequency::Daily) {
        return date.toString("yyyy-MM-dd");
    } else if (frequency == RecurrenceFrequency::Weekly || frequency == RecurrenceFrequency::BiWeekly) {
        return QString("%1-W%2").arg(date.year()).arg(date.weekNumber(), 2, 10, QChar('0'));
    } else if (frequency == RecurrenceFrequency::Monthly) {
        return date.toString("yyyy-MM");
    } else if (frequency == RecurrenceFrequency::Yearly) {
        return QString::number(date.year());
    }
    return QString();
}

void CashFlow::updateOccurrenceKey() {
    int ruleId = ui->entryRecurringCombo->currentData().toInt();
    if (ruleId == -1) {
        ui->entryOccurrenceEdit->clear();
        ui->entryOccurrenceEdit->setEnabled(false);
        return;
    }
    
    ui->entryOccurrenceEdit->setEnabled(true);
    
    // Find the rule to get its frequency
    QList<RecurringRule> rules = database->getAllRecurringRules();
    for (const RecurringRule &rule : rules) {
        if (rule.id == ruleId) {
            QString occurrenceKey = generateOccurrenceKey(ui->entryDateEdit->date(), rule.frequency);
            ui->entryOccurrenceEdit->setText(occurrenceKey);
            break;
        }
    }
}

void CashFlow::onRecurringRuleChanged() {
    updateOccurrenceKey();
}

void CashFlow::onTransactionDateChanged() {
    // Update occurrence key if a recurring rule is selected
    if (ui->entryRecurringCombo->currentData().toInt() != -1) {
        updateOccurrenceKey();
    }
}