Changeset 6c7cfa6

Timestamp:

09/21/26 00:41:03 (9 days ago)

Author:

Klimentina Efremova <klimentina08642@…>

Branches:

main

Children:

2d1ec46

Parents:

6149556

git-author:

Klimentina Efremova <klimentina08642@…> (09/21/26 00:24:09)

git-committer:

Klimentina Efremova <klimentina08642@…> (09/21/26 00:41:03)

Message:

Implemented Advaced database reports

Files:

• database.js (modified) (9 diffs)
• node_modules/.package-lock.json (modified) (1 diff)
• node_modules/semver/README.md (modified) (4 diffs)
• node_modules/semver/classes/range.js (modified) (8 diffs)
• node_modules/semver/classes/semver.js (modified) (2 diffs)
• node_modules/semver/functions/truncate.js (added)
• node_modules/semver/index.js (modified) (2 diffs)
• node_modules/semver/internal/re.js (modified) (1 diff)
• node_modules/semver/package.json (modified) (3 diffs)
• node_modules/semver/range.bnf (modified) (1 diff)
• node_modules/semver/ranges/subset.js (modified) (2 diffs)
• server.js (modified) (37 diffs)

database.js

@@ -1383 +1383 @@
 
     query(
-        `SELECT *
-         FROM report
-         WHERE store_id = $1
-         ORDER BY generated_at DESC`,
+        `SELECT
+            r.date,
+            r.store_id,
+            r.overall_profit,
+            r.sales_trend,
+            r.marketing_growth,
+            r.owner_signature
+         FROM report r
+         WHERE r.store_id = $1
+         ORDER BY r.date DESC`,
         [storeId],
         (err, result) => {
-
             callback(
                 err,
@@ -1404 +1409 @@
 
     query(
-        `SELECT COUNT(*) AS total_products
-         FROM product
-         WHERE store_id = $1`,
+        `SELECT COUNT(DISTINCT se.product_code) AS total_products
+         FROM sells se
+         WHERE se.store_id = $1`,
         [storeId],
         (err, result) => {
@@ -1415 +1420 @@
             }
 
-            stats.total_products =
-                result.rows[0]
-                    ? Number(result.rows[0].total_products)
-                    : 0;
+            stats.total_products = Number(
+                result.rows[0]?.total_products || 0
+            );
 
             query(
-                `SELECT COUNT(*) AS total_orders
-                 FROM "order"
-                 WHERE store_id = $1`,
+                `SELECT COUNT(DISTINCT o.order_num) AS total_orders
+                 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`,
                 [storeId],
                 (err, result) => {
@@ -1432 +1440 @@
                     }
 
-                    stats.total_orders =
-                        result.rows[0]
-                            ? Number(result.rows[0].total_orders)
-                            : 0;
+                    stats.total_orders = Number(
+                        result.rows[0]?.total_orders || 0
+                    );
 
                     query(
@@ -1441 +1448 @@
                             COALESCE(
                                 SUM(
-                                    oi.price * oi.quantity
+                                    p.price * i.quantity
+                                    * (1 - COALESCE(o.discount, 0) / 100.0)
                                 ),
                                 0
                             ) AS total_revenue
-                         FROM order_items oi
+                         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 oi.order_num =
-                              o.order_num
-                         WHERE o.store_id = $1`,
+                           ON o.order_num = i.order_num
+                         WHERE se.store_id = $1`,
                         [storeId],
                         (err, result) => {
@@ -1458 +1469 @@
                             }
 
-                            stats.total_revenue =
-                                Number(
-                                    result.rows[0]
-                                        .total_revenue || 0
-                                );
+                            stats.total_revenue = Number(
+                                result.rows[0]?.total_revenue || 0
+                            );
 
                             query(
-                                `SELECT
-                                    COALESCE(
-                                        AVG(r.rating),
-                                        0
-                                    ) AS avg_rating
-                                 FROM review r
-                                 JOIN product p
-                                   ON r.product_code =
-                                      p.code
-                                 WHERE p.store_id = $1`,
+                                `WITH store_reviews AS (
+                                    SELECT DISTINCT
+                                        r.order_num,
+                                        r.rating
+                                    FROM review r
+                                    JOIN includes i
+                                      ON i.order_num = r.order_num
+                                    JOIN sells se
+                                      ON se.product_code = i.product_code
+                                    WHERE se.store_id = $1
+                                )
+                                SELECT COALESCE(AVG(rating), 0) AS avg_rating
+                                FROM store_reviews`,
                                 [storeId],
                                 (err, result) => {
@@ -1483 +1495 @@
                                     }
 
-                                    stats.avg_rating =
-                                        Number(
-                                            result.rows[0]
-                                                .avg_rating || 0
-                                        );
-
-                                    callback(
-                                        null,
-                                        stats
+                                    stats.avg_rating = Number(
+                                        result.rows[0]?.avg_rating || 0
                                     );
+
+                                    callback(null, stats);
                                 }
                             );
@@ -2293 +2300 @@
 
 
+
+
+/*
+ * ============================================================
+ * ADVANCED REPORTS
+ * ============================================================
+ */
+
+
+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 order_total 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 number_of_orders DESC, total_quantity_sold DESC, total_revenue 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 number_of_orders DESC, total_quantity_sold DESC, current_stock 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 year DESC, month DESC, total_revenue 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 total_revenue 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 number_of_orders 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 average_review 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 revenue_growth 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 number_of_orders 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 total_requests 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 number_of_requests 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 total_hours_worked DESC, total_pay 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 average_pay 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 number_of_product_changes 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, monthly_profit 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;
+$$;
+`;
+
+function installReportFunctions(callback) {
+
+    pool.query(REPORT_FUNCTIONS_SQL)
+        .then(() => {
+            console.log('✅ PostgreSQL report functions installed');
+            if (callback) callback(null);
+        })
+        .catch(err => {
+            console.error('❌ Failed to install PostgreSQL report functions:', err);
+            if (callback) callback(err);
+        });
+}
+
+const REPORT_FUNCTION_NAMES = 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'
+]);
+
+function runReport(reportName, params, callback) {
+
+    if (!REPORT_FUNCTION_NAMES.has(reportName)) {
+        callback(new Error('Unknown report: ' + reportName), null);
+        return;
+    }
+
+    const values = Array.isArray(params) ? params : [];
+
+    const placeholders = values.map(
+        (_, index) => '$' + (index + 1)
+    ).join(', ');
+
+    query(
+        `SELECT * FROM ${reportName}(${placeholders})`,
+        values,
+        (err, result) => {
+            callback(
+                err,
+                result ? result.rows : []
+            );
+        }
+    );
+}
+
+function generateStoreReport(
+    storeId,
+    startDate,
+    endDate,
+    type,
+    period,
+    ownerSignature,
+    callback
+) {
+
+    query(
+        `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) {
+                callback(err, null);
+                return;
+            }
+
+            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);
+
+            const salesTrend =
+                `Revenue ${revenue.toFixed(2)}; Refunds ${refundTotal.toFixed(2)}`;
+
+            query(
+                `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 >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
+                   AND o.last_date_mod < DATE_TRUNC('month', CURRENT_DATE)`,
+                [storeId],
+                (previousErr, previousResult) => {
+
+                    if (previousErr) {
+                        callback(previousErr, null);
+                        return;
+                    }
+
+                    const previousRevenue =
+                        Number(previousResult.rows[0]?.revenue || 0);
+
+                    const growth =
+                        previousRevenue === 0
+                            ? (revenue > 0 ? 100 : 0)
+                            : ((revenue - previousRevenue) / previousRevenue) * 100;
+
+                    const marketingGrowth =
+                        `${growth.toFixed(2)}%`;
+
+                    query(
+                        `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),
+                            salesTrend.slice(0, 100),
+                            marketingGrowth.slice(0, 100),
+                            ownerSignature || 'Not signed yet'
+                        ],
+                        (insertErr, insertResult) => {
+
+                            if (insertErr) {
+                                callback(insertErr, null);
+                                return;
+                            }
+
+                            const report = insertResult.rows[0];
+
+                            /*
+                             * monthly_profit has a composite primary key of
+                             * (report_date, store_id), so it can contain one
+                             * summary row per generated report without changing
+                             * the project database structure.
+                             */
+                            query(
+                                `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
+                                        );
+                                    }
+
+                                    query(
+                                        `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
+                                            );
+                                        }
+                                    );
+                                }
+                            );
+                        }
+                    );
+                }
+            );
+        }
+    );
+}
 /*
  * ============================================================
@@ -2340 +3196 @@
     getStoreStats,
 
+    installReportFunctions,
+    runReport,
+    generateStoreReport,
+
     createOrderNew,
     getOrdersByClient,

node_modules/.package-lock.json

@@ -468 +468 @@
     },
     "node_modules/semver": {
-      "version": "7.7.4",
-      "resolved": "https://registry.npmjs.org/semver/-/semver-7.7.4.tgz",
-      "integrity": "sha512-vFKC2IEtQnVhpT78h1Yp8wzwrf8CM+MzKMHGJZfBtzhZNycRFnXsHk6E5TxIkkMsgNS7mdX3AGB7x2QM2di4lA==",
+      "version": "7.8.5",
+      "resolved": "https://registry.npmjs.org/semver/-/semver-7.8.5.tgz",
+      "integrity": "sha512-Y7/KDsb8LjooZpwaqGyulO6DQlksgCncchHGk+sZIY4SBvUocMBEFH5Ur1fI4dV+Jvl0w6cjvucaIi40puRioA==",
       "dev": true,
       "license": "ISC",

node_modules/semver/README.md

@@ -57 +57 @@
 const semverSort = require('semver/functions/sort')
 const semverRsort = require('semver/functions/rsort')
+const semverTruncate = require('semver/functions/truncate')
 
 // low-level comparators between versions
@@ -400 +401 @@
 caret      ::= '^' partial
 qualifier  ::= ( '-' pre )? ( '+' build )?
-pre        ::= parts
-build      ::= parts
-parts      ::= part ( '.' part ) *
-part       ::= nr | [-0-9A-Za-z]+
-```
+pre        ::= prepart ( '.' prepart ) *
+prepart    ::= nr | alphanumid
+build      ::= buildid ( '.' buildid ) *
+alphanumid ::= ( ['0'-'9'] ) * [-A-Za-z] [-0-9A-Za-z] *
+buildid    ::= [-0-9A-Za-z]+
+```
+
+Note: Prerelease identifiers (`pre`) use `nr` for numeric parts, which
+disallows leading zeros (e.g., `1.2.3-00` is invalid). Build metadata
+identifiers (`build`) allow any alphanumeric string including leading
+zeros (e.g., `1.2.3+00` is valid). This matches the
+[SemVer 2.0.0 specification](https://semver.org/#spec-item-9).
 
 ## Functions
@@ -450 +458 @@
 * `parse(v)`: Attempt to parse a string as a semantic version, returning either
   a `SemVer` object or `null`.
+* `truncate(v, releaseType)`: Return the version with components _lower_
+  than `releaseType` dropped off, e.g.:
+  * `major` removes build & prerelease info and sets minor & patch to 0.
+  * `minor` removes build & prerelease info, and sets patch to 0
+  * `patch` removes build & prerelease info
+  * All prerelease types remove build info only
 
 ### Comparison
@@ -651 +665 @@
 * `require('semver/functions/satisfies')`
 * `require('semver/functions/sort')`
+* `require('semver/functions/truncate')`
 * `require('semver/functions/valid')`
 * `require('semver/ranges/gtr')`

node_modules/semver/classes/range.js

@@ -99 +99 @@
 
   parseRange (range) {
+    // strip build metadata so it can't bleed into the version
+    range = range.replace(BUILDSTRIPRE, '')
+
     // memoize range parsing for performance.
     // this is a very hot path, and fully deterministic.
@@ -224 +227 @@
 const {
   safeRe: re,
+  src,
   t,
   comparatorTrimReplace,
@@ -230 +234 @@
 } = require('../internal/re')
 const { FLAG_INCLUDE_PRERELEASE, FLAG_LOOSE } = require('../internal/constants')
+
+// unbounded global build-metadata stripper used by parseRange
+const BUILDSTRIPRE = new RegExp(src[t.BUILD], 'g')
 
 const isNullSet = c => c.value === '<0.0.0-0'
@@ -271 +278 @@
 const isX = id => !id || id.toLowerCase() === 'x' || id === '*'
 
+const invalidXRangeOrder = (M, m, p) => (
+  (isX(M) && !isX(m)) ||
+  (isX(m) && p && !isX(p))
+)
+
 // ~, ~> --> * (any, kinda silly)
 // ~2, ~2.x, ~2.x.x, ~>2, ~>2.x ~>2.x.x --> >=2.0.0 <3.0.0-0
@@ -288 +300 @@
 const replaceTilde = (comp, options) => {
   const r = options.loose ? re[t.TILDELOOSE] : re[t.TILDE]
+  // if we're including prereleases in the match, then the lower bound is
+  // -0, the lowest possible prerelease value, just like x-ranges and carets.
+  // this keeps `~1.2` equivalent to the `1.2.x` x-range it's documented as.
+  const z = options.includePrerelease ? '-0' : ''
   return comp.replace(r, (_, M, m, p, pr) => {
     debug('tilde', comp, _, M, m, p, pr)
@@ -295 +311 @@
       ret = ''
     } else if (isX(m)) {
-      ret = `>=${M}.0.0 <${+M + 1}.0.0-0`
+      ret = `>=${M}.0.0${z} <${+M + 1}.0.0-0`
     } else if (isX(p)) {
       // ~1.2 == >=1.2.0 <1.3.0-0
-      ret = `>=${M}.${m}.0 <${M}.${+m + 1}.0-0`
+      ret = `>=${M}.${m}.0${z} <${M}.${+m + 1}.0-0`
     } else if (pr) {
       debug('replaceTilde pr', pr)
@@ -367 +383 @@
         if (m === '0') {
           ret = `>=${M}.${m}.${p
-          }${z} <${M}.${m}.${+p + 1}-0`
+          } <${M}.${m}.${+p + 1}-0`
         } else {
           ret = `>=${M}.${m}.${p
-          }${z} <${M}.${+m + 1}.0-0`
+          } <${M}.${+m + 1}.0-0`
         }
       } else {
@@ -396 +412 @@
   return comp.replace(r, (ret, gtlt, M, m, p, pr) => {
     debug('xRange', comp, ret, gtlt, M, m, p, pr)
+    if (invalidXRangeOrder(M, m, p)) {
+      return comp
+    }
+
     const xM = isX(M)
     const xm = xM || isX(m)

node_modules/semver/classes/semver.js

@@ -7 +7 @@
 const parseOptions = require('../internal/parse-options')
 const { compareIdentifiers } = require('../internal/identifiers')
+
+const isPrereleaseIdentifier = (prerelease, identifier) => {
+  const identifiers = identifier.split('.')
+  if (identifiers.length > prerelease.length) {
+    return false
+  }
+
+  for (let i = 0; i < identifiers.length; i++) {
+    if (compareIdentifiers(prerelease[i], identifiers[i]) !== 0) {
+      return false
+    }
+  }
+
+  return true
+}
+
 class SemVer {
   constructor (version, options) {
@@ -310 +326 @@
             prerelease = [identifier]
           }
-          if (compareIdentifiers(this.prerelease[0], identifier) === 0) {
-            if (isNaN(this.prerelease[1])) {
+          if (isPrereleaseIdentifier(this.prerelease, identifier)) {
+            const prereleaseBase = this.prerelease[identifier.split('.').length]
+            if (isNaN(prereleaseBase)) {
               this.prerelease = prerelease
             }

node_modules/semver/index.js

@@ -29 +29 @@
 const cmp = require('./functions/cmp')
 const coerce = require('./functions/coerce')
+const truncate = require('./functions/truncate')
 const Comparator = require('./classes/comparator')
 const Range = require('./classes/range')
@@ -67 +68 @@
   cmp,
   coerce,
+  truncate,
   Comparator,
   Range,

node_modules/semver/internal/re.js

@@ -137 +137 @@
 
 // Something like "2.*" or "1.2.x".
-// Note that "x.x" is a valid xRange identifer, meaning "any version"
+// Note that "x.x" is a valid xRange identifier, meaning "any version"
 // Only the first item is strictly required.
 createToken('XRANGEIDENTIFIERLOOSE', `${src[t.NUMERICIDENTIFIERLOOSE]}|x|X|\\*`)

node_modules/semver/package.json

@@ -1 +1 @@
 {
   "name": "semver",
-  "version": "7.7.4",
+  "version": "7.8.5",
   "description": "The semantic version parser used by npm.",
   "main": "index.js",
@@ -15 +15 @@
   },
   "devDependencies": {
-    "@npmcli/eslint-config": "^6.0.0",
-    "@npmcli/template-oss": "4.29.0",
+    "@npmcli/eslint-config": "^7.0.0",
+    "@npmcli/template-oss": "5.0.0",
     "benchmark": "^2.1.4",
     "tap": "^16.0.0"
@@ -53 +53 @@
   "templateOSS": {
     "//@npmcli/template-oss": "This file is partially managed by @npmcli/template-oss. Edits may be overwritten.",
-    "version": "4.29.0",
+    "version": "5.0.0",
     "engines": ">=10",
     "distPaths": [

node_modules/semver/range.bnf

@@ -11 +11 @@
 caret      ::= '^' partial
 qualifier  ::= ( '-' pre )? ( '+' build )?
-pre        ::= parts
-build      ::= parts
-parts      ::= part ( '.' part ) *
-part       ::= nr | [-0-9A-Za-z]+
+pre        ::= prepart ( '.' prepart ) *
+prepart    ::= nr | alphanumid
+build      ::= buildid ( '.' buildid ) *
+alphanumid ::= ( [0-9] ) * [A-Za-z-] [-0-9A-Za-z] *
+buildid    ::= [-0-9A-Za-z]+

node_modules/semver/ranges/subset.js

@@ -175 +175 @@
           return false
         }
-      } else if (gt.operator === '>=' && !satisfies(gt.semver, String(c), options)) {
+      } else if (gt.operator === '>=' && !c.test(gt.semver)) {
         return false
       }
@@ -193 +193 @@
           return false
         }
-      } else if (lt.operator === '<=' && !satisfies(lt.semver, String(c), options)) {
+      } else if (lt.operator === '<=' && !c.test(lt.semver)) {
         return false
       }

server.js

@@ -7 +7 @@
 const nodemailer = require('nodemailer');
 const bcrypt = require('bcryptjs');
+const { AsyncLocalStorage } = require('async_hooks');
 require('dotenv').config();
 
@@ -356 +357 @@
 });
 
-let transactionClient = null;
+const transactionStorage = new AsyncLocalStorage();
 
 function dbQuery(sql, params = [], callback) {
-    const client = transactionClient || pool;
+    const client = transactionStorage.getStore() || pool;
+
     client.query(sql, params)
         .then(result => callback(null, result))
         .catch(err => callback(err));
 }
+
+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 database = {
@@ -393 +939 @@
 
             if (normalized === 'BEGIN TRANSACTION' || normalized === 'BEGIN') {
-                if (transactionClient) {
+                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 => {
-                    transactionClient = client;
-                    return client.query('BEGIN');
-                }).then(() => callback?.(null))
+
+                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 => {
-                        if (transactionClient) transactionClient.release();
-                        transactionClient = null;
                         callback?.(err);
                     });
+
                 return;
             }
 
             if (normalized === 'COMMIT') {
-                if (!transactionClient) {
+                const client = transactionStorage.getStore();
+
+                if (!client) {
                     callback?.(null);
                     return;
                 }
-                const client = transactionClient;
+
                 client.query('COMMIT')
                     .then(() => {
-                        transactionClient = null;
                         client.release();
                         callback?.(null);
                     })
                     .catch(err => {
-                        transactionClient = null;
-                        client.release();
-                        callback?.(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') {
-                if (!transactionClient) {
+                const client = transactionStorage.getStore();
+
+                if (!client) {
                     callback?.(null);
                     return;
                 }
-                const client = transactionClient;
+
                 client.query('ROLLBACK')
                     .then(() => {
-                        transactionClient = null;
                         client.release();
                         callback?.(null);
                     })
                     .catch(err => {
-                        transactionClient = null;
                         client.release();
                         callback?.(err);
                     });
+
                 return;
             }
@@ -458 +1028 @@
             });
         }
+    },
+
+    async installReportFunctions() {
+        await pool.query(REPORT_FUNCTIONS_SQL);
+        console.log('✅ PostgreSQL report functions 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 || [])
+        );
     },
 
@@ -520 +1133 @@
         const schema = `
             CREATE TABLE IF NOT EXISTS category (
-                id SERIAL PRIMARY KEY,
-                name VARCHAR(50) NOT NULL,
+                                                    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',
+                                                   code VARCHAR(8) PRIMARY KEY DEFAULT '-1',
                 price DECIMAL(10,2) NOT NULL CHECK (price >= 0.0),
                 availability INTEGER NOT NULL,
@@ -534 +1147 @@
                 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,
+                                                 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,
+                                                 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,
+                                                 store_ID VARCHAR(3) PRIMARY KEY,
                 name VARCHAR(50) UNIQUE NOT NULL,
                 date_of_founding DATE NOT NULL,
@@ -553 +1166 @@
                 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,
+                                                    id VARCHAR(10) PRIMARY KEY,
                 first_name VARCHAR(20) NOT NULL,
                 last_name VARCHAR(20) NOT NULL,
@@ -562 +1175 @@
                 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_is VARCHAR(10) PRIMARY KEY REFERENCES personal(id) ON DELETE CASCADE,
+                                                       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
-            );
+                                                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,
+                                                     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,
+                                                  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,
+                                                            client_ID INTEGER PRIMARY KEY REFERENCES client(client_ID) ON DELETE CASCADE,
                 address VARCHAR(200) NOT NULL,
                 city VARCHAR(30) NOT NULL,
@@ -594 +1207 @@
                 country VARCHAR(40) NOT NULL,
                 is_default BOOLEAN DEFAULT TRUE
-            );
+                );
 
             CREATE TABLE IF NOT EXISTS "order" (
-                order_num VARCHAR(11) PRIMARY KEY,
+                                                   order_num VARCHAR(11) PRIMARY KEY,
                 client_ID INTEGER REFERENCES client(client_ID) ON DELETE CASCADE,
                 status VARCHAR(20) NOT NULL DEFAULT 'placed order',
@@ -604 +1217 @@
                 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,
+                                                  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,
+                                                  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 revied', 'approved', 'not approved', 'processed'))
-            );
+                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,
+                                                  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,
@@ -630 +1243 @@
                 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,
+                                                          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,
+                                                          report_date TIMESTAMP NOT NULL,
+                                                          store_ID VARCHAR(3) NOT NULL,
                 monthly_profit NUMERIC NOT NULL DEFAULT 0.0,
                 date TIMESTAMP NOT NULL,
@@ -650 +1263 @@
                 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,
+                                                   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,
+                                                         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,
+                                                   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,
+                                                     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,
+                                                    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,
+                                                        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,
+                                                          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,
+                                                  personal_id VARCHAR(10) REFERENCES personal(id) ON DELETE CASCADE,
                 report_date TIMESTAMP,
                 store_ID VARCHAR(3),
@@ -710 +1323 @@
                 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,
+                                                 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,
+                                                    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,
+                                                    boss_id VARCHAR(10) REFERENCES boss(boss_id) ON DELETE CASCADE,
                 report_date TIMESTAMP,
                 store_ID VARCHAR(3),
@@ -733 +1346 @@
                 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,
+                                                 id VARCHAR(50) PRIMARY KEY,
                 username VARCHAR(100) UNIQUE NOT NULL,
                 email VARCHAR(255) UNIQUE NOT NULL,
@@ -745 +1358 @@
                 force_password_change BOOLEAN DEFAULT FALSE,
                 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-            );
+                );
 
             CREATE TABLE IF NOT EXISTS roles (
-                role_id SERIAL PRIMARY KEY,
-                name VARCHAR(50) UNIQUE NOT NULL,
+                                                 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,
+                                                      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),
+                                                     log_id BIGSERIAL PRIMARY KEY,
+                                                     user_id VARCHAR(50),
                 action VARCHAR(100) NOT NULL,
                 resource_type VARCHAR(50),
@@ -768 +1381 @@
                 ip_address VARCHAR(45),
                 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-            );
