source: view-database.js@ 6149556

main
Last change on this file since 6149556 was 81bc7da, checked in by Klimentina Efremova <klimentina08642@ā€¦>, 3 months ago

Initial commit
  • Property mode set to 100644
File size: 21.3 KB
LineĀ 
1const sqlite3 = require('sqlite3').verbose();
2const path = require('path');
3
4const dbPath = path.join(__dirname, 'database', 'handcraft.db');
5const db = new sqlite3.Database(dbPath, (err) => {
6 if (err) {
7 console.error('Error opening database:', err.message);
8 process.exit(1);
9 }
10});
11
12console.log('\nšŸŽØ HANDCRAFT MARKETPLACE - DATABASE CONTENTS\n');
13
14// Helper function to format date
15function formatDate(dateString) {
16 if (!dateString) return 'N/A';
17 const date = new Date(dateString);
18 return date instanceof Date && !isNaN(date) ? date.toString() : dateString;
19}
20
21// Helper function to check if table exists
22function tableExists(tableName, callback) {
23 db.get(
24 "SELECT name FROM sqlite_master WHERE type='table' AND name = ?",
25 [tableName],
26 (err, row) => {
27 callback(!err && row);
28 }
29 );
30}
31
32// Main execution chain
33function executeChecks() {
34 let currentCheck = 0;
35
36 const checks = [
37 { name: 'clients', func: checkClients },
38 { name: 'personal', func: checkPersonal },
39 { name: 'store', func: checkStore },
40 { name: 'product', func: checkProduct },
41 { name: 'category', func: checkCategory },
42 { name: 'works_in_store', func: checkWorksInStore },
43 { name: 'permissions', func: checkPermissions },
44 { name: 'employees', func: checkEmployees },
45 { name: 'boss', func: checkBoss },
46 { name: 'order', func: checkOrder },
47 { name: 'report', func: checkReport },
48 { name: 'refund', func: checkRefund },
49 { name: 'image', func: checkImage },
50 { name: 'color', func: checkColor }
51 ];
52
53 function next() {
54 currentCheck++;
55 if (currentCheck < checks.length) {
56 checks[currentCheck].func(next);
57 } else {
58 finish();
59 }
60 }
61
62 // Start with first check
63 checks[0].func(next);
64}
65
66// Display CLIENTS table
67function checkClients(next) {
68 console.log('\nšŸ‘¤ CLIENTS TABLE:');
69 console.log('================================================================================');
70
71 tableExists('client', (exists) => {
72 if (!exists) {
73 console.log('Table does not exist\n');
74 if (next) next();
75 return;
76 }
77
78 db.all('SELECT * FROM client', [], (err, rows) => {
79 if (err) {
80 console.log(`Error: ${err.message}\n`);
81 if (next) next();
82 return;
83 }
84
85 if (!rows || rows.length === 0) {
86 console.log('No clients found\n');
87 } else {
88 console.log(`Total: ${rows.length} clients\n`);
89 rows.forEach((client, index) => {
90 console.log(`ID: ${client.client_id} | Name: ${client.first_name || ''} ${client.last_name || ''}`);
91 console.log(`Email: ${client.email || 'N/A'}`);
92 if (index < rows.length - 1) console.log('-'.repeat(80));
93 });
94 console.log(); // Add blank line after table
95 }
96 if (next) next();
97 });
98 });
99}
100
101// Display PERSONAL table
102function checkPersonal(next) {
103 console.log('\nšŸ‘” PERSONAL TABLE (STORE OWNERS/EMPLOYEES):');
104 console.log('================================================================================');
105
106 tableExists('personal', (exists) => {
107 if (!exists) {
108 console.log('Table does not exist\n');
109 if (next) next();
110 return;
111 }
112
113 db.all(`
114 SELECT p.*,
115 CASE WHEN b.boss_id IS NOT NULL THEN 'BOSS'
116 WHEN e.employee_id IS NOT NULL THEN 'EMPLOYEE'
117 ELSE 'PERSONAL' END as role
118 FROM personal p
119 LEFT JOIN boss b ON p.id = b.boss_id
120 LEFT JOIN employees e ON p.id = e.employee_id
121 `, [], (err, rows) => {
122 if (err) {
123 console.log(`Error: ${err.message}\n`);
124 if (next) next();
125 return;
126 }
127
128 if (!rows || rows.length === 0) {
129 console.log('No personal records found\n');
130 } else {
131 console.log(`Total: ${rows.length} personal records\n`);
132 rows.forEach((person, index) => {
133 console.log(`ID: ${person.id || 'N/A'} | Name: ${person.first_name || ''} ${person.last_name || ''} | Role: ${person.role || 'N/A'}`);
134 console.log(`Email: ${person.email || 'N/A'} | SSN: ${person.ssn || 'N/A'}`);
135 if (index < rows.length - 1) console.log('-'.repeat(80));
136 });
137 console.log();
138 }
139 if (next) next();
140 });
141 });
142}
143
144// Display STORE table
145function checkStore(next) {
146 console.log('\nšŸŖ STORE TABLE:');
147 console.log('================================================================================');
148
149 tableExists('store', (exists) => {
150 if (!exists) {
151 console.log('Table does not exist\n');
152 if (next) next();
153 return;
154 }
155
156 db.all('SELECT * FROM store', [], (err, rows) => {
157 if (err) {
158 console.log(`Error: ${err.message}\n`);
159 if (next) next();
160 return;
161 }
162
163 if (!rows || rows.length === 0) {
164 console.log('No stores found\n');
165 } else {
166 console.log(`Total: ${rows.length} stores\n`);
167 rows.forEach((store, index) => {
168 console.log(`ID: ${store.store_id || 'N/A'} | Name: ${store.name || 'N/A'}`);
169 console.log(`Email: ${store.store_email || 'N/A'} | Rating: ${store.rating || '0.0'}`);
170 console.log(`Address: ${store.physical_address || 'N/A'}`);
171 console.log(`Founded: ${formatDate(store.date_of_founding)}`);
172 if (index < rows.length - 1) console.log('-'.repeat(80));
173 });
174 console.log();
175 }
176 if (next) next();
177 });
178 });
179}
180
181// Display PRODUCT table
182function checkProduct(next) {
183 console.log('\nšŸ›ļø PRODUCT TABLE:');
184 console.log('================================================================================');
185
186 tableExists('product', (exists) => {
187 if (!exists) {
188 console.log('Table does not exist\n');
189 if (next) next();
190 return;
191 }
192
193 db.all(`
194 SELECT p.*, c.name as category_name
195 FROM product p
196 LEFT JOIN category c ON p.category_id = c.category_id
197 `, [], (err, rows) => {
198 if (err) {
199 console.log(`Error: ${err.message}\n`);
200 if (next) next();
201 return;
202 }
203
204 if (!rows || rows.length === 0) {
205 console.log('No products found\n');
206 } else {
207 console.log(`Total: ${rows.length} products\n`);
208 rows.forEach((product, index) => {
209 console.log(`Code: ${product.code || 'N/A'} | Price: $${product.price || '0.00'}`);
210 console.log(`Description: ${product.description ? product.description.substring(0, 50) + (product.description.length > 50 ? '...' : '') : 'N/A'}`);
211 console.log(`Store ID: ${product.store_id || 'N/A'} | Category: ${product.category_name || 'N/A'}`);
212 console.log(`Available: ${product.availability || '0'} | Weight: ${product.weight || '0'}kg`);
213 if (index < rows.length - 1) console.log('-'.repeat(80));
214 });
215 console.log();
216 }
217 if (next) next();
218 });
219 });
220}
221
222// Display CATEGORY table
223function checkCategory(next) {
224 console.log('\nšŸ“ CATEGORY TABLE:');
225 console.log('================================================================================');
226
227 tableExists('category', (exists) => {
228 if (!exists) {
229 console.log('Table does not exist\n');
230 if (next) next();
231 return;
232 }
233
234 db.all(`
235 SELECT c1.*, c2.name as parent_name
236 FROM category c1
237 LEFT JOIN category c2 ON c1.parent_category_id = c2.category_id
238 `, [], (err, rows) => {
239 if (err) {
240 console.log(`Error: ${err.message}\n`);
241 if (next) next();
242 return;
243 }
244
245 if (!rows || rows.length === 0) {
246 console.log('No categories found\n');
247 } else {
248 console.log(`Total: ${rows.length} categories\n`);
249 rows.forEach((cat, index) => {
250 console.log(`ID: ${cat.category_id || 'N/A'} | Name: ${cat.name || 'N/A'}`);
251 console.log(`Parent ID: ${cat.parent_category_id || 'None'} | Parent Name: ${cat.parent_name || 'None'}`);
252 console.log(`Description: ${cat.description || 'No description'}`);
253 if (index < rows.length - 1) console.log('-'.repeat(80));
254 });
255 console.log();
256 }
257 if (next) next();
258 });
259 });
260}
261
262// Display WORKS_IN_STORE table
263function checkWorksInStore(next) {
264 console.log('\nšŸ”— WORKS_IN_STORE TABLE:');
265 console.log('================================================================================');
266
267 tableExists('works_in_store', (exists) => {
268 if (!exists) {
269 console.log('Table does not exist\n');
270 if (next) next();
271 return;
272 }
273
274 db.all(`
275 SELECT w.*, p.first_name, p.last_name, s.name as store_name
276 FROM works_in_store w
277 LEFT JOIN personal p ON w.personal_id = p.id
278 LEFT JOIN store s ON w.store_id = s.store_id
279 `, [], (err, rows) => {
280 if (err) {
281 console.log(`Error: ${err.message}\n`);
282 if (next) next();
283 return;
284 }
285
286 if (!rows || rows.length === 0) {
287 console.log('No assignments found\n');
288 } else {
289 console.log(`Total: ${rows.length} assignments\n`);
290 rows.forEach((assign, index) => {
291 console.log(`Personal ID: ${assign.personal_id || 'N/A'} | Store ID: ${assign.store_id || 'N/A'}`);
292 console.log(`Name: ${assign.first_name || ''} ${assign.last_name || ''} | Store: ${assign.store_name || 'N/A'}`);
293 if (index < rows.length - 1) console.log('-'.repeat(80));
294 });
295 console.log();
296 }
297 if (next) next();
298 });
299 });
300}
301
302// Display PERMISSIONS table
303function checkPermissions(next) {
304 console.log('\nšŸ” PERMISSIONS TABLE:');
305 console.log('================================================================================');
306
307 tableExists('permissions', (exists) => {
308 if (!exists) {
309 console.log('Table does not exist\n');
310 if (next) next();
311 return;
312 }
313
314 db.all(`
315 SELECT perm.*, p.first_name, p.last_name
316 FROM permissions perm
317 LEFT JOIN personal p ON perm.personal_id = p.id
318 `, [], (err, rows) => {
319 if (err) {
320 console.log(`Error: ${err.message}\n`);
321 if (next) next();
322 return;
323 }
324
325 if (!rows || rows.length === 0) {
326 console.log('No permissions found\n');
327 } else {
328 console.log(`Total: ${rows.length} permissions\n`);
329 rows.forEach((perm, index) => {
330 console.log(`Personal ID: ${perm.personal_id || 'N/A'} | Name: ${perm.first_name || ''} ${perm.last_name || ''}`);
331 console.log(`Type: ${perm.type || 'N/A'} | Authorization: ${perm.authorisation || 'N/A'}`);
332 if (index < rows.length - 1) console.log('-'.repeat(80));
333 });
334 console.log();
335 }
336 if (next) next();
337 });
338 });
339}
340
341// Display EMPLOYEES table
342function checkEmployees(next) {
343 console.log('\nšŸ‘· EMPLOYEES TABLE:');
344 console.log('================================================================================');
345
346 tableExists('employees', (exists) => {
347 if (!exists) {
348 console.log('Table does not exist\n');
349 if (next) next();
350 return;
351 }
352
353 db.all(`
354 SELECT e.*, p.first_name, p.last_name, p.email
355 FROM employees e
356 LEFT JOIN personal p ON e.employee_id = p.id
357 `, [], (err, rows) => {
358 if (err) {
359 console.log(`Error: ${err.message}\n`);
360 if (next) next();
361 return;
362 }
363
364 if (!rows || rows.length === 0) {
365 console.log('No employees found\n');
366 } else {
367 console.log(`Total: ${rows.length} employees\n`);
368 rows.forEach((emp, index) => {
369 console.log(`Employee ID: ${emp.employee_id || 'N/A'} | Name: ${emp.first_name || ''} ${emp.last_name || ''}`);
370 console.log(`Email: ${emp.email || 'N/A'} | Date Hired: ${formatDate(emp.date_of_hire)}`);
371 if (index < rows.length - 1) console.log('-'.repeat(80));
372 });
373 console.log();
374 }
375 if (next) next();
376 });
377 });
378}
379
380// Display BOSS table
381function checkBoss(next) {
382 console.log('\nšŸ‘‘ BOSS TABLE:');
383 console.log('================================================================================');
384
385 tableExists('boss', (exists) => {
386 if (!exists) {
387 console.log('Table does not exist\n');
388 if (next) next();
389 return;
390 }
391
392 db.all(`
393 SELECT b.*, p.first_name, p.last_name, p.email
394 FROM boss b
395 LEFT JOIN personal p ON b.boss_id = p.id
396 `, [], (err, rows) => {
397 if (err) {
398 console.log(`Error: ${err.message}\n`);
399 if (next) next();
400 return;
401 }
402
403 if (!rows || rows.length === 0) {
404 console.log('No bosses found\n');
405 } else {
406 console.log(`Total: ${rows.length} bosses\n`);
407 rows.forEach((boss, index) => {
408 console.log(`Boss ID: ${boss.boss_id || 'N/A'} | Name: ${boss.first_name || ''} ${boss.last_name || ''}`);
409 console.log(`Email: ${boss.email || 'N/A'} | Signature: ${boss.signature || 'N/A'}`);
410 if (index < rows.length - 1) console.log('-'.repeat(80));
411 });
412 console.log();
413 }
414 if (next) next();
415 });
416 });
417}
418
419// Display ORDER table
420function checkOrder(next) {
421 console.log('\nšŸ“¦ ORDERS TABLE:');
422 console.log('================================================================================');
423
424 tableExists('order', (exists) => {
425 if (!exists) {
426 console.log('Table does not exist\n');
427 if (next) next();
428 return;
429 }
430
431 db.all(`
432 SELECT o.*, c.first_name, c.last_name, s.name as store_name
433 FROM "order" o
434 LEFT JOIN client c ON o.client_id = c.client_id
435 LEFT JOIN store s ON o.store_id = s.store_id
436 `, [], (err, rows) => {
437 if (err) {
438 console.log(`Error: ${err.message}\n`);
439 if (next) next();
440 return;
441 }
442
443 if (!rows || rows.length === 0) {
444 console.log('No orders found\n');
445 } else {
446 console.log(`Total: ${rows.length} orders\n`);
447 rows.forEach((order, index) => {
448 console.log(`Order #: ${order.order_num || 'N/A'} | Client: ${order.first_name || ''} ${order.last_name || ''}`);
449 console.log(`Store: ${order.store_name || 'N/A'} | Date: ${formatDate(order.order_date)}`);
450 console.log(`Status: ${order.status || 'N/A'} | Payment: ${order.payment_method || 'N/A'}`);
451 console.log(`Delivery: ${order.delivery_address ? order.delivery_address.substring(0, 30) + '...' : 'N/A'}`);
452 if (index < rows.length - 1) console.log('-'.repeat(80));
453 });
454 console.log();
455 }
456 if (next) next();
457 });
458 });
459}
460
461// Display REPORT table
462function checkReport(next) {
463 console.log('\nšŸ“Š REPORTS TABLE:');
464 console.log('================================================================================');
465
466 tableExists('report', (exists) => {
467 if (!exists) {
468 console.log('Table does not exist\n');
469 if (next) next();
470 return;
471 }
472
473 db.all('SELECT * FROM report', [], (err, rows) => {
474 if (err) {
475 console.log(`Error: ${err.message}\n`);
476 if (next) next();
477 return;
478 }
479
480 if (!rows || rows.length === 0) {
481 console.log('No reports found\n');
482 } else {
483 console.log(`Total: ${rows.length} reports\n`);
484 rows.forEach((report, index) => {
485 console.log(`Report Date: ${formatDate(report.date)} | Store ID: ${report.store_id || 'N/A'}`);
486 console.log(`Profit: $${report.overall_profit || '0.00'} | Signature: ${report.owner_signature || 'N/A'}`);
487 if (index < rows.length - 1) console.log('-'.repeat(80));
488 });
489 console.log();
490 }
491 if (next) next();
492 });
493 });
494}
495
496// Display REFUND table
497function checkRefund(next) {
498 console.log('\nšŸ’° REFUND TABLE:');
499 console.log('================================================================================');
500
501 tableExists('refund', (exists) => {
502 if (!exists) {
503 console.log('Table does not exist\n');
504 if (next) next();
505 return;
506 }
507
508 db.all('SELECT * FROM refund', [], (err, rows) => {
509 if (err) {
510 console.log(`Error: ${err.message}\n`);
511 if (next) next();
512 return;
513 }
514
515 if (!rows || rows.length === 0) {
516 console.log('No refunds found\n');
517 } else {
518 console.log(`Total: ${rows.length} refunds\n`);
519 rows.forEach((refund, index) => {
520 console.log(`Refund ID: ${refund.refund_id || 'N/A'} | Order: ${refund.order_num || 'N/A'}`);
521 console.log(`Amount: $${refund.amount || '0.00'} | Status: ${refund.status || 'N/A'}`);
522 console.log(`Reason: ${refund.reason || 'N/A'}`);
523 if (index < rows.length - 1) console.log('-'.repeat(80));
524 });
525 console.log();
526 }
527 if (next) next();
528 });
529 });
530}
531
532// Display IMAGE table
533function checkImage(next) {
534 console.log('\nšŸ–¼ļø IMAGE TABLE:');
535 console.log('================================================================================');
536
537 tableExists('image', (exists) => {
538 if (!exists) {
539 console.log('Table does not exist\n');
540 if (next) next();
541 return;
542 }
543
544 db.all('SELECT * FROM image', [], (err, rows) => {
545 if (err) {
546 console.log(`Error: ${err.message}\n`);
547 if (next) next();
548 return;
549 }
550
551 if (!rows || rows.length === 0) {
552 console.log('No images found\n');
553 } else {
554 console.log(`Total: ${rows.length} images\n`);
555 rows.forEach((image, index) => {
556 console.log(`Product Code: ${image.product_code || 'N/A'} | Image: ${image.image || 'N/A'}`);
557 if (index < rows.length - 1) console.log('-'.repeat(80));
558 });
559 console.log();
560 }
561 if (next) next();
562 });
563 });
564}
565
566// Display COLOR table
567function checkColor(next) {
568 console.log('\nšŸŽØ COLOR TABLE:');
569 console.log('================================================================================');
570
571 tableExists('color', (exists) => {
572 if (!exists) {
573 console.log('Table does not exist\n');
574 if (next) next();
575 return;
576 }
577
578 db.all('SELECT * FROM color', [], (err, rows) => {
579 if (err) {
580 console.log(`Error: ${err.message}\n`);
581 if (next) next();
582 return;
583 }
584
585 if (!rows || rows.length === 0) {
586 console.log('No colors found\n');
587 } else {
588 console.log(`Total: ${rows.length} colors\n`);
589 rows.forEach((color, index) => {
590 console.log(`Product Code: ${color.product_code || 'N/A'} | Color: ${color.color || 'N/A'}`);
591 if (index < rows.length - 1) console.log('-'.repeat(80));
592 });
593 console.log();
594 }
595 if (next) next();
596 });
597 });
598}
599
600// Finish and close database
601function finish() {
602 console.log('='.repeat(80));
603 console.log('āœ… Database inspection complete');
604 console.log('='.repeat(80) + '\n');
605
606 db.close((err) => {
607 if (err) {
608 console.error('Error closing database:', err.message);
609 }
610 });
611}
612
613// Start the execution chain
614executeChecks();
Note: See TracBrowser for help on using the repository browser.