Index: tabase/Advanced Database Developement/Triggers/Automatic calculation of store rating from order reviews.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Automatic calculation of store rating from order reviews.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,55 +1,0 @@
-CREATE OR REPLACE FUNCTION update_store_rating()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-DECLARE
-    v_order_num INT;
-    v_store_id INT;
-BEGIN
-    -- Determine which order was affected
-    IF TG_OP = 'DELETE' THEN
-        v_order_num := OLD.order_num;
-    ELSE
-        v_order_num := NEW.order_num;
-    END IF;
-
-    -- Find the store associated with the order
-    SELECT fs.store_id
-    INTO v_store_id
-    FROM for_store fs
-    JOIN "order" o
-        ON o.order_num = v_order_num
-    WHERE fs.request_num IS NOT NULL
-    LIMIT 1;
-
-    -- Update the store rating
-    IF v_store_id IS NOT NULL THEN
-        UPDATE store s
-        SET rating = (
-            SELECT COALESCE(AVG(r.rating), 0)
-            FROM review r
-            JOIN "order" o
-                ON o.order_num = r.order_num
-            -- The exact relationship between orders and stores
-            -- should be used here according to the final schema
-            WHERE o.order_num IN (
-                SELECT i.order_num
-                FROM includes i
-                JOIN sells sl
-                    ON sl.code = i.code
-                WHERE sl.store_id = v_store_id
-            )
-        )
-        WHERE s.store_id = v_store_id;
-    END IF;
-
-    RETURN NULL;
-END;
-$$;
-
-
-CREATE TRIGGER trg_update_store_rating
-AFTER INSERT OR UPDATE OR DELETE
-ON review
-FOR EACH ROW
-EXECUTE FUNCTION update_store_rating();
Index: tabase/Advanced Database Developement/Triggers/Automatic deletion of product changes when the product is deleted.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Automatic deletion of product changes when the product is deleted.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,19 +1,0 @@
-CREATE OR REPLACE FUNCTION delete_product_changes()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    -- Delete all changes associated with the product
-    DELETE FROM change
-    WHERE product_code = OLD.code;
-
-    RETURN OLD;
-END;
-$$;
-
-
-CREATE TRIGGER trg_delete_product_changes
-BEFORE DELETE
-ON product
-FOR EACH ROW
-EXECUTE FUNCTION delete_product_changes();
Index: tabase/Advanced Database Developement/Triggers/Automatic setting of the last modified date for orders.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Automatic setting of the last modified date for orders.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,15 +1,0 @@
-CREATE OR REPLACE FUNCTION update_order_modified_date()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    NEW.last_modified_date := CURRENT_TIMESTAMP;
-    RETURN NEW;
-END;
-$$;
-
-CREATE TRIGGER trg_update_order_modified_date
-BEFORE UPDATE
-ON "order"
-FOR EACH ROW
-EXECUTE FUNCTION update_order_modified_date();
Index: tabase/Advanced Database Developement/Triggers/Automatic update of employee-store statistics after an employee is hired.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Automatic update of employee-store statistics after an employee is hired.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,30 +1,0 @@
-CREATE OR REPLACE FUNCTION initialize_employee_statistics()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    /*
-    * The employee is created first.
-    * Work statistics can be initialized when
-    * the employee is assigned to a store.
-    * The actual store assignment should be handled
-    * through works_in_store. */
-    INSERT INTO worked (
-        employee_id,
-        total_working_hours,
-        total_wage
-    )
-    VALUES (
-        NEW.id,
-        0,
-        0
-    );
-    RETURN NEW;
-END;
-$$;
-
-CREATE TRIGGER trg_initialize_employee_statistics
-AFTER INSERT
-ON employees
-FOR EACH ROW
-EXECUTE FUNCTION initialize_employee_statistics();
Index: tabase/Advanced Database Developement/Triggers/Automatic update of product availability after an order.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Automatic update of product availability after an order.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,47 +1,0 @@
-CREATE OR REPLACE FUNCTION update_product_availability()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    -- When a product is newly added to an order,
-    -- decrease the available quantity.
-    IF TG_OP = 'INSERT' THEN
-
-        UPDATE sells
-        SET quantity = quantity - (
-            SELECT o.quantity
-            FROM "order" o
-            WHERE o.order_num = NEW.order_num
-        )
-        WHERE code = NEW.code;
-
-    -- If the product/order entry is modified,
-    -- restore the old quantity and subtract the new quantity.
-    ELSIF TG_OP = 'UPDATE' THEN
-
-        UPDATE sells
-        SET quantity = quantity
-            + (
-                SELECT o.quantity
-                FROM "order" o
-                WHERE o.order_num = OLD.order_num
-            )
-            - (
-                SELECT o.quantity
-                FROM "order" o
-                WHERE o.order_num = NEW.order_num
-            )
-        WHERE code = NEW.code;
-
-    END IF;
-
-    RETURN NULL;
-END;
-$$;
-
-
-CREATE TRIGGER trg_update_product_availability
-AFTER INSERT OR UPDATE
-ON includes
-FOR EACH ROW
-EXECUTE FUNCTION update_product_availability();
Index: tabase/Advanced Database Developement/Triggers/Prevention of deleting stores with existing orders or reports.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Prevention of deleting stores with existing orders or reports.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,22 +1,0 @@
-CREATE OR REPLACE FUNCTION prevent_store_deletion()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    -- Check whether the store has existing reports
-    IF EXISTS ( SELECT 1 FROM report WHERE store_id = OLD.store_id ) THEN
-        RAISE EXCEPTION 'Store % cannot be deleted because it has existing reports.', OLD.store_id;
-    END IF;
-    -- Check whether the store has products associated with it
-    IF EXISTS ( SELECT 1 FROM sells WHERE store_id = OLD.store_id ) THEN
-        RAISE EXCEPTION 'Store % cannot be deleted because it has existing product records.', OLD.store_id;
-    END IF;
-    RETURN OLD;
-END;
-$$;
-
-CREATE TRIGGER trg_prevent_store_deletion
-BEFORE DELETE
-ON store
-FOR EACH ROW
-EXECUTE FUNCTION prevent_store_deletion();
Index: tabase/Advanced Database Developement/Triggers/Prevention of ordering unavailable products.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Prevention of ordering unavailable products.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,46 +1,0 @@
-CREATE OR REPLACE FUNCTION check_product_availability()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-DECLARE
-    v_available_quantity INT;
-    v_order_quantity INT;
-BEGIN
-    -- Get the quantity requested by the order
-    SELECT quantity
-    INTO v_order_quantity
-    FROM "order"
-    WHERE order_num = NEW.order_num;
-
-    -- Get the currently available quantity of the product
-    SELECT quantity
-    INTO v_available_quantity
-    FROM sells
-    WHERE code = NEW.code;
-
-    -- Check whether the product exists
-    IF v_available_quantity IS NULL THEN
-        RAISE EXCEPTION
-            'Product % is not available in any store.',
-            NEW.code;
-    END IF;
-
-    -- Check whether there is enough stock
-    IF v_order_quantity > v_available_quantity THEN
-        RAISE EXCEPTION
-            'Insufficient stock for product %. Available: %, requested: %.',
-            NEW.code,
-            v_available_quantity,
-            v_order_quantity;
-    END IF;
-
-    RETURN NEW;
-END;
-$$;
-
-
-CREATE TRIGGER trg_check_product_availability
-BEFORE INSERT OR UPDATE
-ON includes
-FOR EACH ROW
-EXECUTE FUNCTION check_product_availability();
Index: tabase/Advanced Database Developement/Triggers/Validation of employee authorization before making a product change.txt
===================================================================
--- database/Advanced Database Developement/Triggers/Validation of employee authorization before making a product change.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,29 +1,0 @@
-CREATE OR REPLACE FUNCTION validate_employee_authorization()
-RETURNS TRIGGER
-LANGUAGE plpgsql
-AS $$
-DECLARE v_authorisation TEXT;
-BEGIN
-    -- Get the employee's authorization
-    SELECT authorisation
-    INTO v_authorisation
-    FROM personal
-    WHERE id = NEW.id;
-    -- Check whether the employee exists
-    IF v_authorisation IS NULL THEN
-        RAISE EXCEPTION 'Employee % does not have valid authorization.', NEW.id;
-    END IF;
-    -- Check whether the authorization matches
-    IF NEW.authorisation IS DISTINCT FROM v_authorisation THEN
-        RAISE EXCEPTION 'Employee % is not authorized to make this product change.', NEW.id;
-    END IF;
-    RETURN NEW;
-END;
-$$;
-
-
-CREATE TRIGGER trg_validate_employee_authorization
-BEFORE INSERT OR UPDATE
-ON makes_change
-FOR EACH ROW
-EXECUTE FUNCTION validate_employee_authorization();
Index: tabase/Advanced Database Developement/Views/Complete overview of customer orders.txt
===================================================================
--- database/Advanced Database Developement/Views/Complete overview of customer orders.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,23 +1,0 @@
-CREATE OR REPLACE VIEW vw_customer_order_overview AS
-SELECT
-    o.order_num,
-    o.quantity,
-    o.status,
-    o.last_modified_date,
-    o.payment_method,
-    o.discount,
-    c.client_id,
-    c.first_name,
-    c.last_name,
-    c.email,
-    p.code AS product_code,
-    p.description AS product_description,
-    p.price FROM "order" o
-JOIN makes_order mo
-    ON mo.order_num = o.order_num
-JOIN client c
-    ON c.client_id = mo.client_id
-JOIN includes i
-    ON i.order_num = o.order_num
-JOIN product p
-    ON p.code = i.code;
Index: tabase/Advanced Database Developement/Views/Complete overview of products and stores.txt
===================================================================
--- database/Advanced Database Developement/Views/Complete overview of products and stores.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,19 +1,0 @@
-CREATE OR REPLACE VIEW vw_product_store_overview AS
-SELECT
-    p.code AS product_code,
-    p.description,
-    p.price,
-    p.availability,
-    p.weight,
-    p.approx_production_time,
-    s.store_id,
-    s.name AS store_name,
-    s.physical_address,
-    s.rating,
-    sl.quantity,
-    sl.discount
-FROM product p
-JOIN sells sl
-    ON sl.code = p.code
-JOIN store s
-   ON s.store_id = sl.store_id;
Index: tabase/Advanced Database Developement/Views/Customer order history.txt
===================================================================
--- database/Advanced Database Developement/Views/Customer order history.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,27 +1,0 @@
-CREATE OR REPLACE VIEW vw_customer_order_history AS
-SELECT
-    c.client_id,
-    c.first_name,
-    c.last_name,
-    c.email,
-
-    o.order_num,
-    o.quantity,
-    o.status,
-    o.last_modified_date,
-    o.payment_method,
-    o.discount,
-
-    p.code AS product_code,
-    p.description AS product_description,
-    p.price
-
-FROM client c
-JOIN makes_order mo
-    ON mo.client_id = c.client_id
-JOIN "order" o
-    ON o.order_num = mo.order_num
-JOIN includes i
-    ON i.order_num = o.order_num
-JOIN product p
-    ON p.code = i.code;
Index: tabase/Advanced Database Developement/Views/Customer request and response overview.txt
===================================================================
--- database/Advanced Database Developement/Views/Customer request and response overview.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,26 +1,0 @@
-CREATE OR REPLACE VIEW vw_customer_request_response AS
-SELECT
-    r.request_num,
-    r.date_and_time,
-    r.problem,
-    r.notes_of_communication,
-    r.customer_satisfaction,
-
-    c.client_id,
-    c.first_name AS client_first_name,
-    c.last_name AS client_last_name,
-    c.email AS client_email,
-
-    p.id AS employee_id,
-    p.first_name AS employee_first_name,
-    p.last_name AS employee_last_name
-
-FROM request r
-JOIN make_request mr
-    ON mr.request_num = r.request_num
-JOIN client c
-    ON c.client_id = mr.client_id
-LEFT JOIN answers a
-    ON a.request_num = r.request_num
-LEFT JOIN personal p
-    ON p.id = a.id;
Index: tabase/Advanced Database Developement/Views/Employee workload and salary report.txt
===================================================================
--- database/Advanced Database Developement/Views/Employee workload and salary report.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,27 +1,0 @@
-CREATE OR REPLACE VIEW vw_employee_workload_salary AS
-SELECT
-    e.id AS employee_id,
-    p.first_name,
-    p.last_name,
-    p.email,
-    e.date_of_hire,
-
-    s.store_id,
-    s.name AS store_name,
-
-    w.week,
-    w.total_week,
-    w.working_hours,
-    w.wage,
-    w.pay_method
-
-FROM employees e
-JOIN personal p
-    ON p.id = e.id
-JOIN works_in_store wis
-    ON wis.id = e.id
-JOIN store s
-    ON s.store_id = wis.store_id
-LEFT JOIN worked w
-    ON w.id = e.id
-    AND w.store_id = wis.store_id;
Index: tabase/Advanced Database Developement/Views/Monthly sales and profit per store.txt
===================================================================
--- database/Advanced Database Developement/Views/Monthly sales and profit per store.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,18 +1,0 @@
-CREATE OR REPLACE VIEW vw_monthly_sales_profit AS
-SELECT
-    s.store_id,
-    s.name AS store_name,
-    r.date,
-    r.month_and_year,
-    r.profit,
-    r.overall_profit,
-    ed.monthly_profit,
-    ed.sales,
-    ed.damages
-
-FROM store s
-JOIN report r
-    ON r.store_id = s.store_id
-LEFT JOIN exchanges_data ed
-    ON ed.store_id = r.store_id
-    AND ed.date = r.date;
Index: tabase/Advanced Database Developement/Views/Store inventory overview.txt
===================================================================
--- database/Advanced Database Developement/Views/Store inventory overview.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,19 +1,0 @@
-CREATE OR REPLACE VIEW vw_store_inventory AS
-SELECT
-    s.store_id,
-    s.name AS store_name,
-
-    p.code AS product_code,
-    p.description,
-    p.price,
-    p.availability,
-    p.approx_production_time,
-
-    sl.quantity,
-    sl.discount
-
-FROM store s
-JOIN sells sl
-    ON sl.store_id = s.store_id
-JOIN product p
-    ON p.code = sl.code;
Index: tabase/Advanced Database Developement/Views/Store performance overview.txt
===================================================================
--- database/Advanced Database Developement/Views/Store performance overview.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,27 +1,0 @@
-CREATE OR REPLACE VIEW vw_store_performance AS
-SELECT
-    s.store_id,
-    s.name AS store_name,
-    s.date_of_founding,
-    s.rating,
-
-    COUNT(DISTINCT r.date) AS number_of_reports,
-    COALESCE(SUM(r.profit), 0) AS total_reported_profit,
-    COALESCE(MAX(r.overall_profit), 0) AS overall_profit,
-
-    COALESCE(SUM(ed.sales), 0) AS total_sales,
-    COALESCE(SUM(ed.damages), 0) AS total_damages,
-    COALESCE(SUM(ed.monthly_profit), 0) AS total_monthly_profit
-
-FROM store s
-LEFT JOIN report r
-    ON r.store_id = s.store_id
-LEFT JOIN exchanges_data ed
-    ON ed.store_id = r.store_id
-    AND ed.date = r.date
-
-GROUP BY
-    s.store_id,
-    s.name,
-    s.date_of_founding,
-    s.rating;
Index: tabase/Advanced Reports for database/Approximate number of orders per client
===================================================================
--- database/Advanced Reports for database/Approximate number of orders per client	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,20 +1,0 @@
-CREATE OR REPLACE FUNCTION get_approximate_orders_per_client()
-RETURNS TABLE (
-    total_clients BIGINT,
-    total_orders BIGINT,
-    approximate_orders_per_client NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        (SELECT COUNT(*) FROM client) AS total_clients,
-        (SELECT COUNT(*) FROM "order") AS total_orders,
-        ROUND(
-            (SELECT COUNT(*) FROM "order")::NUMERIC
-            / NULLIF((SELECT COUNT(*) FROM client), 0),
-            2
-        ) AS approximate_orders_per_client;
-END;
-$$;
Index: tabase/Advanced Reports for database/Clients ordered by number of orders.txt
===================================================================
--- database/Advanced Reports for database/Clients ordered by number of orders.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,26 +1,0 @@
-CREATE OR REPLACE FUNCTION get_clients_by_number_of_orders()
-RETURNS TABLE (
-    client_id INT,
-    client_name TEXT,
-    number_of_orders BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        c.client_id,
-        c.name AS client_name,
-        COUNT(DISTINCT o.order_num) AS number_of_orders
-    FROM client c
-    JOIN makes_order mo
-        ON c.client_id = mo.client_id
-    JOIN "order" o
-        ON mo.order_num = o.order_num
-    GROUP BY
-        c.client_id,
-        c.name
-    ORDER BY
-        number_of_orders DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Each product's monthly sales.txt
===================================================================
--- database/Advanced Reports for database/Each product's monthly sales.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,42 +1,0 @@
-CREATE OR REPLACE FUNCTION get_products_monthly_sales()
-RETURNS TABLE (
-    product_code INT,
-    product_description TEXT,
-    year INT,
-    month INT,
-    number_of_orders BIGINT,
-    total_quantity_sold BIGINT,
-    total_revenue NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        p.code AS product_code,
-        p.description AS product_description,
-        EXTRACT(YEAR FROM o.last_modified_date)::INT AS year,
-        EXTRACT(MONTH FROM o.last_modified_date)::INT AS month,
-        COUNT(DISTINCT o.order_num) AS number_of_orders,
-        SUM(o.quantity) AS total_quantity_sold,
-        SUM(
-            p.price
-            * o.quantity
-            * (1 - COALESCE(o.discount, 0) / 100.0)
-        ) AS total_revenue
-    FROM product p
-    JOIN includes i
-        ON p.code = i.code
-    JOIN "order" o
-        ON i.order_num = o.order_num
-    GROUP BY
-        p.code,
-        p.description,
-        EXTRACT(YEAR FROM o.last_modified_date),
-        EXTRACT(MONTH FROM o.last_modified_date)
-    ORDER BY
-        year DESC,
-        month DESC,
-        total_revenue DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Each store's average pay.txt
===================================================================
--- database/Advanced Reports for database/Each store's average pay.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,24 +1,0 @@
-CREATE OR REPLACE FUNCTION get_stores_average_pay()
-RETURNS TABLE (
-    store_id INT,
-    store_name TEXT,
-    average_pay NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        s.store_id,
-        s.name AS store_name,
-        COALESCE(AVG(w.wage), 0) AS average_pay
-    FROM store s
-    LEFT JOIN worked w
-        ON s.store_id = w.store_id
-    GROUP BY
-        s.store_id,
-        s.name
-    ORDER BY
-        average_pay DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Each store's number of request, including how many have been solved, and how many are still in progress.txt
===================================================================
--- database/Advanced Reports for database/Each store's number of request, including how many have been solved, and how many are still in progress.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,40 +1,0 @@
-CREATE OR REPLACE FUNCTION get_store_request_statistics()
-RETURNS TABLE (
-    store_id INT,
-    store_name TEXT,
-    total_requests BIGINT,
-    solved_requests BIGINT,
-    requests_in_progress BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        s.store_id,
-        s.name AS store_name,
-        COUNT(r.request_num) AS total_requests,
-        COUNT(
-            CASE
-                WHEN r.customer_satisfaction IS NOT NULL
-                THEN 1
-            END
-        ) AS solved_requests,
-        COUNT(
-            CASE
-                WHEN r.customer_satisfaction IS NULL
-                THEN 1
-            END
-        ) AS requests_in_progress
-    FROM store s
-    LEFT JOIN for_store fs
-        ON s.store_id = fs.store_id
-    LEFT JOIN request r
-        ON fs.request_num = r.request_num
-    GROUP BY
-        s.store_id,
-        s.name
-    ORDER BY
-        total_requests DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Employees ordered by total hours worked and total pay.txt
===================================================================
--- database/Advanced Reports for database/Employees ordered by total hours worked and total pay.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,29 +1,0 @@
-CREATE OR REPLACE FUNCTION get_employees_by_hours_and_pay()
-RETURNS TABLE (
-    id BIGINT,
-    employee_name TEXT,
-    total_hours_worked NUMERIC,
-    total_pay NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        e.id,
-        p.name AS employee_name,
-        COALESCE(SUM(w.working_hours), 0) AS total_hours_worked,
-        COALESCE(SUM(w.wage), 0) AS total_pay
-    FROM employees e
-    JOIN personal p
-        ON e.id = p.id
-    LEFT JOIN worked w
-        ON e.id = w.id
-    GROUP BY
-        e.id,
-        p.name
-    ORDER BY
-        total_hours_worked DESC,
-        total_pay DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/List of clients who haven't made an order yet.txt
===================================================================
--- database/Advanced Reports for database/List of clients who haven't made an order yet.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,21 +1,0 @@
-CREATE OR REPLACE FUNCTION get_clients_without_orders()
-RETURNS TABLE (
-    client_id INT,
-    client_name TEXT,
-    email TEXT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        c.client_id,
-        c.name AS client_name,
-        c.email
-    FROM client c
-    LEFT JOIN makes_order mo
-        ON c.client_id = mo.client_id
-    WHERE mo.order_num IS NULL
-    ORDER BY c.client_id;
-END;
-$$;
Index: tabase/Advanced Reports for database/List of most popular products with total number of sales.txt
===================================================================
--- database/Advanced Reports for database/List of most popular products with total number of sales.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,39 +1,0 @@
-CREATE OR REPLACE FUNCTION get_products_by_total_sales()
-RETURNS TABLE (
-    product_code INT,
-    product_description TEXT,
-    product_price NUMERIC,
-    number_of_orders BIGINT,
-    total_revenue NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        p.code AS product_code,
-        p.description AS product_description,
-        p.price AS product_price,
-        COUNT(DISTINCT o.order_num) AS number_of_orders,
-        COALESCE(
-            SUM(
-                p.price
-                * o.quantity
-                * (1 - COALESCE(o.discount, 0) / 100.0)
-            ),
-            0
-        ) AS total_revenue
-    FROM product p
-    LEFT JOIN includes i
-        ON p.code = i.code
-    LEFT JOIN "order" o
-        ON i.order_num = o.order_num
-    GROUP BY
-        p.code,
-        p.description,
-        p.price
-    ORDER BY
-        number_of_orders DESC,
-        total_revenue DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/List of products that are low on stock and high in demand.txt
===================================================================
--- database/Advanced Reports for database/List of products that are low on stock and high in demand.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,39 +1,0 @@
-CREATE OR REPLACE FUNCTION get_low_stock_high_demand_products(
-    p_stock_threshold INT,
-    p_demand_threshold INT
-)
-RETURNS TABLE (
-    product_code INT,
-    product_description TEXT,
-    current_stock INT,
-    number_of_orders BIGINT,
-    total_quantity_sold BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        p.code AS product_code,
-        p.description AS product_description,
-        p.availability AS current_stock,
-        COUNT(DISTINCT o.order_num) AS number_of_orders,
-        COALESCE(SUM(o.quantity), 0) AS total_quantity_sold
-    FROM product p
-    JOIN includes i
-        ON p.code = i.code
-    JOIN "order" o
-        ON i.order_num = o.order_num
-    GROUP BY
-        p.code,
-        p.description,
-        p.availability
-    HAVING
-        p.availability < p_stock_threshold
-        AND COUNT(DISTINCT o.order_num) >= p_demand_threshold
-    ORDER BY
-        number_of_orders DESC,
-        total_quantity_sold DESC,
-        current_stock ASC;
-END;
-$$;
Index: tabase/Advanced Reports for database/List of products who have not been ordered.txt
===================================================================
--- database/Advanced Reports for database/List of products who have not been ordered.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,62 +1,0 @@
-CREATE OR REPLACE FUNCTION get_products_by_total_sales()
-RETURNS TABLE (
-    product_code INT,
-    product_description TEXT,
-    product_price NUMERIC,
-    number_of_orders BIGINT,
-    total_revenue NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        p.code AS product_code,
-        p.description AS product_description,
-        p.price AS product_price,
-        COUNT(DISTINCT o.order_num) AS number_of_orders,
-        COALESCE(
-            SUM(
-                p.price
-                * o.quantity
-                * (1 - COALESCE(o.discount, 0) / 100.0)
-            ),
-            0
-        ) AS total_revenue
-    FROM product p
-    LEFT JOIN includes i
-        ON p.code = i.code
-    LEFT JOIN "order" o
-        ON i.order_num = o.order_num
-    GROUP BY
-        p.code,
-        p.description,
-        p.price
-    ORDER BY
-        number_of_orders DESC,
-        total_revenue DESC;
-END;
-$$;
-CREATE OR REPLACE FUNCTION get_products_never_ordered()
-RETURNS TABLE (
-    product_code INT,
-    product_description TEXT,
-    product_price NUMERIC,
-    current_stock INT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        p.code AS product_code,
-        p.description AS product_description,
-        p.price AS product_price,
-        p.availability AS current_stock
-    FROM product p
-    LEFT JOIN includes i
-        ON p.code = i.code
-    WHERE i.order_num IS NULL
-    ORDER BY p.code;
-END;
-$$;
Index: tabase/Advanced Reports for database/List of reports which haven't been approved.txt
===================================================================
--- database/Advanced Reports for database/List of reports which haven't been approved.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,27 +1,0 @@
-CREATE OR REPLACE FUNCTION get_unapproved_reports()
-RETURNS TABLE (
-    report_date DATE,
-    store_id INT,
-    overall_profit NUMERIC,
-    month_and_year TEXT,
-    profit NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        r.date AS report_date,
-        r.store_id,
-        r.overall_profit,
-        r.month_and_year,
-        r.profit
-    FROM report r
-    LEFT JOIN approves a
-        ON r.date = a.date
-        AND r.store_id = a.store_id
-    WHERE a.date IS NULL
-    ORDER BY
-        r.date DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Number of product changes each employee has made in the last month.txt
===================================================================
--- database/Advanced Reports for database/Number of product changes each employee has made in the last month.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,31 +1,0 @@
-CREATE OR REPLACE FUNCTION get_employee_product_changes_last_month()
-RETURNS TABLE (
-    id BIGINT,
-    employee_name TEXT,
-    number_of_product_changes BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        e.id,
-        p.name AS employee_name,
-        COUNT(DISTINCT mc.date_and_time) AS number_of_product_changes
-    FROM employees e
-    JOIN personal p
-        ON e.id = p.id
-    LEFT JOIN makes_change mc
-        ON e.id = mc.id
-    LEFT JOIN change ch
-        ON mc.date_and_time = ch.date_and_time
-    WHERE
-        mc.date_and_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
-        AND mc.date_and_time < date_trunc('month', CURRENT_DATE)
-    GROUP BY
-        e.id,
-        p.name
-    ORDER BY
-        number_of_product_changes DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Orders ordered by order total from highest to lowest.txt
===================================================================
--- database/Advanced Reports for database/Orders ordered by order total from highest to lowest.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,45 +1,0 @@
-CREATE OR REPLACE FUNCTION get_orders_by_total()
-RETURNS TABLE (
-    order_num INT,
-    client_id INT,
-    client_name TEXT,
-    order_quantity INT,
-    order_status TEXT,
-    payment_method TEXT,
-    discount NUMERIC,
-    order_total NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        o.order_num,
-        c.client_id,
-        c.name,
-        o.quantity,
-        o.status,
-        o.payment_method,
-        COALESCE(o.discount, 0) AS discount,
-        SUM(p.price * o.quantity)
-            * (1 - COALESCE(o.discount, 0) / 100) AS order_total
-    FROM "order" o
-    JOIN makes_order mo
-        ON o.order_num = mo.order_num
-    JOIN client c
-        ON mo.client_id = c.client_id
-    JOIN includes i
-        ON o.order_num = i.order_num
-    JOIN product p
-        ON i.code = p.code
-    GROUP BY
-        o.order_num,
-        c.client_id,
-        c.name,
-        o.quantity,
-        o.status,
-        o.payment_method,
-        o.discount
-    ORDER BY order_total DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Products ordered by number of orders from highest to lowest.txt
===================================================================
--- database/Advanced Reports for database/Products ordered by number of orders from highest to lowest.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,29 +1,0 @@
-CREATE OR REPLACE FUNCTION get_products_by_number_of_orders()
-RETURNS TABLE (
-    product_code INT,
-    product_description TEXT,
-    product_price NUMERIC,
-    number_of_orders BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        p.code AS product_code,
-        p.description AS product_description,
-        p.price AS product_price,
-        COUNT(DISTINCT o.order_num) AS number_of_orders
-    FROM product p
-    JOIN includes i
-        ON p.code = i.code
-    JOIN "order" o
-        ON i.order_num = o.order_num
-    GROUP BY
-        p.code,
-        p.description,
-        p.price
-    ORDER BY
-        number_of_orders DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Store with highest revenue growth in the last calendar year.txt
===================================================================
--- database/Advanced Reports for database/Store with highest revenue growth in the last calendar year.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,74 +1,0 @@
-CREATE OR REPLACE FUNCTION get_store_with_highest_revenue_growth()
-RETURNS TABLE (
-    store_id INT,
-    store_name TEXT,
-    previous_year_revenue NUMERIC,
-    last_year_revenue NUMERIC,
-    revenue_growth NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    WITH yearly_revenue AS (
-        SELECT
-            s.store_id,
-            s.name AS store_name,
-            EXTRACT(YEAR FROM o.last_modified_date)::INT AS year,
-            SUM(
-                p.price
-                * o.quantity
-                * (1 - COALESCE(o.discount, 0) / 100.0)
-            ) AS total_revenue
-        FROM store s
-        JOIN sells se
-            ON s.store_id = se.store_id
-        JOIN product p
-            ON se.code = p.code
-        JOIN includes i
-            ON p.code = i.code
-        JOIN "order" o
-            ON i.order_num = o.order_num
-        WHERE o.last_modified_date >=
-              date_trunc('year', CURRENT_DATE) - INTERVAL '2 years'
-          AND o.last_modified_date <
-              date_trunc('year', CURRENT_DATE)
-        GROUP BY
-            s.store_id,
-            s.name,
-            EXTRACT(YEAR FROM o.last_modified_date)
-    ),
-    revenue_comparison AS (
-        SELECT
-            store_id,
-            store_name,
-            MAX(
-                CASE
-                    WHEN year = EXTRACT(YEAR FROM CURRENT_DATE)::INT - 2
-                    THEN total_revenue
-                    ELSE 0
-                END
-            ) AS previous_year_revenue,
-            MAX(
-                CASE
-                    WHEN year = EXTRACT(YEAR FROM CURRENT_DATE)::INT - 1
-                    THEN total_revenue
-                    ELSE 0
-                END
-            ) AS last_year_revenue
-        FROM yearly_revenue
-        GROUP BY
-            store_id,
-            store_name
-    )
-    SELECT
-        store_id,
-        store_name,
-        previous_year_revenue,
-        last_year_revenue,
-        last_year_revenue - previous_year_revenue AS revenue_growth
-    FROM revenue_comparison
-    ORDER BY revenue_growth DESC
-    LIMIT 1;
-END;
-$$;
Index: tabase/Advanced Reports for database/Stores ordered by highest approximate product review.txt
===================================================================
--- database/Advanced Reports for database/Stores ordered by highest approximate product review.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,32 +1,0 @@
-CREATE OR REPLACE FUNCTION get_stores_by_average_review()
-RETURNS TABLE (
-    store_id INT,
-    store_name TEXT,
-    average_review NUMERIC,
-    number_of_reviews BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        s.store_id,
-        s.name AS store_name,
-        COALESCE(AVG(r.rating), 0) AS average_review,
-        COUNT(r.order_num) AS number_of_reviews
-    FROM store s
-    LEFT JOIN sells se
-        ON s.store_id = se.store_id
-    LEFT JOIN includes i
-        ON se.code = i.code
-    LEFT JOIN "order" o
-        ON i.order_num = o.order_num
-    LEFT JOIN review r
-        ON o.order_num = r.order_num
-    GROUP BY
-        s.store_id,
-        s.name
-    ORDER BY
-        average_review DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Stores ordered by monthly profit including monthly revenue growth.txt
===================================================================
--- database/Advanced Reports for database/Stores ordered by monthly profit including monthly revenue growth.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,64 +1,0 @@
-CREATE OR REPLACE FUNCTION get_stores_by_monthly_profit_and_revenue_growth()
-RETURNS TABLE (
-    store_id INT,
-    store_name TEXT,
-    month_and_year TEXT,
-    monthly_profit NUMERIC,
-    previous_month_revenue NUMERIC,
-    current_month_revenue NUMERIC,
-    revenue_growth NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    WITH monthly_revenue AS (
-        SELECT
-            s.store_id,
-            s.name AS store_name,
-            DATE_TRUNC('month', o.last_modified_date) AS month_date,
-            SUM(
-                p.price
-                * o.quantity
-                * (1 - COALESCE(o.discount, 0) / 100.0)
-            ) AS revenue
-        FROM store s
-        JOIN sells se
-            ON s.store_id = se.store_id
-        JOIN product p
-            ON se.code = p.code
-        JOIN includes i
-            ON p.code = i.code
-        JOIN "order" o
-            ON i.order_num = o.order_num
-        GROUP BY
-            s.store_id,
-            s.name,
-            DATE_TRUNC('month', o.last_modified_date)
-    ),
-    revenue_with_previous AS (
-        SELECT
-            store_id,
-            store_name,
-            month_date,
-            revenue,
-            LAG(revenue) OVER (
-                PARTITION BY store_id
-                ORDER BY month_date
-            ) AS previous_month_revenue
-        FROM monthly_revenue
-    )
-    SELECT
-        store_id,
-        store_name,
-        TO_CHAR(month_date, 'YYYY-MM') AS month_and_year,
-        revenue AS monthly_profit,
-        COALESCE(previous_month_revenue, 0) AS previous_month_revenue,
-        revenue AS current_month_revenue,
-        revenue - COALESCE(previous_month_revenue, 0) AS revenue_growth
-    FROM revenue_with_previous
-    ORDER BY
-        monthly_profit DESC,
-        revenue_growth DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Stores ordered by total revenue in the last calendar year from highest to lowest.txt
===================================================================
--- database/Advanced Reports for database/Stores ordered by total revenue in the last calendar year from highest to lowest.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,49 +1,0 @@
-CREATE OR REPLACE FUNCTION get_stores_by_last_calendar_year_revenue()
-RETURNS TABLE (
-    store_id INT,
-    store_name TEXT,
-    number_of_orders BIGINT,
-    total_quantity_sold BIGINT,
-    total_revenue NUMERIC
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        s.store_id,
-        s.name AS store_name,
-        COUNT(DISTINCT o.order_num) AS number_of_orders,
-        COALESCE(SUM(o.quantity), 0) AS total_quantity_sold,
-        COALESCE(
-            SUM(
-                p.price
-                * o.quantity
-                * (1 - COALESCE(o.discount, 0) / 100.0)
-            ),
-            0
-        ) AS total_revenue
-    FROM store s
-    LEFT JOIN sells se
-        ON s.store_id = se.store_id
-    LEFT JOIN product p
-        ON se.code = p.code
-    LEFT JOIN includes i
-        ON p.code = i.code
-    LEFT JOIN "order" o
-        ON i.order_num = o.order_num
-        AND o.last_modified_date >= DATE_TRUNC(
-            'year',
-            CURRENT_DATE
-        ) - INTERVAL '1 year'
-        AND o.last_modified_date < DATE_TRUNC(
-            'year',
-            CURRENT_DATE
-        )
-    GROUP BY
-        s.store_id,
-        s.name
-    ORDER BY
-        total_revenue DESC;
-END;
-$$;
Index: tabase/Advanced Reports for database/Top 10 employees who have answered the most amount of request in the last month.txt
===================================================================
--- database/Advanced Reports for database/Top 10 employees who have answered the most amount of request in the last month.txt	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ 	(revision )
@@ -1,31 +1,0 @@
-CREATE OR REPLACE FUNCTION get_top_10_employees_by_requests_last_month()
-RETURNS TABLE (
-    id BIGINT,
-    employee_name TEXT,
-    number_of_requests BIGINT
-)
-LANGUAGE plpgsql
-AS $$
-BEGIN
-    RETURN QUERY
-    SELECT
-        e.id,
-        p.name AS employee_name,
-        COUNT(DISTINCT a.request_num) AS number_of_requests
-    FROM employees e
-    JOIN personal p
-        ON e.id = p.id
-    JOIN answers a
-        ON e.id = a.id
-    JOIN request r
-        ON a.request_num = r.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.id,
-        p.name
-    ORDER BY
-        number_of_requests DESC
-    LIMIT 10;
-END;
-$$;
Index: server.js
===================================================================
--- server.js	(revision 6c7cfa631d14a25615bca6e638d689aaeb21e1a2)
+++ server.js	(revision 2d1ec46933bbbd721e985423c96581bc0a7485ed)
@@ -911,4 +911,556 @@
 `;
 
+
+const TRIGGERS_SQL = String.raw`
+-- ============================================================
+-- HANDCRAFT MARKETPLACE TRIGGERS
+-- Compatible with the supplied project schema.
+-- ============================================================
+
+CREATE OR REPLACE FUNCTION update_store_rating()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_order_num VARCHAR(11);
+    v_store_id VARCHAR(3);
+BEGIN
+    IF TG_OP = 'DELETE' THEN
+        v_order_num := OLD.order_num;
+    ELSE
+        v_order_num := NEW.order_num;
+    END IF;
+
+    v_store_id := LEFT(v_order_num, 3);
+
+    UPDATE store s
+    SET rating = COALESCE(
+        (
+            SELECT ROUND(AVG(r.rating), 1)
+            FROM review r
+            JOIN includes i
+              ON i.order_num = r.order_num
+            JOIN sells sl
+              ON sl.product_code = i.product_code
+             AND sl.store_ID = v_store_id
+            JOIN "order" o
+              ON o.order_num = r.order_num
+            WHERE LEFT(o.order_num, 3) = v_store_id
+        ),
+        0
+    )
+    WHERE s.store_ID = v_store_id;
+
+    RETURN NULL;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_update_store_rating ON review;
+
+CREATE TRIGGER trg_update_store_rating
+AFTER INSERT OR UPDATE OR DELETE
+ON review
+FOR EACH ROW
+EXECUTE FUNCTION update_store_rating();
+
+
+CREATE OR REPLACE FUNCTION check_product_availability()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_available_quantity INTEGER;
+BEGIN
+    SELECT p.availability
+    INTO v_available_quantity
+    FROM product p
+    WHERE p.code = NEW.product_code
+    FOR UPDATE;
+
+    IF v_available_quantity IS NULL THEN
+        RAISE EXCEPTION
+            'Product % does not exist or is not available.',
+            NEW.product_code;
+    END IF;
+
+    IF TG_OP = 'INSERT' THEN
+        IF NEW.quantity > v_available_quantity THEN
+            RAISE EXCEPTION
+                'Insufficient stock for product %. Available: %, requested: %.',
+                NEW.product_code,
+                v_available_quantity,
+                NEW.quantity;
+        END IF;
+
+    ELSIF TG_OP = 'UPDATE' THEN
+        IF NEW.product_code = OLD.product_code THEN
+            IF NEW.quantity > OLD.quantity
+               AND (NEW.quantity - OLD.quantity) > v_available_quantity THEN
+                RAISE EXCEPTION
+                    'Insufficient stock for product %. Available: %, additional requested: %.',
+                    NEW.product_code,
+                    v_available_quantity,
+                    NEW.quantity - OLD.quantity;
+            END IF;
+        ELSE
+            IF NEW.quantity > v_available_quantity THEN
+                RAISE EXCEPTION
+                    'Insufficient stock for product %. Available: %, requested: %.',
+                    NEW.product_code,
+                    v_available_quantity,
+                    NEW.quantity;
+            END IF;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_check_product_availability ON includes;
+
+CREATE TRIGGER trg_check_product_availability
+BEFORE INSERT OR UPDATE
+ON includes
+FOR EACH ROW
+EXECUTE FUNCTION check_product_availability();
+
+
+CREATE OR REPLACE FUNCTION update_product_availability()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+
+        UPDATE product
+        SET availability = availability - NEW.quantity
+        WHERE code = NEW.product_code;
+
+    ELSIF TG_OP = 'UPDATE' THEN
+
+        IF NEW.product_code = OLD.product_code THEN
+
+            UPDATE product
+            SET availability = availability - (NEW.quantity - OLD.quantity)
+            WHERE code = NEW.product_code;
+
+        ELSE
+
+            UPDATE product
+            SET availability = availability + OLD.quantity
+            WHERE code = OLD.product_code;
+
+            UPDATE product
+            SET availability = availability - NEW.quantity
+            WHERE code = NEW.product_code;
+
+        END IF;
+
+    ELSIF TG_OP = 'DELETE' THEN
+
+        UPDATE product
+        SET availability = availability + OLD.quantity
+        WHERE code = OLD.product_code;
+
+    END IF;
+
+    RETURN NULL;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_update_product_availability ON includes;
+
+CREATE TRIGGER trg_update_product_availability
+AFTER INSERT OR UPDATE OR DELETE
+ON includes
+FOR EACH ROW
+EXECUTE FUNCTION update_product_availability();
+
+
+CREATE OR REPLACE FUNCTION delete_product_changes()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    DELETE FROM "change"
+    WHERE product_code = OLD.code;
+
+    RETURN OLD;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_delete_product_changes ON product;
+
+CREATE TRIGGER trg_delete_product_changes
+BEFORE DELETE
+ON product
+FOR EACH ROW
+EXECUTE FUNCTION delete_product_changes();
+
+
+CREATE OR REPLACE FUNCTION prevent_store_deletion()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1
+        FROM report
+        WHERE store_ID = OLD.store_ID
+    ) THEN
+        RAISE EXCEPTION
+            'Store % cannot be deleted because it has existing reports.',
+            OLD.store_ID;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM sells
+        WHERE store_ID = OLD.store_ID
+    ) THEN
+        RAISE EXCEPTION
+            'Store % cannot be deleted because it has existing product records.',
+            OLD.store_ID;
+    END IF;
+
+    IF EXISTS (
+        SELECT 1
+        FROM "order"
+        WHERE LEFT(order_num, 3) = OLD.store_ID
+    ) THEN
+        RAISE EXCEPTION
+            'Store % cannot be deleted because it has existing orders.',
+            OLD.store_ID;
+    END IF;
+
+    RETURN OLD;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_prevent_store_deletion ON store;
+
+CREATE TRIGGER trg_prevent_store_deletion
+BEFORE DELETE
+ON store
+FOR EACH ROW
+EXECUTE FUNCTION prevent_store_deletion();
+
+
+CREATE OR REPLACE FUNCTION update_order_modified_date()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    NEW.last_date_mod := CURRENT_TIMESTAMP;
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_update_order_modified_date ON "order";
+
+CREATE TRIGGER trg_update_order_modified_date
+BEFORE UPDATE
+ON "order"
+FOR EACH ROW
+EXECUTE FUNCTION update_order_modified_date();
+
+
+CREATE OR REPLACE FUNCTION validate_employee_authorization()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_authorisation TEXT;
+BEGIN
+    SELECT p.authorisation
+    INTO v_authorisation
+    FROM permissions p
+    WHERE p.personal_id = NEW.personal_id;
+
+    IF v_authorisation IS NULL THEN
+        RAISE EXCEPTION
+            'Employee % does not have valid authorization.',
+            NEW.personal_id;
+    END IF;
+
+    -- makes_change has no authorisation column in the supplied schema.
+    -- The employee's permission row is therefore the source of truth.
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_validate_employee_authorization ON makes_change;
+
+CREATE TRIGGER trg_validate_employee_authorization
+BEFORE INSERT OR UPDATE
+ON makes_change
+FOR EACH ROW
+EXECUTE FUNCTION validate_employee_authorization();
+
+
+CREATE OR REPLACE FUNCTION initialize_employee_statistics()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    /*
+     * The schema creates an employee before store/report assignment in the
+     * normal application flow. If an assignment and a report already exist,
+     * create a zero-hours starting record; otherwise there is nothing to
+     * initialize yet.
+     */
+    INSERT INTO worked (
+        personal_id,
+        report_date,
+        store_ID,
+        wage,
+        pay_method,
+        total_hours,
+        week
+    )
+    SELECT
+        NEW.employee_id,
+        r.date,
+        wis.store_ID,
+        0,
+        'full_time',
+        0,
+        TO_CHAR(CURRENT_DATE - INTERVAL '6 days', 'DD.MM.YYYY')
+            || ' - ' ||
+        TO_CHAR(CURRENT_DATE, 'DD.MM.YYYY')
+    FROM works_in_store wis
+    JOIN LATERAL (
+        SELECT r2.date
+        FROM report r2
+        WHERE r2.store_ID = wis.store_ID
+        ORDER BY r2.date DESC
+        LIMIT 1
+    ) r ON TRUE
+    WHERE wis.personal_id = NEW.employee_id
+    ON CONFLICT (personal_id, report_date, store_ID) DO NOTHING;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_initialize_employee_statistics ON employees;
+
+CREATE TRIGGER trg_initialize_employee_statistics
+AFTER INSERT
+ON employees
+FOR EACH ROW
+EXECUTE FUNCTION initialize_employee_statistics();
+`;
+
+
+const VIEWS_SQL = String.raw`
+-- ============================================================
+-- HANDCRAFT MARKETPLACE VIEWS
+-- Compatible with the supplied project schema.
+-- ============================================================
+
+DROP VIEW IF EXISTS vw_product_store_overview;
+CREATE VIEW vw_product_store_overview AS
+SELECT
+    p.code AS product_code,
+    p.description,
+    p.price,
+    p.availability,
+    p.weight,
+    p.width_x_length_x_depth,
+    p.aprox_production_time,
+    s.store_ID,
+    s.name AS store_name,
+    s.physical_address,
+    s.rating,
+    sl.discount
+FROM product p
+JOIN sells sl
+    ON sl.product_code = p.code
+JOIN store s
+    ON s.store_ID = sl.store_ID;
+
+
+DROP VIEW IF EXISTS vw_customer_order_overview;
+CREATE VIEW vw_customer_order_overview AS
+SELECT
+    o.order_num,
+    i.quantity,
+    o.status,
+    o.last_date_mod,
+    o.payment_method,
+    o.discount,
+    c.client_ID,
+    c.first_name,
+    c.last_name,
+    c.email,
+    p.code AS product_code,
+    p.description AS product_description,
+    p.price
+FROM "order" o
+JOIN makes_request mr
+    ON mr.order_num = o.order_num
+JOIN client c
+    ON c.client_ID = mr.client_ID
+JOIN includes i
+    ON i.order_num = o.order_num
+JOIN product p
+    ON p.code = i.product_code;
+
+
+DROP VIEW IF EXISTS vw_monthly_sales_profit;
+CREATE VIEW vw_monthly_sales_profit AS
+SELECT
+    s.store_ID,
+    s.name AS store_name,
+    r.date AS report_date,
+    mp.month_and_year,
+    mp.profit,
+    r.overall_profit,
+    ed.monthly_profit,
+    ed.sales,
+    ed.damages
+FROM store s
+JOIN report r
+    ON r.store_ID = s.store_ID
+LEFT JOIN monthly_profit mp
+    ON mp.report_date = r.date
+   AND mp.store_ID = r.store_ID
+LEFT JOIN exchanges_data ed
+    ON ed.report_date = r.date
+   AND ed.store_ID = r.store_ID;
+
+
+DROP VIEW IF EXISTS vw_employee_workload_salary;
+CREATE VIEW vw_employee_workload_salary AS
+SELECT
+    e.employee_id,
+    p.first_name,
+    p.last_name,
+    p.email,
+    e.date_of_hire,
+    s.store_ID,
+    s.name AS store_name,
+    w.week,
+    w.total_hours,
+    w.wage,
+    w.pay_method,
+    COALESCE(w.wage * w.total_hours, 0) AS total_pay
+FROM employees e
+JOIN personal p
+    ON p.id = e.employee_id
+JOIN works_in_store wis
+    ON wis.personal_id = e.employee_id
+JOIN store s
+    ON s.store_ID = wis.store_ID
+LEFT JOIN worked w
+    ON w.personal_id = e.employee_id
+   AND w.store_ID = wis.store_ID;
+
+
+DROP VIEW IF EXISTS vw_customer_request_response;
+CREATE VIEW vw_customer_request_response AS
+SELECT
+    r.request_num,
+    r.date_and_time,
+    r.problem,
+    r.notes_of_communication,
+    r.customer_satisfaction,
+    c.client_ID,
+    c.first_name AS client_first_name,
+    c.last_name AS client_last_name,
+    c.email AS client_email,
+    p.id AS employee_id,
+    p.first_name AS employee_first_name,
+    p.last_name AS employee_last_name
+FROM request r
+LEFT JOIN client c
+    ON c.client_ID = CASE
+        WHEN SUBSTRING(r.request_num FROM 9 FOR 4) ~ '^[0-9]{4}$'
+        THEN SUBSTRING(r.request_num FROM 9 FOR 4)::INTEGER
+        ELSE NULL
+    END
+LEFT JOIN answers a
+    ON a.request_num = r.request_num
+LEFT JOIN personal p
+    ON p.id = a.personal_id;
+
+
+DROP VIEW IF EXISTS vw_store_inventory;
+CREATE VIEW vw_store_inventory AS
+SELECT
+    s.store_ID,
+    s.name AS store_name,
+    p.code AS product_code,
+    p.description,
+    p.price,
+    p.availability,
+    p.aprox_production_time,
+    sl.discount
+FROM store s
+JOIN sells sl
+    ON sl.store_ID = s.store_ID
+JOIN product p
+    ON p.code = sl.product_code;
+
+
+DROP VIEW IF EXISTS vw_customer_order_history;
+CREATE VIEW vw_customer_order_history AS
+SELECT
+    c.client_ID,
+    c.first_name,
+    c.last_name,
+    c.email,
+    o.order_num,
+    i.quantity,
+    o.status,
+    o.last_date_mod,
+    o.payment_method,
+    o.discount,
+    p.code AS product_code,
+    p.description AS product_description,
+    p.price
+FROM client c
+JOIN makes_request mr
+    ON mr.client_ID = c.client_ID
+JOIN "order" o
+    ON o.order_num = mr.order_num
+JOIN includes i
+    ON i.order_num = o.order_num
+JOIN product p
+    ON p.code = i.product_code;
+
+
+DROP VIEW IF EXISTS vw_store_performance;
+CREATE VIEW vw_store_performance AS
+SELECT
+    s.store_ID,
+    s.name AS store_name,
+    s.date_of_founding,
+    s.rating,
+    COUNT(DISTINCT r.date) AS number_of_reports,
+    COALESCE(SUM(mp.profit), 0) AS total_reported_profit,
+    COALESCE(MAX(r.overall_profit), 0) AS overall_profit,
+    COALESCE(SUM(ed.sales), 0) AS total_sales,
+    COALESCE(SUM(ed.damages), 0) AS total_damages,
+    COALESCE(SUM(ed.monthly_profit), 0) AS total_monthly_profit
+FROM store s
+LEFT JOIN report r
+    ON r.store_ID = s.store_ID
+LEFT JOIN monthly_profit mp
+    ON mp.report_date = r.date
+   AND mp.store_ID = r.store_ID
+LEFT JOIN exchanges_data ed
+    ON ed.report_date = r.date
+   AND ed.store_ID = r.store_ID
+GROUP BY
+    s.store_ID,
+    s.name,
+    s.date_of_founding,
+    s.rating;
+`;
+
+
 const database = {
     database: {
@@ -1033,4 +1585,12 @@
         await pool.query(REPORT_FUNCTIONS_SQL);
         console.log('✅ PostgreSQL report functions installed');
+    },
+
+    async installTriggersAndViews() {
+        await pool.query(TRIGGERS_SQL);
+        console.log('✅ PostgreSQL triggers installed');
+
+        await pool.query(VIEWS_SQL);
+        console.log('✅ PostgreSQL views installed');
     },
 
@@ -2162,4 +2722,5 @@
         await database.initializeDatabase();
         await database.installReportFunctions();
+        await database.installTriggersAndViews();
         console.log('✅ Database initialization completed');
     } catch (err) {