etf-trade-tracker/server.js

const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const cors = require('cors');
const path = require('path');
const session = require('express-session');
const bcrypt = require('bcrypt');
const crypto = require('crypto');
const fs = require('fs');

const app = express();
const PORT = process.env.PORT || 3000;

app.use(cors({
    origin: true,
    credentials: true
}));
app.use(express.json());
app.use(session({
    secret: process.env.SESSION_SECRET || 'etf-tracker-secret-key',
    resave: false,
    saveUninitialized: false,
    cookie: { 
        secure: false, // Set to true if using HTTPS
        maxAge: 24 * 60 * 60 * 1000 // 24 hours
    }
}));
app.use(express.static('.'));

// Ensure data directory exists (for containers)
const dataDir = process.env.NODE_ENV === 'production' ? '/app/data' : './';
if (process.env.NODE_ENV === 'production' && !fs.existsSync(dataDir)) {
    fs.mkdirSync(dataDir, { recursive: true });
}

const dbPath = path.join(dataDir, 'etf_trades.db');
const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('Error opening database:', err.message);
    } else {
        console.log('Connected to SQLite database');
        initializeDatabase();
    }
});

function initializeDatabase() {
    // Create users table
    const createUsersTableSQL = `
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            email TEXT UNIQUE,
            is_admin BOOLEAN DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_login DATETIME
        )
    `;
    
    // Create trades table with user_id
    const createTradesTableSQL = `
        CREATE TABLE IF NOT EXISTS trades (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            etf_symbol TEXT NOT NULL,
            trade_type TEXT NOT NULL CHECK (trade_type IN ('buy', 'sell')),
            shares REAL NOT NULL CHECK (shares > 0),
            price REAL NOT NULL CHECK (price > 0),
            currency TEXT NOT NULL CHECK (currency IN ('EUR', 'USD', 'GBP')),
            trade_datetime TEXT NOT NULL,
            fees REAL DEFAULT 0 CHECK (fees >= 0),
            notes TEXT,
            total_value REAL NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
    `;

    db.run(createUsersTableSQL, (err) => {
        if (err) {
            console.error('Error creating users table:', err.message);
        } else {
            console.log('Users table ready');
            createDefaultAdmin();
        }
    });
    
    db.run(createTradesTableSQL, (err) => {
        if (err) {
            console.error('Error creating trades table:', err.message);
        } else {
            console.log('Trades table ready');
            
            // Check if user_id column exists, if not add it (for existing databases)
            db.all("PRAGMA table_info(trades)", (err, columns) => {
                if (err) {
                    console.error('Error checking trades table structure:', err.message);
                    return;
                }
                
                const hasUserId = columns.some(col => col.name === 'user_id');
                if (!hasUserId) {
                    console.log('Adding user_id column to existing trades table...');
                    db.run("ALTER TABLE trades ADD COLUMN user_id INTEGER DEFAULT 1", (err) => {
                        if (err) {
                            console.error('Error adding user_id column:', err.message);
                        } else {
                            console.log('user_id column added to trades table');
                            
                            // Update all existing trades to belong to user 1 (admin)
                            db.run("UPDATE trades SET user_id = 1 WHERE user_id IS NULL", (err) => {
                                if (err) {
                                    console.error('Error updating existing trades:', err.message);
                                } else {
                                    console.log('Existing trades updated with user_id');
                                }
                            });
                        }
                    });
                }
            });
        }
    });

    // Create CGT settings table
    const createCGTSettingsTableSQL = `
        CREATE TABLE IF NOT EXISTS cgt_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            rate_1month REAL DEFAULT 40.0,
            rate_6months REAL DEFAULT 35.0,
            rate_1year REAL DEFAULT 30.0,
            rate_2years REAL DEFAULT 20.0,
            rate_longterm REAL DEFAULT 10.0,
            rate_8years REAL DEFAULT 33.0,
            annual_exemption REAL DEFAULT 1270.0,
            enabled BOOLEAN DEFAULT 1,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
            UNIQUE(user_id)
        )
    `;
    
    db.run(createCGTSettingsTableSQL, (err) => {
        if (err) {
            console.error('Error creating CGT settings table:', err.message);
        } else {
            console.log('CGT settings table ready');
        }
    });

    // Create access tokens table
    const createAccessTokensTableSQL = `
        CREATE TABLE IF NOT EXISTS access_tokens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            token_name TEXT NOT NULL,
            token_hash TEXT NOT NULL,
            token_prefix TEXT NOT NULL,
            scopes TEXT DEFAULT 'read,write',
            last_used_at DATETIME,
            expires_at DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    `;
    
    db.run(createAccessTokensTableSQL, (err) => {
        if (err) {
            console.error('Error creating access tokens table:', err.message);
        } else {
            console.log('Access tokens table ready');
        }
    });

    // Create cash accounts table
    const createCashAccountsTableSQL = `
        CREATE TABLE IF NOT EXISTS cash_accounts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            account_name TEXT NOT NULL,
            account_type TEXT DEFAULT 'savings' CHECK (account_type IN ('savings', 'checking', 'money_market', 'cd', 'other')),
            balance REAL NOT NULL DEFAULT 0 CHECK (balance >= 0),
            currency TEXT NOT NULL DEFAULT 'EUR' CHECK (currency IN ('EUR', 'USD', 'GBP')),
            institution_name TEXT,
            interest_rate REAL DEFAULT 0 CHECK (interest_rate >= 0),
            notes TEXT,
            is_active BOOLEAN DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    `;
    
    // Create cash transfers table
    const createCashTransfersTableSQL = `
        CREATE TABLE IF NOT EXISTS cash_transfers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            account_id INTEGER NOT NULL,
            transfer_type TEXT NOT NULL CHECK (transfer_type IN ('deposit', 'withdrawal')),
            amount REAL NOT NULL CHECK (amount > 0),
            description TEXT,
            transfer_date DATE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
            FOREIGN KEY (account_id) REFERENCES cash_accounts (id) ON DELETE CASCADE
        )
    `;
    
    db.run(createCashAccountsTableSQL, (err) => {
        if (err) {
            console.error('Error creating cash accounts table:', err.message);
        } else {
            console.log('Cash accounts table ready');
        }
    });
    
    db.run(createCashTransfersTableSQL, (err) => {
        if (err) {
            console.error('Error creating cash transfers table:', err.message);
        } else {
            console.log('Cash transfers table ready');
        }
    });
    
    // Create price history table
    const createPriceHistoryTableSQL = `
        CREATE TABLE IF NOT EXISTS price_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            etf_symbol TEXT NOT NULL,
            price REAL NOT NULL CHECK (price > 0),
            currency TEXT NOT NULL CHECK (currency IN ('EUR', 'USD', 'GBP')),
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
    `;

    db.run(createPriceHistoryTableSQL, (err) => {
        if (err) {
            console.error('Error creating price history table:', err.message);
        } else {
            console.log('Price history table ready');
        }
    });

    // Create subscriptions table
    const createSubscriptionsTableSQL = `
        CREATE TABLE IF NOT EXISTS subscriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            service_name TEXT NOT NULL,
            monthly_price REAL CHECK (monthly_price > 0),
            annual_price REAL CHECK (annual_price > 0),
            billing_cycle TEXT NOT NULL CHECK (billing_cycle IN ('monthly', 'annual')),
            currency TEXT NOT NULL DEFAULT 'EUR' CHECK (currency IN ('EUR', 'USD', 'GBP')),
            start_date DATE NOT NULL,
            end_date DATE,
            free_trial_days INTEGER DEFAULT 0 CHECK (free_trial_days >= 0),
            category TEXT DEFAULT 'other',
            notes TEXT,
            website_url TEXT,
            is_active BOOLEAN DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
            CHECK (monthly_price IS NOT NULL OR annual_price IS NOT NULL)
        )
    `;

    db.run(createSubscriptionsTableSQL, (err) => {
        if (err) {
            console.error('Error creating subscriptions table:', err.message);
        } else {
            console.log('Subscriptions table ready');

            // Add website_url column if it doesn't exist (for existing databases)
            db.run(`ALTER TABLE subscriptions ADD COLUMN website_url TEXT`, (alterErr) => {
                if (alterErr && !alterErr.message.includes('duplicate column name')) {
                    console.error('Error adding website_url column:', alterErr.message);
                } else if (!alterErr) {
                    console.log('Added website_url column to subscriptions table');
                }
            });
        }
    });
}

