// --- Imports ---
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const cors = require('cors');
const multer = require('multer');
const Papa = require('papaparse');
const fs = require('fs');

// --- Config ---
const app = express();
const port = 3000;
const dbFile = './data/simpleledger.db';
const JWT_SECRET = 'your-super-secret-key-change-this!'; // CHANGE THIS
const upload = multer({ dest: 'uploads/' });

// --- Middleware ---
app.use(cors());
app.use(express.json());
// Serve the frontend file
app.use(express.static(__dirname)); 

// --- Database Setup ---
const db = new sqlite3.Database(dbFile, (err) => {
    if (err) {
        console.error(err.message);
    } else {
        console.log('Connected to the SimpleLedger SQLite database.');
        initDb();
    }
});

function initDb() {
    db.serialize(() => {
        db.run(`CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            password TEXT
        )`);
        
        db.run(`CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER,
            name TEXT,
            type TEXT,
            FOREIGN KEY(userId) REFERENCES users(id)
        )`);
        
        db.run(`CREATE TABLE IF NOT EXISTS vendors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER,
            name TEXT UNIQUE,
            FOREIGN KEY(userId) REFERENCES users(id)
        )`);
        
        db.run(`CREATE TABLE IF NOT EXISTS rules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER,
            keyword TEXT,
            category TEXT,
            FOREIGN KEY(userId) REFERENCES users(id)
        )`);
        
        // --- MODIFIED: transactions is now a parent table ---
        db.run(`CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER,
            date TEXT,
            description TEXT,
            amount REAL,
            source TEXT,
            createdAt TEXT,
            reconciliation_status TEXT DEFAULT 'uncategorized',
            FOREIGN KEY(userId) REFERENCES users(id)
        )`);

        // --- NEW: transaction_splits table ---
        db.run(`CREATE TABLE IF NOT EXISTS transaction_splits (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER,
            transactionId INTEGER,
            category TEXT,
            vendorId INTEGER,
            description TEXT,
            amount REAL,
            FOREIGN KEY(userId) REFERENCES users(id),
            FOREIGN KEY(transactionId) REFERENCES transactions(id) ON DELETE CASCADE,
            FOREIGN KEY(vendorId) REFERENCES vendors(id) ON DELETE SET NULL
        )`);
    });
}

// --- Auth Middleware ---
function authenticateToken(req, res, next) {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    
    if (token == null) return res.sendStatus(401); // No token

    jwt.verify(token, JWT_SECRET, (err, user) => {
        if (err) return res.sendStatus(403); // Invalid token
        req.user = user;
        next();
    });
}

// === AUTH ENDPOINTS ===

app.post('/api/register', async (req, res) => {
    // ... (This endpoint is unchanged from the previous version) ...
    const { username, password } = req.body;
    if (!username || !password) {
        return res.status(400).send('Username and password are required.');
    }
    
    try {
        const hashedPassword = await bcrypt.hash(password, 10);
        
        // Use serialize to ensure user is created before categories are added
        db.serialize(() => {
            // 1. Create the user
            db.run('INSERT INTO users (username, password) VALUES (?, ?)', [username, hashedPassword], function(err) {
                if (err) {
                    return res.status(400).send('Username already taken.');
                }
                
                const newUserId = this.lastID;
                
                // 2. Populate default categories for the new user
                const defaultCategories = [
                    { name: 'Income - Gross Sales', type: 'income' },
                    { name: 'Income - Returns', type: 'income' },
                    { name: 'Expense - Uncategorized', type: 'expense' },
                    { name: 'Expense - Advertising', type: 'expense' },
                    { name: 'Expense - Bank Fees', type: 'expense' },
                    { name: 'Expense - Cost of Goods Sold', type: 'expense' },
                    { name: 'Expense - Discounts & Comps', type: 'expense' },
                    { name: 'Expense - Meals & Entertainment', type: 'expense' },
                    { name: 'Expense - Office Supplies', type: 'expense' },
                    { name: 'Expense - Payroll', type: 'expense' },
                    { name: 'Expense - Rent/Lease', type: 'expense' },
                    { name: 'Expense - Software/Subscriptions', type: 'expense' },
                    { name: 'Expense - Taxes & Licenses', type: 'expense' },
                    { name: 'Expense - Utilities', type: 'expense' },
                    { name: 'Expense - Vehicle', type: 'expense' },
                    { name: 'Liability - Sales Tax', type: 'liability' },
                    { name: 'Liability - Owner\'s Draw', type: 'liability' }
                ];

                const stmt = db.prepare('INSERT INTO categories (userId, name, type) VALUES (?, ?, ?)');
                
                db.run('BEGIN TRANSACTION');
                defaultCategories.forEach(cat => {
                    stmt.run(newUserId, cat.name, cat.type);
                });
                db.run('COMMIT', (commitErr) => {
                    stmt.finalize();
                    if (commitErr) {
                        console.error("Error populating default categories:", commitErr);
                    }
                    res.status(201).send('User created. Please log in.');
                });
            });
        });

    } catch (error) {
        res.status(500).send('Server error.');
    }
});

