Changes between Initial Version and Version 1 of ViewsScript

Timestamp:

05/20/26 10:12:01 (6 days ago)

Author:

231141

Comment:

--

ViewsScript

@@ +1 @@
+{{{#!sql
+-----------------------------
+--- WORKER GLOBAL RATINGS ---
+-----------------------------
+create  MATERIALIZED VIEW worker_rating_summary AS
+SELECT
+    w.id AS worker_id,
+    ua.id AS user_id,
+    ROUND(AVG(r.rating), 2) AS avg_rating,
+    COUNT(r.id) AS total_reviews
+FROM Worker w
+JOIN UserAccount ua ON ua.id = w.user_id
+LEFT JOIN Review r ON r.reviewed_id = ua.id
+GROUP BY w.id, ua.id;
+
+select * from worker_rating_summary where worker_id = 58064
+
+-----------------------------
+-- WORKER CATEGORY RATINGS --
+-----------------------------
+CREATE MATERIALIZED VIEW worker_category_rating_summary AS
+SELECT
+    w.id AS worker_id,
+    tr.category_id,
+    ROUND(AVG(r.rating), 2) AS avg_rating,
+    COUNT(r.id) AS total_reviews
+FROM Worker w
+JOIN UserAccount ua ON ua.id = w.user_id
+JOIN Offer o ON o.worker_id = w.id
+JOIN Task t ON t.offer_id = o.id
+JOIN TaskRequest tr ON tr.id = o.task_request_id
+JOIN Review r ON r.task_id = t.id
+              AND r.reviewed_id = ua.id
+WHERE t.status = 'COMPLETED'
+GROUP BY w.id, tr.category_id;
+
+select * from worker_category_rating_summary where worker_id=95
+
+
+-----------------------------
+--  CLIENT GLOBAL RATINGS  --
+----------------------------- 
+CREATE MATERIALIZED VIEW client_rating_summary AS
+SELECT
+    c.id AS client_id,
+    ua.id AS user_id,
+    ROUND(AVG(r.rating), 2) AS avg_rating,
+    COUNT(*) AS total_reviews
+FROM Client c
+JOIN UserAccount ua
+    ON ua.id = c.user_id
+LEFT JOIN Review r
+    ON r.reviewed_id = ua.id
+GROUP BY
+    c.id,
+    ua.id;
+
+select * from client_rating_summary where client_id=1
+-----------------------------
+--   WORKER PROFILE VIEW   --
+----------------------------- 
+create or replace VIEW worker_profile_view AS
+SELECT
+    w.id AS worker_id,
+    ua.id AS user_id,
+    ua.name,
+    ua.surname,
+    ua.email,
+    ua.phone_number,
+    w.work_mode,
+    w.service_radius_km,
+    l.city,
+    l.latitude,
+    l.longitude,
+    wrs.avg_rating,
+    wrs.total_reviews
+FROM Worker w
+JOIN UserAccount ua ON ua.id = w.user_id
+JOIN Location l ON l.id = w.location_id
+LEFT JOIN worker_rating_summary wrs
+    ON wrs.worker_id = w.id;
+
+select * from worker_profile_view where worker_id = 95
+
+
+-----------------------------
+-- Available Task Requests --
+----------------------------- 
+--(feed/open requests workers can browse) mn e sporo
+--alternativa: parametraized query
+create or replace VIEW available_task_requests_view AS
+SELECT
+    tr.id AS task_request_id,
+    tr.description,
+    tr.work_mode,
+    tr.created_at,
+
+    c.id AS client_id,
+    ua.name AS client_name,
+    ua.surname AS client_surname,
+
+    cat.id AS category_id,
+    cat.category_name,
+
+    l.city,
+
+    w.id AS worker_id,
+
+    ROUND(
+        6371 * ACOS(
+            COS(RADIANS(wl.latitude)) *
+            COS(RADIANS(l.latitude)) *
+            COS(RADIANS(l.longitude) - RADIANS(wl.longitude)) +
+            SIN(RADIANS(wl.latitude)) *
+            SIN(RADIANS(l.latitude))
+        )::NUMERIC,
+    2) AS distance_km
+
+FROM TaskRequest tr
+JOIN Client c
+    ON c.id = tr.client_id
+JOIN UserAccount ua
+    ON ua.id = c.user_id
+JOIN Category cat
+    ON cat.id = tr.category_id
+JOIN Location l
+    ON l.id = tr.location_id
+JOIN WorkerCategory wc
+    ON wc.category_id = tr.category_id
+JOIN Worker w
+    ON w.id = wc.worker_id
+JOIN Location wl
+    ON wl.id = w.location_id
+WHERE tr.status = 'OPEN'
+  AND tr.deleted_at IS NULL
+  -- cheap prefilter first
+  AND ABS(wl.latitude - l.latitude) <= 1
+  AND ABS(wl.longitude - l.longitude) <= 1
+  -- expensive exact filter second
+  AND 6371 * ACOS(
+        COS(RADIANS(wl.latitude)) *
+        COS(RADIANS(l.latitude)) *
+        COS(RADIANS(l.longitude) - RADIANS(wl.longitude)) +
+        SIN(RADIANS(wl.latitude)) *
+        SIN(RADIANS(l.latitude))
+      ) <= w.service_radius_km + 10;
+
+select * from available_task_requests_view where worker_id=95
+
+
+-----------------------------
+--   CLIENT PROFILE VIEW   --
+----------------------------- 
+CREATE VIEW client_profile_view AS
+SELECT
+    c.id AS client_id,
+    ua.id AS user_id,
+    ua.name,
+    ua.surname,
+    ua.email,
+    ua.phone_number,
+
+    COALESCE(crs.avg_rating, 0) AS avg_rating,
+    COALESCE(crs.total_reviews, 0) AS total_reviews
+
+FROM Client c
+
+JOIN UserAccount ua
+    ON ua.id = c.user_id
+
+LEFT JOIN client_rating_summary crs
+    ON crs.user_id = ua.id;
+
+select * from client_profile_view cpv where client_id=1;
+
+
+-----------------------------
+--  Matched Workers View   --
+-----------------------------    
+CREATE OR REPLACE VIEW matched_workers_view AS
+SELECT
+    w.id AS worker_id,
+
+    ua.name,
+    ua.surname,
+
+    w.work_mode,
+    w.service_radius_km,
+
+    l.city,
+
+    cat.id AS category_id,
+    cat.category_name,
+
+    wb.badge_id,
+
+    b.badge_name,
+    b.tier_level,
+    b.min_price,
+    b.max_price,
+
+    COALESCE(wcrs.avg_rating, 0) AS avg_rating,
+
+    ROUND(
+        6371 * ACOS(
+            COS(RADIANS(trl.latitude)) *
+            COS(RADIANS(l.latitude)) *
+            COS(RADIANS(l.longitude) - RADIANS(trl.longitude)) +
+            SIN(RADIANS(trl.latitude)) *
+            SIN(RADIANS(l.latitude))
+        )::NUMERIC,
+    2) AS distance_km,
+
+    tr.id AS task_request_id,
+
+    c.id AS client_id,
+
+    CASE
+        WHEN f.id IS NOT NULL THEN TRUE
+        ELSE FALSE
+    END AS is_favourite
+
+FROM TaskRequest tr
+JOIN Location trl
+    ON trl.id = tr.location_id
+JOIN Client c
+    ON c.id = tr.client_id
+JOIN WorkerCategory wc
+    ON wc.category_id = tr.category_id
+JOIN Worker w
+    ON w.id = wc.worker_id
+JOIN UserAccount ua
+    ON ua.id = w.user_id
+JOIN Location l
+    ON l.id = w.location_id
+JOIN Category cat
+    ON cat.id = tr.category_id
+LEFT JOIN worker_category_rating_summary wcrs
+    ON wcrs.worker_id = w.id
+   AND wcrs.category_id = cat.id
+LEFT JOIN WorkerBadge wb
+    ON wb.worker_id = w.id
+   AND wb.is_active = TRUE
+LEFT JOIN Badge b
+    ON b.id = wb.badge_id
+   AND b.category_id = cat.id
+LEFT JOIN Favourite f
+    ON f.worker_id = w.id
+   AND f.client_id = c.id
+WHERE tr.status = 'OPEN'
+  AND tr.deleted_at IS NULL
+  -- work mode filter EARLY
+  AND (
+         tr.work_mode = 'REMOTE'
+      OR w.work_mode = 'REMOTE'
+      OR tr.work_mode = w.work_mode
+      OR tr.work_mode = 'HYBRID'
+      OR w.work_mode = 'HYBRID'
+  )
+  -- cheap geo prefilter
+  AND ABS(l.latitude - trl.latitude) <= 1
+  AND ABS(l.longitude - trl.longitude) <= 1
+  -- exact distance filter
+  AND 6371 * ACOS(
+        COS(RADIANS(trl.latitude)) *
+        COS(RADIANS(l.latitude)) *
+        COS(RADIANS(l.longitude) - RADIANS(trl.longitude)) +
+        SIN(RADIANS(trl.latitude)) *
+        SIN(RADIANS(l.latitude))
+      ) <= w.service_radius_km;
+     
+select * from matched_workers_view where task_request_id = 118
+
+-----------------------------
+--  Task Request Offers   --
+----------------------------- 
+CREATE OR REPLACE VIEW task_request_offers_view AS
+
+WITH offer_data AS (
+    SELECT
+        o.id AS offer_id,
+
+        o.price,
+        o.initiated_by,
+        o.created_at AS offer_created_at,
+
+        tr.id AS task_request_id,
+        tr.client_id,
+
+        w.id AS worker_id,
+
+        ua.name AS worker_name,
+        ua.surname AS worker_surname,
+
+        w.work_mode,
+
+        COALESCE(wrs.avg_rating, 0) AS avg_rating,
+        COALESCE(wrs.total_reviews, 0) AS total_reviews,
+
+        b.badge_name,
+        b.tier_level,
+
+        ROUND(
+            6371 * ACOS(
+                COS(RADIANS(wl.latitude)) *
+                COS(RADIANS(trl.latitude)) *
+                COS(RADIANS(trl.longitude) - RADIANS(wl.longitude)) +
+                SIN(RADIANS(wl.latitude)) *
+                SIN(RADIANS(trl.latitude))
+            )::NUMERIC,
+        2) AS distance_km,
+
+        CASE
+            WHEN f.id IS NOT NULL THEN TRUE
+            ELSE FALSE
+        END AS is_favourited
+
+    FROM Offer o
+
+    JOIN Worker w
+        ON w.id = o.worker_id
+
+    JOIN UserAccount ua
+        ON ua.id = w.user_id
+
+    JOIN worker_rating_summary wrs
+        ON wrs.worker_id = w.id
+
+    JOIN Location wl
+        ON wl.id = w.location_id
+
+    JOIN TaskRequest tr
+        ON tr.id = o.task_request_id
+
+    JOIN Location trl
+        ON trl.id = tr.location_id
+
+    LEFT JOIN WorkerBadge wb
+        ON wb.worker_id = w.id
+       AND wb.is_active = TRUE
+
+    LEFT JOIN Badge b
+        ON b.id = wb.badge_id
+       AND b.category_id = tr.category_id
+
+    LEFT JOIN Favourite f
+        ON f.worker_id = w.id
+       AND f.client_id = tr.client_id
+
+    WHERE o.offer_status = 'PENDING'
+      AND o.deleted_at IS NULL
+)
+
+SELECT
+    *,
+
+    price = MIN(price)
+        OVER (PARTITION BY task_request_id)
+    AS is_cheapest,
+
+    avg_rating = MAX(avg_rating)
+        OVER (PARTITION BY task_request_id)
+    AS is_top_rated,
+
+    distance_km = MIN(distance_km)
+        OVER (PARTITION BY task_request_id)
+    AS is_closest
+
+FROM offer_data;
+
+select * from task_request_offers_view where task_request_id = 5000001
+
+-----------------------------
+--     Active taks view    --
+----------------------------- 
+CREATE OR REPLACE VIEW active_tasks_view AS
+SELECT t.id         AS task_id,
+       t.created_at AS task_started_at,
+       o.price,
+       o.worker_id  AS worker_id,
+       wu.name      AS worker_name,
+       wu.surname   AS worker_surname,
+       tr.client_id,
+       cu.name      AS client_name,
+       cu.surname   AS client_surname,
+       cat.category_name,
+       l.city
+FROM Task t
+         JOIN Offer o ON o.id = t.offer_id
+         JOIN Worker w ON w.id = o.worker_id
+         JOIN UserAccount wu ON wu.id = w.user_id
+         JOIN TaskRequest tr ON tr.id = o.task_request_id
+         JOIN Client c ON c.id = tr.client_id
+         JOIN UserAccount cu ON cu.id = c.user_id
+         JOIN Category cat ON cat.id = tr.category_id
+         JOIN Location l ON l.id = tr.location_id
+WHERE t.status = 'ACTIVE';
+
+select * from active_tasks_view where worker_id=95
+
+-----------------------------
+--     TASK CHAT           --
+----------------------------- 
+create VIEW task_chat_view AS
+SELECT
+    m.id                                AS message_id,
+    m.text,
+    m.created_at,
+    m.task_id,
+    m.sender_id,
+    ua.name                             AS sender_name,
+    ua.surname                          AS sender_surname,
+    CASE
+        WHEN ua.id = w.user_id THEN 'WORKER'
+        WHEN ua.id = c.user_id THEN 'CLIENT'
+    END                                 AS sender_role,
+    t.status                            AS task_status,
+    o.worker_id,
+    tr.client_id
+FROM Message        m
+JOIN Task           t  ON t.id  = m.task_id
+                      AND t.status = 'ACTIVE'
+JOIN Offer          o  ON o.id  = t.offer_id
+JOIN TaskRequest    tr ON tr.id = o.task_request_id
+JOIN Worker         w  ON w.id  = o.worker_id
+JOIN Client         c  ON c.id  = tr.client_id
+JOIN UserAccount    ua ON ua.id = m.sender_id;
+
+ select * from task_chat_view where task_id = 213
+-----------------------------
+--    COMPLETED TASKS      --
+----------------------------- 
+CREATE VIEW completed_tasks_view AS
+SELECT
+    t.id                AS task_id,
+    t.created_at        AS task_started_at,
+    t.updated_at        AS task_completed_at,
+    o.price,
+    o.worker_id,
+    wu.name             AS worker_name,
+    wu.surname          AS worker_surname,
+    tr.client_id,
+    cu.name             AS client_name,
+    cu.surname          AS client_surname,
+    tr.category_id,
+    cat.category_name,
+    l.city
+FROM Task           t
+JOIN Offer          o   ON o.id   = t.offer_id
+JOIN TaskRequest    tr  ON tr.id  = o.task_request_id
+JOIN Worker         w   ON w.id   = o.worker_id
+JOIN UserAccount    wu  ON wu.id  = w.user_id
+JOIN Client         c   ON c.id   = tr.client_id
+JOIN UserAccount    cu  ON cu.id  = c.user_id
+JOIN Category       cat ON cat.id = tr.category_id
+JOIN Location       l   ON l.id   = tr.location_id
+WHERE t.status = 'COMPLETED';
+
+select * from completed_tasks_view where worker_id = 95
+
+-----------------------------
+--  TASK PAYMENT SUMMARY   --
+----------------------------- 
+-- client payment history, worker earnings history, admin payment monitoring
+CREATE VIEW task_payment_summary_view AS
+SELECT t.id         AS task_id,
+       p.id         AS payment_id,
+       p.amount,
+       p.payment_method,
+       p.status     AS payment_status,
+       p.created_at AS payment_date,
+       c.id        AS client_id,
+       cu.name      AS client_name,
+       cu.surname   AS client_surname,
+       w.id        AS worker_id,
+       wu.name      AS worker_name,
+       wu.surname   AS worker_surname,
+       cat.category_name
+FROM Payment p
+         JOIN Task t ON t.id = p.task_id
+         JOIN Client c ON c.id = p.client_id
+         JOIN UserAccount cu ON cu.id = c.user_id
+         JOIN Worker w ON w.id = p.worker_id
+JOIN UserAccount wu ON wu.id = w.user_id
+JOIN Offer o ON o.id = t.offer_id
+JOIN TaskRequest tr ON tr.id = o.task_request_id
+JOIN Category cat ON cat.id = tr.category_id;
+
+select * from task_payment_summary_view where worker_id = 95
+
+-----------------------------
+--  OPEN COMPLAINTS VIEW   --
+----------------------------- 
+CREATE VIEW open_complaints_view AS
+SELECT
+    comp.id             AS complaint_id,
+    comp.reason,
+    comp.description,
+    comp.status,
+    comp.created_at,
+    comp.task_id,
+    cu.name             AS client_name,
+    cu.surname          AS client_surname,
+    wu.name             AS worker_name,
+    wu.surname          AS worker_surname,
+    cat.category_name
+FROM Complaint      comp
+JOIN Client         c   ON c.id   = comp.client_id
+JOIN UserAccount    cu  ON cu.id  = c.user_id
+JOIN Worker         w   ON w.id   = comp.worker_id
+JOIN UserAccount    wu  ON wu.id  = w.user_id
+JOIN Task           t   ON t.id   = comp.task_id
+JOIN Offer          o   ON o.id   = t.offer_id
+JOIN TaskRequest    tr  ON tr.id  = o.task_request_id
+JOIN Category       cat ON cat.id = tr.category_id
+WHERE comp.status = 'OPEN';
+
+select * from open_complaints_view
+}}}