source: database.js@ 6149556

main
Last change on this file since 6149556 was 33517cc, checked in by Klimentina Efremova <klimentina08642@…>, 10 days ago

Turned database from SQLite to PostgressSQL, updated database changes from Phase 1 and 2
  • Property mode set to 100644
File size: 60.3 KB
Line 
1const { Pool } = require('pg');
2const bcrypt = require('bcryptjs');
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 || 'db_202526z_va_prj_handcraft_store',
28 user: process.env.PGUSER || 'db_202526z_va_prj_handcraft_store_owner',
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});
38
39pool.on('error', (err) => {
40 console.error('❌ Unexpected PostgreSQL pool error:', err);
41});
42
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 });
57}
58
59
60/*
61 * ============================================================
62 * GENERAL CATEGORY
63 * ============================================================
64 */
65
66function ensureGeneralCategory(callback) {
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 }
103 }
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 }
123 }
124 );
125 }
126 }
127 );
128}
129
130
131function getGeneralCategoryId(callback) {
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`,
174 ['General', 'General products category'],
175 (err, result) => {
176
177 if (err) {
178 callback(err, null);
179 } else {
180 const newId = result.rows[0].category_id;
181
182 console.log(
183 `✅ Created new General category with ID: ${newId}`
184 );
185
186 callback(null, newId);
187 }
188 }
189 );
190 }
191 );
192 }
193 );
194}
195
196
197/*
198 * ============================================================
199 * USER FUNCTIONS
200 * ============================================================
201 */
202
203function getUserByUsername(username, callback) {
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 );
218}
219
220
221function getUserById(id, callback) {
222
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;
233 }
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 );
256}
257
258
259function createUser(id, username, email, password, userType, callback) {
260
261 const hashedPassword = bcrypt.hashSync(password, 10);
262
263 query(
264 `INSERT INTO users
265 (id, username, email, password, user_type)
266 VALUES
267 ($1, $2, $3, $4, $5)`,
268 [id, username, email, hashedPassword, userType],
269 (err) => {
270
271 if (err) {
272 callback(err, null);
273 } else {
274 callback(null, id);
275 }
276 }
277 );
278}
279
280
281/*
282 * ============================================================
283 * CLIENT FUNCTIONS
284 * ============================================================
285 */
286
287function getClientByEmail(email, callback) {
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 );
302}
303
304
305function getClientById(id, callback) {
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 );
320}
321
322
323function createClient(clientData, callback) {
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
342 if (err) {
343 callback(err, null);
344 } else {
345 callback(
346 null,
347 result.rows[0].client_id
348 );
349 }
350 }
351 );
352}
353
354
355function verifyClientPassword(
356 password,
357 hashedPassword,
358 callback
359) {
360
361 try {
362
363 const isValid =
364 bcrypt.compareSync(password, hashedPassword);
365
366 callback(null, isValid);
367
368 } catch (err) {
369
370 callback(err, false);
371 }
372}
373
374
375/*
376 * ============================================================
377 * PERSONAL / EMPLOYEE FUNCTIONS
378 * ============================================================
379 */
380
381function getPersonalByEmail(email, callback) {
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 );
396}
397
398
399function getPersonalById(id, callback) {
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 );
414}
415
416
417function verifyPassword(password, hashedPassword) {
418 return bcrypt.compareSync(password, hashedPassword);
419}
420
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`,
436 [hashedPassword, userId],
437 (err) => {
438
439 callback(err);
440 }
441 );
442}
443
444
445/*
446 * ============================================================
447 * PRODUCT FUNCTIONS
448 * ============================================================
449 */
450
451function getProducts(categoryId, searchTerm, callback) {
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
466 const params = [];
467 let paramIndex = 1;
468
469 if (categoryId && categoryId !== 'all') {
470
471 queryText +=
472 ` AND p.category_id = $${paramIndex}`;
473
474 params.push(categoryId);
475 paramIndex++;
476 }
477
478 if (searchTerm) {
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;
490 }
491
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 }
504 }
505 );
506}
507
508
509function getProductById(id, callback) {
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`,
522 [id],
523 (err, result) => {
524
525 if (err || result.rows.length === 0) {
526 callback(err, null);
527 return;
528 }
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 );
565 }
566 );
567}
568
569
570function getProductByCode(code, callback) {
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`,
583 [code],
584 (err, result) => {
585
586 if (err || result.rows.length === 0) {
587 callback(err, null);
588 return;
589 }
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 );
627 }
628 );
629}
630
631
632function addProduct(personalId, productData, callback) {
633
634 getGeneralCategoryId((err, generalCategoryId) => {
635
636 if (err) {
637 callback(err, null);
638 return;
639 }
640
641 const categoryId =
642 productData.category_id || generalCategoryId;
643
644 if (!productData.code) {
645 callback(
646 new Error('Product code is required'),
647 null
648 );
649 return;
650 }
651
652 if (!productData.store_id) {
653 callback(
654 new Error('Store ID is required'),
655 null
656 );
657 return;
658 }
659
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`,
694 [
695 productId,
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 ],
706 (err, result) => {
707
708 if (err) {
709 callback(err, null);
710 return;
711 }
712
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 );
734 }
735 }
736 );
737
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
760 );
761 }
762 }
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) => {
792
793 if (err) {
794 console.error(
795 'Error inserting image:',
796 err
797 );
798 }
799 }
800 );
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) => {
826
827 if (err) {
828 console.error(
829 'Error inserting color:',
830 err
831 );
832 }
833 }
834 );
835 });
836 }
837
838 callback(null, returnedId);
839 }
840 );
841 });
842}
843
844
845function updateProduct(
846 personalId,
847 productData,
848 callback
849) {
850
851 const updates = [];
852 const params = [];
853
854 if (productData.description !== undefined) {
855 updates.push(`description = $${params.length + 1}`);
856 params.push(productData.description);
857 }
858
859 if (productData.price !== undefined) {
860 updates.push(`price = $${params.length + 1}`);
861 params.push(productData.price);
862 }
863
864 if (productData.availability !== undefined) {
865 updates.push(`availability = $${params.length + 1}`);
866 params.push(productData.availability);
867 }
868
869 if (productData.weight !== undefined) {
870 updates.push(`weight = $${params.length + 1}`);
871 params.push(productData.weight);
872 }
873
874 if (productData.dimensions !== undefined) {
875 updates.push(`dimensions = $${params.length + 1}`);
876 params.push(productData.dimensions);
877 }
878
879 if (productData.production_time !== undefined) {
880 updates.push(`production_time = $${params.length + 1}`);
881 params.push(productData.production_time);
882 }
883
884 if (productData.category_id !== undefined) {
885 updates.push(`category_id = $${params.length + 1}`);
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
896 const codeParameter = params.length;
897
898 query(
899 `UPDATE product
900 SET ${updates.join(', ')}
901 WHERE code = $${codeParameter}`,
902 params,
903 (err, result) => {
904
905 if (err) {
906 callback(err, null);
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
935 );
936 }
937 }
938 );
939
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 }
964 }
965 );
966
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;
987 }
988
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 );
1020 }
1021
1022 /*
1023 * Colors
1024 */
1025 if (
1026 productData.colors &&
1027 Array.isArray(productData.colors)
1028 ) {
1029
1030 query(
1031 `DELETE FROM color
1032 WHERE product_code = $1`,
1033 [productData.code],
1034 (err) => {
1035
1036 if (err) {
1037 console.error(
1038 'Error deleting old colors:',
1039 err
1040 );
1041 return;
1042 }
1043
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 }
1063 }
1064 );
1065 });
1066 }
1067 );
1068 }
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 });
1160}
1161
1162
1163/*
1164 * ============================================================
1165 * CATEGORY FUNCTIONS
1166 * ============================================================
1167 */
1168
1169function getCategories(callback) {
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 );
1184}
1185
1186
1187function getCategoriesWithParents(callback) {
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`,
1198 [],
1199 (err, result) => {
1200
1201 callback(
1202 err,
1203 result ? result.rows : []
1204 );
1205 }
1206 );
1207}
1208
1209
1210function createCategory(categoryData, callback) {
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
1229 if (err) {
1230 callback(err, null);
1231 } else {
1232
1233 callback(null, {
1234 id: result.rows[0].category_id,
1235 name: categoryData.name,
1236 parent_id: categoryData.parent_id,
1237 description: categoryData.description
1238 });
1239 }
1240 }
1241 );
1242}
1243
1244
1245/*
1246 * ============================================================
1247 * STORE FUNCTIONS
1248 * ============================================================
1249 */
1250
1251function getStores(callback) {
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 );
1266}
1267
1268
1269function getStoreProducts(storeId, callback) {
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`,
1280 [storeId],
1281 (err, result) => {
1282
1283 callback(
1284 err,
1285 result ? result.rows : []
1286 );
1287 }
1288 );
1289}
1290
1291
1292function getStoreOrders(storeId, callback) {
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`,
1304 [storeId],
1305 (err, result) => {
1306
1307 if (err) {
1308 callback(err, null);
1309 return;
1310 }
1311
1312 const orders = result.rows || [];
1313
1314 if (orders.length === 0) {
1315 callback(null, []);
1316 return;
1317 }
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 });
1349 }
1350 );
1351}
1352
1353
1354function getStoreEmployees(storeId, callback) {
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`,
1370 [storeId],
1371 (err, result) => {
1372
1373 callback(
1374 err,
1375 result ? result.rows : []
1376 );
1377 }
1378 );
1379}
1380
1381
1382function getStoreReports(storeId, callback) {
1383
1384 query(
1385 `SELECT *
1386 FROM report
1387 WHERE store_id = $1
1388 ORDER BY generated_at DESC`,
1389 [storeId],
1390 (err, result) => {
1391
1392 callback(
1393 err,
1394 result ? result.rows : []
1395 );
1396 }
1397 );
1398}
1399
1400
1401function getStoreStats(storeId, callback) {
1402
1403 const stats = {};
1404
1405 query(
1406 `SELECT COUNT(*) AS total_products
1407 FROM product
1408 WHERE store_id = $1`,
1409 [storeId],
1410 (err, result) => {
1411
1412 if (err) {
1413 callback(err, null);
1414 return;
1415 }
1416
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`,
1426 [storeId],
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`,
1452 [storeId],
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`,
1477 [storeId],
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 );
1495 }
1496 );
1497 }
1498 );
1499 }
1500 );
1501 }
1502 );
1503}
1504
1505
1506/*
1507 * ============================================================
1508 * ORDER FUNCTIONS
1509 * ============================================================
1510 */
1511
1512function createOrderNew(orderData, callback) {
1513
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(() => {
1555
1556 const items =
1557 orderData.items || [];
1558
1559 if (items.length === 0) {
1560 return client.query('COMMIT')
1561 .then(() => {
1562
1563 client.release();
1564
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 });
1614 });
1615 })
1616 .catch(err => {
1617 callback(err, null);
1618 });
1619}
1620
1621
1622function getOrdersByClient(clientId, callback) {
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`,
1633 [clientId],
1634 (err, result) => {
1635
1636 if (err) {
1637 callback(err, null);
1638 return;
1639 }
1640
1641 const orders = result.rows || [];
1642
1643 if (orders.length === 0) {
1644 callback(null, []);
1645 return;
1646 }
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 });
1678 }
1679 );
1680}
1681
1682
1683function getAllOrders(callback) {
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`,
1697 [],
1698 (err, result) => {
1699
1700 callback(
1701 err,
1702 result ? result.rows : []
1703 );
1704 }
1705 );
1706}
1707
1708
1709/*
1710 * ============================================================
1711 * REVIEW FUNCTIONS
1712 * ============================================================
1713 */
1714
1715function createReviewNew(reviewData, callback) {
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`,
1734 [
1735 reviewId,
1736 reviewData.client_id,
1737 reviewData.product_code,
1738 reviewData.rating,
1739 reviewData.comment || ''
1740 ],
1741 (err, result) => {
1742
1743 if (err) {
1744 callback(err, null);
1745 } else {
1746 callback(
1747 null,
1748 result.rows[0].review_id
1749 );
1750 }
1751 }
1752 );
1753}
1754
1755
1756/*
1757 * ============================================================
1758 * REQUEST FUNCTIONS
1759 * ============================================================
1760 */
1761
1762function createRequest(requestData, callback) {
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)`,
1775 [
1776 requestData.request_num,
1777 requestData.date_and_time,
1778 requestData.problem,
1779 requestData.client_id,
1780 requestData.store_id
1781 ],
1782 (err) => {
1783
1784 if (err) {
1785 callback(err, null);
1786 } else {
1787 callback(
1788 null,
1789 requestData.request_num
1790 );
1791 }
1792 }
1793 );
1794}
1795
1796
1797/*
1798 * ============================================================
1799 * REFUND FUNCTIONS
1800 * ============================================================
1801 */
1802
1803function createRefund(refundData, callback) {
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())`,
1816 [
1817 refundData.refund_id,
1818 refundData.order_num,
1819 refundData.amount,
1820 refundData.reason
1821 ],
1822 (err) => {
1823
1824 if (err) {
1825 callback(err, null);
1826 } else {
1827 callback(
1828 null,
1829 refundData.refund_id
1830 );
1831 }
1832 }
1833 );
1834}
1835
1836
1837/*
1838 * ============================================================
1839 * EMPLOYEE TASKS
1840 * ============================================================
1841 */
1842
1843function getEmployeeTasks(
1844 personalId,
1845 storeId,
1846 callback
1847) {
1848
1849 const tasks = {
1850 pending_orders: [],
1851 pending_requests: [],
1852 pending_refunds: []
1853 };
1854
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
1875 if (!err) {
1876 tasks.pending_orders =
1877 result.rows || [];
1878 }
1879
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
1900 if (!err) {
1901 tasks.pending_requests =
1902 result.rows || [];
1903 }
1904
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
1930 if (!err) {
1931 tasks.pending_refunds =
1932 result.rows || [];
1933 }
1934
1935 callback(
1936 null,
1937 tasks
1938 );
1939 }
1940 );
1941 }
1942 );
1943 }
1944 );
1945}
1946
1947
1948/*
1949 * ============================================================
1950 * CLIENT STATISTICS
1951 * ============================================================
1952 */
1953
1954function getClientStats(clientId, callback) {
1955
1956 const stats = {};
1957
1958 query(
1959 `SELECT COUNT(*) AS total_orders
1960 FROM "order"
1961 WHERE client_id = $1`,
1962 [clientId],
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`,
1989 [clientId],
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 );
2057 }
2058 );
2059 }
2060 );
2061 }
2062 );
2063 }
2064 );
2065}
2066
2067
2068/*
2069 * ============================================================
2070 * ADMIN USER FUNCTIONS
2071 * ============================================================
2072 */
2073
2074function getAllUsers(callback) {
2075
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
2085 FROM client
2086 ORDER BY client_id`,
2087 [],
2088 (err, result) => {
2089
2090 if (err) {
2091 callback(err, null);
2092 return;
2093 }
2094
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
2121 FROM personal p
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
2128 ORDER BY p.id`,
2129 [],
2130 (err, result) => {
2131
2132 if (err) {
2133 callback(err, null);
2134 return;
2135 }
2136
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
2171 FROM users
2172 ORDER BY id`,
2173 [],
2174 (err, result) => {
2175
2176 if (err) {
2177 callback(err, null);
2178 return;
2179 }
2180
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 }
2219 });
2220
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 );
2238 }
2239 );
2240 }
2241 );
2242 }
2243 );
2244}
2245
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 ],
2282 (err) => {
2283
2284 if (err) {
2285 console.error(
2286 'Error logging audit:',
2287 err
2288 );
2289 }
2290 }
2291 );
2292}
2293
2294
2295/*
2296 * ============================================================
2297 * EXPORTS
2298 * ============================================================
2299 */
2300
2301module.exports = {
2302
2303 pool,
2304
2305 query,
2306
2307 ensureGeneralCategory,
2308 getGeneralCategoryId,
2309
2310 getUserByUsername,
2311 getUserById,
2312 createUser,
2313
2314 getClientByEmail,
2315 getClientById,
2316 createClient,
2317 verifyClientPassword,
2318
2319 getPersonalByEmail,
2320 getPersonalById,
2321 verifyPassword,
2322 updatePasswordAndClearForce,
2323
2324 getProducts,
2325 getProductById,
2326 getProductByCode,
2327 addProduct,
2328 updateProduct,
2329 deleteProduct,
2330
2331 getCategories,
2332 getCategoriesWithParents,
2333 createCategory,
2334
2335 getStores,
2336 getStoreProducts,
2337 getStoreOrders,
2338 getStoreEmployees,
2339 getStoreReports,
2340 getStoreStats,
2341
2342 createOrderNew,
2343 getOrdersByClient,
2344 getAllOrders,
2345
2346 createReviewNew,
2347
2348 createRequest,
2349
2350 createRefund,
2351
2352 getEmployeeTasks,
2353
2354 getClientStats,
2355
2356 getAllUsers,
2357
2358 logAudit
2359};
Note: See TracBrowser for help on using the repository browser.