Index: database.js =================================================================== --- database.js (revision 69f2a41cff81a4c7825abb68eb0f0eff6818e4e5) +++ database.js (revision 591278cabf928650250cb8d40aabda08753a31c5) @@ -24,19 +24,55 @@ database.run('PRAGMA foreign_keys = ON'); -// Helper function to ensure general category exists +// Helper function to ensure general category exists with ID 1 function ensureGeneralCategory(callback) { - database.get('SELECT category_id FROM category WHERE name = ?', ['General'], (err, row) => { + // First check if category with ID 1 exists and is named 'General' + database.get('SELECT category_id, name FROM category WHERE category_id = 1', [], (err, row) => { if (err) { callback(err); - } else if (!row) { + } else if (row && row.name === 'General') { + // Category with ID 1 already exists and is General + console.log('✅ General category exists with ID: 1'); + callback(null); + } else if (row && row.name !== 'General') { + // Category with ID 1 exists but has different name - update it database.run( - 'INSERT INTO category (name, description) VALUES (?, ?)', + 'UPDATE category SET name = ?, description = ? WHERE category_id = 1', ['General', 'General products category'], function(err) { - callback(err); + if (err) { + callback(err); + } else { + console.log('✅ Updated category ID 1 to General'); + callback(null); + } } ); } else { - callback(null); + // No category with ID 1 exists, create it + // First, check if we need to reset the autoincrement sequence + database.run( + 'INSERT INTO category (category_id, name, description) VALUES (1, ?, ?)', + ['General', 'General products category'], + function(err) { + if (err) { + // If insert fails, try without specifying ID (let SQLite assign it) + database.run( + 'INSERT INTO category (name, description) VALUES (?, ?)', + ['General', 'General products category'], + function(err) { + if (err) { + callback(err); + } else { + console.log('✅ Created General category with auto-assigned ID'); + callback(null); + } + } + ); + } else { + console.log('✅ Created General category with ID: 1'); + callback(null); + } + } + ); } }); @@ -45,5 +81,6 @@ // Helper function to get the General category ID function getGeneralCategoryId(callback) { - database.get('SELECT category_id FROM category WHERE name = ?', ['General'], (err, row) => { + // First try to get category with ID 1 that is named 'General' + database.get('SELECT category_id FROM category WHERE category_id = 1 AND name = ?', ['General'], (err, row) => { if (err) { callback(err, null); @@ -51,16 +88,27 @@ callback(null, row.category_id); } else { - // Create General category if it doesn't exist - database.run( - 'INSERT INTO category (name, description) VALUES (?, ?)', - ['General', 'General products category'], - function(err) { - if (err) { - callback(err, null); - } else { - callback(null, this.lastID); - } - } - ); + // If not found with ID 1, try to find by name + database.get('SELECT category_id FROM category WHERE name = ?', ['General'], (err, row) => { + if (err) { + callback(err, null); + } else if (row) { + callback(null, row.category_id); + } else { + // Create General category if it doesn't exist + database.run( + 'INSERT INTO category (name, description) VALUES (?, ?)', + ['General', 'General products category'], + function(err) { + if (err) { + callback(err, null); + } else { + const newId = this.lastID; + console.log(`✅ Created new General category with ID: ${newId}`); + callback(null, newId); + } + } + ); + } + }); } }); @@ -84,6 +132,6 @@ database.all( `SELECT r.* FROM roles r - JOIN user_roles ur ON r.role_id = ur.role_id - WHERE ur.user_id = ?`, + JOIN user_roles ur ON r.role_id = ur.role_id + WHERE ur.user_id = ?`, [id], (err, roles) => { @@ -184,10 +232,10 @@ function getProducts(categoryId, searchTerm, callback) { let query = ` - SELECT p.*, c.name as category_name, s.name as store_name - FROM product p - JOIN category c ON p.category_id = c.category_id - JOIN store s ON p.store_id = s.store_id - WHERE 1=1 - `; + SELECT p.*, c.name as category_name, s.name as store_name + FROM product p + JOIN category c ON p.category_id = c.category_id + JOIN store s ON p.store_id = s.store_id + WHERE 1=1 + `; const params = []; @@ -214,8 +262,8 @@ database.get( `SELECT p.*, c.name as category_name, s.name as store_name - FROM product p - JOIN category c ON p.category_id = c.category_id - JOIN store s ON p.store_id = s.store_id - WHERE p.id = ?`, + FROM product p + JOIN category c ON p.category_id = c.category_id + JOIN store s ON p.store_id = s.store_id + WHERE p.id = ?`, [id], (err, row) => { @@ -256,8 +304,8 @@ database.get( `SELECT p.*, c.name as category_name, s.name as store_name - FROM product p - JOIN category c ON p.category_id = c.category_id - JOIN store s ON p.store_id = s.store_id - WHERE p.code = ?`, + FROM product p + JOIN category c ON p.category_id = c.category_id + JOIN store s ON p.store_id = s.store_id + WHERE p.code = ?`, [code], (err, row) => { @@ -317,7 +365,7 @@ database.run( `INSERT INTO product ( - id, code, description, price, availability, weight, dimensions, - production_time, category_id, store_id, created_at - ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`, + id, code, description, price, availability, weight, dimensions, + production_time, category_id, store_id, created_at + ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`, [ 'PROD_' + Date.now().toString().slice(-8), // Generate a unique ID @@ -341,5 +389,5 @@ database.run( `INSERT INTO "change" (date_and_time, product_code, changes) - VALUES (datetime('now'), ?, ?)`, + VALUES (datetime('now'), ?, ?)`, [productData.code, 'Product created'], function(err) { @@ -353,5 +401,5 @@ database.run( `INSERT INTO makes_change (personal_id, change_date_time, product_code) - VALUES (?, datetime('now'), ?)`, + VALUES (?, datetime('now'), ?)`, [personalId, productData.code], function(err) { @@ -367,5 +415,5 @@ database.run( `INSERT INTO image (product_code, image_url, is_primary) - VALUES (?, ?, ?)`, + VALUES (?, ?, ?)`, [productData.code, imageUrl, index === 0 ? 1 : 0], function(err) { @@ -409,24 +457,30 @@ params.push(productData.description); } + if (productData.price !== undefined) { updates.push('price = ?'); params.push(productData.price); } + if (productData.availability !== undefined) { updates.push('availability = ?'); params.push(productData.availability); } + if (productData.weight !== undefined) { updates.push('weight = ?'); params.push(productData.weight); } + if (productData.dimensions !== undefined) { updates.push('dimensions = ?'); params.push(productData.dimensions); } + if (productData.production_time !== undefined) { updates.push('production_time = ?'); params.push(productData.production_time); } + if (productData.category_id !== undefined) { updates.push('category_id = ?'); @@ -452,5 +506,5 @@ database.run( `INSERT INTO "change" (date_and_time, product_code, changes) - VALUES (datetime('now'), ?, ?)`, + VALUES (datetime('now'), ?, ?)`, [productData.code, changesDesc], function(err) { @@ -461,5 +515,5 @@ database.run( `INSERT INTO makes_change (personal_id, change_date_time, product_code) - VALUES (?, datetime('now'), ?)`, + VALUES (?, datetime('now'), ?)`, [personalId, productData.code], function(err) { @@ -520,5 +574,5 @@ database.run( `INSERT INTO "change" (date_and_time, product_code, changes) - VALUES (datetime('now'), ?, ?)`, + VALUES (datetime('now'), ?, ?)`, [productCode, 'Product deleted'], function(err) { @@ -532,5 +586,5 @@ database.run( `INSERT INTO makes_change (personal_id, change_date_time, product_code) - VALUES (?, datetime('now'), ?)`, + VALUES (?, datetime('now'), ?)`, [personalId, productCode], function(err) { @@ -571,7 +625,7 @@ database.all( `SELECT c1.*, c2.name as parent_name - FROM category c1 - LEFT JOIN category c2 ON c1.parent_category_id = c2.category_id - ORDER BY c1.name`, + FROM category c1 + LEFT JOIN category c2 ON c1.parent_category_id = c2.category_id + ORDER BY c1.name`, [], (err, rows) => { @@ -610,8 +664,8 @@ database.all( `SELECT p.*, c.name as category_name - FROM product p - JOIN category c ON p.category_id = c.category_id - WHERE p.store_id = ? - ORDER BY p.code`, + FROM product p + JOIN category c ON p.category_id = c.category_id + WHERE p.store_id = ? + ORDER BY p.code`, [storeId], (err, rows) => { @@ -628,8 +682,8 @@ database.all( `SELECT o.*, c.first_name, c.last_name - FROM "order" o - JOIN client c ON o.client_id = c.client_id - WHERE o.store_id = ? - ORDER BY o.order_date DESC`, + FROM "order" o + JOIN client c ON o.client_id = c.client_id + WHERE o.store_id = ? + ORDER BY o.order_date DESC`, [storeId], (err, rows) => { @@ -649,7 +703,7 @@ database.all( `SELECT oi.*, p.description - FROM order_items oi - JOIN product p ON oi.product_code = p.code - WHERE oi.order_num = ?`, + FROM order_items oi + JOIN product p ON oi.product_code = p.code + WHERE oi.order_num = ?`, [order.order_num], (err, items) => { @@ -659,5 +713,4 @@ order.items = []; } - completed++; if (completed === orders.length) { @@ -675,9 +728,9 @@ database.all( `SELECT p.*, e.date_of_hire, perm.type as permission_type, perm.authorisation - FROM personal p - JOIN works_in_store w ON p.id = w.personal_id - LEFT JOIN employees e ON p.id = e.employee_id - LEFT JOIN permissions perm ON p.id = perm.personal_id - WHERE w.store_id = ?`, + FROM personal p + JOIN works_in_store w ON p.id = w.personal_id + LEFT JOIN employees e ON p.id = e.employee_id + LEFT JOIN permissions perm ON p.id = perm.personal_id + WHERE w.store_id = ?`, [storeId], (err, rows) => { @@ -690,6 +743,6 @@ database.all( `SELECT * FROM report - WHERE store_id = ? - ORDER BY generated_at DESC`, + WHERE store_id = ? + ORDER BY generated_at DESC`, [storeId], (err, rows) => { @@ -719,7 +772,7 @@ database.get( `SELECT SUM(oi.price * oi.quantity) as total_revenue - FROM order_items oi - JOIN "order" o ON oi.order_num = o.order_num - WHERE o.store_id = ?`, + FROM order_items oi + JOIN "order" o ON oi.order_num = o.order_num + WHERE o.store_id = ?`, [storeId], (err, row) => { @@ -729,11 +782,10 @@ database.get( `SELECT AVG(rating) as avg_rating - FROM review r - JOIN product p ON r.product_code = p.code - WHERE p.store_id = ?`, + FROM review r + JOIN product p ON r.product_code = p.code + WHERE p.store_id = ?`, [storeId], (err, row) => { stats.avg_rating = row && row.avg_rating ? row.avg_rating : 0; - callback(null, stats); } @@ -757,6 +809,6 @@ database.run( `INSERT INTO "order" (order_num, client_id, order_date, quantity, payment_method, - discount, delivery_address, store_id) - VALUES (?, ?, datetime('now'), ?, ?, ?, ?, ?)`, + discount, delivery_address, store_id) + VALUES (?, ?, datetime('now'), ?, ?, ?, ?, ?)`, [ orderData.order_num, @@ -787,5 +839,5 @@ database.run( `INSERT INTO order_items (order_num, product_code, quantity, price) - VALUES (?, ?, ?, ?)`, + VALUES (?, ?, ?, ?)`, [orderData.order_num, item.product_code, item.quantity, item.price], function(err) { @@ -817,8 +869,8 @@ database.all( `SELECT o.*, s.name as store_name - FROM "order" o - JOIN store s ON o.store_id = s.store_id - WHERE o.client_id = ? - ORDER BY o.order_date DESC`, + FROM "order" o + JOIN store s ON o.store_id = s.store_id + WHERE o.client_id = ? + ORDER BY o.order_date DESC`, [clientId], (err, rows) => { @@ -838,7 +890,7 @@ database.all( `SELECT oi.*, p.description - FROM order_items oi - JOIN product p ON oi.product_code = p.code - WHERE oi.order_num = ?`, + FROM order_items oi + JOIN product p ON oi.product_code = p.code + WHERE oi.order_num = ?`, [order.order_num], (err, items) => { @@ -848,5 +900,4 @@ order.items = []; } - completed++; if (completed === orders.length) { @@ -864,8 +915,8 @@ database.all( `SELECT o.*, c.first_name, c.last_name, s.name as store_name - FROM "order" o - JOIN client c ON o.client_id = c.client_id - JOIN store s ON o.store_id = s.store_id - ORDER BY o.order_date DESC`, + FROM "order" o + JOIN client c ON o.client_id = c.client_id + JOIN store s ON o.store_id = s.store_id + ORDER BY o.order_date DESC`, [], (err, rows) => { @@ -879,5 +930,5 @@ database.run( `INSERT INTO review (review_id, client_id, product_code, rating, comment, review_date) - VALUES (?, ?, ?, ?, ?, datetime('now'))`, + VALUES (?, ?, ?, ?, ?, datetime('now'))`, [ 'REV' + Date.now().toString().slice(-8), @@ -901,5 +952,5 @@ database.run( `INSERT INTO request (request_num, date_and_time, problem, client_id, store_id) - VALUES (?, ?, ?, ?, ?)`, + VALUES (?, ?, ?, ?, ?)`, [ requestData.request_num, @@ -923,5 +974,5 @@ database.run( `INSERT INTO refund (refund_id, order_num, amount, reason, request_date) - VALUES (?, ?, ?, ?, datetime('now'))`, + VALUES (?, ?, ?, ?, datetime('now'))`, [ refundData.refund_id, @@ -951,8 +1002,8 @@ database.all( `SELECT o.*, c.first_name, c.last_name - FROM "order" o - JOIN client c ON o.client_id = c.client_id - WHERE o.store_id = ? AND o.status = 'pending' - ORDER BY o.order_date ASC`, + FROM "order" o + JOIN client c ON o.client_id = c.client_id + WHERE o.store_id = ? AND o.status = 'pending' + ORDER BY o.order_date ASC`, [storeId], (err, rows) => { @@ -964,8 +1015,8 @@ database.all( `SELECT r.*, c.first_name, c.last_name - FROM request r - JOIN client c ON r.client_id = c.client_id - WHERE r.store_id = ? AND r.status = 'pending' - ORDER BY r.date_and_time ASC`, + FROM request r + JOIN client c ON r.client_id = c.client_id + WHERE r.store_id = ? AND r.status = 'pending' + ORDER BY r.date_and_time ASC`, [storeId], (err, rows) => { @@ -977,9 +1028,9 @@ database.all( `SELECT rf.*, o.client_id, c.first_name, c.last_name - FROM refund rf - JOIN "order" o ON rf.order_num = o.order_num - JOIN client c ON o.client_id = c.client_id - WHERE o.store_id = ? AND rf.status = 'pending' - ORDER BY rf.request_date ASC`, + FROM refund rf + JOIN "order" o ON rf.order_num = o.order_num + JOIN client c ON o.client_id = c.client_id + WHERE o.store_id = ? AND rf.status = 'pending' + ORDER BY rf.request_date ASC`, [storeId], (err, rows) => { @@ -1010,7 +1061,7 @@ database.get( `SELECT SUM(oi.price * oi.quantity) as total_spent - FROM order_items oi - JOIN "order" o ON oi.order_num = o.order_num - WHERE o.client_id = ?`, + FROM order_items oi + JOIN "order" o ON oi.order_num = o.order_num + WHERE o.client_id = ?`, [clientId], (err, row) => { @@ -1030,5 +1081,4 @@ (err, row) => { stats.delivered_orders = row ? row.delivered_orders : 0; - callback(null, stats); } @@ -1049,6 +1099,6 @@ database.all( `SELECT client_id as id, first_name, last_name, email, 'client' as user_type - FROM client - ORDER BY client_id`, + FROM client + ORDER BY client_id`, [], (err, rows) => { @@ -1060,11 +1110,11 @@ database.all( `SELECT p.id, p.first_name, p.last_name, p.email, - CASE WHEN b.boss_id IS NOT NULL THEN 'store_owner' - WHEN e.employee_id IS NOT NULL THEN 'store_employee' - ELSE 'personal' END as user_type - FROM personal p - LEFT JOIN boss b ON p.id = b.boss_id - LEFT JOIN employees e ON p.id = e.employee_id - ORDER BY p.id`, + CASE WHEN b.boss_id IS NOT NULL THEN 'store_owner' + WHEN e.employee_id IS NOT NULL THEN 'store_employee' + ELSE 'personal' END as user_type + FROM personal p + LEFT JOIN boss b ON p.id = b.boss_id + LEFT JOIN employees e ON p.id = e.employee_id + ORDER BY p.id`, [], (err, rows) => { @@ -1076,6 +1126,6 @@ database.all( `SELECT id, username, email, user_type - FROM users - ORDER BY id`, + FROM users + ORDER BY id`, [], (err, rows) => { @@ -1096,5 +1146,5 @@ database.run( `INSERT INTO audit_log (user_id, action, resource_type, resource_id, details, ip_address) - VALUES (?, ?, ?, ?, ?, ?)`, + VALUES (?, ?, ?, ?, ?, ?)`, [userId, action, resourceType, resourceId, details, ipAddress], (err) => {