DatabaseCreation: seeder.sql

INSERT INTO public.event_status (status_name)
VALUES
    ('Draft'),
    ('Scheduled'),
    ('On Sale'),
    ('Sold Out'),
    ('Completed'),
    ('Cancelled');

INSERT INTO public.order_status (status_name)
VALUES
    ('Open'),
    ('Pending Payment'),
    ('Paid'),
    ('Partially Refunded'),
    ('Refunded'),
    ('Expired'),
    ('Cancelled');

INSERT INTO public.payment_method (method_name)
VALUES
    ('Card'),
    ('Bank Transfer'),
    ('Cash'),
    ('Digital Wallet');

INSERT INTO public.location_type (type_name)
VALUES
    ('Arena'),
    ('Stadium'),
    ('Theater'),
    ('Concert Hall'),
    ('Conference Center'),
    ('Exhibition Hall'),
    ('Club'),
    ('Outdoor Venue');

INSERT INTO public.category (name)
VALUES
    ('Concert'),
    ('Festival'),
    ('Conference'),
    ('Workshop'),
    ('Seminar'),
    ('Sports'),
    ('Theater'),
    ('Exhibition'),
    ('Networking'),
    ('Comedy');

INSERT INTO public.field (field_name)
VALUES
    ('Technology'),
    ('Education'),
    ('Healthcare'),
    ('Finance'),
    ('Art'),
    ('Gaming'),
    ('Science'),
    ('Business'),
    ('Sports'),
    ('Media');

INSERT INTO public.staff_role (description)
VALUES
    ('Security'),
    ('Usher'),
    ('Ticketing'),
    ('Technical Support'),
    ('Stage Crew'),
    ('Guest Relations'),
    ('Operations'),
    ('Cleaning');

INSERT INTO public.staff_team (team_name)
VALUES
    ('Front Gate Team'),
    ('Hall Operations Team'),
    ('Backstage Team'),
    ('Technical Team'),
    ('Guest Support Team'),
    ('Security Team');

INSERT INTO public.attribute (name, data_type, description, is_required)
VALUES
    ('Age Restriction', 'integer', 'Minimum attendee age', false),
    ('Dress Code', 'string', 'Required dress code for the event', false),
    ('Has Parking', 'boolean', 'Whether parking is available', false),
    ('Language', 'string', 'Primary event language', false),
    ('Doors Open', 'datetime', 'Venue doors opening time', true),
    ('Requires ID', 'boolean', 'Whether ID is required at entry', false);

INSERT INTO public.sponsor_type (sponsor_amount_paid, type)
VALUES
    (100.00, 'Bronze'),
    (250.00, 'Silver'),
    (500.00, 'Gold'),
    (999.99, 'Platinum');

-- 1M users
INSERT INTO public.user_app (username, email, contact_phone)
SELECT
    lower(
            substr(fn.first_name, 1, 1) ||
            substr(ln.last_name, 1, 8) ||
            gs::text
    ) AS username,
    lower(
            fn.first_name || '.' || ln.last_name || '.' || gs::text || '@' || dm.domain
    ) AS email,
    CASE
        WHEN gs % 11 = 0 THEN NULL
        ELSE '+389' || ph.prefix || lpad((gs % 1000000)::text, 6, '0')
        END AS contact_phone
FROM generate_series(1, 1000000) AS gs
         CROSS JOIN LATERAL (
    SELECT CASE (gs % 20)
               WHEN 0 THEN 'Andrej'
               WHEN 1 THEN 'Stefan'
               WHEN 2 THEN 'Martin'
               WHEN 3 THEN 'Filip'
               WHEN 4 THEN 'Nikola'
               WHEN 5 THEN 'Aleksandar'
               WHEN 6 THEN 'David'
               WHEN 7 THEN 'Matej'
               WHEN 8 THEN 'Petar'
               WHEN 9 THEN 'Bojan'
               WHEN 10 THEN 'Marija'
               WHEN 11 THEN 'Ana'
               WHEN 12 THEN 'Elena'
               WHEN 13 THEN 'Ivana'
               WHEN 14 THEN 'Sara'
               WHEN 15 THEN 'Teodora'
               WHEN 16 THEN 'Mila'
               WHEN 17 THEN 'Jovana'
               WHEN 18 THEN 'Tamara'
               ELSE 'Kristina'
               END AS first_name
    ) fn
         CROSS JOIN LATERAL (
    SELECT CASE ((gs / 20) % 20)
               WHEN 0 THEN 'Stojanov'
               WHEN 1 THEN 'Nikolov'
               WHEN 2 THEN 'Petrovski'
               WHEN 3 THEN 'Jovanov'
               WHEN 4 THEN 'Trajkovski'
               WHEN 5 THEN 'Mitrevski'
               WHEN 6 THEN 'Ristov'
               WHEN 7 THEN 'Iliev'
               WHEN 8 THEN 'Kostov'
               WHEN 9 THEN 'Georgiev'
               WHEN 10 THEN 'Ivanova'
               WHEN 11 THEN 'Stefanova'
               WHEN 12 THEN 'Petrova'
               WHEN 13 THEN 'Jovanova'
               WHEN 14 THEN 'Trajkovska'
               WHEN 15 THEN 'Mitrevska'
               WHEN 16 THEN 'Ristova'
               WHEN 17 THEN 'Ilieva'
               WHEN 18 THEN 'Kostova'
               ELSE 'Georgieva'
               END AS last_name
    ) ln
         CROSS JOIN LATERAL (
    SELECT CASE (gs % 6)
               WHEN 0 THEN 'gmail.com'
               WHEN 1 THEN 'yahoo.com'
               WHEN 2 THEN 'outlook.com'
               WHEN 3 THEN 'hotmail.com'
               WHEN 4 THEN 'live.com'
               ELSE 'icloud.com'
               END AS domain
    ) dm
         CROSS JOIN LATERAL (
    SELECT CASE (gs % 10)
               WHEN 0 THEN '70'
               WHEN 1 THEN '71'
               WHEN 2 THEN '72'
               WHEN 3 THEN '73'
               WHEN 4 THEN '74'
               WHEN 5 THEN '75'
               WHEN 6 THEN '76'
               WHEN 7 THEN '77'
               WHEN 8 THEN '78'
               ELSE '79'
               END AS prefix
    ) ph;

