Changes between Version 6 and Version 7 of DmlScript

Timestamp:

05/20/26 09:57:40 (6 days ago)

Author:

231141

Comment:

--

DmlScript

@@ -1105 +1105 @@
 
 
+-- =========================================================
+-- HELPER TABLES
+-- =========================================================
+
+CREATE TABLE message_template_client (
+    category TEXT,
+    text TEXT
+);
+
+CREATE TABLE message_template_worker (
+    category TEXT,
+    text TEXT
+);
+
+--add category_id from Category
+ALTER TABLE message_template_client
+    ADD COLUMN category_id INT;
+
+UPDATE message_template_client mtc
+SET category_id = c.id
+FROM Category c
+WHERE c.category_name = mtc.category;
+
+
+ALTER TABLE message_template_worker
+    ADD COLUMN category_id INT;
+
+UPDATE message_template_worker mtc
+SET category_id = c.id
+FROM Category c
+WHERE c.category_name = mtc.category;
+
+
+
+
+
+-- =========================================================
+-- REQUIRED INDEXES
+-- =========================================================
+
+CREATE INDEX IF NOT EXISTS idx_task_created_at
+    ON Task(created_at);
+
+CREATE INDEX IF NOT EXISTS idx_template_worker_category_id
+    ON message_template_worker(category_id);
+
+CREATE INDEX IF NOT EXISTS idx_template_client_category_id
+    ON message_template_client(category_id);
+
+
+-- =========================================================
+-- GENERATE MESSAGES
+DO $$
+DECLARE
+    v_batch_start TIMESTAMP := TIMESTAMP '2017-01-01';
+    v_batch_end   TIMESTAMP;
+
+    v_finish      TIMESTAMP := TIMESTAMP '2026-04-30';
+
+    v_tables_ready BOOLEAN := FALSE;
+BEGIN
+
+    LOOP
+
+        v_batch_end := v_batch_start + INTERVAL '1 week';
+
+        EXIT WHEN v_batch_start >= v_finish;
+
+        RAISE NOTICE '=== BATCH % -> % ===',
+            v_batch_start,
+            v_batch_end;
+
+        -- =====================================================
+        -- 1. CREATE / RESET TEMP TABLES
+        -- =====================================================
+
+        IF NOT v_tables_ready THEN
+
+            CREATE TEMP TABLE tmp_batch_tasks (
+                task_id INT,
+                created_at TIMESTAMP,
+                updated_at TIMESTAMP,
+                worker_user_id INT,
+                client_user_id INT,
+                category_id INT
+            ) ON COMMIT PRESERVE ROWS;
+
+            CREATE INDEX idx_tmp_batch_task_id
+                ON tmp_batch_tasks(task_id);
+
+            CREATE TEMP TABLE tmp_messages_to_insert (
+                task_id INT,
+                sender_id INT,
+                created_at TIMESTAMP,
+                category_id INT
+            ) ON COMMIT PRESERVE ROWS;
+
+            CREATE INDEX idx_tmp_messages_task_id
+                ON tmp_messages_to_insert(task_id);
+
+            v_tables_ready := TRUE;
+
+        ELSE
+
+            TRUNCATE TABLE tmp_batch_tasks;
+            TRUNCATE TABLE tmp_messages_to_insert;
+
+        END IF;
+
+        -- =====================================================
+        -- 2. LOAD TASK DATA
+        -- =====================================================
+
+        INSERT INTO tmp_batch_tasks (
+            task_id,
+            created_at,
+            updated_at,
+            worker_user_id,
+            client_user_id,
+            category_id
+        )
+        SELECT
+            t.id,
+            t.created_at,
+            t.updated_at,
+            w.user_id,
+            c.user_id,
+            tr.category_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.created_at >= v_batch_start
+          AND t.created_at <  v_batch_end;
+
+        RAISE NOTICE 'Tasks loaded: %',
+            (SELECT COUNT(*) FROM tmp_batch_tasks);
+
+        -- =====================================================
+        -- 3. GENERATE MESSAGE METADATA
+        -- =====================================================
+
+        INSERT INTO tmp_messages_to_insert (
+            task_id,
+            sender_id,
+            created_at,
+            category_id
+        )
+        SELECT
+            bt.task_id,
+
+            CASE
+                WHEN random() < 0.5
+                    THEN bt.client_user_id
+                ELSE bt.worker_user_id
+            END AS sender_id,
+
+            bt.created_at
+            + (
+                random() *
+                (bt.updated_at - bt.created_at)
+            ) AS created_at,
+
+            bt.category_id
+
+        FROM tmp_batch_tasks bt
+
+        JOIN LATERAL generate_series(
+            1,
+            floor(random() * 4)::int + 3
+        ) gs ON true;
+
+        RAISE NOTICE 'Messages generated: %',
+            (SELECT COUNT(*) FROM tmp_messages_to_insert);
+
+        -- =====================================================
+        -- 4. INSERT INTO MESSAGE
+        -- =====================================================
+
+        INSERT INTO Message (
+            text,
+            created_at,
+            task_id,
+            sender_id
+        )
+        SELECT
+            COALESCE(
+                CASE
+                    WHEN tmi.sender_id = bt.worker_user_id
+                        THEN mtw.text
+                    ELSE mtc.text
+                END,
+                'Default message'
+            ) AS text,
+
+            tmi.created_at,
+
+            tmi.task_id,
+
+            tmi.sender_id
+
+        FROM tmp_messages_to_insert tmi
+
+        JOIN tmp_batch_tasks bt
+            ON bt.task_id = tmi.task_id
+
+        LEFT JOIN LATERAL (
+    SELECT text
+    FROM message_template_worker mtw
+    WHERE mtw.category_id = tmi.category_id
+    OFFSET floor(
+        random() * (
+            SELECT count(*)
+            FROM message_template_worker x
+            WHERE x.category_id = tmi.category_id
+        )
+    )
+    LIMIT 1
+) mtw
+            ON tmi.sender_id = bt.worker_user_id
+
+        LEFT JOIN LATERAL (
+    SELECT text
+    FROM message_template_client mtc
+    WHERE mtc.category_id = tmi.category_id
+    OFFSET floor(
+        random() * (
+            SELECT count(*)
+            FROM message_template_client x
+            WHERE x.category_id = tmi.category_id
+        )
+    )
+    LIMIT 1
+) mtc
+            ON tmi.sender_id = bt.client_user_id;
+
+        RAISE NOTICE 'Inserted batch % -> %',
+            v_batch_start,
+            v_batch_end;
+
+        v_batch_start := v_batch_end;
+
+    END LOOP;
+
+    RAISE NOTICE 'ALL BATCHES FINISHED';
+
+END;
+$$;
+
+
+-- =========================================================
+-- CLEANUP
+-- =========================================================
+
+DROP TABLE IF EXISTS tmp_messages_to_insert;
+DROP TABLE IF EXISTS tmp_batch_tasks;
+
+DROP INDEX IF EXISTS idx_task_created_at;
+DROP INDEX IF EXISTS idx_template_worker_category_id;
+DROP INDEX IF EXISTS idx_template_client_category_id; 
+
+
+
+-- =========================================================
+-- DROP THE HELPER TABLES
+-- =========================================================
+DROP TABLE IF EXISTS message_template_client ;
+DROP TABLE IF EXISTS message_template_worker;
+
+
+-- =========================================================
+-- DROP DUPLICATE MESSAGE PER TASK
+-- =========================================================
+DROP
+DELETE FROM Message m
+USING Message d
+WHERE m.id > d.id
+  AND m.task_id = d.task_id
+  AND m.text = d.text;
+
+
+
+
+
+
 }}}