DatabaseCreation: data_insertion.sql

CREATE TABLE temp_city_import (
    city TEXT,
    country TEXT
);

--country 242
INSERT INTO country (country_name)
SELECT DISTINCT TRIM(country)
FROM temp_city_import
WHERE country IS NOT NULL
ON CONFLICT (country_name) DO NOTHING;

--city 45 000
INSERT INTO city (city_name, postal_code, country_id)
SELECT
    city_name,
    LPAD((FLOOR(RANDOM() * 10000))::TEXT, 4, '0'),
    country_id
FROM (
    SELECT DISTINCT
        TRIM(tci.city) AS city_name,
        c.country_id
    FROM temp_city_import tci
    JOIN country c
        ON TRIM(tci.country) = TRIM(c.country_name)
    WHERE tci.city IS NOT NULL
      AND tci.country IS NOT NULL
) unique_cities
ON CONFLICT (city_name, country_id) DO NOTHING;


-- venues 200
create table temp_venue_import (title text, address text);

INSERT INTO venue (venue_title, street_address, capacity, city_id)
SELECT
    tvi.title,
    tvi.address,
    (FLOOR(RANDOM() * 19) * 1000 + 2000)::INT,
    (
        SELECT city_id
        FROM city
        WHERE tvi.title IS NOT NULL
        ORDER BY RANDOM()
        LIMIT 1
    )
FROM temp_venue_import tvi
WHERE tvi.title IS NOT NULL
  AND tvi.address IS NOT NULL
LIMIT 200;


-- seat 2 281 000 (sum of capacities of venues)
INSERT INTO seat (seat_number, row_number, section, section_type, venue_id)
SELECT
    s.seat_num,
    r.row_num,
    st.sec_id,
    st.type_name,
    v.venue_id
FROM venue v
CROSS JOIN LATERAL (
    VALUES
        (1, 'VIP',     0.10, 10), -- 10% капацитет, 10 во ред
        (2, 'REGULAR', 0.70, 20), -- 70% капацитет, 20 во ред
        (3, 'BALCONY', 0.20, 20)  -- 20% капацитет, 20 во ред
) AS st(sec_id, type_name, percent, seats_per_row)
CROSS JOIN LATERAL (
    -- Бидејќи (capacity * percent) е секогаш делливо со seats_per_row, нема да имаме децимали
    SELECT generate_series(1, ((v.capacity * st.percent) / st.seats_per_row)::INT) AS row_num
) AS r
CROSS JOIN LATERAL (
    SELECT generate_series(1, st.seats_per_row) AS seat_num
) AS s
WHERE v.capacity > 0;


-- sponsor type 7
INSERT INTO sponsor_type (sponsor_type_name, description)
VALUES
('Gold', 'High-level sponsor with major visibility'),
('Silver', 'Mid-level sponsor with moderate visibility'),
('Bronze', 'Basic sponsorship level with limited visibility'),
('Platinum', 'Top-tier exclusive sponsor'),
('Corporate', 'Large company supporting the event'),
('Media', 'Provides media coverage and promotion'),
('Event', 'Sponsors a specific event');



-- sponsor 2000
CREATE TABLE temp_company_names (
    company_name TEXT
);

INSERT INTO sponsor (sponsor_name, contact_email, website, sponsor_type_id)
SELECT
    tcn.company_name,
    lower(replace(tcn.company_name, ' ', '.')) || '@businessmail.com' AS contact_email,
    'https://www.' || lower(replace(tcn.company_name, ' ', '')) || '.com' AS website,
    (
        SELECT sponsor_type_id
        FROM sponsor_type
        WHERE tcn.company_name IS NOT NULL
        ORDER BY random()
        LIMIT 1
    ) AS sponsor_type_id
FROM temp_company_names tcn
WHERE tcn.company_name IS NOT NULL;

SELECT * FROM sponsor;


-- ticket type 5
INSERT INTO ticket_type (type_name) VALUES
('Standard'),
('VIP'),
('Student'),
('Child'),
('Group');