-- 10000 organisers
INSERT INTO public.organiser (company_name, contact_phone, website_url)
SELECT
    org.company_name,
    '+389' || ph.prefix || lpad(gs::text, 6, '0') AS contact_phone,
    'https://www.' ||
    lower(regexp_replace(org.company_name, '[^a-zA-Z0-9]', '', 'g')) ||
    '.mk' AS website_url
FROM generate_series(1, 10000) AS gs
         CROSS JOIN LATERAL (
    SELECT CASE (gs % 12)
               WHEN 0 THEN 'EventLab ' || gs
               WHEN 1 THEN 'Pulse Events ' || gs
               WHEN 2 THEN 'SummitWorks ' || gs
               WHEN 3 THEN 'LiveNation Hub ' || gs
               WHEN 4 THEN 'ArenaFlow ' || gs
               WHEN 5 THEN 'UrbanStage ' || gs
               WHEN 6 THEN 'BluePeak Events ' || gs
               WHEN 7 THEN 'NextWave Productions ' || gs
               WHEN 8 THEN 'Vision Expo Group ' || gs
               WHEN 9 THEN 'Prime Ticketing ' || gs
               WHEN 10 THEN 'Cultural Connect ' || gs
               ELSE 'Mosaic Events ' || gs
               END AS company_name
    ) org
         CROSS JOIN LATERAL (
    SELECT CASE (gs % 10)
               WHEN 0 THEN '70'
               WHEN 1 THEN '71'
               WHEN 2 THEN '72'
               WHEN 3 THEN '73'
               WHEN 4 THEN '74'
               WHEN 5 THEN '75'
               WHEN 6 THEN '76'
               WHEN 7 THEN '77'
               WHEN 8 THEN '78'
               ELSE '79'
               END AS prefix
    ) ph;

-- 5000 locations
INSERT INTO public.location (name, type_id, address, city, capacity)
SELECT
    CASE lt.type_id
        WHEN 1 THEN 'Arena ' || gs
        WHEN 2 THEN 'Stadium ' || gs
        WHEN 3 THEN 'Theater ' || gs
        WHEN 4 THEN 'Concert Hall ' || gs
        WHEN 5 THEN 'Conference Center ' || gs
        WHEN 6 THEN 'Exhibition Hall ' || gs
        WHEN 7 THEN 'Club ' || gs
        ELSE 'Outdoor Venue ' || gs
        END AS name,
    lt.type_id,
    (100 + (gs % 900))::text || ' ' ||
    CASE (gs % 14)
        WHEN 0 THEN 'Ilindenska'
        WHEN 1 THEN 'Partizanska'
        WHEN 2 THEN 'Makedonija'
        WHEN 3 THEN 'Goce Delcev'
        WHEN 4 THEN 'Jane Sandanski'
        WHEN 5 THEN 'Boris Trajkovski'
        WHEN 6 THEN 'Industriska'
        WHEN 7 THEN 'Marshal Tito'
        WHEN 8 THEN 'Dimitar Vlahov'
        WHEN 9 THEN '11 Oktomvri'
        WHEN 10 THEN 'Kej 13 Noemvri'
        WHEN 11 THEN 'Nikola Vapcarov'
        WHEN 12 THEN 'Koco Racin'
        ELSE 'Vodno'
        END || ' Ul.' AS address,
    CASE (gs % 12)
        WHEN 0 THEN 'Skopje'
        WHEN 1 THEN 'Bitola'
        WHEN 2 THEN 'Ohrid'
        WHEN 3 THEN 'Tetovo'
        WHEN 4 THEN 'Kumanovo'
        WHEN 5 THEN 'Prilep'
        WHEN 6 THEN 'Stip'
        WHEN 7 THEN 'Strumica'
        WHEN 8 THEN 'Veles'
        WHEN 9 THEN 'Gostivar'
        WHEN 10 THEN 'Belgrad'
        ELSE 'Struga'
        END AS city,
    CASE lt.type_id
        WHEN 1 THEN 5000 + (gs % 15001)   -- arena
        WHEN 2 THEN 8000 + (gs % 32001)   -- stadium
        WHEN 3 THEN 150 + (gs % 1851)     -- theater
        WHEN 4 THEN 300 + (gs % 3701)     -- concert hall
        WHEN 5 THEN 200 + (gs % 4801)     -- conference center
        WHEN 6 THEN 500 + (gs % 9501)     -- exhibition hall
        WHEN 7 THEN 80 + (gs % 1121)      -- club
        ELSE 1000 + (gs % 19001)          -- outdoor venue
        END AS capacity
FROM generate_series(1, 5000) AS gs
         JOIN LATERAL (
    SELECT ((gs - 1) % 8) + 1 AS type_id
    ) AS lt ON true;

-- 3000 sponsors
INSERT INTO public.sponsor (name)
SELECT
    CASE (gs % 15)
        WHEN 0 THEN 'Coca-Cola Partner ' || gs
        WHEN 1 THEN 'Telekom Sponsor ' || gs
        WHEN 2 THEN 'Visa Event Sponsor ' || gs
        WHEN 3 THEN 'Sparkasse Partner ' || gs
        WHEN 4 THEN 'Nike Activation ' || gs
        WHEN 5 THEN 'Red Bull Stage Sponsor ' || gs
        WHEN 6 THEN 'Samsung Experience ' || gs
        WHEN 7 THEN 'Alkaloid Support ' || gs
        WHEN 8 THEN 'Tikves Partner ' || gs
        WHEN 9 THEN 'NLB Sponsor ' || gs
        WHEN 10 THEN 'Setec Tech Sponsor ' || gs
        WHEN 11 THEN 'Neptun Partner ' || gs
        WHEN 12 THEN 'Makedonski Telekom Business ' || gs
        WHEN 13 THEN 'Sava Insurance Sponsor ' || gs
        ELSE 'General Sponsor ' || gs
        END