async function createDefaultAdmin() {
    const adminUsername = 'admin';
    const adminPassword = 'admin123';
    
    db.get('SELECT id FROM users WHERE username = ?', [adminUsername], async (err, row) => {
        if (err) {
            console.error('Error checking for admin user:', err.message);
            return;
        }
        
        if (!row) {
            try {
                const passwordHash = await bcrypt.hash(adminPassword, 10);
                db.run(
                    'INSERT INTO users (username, password_hash, is_admin) VALUES (?, ?, ?)',
                    [adminUsername, passwordHash, 1],
                    function(err) {
                        if (err) {
                            console.error('Error creating admin user:', err.message);
                        } else {
                            console.log('Default admin user created (username: admin, password: admin123)');
                        }
                    }
                );
            } catch (error) {
                console.error('Error hashing admin password:', error);
            }
        }
    });
}

// Middleware to check if user is authenticated
function requireAuth(req, res, next) {
    if (req.session && req.session.userId) {
        next();
    } else {
        res.status(401).json({ error: 'Authentication required' });
    }
}

// Middleware to check if user is admin
function requireAdmin(req, res, next) {
    if (req.session && req.session.userId && req.session.isAdmin) {
        next();
    } else {
        res.status(403).json({ error: 'Admin privileges required' });
    }
}

// Middleware for token-based authentication
async function authenticateToken(req, res, next) {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.startsWith('Bearer ') ? authHeader.slice(7) : null;

    if (!token) {
        return res.status(401).json({ error: 'Access token required' });
    }

    // Extract token prefix (first 8 characters)
    const tokenPrefix = token.substring(0, 8);
    const tokenHash = crypto.createHash('sha256').update(token).digest('hex');

    try {
        const sql = `
            SELECT at.*, u.username, u.is_admin 
            FROM access_tokens at 
            JOIN users u ON at.user_id = u.id 
            WHERE at.token_prefix = ? AND at.token_hash = ?
        `;

        db.get(sql, [tokenPrefix, tokenHash], (err, row) => {
            if (err) {
                console.error('Token authentication error:', err);
                return res.status(500).json({ error: 'Authentication error' });
            }

            if (!row) {
                return res.status(401).json({ error: 'Invalid access token' });
            }

            // Check if token is expired
            if (row.expires_at && new Date() > new Date(row.expires_at)) {
                return res.status(401).json({ error: 'Access token expired' });
            }

            // Update last used timestamp
            db.run('UPDATE access_tokens SET last_used_at = CURRENT_TIMESTAMP WHERE id = ?', [row.id]);

            // Set user info for downstream middleware
            req.session = req.session || {};
            req.session.userId = row.user_id;
            req.session.username = row.username;
            req.session.isAdmin = row.is_admin === 1;
            req.tokenAuth = true;
            req.tokenScopes = row.scopes ? row.scopes.split(',') : ['read', 'write'];

            next();
        });
    } catch (error) {
        console.error('Token authentication error:', error);
        res.status(500).json({ error: 'Authentication error' });
    }
}