app.post('/api/login', (req, res) => {
    // ... (This endpoint is unchanged) ...
    const { username, password } = req.body;
    
    db.get('SELECT * FROM users WHERE username = ?', [username], async (err, user) => {
        if (err || !user) {
            return res.status(400).send('Cannot find user.');
        }
        
        try {
            if (await bcrypt.compare(password, user.password)) {
                // User is valid, create JWT
                const accessToken = jwt.sign({ id: user.id, username: user.username }, JWT_SECRET, { expiresIn: '24h' });
                res.json({ accessToken: accessToken });
            } else {
                res.status(400).send('Invalid password.');
            }
        } catch (error) {
            res.status(500).send('Server error.');
        }
    });
});

// === API ENDPOINTS (Protected) ===

// --- Categories ---
app.get('/api/categories', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    db.all('SELECT * FROM categories WHERE userId = ? ORDER BY name', [req.user.id], (err, rows) => {
        if (err) return res.status(500).send(err);
        res.json(rows);
    });
});

app.post('/api/categories', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    const { name, type } = req.body;
    db.run('INSERT INTO categories (userId, name, type) VALUES (?, ?, ?)', [req.user.id, name, type], function(err) {
        if (err) return res.status(500).send(err);
        res.status(201).json({ id: this.lastID, userId: req.user.id, name, type });
    });
});

app.delete('/api/categories/:id', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    db.run('DELETE FROM categories WHERE id = ? AND userId = ?', [req.params.id, req.user.id], function(err) {
        if (err) return res.status(500).send(err);
        if (this.changes === 0) return res.status(404).send('Category not found or user unauthorized.');
        res.sendStatus(204);
    });
});

// --- Vendors ---
app.get('/api/vendors', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    db.all('SELECT * FROM vendors WHERE userId = ? ORDER BY name', [req.user.id], (err, rows) => {
        if (err) return res.status(500).send(err);
        res.json(rows);
    });
});

app.post('/api/vendors', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    const { name } = req.body;
    if (!name) return res.status(400).send('Vendor name is required.');
    
    db.run('INSERT INTO vendors (userId, name) VALUES (?, ?)', [req.user.id, name], function(err) {
        if (err) {
            if (err.message.includes('UNIQUE constraint')) {
                return res.status(400).send('Vendor name must be unique.');
            }
            return res.status(500).send(err);
        }
        res.status(201).json({ id: this.lastID, userId: req.user.id, name });
    });
});

app.delete('/api/vendors/:id', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    db.run('DELETE FROM vendors WHERE id = ? AND userId = ?', [req.params.id, req.user.id], function(err) {
        if (err) return res.status(500).send(err);
        if (this.changes === 0) return res.status(404).send('Vendor not found or user unauthorized.');
        res.sendStatus(204);
    });
});