FROM generate_series(1, 3000) AS gs;

-- 100000 sections
INSERT INTO public.section (section_name, capacity, location_id)
SELECT
    CASE (gs % 12)
        WHEN 0 THEN 'Main Floor'
        WHEN 1 THEN 'Balcony'
        WHEN 2 THEN 'VIP Zone'
        WHEN 3 THEN 'Standing Area'
        WHEN 4 THEN 'Parter'
        WHEN 5 THEN 'Tribina A'
        WHEN 6 THEN 'Tribina B'
        WHEN 7 THEN 'East Wing'
        WHEN 8 THEN 'West Wing'
        WHEN 9 THEN 'Gallery'
        WHEN 10 THEN 'Expo Hall'
        ELSE 'General Admission'
        END || ' ' || ((gs - 1) % 20 + 1) AS section_name,

    GREATEST(
            30,
            LEAST(
                    2500,
                    (l.capacity / (8 + (gs % 18)))::integer
            )
    ) AS capacity,

    l.location_id
FROM generate_series(1, 100000) AS gs
         JOIN LATERAL (
    SELECT location_id, capacity
    FROM public.location
    ORDER BY location_id
    OFFSET ((gs - 1) % (SELECT count(*) FROM public.location))
        LIMIT 1
    ) l ON true;

-- 500000 events
INSERT INTO public.event (
    organiser_id,
    event_status_id,
    title,
    start_datetime,
    end_datetime
)
SELECT
    o.organiser_id,

    CASE
        WHEN gs % 100 < 4 THEN (SELECT event_status_id FROM public.event_status WHERE status_name = 'Cancelled')
        WHEN gs % 100 < 12 THEN (SELECT event_status_id FROM public.event_status WHERE status_name = 'Completed')
        WHEN gs % 100 < 20 THEN (SELECT event_status_id FROM public.event_status WHERE status_name = 'Draft')
        WHEN gs % 100 < 32 THEN (SELECT event_status_id FROM public.event_status WHERE status_name = 'Scheduled')
        WHEN gs % 100 < 92 THEN (SELECT event_status_id FROM public.event_status WHERE status_name = 'On Sale')
        ELSE (SELECT event_status_id FROM public.event_status WHERE status_name = 'Sold Out')
        END AS event_status_id,

    left(
            CASE (gs % 30)
                WHEN 0 THEN 'Skopje Summer Festival'
                WHEN 1 THEN 'Ohrid Cultural Night'
                WHEN 2 THEN 'Balkan Tech Summit'
                WHEN 3 THEN 'Bitola Film Evening'
                WHEN 4 THEN 'Urban Music Fest'
                WHEN 5 THEN 'Stand Up Balkan'
                WHEN 6 THEN 'Wine and Jazz Night'
                WHEN 7 THEN 'Startup Networking Forum'
                WHEN 8 THEN 'Electronic Beats Skopje'
                WHEN 9 THEN 'Traditional Food Festival'
                WHEN 10 THEN 'Gaming Weekend'
                WHEN 11 THEN 'Design and Figma Workshop'
                WHEN 12 THEN 'AI and Data Conference'
                WHEN 13 THEN 'Football Fan Zone'
                WHEN 14 THEN 'Theater Premiere Night'
                WHEN 15 THEN 'Student Career Fair'
                WHEN 16 THEN 'Hip Hop Live Session'
                WHEN 17 THEN 'Business Leadership Forum'
                WHEN 18 THEN 'Photography Exhibition'
                WHEN 19 THEN 'Macedonian Rock Night'
                WHEN 20 THEN 'Regional Book Fair'
                WHEN 21 THEN 'Coding Bootcamp Day'
                WHEN 22 THEN 'Coffee and Tech Talks'
                WHEN 23 THEN 'Ohrid Open Air Concert'
                WHEN 24 THEN 'Skopje Fashion Weekend'
                WHEN 25 THEN 'Jazz Under the Stars'
                WHEN 26 THEN 'Balkan Startup Pitch'
                WHEN 27 THEN 'Museum Night'
                WHEN 28 THEN 'Local Craft Expo'
                ELSE 'International Culture Forum'
                END || ' ' || gs,
            64
    ) AS title,

    timestamp '2023-01-01 18:00:00'
        + ((gs % 1460) * interval '1 day')
        + ((gs % 6) * interval '1 hour') AS start_datetime,

    timestamp '2023-01-01 18:00:00'
        + ((gs % 1460) * interval '1 day')
        + ((gs % 6) * interval '1 hour')
        + (
        CASE
            WHEN gs % 10 IN (0, 1) THEN interval '2 hours'
            WHEN gs % 10 IN (2, 3, 4) THEN interval '3 hours'
            WHEN gs % 10 IN (5, 6) THEN interval '4 hours'
            WHEN gs % 10 = 7 THEN interval '1 day'
            ELSE interval '2 days'
            END
        ) AS end_datetime

FROM generate_series(1, 500000) AS gs
         JOIN LATERAL (
    SELECT organiser_id
    FROM public.organiser
    ORDER BY organiser_id
    OFFSET ((gs - 1) % (SELECT count(*) FROM public.organiser))
        LIMIT 1
    ) o ON true;

