source: database.js@ 591278c

finki-main main
Last change on this file since 591278c was 591278c, checked in by Klimentina Efremova <klimentina08642@…>, 7 months ago

Fixed Product and Category adding
  • Property mode set to 100644
File size: 38.9 KB
Line 
1const sqlite3 = require('sqlite3').verbose();
2const path = require('path');
3const bcrypt = require('bcryptjs');
4const crypto = require('crypto');
5
6const dbPath = path.join(__dirname, 'database', 'handcraft.db');
7const dbDir = path.dirname(dbPath);
8
9// Create database directory if it doesn't exist
10const fs = require('fs');
11if (!fs.existsSync(dbDir)) {
12 fs.mkdirSync(dbDir, { recursive: true });
13}
14
15const database = new sqlite3.Database(dbPath, (err) => {
16 if (err) {
17 console.error('Error opening database:', err.message);
18 } else {
19 console.log('✅ Connected to SQLite database');
20 }
21});
22
23// Enable foreign keys
24database.run('PRAGMA foreign_keys = ON');
25
26// Helper function to ensure general category exists with ID 1
27function ensureGeneralCategory(callback) {
28 // First check if category with ID 1 exists and is named 'General'
29 database.get('SELECT category_id, name FROM category WHERE category_id = 1', [], (err, row) => {
30 if (err) {
31 callback(err);
32 } else if (row && row.name === 'General') {
33 // Category with ID 1 already exists and is General
34 console.log('✅ General category exists with ID: 1');
35 callback(null);
36 } else if (row && row.name !== 'General') {
37 // Category with ID 1 exists but has different name - update it
38 database.run(
39 'UPDATE category SET name = ?, description = ? WHERE category_id = 1',
40 ['General', 'General products category'],
41 function(err) {
42 if (err) {
43 callback(err);
44 } else {
45 console.log('✅ Updated category ID 1 to General');
46 callback(null);
47 }
48 }
49 );
50 } else {
51 // No category with ID 1 exists, create it
52 // First, check if we need to reset the autoincrement sequence
53 database.run(
54 'INSERT INTO category (category_id, name, description) VALUES (1, ?, ?)',
55 ['General', 'General products category'],
56 function(err) {
57 if (err) {
58 // If insert fails, try without specifying ID (let SQLite assign it)
59 database.run(
60 'INSERT INTO category (name, description) VALUES (?, ?)',
61 ['General', 'General products category'],
62 function(err) {
63 if (err) {
64 callback(err);
65 } else {
66 console.log('✅ Created General category with auto-assigned ID');
67 callback(null);
68 }
69 }
70 );
71 } else {
72 console.log('✅ Created General category with ID: 1');
73 callback(null);
74 }
75 }
76 );
77 }
78 });
79}
80
81// Helper function to get the General category ID
82function getGeneralCategoryId(callback) {
83 // First try to get category with ID 1 that is named 'General'
84 database.get('SELECT category_id FROM category WHERE category_id = 1 AND name = ?', ['General'], (err, row) => {
85 if (err) {
86 callback(err, null);
87 } else if (row) {
88 callback(null, row.category_id);
89 } else {
90 // If not found with ID 1, try to find by name
91 database.get('SELECT category_id FROM category WHERE name = ?', ['General'], (err, row) => {
92 if (err) {
93 callback(err, null);
94 } else if (row) {
95 callback(null, row.category_id);
96 } else {
97 // Create General category if it doesn't exist
98 database.run(
99 'INSERT INTO category (name, description) VALUES (?, ?)',
100 ['General', 'General products category'],
101 function(err) {
102 if (err) {
103 callback(err, null);
104 } else {
105 const newId = this.lastID;
106 console.log(`✅ Created new General category with ID: ${newId}`);
107 callback(null, newId);
108 }
109 }
110 );
111 }
112 });
113 }
114 });
115}
116
117// User functions
118function getUserByUsername(username, callback) {
119 database.get('SELECT * FROM users WHERE username = ?', [username], (err, row) => {
120 callback(err, row);
121 });
122}
123
124function getUserById(id, callback) {
125 database.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
126 if (err || !row) {
127 callback(err, null);
128 return;
129 }
130
131 // Get user roles
132 database.all(
133 `SELECT r.* FROM roles r
134 JOIN user_roles ur ON r.role_id = ur.role_id
135 WHERE ur.user_id = ?`,
136 [id],
137 (err, roles) => {
138 if (err) {
139 callback(err, null);
140 } else {
141 row.roles = roles || [];
142 callback(null, row);
143 }
144 }
145 );
146 });
147}
148
149function createUser(id, username, email, password, userType, callback) {
150 const hashedPassword = bcrypt.hashSync(password, 10);
151 database.run(
152 'INSERT INTO users (id, username, email, password, user_type) VALUES (?, ?, ?, ?, ?)',
153 [id, username, email, hashedPassword, userType],
154 function(err) {
155 if (err) {
156 callback(err, null);
157 } else {
158 callback(null, id);
159 }
160 }
161 );
162}
163
164// Client functions
165function getClientByEmail(email, callback) {
166 database.get('SELECT * FROM client WHERE email = ?', [email], (err, row) => {
167 callback(err, row);
168 });
169}
170
171function getClientById(id, callback) {
172 database.get('SELECT * FROM client WHERE client_id = ?', [id], (err, row) => {
173 callback(err, row);
174 });
175}
176
177function createClient(clientData, callback) {
178 const hashedPassword = bcrypt.hashSync(clientData.password, 10);
179 database.run(
180 'INSERT INTO client (first_name, last_name, email, password) VALUES (?, ?, ?, ?)',
181 [clientData.first_name, clientData.last_name, clientData.email, hashedPassword],
182 function(err) {
183 if (err) {
184 callback(err, null);
185 } else {
186 callback(null, this.lastID);
187 }
188 }
189 );
190}
191
192function verifyClientPassword(password, hashedPassword, callback) {
193 try {
194 const isValid = bcrypt.compareSync(password, hashedPassword);
195 callback(null, isValid);
196 } catch (err) {
197 callback(err, false);
198 }
199}
200
201// Personal functions
202function getPersonalByEmail(email, callback) {
203 database.get('SELECT * FROM personal WHERE email = ?', [email], (err, row) => {
204 callback(err, row);
205 });
206}
207
208function getPersonalById(id, callback) {
209 database.get('SELECT * FROM personal WHERE id = ?', [id], (err, row) => {
210 callback(err, row);
211 });
212}
213
214// Password verification for regular users
215function verifyPassword(password, hashedPassword) {
216 return bcrypt.compareSync(password, hashedPassword);
217}
218
219// Password update
220function updatePasswordAndClearForce(userId, newPassword, callback) {
221 const hashedPassword = bcrypt.hashSync(newPassword, 10);
222 database.run(
223 'UPDATE users SET password = ?, force_password_change = 0 WHERE id = ?',
224 [hashedPassword, userId],
225 function(err) {
226 callback(err);
227 }
228 );
229}
230
231// Product functions
232function getProducts(categoryId, searchTerm, callback) {
233 let query = `
234 SELECT p.*, c.name as category_name, s.name as store_name
235 FROM product p
236 JOIN category c ON p.category_id = c.category_id
237 JOIN store s ON p.store_id = s.store_id
238 WHERE 1=1
239 `;
240 const params = [];
241
242 if (categoryId && categoryId !== 'all') {
243 query += ' AND p.category_id = ?';
244 params.push(categoryId);
245 }
246
247 if (searchTerm) {
248 query += ' AND (p.description LIKE ? OR p.code LIKE ?)';
249 params.push(`%${searchTerm}%`, `%${searchTerm}%`);
250 }
251
252 database.all(query, params, (err, rows) => {
253 if (err) {
254 callback(err, null);
255 } else {
256 callback(null, rows || []);
257 }
258 });
259}
260
261function getProductById(id, callback) {
262 database.get(
263 `SELECT p.*, c.name as category_name, s.name as store_name
264 FROM product p
265 JOIN category c ON p.category_id = c.category_id
266 JOIN store s ON p.store_id = s.store_id
267 WHERE p.id = ?`,
268 [id],
269 (err, row) => {
270 if (err || !row) {
271 callback(err, null);
272 } else {
273 // Get images for product
274 database.all(
275 'SELECT * FROM image WHERE product_code = ?',
276 [row.code],
277 (err, images) => {
278 if (err) {
279 callback(err, null);
280 } else {
281 row.images = images || [];
282 // Get colors for product
283 database.all(
284 'SELECT * FROM color WHERE product_code = ?',
285 [row.code],
286 (err, colors) => {
287 if (err) {
288 callback(err, null);
289 } else {
290 row.colors = colors || [];
291 callback(null, row);
292 }
293 }
294 );
295 }
296 }
297 );
298 }
299 }
300 );
301}
302
303function getProductByCode(code, callback) {
304 database.get(
305 `SELECT p.*, c.name as category_name, s.name as store_name
306 FROM product p
307 JOIN category c ON p.category_id = c.category_id
308 JOIN store s ON p.store_id = s.store_id
309 WHERE p.code = ?`,
310 [code],
311 (err, row) => {
312 if (err || !row) {
313 callback(err, null);
314 } else {
315 // Get images for product
316 database.all(
317 'SELECT * FROM image WHERE product_code = ?',
318 [code],
319 (err, images) => {
320 if (err) {
321 callback(err, null);
322 } else {
323 row.images = images || [];
324 // Get colors for product
325 database.all(
326 'SELECT * FROM color WHERE product_code = ?',
327 [code],
328 (err, colors) => {
329 if (err) {
330 callback(err, null);
331 } else {
332 row.colors = colors || [];
333 callback(null, row);
334 }
335 }
336 );
337 }
338 }
339 );
340 }
341 }
342 );
343}
344
345function addProduct(personalId, productData, callback) {
346 getGeneralCategoryId((err, generalCategoryId) => {
347 if (err) {
348 callback(err, null);
349 return;
350 }
351
352 const categoryId = productData.category_id || generalCategoryId;
353
354 // FIXED: Added validation for required fields
355 if (!productData.code) {
356 callback(new Error('Product code is required'), null);
357 return;
358 }
359
360 if (!productData.store_id) {
361 callback(new Error('Store ID is required'), null);
362 return;
363 }
364
365 database.run(
366 `INSERT INTO product (
367 id, code, description, price, availability, weight, dimensions,
368 production_time, category_id, store_id, created_at
369 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`,
370 [
371 'PROD_' + Date.now().toString().slice(-8), // Generate a unique ID
372 productData.code,
373 productData.description || 'No description',
374 productData.price || 0,
375 productData.availability || 0,
376 productData.weight || 0,
377 productData.dimensions || '0x0x0',
378 productData.production_time || 1,
379 categoryId,
380 productData.store_id
381 ],
382 function(err) {
383 if (err) {
384 callback(err, null);
385 } else {
386 const productId = this.lastID;
387
388 // Log the change
389 database.run(
390 `INSERT INTO "change" (date_and_time, product_code, changes)
391 VALUES (datetime('now'), ?, ?)`,
392 [productData.code, 'Product created'],
393 function(err) {
394 if (err) {
395 console.error('Error logging product creation:', err);
396 }
397 }
398 );
399
400 // Log who made the change
401 database.run(
402 `INSERT INTO makes_change (personal_id, change_date_time, product_code)
403 VALUES (?, datetime('now'), ?)`,
404 [personalId, productData.code],
405 function(err) {
406 if (err) {
407 console.error('Error logging change maker:', err);
408 }
409 }
410 );
411
412 // Insert images if provided
413 if (productData.images && Array.isArray(productData.images) && productData.images.length > 0) {
414 productData.images.forEach((imageUrl, index) => {
415 database.run(
416 `INSERT INTO image (product_code, image_url, is_primary)
417 VALUES (?, ?, ?)`,
418 [productData.code, imageUrl, index === 0 ? 1 : 0],
419 function(err) {
420 if (err) {
421 console.error('Error inserting image:', err);
422 }
423 }
424 );
425 });
426 }
427
428 // Insert colors if provided
429 if (productData.colors && Array.isArray(productData.colors) && productData.colors.length > 0) {
430 productData.colors.forEach(color => {
431 database.run(
432 `INSERT INTO color (product_code, name)
433 VALUES (?, ?)`,
434 [productData.code, color],
435 function(err) {
436 if (err) {
437 console.error('Error inserting color:', err);
438 }
439 }
440 );
441 });
442 }
443
444 callback(null, productId);
445 }
446 }
447 );
448 });
449}
450
451function updateProduct(personalId, productData, callback) {
452 const updates = [];
453 const params = [];
454
455 if (productData.description !== undefined) {
456 updates.push('description = ?');
457 params.push(productData.description);
458 }
459
460 if (productData.price !== undefined) {
461 updates.push('price = ?');
462 params.push(productData.price);
463 }
464
465 if (productData.availability !== undefined) {
466 updates.push('availability = ?');
467 params.push(productData.availability);
468 }
469
470 if (productData.weight !== undefined) {
471 updates.push('weight = ?');
472 params.push(productData.weight);
473 }
474
475 if (productData.dimensions !== undefined) {
476 updates.push('dimensions = ?');
477 params.push(productData.dimensions);
478 }
479
480 if (productData.production_time !== undefined) {
481 updates.push('production_time = ?');
482 params.push(productData.production_time);
483 }
484
485 if (productData.category_id !== undefined) {
486 updates.push('category_id = ?');
487 params.push(productData.category_id);
488 }
489
490 if (updates.length === 0) {
491 callback(null, 0);
492 return;
493 }
494
495 params.push(productData.code);
496
497 database.run(
498 `UPDATE product SET ${updates.join(', ')} WHERE code = ?`,
499 params,
500 function(err) {
501 if (err) {
502 callback(err, null);
503 } else {
504 // Log the change
505 const changesDesc = `Product updated: ${updates.join(', ')}`;
506 database.run(
507 `INSERT INTO "change" (date_and_time, product_code, changes)
508 VALUES (datetime('now'), ?, ?)`,
509 [productData.code, changesDesc],
510 function(err) {
511 if (err) {
512 console.error('Error logging product update:', err);
513 }
514 // Log who made the change
515 database.run(
516 `INSERT INTO makes_change (personal_id, change_date_time, product_code)
517 VALUES (?, datetime('now'), ?)`,
518 [personalId, productData.code],
519 function(err) {
520 if (err) {
521 console.error('Error logging change maker:', err);
522 }
523 }
524 );
525 }
526 );
527
528 // Handle images if provided
529 if (productData.images && Array.isArray(productData.images)) {
530 // Delete old images first
531 database.run('DELETE FROM image WHERE product_code = ?', [productData.code], (err) => {
532 if (!err) {
533 // Insert new images
534 productData.images.forEach(image => {
535 database.run(
536 'INSERT INTO image (product_code, image) VALUES (?, ?)',
537 [productData.code, image]
538 );
539 });
540 }
541 });
542 }
543
544 // Handle colors if provided
545 if (productData.colors && Array.isArray(productData.colors)) {
546 // Delete old colors first
547 database.run('DELETE FROM color WHERE product_code = ?', [productData.code], (err) => {
548 if (!err) {
549 // Insert new colors
550 productData.colors.forEach(color => {
551 database.run(
552 'INSERT INTO color (product_code, color) VALUES (?, ?)',
553 [productData.code, color]
554 );
555 });
556 }
557 });
558 }
559
560 callback(null, this.changes);
561 }
562 }
563 );
564}
565
566function deleteProduct(productCode, storeId, personalId, callback) {
567 database.run('BEGIN TRANSACTION', (err) => {
568 if (err) {
569 callback(err);
570 return;
571 }
572
573 // Log the deletion
574 database.run(
575 `INSERT INTO "change" (date_and_time, product_code, changes)
576 VALUES (datetime('now'), ?, ?)`,
577 [productCode, 'Product deleted'],
578 function(err) {
579 if (err) {
580 database.run('ROLLBACK');
581 callback(err);
582 return;
583 }
584
585 // Log who deleted it
586 database.run(
587 `INSERT INTO makes_change (personal_id, change_date_time, product_code)
588 VALUES (?, datetime('now'), ?)`,
589 [personalId, productCode],
590 function(err) {
591 if (err) {
592 database.run('ROLLBACK');
593 callback(err);
594 return;
595 }
596
597 // Delete the product (cascades to image, color)
598 database.run(
599 'DELETE FROM product WHERE code = ? AND store_id = ?',
600 [productCode, storeId],
601 function(err) {
602 if (err) {
603 database.run('ROLLBACK');
604 callback(err);
605 } else {
606 database.run('COMMIT', callback);
607 }
608 }
609 );
610 }
611 );
612 }
613 );
614 });
615}
616
617// Category functions
618function getCategories(callback) {
619 database.all('SELECT * FROM category ORDER BY name', [], (err, rows) => {
620 callback(err, rows || []);
621 });
622}
623
624function getCategoriesWithParents(callback) {
625 database.all(
626 `SELECT c1.*, c2.name as parent_name
627 FROM category c1
628 LEFT JOIN category c2 ON c1.parent_category_id = c2.category_id
629 ORDER BY c1.name`,
630 [],
631 (err, rows) => {
632 callback(err, rows || []);
633 }
634 );
635}
636
637function createCategory(categoryData, callback) {
638 database.run(
639 'INSERT INTO category (name, description, parent_category_id) VALUES (?, ?, ?)',
640 [categoryData.name, categoryData.description || null, categoryData.parent_id || null],
641 function(err) {
642 if (err) {
643 callback(err, null);
644 } else {
645 callback(null, {
646 id: this.lastID,
647 name: categoryData.name,
648 parent_id: categoryData.parent_id,
649 description: categoryData.description
650 });
651 }
652 }
653 );
654}
655
656// Store functions
657function getStores(callback) {
658 database.all('SELECT * FROM store ORDER BY name', [], (err, rows) => {
659 callback(err, rows || []);
660 });
661}
662
663function getStoreProducts(storeId, callback) {
664 database.all(
665 `SELECT p.*, c.name as category_name
666 FROM product p
667 JOIN category c ON p.category_id = c.category_id
668 WHERE p.store_id = ?
669 ORDER BY p.code`,
670 [storeId],
671 (err, rows) => {
672 if (err) {
673 callback(err, null);
674 } else {
675 callback(null, rows || []);
676 }
677 }
678 );
679}
680
681function getStoreOrders(storeId, callback) {
682 database.all(
683 `SELECT o.*, c.first_name, c.last_name
684 FROM "order" o
685 JOIN client c ON o.client_id = c.client_id
686 WHERE o.store_id = ?
687 ORDER BY o.order_date DESC`,
688 [storeId],
689 (err, rows) => {
690 if (err) {
691 callback(err, null);
692 } else {
693 // Get order items for each order
694 let completed = 0;
695 const orders = rows || [];
696
697 if (orders.length === 0) {
698 callback(null, []);
699 return;
700 }
701
702 orders.forEach(order => {
703 database.all(
704 `SELECT oi.*, p.description
705 FROM order_items oi
706 JOIN product p ON oi.product_code = p.code
707 WHERE oi.order_num = ?`,
708 [order.order_num],
709 (err, items) => {
710 if (!err) {
711 order.items = items || [];
712 } else {
713 order.items = [];
714 }
715 completed++;
716 if (completed === orders.length) {
717 callback(null, orders);
718 }
719 }
720 );
721 });
722 }
723 }
724 );
725}
726
727function getStoreEmployees(storeId, callback) {
728 database.all(
729 `SELECT p.*, e.date_of_hire, perm.type as permission_type, perm.authorisation
730 FROM personal p
731 JOIN works_in_store w ON p.id = w.personal_id
732 LEFT JOIN employees e ON p.id = e.employee_id
733 LEFT JOIN permissions perm ON p.id = perm.personal_id
734 WHERE w.store_id = ?`,
735 [storeId],
736 (err, rows) => {
737 callback(err, rows || []);
738 }
739 );
740}
741
742function getStoreReports(storeId, callback) {
743 database.all(
744 `SELECT * FROM report
745 WHERE store_id = ?
746 ORDER BY generated_at DESC`,
747 [storeId],
748 (err, rows) => {
749 callback(err, rows || []);
750 }
751 );
752}
753
754function getStoreStats(storeId, callback) {
755 const stats = {};
756
757 // Get total products
758 database.get(
759 'SELECT COUNT(*) as total_products FROM product WHERE store_id = ?',
760 [storeId],
761 (err, row) => {
762 stats.total_products = row ? row.total_products : 0;
763
764 // Get total orders
765 database.get(
766 'SELECT COUNT(*) as total_orders FROM "order" WHERE store_id = ?',
767 [storeId],
768 (err, row) => {
769 stats.total_orders = row ? row.total_orders : 0;
770
771 // Get total revenue
772 database.get(
773 `SELECT SUM(oi.price * oi.quantity) as total_revenue
774 FROM order_items oi
775 JOIN "order" o ON oi.order_num = o.order_num
776 WHERE o.store_id = ?`,
777 [storeId],
778 (err, row) => {
779 stats.total_revenue = row && row.total_revenue ? row.total_revenue : 0;
780
781 // Get average rating
782 database.get(
783 `SELECT AVG(rating) as avg_rating
784 FROM review r
785 JOIN product p ON r.product_code = p.code
786 WHERE p.store_id = ?`,
787 [storeId],
788 (err, row) => {
789 stats.avg_rating = row && row.avg_rating ? row.avg_rating : 0;
790 callback(null, stats);
791 }
792 );
793 }
794 );
795 }
796 );
797 }
798 );
799}
800
801// Order functions
802function createOrderNew(orderData, callback) {
803 database.run('BEGIN TRANSACTION', (err) => {
804 if (err) {
805 callback(err, null);
806 return;
807 }
808
809 database.run(
810 `INSERT INTO "order" (order_num, client_id, order_date, quantity, payment_method,
811 discount, delivery_address, store_id)
812 VALUES (?, ?, datetime('now'), ?, ?, ?, ?, ?)`,
813 [
814 orderData.order_num,
815 orderData.client_id,
816 orderData.quantity,
817 orderData.payment_method,
818 orderData.discount,
819 orderData.delivery_address,
820 orderData.store_id
821 ],
822 function(err) {
823 if (err) {
824 database.run('ROLLBACK');
825 callback(err, null);
826 return;
827 }
828
829 let itemsInserted = 0;
830 const items = orderData.items || [];
831
832 if (items.length === 0) {
833 database.run('COMMIT');
834 callback(null, orderData.order_num);
835 return;
836 }
837
838 items.forEach(item => {
839 database.run(
840 `INSERT INTO order_items (order_num, product_code, quantity, price)
841 VALUES (?, ?, ?, ?)`,
842 [orderData.order_num, item.product_code, item.quantity, item.price],
843 function(err) {
844 if (err) {
845 database.run('ROLLBACK');
846 callback(err, null);
847 return;
848 }
849
850 itemsInserted++;
851 if (itemsInserted === items.length) {
852 database.run('COMMIT', (err) => {
853 if (err) {
854 callback(err, null);
855 } else {
856 callback(null, orderData.order_num);
857 }
858 });
859 }
860 }
861 );
862 });
863 }
864 );
865 });
866}
867
868function getOrdersByClient(clientId, callback) {
869 database.all(
870 `SELECT o.*, s.name as store_name
871 FROM "order" o
872 JOIN store s ON o.store_id = s.store_id
873 WHERE o.client_id = ?
874 ORDER BY o.order_date DESC`,
875 [clientId],
876 (err, rows) => {
877 if (err) {
878 callback(err, null);
879 } else {
880 // Get order items for each order
881 let completed = 0;
882 const orders = rows || [];
883
884 if (orders.length === 0) {
885 callback(null, []);
886 return;
887 }
888
889 orders.forEach(order => {
890 database.all(
891 `SELECT oi.*, p.description
892 FROM order_items oi
893 JOIN product p ON oi.product_code = p.code
894 WHERE oi.order_num = ?`,
895 [order.order_num],
896 (err, items) => {
897 if (!err) {
898 order.items = items || [];
899 } else {
900 order.items = [];
901 }
902 completed++;
903 if (completed === orders.length) {
904 callback(null, orders);
905 }
906 }
907 );
908 });
909 }
910 }
911 );
912}
913
914function getAllOrders(callback) {
915 database.all(
916 `SELECT o.*, c.first_name, c.last_name, s.name as store_name
917 FROM "order" o
918 JOIN client c ON o.client_id = c.client_id
919 JOIN store s ON o.store_id = s.store_id
920 ORDER BY o.order_date DESC`,
921 [],
922 (err, rows) => {
923 callback(err, rows || []);
924 }
925 );
926}
927
928// Review functions
929function createReviewNew(reviewData, callback) {
930 database.run(
931 `INSERT INTO review (review_id, client_id, product_code, rating, comment, review_date)
932 VALUES (?, ?, ?, ?, ?, datetime('now'))`,
933 [
934 'REV' + Date.now().toString().slice(-8),
935 reviewData.client_id,
936 reviewData.product_code,
937 reviewData.rating,
938 reviewData.comment || ''
939 ],
940 function(err) {
941 if (err) {
942 callback(err, null);
943 } else {
944 callback(null, this.lastID);
945 }
946 }
947 );
948}
949
950// Request functions
951function createRequest(requestData, callback) {
952 database.run(
953 `INSERT INTO request (request_num, date_and_time, problem, client_id, store_id)
954 VALUES (?, ?, ?, ?, ?)`,
955 [
956 requestData.request_num,
957 requestData.date_and_time,
958 requestData.problem,
959 requestData.client_id,
960 requestData.store_id
961 ],
962 function(err) {
963 if (err) {
964 callback(err, null);
965 } else {
966 callback(null, requestData.request_num);
967 }
968 }
969 );
970}
971
972// Refund functions
973function createRefund(refundData, callback) {
974 database.run(
975 `INSERT INTO refund (refund_id, order_num, amount, reason, request_date)
976 VALUES (?, ?, ?, ?, datetime('now'))`,
977 [
978 refundData.refund_id,
979 refundData.order_num,
980 refundData.amount,
981 refundData.reason
982 ],
983 function(err) {
984 if (err) {
985 callback(err, null);
986 } else {
987 callback(null, refundData.refund_id);
988 }
989 }
990 );
991}
992
993// Employee task functions
994function getEmployeeTasks(personalId, storeId, callback) {
995 const tasks = {
996 pending_orders: [],
997 pending_requests: [],
998 pending_refunds: []
999 };
1000
1001 // Get pending orders
1002 database.all(
1003 `SELECT o.*, c.first_name, c.last_name
1004 FROM "order" o
1005 JOIN client c ON o.client_id = c.client_id
1006 WHERE o.store_id = ? AND o.status = 'pending'
1007 ORDER BY o.order_date ASC`,
1008 [storeId],
1009 (err, rows) => {
1010 if (!err) {
1011 tasks.pending_orders = rows || [];
1012 }
1013
1014 // Get pending requests
1015 database.all(
1016 `SELECT r.*, c.first_name, c.last_name
1017 FROM request r
1018 JOIN client c ON r.client_id = c.client_id
1019 WHERE r.store_id = ? AND r.status = 'pending'
1020 ORDER BY r.date_and_time ASC`,
1021 [storeId],
1022 (err, rows) => {
1023 if (!err) {
1024 tasks.pending_requests = rows || [];
1025 }
1026
1027 // Get pending refunds
1028 database.all(
1029 `SELECT rf.*, o.client_id, c.first_name, c.last_name
1030 FROM refund rf
1031 JOIN "order" o ON rf.order_num = o.order_num
1032 JOIN client c ON o.client_id = c.client_id
1033 WHERE o.store_id = ? AND rf.status = 'pending'
1034 ORDER BY rf.request_date ASC`,
1035 [storeId],
1036 (err, rows) => {
1037 if (!err) {
1038 tasks.pending_refunds = rows || [];
1039 }
1040 callback(null, tasks);
1041 }
1042 );
1043 }
1044 );
1045 }
1046 );
1047}
1048
1049// Client stats functions
1050function getClientStats(clientId, callback) {
1051 const stats = {};
1052
1053 // Get total orders
1054 database.get(
1055 'SELECT COUNT(*) as total_orders FROM "order" WHERE client_id = ?',
1056 [clientId],
1057 (err, row) => {
1058 stats.total_orders = row ? row.total_orders : 0;
1059
1060 // Get total spent
1061 database.get(
1062 `SELECT SUM(oi.price * oi.quantity) as total_spent
1063 FROM order_items oi
1064 JOIN "order" o ON oi.order_num = o.order_num
1065 WHERE o.client_id = ?`,
1066 [clientId],
1067 (err, row) => {
1068 stats.total_spent = row && row.total_spent ? row.total_spent : 0;
1069
1070 // Get pending orders
1071 database.get(
1072 'SELECT COUNT(*) as pending_orders FROM "order" WHERE client_id = ? AND status = "pending"',
1073 [clientId],
1074 (err, row) => {
1075 stats.pending_orders = row ? row.pending_orders : 0;
1076
1077 // Get delivered orders
1078 database.get(
1079 'SELECT COUNT(*) as delivered_orders FROM "order" WHERE client_id = ? AND status = "delivered"',
1080 [clientId],
1081 (err, row) => {
1082 stats.delivered_orders = row ? row.delivered_orders : 0;
1083 callback(null, stats);
1084 }
1085 );
1086 }
1087 );
1088 }
1089 );
1090 }
1091 );
1092}
1093
1094// User functions for admin
1095function getAllUsers(callback) {
1096 const users = [];
1097
1098 // Get client users
1099 database.all(
1100 `SELECT client_id as id, first_name, last_name, email, 'client' as user_type
1101 FROM client
1102 ORDER BY client_id`,
1103 [],
1104 (err, rows) => {
1105 if (!err) {
1106 users.push(...(rows || []));
1107 }
1108
1109 // Get personal users
1110 database.all(
1111 `SELECT p.id, p.first_name, p.last_name, p.email,
1112 CASE WHEN b.boss_id IS NOT NULL THEN 'store_owner'
1113 WHEN e.employee_id IS NOT NULL THEN 'store_employee'
1114 ELSE 'personal' END as user_type
1115 FROM personal p
1116 LEFT JOIN boss b ON p.id = b.boss_id
1117 LEFT JOIN employees e ON p.id = e.employee_id
1118 ORDER BY p.id`,
1119 [],
1120 (err, rows) => {
1121 if (!err) {
1122 users.push(...(rows || []));
1123 }
1124
1125 // Get system users
1126 database.all(
1127 `SELECT id, username, email, user_type
1128 FROM users
1129 ORDER BY id`,
1130 [],
1131 (err, rows) => {
1132 if (!err) {
1133 users.push(...(rows || []));
1134 }
1135 callback(null, users);
1136 }
1137 );
1138 }
1139 );
1140 }
1141 );
1142}
1143
1144// Audit log function
1145function logAudit(userId, action, resourceType, resourceId, details, ipAddress) {
1146 database.run(
1147 `INSERT INTO audit_log (user_id, action, resource_type, resource_id, details, ip_address)
1148 VALUES (?, ?, ?, ?, ?, ?)`,
1149 [userId, action, resourceType, resourceId, details, ipAddress],
1150 (err) => {
1151 if (err) {
1152 console.error('Error logging audit:', err);
1153 }
1154 }
1155 );
1156}
1157
1158module.exports = {
1159 database,
1160 ensureGeneralCategory,
1161 getGeneralCategoryId,
1162 getUserByUsername,
1163 getUserById,
1164 createUser,
1165 getClientByEmail,
1166 getClientById,
1167 createClient,
1168 verifyClientPassword,
1169 getPersonalByEmail,
1170 getPersonalById,
1171 verifyPassword,
1172 updatePasswordAndClearForce,
1173 getProducts,
1174 getProductById,
1175 getProductByCode,
1176 addProduct,
1177 updateProduct,
1178 deleteProduct,
1179 getCategories,
1180 getCategoriesWithParents,
1181 createCategory,
1182 getStores,
1183 getStoreProducts,
1184 getStoreOrders,
1185 getStoreEmployees,
1186 getStoreReports,
1187 getStoreStats,
1188 createOrderNew,
1189 getOrdersByClient,
1190 getAllOrders,
1191 createReviewNew,
1192 createRequest,
1193 createRefund,
1194 getEmployeeTasks,
1195 getClientStats,
1196 getAllUsers,
1197 logAudit
1198};
Note: See TracBrowser for help on using the repository browser.