// --- Rules ---
app.get('/api/rules', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    db.all('SELECT * FROM rules WHERE userId = ?', [req.user.id], (err, rows) => {
        if (err) return res.status(500).send(err);
        res.json(rows);
    });
});

app.post('/api/rules', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    const { keyword, category } = req.body;
    db.run('INSERT INTO rules (userId, keyword, category) VALUES (?, ?, ?)', [req.user.id, keyword, category], function(err) {
        if (err) return res.status(500).send(err);
        res.status(201).json({ id: this.lastID, userId: req.user.id, keyword, category });
    });
});

// --- MODIFIED: apply-all now updates splits ---
app.post('/api/rules/apply-all', authenticateToken, async (req, res) => {
    const userId = req.user.id;
    
    try {
        const rules = await new Promise((resolve, reject) => {
            db.all('SELECT * FROM rules WHERE userId = ?', [userId], (err, rows) => {
                if (err) reject(err);
                resolve(rows);
            });
        });

        if (rules.length === 0) return res.status(200).send('No rules to apply.');

        // Get all transactions that are uncategorized and have only one split
        const transactionsToUpdate = await new Promise((resolve, reject) => {
            db.all(`
                SELECT t.id as transactionId, s.id as splitId, t.description 
                FROM transactions t
                JOIN transaction_splits s ON t.id = s.transactionId
                WHERE t.userId = ? AND t.reconciliation_status = 'uncategorized'
            `, [userId], (err, rows) => {
                if (err) reject(err);
                resolve(rows);
            });
        });
        
        if (transactionsToUpdate.length === 0) {
            return res.status(200).send('No uncategorized transactions to update.');
        }

        let updatedCount = 0;
        const updateSplitStmt = db.prepare('UPDATE transaction_splits SET category = ? WHERE id = ?');
        const updateTxStmt = db.prepare("UPDATE transactions SET reconciliation_status = 'categorized' WHERE id = ?");
        
        db.run('BEGIN TRANSACTION');
        for (const tx of transactionsToUpdate) {
            const desc = tx.description.toUpperCase();
            for (const rule of rules) {
                if (desc.includes(rule.keyword.toUpperCase())) {
                    updateSplitStmt.run(rule.category, tx.splitId);
                    updateTxStmt.run(tx.transactionId);
                    updatedCount++;
                    break; // Stop after first matching rule
                }
            }
        }
        db.run('COMMIT', (err) => {
            if (err) {
                console.error("Error committing rule updates:", err);
                return res.status(500).send('Error updating transactions.');
            }
            updateSplitStmt.finalize();
            updateTxStmt.finalize();
            res.status(200).send(`Successfully updated ${updatedCount} transactions.`);
        });

    } catch (error) {
        console.error("Error applying rules:", error);
        res.status(500).send('Server error while applying rules.');
    }
});

app.delete('/api/rules/:id', authenticateToken, (req, res) => {
    // ... (This endpoint is unchanged) ...
    db.run('DELETE FROM rules WHERE id = ? AND userId = ?', [req.params.id, req.user.id], function(err) {
        if (err) return res.status(500).send(err);
        if (this.changes === 0) return res.status(404).send('Rule not found or user unauthorized.');
        res.sendStatus(204);
    });
});

// --- Transactions ---

// --- MODIFIED: /api/transactions now returns summary data ---
app.get('/api/transactions', authenticateToken, (req, res) => {
    // This query is now more complex. It joins transactions with their splits
    // to get a split_count and the category (if only one split exists).
    const query = `
        SELECT 
            t.id, 
            t.date, 
            t.description, 
            t.amount, 
            t.reconciliation_status,
            COUNT(s.id) as split_count,
            MAX(CASE WHEN (SELECT COUNT(*) FROM transaction_splits s_inner WHERE s_inner.transactionId = t.id) = 1 THEN s.category ELSE NULL END) as category,
            MAX(CASE WHEN (SELECT COUNT(*) FROM transaction_splits s_inner WHERE s_inner.transactionId = t.id) = 1 THEN v.name ELSE NULL END) as vendorName
        FROM transactions t
        LEFT JOIN transaction_splits s ON t.id = s.transactionId
        LEFT JOIN vendors v ON s.vendorId = v.id
        WHERE t.userId = ?
        GROUP BY t.id
        ORDER BY t.date DESC
    `;
    
    db.all(query, [req.user.id], (err, rows) => {
        if (err) return res.status(500).send(err);
        rows.forEach(r => r.date = new Date(r.date));
        res.json(rows);
    });
});