-- 700,000 rows total
INSERT INTO public.event_category (event_id, category_id)
SELECT
    e.event_id,
    ((e.event_id - 1) % (SELECT count(*) FROM public.category)) + 1 AS category_id
FROM public.event e;

INSERT INTO public.event_category (event_id, category_id)
SELECT
    e.event_id,
    ((e.event_id + 2) % (SELECT count(*) FROM public.category)) + 1 AS category_id
FROM public.event e
WHERE e.event_id % 10 IN (0, 1, 2);

INSERT INTO public.event_category (event_id, category_id)
SELECT
    e.event_id,
    ((e.event_id + 5) % (SELECT count(*) FROM public.category)) + 1 AS category_id
FROM public.event e
WHERE e.event_id % 10 = 0;

-- 1.25M sessions for 500,000 events
WITH numbered_sections AS (
    SELECT
        section_id,
        row_number() OVER (ORDER BY section_id) AS rn
    FROM public.section
),
     section_count AS (
         SELECT count(*) AS cnt
         FROM public.section
     )
INSERT INTO public.event_schedule_session (
    event_id,
    session_title,
    start_time,
    end_time,
    section_id
)
SELECT
    e.event_id,

    CASE gs.session_no
        WHEN 1 THEN 'Opening Session'
        WHEN 2 THEN 'Main Program'
        WHEN 3 THEN 'Evening Program'
        ELSE 'Final Session'
        END,

    e.start_datetime + ((gs.session_no - 1) * interval '2 hours'),

    LEAST(
            e.start_datetime + (gs.session_no * interval '2 hours'),
            e.end_datetime
    ),

    ns.section_id

FROM public.event e

         JOIN LATERAL generate_series(
        1,
        CASE
            WHEN e.event_id % 10 = 0 THEN 4
            WHEN e.event_id % 10 IN (1,2,3) THEN 3
            ELSE 2
            END
                      ) AS gs(session_no) ON true

         CROSS JOIN section_count sc

         JOIN numbered_sections ns
              ON ns.rn = ((e.event_id + gs.session_no - 2) % sc.cnt) + 1

WHERE e.end_datetime >
      e.start_datetime + ((gs.session_no - 1) * interval '2 hours');

-- at least 500000 rows for 3 ticket types.
INSERT INTO public.ticket_type (
    event_id,
    tier_name,
    total_allocated,
    presale_start,
    presale_end
)
SELECT
    e.event_id,

    tt.tier_name,

    CASE tt.tier_name
        WHEN 'General Admission' THEN 200 + (e.event_id % 1800)
        WHEN 'VIP' THEN 20 + (e.event_id % 180)
        WHEN 'Student' THEN 50 + (e.event_id % 450)
        ELSE 100 + (e.event_id % 700)
        END AS total_allocated,

    CASE
        WHEN tt.tier_name = 'General Admission' THEN e.start_datetime - interval '45 days'
        WHEN tt.tier_name = 'VIP' AND e.event_id % 4 <> 0 THEN e.start_datetime - interval '60 days'
        WHEN tt.tier_name = 'Student' AND e.event_id % 3 <> 0 THEN e.start_datetime - interval '30 days'
        ELSE NULL
        END AS presale_start,

    CASE
        WHEN tt.tier_name = 'General Admission' THEN e.start_datetime - interval '7 days'
        WHEN tt.tier_name = 'VIP' AND e.event_id % 4 <> 0 THEN e.start_datetime - interval '10 days'
        WHEN tt.tier_name = 'Student' AND e.event_id % 3 <> 0 THEN e.start_datetime - interval '5 days'
        ELSE NULL
        END AS presale_end

FROM public.event e
         JOIN LATERAL (
    SELECT *
    FROM (
             VALUES
                 (1, 'General Admission'),
                 (2, 'VIP'),
                 (3, 'Student')
         ) AS x(type_no, tier_name)
    WHERE x.type_no <= CASE
                           WHEN e.event_id % 10 IN (0, 1, 2, 3, 4, 5, 6) THEN 3
                           ELSE 2
        END
    ) tt ON true
WHERE e.start_datetime > timestamp '2023-02-20';

-- at least 500000 rows, one row per event for required/event-common attributes, fewer for optional ones.
-- Language: string
INSERT INTO public.value (
    attribute_id,
    value_string,
    value_int,
    value_datetime,
    value_bool,
    event_id
)
SELECT
    a.attribute_id,
    CASE (e.event_id % 7)
        WHEN 0 THEN 'Macedonian'
        WHEN 1 THEN 'English'
        WHEN 2 THEN 'Albanian'
        WHEN 3 THEN 'Serbian'
        WHEN 4 THEN 'Macedonian and English'
        WHEN 5 THEN 'Macedonian and Albanian'
        ELSE 'English and Serbian'
        END AS value_string,
    NULL,
    NULL,
    NULL,
    e.event_id
FROM public.event e
         JOIN public.attribute a ON a.name = 'Language';

-- Doors Open: datetime
INSERT INTO public.value (
    attribute_id,
    value_string,
    value_int,
    value_datetime,
    value_bool,
    event_id
)
SELECT
    a.attribute_id,
    NULL,
    NULL,
    e.start_datetime - interval '1 hour',
    NULL,
    e.event_id
FROM public.event e
         JOIN public.attribute a ON a.name = 'Doors Open';

-- Has Parking: boolean
INSERT INTO public.value (
    attribute_id,
    value_string,
    value_int,
    value_datetime,
    value_bool,
    event_id
)
SELECT
    a.attribute_id,
    NULL,
    NULL,
    NULL,
    CASE
        WHEN e.event_id % 10 IN (0, 1, 2, 3, 4, 5) THEN true
        ELSE false
        END,
    e.event_id
FROM public.event e
         JOIN public.attribute a ON a.name = 'Has Parking'
WHERE e.event_id % 100 < 80;