// Combined authentication middleware (session or token)
function requireAuthOrToken(req, res, next) {
    // Check for token authentication first
    if (req.headers['authorization']) {
        return authenticateToken(req, res, next);
    }
    
    // Fall back to session authentication
    return requireAuth(req, res, next);
}

// Authentication endpoints
app.post('/api/login', async (req, res) => {
    const { username, password } = req.body;

    if (!username || !password) {
        return res.status(400).json({ error: 'Username and password required' });
    }

    db.get('SELECT * FROM users WHERE username = ?', [username], async (err, user) => {
        if (err) {
            console.error('Login error:', err.message);
            return res.status(500).json({ error: 'Server error' });
        }

        if (!user) {
            return res.status(401).json({ error: 'Invalid credentials' });
        }

        try {
            const isValid = await bcrypt.compare(password, user.password_hash);
            if (!isValid) {
                return res.status(401).json({ error: 'Invalid credentials' });
            }

            // Update last login
            db.run('UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?', [user.id]);

            // Set session
            req.session.userId = user.id;
            req.session.username = user.username;
            req.session.isAdmin = user.is_admin;

            res.json({
                id: user.id,
                username: user.username,
                isAdmin: user.is_admin
            });
        } catch (error) {
            console.error('Password comparison error:', error);
            res.status(500).json({ error: 'Server error' });
        }
    });
});

app.post('/api/logout', (req, res) => {
    req.session.destroy((err) => {
        if (err) {
            return res.status(500).json({ error: 'Could not log out' });
        }
        res.json({ message: 'Logged out successfully' });
    });
});

app.get('/api/me', requireAuthOrToken, (req, res) => {
    res.json({
        id: req.session.userId,
        username: req.session.username,
        isAdmin: req.session.isAdmin
    });
});

// User management endpoints (admin only)
app.get('/api/admin/users', requireAdmin, (req, res) => {
    const sql = 'SELECT id, username, email, is_admin, created_at, last_login FROM users ORDER BY created_at DESC';
    
    db.all(sql, [], (err, rows) => {
        if (err) {
            console.error('Error fetching users:', err.message);
            res.status(500).json({ error: 'Failed to fetch users' });
            return;
        }
        
        res.json(rows);
    });
});

app.post('/api/admin/users', requireAdmin, async (req, res) => {
    const { username, password, email, isAdmin } = req.body;

    if (!username || !password) {
        return res.status(400).json({ error: 'Username and password required' });
    }

    try {
        const passwordHash = await bcrypt.hash(password, 10);
        const sql = 'INSERT INTO users (username, password_hash, email, is_admin) VALUES (?, ?, ?, ?)';
        
        db.run(sql, [username, passwordHash, email || null, isAdmin ? 1 : 0], function(err) {
            if (err) {
                if (err.message.includes('UNIQUE constraint failed')) {
                    res.status(400).json({ error: 'Username or email already exists' });
                } else {
                    console.error('Error creating user:', err.message);
                    res.status(500).json({ error: 'Failed to create user' });
                }
                return;
            }

            res.status(201).json({
                id: this.lastID,
                username,
                email,
                isAdmin: isAdmin || false
            });
        });
    } catch (error) {
        console.error('Password hashing error:', error);
        res.status(500).json({ error: 'Server error' });
    }
});

app.delete('/api/admin/users/:id', requireAdmin, (req, res) => {
    const userId = req.params.id;

    if (!userId || isNaN(userId)) {
        return res.status(400).json({ error: 'Invalid user ID' });
    }

    // Prevent deleting yourself
    if (parseInt(userId) === req.session.userId) {
        return res.status(400).json({ error: 'Cannot delete your own account' });
    }

    db.run('DELETE FROM users WHERE id = ?', [userId], function(err) {
        if (err) {
            console.error('Error deleting user:', err.message);
            res.status(500).json({ error: 'Failed to delete user' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'User not found' });
            return;
        }

        res.json({ message: 'User deleted successfully' });
    });
});

