DatabaseCreation: MajStore_DML.sql

--user management and roles ---------------------------------------------------------------------------------------------------------

-- Permission - import CSV

insert into Role (role_name)
values ('Admin'),
       ('Client'),
       ('Worker');

-- Role_Permission - import CSV


-- user ------------------------------------------------------------------------------------------------------------------------
create table if not exists temp_names(name text);                   -- import CSV
create table if not exists temp_surnames(surname text);             -- import CSV

create or replace procedure insert_users(batch_size INT, total INT) -- insert-от беше премногу спор на сервер, па употребивме процедура
    language plpgsql
as
$$
declare
    i int := 0;
begin
    while i < total
        loop
            insert into "User" (first_name, last_name, username, email, password_hash)
            select mn.name,
                   s.surname,
                   lower(mn.name) || '_' || lower(s.surname) || (i + gs),
                   lower(mn.name) || '.' || lower(s.surname) || (i + gs) || '@gmail.com',
                   md5(mn.name || s.surname || (i + gs))
            from generate_series(1, batch_size) gs
                cross join lateral (
                select name
                from temp_names
                where random() + gs * 0 is not null
                order by random()
                limit 1
                ) mn
                cross join lateral (
                select surname
                from temp_surnames
                where random() + gs * 0 is not null
                order by random()
                limit 1
                ) s
            order by random();

            i := i + batch_size;

            COMMIT;
            raise notice 'Inserted % / %', i, total;
        end loop;
end;
$$;

call insert_users(15000, 240000);






-- worker
insert into User_Role (role_id, user_id)
select 3, w.user_id
from Worker w
on conflict (user_id, role_id) do nothing;

-- admin
insert into User_Role (role_id, user_id)
select 1, u.user_id
from "User" u
where u.user_id not in (
    select user_id from User_Role
)
limit 20;

-- user
insert into User_Role (role_id, user_id)
select 2, u.user_id
from "User" u
where not exists (
    select 1
    from User_Role ur
    where ur.user_id = u.user_id
);





create table temp_Notification (
    notification_id serial primary key,
    message text not null,
    is_read bool not null,
    created_at timestamp not null ,
    user_id int,
    constraint fk_user foreign key (user_id) references "User"(user_id) ON DELETE CASCADE
);

insert into temp_Notification (message, is_read, created_at, user_id)
select
    'Your premium plan is activated',
    (random() < 0.5),
    pw.start_date,
    w.user_id
from premium_worker pw
join Worker w on w.worker_id = pw.worker_id;

insert into temp_Notification (message, is_read, created_at, user_id)
select
    case
        when s.status_name = 'Accepted' then 'Your application was accepted'
        when s.status_name = 'Rejected' then 'Your application was rejected'
    end,
    false,
    a.created_at,
    w.user_id
from Application a
join Application_Status s on s.status_id = a.status_id
join Worker w on w.worker_id = a.worker_id
where s.status_name in ('Accepted', 'Rejected');

insert into temp_Notification (message, is_read, created_at, user_id)
select
    'New worker has sent you an application',
    false,
    a.created_at,
    p.user_id
from Application a
join Post p on p.post_id = a.post_id;

insert into Notification
select *
from temp_Notification
order by random();



-- Location - import CSV


-- worker ------------------------------------------------------------------------------------------------------------------------

create table if not exists temp_worker_bios(bio text);                      -- import CSV

create or replace procedure insert_workers( batch_size int4,  total int4)   -- insert-от беше премногу спор на сервер, па употребивме процедура
    language plpgsql
as $$
declare
    i int := 0;
begin
    while i < total loop

        insert into Worker (bio, works_remote, location_id, user_id)
        select
            b.bio,
            (random() > 0.5) AS works_remote,
            l.location_id,
            u.user_id
        from (
            select user_id, random() AS r
            from "User"
            order by random()
            limit batch_size
        ) u
        join lateral (
            select location_id
            from Location
            order by (random() + u.r)
            limit 1
        ) l on true
        join lateral (
            select bio
            from temp_worker_bios
            order by (random() + u.r)
            limit 1
        ) b on true;

        i := i + batch_size;
        COMMIT;
        raise notice 'Inserted % / %', i, total;

    end loop;