-- category

-- 1. PARENT CATEGORIES
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Events', 'All types of public and private events', NULL),
('Entertainment', 'Shows, concerts, movies and performances', NULL),
('Sports', 'Sport competitions and tournaments', NULL),
('Education', 'Learning and training activities', NULL),
('Business', 'Corporate and professional events', NULL),
('Technology', 'Tech-related events and expos', NULL),
('Lifestyle', 'Social and lifestyle events', NULL);

-- 2. EVENTS SUBCATEGORIES
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Concerts', 'Live music performances', 1),
('Festivals', 'Seasonal and cultural festivals', 1),
('Exhibitions', 'Art and museum exhibitions', 1),
('Fairs', 'Public fairs and trade fairs', 1),
('Ceremonies', 'Official and private ceremonies', 1);

-- 3. ENTERTAINMENT
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Theater', 'Stage plays and performances', 2),
('Cinema', 'Movie screenings and premieres', 2),
('Comedy Shows', 'Stand-up comedy events', 2),
('Dance Shows', 'Dance and performance shows', 2);

-- 4. SPORTS
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Football', 'Football matches and tournaments', 3),
('Basketball', 'Basketball leagues and games', 3),
('Tennis', 'Tennis tournaments', 3),
('Athletics', 'Track and field competitions', 3),
('Martial Arts', 'Combat sports events', 3);

-- 5. EDUCATION
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Workshops', 'Hands-on skill workshops', 4),
('Seminars', 'Educational seminars and talks', 4),
('Online Courses', 'Virtual learning sessions', 4),
('Training Sessions', 'Professional training programs', 4),
('Lectures', 'Academic lectures and presentations', 4);

-- 6. BUSINESS
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Conferences', 'Large professional conferences', 5),
('Networking Events', 'Business networking meetups', 5),
('Product Launches', 'New product presentations', 5),
('Work Meetings', 'Corporate meetings and events', 5),
('Trade Shows', 'Industry exhibitions and fairs', 5);

-- 7. TECHNOLOGY
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Tech Conferences', 'Technology industry conferences', 6),
('Hackathons', 'Programming competitions', 6),
('Startup Events', 'Startup pitching and networking', 6),
('AI Workshops', 'Artificial intelligence learning events', 6),
('IT Seminars', 'Information technology seminars', 6);

-- 8. LIFESTYLE
INSERT INTO category (category_name, category_description, parent_category_id) VALUES
('Fashion Shows', 'Clothing and fashion events', 7),
('Food Festivals', 'Food and drink festivals', 7),
('Wellness Events', 'Health and wellness activities', 7),
('Charity Events', 'Fundraising and charity activities', 7),
('Social Gatherings', 'Community and social meetups', 7);



-- customers 10000
CREATE TABLE temp_name(first_name TEXT);
CREATE TABLE temp_surname(last_name TEXT);

INSERT INTO customer (email, first_name, last_name, hash_password)
SELECT
    LOWER(fn.first_name || '.' || ln.last_name || '@gmail.com') AS email,
    fn.first_name,
    ln.last_name,
    MD5(RANDOM()::TEXT) AS hash_password
FROM (SELECT DISTINCT first_name FROM temp_name) fn
CROSS JOIN (SELECT DISTINCT last_name FROM temp_surname) ln
LIMIT 10000;


-- admins 1000
CREATE TABLE temp_admin_name(first_name TEXT);
CREATE TABLE temp_admin_surname(last_name TEXT);

INSERT INTO admin (email, first_name, last_name, password_hash)
SELECT
    LOWER(fn.first_name || '.' || ln.last_name || ROW_NUMBER() OVER() || '@event.com') AS email,
    fn.first_name,
    ln.last_name,
    MD5(RANDOM()::TEXT) AS password_hash
FROM temp_admin_name fn
CROSS JOIN temp_admin_surname ln
LIMIT 1000;