// Access Token Management endpoints
app.get('/api/tokens', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT id, token_name, token_prefix, scopes, last_used_at, expires_at, created_at
        FROM access_tokens 
        WHERE user_id = ?
        ORDER BY created_at DESC
    `;

    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching tokens:', err.message);
            res.status(500).json({ error: 'Failed to fetch access tokens' });
            return;
        }

        res.json(rows || []);
    });
});

app.post('/api/tokens', requireAuthOrToken, (req, res) => {
    const { name, expires_in_days = null, scopes = 'read,write' } = req.body;

    if (!name || name.trim().length === 0) {
        return res.status(400).json({ error: 'Token name is required' });
    }

    if (name.length > 50) {
        return res.status(400).json({ error: 'Token name must be 50 characters or less' });
    }

    // Generate a secure token (32 bytes = 64 hex characters)
    const token = crypto.randomBytes(32).toString('hex');
    const tokenPrefix = token.substring(0, 8);
    const tokenHash = crypto.createHash('sha256').update(token).digest('hex');

    // Calculate expiration date
    const expiresAt = expires_in_days ? new Date(Date.now() + expires_in_days * 24 * 60 * 60 * 1000) : null;

    const sql = `
        INSERT INTO access_tokens (user_id, token_name, token_hash, token_prefix, scopes, expires_at)
        VALUES (?, ?, ?, ?, ?, ?)
    `;

    db.run(sql, [req.session.userId, name.trim(), tokenHash, tokenPrefix, scopes, expiresAt], function(err) {
        if (err) {
            console.error('Error creating access token:', err.message);
            res.status(500).json({ error: 'Failed to create access token' });
            return;
        }

        res.status(201).json({
            message: 'Access token created successfully',
            token_id: this.lastID,
            token: token, // Only returned once
            token_name: name.trim(),
            scopes: scopes,
            expires_at: expiresAt
        });
    });
});

app.delete('/api/tokens/:id', requireAuthOrToken, (req, res) => {
    const tokenId = req.params.id;

    if (!tokenId || isNaN(tokenId)) {
        return res.status(400).json({ error: 'Invalid token ID' });
    }

    const sql = 'DELETE FROM access_tokens WHERE id = ? AND user_id = ?';

    db.run(sql, [tokenId, req.session.userId], function(err) {
        if (err) {
            console.error('Error deleting access token:', err.message);
            res.status(500).json({ error: 'Failed to delete access token' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Access token not found' });
            return;
        }

        res.json({ message: 'Access token deleted successfully' });
    });
});

app.patch('/api/tokens/:id', requireAuthOrToken, (req, res) => {
    const tokenId = req.params.id;
    const { name } = req.body;

    if (!tokenId || isNaN(tokenId)) {
        return res.status(400).json({ error: 'Invalid token ID' });
    }

    if (!name || name.trim().length === 0) {
        return res.status(400).json({ error: 'Token name is required' });
    }

    if (name.length > 50) {
        return res.status(400).json({ error: 'Token name must be 50 characters or less' });
    }

    const sql = 'UPDATE access_tokens SET token_name = ? WHERE id = ? AND user_id = ?';

    db.run(sql, [name.trim(), tokenId, req.session.userId], function(err) {
        if (err) {
            console.error('Error updating access token:', err.message);
            res.status(500).json({ error: 'Failed to update access token' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Access token not found' });
            return;
        }

        res.json({ message: 'Access token updated successfully' });
    });
});

// Cash Accounts Management endpoints
app.get('/api/cash-accounts', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT id, account_name, account_type, balance, currency, institution_name, 
               interest_rate, notes, is_active, created_at, updated_at
        FROM cash_accounts 
        WHERE user_id = ? AND is_active = 1
        ORDER BY account_name ASC
    `;

    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching cash accounts:', err.message);
            res.status(500).json({ error: 'Failed to fetch cash accounts' });
            return;
        }

        res.json(rows || []);
    });
});

app.post('/api/cash-accounts', requireAuthOrToken, (req, res) => {
    const {
        account_name,
        account_type = 'savings',
        balance = 0,
        currency = 'EUR',
        institution_name = '',
        interest_rate = 0,
        notes = ''
    } = req.body;

    if (!account_name || account_name.trim().length === 0) {
        return res.status(400).json({ error: 'Account name is required' });
    }

    if (account_name.length > 100) {
        return res.status(400).json({ error: 'Account name must be 100 characters or less' });
    }

    if (balance < 0) {
        return res.status(400).json({ error: 'Balance cannot be negative' });
    }

    if (interest_rate < 0) {
        return res.status(400).json({ error: 'Interest rate cannot be negative' });
    }

    const sql = `
        INSERT INTO cash_accounts (
            user_id, account_name, account_type, balance, currency, 
            institution_name, interest_rate, notes
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;

    db.run(sql, [
        req.session.userId,
        account_name.trim(),
        account_type,
        parseFloat(balance),
        currency,
        institution_name.trim(),
        parseFloat(interest_rate),
        notes.trim()
    ], function(err) {
        if (err) {
            console.error('Error creating cash account:', err.message);
            res.status(500).json({ error: 'Failed to create cash account' });
            return;
        }

        res.status(201).json({
            message: 'Cash account created successfully',
            account_id: this.lastID,
            account_name: account_name.trim(),
            account_type,
            balance: parseFloat(balance),
            currency
        });
    });
});

app.put('/api/cash-accounts/:id', requireAuthOrToken, (req, res) => {
    const accountId = req.params.id;
    const {
        account_name,
        account_type,
        balance,
        currency,
        institution_name = '',
        interest_rate = 0,
        notes = ''
    } = req.body;

    if (!accountId || isNaN(accountId)) {
        return res.status(400).json({ error: 'Invalid account ID' });
    }

    if (!account_name || account_name.trim().length === 0) {
        return res.status(400).json({ error: 'Account name is required' });
    }

    if (account_name.length > 100) {
        return res.status(400).json({ error: 'Account name must be 100 characters or less' });
    }

    if (balance < 0) {
        return res.status(400).json({ error: 'Balance cannot be negative' });
    }

    if (interest_rate < 0) {
        return res.status(400).json({ error: 'Interest rate cannot be negative' });
    }

    const sql = `
        UPDATE cash_accounts 
        SET account_name = ?, account_type = ?, balance = ?, currency = ?, 
            institution_name = ?, interest_rate = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ? AND user_id = ?
    `;

    db.run(sql, [
        account_name.trim(),
        account_type,
        parseFloat(balance),
        currency,
        institution_name.trim(),
        parseFloat(interest_rate),
        notes.trim(),
        accountId,
        req.session.userId
    ], function(err) {
        if (err) {
            console.error('Error updating cash account:', err.message);
            res.status(500).json({ error: 'Failed to update cash account' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Cash account not found' });
            return;
        }

        res.json({ message: 'Cash account updated successfully' });
    });
});

app.delete('/api/cash-accounts/:id', requireAuthOrToken, (req, res) => {
    const accountId = req.params.id;

    if (!accountId || isNaN(accountId)) {
        return res.status(400).json({ error: 'Invalid account ID' });
    }

    const sql = 'UPDATE cash_accounts SET is_active = 0 WHERE id = ? AND user_id = ?';

    db.run(sql, [accountId, req.session.userId], function(err) {
        if (err) {
            console.error('Error deleting cash account:', err.message);
            res.status(500).json({ error: 'Failed to delete cash account' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Cash account not found' });
            return;
        }

        res.json({ message: 'Cash account deleted successfully' });
    });
});

app.get('/api/cash-summary', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT 
            SUM(CASE WHEN currency = 'EUR' THEN balance ELSE 0 END) as total_eur,
            SUM(CASE WHEN currency = 'USD' THEN balance ELSE 0 END) as total_usd,
            COUNT(*) as account_count,
            AVG(CASE WHEN interest_rate > 0 THEN interest_rate ELSE NULL END) as avg_interest_rate
        FROM cash_accounts 
        WHERE user_id = ? AND is_active = 1
    `;

    db.get(sql, [req.session.userId], (err, row) => {
        if (err) {
            console.error('Error fetching cash summary:', err.message);
            res.status(500).json({ error: 'Failed to fetch cash summary' });
            return;
        }

        res.json({
            total_eur: row.total_eur || 0,
            total_usd: row.total_usd || 0,
            account_count: row.account_count || 0,
            avg_interest_rate: row.avg_interest_rate || 0
        });
    });
});