end;
$$;

call insert_workers(1000,100000);




create or replace procedure insert_calendar(batch_size INT, total INT)
    language plpgsql
as
$$
declare
    i INT := 0;
begin
    while i < total loop
        insert into calendar (worker_id)
        select
            w.worker_id
        from (
            select worker_id
            from Worker
            order by worker_id
            limit batch_size
            offset i
        ) w;
        i := i + batch_size;

        COMMIT;
        raise notice 'Inserted % / % posts', i, total;
    end loop;
end;
$$;

call insert_calendar(5000,142000);





insert into Day (day_name) values
    ('Monday'),
    ('Tuesday'),
    ('Wednesday'),
    ('Thursday'),
    ('Friday'),
    ('Saturday'),
    ('Sunday');





insert into Work_Day (start_time, end_time, calendar_id, day_id)
with random_calendars as (
    select calendar_id from Calendar
),
random_days as (
    select day_id from Day
),
generated_slots as (
    select
        c.calendar_id,
        d.day_id,
        ( now() - interval '2 years'
    + (random() * (interval '2 years 1 month'))
    + interval '8 hours'
    + (interval '1 hour' * floor(random() * 9))
    ) as start_time,
        (floor(random() * 5) + 1)::int as duration_hours
    from random_calendars c
    cross join random_days d
    where random() < 0.7
)
select
    gs.start_time,
    gs.start_time + (interval '1 hour' * gs.duration_hours),
    gs.calendar_id,
    gs.day_id
from generated_slots gs
order by random()
limit 1500000;






create table temp_calendar_event(title text, description text);   -- import CSV

create or replace procedure insert_calendar_events(batch_size int, total int)
    language plpgsql
as $$
declare
    i int := 0;
begin
    while i < total loop
        insert into calendar_event (title, description, start_time, end_time, calendar_id)
        select
            t.title,
            t.description,
            x.event_start as start_time,
            x.event_start
                + ((1 + floor(random() * 3)) * interval '1 hour') as end_time,
            x.calendar_id
        from (
            select
                calendar_id,
                start_time + ((floor(random() * 3)) * interval '1 hour') as event_start,
                row_number() over (order by random()) as rn
            from work_day
            order by random()
            limit batch_size
        ) x
        join (
            select
                title,
                description,
                row_number() over (order by random()) as rn
            from temp_calendar_event
            order by random()
            limit batch_size
        ) t on t.rn = x.rn;

        i := i + batch_size;
        COMMIT;
        raise notice 'inserted % / %', i, total;

    end loop;
end;
$$;

call insert_calendar_events(10000, 5000000);







create table if not exists temp_cert_names(name text);          -- import CSV
create table if not exists temp_cert_issuers(issuer text);      -- import CSV

with cert_names as (
    select name, row_number() over (order by random()) as rn
    from temp_cert_names
),
     issuers as (
         select issuer, row_number() over (order by random()) as rn
         from temp_cert_issuers
     ),
     random_workers as (
         select worker_id, row_number() over (order by random()) as rn
         from worker
     )
insert into certificate (certificate_name, issuer, worker_id)
select
    c.name   as certificate_name,
    i.issuer as issuer,
    w.worker_id
from random_workers w
         cross join cert_names c
         cross join issuers i
order by random()
limit 20000;



-- Specialty - import CSV



create or replace procedure insert_worker_specialties(batch_size INT, total INT)
    language plpgsql
as
$$
declare
    i INT := 0;
begin
    while i < total loop
        insert into worker_specialty (worker_id, specialty_id)
        select
            w.worker_id,
            s.specialty_id
        from (
            select worker_id
            from Worker
            order by worker_id
            limit batch_size
            offset i
        ) w
        join lateral (
            select specialty_id
            from Specialty
            order by random()
            limit (1 + floor(random() * 3))::int
        ) s on true;

        i := i + batch_size;
        COMMIT;
        raise notice 'Inserted % / % posts', i, total;

    end loop;