-- Event admins ~ 600
INSERT INTO event_admin (admin_id)
SELECT admin_id FROM admin ORDER BY RANDOM() LIMIT 600;

-- Ticket admins ~ 400
INSERT INTO ticket_admin (admin_id)
SELECT a.admin_id
FROM admin a
WHERE NOT EXISTS (SELECT 1 FROM event_admin ea WHERE ea.admin_id = a.admin_id);

-- 30% overlap (event admin da e i ticket admin)
INSERT INTO ticket_admin (admin_id)
SELECT admin_id
FROM event_admin
ORDER BY RANDOM()
LIMIT (SELECT COUNT(*) * 0.3 FROM event_admin);



-- discount 1200
INSERT INTO discount (discount_code, discount_percent, event_admin_id)
SELECT
    'DSC' || upper(substr(md5(random()::text), 1, 4)) || row_number() OVER() AS discount_code,

    CASE
        WHEN random() < 0.45 THEN (floor(random() * 16) + 5)
        WHEN random() < 0.75 THEN (floor(random() * 15) + 21)
        WHEN random() < 0.92 THEN (floor(random() * 15) + 36)
        ELSE (floor(random() * 20) + 51)
    END AS discount_percent,

    ea.admin_id
FROM event_admin ea
CROSS JOIN generate_series(1, 2) AS i;


-- payment
INSERT INTO payment (amount, payment_method, payment_status, payment_date, customer_id, discount_id)
SELECT
    -- Пресметка на финалниот износ (со вклучен попуст)
    CASE
        WHEN d.discount_percent IS NOT NULL THEN
            -- Пример: 5000 * (1 - 20/100) = 4000
            ((floor(random() * 471) * 10 + 300) * (1 - d.discount_percent / 100.0))::NUMERIC(10,2)
        ELSE
            -- Нема попуст, плаќа полна цена
            (floor(random() * 471) * 10 + 300)::NUMERIC(10,2)
    END AS amount,

    (ARRAY['CARD','ONLINE','CASH','TRANSFER'])[FLOOR(RANDOM() * 4 + 1)::int] AS payment_method,

    CASE
        WHEN random() < 0.80 THEN 'COMPLETED'
        WHEN random() < 0.90 THEN 'FAILED'
        WHEN random() < 0.96 THEN 'PENDING'
        ELSE 'REFUNDED'
    END AS payment_status,

    NOW() - (RANDOM() * INTERVAL '730 days') AS payment_date,

    (FLOOR(RANDOM() * 10000) + 1)::int AS customer_id,

    d.discount_id
FROM generate_series(1, 5000000) AS s
-- Со LEFT JOIN ги додаваме процентите на попуст за тие 15% од редовите
LEFT JOIN (
    SELECT discount_id, discount_percent, row_number() OVER () as rnum
    FROM discount
) d ON (s % 100 < 15) AND (d.rnum = (s % (SELECT count(*) FROM discount) + 1));


-- event 1600
INSERT INTO event (name, description, start_datetime, end_datetime, status, venue_id, category_id)
SELECT
    (ARRAY['Global', 'Premium', 'International', 'Live', 'Elite', 'Historic', 'Annual', 'Virtual'])[FLOOR(RANDOM() * 8 + 1)]
    || ' ' || cat.category_name
    || ' ' || (ARRAY['Summit', 'Festival', 'Gala', 'Masterclass', 'Experience', 'Championship'])[FLOOR(RANDOM() * 6 + 1)] AS name,

    'Exclusive ' || LOWER(cat.category_name) || ' event happening at a top venue.' AS description,

    eb.gen_start_dt AS start_datetime,
    eb.gen_start_dt + (INTERVAL '1 minute' * (FLOOR(RANDOM() * 241) + 120)) AS end_datetime,

    CASE
        WHEN eb.gen_start_dt < NOW() - INTERVAL '1 day' THEN
            CASE WHEN RANDOM() < 0.05 THEN 'CANCELLED' ELSE 'COMPLETED' END
        WHEN eb.gen_start_dt BETWEEN NOW() - INTERVAL '1 day' AND NOW() + INTERVAL '1 day' THEN 'ONGOING'
        ELSE 'PUBLISHED'
    END AS status,

    -- Го користиме r_idx во OFFSET за да го присилиме PostgreSQL да пребарува различно секој пат
    (SELECT venue_id FROM venue OFFSET (eb.r_idx % (SELECT count(*) FROM venue)) LIMIT 1) as venue_id,

    cat.category_id
