|
|
|
|
|
import initSqlJs, { Database } from 'sql.js';
|
|
|
|
|
|
let db: Database | null = null;
|
|
|
|
|
|
export default {
|
|
|
initialize: async () => {
|
|
|
if (db) {
|
|
|
return db;
|
|
|
}
|
|
|
|
|
|
try {
|
|
|
const SQL = await initSqlJs({
|
|
|
locateFile: (file) => `https://sql.js.org/dist/${file}`,
|
|
|
});
|
|
|
|
|
|
db = new SQL.Database();
|
|
|
|
|
|
|
|
|
db.exec(`
|
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
|
id TEXT PRIMARY KEY,
|
|
|
title TEXT NOT NULL,
|
|
|
amount REAL NOT NULL,
|
|
|
type TEXT NOT NULL,
|
|
|
category TEXT NOT NULL,
|
|
|
date TEXT NOT NULL,
|
|
|
note TEXT,
|
|
|
message TEXT,
|
|
|
product_id TEXT,
|
|
|
quantity INTEGER
|
|
|
);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
|
id TEXT PRIMARY KEY,
|
|
|
name TEXT NOT NULL,
|
|
|
sku TEXT NOT NULL,
|
|
|
quantity INTEGER NOT NULL,
|
|
|
price REAL NOT NULL,
|
|
|
category TEXT NOT NULL,
|
|
|
last_updated TEXT NOT NULL,
|
|
|
reorder_point INTEGER DEFAULT 5,
|
|
|
trend TEXT DEFAULT 'stable',
|
|
|
demand TEXT DEFAULT 'medium'
|
|
|
);
|
|
|
`);
|
|
|
|
|
|
console.info('SQLite database initialized successfully');
|
|
|
|
|
|
return db;
|
|
|
} catch (err) {
|
|
|
console.error('Failed to initialize SQLite database:', err);
|
|
|
throw err;
|
|
|
}
|
|
|
},
|
|
|
|
|
|
exec: (sql: string, params: any[] = []) => {
|
|
|
if (!db) {
|
|
|
throw new Error('Database not initialized. Call initialize() first.');
|
|
|
}
|
|
|
|
|
|
try {
|
|
|
db.run(sql, params);
|
|
|
|
|
|
const selectStatement = db.prepare(sql);
|
|
|
|
|
|
if (selectStatement.sql.startsWith('SELECT')) {
|
|
|
selectStatement.bind(params);
|
|
|
|
|
|
const result = [];
|
|
|
while (selectStatement.step()) {
|
|
|
const row = selectStatement.getAsObject();
|
|
|
result.push(row);
|
|
|
}
|
|
|
selectStatement.free();
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
|
|
|
return [];
|
|
|
} catch (error: any) {
|
|
|
console.error(`Failed to execute SQL: ${sql}`, error);
|
|
|
throw error;
|
|
|
}
|
|
|
}
|
|
|
};
|
|
|
|