+                );
 
             CREATE INDEX IF NOT EXISTS idx_product_category ON product(category_id);
@@ -783 +1396 @@
 
         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)
+        `);
 
         const roles = [
@@ -812 +1581 @@
         await pool.query(`INSERT INTO boss(boss_id) VALUES('000000') ON CONFLICT(boss_id) DO NOTHING`);
         await pool.query(
-            `INSERT INTO permissions(personal_is,type,authorisation)
-             VALUES('000000','ADMIN','full_access') ON CONFLICT(personal_is) DO NOTHING`
+            `INSERT INTO permissions(personal_id,type,authorisation)
+             VALUES('000000','ADMIN','full_access') ON CONFLICT(personal_id) DO NOTHING`
         );
         await pool.query(
@@ -832 +1601 @@
                              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
+                      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`,
@@ -847 +1616 @@
                              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
+                      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`,
+                 LIMIT 1`,
             [username],
             (err, result) => callback(err, result?.rows?.[0])
@@ -937 +1706 @@
         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 ') : ''}
+                         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 || []));
@@ -947 +1716 @@
             `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
+                 LEFT JOIN category c ON c.id=p.category_id
              WHERE p.code=$1 LIMIT 1`,
             [String(id)],
@@ -958 +1727 @@
             `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
+                 LEFT JOIN category c ON c.id=p.category_id
              WHERE p.code=$1`,
             [code],
