+
+
×
+
Create New Category
+
@@ -408,4 +449,5 @@
document.addEventListener('DOMContentLoaded', async () => {
const user = await loadUserData();
+
if (!user || user.userType !== 'store_owner') {
window.location.href = 'index.html';
@@ -464,4 +506,21 @@
// Generate report
document.getElementById('generate-store-report').addEventListener('click', generateStoreReport);
+
+ // Add Category button functionality
+ document.getElementById('add-category-btn').addEventListener('click', () => {
+ openCategoryModal();
+ });
+
+ document.getElementById('edit-add-category-btn').addEventListener('click', () => {
+ openCategoryModal('edit');
+ });
+
+ // Category form submission
+ document.getElementById('add-category-form').addEventListener('submit', createCategory);
+
+ // Cancel button
+ document.getElementById('cancel-category-btn').addEventListener('click', () => {
+ document.getElementById('add-category-modal').style.display = 'none';
+ });
});
@@ -499,6 +558,6 @@
const response = await fetch(`/api/store-products?storeId=${storeId}`);
const data = await response.json();
-
const tbody = document.getElementById('products-list');
+
if (data.success && data.products.length > 0) {
tbody.innerHTML = data.products.map(product => `
@@ -528,6 +587,6 @@
const response = await fetch(`/api/store-orders?storeId=${storeId}`);
const data = await response.json();
-
const tbody = document.getElementById('orders-list');
+
if (data.success && data.orders.length > 0) {
tbody.innerHTML = data.orders.map(order => {
@@ -560,6 +619,6 @@
const response = await fetch(`/api/store-employees?storeId=${storeId}`);
const data = await response.json();
-
const tbody = document.getElementById('employees-list');
+
if (data.success && data.employees.length > 0) {
tbody.innerHTML = data.employees.map(emp => `
@@ -592,12 +651,75 @@
if (data.success) {
const options = data.categories.map(cat =>
+ `
`
+ ).join('');
+
+ document.getElementById('product-category').innerHTML = '
' + options;
+ document.getElementById('edit-product-category').innerHTML = '
' + options;
+
+ // Also load for category parent dropdown
+ const parentOptions = data.categories.map(cat =>
`
`
).join('');
-
- document.getElementById('product-category').innerHTML = '
' + options;
- document.getElementById('edit-product-category').innerHTML = '
' + options;
+ document.getElementById('category-parent').innerHTML = '
' + parentOptions;
}
} catch (error) {
console.error('Error loading categories:', error);
+ }
+ }
+
+ function openCategoryModal(source = 'add') {
+ // Store which form triggered this
+ document.getElementById('add-category-modal').dataset.source = source;
+ document.getElementById('add-category-modal').style.display = 'block';
+ document.getElementById('add-category-form').reset();
+ }
+
+ async function createCategory(e) {
+ e.preventDefault();
+
+ const categoryName = document.getElementById('category-name').value.trim();
+ const categoryDescription = document.getElementById('category-description').value.trim();
+ const parentId = document.getElementById('category-parent').value;
+
+ if (!categoryName) {
+ alert('Please enter a category name');
+ return;
+ }
+
+ const categoryData = {
+ name: categoryName,
+ description: categoryDescription,
+ parentId: parentId || null
+ };
+
+ try {
+ const response = await fetch('/api/create-category', {
+ method: 'POST',
+ headers: { 'Content-Type': 'application/json' },
+ body: JSON.stringify(categoryData)
+ });
+
+ const data = await response.json();
+
+ if (data.success) {
+ alert(`Category "${categoryName}" created successfully!`);
+ document.getElementById('add-category-modal').style.display = 'none';
+
+ // Reload categories in all dropdowns
+ await loadCategories();
+
+ // Auto-select the new category if it came from add product form
+ const source = document.getElementById('add-category-modal').dataset.source;
+ if (source === 'edit') {
+ document.getElementById('edit-product-category').value = data.category.id;
+ } else {
+ document.getElementById('product-category').value = data.category.id;
+ }
+ } else {
+ alert(data.message || 'Failed to create category');
+ }
+ } catch (error) {
+ console.error('Error creating category:', error);
+ alert('An error occurred while creating the category');
}
}
@@ -607,4 +729,5 @@
const storeId = document.getElementById('store-select').value;
+
if (!storeId) {
alert('Please select a store first');
@@ -679,4 +802,5 @@
const storeId = document.getElementById('store-select').value;
+
if (!storeId) {
alert('Please select a store first');
@@ -686,4 +810,5 @@
const password = document.getElementById('employee-password').value;
const passwordRegex = /^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])[A-Za-z\d@$!%*?&]{8,}$/;
+
if (!passwordRegex.test(password)) {
alert('Password must have at least 8 characters, including uppercase, lowercase, number and special character');
@@ -762,4 +887,5 @@
const storeId = document.getElementById('store-select').value;
+
const formData = {
code: document.getElementById('edit-product-code').value,
@@ -860,4 +986,5 @@
const storeId = document.getElementById('store-select').value;
+
const formData = {
employeeId: document.getElementById('edit-employee-id').value,
@@ -897,7 +1024,9 @@
let startDate, endDate;
+
if (period === 'custom') {
startDate = document.getElementById('report-start').value;
endDate = document.getElementById('report-end').value;
+
if (!startDate || !endDate) {
alert('Please select start and end dates');
Index: server.js
===================================================================
--- server.js (revision 69f2a41cff81a4c7825abb68eb0f0eff6818e4e5)
+++ server.js (revision 591278cabf928650250cb8d40aabda08753a31c5)
@@ -10,4 +10,5 @@
const port = process.env.PORT || 3000;
+
const sessions = new Map();
const verificationCodes = new Map();
@@ -31,4 +32,5 @@
}
};
+
emailTransporter = nodemailer.createTransport(emailConfig);
@@ -73,16 +75,16 @@
subject: 'Your Verification Code - Handcraft Marketplace',
html: `
-
-
🎨 Handcraft Marketplace
-
-
Account Verification
-
Your verification code is:
-
- ${code}
-
-
⚠️ This code will expire in 30 seconds
-
If you didn't request this verification, please ignore this email.
-
-
`
+
+
🎨 Handcraft Marketplace
+
+
Account Verification
+
Your verification code is:
+
+ ${code}
+
+
⚠️ This code will expire in 30 seconds
+
If you didn't request this verification, please ignore this email.
+
+
`
};
@@ -104,16 +106,16 @@
subject: 'Your 2FA Code - Handcraft Marketplace',
html: `
-
-
🎨 Handcraft Marketplace
-
-
Two-Factor Authentication
-
Your login verification code is:
-
- ${code}
-
-
⚠️ This code will expire in 30 seconds
-
If you're not trying to login, please secure your account immediately.
-
-
`
+
+
🎨 Handcraft Marketplace
+
+
Two-Factor Authentication
+
Your login verification code is:
+
+ ${code}
+
+
⚠️ This code will expire in 30 seconds
+
If you're not trying to login, please secure your account immediately.
+
+
`
};
@@ -135,17 +137,17 @@
subject: 'Store Registration Verification - Handcraft Marketplace',
html: `
-
-
🎨 Handcraft Marketplace
-
-
Store Registration Verification
-
Thank you for registering your store "${storeName}" on Handcraft Marketplace!
-
Your verification code is:
-
- ${code}
-
-
⚠️ This code will expire in 30 seconds
-
If you didn't request this verification, please ignore this email.
-
-
`
+
+
🎨 Handcraft Marketplace
+
+
Store Registration Verification
+
Thank you for registering your store "${storeName}" on Handcraft Marketplace!
+
Your verification code is:
+
+ ${code}
+
+
⚠️ This code will expire in 30 seconds
+
If you didn't request this verification, please ignore this email.
+
+
`
};
@@ -350,19 +352,17 @@
try {
- // Check if all tables exist
- const checkTablesQuery = `
- SELECT table_name
- FROM information_schema.tables
- WHERE table_schema = 'public'
- `;
-
+ // For SQLite, we need to use a different approach to check tables
const result = await new Promise((resolve, reject) => {
- database.database.all(checkTablesQuery, [], (err, rows) => {
- if (err) reject(err);
- else resolve(rows || []);
- });
- });
-
- const existingTables = result.map(row => row.table_name);
+ database.database.all(
+ "SELECT name FROM sqlite_master WHERE type='table'",
+ [],
+ (err, rows) => {
+ if (err) reject(err);
+ else resolve(rows || []);
+ }
+ );
+ });
+
+ const existingTables = result.map(row => row.name);
const missingTables = requiredTables.filter(table => !existingTables.includes(table));
@@ -409,26 +409,26 @@
// Drop in reverse order of creation (respect foreign keys)
const dropQueries = [
- 'DROP TABLE IF EXISTS user_roles CASCADE',
- 'DROP TABLE IF EXISTS roles CASCADE',
- 'DROP TABLE IF EXISTS delivery_address CASCADE',
- 'DROP TABLE IF EXISTS image CASCADE',
- 'DROP TABLE IF EXISTS color CASCADE',
- 'DROP TABLE IF EXISTS audit_log CASCADE',
- 'DROP TABLE IF EXISTS report CASCADE',
- 'DROP TABLE IF EXISTS refund CASCADE',
- 'DROP TABLE IF EXISTS request CASCADE',
- 'DROP TABLE IF EXISTS review CASCADE',
- 'DROP TABLE IF EXISTS order_items CASCADE',
- 'DROP TABLE IF EXISTS "order" CASCADE',
- 'DROP TABLE IF EXISTS permissions CASCADE',
- 'DROP TABLE IF EXISTS works_in_store CASCADE',
- 'DROP TABLE IF EXISTS employees CASCADE',
- 'DROP TABLE IF EXISTS boss CASCADE',
- 'DROP TABLE IF EXISTS product CASCADE',
- 'DROP TABLE IF EXISTS personal CASCADE',
- 'DROP TABLE IF EXISTS users CASCADE',
- 'DROP TABLE IF EXISTS category CASCADE',
- 'DROP TABLE IF EXISTS store CASCADE',
- 'DROP TABLE IF EXISTS client CASCADE'
+ 'DROP TABLE IF EXISTS user_roles',
+ 'DROP TABLE IF EXISTS roles',
+ 'DROP TABLE IF EXISTS delivery_address',
+ 'DROP TABLE IF EXISTS image',
+ 'DROP TABLE IF EXISTS color',
+ 'DROP TABLE IF EXISTS audit_log',
+ 'DROP TABLE IF EXISTS report',
+ 'DROP TABLE IF EXISTS refund',
+ 'DROP TABLE IF EXISTS request',
+ 'DROP TABLE IF EXISTS review',
+ 'DROP TABLE IF EXISTS order_items',
+ 'DROP TABLE IF EXISTS "order"',
+ 'DROP TABLE IF EXISTS permissions',
+ 'DROP TABLE IF EXISTS works_in_store',
+ 'DROP TABLE IF EXISTS employees',
+ 'DROP TABLE IF EXISTS boss',
+ 'DROP TABLE IF EXISTS product',
+ 'DROP TABLE IF EXISTS personal',
+ 'DROP TABLE IF EXISTS users',
+ 'DROP TABLE IF EXISTS category',
+ 'DROP TABLE IF EXISTS store',
+ 'DROP TABLE IF EXISTS client'
];
@@ -463,223 +463,223 @@
// Client table (SERIAL ID starting from 1000)
`CREATE TABLE IF NOT EXISTS client (
- client_id SERIAL PRIMARY KEY,
- first_name VARCHAR(100) NOT NULL,
- last_name VARCHAR(100) NOT NULL,
- email VARCHAR(255) UNIQUE NOT NULL,
- password VARCHAR(255) NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ client_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ first_name VARCHAR(100) NOT NULL,
+ last_name VARCHAR(100) NOT NULL,
+ email VARCHAR(255) UNIQUE NOT NULL,
+ password VARCHAR(255) NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Store table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS store (
- store_id VARCHAR(10) PRIMARY KEY,
- name VARCHAR(255) NOT NULL,
- date_of_founding DATE NOT NULL,
- physical_address TEXT NOT NULL,
- store_email VARCHAR(255) UNIQUE NOT NULL,
- rating DECIMAL(3,2) DEFAULT 0.0
- )`,
+ store_id VARCHAR(10) PRIMARY KEY,
+ name VARCHAR(255) NOT NULL,
+ date_of_founding DATE NOT NULL,
+ physical_address TEXT NOT NULL,
+ store_email VARCHAR(255) UNIQUE NOT NULL,
+ rating DECIMAL(3,2) DEFAULT 0.0
+ )`,
// Category table (SERIAL ID starting from 1)
`CREATE TABLE IF NOT EXISTS category (
- category_id SERIAL PRIMARY KEY,
- name VARCHAR(100) NOT NULL,
- description TEXT,
- parent_category_id INTEGER REFERENCES category(category_id) ON DELETE SET NULL
- )`,
+ category_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name VARCHAR(100) NOT NULL,
+ description TEXT,
+ parent_category_id INTEGER REFERENCES category(category_id) ON DELETE SET NULL
+ )`,
// Users table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS users (
- id VARCHAR(50) PRIMARY KEY,
- username VARCHAR(100) UNIQUE NOT NULL,
- email VARCHAR(255) UNIQUE NOT NULL,
- password VARCHAR(255) NOT NULL,
- user_type VARCHAR(50) NOT NULL,
- force_password_change INTEGER DEFAULT 0,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ id VARCHAR(50) PRIMARY KEY,
+ username VARCHAR(100) UNIQUE NOT NULL,
+ email VARCHAR(255) UNIQUE NOT NULL,
+ password VARCHAR(255) NOT NULL,
+ user_type VARCHAR(50) NOT NULL,
+ force_password_change INTEGER DEFAULT 0,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Personal table (VARCHAR ID - format: storeId(3) + '001' for owner, storeId(3) + employeeNum(3) for employees)
`CREATE TABLE IF NOT EXISTS personal (
- id VARCHAR(10) PRIMARY KEY,
- first_name VARCHAR(100) NOT NULL,
- last_name VARCHAR(100) NOT NULL,
- ssn VARCHAR(13) UNIQUE NOT NULL,
- email VARCHAR(255) UNIQUE NOT NULL,
- password VARCHAR(255) NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ id VARCHAR(10) PRIMARY KEY,
+ first_name VARCHAR(100) NOT NULL,
+ last_name VARCHAR(100) NOT NULL,
+ ssn VARCHAR(13) UNIQUE NOT NULL,
+ email VARCHAR(255) UNIQUE NOT NULL,
+ password VARCHAR(255) NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Product table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS product (
- id VARCHAR(50) PRIMARY KEY,
- code VARCHAR(20) UNIQUE NOT NULL,
- description TEXT NOT NULL,
- price DECIMAL(10,2) NOT NULL,
- availability INTEGER NOT NULL DEFAULT 0,
- weight DECIMAL(10,2),
- dimensions VARCHAR(50),
- production_time INTEGER,
- category_id INTEGER REFERENCES category(category_id) ON DELETE SET NULL,
- store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ id VARCHAR(50) PRIMARY KEY,
+ code VARCHAR(20) UNIQUE NOT NULL,
+ description TEXT NOT NULL,
+ price DECIMAL(10,2) NOT NULL,
+ availability INTEGER NOT NULL DEFAULT 0,
+ weight DECIMAL(10,2),
+ dimensions VARCHAR(50),
+ production_time INTEGER,
+ category_id INTEGER REFERENCES category(category_id) ON DELETE SET NULL,
+ store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Boss table (VARCHAR ID - references personal.id)
`CREATE TABLE IF NOT EXISTS boss (
- boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
- signature TEXT NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
+ signature TEXT NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Employees table (VARCHAR ID - references personal.id)
`CREATE TABLE IF NOT EXISTS employees (
- employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
- date_of_hire DATE NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
+ date_of_hire DATE NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Works_in_store table (junction)
`CREATE TABLE IF NOT EXISTS works_in_store (
- personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
- store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
- PRIMARY KEY (personal_id, store_id)
- )`,
+ personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
+ store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
+ PRIMARY KEY (personal_id, store_id)
+ )`,
// Permissions table
`CREATE TABLE IF NOT EXISTS permissions (
- permission_id SERIAL PRIMARY KEY,
- personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
- type VARCHAR(50) NOT NULL,
- authorisation TEXT,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ permission_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
+ type VARCHAR(50) NOT NULL,
+ authorisation TEXT,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Order table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS "order" (
- order_num VARCHAR(20) PRIMARY KEY,
- client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
- order_date TIMESTAMP NOT NULL,
- quantity INTEGER NOT NULL,
- payment_method VARCHAR(50) NOT NULL,
- discount DECIMAL(10,2) DEFAULT 0,
- delivery_address TEXT NOT NULL,
- store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE SET NULL,
- status VARCHAR(50) DEFAULT 'pending',
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ order_num VARCHAR(20) PRIMARY KEY,
+ client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
+ order_date TIMESTAMP NOT NULL,
+ quantity INTEGER NOT NULL,
+ payment_method VARCHAR(50) NOT NULL,
+ discount DECIMAL(10,2) DEFAULT 0,
+ delivery_address TEXT NOT NULL,
+ store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE SET NULL,
+ status VARCHAR(50) DEFAULT 'pending',
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Order_items table
`CREATE TABLE IF NOT EXISTS order_items (
- item_id SERIAL PRIMARY KEY,
- order_num VARCHAR(20) REFERENCES "order"(order_num) ON DELETE CASCADE,
- product_code VARCHAR(20) REFERENCES product(code) ON DELETE SET NULL,
- quantity INTEGER NOT NULL,
- price DECIMAL(10,2) NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ item_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ order_num VARCHAR(20) REFERENCES "order"(order_num) ON DELETE CASCADE,
+ product_code VARCHAR(20) REFERENCES product(code) ON DELETE SET NULL,
+ quantity INTEGER NOT NULL,
+ price DECIMAL(10,2) NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Review table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS review (
- review_id VARCHAR(20) PRIMARY KEY,
- client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
- product_code VARCHAR(20) REFERENCES product(code) ON DELETE CASCADE,
- rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
- comment TEXT,
- review_date TIMESTAMP NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ review_id VARCHAR(20) PRIMARY KEY,
+ client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
+ product_code VARCHAR(20) REFERENCES product(code) ON DELETE CASCADE,
+ rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
+ comment TEXT,
+ review_date TIMESTAMP NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Request table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS request (
- request_num VARCHAR(50) PRIMARY KEY,
- date_and_time TIMESTAMP NOT NULL,
- problem TEXT NOT NULL,
- client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
- store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
- status VARCHAR(50) DEFAULT 'pending',
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ request_num VARCHAR(50) PRIMARY KEY,
+ date_and_time TIMESTAMP NOT NULL,
+ problem TEXT NOT NULL,
+ client_id INTEGER REFERENCES client(client_id) ON DELETE SET NULL,
+ store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
+ status VARCHAR(50) DEFAULT 'pending',
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Refund table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS refund (
- refund_id VARCHAR(50) PRIMARY KEY,
- order_num VARCHAR(20) REFERENCES "order"(order_num) ON DELETE CASCADE,
- amount DECIMAL(10,2) NOT NULL,
- reason TEXT NOT NULL,
- status VARCHAR(50) DEFAULT 'pending',
- request_date TIMESTAMP NOT NULL,
- processed_date TIMESTAMP,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ refund_id VARCHAR(50) PRIMARY KEY,
+ order_num VARCHAR(20) REFERENCES "order"(order_num) ON DELETE CASCADE,
+ amount DECIMAL(10,2) NOT NULL,
+ reason TEXT NOT NULL,
+ status VARCHAR(50) DEFAULT 'pending',
+ request_date TIMESTAMP NOT NULL,
+ processed_date TIMESTAMP,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Report table (VARCHAR ID)
`CREATE TABLE IF NOT EXISTS report (
- id VARCHAR(50) PRIMARY KEY,
- store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
- period VARCHAR(50) NOT NULL,
- start_date DATE NOT NULL,
- end_date DATE NOT NULL,
- type VARCHAR(50) NOT NULL,
- generated_by VARCHAR(10) REFERENCES personal(id) ON DELETE SET NULL,
- generated_at TIMESTAMP NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ id VARCHAR(50) PRIMARY KEY,
+ store_id VARCHAR(10) REFERENCES store(store_id) ON DELETE CASCADE,
+ period VARCHAR(50) NOT NULL,
+ start_date DATE NOT NULL,
+ end_date DATE NOT NULL,
+ type VARCHAR(50) NOT NULL,
+ generated_by VARCHAR(10) REFERENCES personal(id) ON DELETE SET NULL,
+ generated_at TIMESTAMP NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Audit_log table (SERIAL ID)
`CREATE TABLE IF NOT EXISTS audit_log (
- log_id SERIAL PRIMARY KEY,
- user_id VARCHAR(50),
- action VARCHAR(100) NOT NULL,
- resource_type VARCHAR(50),
- resource_id VARCHAR(50),
- details TEXT,
- ip_address VARCHAR(45),
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ log_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ user_id VARCHAR(50),
+ action VARCHAR(100) NOT NULL,
+ resource_type VARCHAR(50),
+ resource_id VARCHAR(50),
+ details TEXT,
+ ip_address VARCHAR(45),
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Color table (SERIAL ID)
`CREATE TABLE IF NOT EXISTS color (
- color_id SERIAL PRIMARY KEY,
- name VARCHAR(50) NOT NULL,
- hex_code VARCHAR(7) NOT NULL,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ color_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name VARCHAR(50) NOT NULL,
+ hex_code VARCHAR(7) NOT NULL,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Image table (SERIAL ID)
`CREATE TABLE IF NOT EXISTS image (
- image_id SERIAL PRIMARY KEY,
- product_code VARCHAR(20) REFERENCES product(code) ON DELETE CASCADE,
- image_url TEXT NOT NULL,
- is_primary BOOLEAN DEFAULT FALSE,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ image_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ product_code VARCHAR(20) REFERENCES product(code) ON DELETE CASCADE,
+ image_url TEXT NOT NULL,
+ is_primary BOOLEAN DEFAULT FALSE,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Delivery_address table (SERIAL ID)
`CREATE TABLE IF NOT EXISTS delivery_address (
- address_id SERIAL PRIMARY KEY,
- client_id INTEGER REFERENCES client(client_id) ON DELETE CASCADE,
- address TEXT NOT NULL,
- city VARCHAR(100) NOT NULL,
- postcode VARCHAR(20) NOT NULL,
- country VARCHAR(100) NOT NULL,
- is_default BOOLEAN DEFAULT FALSE,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ address_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ client_id INTEGER REFERENCES client(client_id) ON DELETE CASCADE,
+ address TEXT NOT NULL,
+ city VARCHAR(100) NOT NULL,
+ postcode VARCHAR(20) NOT NULL,
+ country VARCHAR(100) NOT NULL,
+ is_default BOOLEAN DEFAULT FALSE,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// Roles table (SERIAL ID)
`CREATE TABLE IF NOT EXISTS roles (
- role_id SERIAL PRIMARY KEY,
- name VARCHAR(50) UNIQUE NOT NULL,
- description TEXT,
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- )`,
+ role_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name VARCHAR(50) UNIQUE NOT NULL,
+ description TEXT,
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+ )`,
// User_roles table (junction)
`CREATE TABLE IF NOT EXISTS user_roles (
- user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE,
- role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE,
- PRIMARY KEY (user_id, role_id)
- )`
+ user_id VARCHAR(50) REFERENCES users(id) ON DELETE CASCADE,
+ role_id INTEGER REFERENCES roles(role_id) ON DELETE CASCADE,
+ PRIMARY KEY (user_id, role_id)
+ )`
];
@@ -766,16 +766,5 @@
console.log('📝 Inserting initial data...');
- // Insert General category (ID will be 1 due to SERIAL)
- database.database.run(
- `INSERT INTO category (name, description)
- VALUES ('General', 'General products category')
- ON CONFLICT DO NOTHING`,
- [],
- (err) => {
- if (err) {
- console.error('Error inserting General category:', err.message);
- }
- }
- );
+ // REMOVED: Category insertion - now handled by database.ensureGeneralCategory()
// Insert admin user
@@ -785,6 +774,6 @@
database.database.run(
`INSERT INTO users (id, username, email, password, user_type, force_password_change)
- VALUES ($1, $2, $3, $4, $5, $6)
- ON CONFLICT DO NOTHING`,
+ VALUES ($1, $2, $3, $4, $5, $6)
+ ON CONFLICT DO NOTHING`,
[adminId, 'admin', 'admin@handcraft.com', adminPassword, 'admin', 1],
(err) => {
@@ -811,6 +800,6 @@
database.database.run(
`INSERT INTO roles (name, description)
- VALUES ($1, $2)
- ON CONFLICT DO NOTHING`,
+ VALUES ($1, $2)
+ ON CONFLICT DO NOTHING`,
[role.name, role.description],
(err) => {
@@ -822,10 +811,10 @@
console.log('✅ Roles inserted');
- // Check if General category exists
+ // Ensure General category exists
database.ensureGeneralCategory((err) => {
if (err) {
console.error('Error ensuring General category:', err.message);
} else {
- console.log('✅ General category exists');
+ console.log('✅ General category checked/created');
}
resolve();
@@ -925,4 +914,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { username, email, password, userType, firstName, lastName } = JSON.parse(body);
@@ -989,4 +979,5 @@
console.log('✅ Verification email sent to:', email);
database.logAudit(null, 'REGISTER_ATTEMPT', 'user', null, `Registration attempt for ${email} as ${userType}`, ipAddress);
+
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
@@ -1016,4 +1007,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const formData = JSON.parse(body);
@@ -1182,4 +1174,5 @@
console.log('✅ Store registration email sent to:', formData.ownerEmail);
database.logAudit(null, 'STORE_REGISTER_ATTEMPT', 'store', null, `Store registration attempt: ${formData.storeName}`, ipAddress);
+
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
@@ -1214,4 +1207,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { firstName, lastName, email, password, address, city, postcode, country, isDefaultAddress } = JSON.parse(body);
@@ -1278,4 +1272,5 @@
console.log('✅ Verification email sent to:', email);
database.logAudit(null, 'CLIENT_REGISTER_ATTEMPT', 'client', null, `Client registration attempt for ${email}`, ipAddress);
+
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
@@ -1304,4 +1299,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { email } = JSON.parse(body);
@@ -1438,4 +1434,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { email, code } = JSON.parse(body);
@@ -1668,4 +1665,5 @@
} else {
const userId = 'user_' + Date.now().toString().slice(-8);
+
database.createUser(userId, tempUserData.username, tempUserData.email, tempUserData.password, tempUserData.userType, (err, userId) => {
if (err) {
@@ -1698,6 +1696,8 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { email, password } = JSON.parse(body);
+
console.log(`🔍 Login attempt for email: ${email}`);
@@ -1710,4 +1710,5 @@
if (client) {
console.log(`🔍 Found client: ${client.email}`);
+
if (!client.password) {
console.log('❌ Client has no password set');
@@ -1732,7 +1733,9 @@
const sessionId = generateSessionId();
const clientId = client.client_ID;
+
sessions.set(sessionId, `client_${clientId}`);
console.log(`✅ Client login successful. Session: ${sessionId}, User: client_${clientId}`);
+
database.logAudit(clientId, 'LOGIN_SUCCESS', 'auth',
typeof clientId === 'string' ? clientId : String(clientId),
@@ -1743,5 +1746,4 @@
'Set-Cookie': `sessionId=${sessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict`
});
-
res.end(JSON.stringify({
success: true,
@@ -1768,4 +1770,5 @@
if (personal) {
console.log(`🔍 Found personal user: ${personal.email}`);
+
if (!personal.password) {
console.log('❌ Personal has no password set');
@@ -1803,4 +1806,5 @@
const twoFACode = generateVerificationCode();
+
verificationCodes.set(personal.email, {
code: twoFACode,
@@ -1862,4 +1866,5 @@
const twoFACode = generateVerificationCode();
+
verificationCodes.set(personal.email, {
code: twoFACode,
@@ -1923,4 +1928,5 @@
const twoFACode = generateVerificationCode();
+
verificationCodes.set(userByUsername.email, {
code: twoFACode,
@@ -1973,4 +1979,5 @@
const twoFACode = generateVerificationCode();
+
verificationCodes.set(user.email, {
code: twoFACode,
@@ -2038,4 +2045,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { email } = JSON.parse(body);
@@ -2060,4 +2068,5 @@
const newTwoFACode = generateVerificationCode();
+
verificationCodes.set(userByUsername.email, {
code: newTwoFACode,
@@ -2095,4 +2104,5 @@
const newTwoFACode = generateVerificationCode();
+
verificationCodes.set(user.email, {
code: newTwoFACode,
@@ -2135,4 +2145,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { email, code } = JSON.parse(body);
@@ -2173,5 +2184,4 @@
'Set-Cookie': `sessionId=${tempSessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict`
});
-
res.end(JSON.stringify({
success: true,
@@ -2203,4 +2213,5 @@
// Determine redirect based on user type
let redirectTo = '';
+
switch(verificationData.userType) {
case 'client':
@@ -2226,5 +2237,4 @@
'Set-Cookie': `sessionId=${sessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict`
});
-
res.end(JSON.stringify({
success: true,
@@ -2279,4 +2289,5 @@
if (userIdStr.startsWith('client_')) {
const clientId = parseInt(userIdStr.replace('client_', ''));
+
database.getClientById(clientId, (err, client) => {
if (err || !client) {
@@ -2298,6 +2309,8 @@
});
}
+
else if (userIdStr.startsWith('personal_')) {
const personalId = userIdStr.replace('personal_', '');
+
database.getPersonalById(personalId, (err, personal) => {
if (err || !personal) {
@@ -2318,6 +2331,6 @@
database.database.all(
`SELECT s.* FROM store s
- JOIN works_in_store w ON s.store_id = w.store_id
- WHERE w.personal_id = $1`,
+ JOIN works_in_store w ON s.store_id = w.store_id
+ WHERE w.personal_id = $1`,
[personalId],
(err, stores) => {
@@ -2353,6 +2366,6 @@
database.database.all(
`SELECT s.* FROM store s
- JOIN works_in_store w ON s.store_id = w.store_id
- WHERE w.personal_id = $1`,
+ JOIN works_in_store w ON s.store_id = w.store_id
+ WHERE w.personal_id = $1`,
[personalId],
(err, stores) => {
@@ -2445,4 +2458,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const categoryData = JSON.parse(body);
@@ -2470,4 +2484,5 @@
} else {
database.logAudit(personalId, 'CATEGORY_CREATED', 'category', category.id.toString(), `New category created: ${category.name}`, ipAddress);
+
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
@@ -2526,7 +2541,7 @@
body += chunk.toString();
});
+
req.on('end', () => {
const orderData = JSON.parse(body);
-
const userIdStr = String(userId);
@@ -2601,4 +2616,5 @@
if (userIdStr.startsWith('client_')) {
const clientId = parseInt(userIdStr.replace('client_', ''));
+
database.getOrdersByClient(clientId, (err, orders) => {
if (err) {
@@ -2623,11 +2639,12 @@
body += chunk.toString();
});
+
req.on('end', () => {
const reviewData = JSON.parse(body);
-
const userIdStr = String(userId);
if (userIdStr.startsWith('client_')) {
const clientId = parseInt(userIdStr.replace('client_', ''));
+
reviewData.client_id = clientId;
@@ -2656,7 +2673,7 @@
body += chunk.toString();
});
+
req.on('end', () => {
const requestData = JSON.parse(body);
-
const userIdStr = String(userId);
@@ -2726,7 +2743,7 @@
body += chunk.toString();
});
+
req.on('end', () => {
const refundData = JSON.parse(body);
-
const userIdStr = String(userId);
@@ -2745,5 +2762,4 @@
const storeId = result[0].store_id;
-
const now = new Date();
const month = (now.getMonth() + 1).toString().padStart(2, '0');
@@ -2797,4 +2813,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const productData = JSON.parse(body);
@@ -2828,6 +2845,7 @@
}
+ // FIXED: Changed SQL syntax from SUBSTRING(code FROM 4) to SUBSTR(code, 4) for SQLite compatibility
database.database.get(
- 'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) as max_product_num FROM product WHERE store_id = $1',
+ 'SELECT MAX(CAST(SUBSTR(code, 4) AS INTEGER)) as max_product_num FROM product WHERE store_id = $1',
[storeId],
(err, result) => {
@@ -2863,4 +2881,5 @@
} else {
database.logAudit(personalId, 'PRODUCT_ADDED', 'product', productId.toString(), 'New product added', ipAddress);
+
res.writeHead(200, { 'Content-Type': 'application/json' });
res.end(JSON.stringify({
@@ -2888,4 +2907,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const productData = JSON.parse(body);
@@ -2996,4 +3016,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { currentPassword, newPassword, confirmPassword } = JSON.parse(body);
@@ -3089,4 +3110,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { firstName, lastName, ssn, email, password, storeId, dateOfHire } = JSON.parse(body);
@@ -3300,4 +3322,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { employeeId, storeId } = JSON.parse(body);
@@ -3451,4 +3474,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { employeeId, storeId, status } = JSON.parse(body);
@@ -3550,4 +3574,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { employeeId, storeId, firstName, lastName, email } = JSON.parse(body);
@@ -3966,4 +3991,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { productCode, storeId } = JSON.parse(body);
@@ -4035,4 +4061,5 @@
body += chunk.toString();
});
+
req.on('end', () => {
const { storeId, period, startDate, endDate, type } = JSON.parse(body);