FROM (
    SELECT
        row_number() OVER () as r_idx,
        NOW() + (INTERVAL '1 day' * (FLOOR(RANDOM() * 600) - 400))
              + (INTERVAL '1 minute' * FLOOR(RANDOM() * 1440)) AS gen_start_dt
    FROM generate_series(1, 1200)
) eb
CROSS JOIN LATERAL (
    SELECT category_id, category_name
    FROM category
    WHERE parent_category_id IS NOT NULL
    -- Овде додаваме услов кој зависи од eb.r_idx за да спречиме кеширање
    AND eb.r_idx IS NOT NULL
    ORDER BY RANDOM()
    LIMIT 1
) cat;


-- event_manages ~2421
INSERT INTO event_manages (event_id, event_admin_id)
SELECT
    e.event_id,
    adm.admin_id
FROM event e
CROSS JOIN LATERAL (
    -- Ова подпрашање генерира случаен број на админи (1-3) за секој настан
    SELECT ea.admin_id
    FROM event_admin ea
    WHERE e.event_id IS NOT NULL -- Го присилуваме ре-евалуирањето за секој настан
    ORDER BY RANDOM()
    LIMIT (FLOOR(RANDOM() * 3) + 1) -- Секој настан ќе има 1, 2 или 3 админи
) adm;


-- event sponsorship ~ 120 000
INSERT INTO event_sponsorship (sponsor_id, event_id, sponsorship_amount, contract_date)
SELECT DISTINCT ON (e.event_id, rand_s.sponsor_id)
    rand_s.sponsor_id,
    e.event_id,
    (FLOOR(RANDOM() * 14501) * 10 + 5000)::NUMERIC(12,2) AS sponsorship_amount,
    LEAST(
        NOW(),
        e.start_datetime - (INTERVAL '1 day' * (FLOOR(RANDOM() * 30) + 5))
    ) - (INTERVAL '1 day' * FLOOR(RANDOM() * 60)) AS contract_date

FROM event e
CROSS JOIN LATERAL generate_series(1, 100) AS s_count
CROSS JOIN LATERAL (
    SELECT s.sponsor_id
    FROM sponsor s
    WHERE e.event_id IS NOT NULL
    ORDER BY RANDOM()
    LIMIT (FLOOR(RANDOM() * 51) + 50)
) rand_s;


-- event media
INSERT INTO event_media (url, event_id)
SELECT
    'https://picsum.photos/seed/' || e.event_id || '-' || s.idx || '/1200/800' AS url,
    e.event_id
FROM event e
CROSS JOIN LATERAL (
    -- Генерираме случаен број на слики (помеѓу 20 и 50) за секој настан
    SELECT generate_series(1, (FLOOR(RANDOM() * 31) + 20)::INT) AS idx
) s;


-- Поправка на payment бидејќи сите редици имаат discount id и се користат само првите 15 discount кодови

DO $$
DECLARE
    total_discounts INT;
BEGIN
    SELECT count(*) INTO total_discounts FROM discount;

    -- 1. Прво ги ресетираме сите на NULL за да нема грешки од претходно
    UPDATE payment SET discount_id = NULL;

    -- 2. Ажурираме 15% од редовите со вистински попусти (користиме batches за брзина)
    -- Ова ќе ги искористи сите 1200 кодови (payment_id % total_discounts)
    UPDATE payment
    SET discount_id = (payment_id % total_discounts) + 1
    WHERE payment_id % 100 < 15;

    -- 3. Го средуваме amount во payment:
    -- Ако е NULL, ставаме полна цена. Ако има попуст, amount веќе се смета за платена сума
    UPDATE payment p
    SET amount = (floor(random() * 471) * 10 + 300)::NUMERIC(10,2)
    WHERE p.discount_id IS NULL;

    COMMIT;
