DatabaseCreation: DDL.sql

INSERT INTO COUNTRY (name, currency)
VALUES
('Germany', 'EUR'),
('France', 'EUR'),
('Italy', 'EUR'),
('Spain', 'EUR'),
('Netherlands', 'EUR'),
('Portugal', 'EUR'),
('Greece', 'EUR'),
('Austria', 'EUR'),
('Belgium', 'EUR'),
('United Kingdom', 'GBP'),
('Switzerland', 'CHF'),
('Sweden', 'SEK'),
('Norway', 'NOK'),
('Denmark', 'DKK'),
('Poland', 'PLN'),
('Czech Republic', 'CZK'),
('Hungary', 'HUF');



INSERT INTO CITY (country_id, name)
SELECT
    (RANDOM()*4 + 1)::INT,
    'City_' || g
FROM generate_series(1,500) g;



INSERT INTO CATEGORY (parent_id, name)
SELECT
    CASE WHEN g % 5 = 0 THEN NULL ELSE (RANDOM()*99 + 1)::INT END,
    'Category_' || g
FROM generate_series(1,100) g;



INSERT INTO APP_USER (email, username, first_name, last_name, num_trip_members)
SELECT
    'user' || g || '@mail.com',
    'user_' || g,
    'Name_' || g,
    'Surname_' || g,
    (RANDOM()*5)::INT
FROM generate_series(1, 500000) g;



INSERT INTO PLACE (city_id, category_id, name, description, address, latitude, longitude, avg_price, rating)
SELECT
    (RANDOM()*499 + 1)::INT,
    (RANDOM()*99 + 1)::INT,
    'Place_' || g,
    'Nice place ' || g,
    'Street ' || g,
    (RANDOM()*180 - 90),
    (RANDOM()*360 - 180),
    (RANDOM()*100)::DECIMAL(10,2),
    (RANDOM()*5)::DECIMAL(3,2)
FROM generate_series(1,1000000) g;




INSERT INTO OPENING_HOURS (place_id, day_of_week, open_time, close_time, is_closed)
SELECT
    p.place_id,
    CASE (RANDOM()*6)::INT
        WHEN 0 THEN 'Monday'
        WHEN 1 THEN 'Tuesday'
        WHEN 2 THEN 'Wednesday'
        WHEN 3 THEN 'Thursday'
        WHEN 4 THEN 'Friday'
        WHEN 5 THEN 'Saturday'
        ELSE 'Sunday'
    END,
    (TIME '06:00:00' + (RANDOM()*6)::INT * INTERVAL '1 hour'),
    (TIME '14:00:00' + (RANDOM()*8)::INT * INTERVAL '1 hour'),
    (RANDOM() < 0.05)
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT place_id
    FROM PLACE
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) p;




INSERT INTO TRANSPORT_MODE (name, estimated_cost, avg_speed_kmh)
VALUES
('Bus', 1.5, 45),
('Train', 2.15, 50),
('Bicycle', 10.00, 20),
('Walking', 0.00, 5),
('Taxi', 20.0, 50),
('Metro', 2.5, 40);



INSERT INTO TRIP_STATUS (user_id, status_name, status_comment, updated_at)
SELECT
    u.user_id,
    CASE
        WHEN RANDOM() < 0.6 THEN 'completed'::trip_status_name
        WHEN RANDOM() < 0.3 THEN 'in_progress'::trip_status_name
        ELSE 'cancelled'::trip_status_name
    END,
    CASE
        WHEN RANDOM() < 0.5 THEN 'Trip updated successfully'
    END,
    NOW() - (RANDOM()*365 || ' days')::INTERVAL
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u;




INSERT INTO TRIP_HISTORY (user_id, total_spent, notes, completed_at)
SELECT
    u.user_id,
    (RANDOM()*10000)::DECIMAL(10,2),
    'History_' || g,
    NOW() - (RANDOM()*1000 || ' days')::INTERVAL