-- Requires ID: boolean
INSERT INTO public.value (
    attribute_id,
    value_string,
    value_int,
    value_datetime,
    value_bool,
    event_id
)
SELECT
    a.attribute_id,
    NULL,
    NULL,
    NULL,
    CASE
        WHEN e.event_id % 10 IN (0, 1, 2, 3) THEN true
        ELSE false
        END,
    e.event_id
FROM public.event e
         JOIN public.attribute a ON a.name = 'Requires ID'
WHERE e.event_id % 100 < 70;

-- Age Restriction: integer
INSERT INTO public.value (
    attribute_id,
    value_string,
    value_int,
    value_datetime,
    value_bool,
    event_id
)
SELECT
    a.attribute_id,
    NULL,
    CASE
        WHEN e.event_id % 20 IN (0, 1, 2) THEN 18
        WHEN e.event_id % 20 = 3 THEN 21
        ELSE 12
        END,
    NULL,
    NULL,
    e.event_id
FROM public.event e
         JOIN public.attribute a ON a.name = 'Age Restriction'
WHERE e.event_id % 100 < 45;

-- Dress Code: string
INSERT INTO public.value (
    attribute_id,
    value_string,
    value_int,
    value_datetime,
    value_bool,
    event_id
)
SELECT
    a.attribute_id,
    CASE (e.event_id % 6)
        WHEN 0 THEN 'Casual'
        WHEN 1 THEN 'Smart Casual'
        WHEN 2 THEN 'Formal'
        WHEN 3 THEN 'Business Casual'
        WHEN 4 THEN 'Festival Outfit'
        ELSE 'No Dress Code'
        END,
    NULL,
    NULL,
    NULL,
    e.event_id
FROM public.event e
         JOIN public.attribute a ON a.name = 'Dress Code'
WHERE e.event_id % 100 < 35;

-- at least 500000 rows for 3 ticket types.
INSERT INTO public.price_tier (
    ticket_type_id,
    price,
    valid_from,
    valid_to
)
SELECT
    tt.ticket_type_id,
    CASE tt.tier_name
        WHEN 'General Admission' THEN 500 + (tt.event_id % 2500)
        WHEN 'Student' THEN 300 + (tt.event_id % 1200)
        WHEN 'VIP' THEN 1500 + (tt.event_id % 6000)
        ELSE 700 + (tt.event_id % 2000)
        END::numeric(10,2),
    tt.presale_start,
    tt.presale_end
FROM public.ticket_type tt
WHERE tt.presale_start IS NOT NULL
  AND tt.presale_end IS NOT NULL;

-- some are promo
INSERT INTO public.price_tier (
    ticket_type_id,
    price,
    valid_from,
    valid_to
)
SELECT
    tt.ticket_type_id,
    CASE tt.tier_name
        WHEN 'General Admission' THEN 800 + (tt.event_id % 3000)
        WHEN 'Student' THEN 500 + (tt.event_id % 1500)
        WHEN 'VIP' THEN 2500 + (tt.event_id % 9000)
        ELSE 1000 + (tt.event_id % 2500)
        END::numeric(10,2),
    tt.presale_end,
    e.start_datetime
FROM public.ticket_type tt
         JOIN public.event e ON e.event_id = tt.event_id
WHERE tt.presale_end IS NOT NULL
  AND e.start_datetime > tt.presale_end;

-- 100000 promo codes
INSERT INTO public.promo_code (code)
SELECT
    upper(
            CASE (gs % 12)
                WHEN 0 THEN 'SKOPJE'
                WHEN 1 THEN 'OHRID'
                WHEN 2 THEN 'BALKAN'
                WHEN 3 THEN 'STUDENT'
                WHEN 4 THEN 'EARLY'
                WHEN 5 THEN 'SUMMER'
                WHEN 6 THEN 'VIP'
                WHEN 7 THEN 'FEST'
                WHEN 8 THEN 'TECH'
                WHEN 9 THEN 'MUSIC'
                WHEN 10 THEN 'CULTURE'
                ELSE 'EVENT'
                END || gs
    )
FROM generate_series(1, 100000) AS gs;

-- some events have discounts
INSERT INTO public.discount (
    event_id,
    discount_percent,
    promo_code_id
)
SELECT
    e.event_id,
    CASE (e.event_id % 6)
        WHEN 0 THEN 5.00
        WHEN 1 THEN 10.00
        WHEN 2 THEN 15.00
        WHEN 3 THEN 20.00
        WHEN 4 THEN 25.00
        ELSE 30.00
        END AS discount_percent,
    CASE
        WHEN e.event_id % 3 = 0 THEN pc.promo_code_id
        ELSE NULL
        END AS promo_code_id
FROM public.event e
         LEFT JOIN public.promo_code pc
                   ON pc.promo_code_id = ((e.event_id - 1) % 100000) + 1
WHERE e.event_id % 100 < 35;

-- 10M rows
INSERT INTO public.seat (
    row_identifier,
    section_id,
    seat_number,
    is_accessible,
    is_available
)
SELECT
    chr(65 + ((gs.seat_no - 1) / 20)::int),
    s.section_id,
    (((gs.seat_no - 1) % 20) + 1)::text,
    (gs.seat_no % 50 = 0),
    NOT (gs.seat_no % 20 IN (0, 1))
FROM public.section s
         CROSS JOIN generate_series(1, 100) AS gs(seat_no);

-- at least sponsor number of rows
INSERT INTO public.sponsor_type_sponsor (
    sponsor_id,
    sponsor_type_id
)
SELECT
    s.sponsor_id,
    ((s.sponsor_id - 1) % (SELECT count(*) FROM public.sponsor_type)) + 1
FROM public.sponsor s;