END $$;


-- tickets

--Проверка само за еден настан

DO $$
DECLARE
    r_event RECORD;
    v_total_seats INT;
    v_to_fill INT;
    v_offset_completed INT := 0;
    v_offset_refunded INT := 0;
    v_total_refunded INT := 3926;
BEGIN
    FOR r_event IN (SELECT event_id, venue_id, start_datetime, end_datetime, status as event_status FROM event ORDER BY start_datetime ASC LIMIT 1) LOOP

        SELECT count(*) INTO v_total_seats FROM seat WHERE venue_id = r_event.venue_id;

        IF r_event.start_datetime > '2026-05-01' THEN
            v_to_fill := floor(v_total_seats * (random() * 0.15 + 0.05));
        ELSE
            v_to_fill := floor(v_total_seats * (random() * 0.55 + 0.30));
        END IF;

        INSERT INTO ticket (
            status, reserved_at, expires_at, purchased_at,
            ticket_price, qr_code, customer_id, seat_id,
            event_id, ticket_type_id, payment_id
        )
        SELECT
            CASE
                WHEN r_event.event_status = 'CANCELLED' THEN 'CANCELLED'
                WHEN p.payment_id IS NOT NULL THEN
                    (CASE WHEN r_event.end_datetime < NOW() THEN 'SCANNED' ELSE 'PURCHASED' END)
                WHEN r_event.end_datetime < NOW() THEN 'CANCELLED'
                WHEN s.rn <= v_to_fill THEN 'RESERVED'
                ELSE 'AVAILABLE'
            END,
            
            CASE
                WHEN p.payment_id IS NOT NULL THEN LEAST(p.payment_date, r_event.start_datetime) - INTERVAL '25 minutes'
                WHEN s.rn <= v_to_fill AND r_event.end_datetime > NOW() AND r_event.event_status != 'CANCELLED' THEN NOW() - INTERVAL '10 minutes'
                ELSE NULL
            END,
            r_event.end_datetime,
            
            CASE
                WHEN p.payment_id IS NOT NULL THEN LEAST(p.payment_date, r_event.end_datetime - INTERVAL '1 minute')
                ELSE NULL
            END,
            CASE
                WHEN d.discount_percent IS NOT NULL THEN (p.amount / (1.0 - (d.discount_percent / 100.0)))::NUMERIC(10,2)
                WHEN p.payment_id IS NOT NULL THEN p.amount
                ELSE (FLOOR(RANDOM() * 400) + 250)::NUMERIC(10,2)
            END,
            md5(r_event.event_id::text || s.seat_id::text || random()::text),
            p.customer_id, s.seat_id, r_event.event_id, 1, p.payment_id
        FROM (
            SELECT seat_id, ROW_NUMBER() OVER (ORDER BY seat_id) as rn
            FROM seat WHERE venue_id = r_event.venue_id
        ) s
        LEFT JOIN LATERAL (
            SELECT payment_id, amount, customer_id, payment_date, discount_id, payment_status
            FROM payment
            WHERE
                (r_event.event_status = 'CANCELLED' AND payment_status = 'REFUNDED' AND payment_id > v_offset_refunded)
                OR
                (r_event.event_status != 'CANCELLED' AND payment_status = 'COMPLETED' AND payment_id > v_offset_completed)
            ORDER BY payment_id
            LIMIT 1 OFFSET (s.rn - 1)
        ) p ON s.rn <= CASE WHEN r_event.event_status = 'CANCELLED' THEN 85 ELSE floor(v_to_fill * 0.8) END
        LEFT JOIN discount d ON p.discount_id = d.discount_id;

        RAISE NOTICE 'Тестот е готов за настанот: %', r_event.event_id;
    END LOOP;
