DatabaseCreation: insert-tables-eventix.sql

 ------------- INSERT во мали табели и INSERT на default вредности за поголеми табели -------------

 -- SYSTEM / UNKNOWN USER 
INSERT INTO APP_USER (id, first_name, last_name, email, phone, created_at, user_password) VALUES (1, 'Unknown', 'User', 'unknown@system.com', '0000000', CURRENT_DATE, 'system');

 -- STATUS 
INSERT INTO STATUS (status_name, description) VALUES ('CREATED', 'Order has been created, but not processed yet'), ('PAID', 'Order has been paid for in full and has been completed'), ('CANCELLED', 'Order has been cancelled, no payment has went through');

 -- PAYMENT_METHOD 
INSERT INTO PAYMENT_METHOD (method_name) VALUES ('CASH'), ('CARD');

 -- TICKET_TYPE 
INSERT INTO TICKET_TYPE (type_name) VALUES ('STANDARD'), ('VIP'), ('PARTER'), ('GENERAL_ADMISSION');

 -- ROLES 
INSERT INTO ROLES (role_name) VALUES ('ADMINISTRATOR'), ('USER');

 -- EVENT_ROLE 
INSERT INTO EVENT_ROLE (role_name) VALUES ('EVENT_ADMIN'), ('SALES_MANAGER'), ('INFO_ADMIN'), ('CONTENT_MANAGER');

 -- CATEGORIZATION 
INSERT INTO categorization (category_name) VALUES ('Uncategorized'), ('Concerts'), ('Theatre'), ('Cinema'), ('Festivals'), ('Exhibitions'), ('Seminars');

 -- SUBCATEGORY 
INSERT INTO SUBCATEGORY (subcategory_name, CATEGORIZATIONid)
SELECT sub.subcategory_name, c.id
FROM CATEGORIZATION c
JOIN (VALUES
    ('Concerts', 'Rock'),
    ('Concerts', 'Pop'),
    ('Concerts', 'Jazz'),
    ('Concerts', 'Classical'),
    ('Concerts', 'Electronic'),
    ('Theatre', 'Drama'),
    ('Theatre', 'Comedy'),
    ('Theatre', 'Musical'),
    ('Theatre', 'Opera'),
    ('Cinema', 'Action'),
    ('Cinema', 'Drama'),
    ('Cinema', 'Horror'),
    ('Cinema', 'Documentary'),
    ('Festivals', 'Music'),
    ('Festivals', 'Food'),
    ('Festivals', 'Art'),
    ('Festivals', 'Film'),
    ('Exhibitions', 'Art'),
    ('Exhibitions', 'Science'),
    ('Exhibitions', 'History'),
    ('Exhibitions', 'Technology'),
    ('Seminars', 'Business'),
    ('Seminars', 'Technology'),
    ('Seminars', 'Health'),
    ('Seminars', 'Education')
) AS sub(cat_name, subcategory_name) ON c.category_name  = sub.cat_name;



 ------------------------------------ INSERT во табела APP_USER ------------------------------------ --

DROP TABLE IF EXISTS temp_male_names, temp_female_names, surnames;
CREATE TABLE temp_surnamesss (surname TEXT);
WITH all_names AS (
    SELECT DISTINCT name FROM temp_male_names
    UNION
    SELECT DISTINCT name FROM temp_female_names
),
surnames AS (
    SELECT DISTINCT surname FROM temp_surnamesss
)
INSERT INTO app_user (first_name, last_name, email, phone, created_at, user_password)
SELECT 
    n.name AS first_name,
    s.surname AS last_name,

    regexp_replace(n.name || '.' || s.surname, '[^A-Za-z0-9._%+-]', '', 'g') 
    || floor(random()*100000)::text || '@gmail.com' AS email,

    (trunc(random() * 900000000) + 100000000)::text AS phone,

    CURRENT_DATE - (trunc(random() * 3650))::int AS created_at,

    n.name || s.surname AS user_password

FROM all_names n
CROSS JOIN surnames s
ORDER BY random()
LIMIT 50000;

 ------------------------------------ INSERT во табела USER_ROLES ------------------------------------ --

INSERT INTO user_roles (app_userid, rolesid)
SELECT u.id, r.id
FROM app_user u
JOIN roles r ON r.role_name = 'USER'
UNION ALL
SELECT u.id, r.id
FROM app_user u
JOIN roles r ON r.role_name = 'ADMINISTRATOR'
WHERE random() < 0.001;

 ------------------------------------ INSERT во табела VENUE ------------------------------------ --

-- Podatocite za iminja, ulici i gradovi se vnesuvaat od CSV fajlovi

DROP TABLE IF EXISTS temp_venue_prefix, temp_venue_names, temp_street_names, temp_street_sufix, temp_cities; 

create TEMP TABLE temp_venue_prefix (name TEXT);
create TEMP TABLE temp_venue_names  (name TEXT);
create TEMP TABLE temp_street_names (name TEXT);
create TEMP TABLE temp_street_sufix (name TEXT);
create TEMP TABLE temp_cities       (name TEXT);

\copy temp_venue_prefix(name) FROM 'D:\Downloads\prefix_venue.csv' DELIMITER ',' CSV HEADER;
\copy temp_venue_names(name)  FROM 'D:\Downloads\venue_name.csv'  DELIMITER ',' CSV HEADER;
\copy temp_street_names(name) FROM 'D:\Downloads\street_name.csv' DELIMITER ',' CSV HEADER;
\copy temp_street_sufix(name) FROM 'D:\Downloads\street_sufix.csv' DELIMITER ',' CSV HEADER;
\copy temp_cities(name)       FROM 'D:\Downloads\cities.csv'       DELIMITER ',' CSV HEADER;


