DatabaseProgramming: MajStore_Triggers_Functions_Procedures.sql

-- 1
create or replace function fn_insert_premium_worker()
    returns trigger
    language plpgsql
as $$
    declare completed_status_id int;
begin
    select status_id
    into completed_status_id
    from Payment_Status
    where status_name = 'Completed';

    if new.payment_status = completed_status_id then
        insert into Premium_Worker (start_date,end_date,plan_id,worker_id,payment_id)
        select
            new.payment_date,
            new.payment_date + interval '1 day' * pp.duration_days,
            new.plan_id,
            wm.worker_id,
            new.payment_id
        from Worker_Method wm
        join Premium_Plan pp
            on pp.plan_id = new.plan_id
        where wm.worker_method_id = new.worker_method_id;
    end if;
    return new;
end;
$$;

create trigger trg_insert_premium_worker
after insert or update on Payment
for each row
execute function fn_insert_premium_worker();

--test
insert into payment(amount, payment_date, worker_method_id, payment_status, plan_id)
values (100, now(), 1, 2, 1);

select * from premium_worker order by premium_id desc limit 3;






-- 2
create or replace function fn_is_worker_premium(p_worker_id int)
    returns boolean
    language plpgsql
as $$
    declare v_exists boolean;
begin
    select exists (
        select 1
        from premium_worker pw
        where pw.worker_id = p_worker_id
          and now() between pw.start_date and pw.end_date
    )
    into v_exists;

    return v_exists;
end;
$$;

-- test: true
insert into premium_worker(start_date,end_date,plan_id,worker_id,payment_id)
values (now() - interval '1 day',now() + interval '30 days',1,6630361,1);

select fn_is_worker_premium(6630361);

-- test: false
insert into premium_worker(start_date,end_date,plan_id,worker_id,payment_id)
values (now() - interval '30 days',now() - interval '1 day',1,6630362,1);

select fn_is_worker_premium(6630362);






-- 3
create or replace function fn_notify_worker_premium_start()
    returns trigger
    language plpgsql
as $$
    declare v_user_id  int; v_plan_name text;
begin
    select w.user_id
    into v_user_id
    from worker w
    where w.worker_id = new.worker_id;

    select pp.name
    into v_plan_name
    from premium_plan pp
    where pp.plan_id = new.plan_id;

    insert into notification(message, is_read, created_at, user_id)
    values ('Your Premium subscription (' || v_plan_name || ') is now active until ' || to_char(new.end_date, 'DD Mon YYYY'),false,now(),v_user_id);

    return new;
end;
$$;

create trigger trg_notify_worker_premium_start
    after insert on premium_worker
    for each row
execute function fn_notify_worker_premium_start();


--test
insert into premium_worker(start_date, end_date, plan_id, worker_id, payment_id)
values (now(), now() + interval '30 days', 1, 6630361, 1);

select * from notification
where user_id = (select user_id from worker where worker_id = 6630361)
order by created_at desc
limit 5;






-- 4
create or replace function fn_prevent_review_without_hire()
    returns trigger
    language plpgsql
as $$
    declare v_worker_id int;
begin
    select ws.worker_id
    into v_worker_id
    from worker_specialty ws
    where ws.worker_specialty_id = new.worker_specialty_id;

    if v_worker_id is null then
        raise exception 'Worker_Specialty % not found.', new.worker_specialty_id;
    end if;

    if not exists (
        select 1
        from application a
            join application_status s on a.status_id  = s.status_id
            join post p on a.post_id = p.post_id
            join post_specialty ps on ps.post_id = p.post_id
            join worker_specialty ws on ws.worker_specialty_id = new.worker_specialty_id
        where a.worker_id = v_worker_id
          and p.user_id = new.reviewer_id
          and s.status_name = 'Accepted'
          and ps.specialty_id = ws.specialty_id
    ) then
        raise exception 'User % has not hired worker % for this specialty, and cannot review them.', new.reviewer_id, v_worker_id;
    end if;

    return new;
end;
$$;

create trigger trg_prevent_review_without_hire
    before insert on Review
    for each row
execute function fn_prevent_review_without_hire();

--test should fail
insert into review(grade, created_at, reviewer_id, worker_specialty_id)
values (5, '2026-05-14 12:00:00.000', 218486, 1);

--test should pass
insert into review(grade, created_at, reviewer_id, worker_specialty_id)
values (5, '2026-05-14 12:00:00.000', 366799, 2);








-- 5
create or replace function block_application_to_closed_post()
    returns trigger
    language plpgsql
as $$
    declare v_status_name text;
begin
    select ps.status_name
    into v_status_name
    from post p
    join post_status ps on p.status_id = ps.status_id
    where p.post_id = new.post_id;

    if v_status_name is null
       then raise exception 'Post % does not exist.', new.post_id;
    end if;

    if v_status_name <> 'Active' then
        raise exception 'Cannot apply to post %: status is "%", only "Active" posts accept applications.', new.post_id, v_status_name;
    end if;

    return new;
end;
$$;

create trigger trg_block_application_to_closed_post
before insert on application
for each row execute function block_application_to_closed_post();

-- test
insert into Application(message,needed_time,expected_price,created_at,worker_id,post_id,status_id)
values('I need work','09:00:00',200,'2025-05-06 17:36:15.015',6630361,11,1);






-- 6
create or replace function prevent_duplicate_application()
    returns trigger
    language plpgsql