FROM generate_series(1,10000000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u;




INSERT INTO TRIP (user_id, city_id, status_id, history_id, title, start_date, end_date, total_budget, estimated_cost)
SELECT
    u.user_id,
    c.city_id,
    (
        SELECT status_id
        FROM TRIP_STATUS
        ORDER BY RANDOM() + (g * 0)
        LIMIT 1
    ),
    (
        SELECT history_id
        FROM TRIP_HISTORY
        ORDER BY RANDOM() + (g * 0)
        LIMIT 1
    ),
    'Trip_' || g,
    CURRENT_DATE - (RANDOM()*365)::INT,
    CURRENT_DATE + (RANDOM()*365)::INT,
    b.total_budget,
    LEAST(
        b.total_budget - 0.01,
        ROUND((b.total_budget * (0.5 + RANDOM() * 0.45))::NUMERIC, 2)
    )
--ova pod treba
FROM generate_series(1,2500000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u
CROSS JOIN LATERAL (
    SELECT city_id
    FROM CITY
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) c
CROSS JOIN LATERAL (
    SELECT ROUND((RANDOM()*5000 + 500)::NUMERIC, 2) AS total_budget
) b;




INSERT INTO TRIP_MEMBER (user_id, trip_id, first_name, last_name, username, role)
SELECT
    u.user_id,
    t.trip_id,
    'Name_' || g,
    'Surname_' || g,
    'member_' || g,
    CASE WHEN RANDOM() > 0.7 THEN 'owner'::user_role ELSE 'member'::user_role END
FROM generate_series(1,10000000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u
CROSS JOIN LATERAL (
    SELECT trip_id
    FROM TRIP
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) t;




INSERT INTO TRIP_DAY (trip_id, day_number, actual_date)
SELECT
    t.trip_id,
    (RANDOM()*14 + 1)::INT,
    CURRENT_DATE + (RANDOM()*365)::INT
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT trip_id
    FROM TRIP
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) t;




INSERT INTO SCHEDULE_TIME (scheduled_start_time, scheduled_end_time)
SELECT
    (TIME '06:00:00' + (RANDOM()*12)::INT * INTERVAL '1 hour'),
    (TIME '12:00:00' + (RANDOM()*10)::INT * INTERVAL '1 hour')
FROM generate_series(1,500) g;


INSERT INTO PLAN_ITEM (trip_day_id, place_id, schedule_time_id, estimated_cost, notes)
SELECT
    td.trip_day_id,
    p.place_id,
    s.schedule_time_id,
    (RANDOM()*200)::DECIMAL(10,2),
    'Plan note ' || g
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT trip_day_id
    FROM TRIP_DAY
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) td
CROSS JOIN LATERAL (
    SELECT place_id
    FROM PLACE
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) p
CROSS JOIN LATERAL (
    SELECT schedule_time_id
    FROM SCHEDULE_TIME
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) s;




INSERT INTO DISTANCE_SEGMENT (trip_id, predecessor_item_id, successor_item_id, transport_mode_id, distance_km, duration_minutes)
SELECT
    t.trip_id,
    pi1.plan_item_id,
    pi2.plan_item_id,
    tm.transport_mode_id,
    ROUND((RANDOM()*200 + 1)::NUMERIC, 3),
    (RANDOM()*240 + 10)::INT
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT trip_id
    FROM TRIP
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) t
CROSS JOIN LATERAL (
    SELECT plan_item_id
    FROM PLAN_ITEM
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) pi1
CROSS JOIN LATERAL (
    SELECT plan_item_id
    FROM PLAN_ITEM
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) pi2
CROSS JOIN LATERAL (
    SELECT transport_mode_id
    FROM TRANSPORT_MODE
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) tm
WHERE RANDOM() > 0.1;




INSERT INTO ACCOMODATION (trip_id, place_id)
SELECT
    t.trip_id,
    p.place_id
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT trip_id
    FROM TRIP
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) t
CROSS JOIN LATERAL (
    SELECT place_id
    FROM PLACE
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) p;