// --- NEW: /api/transactions/:id/splits (GET) ---
// Gets the detailed splits for a single transaction (for the modal)
app.get('/api/transactions/:id/splits', authenticateToken, (req, res) => {
    const query = `
        SELECT s.*, v.name as vendorName 
        FROM transaction_splits s
        LEFT JOIN vendors v ON s.vendorId = v.id
        WHERE s.userId = ? AND s.transactionId = ?
    `;
    db.all(query, [req.user.id, req.params.id], (err, rows) => {
        if (err) return res.status(500).send(err);
        res.json(rows);
    });
});

// --- NEW: /api/transactions/:id/splits (PUT) ---
// Saves the new set of splits from the modal
app.put('/api/transactions/:id/splits', authenticateToken, (req, res) => {
    const log = (msg) => require('fs').appendFileSync('debug.log', `[${new Date().toISOString()}] ${msg}\n`);

    log('--- New request to PUT /api/transactions/:id/splits ---');
    
    const userId = req.user.id;
    const transactionId = parseInt(req.params.id, 10);
    const splits = req.body;

    log(`userId: ${userId}, transactionId: ${transactionId}`);
    log(`splits payload: ${JSON.stringify(splits, null, 2)}`);

    if (isNaN(transactionId)) {
        log('Error: Invalid transaction ID.');
        return res.status(400).send('Invalid transaction ID.');
    }

    if (!splits || !Array.isArray(splits) || splits.length === 0) {
        log('Error: Valid splits array is required.');
        return res.status(400).send('Valid splits array is required.');
    }

    db.get('SELECT amount FROM transactions WHERE id = ? AND userId = ?', [transactionId, userId], (err, tx) => {
        if (err) {
            log(`Error getting transaction: ${err.message}`);
            return res.status(500).send('Server error.');
        }
        if (!tx) {
            log('Error: Transaction not found.');
            return res.status(404).send('Transaction not found.');
        }

        const splitSum = splits.reduce((sum, s) => sum + parseFloat(s.amount), 0);
        if (Math.abs(splitSum - tx.amount) > 0.001) {
            log(`Error: Split amounts do not match transaction total. splitSum: ${splitSum}, tx.amount: ${tx.amount}`);
            return res.status(400).send(`Split amounts do not match transaction total.`);
        }

        db.run('BEGIN TRANSACTION', (err) => {
            if (err) {
                log(`Error beginning transaction: ${err.message}`);
                return res.status(500).send('Error saving splits.');
            }

            db.run('DELETE FROM transaction_splits WHERE userId = ? AND transactionId = ?', [userId, transactionId], function(err) {
                if (err) {
                    log(`Error deleting splits: ${err.message}`);
                    return db.run('ROLLBACK');
                }
                log(`Deleted ${this.changes} old splits.`);

                const insertStmt = db.prepare('INSERT INTO transaction_splits (userId, transactionId, category, vendorId, description, amount) VALUES (?, ?, ?, ?, ?, ?)');

                function insertSplit(index) {
                    if (index >= splits.length) {
                        log('All splits inserted.');
                        db.run("UPDATE transactions SET reconciliation_status = 'categorized' WHERE id = ?", [transactionId], (err) => {
                            if (err) {
                                log(`Error updating transaction status: ${err.message}`);
                                return db.run('ROLLBACK');
                            }
                            db.run('COMMIT', (commitErr) => {
                                insertStmt.finalize();
                                if (commitErr) {
                                    log(`Error committing transaction: ${commitErr.message}`);
                                    return res.status(500).send('Error saving splits.');
                                }
                                log('Transaction committed successfully.');
                                res.sendStatus(200);
                            });
                        });
                        return;
                    }

                    const s = splits[index];
                    const vendorId = s.vendorId ? parseInt(s.vendorId, 10) : null;
                    const description = s.description ? s.description.trim() : '';
                    log(`Inserting split ${index + 1}/${splits.length}: ${JSON.stringify(s)}`);
                    insertStmt.run(userId, transactionId, s.category, vendorId, description, s.amount, (err) => {
                        if (err) {
                            log(`Error inserting split: ${err.message}`);
                            db.run('ROLLBACK', () => {
                                insertStmt.finalize();
                                res.status(500).send('Error saving splits.');
                            });
                        } else {
                            insertSplit(index + 1);
                        }
                    });
                }

                insertSplit(0);
            });
        });
    });
});