-- at least event number of rows
INSERT INTO public.sponsor_event (
    event_id,
    sponsor_id,
    sponsor_type,
    sponsor_amount_paid
)
SELECT
    e.event_id,
    ((e.event_id - 1) % (SELECT count(*) FROM public.sponsor)) + 1,
    CASE (e.event_id % 4)
        WHEN 0 THEN 'Bronze'
        WHEN 1 THEN 'Silver'
        WHEN 2 THEN 'Gold'
        ELSE 'Platinum'
        END,
    CASE (e.event_id % 4)
        WHEN 0 THEN 100.00
        WHEN 1 THEN 250.00
        WHEN 2 THEN 500.00
        ELSE 999.99
        END
FROM public.event e
WHERE e.event_id % 100 < 20;

-- 60000 rows
INSERT INTO public.staff_member (
    assigned_role,
    name,
    surname,
    contact_phone,
    staff_team_id
)
SELECT
    ((gs - 1) % (SELECT count(*) FROM public.staff_role)) + 1,

    CASE (gs % 20)
        WHEN 0 THEN 'Andrej'
        WHEN 1 THEN 'Stefan'
        WHEN 2 THEN 'Martin'
        WHEN 3 THEN 'Filip'
        WHEN 4 THEN 'Nikola'
        WHEN 5 THEN 'David'
        WHEN 6 THEN 'Matej'
        WHEN 7 THEN 'Bojan'
        WHEN 8 THEN 'Petar'
        WHEN 9 THEN 'Viktor'
        WHEN 10 THEN 'Marija'
        WHEN 11 THEN 'Ana'
        WHEN 12 THEN 'Elena'
        WHEN 13 THEN 'Ivana'
        WHEN 14 THEN 'Sara'
        WHEN 15 THEN 'Teodora'
        WHEN 16 THEN 'Mila'
        WHEN 17 THEN 'Jovana'
        WHEN 18 THEN 'Tamara'
        ELSE 'Kristina'
        END,

    CASE ((gs / 20) % 20)
        WHEN 0 THEN 'Stojanov'
        WHEN 1 THEN 'Nikolov'
        WHEN 2 THEN 'Petrovski'
        WHEN 3 THEN 'Jovanov'
        WHEN 4 THEN 'Trajkovski'
        WHEN 5 THEN 'Mitrevski'
        WHEN 6 THEN 'Ristov'
        WHEN 7 THEN 'Iliev'
        WHEN 8 THEN 'Kostov'
        WHEN 9 THEN 'Georgiev'
        WHEN 10 THEN 'Ivanova'
        WHEN 11 THEN 'Stefanova'
        WHEN 12 THEN 'Petrova'
        WHEN 13 THEN 'Jovanova'
        WHEN 14 THEN 'Trajkovska'
        WHEN 15 THEN 'Mitrevska'
        WHEN 16 THEN 'Ristova'
        WHEN 17 THEN 'Ilieva'
        WHEN 18 THEN 'Kostova'
        ELSE 'Georgieva'
        END,

    '+389' ||
    CASE (gs % 10)
        WHEN 0 THEN '70'
        WHEN 1 THEN '71'
        WHEN 2 THEN '72'
        WHEN 3 THEN '73'
        WHEN 4 THEN '74'
        WHEN 5 THEN '75'
        WHEN 6 THEN '76'
        WHEN 7 THEN '77'
        WHEN 8 THEN '78'
        ELSE '79'
        END ||
    lpad((gs % 1000000)::text, 6, '0'),

    ((gs - 1) % (SELECT count(*) FROM public.staff_team)) + 1

FROM generate_series(1, 60000) AS gs;

-- about 30% of events
INSERT INTO public.staff_team_event_schedule (
    shift_start,
    shift_end,
    is_available,
    staff_team_id,
    event_id
)
SELECT
    e.start_datetime - interval '2 hours',
    e.end_datetime + interval '1 hour',
    CASE WHEN e.event_id % 20 = 0 THEN false ELSE true END,
    ((e.event_id - 1) % (SELECT count(*) FROM public.staff_team)) + 1,
    e.event_id
FROM public.event e
WHERE e.event_id % 100 < 30;

-- 100000 exhibitors
INSERT INTO public.exhibitor (
    name,
    surname,
    field_id
)
SELECT
    CASE (gs % 20)
        WHEN 0 THEN 'Andrej'
        WHEN 1 THEN 'Stefan'
        WHEN 2 THEN 'Martin'
        WHEN 3 THEN 'Filip'
        WHEN 4 THEN 'Nikola'
        WHEN 5 THEN 'Aleksandar'
        WHEN 6 THEN 'David'
        WHEN 7 THEN 'Matej'
        WHEN 8 THEN 'Petar'
        WHEN 9 THEN 'Bojan'
        WHEN 10 THEN 'Marija'
        WHEN 11 THEN 'Ana'
        WHEN 12 THEN 'Elena'
        WHEN 13 THEN 'Ivana'
        WHEN 14 THEN 'Sara'
        WHEN 15 THEN 'Teodora'
        WHEN 16 THEN 'Mila'
        WHEN 17 THEN 'Jovana'
        WHEN 18 THEN 'Tamara'
        ELSE 'Kristina'
        END,

    CASE ((gs / 20) % 20)
        WHEN 0 THEN 'Stojanov'
        WHEN 1 THEN 'Nikolov'
        WHEN 2 THEN 'Petrovski'
        WHEN 3 THEN 'Jovanov'
        WHEN 4 THEN 'Trajkovski'
        WHEN 5 THEN 'Mitrevski'
        WHEN 6 THEN 'Ristov'
        WHEN 7 THEN 'Iliev'
        WHEN 8 THEN 'Kostov'
        WHEN 9 THEN 'Georgiev'
        WHEN 10 THEN 'Ivanova'
        WHEN 11 THEN 'Stefanova'
        WHEN 12 THEN 'Petrova'
        WHEN 13 THEN 'Jovanova'
        WHEN 14 THEN 'Trajkovska'
        WHEN 15 THEN 'Mitrevska'
        WHEN 16 THEN 'Ristova'
        WHEN 17 THEN 'Ilieva'
        WHEN 18 THEN 'Kostova'
        ELSE 'Georgieva'
        END,

    ((gs - 1) % (SELECT count(*) FROM public.field)) + 1