// Cash Transfers API endpoints
app.post('/api/cash-transfers', requireAuthOrToken, (req, res) => {
    const {
        account_id,
        transfer_type,
        amount,
        description = '',
        transfer_date
    } = req.body;

    if (!account_id || !transfer_type || !amount || !transfer_date) {
        return res.status(400).json({ error: 'Missing required fields' });
    }

    if (!['deposit', 'withdrawal'].includes(transfer_type)) {
        return res.status(400).json({ error: 'Invalid transfer type' });
    }

    if (amount <= 0) {
        return res.status(400).json({ error: 'Amount must be greater than 0' });
    }

    // First check if account belongs to user
    db.get('SELECT * FROM cash_accounts WHERE id = ? AND user_id = ? AND is_active = 1', 
        [account_id, req.session.userId], 
        (err, account) => {
            if (err) {
                console.error('Error checking account:', err.message);
                return res.status(500).json({ error: 'Failed to verify account' });
            }

            if (!account) {
                return res.status(404).json({ error: 'Account not found' });
            }

            // For withdrawals, check if sufficient balance
            if (transfer_type === 'withdrawal' && account.balance < amount) {
                return res.status(400).json({ error: 'Insufficient balance' });
            }

            // Begin transaction
            db.serialize(() => {
                db.run('BEGIN TRANSACTION');

                // Insert transfer record
                const insertTransferSQL = `
                    INSERT INTO cash_transfers (user_id, account_id, transfer_type, amount, description, transfer_date)
                    VALUES (?, ?, ?, ?, ?, ?)
                `;

                db.run(insertTransferSQL, [req.session.userId, account_id, transfer_type, amount, description, transfer_date], 
                    function(err) {
                        if (err) {
                            console.error('Error inserting transfer:', err.message);
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: 'Failed to create transfer' });
                        }

                        const transferId = this.lastID;

                        // Update account balance
                        const balanceChange = transfer_type === 'deposit' ? amount : -amount;
                        const updateBalanceSQL = `
                            UPDATE cash_accounts 
                            SET balance = balance + ?, updated_at = CURRENT_TIMESTAMP 
                            WHERE id = ? AND user_id = ?
                        `;

                        db.run(updateBalanceSQL, [balanceChange, account_id, req.session.userId], 
                            function(err) {
                                if (err) {
                                    console.error('Error updating balance:', err.message);
                                    db.run('ROLLBACK');
                                    return res.status(500).json({ error: 'Failed to update balance' });
                                }

                                db.run('COMMIT');

                                res.json({
                                    id: transferId,
                                    account_id,
                                    transfer_type,
                                    amount,
                                    description,
                                    transfer_date,
                                    message: 'Transfer created successfully'
                                });
                            });
                    });
            });
        });
});

app.get('/api/cash-transfers', requireAuthOrToken, (req, res) => {
    const { account_id } = req.query;
    
    let sql = `
        SELECT ct.*, ca.account_name, ca.currency
        FROM cash_transfers ct
        JOIN cash_accounts ca ON ct.account_id = ca.id
        WHERE ct.user_id = ?
    `;
    const params = [req.session.userId];
    
    if (account_id) {
        sql += ' AND ct.account_id = ?';
        params.push(account_id);
    }
    
    sql += ' ORDER BY ct.transfer_date DESC, ct.created_at DESC';

    db.all(sql, params, (err, rows) => {
        if (err) {
            console.error('Error fetching transfers:', err.message);
            res.status(500).json({ error: 'Failed to fetch transfers' });
            return;
        }

        res.json(rows);
    });
});

app.get('/api/trades', requireAuthOrToken, (req, res) => {
    const sql = 'SELECT * FROM trades WHERE user_id = ? ORDER BY trade_datetime DESC, created_at DESC';
    
    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching trades:', err.message);
            res.status(500).json({ error: 'Failed to fetch trades' });
            return;
        }
        
        const trades = rows.map(row => ({
            id: row.id.toString(),
            etfSymbol: row.etf_symbol,
            tradeType: row.trade_type,
            shares: row.shares,
            price: row.price,
            currency: row.currency,
            dateTime: row.trade_datetime,
            fees: row.fees,
            notes: row.notes,
            totalValue: row.total_value,
            timestamp: new Date(row.created_at).getTime()
        }));
        
        res.json(trades);
    });
});

