Index: server.js =================================================================== --- server.js (revision 2d1ec46933bbbd721e985423c96581bc0a7485ed) +++ server.js (revision 69f2a41cff81a4c7825abb68eb0f0eff6818e4e5) @@ -1,5 +1,5 @@ const http = require('http'); const url = require('url'); -const { Pool } = require('pg'); +const database = require('./database.js'); const fs = require('fs'); const path = require('path'); @@ -7,9 +7,7 @@ const nodemailer = require('nodemailer'); const bcrypt = require('bcryptjs'); -const { AsyncLocalStorage } = require('async_hooks'); require('dotenv').config(); const port = process.env.PORT || 3000; - const sessions = new Map(); const verificationCodes = new Map(); @@ -26,9 +24,5 @@ const emailConfig = { host: process.env.SMTP_HOST || 'smtp.gmail.com', - port: (() => { - const configuredPort = parseInt(process.env.SMTP_PORT, 10); - if (process.env.SMTP_HOST === 'smtp.ethereal.email' && configuredPort === 3000) return 587; - return configuredPort || 587; - })(), + port: parseInt(process.env.SMTP_PORT) || 587, secure: false, auth: { @@ -37,5 +31,4 @@ } }; - emailTransporter = nodemailer.createTransport(emailConfig); @@ -60,5 +53,4 @@ const codeMatch = mailOptions.html.match(/\b\d{6}\b/); const code = codeMatch ? codeMatch[0] : 'unknown'; - console.log(''); console.log('๐ŸŽฏ ===== VERIFICATION CODE ====='); @@ -69,5 +61,4 @@ console.log('================================'); console.log(''); - resolve({ messageId: 'dev-' + Date.now() }); }); @@ -174,5 +165,5 @@ let code = ''; for(let i = 0; i < 6; i++) { - code += crypto.randomInt(0, 10); + code += crypto.randomInt(0, 9); } return code; @@ -309,1630 +300,335 @@ requireAuth(req, res, (userId) => { const userIdStr = String(userId); - - // Check if this is the admin user (ID 000000) - if (userIdStr === '000000') { - // Admin is not a store owner - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' })); - return; - } - - // Check if it's a personal user - if (userIdStr.startsWith('personal_')) { - const personalId = userIdStr.replace('personal_', ''); - - database.database.get( - 'SELECT boss_id FROM boss WHERE boss_id = $1', - [personalId], - (err, boss) => { - if (err || !boss) { - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' })); - return; - } - - callback(personalId); + const personalId = userIdStr.replace('personal_', ''); + + database.database.get( + 'SELECT boss_id FROM boss WHERE boss_id = $1', + [personalId], + (err, boss) => { + if (err || !boss) { + res.writeHead(403, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' })); + return; } - ); - } else { - // Not a personal user, so not a store owner - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Access denied - not a store owner' })); - } + + callback(personalId); + } + ); }); }; } - - -const pool = new Pool({ - connectionString: process.env.DATABASE_URL, - host: process.env.PGHOST || process.env.DB_HOST || 'localhost', - port: Number(process.env.PGPORT || process.env.DB_PORT || 5432), - user: process.env.PGUSER || process.env.DB_USER || 'postgres', - password: process.env.PGPASSWORD || process.env.DB_PASSWORD || '', - database: process.env.PGDATABASE || process.env.DB_NAME || 'handcraft_marketplace', - max: Number(process.env.PG_POOL_MAX || 10), - idleTimeoutMillis: 30000 -}); - -const transactionStorage = new AsyncLocalStorage(); - -function dbQuery(sql, params = [], callback) { - const client = transactionStorage.getStore() || pool; - - client.query(sql, params) - .then(result => callback(null, result)) - .catch(err => callback(err)); +// Database initialization function +async function initializeDatabase() { + console.log('๐Ÿ” Checking database schema...'); + + // List of all required tables + const requiredTables = [ + 'client', + 'store', + 'category', + 'users', + 'personal', + 'product', + 'boss', + 'employees', + 'works_in_store', + 'permissions', + 'order', + 'order_items', + 'review', + 'request', + 'refund', + 'report', + 'audit_log', + 'color', + 'image', + 'delivery_address', + 'roles', + 'user_roles' + ]; + + try { + // Check if all tables exist + const checkTablesQuery = ` + SELECT table_name + FROM information_schema.tables + WHERE table_schema = 'public' + `; + + 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); + const missingTables = requiredTables.filter(table => !existingTables.includes(table)); + + if (missingTables.length > 0) { + console.log(`โš ๏ธ Missing tables: ${missingTables.join(', ')}`); + console.log('๐Ÿ”„ Recreating entire database...'); + + // Drop all tables in correct order (respecting foreign keys) + await dropAllTables(); + + // Create all tables + await createAllTables(); + + // Create indexes + await createIndexes(); + + // Insert initial data + await insertInitialData(); + + console.log('โœ… Database recreation completed'); + } else { + console.log('โœ… All required tables exist'); + } + } catch (err) { + console.error('โŒ Error checking database schema:', err); + console.log('โš ๏ธ Attempting to recreate database anyway...'); + + try { + await dropAllTables(); + await createAllTables(); + await createIndexes(); + await insertInitialData(); + console.log('โœ… Database recreation completed'); + } catch (createErr) { + console.error('โŒ Failed to recreate database:', createErr); + } + } } -const REPORT_FUNCTIONS_SQL = String.raw` --- ============================================================ --- HANDCRAFT MARKETPLACE REPORT FUNCTIONS --- PostgreSQL / exact project schema --- ============================================================ - -CREATE OR REPLACE FUNCTION get_orders_by_total() -RETURNS TABLE ( - order_num VARCHAR(11), - client_id INTEGER, - client_name TEXT, - order_quantity BIGINT, - order_status VARCHAR(20), - payment_method VARCHAR(250), - discount NUMERIC, - order_total NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - o.order_num, - o.client_id, - CONCAT_WS(' ', c.first_name, c.last_name) AS client_name, - COALESCE(SUM(i.quantity), 0)::BIGINT AS order_quantity, - o.status, - o.payment_method, - COALESCE(o.discount, 0)::NUMERIC AS discount, - ROUND( - COALESCE(SUM(p.price * i.quantity), 0) - * (1 - COALESCE(o.discount, 0) / 100.0), - 2 - ) AS order_total - FROM "order" o - LEFT JOIN client c ON c.client_id = o.client_id - LEFT JOIN includes i ON i.order_num = o.order_num - LEFT JOIN product p ON p.code = i.product_code - GROUP BY - o.order_num, o.client_id, c.first_name, c.last_name, - o.status, o.payment_method, o.discount - ORDER BY 8 DESC, o.order_num; -$$; - -CREATE OR REPLACE FUNCTION get_products_by_total_sales() -RETURNS TABLE ( - product_code VARCHAR(8), - product_description VARCHAR(500), - product_price NUMERIC, - number_of_orders BIGINT, - total_quantity_sold BIGINT, - total_revenue NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - p.code, - p.description, - p.price::NUMERIC, - COUNT(DISTINCT i.order_num) AS number_of_orders, - COALESCE(SUM(i.quantity), 0)::BIGINT AS total_quantity_sold, - ROUND( - COALESCE( - SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ), - 0 - ), - 2 - ) AS total_revenue - FROM product p - LEFT JOIN includes i ON i.product_code = p.code - LEFT JOIN "order" o ON o.order_num = i.order_num - GROUP BY p.code, p.description, p.price - ORDER BY 4 DESC, 5 DESC, 6 DESC; -$$; - -CREATE OR REPLACE FUNCTION get_low_stock_high_demand_products( - p_stock_threshold INTEGER, - p_demand_threshold INTEGER -) -RETURNS TABLE ( - product_code VARCHAR(8), - product_description VARCHAR(500), - current_stock INTEGER, - number_of_orders BIGINT, - total_quantity_sold BIGINT -) -LANGUAGE sql -AS $$ - SELECT - p.code, - p.description, - p.availability, - COUNT(DISTINCT i.order_num), - COALESCE(SUM(i.quantity), 0)::BIGINT - FROM product p - JOIN includes i ON i.product_code = p.code - GROUP BY p.code, p.description, p.availability - HAVING - p.availability < p_stock_threshold - AND COUNT(DISTINCT i.order_num) >= p_demand_threshold - ORDER BY 4 DESC, 5 DESC, 3 ASC; -$$; - -CREATE OR REPLACE FUNCTION get_products_monthly_sales() -RETURNS TABLE ( - product_code VARCHAR(8), - product_description VARCHAR(500), - year INTEGER, - month INTEGER, - number_of_orders BIGINT, - total_quantity_sold BIGINT, - total_revenue NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - p.code, - p.description, - EXTRACT(YEAR FROM o.last_date_mod)::INTEGER, - EXTRACT(MONTH FROM o.last_date_mod)::INTEGER, - COUNT(DISTINCT o.order_num), - COALESCE(SUM(i.quantity), 0)::BIGINT, - ROUND( - COALESCE( - SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ), - 0 - ), - 2 - ) - FROM product p - JOIN includes i ON i.product_code = p.code - JOIN "order" o ON o.order_num = i.order_num - GROUP BY - p.code, p.description, - EXTRACT(YEAR FROM o.last_date_mod), - EXTRACT(MONTH FROM o.last_date_mod) - ORDER BY 3 DESC, 4 DESC, 7 DESC; -$$; - -CREATE OR REPLACE FUNCTION get_stores_by_last_calendar_year_revenue() -RETURNS TABLE ( - store_id VARCHAR(3), - store_name VARCHAR(50), - number_of_orders BIGINT, - total_quantity_sold BIGINT, - total_revenue NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - s.store_id, - s.name, - COUNT(DISTINCT o.order_num), - COALESCE(SUM(i.quantity), 0)::BIGINT, - ROUND( - COALESCE( - SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ), - 0 - ), - 2 - ) - FROM store s - LEFT JOIN sells se ON se.store_id = s.store_id - LEFT JOIN product p ON p.code = se.product_code - LEFT JOIN includes i ON i.product_code = p.code - LEFT JOIN "order" o - ON o.order_num = i.order_num - AND o.last_date_mod >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year' - AND o.last_date_mod < DATE_TRUNC('year', CURRENT_DATE) - GROUP BY s.store_id, s.name - ORDER BY 5 DESC, s.store_id; -$$; - -CREATE OR REPLACE FUNCTION get_products_never_ordered() -RETURNS TABLE ( - product_code VARCHAR(8), - product_description VARCHAR(500), - product_price NUMERIC, - current_stock INTEGER -) -LANGUAGE sql -AS $$ - SELECT p.code, p.description, p.price::NUMERIC, p.availability - FROM product p - WHERE NOT EXISTS ( - SELECT 1 - FROM includes i - WHERE i.product_code = p.code - ) - ORDER BY p.code; -$$; - -CREATE OR REPLACE FUNCTION get_products_by_number_of_orders() -RETURNS TABLE ( - product_code VARCHAR(8), - product_description VARCHAR(500), - product_price NUMERIC, - number_of_orders BIGINT -) -LANGUAGE sql -AS $$ - SELECT - p.code, - p.description, - p.price::NUMERIC, - COUNT(DISTINCT i.order_num) - FROM product p - JOIN includes i ON i.product_code = p.code - GROUP BY p.code, p.description, p.price - ORDER BY 4 DESC, p.code; -$$; - -CREATE OR REPLACE FUNCTION get_stores_by_average_review() -RETURNS TABLE ( - store_id VARCHAR(3), - store_name VARCHAR(50), - average_review NUMERIC, - number_of_reviews BIGINT -) -LANGUAGE sql -AS $$ - WITH store_reviews AS ( - SELECT DISTINCT - s.store_id, - s.name AS store_name, - r.order_num, - r.rating - FROM store s - JOIN sells se ON se.store_id = s.store_id - JOIN includes i ON i.product_code = se.product_code - JOIN review r ON r.order_num = i.order_num - ) - SELECT - s.store_id, - s.name, - COALESCE(ROUND(AVG(sr.rating), 2), 0)::NUMERIC, - COUNT(sr.order_num) - FROM store s - LEFT JOIN store_reviews sr ON sr.store_id = s.store_id - GROUP BY s.store_id, s.name - ORDER BY 3 DESC, s.store_id; -$$; - -CREATE OR REPLACE FUNCTION get_store_with_highest_revenue_growth() -RETURNS TABLE ( - store_id VARCHAR(3), - store_name VARCHAR(50), - previous_year_revenue NUMERIC, - last_year_revenue NUMERIC, - revenue_growth NUMERIC -) -LANGUAGE sql -AS $$ - WITH store_years AS ( - SELECT - s.store_id, - s.name AS store_name, - EXTRACT(YEAR FROM o.last_date_mod)::INTEGER AS sales_year, - SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ) AS revenue - FROM store s - JOIN sells se ON se.store_id = s.store_id - JOIN includes i ON i.product_code = se.product_code - JOIN product p ON p.code = i.product_code - JOIN "order" o ON o.order_num = i.order_num - WHERE o.last_date_mod >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '2 years' - AND o.last_date_mod < DATE_TRUNC('year', CURRENT_DATE) - GROUP BY s.store_id, s.name, EXTRACT(YEAR FROM o.last_date_mod) - ), - comparison AS ( - SELECT - s.store_id, - s.name AS store_name, - COALESCE(MAX(CASE - WHEN sy.sales_year = EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 2 - THEN sy.revenue ELSE 0 END), 0) AS previous_year_revenue, - COALESCE(MAX(CASE - WHEN sy.sales_year = EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1 - THEN sy.revenue ELSE 0 END), 0) AS last_year_revenue - FROM store s - LEFT JOIN store_years sy ON sy.store_id = s.store_id - GROUP BY s.store_id, s.name - ) - SELECT - store_id, - store_name, - ROUND(previous_year_revenue, 2), - ROUND(last_year_revenue, 2), - ROUND(last_year_revenue - previous_year_revenue, 2) - FROM comparison - ORDER BY 5 DESC, store_id - LIMIT 1; -$$; - -CREATE OR REPLACE FUNCTION get_clients_by_number_of_orders() -RETURNS TABLE ( - client_id INTEGER, - client_name TEXT, - number_of_orders BIGINT -) -LANGUAGE sql -AS $$ - SELECT - c.client_id, - CONCAT_WS(' ', c.first_name, c.last_name), - COUNT(o.order_num) - FROM client c - JOIN "order" o ON o.client_id = c.client_id - GROUP BY c.client_id, c.first_name, c.last_name - ORDER BY 3 DESC, c.client_id; -$$; - -CREATE OR REPLACE FUNCTION get_approximate_orders_per_client() -RETURNS TABLE ( - total_clients BIGINT, - total_orders BIGINT, - approximate_orders_per_client NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - (SELECT COUNT(*) FROM client), - (SELECT COUNT(*) FROM "order"), - ROUND( - (SELECT COUNT(*)::NUMERIC FROM "order") - / NULLIF((SELECT COUNT(*) FROM client), 0), - 2 - ); -$$; - -CREATE OR REPLACE FUNCTION get_clients_without_orders() -RETURNS TABLE ( - client_id INTEGER, - client_name TEXT, - email VARCHAR(50) -) -LANGUAGE sql -AS $$ - SELECT - c.client_id, - CONCAT_WS(' ', c.first_name, c.last_name), - c.email - FROM client c - WHERE NOT EXISTS ( - SELECT 1 FROM "order" o WHERE o.client_id = c.client_id - ) - ORDER BY c.client_id; -$$; - -CREATE OR REPLACE FUNCTION get_store_request_statistics() -RETURNS TABLE ( - store_id VARCHAR(3), - store_name VARCHAR(50), - total_requests BIGINT, - solved_requests BIGINT, - requests_in_progress BIGINT -) -LANGUAGE sql -AS $$ - SELECT - s.store_id, - s.name, - COUNT(r.request_num), - COUNT(r.request_num) FILTER (WHERE r.customer_satisfaction > 0), - COUNT(r.request_num) FILTER (WHERE r.customer_satisfaction <= 0) - FROM store s - LEFT JOIN for_store fs ON fs.store_id = s.store_id - LEFT JOIN request r ON r.request_num = fs.request_num - GROUP BY s.store_id, s.name - ORDER BY 3 DESC, s.store_id; -$$; - -CREATE OR REPLACE FUNCTION get_top_10_employees_by_requests_last_month() -RETURNS TABLE ( - employee_id VARCHAR(10), - employee_name TEXT, - number_of_requests BIGINT -) -LANGUAGE sql -AS $$ - SELECT - e.employee_id, - CONCAT_WS(' ', p.first_name, p.last_name), - COUNT(DISTINCT a.request_num) - FROM employees e - JOIN personal p ON p.id = e.employee_id - JOIN answers a ON a.personal_id = e.employee_id - JOIN request r ON r.request_num = a.request_num - WHERE r.date_and_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' - AND r.date_and_time < DATE_TRUNC('month', CURRENT_DATE) - GROUP BY e.employee_id, p.first_name, p.last_name - ORDER BY 3 DESC, e.employee_id - LIMIT 10; -$$; - -CREATE OR REPLACE FUNCTION get_employees_by_hours_and_pay() -RETURNS TABLE ( - employee_id VARCHAR(10), - employee_name TEXT, - total_hours_worked NUMERIC, - total_pay NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - e.employee_id, - CONCAT_WS(' ', p.first_name, p.last_name), - COALESCE(SUM(w.total_hours), 0), - COALESCE(SUM(w.wage * w.total_hours), 0) - FROM employees e - JOIN personal p ON p.id = e.employee_id - LEFT JOIN worked w ON w.personal_id = e.employee_id - GROUP BY e.employee_id, p.first_name, p.last_name - ORDER BY 3 DESC, 4 DESC, e.employee_id; -$$; - -CREATE OR REPLACE FUNCTION get_stores_average_pay() -RETURNS TABLE ( - store_id VARCHAR(3), - store_name VARCHAR(50), - average_pay NUMERIC -) -LANGUAGE sql -AS $$ - SELECT - s.store_id, - s.name, - COALESCE(ROUND(AVG(w.wage), 2), 0) - FROM store s - LEFT JOIN worked w ON w.store_id = s.store_id - GROUP BY s.store_id, s.name - ORDER BY 3 DESC, s.store_id; -$$; - -CREATE OR REPLACE FUNCTION get_employee_product_changes_last_month() -RETURNS TABLE ( - employee_id VARCHAR(10), - employee_name TEXT, - number_of_product_changes BIGINT -) -LANGUAGE sql -AS $$ - SELECT - e.employee_id, - CONCAT_WS(' ', p.first_name, p.last_name), - COUNT(mc.change_date_time) - FROM employees e - JOIN personal p ON p.id = e.employee_id - LEFT JOIN makes_change mc - ON mc.personal_id = e.employee_id - AND mc.change_date_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' - AND mc.change_date_time < DATE_TRUNC('month', CURRENT_DATE) - GROUP BY e.employee_id, p.first_name, p.last_name - ORDER BY 3 DESC, e.employee_id; -$$; - -CREATE OR REPLACE FUNCTION get_stores_by_monthly_profit_and_revenue_growth() -RETURNS TABLE ( - store_id VARCHAR(3), - store_name VARCHAR(50), - month_and_year TEXT, - monthly_profit NUMERIC, - previous_month_revenue NUMERIC, - current_month_revenue NUMERIC, - revenue_growth NUMERIC -) -LANGUAGE sql -AS $$ - WITH monthly_revenue AS ( - SELECT - s.store_id, - s.name AS store_name, - DATE_TRUNC('month', o.last_date_mod)::DATE AS month_date, - SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ) AS revenue - FROM store s - JOIN sells se ON se.store_id = s.store_id - JOIN includes i ON i.product_code = se.product_code - JOIN product p ON p.code = i.product_code - JOIN "order" o ON o.order_num = i.order_num - GROUP BY s.store_id, s.name, DATE_TRUNC('month', o.last_date_mod) - ), - with_previous AS ( - SELECT - store_id, - store_name, - month_date, - revenue, - LAG(revenue) OVER ( - PARTITION BY store_id - ORDER BY month_date - ) AS previous_revenue - FROM monthly_revenue - ) - SELECT - store_id, - store_name, - TO_CHAR(month_date, 'YYYY-MM'), - ROUND(revenue, 2), - ROUND(COALESCE(previous_revenue, 0), 2), - ROUND(revenue, 2), - ROUND(revenue - COALESCE(previous_revenue, 0), 2) - FROM with_previous - ORDER BY month_date DESC, 4 DESC, store_id; -$$; - -CREATE OR REPLACE FUNCTION get_unapproved_reports() -RETURNS TABLE ( - report_date TIMESTAMP, - store_id VARCHAR(3), - overall_profit NUMERIC, - sales_trend VARCHAR(100), - marketing_growth VARCHAR(100), - owner_signature VARCHAR(50) -) -LANGUAGE sql -AS $$ - SELECT - r.date, - r.store_id, - r.overall_profit, - r.sales_trend, - r.marketing_growth, - r.owner_signature - FROM report r - LEFT JOIN approves a - ON a.report_date = r.date - AND a.store_id = r.store_id - WHERE a.report_date IS NULL - ORDER BY r.date DESC, r.store_id; -$$; -`; - - -const TRIGGERS_SQL = String.raw` --- ============================================================ --- HANDCRAFT MARKETPLACE TRIGGERS --- Compatible with the supplied project schema. --- ============================================================ - -CREATE OR REPLACE FUNCTION update_store_rating() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -DECLARE - v_order_num VARCHAR(11); - v_store_id VARCHAR(3); -BEGIN - IF TG_OP = 'DELETE' THEN - v_order_num := OLD.order_num; - ELSE - v_order_num := NEW.order_num; - END IF; - - v_store_id := LEFT(v_order_num, 3); - - UPDATE store s - SET rating = COALESCE( - ( - SELECT ROUND(AVG(r.rating), 1) - FROM review r - JOIN includes i - ON i.order_num = r.order_num - JOIN sells sl - ON sl.product_code = i.product_code - AND sl.store_ID = v_store_id - JOIN "order" o - ON o.order_num = r.order_num - WHERE LEFT(o.order_num, 3) = v_store_id - ), - 0 - ) - WHERE s.store_ID = v_store_id; - - RETURN NULL; -END; -$$; - -DROP TRIGGER IF EXISTS trg_update_store_rating ON review; - -CREATE TRIGGER trg_update_store_rating -AFTER INSERT OR UPDATE OR DELETE -ON review -FOR EACH ROW -EXECUTE FUNCTION update_store_rating(); - - -CREATE OR REPLACE FUNCTION check_product_availability() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -DECLARE - v_available_quantity INTEGER; -BEGIN - SELECT p.availability - INTO v_available_quantity - FROM product p - WHERE p.code = NEW.product_code - FOR UPDATE; - - IF v_available_quantity IS NULL THEN - RAISE EXCEPTION - 'Product % does not exist or is not available.', - NEW.product_code; - END IF; - - IF TG_OP = 'INSERT' THEN - IF NEW.quantity > v_available_quantity THEN - RAISE EXCEPTION - 'Insufficient stock for product %. Available: %, requested: %.', - NEW.product_code, - v_available_quantity, - NEW.quantity; - END IF; - - ELSIF TG_OP = 'UPDATE' THEN - IF NEW.product_code = OLD.product_code THEN - IF NEW.quantity > OLD.quantity - AND (NEW.quantity - OLD.quantity) > v_available_quantity THEN - RAISE EXCEPTION - 'Insufficient stock for product %. Available: %, additional requested: %.', - NEW.product_code, - v_available_quantity, - NEW.quantity - OLD.quantity; - END IF; - ELSE - IF NEW.quantity > v_available_quantity THEN - RAISE EXCEPTION - 'Insufficient stock for product %. Available: %, requested: %.', - NEW.product_code, - v_available_quantity, - NEW.quantity; - END IF; - END IF; - END IF; - - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_check_product_availability ON includes; - -CREATE TRIGGER trg_check_product_availability -BEFORE INSERT OR UPDATE -ON includes -FOR EACH ROW -EXECUTE FUNCTION check_product_availability(); - - -CREATE OR REPLACE FUNCTION update_product_availability() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -BEGIN - IF TG_OP = 'INSERT' THEN - - UPDATE product - SET availability = availability - NEW.quantity - WHERE code = NEW.product_code; - - ELSIF TG_OP = 'UPDATE' THEN - - IF NEW.product_code = OLD.product_code THEN - - UPDATE product - SET availability = availability - (NEW.quantity - OLD.quantity) - WHERE code = NEW.product_code; - - ELSE - - UPDATE product - SET availability = availability + OLD.quantity - WHERE code = OLD.product_code; - - UPDATE product - SET availability = availability - NEW.quantity - WHERE code = NEW.product_code; - - END IF; - - ELSIF TG_OP = 'DELETE' THEN - - UPDATE product - SET availability = availability + OLD.quantity - WHERE code = OLD.product_code; - - END IF; - - RETURN NULL; -END; -$$; - -DROP TRIGGER IF EXISTS trg_update_product_availability ON includes; - -CREATE TRIGGER trg_update_product_availability -AFTER INSERT OR UPDATE OR DELETE -ON includes -FOR EACH ROW -EXECUTE FUNCTION update_product_availability(); - - -CREATE OR REPLACE FUNCTION delete_product_changes() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -BEGIN - DELETE FROM "change" - WHERE product_code = OLD.code; - - RETURN OLD; -END; -$$; - -DROP TRIGGER IF EXISTS trg_delete_product_changes ON product; - -CREATE TRIGGER trg_delete_product_changes -BEFORE DELETE -ON product -FOR EACH ROW -EXECUTE FUNCTION delete_product_changes(); - - -CREATE OR REPLACE FUNCTION prevent_store_deletion() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -BEGIN - IF EXISTS ( - SELECT 1 - FROM report - WHERE store_ID = OLD.store_ID - ) THEN - RAISE EXCEPTION - 'Store % cannot be deleted because it has existing reports.', - OLD.store_ID; - END IF; - - IF EXISTS ( - SELECT 1 - FROM sells - WHERE store_ID = OLD.store_ID - ) THEN - RAISE EXCEPTION - 'Store % cannot be deleted because it has existing product records.', - OLD.store_ID; - END IF; - - IF EXISTS ( - SELECT 1 - FROM "order" - WHERE LEFT(order_num, 3) = OLD.store_ID - ) THEN - RAISE EXCEPTION - 'Store % cannot be deleted because it has existing orders.', - OLD.store_ID; - END IF; - - RETURN OLD; -END; -$$; - -DROP TRIGGER IF EXISTS trg_prevent_store_deletion ON store; - -CREATE TRIGGER trg_prevent_store_deletion -BEFORE DELETE -ON store -FOR EACH ROW -EXECUTE FUNCTION prevent_store_deletion(); - - -CREATE OR REPLACE FUNCTION update_order_modified_date() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -BEGIN - NEW.last_date_mod := CURRENT_TIMESTAMP; - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_update_order_modified_date ON "order"; - -CREATE TRIGGER trg_update_order_modified_date -BEFORE UPDATE -ON "order" -FOR EACH ROW -EXECUTE FUNCTION update_order_modified_date(); - - -CREATE OR REPLACE FUNCTION validate_employee_authorization() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -DECLARE - v_authorisation TEXT; -BEGIN - SELECT p.authorisation - INTO v_authorisation - FROM permissions p - WHERE p.personal_id = NEW.personal_id; - - IF v_authorisation IS NULL THEN - RAISE EXCEPTION - 'Employee % does not have valid authorization.', - NEW.personal_id; - END IF; - - -- makes_change has no authorisation column in the supplied schema. - -- The employee's permission row is therefore the source of truth. - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_validate_employee_authorization ON makes_change; - -CREATE TRIGGER trg_validate_employee_authorization -BEFORE INSERT OR UPDATE -ON makes_change -FOR EACH ROW -EXECUTE FUNCTION validate_employee_authorization(); - - -CREATE OR REPLACE FUNCTION initialize_employee_statistics() -RETURNS TRIGGER -LANGUAGE plpgsql -AS $$ -BEGIN - /* - * The schema creates an employee before store/report assignment in the - * normal application flow. If an assignment and a report already exist, - * create a zero-hours starting record; otherwise there is nothing to - * initialize yet. - */ - INSERT INTO worked ( - personal_id, - report_date, - store_ID, - wage, - pay_method, - total_hours, - week - ) - SELECT - NEW.employee_id, - r.date, - wis.store_ID, - 0, - 'full_time', - 0, - TO_CHAR(CURRENT_DATE - INTERVAL '6 days', 'DD.MM.YYYY') - || ' - ' || - TO_CHAR(CURRENT_DATE, 'DD.MM.YYYY') - FROM works_in_store wis - JOIN LATERAL ( - SELECT r2.date - FROM report r2 - WHERE r2.store_ID = wis.store_ID - ORDER BY r2.date DESC - LIMIT 1 - ) r ON TRUE - WHERE wis.personal_id = NEW.employee_id - ON CONFLICT (personal_id, report_date, store_ID) DO NOTHING; - - RETURN NEW; -END; -$$; - -DROP TRIGGER IF EXISTS trg_initialize_employee_statistics ON employees; - -CREATE TRIGGER trg_initialize_employee_statistics -AFTER INSERT -ON employees -FOR EACH ROW -EXECUTE FUNCTION initialize_employee_statistics(); -`; - - -const VIEWS_SQL = String.raw` --- ============================================================ --- HANDCRAFT MARKETPLACE VIEWS --- Compatible with the supplied project schema. --- ============================================================ - -DROP VIEW IF EXISTS vw_product_store_overview; -CREATE VIEW vw_product_store_overview AS -SELECT - p.code AS product_code, - p.description, - p.price, - p.availability, - p.weight, - p.width_x_length_x_depth, - p.aprox_production_time, - s.store_ID, - s.name AS store_name, - s.physical_address, - s.rating, - sl.discount -FROM product p -JOIN sells sl - ON sl.product_code = p.code -JOIN store s - ON s.store_ID = sl.store_ID; - - -DROP VIEW IF EXISTS vw_customer_order_overview; -CREATE VIEW vw_customer_order_overview AS -SELECT - o.order_num, - i.quantity, - o.status, - o.last_date_mod, - o.payment_method, - o.discount, - c.client_ID, - c.first_name, - c.last_name, - c.email, - p.code AS product_code, - p.description AS product_description, - p.price -FROM "order" o -JOIN makes_request mr - ON mr.order_num = o.order_num -JOIN client c - ON c.client_ID = mr.client_ID -JOIN includes i - ON i.order_num = o.order_num -JOIN product p - ON p.code = i.product_code; - - -DROP VIEW IF EXISTS vw_monthly_sales_profit; -CREATE VIEW vw_monthly_sales_profit AS -SELECT - s.store_ID, - s.name AS store_name, - r.date AS report_date, - mp.month_and_year, - mp.profit, - r.overall_profit, - ed.monthly_profit, - ed.sales, - ed.damages -FROM store s -JOIN report r - ON r.store_ID = s.store_ID -LEFT JOIN monthly_profit mp - ON mp.report_date = r.date - AND mp.store_ID = r.store_ID -LEFT JOIN exchanges_data ed - ON ed.report_date = r.date - AND ed.store_ID = r.store_ID; - - -DROP VIEW IF EXISTS vw_employee_workload_salary; -CREATE VIEW vw_employee_workload_salary AS -SELECT - e.employee_id, - p.first_name, - p.last_name, - p.email, - e.date_of_hire, - s.store_ID, - s.name AS store_name, - w.week, - w.total_hours, - w.wage, - w.pay_method, - COALESCE(w.wage * w.total_hours, 0) AS total_pay -FROM employees e -JOIN personal p - ON p.id = e.employee_id -JOIN works_in_store wis - ON wis.personal_id = e.employee_id -JOIN store s - ON s.store_ID = wis.store_ID -LEFT JOIN worked w - ON w.personal_id = e.employee_id - AND w.store_ID = wis.store_ID; - - -DROP VIEW IF EXISTS vw_customer_request_response; -CREATE VIEW vw_customer_request_response AS -SELECT - r.request_num, - r.date_and_time, - r.problem, - r.notes_of_communication, - r.customer_satisfaction, - c.client_ID, - c.first_name AS client_first_name, - c.last_name AS client_last_name, - c.email AS client_email, - p.id AS employee_id, - p.first_name AS employee_first_name, - p.last_name AS employee_last_name -FROM request r -LEFT JOIN client c - ON c.client_ID = CASE - WHEN SUBSTRING(r.request_num FROM 9 FOR 4) ~ '^[0-9]{4}$' - THEN SUBSTRING(r.request_num FROM 9 FOR 4)::INTEGER - ELSE NULL - END -LEFT JOIN answers a - ON a.request_num = r.request_num -LEFT JOIN personal p - ON p.id = a.personal_id; - - -DROP VIEW IF EXISTS vw_store_inventory; -CREATE VIEW vw_store_inventory AS -SELECT - s.store_ID, - s.name AS store_name, - p.code AS product_code, - p.description, - p.price, - p.availability, - p.aprox_production_time, - sl.discount -FROM store s -JOIN sells sl - ON sl.store_ID = s.store_ID -JOIN product p - ON p.code = sl.product_code; - - -DROP VIEW IF EXISTS vw_customer_order_history; -CREATE VIEW vw_customer_order_history AS -SELECT - c.client_ID, - c.first_name, - c.last_name, - c.email, - o.order_num, - i.quantity, - o.status, - o.last_date_mod, - o.payment_method, - o.discount, - p.code AS product_code, - p.description AS product_description, - p.price -FROM client c -JOIN makes_request mr - ON mr.client_ID = c.client_ID -JOIN "order" o - ON o.order_num = mr.order_num -JOIN includes i - ON i.order_num = o.order_num -JOIN product p - ON p.code = i.product_code; - - -DROP VIEW IF EXISTS vw_store_performance; -CREATE VIEW vw_store_performance AS -SELECT - s.store_ID, - s.name AS store_name, - s.date_of_founding, - s.rating, - COUNT(DISTINCT r.date) AS number_of_reports, - COALESCE(SUM(mp.profit), 0) AS total_reported_profit, - COALESCE(MAX(r.overall_profit), 0) AS overall_profit, - COALESCE(SUM(ed.sales), 0) AS total_sales, - COALESCE(SUM(ed.damages), 0) AS total_damages, - COALESCE(SUM(ed.monthly_profit), 0) AS total_monthly_profit -FROM store s -LEFT JOIN report r - ON r.store_ID = s.store_ID -LEFT JOIN monthly_profit mp - ON mp.report_date = r.date - AND mp.store_ID = r.store_ID -LEFT JOIN exchanges_data ed - ON ed.report_date = r.date - AND ed.store_ID = r.store_ID -GROUP BY - s.store_ID, - s.name, - s.date_of_founding, - s.rating; -`; - - -const database = { - database: { - get(sql, params, callback) { - if (typeof params === 'function') { - callback = params; - params = []; - } - dbQuery(sql, params || [], (err, result) => { - callback(err, result && result.rows ? result.rows[0] : undefined); - }); - }, - all(sql, params, callback) { - if (typeof params === 'function') { - callback = params; - params = []; - } - dbQuery(sql, params || [], (err, result) => { - callback(err, result ? result.rows : []); - }); - }, - run(sql, params, callback) { - if (typeof params === 'function') { - callback = params; - params = []; - } - const normalized = String(sql).trim().replace(/;\s*$/, '').toUpperCase(); - - if (normalized === 'BEGIN TRANSACTION' || normalized === 'BEGIN') { - const existingClient = transactionStorage.getStore(); - - // A transaction is already active in this async execution context. - // Do not create a second transaction on the same request. - if (existingClient) { - callback?.(null); - return; - } - - pool.connect() - .then(client => { - return client.query('BEGIN') - .then(() => { - // Everything scheduled by the callback now inherits - // this client through AsyncLocalStorage. Other - // concurrent requests get their own transaction. - transactionStorage.run(client, () => { - callback?.(null); - }); - }) - .catch(err => { - client.release(); - callback?.(err); - }); - }) - .catch(err => { - callback?.(err); - }); - - return; - } - - if (normalized === 'COMMIT') { - const client = transactionStorage.getStore(); - - if (!client) { - callback?.(null); - return; - } - - client.query('COMMIT') - .then(() => { - client.release(); - callback?.(null); - }) - .catch(err => { - // COMMIT may fail before the transaction is completed. - // Roll back before releasing the client when possible. - client.query('ROLLBACK') - .catch(() => {}) - .then(() => { - client.release(); - callback?.(err); - }); - }); - - return; - } - - if (normalized === 'ROLLBACK') { - const client = transactionStorage.getStore(); - - if (!client) { - callback?.(null); - return; - } - - client.query('ROLLBACK') - .then(() => { - client.release(); - callback?.(null); - }) - .catch(err => { - client.release(); - callback?.(err); - }); - - return; - } - - dbQuery(sql, params || [], (err, result) => { - if (callback) { - callback.call( - { changes: result ? result.rowCount : 0, lastID: result?.rows?.[0]?.id }, - err - ); - } +function dropAllTables() { + return new Promise((resolve, reject) => { + console.log('๐Ÿ—‘๏ธ Dropping all tables...'); + + // 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' + ]; + + let index = 0; + + function runNext() { + if (index >= dropQueries.length) { + console.log('โœ… All tables dropped'); + resolve(); + return; + } + + database.database.run(dropQueries[index], [], (err) => { + if (err) { + console.error(`Error dropping table: ${err.message}`); + // Continue anyway + } + index++; + runNext(); }); } - }, - - async installReportFunctions() { - await pool.query(REPORT_FUNCTIONS_SQL); - console.log('โœ… PostgreSQL report functions installed'); - }, - - async installTriggersAndViews() { - await pool.query(TRIGGERS_SQL); - console.log('โœ… PostgreSQL triggers installed'); - - await pool.query(VIEWS_SQL); - console.log('โœ… PostgreSQL views installed'); - }, - - runReport(reportName, params, callback) { - const allowed = new Set([ - 'get_orders_by_total', - 'get_products_by_total_sales', - 'get_low_stock_high_demand_products', - 'get_products_monthly_sales', - 'get_stores_by_last_calendar_year_revenue', - 'get_products_never_ordered', - 'get_products_by_number_of_orders', - 'get_stores_by_average_review', - 'get_store_with_highest_revenue_growth', - 'get_clients_by_number_of_orders', - 'get_approximate_orders_per_client', - 'get_clients_without_orders', - 'get_store_request_statistics', - 'get_top_10_employees_by_requests_last_month', - 'get_employees_by_hours_and_pay', - 'get_stores_average_pay', - 'get_employee_product_changes_last_month', - 'get_stores_by_monthly_profit_and_revenue_growth', - 'get_unapproved_reports' - ]); - - if (!allowed.has(reportName)) { - callback(new Error('Unknown report: ' + reportName), null); - return; - } - - const values = Array.isArray(params) ? params : []; - const placeholders = values.map((_, index) => '$' + (index + 1)).join(', '); - - dbQuery( - `SELECT * FROM ${reportName}(${placeholders})`, - values, - (err, result) => callback(err, result?.rows || []) - ); - }, - - async initializeDatabase() { - // The database supplied by the project is authoritative. Existing tables - // are removed before recreation so an old incompatible schema can never - // survive a restart and cause CREATE TABLE IF NOT EXISTS to skip columns. - const schemaCompatibility = await pool.query(` - SELECT - EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='product') AS product_exists, - EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='category_id') AS product_has_category, - EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='product' AND column_name='store_id') AS product_has_store, - EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='order' AND column_name='store_id') AS order_has_store, - EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='request' AND column_name='store_id') AS request_has_store, - 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, - 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 - `); - - const c = schemaCompatibility.rows[0]; - const forceReset = String(process.env.RESET_DATABASE || '').toLowerCase() === 'true' || process.env.RESET_DATABASE === '1'; - 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; - const resetDatabase = forceReset || schemaMismatch; - - if (resetDatabase) { - console.log('๐Ÿงน Existing PostgreSQL schema is incompatible with the project schema. Recreating project tables...'); - await pool.query(` - DROP TABLE IF EXISTS audit_log CASCADE; - DROP TABLE IF EXISTS user_roles CASCADE; - DROP TABLE IF EXISTS roles CASCADE; - DROP TABLE IF EXISTS users CASCADE; - DROP TABLE IF EXISTS approves CASCADE; - DROP TABLE IF EXISTS includes CASCADE; - DROP TABLE IF EXISTS sells CASCADE; - DROP TABLE IF EXISTS worked CASCADE; - DROP TABLE IF EXISTS works_in_store CASCADE; - DROP TABLE IF EXISTS makes_change CASCADE; - DROP TABLE IF EXISTS "change" CASCADE; - DROP TABLE IF EXISTS for_store CASCADE; - DROP TABLE IF EXISTS answers CASCADE; - DROP TABLE IF EXISTS makes_request CASCADE; - DROP TABLE IF EXISTS request CASCADE; - DROP TABLE IF EXISTS exchanges_data CASCADE; - DROP TABLE IF EXISTS monthly_profit CASCADE; - DROP TABLE IF EXISTS report CASCADE; - DROP TABLE IF EXISTS refund CASCADE; - DROP TABLE IF EXISTS review CASCADE; - DROP TABLE IF EXISTS "order" CASCADE; - DROP TABLE IF EXISTS delivery_address CASCADE; - DROP TABLE IF EXISTS client CASCADE; - DROP TABLE IF EXISTS employees CASCADE; - DROP TABLE IF EXISTS boss CASCADE; - DROP TABLE IF EXISTS permissions CASCADE; - DROP TABLE IF EXISTS personal CASCADE; - DROP TABLE IF EXISTS color CASCADE; - DROP TABLE IF EXISTS image CASCADE; - DROP TABLE IF EXISTS product CASCADE; - DROP TABLE IF EXISTS store CASCADE; - DROP TABLE IF EXISTS category CASCADE; - `); - } - - const schema = ` - CREATE TABLE IF NOT EXISTS category ( - id SERIAL PRIMARY KEY, - name VARCHAR(50) NOT NULL, - parent_category_id INTEGER REFERENCES category(id) NOT NULL - ); - - CREATE TABLE IF NOT EXISTS product ( - code VARCHAR(8) PRIMARY KEY DEFAULT '-1', - price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0), - availability INTEGER NOT NULL, - weight DECIMAL(5,2) NOT NULL CHECK (weight > 0), - width_x_length_x_depth VARCHAR(20) NOT NULL, - aprox_production_time INTEGER NOT NULL, - description VARCHAR(500) NOT NULL, - category_id INTEGER NOT NULL REFERENCES category(id) ON DELETE SET DEFAULT - ); - - CREATE TABLE IF NOT EXISTS image ( - product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, - image VARCHAR NOT NULL DEFAULT 'Image NOT found!' - ); - - CREATE TABLE IF NOT EXISTS color ( - product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, - color VARCHAR(50) - ); - - CREATE TABLE IF NOT EXISTS store ( - store_ID VARCHAR(3) PRIMARY KEY, - name VARCHAR(50) UNIQUE NOT NULL, + + runNext(); + }); +} + +function createAllTables() { + return new Promise((resolve, reject) => { + console.log('๐Ÿ—๏ธ Creating tables...'); + + const createQueries = [ + // 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 + )`, + + // 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 VARCHAR(100) NOT NULL, - store_email VARCHAR(40) UNIQUE NOT NULL CHECK (store_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), - rating DECIMAL(2,1) NOT NULL DEFAULT 0 CHECK (rating>=0.0 AND rating<=5.0) - ); - - CREATE TABLE IF NOT EXISTS personal ( - id VARCHAR(10) PRIMARY KEY, - first_name VARCHAR(20) NOT NULL, - last_name VARCHAR(20) NOT NULL, - ssn VARCHAR(13) NOT NULL CHECK (ssn ~ '^[0-9]{13}$'), - email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), - password VARCHAR NOT NULL - ); - - CREATE TABLE IF NOT EXISTS permissions ( - personal_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE, - type VARCHAR(50) NOT NULL, - authorisation VARCHAR(50) NOT NULL - ); - - CREATE TABLE IF NOT EXISTS boss ( - boss_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE - ); - - CREATE TABLE IF NOT EXISTS employees ( - employee_id VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE, - date_of_hire DATE NOT NULL - ); - - CREATE TABLE IF NOT EXISTS client ( - client_ID SERIAL PRIMARY KEY, - first_name VARCHAR(50) NOT NULL, - last_name VARCHAR(50) NOT NULL, - email VARCHAR(50) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'), - password VARCHAR NOT NULL - ); - - CREATE TABLE IF NOT EXISTS delivery_address ( - client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID) ON DELETE CASCADE, - address VARCHAR(200) NOT NULL, - city VARCHAR(30) NOT NULL, - postcode VARCHAR(20) NOT NULL, - country VARCHAR(40) NOT NULL, - is_default BOOLEAN DEFAULT TRUE - ); - - CREATE TABLE IF NOT EXISTS "order" ( - order_num VARCHAR(11) PRIMARY KEY, - client_ID INTEGER REFERENCES client(client_ID) ON DELETE CASCADE, - status VARCHAR(20) NOT NULL DEFAULT 'placed order', - last_date_mod TIMESTAMP NOT NULL, - payment_method VARCHAR(250) NOT NULL, - discount DECIMAL(5,2) DEFAULT 0.0 CHECK(discount>=0.0 AND discount<=100.00), - CONSTRAINT check_status CHECK (status IN ('placed order', 'being processed', 'shipping', 'delivered', 'canceled')) - ); - - CREATE TABLE IF NOT EXISTS review ( - order_num VARCHAR(11) PRIMARY KEY REFERENCES "order"(order_num) ON DELETE CASCADE, - comment VARCHAR(300), - rating DECIMAL(2,1) NOT NULL CHECK(rating>=0.0 AND rating<=5.0), - last_mod_date TIMESTAMP NOT NULL - ); - - CREATE TABLE IF NOT EXISTS refund ( - refund_id SERIAL PRIMARY KEY, - order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE, - reason VARCHAR(300), - amount DECIMAL(5,2) NOT NULL, - status VARCHAR(100) NOT NULL DEFAULT 'requested refund', - CONSTRAINT check_refund_status CHECK (status IN ('requested refund', 'being reviewed', 'approved', 'not approved', 'processed')) - ); - - CREATE TABLE IF NOT EXISTS report ( - date TIMESTAMP NOT NULL, - store_ID VARCHAR(3) NOT NULL REFERENCES store(store_ID) ON DELETE CASCADE, - overall_profit NUMERIC NOT NULL DEFAULT 0.0 CHECK(overall_profit>=0), - sales_trend VARCHAR(100) NOT NULL, - marketing_growth VARCHAR(100) NOT NULL, - owner_signature VARCHAR(50) NOT NULL DEFAULT 'Not signed yet', - PRIMARY KEY (date, store_ID) - ); - - CREATE TABLE IF NOT EXISTS monthly_profit ( - report_date TIMESTAMP NOT NULL, - store_ID VARCHAR(3) NOT NULL, - month_and_year DATE NOT NULL, - profit NUMERIC NOT NULL DEFAULT 0.0, - PRIMARY KEY(report_date, store_ID), - FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE - ); - - CREATE TABLE IF NOT EXISTS exchanges_data ( - report_date TIMESTAMP NOT NULL, - store_ID VARCHAR(3) NOT NULL, - monthly_profit NUMERIC NOT NULL DEFAULT 0.0, - date TIMESTAMP NOT NULL, - sales NUMERIC NOT NULL DEFAULT 0.0, - damages NUMERIC NOT NULL DEFAULT 0.0 CHECK (damages<=0), - PRIMARY KEY (report_date, store_ID), - FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE - ); - - CREATE TABLE IF NOT EXISTS request ( - request_num VARCHAR(14) PRIMARY KEY, - date_and_time TIMESTAMP NOT NULL, - problem VARCHAR(300) NOT NULL, - notes_of_communication VARCHAR, - customer_satisfaction NUMERIC NOT NULL - ); - - CREATE TABLE IF NOT EXISTS makes_request ( - client_ID INTEGER NOT NULL REFERENCES client(client_ID) ON DELETE CASCADE, - order_num VARCHAR(11) UNIQUE NOT NULL REFERENCES "order"(order_num) ON DELETE CASCADE, - PRIMARY KEY(client_ID, order_num) - ); - - CREATE TABLE IF NOT EXISTS answers ( - request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE, - personal_id VARCHAR(10) NOT NULL REFERENCES personal(id) ON DELETE CASCADE, - PRIMARY KEY(request_num, personal_id) - ); - - CREATE TABLE IF NOT EXISTS for_store ( - request_num VARCHAR(14) REFERENCES request(request_num) ON DELETE CASCADE, - store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE, - PRIMARY KEY(request_num, store_ID) - ); - - CREATE TABLE IF NOT EXISTS "change" ( - date_and_time TIMESTAMP NOT NULL, - product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, - changes VARCHAR NOT NULL, - PRIMARY KEY (date_and_time, product_code) - ); - - CREATE TABLE IF NOT EXISTS makes_change ( - personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, - change_date_time TIMESTAMP, - product_code VARCHAR(8), - PRIMARY KEY(personal_id, change_date_time, product_code), - FOREIGN KEY(change_date_time, product_code) REFERENCES "change"(date_and_time, product_code) ON DELETE CASCADE - ); - - CREATE TABLE IF NOT EXISTS works_in_store ( - personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, - store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE, - PRIMARY KEY(personal_id, store_ID) - ); - - CREATE TABLE IF NOT EXISTS worked ( - personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE, - report_date TIMESTAMP, - store_ID VARCHAR(3), - wage NUMERIC NOT NULL CHECK (wage>=0), - pay_method VARCHAR DEFAULT 'full-time', - total_hours NUMERIC NOT NULL, - week VARCHAR(23) NOT NULL, - PRIMARY KEY (personal_id, report_date, store_ID), - FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE, - CONSTRAINT check_pay_method CHECK (pay_method IN ('full_time', 'part-time', 'custom')) - ); - - CREATE TABLE IF NOT EXISTS sells ( - product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, - store_ID VARCHAR(3) REFERENCES store(store_ID) ON DELETE CASCADE, - discount NUMERIC NOT NULL DEFAULT 0.0, - PRIMARY KEY (product_code, store_ID) - ); - - CREATE TABLE IF NOT EXISTS includes ( - order_num VARCHAR(11) REFERENCES "order"(order_num) ON DELETE CASCADE, - product_code VARCHAR(8) REFERENCES product(code) ON DELETE CASCADE, - quantity INTEGER NOT NULL CHECK(quantity>=0), - PRIMARY KEY (order_num, product_code) - ); - - CREATE TABLE IF NOT EXISTS approves ( - boss_id VARCHAR(10) REFERENCES boss(boss_id) ON DELETE CASCADE, - report_date TIMESTAMP, - store_ID VARCHAR(3), - owner_signature VARCHAR NOT NULL, - PRIMARY KEY (boss_id, report_date, store_ID), - FOREIGN KEY (report_date, store_ID) REFERENCES report(date, store_ID) ON DELETE CASCADE - ); - - -- These four small tables are application authentication/audit storage. - -- They do not modify any of the project tables above. - CREATE TABLE IF NOT EXISTS users ( - id VARCHAR(50) PRIMARY KEY, + 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 + )`, + + // 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 BOOLEAN DEFAULT FALSE, + force_password_change INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP - ); - - CREATE TABLE IF NOT EXISTS roles ( - role_id SERIAL PRIMARY KEY, - name VARCHAR(50) UNIQUE NOT NULL, - description TEXT - ); - - 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) - ); - - CREATE TABLE IF NOT EXISTS audit_log ( - log_id BIGSERIAL PRIMARY KEY, - user_id VARCHAR(50), + )`, + + // 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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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) + )`, + + // 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 + )`, + + // 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_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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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), @@ -1941,790 +637,212 @@ ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP - ); - - CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id); - CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_ID); - CREATE INDEX IF NOT EXISTS idx_review_order ON review(order_num); - CREATE INDEX IF NOT EXISTS idx_request_date ON request(date_and_time); - CREATE INDEX IF NOT EXISTS idx_refund_order ON refund(order_num); - CREATE INDEX IF NOT EXISTS idx_personal_email ON personal(email); - CREATE INDEX IF NOT EXISTS idx_client_email ON client(email); - CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); - CREATE INDEX IF NOT EXISTS idx_users_username ON users(username); - CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id); - `; - - await pool.query(schema); - - // ------------------------------------------------------------ - // Compatibility migration for older PostgreSQL databases. - // - // Some existing project databases contain a permissions table - // created by an older version of the schema with a typo such as - // personal_is instead of personal_id. CREATE TABLE IF NOT EXISTS cannot add - // missing columns to an existing table, so the admin bootstrap - // INSERT would otherwise fail with PostgreSQL error 42703. - // - // The migration is intentionally non-destructive: it keeps all - // existing rows, renames the typo when possible, and migrates the old typo column into the expected schema without deleting - // permission values. - // ------------------------------------------------------------ - await pool.query(` - DO $$ - BEGIN - -- Some older versions of the database used the typo - -- personal_is instead of personal_id. Rename it rather - -- than adding a second column: the old column may be NOT NULL - -- and would otherwise make the admin bootstrap INSERT fail. - IF EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'personal_is' - ) AND NOT EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'personal_id' - ) THEN - ALTER TABLE permissions - RENAME COLUMN personal_is TO personal_id; - END IF; - - -- If neither spelling exists, add the expected column. - IF NOT EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'personal_id' - ) THEN - ALTER TABLE permissions - ADD COLUMN personal_id VARCHAR(10); - END IF; - - -- Some databases were already partially migrated and therefore - -- contain BOTH personal_is and personal_id. If the old typo - -- column participates in the primary key, PostgreSQL will not - -- allow us to drop its NOT NULL requirement. Migrate the - -- primary-key data to personal_id first, then remove the old - -- typo column from the key and drop it. - IF EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'personal_is' - ) AND EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'personal_id' - ) THEN - -- Copy old primary-key values into the new column where - -- the new column is currently empty. - UPDATE permissions - SET personal_id = personal_is - WHERE personal_id IS NULL - AND personal_is IS NOT NULL; - - -- Remove the old typo column from the primary key. - DO $drop_old_permission_pk$ - DECLARE - pk_name TEXT; - BEGIN - SELECT tc.constraint_name - INTO pk_name - FROM information_schema.table_constraints tc - JOIN information_schema.key_column_usage kcu - ON kcu.constraint_name = tc.constraint_name - AND kcu.table_schema = tc.table_schema - AND kcu.table_name = tc.table_name - WHERE tc.table_schema = 'public' - AND tc.table_name = 'permissions' - AND tc.constraint_type = 'PRIMARY KEY' - AND kcu.column_name = 'personal_is' - LIMIT 1; - - IF pk_name IS NOT NULL THEN - EXECUTE format( - 'ALTER TABLE permissions DROP CONSTRAINT %I', - pk_name - ); - END IF; - END - $drop_old_permission_pk$; - - -- The application uses personal_id as the primary key. - -- Drop the obsolete typo column after preserving its data. - ALTER TABLE permissions - DROP COLUMN personal_is; - - -- Recreate the primary key on the correct column if one - -- was removed above and no primary key currently exists. - IF NOT EXISTS ( - SELECT 1 - FROM information_schema.table_constraints - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND constraint_type = 'PRIMARY KEY' - ) THEN - ALTER TABLE permissions - ADD PRIMARY KEY (personal_id); - END IF; - END IF; - - IF NOT EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'type' - ) THEN - ALTER TABLE permissions - ADD COLUMN type VARCHAR(50); - END IF; - - IF NOT EXISTS ( - SELECT 1 - FROM information_schema.columns - WHERE table_schema = 'public' - AND table_name = 'permissions' - AND column_name = 'authorisation' - ) THEN - ALTER TABLE permissions - ADD COLUMN authorisation VARCHAR(50); - END IF; - END - $$; - `); - - // ON CONFLICT(personal_id) requires a unique/exclusion constraint - // that PostgreSQL can use for conflict inference. A unique index - // permits multiple NULL values, so this remains safe for any legacy - // permission rows that do not have a personal_id yet. - await pool.query(` - CREATE UNIQUE INDEX IF NOT EXISTS - permissions_personal_id_unique - ON permissions(personal_id) - `); - + )`, + + // 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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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 + )`, + + // 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) + )` + ]; + + let index = 0; + + function runNext() { + if (index >= createQueries.length) { + console.log('โœ… All tables created'); + resolve(); + return; + } + + const tableName = createQueries[index].split('TABLE')[1].split('(')[0].trim().replace('IF NOT EXISTS', '').trim(); + console.log(`Creating table: ${tableName}...`); + + database.database.run(createQueries[index], [], (err) => { + if (err) { + console.error(`Error creating table: ${err.message}`); + reject(err); + return; + } + console.log(`โœ… Created table: ${tableName}`); + index++; + runNext(); + }); + } + + runNext(); + }); +} + +function createIndexes() { + return new Promise((resolve, reject) => { + console.log('๐Ÿ“Š Creating indexes...'); + + const indexQueries = [ + 'CREATE INDEX IF NOT EXISTS idx_product_store ON product(store_id)', + 'CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id)', + 'CREATE INDEX IF NOT EXISTS idx_order_client ON "order"(client_id)', + 'CREATE INDEX IF NOT EXISTS idx_order_store ON "order"(store_id)', + 'CREATE INDEX IF NOT EXISTS idx_order_date ON "order"(order_date)', + 'CREATE INDEX IF NOT EXISTS idx_review_client ON review(client_id)', + 'CREATE INDEX IF NOT EXISTS idx_review_product ON review(product_code)', + 'CREATE INDEX IF NOT EXISTS idx_request_client ON request(client_id)', + 'CREATE INDEX IF NOT EXISTS idx_request_store ON request(store_id)', + 'CREATE INDEX IF NOT EXISTS idx_refund_order ON refund(order_num)', + 'CREATE INDEX IF NOT EXISTS idx_refund_status ON refund(status)', + 'CREATE INDEX IF NOT EXISTS idx_personal_email ON personal(email)', + 'CREATE INDEX IF NOT EXISTS idx_client_email ON client(email)', + 'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)', + 'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)', + 'CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id)', + 'CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action)', + 'CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at)', + 'CREATE INDEX IF NOT EXISTS idx_delivery_client ON delivery_address(client_id)', + 'CREATE INDEX IF NOT EXISTS idx_works_in_store_personal ON works_in_store(personal_id)', + 'CREATE INDEX IF NOT EXISTS idx_works_in_store_store ON works_in_store(store_id)' + ]; + + let index = 0; + + function runNext() { + if (index >= indexQueries.length) { + console.log('โœ… Indexes created'); + resolve(); + return; + } + + database.database.run(indexQueries[index], [], (err) => { + if (err) { + console.log(`โš ๏ธ Index creation warning for ${indexQueries[index].substring(0, 50)}...: ${err.message}`); + } + index++; + runNext(); + }); + } + + runNext(); + }); +} + +function insertInitialData() { + return new Promise((resolve, reject) => { + 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); + } + } + ); + + // Insert admin user + const adminId = 'admin_' + Date.now().toString().slice(-6); + const adminPassword = bcrypt.hashSync('Admin123!', 10); + + 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`, + [adminId, 'admin', 'admin@handcraft.com', adminPassword, 'admin', 1], + (err) => { + if (err) { + console.error('Error inserting admin user:', err.message); + } else { + console.log('โœ… Admin user created'); + } + } + ); + + // Insert default roles const roles = [ - ['admin', 'System administrator'], - ['store_owner', 'Store owner'], - ['store_employee', 'Store employee'], - ['client', 'Registered client'], - ['guest', 'Unregistered guest'] + { name: 'admin', description: 'System administrator' }, + { name: 'store_owner', description: 'Store owner' }, + { name: 'store_employee', description: 'Store employee' }, + { name: 'client', description: 'Registered client' }, + { name: 'guest', description: 'Unregistered guest' } ]; - for (const [name, description] of roles) { - await pool.query( - 'INSERT INTO roles(name, description) VALUES($1,$2) ON CONFLICT(name) DO NOTHING', - [name, description] + let rolesInserted = 0; + + roles.forEach(role => { + database.database.run( + `INSERT INTO roles (name, description) + VALUES ($1, $2) + ON CONFLICT DO NOTHING`, + [role.name, role.description], + (err) => { + if (err) { + console.error(`Error inserting role ${role.name}:`, err.message); + } + rolesInserted++; + if (rolesInserted === roles.length) { + console.log('โœ… Roles inserted'); + + // Check if General category exists + database.ensureGeneralCategory((err) => { + if (err) { + console.error('Error ensuring General category:', err.message); + } else { + console.log('โœ… General category exists'); + } + resolve(); + }); + } + } ); - } - - const hash = bcrypt.hashSync('Admin123!', 10); - await pool.query( - `INSERT INTO users(id, username, email, password, user_type, force_password_change) - VALUES('000000','admin','admin@handcraft.com',$1,'admin',TRUE) ON CONFLICT(id) DO NOTHING`, - [hash] - ); - await pool.query( - `INSERT INTO personal(id, first_name, last_name, ssn, email, password) - VALUES('000000','Admin','User','0000000000000','admin@handcraft.com',$1) ON CONFLICT(id) DO NOTHING`, - [hash] - ); - await pool.query(`INSERT INTO boss(boss_id) VALUES('000000') ON CONFLICT(boss_id) DO NOTHING`); - await pool.query( - `INSERT INTO permissions(personal_id,type,authorisation) - VALUES('000000','ADMIN','full_access') ON CONFLICT(personal_id) DO NOTHING` - ); - await pool.query( - `INSERT INTO user_roles(user_id,role_id) - SELECT '000000', role_id FROM roles WHERE name='admin' ON CONFLICT DO NOTHING` - ); - console.log('โœ… PostgreSQL project schema was recreated successfully'); - }, - - close() { - return pool.end(); - }, - - getUserById(id, callback) { - dbQuery( - `SELECT u.*, - COALESCE(json_agg(json_build_object('name',r.name,'description',r.description)) - FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles - FROM users u - LEFT JOIN user_roles ur ON ur.user_id=u.id - LEFT JOIN roles r ON r.role_id=ur.role_id - WHERE u.id=$1 - GROUP BY u.id`, - [String(id)], - (err, result) => callback(err, result?.rows?.[0]) - ); - }, - - getUserByUsername(username, callback) { - dbQuery( - `SELECT u.*, - COALESCE(json_agg(json_build_object('name',r.name,'description',r.description)) - FILTER (WHERE r.role_id IS NOT NULL), '[]') AS roles - FROM users u - LEFT JOIN user_roles ur ON ur.user_id=u.id - LEFT JOIN roles r ON r.role_id=ur.role_id - WHERE u.username=$1 OR u.email=$1 - GROUP BY u.id - LIMIT 1`, - [username], - (err, result) => callback(err, result?.rows?.[0]) - ); - }, - - createUser(id, username, email, password, userType, callback) { - dbQuery( - `INSERT INTO users(id,username,email,password,user_type,force_password_change) - VALUES($1,$2,$3,$4,$5,FALSE) RETURNING id`, - [String(id), username, email, password, userType], - (err, result) => { - if (err) return callback(err); - const roleName = userType === 'client' ? 'client' : - userType === 'store_owner' ? 'store_owner' : - userType === 'store_employee' ? 'store_employee' : 'guest'; - dbQuery( - `INSERT INTO user_roles(user_id,role_id) - SELECT $1, role_id FROM roles WHERE name=$2`, - [String(id), roleName], - roleErr => callback(roleErr, String(id)) - ); - } - ); - }, - - createClient(data, callback) { - dbQuery( - `INSERT INTO client(first_name,last_name,email,password) - VALUES($1,$2,$3,$4) RETURNING client_id`, - [data.firstName || data.first_name || '', data.lastName || data.last_name || '', data.email, data.password], - (err, result) => callback(err, result?.rows?.[0]?.client_id) - ); - }, - - getClientByEmail(email, callback) { - dbQuery('SELECT * FROM client WHERE email=$1', [email], - (err, result) => callback(err, result?.rows?.[0])); - }, - - getClientById(id, callback) { - dbQuery('SELECT * FROM client WHERE client_id=$1', [id], - (err, result) => callback(err, result?.rows?.[0])); - }, - - getPersonalByEmail(email, callback) { - dbQuery('SELECT * FROM personal WHERE email=$1', [email], - (err, result) => callback(err, result?.rows?.[0])); - }, - - getPersonalById(id, callback) { - dbQuery('SELECT * FROM personal WHERE id=$1', [String(id)], - (err, result) => callback(err, result?.rows?.[0])); - }, - - verifyPassword(password, hash) { - try { return bcrypt.compareSync(password, hash); } catch { return false; } - }, - - verifyClientPassword(password, hash, callback) { - bcrypt.compare(password, hash, callback); - }, - - logAudit(userId, action, resourceType, resourceId, details, ipAddress) { - dbQuery( - `INSERT INTO audit_log(user_id,action,resource_type,resource_id,details,ip_address) - VALUES($1,$2,$3,$4,$5,$6)`, - [userId == null ? null : String(userId), action, resourceType, - resourceId == null ? null : String(resourceId), details, ipAddress], - () => {} - ); - }, - - getProducts(categoryId, searchTerm, callback) { - const params = []; - const where = []; - if (categoryId) { - params.push(categoryId); - where.push(`p.category_id=$${params.length}`); - } - if (searchTerm) { - params.push(`%${searchTerm}%`); - where.push(`(p.description ILIKE $${params.length} OR p.code ILIKE $${params.length})`); - } - const sql = `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id - FROM product p - LEFT JOIN category c ON c.id=p.category_id - ${where.length ? 'WHERE ' + where.join(' AND ') : ''} - ORDER BY p.code`; - dbQuery(sql, params, (err, result) => callback(err, result?.rows || [])); - }, - - getProductById(id, callback) { - dbQuery( - `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id - FROM product p - LEFT JOIN category c ON c.id=p.category_id - WHERE p.code=$1 LIMIT 1`, - [String(id)], - (err,result)=>callback(err,result?.rows?.[0]) - ); - }, - - getProductByCode(code, callback) { - dbQuery( - `SELECT p.*, c.name AS category_name, LEFT(p.code,3) AS store_id - FROM product p - LEFT JOIN category c ON c.id=p.category_id - WHERE p.code=$1`, - [code], - (err,result)=>callback(err,result?.rows?.[0]) - ); - }, - - addProduct(personalId, data, callback) { - const storeId = data.store_id || data.storeId || String(data.code || '').slice(0,3); - if (!data.category_id) { - return callback(new Error('category_id is required because product.category_id is NOT NULL')); - } - dbQuery( - `INSERT INTO product(code,price,availability,weight,width_x_length_x_depth, - aprox_production_time,description,category_id) - VALUES($1,$2,$3,$4,$5,$6,$7,$8) RETURNING code`, - [ - data.code, data.price, data.availability ?? 0, data.weight, - data.width_x_length_x_depth || data.dimensions || '', - data.aprox_production_time ?? data.production_time ?? 0, - data.description, data.category_id - ], - (err,result)=>{ - if (err) return callback(err); - dbQuery( - `INSERT INTO sells(product_code,store_ID,discount) - VALUES($1,$2,$3) - ON CONFLICT(product_code,store_ID) - DO UPDATE SET discount=EXCLUDED.discount`, - [data.code,storeId,data.discount || 0], - e => callback(e, data.code) - ); - } - ); - }, - - updateProduct(personalId, data, callback) { - const fields = []; - const params = []; - const allowed = [ - ['price','price'], ['availability','availability'], ['weight','weight'], - ['width_x_length_x_depth','width_x_length_x_depth'], - ['dimensions','width_x_length_x_depth'], - ['aprox_production_time','aprox_production_time'], - ['production_time','aprox_production_time'], - ['description','description'], ['category_id','category_id'] - ]; - for (const [input,col] of allowed) { - if (data[input] !== undefined) { - params.push(data[input]); - fields.push(`${col}=$${params.length}`); - } - } - if (!fields.length) return callback(null,0); - params.push(data.code); - dbQuery(`UPDATE product SET ${fields.join(', ')} WHERE code=$${params.length}`, params, - (err,result)=>callback(err,result?.rowCount || 0)); - }, - - deleteProduct(productCode, storeId, personalId, callback) { - dbQuery('DELETE FROM product WHERE code=$1 AND LEFT(code,3)=$2', [productCode,storeId], - (err)=>callback(err)); - }, - - createCategory(data, callback) { - const parent = data.parent_category_id ?? data.parentCategoryId ?? data.parent_id; - if (parent === undefined || parent === null || parent === '') { - return callback(new Error('parent_category_id is required by the project schema')); - } - dbQuery( - `INSERT INTO category(name,parent_category_id) VALUES($1,$2) RETURNING id,name,parent_category_id`, - [data.name, parent], - (err,result)=>callback(err,result?.rows?.[0]) - ); - }, - - getCategories(callback) { - dbQuery('SELECT * FROM category ORDER BY name', [], (err,result)=>callback(err,result?.rows||[])); - }, - - getCategoriesWithParents(callback) { - dbQuery( - `SELECT c.*,p.name AS parent_name - FROM category c LEFT JOIN category p ON p.id=c.parent_category_id - ORDER BY c.name`, - [], (err,result)=>callback(err,result?.rows||[]) - ); - }, - - getStores(callback) { - dbQuery('SELECT * FROM store ORDER BY name', [], (err,result)=>callback(err,result?.rows||[])); - }, - - createOrderNew(data, callback) { - const items = data.items || data.products || data.order_items || []; - const storeId = data.store_id || data.storeId || (items[0]?.code ? String(items[0].code).slice(0,3) : null); - if (!storeId) return callback(new Error('Store ID is required')); - const year = String(new Date().getFullYear()).slice(-3); - - dbQuery( - `SELECT COUNT(*)::int AS n - FROM "order" - WHERE LEFT(order_num,3)=$1 - AND SUBSTRING(order_num FROM 4 FOR 3)=$2`, - [storeId, year], - (countErr,countResult)=>{ - if (countErr) return callback(countErr); - const seq=Number(countResult.rows[0].n)+1; - const orderNum=`${storeId}${year}${String(seq).padStart(5,'0')}`; - dbQuery( - `INSERT INTO "order"(order_num,client_ID,status,last_date_mod,payment_method,discount) - VALUES($1,$2,$3,CURRENT_TIMESTAMP,$4,$5) RETURNING order_num`, - [orderNum,data.client_id,data.status||'placed order',data.payment_method||'cash',data.discount||0], - (err,result)=>{ - if(err) return callback(err); - let pending=items.length; - if(!pending) return callback(null,orderNum); - let firstErr=null; - for(const item of items){ - dbQuery( - `INSERT INTO includes(order_num,product_code,quantity) VALUES($1,$2,$3)`, - [orderNum,item.product_code||item.code,item.quantity||1], - e=>{ if(e && !firstErr) firstErr=e; if(--pending===0) callback(firstErr,firstErr?undefined:orderNum); } - ); - } - } - ); - } - ); - }, - - getOrdersByClient(clientId, callback) { - dbQuery( - `SELECT o.*, LEFT(o.order_num,3) AS store_id, - o.last_date_mod AS order_date, - COALESCE(json_agg(json_build_object('product_code',i.product_code,'quantity',i.quantity,'price',p.price)) - FILTER(WHERE i.product_code IS NOT NULL),'[]') AS items - FROM "order" o - LEFT JOIN includes i ON i.order_num=o.order_num - LEFT JOIN product p ON p.code=i.product_code - WHERE o.client_ID=$1 - GROUP BY o.order_num - ORDER BY o.last_date_mod DESC`, - [clientId],(err,result)=>callback(err,result?.rows||[]) - ); - }, - - createReviewNew(data, callback) { - dbQuery( - `INSERT INTO review(order_num,comment,rating,last_mod_date) - VALUES($1,$2,$3,CURRENT_TIMESTAMP) - RETURNING order_num`, - [data.order_num,data.comment||null,data.rating], - (err,result)=>callback(err,result?.rows?.[0]?.order_num) - ); - }, - - createRequest(data, callback) { - dbQuery( - `INSERT INTO request(request_num,date_and_time,problem,notes_of_communication,customer_satisfaction) - VALUES($1,$2,$3,$4,0) RETURNING request_num`, - [data.request_num,data.date_and_time,data.problem,data.notes_of_communication||null], - (err,result)=>{ - if (err) return callback(err); - dbQuery( - `INSERT INTO for_store(request_num,store_ID) VALUES($1,$2)`, - [data.request_num,data.store_id], - storeErr=>{ - if (storeErr) return callback(storeErr); - if (data.order_num) { - dbQuery( - `INSERT INTO makes_request(client_ID,order_num) VALUES($1,$2)`, - [data.client_id,data.order_num], - e=>callback(e,data.request_num) - ); - } else { - callback(null,data.request_num); - } - } - ); - } - ); - }, - - createRefund(data, callback) { - const suppliedId = data.refund_id; - const query = suppliedId - ? `INSERT INTO refund(refund_id,order_num,reason,amount,status) VALUES($1,$2,$3,$4,$5) RETURNING refund_id` - : `INSERT INTO refund(order_num,reason,amount,status) VALUES($1,$2,$3,$4) RETURNING refund_id`; - const params = suppliedId - ? [Number.parseInt(String(suppliedId),10) || undefined,data.order_num,data.reason||null,data.amount,data.status||'requested refund'] - : [data.order_num,data.reason||null,data.amount,data.status||'requested refund']; - dbQuery(query,params,(err,result)=>callback(err,result?.rows?.[0]?.refund_id)); - }, - - getAllUsers(callback) { - dbQuery(`SELECT u.*,COALESCE(json_agg(r.name) FILTER(WHERE r.role_id IS NOT NULL),'[]') roles - FROM users u LEFT JOIN user_roles ur ON ur.user_id=u.id - LEFT JOIN roles r ON r.role_id=ur.role_id GROUP BY u.id ORDER BY u.created_at DESC`, - [],(err,result)=>callback(err,result?.rows||[])); - }, - - getAllOrders(callback) { - dbQuery(`SELECT o.*, LEFT(o.order_num,3) AS store_id, o.last_date_mod AS order_date, - c.first_name,c.last_name,c.email - FROM "order" o - LEFT JOIN client c ON c.client_id=o.client_ID - ORDER BY o.last_date_mod DESC`, - [],(err,result)=>callback(err,result?.rows||[])); - }, - - getStoreProducts(storeId, callback) { - dbQuery(`SELECT p.*,c.name AS category_name,LEFT(p.code,3) AS store_id,s.discount - FROM product p - LEFT JOIN category c ON c.id=p.category_id - LEFT JOIN sells s ON s.product_code=p.code AND s.store_ID=$1 - WHERE LEFT(p.code,3)=$1 OR EXISTS(SELECT 1 FROM sells sx WHERE sx.product_code=p.code AND sx.store_ID=$1) - ORDER BY p.code`,[storeId],(err,result)=>callback(err,result?.rows||[])); - }, - - getStoreOrders(storeId, callback) { - dbQuery(`SELECT o.*,LEFT(o.order_num,3) AS store_id,o.last_date_mod AS order_date,c.first_name,c.last_name - FROM "order" o - LEFT JOIN client c ON c.client_id=o.client_ID - WHERE LEFT(o.order_num,3)=$1 ORDER BY o.last_date_mod DESC`,[storeId], - (err,result)=>callback(err,result?.rows||[])); - }, - - getStoreEmployees(storeId, callback) { - dbQuery(`SELECT p.*,e.date_of_hire,per.type,per.authorisation - FROM personal p JOIN works_in_store w ON w.personal_id=p.id - LEFT JOIN employees e ON e.employee_id=p.id - LEFT JOIN permissions per ON per.personal_id=p.id - WHERE w.store_ID=$1 ORDER BY p.last_name,p.first_name`, - [storeId],(err,result)=>callback(err,result?.rows||[])); - }, - - getStoreReports(storeId, callback) { - dbQuery( - `SELECT date, store_id, overall_profit, sales_trend, marketing_growth, owner_signature - FROM report - WHERE store_id = $1 - ORDER BY date DESC`, - [storeId], - (err, result) => callback(err, result?.rows || []) - ); - }, - - getStoreStats(storeId, callback) { - const sql = ` - SELECT - (SELECT COUNT(DISTINCT product_code) - FROM sells - WHERE store_ID = $1)::int AS product_count, - - (SELECT COUNT(DISTINCT o.order_num) - FROM sells se - JOIN includes i ON i.product_code = se.product_code - JOIN "order" o ON o.order_num = i.order_num - WHERE se.store_ID = $1)::int AS order_count, - - (SELECT COALESCE(SUM( - i.quantity * p.price - * (1 - COALESCE(o.discount, 0) / 100.0) - ), 0) - FROM sells se - JOIN includes i ON i.product_code = se.product_code - JOIN "order" o ON o.order_num = i.order_num - JOIN product p ON p.code = i.product_code - WHERE se.store_ID = $1) AS revenue, - - (SELECT COUNT(*) - FROM works_in_store - WHERE store_ID = $1)::int AS employee_count, - - (SELECT COUNT(*) - FROM for_store - WHERE store_ID = $1)::int AS request_count, - - (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 - `; - - dbQuery(sql, [storeId], (err, result) => { - callback(err, result?.rows?.[0] || {}); - }); - }, - - generateStoreReport(storeId, startDate, endDate, type, period, ownerSignature, callback) { - dbQuery( - `WITH sales AS ( - SELECT COALESCE(SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ), 0) AS revenue - FROM sells se - JOIN product p ON p.code = se.product_code - JOIN includes i ON i.product_code = se.product_code - JOIN "order" o ON o.order_num = i.order_num - WHERE se.store_ID = $1 - AND o.last_date_mod >= $2::timestamp - AND o.last_date_mod < ($3::timestamp + INTERVAL '1 day') - ), - refunds AS ( - SELECT COALESCE(SUM(rf.amount), 0) AS refund_total - FROM refund rf - JOIN "order" o ON o.order_num = rf.order_num - WHERE LEFT(o.order_num, 3) = $1 - AND o.last_date_mod >= $2::timestamp - AND o.last_date_mod < ($3::timestamp + INTERVAL '1 day') - AND rf.status IN ('approved', 'processed') - ) - SELECT sales.revenue, refunds.refund_total, - sales.revenue - refunds.refund_total AS net_profit - FROM sales CROSS JOIN refunds`, - [storeId, startDate, endDate], - (err, result) => { - if (err) return callback(err); - - const row = result.rows[0] || {}; - const revenue = Number(row.revenue || 0); - const refundTotal = Number(row.refund_total || 0); - const netProfit = Number(row.net_profit || 0); - - dbQuery( - `SELECT COALESCE(SUM( - p.price * i.quantity - * (1 - COALESCE(o.discount, 0) / 100.0) - ), 0) AS previous_revenue - FROM sells se - JOIN product p ON p.code = se.product_code - JOIN includes i ON i.product_code = se.product_code - JOIN "order" o ON o.order_num = i.order_num - WHERE se.store_ID = $1 - AND o.last_date_mod >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' - AND o.last_date_mod < DATE_TRUNC('month', CURRENT_DATE)`, - [storeId], - (previousErr, previousResult) => { - if (previousErr) return callback(previousErr); - - const previousRevenue = Number(previousResult.rows[0]?.previous_revenue || 0); - const growth = previousRevenue === 0 - ? (revenue > 0 ? 100 : 0) - : ((revenue - previousRevenue) / previousRevenue) * 100; - - dbQuery( - `INSERT INTO report - (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) - VALUES - (CURRENT_TIMESTAMP, $1, $2, $3, $4, $5) - RETURNING date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature`, - [ - storeId, - Math.max(0, netProfit), - `Revenue ${revenue.toFixed(2)}; Refunds ${refundTotal.toFixed(2)}`.slice(0, 100), - `${growth.toFixed(2)}%`, - ownerSignature || 'Not signed yet' - ], - (insertErr, insertResult) => { - if (insertErr) return callback(insertErr); - - const report = insertResult.rows[0]; - - dbQuery( - `INSERT INTO monthly_profit - (report_date, store_ID, month_and_year, profit) - VALUES - ($1, $2, DATE_TRUNC('month', $3::timestamp)::DATE, $4) - ON CONFLICT (report_date, store_ID) - DO UPDATE SET - month_and_year = EXCLUDED.month_and_year, - profit = EXCLUDED.profit`, - [report.date, storeId, endDate, Math.max(0, netProfit)], - (monthlyErr) => { - if (monthlyErr) console.error('Warning inserting monthly profit:', monthlyErr); - - dbQuery( - `INSERT INTO exchanges_data - (report_date, store_ID, monthly_profit, date, sales, damages) - VALUES ($1, $2, $3, CURRENT_TIMESTAMP, $4, $5) - ON CONFLICT (report_date, store_ID) - DO UPDATE SET - monthly_profit = EXCLUDED.monthly_profit, - date = EXCLUDED.date, - sales = EXCLUDED.sales, - damages = EXCLUDED.damages`, - [report.date, storeId, Math.max(0, netProfit), revenue, -refundTotal], - (exchangeErr) => { - if (exchangeErr) console.error('Warning inserting exchange data:', exchangeErr); - callback(null, report); - } - ); - } - ); - } - ); - } - ); - } - ); - }, - - getEmployeeTasks(personalId, storeId, callback) { - dbQuery(`SELECT r.*,a.personal_id AS answered_by - FROM request r - JOIN for_store fs ON fs.request_num=r.request_num - LEFT JOIN answers a ON a.request_num=r.request_num - WHERE fs.store_ID=$1 AND (a.personal_id=$2 OR a.personal_id IS NULL) - ORDER BY r.date_and_time DESC`, - [storeId,personalId],(err,result)=>callback(err,result?.rows||[])); - }, - - getClientStats(clientId, callback) { - dbQuery(`SELECT - (SELECT COUNT(*) FROM "order" WHERE client_ID=$1)::int AS order_count, - (SELECT COUNT(*) FROM review r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS review_count, - (SELECT COUNT(*) FROM makes_request WHERE client_ID=$1)::int AS request_count, - (SELECT COUNT(*) FROM refund r JOIN "order" o ON o.order_num=r.order_num WHERE o.client_ID=$1)::int AS refund_count`, - [clientId],(err,result)=>callback(err,result?.rows?.[0]||{})); - } -}; - - - -// PostgreSQL schema initialization. -// The schema is based on the supplied Pasted markdown.md. A few invalid PostgreSQL -// declarations in the original paste are corrected here (for example DECIMMAL, -// PARTIAL KEY, and composite-key column-name typos). The existing HTTP layer also -// uses only the project schema plus the four authentication/audit support tables. -(async () => { + }); + }); +} + +// Initialize database on startup +(async function() { try { - await database.initializeDatabase(); - await database.installReportFunctions(); - await database.installTriggersAndViews(); + await initializeDatabase(); console.log('โœ… Database initialization completed'); } catch (err) { console.error('โŒ Database initialization failed:', err); - process.exitCode = 1; } })(); @@ -2777,46 +895,4 @@ serveStaticFile(res, 'verify-2fa.html', 'text/html'); } else if (pathname === '/admin.html') { - // Check if user is authenticated - const cookies = parseCookies(req); - const sessionId = cookies.sessionId; - - if (!sessionId || !sessions.has(sessionId)) { - res.writeHead(302, { 'Location': '/login.html' }); - res.end(); - return; - } - - // Get user from session - const userId = sessions.get(sessionId); - - // Check if this is the admin user - if (userId !== '000000') { - // Not admin, redirect to appropriate dashboard - if (userId.startsWith('client_')) { - res.writeHead(302, { 'Location': '/client-dashboard.html' }); - } else if (userId.startsWith('personal_')) { - // Check if store owner or employee - const personalId = userId.replace('personal_', ''); - - database.database.get( - 'SELECT boss_id FROM boss WHERE boss_id = $1', - [personalId], - (err, boss) => { - if (boss) { - res.writeHead(302, { 'Location': '/store-owner.html' }); - } else { - res.writeHead(302, { 'Location': '/store-employee.html' }); - } - res.end(); - } - ); - return; - } else { - res.writeHead(302, { 'Location': '/dashboard.html' }); - } - res.end(); - return; - } - serveStaticFile(res, 'admin.html', 'text/html'); } else if (pathname === '/store-owner.html') { @@ -2970,5 +1046,4 @@ const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/; - if (!emailRegex.test(formData.ownerEmail)) { res.writeHead(400, { 'Content-Type': 'application/json' }); @@ -3052,5 +1127,4 @@ // Next store number is max + 1, starting from 1 if no stores exist let nextStoreNumber = 1; - if (result && result.max_store_num) { // Extract numeric part from store_id (format: XXX) @@ -3435,5 +1509,4 @@ database.database.run('ROLLBACK'); console.error('Error inserting personal:', err); - if (err.code === '23505') { res.writeHead(400, { 'Content-Type': 'application/json' }); @@ -3451,6 +1524,6 @@ // Insert into boss table (boss_id is VARCHAR, references personal.id) database.database.run( - 'INSERT INTO boss (boss_id) VALUES ($1)', - [tempStoreData.personalId], + 'INSERT INTO boss (boss_id, signature) VALUES ($1, $2)', + [tempStoreData.personalId, tempStoreData.signature], (err) => { if (err) { @@ -3484,54 +1557,36 @@ } - // Also create entry in users table for login with force_password_change = 1 - database.database.run( - 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ($1, $2, $3, $4, $5, $6)', - [ - tempStoreData.personalId, - `${tempStoreData.ownerFirstName} ${tempStoreData.ownerLastName}`, - tempStoreData.ownerEmail, - bcrypt.hashSync(tempStoreData.password, 10), - 'store_owner', - 1 - ], - (err) => { - if (err) { - console.error('Error creating user entry for store owner:', err); - } - - database.database.run('COMMIT', (commitErr) => { - if (commitErr) { - console.error('Error committing transaction:', commitErr); - database.database.run('ROLLBACK'); - res.writeHead(500, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Error completing registration' })); - return; - } - - tempStoreRegistrations.delete(code); - verificationCodes.delete(email); - - console.log(`โœ… Store registration completed successfully:`); - console.log(` Store ID: ${tempStoreData.storeId}`); - console.log(` Store Name: ${tempStoreData.storeName}`); - console.log(` Personal ID: ${tempStoreData.personalId}`); - console.log(` Owner: ${tempStoreData.ownerFirstName} ${tempStoreData.ownerLastName}`); - - database.logAudit(tempStoreData.personalId, 'STORE_REGISTER_SUCCESS', 'store', tempStoreData.storeId, `Store registered: ${tempStoreData.storeName}`, ipAddress); - - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - message: 'Store registration successful! You can now login.', - storeId: tempStoreData.storeId, - storeIdPadded: tempStoreData.storeIdPadded, - storeName: tempStoreData.storeName, - personalId: tempStoreData.personalId, - userType: 'store_owner', - redirectTo: 'login.html' - })); - }); + database.database.run('COMMIT', (commitErr) => { + if (commitErr) { + console.error('Error committing transaction:', commitErr); + database.database.run('ROLLBACK'); + res.writeHead(500, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Error completing registration' })); + return; } - ); + + tempStoreRegistrations.delete(code); + verificationCodes.delete(email); + + console.log(`โœ… Store registration completed successfully:`); + console.log(` Store ID: ${tempStoreData.storeId}`); + console.log(` Store Name: ${tempStoreData.storeName}`); + console.log(` Personal ID: ${tempStoreData.personalId}`); + console.log(` Owner: ${tempStoreData.ownerFirstName} ${tempStoreData.ownerLastName}`); + + database.logAudit(tempStoreData.personalId, 'STORE_REGISTER_SUCCESS', 'store', tempStoreData.storeId, `Store registered: ${tempStoreData.storeName}`, ipAddress); + + res.writeHead(200, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ + success: true, + message: 'Store registration successful! You can now login.', + storeId: tempStoreData.storeId, + storeIdPadded: tempStoreData.storeIdPadded, + storeName: tempStoreData.storeName, + personalId: tempStoreData.personalId, + userType: 'store_owner', + redirectTo: 'login.html' + })); + }); } ); @@ -3613,5 +1668,4 @@ } else { const userId = 'user_' + Date.now().toString().slice(-8); - database.createUser(userId, tempUserData.username, tempUserData.email, tempUserData.password, tempUserData.userType, (err, userId) => { if (err) { @@ -3646,69 +1700,5 @@ req.on('end', () => { const { email, password } = JSON.parse(body); - console.log(`๐Ÿ” Login attempt for email: ${email}`); - - // First check if it's the admin user (special case) - if (email === 'admin@handcraft.com') { - database.getUserByUsername('admin', (err, adminUser) => { - if (err || !adminUser) { - console.error('Admin user not found'); - database.logAudit(null, 'LOGIN_FAILED', 'auth', null, `Admin login failed - user not found`, ipAddress); - res.writeHead(401, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Invalid email or password' })); - return; - } - - if (database.verifyPassword(password, adminUser.password)) { - const isFirstTimeLogin = adminUser.force_password_change === 1; - - const twoFACode = generateVerificationCode(); - verificationCodes.set(adminUser.email, { - code: twoFACode, - timestamp: Date.now(), - userId: adminUser.id, - isFirstTimeLogin: isFirstTimeLogin, - userType: 'admin', - needsPasswordChange: isFirstTimeLogin - }); - - console.log(`โฐ Generated 2FA code for admin ${adminUser.email}`); - - send2FACode(adminUser.email, twoFACode) - .then(() => { - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - message: 'Two-factor authentication code sent to your email', - requires2FA: true, - email: adminUser.email, - username: adminUser.username, - isFirstTimeLogin: isFirstTimeLogin, - userType: 'admin' - })); - }) - .catch(error => { - console.error('Error sending 2FA email:', error); - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - message: 'Two-factor authentication required', - requires2FA: true, - email: adminUser.email, - username: adminUser.username, - isFirstTimeLogin: isFirstTimeLogin, - userType: 'admin', - developmentCode: twoFACode - })); - }); - } else { - database.logAudit(adminUser.id, 'LOGIN_FAILED', 'auth', adminUser.id.toString(), 'Invalid password for admin', ipAddress); - res.writeHead(401, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Invalid email or password' })); - } - }); - - return; - } // First check if it's a client @@ -3720,5 +1710,4 @@ if (client) { console.log(`๐Ÿ” Found client: ${client.email}`); - if (!client.password) { console.log('โŒ Client has no password set'); @@ -3746,5 +1735,4 @@ console.log(`โœ… Client login successful. Session: ${sessionId}, User: client_${clientId}`); - database.logAudit(clientId, 'LOGIN_SUCCESS', 'auth', typeof clientId === 'string' ? clientId : String(clientId), @@ -3769,5 +1757,4 @@ })); }); - return; } @@ -3781,5 +1768,4 @@ if (personal) { console.log(`๐Ÿ” Found personal user: ${personal.email}`); - if (!personal.password) { console.log('โŒ Personal has no password set'); @@ -3800,5 +1786,5 @@ // Check if this is a boss (store owner) database.database.get( - 'SELECT boss_id FROM boss WHERE boss_id = $1', + 'SELECT boss_id FROM boss WHERE boss_id = ?', [personal.id], (err, boss) => { @@ -3811,5 +1797,5 @@ // Check if first time login from users table database.database.get( - 'SELECT force_password_change FROM users WHERE email = $1', + 'SELECT force_password_change FROM users WHERE email = ?', [email], (err, user) => { @@ -3855,5 +1841,4 @@ } ); - return; } @@ -3861,5 +1846,5 @@ // Check if this is an employee database.database.get( - 'SELECT employee_id FROM employees WHERE employee_id = $1', + 'SELECT employee_id FROM employees WHERE employee_id = ?', [personal.id], (err, employee) => { @@ -3871,5 +1856,5 @@ // This is an employee database.database.get( - 'SELECT force_password_change FROM users WHERE email = $1', + 'SELECT force_password_change FROM users WHERE email = ?', [email], (err, user) => { @@ -3915,5 +1900,4 @@ } ); - return; } @@ -3922,5 +1906,5 @@ // Treat as regular user database.database.get( - 'SELECT * FROM users WHERE email = $1', + 'SELECT * FROM users WHERE email = ?', [email], (err, user) => { @@ -3935,5 +1919,6 @@ if (database.verifyPassword(password, userByUsername.password)) { - const isFirstTimeLogin = userByUsername.force_password_change === 1; + const isAdminUser = userByUsername.username === 'admin'; + const isFirstTimeLogin = isAdminUser && userByUsername.force_password_change === 1; const twoFACode = generateVerificationCode(); @@ -3943,5 +1928,5 @@ userId: userByUsername.id, isFirstTimeLogin: isFirstTimeLogin, - userType: userByUsername.user_type, + userType: isAdminUser ? 'admin' : userByUsername.user_type, needsPasswordChange: isFirstTimeLogin }); @@ -3957,5 +1942,5 @@ username: userByUsername.username, isFirstTimeLogin: isFirstTimeLogin, - userType: userByUsername.user_type + userType: isAdminUser ? 'admin' : userByUsername.user_type })); }) @@ -3970,5 +1955,5 @@ username: userByUsername.username, isFirstTimeLogin: isFirstTimeLogin, - userType: userByUsername.user_type, + userType: isAdminUser ? 'admin' : userByUsername.user_type, developmentCode: twoFACode })); @@ -3980,10 +1965,10 @@ } }); - return; } if (database.verifyPassword(password, user.password)) { - const isFirstTimeLogin = user.force_password_change === 1; + const isAdminUser = user.username === 'admin'; + const isFirstTimeLogin = isAdminUser && user.force_password_change === 1; const twoFACode = generateVerificationCode(); @@ -3993,5 +1978,5 @@ userId: user.id, isFirstTimeLogin: isFirstTimeLogin, - userType: user.user_type, + userType: isAdminUser ? 'admin' : user.user_type, needsPasswordChange: isFirstTimeLogin }); @@ -4007,5 +1992,5 @@ username: user.username, isFirstTimeLogin: isFirstTimeLogin, - userType: user.user_type + userType: isAdminUser ? 'admin' : user.user_type })); }) @@ -4020,5 +2005,5 @@ username: user.username, isFirstTimeLogin: isFirstTimeLogin, - userType: user.user_type, + userType: isAdminUser ? 'admin' : user.user_type, developmentCode: twoFACode })); @@ -4036,5 +2021,4 @@ ); }); - return; } @@ -4080,6 +2064,6 @@ timestamp: Date.now(), userId: userByUsername.id, - isFirstTimeLogin: userByUsername.force_password_change === 1, - needsPasswordChange: userByUsername.force_password_change === 1, + isAdmin: userByUsername.username === 'admin' && userByUsername.force_password_change === 1, + needsPasswordChange: userByUsername.username === 'admin' && userByUsername.force_password_change === 1, userType: userByUsername.user_type }); @@ -4107,5 +2091,4 @@ }); }); - return; } @@ -4116,6 +2099,6 @@ timestamp: Date.now(), userId: user.id, - isFirstTimeLogin: user.force_password_change === 1, - needsPasswordChange: user.force_password_change === 1, + isAdmin: user.username === 'admin' && user.force_password_change === 1, + needsPasswordChange: user.username === 'admin' && user.force_password_change === 1, userType: user.user_type }); @@ -4186,16 +2169,4 @@ verificationCodes.delete(email); - // Determine redirect based on user type - let redirectTo = 'change-password.html?forced=true'; - if (verificationData.userType === 'store_owner') { - redirectTo = 'change-password.html?forced=true&redirect=store-owner.html'; - } else if (verificationData.userType === 'store_employee') { - redirectTo = 'change-password.html?forced=true&redirect=store-employee.html'; - } else if (verificationData.userType === 'admin') { - redirectTo = 'change-password.html?forced=true&redirect=admin.html'; - } else if (verificationData.userType === 'client') { - redirectTo = 'change-password.html?forced=true&redirect=client-dashboard.html'; - } - res.writeHead(200, { 'Content-Type': 'application/json', @@ -4208,7 +2179,6 @@ requiresPasswordChange: true, userType: verificationData.userType, - redirectTo: redirectTo + redirectTo: 'change-password.html?forced=true' })); - return; } @@ -4233,5 +2203,4 @@ // Determine redirect based on user type let redirectTo = ''; - switch(verificationData.userType) { case 'client': @@ -4284,5 +2253,4 @@ 'Set-Cookie': 'sessionId=; HttpOnly; Path=/; Expires=Thu, 01 Jan 1970 00:00:00 GMT; SameSite=Strict' }); - res.end(JSON.stringify({ success: true, message: 'Successfully logged out' })); } @@ -4294,96 +2262,21 @@ if (tempAdminSessions.has(sessionId)) { - // This is a temporary session (password change required) - // Get user info to determine type - database.getUserById(userId, (err, user) => { - if (err || !user) { - // Check if it's a personal user - database.getPersonalById(userId, (err, personal) => { - if (err || !personal) { - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - user: { - id: userId, - username: 'admin', - userType: 'admin', - needsPasswordChange: true - }, - isTempSession: true - })); - } else { - // Personal user (store owner/employee) - database.database.get( - 'SELECT boss_id FROM boss WHERE boss_id = $1', - [userId], - (err, boss) => { - let userType = 'store_employee'; - if (boss) { - userType = 'store_owner'; - } - - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - user: { - id: personal.id, - firstName: personal.first_name, - lastName: personal.last_name, - email: personal.email, - userType: userType, - needsPasswordChange: true - }, - isTempSession: true - })); - } - ); - } - }); - } else { - // Regular user (admin) - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - user: { - id: user.id, - username: user.username, - email: user.email, - userType: user.user_type || 'admin', - needsPasswordChange: true - }, - isTempSession: true - })); - } - }); - - return; - } - - // Regular session + res.writeHead(200, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ + success: true, + user: { + id: userId, + username: 'admin', + needsPasswordChange: true + }, + isTempSession: true + })); + return; + } + const userIdStr = String(userId); - if (userIdStr === '000000') { - // Admin user - database.getUserById(userIdStr, (err, user) => { - if (err || !user) { - res.writeHead(404, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'User not found' })); - } else { - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - user: { - id: user.id, - username: user.username, - email: user.email, - userType: 'admin' - } - })); - } - }); - } - else if (userIdStr.startsWith('client_')) { + if (userIdStr.startsWith('client_')) { const clientId = parseInt(userIdStr.replace('client_', '')); - database.getClientById(clientId, (err, client) => { if (err || !client) { @@ -4407,5 +2300,4 @@ else if (userIdStr.startsWith('personal_')) { const personalId = userIdStr.replace('personal_', ''); - database.getPersonalById(personalId, (err, personal) => { if (err || !personal) { @@ -4578,5 +2470,4 @@ } 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({ @@ -4586,5 +2477,5 @@ id: category.id, name: category.name, - parent_id: category.parent_category_id, + parent_id: category.parent_id, description: category.description } @@ -4637,4 +2528,5 @@ req.on('end', () => { const orderData = JSON.parse(body); + const userIdStr = String(userId); @@ -4652,6 +2544,6 @@ database.database.get( - '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)', - [storeId, new Date().getFullYear().toString()], + 'SELECT COUNT(*) as order_count FROM "order" WHERE store_id = $1 AND EXTRACT(YEAR FROM order_date) = $2', + [storeId, new Date().getFullYear()], (err, result) => { if (err) { @@ -4662,5 +2554,5 @@ } - const orderCount = result ? result.order_count + 1 : 1; + const orderCount = result && result[0] ? parseInt(result[0].order_count) + 1 : 1; const orderNumPadded = orderCount.toString().padStart(5, '0'); @@ -4709,5 +2601,4 @@ if (userIdStr.startsWith('client_')) { const clientId = parseInt(userIdStr.replace('client_', '')); - database.getOrdersByClient(clientId, (err, orders) => { if (err) { @@ -4734,4 +2625,5 @@ req.on('end', () => { const reviewData = JSON.parse(body); + const userIdStr = String(userId); @@ -4766,4 +2658,5 @@ req.on('end', () => { const requestData = JSON.parse(body); + const userIdStr = String(userId); @@ -4783,6 +2676,6 @@ database.database.get( - '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', - [storeId, now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')], + 'SELECT COUNT(*) as request_count FROM request WHERE store_id = $1 AND EXTRACT(YEAR FROM date_and_time) = $2 AND EXTRACT(MONTH FROM date_and_time) = $3', + [storeId, now.getFullYear(), now.getMonth() + 1], (err, result) => { if (err) { @@ -4793,5 +2686,5 @@ } - const requestCount = result ? result.request_count + 1 : 1; + const requestCount = result && result[0] ? parseInt(result[0].request_count) + 1 : 1; const requestSeqPadded = requestCount.toString().padStart(2, '0'); @@ -4835,4 +2728,5 @@ req.on('end', () => { const refundData = JSON.parse(body); + const userIdStr = String(userId); @@ -4841,8 +2735,8 @@ database.database.get( - 'SELECT LEFT(order_num,3) AS store_id FROM "order" WHERE order_num = $1', + 'SELECT store_id FROM "order" WHERE order_num = $1', [refundData.order_num], (err, result) => { - if (err || !result) { + if (err || !result || result.length === 0) { res.writeHead(404, { 'Content-Type': 'application/json' }); res.end(JSON.stringify({ success: false, message: 'Order not found' })); @@ -4850,5 +2744,6 @@ } - const storeId = result.store_id; + const storeId = result[0].store_id; + const now = new Date(); const month = (now.getMonth() + 1).toString().padStart(2, '0'); @@ -4856,6 +2751,6 @@ database.database.get( - '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)', - [now.getFullYear().toString(), (now.getMonth() + 1).toString().padStart(2, '0')], + 'SELECT COUNT(*) as refund_count FROM refund WHERE EXTRACT(YEAR FROM request_date) = $1 AND EXTRACT(MONTH FROM request_date) = $2', + [now.getFullYear(), now.getMonth() + 1], (err, result) => { if (err) { @@ -4866,5 +2761,5 @@ } - const refundCount = result ? result.refund_count + 1 : 1; + const refundCount = result && result[0] ? parseInt(result[0].refund_count) + 1 : 1; const refundSeqPadded = refundCount.toString().padStart(2, '0'); @@ -4933,7 +2828,6 @@ } - // PostgreSQL: extract the numeric product sequence from the store-prefixed product code database.database.get( - 'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) AS max_product_num FROM product WHERE LEFT(code,3) = $1', + 'SELECT MAX(CAST(SUBSTRING(code FROM 4) AS INTEGER)) as max_product_num FROM product WHERE store_id = $1', [storeId], (err, result) => { @@ -5004,5 +2898,5 @@ database.database.get( - 'SELECT LEFT(code,3) AS store_id FROM product WHERE code = $1', + 'SELECT store_id FROM product WHERE code = $1', [productData.code], (err, product) => { @@ -5087,5 +2981,4 @@ } - // Updated /api/force-change-password endpoint with redirect handling else if (pathname === '/api/force-change-password' && req.method === 'POST') { const cookies = parseCookies(req); @@ -5104,203 +2997,67 @@ }); req.on('end', () => { - try { - const { newPassword, confirmPassword, redirectTo } = JSON.parse(body); - - if (!newPassword || !confirmPassword) { - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'All fields are required' })); + const { currentPassword, newPassword, confirmPassword } = JSON.parse(body); + + if (!currentPassword || !newPassword || !confirmPassword) { + res.writeHead(400, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'All fields are required' })); + return; + } + + if (newPassword !== confirmPassword) { + res.writeHead(400, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'New passwords do not match' })); + return; + } + + if (!validatePassword(newPassword)) { + res.writeHead(400, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ + success: false, + message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character' + })); + return; + } + + database.getUserByUsername('admin', (err, user) => { + if (err || !user) { + res.writeHead(404, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'User not found' })); return; } - if (newPassword !== confirmPassword) { - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'New passwords do not match' })); - return; - } - - if (!validatePassword(newPassword)) { - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'Password must have at least 8 characters, including uppercase, lowercase, number and special character' - })); - return; - } - - // First, try to find the user in the users table (for admin) - database.getUserById(userId, (err, user) => { - if (err) { - console.error('Error finding user by ID:', err); + database.verifyPassword(currentPassword, user.password, (err, isValid) => { + if (err || !isValid) { + res.writeHead(400, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Current password is incorrect' })); + return; } - if (user) { - // Found in users table (admin or regular user) - console.log('Found user in users table:', user); - - const hashedPassword = bcrypt.hashSync(newPassword, 10); - - database.database.run( - 'UPDATE users SET password = $1, force_password_change = FALSE WHERE id = $2', - [hashedPassword, userId], - function(err) { - if (err) { - console.error('Error updating password:', err); - res.writeHead(500, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Failed to update password' })); - return; - } - - // Also update password in personal table if it exists (for admin) - database.database.run( - 'UPDATE personal SET password = $1 WHERE id = $2', - [hashedPassword, userId], - function(err) { - if (err) { - console.log('No personal record to update for ID:', userId); - } - } - ); - - // Clear temp session - tempAdminSessions.delete(sessionId); - - // Create new permanent session - const newSessionId = generateSessionId(); - - // Determine how to store the user ID based on user type - let sessionUserId = String(userId); - - if (user.user_type === 'store_owner' || user.user_type === 'store_employee') { - sessionUserId = `personal_${userId}`; - } - - sessions.set(newSessionId, sessionUserId); - - // Determine redirect based on user type or provided redirectTo - let finalRedirect = redirectTo || 'dashboard.html'; - - if (!redirectTo) { - if (user.username === 'admin' || user.user_type === 'admin') { - finalRedirect = 'admin.html'; - } else if (user.user_type === 'store_owner') { - finalRedirect = 'store-owner.html'; - } else if (user.user_type === 'store_employee') { - finalRedirect = 'store-employee.html'; - } else if (user.user_type === 'client') { - finalRedirect = 'client-dashboard.html'; - } - } - - console.log(`Password changed successfully for user ${userId}, redirecting to ${finalRedirect}`); - - database.logAudit(userId, 'FORCED_PASSWORD_CHANGE', 'auth', userId.toString(), - `${user.user_type || 'user'} forced password change completed`, ipAddress); - - // Set the cookie with proper options - res.writeHead(200, { - 'Content-Type': 'application/json', - 'Set-Cookie': `sessionId=${newSessionId}; HttpOnly; Path=/; Max-Age=86400; SameSite=Strict` // Extended to 24 hours - }); - - res.end(JSON.stringify({ - success: true, - message: 'Password changed successfully.', - redirectTo: finalRedirect, - userType: user.user_type || 'user' - })); - } - ); - } else { - // Not found in users table, check personal table (for store owners/employees) - console.log('User not found in users table, checking personal table for ID:', userId); - - database.getPersonalById(userId, (err, personal) => { - if (err) { - console.error('Error finding personal by ID:', err); - } - - if (personal) { - console.log('Found user in personal table:', personal); - - // Update password in personal table - const hashedPassword = bcrypt.hashSync(newPassword, 10); - - database.database.run( - 'UPDATE personal SET password = $1 WHERE id = $2', - [hashedPassword, userId], - function(err) { - if (err) { - console.error('Error updating personal password:', err); - res.writeHead(500, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Failed to update password' })); - return; - } - - // Also update in users table if exists - database.database.run( - 'UPDATE users SET password = $1, force_password_change = FALSE WHERE email = $2', - [hashedPassword, personal.email], - function(err) { - if (err) { - console.log('No users record to update for email:', personal.email); - } - } - ); - - // Determine user type (boss/owner or employee) - database.database.get( - 'SELECT boss_id FROM boss WHERE boss_id = $1', - [userId], - (err, boss) => { - let userType = 'store_employee'; - let finalRedirect = redirectTo || 'store-employee.html'; - - if (boss) { - userType = 'store_owner'; - finalRedirect = redirectTo || 'store-owner.html'; - } - - // Clear temp session - tempAdminSessions.delete(sessionId); - - // Create new permanent session with personal_ prefix - const newSessionId = generateSessionId(); - sessions.set(newSessionId, `personal_${userId}`); - - console.log(`Password changed successfully for ${userType} ${userId}, redirecting to ${finalRedirect}`); - - database.logAudit(userId, 'FORCED_PASSWORD_CHANGE', 'auth', userId.toString(), - `${userType} forced password change completed`, ipAddress); - - // Set the cookie with proper options - extended to 24 hours - res.writeHead(200, { - 'Content-Type': 'application/json', - 'Set-Cookie': `sessionId=${newSessionId}; HttpOnly; Path=/; Max-Age=86400; SameSite=Strict` - }); - - res.end(JSON.stringify({ - success: true, - message: 'Password changed successfully.', - redirectTo: finalRedirect, - userType: userType - })); - } - ); - } - ); - } else { - // User not found in any table - console.error('User not found in any table with ID:', userId); - res.writeHead(404, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'User not found' })); - } - }); - } + database.updatePasswordAndClearForce(userId, newPassword, (err) => { + if (err) { + res.writeHead(500, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Failed to update password' })); + } else { + tempAdminSessions.delete(sessionId); + + const newSessionId = generateSessionId(); + sessions.set(newSessionId, String(userId)); + + database.logAudit(userId, 'FORCED_PASSWORD_CHANGE', 'auth', userId.toString(), + 'Admin forced password change completed', ipAddress); + + res.writeHead(200, { + 'Content-Type': 'application/json', + 'Set-Cookie': `sessionId=${newSessionId}; HttpOnly; Path=/; Max-Age=3600; SameSite=Strict` + }); + res.end(JSON.stringify({ + success: true, + message: 'Password changed successfully. You can now access the dashboard.', + redirectTo: 'admin.html' + })); + } + }); }); - } catch (parseError) { - console.error('JSON parse error:', parseError); - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Invalid request format' })); - } + }); }); } @@ -5309,11 +3066,4 @@ requireAuth(req, res, (userId) => { const userIdStr = String(userId); - - // Check if this is the admin user - if (userIdStr === '000000') { - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Only store owners can register employees' })); - return; - } if (!userIdStr.startsWith('personal_')) { @@ -5445,11 +3195,7 @@ database.database.run('ROLLBACK'); console.error('Error inserting personal:', err); - if (err.code === '23505') { res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'This personal ID is already taken. Please try again.' - })); + res.end(JSON.stringify({ success: false, message: 'This personal ID is already taken. Please try again.' })); } else { res.writeHead(400, { 'Content-Type': 'application/json' }); @@ -5491,41 +3237,23 @@ } - // Also create entry in users table for login with force_password_change = 1 - database.database.run( - 'INSERT INTO users (id, username, email, password, user_type, force_password_change) VALUES ($1, $2, $3, $4, $5, $6)', - [ - newPersonalId, - `${firstName} ${lastName}`, - email, - bcrypt.hashSync(password, 10), - 'store_employee', - 1 - ], - (err) => { - if (err) { - console.error('Error creating user entry for employee:', err); - } - - database.database.run('COMMIT', (err) => { - if (err) { - database.database.run('ROLLBACK'); - console.error('Error committing transaction:', err); - res.writeHead(500, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Error completing registration' })); - return; - } - - database.logAudit(personalId, 'EMPLOYEE_REGISTERED', 'employee', newPersonalId, `Employee registered: ${firstName} ${lastName}`, ipAddress); - - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - message: 'Employee registered successfully!', - employeeId: newPersonalId, - name: `${firstName} ${lastName}` - })); - }); + database.database.run('COMMIT', (err) => { + if (err) { + database.database.run('ROLLBACK'); + console.error('Error committing transaction:', err); + res.writeHead(500, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Error completing registration' })); + return; } - ); + + database.logAudit(personalId, 'EMPLOYEE_REGISTERED', 'employee', newPersonalId, `Employee registered: ${firstName} ${lastName}`, ipAddress); + + res.writeHead(200, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ + success: true, + message: 'Employee registered successfully!', + employeeId: newPersonalId, + name: `${firstName} ${lastName}` + })); + }); } ); @@ -5550,11 +3278,4 @@ const userIdStr = String(userId); - // Check if this is the admin user - if (userIdStr === '000000') { - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Only store owners can delete employees' })); - return; - } - if (!userIdStr.startsWith('personal_')) { res.writeHead(403, { 'Content-Type': 'application/json' }); @@ -5666,32 +3387,21 @@ } - // Also delete from users table - database.database.run( - 'DELETE FROM users WHERE id = $1', - [employeeId], - (err) => { - if (err) { - console.error('Error deleting from users:', err); - } - - database.database.run('COMMIT', (commitErr) => { - if (commitErr) { - database.database.run('ROLLBACK'); - console.error('Error committing transaction:', commitErr); - res.writeHead(500, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Error completing deletion' })); - return; - } - - database.logAudit(personalId, 'EMPLOYEE_DELETED', 'employee', employeeId, `Employee deleted from store ${storeId}`, ipAddress); - - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - message: 'Employee deleted successfully' - })); - }); + database.database.run('COMMIT', (commitErr) => { + if (commitErr) { + database.database.run('ROLLBACK'); + console.error('Error committing transaction:', commitErr); + res.writeHead(500, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Error completing deletion' })); + return; } - ); + + database.logAudit(personalId, 'EMPLOYEE_DELETED', 'employee', employeeId, `Employee deleted from store ${storeId}`, ipAddress); + + res.writeHead(200, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ + success: true, + message: 'Employee deleted successfully' + })); + }); } ); @@ -5719,11 +3429,4 @@ const userIdStr = String(userId); - // Check if this is the admin user - if (userIdStr === '000000') { - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Only store owners can update employee status' })); - return; - } - if (!userIdStr.startsWith('personal_')) { res.writeHead(403, { 'Content-Type': 'application/json' }); @@ -5825,11 +3528,4 @@ const userIdStr = String(userId); - // Check if this is the admin user - if (userIdStr === '000000') { - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Only store owners can update employees' })); - return; - } - if (!userIdStr.startsWith('personal_')) { res.writeHead(403, { 'Content-Type': 'application/json' }); @@ -5887,10 +3583,10 @@ if (firstName) { - updates.push(`first_name = $${params.length + 1}`); + updates.push('first_name = $' + (params.length + 1)); params.push(firstName); } if (lastName) { - updates.push(`last_name = $${params.length + 1}`); + updates.push('last_name = $' + (params.length + 1)); params.push(lastName); } @@ -5902,5 +3598,5 @@ return; } - updates.push(`email = $${params.length + 1}`); + updates.push('email = $' + (params.length + 1)); params.push(email); } @@ -5923,38 +3619,4 @@ res.end(JSON.stringify({ success: false, message: 'Error updating employee information' })); return; - } - - // Also update in users table if email was changed - if (email) { - database.database.run( - 'UPDATE users SET email = $1 WHERE id = $2', - [email, employeeId], - (err) => { - if (err) { - console.error('Error updating user email:', err); - } - } - ); - } - - if (firstName || lastName) { - database.database.get( - 'SELECT first_name, last_name FROM personal WHERE id = $1', - [employeeId], - (err, personal) => { - if (!err && personal) { - const newUsername = `${personal.first_name} ${personal.last_name}`; - database.database.run( - 'UPDATE users SET username = $1 WHERE id = $2', - [newUsername, employeeId], - (err) => { - if (err) { - console.error('Error updating user username:', err); - } - } - ); - } - } - ); } @@ -6004,5 +3666,4 @@ } ); - return; } @@ -6058,5 +3719,4 @@ } ); - return; } @@ -6112,5 +3772,4 @@ } ); - return; } @@ -6166,5 +3825,4 @@ } ); - return; } @@ -6220,5 +3878,4 @@ } ); - return; } @@ -6248,66 +3905,7 @@ } - else if (pathname === '/api/advanced-reports' && req.method === 'GET') { - requireRole('admin')(req, res, () => { - const reportName = parsedUrl.query.report; - - if (!reportName) { - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'The report query parameter is required' - })); - return; - } - - let params = []; - - if (reportName === 'get_low_stock_high_demand_products') { - const stockThreshold = Number(parsedUrl.query.stockThreshold ?? 5); - const demandThreshold = Number(parsedUrl.query.demandThreshold ?? 5); - - if (!Number.isInteger(stockThreshold) || !Number.isInteger(demandThreshold)) { - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'stockThreshold and demandThreshold must be integers' - })); - return; - } - - params = [stockThreshold, demandThreshold]; - } - - database.runReport(reportName, params, (err, rows) => { - if (err) { - console.error('Error executing report:', err); - res.writeHead(500, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'Error executing report: ' + err.message - })); - return; - } - - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - report: reportName, - rows - })); - }); - }); - } - else if (pathname === '/api/employee-tasks' && req.method === 'GET') { requireAuth(req, res, (userId) => { const userIdStr = String(userId); - - // Check if this is the admin user - if (userIdStr === '000000') { - res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ success: false, message: 'Only store employees can access this endpoint' })); - return; - } if (!userIdStr.startsWith('personal_')) { @@ -6434,38 +4032,13 @@ requireStoreOwner()(req, res, (personalId) => { let body = ''; - req.on('data', chunk => { body += chunk.toString(); }); - req.on('end', () => { - let data; - - try { - data = JSON.parse(body); - } catch (err) { - res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'Invalid JSON request body' - })); - return; - } - - const { - storeId, - period, - startDate, - endDate, - type, - ownerSignature - } = data; + const { storeId, period, startDate, endDate, type } = JSON.parse(body); if (!storeId || !period || !startDate || !endDate || !type) { res.writeHead(400, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'All fields are required' - })); + res.end(JSON.stringify({ success: false, message: 'All fields are required' })); return; } @@ -6477,62 +4050,38 @@ if (err || !ownsStore) { res.writeHead(403, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: false, - message: 'You are not authorized to generate reports for this store' - })); + res.end(JSON.stringify({ success: false, message: 'You are not authorized to generate reports for this store' })); return; } - database.generateStoreReport( - storeId, - startDate, - endDate, - type, - period, - ownerSignature, - (reportErr, report) => { - if (reportErr) { - console.error('Error generating report:', reportErr); + const reportId = 'RPT' + Date.now().toString().slice(-6); + + database.database.run( + 'INSERT INTO report (id, store_id, period, start_date, end_date, type, generated_by, generated_at) VALUES ($1, $2, $3, $4, $5, $6, $7, CURRENT_TIMESTAMP)', + [reportId, storeId, period, startDate, endDate, type, personalId], + function(err) { + if (err) { + console.error('Error generating report:', err); res.writeHead(500, { 'Content-Type': 'application/json' }); + res.end(JSON.stringify({ success: false, message: 'Error generating report: ' + err.message })); + } else { + database.logAudit(personalId, 'REPORT_GENERATED', 'report', reportId, `Report generated: ${type} for ${period}`, ipAddress); + + res.writeHead(200, { 'Content-Type': 'application/json' }); res.end(JSON.stringify({ - success: false, - message: 'Error generating report: ' + reportErr.message + success: true, + message: 'Report generated successfully', + reportId: reportId, + report: { + id: reportId, + storeId: storeId, + period: period, + startDate: startDate, + endDate: endDate, + type: type, + generatedBy: personalId, + generatedAt: new Date().toISOString() + } })); - return; } - - const reportId = - 'RPT' + - new Date(report.date).getTime().toString().slice(-6); - - database.logAudit( - personalId, - 'REPORT_GENERATED', - 'report', - reportId, - `Report generated: ${type} for ${period}`, - ipAddress - ); - - res.writeHead(200, { 'Content-Type': 'application/json' }); - res.end(JSON.stringify({ - success: true, - message: 'Report generated successfully', - reportId, - report: { - id: reportId, - storeId: report.store_id, - period, - startDate, - endDate, - type, - generatedBy: personalId, - generatedAt: report.date, - overallProfit: report.overall_profit, - salesTrend: report.sales_trend, - marketingGrowth: report.marketing_growth, - ownerSignature: report.owner_signature - } - })); } );