FROM generate_series(1, 100000) AS gs;

-- 500000 rows
INSERT INTO public.subscription_organiser (
    user_id,
    organiser_id,
    created_at
)
SELECT
    ((gs - 1) % 1000000) + 1,
    ((gs - 1) % (SELECT count(*) FROM public.organiser)) + 1,
    timestamp '2023-01-01' + ((gs % 900) * interval '1 day')
FROM generate_series(1, 500000) AS gs;

-- 500000 rows
INSERT INTO public.subscription_location (
    user_id,
    location_id,
    created_at
)
SELECT
    ((gs - 1) % 1000000) + 1,
    ((gs - 1) % (SELECT count(*) FROM public.location)) + 1,
    timestamp '2023-01-01' + ((gs % 900) * interval '1 day')
FROM generate_series(1, 500000) AS gs;

-- multi-pass so some events have more than 1 exhibitor per session.
INSERT INTO public.exhibitor_event_schedule_session (
    exhibitor_id,
    event_schedule_session_id,
    start_time,
    end_time
)
SELECT
    ex.exhibitor_id,
    ess.schedule_id,
    ess.start_time,
    ess.end_time
FROM public.exhibitor ex
         JOIN (
    SELECT
        schedule_id,
        start_time,
        end_time,
        row_number() OVER (ORDER BY schedule_id) AS rn
    FROM public.event_schedule_session
) ess
              ON ess.rn = ((ex.exhibitor_id - 1) % (
                  SELECT count(*) FROM public.event_schedule_session
              )) + 1;

INSERT INTO public.exhibitor_event_schedule_session (
    exhibitor_id,
    event_schedule_session_id,
    start_time,
    end_time
)
SELECT
    ex.exhibitor_id,
    ess.schedule_id,
    ess.start_time,
    ess.end_time
FROM public.exhibitor ex
         JOIN (
    SELECT
        schedule_id,
        start_time,
        end_time,
        row_number() OVER (ORDER BY schedule_id) AS rn
    FROM public.event_schedule_session
) ess
              ON ess.rn = ((ex.exhibitor_id + 50000) % (
                  SELECT count(*) FROM public.event_schedule_session
              )) + 1
WHERE ex.exhibitor_id % 3 = 0
ON CONFLICT (exhibitor_id, event_schedule_session_id) DO NOTHING;

-- 10M order_cart rows, run in 1M batches
-- This is done so that if the script fails, the data is not lost, and the script can be rerun.
INSERT INTO public.order_cart (
    user_id,
    discount_id,
    status_id,
    created_at,
    total_price
)
SELECT
    ((gs - 1) % 1000000) + 1,

    CASE
        WHEN gs % 100 < 35
            THEN ((gs - 1) % (SELECT COUNT(*) FROM public.discount)) + 1
        ELSE NULL
        END,

    CASE
        WHEN gs % 100 < 72 THEN (SELECT status_id FROM public.order_status WHERE status_name = 'Paid')
        WHEN gs % 100 < 82 THEN (SELECT status_id FROM public.order_status WHERE status_name = 'Pending Payment')
        WHEN gs % 100 < 89 THEN (SELECT status_id FROM public.order_status WHERE status_name = 'Cancelled')
        WHEN gs % 100 < 94 THEN (SELECT status_id FROM public.order_status WHERE status_name = 'Refunded')
        WHEN gs % 100 < 98 THEN (SELECT status_id FROM public.order_status WHERE status_name = 'Expired')
        ELSE (SELECT status_id FROM public.order_status WHERE status_name = 'Open')
        END,

    timestamp '2023-01-01 10:00:00'
        + ((gs % 1100) * interval '1 day')
        + ((gs % 86400) * interval '1 second'),

    CASE
        WHEN gs % 10 IN (0,1,2,3) THEN 800 + (gs % 2200)
        WHEN gs % 10 IN (4,5,6)   THEN 1500 + (gs % 4500)
        WHEN gs % 10 IN (7,8)     THEN 3000 + (gs % 7000)
        ELSE 6000 + (gs % 12000)
        END::numeric(12,2)

FROM generate_series(9000001, 10000000) gs;

-- 10M payments, run in 1M batches
-- This is done so that if the script fails, the data is not lost, and the script can be rerun.
INSERT INTO public.payment (
    order_id,
    method_id,
    amount_paid,
    processed_at,
    transaction_id,
    is_refunded
)
SELECT
    o.order_id,

    ((o.order_id - 1) % (SELECT count(*) FROM public.payment_method)) + 1 AS method_id,

    o.total_price AS amount_paid,

    o.created_at + ((o.order_id % 3600) * interval '1 second') AS processed_at,

    'TXN-' || lpad(o.order_id::text, 12, '0') AS transaction_id,

    CASE
        WHEN o.order_id % 100 IN (0, 1, 2, 3, 4) THEN true
        ELSE false
        END AS is_refunded

FROM public.order_cart o
WHERE o.order_id BETWEEN 9000001 AND 10000000;