// --- MODIFIED: DELETE now triggers ON DELETE CASCADE ---
app.delete('/api/transactions/:id', authenticateToken, (req, res) => {
    // Thanks to 'ON DELETE CASCADE' in the database schema,
    // deleting the parent transaction will automatically delete all its splits.
    db.run('DELETE FROM transactions WHERE id = ? AND userId = ?', [req.params.id, req.user.id], function(err) {
        if (err) return res.status(500).send(err);
        if (this.changes === 0) return res.status(404).send('Transaction not found or user unauthorized.');
        res.sendStatus(204);
    });
});

// --- Dashboard ---
// --- MODIFIED: Dashboard queries now read from splits table ---
app.get('/api/dashboard-summary', authenticateToken, (req, res) => {
    const userId = req.user.id;
    let summary = {
        stats: {},
        pieData: {}
    };

    // --- FIX: Only 'expense' should count as totalExpenses. 'liability' should not. ---
    const statsQuery = `
        SELECT 
            (SELECT SUM(s.amount) FROM transaction_splits s JOIN categories c ON s.category = c.name WHERE s.userId = ? AND c.type = 'income' AND s.amount > 0) as totalIncome,
            (SELECT SUM(s.amount) FROM transaction_splits s JOIN categories c ON s.category = c.name WHERE s.userId = ? AND c.type = 'income' AND s.amount < 0) as totalReturns,
            (SELECT SUM(s.amount) FROM transaction_splits s JOIN categories c ON s.category = c.name WHERE s.userId = ? AND c.type = 'expense') as totalExpenses
    `;
    
    const pieQuery = `
        SELECT category, SUM(amount) as total 
        FROM transaction_splits 
        WHERE userId = ? AND amount < 0
        GROUP BY category
        ORDER BY total ASC
        LIMIT 10
    `;

    db.get(statsQuery, [userId, userId, userId], (err, stats) => {
        if (err) return res.status(500).send(err);
        summary.stats = {
            totalIncome: stats.totalIncome || 0,
            totalReturns: stats.totalReturns || 0, // This includes 'Discounts & Comps' if categorized as 'income' (negative)
            totalExpenses: stats.totalExpenses || 0
        };

        db.all(pieQuery, [userId], (err, pieRows) => {
            if (err) return res.status(500).send(err);
            summary.pieData = {
                labels: pieRows.map(r => r.category),
                data: pieRows.map(r => Math.abs(r.total))
            };
            res.json(summary);
        });
    });
});

