QueryOptimization: MajStore_Views_Indexes.sql

-- View 1
create or replace view vw_worker_premium_analytics as
select w.worker_id,
    date_trunc('month', py.payment_date) as month,
    pp.plan_id, pp.name AS plan_name,
    COUNT(pw.premium_id) filter ( where ps.status_name = 'Completed' ) as total_plans_bought,
    SUM(py.amount) filter ( where ps.status_name = 'Completed' ) as total_spent,
    MIN(pw.start_date) as first_plan_start,
    MAX(pw.end_date) as last_plan_end
from Premium_Worker pw
    join Worker w on w.worker_id = pw.worker_id
    join Payment py on py.payment_id = pw.payment_id
    join Payment_Status ps on ps.status_id = py.payment_status
    join Worker_Method wm on wm.worker_method_id = py.worker_method_id
    join Premium_Plan pp on pp.plan_id = pw.plan_id
group by w.worker_id, date_trunc('month', py.payment_date), pp.plan_id, pp.name;

-- test
select * from vw_worker_premium_analytics
where worker_id = 6630371;




-- View 2
create or replace view vw_owner_payment_analytics_dashboard as
with daily_stats as (
    select
        date_trunc('day', py.payment_date) as day,
        pm.method_id,
        pm.method_name,
        pm.tax,
        COUNT(py.payment_id) as n_per_method_per_day,
        SUM(py.amount) as t_per_method_day
    from Payment py
    join Payment_Status ps on ps.status_id = py.payment_status
    join Worker_Method wm on wm.worker_method_id = py.worker_method_id
    join Payment_Method pm on pm.method_id = wm.method_id
    where ps.status_name = 'Completed'
    group by date_trunc('day', py.payment_date), pm.method_id, pm.method_name, pm.tax
), ranked as (
    select *,
           SUM(n_per_method_per_day) over (partition by day) as t_n_payments_day,
           SUM(t_per_method_day) over (partition by day) as t_amount_day,
           SUM(t_per_method_day * tax) OVER (partition by day) as t_tax_day,
           ROW_NUMBER() over (partition by day ORDER BY n_per_method_per_day desc) as rn
    from daily_stats
)
select
    day,
    t_amount_day as total_earned,
    t_n_payments_day as total_transactions,
    ROUND(t_tax_day::NUMERIC, 2) as total_tax_collected,
    method_id as most_used_method_id,
    method_name as most_used_method,
    ROUND((n_per_method_per_day::NUMERIC / NULLIF(t_n_payments_day, 0)) * 100, 2) as most_used_method_pct
from ranked
where rn = 1;

-- test
select * from vw_owner_payment_analytics_dashboard
where day = '2024-11-17 00:00:00.000';






-- View 3
create or replace view vw_review_analytics as
select
    ws.worker_id,
    u.username,
    s.specialty_name,
    ws.specialty_id,
    (
        select avg(r.grade)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
    ) as avg_grade,
    (
        select count(*)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
    ) as total_reviews,
    (
        select count(*)
        from Review_Comment rc
        where rc.review_id in (
            select r.review_id
            from Review r
            where r.worker_specialty_id = ws.worker_specialty_id
        )
    ) as total_comments,
    (
        select count(*)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
          and r.grade = 1
    ) as one_star,
    (
        select count(*)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
          and r.grade = 2
    ) as two_star,
    (
        select count(*)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
          and r.grade = 3
    ) as three_star,
    (
        select count(*)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
          and r.grade = 4
    ) as four_star,
    (
        select count(*)
        from Review r
        where r.worker_specialty_id = ws.worker_specialty_id
          and r.grade = 5
    ) as five_star
from Worker_Specialty ws
    join specialty s on ws.specialty_id = s.specialty_id
    join worker w on ws.worker_id = w.worker_id
    join "User" u on u.user_id = w.user_id;

-- test
select * from vw_review_analytics
where worker_id = 6630378;







-- View 4
create or replace view vw_worker_aggregate as
select
    w.worker_id,
    u.first_name,
    u.last_name,
    u.username,
    count(distinct a.application_id) as total_applications,
    count(distinct a.application_id) filter (where apps.status_name = 'Accepted') as accepted_applications,
    count(distinct a.application_id) filter (where apps.status_name = 'Rejected') as rejected_applications,

    round(count(distinct a.application_id) filter (
        where apps.status_name = 'Accepted'
        )::numeric   / count(distinct a.application_id)*100,2) as accepted_percentage,

    round(count(distinct a.application_id) filter (
        where apps.status_name = 'Rejected'
        )::numeric   / count(distinct a.application_id)*100,2) as rejected_percentage,

    count(distinct a.application_id) filter (
        where apps.status_name = 'Accepted'and ps.status_name = 'Completed'
        ) as completed_jobs