app.post('/api/trades', requireAuthOrToken, (req, res) => {
    const {
        etfSymbol,
        tradeType,
        shares,
        price,
        currency,
        dateTime,
        fees = 0,
        notes = ''
    } = req.body;

    if (!etfSymbol || !tradeType || !shares || !price || !currency || !dateTime) {
        return res.status(400).json({ error: 'Missing required fields' });
    }

    if (!['buy', 'sell'].includes(tradeType)) {
        return res.status(400).json({ error: 'Invalid trade type' });
    }

    if (!['EUR', 'USD', 'GBP'].includes(currency)) {
        return res.status(400).json({ error: 'Invalid currency' });
    }

    if (shares <= 0 || price <= 0 || fees < 0) {
        return res.status(400).json({ error: 'Invalid numeric values' });
    }

    const totalValue = (shares * price) + fees;

    const sql = `
        INSERT INTO trades (user_id, etf_symbol, trade_type, shares, price, currency, trade_datetime, fees, notes, total_value)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const params = [
        req.session.userId,
        etfSymbol.toUpperCase(),
        tradeType,
        shares,
        price,
        currency,
        dateTime,
        fees,
        notes,
        totalValue
    ];

    db.run(sql, params, function(err) {
        if (err) {
            console.error('Error inserting trade:', err.message);
            res.status(500).json({ error: 'Failed to add trade' });
            return;
        }

        const newTrade = {
            id: this.lastID.toString(),
            etfSymbol: etfSymbol.toUpperCase(),
            tradeType,
            shares,
            price,
            currency,
            dateTime,
            fees,
            notes,
            totalValue,
            timestamp: Date.now()
        };

        res.status(201).json(newTrade);
    });
});

app.delete('/api/trades/:id', requireAuthOrToken, (req, res) => {
    const tradeId = req.params.id;

    if (!tradeId || isNaN(tradeId)) {
        return res.status(400).json({ error: 'Invalid trade ID' });
    }

    const sql = 'DELETE FROM trades WHERE id = ? AND user_id = ?';

    db.run(sql, [tradeId, req.session.userId], function(err) {
        if (err) {
            console.error('Error deleting trade:', err.message);
            res.status(500).json({ error: 'Failed to delete trade' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Trade not found' });
            return;
        }

        res.json({ message: 'Trade deleted successfully' });
    });
});

app.delete('/api/trades', requireAuthOrToken, (req, res) => {
    const sql = 'DELETE FROM trades WHERE user_id = ?';

    db.run(sql, [req.session.userId], function(err) {
        if (err) {
            console.error('Error clearing trades:', err.message);
            res.status(500).json({ error: 'Failed to clear trades' });
            return;
        }

        res.json({ 
            message: 'All trades cleared successfully',
            deletedCount: this.changes
        });
    });
});

// CGT Settings endpoints
app.get('/api/cgt-settings', requireAuthOrToken, (req, res) => {
    const sql = 'SELECT * FROM cgt_settings WHERE user_id = ?';
    
    db.get(sql, [req.session.userId], (err, row) => {
        if (err) {
            console.error('Error fetching CGT settings:', err.message);
            res.status(500).json({ error: 'Failed to fetch CGT settings' });
            return;
        }
        
        // Return default settings if none exist
        if (!row) {
            res.json({
                rate_1month: 40.0,
                rate_6months: 35.0,
                rate_1year: 30.0,
                rate_2years: 20.0,
                rate_longterm: 10.0,
                rate_8years: 33.0,
                annual_exemption: 1270.0,
                enabled: true
            });
            return;
        }
        
        res.json({
            rate_1month: row.rate_1month,
            rate_6months: row.rate_6months,
            rate_1year: row.rate_1year,
            rate_2years: row.rate_2years,
            rate_longterm: row.rate_longterm,
            rate_8years: row.rate_8years,
            annual_exemption: row.annual_exemption,
            enabled: row.enabled === 1
        });
    });
});

app.post('/api/cgt-settings', requireAuthOrToken, (req, res) => {
    const {
        rate_1month = 40.0,
        rate_6months = 35.0,
        rate_1year = 30.0,
        rate_2years = 20.0,
        rate_longterm = 10.0,
        rate_8years = 33.0,
        annual_exemption = 1270.0,
        enabled = true
    } = req.body;

    // Validate rates are between 0 and 100
    const rates = [rate_1month, rate_6months, rate_1year, rate_2years, rate_longterm, rate_8years];
    if (rates.some(rate => rate < 0 || rate > 100)) {
        return res.status(400).json({ error: 'CGT rates must be between 0 and 100' });
    }

    if (annual_exemption < 0) {
        return res.status(400).json({ error: 'Annual exemption cannot be negative' });
    }

    const sql = `
        INSERT OR REPLACE INTO cgt_settings 
        (user_id, rate_1month, rate_6months, rate_1year, rate_2years, rate_longterm, rate_8years, annual_exemption, enabled, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
    `;

    const params = [
        req.session.userId,
        rate_1month,
        rate_6months,
        rate_1year,
        rate_2years,
        rate_longterm,
        rate_8years,
        annual_exemption,
        enabled ? 1 : 0
    ];

    db.run(sql, params, function(err) {
        if (err) {
            console.error('Error saving CGT settings:', err.message);
            res.status(500).json({ error: 'Failed to save CGT settings' });
            return;
        }

        res.json({
            message: 'CGT settings saved successfully',
            settings: {
                rate_1month,
                rate_6months,
                rate_1year,
                rate_2years,
                rate_longterm,
                rate_8years,
                annual_exemption,
                enabled
            }
        });
    });
});

app.get('/api/portfolio-summary', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT 
            etf_symbol,
            SUM(CASE WHEN trade_type = 'buy' THEN shares ELSE -shares END) as total_shares,
            SUM(CASE WHEN trade_type = 'buy' THEN total_value ELSE -total_value END) as total_invested,
            COUNT(*) as trade_count
        FROM trades 
        WHERE user_id = ?
        GROUP BY etf_symbol
        HAVING total_shares > 0
        ORDER BY total_invested DESC
    `;

    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching portfolio summary:', err.message);
            res.status(500).json({ error: 'Failed to fetch portfolio summary' });
            return;
        }

        const summary = rows.map(row => ({
            symbol: row.etf_symbol,
            totalShares: row.total_shares,
            totalInvested: row.total_invested,
            avgPrice: row.total_shares > 0 ? row.total_invested / row.total_shares : 0,
            trades: row.trade_count
        }));

        res.json(summary);
    });
});