@@ -985 +1754 @@
                     `INSERT INTO sells(product_code,store_ID,discount)
                      VALUES($1,$2,$3)
-                     ON CONFLICT(product_code,store_ID)
+                         ON CONFLICT(product_code,store_ID)
                      DO UPDATE SET discount=EXCLUDED.discount`,
                     [data.code,storeId,data.discount || 0],
@@ -1092 +1861 @@
         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
+                 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
+                 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
@@ -1109 +1878 @@
             `INSERT INTO review(order_num,comment,rating,last_mod_date)
              VALUES($1,$2,$3,CURRENT_TIMESTAMP)
-             RETURNING order_num`,
+                 RETURNING order_num`,
             [data.order_num,data.comment||null,data.rating],
             (err,result)=>callback(err,result?.rows?.[0]?.order_num)
@@ -1162 +1931 @@
     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
+                     c.first_name,c.last_name,c.email
                  FROM "order" o
-                 LEFT JOIN client c ON c.client_id=o.client_ID
+                     LEFT JOIN client c ON c.client_id=o.client_ID
                  ORDER BY o.last_date_mod DESC`,
             [],(err,result)=>callback(err,result?.rows||[]));
@@ -1172 +1941 @@
         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
+                     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||[]));
@@ -1181 +1950 @@
         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
