| 859 | | |
| | 859 | |
| | 860 | --++++++++++ notification type +++++++++++ |
| | 861 | |
| | 862 | INSERT INTO NotificationType (name, description) |
| | 863 | VALUES |
| | 864 | ('New Task Created', 'Triggered when a new task is created and awaiting offers.'), |
| | 865 | ('Task Accepted', 'Triggered when a task is accepted by a worker.'), |
| | 866 | ('Task Started', 'Triggered when a worker begins a task.'), |
| | 867 | ('Task Completed', 'Triggered when a task is marked complete.'), |
| | 868 | ('Task Cancelled', 'Triggered when a task is cancelled.'), |
| | 869 | ('Offer Received', 'Triggered when a new offer is submitted on a task.'), |
| | 870 | ('Offer Accepted', 'Triggered when an offer is accepted by the client.'), |
| | 871 | ('Offer Rejected', 'Triggered when an offer is rejected.'), |
| | 872 | ('Offer Expired', 'Triggered when an offer expires without a response.'), |
| | 873 | ('Payment Pending', 'Triggered when a payment is being processed.'), |
| | 874 | ('Payment Successful', 'Triggered when a payment completes successfully.'), |
| | 875 | ('Payment Failed', 'Triggered when a payment attempt fails.'), |
| | 876 | ('Review Received', 'Triggered when a user receives a new review.'), |
| | 877 | ('Complaint Opened', 'Triggered when a complaint is filed.'), |
| | 878 | ('Complaint Resolved', 'Triggered when a complaint is resolved.'), |
| | 879 | ('Complaint Dismissed', 'Triggered when a complaint is dismissed.'), |
| | 880 | ('Message Received', 'Triggered when a new message is received.'), |
| | 881 | ('Badge Earned', 'Triggered when a worker earns a new badge.'), |
| | 882 | ('Profile Viewed', 'Triggered when a user profile is viewed.'), |
| | 883 | ('System Announcement', 'General system-wide announcements.'); |
| | 884 | |
| | 885 | |
| | 886 | |
| | 887 | |
| | 888 | |
| | 889 | DO $$ |
| | 890 | DECLARE |
| | 891 | batch_size INT := 100000; |
| | 892 | total_rows INT := 1000000; |
| | 893 | offset_val INT := 0; |
| | 894 | |
| | 895 | task_ids INT[]; |
| | 896 | client_ids INT[]; |
| | 897 | worker_ids INT[]; |
| | 898 | BEGIN |
| | 899 | -- Load matched task/client/worker combos from the actual join chain |
| | 900 | SELECT |
| | 901 | array_agg(t.id), |
| | 902 | array_agg(tr.client_id), |
| | 903 | array_agg(o.worker_id) |
| | 904 | INTO task_ids, client_ids, worker_ids |
| | 905 | FROM Task t |
| | 906 | JOIN Offer o ON o.id = t.offer_id |
| | 907 | JOIN TaskRequest tr ON tr.id = o.task_request_id; |
| | 908 | |
| | 909 | WHILE offset_val < total_rows LOOP |
| | 910 | INSERT INTO Complaint |
| | 911 | (reason, description, status, created_at, updated_at, |
| | 912 | task_id, client_id, worker_id) |
| | 913 | SELECT |
| | 914 | CASE |
| | 915 | WHEN is_worker_complaint THEN |
| | 916 | CASE floor(random() * 6)::INT |
| | 917 | WHEN 0 THEN 'Client: Payment not made as agreed' |
| | 918 | WHEN 1 THEN 'Client: Unsafe or hazardous work conditions' |
| | 919 | WHEN 2 THEN 'Client: Abusive or threatening behaviour' |
| | 920 | WHEN 3 THEN 'Client: Scope creep beyond agreed terms' |
| | 921 | WHEN 4 THEN 'Client: Access denied to work site' |
| | 922 | ELSE 'Client: False damage claim' |
| | 923 | END |
| | 924 | ELSE |
| | 925 | CASE floor(random() * 6)::INT |
| | 926 | WHEN 0 THEN 'Worker: Work not completed as agreed' |
| | 927 | WHEN 1 THEN 'Worker: Poor quality of service' |
| | 928 | WHEN 2 THEN 'Worker: No-show or late arrival' |
| | 929 | WHEN 3 THEN 'Worker: Unprofessional behaviour' |
| | 930 | WHEN 4 THEN 'Worker: Overcharged or billing dispute' |
| | 931 | ELSE 'Worker: Damaged property or belongings' |
| | 932 | END |
| | 933 | END AS reason, |
| | 934 | |
| | 935 | CASE |
| | 936 | WHEN is_worker_complaint THEN |
| | 937 | CASE floor(random() * 4)::INT |
| | 938 | WHEN 0 THEN 'The client did not fulfil the agreed payment terms.' |
| | 939 | WHEN 1 THEN 'The client created conditions that made work impossible.' |
| | 940 | WHEN 2 THEN 'The client behaved in an abusive or threatening manner.' |
| | 941 | ELSE NULL |
| | 942 | END |
| | 943 | ELSE |
| | 944 | CASE floor(random() * 4)::INT |
| | 945 | WHEN 0 THEN 'The worker did not follow the agreed specifications.' |
| | 946 | WHEN 1 THEN 'The quality of the delivered work was unsatisfactory.' |
| | 947 | WHEN 2 THEN 'The worker arrived significantly late without notice.' |
| | 948 | ELSE NULL |
| | 949 | END |
| | 950 | END AS description, |
| | 951 | |
| | 952 | CASE floor(random() * 3)::INT |
| | 953 | WHEN 0 THEN 'OPEN' |
| | 954 | WHEN 1 THEN 'RESOLVED' |
| | 955 | ELSE 'DISMISSED' |
| | 956 | END AS status, |
| | 957 | |
| | 958 | created_ts AS created_at, |
| | 959 | |
| | 960 | CASE WHEN floor(random() * 3)::INT > 0 |
| | 961 | THEN created_ts + (random() * INTERVAL '30 days') |
| | 962 | ELSE NULL |
| | 963 | END AS updated_at, |
| | 964 | |
| | 965 | task_ids [idx] AS task_id, |
| | 966 | client_ids[idx] AS client_id, |
| | 967 | worker_ids[idx] AS worker_id |
| | 968 | |
| | 969 | FROM ( |
| | 970 | SELECT |
| | 971 | i, |
| | 972 | '2017-01-15 19:44:09.798'::TIMESTAMP |
| | 973 | + random() * ('2026-04-29 23:45:45.249'::TIMESTAMP - '2017-01-15 19:44:09.798'::TIMESTAMP) AS created_ts, |
| | 974 | random() < 0.5 AS is_worker_complaint, |
| | 975 | 1 + floor(random() * array_length(task_ids, 1))::INT AS idx |
| | 976 | FROM generate_series(offset_val + 1, offset_val + batch_size) AS i |
| | 977 | ) sub; |
| | 978 | |
| | 979 | offset_val := offset_val + batch_size; |
| | 980 | RAISE NOTICE 'Inserted % / % rows', offset_val, total_rows; |
| | 981 | END LOOP; |
| | 982 | END; |
| | 983 | $$; |
| | 984 | |
| | 985 | |
| | 986 | --++++++++++ compalaints +++++++++++ |
| | 987 | CREATE TEMP TABLE complaints_ordered AS |
| | 988 | SELECT |
| | 989 | c.id AS cid, |
| | 990 | cl.user_id AS client_user_id, |
| | 991 | w.user_id AS worker_user_id, |
| | 992 | random() AS rnd |
| | 993 | FROM Complaint c |
| | 994 | JOIN Task t ON t.id = c.task_id |
| | 995 | JOIN Offer o ON o.id = t.offer_id |
| | 996 | JOIN TaskRequest tr ON tr.id = o.task_request_id |
| | 997 | JOIN Client cl ON cl.id = tr.client_id AND cl.id = c.client_id |
| | 998 | JOIN Worker w ON w.id = o.worker_id AND w.id = c.worker_id; |
| | 999 | |
| | 1000 | CREATE INDEX ON complaints_ordered (rnd); |
| | 1001 | |
| | 1002 | INSERT INTO ComplaintAttachment (file_url, description, complaint_id, user_id) |
| | 1003 | SELECT |
| | 1004 | 'https://storage.example.com/complaints/' |
| | 1005 | || cid || '/' |
| | 1006 | || md5(random()::TEXT) || '.' |
| | 1007 | || CASE floor(random() * 4)::INT |
| | 1008 | WHEN 0 THEN 'jpg' |
| | 1009 | WHEN 1 THEN 'png' |
| | 1010 | WHEN 2 THEN 'pdf' |
| | 1011 | ELSE 'mp4' |
| | 1012 | END AS file_url, |
| | 1013 | CASE floor(random() * 3)::INT |
| | 1014 | WHEN 0 THEN 'Photo evidence of the issue' |
| | 1015 | WHEN 1 THEN 'Screenshot of conversation' |
| | 1016 | ELSE NULL |
| | 1017 | END AS description, |
| | 1018 | cid AS complaint_id, |
| | 1019 | CASE WHEN random() < 0.5 THEN client_user_id ELSE worker_user_id END AS user_id |
| | 1020 | FROM complaints_ordered |
| | 1021 | ORDER BY rnd |
| | 1022 | LIMIT 2000000; -- Your total desired rows |
| | 1023 | |
| | 1024 | DROP TABLE IF EXISTS complaints_ordered; |
| | 1025 | |
| | 1026 | |
| | 1027 | --++++++++++ payments +++++++++++ |
| | 1028 | CREATE TEMP TABLE payments_base AS |
| | 1029 | SELECT |
| | 1030 | t.id AS task_id, |
| | 1031 | tr.client_id, |
| | 1032 | o.worker_id, |
| | 1033 | row_number() OVER (ORDER BY random()) AS rn |
| | 1034 | FROM Task t |
| | 1035 | JOIN Offer o ON o.id = t.offer_id |
| | 1036 | JOIN TaskRequest tr ON tr.id = o.task_request_id |
| | 1037 | CROSS JOIN generate_series(1, 10) AS multiplier |
| | 1038 | LIMIT 10000000; |
| | 1039 | |
| | 1040 | |
| | 1041 | truncate table Payment restart identity cascade; |
| | 1042 | |
| | 1043 | DO $$ |
| | 1044 | DECLARE |
| | 1045 | batch_size INT := 500000; |
| | 1046 | max_rn INT := (SELECT MAX(rn) FROM payments_base); |
| | 1047 | curr_rn INT := 1; |
| | 1048 | BEGIN |
| | 1049 | WHILE curr_rn <= max_rn LOOP |
| | 1050 | INSERT INTO Payment |
| | 1051 | (amount, payment_method, status, |
| | 1052 | task_id, client_id, worker_id, created_at, updated_at) |
| | 1053 | SELECT |
| | 1054 | (floor(random() * 99000) + 1000)::INT AS amount, |
| | 1055 | |
| | 1056 | CASE floor(random() * 3)::INT |
| | 1057 | WHEN 0 THEN 'CARD' |
| | 1058 | WHEN 1 THEN 'PAYPAL' |
| | 1059 | ELSE 'CASH' |
| | 1060 | END AS payment_method, |
| | 1061 | |
| | 1062 | CASE floor(random() * 10)::INT |
| | 1063 | WHEN 0 THEN 'FAILED' |
| | 1064 | WHEN 1 THEN 'PENDING' |
| | 1065 | ELSE 'PAID' |
| | 1066 | END AS status, |
| | 1067 | |
| | 1068 | pb.task_id, |
| | 1069 | pb.client_id, |
| | 1070 | pb.worker_id, |
| | 1071 | |
| | 1072 | created_ts AS created_at, |
| | 1073 | |
| | 1074 | CASE WHEN random() > 0.2 |
| | 1075 | THEN LEAST(created_ts + (random() * INTERVAL '30 days'), NOW()) |
| | 1076 | ELSE NULL |
| | 1077 | END AS updated_at |
| | 1078 | |
| | 1079 | FROM payments_base pb |
| | 1080 | CROSS JOIN LATERAL ( |
| | 1081 | SELECT '2017-01-15 19:44:09.798'::TIMESTAMP |
| | 1082 | + random() * ('2026-04-29 23:45:45.249'::TIMESTAMP - '2017-01-15 19:44:09.798'::TIMESTAMP) AS created_ts |
| | 1083 | ) ts |
| | 1084 | WHERE pb.rn BETWEEN curr_rn AND curr_rn + batch_size - 1; |
| | 1085 | |
| | 1086 | curr_rn := curr_rn + batch_size; |
| | 1087 | RAISE NOTICE 'Inserted up to row % / %', curr_rn - 1, max_rn; |
| | 1088 | END LOOP; |
| | 1089 | END; |
| | 1090 | $$; |
| | 1091 | |
| | 1092 | DROP TABLE payments_base; |
| | 1093 | |