END $$;


-- Сите настани по batches

-- 1. Креираме привремена копија на плаќањата подредени по редослед
DROP TABLE IF EXISTS temp_pay_queue;
CREATE TEMP TABLE temp_pay_queue AS
SELECT payment_id, amount, customer_id, payment_date, payment_status,
       ROW_NUMBER() OVER (PARTITION BY payment_status ORDER BY payment_id) as rn
FROM payment;

-- 2. Овој индекс е САМО за оваа привремена табела
CREATE INDEX idx_temp_pay_rn ON temp_pay_queue(payment_status, rn);


DO $$
DECLARE
    r_event RECORD;
    v_total_seats INT;
    v_to_fill INT;
    v_event_counter INT := 0;
    v_pay_ptr INT := 1;  -- Почнуваме од првото COMPLETED плаќање во temp табелата
    v_ref_ptr INT := 1;  -- Почнуваме од првото REFUNDED плаќање во temp табелата
BEGIN
    
    TRUNCATE TABLE ticket RESTART IDENTITY CASCADE;

    FOR r_event IN (
        SELECT event_id, venue_id, start_datetime, end_datetime, status as event_status
        FROM event
        ORDER BY start_datetime ASC
    ) LOOP

        -- 1. Земи капацитет за венеуто на настанот
        SELECT count(*) INTO v_total_seats FROM seat WHERE venue_id = r_event.venue_id;

        -- 2. Одреди колку вкупно седишта ќе бидат „зафатени“ (продадени/резервирани)
        IF r_event.start_datetime > '2026-05-01' THEN
            v_to_fill := floor(v_total_seats * (random() * 0.15 + 0.05));
        ELSE
            v_to_fill := floor(v_total_seats * (random() * 0.55 + 0.30));
        END IF;

        -- 3. Полнење на TICKET
        INSERT INTO ticket (
            status, reserved_at, expires_at, purchased_at,
            ticket_price, qr_code, customer_id, seat_id,
            event_id, ticket_type_id, payment_id
        )
        SELECT
            CASE
                WHEN r_event.event_status = 'CANCELLED' THEN 'CANCELLED'
                WHEN tp.payment_id IS NOT NULL THEN
                    (CASE WHEN r_event.end_datetime < NOW() THEN 'SCANNED' ELSE 'PURCHASED' END)
                WHEN s.rn <= v_to_fill AND r_event.end_datetime > NOW() THEN 'RESERVED'
                ELSE 'AVAILABLE'
            END,
            -- Reserved_at
            CASE
                WHEN tp.payment_id IS NOT NULL THEN LEAST(tp.payment_date, r_event.start_datetime) - INTERVAL '25 minutes'
                WHEN s.rn <= v_to_fill AND r_event.end_datetime > NOW() THEN NOW() - INTERVAL '10 minutes'
                ELSE NULL
            END,
            r_event.end_datetime, -- Expires_at
            -- Purchased_at
            CASE
                WHEN tp.payment_id IS NOT NULL THEN LEAST(tp.payment_date, r_event.end_datetime - INTERVAL '1 minute')
                ELSE NULL
            END,
            -- Цена (од плаќање или рандом)
            COALESCE(tp.amount, (FLOOR(RANDOM() * 400) + 250)::NUMERIC(10,2)),
            md5(r_event.event_id::text || s.seat_id::text || random()::text),
            tp.customer_id, s.seat_id, r_event.event_id, 1, tp.payment_id
        FROM (
            SELECT seat_id, ROW_NUMBER() OVER (ORDER BY seat_id) as rn
            FROM seat WHERE venue_id = r_event.venue_id
        ) s
        LEFT JOIN temp_pay_queue tp ON
            (
                r_event.event_status = 'CANCELLED'
                AND tp.payment_status = 'REFUNDED'
                AND tp.rn = (v_ref_ptr + s.rn - 1)
                AND s.rn <= 85 -- Земи точно 85 рефундирани за откажан настан
            )
            OR
            (
                r_event.event_status != 'CANCELLED'
                AND tp.payment_status = 'COMPLETED'
                AND tp.rn = (v_pay_ptr + s.rn - 1)
                AND s.rn <= floor(v_to_fill * 0.8) -- 80% од пополнетоста се продадени
            )
        WHERE s.rn <= v_total_seats;

        -- 4. Помести ги покажувачите за следниот настан
        IF r_event.event_status = 'CANCELLED' THEN
            v_ref_ptr := v_ref_ptr + 85;
        ELSE
            v_pay_ptr := v_pay_ptr + floor(v_to_fill * 0.8);
        END IF;

        v_event_counter := v_event_counter + 1;

        -- Известување за прогрес
        IF v_event_counter % 1 = 0 THEN
            RAISE NOTICE 'Настан % завршен (ID: %). Вкупно тикети: %',
                         v_event_counter, r_event.event_id, (SELECT count(*) FROM ticket);
            COMMIT;
        END IF;
    END LOOP;

    RAISE NOTICE 'Процесот е комплетиран успешно!';