from  worker  w
    join "User"  u on u.user_id= w.user_id
    join application a on a.worker_id= w.worker_id
    join application_status apps on apps.status_id = a.status_id
    join post po on po.post_id  = a.post_id
    join post_status  ps   on ps.status_id  = po.status_id
group by w.worker_id, u.first_name, u.last_name, u.username;

-- test
select *
from vw_worker_aggregate
where worker_id = 6630378;

insert into application (message, needed_time, expected_price, created_at, worker_id, post_id, status_id)
        values ('Adding a new application', '01:00:00', 5000, '2023-06-22 14:00:03.945', 6722966, 4137859, 3)
update Application set expected_price=5509 where application_id = 8308242;

-- index
create index idx_application_worker_id on application(worker_id);





-- View 5

create materialized view vw_app_aggregate as
select
    count(distinct u.user_id) as total_users,
    count(distinct p.post_id) as total_posts,
    count(distinct p.post_id) filter (where ps.status_name = 'Active') as active_posts,
    count(distinct p.post_id) filter (where ps.status_name = 'Completed') as completed_posts,
    count(distinct p.post_id) filter (where ps.status_name = 'Deleted') as deleted_posts,
    round(count(distinct p.post_id) filter (where ps.status_name = 'Active')::numeric
        / count(distinct p.post_id) * 100, 2) as active_post_pct,
    count(distinct a.application_id) as total_applications,
    (select max(p2.created_at) from post p2) as latest_post_date
from "User" u
    left join post p on p.user_id = u.user_id
    left join post_status ps on ps.status_id = p.status_id
    left join application a on a.post_id = p.post_id;

-- test
select *
from vw_app_aggregate;

insert into post (title, description, created_at, user_id, location_id, status_id)
        values ('Needing a plumber', 'The bath is leaking', '2024-03-07 18:52:55.693', 503189, 9199, 2)

update post
set description = 'The bath is leaking from the bottom'
where user_id = 503189 and created_at = '2024-03-07 18:52:55.693'






-- View 6
create or replace view vw_location_aggregate as
select
    l.location_id,
    l.city,
    l.region,
    count(distinct p.post_id) as total_posts,
    count(distinct p.post_id) filter (where ps.status_name = 'Active') as active_posts,
    count(distinct p.post_id) filter (where ps.status_name = 'Completed') as completed_posts,
    count(distinct w.worker_id) as total_workers,
    count(distinct w.worker_id) filter (where w.works_remote = true) as remote_workers
from location l
    left join post p on p.location_id= l.location_id
    left join post_status ps on ps.status_id = p.status_id
    left join worker w on w.location_id = l.location_id
group by l.location_id, l.city, l.region;

--test
select *
from vw_location_aggregate
where location_id = 7;

-- index
create index idx_post_location on Post(location_id);







-- View 7
create or replace view vw_post_full_details as
select p.post_id, p.title, p.description, p.created_at,
   ps.status_name,
   u.user_id, u.first_name, u.last_name, u.username,
   l.city, l.region, l.address,

   array_agg(distinct s.specialty_name)  as specialties,
   array_agg(distinct pi.image_id) as image_ids,
   count(distinct a.application_id)  as total_applications,
   count(distinct a.application_id) filter ( where apps.status_name = 'Accepted') as accepted_applications,
   count(distinct a.application_id) filter (where apps.status_name = 'Rejected') as rejected_applications,
   round(avg(a.expected_price)::numeric, 2) as avg_expected_price,
   min(a.expected_price) as min_expected_price,
   max(a.expected_price) as max_expected_price

from post p
        join post_status ps on ps.status_id  = p.status_id
        join "User" u  on u.user_id = p.user_id
        join location l  on l.location_id = p.location_id
        join post_specialty psp  on psp.post_id   = p.post_id
        join specialty s on s.specialty_id = psp.specialty_id
        join post_image pi on pi.post_id = p.post_id
        join application a on a.post_id = p.post_id
        join application_status apps on apps.status_id = a.status_id

group by p.post_id, p.title, p.description, p.created_at,
   ps.status_name,
   u.user_id, u.first_name, u.last_name, u.username,
   l.city, l.region, l.address;

-- test
select *
from vw_post_full_details
where post_id = 1539670;

insert into Post_Specialty(post_id, specialty_id)
        values (919803, 1);