+                     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||[]));
@@ -1189 +1958 @@
         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_is=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||[]));
@@ -1196 +1965 @@
 
     getStoreReports(storeId, callback) {
-        dbQuery(`SELECT * FROM report WHERE store_ID=$1 ORDER BY date DESC`,[storeId],
-            (err,result)=>callback(err,result?.rows||[]));
+        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(*) FROM product WHERE LEFT(code,3)=$1)::int AS product_count,
-            (SELECT COUNT(*) FROM "order" WHERE LEFT(order_num,3)=$1)::int AS order_count,
-            (SELECT COALESCE(SUM(i.quantity*p.price*(1-o.discount/100)),0)
-             FROM "order" o JOIN includes i ON i.order_num=o.order_num JOIN product p ON p.code=i.product_code
-             WHERE LEFT(o.order_num,3)=$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]||{}));
+        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);
+                                            }
+                                        );
+                                    }
+                                );
+                            }
+                        );
+                    }
+                );
+            }
+        );
     },
 
@@ -1216 +2134 @@
         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
+                          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`,
@@ -1225 +2143 @@
     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`,
+                         (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]||{}));
     }
@@ -1243 +2161 @@
     try {
         await database.initializeDatabase();
+        await database.installReportFunctions();
         console.log('✅ Database initialization completed');
     } catch (err) {
@@ -4768 +5687 @@
     }
 
+    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) => {
@@ -4902 +5873 @@
         requireStoreOwner()(req, res, (personalId) => {
             let body = '';
+
             req.on('data', chunk => {
                 body += chunk.toString();
             });
+
             req.on('end', () => {
-                const { storeId, period, startDate, endDate, type } = JSON.parse(body);
+                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;
 
                 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;
                 }
@@ -4920 +5916 @@
                         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;
                         }
 
-                        const reportId = 'RPT' + Date.now().toString().slice(-6);
-
-                        database.database.run(
-                            'INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES (CURRENT_TIMESTAMP, $1, 0, $2, $3, $4)',
-                            [storeId, period, type, 'Not signed yet'],
-                            function(err) {
-                                if (err) {
-                                    console.error('Error generating report:', err);
+                        database.generateStoreReport(
+                            storeId,
+                            startDate,
+                            endDate,
+                            type,
+                            period,
+                            ownerSignature,
+                            (reportErr, report) => {
+                                if (reportErr) {
+                                    console.error('Error generating report:', reportErr);
                                     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: 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()
-                                        }
+                                        success: false,
+                                        message: 'Error generating report: ' + reportErr.message
                                     }));
+                                    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
+                                    }
+                                }));
                             }
                         );