END $$;


-- review
TRUNCATE TABLE review RESTART IDENTITY CASCADE;

INSERT INTO review (rating, comment, customer_id, event_id)
SELECT
    v.rating,
    CASE
        -- 35% од луѓето не оставаат коментар (NULL)
        WHEN (random() * 100)::int % 3 = 0 THEN NULL

        -- Позитивни (4 и 5)
        WHEN v.rating >= 4 THEN
            (ARRAY['Amazing experience!', 'Great organization.', 'The sound was perfect.', 'Loved every second!', 'Unforgettable night.', 'Everything was spot on!', 'Fantastic atmosphere.', 'Would come again!'])[floor(random() * 8 + 1)]

        -- Неутрални (3)
        WHEN v.rating = 3 THEN
            (ARRAY['Good but crowded.', 'Average experience.', 'Solid performance.', 'It was okay.'])[floor(random() * 4 + 1)]

        -- Негативни (1 и 2)
        ELSE
            (ARRAY['Disappointing.', 'Could be much better.', 'Not worth the money.', 'Bad organization.', 'Sound was terrible.'])[floor(random() * 5 + 1)]
    END AS comment,
    v.customer_id,
    v.event_id
FROM (
    -- Ова под-барање гарантира дека RANDOM() се повикува за СЕКОЈ ред посебно
    SELECT
        customer_id,
        event_id,
        (CASE
            WHEN random() < 0.5 THEN (floor(random() * 2) + 4)::SMALLINT -- 50% шанса за 4 или 5
            WHEN random() < 0.8 THEN 3::SMALLINT                         -- 30% шанса за 3
            ELSE (floor(random() * 2) + 1)::SMALLINT                   -- 20% шанса за 1 или 2
        END) AS rating
    FROM ticket
    WHERE status = 'SCANNED'
) v
ON CONFLICT (customer_id, event_id) DO NOTHING;


-- notification
INSERT INTO notification (title, message, created_at, is_read, customer_id, event_id)
SELECT
    'New Event Alert: ' || e.name,
    'Hey ' || c.first_name || ', a new event "' || e.name || '" has been published! Book your tickets now.',
    -- Ова гарантира дека created_at никогаш нема да го помине денешниот датум
    LEAST(e.start_datetime - INTERVAL '30 days', CURRENT_TIMESTAMP),
    -- Прочитано само ако е навистина старо
    (LEAST(e.start_datetime - INTERVAL '30 days', CURRENT_TIMESTAMP) < NOW() - INTERVAL '1 month'),
    c.customer_id,
    e.event_id
FROM event e
CROSS JOIN customer c
WHERE e.status = 'PUBLISHED';