insert into Post_Image(image_file, post_id)
        values ('x48562878a554caab5dd263b0e66a3ff246d272d4988137609211e640faddb168967b0ae78a446bebbd26b6c6386d548e8f38474363c68595cf054f3a212ebae567640dc8227c9fd60a17bb3c59226226ba83612c47e89d3c02c68350a1f025a4d1d8d1356894e72614763be27b43f84208d1f08e07ff92e9b6f524e8da169885b41621dbdd1438376bb0215285e1ff3817ffce1dbe2eb04d3a8d9ba5078f853e3b7eb87d9343b0c0741c788e669a2b16d5d616675c16c53798dbfee28139377bc15f99f7fe10e382162519eda8f81a7301371f33c9fd857605c5a30135ff4fd2015cf7fc8675971d448a197c5c6d91857b35ab3601d5c5b3e8722da96c3cc93eaf31f7569e864e9e628421b6338e0491cbe407a3243452ee2007a4fe4c20f6c91c50be875b6178f04605c8fc3b762700d12255c916f35e2e9367fa9798fecf5a88fe3d9c1b0d1154b3779fde45bd44376de95dc5eea787bb868f23c14adf8870035acad57427cf33854b47361d57d5fdcde2b187105373f3b93916efe20fa708af47f74ff04a748575e9594dffc4c8296f6182c27179430faf22286825f378a6fe78d23f2bab6ef1b5e762909bd1dc14bc0f7eaff94d8f9e34a03d6a74782241064b89514fe6a7617a0c4d71d25d906ecf7eec27cfa34728fce19acb70f36210214a54f53c191d0ddc0236586fce596f55b714073af9ffd8e1db90b732c846cbd01867dd0f15652110a336814085a5607e986d489440f7a83e4a9fac17b3918abee978fab3a855ae38e0a7a83ef84e1032191f560f2f6c82f82dad394c54df376bfecfcc8ca65f384742d46756a4d0a25382e33f6c90f50be4fb2e13cbcab91e420ae3c8a6dc2f72fa11f1e8cafce053c7e04ecfb522e5f31de9676b5aaafa2685c2cc4c900a4e43f3d7fd6554f414a594392522cbe7fba08eb640e7331e1c12451437fd0ffe553fe873076428ba59ca433de619e792daa20aa67e9b6c95e4535c8abd1fa3bd2c77255bac6e25250174cc146107e6969425c1472fe43df4f2544984205f2d239f92210bb5f2bf6227571f47b0d4ed375b8586d9bc247dce226d0775423651f0cc5680ea32b94f3cdac65046ff82cc1127be50dafced937f70c4a4ac39fa2636f3b13378fac28398f9f0b44a52683613ed6e5e61fa8c83b0604ac7459d55d67ee5d5983b9cdf068cef996d87546ed39080af3fd2124f7cb702f8a3c405a0a28374dcb0f71a8c2fc5fc6bc9d15e3f8b88c535dc5347831f59b23adf1ea9ca691bc2a554dd27c507db2752601958732b19ac3763a5aac25abcc14278e6a02a5c891f1162db132aeebd49e3ea61ce0405638b8a1e71a968d85ded791480b956e4e0c2729e1bcb1bb4156ea8fc320d94626ce714f575b7e0e34a75ec62074259bc8cb91869b7910407538b2f710fd6c8a1bd8bd114551765384d12e9b8ff06a40c85461a45d5da8ffede03ebbc6a6e37fb258d68bf95c331a2fd86d36b1d3a96c813e0fa2ce29dc4c64b17d4ef1aeb7bd2a73a5405e91bc63585d7fa5597a7e8b297a5f2ba6a2b882f12f5a81f', 1863168);

--index
create index idx_application_post_id on Application(post_id);
create index idx_post_specialty_post_id on Post_Specialty(post_id);
create index idx_post_image_post_id on Post_Image(post_id);







-- View 8
create or replace view vw_user_notifications as
select
   u.user_id,
   u.first_name,
   u.last_name,
   u.username,
   n.notification_id,
   n.message,
   n.is_read,
   n.created_at as notification_created_at
from "User" u
   join notification n on n.user_id = u.user_id
order by n.created_at desc

-- test
select *
from vw_user_notifications
where user_id = 903515;

insert into notification(message, is_read, created_at, user_id)
        values ('New worker has sent you an application', false, '2026-03-13 02:20:26.203', 210032)

update notification
set is_read = true
where user_id = 210032 and created_at = '2026-03-13 02:20:26.203'








-- View 9
create or replace view vw_worker_applications as
select
    w.worker_id,
    u.first_name,
    u.last_name,
    a.application_id,
    a.message,
    a.expected_price,
    a.needed_time,
    a.created_at,
    apps.status_name as application_status,
    p.post_id,
    p.title as post_title,
    p.description as post_description,
    ps.status_name as post_status,
    l.city,
    l.region
from worker w
    join "User" u on u.user_id  = w.user_id
    join application a on a.worker_id = w.worker_id
    join application_status apps on apps.status_id = a.status_id
    join post p on p.post_id = a.post_id
    join post_status ps on ps.status_id = p.status_id
    left join location l on l.location_id = p.location_id;

-- test
select *
from vw_worker_applications
where worker_id = 6630371;