// --- File Upload & Processing ---
// --- MODIFIED: Upload now creates a transaction AND a default split ---
app.post('/api/upload', authenticateToken, upload.fields([{ name: 'bankFile', maxCount: 1 }, { name: 'salesFile', maxCount: 1 }]), async (req, res) => {
    const userId = req.user.id;
    
    try {
        const [rules, existingTransactions] = await Promise.all([
            new Promise((resolve, reject) => {
                db.all('SELECT * FROM rules WHERE userId = ?', [userId], (err, rows) => {
                    if (err) reject(err);
                    resolve(rows);
                });
            }),
            new Promise((resolve, reject) => {
                db.all('SELECT date, description, amount FROM transactions WHERE userId = ?', [userId], (err, rows) => {
                    if (err) reject(err);
                    resolve(rows);
                });
            })
        ]);

        const existingTxKeys = new Set(existingTransactions.map(tx => `${new Date(tx.date).toISOString().slice(0, 10)}:${tx.description.trim()}:${tx.amount.toFixed(2)}`));
        let transactionsToInsert = [];
        let skippedCount = 0;

        const smartGuessCategory = (description) => {
            const desc = description.toUpperCase();
            const guesses = {
                'Expense - Payroll': ['PAYROLL', 'ADP '],
                'Expense - Rent/Lease': ['RENT', 'COURTYARD OF ACH'],
                'Expense - Utilities': ['UINET', 'COMCAST', 'VERIZON', 'ELECTRIC', 'POWER', 'GAS '],
                'Expense - Vehicle': ['CARWASH', 'AUTOZONE', 'PEP BOYS', 'GASOLINE', 'EXXON', 'MOBIL', 'RSCARWASH'],
                'Expense - Office Supplies': ['STAPLES', 'OFFICE DEPOT', 'OFFICEMAX', 'WAL MART', 'MAUI INC'],
                'Expense - Software/Subscriptions': ['OPENAI', 'CHATGPT', 'GOOGLE *', 'MSFT *', 'ADOBE'],
                'Expense - Meals & Entertainment': ['RESTAURANT', 'DOORDASH', 'UBER EATS', 'GRUBHUB'],
                'Expense - Bank Fees': ['MAINTENANCE FEE', 'SQUARE PAYMENT PROCESSING'],
                'Liability - Owner\'s Draw': ['PERSONAL', 'TRANSFER TO SELF']
            };

            for (const [category, keywords] of Object.entries(guesses)) {
                for (const keyword of keywords) {
                    if (desc.includes(keyword)) {
                        return category;
                    }
                }
            }
            return null;
        };
        
        const applyCategorization = (tx, split) => {
            const desc = tx.description.toUpperCase();
            
            const guess = smartGuessCategory(tx.description);
            if (guess) {
                split.category = guess;
                tx.reconciliation_status = 'categorized';
                return;
            }

            for (const rule of rules) {
                if (desc.includes(rule.keyword.toUpperCase())) {
                    split.category = rule.category;
                    tx.reconciliation_status = 'categorized';
                    return;
                }
            }
        };

        const processBankCSV = (data) => {
            data.forEach(row => {
                const date = new Date(row.Date);
                const debit = parseFloat(row.Debit) || 0;
                const credit = parseFloat(row.Credit) || 0;
                const amount = credit - debit;
                const description = row.Description ? row.Description.trim() : '';
                const key = `${date.toISOString().slice(0, 10)}:${description}:${amount.toFixed(2)}`;

                if (!isNaN(date) && amount !== 0) {
                    if (existingTxKeys.has(key)) {
                        skippedCount++;
                        return;
                    }
                    existingTxKeys.add(key);
                    
                    let tx = {
                        date: date.toISOString(),
                        description: description,
                        amount: amount,
                        source: 'bank_csv',
                        reconciliation_status: 'uncategorized',
                        createdAt: new Date().toISOString()
                    };
                    let split = {
                        category: 'Expense - Uncategorized',
                        description: description,
                        amount: amount
                    };
                    applyCategorization(tx, split);
                    transactionsToInsert.push({ tx, split });
                }
            });
        };
        
        const processSalesCSV = (data, fileName) => {
            let salesData = {};
            const dateMatch = fileName.match(/(\d{4}-\d{2}-\d{2})/);
            const txDate = dateMatch ? new Date(dateMatch[1]) : new Date();

            const cleanCurrency = (val) => {
                if (!val) return 0;
                val = String(val).replace(/[$",]/g, '');
                if (val.startsWith('(') && val.endsWith(')')) {
                    val = '-' + val.substring(1, val.length - 1);
                }
                return parseFloat(val) || 0;
            };
            
            data.forEach(row => {
                const key = (row[0] || '').replace(/"/g, '').trim();
                const value = cleanCurrency(row[1]);
                salesData[key] = value;
            });

            const addTx = (desc, cat, amount) => {
                if (amount !== 0) {
                    const key = `${txDate.toISOString().slice(0, 10)}:${desc}:${amount.toFixed(2)}`;
                    if (existingTxKeys.has(key)) {
                        skippedCount++;
                        return;
                    }
                    existingTxKeys.add(key);

                    let tx = {
                        date: txDate.toISOString(),
                        description: desc,
                        amount: amount,
                        source: 'sales_summary_csv',
                        reconciliation_status: 'categorized',
                        createdAt: new Date().toISOString()
                    };
                    let split = {
                        category: cat,
                        description: desc,
                        amount: amount
                    };
                    transactionsToInsert.push({ tx, split });
                }
            };
            
            addTx('Gross Sales', 'Income - Gross Sales', salesData['Gross sales'] || 0);
            addTx('Returns', 'Income - Returns', salesData['Returns'] || 0);
            addTx('Discounts & Comps', 'Expense - Discounts & Comps', salesData['Discounts & comps'] || 0);
            addTx('Taxes Collected', 'Liability - Sales Tax', salesData['Taxes'] || 0);
            addTx('Square Payment Processing Fees', 'Expense - Bank Fees', salesData['Square payment processing fees'] || 0);
        };

        if (req.files.bankFile) {
            const file = req.files.bankFile[0];
            const content = fs.readFileSync(file.path, 'utf8');
            const results = Papa.parse(content, { header: true, skipEmptyLines: true });
            processBankCSV(results.data);
            fs.unlinkSync(file.path);
        }
        
        if (req.files.salesFile) {
            const file = req.files.salesFile[0];
            const content = fs.readFileSync(file.path, 'utf8');
            const results = Papa.parse(content, { header: false, skipEmptyLines: true });
            processSalesCSV(results.data, file.originalname);
            fs.unlinkSync(file.path);
        }

        if (transactionsToInsert.length === 0) {
            return res.status(200).send(`No new transactions to insert. Skipped ${skippedCount} duplicate transactions.`);
        }

        const txStmt = db.prepare('INSERT INTO transactions (userId, date, description, amount, source, createdAt, reconciliation_status) VALUES (?, ?, ?, ?, ?, ?, ?)');
        const splitStmt = db.prepare('INSERT INTO transaction_splits (userId, transactionId, category, description, amount) VALUES (?, ?, ?, ?, ?)');
        
        db.serialize(() => {
            db.run('BEGIN TRANSACTION');
            
            transactionsToInsert.forEach(item => {
                const { tx, split } = item;
                txStmt.run(userId, tx.date, tx.description, tx.amount, tx.source, tx.createdAt, tx.reconciliation_status, function(err) {
                    if (err) {
                        console.error("Error inserting transaction:", err);
                    } else {
                        const transactionId = this.lastID;
                        splitStmt.run(userId, transactionId, split.category, split.description, split.amount);
                    }
                });
            });

            db.run('COMMIT', (err) => {
                txStmt.finalize();
                splitStmt.finalize();
                if (err) {
                    console.error("DB commit error:", err);
                    return res.status(500).send('Error saving transactions.');
                }
                res.status(201).send(`Successfully inserted ${transactionsToInsert.length} new transactions. Skipped ${skippedCount} duplicate transactions.`);
            });
        });

    } catch (error) {
        console.error("Error processing upload:", error);
        res.status(500).send('Server error during file upload.');
    }
});

// --- Serve Frontend ---
app.get('*', (req, res) => {
    res.sendFile(__dirname + '/index.html');
});

// --- Start Server ---
app.listen(port, () => {
    console.log(`SimpleLedger server running at http://localhost:${port}`);
});