source: database.js@ 62b2964

finki-main main
Last change on this file since 62b2964 was 62b2964, checked in by Klimentina Efremova <klimentina08642@…>, 2 weeks ago

Project Handcraft Marketplace
  • Property mode set to 100644
File size: 60.3 KB
RevLine 
[62b2964]1const { Pool } = require('pg');
[69f2a41]2const bcrypt = require('bcryptjs');
[62b2964]3require('dotenv').config();
4
5/*
6 * ============================================================
7 * PostgreSQL CONNECTION
8 * ============================================================
9 *
10 * Put your actual FINKI connection information in .env
11 *
12 * Example:
13 *
14 * PGHOST=localhost
15 * PGPORT=5432
16 * PGDATABASE=handcraft
17 * PGUSER=your_username
18 * PGPASSWORD=your_password
19 *
20 * If you use the SSH tunnel, PGHOST/PGPORT will normally
21 * point to the LOCAL end of the SSH tunnel.
22 */
23
24const pool = new Pool({
25 host: process.env.PGHOST || 'localhost',
26 port: parseInt(process.env.PGPORT || '5432', 10),
27 database: process.env.PGDATABASE || 'handcraft',
28 user: process.env.PGUSER || 'postgres',
29 password: process.env.PGPASSWORD || '',
30 max: 10,
31 idleTimeoutMillis: 30000,
32 connectionTimeoutMillis: 10000
33});
34
35pool.on('connect', () => {
36 console.log('✅ Connected to PostgreSQL database');
37});
[69f2a41]38
[62b2964]39pool.on('error', (err) => {
40 console.error('❌ Unexpected PostgreSQL pool error:', err);
41});
[69f2a41]42
[62b2964]43/*
44 * ============================================================
45 * HELPER
46 * ============================================================
47 */
48
49function query(text, params = [], callback) {
50 pool.query(text, params)
51 .then(result => {
52 callback(null, result);
53 })
54 .catch(err => {
55 callback(err, null);
56 });
[69f2a41]57}
58
59
[62b2964]60/*
61 * ============================================================
62 * GENERAL CATEGORY
63 * ============================================================
64 */
[69f2a41]65
66function ensureGeneralCategory(callback) {
[62b2964]67
68 query(
69 `SELECT category_id, name
70 FROM category
71 WHERE category_id = 1`,
72 [],
73 (err, result) => {
74
75 if (err) {
76 callback(err);
77 return;
78 }
79
80 const row = result.rows[0];
81
82 if (row && row.name === 'General') {
83
84 console.log('✅ General category exists with ID: 1');
85 callback(null);
86
87 } else if (row && row.name !== 'General') {
88
89 query(
90 `UPDATE category
91 SET name = $1,
92 description = $2
93 WHERE category_id = 1`,
94 ['General', 'General products category'],
95 (err) => {
96
97 if (err) {
98 callback(err);
99 } else {
100 console.log('✅ Updated category ID 1 to General');
101 callback(null);
102 }
[591278c]103 }
[62b2964]104 );
105
106 } else {
107
108 query(
109 `INSERT INTO category
110 (category_id, name, description)
111 VALUES
112 (1, $1, $2)
113 ON CONFLICT (category_id) DO NOTHING`,
114 ['General', 'General products category'],
115 (err) => {
116
117 if (err) {
118 callback(err);
119 } else {
120 console.log('✅ Created General category with ID: 1');
121 callback(null);
122 }
[591278c]123 }
[62b2964]124 );
125 }
[69f2a41]126 }
[62b2964]127 );
[69f2a41]128}
129
[62b2964]130
[69f2a41]131function getGeneralCategoryId(callback) {
[62b2964]132
133 query(
134 `SELECT category_id
135 FROM category
136 WHERE category_id = 1
137 AND name = $1`,
138 ['General'],
139 (err, result) => {
140
141 if (err) {
142 callback(err, null);
143 return;
144 }
145
146 if (result.rows.length > 0) {
147 callback(null, result.rows[0].category_id);
148 return;
149 }
150
151 query(
152 `SELECT category_id
153 FROM category
154 WHERE name = $1`,
155 ['General'],
156 (err, result) => {
157
158 if (err) {
159 callback(err, null);
160 return;
161 }
162
163 if (result.rows.length > 0) {
164 callback(null, result.rows[0].category_id);
165 return;
166 }
167
168 query(
169 `INSERT INTO category
170 (name, description)
171 VALUES
172 ($1, $2)
173 RETURNING category_id`,
[591278c]174 ['General', 'General products category'],
[62b2964]175 (err, result) => {
176
[591278c]177 if (err) {
178 callback(err, null);
179 } else {
[62b2964]180 const newId = result.rows[0].category_id;
181
182 console.log(
183 `✅ Created new General category with ID: ${newId}`
184 );
185
[591278c]186 callback(null, newId);
187 }
188 }
189 );
[69f2a41]190 }
[62b2964]191 );
[69f2a41]192 }
[62b2964]193 );
[69f2a41]194}
195
[62b2964]196
197/*
198 * ============================================================
199 * USER FUNCTIONS
200 * ============================================================
201 */
202
[69f2a41]203function getUserByUsername(username, callback) {
[62b2964]204
205 query(
206 `SELECT *
207 FROM users
208 WHERE username = $1`,
209 [username],
210 (err, result) => {
211
212 callback(
213 err,
214 result ? result.rows[0] : null
215 );
216 }
217 );
[69f2a41]218}
219
[62b2964]220
[69f2a41]221function getUserById(id, callback) {
222
[62b2964]223 query(
224 `SELECT *
225 FROM users
226 WHERE id = $1`,
227 [id],
228 (err, result) => {
229
230 if (err || !result || result.rows.length === 0) {
231 callback(err, null);
232 return;
[69f2a41]233 }
[62b2964]234
235 const row = result.rows[0];
236
237 query(
238 `SELECT r.*
239 FROM roles r
240 JOIN user_roles ur
241 ON r.role_id = ur.role_id
242 WHERE ur.user_id = $1`,
243 [id],
244 (err, result) => {
245
246 if (err) {
247 callback(err, null);
248 } else {
249 row.roles = result.rows || [];
250 callback(null, row);
251 }
252 }
253 );
254 }
255 );
[69f2a41]256}
257
[62b2964]258
[69f2a41]259function createUser(id, username, email, password, userType, callback) {
[62b2964]260
[69f2a41]261 const hashedPassword = bcrypt.hashSync(password, 10);
[62b2964]262
263 query(
264 `INSERT INTO users
265 (id, username, email, password, user_type)
266 VALUES
267 ($1, $2, $3, $4, $5)`,
[69f2a41]268 [id, username, email, hashedPassword, userType],
[62b2964]269 (err) => {
270
[69f2a41]271 if (err) {
272 callback(err, null);
273 } else {
274 callback(null, id);
275 }
276 }
277 );
278}
279
[62b2964]280
281/*
282 * ============================================================
283 * CLIENT FUNCTIONS
284 * ============================================================
285 */
286
[69f2a41]287function getClientByEmail(email, callback) {
[62b2964]288
289 query(
290 `SELECT *
291 FROM client
292 WHERE email = $1`,
293 [email],
294 (err, result) => {
295
296 callback(
297 err,
298 result ? result.rows[0] : null
299 );
300 }
301 );
[69f2a41]302}
303
[62b2964]304
[69f2a41]305function getClientById(id, callback) {
[62b2964]306
307 query(
308 `SELECT *
309 FROM client
310 WHERE client_id = $1`,
311 [id],
312 (err, result) => {
313
314 callback(
315 err,
316 result ? result.rows[0] : null
317 );
318 }
319 );
[69f2a41]320}
321
[62b2964]322
[69f2a41]323function createClient(clientData, callback) {
[62b2964]324
325 const hashedPassword =
326 bcrypt.hashSync(clientData.password, 10);
327
328 query(
329 `INSERT INTO client
330 (first_name, last_name, email, password)
331 VALUES
332 ($1, $2, $3, $4)
333 RETURNING client_id`,
334 [
335 clientData.first_name,
336 clientData.last_name,
337 clientData.email,
338 hashedPassword
339 ],
340 (err, result) => {
341
[69f2a41]342 if (err) {
343 callback(err, null);
344 } else {
[62b2964]345 callback(
346 null,
347 result.rows[0].client_id
348 );
[69f2a41]349 }
350 }
351 );
352}
353
[62b2964]354
355function verifyClientPassword(
356 password,
357 hashedPassword,
358 callback
359) {
360
[69f2a41]361 try {
[62b2964]362
363 const isValid =
364 bcrypt.compareSync(password, hashedPassword);
365
[69f2a41]366 callback(null, isValid);
[62b2964]367
[69f2a41]368 } catch (err) {
[62b2964]369
[69f2a41]370 callback(err, false);
371 }
372}
373
[62b2964]374
375/*
376 * ============================================================
377 * PERSONAL / EMPLOYEE FUNCTIONS
378 * ============================================================
379 */
380
[69f2a41]381function getPersonalByEmail(email, callback) {
[62b2964]382
383 query(
384 `SELECT *
385 FROM personal
386 WHERE email = $1`,
387 [email],
388 (err, result) => {
389
390 callback(
391 err,
392 result ? result.rows[0] : null
393 );
394 }
395 );
[69f2a41]396}
397
[62b2964]398
[69f2a41]399function getPersonalById(id, callback) {
[62b2964]400
401 query(
402 `SELECT *
403 FROM personal
404 WHERE id = $1`,
405 [id],
406 (err, result) => {
407
408 callback(
409 err,
410 result ? result.rows[0] : null
411 );
412 }
413 );
[69f2a41]414}
415
[62b2964]416
[69f2a41]417function verifyPassword(password, hashedPassword) {
418 return bcrypt.compareSync(password, hashedPassword);
419}
420
[62b2964]421
422function updatePasswordAndClearForce(
423 userId,
424 newPassword,
425 callback
426) {
427
428 const hashedPassword =
429 bcrypt.hashSync(newPassword, 10);
430
431 query(
432 `UPDATE users
433 SET password = $1,
434 force_password_change = 0
435 WHERE id = $2`,
[69f2a41]436 [hashedPassword, userId],
[62b2964]437 (err) => {
438
[69f2a41]439 callback(err);
440 }
441 );
442}
443
[62b2964]444
445/*
446 * ============================================================
447 * PRODUCT FUNCTIONS
448 * ============================================================
449 */
450
[69f2a41]451function getProducts(categoryId, searchTerm, callback) {
[62b2964]452
453 let queryText = `
454 SELECT
455 p.*,
456 c.name AS category_name,
457 s.name AS store_name
458 FROM product p
459 JOIN category c
460 ON p.category_id = c.category_id
461 JOIN store s
462 ON p.store_id = s.store_id
463 WHERE 1 = 1
464 `;
465
[69f2a41]466 const params = [];
[62b2964]467 let paramIndex = 1;
[69f2a41]468
469 if (categoryId && categoryId !== 'all') {
[62b2964]470
471 queryText +=
472 ` AND p.category_id = $${paramIndex}`;
473
[69f2a41]474 params.push(categoryId);
[62b2964]475 paramIndex++;
[69f2a41]476 }
477
478 if (searchTerm) {
[62b2964]479
480 queryText +=
481 ` AND (
482 p.description ILIKE $${paramIndex}
483 OR p.code ILIKE $${paramIndex + 1}
484 )`;
485
486 params.push(`%${searchTerm}%`);
487 params.push(`%${searchTerm}%`);
488
489 paramIndex += 2;
[69f2a41]490 }
491
[62b2964]492 queryText += ` ORDER BY p.code`;
493
494 query(
495 queryText,
496 params,
497 (err, result) => {
498
499 if (err) {
500 callback(err, null);
501 } else {
502 callback(null, result.rows || []);
503 }
[69f2a41]504 }
[62b2964]505 );
[69f2a41]506}
507
[62b2964]508
[69f2a41]509function getProductById(id, callback) {
[62b2964]510
511 query(
512 `SELECT
513 p.*,
514 c.name AS category_name,
515 s.name AS store_name
516 FROM product p
517 JOIN category c
518 ON p.category_id = c.category_id
519 JOIN store s
520 ON p.store_id = s.store_id
521 WHERE p.id = $1`,
[69f2a41]522 [id],
[62b2964]523 (err, result) => {
524
525 if (err || result.rows.length === 0) {
[69f2a41]526 callback(err, null);
[62b2964]527 return;
[69f2a41]528 }
[62b2964]529
530 const row = result.rows[0];
531
532 query(
533 `SELECT *
534 FROM image
535 WHERE product_code = $1`,
536 [row.code],
537 (err, result) => {
538
539 if (err) {
540 callback(err, null);
541 return;
542 }
543
544 row.images = result.rows || [];
545
546 query(
547 `SELECT *
548 FROM color
549 WHERE product_code = $1`,
550 [row.code],
551 (err, result) => {
552
553 if (err) {
554 callback(err, null);
555 } else {
556 row.colors =
557 result.rows || [];
558
559 callback(null, row);
560 }
561 }
562 );
563 }
564 );
[69f2a41]565 }
566 );
567}
568
[62b2964]569
[69f2a41]570function getProductByCode(code, callback) {
[62b2964]571
572 query(
573 `SELECT
574 p.*,
575 c.name AS category_name,
576 s.name AS store_name
577 FROM product p
578 JOIN category c
579 ON p.category_id = c.category_id
580 JOIN store s
581 ON p.store_id = s.store_id
582 WHERE p.code = $1`,
[69f2a41]583 [code],
[62b2964]584 (err, result) => {
585
586 if (err || result.rows.length === 0) {
[69f2a41]587 callback(err, null);
[62b2964]588 return;
[69f2a41]589 }
[62b2964]590
591 const row = result.rows[0];
592
593 query(
594 `SELECT *
595 FROM image
596 WHERE product_code = $1`,
597 [code],
598 (err, result) => {
599
600 if (err) {
601 callback(err, null);
602 return;
603 }
604
605 row.images = result.rows || [];
606
607 query(
608 `SELECT *
609 FROM color
610 WHERE product_code = $1`,
611 [code],
612 (err, result) => {
613
614 if (err) {
615 callback(err, null);
616 } else {
617
618 row.colors =
619 result.rows || [];
620
621 callback(null, row);
622 }
623 }
624 );
625 }
626 );
[69f2a41]627 }
628 );
629}
630
[62b2964]631
[69f2a41]632function addProduct(personalId, productData, callback) {
[62b2964]633
[69f2a41]634 getGeneralCategoryId((err, generalCategoryId) => {
[62b2964]635
[69f2a41]636 if (err) {
637 callback(err, null);
638 return;
639 }
640
[62b2964]641 const categoryId =
642 productData.category_id || generalCategoryId;
[69f2a41]643
644 if (!productData.code) {
[62b2964]645 callback(
646 new Error('Product code is required'),
647 null
648 );
[69f2a41]649 return;
650 }
651
652 if (!productData.store_id) {
[62b2964]653 callback(
654 new Error('Store ID is required'),
655 null
656 );
[69f2a41]657 return;
658 }
659
[62b2964]660 const productId =
661 'PROD_' +
662 Date.now().toString().slice(-8);
663
664 query(
665 `INSERT INTO product
666 (
667 id,
668 code,
669 description,
670 price,
671 availability,
672 weight,
673 dimensions,
674 production_time,
675 category_id,
676 store_id,
677 created_at
678 )
679 VALUES
680 (
681 $1,
682 $2,
683 $3,
684 $4,
685 $5,
686 $6,
687 $7,
688 $8,
689 $9,
690 $10,
691 NOW()
692 )
693 RETURNING id`,
[69f2a41]694 [
[62b2964]695 productId,
[69f2a41]696 productData.code,
697 productData.description || 'No description',
698 productData.price || 0,
699 productData.availability || 0,
700 productData.weight || 0,
701 productData.dimensions || '0x0x0',
702 productData.production_time || 1,
703 categoryId,
704 productData.store_id
705 ],
[62b2964]706 (err, result) => {
707
[69f2a41]708 if (err) {
709 callback(err, null);
[62b2964]710 return;
711 }
[69f2a41]712
[62b2964]713 const returnedId = result.rows[0].id;
714
715 /*
716 * Log product creation
717 */
718 query(
719 `INSERT INTO "change"
720 (date_and_time, product_code, changes)
721 VALUES
722 (NOW(), $1, $2)`,
723 [
724 productData.code,
725 'Product created'
726 ],
727 (err) => {
728
729 if (err) {
730 console.error(
731 'Error logging product creation:',
732 err
733 );
[69f2a41]734 }
[62b2964]735 }
736 );
[69f2a41]737
[62b2964]738 /*
739 * Log who made the change
740 */
741 query(
742 `INSERT INTO makes_change
743 (
744 personal_id,
745 change_date_time,
746 product_code
747 )
748 VALUES
749 ($1, NOW(), $2)`,
750 [
751 personalId,
752 productData.code
753 ],
754 (err) => {
755
756 if (err) {
757 console.error(
758 'Error logging change maker:',
759 err
[69f2a41]760 );
[62b2964]761 }
[69f2a41]762 }
[62b2964]763 );
764
765 /*
766 * Insert images
767 */
768 if (
769 productData.images &&
770 Array.isArray(productData.images) &&
771 productData.images.length > 0
772 ) {
773
774 productData.images.forEach(
775 (imageUrl, index) => {
776
777 query(
778 `INSERT INTO image
779 (
780 product_code,
781 image_url,
782 is_primary
783 )
784 VALUES
785 ($1, $2, $3)`,
786 [
787 productData.code,
788 imageUrl,
789 index === 0
790 ],
791 (err) => {
[69f2a41]792
793 if (err) {
[62b2964]794 console.error(
795 'Error inserting image:',
796 err
797 );
[69f2a41]798 }
799 }
800 );
[62b2964]801 }
802 );
803 }
804
805 /*
806 * Insert colors
807 */
808 if (
809 productData.colors &&
810 Array.isArray(productData.colors) &&
811 productData.colors.length > 0
812 ) {
813
814 productData.colors.forEach(color => {
815
816 query(
817 `INSERT INTO color
818 (product_code, name)
819 VALUES
820 ($1, $2)`,
821 [
822 productData.code,
823 color
824 ],
825 (err) => {
[69f2a41]826
[62b2964]827 if (err) {
828 console.error(
829 'Error inserting color:',
830 err
831 );
832 }
833 }
834 );
835 });
[69f2a41]836 }
[62b2964]837
838 callback(null, returnedId);
[69f2a41]839 }
840 );
841 });
842}
843
[62b2964]844
845function updateProduct(
846 personalId,
847 productData,
848 callback
849) {
850
[69f2a41]851 const updates = [];
852 const params = [];
853
854 if (productData.description !== undefined) {
[62b2964]855 updates.push(`description = $${params.length + 1}`);
[69f2a41]856 params.push(productData.description);
857 }
[591278c]858
[69f2a41]859 if (productData.price !== undefined) {
[62b2964]860 updates.push(`price = $${params.length + 1}`);
[69f2a41]861 params.push(productData.price);
862 }
[591278c]863
[69f2a41]864 if (productData.availability !== undefined) {
[62b2964]865 updates.push(`availability = $${params.length + 1}`);
[69f2a41]866 params.push(productData.availability);
867 }
[591278c]868
[69f2a41]869 if (productData.weight !== undefined) {
[62b2964]870 updates.push(`weight = $${params.length + 1}`);
[69f2a41]871 params.push(productData.weight);
872 }
[591278c]873
[69f2a41]874 if (productData.dimensions !== undefined) {
[62b2964]875 updates.push(`dimensions = $${params.length + 1}`);
[69f2a41]876 params.push(productData.dimensions);
877 }
[591278c]878
[69f2a41]879 if (productData.production_time !== undefined) {
[62b2964]880 updates.push(`production_time = $${params.length + 1}`);
[69f2a41]881 params.push(productData.production_time);
882 }
[591278c]883
[69f2a41]884 if (productData.category_id !== undefined) {
[62b2964]885 updates.push(`category_id = $${params.length + 1}`);
[69f2a41]886 params.push(productData.category_id);
887 }
888
889 if (updates.length === 0) {
890 callback(null, 0);
891 return;
892 }
893
894 params.push(productData.code);
895
[62b2964]896 const codeParameter = params.length;
897
898 query(
899 `UPDATE product
900 SET ${updates.join(', ')}
901 WHERE code = $${codeParameter}`,
[69f2a41]902 params,
[62b2964]903 (err, result) => {
904
[69f2a41]905 if (err) {
906 callback(err, null);
[62b2964]907 return;
908 }
909
910 const changesDesc =
911 `Product updated: ${updates.join(', ')}`;
912
913 /*
914 * Log product change
915 */
916 query(
917 `INSERT INTO "change"
918 (
919 date_and_time,
920 product_code,
921 changes
922 )
923 VALUES
924 (NOW(), $1, $2)`,
925 [
926 productData.code,
927 changesDesc
928 ],
929 (err) => {
930
931 if (err) {
932 console.error(
933 'Error logging product update:',
934 err
[69f2a41]935 );
936 }
[62b2964]937 }
938 );
[69f2a41]939
[62b2964]940 /*
941 * Log who made the change
942 */
943 query(
944 `INSERT INTO makes_change
945 (
946 personal_id,
947 change_date_time,
948 product_code
949 )
950 VALUES
951 ($1, NOW(), $2)`,
952 [
953 personalId,
954 productData.code
955 ],
956 (err) => {
957
958 if (err) {
959 console.error(
960 'Error logging change maker:',
961 err
962 );
963 }
[69f2a41]964 }
[62b2964]965 );
[69f2a41]966
[62b2964]967 /*
968 * Images
969 */
970 if (
971 productData.images &&
972 Array.isArray(productData.images)
973 ) {
974
975 query(
976 `DELETE FROM image
977 WHERE product_code = $1`,
978 [productData.code],
979 (err) => {
980
981 if (err) {
982 console.error(
983 'Error deleting old images:',
984 err
985 );
986 return;
[69f2a41]987 }
988
[62b2964]989 productData.images.forEach(
990 (image, index) => {
991
992 query(
993 `INSERT INTO image
994 (
995 product_code,
996 image_url,
997 is_primary
998 )
999 VALUES
1000 ($1, $2, $3)`,
1001 [
1002 productData.code,
1003 image,
1004 index === 0
1005 ],
1006 (err) => {
1007
1008 if (err) {
1009 console.error(
1010 'Error inserting image:',
1011 err
1012 );
1013 }
1014 }
1015 );
1016 }
1017 );
1018 }
1019 );
[69f2a41]1020 }
1021
[62b2964]1022 /*
1023 * Colors
1024 */
1025 if (
1026 productData.colors &&
1027 Array.isArray(productData.colors)
1028 ) {
[69f2a41]1029
[62b2964]1030 query(
1031 `DELETE FROM color
1032 WHERE product_code = $1`,
1033 [productData.code],
1034 (err) => {
[69f2a41]1035
1036 if (err) {
[62b2964]1037 console.error(
1038 'Error deleting old colors:',
1039 err
1040 );
[69f2a41]1041 return;
1042 }
1043
[62b2964]1044 productData.colors.forEach(color => {
1045
1046 query(
1047 `INSERT INTO color
1048 (product_code, name)
1049 VALUES
1050 ($1, $2)`,
1051 [
1052 productData.code,
1053 color
1054 ],
1055 (err) => {
1056
1057 if (err) {
1058 console.error(
1059 'Error inserting color:',
1060 err
1061 );
1062 }
[69f2a41]1063 }
[62b2964]1064 );
1065 });
[69f2a41]1066 }
1067 );
1068 }
[62b2964]1069
1070 callback(null, result.rowCount);
1071 }
1072 );
1073}
1074
1075
1076function deleteProduct(
1077 productCode,
1078 storeId,
1079 personalId,
1080 callback
1081) {
1082
1083 pool.connect()
1084 .then(client => {
1085
1086 return client.query('BEGIN')
1087 .then(() => {
1088
1089 return client.query(
1090 `INSERT INTO "change"
1091 (
1092 date_and_time,
1093 product_code,
1094 changes
1095 )
1096 VALUES
1097 (NOW(), $1, $2)`,
1098 [
1099 productCode,
1100 'Product deleted'
1101 ]
1102 );
1103 })
1104 .then(() => {
1105
1106 return client.query(
1107 `INSERT INTO makes_change
1108 (
1109 personal_id,
1110 change_date_time,
1111 product_code
1112 )
1113 VALUES
1114 ($1, NOW(), $2)`,
1115 [
1116 personalId,
1117 productCode
1118 ]
1119 );
1120 })
1121 .then(() => {
1122
1123 return client.query(
1124 `DELETE FROM product
1125 WHERE code = $1
1126 AND store_id = $2`,
1127 [
1128 productCode,
1129 storeId
1130 ]
1131 );
1132 })
1133 .then(result => {
1134
1135 return client.query('COMMIT')
1136 .then(() => {
1137
1138 client.release();
1139
1140 callback(
1141 null,
1142 result.rowCount
1143 );
1144 });
1145 })
1146 .catch(err => {
1147
1148 return client.query('ROLLBACK')
1149 .catch(() => {})
1150 .then(() => {
1151
1152 client.release();
1153 callback(err);
1154 });
1155 });
1156 })
1157 .catch(err => {
1158 callback(err);
1159 });
[69f2a41]1160}
1161
[62b2964]1162
1163/*
1164 * ============================================================
1165 * CATEGORY FUNCTIONS
1166 * ============================================================
1167 */
1168
[69f2a41]1169function getCategories(callback) {
[62b2964]1170
1171 query(
1172 `SELECT *
1173 FROM category
1174 ORDER BY name`,
1175 [],
1176 (err, result) => {
1177
1178 callback(
1179 err,
1180 result ? result.rows : []
1181 );
1182 }
1183 );
[69f2a41]1184}
1185
[62b2964]1186
[69f2a41]1187function getCategoriesWithParents(callback) {
[62b2964]1188
1189 query(
1190 `SELECT
1191 c1.*,
1192 c2.name AS parent_name
1193 FROM category c1
1194 LEFT JOIN category c2
1195 ON c1.parent_category_id =
1196 c2.category_id
1197 ORDER BY c1.name`,
[69f2a41]1198 [],
[62b2964]1199 (err, result) => {
1200
1201 callback(
1202 err,
1203 result ? result.rows : []
1204 );
[69f2a41]1205 }
1206 );
1207}
1208
[62b2964]1209
[69f2a41]1210function createCategory(categoryData, callback) {
[62b2964]1211
1212 query(
1213 `INSERT INTO category
1214 (
1215 name,
1216 description,
1217 parent_category_id
1218 )
1219 VALUES
1220 ($1, $2, $3)
1221 RETURNING category_id`,
1222 [
1223 categoryData.name,
1224 categoryData.description || null,
1225 categoryData.parent_id || null
1226 ],
1227 (err, result) => {
1228
[69f2a41]1229 if (err) {
1230 callback(err, null);
1231 } else {
[62b2964]1232
[69f2a41]1233 callback(null, {
[62b2964]1234 id: result.rows[0].category_id,
[69f2a41]1235 name: categoryData.name,
1236 parent_id: categoryData.parent_id,
1237 description: categoryData.description
1238 });
1239 }
1240 }
1241 );
1242}
1243
[62b2964]1244
1245/*
1246 * ============================================================
1247 * STORE FUNCTIONS
1248 * ============================================================
1249 */
1250
[69f2a41]1251function getStores(callback) {
[62b2964]1252
1253 query(
1254 `SELECT *
1255 FROM store
1256 ORDER BY name`,
1257 [],
1258 (err, result) => {
1259
1260 callback(
1261 err,
1262 result ? result.rows : []
1263 );
1264 }
1265 );
[69f2a41]1266}
1267
[62b2964]1268
[69f2a41]1269function getStoreProducts(storeId, callback) {
[62b2964]1270
1271 query(
1272 `SELECT
1273 p.*,
1274 c.name AS category_name
1275 FROM product p
1276 JOIN category c
1277 ON p.category_id = c.category_id
1278 WHERE p.store_id = $1
1279 ORDER BY p.code`,
[69f2a41]1280 [storeId],
[62b2964]1281 (err, result) => {
1282
1283 callback(
1284 err,
1285 result ? result.rows : []
1286 );
[69f2a41]1287 }
1288 );
1289}
1290
[62b2964]1291
[69f2a41]1292function getStoreOrders(storeId, callback) {
[62b2964]1293
1294 query(
1295 `SELECT
1296 o.*,
1297 c.first_name,
1298 c.last_name
1299 FROM "order" o
1300 JOIN client c
1301 ON o.client_id = c.client_id
1302 WHERE o.store_id = $1
1303 ORDER BY o.order_date DESC`,
[69f2a41]1304 [storeId],
[62b2964]1305 (err, result) => {
1306
[69f2a41]1307 if (err) {
1308 callback(err, null);
[62b2964]1309 return;
1310 }
[69f2a41]1311
[62b2964]1312 const orders = result.rows || [];
[69f2a41]1313
[62b2964]1314 if (orders.length === 0) {
1315 callback(null, []);
1316 return;
[69f2a41]1317 }
[62b2964]1318
1319 let completed = 0;
1320
1321 orders.forEach(order => {
1322
1323 query(
1324 `SELECT
1325 oi.*,
1326 p.description
1327 FROM order_items oi
1328 JOIN product p
1329 ON oi.product_code = p.code
1330 WHERE oi.order_num = $1`,
1331 [order.order_num],
1332 (err, result) => {
1333
1334 if (!err) {
1335 order.items =
1336 result.rows || [];
1337 } else {
1338 order.items = [];
1339 }
1340
1341 completed++;
1342
1343 if (completed === orders.length) {
1344 callback(null, orders);
1345 }
1346 }
1347 );
1348 });
[69f2a41]1349 }
1350 );
1351}
1352
[62b2964]1353
[69f2a41]1354function getStoreEmployees(storeId, callback) {
[62b2964]1355
1356 query(
1357 `SELECT
1358 p.*,
1359 e.date_of_hire,
1360 perm.type AS permission_type,
1361 perm.authorisation
1362 FROM personal p
1363 JOIN works_in_store w
1364 ON p.id = w.personal_id
1365 LEFT JOIN employees e
1366 ON p.id = e.employee_id
1367 LEFT JOIN permissions perm
1368 ON p.id = perm.personal_id
1369 WHERE w.store_id = $1`,
[69f2a41]1370 [storeId],
[62b2964]1371 (err, result) => {
1372
1373 callback(
1374 err,
1375 result ? result.rows : []
1376 );
[69f2a41]1377 }
1378 );
1379}
1380
[62b2964]1381
[69f2a41]1382function getStoreReports(storeId, callback) {
[62b2964]1383
1384 query(
1385 `SELECT *
1386 FROM report
1387 WHERE store_id = $1
1388 ORDER BY generated_at DESC`,
[69f2a41]1389 [storeId],
[62b2964]1390 (err, result) => {
1391
1392 callback(
1393 err,
1394 result ? result.rows : []
1395 );
[69f2a41]1396 }
1397 );
1398}
1399
[62b2964]1400
[69f2a41]1401function getStoreStats(storeId, callback) {
[62b2964]1402
[69f2a41]1403 const stats = {};
1404
[62b2964]1405 query(
1406 `SELECT COUNT(*) AS total_products
1407 FROM product
1408 WHERE store_id = $1`,
[69f2a41]1409 [storeId],
[62b2964]1410 (err, result) => {
1411
1412 if (err) {
1413 callback(err, null);
1414 return;
1415 }
[69f2a41]1416
[62b2964]1417 stats.total_products =
1418 result.rows[0]
1419 ? Number(result.rows[0].total_products)
1420 : 0;
1421
1422 query(
1423 `SELECT COUNT(*) AS total_orders
1424 FROM "order"
1425 WHERE store_id = $1`,
[69f2a41]1426 [storeId],
[62b2964]1427 (err, result) => {
1428
1429 if (err) {
1430 callback(err, null);
1431 return;
1432 }
1433
1434 stats.total_orders =
1435 result.rows[0]
1436 ? Number(result.rows[0].total_orders)
1437 : 0;
1438
1439 query(
1440 `SELECT
1441 COALESCE(
1442 SUM(
1443 oi.price * oi.quantity
1444 ),
1445 0
1446 ) AS total_revenue
1447 FROM order_items oi
1448 JOIN "order" o
1449 ON oi.order_num =
1450 o.order_num
1451 WHERE o.store_id = $1`,
[69f2a41]1452 [storeId],
[62b2964]1453 (err, result) => {
1454
1455 if (err) {
1456 callback(err, null);
1457 return;
1458 }
1459
1460 stats.total_revenue =
1461 Number(
1462 result.rows[0]
1463 .total_revenue || 0
1464 );
1465
1466 query(
1467 `SELECT
1468 COALESCE(
1469 AVG(r.rating),
1470 0
1471 ) AS avg_rating
1472 FROM review r
1473 JOIN product p
1474 ON r.product_code =
1475 p.code
1476 WHERE p.store_id = $1`,
[69f2a41]1477 [storeId],
[62b2964]1478 (err, result) => {
1479
1480 if (err) {
1481 callback(err, null);
1482 return;
1483 }
1484
1485 stats.avg_rating =
1486 Number(
1487 result.rows[0]
1488 .avg_rating || 0
1489 );
1490
1491 callback(
1492 null,
1493 stats
1494 );
[69f2a41]1495 }
1496 );
1497 }
1498 );
1499 }
1500 );
1501 }
1502 );
1503}
1504
[62b2964]1505
1506/*
1507 * ============================================================
1508 * ORDER FUNCTIONS
1509 * ============================================================
1510 */
1511
[69f2a41]1512function createOrderNew(orderData, callback) {
1513
[62b2964]1514 pool.connect()
1515 .then(client => {
1516
1517 return client.query('BEGIN')
1518 .then(() => {
1519
1520 return client.query(
1521 `INSERT INTO "order"
1522 (
1523 order_num,
1524 client_id,
1525 order_date,
1526 quantity,
1527 payment_method,
1528 discount,
1529 delivery_address,
1530 store_id
1531 )
1532 VALUES
1533 (
1534 $1,
1535 $2,
1536 NOW(),
1537 $3,
1538 $4,
1539 $5,
1540 $6,
1541 $7
1542 )`,
1543 [
1544 orderData.order_num,
1545 orderData.client_id,
1546 orderData.quantity,
1547 orderData.payment_method,
1548 orderData.discount,
1549 orderData.delivery_address,
1550 orderData.store_id
1551 ]
1552 );
1553 })
1554 .then(() => {
[69f2a41]1555
[62b2964]1556 const items =
1557 orderData.items || [];
[69f2a41]1558
[62b2964]1559 if (items.length === 0) {
1560 return client.query('COMMIT')
1561 .then(() => {
[69f2a41]1562
[62b2964]1563 client.release();
[69f2a41]1564
[62b2964]1565 callback(
1566 null,
1567 orderData.order_num
1568 );
1569 });
1570 }
1571
1572 return Promise.all(
1573 items.map(item => {
1574
1575 return client.query(
1576 `INSERT INTO order_items
1577 (
1578 order_num,
1579 product_code,
1580 quantity,
1581 price
1582 )
1583 VALUES
1584 ($1, $2, $3, $4)`,
1585 [
1586 orderData.order_num,
1587 item.product_code,
1588 item.quantity,
1589 item.price
1590 ]
1591 );
1592 })
1593 )
1594 .then(() => client.query('COMMIT'))
1595 .then(() => {
1596
1597 client.release();
1598
1599 callback(
1600 null,
1601 orderData.order_num
1602 );
1603 });
1604 })
1605 .catch(err => {
1606
1607 return client.query('ROLLBACK')
1608 .catch(() => {})
1609 .then(() => {
1610
1611 client.release();
1612 callback(err, null);
1613 });
[69f2a41]1614 });
[62b2964]1615 })
1616 .catch(err => {
1617 callback(err, null);
1618 });
[69f2a41]1619}
1620
[62b2964]1621
[69f2a41]1622function getOrdersByClient(clientId, callback) {
[62b2964]1623
1624 query(
1625 `SELECT
1626 o.*,
1627 s.name AS store_name
1628 FROM "order" o
1629 JOIN store s
1630 ON o.store_id = s.store_id
1631 WHERE o.client_id = $1
1632 ORDER BY o.order_date DESC`,
[69f2a41]1633 [clientId],
[62b2964]1634 (err, result) => {
1635
[69f2a41]1636 if (err) {
1637 callback(err, null);
[62b2964]1638 return;
1639 }
[69f2a41]1640
[62b2964]1641 const orders = result.rows || [];
[69f2a41]1642
[62b2964]1643 if (orders.length === 0) {
1644 callback(null, []);
1645 return;
[69f2a41]1646 }
[62b2964]1647
1648 let completed = 0;
1649
1650 orders.forEach(order => {
1651
1652 query(
1653 `SELECT
1654 oi.*,
1655 p.description
1656 FROM order_items oi
1657 JOIN product p
1658 ON oi.product_code = p.code
1659 WHERE oi.order_num = $1`,
1660 [order.order_num],
1661 (err, result) => {
1662
1663 if (!err) {
1664 order.items =
1665 result.rows || [];
1666 } else {
1667 order.items = [];
1668 }
1669
1670 completed++;
1671
1672 if (completed === orders.length) {
1673 callback(null, orders);
1674 }
1675 }
1676 );
1677 });
[69f2a41]1678 }
1679 );
1680}
1681
[62b2964]1682
[69f2a41]1683function getAllOrders(callback) {
[62b2964]1684
1685 query(
1686 `SELECT
1687 o.*,
1688 c.first_name,
1689 c.last_name,
1690 s.name AS store_name
1691 FROM "order" o
1692 JOIN client c
1693 ON o.client_id = c.client_id
1694 JOIN store s
1695 ON o.store_id = s.store_id
1696 ORDER BY o.order_date DESC`,
[69f2a41]1697 [],
[62b2964]1698 (err, result) => {
1699
1700 callback(
1701 err,
1702 result ? result.rows : []
1703 );
[69f2a41]1704 }
1705 );
1706}
1707
[62b2964]1708
1709/*
1710 * ============================================================
1711 * REVIEW FUNCTIONS
1712 * ============================================================
1713 */
1714
[69f2a41]1715function createReviewNew(reviewData, callback) {
[62b2964]1716
1717 const reviewId =
1718 'REV' +
1719 Date.now().toString().slice(-8);
1720
1721 query(
1722 `INSERT INTO review
1723 (
1724 review_id,
1725 client_id,
1726 product_code,
1727 rating,
1728 comment,
1729 review_date
1730 )
1731 VALUES
1732 ($1, $2, $3, $4, $5, NOW())
1733 RETURNING review_id`,
[69f2a41]1734 [
[62b2964]1735 reviewId,
[69f2a41]1736 reviewData.client_id,
1737 reviewData.product_code,
1738 reviewData.rating,
1739 reviewData.comment || ''
1740 ],
[62b2964]1741 (err, result) => {
1742
[69f2a41]1743 if (err) {
1744 callback(err, null);
1745 } else {
[62b2964]1746 callback(
1747 null,
1748 result.rows[0].review_id
1749 );
[69f2a41]1750 }
1751 }
1752 );
1753}
1754
[62b2964]1755
1756/*
1757 * ============================================================
1758 * REQUEST FUNCTIONS
1759 * ============================================================
1760 */
1761
[69f2a41]1762function createRequest(requestData, callback) {
[62b2964]1763
1764 query(
1765 `INSERT INTO request
1766 (
1767 request_num,
1768 date_and_time,
1769 problem,
1770 client_id,
1771 store_id
1772 )
1773 VALUES
1774 ($1, $2, $3, $4, $5)`,
[69f2a41]1775 [
1776 requestData.request_num,
1777 requestData.date_and_time,
1778 requestData.problem,
1779 requestData.client_id,
1780 requestData.store_id
1781 ],
[62b2964]1782 (err) => {
1783
[69f2a41]1784 if (err) {
1785 callback(err, null);
1786 } else {
[62b2964]1787 callback(
1788 null,
1789 requestData.request_num
1790 );
[69f2a41]1791 }
1792 }
1793 );
1794}
1795
[62b2964]1796
1797/*
1798 * ============================================================
1799 * REFUND FUNCTIONS
1800 * ============================================================
1801 */
1802
[69f2a41]1803function createRefund(refundData, callback) {
[62b2964]1804
1805 query(
1806 `INSERT INTO refund
1807 (
1808 refund_id,
1809 order_num,
1810 amount,
1811 reason,
1812 request_date
1813 )
1814 VALUES
1815 ($1, $2, $3, $4, NOW())`,
[69f2a41]1816 [
1817 refundData.refund_id,
1818 refundData.order_num,
1819 refundData.amount,
1820 refundData.reason
1821 ],
[62b2964]1822 (err) => {
1823
[69f2a41]1824 if (err) {
1825 callback(err, null);
1826 } else {
[62b2964]1827 callback(
1828 null,
1829 refundData.refund_id
1830 );
[69f2a41]1831 }
1832 }
1833 );
1834}
1835
[62b2964]1836
1837/*
1838 * ============================================================
1839 * EMPLOYEE TASKS
1840 * ============================================================
1841 */
1842
1843function getEmployeeTasks(
1844 personalId,
1845 storeId,
1846 callback
1847) {
1848
[69f2a41]1849 const tasks = {
1850 pending_orders: [],
1851 pending_requests: [],
1852 pending_refunds: []
1853 };
1854
[62b2964]1855 /*
1856 * Pending orders
1857 */
1858 query(
1859 `SELECT
1860 o.*,
1861 c.first_name,
1862 c.last_name
1863 FROM "order" o
1864 JOIN client c
1865 ON o.client_id = c.client_id
1866 WHERE o.store_id = $1
1867 AND o.status = $2
1868 ORDER BY o.order_date ASC`,
1869 [
1870 storeId,
1871 'pending'
1872 ],
1873 (err, result) => {
1874
[69f2a41]1875 if (!err) {
[62b2964]1876 tasks.pending_orders =
1877 result.rows || [];
[69f2a41]1878 }
1879
[62b2964]1880 /*
1881 * Pending requests
1882 */
1883 query(
1884 `SELECT
1885 r.*,
1886 c.first_name,
1887 c.last_name
1888 FROM request r
1889 JOIN client c
1890 ON r.client_id = c.client_id
1891 WHERE r.store_id = $1
1892 AND r.status = $2
1893 ORDER BY r.date_and_time ASC`,
1894 [
1895 storeId,
1896 'pending'
1897 ],
1898 (err, result) => {
1899
[69f2a41]1900 if (!err) {
[62b2964]1901 tasks.pending_requests =
1902 result.rows || [];
[69f2a41]1903 }
1904
[62b2964]1905 /*
1906 * Pending refunds
1907 */
1908 query(
1909 `SELECT
1910 rf.*,
1911 o.client_id,
1912 c.first_name,
1913 c.last_name
1914 FROM refund rf
1915 JOIN "order" o
1916 ON rf.order_num =
1917 o.order_num
1918 JOIN client c
1919 ON o.client_id =
1920 c.client_id
1921 WHERE o.store_id = $1
1922 AND rf.status = $2
1923 ORDER BY rf.request_date ASC`,
1924 [
1925 storeId,
1926 'pending'
1927 ],
1928 (err, result) => {
1929
[69f2a41]1930 if (!err) {
[62b2964]1931 tasks.pending_refunds =
1932 result.rows || [];
[69f2a41]1933 }
[62b2964]1934
1935 callback(
1936 null,
1937 tasks
1938 );
[69f2a41]1939 }
1940 );
1941 }
1942 );
1943 }
1944 );
1945}
1946
[62b2964]1947
1948/*
1949 * ============================================================
1950 * CLIENT STATISTICS
1951 * ============================================================
1952 */
1953
[69f2a41]1954function getClientStats(clientId, callback) {
[62b2964]1955
[69f2a41]1956 const stats = {};
1957
[62b2964]1958 query(
1959 `SELECT COUNT(*) AS total_orders
1960 FROM "order"
1961 WHERE client_id = $1`,
[69f2a41]1962 [clientId],
[62b2964]1963 (err, result) => {
1964
1965 if (err) {
1966 callback(err, null);
1967 return;
1968 }
1969
1970 stats.total_orders =
1971 Number(
1972 result.rows[0]
1973 ? result.rows[0].total_orders
1974 : 0
1975 );
1976
1977 query(
1978 `SELECT
1979 COALESCE(
1980 SUM(
1981 oi.price * oi.quantity
1982 ),
1983 0
1984 ) AS total_spent
1985 FROM order_items oi
1986 JOIN "order" o
1987 ON oi.order_num = o.order_num
1988 WHERE o.client_id = $1`,
[69f2a41]1989 [clientId],
[62b2964]1990 (err, result) => {
1991
1992 if (err) {
1993 callback(err, null);
1994 return;
1995 }
1996
1997 stats.total_spent =
1998 Number(
1999 result.rows[0]
2000 ? result.rows[0].total_spent
2001 : 0
2002 );
2003
2004 query(
2005 `SELECT COUNT(*) AS pending_orders
2006 FROM "order"
2007 WHERE client_id = $1
2008 AND status = $2`,
2009 [
2010 clientId,
2011 'pending'
2012 ],
2013 (err, result) => {
2014
2015 if (err) {
2016 callback(err, null);
2017 return;
2018 }
2019
2020 stats.pending_orders =
2021 Number(
2022 result.rows[0]
2023 ? result.rows[0]
2024 .pending_orders
2025 : 0
2026 );
2027
2028 query(
2029 `SELECT
2030 COUNT(*) AS delivered_orders
2031 FROM "order"
2032 WHERE client_id = $1
2033 AND status = $2`,
2034 [
2035 clientId,
2036 'delivered'
2037 ],
2038 (err, result) => {
2039
2040 if (err) {
2041 callback(err, null);
2042 return;
2043 }
2044
2045 stats.delivered_orders =
2046 Number(
2047 result.rows[0]
2048 ? result.rows[0]
2049 .delivered_orders
2050 : 0
2051 );
2052
2053 callback(
2054 null,
2055 stats
2056 );
[69f2a41]2057 }
2058 );
2059 }
2060 );
2061 }
2062 );
2063 }
2064 );
2065}
2066
[62b2964]2067
2068/*
2069 * ============================================================
2070 * ADMIN USER FUNCTIONS
2071 * ============================================================
2072 */
2073
[69f2a41]2074function getAllUsers(callback) {
2075
[62b2964]2076 query(
2077 `SELECT
2078 client_id AS id,
2079 first_name,
2080 last_name,
2081 email,
2082 'client' AS user_type,
2083 NULL AS username,
2084 5 AS role_priority
[4dff800]2085 FROM client
2086 ORDER BY client_id`,
[69f2a41]2087 [],
[62b2964]2088 (err, result) => {
2089
2090 if (err) {
2091 callback(err, null);
2092 return;
[69f2a41]2093 }
2094
[62b2964]2095 const usersMap = new Map();
2096
2097 result.rows.forEach(row => {
2098 usersMap.set(row.id, row);
2099 });
2100
2101 /*
2102 * Personal users
2103 */
2104 query(
2105 `SELECT
2106 p.id,
2107 p.first_name,
2108 p.last_name,
2109 p.email,
2110 CASE
2111 WHEN b.boss_id IS NOT NULL
2112 THEN 'store_owner'
2113 ELSE 'store_employee'
2114 END AS user_type,
2115 NULL AS username,
2116 CASE
2117 WHEN b.boss_id IS NOT NULL
2118 THEN 2
2119 ELSE 4
2120 END AS role_priority
[4dff800]2121 FROM personal p
[62b2964]2122 LEFT JOIN boss b
2123 ON p.id = b.boss_id
2124 LEFT JOIN employees e
2125 ON p.id = e.employee_id
2126 WHERE b.boss_id IS NOT NULL
2127 OR e.employee_id IS NOT NULL
[4dff800]2128 ORDER BY p.id`,
[69f2a41]2129 [],
[62b2964]2130 (err, result) => {
2131
2132 if (err) {
2133 callback(err, null);
2134 return;
[69f2a41]2135 }
2136
[62b2964]2137 result.rows.forEach(row => {
2138
2139 const existing =
2140 usersMap.get(row.id);
2141
2142 if (
2143 !existing ||
2144 (
2145 existing.role_priority &&
2146 row.role_priority <
2147 existing.role_priority
2148 )
2149 ) {
2150 usersMap.set(
2151 row.id,
2152 row
2153 );
2154 }
2155 });
2156
2157 /*
2158 * System users
2159 */
2160 query(
2161 `SELECT
2162 id,
2163 username,
2164 email,
2165 user_type,
2166 CASE
2167 WHEN user_type = 'admin'
2168 THEN 1
2169 ELSE 3
2170 END AS role_priority
[4dff800]2171 FROM users
2172 ORDER BY id`,
[69f2a41]2173 [],
[62b2964]2174 (err, result) => {
2175
2176 if (err) {
2177 callback(err, null);
2178 return;
[69f2a41]2179 }
[4dff800]2180
[62b2964]2181 result.rows.forEach(row => {
2182
2183 const existing =
2184 usersMap.get(row.id);
2185
2186 if (
2187 !existing ||
2188 (
2189 existing.role_priority &&
2190 row.role_priority <
2191 existing.role_priority
2192 )
2193 ) {
2194
2195 const userData = {
2196 id: row.id,
2197 username: row.username,
2198 email: row.email,
2199 user_type: row.user_type,
2200 role_priority: row.role_priority
2201 };
2202
2203 if (
2204 row.user_type ===
2205 'admin'
2206 ) {
2207 userData.first_name =
2208 'Admin';
2209
2210 userData.last_name =
2211 'User';
2212 }
2213
2214 usersMap.set(
2215 row.id,
2216 userData
2217 );
2218 }
[4dff800]2219 });
2220
[62b2964]2221 const users =
2222 Array.from(
2223 usersMap.values()
2224 ).map(user => {
2225
2226 const {
2227 role_priority,
2228 ...userWithoutPriority
2229 } = user;
2230
2231 return userWithoutPriority;
2232 });
2233
2234 callback(
2235 null,
2236 users
2237 );
[69f2a41]2238 }
2239 );
2240 }
2241 );
2242 }
2243 );
2244}
2245
[62b2964]2246
2247/*
2248 * ============================================================
2249 * AUDIT LOG
2250 * ============================================================
2251 */
2252
2253function logAudit(
2254 userId,
2255 action,
2256 resourceType,
2257 resourceId,
2258 details,
2259 ipAddress
2260) {
2261
2262 query(
2263 `INSERT INTO audit_log
2264 (
2265 user_id,
2266 action,
2267 resource_type,
2268 resource_id,
2269 details,
2270 ip_address
2271 )
2272 VALUES
2273 ($1, $2, $3, $4, $5, $6)`,
2274 [
2275 userId,
2276 action,
2277 resourceType,
2278 resourceId,
2279 details,
2280 ipAddress
2281 ],
[69f2a41]2282 (err) => {
[62b2964]2283
[69f2a41]2284 if (err) {
[62b2964]2285 console.error(
2286 'Error logging audit:',
2287 err
2288 );
[69f2a41]2289 }
2290 }
2291 );
2292}
2293
[62b2964]2294
2295/*
2296 * ============================================================
2297 * EXPORTS
2298 * ============================================================
2299 */
2300
[69f2a41]2301module.exports = {
[62b2964]2302
2303 pool,
2304
2305 query,
2306
[69f2a41]2307 ensureGeneralCategory,
2308 getGeneralCategoryId,
[62b2964]2309
[69f2a41]2310 getUserByUsername,
2311 getUserById,
2312 createUser,
[62b2964]2313
[69f2a41]2314 getClientByEmail,
2315 getClientById,
2316 createClient,
2317 verifyClientPassword,
[62b2964]2318
[69f2a41]2319 getPersonalByEmail,
2320 getPersonalById,
2321 verifyPassword,
2322 updatePasswordAndClearForce,
[62b2964]2323
[69f2a41]2324 getProducts,
2325 getProductById,
2326 getProductByCode,
2327 addProduct,
2328 updateProduct,
2329 deleteProduct,
[62b2964]2330
[69f2a41]2331 getCategories,
2332 getCategoriesWithParents,
2333 createCategory,
[62b2964]2334
[69f2a41]2335 getStores,
2336 getStoreProducts,
2337 getStoreOrders,
2338 getStoreEmployees,
2339 getStoreReports,
2340 getStoreStats,
[62b2964]2341
[69f2a41]2342 createOrderNew,
2343 getOrdersByClient,
2344 getAllOrders,
[62b2964]2345
[69f2a41]2346 createReviewNew,
[62b2964]2347
[69f2a41]2348 createRequest,
[62b2964]2349
[69f2a41]2350 createRefund,
[62b2964]2351
[69f2a41]2352 getEmployeeTasks,
[62b2964]2353
[69f2a41]2354 getClientStats,
[62b2964]2355
[69f2a41]2356 getAllUsers,
[62b2964]2357
[69f2a41]2358 logAudit
2359};
Note: See TracBrowser for help on using the repository browser.