Desktop tool for resellers to track inventory, costs, and sales with fast CRUD,
real-time analytics, and a clean dark UI.
Sample SQL
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const dbPath = path.join(__dirname, '../database/inventory.db');
const db = new sqlite3.Database(dbPath, (err) => {
if (err) {
console.error('Database connection error:', err);
} else {
console.log('Connected to SQLite database.');
}
});
const initializeDatabase = () => {
db.run(`
CREATE TABLE IF NOT EXISTS inventory (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
sku TEXT NOT NULL,
shoe_name TEXT NOT NULL,
size TEXT NOT NULL,
card TEXT,
retail REAL,
predicted_resell REAL,
resell REAL,
quantity INTEGER DEFAULT 1,
date_sold TEXT
)
`);
db.run(`
CREATE TABLE IF NOT EXISTS banks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
bank_name TEXT UNIQUE NOT NULL
)
`);
};
const getAllInventory = () => {
return new Promise((resolve, reject) => {
db.all('SELECT * FROM inventory', [], (err, rows) => {
if (err) reject(err);
resolve(rows);
});
});
};
const addInventory = (item) => {
return new Promise((resolve, reject) => {
db.run(
`INSERT INTO inventory (sku, shoe_name, size, card, retail, predicted_resell, resell, quantity)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
[item.sku, item.shoe_name, item.size, item.card, item.retail, item.predicted_resell, item.resell, 1], // Always 1 per row
function (err) {
if (err) reject(err);
resolve({ id: this.lastID });
}
);
});
};
const deleteInventory = (id) => {
return new Promise((resolve, reject) => {
db.run(`DELETE FROM inventory WHERE id = ?`, [id], function (err) {
if (err) reject(err);
resolve(true);
});
});
};
const updateInventory = (item) => {
return new Promise((resolve, reject) => {
db.run(
`UPDATE inventory
SET sku = ?, shoe_name = ?, size = ?, card = ?, retail = ?, predicted_resell = ?, quantity = ?, date_sold = COALESCE(?, date_sold), resell = COALESCE(?, resell)
WHERE id = ?`,
[
item.sku,
item.shoe_name,
item.size,
item.card,
item.retail,
item.predicted_resell,
item.quantity,
item.date_sold || null, // If no date_sold is provided, keep the existing value
item.resell || null, // If no resell is provided, keep the existing value
item.id
],
(err) => {
if (err) reject(err);
resolve(true);
}
);
});
};
const addBank = (bankName) => {
return new Promise((resolve, reject) => {
db.get("SELECT 1 FROM banks WHERE bank_name = ?", [bankName], (err, row) => {
if (err) {
console.error("Error checking bank:", err);
reject(new Error("⚠ Database error. Please try again."));
return;
}
if (row) {
reject(new Error("Bank already exists! Enter a different name.")); // ✅ FIXED: Removed encoding issue
return;
}
db.run("INSERT INTO banks (bank_name) VALUES (?)", [bankName], function (err) {
if (err) {
console.error("Error adding bank:", err);
reject(new Error("❌ Error adding bank. Please try again.")); // ✅ Clean message
return;
}
resolve({ id: this.lastID });
});
});
});
};
const getBanks = () => {
return new Promise((resolve, reject) => {
db.all("SELECT * FROM banks", [], (err, rows) => {
if (err) {
console.error("Error fetching banks:", err);
reject(err);
} else {
resolve(rows);
}
});
});
};
const deleteBank = (id) => {
return new Promise((resolve, reject) => {
db.run("DELETE FROM banks WHERE id = ?", [id], (err) => {
if (err) {
console.error("Error deleting bank:", err);
reject(new Error("Failed to delete bank."));
return;
}
resolve(true);
});
});
};
const getAnalytics = (filterType) => {
let query = `
SELECT
COALESCE(SUM(resell), 0) as totalSales,
COALESCE(SUM(resell - retail), 0) as totalProfit
FROM inventory WHERE date_sold IS NOT NULL
`;
let graphQuery = `
SELECT
date_sold as date,
SUM(resell) as amount
FROM inventory WHERE date_sold IS NOT NULL
`;
let recentSalesQuery = `
SELECT shoe_name, retail, resell, (resell - retail) as profit, date_sold
FROM inventory WHERE date_sold IS NOT NULL
ORDER BY date_sold DESC
LIMIT 10
`;
// ✅ Apply filter for "Year" data
if (filterType === "year") {
query += ` AND date_sold >= date('now', '-12 months')`; // ✅ Only last 12 months
graphQuery += ` AND date_sold >= date('now', '-12 months')`;
recentSalesQuery += ` AND date_sold >= date('now', '-12 months')`;
}
graphQuery += " GROUP BY date_sold ORDER BY date_sold";
return new Promise((resolve, reject) => {
db.get(query, [], (err, row) => {
if (err) reject(err);
db.all(graphQuery, [], (err, graphRows) => {
if (err) reject(err);
db.all(recentSalesQuery, [], (err, salesRows) => {
if (err) reject(err);
resolve({
totalSales: row?.totalSales || 0,
totalProfit: row?.totalProfit || 0,
salesData: graphRows.map(entry => ({
date: entry.date,
amount: entry.amount
})),
recentSales: salesRows || []
});
});
});
});
});
};
const getInventorySummary = () => {
return new Promise((resolve, reject) => {
db.all(
`SELECT sku, shoe_name, size, retail, resell FROM inventory WHERE date_sold IS NULL`,
[],
(err, rows) => {
if (err) reject(err);
resolve(rows);
}
);
});
};
const sellItem = (item) => {
return new Promise((resolve, reject) => {
db.run(
`UPDATE inventory SET date_sold = ?, resell = ? WHERE id = ?`,
[item.date_sold, item.resell, item.id],
(err) => {
if (err) reject(err);
resolve(true);
}
);
});
};
module.exports = {
initializeDatabase,
getAllInventory,
addInventory,
deleteInventory,
updateInventory,
sellItem,
getAnalytics,
addBank,
getBanks,
getInventorySummary,
deleteBank
};