-- 10M payments, run in 1M batches
-- This is done so that if the script fails, the data is not lost, and the script can be rerun.
-- Paid/refunded orders get 1-4 tickets.
-- Pending/cancelled/expired/open orders get 0 tickets.
INSERT INTO public.ticket (
    order_id,
    ticket_type_id,
    lock_expires_at,
    status,
    barcode_hash,
    seat_id,
    is_scanned,
    scanned_at,
    is_presale
)
SELECT
    o.order_id,
    -- from select count(*) from public.ticket_type;
    ((o.order_id + gs.ticket_no - 2) % 1303698) + 1 AS ticket_type_id,

    o.created_at + interval '15 minutes' AS lock_expires_at,

    CASE
        WHEN o.status_id IN (3, 4) THEN 'VALID'
        WHEN o.status_id = 5 THEN 'REFUNDED'
        ELSE 'EXPIRED'
        END AS status,

    md5(o.order_id::text || '-' || gs.ticket_no::text) AS barcode_hash,

    -- from select count(*) from public.seat;
    (((o.order_id - 1) * 4 + gs.ticket_no - 1) % 10000000) + 1 AS seat_id,

    o.status_id IN (3, 4) AND o.order_id % 100 < 45 AS is_scanned,

    CASE
        WHEN o.status_id IN (3, 4) AND o.order_id % 100 < 45
            THEN o.created_at + interval '7 days'
        ELSE NULL
        END AS scanned_at,

    o.order_id % 100 < 55 AS is_presale

FROM public.order_cart o
         JOIN LATERAL generate_series(
        1,
        CASE
            WHEN o.status_id IN (1, 2, 6, 7) THEN 0
            WHEN o.order_id % 100 < 65 THEN 1
            WHEN o.order_id % 100 < 90 THEN 2
            WHEN o.order_id % 100 < 98 THEN 3
            ELSE 4
            END
                      ) gs(ticket_no) ON true
WHERE o.order_id BETWEEN 9000001 AND 10000000;

-- 1M reviews
-- Some events get 0 reviews, some get many reviews
-- Review dates are after the event ends
INSERT INTO public.review (
    user_id,
    event_id,
    star_rating,
    review_text,
    created_at
)
SELECT
    1 + ((gs::bigint * 15485863) % 1000000) AS user_id,

    e.event_id,

    CASE
        WHEN gs % 100 < 6 THEN 1
        WHEN gs % 100 < 15 THEN 2
        WHEN gs % 100 < 34 THEN 3
        WHEN gs % 100 < 68 THEN 4
        ELSE 5
        END AS star_rating,

    CASE (gs % 12)
        WHEN 0 THEN 'Great organization and smooth entry.'
        WHEN 1 THEN 'Good event, but the venue was crowded.'
        WHEN 2 THEN 'The program was interesting and useful.'
        WHEN 3 THEN 'Sound and lighting could have been better.'
        WHEN 4 THEN 'Excellent atmosphere and friendly staff.'
        WHEN 5 THEN 'Ticket scanning was fast and simple.'
        WHEN 6 THEN 'The location was easy to find.'
        WHEN 7 THEN 'Nice experience, would attend again.'
        WHEN 8 THEN 'The event started a bit late.'
        WHEN 9 THEN 'Good value for the ticket price.'
        WHEN 10 THEN 'Very well organized Balkan-style event.'
        ELSE 'Solid event overall.'
        END AS review_text,

    e.end_datetime
        + ((gs % 45) * interval '1 day')
        + ((gs % 86400) * interval '1 second') AS created_at

FROM generate_series(1, 1000000) AS gs
         JOIN public.event e
              ON e.event_id = 1 + ((gs::bigint * gs::bigint * 7919) % 500000);

-- 1M waitlist entries
-- Uneven distribution: most sessions get 0, popular sessions get many
-- Dates are based on the actual session start_time
INSERT INTO public.waitlist_entry (
    user_id,
    event_schedule_session_id,
    joined_at,
    status,
    notified_at,
    expires_at
)
SELECT
    1 + ((gs::bigint * 15485863) % 1000000) AS user_id,

    ns.schedule_id AS event_schedule_session_id,

    joined_at_value AS joined_at,

    status_value AS status,

    CASE
        WHEN status_value IN ('NOTIFIED', 'EXPIRED', 'CONVERTED')
            THEN joined_at_value + interval '2 days'
        ELSE NULL
        END AS notified_at,

    CASE
        WHEN status_value = 'EXPIRED'
            THEN joined_at_value + interval '7 days'
        ELSE NULL
        END AS expires_at

FROM generate_series(1, 1000000) AS gs
         JOIN public.event_schedule_session ns
              ON ns.schedule_id =
                 1 + (
                     (gs::bigint * gs::bigint * 7919)
                         % (SELECT max(schedule_id)::bigint FROM public.event_schedule_session)
                     )

         CROSS JOIN LATERAL (
    SELECT
        ns.start_time
            - ((1 + (gs % 60)) * interval '1 day')
            + ((gs % 86400) * interval '1 second') AS joined_at_value
    ) j

         CROSS JOIN LATERAL (
    SELECT
        CASE
            WHEN gs % 100 < 60 THEN 'ACTIVE'
            WHEN gs % 100 < 78 THEN 'NOTIFIED'
            WHEN gs % 100 < 90 THEN 'EXPIRED'
            ELSE 'CONVERTED'
            END AS status_value
    ) s;

-- fewer refunds than payments
INSERT INTO public.refund_request (
    reason,
    requested_at,
    accepted_at,
    user_id,
    payment_id
)
SELECT
    CASE (p.payment_id % 8)
        WHEN 0 THEN 'Event cancelled'
        WHEN 1 THEN 'User could not attend'
        WHEN 2 THEN 'Duplicate payment'
        WHEN 3 THEN 'Wrong ticket type selected'
        WHEN 4 THEN 'Schedule conflict'
        WHEN 5 THEN 'Payment issue'
        WHEN 6 THEN 'Venue changed'
        ELSE 'Other reason'
        END AS reason,

    p.processed_at + interval '1 day' AS requested_at,

    p.processed_at + interval '3 days' AS accepted_at,

    o.user_id,
    p.payment_id

FROM public.payment p
         JOIN public.order_cart o
              ON o.order_id = p.order_id
WHERE p.is_refunded = true;