DROP TABLE IF EXISTS temp_venuess, temp_addreses;
CREATE TEMP TABLE temp_venuess (id INT, venue TEXT);
INSERT INTO temp_venuess (id, venue)
SELECT row_number() OVER () AS id, venue
FROM (
    SELECT DISTINCT
        CASE variant
            WHEN 1 THEN vp.name || ' ' || vn.name
            WHEN 2 THEN vp.name || ' ' || vn.name || ' I'
            WHEN 3 THEN vp.name || ' ' || vn.name || ' II'
            WHEN 4 THEN vp.name || ' ' || vn.name || ' III'
        END AS venue
    FROM temp_venue_prefix vp
    CROSS JOIN temp_venue_names vn
    CROSS JOIN generate_series(1, 4) AS variant
) unique_names
LIMIT 50000;

 -- Адреси --
CREATE TEMP TABLE temp_addreses (id INT, city TEXT, adress TEXT);

INSERT INTO temp_addreses (id, city, adress)
SELECT
    row_number() OVER () AS id,
    NULL AS city,
    num::TEXT || ' ' || sn.name || ' ' || ss.name AS adress
FROM temp_street_names sn
CROSS JOIN temp_street_sufix ss
CROSS JOIN generate_series(1, 99) AS num
LIMIT 50000;

 -- city --
WITH addr AS (
    select a.*, row_number() OVER () AS rn FROM temp_addreses a
),
cities AS (
    select name, row_number() OVER () AS rn FROM temp_cities
),
cnt AS (
    SELECT count(*) AS c FROM cities
)
UPDATE temp_addreses a
SET city = c.name
FROM addr ad
JOIN cnt ON true
JOIN cities c ON c.rn = ((ad.rn - 1) % cnt.c) + 1
WHERE a.adress = ad.adress;

DELETE FROM venue;
INSERT INTO venue (venue_name, city, address)
SELECT
    LEFT(v.venue,  100) AS venue_name,
    LEFT(a.city,    20) AS city,
    LEFT(a.adress, 100) AS address
FROM temp_venuess  v
JOIN temp_addreses a ON a.id = v.id;


 ------------------------------------ INSERT во табела HALL ------------------------------------ --
DROP TABLE IF EXISTS temp_hall_dist, temp_venue_numbered;

CREATE TEMP TABLE temp_hall_dist (
    r_from INT, r_to INT, n INT
);
INSERT INTO temp_hall_dist VALUES
    ( 0, 44, 1),   -- 45% imaat 1 hall
    (45, 74, 2),   -- 30% imaat 2 halls
    (75, 89, 3),   -- 15% imaat 3 halls
    (90, 97, 4),   -- 8% imaat 4 halls
    (98, 99, 5);   -- 2% imaat 5 halls  

CREATE TEMP TABLE temp_venue_numbered AS
select id AS venue_id, (row_number() OVER (ORDER BY id) - 1) AS rn
FROM venue;

INSERT INTO hall (hall_name, capacity, venueid)
select 'Hall ' || s AS hall_name,
    CASE s
        WHEN 1 THEN 30  + ((vn.rn * 7)  % 271)  -- max 300  
        WHEN 2 THEN 20  + ((vn.rn * 11) % 181)  -- max 200 
        WHEN 3 THEN 15  + ((vn.rn * 13) % 136)  -- max 150 
        WHEN 4 THEN 10  + ((vn.rn * 17) % 91)   -- max 100 
        ELSE        10  + ((vn.rn * 19) % 91)   -- max 100 
    END AS capacity,
    vn.venue_id AS venueid
FROM temp_venue_numbered vn
JOIN temp_hall_dist d ON (vn.rn % 100) BETWEEN d.r_from AND d.r_to
CROSS JOIN generate_series(1, d.n) AS s;


-- ------------------------------------ INSERT во табела SEAT ------------------------------------ --

-- Distribucija po tip:
-- PARTER (tip 3): prvi 20% sedishta po sala
-- STANDARD (tip 1): sredni 75% sedishta po sala
-- VIP (tip 2): poslednite 5% sedishta po sala
-- GENERAL_ADMISSION (tip 4): sali so kapacitet < 20
-- Napomena: Poradi golemiot broj na redovi, 
-- se izvrshuvashe vo 4 batches po hall id range
INSERT INTO SEAT (seat_number, HALLid, TICKET_TYPEid)
select s.n AS seat_number, h.id AS HALLid,
    CASE
        WHEN s.n > h.capacity * 0.95 THEN 2 -- VIP ~5%
        WHEN s.n <= h.capacity * 0.20 THEN 3 -- PARTER ~20%
        ELSE 1 -- STANDARD ~75%
    END AS TICKET_TYPEid
FROM hall h
CROSS JOIN LATERAL generate_series(1, h.capacity) AS s(n)
WHERE h.capacity >= 20
  
UNION ALL
 
INSERT INTO SEAT (seat_number, HALLid, TICKET_TYPEid)
SELECT 1, h.id, 4
FROM hall h
WHERE h.capacity < 20;

 ------------------------------------ INSERT во табела PROMO_CODE ------------------------------------ --

