Changes between Version 17 and Version 18 of AdvancedApplicationDevelopment

Timestamp:

09/30/25 09:10:24 (2 weeks ago)

Author:

223270

Comment:

--

AdvancedApplicationDevelopment

@@ -79 +79 @@
 AFTER DELETE ON review
 FOR EACH ROW EXECUTE FUNCTION trg_recalc_dest_flag_trigger();
+}}}
+
+2. Тежинска просечна оцена, каде што помал број рецензии имаат помала тежина - без деактивирање, само со можност за admin да ги филтрира. 
+{{{
+CREATE OR REPLACE FUNCTION recalc_destination_flag_weighted(p_dest INT)
+RETURNS VOID LANGUAGE plpgsql AS $$
+DECLARE
+    avg_rating FLOAT;
+    total_reviews INT;
+    weight_rating FLOAT;
+BEGIN
+    SELECT AVG(quality), COUNT(*) INTO avg_rating, total_reviews
+    FROM review
+    WHERE id_destination = p_dest;
+
+    IF total_reviews < 5 THEN
+        weight_rating := avg_rating * (total_reviews::FLOAT / 5);
+    ELSE
+        weight_rating := avg_rating;
+    END IF;
+
+    IF total_reviews >= 1 AND COALESCE(weight_rating, 0) < 3 THEN
+        UPDATE destination SET is_flagged = TRUE WHERE id_destination = p_dest;
+    ELSE
+        UPDATE destination SET is_flagged = FALSE WHERE id_destination = p_dest;
+    END IF;
+END;
+$$;
 }}}