OtherTopics: perf_test_data.sql

-- =============================================================
--  CityFix - perf_test_data.sql  (фаза P9)
--  Посебна шема perf со голем број синтетички податоци, само за
--  анализа на перформансите. Шемата project не се менува.
--  Табелите имаат иста структура, примарни клучеви и единствени
--  ограничувања како во project (без тригери и надворешни клучеви,
--  за побрзо генерирање).
-- =============================================================
SET client_min_messages TO warning;
DROP SCHEMA IF EXISTS perf CASCADE;
CREATE SCHEMA perf;
SET search_path TO perf, project;

CREATE TABLE admins      (LIKE project.admins      INCLUDING ALL);
CREATE TABLE workers     (LIKE project.workers     INCLUDING ALL);
CREATE TABLE citizens    (LIKE project.citizens    INCLUDING ALL);
CREATE TABLE categories  (LIKE project.categories  INCLUDING ALL);
CREATE TABLE reports     (LIKE project.reports     INCLUDING ALL);
CREATE TABLE photos      (LIKE project.photos      INCLUDING ALL);
CREATE TABLE status_logs (LIKE project.status_logs INCLUDING ALL);
CREATE TABLE comments    (LIKE project.comments    INCLUDING ALL);
CREATE TABLE assignments (LIKE project.assignments INCLUDING ALL);

SELECT setseed(0.2026);

INSERT INTO admins     SELECT * FROM project.admins;
INSERT INTO categories SELECT * FROM project.categories;

INSERT INTO workers (worker_id, full_name, email, password)
SELECT g, 'Работник ' || g, 'worker' || g || '@skopje.gov.mk', md5(g::text)
FROM generate_series(1, 40) g;

INSERT INTO citizens (citizen_id, full_name, email, phone, password)
SELECT g, 'Граѓанин ' || g, 'citizen' || g || '@example.mk',
       CASE WHEN g % 3 = 0 THEN NULL ELSE '+3897' || lpad(g::text, 7, '0') END, md5(g::text)
FROM generate_series(1, 5000) g;

-- 100 000 пријави од 2021 до 2026; околу 4 % се сè уште отворени
INSERT INTO reports (report_id, description, location_text, latitude, longitude,
                     status, priority, created_at, citizen_id, category_id)
SELECT g,
       'Синтетичка пријава ' || g,
       CASE WHEN random() < 0.8 THEN 'Адреса ' || (g % 900) END,
       round((41.970 + random() * 0.050)::numeric, 6),
       round((21.350 + random() * 0.150)::numeric, 6),
       CASE WHEN x < 0.010 THEN 'submitted'
            WHEN x < 0.025 THEN 'received'
            WHEN x < 0.040 THEN 'in_progress'
            WHEN x < 0.110 THEN 'rejected'
            ELSE 'resolved' END,
       (ARRAY['low', 'medium', 'high', 'urgent'])[1 + floor(random() * 4)::int],
       TIMESTAMP '2021-01-01' + random() * (TIMESTAMP '2026-09-01' - TIMESTAMP '2021-01-01'),
       1 + floor(random() * 5000)::int,
       1 + floor(random() * 6)::int
FROM (SELECT g, random() AS x FROM generate_series(1, 100000) g) s;

-- доделување за секоја пријава која не е само поднесена
INSERT INTO assignments (assignment_id, assigned_at, note, admin_id, report_id, worker_id)
SELECT r.report_id, r.created_at + interval '2 hours', NULL,
       1 + (r.report_id % 2), r.report_id, 1 + ((r.category_id * 7 + r.report_id) % 40)
FROM reports r
WHERE r.status <> 'submitted';

-- историја на статуси според конечниот статус
INSERT INTO status_logs (status, changed_at, note, report_id, worker_id)
SELECT s.status, r.created_at + s.offs, NULL, r.report_id,
       CASE WHEN s.status = 'submitted' THEN NULL ELSE a.worker_id END
FROM reports r
LEFT JOIN assignments a ON a.report_id = r.report_id
CROSS JOIN LATERAL (VALUES
    ('submitted',   interval '0',        0),
    ('received',    interval '3 hours',  1),
    ('in_progress', interval '1 day',    2),
    ('resolved',    interval '1 day' * (2 + r.report_id % 9), 3),
    ('rejected',    interval '5 hours',  9)) AS s(status, offs, step)
WHERE s.step = 0
   OR (r.status = 'received'    AND s.step <= 1)
   OR (r.status = 'in_progress' AND s.step <= 2)
   OR (r.status = 'resolved'    AND s.step <= 3)
   OR (r.status = 'rejected'    AND (s.step <= 1 OR s.step = 9));

INSERT INTO comments (content, created_at, report_id, worker_id)
SELECT 'Коментар за напредокот', a.assigned_at + interval '6 hours', a.report_id, a.worker_id
FROM assignments a
WHERE a.report_id % 2 = 0;

INSERT INTO photos (image_url, report_id)
SELECT '/uploads/reports/' || r.report_id || '/photo_' || n || '.jpg', r.report_id
FROM reports r
CROSS JOIN generate_series(1, 2) n
WHERE n <= r.report_id % 3;

SELECT setval(pg_get_serial_sequence('perf.status_logs', 'log_id'), (SELECT MAX(log_id) FROM perf.status_logs));
SELECT setval(pg_get_serial_sequence('perf.comments', 'comment_id'), (SELECT MAX(comment_id) FROM perf.comments));
SELECT setval(pg_get_serial_sequence('perf.photos', 'photo_id'), (SELECT MAX(photo_id) FROM perf.photos));

ANALYZE admins, workers, citizens, categories, reports, photos, status_logs, comments, assignments;