end;
$$;

call insert_worker_specialties(50, 100000);



-- post ------------------------------------------------------------------------------------------------------------------------
insert into Post_Status (status_name) values
    ('Active'),
    ('Deleted'),
    ('Completed');



create table if not exists temp_posts (title text, description text);   -- import CSV

create or replace procedure insert_posts(batch_size int, total int)
    language plpgsql
as $$
declare
    i int := 0;
begin
    while i < total loop

        insert into post (title, description, user_id, location_id, status_id, created_at)
        select
            p.title,
            p.description,
            u.user_id,
            l.location_id,
            (1 + floor(random() * 3))::int,
            (now() - interval '3 years' * random())::timestamp
        from (
            select title, description, row_number() over (order by random()) as rn
            from temp_posts
            order by random()
            limit batch_size
        ) p
        join (
            select user_id, row_number() over (order by random()) as rn
            from "User"
            order by random()
            limit batch_size
        ) u on u.rn = p.rn
        join (
            select location_id, row_number() over (order by random()) as rn
            from location
            order by random()
            limit batch_size
        ) l on l.rn = p.rn;

        i := i + batch_size;
        COMMIT;
        raise notice 'Inserted % / %', i, total;
    end loop;
end;
$$;

call insert_posts(1000, 5000000);





create table temp_post_images(image_url text);   -- import CSV

create or replace procedure public.insert_post_image(batch_size integer, total integer)
    language plpgsql
as $$
declare
    i INT := 0;
begin
    while i < total loop
        insert into Post_Image (image_file, post_id)
        select img.image_url::bytea, p.post_id
        from (
            select post_id
            from Post
            order by post_id
            limit batch_size
            offset i
        ) p
        cross join lateral (
            select image_url
            from temp_post_images
            where random() + p.post_id * 0 is not null
            order by random()
            limit floor(random() * 4)::INT
        ) img;

        i := i + batch_size;

        COMMIT;
        raise notice 'Inserted % / %', i, total;
    end loop;
end;
$$;

call insert_post_image(10000,5000000);







create or replace procedure insert_post_specialties(batch_size int, total int)
    language plpgsql
as
$$
declare
    i int := 0;
begin
    while i < total loop
        insert into Post_Specialty (post_id, specialty_id)
        select p.post_id, s.specialty_id
        from (
            select post_id
            from Post
            order by post_id
            limit batch_size
            offset i
        ) p
        join lateral (
            select specialty_id
            from Specialty
            order by random()
            limit (1 + floor(random() * 3))::int
        ) s on true;

        i := i + batch_size;

        COMMIT;
        raise notice 'Inserted % / % posts', i, total;
    end loop;
end;
$$;

call insert_post_specialties(1000, 5000000);



-- application ------------------------------------------------------------------------------------------------------------------------
insert into Application_Status (status_name) values
    ('Pending'),
    ('Accepted'),
    ('Deleted'),
    ('Rejected');



create table if not exists temp_application_messages(message text);   -- import CSV

create or replace procedure insert_apps(batch_size INT, total INT)
language plpgsql
as
$$
declare
    i INT := 0;
begin
    while i < total loop
        insert into Application (message, needed_time, expected_price, created_at, worker_id, post_id, status_id)
        select
            m.message,
            (interval '1 hour' * (1 + floor(random() * 23)))::time as needed_time,
            (10 + floor(random() * 990))::float                    as expected_price,
            (p.created_at + interval '3 days' + interval '1 day' * floor(random() * 30)) as created_at,
            w.worker_id,
            p.post_id,
            s.status_id
        from (
            select post_id, created_at
            from Post
            order by random()
            limit batch_size
        ) p
        join lateral  (
            select worker_id
            from Worker
            order by random() + p.post_id * 0.0001
            limit 1
        ) w on true
         join lateral (
            select status_id
            from Application_Status
           order by random() + p.post_id * 0.0001
            limit 1
        ) s on true
         join lateral (
            select message
            from temp_application_messages
            order by random() + p.post_id * 0.0001
            limit 1
        ) m on true;

        i := i + batch_size;
        commit;
        raise notice 'Inserted % / %', i, total;
  end loop;