// Save price update to history
app.post('/api/price-history', requireAuthOrToken, (req, res) => {
    const { etf_symbol, price, currency } = req.body;
    
    if (!etf_symbol || !price || !currency) {
        return res.status(400).json({ error: 'Missing required fields: etf_symbol, price, currency' });
    }
    
    if (price <= 0) {
        return res.status(400).json({ error: 'Price must be greater than 0' });
    }
    
    if (!['EUR', 'USD', 'GBP'].includes(currency)) {
        return res.status(400).json({ error: 'Currency must be EUR, USD, or GBP' });
    }
    
    const sql = `
        INSERT INTO price_history (user_id, etf_symbol, price, currency)
        VALUES (?, ?, ?, ?)
    `;
    
    db.run(sql, [req.session.userId, etf_symbol, price, currency], function(err) {
        if (err) {
            console.error('Error saving price history:', err.message);
            res.status(500).json({ error: 'Failed to save price history' });
            return;
        }
        
        res.json({
            id: this.lastID,
            etf_symbol,
            price,
            currency,
            updated_at: new Date().toISOString()
        });
    });
});

// Get price history for a specific ETF
app.get('/api/price-history/:symbol', requireAuthOrToken, (req, res) => {
    const { symbol } = req.params;
    const { limit = 50 } = req.query;
    
    const sql = `
        SELECT etf_symbol, price, currency, updated_at
        FROM price_history 
        WHERE user_id = ? AND etf_symbol = ?
        ORDER BY updated_at DESC
        LIMIT ?
    `;
    
    db.all(sql, [req.session.userId, symbol, parseInt(limit)], (err, rows) => {
        if (err) {
            console.error('Error fetching price history:', err.message);
            res.status(500).json({ error: 'Failed to fetch price history' });
            return;
        }
        
        const history = rows.map(row => ({
            symbol: row.etf_symbol,
            price: row.price,
            currency: row.currency,
            updatedAt: row.updated_at
        }));
        
        res.json(history);
    });
});

// Get latest prices for all ETFs
app.get('/api/latest-prices', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT DISTINCT etf_symbol,
               FIRST_VALUE(price) OVER (PARTITION BY etf_symbol ORDER BY updated_at DESC) as latest_price,
               FIRST_VALUE(currency) OVER (PARTITION BY etf_symbol ORDER BY updated_at DESC) as currency,
               FIRST_VALUE(updated_at) OVER (PARTITION BY etf_symbol ORDER BY updated_at DESC) as updated_at
        FROM price_history 
        WHERE user_id = ?
        ORDER BY etf_symbol
    `;
    
    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching latest prices:', err.message);
            res.status(500).json({ error: 'Failed to fetch latest prices' });
            return;
        }
        
        const prices = {};
        rows.forEach(row => {
            prices[row.etf_symbol] = {
                price: row.latest_price,
                currency: row.currency,
                updatedAt: row.updated_at
            };
        });
        
        res.json(prices);
    });
});

// Subscriptions Management endpoints
app.get('/api/subscriptions', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT id, service_name, monthly_price, annual_price, billing_cycle, currency,
               start_date, end_date, free_trial_days, category, notes, website_url, is_active,
               created_at, updated_at
        FROM subscriptions
        WHERE user_id = ? AND is_active = 1
        ORDER BY service_name ASC
    `;

    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching subscriptions:', err.message);
            res.status(500).json({ error: 'Failed to fetch subscriptions' });
            return;
        }

        const subscriptions = rows.map(row => ({
            id: row.id,
            serviceName: row.service_name,
            monthlyPrice: row.monthly_price,
            annualPrice: row.annual_price,
            billingCycle: row.billing_cycle,
            currency: row.currency,
            startDate: row.start_date,
            endDate: row.end_date,
            freeTrialDays: row.free_trial_days,
            category: row.category,
            notes: row.notes,
            website_url: row.website_url,
            isActive: row.is_active === 1,
            createdAt: row.created_at,
            updatedAt: row.updated_at
        }));

        res.json(subscriptions);
    });
});