INSERT INTO REVIEW (user_id, plan_item_id, rating, comment, visited_at, created_at)
SELECT
    u.user_id,
    pi.plan_item_id,
    (RANDOM()*5)::DECIMAL(3,2),
    'Review ' || g,
    NOW(),
    NOW()
FROM generate_series(1,500000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u
CROSS JOIN LATERAL (
    SELECT plan_item_id
    FROM PLAN_ITEM
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) pi;




INSERT INTO FAVOURITES (user_id, place_id)
SELECT
    u.user_id,
    p.place_id
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u
CROSS JOIN LATERAL (
    SELECT place_id
    FROM PLACE
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) p
ON CONFLICT DO NOTHING;




INSERT INTO CHECKLIST (user_id, trip_id, title)
SELECT
    u.user_id,
    t.trip_id,
    'Checklist_' || g
FROM generate_series(1,500000) g
CROSS JOIN LATERAL (
    SELECT user_id
    FROM APP_USER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) u
CROSS JOIN LATERAL (
    SELECT trip_id
    FROM TRIP
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) t;




INSERT INTO CHECKLIST_ITEM (checklist_id, item_id, description, is_checked)
SELECT
    c.item_id,
    g,
    CASE (RANDOM()*5)::INT
        WHEN 0 THEN 'Passport'
        WHEN 1 THEN 'Tickets'
        WHEN 2 THEN 'Hotel reservation'
        WHEN 3 THEN 'Insurance'
        WHEN 4 THEN 'Clothes'
        ELSE 'Other item'
    END,
    (RANDOM() < 0.3)
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT item_id
    FROM CHECKLIST
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) c;




INSERT INTO GROUP_CHAT (message_text, sent_at)
SELECT
    'Message ' || g,
    NOW() - (RANDOM()*10000 || ' seconds')::INTERVAL
FROM generate_series(1,10000000) g;




INSERT INTO GROUP_CHAT_TRIP_MEMBER (group_id, trip_member_id)
SELECT
    gc.group_id,
    tm.trip_member_id
FROM generate_series(1,1000000) g
CROSS JOIN LATERAL (
    SELECT group_id
    FROM GROUP_CHAT
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) gc
CROSS JOIN LATERAL (
    SELECT trip_member_id
    FROM TRIP_MEMBER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) tm
ON CONFLICT DO NOTHING;




INSERT INTO NOTIFICATION (group_id, trip_member_id, is_read)
SELECT
    gc.group_id,
    tm.trip_member_id,
    (RANDOM() > 0.5)
FROM generate_series(1,10000000) g
CROSS JOIN LATERAL (
    SELECT group_id
    FROM GROUP_CHAT
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) gc
CROSS JOIN LATERAL (
    SELECT trip_member_id
    FROM TRIP_MEMBER
    ORDER BY RANDOM() + (g * 0)
    LIMIT 1
) tm;




UPDATE TRIP t
SET
    estimated_cost = calc.new_estimated_cost,
    total_budget   = calc.new_estimated_cost + ROUND((RANDOM() * 500 + 100)::NUMERIC, 2)
FROM (
    SELECT
        t2.trip_id,
        COALESCE(
            (SELECT SUM(pi.estimated_cost)
             FROM PLAN_ITEM pi
             JOIN TRIP_DAY td ON pi.trip_day_id = td.trip_day_id
             WHERE td.trip_id = t2.trip_id), 0)
        + COALESCE(
            (SELECT SUM(ds.distance_km * tm.estimated_cost)
             FROM DISTANCE_SEGMENT ds
             JOIN TRANSPORT_MODE tm ON ds.transport_mode_id = tm.transport_mode_id
             WHERE ds.trip_id = t2.trip_id), 0)
        AS new_estimated_cost
    FROM TRIP t2
) calc
WHERE t.trip_id = calc.trip_id;