INSERT INTO notification (title, message, created_at, is_read, customer_id, event_id)
SELECT
    'Event Cancelled: ' || e.name,
    'Dear ' || c.first_name || ', we regret to inform you that "' || e.name || '" has been cancelled. Your refund is processed.',
    -- Известувањето се пратило 2 дена пред планираниот почеток, но не подоцна од СЕГА
    LEAST(e.start_datetime - INTERVAL '2 days', CURRENT_TIMESTAMP),
    -- Бидејќи е итна вест, претпоставуваме дека 95% ја прочитале ако поминал 1 ден
    (LEAST(e.start_datetime - INTERVAL '2 days', CURRENT_TIMESTAMP) < NOW() - INTERVAL '1 day'),
    t.customer_id,
    e.event_id
FROM ticket t
JOIN event e ON t.event_id = e.event_id
JOIN customer c ON t.customer_id = c.customer_id
WHERE t.status = 'CANCELLED';

INSERT INTO notification (title, message, created_at, is_read, customer_id, event_id)
SELECT
    'How was ' || e.name || '?',
    'Hi ' || c.first_name || ', share your experience! Leave a review for "' || e.name || '" and help others.',
    -- Нотификацијата се праќа 2 часа по завршувањето на настанот
    LEAST(e.end_datetime + INTERVAL '2 hours', CURRENT_TIMESTAMP),
    -- Прочитани се ако поминале повеќе од 2 недели од праќањето
    (LEAST(e.end_datetime + INTERVAL '2 hours', CURRENT_TIMESTAMP) < NOW() - INTERVAL '14 days'),
    t.customer_id,
    e.event_id
FROM ticket t
JOIN event e ON t.event_id = e.event_id
JOIN customer c ON t.customer_id = c.customer_id
WHERE t.status = 'SCANNED';


INSERT INTO notification (title, message, created_at, is_read, customer_id, event_id)
SELECT
    'Starting Soon: ' || e.name,
    'Hey ' || c.first_name || ', get ready! "' || e.name || '" starts in 1 hour. Have your QR code ready!',
    -- Се праќа точно 1 час пред почетокот
    LEAST(e.start_datetime - INTERVAL '1 hour', CURRENT_TIMESTAMP),
    -- Скоро секогаш е FALSE (непрочитано), освен ако настанот веќе почнал одамна
    (LEAST(e.start_datetime - INTERVAL '1 hour', CURRENT_TIMESTAMP) < NOW() - INTERVAL '1 day'),
    t.customer_id,
    e.event_id
FROM ticket t
JOIN event e ON t.event_id = e.event_id
JOIN customer c ON t.customer_id = c.customer_id
WHERE t.status = 'PURCHASED';

-- seat reservation
INSERT INTO seat_reservation (reserved_at, status, seat_id, ticket_id)
SELECT
    -- Ако нема reserved_at, земи purchased_at, ако нема и тоа, стави 2 часа пред почеток на настанот
    COALESCE(t.reserved_at, t.purchased_at, e.start_datetime - INTERVAL '2 hours'),
    CASE
        WHEN t.status = 'RESERVED' THEN 'ACTIVE'
        WHEN t.status = 'CANCELLED' THEN 'CANCELLED'
        ELSE 'EXPIRED'
    END,
    t.seat_id,
    t.ticket_id
FROM ticket t
JOIN event e ON t.event_id = e.event_id
WHERE t.status != 'AVAILABLE';

INSERT INTO seat_reservation (reserved_at, status, seat_id, ticket_id)
SELECT
    COALESCE(t.reserved_at, t.purchased_at, e.start_datetime - INTERVAL '2 hours') - INTERVAL '45 minutes',
    'EXPIRED',
    t.seat_id,
    t.ticket_id
FROM ticket t
JOIN event e ON t.event_id = e.event_id
WHERE t.status != 'AVAILABLE'
ORDER BY random()
LIMIT 1000000;



-- Бришење на temp табелите
DROP TABLE IF EXISTS temp_city_import, temp_venue_import, temp_name, temp_surname, temp_company_names, temp_admin_name, temp_admin_surname;