end;
$$;

call insert_apps(10000, 5000000);




-- reviews and ratings ------------------------------------------------------------------------------------------------------------------------

create or replace procedure insert_reviews(batch_size int, total int)
    language plpgsql
as
$$
declare
    i int := 0;
begin
    while i < total loop
        insert into Review (grade, created_at, reviewer_id, worker_specialty_id)
        select
            floor(random() * 6)::int as grade,
            (now() - interval '3 year' * random()) as created_at,
            u.user_id,
            ws.worker_specialty_id
        from (
            select worker_specialty_id, random() as r
            from Worker_Specialty
            order by random()
            limit 1
        ) ws
        join lateral (
            select user_id
            from "User"
            order by random()
            limit floor(3 + random() * 5)::int
        ) u on true;

        i := i + batch_size;
        COMMIT;
        raise notice 'Inserted % / % ', i, total;
    end loop;
end;
$$;

call insert_reviews(1, 1000000);





create table temp_positive_comments( comment text );          -- import CSV
create table temp_negative_comments( comment text);           -- import CSV

insert into Review_Comment (review_id, reply)
select r.review_id,
    case
        when r.grade in (4, 5) then (
            select comment
            from temp_positive_comments
            order by random() * r.review_id
            limit 1
        )
        else (
            select comment
            from temp_negative_comments
            order by random() * r.review_id
            limit 1
        )
    end as reply
from review r
where random() > 0.4;






insert into Payment_Method (method_name, max_amount, tax) values
    ('Credit Card', 50000, 0.02),
    ('Debit Card', 20000, 0.015),
    ('PayPal', 30000, 0.03),
    ('Apple Pay', 25000, 0.02),
    ('Google Pay', 25000, 0.02);




insert into Worker_Method (worker_id, method_id)
select w.worker_id, pm.method_id
from (
    select worker_id
    from worker
    order by random()
    limit 50000
) w
join lateral (
    select method_id
    from payment_method
    order by random() + w.worker_id
    limit 1 + floor(random() * 3)
) pm on true;




insert into Payment_Status (status_name) values
    ('Pending'),
    ('Completed'),
    ('Failed'),
    ('Cancelled'),
    ('Processing');



insert into Payment (amount, payment_date, worker_method_id, payment_status, plan_id)
select
    (pp.price * (1 + wm.tax))::numeric(10,2) as amount,
    now() - interval '2 years' * random() as payment_date,
    wm.worker_method_id,
    rs.status_id,
    pp.plan_id
from generate_series(1, 500000) gs
cross join lateral (
    select plan_id, price
    from Premium_Plan
    order by random() * gs
    limit 1
) pp
cross join lateral (
    select wm.worker_method_id, pm.tax
    from Worker_Method wm
    join Payment_Method pm on wm.method_id = pm.method_id
    order by random() * gs
    limit 1
) wm
cross join lateral (
    select status_id
    from Payment_Status
    order by  random() * gs
    limit 1
) rs
order by payment_date;



insert into Premium_Plan (name, price, duration_days, description) values
    ('Recommended Boost', 49.99, 7, 'Appear in the recommended workers list for 1 week'),
    ('Top Listing', 99.99, 14, 'Feature your profile at the top of search results for 2 weeks'),
    ('Premium Highlight', 149.99, 30, 'Highlight your profile for a month with special badge'),
    ('Monthly Spotlight', 199.99, 30, 'Your profile will appear in monthly spotlight section');




insert into Premium_Worker (start_date, end_date, plan_id, worker_id, payment_id)
select
    p.payment_date as start_date,
    p.payment_date + interval '1 day' * pp.duration_days as end_date,
    pp.plan_id,
    wm.worker_id,
    p.payment_id
from Payment p join Worker_Method wm on wm.worker_method_id = p.worker_method_id
    join Premium_Plan pp on pp.plan_id = p.plan_id
    join Payment_Status ps on p.payment_status = ps.status_id
where ps.status_name = 'Completed';