|| '''[[WikiStart| Home ]]''' || '''[[DatabaseCreation| Database Creation ]]''' ||
{{{#!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
}}}