INSERT INTO PROMO_CODE (code, discount_percent, expiration_date, APP_USERid)
select 'PROMO-' || gs.rn || '-' || upper(substr(md5(random()::TEXT), 1, 8)) AS code,

    -- Realni popusti: 5, 10, 15, 20, 25, 30, 50
    (ARRAY[5, 10, 15, 20, 25, 30, 50])[(random() * 6)::INT + 1] AS discount_percent,

    -- 33% istecheni, 33% aktivni, 33% idni
    CASE
        WHEN gs.rn % 3 = 0 THEN CURRENT_DATE - (random() * 365)::INT
        WHEN gs.rn % 3 = 1 THEN CURRENT_DATE + (random() * 365)::INT
        ELSE CURRENT_DATE + (random() * 730)::INT
    END AS expiration_date,

    -- 20% imaat sopstvenik (user 2-50001), 80% se sistemski (default 1)
    CASE
        WHEN random() < 0.20 THEN (2 + (random() * 49999)::INT)
        ELSE 1
    END AS APP_USERid

FROM generate_series(1, 10000) AS gs(rn);

 ------------------------------------ INSERT во табела ORDER ------------------------------------ --

INSERT INTO USER_ORDER (order_date, total_amount, APP_USERid, STATUSid, PROMO_CODEid)
WITH
    user_ids AS (SELECT id FROM APP_USER),
    status_ids AS (SELECT id, status_name FROM STATUS),
    promo_ids AS (SELECT id FROM PROMO_CODE),

    user_arr AS (SELECT array_agg(id) AS arr FROM user_ids),
    status_paid AS (SELECT id FROM STATUS WHERE status_name = 'PAID'),
    status_created AS (SELECT id FROM STATUS WHERE status_name = 'CREATED'),
    status_cancelled AS (SELECT id FROM STATUS WHERE status_name = 'CANCELLED'),
    promo_arr AS (SELECT array_agg(id) AS arr FROM promo_ids)

SELECT
    -- Datumi: 70% minato, 20% idnina, 10% sega
    CASE
        WHEN (gs.rn % 10) < 7 THEN CURRENT_DATE - (random() * 1095)::INT
        WHEN (gs.rn % 10) < 9 THEN CURRENT_DATE + (random() * 180)::INT
        ELSE CURRENT_DATE - (random() * 7)::INT
    END AS order_date,

    -- Realen total_amount: 1-4 tiketi * realna cena
    (1 + (random() * 3)::INT) *
    (ARRAY[500,800,1000,1200,1500,2000,2500,3000])[(1 + (random() * 7)::INT)] AS total_amount,

    -- APP_USERid od tabelata
    (user_arr.arr)[(1 + (random() * (array_length(user_arr.arr, 1) - 1))::INT)] AS APP_USERid,

    -- Status so realna raspredlba
    CASE
        WHEN random() < 0.60 THEN (SELECT id FROM status_paid)
        WHEN random() < 0.85 THEN (SELECT id FROM status_created)
        ELSE (SELECT id FROM status_cancelled)
    END AS STATUSid,

    -- 15% so promo code, 85% NULL
    CASE
        WHEN random() < 0.15 AND array_length(promo_arr.arr, 1) > 0
        THEN (promo_arr.arr)[(1 + (random() * (array_length(promo_arr.arr, 1) - 1))::INT)]
        ELSE NULL
    END AS PROMO_CODEid

FROM generate_series(1, 5000000) AS gs(rn)
CROSS JOIN user_arr
CROSS JOIN promo_arr;

 ------------------------------------ INSERT во табела EVENT ------------------------------------ --

-- Podatocite za naslovi i opisi se vnesuvaat od CSV fajlovi
DROP TABLE IF EXISTS
    temp_event_base, temp_event_suffix, temp_event_prefix,
    temp_event_desc, temp_event_titles;