app.post('/api/subscriptions', requireAuthOrToken, (req, res) => {
    const {
        serviceName,
        monthlyPrice,
        annualPrice,
        billingCycle,
        currency = 'EUR',
        startDate,
        endDate,
        freeTrialDays = 0,
        category = 'other',
        notes = '',
        websiteUrl = ''
    } = req.body;

    if (!serviceName || !billingCycle || !startDate) {
        return res.status(400).json({ error: 'Service name, billing cycle, and start date are required' });
    }

    if (!['monthly', 'annual'].includes(billingCycle)) {
        return res.status(400).json({ error: 'Billing cycle must be monthly or annual' });
    }

    if (!['EUR', 'USD', 'GBP'].includes(currency)) {
        return res.status(400).json({ error: 'Currency must be EUR, USD, or GBP' });
    }

    if (!monthlyPrice && !annualPrice) {
        return res.status(400).json({ error: 'Either monthly price or annual price must be provided' });
    }

    if (monthlyPrice && monthlyPrice <= 0) {
        return res.status(400).json({ error: 'Monthly price must be greater than 0' });
    }

    if (annualPrice && annualPrice <= 0) {
        return res.status(400).json({ error: 'Annual price must be greater than 0' });
    }

    if (freeTrialDays < 0) {
        return res.status(400).json({ error: 'Free trial days cannot be negative' });
    }

    const sql = `
        INSERT INTO subscriptions (
            user_id, service_name, monthly_price, annual_price, billing_cycle, currency,
            start_date, end_date, free_trial_days, category, notes, website_url
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    db.run(sql, [
        req.session.userId,
        serviceName.trim(),
        monthlyPrice || null,
        annualPrice || null,
        billingCycle,
        currency,
        startDate,
        endDate || null,
        freeTrialDays,
        category.trim(),
        notes.trim(),
        websiteUrl ? websiteUrl.trim() : null
    ], function(err) {
        if (err) {
            console.error('Error creating subscription:', err.message);
            res.status(500).json({ error: 'Failed to create subscription' });
            return;
        }

        res.status(201).json({
            id: this.lastID,
            serviceName: serviceName.trim(),
            monthlyPrice,
            annualPrice,
            billingCycle,
            currency,
            startDate,
            endDate,
            freeTrialDays,
            category: category.trim(),
            notes: notes.trim(),
            message: 'Subscription created successfully'
        });
    });
});

app.put('/api/subscriptions/:id', requireAuthOrToken, (req, res) => {
    const subscriptionId = req.params.id;
    const {
        serviceName,
        monthlyPrice,
        annualPrice,
        billingCycle,
        currency,
        startDate,
        endDate,
        freeTrialDays = 0,
        category = 'other',
        notes = '',
        websiteUrl = ''
    } = req.body;


    if (!subscriptionId || isNaN(subscriptionId)) {
        return res.status(400).json({ error: 'Invalid subscription ID' });
    }

    if (!serviceName || !billingCycle || !startDate) {
        return res.status(400).json({ error: 'Service name, billing cycle, and start date are required' });
    }

    if (!['monthly', 'annual'].includes(billingCycle)) {
        return res.status(400).json({ error: 'Billing cycle must be monthly or annual' });
    }

    if (!['EUR', 'USD', 'GBP'].includes(currency)) {
        return res.status(400).json({ error: 'Currency must be EUR, USD, or GBP' });
    }

    if (!monthlyPrice && !annualPrice) {
        return res.status(400).json({ error: 'Either monthly price or annual price must be provided' });
    }

    const sql = `
        UPDATE subscriptions
        SET service_name = ?, monthly_price = ?, annual_price = ?, billing_cycle = ?,
            currency = ?, start_date = ?, end_date = ?, free_trial_days = ?,
            category = ?, notes = ?, website_url = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ? AND user_id = ?
    `;

    const websiteUrlValue = websiteUrl ? websiteUrl.trim() : null;

    db.run(sql, [
        serviceName.trim(),
        monthlyPrice || null,
        annualPrice || null,
        billingCycle,
        currency,
        startDate,
        endDate || null,
        freeTrialDays,
        category.trim(),
        notes.trim(),
        websiteUrlValue,
        subscriptionId,
        req.session.userId
    ], function(err) {
        if (err) {
            console.error('Error updating subscription:', err.message);
            res.status(500).json({ error: 'Failed to update subscription' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Subscription not found' });
            return;
        }

        res.json({ message: 'Subscription updated successfully' });
    });
});

app.delete('/api/subscriptions/:id', requireAuthOrToken, (req, res) => {
    const subscriptionId = req.params.id;

    if (!subscriptionId || isNaN(subscriptionId)) {
        return res.status(400).json({ error: 'Invalid subscription ID' });
    }

    const sql = 'UPDATE subscriptions SET is_active = 0 WHERE id = ? AND user_id = ?';

    db.run(sql, [subscriptionId, req.session.userId], function(err) {
        if (err) {
            console.error('Error deleting subscription:', err.message);
            res.status(500).json({ error: 'Failed to delete subscription' });
            return;
        }

        if (this.changes === 0) {
            res.status(404).json({ error: 'Subscription not found' });
            return;
        }

        res.json({ message: 'Subscription deleted successfully' });
    });
});

app.get('/api/subscriptions-summary', requireAuthOrToken, (req, res) => {
    const sql = `
        SELECT
            COUNT(*) as total_subscriptions,
            SUM(CASE
                WHEN billing_cycle = 'monthly' AND monthly_price IS NOT NULL THEN monthly_price
                WHEN billing_cycle = 'annual' AND annual_price IS NOT NULL THEN annual_price / 12
                ELSE 0
            END) as total_monthly_cost,
            SUM(CASE
                WHEN billing_cycle = 'annual' AND annual_price IS NOT NULL THEN annual_price
                WHEN billing_cycle = 'monthly' AND monthly_price IS NOT NULL THEN monthly_price * 12
                ELSE 0
            END) as total_annual_cost,
            currency,
            COUNT(CASE WHEN billing_cycle = 'monthly' THEN 1 END) as monthly_subscriptions,
            COUNT(CASE WHEN billing_cycle = 'annual' THEN 1 END) as annual_subscriptions
        FROM subscriptions
        WHERE user_id = ? AND is_active = 1
        GROUP BY currency
    `;

    db.all(sql, [req.session.userId], (err, rows) => {
        if (err) {
            console.error('Error fetching subscriptions summary:', err.message);
            res.status(500).json({ error: 'Failed to fetch subscriptions summary' });
            return;
        }

        const summary = rows.length ? rows : [{
            total_subscriptions: 0,
            total_monthly_cost: 0,
            total_annual_cost: 0,
            currency: 'EUR',
            monthly_subscriptions: 0,
            annual_subscriptions: 0
        }];

        res.json(summary);
    });
});

app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, 'index.html'));
});

process.on('SIGINT', () => {
    console.log('\nShutting down server...');
    db.close((err) => {
        if (err) {
            console.error('Error closing database:', err.message);
        } else {
            console.log('Database connection closed.');
        }
        process.exit(0);
    });
});

app.listen(PORT, '0.0.0.0', () => {
    console.log(`Server running on http://0.0.0.0:${PORT}`);
});