as $$
begin
    if exists (
        select 1
        from application a
        join application_status s on a.status_id = s.status_id
        where a.worker_id = new.worker_id
          and a.post_id = new.post_id
          and s.status_name not in ('Cancelled')
    ) then
        raise exception 'Worker % already has an application for post %.', new.worker_id, new.post_id;
    end if;

    return new;
end;
$$;

create trigger trg_prevent_duplicate_application
before insert on application
for each row execute function prevent_duplicate_application();

-- test
insert into Application(message,needed_time,expected_price,created_at,worker_id,post_id,status_id)
values('I need work','09:00:00',200,'2025-05-06 17:36:15.015',6630361,9,1);






-- 7
create or replace function fn_auto_create_calendar()
    returns trigger
    language plpgsql
as $$
    declare v_worker_id int;
begin
    v_worker_id := new.worker_id;

    insert into calendar(worker_id)
    values (v_worker_id);

    return new;
end;
$$;

create trigger trg_auto_create_calendar
after insert on worker
for each row execute function fn_auto_create_calendar();

--test
insert into worker(bio, works_remote, user_id, location_id)
values ('Experienced electrician', true, 70728, 1);

select * from calendar where worker_id = (select max(worker_id) from worker);
select * from worker where worker_id = 6772445;






-- 8
create or replace function fn_auto_create_worker_notification()
    returns trigger
    language plpgsql
as $$
    declare
        v_worker_user_id int;
        v_status_name    text;
begin
    select s.status_name
    into  v_status_name
    from  application_status s
    where s.status_id = new.status_id;

    if v_status_name in ('Accepted', 'Rejected', 'Deleted') then
        select w.user_id
        into v_worker_user_id
        from worker w
        where w.worker_id = new.worker_id;

        insert into notification(message, is_read, created_at, user_id)
        values (
            'Your application has been ' || v_status_name,
            false,
            now(),
            v_worker_user_id
        );
    end if;

    return new;
end;
$$;

create trigger trg_auto_create_worker_notification
after update on application
for each row execute function fn_auto_create_worker_notification()


--test
update application
set status_id = 2
where application_id = 22279;

select * from notification
where user_id = (select w.user_id from worker w where w.worker_id = 6746074)
order by created_at desc
limit 5;






-- 9
create or replace procedure sp_reject_pending_applications(p_post_id int)
    language plpgsql
as $$
declare
    v_rejected_status_id int;
    v_completed_post_status_id int;
    v_current_post_status text;
begin
    select ps.status_name
    into v_current_post_status
    from post p join post_status ps on ps.status_id = p.status_id
    where p.post_id = p_post_id;

    if v_current_post_status is null then
        raise exception 'Post % does not exist.', p_post_id;
    end if;

    if v_current_post_status <> 'Active' then
        raise exception 'Post % is not active, current status is "%".', p_post_id, v_current_post_status;
    end if;

    select status_id
    into v_completed_post_status_id
    from post_status
    where status_name = 'Completed';

    update post
    set status_id = v_completed_post_status_id
    where post_id = p_post_id;

    select status_id
    into v_rejected_status_id
    from application_status
    where status_name = 'Rejected';

    update application
    set status_id = v_rejected_status_id
    where post_id = p_post_id
    and status_id not in (
        select status_id from application_status
        where status_name in ('Accepted', 'Deleted')
    );
end;
$$;

-- test
call sp_reject_pending_applications(19);

select ps.status_name from post p
join post_status ps on ps.status_id = p.status_id
where p.post_id = 19;

select a.application_id, s.status_name
from application a
join application_status s on s.status_id = a.status_id
where a.post_id = 19;







-- 10
create or replace function fn_notify_client_new_application()
    returns trigger
    language plpgsql
as $$
    declare
        v_client_user_id int;
        v_post_title     text;
        v_worker_name    text;
begin
    select p.user_id, p.title
    into v_client_user_id, v_post_title
    from post p
    where p.post_id = new.post_id;

    select u.first_name || ' ' || u.last_name
    into v_worker_name
    from worker w
        join "User" u on u.user_id = w.user_id
    where w.worker_id = new.worker_id;

    insert into notification(message, is_read, created_at, user_id)
    values (
        'Worker ' || v_worker_name || ' has applied to your post "' || v_post_title || '"',
        false,
        now(),
        v_client_user_id
    );

    return new;
end;
$$;

create trigger trg_notify_client_new_application
after insert on application
for each row execute function fn_notify_client_new_application();

-- test
insert into application(message, needed_time, expected_price, created_at, worker_id, post_id, status_id)
values ('I can do the job', '02:00:00', 150.00, '2026-05-14 12:00:00.000', 6630372, 166254, 1);

select * from notification
where user_id = (select user_id from post where post_id = 166254)
order by created_at desc
limit 5;









-- 11
create or replace function fn_check_worker_specialty_for_post()
    returns trigger
    language plpgsql
as $$
    declare v_has_specialty bool;
begin
    select exists (
        select 1
        from worker_specialty ws
        join post_specialty ps on ps.specialty_id = ws.specialty_id
        where ws.worker_id = new.worker_id
          and ps.post_id = new.post_id
    )
    into v_has_specialty;

    if not v_has_specialty then
        raise exception
            'Worker % does not have any of the required specialties for post %.', new.worker_id, new.post_id;
    end if;

    return new;
end;
$$;

create trigger trg_check_worker_specialty_for_post
before insert on application
for each row execute function fn_check_worker_specialty_for_post();

-- test
insert into application(message, needed_time, expected_price, created_at, worker_id, post_id, status_id)
values ('I can do the job', '02:00:00', 150.00, '2026-05-14 12:00:00.000', 6630357, 166252, 1);