CREATE TABLE temp_event_base (id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE temp_event_suffix (id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE temp_event_prefix (id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE temp_event_desc (id BIGSERIAL PRIMARY KEY, name TEXT);

\copy temp_event_base(name)   FROM 'D:\Downloads\event_base.csv'   DELIMITER ',' CSV HEADER;
\copy temp_event_suffix(name) FROM 'D:\Downloads\event_suffix.csv' DELIMITER ',' CSV HEADER;
\copy temp_event_prefix(name) FROM 'D:\Downloads\event_prefix.csv' DELIMITER ',' CSV HEADER;
\copy temp_event_desc(name)   FROM 'D:\Downloads\event_desc.csv'   DELIMITER ',' CSV HEADER;

-- Kreiranje naslov
-- Opcija 1: base + suffix            "Rock Night 2025"
-- Opcija 2: prefix + base + suffix   "Annual Rock Night 2025"
-- Vkupno: 556 * 95 * 2 = 105 640 unique titles

drop table if exists temp_event_titles;
CREATE TEMP TABLE temp_event_titles (id INT, title TEXT, cat_hint TEXT);

INSERT INTO temp_event_titles (id, title, cat_hint)
SELECT
    row_number() OVER () AS id,
    title,
    -- Kategorija na nastanot da se bira spored kluchni zborovi vo naslovot
    CASE
        WHEN title ~* 'concert|music|jazz|rock|blues|soul|pop|band|singer|guitar|piano|violin|orchestra|symphon|recital|unplugged|acoustic|choir|opera gala'
            THEN 'Concerts'
        WHEN title ~* 'theatre|theater|drama|comedy show|musical|improv|stand.up|monologue|shakespeare|puppet|cabaret|burlesque|pantomime|circus|sketch'
            THEN 'Theatre'
        WHEN title ~* 'film|cinema|movie|screening|documentary|director|cineplex'
            THEN 'Cinema'
        WHEN title ~* 'festival|fest|fair|market|carnival|expo(?!sition)'
            THEN 'Festivals'
        WHEN title ~* 'exhibition|exhibit|gallery|sculpture|painting|installation|photo.*show|art show|retrospective|solo.*show|group.*show'
            THEN 'Exhibitions'
        WHEN title ~* 'seminar|workshop|conference|summit|forum|congress|symposium|bootcamp|masterclass|training|webinar|hackathon'
            THEN 'Seminars'
        ELSE NULL
    END AS cat_hint
FROM (
    -- Prva opcija samo so osnova i sufiks
    SELECT DISTINCT b.name || ' ' || s.name AS title
    FROM temp_event_base b
    CROSS JOIN temp_event_suffix s

    UNION

    -- Vtorata opcija prefiks pa osnova pa sufiks
    SELECT DISTINCT p.name || ' ' || b.name || ' ' || s.name AS title
    FROM temp_event_prefix p
    CROSS JOIN temp_event_base b
    CROSS JOIN temp_event_suffix s
) unique_titles
LIMIT 100000;

-- Stavanje na categorizationid na nastanot cat_hint
-- 1=Concerts, 2=Theatre, 3=Cinema, 4=Festivals, 5=Exhibitions, 6=Seminars

-- Insert vo EVENT 
DELETE FROM event;
INSERT INTO event (title, description, start_date, end_date, categorizationid)
SELECT
    LEFT(t.title, 50) AS title,

    -- ~60% imaat opis a ~40% im e NULL
    CASE
        WHEN (t.id % 10) < 6
        THEN (SELECT name 
                  FROM temp_event_desc
              WHERE temp_event_desc.id = (t.id % (SELECT max(id) FROM temp_event_desc)) + 1)
        ELSE NULL
    END 
    AS description,

    -- start_date: ~55% stari (pochnati od 2022-01-01 do denes), ~45% idni (pochnati denes do 2027-12-31)
    CASE
        WHEN (t.id % 20) < 11
        THEN DATE '2022-01-01' + ((t.id * 7 + 13) % 1186)
        ELSE DATE '2025-05-01' + ((t.id * 11 + 7) % 974)
    END                                                  
    AS start_date,

        -- end_date
    CASE
        -- Izlozhbi ~ 3–21 dena
        WHEN t.cat_hint = 'Exhibitions'
        THEN (CASE WHEN (t.id % 20) < 11
                   THEN DATE '2022-01-01' + ((t.id * 7 + 13) % 1186)
                   ELSE DATE '2025-05-01' + ((t.id * 11 + 7) % 974)
              END) + ((t.id % 19) + 3)

        -- Seminari ~ 1–5 dena
        WHEN t.cat_hint = 'Seminars'
        THEN (CASE WHEN (t.id % 20) < 11
                   THEN DATE '2022-01-01' + ((t.id * 7 + 13) % 1186)
                   ELSE DATE '2025-05-01' + ((t.id * 11 + 7) % 974)
              END) + ((t.id % 5))

        -- Festivali ~ 1–4 dena
        WHEN t.cat_hint = 'Festivals'
        THEN (CASE WHEN (t.id % 20) < 11
                   THEN DATE '2022-01-01' + ((t.id * 7 + 13) % 1186)
                   ELSE DATE '2025-05-01' + ((t.id * 11 + 7) % 974)
              END) + ((t.id % 4))

        -- Se drugo e tie shto traat eden den
        ELSE (CASE WHEN (t.id % 20) < 11
                   THEN DATE '2022-01-01' + ((t.id * 7 + 13) % 1186)
                   ELSE DATE '2025-05-01' + ((t.id * 11 + 7) % 974)
              END)
    END                                                  
    AS end_date,

    -- categorizationid
    CASE t.cat_hint
        WHEN 'Concerts'    THEN 1
        WHEN 'Theatre'     THEN 2
        WHEN 'Cinema'      THEN 3
        WHEN 'Festivals'   THEN 4
        WHEN 'Exhibitions' THEN 5
        WHEN 'Seminars'    THEN 6
        ELSE (t.id % 6) + 1
    END                                                  
    AS categorizationid

FROM temp_event_titles t;

-- subcategory dodavanje
UPDATE event e
SET subcategoryid = (
    SELECT s.id
    FROM subcategory s
    WHERE s.categorizationid = e.categorizationid
    ORDER BY random()
    LIMIT 1
)
WHERE e.categorizationid != 1;  -- Uncategorized nema subcategory




-- ------------------------------------ INSERT во табела REVIEW ------------------------------------ --
-- Rating distribucija: 5=35% 4=30% 3=20% 2=10% 1=5%
-- Comment: 65% imaat komentar, 35% NULL
-- app_userid: 85% imaat korisnik, 15% anonimni
-- Napomena: Poradi golemiot broj na redovi, se izvrshuvaa vo batches po event_id range (25,000 nastani po batch)

DROP TABLE IF EXISTS temp_ev, temp_usr, temp_comments;

CREATE TEMP TABLE temp_ev AS
SELECT id AS event_id, (row_number() OVER (ORDER BY id) - 1) AS rn
FROM event;

CREATE TEMP TABLE temp_usr AS
SELECT id AS user_id, (row_number() OVER (ORDER BY id) - 1) AS rn
FROM app_user;

CREATE INDEX ON temp_ev(rn);
CREATE INDEX ON temp_usr(rn);

CREATE TEMP TABLE temp_comments (id SERIAL PRIMARY KEY, txt TEXT);

\copy temp_comments(txt) FROM 'D:\Downloads\comments.csv' CSV HEADER;

INSERT INTO review (rating, review_comment, app_userid, eventid)
SELECT
    CASE
        WHEN ((e.rn * 31 + s * 17 + 3) % 100) < 35 THEN 5
        WHEN ((e.rn * 31 + s * 17 + 3) % 100) < 65 THEN 4
        WHEN ((e.rn * 31 + s * 17 + 3) % 100) < 85 THEN 3
        WHEN ((e.rn * 31 + s * 17 + 3) % 100) < 95 THEN 2
        ELSE 1
    END AS rating,
    CASE
        WHEN ((e.rn * 13 + s * 7) % 100) < 65 THEN c.txt
        ELSE NULL
    END AS review_comment,
    CASE
        WHEN ((e.rn * 7 + s * 19) % 100) < 85 THEN u.user_id
        ELSE NULL
    END AS app_userid,
    e.event_id AS eventid
FROM temp_ev e
CROSS JOIN LATERAL generate_series(1,
    CASE
        WHEN (e.rn % 100) < 10 THEN 20  + (e.rn % 21)
        WHEN (e.rn % 100) < 60 THEN 60  + (e.rn % 61)
        WHEN (e.rn % 100) < 90 THEN 120 + (e.rn % 61)
        ELSE                        180 + (e.rn % 71)
    END
) AS s
JOIN temp_comments c
    ON c.id = ((e.rn * 23 + s * 11) % 110) + 1
LEFT JOIN temp_usr u
    ON u.rn = ((e.rn * 41 + s * 13) % (SELECT max(rn) + 1 FROM temp_usr));

DROP TABLE IF EXISTS temp_ev, temp_usr, temp_comments;

-- Proverka
SELECT count(*) AS total_reviews FROM review;
SELECT rating, count(*) AS cnt,
       round(count(*) * 100.0 / sum(count(*)) OVER (), 1) AS pct
FROM review GROUP BY rating ORDER BY rating DESC;


-- ------------------------------------ INSERT во табела ORDER ------------------------------------ --

------------------------------
INSERT INTO USER_ORDER (order_date, total_amount, APP_USERid, STATUSid, PROMO_CODEid)
WITH
    users      AS (SELECT array_agg(id) AS uids, count(*)::INT AS cnt
                   FROM APP_USER WHERE id != 1),
    status_ids AS (
        SELECT
            (SELECT id FROM STATUS WHERE status_name = 'PAID')      AS paid_id,
            (SELECT id FROM STATUS WHERE status_name = 'CREATED')   AS created_id,
            (SELECT id FROM STATUS WHERE status_name = 'CANCELLED') AS cancelled_id
    ),
    promo      AS (SELECT array_agg(id) AS pids, count(*)::INT AS pcnt
                   FROM PROMO_CODE)
SELECT
    CASE
        WHEN gs.rn % 10 < 7 THEN CURRENT_DATE - (random()*1095)::INT
        WHEN gs.rn % 10 < 9 THEN CURRENT_DATE + (random()*180)::INT
        ELSE                     CURRENT_DATE - (random()*7)::INT
    END AS order_date,

    (1 + (random()*3)::INT) *
    (ARRAY[500,800,1000,1200,1500,2000,2500,3000])[(random()*7)::INT + 1] AS total_amount,

    uids[(random()*(cnt-1))::INT + 1] AS APP_USERid,

    CASE
        WHEN random() < 0.60 THEN (SELECT paid_id FROM status_ids)
        WHEN random() < 0.85 THEN (SELECT created_id FROM status_ids)
        ELSE                      (SELECT cancelled_id FROM status_ids)
    END AS STATUSid,

    CASE
        WHEN random() < 0.15 THEN pids[(random()*(pcnt-1))::INT + 1]
        ELSE NULL
    END AS PROMO_CODEid

FROM generate_series(1, 5000000) AS gs(rn)
CROSS JOIN users
CROSS JOIN promo;

-- Verifikacija
SELECT count(*) AS total_orders FROM user_order;
SELECT s.status_name, count(*) AS cnt,
       round(count(*)*100.0/sum(count(*)) OVER(),1) AS pct
FROM user_order uo
JOIN status s ON s.id = uo.statusid
GROUP BY s.status_name ORDER BY s.status_name;


-- ------------------------------------ INSERT во табела WAITLIST ------------------------------------ --
INSERT INTO WAITLIST (created_at, status, EVENTid)
WITH
    events AS (SELECT array_agg(id) AS eids, count(*)::INT AS cnt FROM EVENT)
SELECT
    CURRENT_DATE - (random()*365)::INT AS created_at,
    CASE
        WHEN random() < 0.50 THEN 'PENDING'
        WHEN random() < 0.80 THEN 'CONFIRMED'
        ELSE 'CANCELLED'
    END AS status,
    eids[(random()*(cnt-1))::INT + 1] AS EVENTid
FROM generate_series(1, 10000) AS gs(rn)
CROSS JOIN events;

SELECT count(*) FROM waitlist;


-- ------------------------------------ INSERT во табела EVENT_HALL (M:N) ------------------------------------ --

INSERT INTO EVENT_HALL (EVENTid, HALLid, allowed_access)
WITH
    events AS (SELECT array_agg(id) AS eids, count(*)::INT AS ecnt FROM EVENT),
    halls  AS (SELECT array_agg(id) AS hids, count(*)::INT AS hcnt FROM HALL)
SELECT DISTINCT ON (e_id, h_id)
    e_id,
    h_id,
    random() < 0.70 AS allowed_access
FROM (
    SELECT
        eids[(random()*(ecnt-1))::INT + 1] AS e_id,
        hids[(random()*(hcnt-1))::INT + 1] AS h_id
    FROM generate_series(1, 220000) gs  -- 100k eventi * 2.2 prosechno
    CROSS JOIN events
    CROSS JOIN halls
) sub;

-- Proverka
SELECT count(*) AS total FROM event_hall;
SELECT avg(hall_count)::INT AS avg_hali_po_event
FROM (SELECT eventid, count(*) AS hall_count FROM event_hall GROUP BY eventid) x;

-- ------------------------------------ INSERT во табела EVENT_TICKET_TYPE (M:N) ------------------------------------ --

INSERT INTO EVENT_TICKET_TYPE (EVENTid, TICKET_TYPEid, price, quantity_available)
WITH
    events AS (SELECT array_agg(id) AS eids, count(*)::INT AS ecnt FROM EVENT)
SELECT DISTINCT ON (e_id, tt_id)
    e_id,
    tt_id,
    CASE tt_id
        WHEN 4 THEN (200  + (random() * 300)::INT)
        WHEN 1 THEN (500  + (random() * 1000)::INT)
        WHEN 3 THEN (1000 + (random() * 1500)::INT)
        WHEN 2 THEN (2000 + (random() * 3000)::INT)
    END AS price,
    (50 + (random() * 950)::INT) AS quantity_available
FROM (
    SELECT
        eids[(random()*(ecnt-1))::INT + 1] AS e_id,
        unnest(
            CASE
                WHEN random() < 0.30 THEN ARRAY[1,2,3,4]
                WHEN random() < 0.60 THEN ARRAY[1,3,4]
                WHEN random() < 0.80 THEN ARRAY[1,2,3]
                ELSE ARRAY[1,4]
            END
        ) AS tt_id
    FROM generate_series(1, 120000) gs
    CROSS JOIN events
) sub;

-- Verifikacija
SELECT count(*) AS total FROM event_ticket_type;
SELECT avg(tt_count)::INT AS avg_tipovi_po_event
FROM (SELECT eventid, count(*) AS tt_count FROM event_ticket_type GROUP BY eventid) x;

-- ------------------------------------ INSERT во табела EVENT_IMAGE ------------------------------------ --

-- https://picsum.photos/seed/12345/800/600

INSERT INTO EVENT_IMAGE (image_url, EVENTid)
SELECT 'https://picsum.photos/seed/' || e.id+9 || '/800/600', e.id
FROM event e;

-- ------------------------------------ INSERT во табела USER_CATEGORY_SUBSCRIPTION ------------------------------------ --

INSERT INTO USER_CATEGORY_SUBSCRIPTION (APP_USERid, CATEGORIZATIONid)
SELECT DISTINCT ON (u.id, c.id)
    u.id AS APP_USERid,
    c.id AS CATEGORIZATIONid
FROM (SELECT id FROM app_user WHERE id != 1 ORDER BY random() LIMIT 20000) u
CROSS JOIN (SELECT id FROM categorization WHERE id != 1) c
WHERE random() < 0.35;

SELECT count(*) FROM user_category_subscription;


-- ------------------------------- INSERT во табела USER_SUBCATEGORY_SUBSCRIPTION ------------------------------ --

INSERT INTO USER_SUBCATEGORY_SUBSCRIPTION (APP_USERid, SUBCATEGORYid)
SELECT DISTINCT ON (u.id, s.id)
    u.id AS APP_USERid,
    s.id AS SUBCATEGORYid
FROM (SELECT id FROM app_user WHERE id != 1 ORDER BY random() LIMIT 50000) u
CROSS JOIN (SELECT id FROM subcategory) s
WHERE random() < 0.10;

SELECT count(*) AS total FROM user_subcategory_subscription;


-- ------------------------------- INSERT во табела TICKET ------------------------------ --

-- Trgnati constraints za pobrzi inserti
ALTER TABLE ticket DROP CONSTRAINT ticket_code_key;
ALTER TABLE ticket DROP CONSTRAINT FK_ticket_ticket_type;
ALTER TABLE ticket DROP CONSTRAINT FK_ticket_user_order;
ALTER TABLE ticket DROP CONSTRAINT FK_ticket_user;
ALTER TABLE ticket DROP CONSTRAINT FK_ticket_event;
ALTER TABLE ticket DROP CONSTRAINT FK_ticket_hall;
ALTER TABLE ticket DROP CONSTRAINT FK_ticket_seat;


INSERT INTO TICKET (code, status, TICKET_TYPEid, USER_ORDERid, SEATid, APP_USERid, EVENTid, HALLid)
WITH
    valid_tickets AS (
        SELECT
            eh.eventid,
            eh.hallid,
            s.id AS seatid,
            s.ticket_typeid,
            ROW_NUMBER() OVER (PARTITION BY eh.eventid, eh.hallid ORDER BY s.id) AS rn,
            h.capacity
        FROM event_hall eh
        JOIN hall h ON h.id = eh.hallid
        JOIN seat s ON s.hallid = eh.hallid
    ),
    filtered AS (
        SELECT vt.*
        FROM valid_tickets vt
        WHERE vt.rn <= vt.capacity
          AND EXISTS (
            SELECT 1 FROM event_ticket_type ett
            WHERE ett.eventid = vt.eventid
              AND ett.ticket_typeid = vt.ticket_typeid
          )
        LIMIT 10000000
    ),
    orders AS (
        SELECT id, app_userid,
               ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM user_order
    ),
    total_orders AS (SELECT count(*)::INT AS cnt FROM user_order)
SELECT
    'TKT-' || row_number() OVER () AS code,
    CASE WHEN random() < 0.70 THEN 'ACTIVE'
         WHEN random() < 0.90 THEN 'USED'
         ELSE 'CANCELLED'
    END AS status,
    f.ticket_typeid,
    o.id           AS user_orderid,
    f.seatid,
    o.app_userid,
    f.eventid,
    f.hallid
FROM filtered f
JOIN orders o ON o.rn = ((f.rn - 1) % (SELECT cnt FROM total_orders)) + 1;

SELECT count(*) AS total_tickets FROM ticket;




-- proverki

-- 1. Vkupno po status
SELECT status, count(*) AS cnt,
       round(count(*)*100.0/sum(count(*)) OVER(),1) AS pct
FROM ticket GROUP BY status ORDER BY status;

-- 2. Dali ima ticket so hall koj ne e vo event_hall za toj event
SELECT count(*) AS hall_ne_e_vo_event
FROM ticket t
WHERE NOT EXISTS (
    SELECT 1 FROM event_hall eh
    WHERE eh.eventid = t.eventid
      AND eh.hallid = t.hallid
);

-- 3. Dali ima ticket so tip koj ne postoi vo event_ticket_type
SELECT count(*) AS tip_ne_e_vo_event
FROM ticket t
WHERE NOT EXISTS (
    SELECT 1 FROM event_ticket_type ett
    WHERE ett.eventid = t.eventid
      AND ett.ticket_typeid = t.ticket_typeid
);


-- 4. Dali ima duplikati seat+event
SELECT count(*) AS duplikati_seat_event
FROM (
    SELECT seatid, eventid
    FROM ticket
    WHERE seatid IS NOT NULL
    GROUP BY seatid, eventid
    HAVING count(*) > 1
) x;


-- 5. Dali app_userid e konzistenten so user_orderid
SELECT count(*) AS pogreshen_user
FROM ticket t
JOIN user_order uo ON uo.id = t.user_orderid
WHERE uo.app_userid != t.app_userid;

-- 6. Dali ima GA tiketi so seat
SELECT count(*) AS ga_so_seat
FROM ticket
WHERE ticket_typeid = 4
  AND seatid IS NOT NULL;

-- Finalna statistika
SELECT
    count(*)                                        AS vkupno_tiketi,
    count(*) FILTER (WHERE seatid IS NOT NULL)      AS so_seat,
    count(*) FILTER (WHERE seatid IS NULL)          AS bez_seat,
    count(*) FILTER (WHERE status = 'ACTIVE')       AS active,
    count(*) FILTER (WHERE status = 'USED')         AS used,
    count(*) FILTER (WHERE status = 'CANCELLED')    AS cancelled
FROM ticket;

-- Vrateni constraints
ALTER TABLE ticket ADD CONSTRAINT ticket_code_key UNIQUE (code);
ALTER TABLE ticket ADD CONSTRAINT FK_ticket_ticket_type
    FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE(id);
ALTER TABLE ticket ADD CONSTRAINT FK_ticket_user_order
    FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER(id);
ALTER TABLE ticket ADD CONSTRAINT FK_ticket_user
    FOREIGN KEY (APP_USERid) REFERENCES APP_USER(id);
ALTER TABLE ticket ADD CONSTRAINT FK_ticket_event
    FOREIGN KEY (EVENTid) REFERENCES EVENT(id);
ALTER TABLE ticket ADD CONSTRAINT FK_ticket_hall
    FOREIGN KEY (HALLid) REFERENCES HALL(id);
ALTER TABLE ticket ADD CONSTRAINT FK_ticket_seat
    FOREIGN KEY (SEATid) REFERENCES SEAT(id);

-- ------------------------------------ INSERT во табела PAYMENT ------------------------------------ --

INSERT INTO PAYMENT (amount, status, payment_date, USER_ORDERid, PAYMENT_METHODid)
SELECT
    uo.total_amount AS amount,

    CASE s.status_name
        WHEN 'PAID'      THEN 'COMPLETED'
        WHEN 'CANCELLED' THEN (CASE WHEN random() < 0.5 THEN 'FAILED' ELSE 'COMPLETED' END)
        WHEN 'CREATED'   THEN (CASE WHEN random() < 0.7 THEN 'PENDING' ELSE 'COMPLETED' END)
    END AS status,

    uo.order_date + (random() * 2)::INT AS payment_date,

    uo.id AS user_orderid,

    CASE WHEN random() < 0.30 THEN 1 ELSE 2 END AS payment_methodid

FROM user_order uo
JOIN status s ON s.id = uo.statusid;

SELECT count(*) AS total_payments FROM payment;
SELECT status, count(*) AS cnt,
       round(count(*)*100.0/sum(count(*)) OVER(),1) AS pct
FROM payment GROUP BY status ORDER BY status;


-- ------------------------------------ INSERT во табела REFUND ------------------------------------ --

INSERT INTO REFUND (amount, reason, status, PAYMENTid)
SELECT
    CASE
        WHEN random() < 0.60 THEN p.amount
        ELSE 0
    END AS amount,

    (ARRAY[
        'Event cancelled by organizer',
        'Duplicate payment',
        'Customer request',
        'Technical error during purchase',
        'Event rescheduled',
        'Venue capacity issue',
        'Medical emergency',
        'Unable to attend'
    ])[(random()*7)::INT + 1] AS reason,

    CASE
        WHEN random() < 0.60 THEN 'APPROVED'
        WHEN random() < 0.85 THEN 'REQUESTED'
        ELSE                      'REJECTED'
    END AS status,

    p.id AS PAYMENTid

FROM (SELECT id, amount FROM payment WHERE status = 'COMPLETED' LIMIT 50000) p;

SELECT count(*) AS total_refunds FROM refund;
SELECT status, count(*) AS cnt, round(count(*)*100.0/sum(count(*)) OVER(),1) AS pct
FROM refund GROUP BY status ORDER BY status;

-- ------------------------------------ INSERT во табела NOTIFICATION ------------------------------------ --
INSERT INTO NOTIFICATION (message, created_at, APP_USERid)
SELECT
    (ARRAY[
        'Your ticket has been confirmed.',
        'New event available in your subscribed category.',
        'Your order has been successfully paid.',
        'Reminder: Your event starts tomorrow.',
        'Your refund has been approved.',
        'A new promo code is available for you.',
        'Event you liked has new available seats.',
        'Your review has been published.',
        'Order cancellation confirmed.',
        'New events added near your location.',
        'Your waitlist request has been confirmed.',
        'Payment failed. Please try again.',
        'New event added in your city.',
        'Your ticket has been cancelled.',
        'Upcoming event reminder: starts in 3 days.'
    ])[(random()*14)::INT + 1] AS message,

    CURRENT_DATE - (random()*730)::INT AS created_at,

    (SELECT id FROM app_user WHERE id != 1 
     ORDER BY random() LIMIT 1) AS APP_USERid

FROM generate_series(1, 1000000) gs;


-- ------------------------------------ INSERT во табела USER_EVENT ------------------------------------ --
INSERT INTO USER_EVENT (APP_USERid, EVENTid, EVENT_ROLEid)
SELECT DISTINCT ON (u.id, e.id)
    u.id AS APP_USERid,
    e.id AS EVENTid,
    CASE
        WHEN random() < 0.10 THEN 1  -- EVENT_ADMIN
        WHEN random() < 0.30 THEN 2  -- SALES_MANAGER
        WHEN random() < 0.60 THEN 3  -- INFO_ADMIN
        ELSE 4  -- CONTENT_MANAGER
    END AS EVENT_ROLEid
FROM (SELECT id FROM app_user WHERE id != 1 ORDER BY random() LIMIT 5000) u
CROSS JOIN (SELECT id FROM event ORDER BY random() LIMIT 5000) e
WHERE random() < 0.015;

SELECT count(*) AS total FROM user_event;
SELECT er.role_name, count(*) AS cnt,
       round(count(*)*100.0/sum(count(*)) OVER(),1) AS pct
FROM user_event ue
JOIN event_role er ON er.id = ue.event_roleid
GROUP BY er.role_name
ORDER BY er.role_name;

-- ------------------------------- INSERT во табела USER_WAITLIST ------------------------------ --

TRUNCATE TABLE user_waitlist;

INSERT INTO USER_WAITLIST (APP_USERid, WAITLISTid)
SELECT
    u.id AS APP_USERid,
    w.id AS WAITLISTid
FROM waitlist w
JOIN (
    SELECT id, row_number() OVER (ORDER BY random()) AS rn
    FROM app_user WHERE id != 1
) u ON u.rn = (w.id % 50000) + 1;

SELECT count(*) FROM user_waitlist;

-- ------------------------------- INSERT во табела REFUND ------------------------------ --

INSERT INTO REFUND (amount, reason, status, PAYMENTid)
SELECT
    CASE
        WHEN random() < 0.60 THEN p.amount
        ELSE 0
    END AS amount,

    (ARRAY[
        'Event cancelled by organizer',
        'Duplicate payment',
        'Customer request',
        'Technical error during purchase',
        'Event rescheduled',
        'Venue capacity issue',
        'Medical emergency',
        'Unable to attend'
    ])[(random()*7)::INT + 1] AS reason,

    CASE
        WHEN random() < 0.60 THEN 'APPROVED'
        WHEN random() < 0.85 THEN 'REQUESTED'
        ELSE                      'REJECTED'
    END AS status,

    p.id AS PAYMENTid

FROM (SELECT id, amount FROM payment WHERE status = 'COMPLETED' LIMIT 50000) p;

SELECT count(*) AS total_refunds FROM refund;
SELECT status, count(*) AS cnt,
       round(count(*)*100.0/sum(count(*)) OVER(),1) AS pct
FROM refund GROUP BY status ORDER BY status;