Changes in server.js [79fff4f:06ebe74]
Legend:
- Unmodified
- Added
- Removed
-
server.js
r79fff4f r06ebe74 1 1 const http = require('http'); 2 2 const url = require('url'); 3 const database = require('./database.js');3 const { Pool } = require('pg'); 4 4 const fs = require('fs'); 5 5 const path = require('path'); … … 25 25 const emailConfig = { 26 26 host: process.env.SMTP_HOST || 'smtp.gmail.com', 27 port: parseInt(process.env.SMTP_PORT) || 587, 27 port: (() => { 28 const configuredPort = parseInt(process.env.SMTP_PORT, 10); 29 if (process.env.SMTP_HOST === 'smtp.ethereal.email' && configuredPort === 3000) return 587; 30 return configuredPort || 587; 31 })(), 28 32 secure: false, 29 33 auth: { … … 169 173 let code = ''; 170 174 for(let i = 0; i < 6; i++) { 171 code += crypto.randomInt(0, 9);175 code += crypto.randomInt(0, 10); 172 176 } 173 177 return code; … … 318 322 319 323 database.database.get( 320 'SELECT boss_id FROM boss WHERE boss_id = ?',324 'SELECT boss_id FROM boss WHERE boss_id = $1', 321 325 [personalId], 322 326 (err, boss) => { … … 339 343 } 340 344 341 // Database initialization function 342 async function initializeDatabase() { 343 console.log('🔍 Checking database schema...'); 344 345 // List of all required tables 346 const requiredTables = [ 347 'client', 348 'store', 349 'category', 350 'users', 351 'personal', 352 'product', 353 'boss', 354 'employees', 355 'works_in_store', 356 'permissions', 357 'order', 358 'order_items', 359 'review', 360 'request', 361 'refund', 362 'report', 363 'audit_log', 364 'color', 365 'image', 366 'delivery_address', 367 'roles', 368 'user_roles' 369 ]; 370 371 try { 372 // For SQLite, we need to use a different approach to check tables 373 const result = await new Promise((resolve, reject) => { 374 database.database.all( 375 "SELECT name FROM sqlite_master WHERE type='table'", 376 [], 377 (err, rows) => { 378 if (err) reject(err); 379 else resolve(rows || []); 380 } 381 ); 382 }); 383 384 const existingTables = result.map(row => row.name); 385 const missingTables = requiredTables.filter(table => !existingTables.includes(table)); 386 387 if (missingTables.length > 0) { 388 console.log(`⚠️ Missing tables: ${missingTables.join(', ')}`); 389 console.log('🔄 Recreating entire database...'); 390 391 // Drop all tables in correct order (respecting foreign keys) 392 await dropAllTables(); 393 394 // Create all tables 395 await createAllTables(); 396 397 // Create indexes 398 await createIndexes(); 399 400 // Insert initial data 401 await insertInitialData(); 402 403 console.log('✅ Database recreation completed'); 404 } else { 405 console.log('✅ All required tables exist'); 406 // Even if tables exist, ensure admin user exists with ID 000000 407 await ensureAdminUser(); 408 } 409 } catch (err) { 410 console.error('❌ Error checking database schema:', err); 411 console.log('⚠️ Attempting to recreate database anyway...'); 412 413 try { 414 await dropAllTables(); 415 await createAllTables(); 416 await createIndexes(); 417 await insertInitialData(); 418 console.log('✅ Database recreation completed'); 419 } catch (createErr) { 420 console.error('❌ Failed to recreate database:', createErr); 421 } 422 } 345 346 347 const pool = new Pool({ 348 connectionString: process.env.DATABASE_URL, 349 host: process.env.PGHOST || process.env.DB_HOST || 'localhost', 350 port: Number(process.env.PGPORT || process.env.DB_PORT || 5432), 351 user: process.env.PGUSER || process.env.DB_USER || 'postgres', 352 password: process.env.PGPASSWORD || process.env.DB_PASSWORD || '', 353 database: process.env.PGDATABASE || process.env.DB_NAME || 'handcraft_marketplace', 354 max: Number(process.env.PG_POOL_MAX || 10), 355 idleTimeoutMillis: 30000 356 }); 357 358 let transactionClient = null; 359 360 function dbQuery(sql, params = [], callback) { 361 const client = transactionClient || pool; 362 client.query(sql, params) 363 .then(result => callback(null, result)) 364 .catch(err => callback(err)); 423 365 } 424 366 425 // Function to ensure admin user exists with ID 000000 426 function ensureAdminUser() { 427 return new Promise((resolve) => { 428 database.database.get( 429 'SELECT * FROM users WHERE id = ? OR username = ? OR email = ?', 430 ['000000', 'admin', 'admin@handcraft.com'], 431 (err, existingAdmin) => { 432 if (err) { 433 console.error('Error checking for existing admin:', err.message); 434 resolve(); 367 const database = { 368 database: { 369 get(sql, params, callback) { 370 if (typeof params === 'function') { 371 callback = params; 372 params = []; 373 } 374 dbQuery(sql, params || [], (err, result) => { 375 callback(err, result && result.rows ? result.rows[0] : undefined); 376 }); 377 }, 378 all(sql, params, callback) { 379 if (typeof params === 'function') { 380 callback = params; 381 params = []; 382 } 383 dbQuery(sql, params || [], (err, result) => { 384 callback(err, result ? result.rows : []); 385 }); 386 }, 387 run(sql, params, callback) { 388 if (typeof params === 'function') { 389 callback = params; 390 params = []; 391 } 392 const normalized = String(sql).trim().replace(/;\s*$/, '').toUpperCase(); 393 394 if (normalized === 'BEGIN TRANSACTION' || normalized === 'BEGIN') { 395 if (transactionClient) { 396 callback?.(null); 435 397 return; 436 398 } 437 438 // Insert admin user if it doesn't exist 439 if (!existingAdmin) { 440 const adminId = '000000'; 441 const adminPassword = bcrypt.hashSync('Admin123!', 10); 442 443 // Start a transaction 444 database.database.run('BEGIN TRANSACTION', (err) => { 445 if (err) { 446 console.error('Error beginning transaction:', err); 447 resolve(); 448 return; 449 } 450 451 // Insert into users table 452 database.database.run( 453 `INSERT INTO users (id, username, email, password, user_type, force_password_change) 454 VALUES (?, ?, ?, ?, ?, ?)`, 455 [adminId, 'admin', 'admin@handcraft.com', adminPassword, 'admin', 1], 456 function(err) { 457 if (err) { 458 database.database.run('ROLLBACK'); 459 console.error('Error inserting admin user:', err.message); 460 resolve(); 461 return; 462 } 463 464 // Insert into personal table (required for boss table) 465 database.database.run( 466 `INSERT INTO personal (id, first_name, last_name, ssn, email, password) 467 VALUES (?, ?, ?, ?, ?, ?)`, 468 [adminId, 'Admin', 'User', '0000000000000', 'admin@handcraft.com', adminPassword], 469 function(err) { 470 if (err) { 471 database.database.run('ROLLBACK'); 472 console.error('Error inserting admin personal:', err.message); 473 resolve(); 474 return; 475 } 476 477 // Insert into boss table (store owner) 478 database.database.run( 479 `INSERT INTO boss (boss_id, signature) 480 VALUES (?, ?)`, 481 [adminId, 'Admin Signature'], 482 function(err) { 483 if (err) { 484 database.database.run('ROLLBACK'); 485 console.error('Error inserting admin boss:', err.message); 486 resolve(); 487 return; 488 } 489 490 // Insert into permissions 491 database.database.run( 492 `INSERT INTO permissions (personal_id, type, authorisation) 493 VALUES (?, ?, ?)`, 494 [adminId, 'ADMIN', 'full_access'], 495 function(err) { 496 if (err) { 497 console.error('Error inserting admin permissions:', err.message); 498 // Continue even if this fails 499 } 500 501 // Assign admin role 502 database.database.get( 503 'SELECT role_id FROM roles WHERE name = ?', 504 ['admin'], 505 (err, adminRole) => { 506 if (!err && adminRole) { 507 database.database.run( 508 'INSERT OR IGNORE INTO user_roles (user_id, role_id) VALUES (?, ?)', 509 [adminId, adminRole.role_id], 510 (err) => { 511 if (err) { 512 console.error('Error assigning admin role:', err.message); 513 } 514 } 515 ); 516 } 517 518 database.database.run('COMMIT', (commitErr) => { 519 if (commitErr) { 520 console.error('Error committing transaction:', commitErr); 521 database.database.run('ROLLBACK'); 522 } else { 523 console.log('\n'); 524 console.log('🔐 ===== ADMIN CREDENTIALS ====='); 525 console.log('🆔 ID: 000000'); 526 console.log('👤 Username: admin'); 527 console.log('📧 Email: admin@handcraft.com'); 528 console.log('🔑 Password: Admin123!'); 529 console.log('⚠️ This is a first-time login. You will be required to change your password after 2FA verification.'); 530 console.log('================================\n'); 531 } 532 resolve(); 533 }); 534 } 535 ); 536 } 537 ); 538 } 539 ); 540 } 541 ); 542 } 543 ); 399 pool.connect().then(client => { 400 transactionClient = client; 401 return client.query('BEGIN'); 402 }).then(() => callback?.(null)) 403 .catch(err => { 404 if (transactionClient) transactionClient.release(); 405 transactionClient = null; 406 callback?.(err); 544 407 }); 545 } else { 546 console.log('✅ Admin user already exists with ID:', existingAdmin.id); 547 resolve(); 408 return; 409 } 410 411 if (normalized === 'COMMIT') { 412 if (!transactionClient) { 413 callback?.(null); 414 return; 548 415 } 549 } 550 ); 551 }); 552 } 553 554 function dropAllTables() { 555 return new Promise((resolve, reject) => { 556 console.log('🗑️ Dropping all tables...'); 557 558 // Drop in reverse order of creation (respect foreign keys) 559 const dropQueries = [ 560 'DROP TABLE IF EXISTS user_roles', 561 'DROP TABLE IF EXISTS roles', 562 'DROP TABLE IF EXISTS delivery_address', 563 'DROP TABLE IF EXISTS image', 564 'DROP TABLE IF EXISTS color', 565 'DROP TABLE IF EXISTS audit_log', 566 'DROP TABLE IF EXISTS report', 567 'DROP TABLE IF EXISTS refund', 568 'DROP TABLE IF EXISTS request', 569 'DROP TABLE IF EXISTS review', 570 'DROP TABLE IF EXISTS order_items', 571 'DROP TABLE IF EXISTS "order"', 572 'DROP TABLE IF EXISTS permissions', 573 'DROP TABLE IF EXISTS works_in_store', 574 'DROP TABLE IF EXISTS employees', 575 'DROP TABLE IF EXISTS boss', 576 'DROP TABLE IF EXISTS product', 577 'DROP TABLE IF EXISTS personal', 578 'DROP TABLE IF EXISTS users', 579 'DROP TABLE IF EXISTS category', 580 'DROP TABLE IF EXISTS store', 581 'DROP TABLE IF EXISTS client' 582 ]; 583 584 let index = 0; 585 586 function runNext() { 587 if (index >= dropQueries.length) { 588 console.log('✅ All tables dropped'); 589 resolve(); 416 const client = transactionClient; 417 client.query('COMMIT') 418 .then(() => { 419 transactionClient = null; 420 client.release(); 421 callback?.(null); 422 }) 423 .catch(err => { 424 transactionClient = null; 425 client.release(); 426 callback?.(err); 427 }); 590 428 return; 591 429 } 592 430 593 database.database.run(dropQueries[index], [], (err) =>{594 if ( err) {595 c onsole.error(`Error dropping table: ${err.message}`);596 // Continue anyway431 if (normalized === 'ROLLBACK') { 432 if (!transactionClient) { 433 callback?.(null); 434 return; 597 435 } 598 index++; 599 runNext(); 436 const client = transactionClient; 437 client.query('ROLLBACK') 438 .then(() => { 439 transactionClient = null; 440 client.release(); 441 callback?.(null); 442 }) 443 .catch(err => { 444 transactionClient = null; 445 client.release(); 446 callback?.(err); 447 }); 448 return; 449 } 450 451 dbQuery(sql, params || [], (err, result) => { 452 if (callback) { 453 callback.call( 454 { changes: result ? result.rowCount : 0, lastID: result?.rows?.[0]?.id }, 455 err 456 ); 457 } 600 458 }); 601 459 } 602 603 runNext(); 604 }); 605 } 606 607 function createAllTables() { 608 return new Promise((resolve, reject) => { 609 console.log('🏗️ Creating tables...'); 610 611 const createQueries = [ 612 // Client table (SERIAL ID starting from 1000) 613 `CREATE TABLE IF NOT EXISTS client ( 614 client_id INTEGER PRIMARY KEY AUTOINCREMENT, 615 first_name VARCHAR(100) NOT NULL, 616 last_name VARCHAR(100) NOT NULL, 617 email VARCHAR(255) UNIQUE NOT NULL, 618 password VARCHAR(255) NOT NULL, 619 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 620 )`, 621 622 // Store table (VARCHAR ID) 623 `CREATE TABLE IF NOT EXISTS store ( 624 store_id VARCHAR(10) PRIMARY KEY, 625 name VARCHAR(255) NOT NULL, 460 }, 461 462 async initializeDatabase() { 463 // The database supplied by the project is authoritative. Existing tables 464 // are removed before recreation so an old incompatible schema can never 465 // survive a restart and cause CREATE TABLE IF NOT EXISTS to skip columns. 466 const schemaCompatibility = await pool.query(` 467 SELECT 468 EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='product') AS product_exists, 469 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='category_id') AS product_has_category, 470 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='store_id') AS product_has_store, 471 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='order' AND column_name='store_id') AS order_has_store, 472 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='request' AND column_name='store_id') AS request_has_store, 473 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='review' AND column_name='review_id') AS review_has_review_id, 474 EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='refund' AND column_name='request_date') AS refund_has_request_date 475 `); 476 477 const c = schemaCompatibility.rows[0]; 478 const forceReset = String(process.env.RESET_DATABASE || '').toLowerCase() === 'true' || process.env.RESET_DATABASE === '1'; 479 const schemaMismatch = !c.product_exists || !c.product_has_category || c.product_has_store || c.order_has_store || c.request_has_store || c.review_has_review_id || c.refund_has_request_date; 480 const resetDatabase = forceReset || schemaMismatch; 481 482 if (resetDatabase) { 483 console.log('🧹 Existing PostgreSQL schema is incompatible with the project schema. Recreating project tables...'); 484 await pool.query(` 485 DROP TABLE IF EXISTS audit_log CASCADE; 486 DROP TABLE IF EXISTS user_roles CASCADE; 487 DROP TABLE IF EXISTS roles CASCADE; 488 DROP TABLE IF EXISTS users CASCADE; 489 DROP TABLE IF EXISTS approves CASCADE; 490 DROP TABLE IF EXISTS includes CASCADE; 491 DROP TABLE IF EXISTS sells CASCADE; 492 DROP TABLE IF EXISTS worked CASCADE; 493 DROP TABLE IF EXISTS works_in_store CASCADE; 494 DROP TABLE IF EXISTS makes_change CASCADE; 495 DROP TABLE IF EXISTS "change" CASCADE; 496 DROP TABLE IF EXISTS for_store CASCADE; 497 DROP TABLE IF EXISTS answers CASCADE; 498 DROP TABLE IF EXISTS makes_request CASCADE; 499 DROP TABLE IF EXISTS request CASCADE; 500 DROP TABLE IF EXISTS exchanges_data CASCADE; 501 DROP TABLE IF EXISTS monthly_profit CASCADE; 502 DROP TABLE IF EXISTS report CASCADE; 503 DROP TABLE IF EXISTS refund CASCADE; 504 DROP TABLE IF EXISTS review CASCADE; 505 DROP TABLE IF EXISTS "order" CASCADE; 506 DROP TABLE IF EXISTS delivery_address CASCADE; 507 DROP TABLE IF EXISTS client CASCADE; 508 DROP TABLE IF EXISTS employees CASCADE; 509 DROP TABLE IF EXISTS boss CASCADE; 510 DROP TABLE IF EXISTS permissions CASCADE; 511 DROP TABLE IF EXISTS personal CASCADE; 512 DROP TABLE IF EXISTS color CASCADE; 513 DROP TABLE IF EXISTS image CASCADE; 514 DROP TABLE IF EXISTS product CASCADE; 515 DROP TABLE IF EXISTS store CASCADE; 516 DROP TABLE IF EXISTS category CASCADE; 517 `); 518 } 519 520 const schema = ` 521 CREATE TABLE IF NOT EXISTS category ( 522 id SERIAL PRIMARY KEY, 523 name VARCHAR(50) NOT NULL, 524 parent_category_id INTEGER REFERENCES category(id) NOT NULL 525 ); 526 527 CREATE TABLE IF NOT EXISTS product ( 528 code VARCHAR(8) PRIMARY KEY DEFAULT '-1', 529 price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0), 530 availability INTEGER NOT NULL, 531 weight DECIMAL(5,2) NOT NULL CHECK (weight > 0), 532 width_x_length_x_depth VARCHAR(20) NOT NULL, 533 aprox_production_time INTEGER NOT NULL, 534 description VARCHAR(500) NOT NULL, 535 category_id INTEGER NOT NULL REFERENCES category(id) ON DELETE SET DEFAULT 536 ); 537 538 CREATE TABLE IF NOT EXISTS image ( 539 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, 540 image VARCHAR NOT NULL DEFAULT 'Image NOT found!' 541 ); 542 543 CREATE TABLE IF NOT EXISTS color ( 544 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, 545 color VARCHAR(50) 546 ); 547 548 CREATE TABLE IF NOT EXISTS store ( 549 store_ID VARCHAR(3) PRIMARY KEY, 550 name VARCHAR(50) UNIQUE NOT NULL, 626 551 date_of_founding DATE NOT NULL, 627 physical_address TEXT NOT NULL, 628 store_email VARCHAR(255) UNIQUE NOT NULL, 629 rating DECIMAL(3,2) DEFAULT 0.0 630 )`, 631 632 // Category table (SERIAL ID starting from 1) 633 `CREATE TABLE IF NOT EXISTS category ( 634 category_id INTEGER PRIMARY KEY AUTOINCREMENT, 635 name VARCHAR(100) NOT NULL, 636 description TEXT, 637 parent_category_id INTEGER REFERENCES category(category_id) ON DELETE SET NULL 638 )`, 639 640 // Users table (VARCHAR ID) 641 `CREATE TABLE IF NOT EXISTS users ( 552 physical_address VARCHAR(100) NOT NULL, 553 store_email VARCHAR(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), 554 rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0) 555 ); 556 557 CREATE TABLE IF NOT EXISTS personal ( 558 id VARCHAR(10) PRIMARY KEY, 559 first_name VARCHAR(20) NOT NULL, 560 last_name VARCHAR(20) NOT NULL, 561 ssn VARCHAR(13) NOT NULL CHECK (ssn ~ '^[0-9]{13}$'), 562 email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), 563 password VARCHAR NOT NULL 564 ); 565 566 CREATE TABLE IF NOT EXISTS permissions ( 567 personal_is VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE, 568 type VARCHAR(50) NOT NULL, 569 authorisation VARCHAR(50) NOT NULL 570 ); 571 572 CREATE TABLE IF NOT EXISTS boss ( 573 boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE 574 ); 575 576 CREATE TABLE IF NOT EXISTS employees ( 577 employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE, 578 date_of_hire DATE NOT NULL 579 ); 580 581 CREATE TABLE IF NOT EXISTS client ( 582 client_ID SERIAL PRIMARY KEY, 583 first_name VARCHAR(50) NOT NULL, 584 last_name VARCHAR(50) NOT NULL, 585 email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), 586 password VARCHAR NOT NULL 587 ); 588 589 CREATE TABLE IF NOT EXISTS delivery_address ( 590 client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID) ON DELETE CASCADE, 591 address VARCHAR(200) NOT NULL, 592 city VARCHAR(30) NOT NULL, 593 postcode VARCHAR(20) NOT NULL, 594 country VARCHAR(40) NOT NULL, 595 is_default BOOLEAN DEFAULT TRUE 596 ); 597 598 CREATE TABLE IF NOT EXISTS "order" ( 599 order_num VARCHAR(11) PRIMARY KEY, 600 client_ID INTEGER REFERENCES client(client_ID) ON DELETE CASCADE, 601 status VARCHAR(20) NOT NULL DEFAULT 'placed order', 602 last_date_mod TIMESTAMP NOT NULL, 603 payment_method VARCHAR(250) NOT NULL, 604 discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<=100.00), 605 CONSTRAINT check_status CHECK (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled')) 606 ); 607 608 CREATE TABLE IF NOT EXISTS review ( 609 order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num) ON DELETE CASCADE, 610 comment VARCHAR(300), 611 rating DECIMAL(2,1) NOT NULL CHECK(rating>=0.0 AND rating<=5.0), 612 last_mod_date TIMESTAMP NOT NULL 613 ); 614 615 CREATE TABLE IF NOT EXISTS refund ( 616 refund_id SERIAL PRIMARY KEY, 617 order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE, 618 reason VARCHAR(300), 619 amount DECIMAL(5,2) NOT NULL, 620 status VARCHAR(100) NOT NULL DEFAULT 'requested refund', 621 CONSTRAINT check_refund_status CHECK (status IN ('requested refund', 'being revied', 'approved', 'not approved', 'processed')) 622 ); 623 624 CREATE TABLE IF NOT EXISTS report ( 625 date TIMESTAMP NOT NULL, 626 store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID) ON DELETE CASCADE, 627 overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0), 628 sales_trend VARCHAR(100) NOT NULL, 629 marketing_growth VARCHAR(100) NOT NULL, 630 owner_signature VARCHAR(50) NOT NULL DEFAULT 'Not signed yet', 631 PRIMARY KEY (date, store_ID) 632 ); 633 634 CREATE TABLE IF NOT EXISTS monthly_profit ( 635 report_date TIMESTAMP NOT NULL, 636 store_ID VARCHAR(3) NOT NULL, 637 month_and_year DATE NOT NULL, 638 profit NUMERIC NOT NULL DEFAULT 0.0, 639 PRIMARY KEY(report_date, store_ID), 640 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE 641 ); 642 643 CREATE TABLE IF NOT EXISTS exchanges_data ( 644 report_date TIMESTAMP NOT NULL, 645 store_ID VARCHAR(3) NOT NULL, 646 monthly_profit NUMERIC NOT NULL DEFAULT 0.0, 647 date TIMESTAMP NOT NULL, 648 sales NUMERIC NOT NULL DEFAULT 0.0, 649 damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages<=0), 650 PRIMARY KEY (report_date, store_ID), 651 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE 652 ); 653 654 CREATE TABLE IF NOT EXISTS request ( 655 request_num VARCHAR(14) PRIMARY KEY, 656 date_and_time TIMESTAMP NOT NULL, 657 problem VARCHAR(300) NOT NULL, 658 notes_of_communication VARCHAR, 659 customer_satisfaction NUMERIC NOT NULL 660 ); 661 662 CREATE TABLE IF NOT EXISTS makes_request ( 663 client_ID INTEGER NOT NULL REFERENCES client(client_ID) ON DELETE CASCADE, 664 order_num VARCHAR(11) UNIQUE NOT NULL REFERENCES "order"(order_num) ON DELETE CASCADE, 665 PRIMARY KEY(client_ID, order_num) 666 ); 667 668 CREATE TABLE IF NOT EXISTS answers ( 669 request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE, 670 personal_id VARCHAR(10) NOT NULL REFERENCES personal(id) ON DELETE CASCADE, 671 PRIMARY KEY(request_num, personal_id) 672 ); 673 674 CREATE TABLE IF NOT EXISTS for_store ( 675 request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE, 676 store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE, 677 PRIMARY KEY(request_num, store_ID) 678 ); 679 680 CREATE TABLE IF NOT EXISTS "change" ( 681 date_and_time TIMESTAMP NOT NULL, 682 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, 683 changes VARCHAR NOT NULL, 684 PRIMARY KEY (date_and_time, product_code) 685 ); 686 687 CREATE TABLE IF NOT EXISTS makes_change ( 688 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, 689 change_date_time TIMESTAMP, 690 product_code VARCHAR(8), 691 PRIMARY KEY(personal_id, change_date_time, product_code), 692 FOREIGN KEY(change_date_time, product_code) REFERENCES "change"(date_and_time, product_code) ON DELETE CASCADE 693 ); 694 695 CREATE TABLE IF NOT EXISTS works_in_store ( 696 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, 697 store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE, 698 PRIMARY KEY(personal_id, store_ID) 699 ); 700 701 CREATE TABLE IF NOT EXISTS worked ( 702 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, 703 report_date TIMESTAMP, 704 store_ID VARCHAR(3), 705 wage NUMERIC NOT NULL CHECK (wage>=0), 706 pay_method VARCHAR DEFAULT 'full-time', 707 total_hours NUMERIC NOT NULL, 708 week VARCHAR(23) NOT NULL, 709 PRIMARY KEY (personal_id, report_date, store_ID), 710 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE, 711 CONSTRAINT check_pay_method CHECK (pay_method IN ('full_time', 'part-time', 'custom')) 712 ); 713 714 CREATE TABLE IF NOT EXISTS sells ( 715 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, 716 store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE, 717 discount NUMERIC NOT NULL DEFAULT 0.0, 718 PRIMARY KEY (product_code, store_ID) 719 ); 720 721 CREATE TABLE IF NOT EXISTS includes ( 722 order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE, 723 product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, 724 quantity INTEGER NOT NULL CHECK(quantity>=0), 725 PRIMARY KEY (order_num, product_code) 726 ); 727 728 CREATE TABLE IF NOT EXISTS approves ( 729 boss_id VARCHAR(10) REFERENCES boss(boss_id) ON DELETE CASCADE, 730 report_date TIMESTAMP, 731 store_ID VARCHAR(3), 732 owner_signature VARCHAR NOT NULL, 733 PRIMARY KEY (boss_id, report_date, store_ID), 734 FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE 735 ); 736 737 -- These four small tables are application authentication/audit storage. 738 -- They do not modify any of the project tables above. 739 CREATE TABLE IF NOT EXISTS users ( 642 740 id VARCHAR(50) PRIMARY KEY, 643 741 username VARCHAR(100) UNIQUE NOT NULL, … … 645 743 password VARCHAR(255) NOT NULL, 646 744 user_type VARCHAR(50) NOT NULL, 647 force_password_change INTEGER DEFAULT 0,745 force_password_change BOOLEAN DEFAULT FALSE, 648 746 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 649 )`, 650 651 // Personal table (VARCHAR ID - format: storeId(3) + '001' for owner, storeId(3) + employeeNum(3) for employees) 652 `CREATE TABLE IF NOT EXISTS personal ( 653 id VARCHAR(10) PRIMARY KEY, 654 first_name VARCHAR(100) NOT NULL, 655 last_name VARCHAR(100) NOT NULL, 656 ssn VARCHAR(13) UNIQUE NOT NULL, 657 email VARCHAR(255) UNIQUE NOT NULL, 658 password VARCHAR(255) NOT NULL, 659 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 660 )`, 661 662 // Product table (VARCHAR ID) 663 `CREATE TABLE IF NOT EXISTS product ( 664 id VARCHAR(50) PRIMARY KEY, 665 code VARCHAR(20) UNIQUE NOT NULL, 666 description TEXT NOT NULL, 667 price DECIMAL(10,2) NOT NULL, 668 availability INTEGER NOT NULL DEFAULT 0, 669 weight DECIMAL(10,2), 670 dimensions VARCHAR(50), 671 production_time INTEGER, 672 category_id INTEGER REFERENCES category(category_id) ON DELETE SET NULL, 673 store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE, 674 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 675 )`, 676 677 // Boss table (VARCHAR ID - references personal.id) 678 `CREATE TABLE IF NOT EXISTS boss ( 679 boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE, 680 signature TEXT NOT NULL, 681 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 682 )`, 683 684 // Employees table (VARCHAR ID - references personal.id) 685 `CREATE TABLE IF NOT EXISTS employees ( 686 employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE, 687 date_of_hire DATE NOT NULL, 688 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 689 )`, 690 691 // Works_in_store table (junction) 692 `CREATE TABLE IF NOT EXISTS works_in_store ( 693 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, 694 store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE, 695 PRIMARY KEY (personal_id, store_id) 696 )`, 697 698 // Permissions table 699 `CREATE TABLE IF NOT EXISTS permissions ( 700 permission_id INTEGER PRIMARY KEY AUTOINCREMENT, 701 personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, 702 type VARCHAR(50) NOT NULL, 703 authorisation TEXT, 704 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 705 )`, 706 707 // Order table (VARCHAR ID) 708 `CREATE TABLE IF NOT EXISTS "order" ( 709 order_num VARCHAR(20) PRIMARY KEY, 710 client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL, 711 order_date TIMESTAMP NOT NULL, 712 quantity INTEGER NOT NULL, 713 payment_method VARCHAR(50) NOT NULL, 714 discount DECIMAL(10,2) DEFAULT 0, 715 delivery_address TEXT NOT NULL, 716 store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE SET NULL, 717 status VARCHAR(50) DEFAULT 'pending', 718 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 719 )`, 720 721 // Order_items table 722 `CREATE TABLE IF NOT EXISTS order_items ( 723 item_id INTEGER PRIMARY KEY AUTOINCREMENT, 724 order_num VARCHAR(20) REFERENCES "order"(order_num) ON DELETE CASCADE, 725 product_code VARCHAR(20) REFERENCES product(code) ON DELETE SET NULL, 726 quantity INTEGER NOT NULL, 727 price DECIMAL(10,2) NOT NULL, 728 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 729 )`, 730 731 // Review table (VARCHAR ID) 732 `CREATE TABLE IF NOT EXISTS review ( 733 review_id VARCHAR(20) PRIMARY KEY, 734 client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL, 735 product_code VARCHAR(20) REFERENCES product(code) ON DELETE CASCADE, 736 rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5), 737 comment TEXT, 738 review_date TIMESTAMP NOT NULL, 739 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 740 )`, 741 742 // Request table (VARCHAR ID) 743 `CREATE TABLE IF NOT EXISTS request ( 744 request_num VARCHAR(50) PRIMARY KEY, 745 date_and_time TIMESTAMP NOT NULL, 746 problem TEXT NOT NULL, 747 client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL, 748 store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE, 749 status VARCHAR(50) DEFAULT 'pending', 750 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 751 )`, 752 753 // Refund table (VARCHAR ID) 754 `CREATE TABLE IF NOT EXISTS refund ( 755 refund_id VARCHAR(50) PRIMARY KEY, 756 order_num VARCHAR(20) REFERENCES "order"(order_num) ON DELETE CASCADE, 757 amount DECIMAL(10,2) NOT NULL, 758 reason TEXT NOT NULL, 759 status VARCHAR(50) DEFAULT 'pending', 760 request_date TIMESTAMP NOT NULL, 761 processed_date TIMESTAMP, 762 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 763 )`, 764 765 // Report table (VARCHAR ID) 766 `CREATE TABLE IF NOT EXISTS report ( 767 id VARCHAR(50) PRIMARY KEY, 768 store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE, 769 period VARCHAR(50) NOT NULL, 770 start_date DATE NOT NULL, 771 end_date DATE NOT NULL, 772 type VARCHAR(50) NOT NULL, 773 generated_by VARCHAR(10) REFERENCES personal(id) ON DELETE SET NULL, 774 generated_at TIMESTAMP NOT NULL, 775 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 776 )`, 777 778 // Audit_log table (SERIAL ID) 779 `CREATE TABLE IF NOT EXISTS audit_log ( 780 log_id INTEGER PRIMARY KEY AUTOINCREMENT, 747 ); 748 749 CREATE TABLE IF NOT EXISTS roles ( 750 role_id SERIAL PRIMARY KEY, 751 name VARCHAR(50) UNIQUE NOT NULL, 752 description TEXT 753 ); 754 755 CREATE TABLE IF NOT EXISTS user_roles ( 756 user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE, 757 role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE, 758 PRIMARY KEY(user_id, role_id) 759 ); 760 761 CREATE TABLE IF NOT EXISTS audit_log ( 762 log_id BIGSERIAL PRIMARY KEY, 781 763 user_id VARCHAR(50), 782 764 action VARCHAR(100) NOT NULL, … … 786 768 ip_address VARCHAR(45), 787 769 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 788 )`, 789 790 // Color table (SERIAL ID) 791 `CREATE TABLE IF NOT EXISTS color ( 792 color_id INTEGER PRIMARY KEY AUTOINCREMENT, 793 name VARCHAR(50) NOT NULL, 794 hex_code VARCHAR(7) NOT NULL, 795 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 796 )`, 797 798 // Image table (SERIAL ID) 799 `CREATE TABLE IF NOT EXISTS image ( 800 image_id INTEGER PRIMARY KEY AUTOINCREMENT, 801 product_code VARCHAR(20) REFERENCES product(code) ON DELETE CASCADE, 802 image_url TEXT NOT NULL, 803 is_primary BOOLEAN DEFAULT FALSE, 804 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 805 )`, 806 807 // Delivery_address table (SERIAL ID) 808 `CREATE TABLE IF NOT EXISTS delivery_address ( 809 address_id INTEGER PRIMARY KEY AUTOINCREMENT, 810 client_id INTEGER REFERENCES client(client_id) ON DELETE CASCADE, 811 address TEXT NOT NULL, 812 city VARCHAR(100) NOT NULL, 813 postcode VARCHAR(20) NOT NULL, 814 country VARCHAR(100) NOT NULL, 815 is_default BOOLEAN DEFAULT FALSE, 816 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 817 )`, 818 819 // Roles table (SERIAL ID) 820 `CREATE TABLE IF NOT EXISTS roles ( 821 role_id INTEGER PRIMARY KEY AUTOINCREMENT, 822 name VARCHAR(50) UNIQUE NOT NULL, 823 description TEXT, 824 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 825 )`, 826 827 // User_roles table (junction) 828 `CREATE TABLE IF NOT EXISTS user_roles ( 829 user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE, 830 role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE, 831 PRIMARY KEY (user_id, role_id) 832 )` 770 ); 771 772 CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id); 773 CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_ID); 774 CREATE INDEX IF NOT EXISTS idx_review_order ON review(order_num); 775 CREATE INDEX IF NOT EXISTS idx_request_date ON request(date_and_time); 776 CREATE INDEX IF NOT EXISTS idx_refund_order ON refund(order_num); 777 CREATE INDEX IF NOT EXISTS idx_personal_email ON personal(email); 778 CREATE INDEX IF NOT EXISTS idx_client_email ON client(email); 779 CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); 780 CREATE INDEX IF NOT EXISTS idx_users_username ON users(username); 781 CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id); 782 `; 783 784 await pool.query(schema); 785 786 const roles = [ 787 ['admin', 'System administrator'], 788 ['store_owner', 'Store owner'], 789 ['store_employee', 'Store employee'], 790 ['client', 'Registered client'], 791 ['guest', 'Unregistered guest'] 833 792 ]; 834 793 835 let index = 0; 836 837 function runNext() { 838 if (index >= createQueries.length) { 839 console.log('✅ All tables created'); 840 resolve(); 841 return; 842 } 843 844 const tableName = createQueries[index].split('TABLE')[1].split('(')[0].trim().replace('IF NOT EXISTS', '').trim(); 845 console.log(`Creating table: ${tableName}...`); 846 847 database.database.run(createQueries[index], [], (err) => { 848 if (err) { 849 console.error(`Error creating table: ${err.message}`); 850 reject(err); 851 return; 852 } 853 console.log(`✅ Created table: ${tableName}`); 854 index++; 855 runNext(); 856 }); 794 for (const [name, description] of roles) { 795 await pool.query( 796 'INSERT INTO roles(name, description) VALUES($1,$2) ON CONFLICT(name) DO NOTHING', 797 [name, description] 798 ); 857 799 } 858 800 859 runNext(); 860 }); 861 } 862 863 function createIndexes() { 864 return new Promise((resolve, reject) => { 865 console.log('📊 Creating indexes...'); 866 867 const indexQueries = [ 868 'CREATE INDEX IF NOT EXISTS idx_product_store ON product(store_id)', 869 'CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id)', 870 'CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_id)', 871 'CREATE INDEX IF NOT EXISTS idx_order_store ON "order"(store_id)', 872 'CREATE INDEX IF NOT EXISTS idx_order_date ON "order"(order_date)', 873 'CREATE INDEX IF NOT EXISTS idx_review_client ON review(client_id)', 874 'CREATE INDEX IF NOT EXISTS idx_review_product ON review(product_code)', 875 'CREATE INDEX IF NOT EXISTS idx_request_client ON request(client_id)', 876 'CREATE INDEX IF NOT EXISTS idx_request_store ON request(store_id)', 877 'CREATE INDEX IF NOT EXISTS idx_refund_order ON refund(order_num)', 878 'CREATE INDEX IF NOT EXISTS idx_refund_status ON refund(status)', 879 'CREATE INDEX IF NOT EXISTS idx_personal_email ON personal(email)', 880 'CREATE INDEX IF NOT EXISTS idx_client_email ON client(email)', 881 'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)', 882 'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)', 883 'CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id)', 884 'CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action)', 885 'CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at)', 886 'CREATE INDEX IF NOT EXISTS idx_delivery_client ON delivery_address(client_id)', 887 'CREATE INDEX IF NOT EXISTS idx_works_in_store_personal ON works_in_store(personal_id)', 888 'CREATE INDEX IF NOT EXISTS idx_works_in_store_store ON works_in_store(store_id)' 801 const hash = bcrypt.hashSync('Admin123!', 10); 802 await pool.query( 803 `INSERT INTO users(id, username, email, password, user_type, force_password_change) 804 VALUES('000000','admin','admin@handcraft.com',$1,'admin',TRUE) ON CONFLICT(id) DO NOTHING`, 805 [hash] 806 ); 807 await pool.query( 808 `INSERT INTO personal(id, first_name, last_name, ssn, email, password) 809 VALUES('000000','Admin','User','0000000000000','admin@handcraft.com',$1) ON CONFLICT(id) DO NOTHING`, 810 [hash] 811 ); 812 await pool.query(`INSERT INTO boss(boss_id) VALUES('000000') ON CONFLICT(boss_id) DO NOTHING`); 813 await pool.query( 814 `INSERT INTO permissions(personal_is,type,authorisation) 815 VALUES('000000','ADMIN','full_access') ON CONFLICT(personal_is) DO NOTHING` 816 ); 817 await pool.query( 818 `INSERT INTO user_roles(user_id,role_id) 819 SELECT '000000', role_id FROM roles WHERE name='admin' ON CONFLICT DO NOTHING` 820 ); 821 console.log('✅ PostgreSQL project schema was recreated successfully'); 822 }, 823 824 close() { 825 return pool.end(); 826 }, 827 828 getUserById(id, callback) { 829 dbQuery( 830 `SELECT u.*, 831 COALESCE(json_agg(json_build_object('name',r.name,'description',r.description)) 832 FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles 833 FROM users u 834 LEFT JOIN user_roles ur ON ur.user_id=u.id 835 LEFT JOIN roles r ON r.role_id=ur.role_id 836 WHERE u.id=$1 837 GROUP BY u.id`, 838 [String(id)], 839 (err, result) => callback(err, result?.rows?.[0]) 840 ); 841 }, 842 843 getUserByUsername(username, callback) { 844 dbQuery( 845 `SELECT u.*, 846 COALESCE(json_agg(json_build_object('name',r.name,'description',r.description)) 847 FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles 848 FROM users u 849 LEFT JOIN user_roles ur ON ur.user_id=u.id 850 LEFT JOIN roles r ON r.role_id=ur.role_id 851 WHERE u.username=$1 OR u.email=$1 852 GROUP BY u.id 853 LIMIT 1`, 854 [username], 855 (err, result) => callback(err, result?.rows?.[0]) 856 ); 857 }, 858 859 createUser(id, username, email, password, userType, callback) { 860 dbQuery( 861 `INSERT INTO users(id,username,email,password,user_type,force_password_change) 862 VALUES($1,$2,$3,$4,$5,FALSE) RETURNING id`, 863 [String(id), username, email, password, userType], 864 (err, result) => { 865 if (err) return callback(err); 866 const roleName = userType === 'client' ? 'client' : 867 userType === 'store_owner' ? 'store_owner' : 868 userType === 'store_employee' ? 'store_employee' : 'guest'; 869 dbQuery( 870 `INSERT INTO user_roles(user_id,role_id) 871 SELECT $1, role_id FROM roles WHERE name=$2`, 872 [String(id), roleName], 873 roleErr => callback(roleErr, String(id)) 874 ); 875 } 876 ); 877 }, 878 879 createClient(data, callback) { 880 dbQuery( 881 `INSERT INTO client(first_name,last_name,email,password) 882 VALUES($1,$2,$3,$4) RETURNING client_id`, 883 [data.firstName || data.first_name || '', data.lastName || data.last_name || '', data.email, data.password], 884 (err, result) => callback(err, result?.rows?.[0]?.client_id) 885 ); 886 }, 887 888 getClientByEmail(email, callback) { 889 dbQuery('SELECT * FROM client WHERE email=$1', [email], 890 (err, result) => callback(err, result?.rows?.[0])); 891 }, 892 893 getClientById(id, callback) { 894 dbQuery('SELECT * FROM client WHERE client_id=$1', [id], 895 (err, result) => callback(err, result?.rows?.[0])); 896 }, 897 898 getPersonalByEmail(email, callback) { 899 dbQuery('SELECT * FROM personal WHERE email=$1', [email], 900 (err, result) => callback(err, result?.rows?.[0])); 901 }, 902 903 getPersonalById(id, callback) { 904 dbQuery('SELECT * FROM personal WHERE id=$1', [String(id)], 905 (err, result) => callback(err, result?.rows?.[0])); 906 }, 907 908 verifyPassword(password, hash) { 909 try { return bcrypt.compareSync(password, hash); } catch { return false; } 910 }, 911 912 verifyClientPassword(password, hash, callback) { 913 bcrypt.compare(password, hash, callback); 914 }, 915 916 logAudit(userId, action, resourceType, resourceId, details, ipAddress) { 917 dbQuery( 918 `INSERT INTO audit_log(user_id,action,resource_type,resource_id,details,ip_address) 919 VALUES($1,$2,$3,$4,$5,$6)`, 920 [userId == null ? null : String(userId), action, resourceType, 921 resourceId == null ? null : String(resourceId), details, ipAddress], 922 () => {} 923 ); 924 }, 925 926 getProducts(categoryId, searchTerm, callback) { 927 const params = []; 928 const where = []; 929 if (categoryId) { 930 params.push(categoryId); 931 where.push(`p.category_id=$${params.length}`); 932 } 933 if (searchTerm) { 934 params.push(`%${searchTerm}%`); 935 where.push(`(p.description ILIKE $${params.length} OR p.code ILIKE $${params.length})`); 936 } 937 const sql = `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id 938 FROM product p 939 LEFT JOIN category c ON c.id=p.category_id 940 ${where.length ? 'WHERE ' + where.join(' AND ') : ''} 941 ORDER BY p.code`; 942 dbQuery(sql, params, (err, result) => callback(err, result?.rows || [])); 943 }, 944 945 getProductById(id, callback) { 946 dbQuery( 947 `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id 948 FROM product p 949 LEFT JOIN category c ON c.id=p.category_id 950 WHERE p.code=$1 LIMIT 1`, 951 [String(id)], 952 (err,result)=>callback(err,result?.rows?.[0]) 953 ); 954 }, 955 956 getProductByCode(code, callback) { 957 dbQuery( 958 `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id 959 FROM product p 960 LEFT JOIN category c ON c.id=p.category_id 961 WHERE p.code=$1`, 962 [code], 963 (err,result)=>callback(err,result?.rows?.[0]) 964 ); 965 }, 966 967 addProduct(personalId, data, callback) { 968 const storeId = data.store_id || data.storeId || String(data.code || '').slice(0,3); 969 if (!data.category_id) { 970 return callback(new Error('category_id is required because product.category_id is NOT NULL')); 971 } 972 dbQuery( 973 `INSERT INTO product(code,price,availability,weight,width_x_length_x_depth, 974 aprox_production_time,description,category_id) 975 VALUES($1,$2,$3,$4,$5,$6,$7,$8) RETURNING code`, 976 [ 977 data.code, data.price, data.availability ?? 0, data.weight, 978 data.width_x_length_x_depth || data.dimensions || '', 979 data.aprox_production_time ?? data.production_time ?? 0, 980 data.description, data.category_id 981 ], 982 (err,result)=>{ 983 if (err) return callback(err); 984 dbQuery( 985 `INSERT INTO sells(product_code,store_ID,discount) 986 VALUES($1,$2,$3) 987 ON CONFLICT(product_code,store_ID) 988 DO UPDATE SET discount=EXCLUDED.discount`, 989 [data.code,storeId,data.discount || 0], 990 e => callback(e, data.code) 991 ); 992 } 993 ); 994 }, 995 996 updateProduct(personalId, data, callback) { 997 const fields = []; 998 const params = []; 999 const allowed = [ 1000 ['price','price'], ['availability','availability'], ['weight','weight'], 1001 ['width_x_length_x_depth','width_x_length_x_depth'], 1002 ['dimensions','width_x_length_x_depth'], 1003 ['aprox_production_time','aprox_production_time'], 1004 ['production_time','aprox_production_time'], 1005 ['description','description'], ['category_id','category_id'] 889 1006 ]; 890 891 let index = 0; 892 893 function runNext() { 894 if (index >= indexQueries.length) { 895 console.log('✅ Indexes created'); 896 resolve(); 897 return; 898 } 899 900 database.database.run(indexQueries[index], [], (err) => { 901 if (err) { 902 console.log(`⚠️ Index creation warning for ${indexQueries[index].substring(0, 50)}...: ${err.message}`); 903 } 904 index++; 905 runNext(); 906 }); 1007 for (const [input,col] of allowed) { 1008 if (data[input] !== undefined) { 1009 params.push(data[input]); 1010 fields.push(`${col}=$${params.length}`); 1011 } 907 1012 } 908 909 runNext(); 910 }); 911 } 912 913 function insertInitialData() { 914 return new Promise((resolve, reject) => { 915 console.log('📝 Inserting initial data...'); 916 917 // Insert default roles 918 const roles = [ 919 { name: 'admin', description: 'System administrator' }, 920 { name: 'store_owner', description: 'Store owner' }, 921 { name: 'store_employee', description: 'Store employee' }, 922 { name: 'client', description: 'Registered client' }, 923 { name: 'guest', description: 'Unregistered guest' } 924 ]; 925 926 let rolesInserted = 0; 927 928 roles.forEach(role => { 929 database.database.run( 930 `INSERT INTO roles (name, description) 931 VALUES (?, ?) 932 ON CONFLICT DO NOTHING`, 933 [role.name, role.description], 934 (err) => { 935 if (err) { 936 console.error(`Error inserting role ${role.name}:`, err.message); 937 } 938 rolesInserted++; 939 940 if (rolesInserted === roles.length) { 941 console.log('✅ Roles inserted'); 942 // Create admin user with ID 000000 943 createAdminUser(); 944 945 // Ensure General category exists 946 database.ensureGeneralCategory((err) => { 947 if (err) { 948 console.error('Error ensuring General category:', err.message); 949 } else { 950 console.log('✅ General category checked/created'); 951 } 952 resolve(); 953 }); 954 } 955 } 956 ); 957 }); 958 }); 959 } 960 961 // Function to create admin user with ID 000000 962 function createAdminUser() { 963 const adminId = '000000'; 964 const adminPassword = bcrypt.hashSync('Admin123!', 10); 965 966 database.database.get( 967 'SELECT * FROM users WHERE id = ? OR username = ? OR email = ?', 968 [adminId, 'admin', 'admin@handcraft.com'], 969 (err, existingAdmin) => { 970 if (err) { 971 console.error('Error checking for existing admin:', err.message); 972 return; 973 } 974 975 if (!existingAdmin) { 976 // Start a transaction 977 database.database.run('BEGIN TRANSACTION', (err) => { 978 if (err) { 979 console.error('Error beginning transaction:', err); 980 return; 981 } 982 983 // Insert into users table 984 database.database.run( 985 `INSERT INTO users (id, username, email, password, user_type, force_password_change) 986 VALUES (?, ?, ?, ?, ?, ?)`, 987 [adminId, 'admin', 'admin@handcraft.com', adminPassword, 'admin', 1], 988 function(err) { 989 if (err) { 990 database.database.run('ROLLBACK'); 991 console.error('Error inserting admin user:', err.message); 992 return; 993 } 994 995 // Insert into personal table (required for boss table) 996 database.database.run( 997 `INSERT INTO personal (id, first_name, last_name, ssn, email, password) 998 VALUES (?, ?, ?, ?, ?, ?)`, 999 [adminId, 'Admin', 'User', '0000000000000', 'admin@handcraft.com', adminPassword], 1000 function(err) { 1001 if (err) { 1002 database.database.run('ROLLBACK'); 1003 console.error('Error inserting admin personal:', err.message); 1004 return; 1005 } 1006 1007 // Insert into boss table (store owner) 1008 database.database.run( 1009 `INSERT INTO boss (boss_id, signature) 1010 VALUES (?, ?)`, 1011 [adminId, 'Admin Signature'], 1012 function(err) { 1013 if (err) { 1014 database.database.run('ROLLBACK'); 1015 console.error('Error inserting admin boss:', err.message); 1016 return; 1017 } 1018 1019 // Insert into permissions 1020 database.database.run( 1021 `INSERT INTO permissions (personal_id, type, authorisation) 1022 VALUES (?, ?, ?)`, 1023 [adminId, 'ADMIN', 'full_access'], 1024 function(err) { 1025 if (err) { 1026 console.error('Error inserting admin permissions:', err.message); 1027 // Continue even if this fails 1028 } 1029 1030 // Assign admin role 1031 database.database.get( 1032 'SELECT role_id FROM roles WHERE name = ?', 1033 ['admin'], 1034 (err, adminRole) => { 1035 if (!err && adminRole) { 1036 database.database.run( 1037 'INSERT OR IGNORE INTO user_roles (user_id, role_id) VALUES (?, ?)', 1038 [adminId, adminRole.role_id], 1039 (err) => { 1040 if (err) { 1041 console.error('Error assigning admin role:', err.message); 1042 } 1043 } 1044 ); 1045 } 1046 1047 database.database.run('COMMIT', (commitErr) => { 1048 if (commitErr) { 1049 console.error('Error committing transaction:', commitErr); 1050 database.database.run('ROLLBACK'); 1051 } else { 1052 console.log('\n'); 1053 console.log('🔐 ===== ADMIN CREDENTIALS ====='); 1054 console.log('🆔 ID: 000000'); 1055 console.log('👤 Username: admin'); 1056 console.log('📧 Email: admin@handcraft.com'); 1057 console.log('🔑 Password: Admin123!'); 1058 console.log('⚠️ This is a first-time login. You will be required to change your password after 2FA verification.'); 1059 console.log('================================\n'); 1060 } 1061 }); 1062 } 1063 ); 1064 } 1065 ); 1066 } 1067 ); 1068 } 1013 if (!fields.length) return callback(null,0); 1014 params.push(data.code); 1015 dbQuery(`UPDATE product SET ${fields.join(', ')} WHERE code=$${params.length}`, params, 1016 (err,result)=>callback(err,result?.rowCount || 0)); 1017 }, 1018 1019 deleteProduct(productCode, storeId, personalId, callback) { 1020 dbQuery('DELETE FROM product WHERE code=$1 AND LEFT(code,3)=$2', [productCode,storeId], 1021 (err)=>callback(err)); 1022 }, 1023 1024 createCategory(data, callback) { 1025 const parent = data.parent_category_id ?? data.parentCategoryId ?? data.parent_id; 1026 if (parent === undefined || parent === null || parent === '') { 1027 return callback(new Error('parent_category_id is required by the project schema')); 1028 } 1029 dbQuery( 1030 `INSERT INTO category(name,parent_category_id) VALUES($1,$2) RETURNING id,name,parent_category_id`, 1031 [data.name, parent], 1032 (err,result)=>callback(err,result?.rows?.[0]) 1033 ); 1034 }, 1035 1036 getCategories(callback) { 1037 dbQuery('SELECT * FROM category ORDER BY name', [], (err,result)=>callback(err,result?.rows||[])); 1038 }, 1039 1040 getCategoriesWithParents(callback) { 1041 dbQuery( 1042 `SELECT c.*,p.name AS parent_name 1043 FROM category c LEFT JOIN category p ON p.id=c.parent_category_id 1044 ORDER BY c.name`, 1045 [], (err,result)=>callback(err,result?.rows||[]) 1046 ); 1047 }, 1048 1049 getStores(callback) { 1050 dbQuery('SELECT * FROM store ORDER BY name', [], (err,result)=>callback(err,result?.rows||[])); 1051 }, 1052 1053 createOrderNew(data, callback) { 1054 const items = data.items || data.products || data.order_items || []; 1055 const storeId = data.store_id || data.storeId || (items[0]?.code ? String(items[0].code).slice(0,3) : null); 1056 if (!storeId) return callback(new Error('Store ID is required')); 1057 const year = String(new Date().getFullYear()).slice(-3); 1058 1059 dbQuery( 1060 `SELECT COUNT(*)::int AS n 1061 FROM "order" 1062 WHERE LEFT(order_num,3)=$1 1063 AND SUBSTRING(order_num FROM 4 FOR 3)=$2`, 1064 [storeId, year], 1065 (countErr,countResult)=>{ 1066 if (countErr) return callback(countErr); 1067 const seq=Number(countResult.rows[0].n)+1; 1068 const orderNum=`${storeId}${year}${String(seq).padStart(5,'0')}`; 1069 dbQuery( 1070 `INSERT INTO "order"(order_num,client_ID,status,last_date_mod,payment_method,discount) 1071 VALUES($1,$2,$3,CURRENT_TIMESTAMP,$4,$5) RETURNING order_num`, 1072 [orderNum,data.client_id,data.status||'placed order',data.payment_method||'cash',data.discount||0], 1073 (err,result)=>{ 1074 if(err) return callback(err); 1075 let pending=items.length; 1076 if(!pending) return callback(null,orderNum); 1077 let firstErr=null; 1078 for(const item of items){ 1079 dbQuery( 1080 `INSERT INTO includes(order_num,product_code,quantity) VALUES($1,$2,$3)`, 1081 [orderNum,item.product_code||item.code,item.quantity||1], 1082 e=>{ if(e && !firstErr) firstErr=e; if(--pending===0) callback(firstErr,firstErr?undefined:orderNum); } 1069 1083 ); 1070 1084 } 1071 ); 1072 }); 1073 } else { 1074 console.log('✅ Admin user already exists with ID:', existingAdmin.id); 1075 } 1076 } 1077 ); 1078 } 1079 1080 // Initialize database on startup 1081 (async function() { 1085 } 1086 ); 1087 } 1088 ); 1089 }, 1090 1091 getOrdersByClient(clientId, callback) { 1092 dbQuery( 1093 `SELECT o.*, LEFT(o.order_num,3) AS store_id, 1094 o.last_date_mod AS order_date, 1095 COALESCE(json_agg(json_build_object('product_code',i.product_code,'quantity',i.quantity,'price',p.price)) 1096 FILTER(WHERE i.product_code IS NOT NULL),'[]') AS items 1097 FROM "order" o 1098 LEFT JOIN includes i ON i.order_num=o.order_num 1099 LEFT JOIN product p ON p.code=i.product_code 1100 WHERE o.client_ID=$1 1101 GROUP BY o.order_num 1102 ORDER BY o.last_date_mod DESC`, 1103 [clientId],(err,result)=>callback(err,result?.rows||[]) 1104 ); 1105 }, 1106 1107 createReviewNew(data, callback) { 1108 dbQuery( 1109 `INSERT INTO review(order_num,comment,rating,last_mod_date) 1110 VALUES($1,$2,$3,CURRENT_TIMESTAMP) 1111 RETURNING order_num`, 1112 [data.order_num,data.comment||null,data.rating], 1113 (err,result)=>callback(err,result?.rows?.[0]?.order_num) 1114 ); 1115 }, 1116 1117 createRequest(data, callback) { 1118 dbQuery( 1119 `INSERT INTO request(request_num,date_and_time,problem,notes_of_communication,customer_satisfaction) 1120 VALUES($1,$2,$3,$4,0) RETURNING request_num`, 1121 [data.request_num,data.date_and_time,data.problem,data.notes_of_communication||null], 1122 (err,result)=>{ 1123 if (err) return callback(err); 1124 dbQuery( 1125 `INSERT INTO for_store(request_num,store_ID) VALUES($1,$2)`, 1126 [data.request_num,data.store_id], 1127 storeErr=>{ 1128 if (storeErr) return callback(storeErr); 1129 if (data.order_num) { 1130 dbQuery( 1131 `INSERT INTO makes_request(client_ID,order_num) VALUES($1,$2)`, 1132 [data.client_id,data.order_num], 1133 e=>callback(e,data.request_num) 1134 ); 1135 } else { 1136 callback(null,data.request_num); 1137 } 1138 } 1139 ); 1140 } 1141 ); 1142 }, 1143 1144 createRefund(data, callback) { 1145 const suppliedId = data.refund_id; 1146 const query = suppliedId 1147 ? `INSERT INTO refund(refund_id,order_num,reason,amount,status) VALUES($1,$2,$3,$4,$5) RETURNING refund_id` 1148 : `INSERT INTO refund(order_num,reason,amount,status) VALUES($1,$2,$3,$4) RETURNING refund_id`; 1149 const params = suppliedId 1150 ? [Number.parseInt(String(suppliedId),10) || undefined,data.order_num,data.reason||null,data.amount,data.status||'requested refund'] 1151 : [data.order_num,data.reason||null,data.amount,data.status||'requested refund']; 1152 dbQuery(query,params,(err,result)=>callback(err,result?.rows?.[0]?.refund_id)); 1153 }, 1154 1155 getAllUsers(callback) { 1156 dbQuery(`SELECT u.*,COALESCE(json_agg(r.name) FILTER(WHERE r.role_id IS NOT NULL),'[]') roles 1157 FROM users u LEFT JOIN user_roles ur ON ur.user_id=u.id 1158 LEFT JOIN roles r ON r.role_id=ur.role_id GROUP BY u.id ORDER BY u.created_at DESC`, 1159 [],(err,result)=>callback(err,result?.rows||[])); 1160 }, 1161 1162 getAllOrders(callback) { 1163 dbQuery(`SELECT o.*, LEFT(o.order_num,3) AS store_id, o.last_date_mod AS order_date, 1164 c.first_name,c.last_name,c.email 1165 FROM "order" o 1166 LEFT JOIN client c ON c.client_id=o.client_ID 1167 ORDER BY o.last_date_mod DESC`, 1168 [],(err,result)=>callback(err,result?.rows||[])); 1169 }, 1170 1171 getStoreProducts(storeId, callback) { 1172 dbQuery(`SELECT p.*,c.name AS category_name,LEFT(p.code,3) AS store_id,s.discount 1173 FROM product p 1174 LEFT JOIN category c ON c.id=p.category_id 1175 LEFT JOIN sells s ON s.product_code=p.code AND s.store_ID=$1 1176 WHERE LEFT(p.code,3)=$1 OR EXISTS(SELECT 1 FROM sells sx WHERE sx.product_code=p.code AND sx.store_ID=$1) 1177 ORDER BY p.code`,[storeId],(err,result)=>callback(err,result?.rows||[])); 1178 }, 1179 1180 getStoreOrders(storeId, callback) { 1181 dbQuery(`SELECT o.*,LEFT(o.order_num,3) AS store_id,o.last_date_mod AS order_date,c.first_name,c.last_name 1182 FROM "order" o 1183 LEFT JOIN client c ON c.client_id=o.client_ID 1184 WHERE LEFT(o.order_num,3)=$1 ORDER BY o.last_date_mod DESC`,[storeId], 1185 (err,result)=>callback(err,result?.rows||[])); 1186 }, 1187 1188 getStoreEmployees(storeId, callback) { 1189 dbQuery(`SELECT p.*,e.date_of_hire,per.type,per.authorisation 1190 FROM personal p JOIN works_in_store w ON w.personal_id=p.id 1191 LEFT JOIN employees e ON e.employee_id=p.id 1192 LEFT JOIN permissions per ON per.personal_is=p.id 1193 WHERE w.store_ID=$1 ORDER BY p.last_name,p.first_name`, 1194 [storeId],(err,result)=>callback(err,result?.rows||[])); 1195 }, 1196 1197 getStoreReports(storeId, callback) { 1198 dbQuery(`SELECT * FROM report WHERE store_ID=$1 ORDER BY date DESC`,[storeId], 1199 (err,result)=>callback(err,result?.rows||[])); 1200 }, 1201 1202 getStoreStats(storeId, callback) { 1203 const sql=`SELECT 1204 (SELECT COUNT(*) FROM product WHERE LEFT(code,3)=$1)::int AS product_count, 1205 (SELECT COUNT(*) FROM "order" WHERE LEFT(order_num,3)=$1)::int AS order_count, 1206 (SELECT COALESCE(SUM(i.quantity*p.price*(1-o.discount/100)),0) 1207 FROM "order" o JOIN includes i ON i.order_num=o.order_num JOIN product p ON p.code=i.product_code 1208 WHERE LEFT(o.order_num,3)=$1) AS revenue, 1209 (SELECT COUNT(*) FROM works_in_store WHERE store_ID=$1)::int AS employee_count, 1210 (SELECT COUNT(*) FROM for_store WHERE store_ID=$1)::int AS request_count, 1211 (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE LEFT(o.order_num,3)=$1)::int AS refund_count`; 1212 dbQuery(sql,[storeId],(err,result)=>callback(err,result?.rows?.[0]||{})); 1213 }, 1214 1215 getEmployeeTasks(personalId, storeId, callback) { 1216 dbQuery(`SELECT r.*,a.personal_id AS answered_by 1217 FROM request r 1218 JOIN for_store fs ON fs.request_num=r.request_num 1219 LEFT JOIN answers a ON a.request_num=r.request_num 1220 WHERE fs.store_ID=$1 AND (a.personal_id=$2 OR a.personal_id IS NULL) 1221 ORDER BY r.date_and_time DESC`, 1222 [storeId,personalId],(err,result)=>callback(err,result?.rows||[])); 1223 }, 1224 1225 getClientStats(clientId, callback) { 1226 dbQuery(`SELECT 1227 (SELECT COUNT(*) FROM "order" WHERE client_ID=$1)::int AS order_count, 1228 (SELECT COUNT(*) FROM review r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS review_count, 1229 (SELECT COUNT(*) FROM makes_request WHERE client_ID=$1)::int AS request_count, 1230 (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS refund_count`, 1231 [clientId],(err,result)=>callback(err,result?.rows?.[0]||{})); 1232 } 1233 }; 1234 1235 1236 1237 // PostgreSQL schema initialization. 1238 // The schema is based on the supplied Pasted markdown.md. A few invalid PostgreSQL 1239 // declarations in the original paste are corrected here (for example DECIMMAL, 1240 // PARTIAL KEY, and composite-key column-name typos). The existing HTTP layer also 1241 // uses only the project schema plus the four authentication/audit support tables. 1242 (async () => { 1082 1243 try { 1083 await initializeDatabase();1244 await database.initializeDatabase(); 1084 1245 console.log('✅ Database initialization completed'); 1085 1246 } catch (err) { 1086 1247 console.error('❌ Database initialization failed:', err); 1248 process.exitCode = 1; 1087 1249 } 1088 1250 })(); … … 1158 1320 1159 1321 database.database.get( 1160 'SELECT boss_id FROM boss WHERE boss_id = ?',1322 'SELECT boss_id FROM boss WHERE boss_id = $1', 1161 1323 [personalId], 1162 1324 (err, boss) => { … … 1380 1542 1381 1543 database.database.get( 1382 'SELECT store_id FROM store WHERE store_email = ?',1544 'SELECT store_id FROM store WHERE store_email = $1', 1383 1545 [formData.storeEmail], 1384 1546 (err, existingStore) => { … … 1760 1922 // Insert into store table (store_id is VARCHAR) 1761 1923 database.database.run( 1762 'INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES ( ?, ?, ?, ?, ?, ?)',1924 'INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES ($1, $2, $3, $4, $5, $6)', 1763 1925 [ 1764 1926 tempStoreData.storeId, … … 1780 1942 // Insert into personal table (id is VARCHAR) 1781 1943 database.database.run( 1782 'INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES ( ?, ?, ?, ?, ?, ?)',1944 'INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES ($1, $2, $3, $4, $5, $6)', 1783 1945 [ 1784 1946 tempStoreData.personalId, … … 1809 1971 // Insert into boss table (boss_id is VARCHAR, references personal.id) 1810 1972 database.database.run( 1811 'INSERT INTO boss (boss_id , signature) VALUES (?, ?)',1812 [tempStoreData.personalId , tempStoreData.signature],1973 'INSERT INTO boss (boss_id) VALUES ($1)', 1974 [tempStoreData.personalId], 1813 1975 (err) => { 1814 1976 if (err) { … … 1822 1984 // Insert into works_in_store table (personal_id is VARCHAR, store_id is VARCHAR) 1823 1985 database.database.run( 1824 'INSERT INTO works_in_store (personal_id, store_id) VALUES ( ?, ?)',1986 'INSERT INTO works_in_store (personal_id, store_id) VALUES ($1, $2)', 1825 1987 [tempStoreData.personalId, tempStoreData.storeId], 1826 1988 (err) => { … … 1835 1997 // Insert into permissions table (personal_id is VARCHAR) 1836 1998 database.database.run( 1837 'INSERT INTO permissions (personal_id, type, authorisation) VALUES ( ?, ?, ?)',1999 'INSERT INTO permissions (personal_id, type, authorisation) VALUES ($1, $2, $3)', 1838 2000 [tempStoreData.personalId, 'BOSS', 'full_access'], 1839 2001 (err) => { … … 1844 2006 // Also create entry in users table for login with force_password_change = 1 1845 2007 database.database.run( 1846 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ( ?, ?, ?, ?, ?, ?)',2008 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ($1, $2, $3, $4, $5, $6)', 1847 2009 [ 1848 2010 tempStoreData.personalId, … … 1937 2099 if (tempUserData.address && tempUserData.city && tempUserData.postcode && tempUserData.country) { 1938 2100 database.database.run( 1939 'INSERT INTO delivery_address (client_id, address, city, postcode, country, is_default) VALUES ( ?, ?, ?, ?, ?, ?)',2101 'INSERT INTO delivery_address (client_id, address, city, postcode, country, is_default) VALUES ($1, $2, $3, $4, $5, $6)', 1940 2102 [ 1941 2103 clientId, … … 2158 2320 // Check if this is a boss (store owner) 2159 2321 database.database.get( 2160 'SELECT boss_id FROM boss WHERE boss_id = ?',2322 'SELECT boss_id FROM boss WHERE boss_id = $1', 2161 2323 [personal.id], 2162 2324 (err, boss) => { … … 2169 2331 // Check if first time login from users table 2170 2332 database.database.get( 2171 'SELECT force_password_change FROM users WHERE email = ?',2333 'SELECT force_password_change FROM users WHERE email = $1', 2172 2334 [email], 2173 2335 (err, user) => { … … 2219 2381 // Check if this is an employee 2220 2382 database.database.get( 2221 'SELECT employee_id FROM employees WHERE employee_id = ?',2383 'SELECT employee_id FROM employees WHERE employee_id = $1', 2222 2384 [personal.id], 2223 2385 (err, employee) => { … … 2229 2391 // This is an employee 2230 2392 database.database.get( 2231 'SELECT force_password_change FROM users WHERE email = ?',2393 'SELECT force_password_change FROM users WHERE email = $1', 2232 2394 [email], 2233 2395 (err, user) => { … … 2280 2442 // Treat as regular user 2281 2443 database.database.get( 2282 'SELECT * FROM users WHERE email = ?',2444 'SELECT * FROM users WHERE email = $1', 2283 2445 [email], 2284 2446 (err, user) => { … … 2422 2584 2423 2585 database.database.get( 2424 'SELECT * FROM users WHERE email = ?',2586 'SELECT * FROM users WHERE email = $1', 2425 2587 [email], 2426 2588 (err, user) => { … … 2673 2835 // Personal user (store owner/employee) 2674 2836 database.database.get( 2675 'SELECT boss_id FROM boss WHERE boss_id = ?',2837 'SELECT boss_id FROM boss WHERE boss_id = $1', 2676 2838 [userId], 2677 2839 (err, boss) => { … … 2774 2936 2775 2937 database.database.get( 2776 'SELECT boss_id FROM boss WHERE boss_id = ?',2938 'SELECT boss_id FROM boss WHERE boss_id = $1', 2777 2939 [personalId], 2778 2940 (err, boss) => { … … 2784 2946 database.database.all( 2785 2947 `SELECT s.* FROM store s 2786 JOIN works_in_store w ON s.store_id = w.store_id2787 WHERE w.personal_id = ?`,2948 JOIN works_in_store w ON s.store_id = w.store_id 2949 WHERE w.personal_id = $1`, 2788 2950 [personalId], 2789 2951 (err, stores) => { … … 2809 2971 } else { 2810 2972 database.database.get( 2811 'SELECT employee_id FROM employees WHERE employee_id = ?',2973 'SELECT employee_id FROM employees WHERE employee_id = $1', 2812 2974 [personalId], 2813 2975 (err, employee) => { … … 2819 2981 database.database.all( 2820 2982 `SELECT s.* FROM store s 2821 JOIN works_in_store w ON s.store_id = w.store_id2822 WHERE w.personal_id = ?`,2983 JOIN works_in_store w ON s.store_id = w.store_id 2984 WHERE w.personal_id = $1`, 2823 2985 [personalId], 2824 2986 (err, stores) => { … … 2944 3106 id: category.id, 2945 3107 name: category.name, 2946 parent_id: category.parent_ id,3108 parent_id: category.parent_category_id, 2947 3109 description: category.description 2948 3110 } … … 3010 3172 3011 3173 database.database.get( 3012 'SELECT COUNT(*) as order_count FROM "order" WHERE store_id = ? AND strftime("%Y", order_date) = ?',3174 'SELECT COUNT(*) AS order_count FROM "order" WHERE LEFT(order_num,3) = $1 AND SUBSTRING(order_num FROM 4 FOR 3)::INTEGER = ($2::INTEGER % 1000)', 3013 3175 [storeId, new Date().getFullYear().toString()], 3014 3176 (err, result) => { … … 3141 3303 3142 3304 database.database.get( 3143 'SELECT COUNT(*) as request_count FROM request WHERE store_id = ? AND strftime("%Y", date_and_time) = ? AND strftime("%m", date_and_time) = ?',3305 'SELECT COUNT(*)::int AS request_count FROM request r JOIN for_store fs ON fs.request_num=r.request_num WHERE fs.store_ID = $1 AND EXTRACT(YEAR FROM r.date_and_time)::int = $2::int AND EXTRACT(MONTH FROM r.date_and_time)::int = $3::int', 3144 3306 [storeId, now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')], 3145 3307 (err, result) => { … … 3199 3361 3200 3362 database.database.get( 3201 'SELECT store_id FROM "order" WHERE order_num = ?',3363 'SELECT LEFT(order_num,3) AS store_id FROM "order" WHERE order_num = $1', 3202 3364 [refundData.order_num], 3203 3365 (err, result) => { … … 3214 3376 3215 3377 database.database.get( 3216 'SELECT COUNT(*) as refund_count FROM refund WHERE strftime("%Y", request_date) = ? AND strftime("%m", request_date) = ?',3378 'SELECT COUNT(*)::int AS refund_count FROM refund WHERE SUBSTRING(refund_id::text FROM 4 FOR 2)::int = $2::int AND SUBSTRING(refund_id::text FROM 6 FOR 3)::int = ($1::int % 1000)', 3217 3379 [now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')], 3218 3380 (err, result) => { … … 3264 3426 3265 3427 database.database.get( 3266 'SELECT store_id FROM works_in_store WHERE personal_id = ?',3428 'SELECT store_id FROM works_in_store WHERE personal_id = $1', 3267 3429 [personalId], 3268 3430 (err, bossStore) => { … … 3282 3444 3283 3445 database.database.get( 3284 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',3446 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 3285 3447 [personalId, storeId], 3286 3448 (err, ownsStore) => { … … 3291 3453 } 3292 3454 3293 // FIXED: Changed SQL syntax from SUBSTRING(code FROM 4) to SUBSTR(code, 4) for SQLite compatibility3455 // PostgreSQL: extract the numeric product sequence from the store-prefixed product code 3294 3456 database.database.get( 3295 'SELECT MAX(CAST(SUBSTR (code, 4) AS INTEGER)) as max_product_num FROM product WHERE store_id = ?',3457 'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) AS max_product_num FROM product WHERE LEFT(code,3) = $1', 3296 3458 [storeId], 3297 3459 (err, result) => { … … 3362 3524 3363 3525 database.database.get( 3364 'SELECT store_id FROM product WHERE code = ?',3526 'SELECT LEFT(code,3) AS store_id FROM product WHERE code = $1', 3365 3527 [productData.code], 3366 3528 (err, product) => { … … 3372 3534 3373 3535 database.database.get( 3374 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',3536 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 3375 3537 [personalId, product.store_id], 3376 3538 (err, ownsStore) => { … … 3499 3661 3500 3662 database.database.run( 3501 'UPDATE users SET password = ?, force_password_change = 0 WHERE id = ?',3663 'UPDATE users SET password = $1, force_password_change = FALSE WHERE id = $2', 3502 3664 [hashedPassword, userId], 3503 3665 function(err) { … … 3511 3673 // Also update password in personal table if it exists (for admin) 3512 3674 database.database.run( 3513 'UPDATE personal SET password = ? WHERE id = ?',3675 'UPDATE personal SET password = $1 WHERE id = $2', 3514 3676 [hashedPassword, userId], 3515 3677 function(err) { … … 3585 3747 3586 3748 database.database.run( 3587 'UPDATE personal SET password = ? WHERE id = ?',3749 'UPDATE personal SET password = $1 WHERE id = $2', 3588 3750 [hashedPassword, userId], 3589 3751 function(err) { … … 3597 3759 // Also update in users table if exists 3598 3760 database.database.run( 3599 'UPDATE users SET password = ?, force_password_change = 0 WHERE email = ?',3761 'UPDATE users SET password = $1, force_password_change = FALSE WHERE email = $2', 3600 3762 [hashedPassword, personal.email], 3601 3763 function(err) { … … 3608 3770 // Determine user type (boss/owner or employee) 3609 3771 database.database.get( 3610 'SELECT boss_id FROM boss WHERE boss_id = ?',3772 'SELECT boss_id FROM boss WHERE boss_id = $1', 3611 3773 [userId], 3612 3774 (err, boss) => { … … 3684 3846 3685 3847 database.database.get( 3686 'SELECT boss_id FROM boss WHERE boss_id = ?',3848 'SELECT boss_id FROM boss WHERE boss_id = $1', 3687 3849 [personalId], 3688 3850 (err, boss) => { … … 3790 3952 3791 3953 database.database.run( 3792 'INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES ( ?, ?, ?, ?, ?, ?)',3954 'INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES ($1, $2, $3, $4, $5, $6)', 3793 3955 [ 3794 3956 newPersonalId, … … 3818 3980 3819 3981 database.database.run( 3820 'INSERT INTO employees (employee_id, date_of_hire) VALUES ( ?, ?)',3982 'INSERT INTO employees (employee_id, date_of_hire) VALUES ($1, $2)', 3821 3983 [newPersonalId, dateOfHire], 3822 3984 (err) => { … … 3830 3992 3831 3993 database.database.run( 3832 'INSERT INTO works_in_store (personal_id, store_id) VALUES ( ?, ?)',3994 'INSERT INTO works_in_store (personal_id, store_id) VALUES ($1, $2)', 3833 3995 [newPersonalId, storeId], 3834 3996 (err) => { … … 3842 4004 3843 4005 database.database.run( 3844 'INSERT INTO permissions (personal_id, type, authorisation) VALUES ( ?, ?, ?)',4006 'INSERT INTO permissions (personal_id, type, authorisation) VALUES ($1, $2, $3)', 3845 4007 [newPersonalId, 'EMPLOYEE', 'limited_access'], 3846 4008 (err) => { … … 3851 4013 // Also create entry in users table for login with force_password_change = 1 3852 4014 database.database.run( 3853 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ( ?, ?, ?, ?, ?, ?)',4015 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ($1, $2, $3, $4, $5, $6)', 3854 4016 [ 3855 4017 newPersonalId, … … 3924 4086 3925 4087 database.database.get( 3926 'SELECT boss_id FROM boss WHERE boss_id = ?',4088 'SELECT boss_id FROM boss WHERE boss_id = $1', 3927 4089 [personalId], 3928 4090 (err, boss) => { … … 3947 4109 3948 4110 database.database.get( 3949 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4111 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 3950 4112 [personalId, storeId], 3951 4113 (err, bossStore) => { … … 3957 4119 3958 4120 database.database.get( 3959 'SELECT personal_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4121 'SELECT personal_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 3960 4122 [employeeId, storeId], 3961 4123 (err, employeeStore) => { … … 3967 4129 3968 4130 database.database.get( 3969 'SELECT boss_id FROM boss WHERE boss_id = ?',4131 'SELECT boss_id FROM boss WHERE boss_id = $1', 3970 4132 [employeeId], 3971 4133 (err, isBoss) => { … … 3989 4151 3990 4152 database.database.run( 3991 'DELETE FROM works_in_store WHERE personal_id = ? AND store_id = ?',4153 'DELETE FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 3992 4154 [employeeId, storeId], 3993 4155 (err) => { … … 4001 4163 4002 4164 database.database.run( 4003 'DELETE FROM employees WHERE employee_id = ?',4165 'DELETE FROM employees WHERE employee_id = $1', 4004 4166 [employeeId], 4005 4167 (err) => { … … 4009 4171 4010 4172 database.database.run( 4011 'DELETE FROM permissions WHERE personal_id = ?',4173 'DELETE FROM permissions WHERE personal_id = $1', 4012 4174 [employeeId], 4013 4175 (err) => { … … 4017 4179 4018 4180 database.database.run( 4019 'DELETE FROM personal WHERE id = ?',4181 'DELETE FROM personal WHERE id = $1', 4020 4182 [employeeId], 4021 4183 (err) => { … … 4026 4188 // Also delete from users table 4027 4189 database.database.run( 4028 'DELETE FROM users WHERE id = ?',4190 'DELETE FROM users WHERE id = $1', 4029 4191 [employeeId], 4030 4192 (err) => { … … 4093 4255 4094 4256 database.database.get( 4095 'SELECT boss_id FROM boss WHERE boss_id = ?',4257 'SELECT boss_id FROM boss WHERE boss_id = $1', 4096 4258 [personalId], 4097 4259 (err, boss) => { … … 4116 4278 4117 4279 database.database.get( 4118 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4280 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4119 4281 [personalId, storeId], 4120 4282 (err, bossStore) => { … … 4126 4288 4127 4289 database.database.get( 4128 'SELECT personal_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4290 'SELECT personal_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4129 4291 [employeeId, storeId], 4130 4292 (err, employeeStore) => { … … 4150 4312 4151 4313 database.database.run( 4152 'UPDATE permissions SET type = ?, authorisation = ? WHERE personal_id = ?',4314 'UPDATE permissions SET type = $1, authorisation = $2 WHERE personal_id = $3', 4153 4315 [permissionType, authorization, employeeId], 4154 4316 function(err) { … … 4199 4361 4200 4362 database.database.get( 4201 'SELECT boss_id FROM boss WHERE boss_id = ?',4363 'SELECT boss_id FROM boss WHERE boss_id = $1', 4202 4364 [personalId], 4203 4365 (err, boss) => { … … 4222 4384 4223 4385 database.database.get( 4224 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4386 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4225 4387 [personalId, storeId], 4226 4388 (err, bossStore) => { … … 4232 4394 4233 4395 database.database.get( 4234 'SELECT personal_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4396 'SELECT personal_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4235 4397 [employeeId, storeId], 4236 4398 (err, employeeStore) => { … … 4245 4407 4246 4408 if (firstName) { 4247 updates.push( 'first_name = ?');4409 updates.push(`first_name = $${params.length + 1}`); 4248 4410 params.push(firstName); 4249 4411 } 4250 4412 4251 4413 if (lastName) { 4252 updates.push( 'last_name = ?');4414 updates.push(`last_name = $${params.length + 1}`); 4253 4415 params.push(lastName); 4254 4416 } … … 4260 4422 return; 4261 4423 } 4262 updates.push( 'email = ?');4424 updates.push(`email = $${params.length + 1}`); 4263 4425 params.push(email); 4264 4426 } … … 4273 4435 4274 4436 database.database.run( 4275 `UPDATE personal SET ${updates.join(', ')} WHERE id = ?`,4437 `UPDATE personal SET ${updates.join(', ')} WHERE id = $${params.length}`, 4276 4438 params, 4277 4439 function(err) { … … 4286 4448 if (email) { 4287 4449 database.database.run( 4288 'UPDATE users SET email = ? WHERE id = ?',4450 'UPDATE users SET email = $1 WHERE id = $2', 4289 4451 [email, employeeId], 4290 4452 (err) => { … … 4298 4460 if (firstName || lastName) { 4299 4461 database.database.get( 4300 'SELECT first_name, last_name FROM personal WHERE id = ?',4462 'SELECT first_name, last_name FROM personal WHERE id = $1', 4301 4463 [employeeId], 4302 4464 (err, personal) => { … … 4304 4466 const newUsername = `${personal.first_name} ${personal.last_name}`; 4305 4467 database.database.run( 4306 'UPDATE users SET username = ? WHERE id = ?',4468 'UPDATE users SET username = $1 WHERE id = $2', 4307 4469 [newUsername, employeeId], 4308 4470 (err) => { … … 4342 4504 if (!storeId) { 4343 4505 database.database.get( 4344 'SELECT store_id FROM works_in_store WHERE personal_id = ?LIMIT 1',4506 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1', 4345 4507 [personalId], 4346 4508 (err, store) => { … … 4367 4529 4368 4530 database.database.get( 4369 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4531 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4370 4532 [personalId, storeId], 4371 4533 (err, ownsStore) => { … … 4396 4558 if (!storeId) { 4397 4559 database.database.get( 4398 'SELECT store_id FROM works_in_store WHERE personal_id = ?LIMIT 1',4560 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1', 4399 4561 [personalId], 4400 4562 (err, store) => { … … 4421 4583 4422 4584 database.database.get( 4423 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4585 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4424 4586 [personalId, storeId], 4425 4587 (err, ownsStore) => { … … 4450 4612 if (!storeId) { 4451 4613 database.database.get( 4452 'SELECT store_id FROM works_in_store WHERE personal_id = ?LIMIT 1',4614 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1', 4453 4615 [personalId], 4454 4616 (err, store) => { … … 4475 4637 4476 4638 database.database.get( 4477 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4639 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4478 4640 [personalId, storeId], 4479 4641 (err, ownsStore) => { … … 4504 4666 if (!storeId) { 4505 4667 database.database.get( 4506 'SELECT store_id FROM works_in_store WHERE personal_id = ?LIMIT 1',4668 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1', 4507 4669 [personalId], 4508 4670 (err, store) => { … … 4529 4691 4530 4692 database.database.get( 4531 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4693 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4532 4694 [personalId, storeId], 4533 4695 (err, ownsStore) => { … … 4558 4720 if (!storeId) { 4559 4721 database.database.get( 4560 'SELECT store_id FROM works_in_store WHERE personal_id = ?LIMIT 1',4722 'SELECT store_id FROM works_in_store WHERE personal_id = $1 LIMIT 1', 4561 4723 [personalId], 4562 4724 (err, store) => { … … 4583 4745 4584 4746 database.database.get( 4585 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4747 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4586 4748 [personalId, storeId], 4587 4749 (err, ownsStore) => { … … 4684 4846 4685 4847 database.database.get( 4686 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4848 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4687 4849 [personalId, storeId], 4688 4850 (err, ownsStore) => { … … 4753 4915 4754 4916 database.database.get( 4755 'SELECT store_id FROM works_in_store WHERE personal_id = ? AND store_id = ?',4917 'SELECT store_id FROM works_in_store WHERE personal_id = $1 AND store_id = $2', 4756 4918 [personalId, storeId], 4757 4919 (err, ownsStore) => { … … 4765 4927 4766 4928 database.database.run( 4767 'INSERT INTO report ( id, store_id, period, start_date, end_date, type, generated_by, generated_at) VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)',4768 [ reportId, storeId, period, startDate, endDate, type, personalId],4929 'INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES (CURRENT_TIMESTAMP, $1, 0, $2, $3, $4)', 4930 [storeId, period, type, 'Not signed yet'], 4769 4931 function(err) { 4770 4932 if (err) {
Note:
See TracChangeset
for help on using the changeset viewer.
