import { fileURLToPath } from 'url';
import { dirname, join } from 'path';
import { existsSync, readFileSync } from 'fs';
import { Transaction, Budget, SpendingSummary, TransactionFilters } from '../types.js';
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
const DATA_DIR = join(__dirname, '..', '..', 'data');
const TRANSACTIONS_CSV = join(DATA_DIR, 'transactions.csv');
const BUDGETS_CSV = join(DATA_DIR, 'budgets.csv');
interface DatabaseState {
transactions: Transaction[];
budgets: Budget[];
loaded: boolean;
}
const db: DatabaseState = {
transactions: [],
budgets: [],
loaded: false
};
function parseCSV(content: string): any[] {
const lines = content.trim().split('\n');
if (lines.length < 2) return [];
const headers = lines[0].split(',').map(h => h.trim());
const rows: any[] = [];
for (let i = 1; i < lines.length; i++) {
const values = parseCSVLine(lines[i]);
const obj: Record<string, any> = {};
headers.forEach((header, index) => {
const value = values[index] || '';
// Convert numeric fields
if (['amount', 'monthly_budget', 'id'].includes(header)) {
obj[header] = value ? parseFloat(value) : 0;
} else {
obj[header] = value;
}
});
rows.push(obj);
}
return rows;
}
function parseCSVLine(line: string): string[] {
const values: string[] = [];
let current = '';
let inQuotes = false;
for (let i = 0; i < line.length; i++) {
const char = line[i];
if (char === '"') {
inQuotes = !inQuotes;
} else if (char === ',' && !inQuotes) {
values.push(current.trim());
current = '';
} else {
current += char;
}
}
values.push(current.trim());
return values;
}
function ensureDataLoaded() {
if (db.loaded) return;
if (existsSync(TRANSACTIONS_CSV)) {
const content = readFileSync(TRANSACTIONS_CSV, 'utf-8');
db.transactions = parseCSV(content);
} else {
console.warn(`Warning: Transactions file not found at ${TRANSACTIONS_CSV}`);
}
if (existsSync(BUDGETS_CSV)) {
const content = readFileSync(BUDGETS_CSV, 'utf-8');
db.budgets = parseCSV(content);
} else {
console.warn(`Warning: Budgets file not found at ${BUDGETS_CSV}`);
}
db.loaded = true;
}
// Reset database - forces reload from CSV
export async function resetDatabase(): Promise<void> {
db.loaded = false;
db.transactions = [];
db.budgets = [];
ensureDataLoaded();
}
export async function queryTransactions(filters: TransactionFilters): Promise<Transaction[]> {
ensureDataLoaded();
let results = db.transactions;
if (filters.category) {
results = results.filter(t => t.category === filters.category);
}
if (filters.department) {
results = results.filter(t => t.department === filters.department);
}
if (filters.minAmount !== undefined) {
results = results.filter(t => t.amount >= filters.minAmount!);
}
if (filters.maxAmount !== undefined) {
results = results.filter(t => t.amount <= filters.maxAmount!);
}
if (filters.startDate) {
results = results.filter(t => t.date >= filters.startDate!);
}
if (filters.endDate) {
results = results.filter(t => t.date <= filters.endDate!);
}
if (filters.vendor) {
const search = filters.vendor.toLowerCase();
results = results.filter(t => t.vendor.toLowerCase().includes(search));
}
// Sort by date DESC and limit to 50
return results
.sort((a, b) => new Date(b.date).getTime() - new Date(a.date).getTime())
.slice(0, 50);
}
export async function getSpendingByCategory(
department?: string,
startDate?: string,
endDate?: string
): Promise<SpendingSummary[]> {
ensureDataLoaded();
let ptx = db.transactions;
if (department) {
ptx = ptx.filter(t => t.department === department);
}
if (startDate) {
ptx = ptx.filter(t => t.date >= startDate);
}
if (endDate) {
ptx = ptx.filter(t => t.date <= endDate);
}
const map = new Map<string, SpendingSummary>();
for (const t of ptx) {
const key = `${t.category}-${t.currency}`;
if (!map.has(key)) {
map.set(key, {
category: t.category,
total: 0,
count: 0,
currency: t.currency
});
}
const entry = map.get(key)!;
entry.total += t.amount;
entry.count += 1;
}
return Array.from(map.values()).sort((a, b) => b.total - a.total);
}
export async function getSpendingByDepartment(
startDate?: string,
endDate?: string
): Promise<Array<{ department: string; total: number; count: number; currency: string }>> {
ensureDataLoaded();
let ptx = db.transactions;
if (startDate) {
ptx = ptx.filter(t => t.date >= startDate);
}
if (endDate) {
ptx = ptx.filter(t => t.date <= endDate);
}
const map = new Map<string, { department: string; total: number; count: number; currency: string }>();
for (const t of ptx) {
const key = `${t.department}-${t.currency}`;
if (!map.has(key)) {
map.set(key, {
department: t.department,
total: 0,
count: 0,
currency: t.currency
});
}
const entry = map.get(key)!;
entry.total += t.amount;
entry.count += 1;
}
return Array.from(map.values()).sort((a, b) => b.total - a.total);
}
export async function compareToBudget(department?: string): Promise<Array<{
department: string;
category: string;
budget: number;
actual: number;
remaining: number;
percentUsed: number;
status: string;
currency: string;
}>> {
ensureDataLoaded();
// Get current month's spending
const currentDate = new Date();
const startOfMonth = `${currentDate.getFullYear()}-${String(currentDate.getMonth() + 1).padStart(2, '0')}-01`;
const endOfMonth = `${currentDate.getFullYear()}-${String(currentDate.getMonth() + 1).padStart(2, '0')}-31`;
const results = [];
for (const b of db.budgets) {
if (department && b.department !== department) continue;
// Calculate actual spending for this budget category in the current month
const actual = db.transactions
.filter(t =>
t.department === b.department &&
t.category === b.category &&
t.date >= startOfMonth &&
t.date <= endOfMonth
)
.reduce((sum, t) => sum + t.amount, 0);
const remaining = b.monthly_budget - actual;
const percentUsed = (actual / b.monthly_budget) * 100;
let status = 'OK';
if (percentUsed >= 100) {
status = 'OVER BUDGET';
} else if (percentUsed >= 80) {
status = 'WARNING';
}
results.push({
department: b.department,
category: b.category,
budget: b.monthly_budget,
actual,
remaining,
percentUsed: Math.round(percentUsed * 10) / 10,
status,
currency: b.currency
});
}
return results;
}
export async function getTopVendors(
limit: number = 10,
department?: string
): Promise<Array<{
rank: number;
vendor: string;
total: number;
transactionCount: number;
averageTransaction: number;
currency: string;
}>> {
ensureDataLoaded();
let ptx = db.transactions;
if (department) {
ptx = ptx.filter(t => t.department === department);
}
const map = new Map<string, {
vendor: string;
total: number;
transactionCount: number;
currency: string;
}>();
for (const t of ptx) {
const key = `${t.vendor}-${t.currency}`;
if (!map.has(key)) {
map.set(key, {
vendor: t.vendor,
total: 0,
transactionCount: 0,
currency: t.currency
});
}
const entry = map.get(key)!;
entry.total += t.amount;
entry.transactionCount += 1;
}
const sorted = Array.from(map.values())
.sort((a, b) => b.total - a.total)
.slice(0, limit);
return sorted.map((item, index) => ({
rank: index + 1,
vendor: item.vendor,
total: item.total,
transactionCount: item.transactionCount,
averageTransaction: Math.round((item.total / item.transactionCount) * 100) / 100,
currency: item.currency
}));
}
