Changeset 6c7cfa6 for database.js

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

File:

• database.js (modified) (9 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,