Changes between Initial Version and Version 1 of DmlScript

Timestamp:

05/04/26 11:43:06 (3 weeks ago)

Author:

231141

Comment:

--

DmlScript

@@ +1 @@
+This DML script simulates the real behavior of the application over time, rather than inserting all data at once. The key idea is that several parts of the system are interdependent, especially offer pricing, badges, and reviews.
+
+The price of each offer is not purely random—it depends on the worker’s badge in the corresponding category. The badge itself is calculated based on the worker’s average ratings derived from reviews accumulated over time. In other words, there is a clear dependency chain:
+reviews → badges → offer price
+
+Because of this dependency, the data cannot be generated in a single step. Instead, the script simulates the application timeline, where data is created in a realistic order:
+
+!TaskRequests are created first
+then Offers are generated
+one offer per task is accepted and Tasks are created
+Tasks are completed
+Reviews are added
+and finally, badges are recalculated
+
+This process runs in monthly batches (e.g., February → March), allowing reviews to accumulate gradually and workers to progress naturally through badge levels.
+
+For simplicity and performance reasons, badge recalculation is not triggered after every change (such as each new review). Instead, it is performed at the end of each month, which significantly reduces computational overhead while still preserving a realistic system behavior.
+{{{#!sql
+--++++++++++ helpers ++++++++++
+
+CREATE TABLE female_names (
+    name VARCHAR(100)
+);
+CREATE TABLE male_names (
+    name VARCHAR(100)
+);
+
+CREATE TABLE male_surnames (
+    male_surname VARCHAR(100)
+);
+
+CREATE TABLE female_surnames (
+    female_surname VARCHAR(100)
+);
+CREATE TABLE cities (
+    city VARCHAR(25),
+    latitude DECIMAL,
+    longitude DECIMAL
+);
+--+++++++++++ location ++++++++++
+INSERT INTO cities (city, latitude, longitude) VALUES
+('Skopje', 41.9981, 21.4254),
+('Bitola', 41.0314, 21.3347),
+('Kumanovo', 42.1322, 21.7144),
+('Prilep', 41.3451, 21.5550),
+('Tetovo', 42.0097, 20.9716),
+('Veles', 41.7156, 21.7756),
+('Stip', 41.7458, 22.1958),
+('Ohrid', 41.1231, 20.8016),
+('Gostivar', 41.8000, 20.9167),
+('Strumica', 41.4378, 22.6427),
+('Kavadarci', 41.4331, 22.0119),
+('Kocani', 41.9164, 22.4128),
+('Kicevo', 41.5136, 20.9586),
+('Struga', 41.1770, 20.6770),
+('Radovis', 41.6383, 22.4647),
+('Gevgelija', 41.1417, 22.5014),
+('Debar', 41.5244, 20.5242),
+('Kriva Palanka', 42.2009, 22.3317),
+('Sveti Nikole', 41.8696, 21.9527),
+('Negotino', 41.4839, 22.0892),
+('Resen', 41.0889, 21.0122),
+('Delcevo', 41.9672, 22.7694),
+('Vinica', 41.8828, 22.5092),
+('Berovo', 41.7031, 22.8578),
+('Kratovo', 42.0784, 22.1800),
+('Krusevo', 41.3689, 21.2489),
+('Bogdanci', 41.2031, 22.5756),
+('Demir Hisar', 41.2214, 21.2031),
+('Demir Kapija', 41.4053, 22.2467),
+('Pehcevo', 41.7633, 22.8892),
+('Makedonska Kamenica', 42.0208, 22.5876),
+('Probistip', 41.9985, 22.1786),
+('Valandovo', 41.3174, 22.5619)
+ON CONFLICT DO NOTHING;
+
+
+INSERT INTO Location (city, latitude, longitude)
+SELECT
+    'Skopje',
+
+    ROUND((41.97 + random() * 0.06)::numeric, 6),
+    ROUND((21.38 + random() * 0.08)::numeric, 6)
+
+FROM generate_series(1, 4000)
+ON CONFLICT DO NOTHING;
+
+INSERT INTO Location (city, latitude, longitude)
+SELECT
+    c.city,
+
+    ROUND((c.latitude - 0.03 + random() * 0.06)::numeric, 6),
+    ROUND((c.longitude - 0.05 + random() * 0.10)::numeric, 6)
+
+FROM cities c
+CROSS JOIN generate_series(1, 200)
+WHERE c.city <> 'Skopje'
+ON CONFLICT DO NOTHING;
+
+
+-- ++++++++++ userAccount ++++++++++
+
+CREATE OR REPLACE FUNCTION clean_text(input TEXT)
+RETURNS TEXT AS $$
+BEGIN
+    RETURN TRANSLATE(input,
+        'абвгдѓежзѕијклљмнњопрстќуфхцчџшАБВГДЃЕЖЗЅИЈКЛЉМНЊОПРСТЌУФХЦЧЏШ',  -- Cyrillic
+        'abvgdgezzsijkljmnjoprstkufhccdsABVGDGEZZSIJKLJMJNJOPRSTKUFHCCDS'   -- Latin
+    );
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+WITH offset_cte AS (
+    SELECT COUNT(*) AS offset_val FROM UserAccount
+),
+all_data AS (
+    SELECT
+        name,
+        surname,
+        ROW_NUMBER() OVER () AS rn
+    FROM (
+        -- машки
+        SELECT clean_text(TRIM(m.name)) AS name,
+               clean_text(TRIM(s.surname)) AS surname
+        FROM male_names m
+        CROSS JOIN surnames s
+
+        UNION ALL
+
+        -- женски
+        SELECT clean_text(TRIM(f.name)) AS name, clean_text(TRIM(s.female_surname)) AS surname
+        FROM female_names f
+        CROSS JOIN female_surnames s
+    ) t
+    ORDER BY random()
+    LIMIT 350000
+)
+
+INSERT INTO UserAccount (name, surname, email, phone_number, created_at)
+SELECT
+    a.name,
+    a.surname,
+
+     LOWER(REGEXP_REPLACE(a.name, '[^a-zA-Z0-9]', '', 'g') || '.' ||
+        REGEXP_REPLACE(a.surname, '[^a-zA-Z0-9]', '', 'g') ||
+        (a.rn + o.offset_val) ||
+        '@gmail.com'),
+
+     '+3897'
+         || (ARRAY['1','2','5','7','8'])[((a.rn + o.offset_val - 1) % 5) + 1]
+         || LPAD((a.rn + o.offset_val)::text, 6, '0'),
+
+    TIMESTAMP '2017-01-01' + (random() * (NOW() - '2017-01-01'))
+
+FROM all_data AS a
+CROSS JOIN offset_cte AS o;
+
+drop table female_names;
+drop table female_surnames;
+drop table male_names;
+drop table surnames;
+
+--++++++++++ client ++++++++++
+INSERT INTO Client (user_id)
+SELECT id
+FROM UserAccount
+WHERE id NOT IN (SELECT user_id FROM Client)
+ORDER BY random()
+LIMIT 100000;
+
+--++++++++++ worker ++++++++++
+WITH random_workers AS (
+    SELECT id, created_at, ROW_NUMBER() OVER (ORDER BY random()) AS rn
+    FROM UserAccount
+    WHERE id NOT IN (SELECT user_id FROM Client)
+    LIMIT 250000
+),
+random_locations AS (
+    SELECT id, ROW_NUMBER() OVER (ORDER BY random()) AS rn
+    FROM Location
+),
+location_count AS (
+    SELECT COUNT(*) AS total FROM Location
+)
+INSERT INTO Worker (user_id, work_mode, service_radius_km, location_id, created_at)
+SELECT
+    w.id,
+    (ARRAY['HYBRID','ONSITE','REMOTE'])[floor(random() * 3 + 1)::int],
+    15,
+    l.id,
+    w.created_at
+FROM random_workers w
+CROSS JOIN location_count lc
+JOIN random_locations l ON ((w.rn - 1) % lc.total + 1) = l.rn;
+
+--++++++++++ favourited ++++++++++
+
+WITH worker_count AS (
+    SELECT COUNT(*) AS cnt FROM Worker
+),
+client_sample AS (
+    SELECT id, row_number() OVER () AS rn
+    FROM Client
+    ORDER BY random()
+    LIMIT 15000
+),
+worker_sample AS (
+    SELECT id, row_number() OVER () AS rn
+    FROM Worker
+    ORDER BY random()
+    LIMIT 15000
+)
+INSERT INTO Favourite (worker_id, client_id)
+SELECT
+    w.id AS worker_id,
+    c.id AS client_id
+FROM client_sample c
+JOIN worker_sample w
+    ON w.rn = ((c.rn * 1000003) % 15000) + 1  -- prime number shuffle
+ON CONFLICT DO NOTHING;
+
+--++++++++++ category ++++++++++
+
+CREATE TABLE category_staging (
+    category_name        VARCHAR(100),
+    description          VARCHAR(500),
+    parent_category_name VARCHAR(100)
+);
+
+INSERT INTO Category (category_name, description, parent_category_id)
+SELECT category_name, description, NULL
+FROM category_staging
+WHERE parent_category_name IS NULL OR parent_category_name = ''
+ON CONFLICT (category_name) DO NOTHING;
+
+INSERT INTO Category (category_name, description, parent_category_id)
+SELECT
+    s.category_name,
+    s.description,
+    p.id
+FROM category_staging s
+JOIN Category p ON p.category_name = s.parent_category_name
+WHERE s.parent_category_name IS NOT NULL AND s.parent_category_name != ''
+ON CONFLICT (category_name) DO NOTHING;
+
+DROP TABLE category_staging;
+
+--++++++++++ worker_category ++++++++++
+--dodeluvame samo child categories
+INSERT INTO WorkerCategory (worker_id, category_id)
+SELECT worker_id, category_id
+FROM (
+    SELECT
+        w.id AS worker_id,
+        c.id AS category_id,
+        ROW_NUMBER() OVER (
+            PARTITION BY w.id
+            ORDER BY random()
+        ) AS rn
+    FROM Worker w
+    JOIN Category c
+        ON c.parent_category_id IS NOT NULL
+) t
+WHERE rn <= 3
+ON CONFLICT DO NOTHING;
+
+
+-- ++++++++++ badges ++++++++++
+
+WITH tier_definitions AS (
+    SELECT * FROM (VALUES
+        (1, 'Beginner',   'Entry-level worker with basic experience in this category',              0.00, 0.20),
+        (2, 'Apprentice', 'Developing worker familiar with standard tasks in this category',        0.20, 0.40),
+        (3, 'Skilled',    'Competent worker with consistent positive ratings in this category',     0.40, 0.60),
+        (4, 'Advanced',   'Experienced worker handling complex tasks in this category',             0.60, 0.80),
+        (5, 'Expert',     'Top-rated professional trusted for high-demand work in this category',   0.80, 1.00)
+    ) AS t(tier_level, tier_name, tier_description, min_factor, max_factor)
+),
+category_price_ranges AS (
+    SELECT * FROM (VALUES
+        ('Home Cleaning',             5,   150),
+        ('Plumbing',                  20,  300),
+        ('Electrical',                25,  400),
+        ('General Handyman',          10,  250),
+        ('Painting',                  15,  350),
+        ('Outdoor & Lawn',            10,  280),
+        ('Moving & Hauling',          15,  250),
+        ('Tech & Smart Home',         20,  350),
+        ('Organization & Decluttering',10, 220),
+        ('Errands & Delivery',         5,  150),
+        ('Pet Care',                  10,  200),
+        ('Childcare & Tutoring',      10,  200),
+        ('Cooking & Meal Prep',       10,  300),
+        ('Vehicle & Auto',            10,  250),
+        ('Administrative & Business', 10,  220),
+        ('Remodeling & Renovation',   25,  600),
+        ('Event & Party Setup',       10,  300),
+        ('Personal Assistance',       10,  220)
+    ) AS p(category_name, base_min, base_max)
+)
+INSERT INTO Badge (badge_name, description, category_id, min_price, max_price, tier_level)
+SELECT
+    c.category_name || ' - ' || t.tier_name                          AS badge_name,
+    t.tier_description || ' (Category: ' || c.category_name || ')'  AS description,
+    c.id                                                             AS category_id,
+    FLOOR(cpr.base_min + (cpr.base_max - cpr.base_min) * t.min_factor)::INT AS min_price,
+    FLOOR(cpr.base_min + (cpr.base_max - cpr.base_min) * t.max_factor)::INT AS max_price,
+    t.tier_level
+FROM Category c
+JOIN category_price_ranges cpr ON cpr.category_name = c.category_name
+CROSS JOIN tier_definitions t
+WHERE c.parent_category_id IS NULL  -- only parent categories get badges
+ORDER BY c.id, t.tier_level;
+
+--++++++++++ worker_badge ++++++++++
+-- not valid yet, needs review for tasks to calculate properly
+--initially everyone gets begginer badge in their categories
+INSERT INTO WorkerBadge (worker_id, badge_id, date_acquired,is_active)
+SELECT DISTINCT
+    wc.worker_id,
+    b.id,
+    w.created_at,
+    true
+FROM WorkerCategory wc
+JOIN Worker w ON w.id = wc.worker_id
+JOIN Category child ON child.id = wc.category_id
+JOIN Badge b
+    ON b.category_id = child.parent_category_id
+   AND b.tier_level = 1
+WHERE child.parent_category_id IS NOT NULL
+
+ON CONFLICT (worker_id, badge_id) DO UPDATE
+SET is_active = TRUE,
+    date_acquired = EXCLUDED.date_acquired;
+    
+
+--++++++++++ task requests ++++++++++
+--5milioni taskrequests
+DO $$
+DECLARE
+    v_min_id        BIGINT;
+    v_max_id        BIGINT;
+    v_batch_size    INT  := 5000;   -- clients per iteration
+    v_rows_per_cli  INT  := 50;     -- task rows per client
+    v_offset        BIGINT := 0;
+    v_batch_min     BIGINT;
+    v_batch_max     BIGINT;
+    v_rows_inserted BIGINT := 0;
+BEGIN
+    SELECT MIN(id), MAX(id)
+      INTO v_min_id, v_max_id
+      FROM Client;
+
+    LOOP
+        v_batch_min := v_min_id + v_offset;
+        v_batch_max := v_min_id + v_offset + v_batch_size - 1;
+
+        EXIT WHEN v_batch_min > v_max_id;
+
+        INSERT INTO TaskRequest
+            (client_id, description, work_mode, status,
+             category_id, location_id, created_at)
+        SELECT
+            c.id,
+            td.task_description,
+            (ARRAY['HYBRID','ONSITE','REMOTE'])[floor(random()*3+1)::int],
+            'OPEN',
+            cat.id,
+            l.id,
+            u.created_at + INTERVAL '1 minute'
+                + (random() * (TIMESTAMP '2026-01-01' - u.created_at - INTERVAL '1 minute'))
+        FROM Client c
+        JOIN UserAccount u  ON c.user_id = u.id
+        -- only clients in this slice
+        JOIN generate_series(1, v_rows_per_cli) gs(n) ON TRUE
+        JOIN LATERAL (
+            SELECT task_description, category_name
+              FROM temp_task_desc
+             ORDER BY RANDOM()* gs.n
+             LIMIT 1
+        ) td ON TRUE
+        JOIN Category cat ON cat.category_name = td.category_name
+        JOIN LATERAL (
+                SELECT id
+                FROM Location
+                ORDER BY RANDOM()* gs.n
+                LIMIT 1
+                ) l ON true
+        WHERE c.id BETWEEN v_batch_min AND v_batch_max;
+
+        GET DIAGNOSTICS v_rows_inserted = ROW_COUNT;
+        RAISE NOTICE 'Batch offset=% | clients %-% | rows inserted=%',
+            v_offset, v_batch_min, v_batch_max, v_rows_inserted;
+
+        v_offset := v_offset + v_batch_size;
+
+        COMMIT;
+    END LOOP;
+
+    RAISE NOTICE 'Done. All batches complete.';
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- ================================================================
+--helper procedura za badges
+
+CREATE OR REPLACE PROCEDURE recalc_worker_badges(p_as_of TIMESTAMP, p_from TIMESTAMP)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+    -- Само работници со нови reviews во овој batch
+    DROP TABLE IF EXISTS tmp_affected_workers;
+    CREATE TEMP TABLE tmp_affected_workers AS
+        SELECT DISTINCT o.worker_id
+        FROM  Review r
+        JOIN  Task  t ON t.id = r.task_id
+        JOIN  Offer o ON o.id = t.offer_id
+        WHERE r.created_at >  p_from
+          AND r.created_at <= p_as_of;
+
+    CREATE INDEX ON tmp_affected_workers (worker_id);
+
+    IF NOT EXISTS (SELECT 1 FROM tmp_affected_workers) THEN
+        RETURN;
+    END IF;
+
+    -- ОПТ: GROUP BY оди директно — ист резултат, нема промена во логиката.
+    -- Не скенираме повеќе Review отколку што треба бидејќи
+    -- tmp_affected_workers е мал сет.
+    DROP TABLE IF EXISTS tmp_avg_ratings;
+    CREATE TEMP TABLE tmp_avg_ratings AS
+        SELECT
+            o.worker_id,
+            parent.id     AS parent_category_id,
+            AVG(r.rating) AS avg_rating,
+            COUNT(*)      AS review_count
+        FROM  Review r
+        JOIN  Task        t      ON t.id      = r.task_id
+        JOIN  Offer       o      ON o.id      = t.offer_id
+        JOIN  TaskRequest tr     ON tr.id     = o.task_request_id
+        JOIN  Category    child  ON child.id  = tr.category_id
+        JOIN  Category    parent ON parent.id = child.parent_category_id
+        WHERE o.worker_id IN (SELECT worker_id FROM tmp_affected_workers)
+          AND r.created_at <= p_as_of
+        GROUP BY o.worker_id, parent.id;
+
+    CREATE INDEX ON tmp_avg_ratings (worker_id, parent_category_id);
+
+    DROP TABLE IF EXISTS tmp_new_badges;
+    CREATE TEMP TABLE tmp_new_badges AS
+        SELECT DISTINCT ON (ar.worker_id, ar.parent_category_id)
+            ar.worker_id,
+            ar.parent_category_id,
+            b.id AS badge_id
+        FROM  tmp_avg_ratings ar
+        JOIN  Badge b ON b.category_id = ar.parent_category_id
+        ORDER BY
+            ar.worker_id,
+            ar.parent_category_id,
+            ABS(b.tier_level - ROUND((ar.avg_rating - 1) / 4.0 * 4 + 1));
+
+    CREATE INDEX ON tmp_new_badges (worker_id);
+    CREATE INDEX ON tmp_new_badges (badge_id);
+
+    -- Деактивирај стари badges
+    UPDATE WorkerBadge wb
+    SET    is_active = FALSE
+    WHERE  wb.is_active = TRUE
+      AND  wb.worker_id IN (SELECT worker_id FROM tmp_affected_workers)
+      AND  NOT EXISTS (
+          SELECT 1 FROM tmp_new_badges nb
+          WHERE nb.worker_id = wb.worker_id
+            AND nb.badge_id  = wb.badge_id
+      );
+
+    -- Вметни/ажурирај точни badges
+    INSERT INTO WorkerBadge (worker_id, badge_id, date_acquired, is_active)
+    SELECT worker_id, badge_id, p_as_of::DATE, TRUE
+    FROM   tmp_new_badges
+    ON CONFLICT (worker_id, badge_id) DO UPDATE
+        SET is_active     = TRUE,
+            date_acquired = EXCLUDED.date_acquired;
+
+END;
+$$;
+
+
+-------------------------------------------------------------------
+
+DO $$
+DECLARE
+    v_batch_start   TIMESTAMP;
+    v_batch_end     TIMESTAMP;
+    v_origin        TIMESTAMP := TIMESTAMP '2023-02-01';
+    v_finish        TIMESTAMP := TIMESTAMP '2023-03-01'; -- прошири колку сакаш, најполовно на 3-4 месеци,
+    v_cnt           BIGINT;
+    v_tables_ready  BOOLEAN   := FALSE;
+BEGIN
+    v_batch_start := v_origin;
+
+    LOOP
+        v_batch_end := v_batch_start + INTERVAL '1 month';
+        EXIT WHEN v_batch_start >= v_finish;
+
+        RAISE NOTICE '=== BATCH % → % ===', v_batch_start, v_batch_end;
+
+        -- ----------------------------------------------------------------
+        -- ОПТ 1: Создај temp табели САМО еднаш, потоа TRUNCATE
+        -- Избегнува каталошки lock + index rebuild на секоја итерација
+        -- ----------------------------------------------------------------
+        IF NOT v_tables_ready THEN
+            CREATE TEMP TABLE tmp_worker_sample (
+                category_id  INT,
+                worker_id    INT,
+                created_at   TIMESTAMP
+            ) ON COMMIT PRESERVE ROWS;
+            CREATE INDEX ON tmp_worker_sample (category_id, worker_id);
+
+            CREATE TEMP TABLE tmp_offers_to_make (
+                task_request_id INT,
+                worker_id       INT,
+                worker_created  TIMESTAMP,
+                tr_created      TIMESTAMP,
+                client_created  TIMESTAMP,
+                initiated_by    TEXT,
+                price           INT
+            ) ON COMMIT PRESERVE ROWS;
+            CREATE INDEX ON tmp_offers_to_make (task_request_id);
+
+            CREATE TEMP TABLE tmp_batch_offers (
+                id              INT,
+                task_request_id INT,
+                created_at      TIMESTAMP
+            ) ON COMMIT PRESERVE ROWS;
+            CREATE INDEX ON tmp_batch_offers (task_request_id);
+            CREATE INDEX ON tmp_batch_offers (id);
+
+            CREATE TEMP TABLE tmp_batch_tasks (
+                task_id         INT,
+                updated_at      TIMESTAMP,
+                worker_id       INT,
+                worker_user_id  INT,
+                client_user_id  INT
+            ) ON COMMIT PRESERVE ROWS;
+            CREATE INDEX ON tmp_batch_tasks (task_id);
+
+            v_tables_ready := TRUE;
+        ELSE
+            TRUNCATE tmp_worker_sample;
+            TRUNCATE tmp_offers_to_make;
+            TRUNCATE tmp_batch_offers;
+            TRUNCATE tmp_batch_tasks;
+        END IF;
+
+        -- ----------------------------------------------------------------
+        -- ОПТ 2: Земи max 20 работници по категорија ЕДНАШ
+        -- Истата логика, но RANDOM() се вика еднаш, не за секој TaskRequest
+        -- ----------------------------------------------------------------
+        INSERT INTO tmp_worker_sample (category_id, worker_id, created_at)
+        WITH numbered AS (
+            SELECT
+                wc.category_id,
+                w.id AS worker_id,
+                w.created_at,
+                ROW_NUMBER() OVER (
+                    PARTITION BY wc.category_id
+                    ORDER BY RANDOM()
+                ) AS rn
+            FROM Worker w
+            JOIN WorkerCategory wc ON wc.worker_id = w.id
+            WHERE w.created_at < v_batch_end
+        )
+        SELECT category_id, worker_id, created_at
+        FROM numbered
+        WHERE rn <= 20;
+
+        -- ----------------------------------------------------------------
+        -- ОПТ 3: Генерирај парови (TaskRequest, Worker) ЕДНАШ во temp табела
+        -- Наместо да се прави lateral join двапати (worker + client offers)
+        -- со ORDER BY RANDOM() на секој ред — ова е главниот bottleneck.
+        -- Истиот број на offers (2–5 по TaskRequest), иста рандомност.
+        -- ----------------------------------------------------------------
+
+        INSERT INTO tmp_offers_to_make
+            (task_request_id, worker_id, worker_created, tr_created, client_created, initiated_by, price)
+        SELECT
+            tr.id,
+            w.worker_id,
+            w.created_at,
+            tr.created_at,
+            u.created_at,
+            'WORKER',
+            COALESCE(
+                FLOOR(RANDOM() * (b.max_price - b.min_price + 1) + b.min_price),
+                FLOOR(RANDOM() * (300 - 100 + 1) + 100)
+            )::INT
+        FROM TaskRequest tr
+        JOIN Client       c      ON c.id      = tr.client_id
+        JOIN UserAccount  u      ON u.id      = c.user_id
+        JOIN LATERAL (
+
+            -- ОПТ: tmp_worker_sample е веќе случаен → само земи 2–5
+            SELECT bw.worker_id, bw.created_at
+            FROM   tmp_worker_sample bw
+            WHERE  bw.category_id = tr.category_id
+            LIMIT  (FLOOR(RANDOM()*4)+2)   -- нема ORDER BY RANDOM() овде!
+        ) w ON TRUE
+        JOIN Category child   ON child.id   = tr.category_id
+        JOIN Category parent  ON parent.id  = child.parent_category_id
+        LEFT JOIN WorkerBadge wb
+            ON  wb.worker_id  = w.worker_id
+            AND wb.is_active  = TRUE
+        LEFT JOIN Badge b
+            ON  b.id          = wb.badge_id
+            AND b.category_id = parent.id
+        WHERE tr.status     = 'OPEN'
+          AND tr.created_at >= v_batch_start
+          AND tr.created_at <  v_batch_end;
+
+        -- ------------------------------------------------
+        -- 1. WORKER-INITIATED OFFERS
+        -- ------------------------------------------------
+        INSERT INTO Offer
+            (worker_id, task_request_id, price, offer_status, initiated_by, created_at)
+        SELECT
+            worker_id,
+            task_request_id,
+            price,
+            'PENDING',
+            'WORKER',
+            GREATEST(
+                tr_created     + INTERVAL '1 minute',
+                worker_created + INTERVAL '1 hour',
+                client_created + INTERVAL '1 minute',
+                LEAST(
+                    tr_created + (RANDOM() * INTERVAL '12 hours'),
+                    v_batch_end - INTERVAL '1 hour'
+                )
+            )
+        FROM tmp_offers_to_make;
+
+        GET DIAGNOSTICS v_cnt = ROW_COUNT;
+        RAISE NOTICE '  Worker offers inserted: %', v_cnt;
+
+        -- ------------------------------------------------
+        -- 2. CLIENT-INITIATED OFFERS (~50% од истите парови)
+        -- Ист ефект: RANDOM() < 0.5 филтер
+        -- ------------------------------------------------
+        INSERT INTO Offer
+            (worker_id, task_request_id, price, offer_status, initiated_by, created_at)
+        SELECT
+            worker_id,
+            task_request_id,
+            FLOOR(RANDOM() * (300 - 100 + 1) + 100)::INT,
+            'PENDING',
+            'CLIENT',
+            GREATEST(
+                tr_created     + INTERVAL '1 minute',
+                worker_created + INTERVAL '1 hour',
+                client_created + INTERVAL '1 minute',
+                LEAST(
+                    tr_created + (RANDOM() * INTERVAL '12 hours'),
+                    v_batch_end - INTERVAL '1 hour'
+                )
+            )
+        FROM tmp_offers_to_make
+        WHERE RANDOM() < 0.5;
+
+        GET DIAGNOSTICS v_cnt = ROW_COUNT;
+        RAISE NOTICE '  Client offers inserted: %', v_cnt;
+
+        -- ------------------------------------------------
+        -- 3. ACCEPT ЕДЕН OFFER PО TASK, REJECT ОСТАНАТИТЕ
+        -- ОПТ: Еден UPDATE со CASE наместо два одделни UPDATE
+        -- Ист резултат: 1 ACCEPTED, останатите REJECTED по TaskRequest
+        -- ------------------------------------------------
+        INSERT INTO tmp_batch_offers (id, task_request_id, created_at)
+        SELECT o.id, o.task_request_id, o.created_at
+        FROM Offer o
+        WHERE o.offer_status = 'PENDING'
+          AND o.created_at  >= v_batch_start
+          AND o.created_at  <  v_batch_end;
+
+        WITH last_offer AS (
+            SELECT task_request_id,
+                   MAX(created_at) AS last_offer_time
+            FROM   tmp_batch_offers
+            GROUP BY task_request_id
+        ),
+        ranked AS (
+            SELECT o.id,
+                   o.task_request_id,
+                   o.created_at      AS offer_created_at,
+                   lo.last_offer_time,
+                   ROW_NUMBER() OVER (
+                       PARTITION BY o.task_request_id
+                       ORDER BY RANDOM()
+                   ) AS rn
+            FROM  tmp_batch_offers o
+            JOIN  last_offer lo ON lo.task_request_id = o.task_request_id
+        )
+        -- ОПТ: Еден UPDATE со CASE → половина I/O наспроти два UPDATE
+        UPDATE Offer o
+        SET
+            offer_status = CASE WHEN r.rn = 1 THEN 'ACCEPTED' ELSE 'REJECTED' END,
+            updated_at   = CASE
+                WHEN r.rn = 1 THEN
+                    GREATEST(
+                        r.offer_created_at + INTERVAL '1 minute',
+                        LEAST(
+                            GREATEST(r.offer_created_at, r.last_offer_time)
+                                + (RANDOM() * INTERVAL '1 hour'),
+                            v_batch_end - INTERVAL '1 hour'
+                        )
+                    )
+                ELSE
+                    GREATEST(o.created_at,
+                        GREATEST(
+                            r.offer_created_at + INTERVAL '1 minute',
+                            LEAST(
+                                GREATEST(r.offer_created_at, r.last_offer_time)
+                                    + (RANDOM() * INTERVAL '1 hour'),
+                                v_batch_end - INTERVAL '1 hour'
+                            )
+                        )
+                    )
+                END
+        FROM ranked r
+        WHERE o.id = r.id;
+
+        -- ------------------------------------------------
+        -- 4. CLOSE ACCEPTED TASK REQUESTS
+        -- ------------------------------------------------
+        WITH accepted_tasks AS (
+            SELECT DISTINCT task_request_id, updated_at
+            FROM   Offer
+            WHERE  offer_status = 'ACCEPTED'
+              AND  created_at  >= v_batch_start
+              AND  created_at  <  v_batch_end
+        )
+        UPDATE TaskRequest tr
+        SET    status     = 'CLOSED',
+               updated_at = GREATEST(at2.updated_at, tr.created_at + INTERVAL '1 minute')
+        FROM   accepted_tasks at2
+        WHERE  tr.id     = at2.task_request_id
+          AND  tr.status = 'OPEN';
+
+        -- ------------------------------------------------
+        -- 5. CREATE TASKS
+        -- ------------------------------------------------
+        INSERT INTO Task (offer_id, status, created_at)
+        SELECT o.id,
+               'ACTIVE',
+               GREATEST(
+                   o.updated_at,
+                   o.created_at  + INTERVAL '1 minute',
+                   u.created_at  + INTERVAL '1 minute'
+               )
+        FROM   Offer o
+        JOIN   TaskRequest  tr ON tr.id = o.task_request_id
+        JOIN   Client        c ON c.id  = tr.client_id
+        JOIN   UserAccount   u ON u.id  = c.user_id
+        WHERE  o.offer_status = 'ACCEPTED'
+          AND  o.created_at  >= v_batch_start
+          AND  o.created_at  <  v_batch_end
+          AND  NOT EXISTS (
+              SELECT 1 FROM Task t WHERE t.offer_id = o.id
+          );
+
+        GET DIAGNOSTICS v_cnt = ROW_COUNT;
+        RAISE NOTICE '  Tasks created: %', v_cnt;
+
+        -- ------------------------------------------------
+        -- 6. COMPLETE TASKS
+        -- ------------------------------------------------
+        UPDATE Task t
+        SET    status     = 'COMPLETED',
+               updated_at = GREATEST(
+                   t.created_at + INTERVAL '1 hour',
+                   LEAST(
+                       t.created_at + (RANDOM() * INTERVAL '2 days'),
+                       v_batch_end  - INTERVAL '1 hour'
+                   )
+               )
+        WHERE  t.status     = 'ACTIVE'
+          AND  t.created_at >= v_batch_start
+          AND  t.created_at <  v_batch_end;
+
+        -- ------------------------------------------------
+        -- 7. REVIEWS
+        -- ------------------------------------------------
+        INSERT INTO tmp_batch_tasks
+            (task_id, updated_at, worker_id, worker_user_id, client_user_id)
+        SELECT
+            t.id,
+            t.updated_at,
+            o.worker_id,
+            w.user_id,
+            c.user_id
+        FROM  Task        t
+        JOIN  Offer       o  ON o.id  = t.offer_id
+        JOIN  Worker      w  ON w.id  = o.worker_id
+        JOIN  TaskRequest tr ON tr.id = o.task_request_id
+        JOIN  Client      c  ON c.id  = tr.client_id
+        WHERE t.status     = 'COMPLETED'
+          AND t.created_at >= v_batch_start
+          AND t.created_at <  v_batch_end;
+
+        -- Client → Worker
+        INSERT INTO Review
+            (task_id, reviewer_id, reviewed_id, rating, created_at)
+        SELECT
+            bt.task_id,
+            bt.client_user_id,
+            bt.worker_user_id,
+            LEAST(5, GREATEST(1,
+                ROUND((random()+random()+random()+random())/4.0*4+1)::INT
+            )),
+            GREATEST(
+                bt.updated_at + INTERVAL '1 minute',
+                LEAST(
+                    bt.updated_at + (RANDOM() * INTERVAL '12 hours'),
+                    v_batch_end   - INTERVAL '1 hour'
+                )
+            )
+        FROM  tmp_batch_tasks bt
+        LEFT JOIN Review r ON r.task_id    = bt.task_id
+                          AND r.reviewer_id = bt.client_user_id
+        WHERE r.id IS NULL;
+
+        GET DIAGNOSTICS v_cnt = ROW_COUNT;
+        RAISE NOTICE '  Reviews client->worker: %', v_cnt;
+
+        -- Worker → Client
+        INSERT INTO Review
+            (task_id, reviewer_id, reviewed_id, rating, created_at)
+        SELECT
+            bt.task_id,
+            bt.worker_user_id,
+            bt.client_user_id,
+            LEAST(5, GREATEST(1,
+                ROUND((random()+random()+random())/3.0*4+1)::INT
+            )),
+            GREATEST(
+                bt.updated_at + INTERVAL '1 minute',
+                LEAST(
+                    bt.updated_at + (RANDOM() * INTERVAL '12 hours'),
+                    v_batch_end   - INTERVAL '1 hour'
+                )
+            )
+        FROM  tmp_batch_tasks bt
+        LEFT JOIN Review r ON r.task_id    = bt.task_id
+                          AND r.reviewer_id = bt.worker_user_id
+        WHERE r.id IS NULL;
+
+        GET DIAGNOSTICS v_cnt = ROW_COUNT;
+        RAISE NOTICE '  Reviews worker->client: %', v_cnt;
+
+        -- ------------------------------------------------
+        -- 8. BADGE RECALC
+        -- ------------------------------------------------
+        CALL recalc_worker_badges(v_batch_end, v_batch_start);
+        RAISE NOTICE '  Badges updated.';
+
+        COMMIT;
+        v_batch_start := v_batch_end;
+    END LOOP;
+
+    RAISE NOTICE 'All batches done.';
+END;
+$$ LANGUAGE plpgsql;
+
+   
+
+}}}