RelationalDesign: data_load.sql

File data_load.sql, 19.7 KB (added by 221544, 4 days ago)
Line 
1-- ===========================================================================
2-- Ecore - Project Management System
3-- PHASE P2 : Sample data load (DML)
4-- Target : PostgreSQL, schema "project" (run schema_creation.sql first)
5--
6-- Re-runnable: every table is TRUNCATEd (RESTART IDENTITY CASCADE) before load.
7-- Explicit IDs are used so cross-references stay readable; identity sequences
8-- are re-synced at the end so future app inserts don't collide.
9-- ===========================================================================
10
11SET search_path TO project;
12
13-- ---------------------------------------------------------------------------
14-- 0. Clean slate (order-independent thanks to CASCADE; RESTART IDENTITY resets seqs)
15-- ---------------------------------------------------------------------------
16TRUNCATE TABLE
17 project.notification_preferences,
18 project.invites,
19 project.notifications,
20 project.timesheet_entries,
21 project.timesheet_projects,
22 project.invoice_line_items,
23 project.payments,
24 project.invoices,
25 project.task_comments,
26 project.file_comments,
27 project.task_files,
28 project.task_workers,
29 project.milestones,
30 project.tasks,
31 project.domains,
32 project.projects,
33 project.users
34RESTART IDENTITY CASCADE;
35
36-- ---------------------------------------------------------------------------
37-- 1. users (1 admin, 3 workers, 3 clients) password hash = demo bcrypt hash
38-- ---------------------------------------------------------------------------
39INSERT INTO project.users (id, email, password, role, name, company, phone_number, skills, profile_status, created_at) VALUES
40(1, 'admin@ecore.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'ADMIN', 'Ana Petrovska', 'Ecore', '+389 70 111 222', ARRAY['management','sql']::TEXT[], 'COMPLETE', '2026-01-10 09:00:00'),
41(2, 'bojan@ecore.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'WORKER', 'Bojan Ilievski', 'Ecore', '+389 70 333 444', ARRAY['react','node.js','css']::TEXT[], 'COMPLETE', '2026-01-12 09:00:00'),
42(3, 'marija@ecore.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'WORKER', 'Marija Stojanova', 'Ecore', '+389 70 555 666', ARRAY['backend','postgresql','prisma']::TEXT[],'COMPLETE', '2026-01-12 09:30:00'),
43(4, 'goran@acme.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'CLIENT', 'Goran Trajkovski', 'ACME DOO', '+389 71 100 100', ARRAY[]::TEXT[], 'COMPLETE', '2026-02-01 10:00:00'),
44(5, 'elena@nova.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'CLIENT', 'Elena Nikolova', 'Nova Ltd', '+389 71 200 200', ARRAY[]::TEXT[], 'COMPLETE', '2026-02-03 10:00:00'),
45(6, 'stefan@bluewave.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'CLIENT', 'Stefan Dimitrov', 'BlueWave', '+389 71 300 300', ARRAY[]::TEXT[], 'INCOMPLETE', '2026-02-05 10:00:00'),
46(7, 'ivana@ecore.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'WORKER', 'Ivana Petrova', 'Ecore', '+389 70 777 888', ARRAY['smm','design','copywriting']::TEXT[], 'COMPLETE', '2026-01-15 09:00:00');
47
48-- ---------------------------------------------------------------------------
49-- 2. projects (linked to client users)
50-- ---------------------------------------------------------------------------
51INSERT INTO project.projects (id, name, description, client_id, status, service_type, metadata, start_date, end_date, created_at) VALUES
52(1, 'ACME Corporate Website', 'Marketing website for ACME DOO', 4, 'ACTIVE', 'WEBSITE', '{"pages": 8, "cms": "custom"}', '2026-02-10', NULL, '2026-02-10 11:00:00'),
53(2, 'Nova Mobile App', 'Cross-platform customer app for Nova Ltd', 5, 'ACTIVE', 'WEB_APP', '{"platforms": ["ios","android"]}', '2026-03-01', NULL, '2026-03-01 11:00:00'),
54(3, 'BlueWave SMM Campaign', 'Q2 social media management for BlueWave', 6, 'ACTIVE', 'SMM', '{"channels": ["instagram","facebook"], "monthlyAdSpend": 500}', '2026-04-01', NULL, '2026-04-01 11:00:00'),
55(4, 'ACME E-commerce Platform', 'Online shop with payment gateway for ACME', 4, 'COMPLETED', 'WEB_APP', '{"features": ["cart","checkout","stock"]}','2026-01-15', '2026-05-30', '2026-01-15 11:00:00');
56
57-- ---------------------------------------------------------------------------
58-- 3. domains (linked to clients; cascade on client delete)
59-- ---------------------------------------------------------------------------
60INSERT INTO project.domains (id, client_id, domain_name, is_primary, is_active, hosting_provider, hosting_plan, activation_date, expiration_date, hosting_expiry, lifespan_years, status, created_at) VALUES
61(1, 4, 'acme.mk', TRUE, TRUE, 'MKhost', 'Business', '2026-02-11', '2027-02-11', '2027-02-11', 1, 'ACTIVE', '2026-02-11 12:00:00'),
62(2, 4, 'shop.acme.mk', FALSE, TRUE, 'MKhost', 'Business', '2026-02-11', '2027-02-11', '2027-02-11', 1, 'ACTIVE', '2026-02-11 12:05:00'),
63(3, 5, 'nova-app.mk', TRUE, TRUE, 'Hetzner', 'Cloud', '2026-03-02', '2028-03-02', '2027-03-02', 2, 'ACTIVE', '2026-03-02 12:00:00'),
64(4, 6, 'bluewave.mk', TRUE, TRUE, 'MKhost', 'Starter', '2026-04-02', '2027-04-02', '2027-04-02', 1, 'ACTIVE', '2026-04-02 12:00:00'),
65(5, 6, 'blog.bluewave.mk', FALSE, FALSE,'MKhost', 'Starter', NULL, '2027-04-02', '2027-04-02', 1, 'PENDING', '2026-04-02 12:10:00');
66
67-- ---------------------------------------------------------------------------
68-- 4. tasks (linked to clients and projects)
69-- ---------------------------------------------------------------------------
70INSERT INTO project.tasks (id, title, description, status, due_date, client_id, project_id, created_at) VALUES
71(1, 'Design homepage mockup', 'High-fidelity mockup of the ACME homepage', 'DONE', '2026-02-20', 4, 1, '2026-02-11 09:00:00'),
72(2, 'Develop landing page', 'Implement responsive landing page from mockup', 'IN_PROGRESS', '2026-03-05', 4, 1, '2026-02-21 09:00:00'),
73(3, 'Set up hosting', 'Provision hosting and deploy staging build', 'PENDING', '2026-03-10', 4, 1, '2026-02-21 09:10:00'),
74(4, 'Build authentication module', 'JWT login/register for the Nova app', 'IN_PROGRESS', '2026-03-20', 5, 2, '2026-03-02 09:00:00'),
75(5, 'API integration', 'Integrate Nova backend REST API', 'PENDING', '2026-03-28', 5, 2, '2026-03-02 09:20:00'),
76(6, 'Create content calendar', 'Q2 posting schedule for BlueWave', 'DONE', '2026-04-05', 6, 3, '2026-04-02 09:00:00'),
77(7, 'Design ad creatives', 'Static + carousel creatives for campaign', 'IN_PROGRESS', '2026-04-15', 6, 3, '2026-04-03 09:00:00'),
78(8, 'Product catalog schema', 'Model products, variants and stock', 'PENDING', '2026-02-10', 4, 4, '2026-01-16 09:00:00'),
79(9, 'Payment gateway integration', 'Integrate CPay/Stripe checkout', 'DONE', '2026-04-20', 4, 4, '2026-01-20 09:00:00'),
80(10, 'SEO optimization', 'On-page SEO for BlueWave blog', 'PENDING', '2026-05-01', 6, 3, '2026-04-10 09:00:00');
81
82-- ---------------------------------------------------------------------------
83-- 5. milestones (under tasks 1, 2, 4)
84-- ---------------------------------------------------------------------------
85INSERT INTO project.milestones (id, task_id, title, description, is_done, done_at, done_by, created_by_id, deployed, pushed_to_github, "order", created_at) VALUES
86(1, 1, 'Wireframe approved', 'Low-fidelity wireframe signed off by client', TRUE, '2026-02-14 15:00:00', 4, 2, FALSE, FALSE, 0, '2026-02-11 10:00:00'),
87(2, 1, 'Visual design done', 'Final visual design delivered', TRUE, '2026-02-19 16:00:00', 1, 2, FALSE, FALSE, 1, '2026-02-14 10:00:00'),
88(3, 2, 'HTML/CSS structure', 'Static markup complete', TRUE, '2026-02-28 12:00:00', 2, 2, TRUE, TRUE, 0, '2026-02-21 10:00:00'),
89(4, 2, 'Responsive styling', 'Mobile + tablet breakpoints', FALSE, NULL, NULL, 2, FALSE, FALSE, 1, '2026-02-28 10:00:00'),
90(5, 4, 'DB schema for auth', 'Users + sessions tables', TRUE, '2026-03-10 11:00:00', 3, 3, FALSE, TRUE, 0, '2026-03-02 10:00:00');
91
92-- ---------------------------------------------------------------------------
93-- 6. task_workers (assign workers 2,3,7 to tasks; M:N)
94-- ---------------------------------------------------------------------------
95INSERT INTO project.task_workers (id, task_id, user_id) VALUES
96(1, 1, 2),
97(2, 2, 2),
98(3, 2, 7),
99(4, 4, 3),
100(5, 5, 3),
101(6, 6, 7),
102(7, 7, 2),
103(8, 9, 3);
104
105-- ---------------------------------------------------------------------------
106-- 7. task_files (various review statuses)
107-- ---------------------------------------------------------------------------
108INSERT INTO project.task_files (id, task_id, file_name, file_url, file_type, section, review_status, review_comment, is_completed, uploaded_by, version, visible_to_client, uploaded_at) VALUES
109(1, 1, 'homepage-mockup-v1.png', '/uploads/homepage-mockup-v1.png', 'image/png', 'design', 'APPROVED', 'Approved by client', TRUE, 2, 1, TRUE, '2026-02-18 14:00:00'),
110(2, 2, 'landing-page.zip', '/uploads/landing-page.zip', 'application/zip', 'build', 'PENDING', NULL, FALSE, 2, 2, FALSE, '2026-03-01 14:00:00'),
111(3, 4, 'auth-flow-diagram.pdf', '/uploads/auth-flow-diagram.pdf', 'application/pdf', 'docs', 'NEEDS_REVISION', 'Please revise token expiry', FALSE, 3, 1, TRUE, '2026-03-08 14:00:00'),
112(4, 6, 'content-calendar.xlsx', '/uploads/content-calendar.xlsx', 'application/xlsx', 'planning','APPROVED', 'Looks good', TRUE, 7, 1, TRUE, '2026-04-04 14:00:00');
113
114-- ---------------------------------------------------------------------------
115-- 8. file_comments (on task_files; author = user)
116-- ---------------------------------------------------------------------------
117INSERT INTO project.file_comments (id, file_id, user_id, content, visible_to_client, created_at) VALUES
118(1, 1, 4, 'Looks great, approved!', TRUE, '2026-02-18 15:00:00'),
119(2, 3, 1, 'Please revise the token expiry handling.', FALSE, '2026-03-08 16:00:00'),
120(3, 3, 3, 'Understood, will update by tomorrow.', FALSE, '2026-03-08 16:30:00');
121
122-- ---------------------------------------------------------------------------
123-- 9. task_comments (on tasks; author = user)
124-- ---------------------------------------------------------------------------
125INSERT INTO project.task_comments (id, task_id, user_id, content, visible_to_client, created_at) VALUES
126(1, 2, 4, 'When will the landing page be ready?', TRUE, '2026-02-25 10:00:00'),
127(2, 2, 2, 'Aiming for end of this week.', TRUE, '2026-02-25 10:20:00'),
128(3, 4, 1, 'Please prioritize the auth module.', FALSE, '2026-03-05 09:00:00');
129
130-- ---------------------------------------------------------------------------
131-- 10. invoices (billed to clients)
132-- ---------------------------------------------------------------------------
133INSERT INTO project.invoices (id, invoice_number, amount, subtotal, tax_rate, tax_amount, due_date, issue_date, status, description, payment_terms, client_id, paid_at, created_at) VALUES
134(1, 'INV-2026-001', 1416.00, 1200.00, 0.18, 216.00, '2026-03-15', '2026-03-01', 'PAID', 'ACME website - phase 1', 'Net 14', 4, '2026-03-10 12:00:00', '2026-03-01 12:00:00'),
135(2, 'INV-2026-002', 4130.00, 3500.00, 0.18, 630.00, '2026-04-15', '2026-04-01', 'PENDING', 'Nova mobile app - milestone 1', 'Net 14', 5, NULL, '2026-04-01 12:00:00'),
136(3, 'INV-2026-003', 944.00, 800.00, 0.18, 144.00, '2026-05-05', '2026-04-20', 'PARTIAL', 'BlueWave SMM - April', 'Net 14', 6, NULL, '2026-04-20 12:00:00'),
137(4, 'INV-2026-004', 2950.00, 2500.00, 0.18, 450.00, '2026-06-01', '2026-05-15', 'PENDING', 'ACME e-commerce - final', 'Net 14', 4, NULL, '2026-05-15 12:00:00');
138
139-- ---------------------------------------------------------------------------
140-- 11. invoice_line_items
141-- ---------------------------------------------------------------------------
142INSERT INTO project.invoice_line_items (id, invoice_id, name, description, quantity, unit_price, sort_order) VALUES
143(1, 1, 'Website design', 'Homepage + 7 subpages', 1, 800.00, 0),
144(2, 1, 'Hosting setup', 'Provisioning + deployment', 1, 400.00, 1),
145(3, 2, 'Mobile app development', 'Auth + core screens', 1, 3000.00, 0),
146(4, 2, 'UI/UX design', 'App design system', 1, 500.00, 1),
147(5, 3, 'SMM management', 'Monthly management fee', 1, 800.00, 0),
148(6, 4, 'E-commerce development', 'Catalog + checkout + payments',1, 2500.00, 0);
149
150-- ---------------------------------------------------------------------------
151-- 12. payments (invoice 1 paid in full; invoice 3 partial)
152-- ---------------------------------------------------------------------------
153INSERT INTO project.payments (id, invoice_id, amount, paid_at, note, created_at) VALUES
154(1, 1, 1416.00, '2026-03-10 12:00:00', 'Full payment - bank transfer', '2026-03-10 12:00:00'),
155(2, 3, 500.00, '2026-05-02 09:00:00', 'Partial payment', '2026-05-02 09:00:00');
156
157-- ---------------------------------------------------------------------------
158-- 13. timesheet_projects
159-- ---------------------------------------------------------------------------
160INSERT INTO project.timesheet_projects (id, project_name, client_id, description, is_paid, paid_at, created_at) VALUES
161(1, 'ACME Website Build', 4, 'Time tracking for ACME website', FALSE, NULL, '2026-02-11 09:00:00'),
162(2, 'Nova App Sprint 1', 5, 'Sprint 1 for Nova app', TRUE, '2026-04-01 10:00:00', '2026-03-02 09:00:00'),
163(3, 'Internal Tooling', NULL, 'Internal dev tooling (non-billable)', FALSE, NULL, '2026-01-20 09:00:00');
164
165-- ---------------------------------------------------------------------------
166-- 14. timesheet_entries
167-- ---------------------------------------------------------------------------
168INSERT INTO project.timesheet_entries (id, project_id, date, hours_worked, hourly_rate, total_pay, notes, created_at) VALUES
169(1, 1, '2026-02-12', 8.0, 25.00, 200.00, 'Homepage layout', '2026-02-12 18:00:00'),
170(2, 1, '2026-02-13', 6.0, 25.00, 150.00, 'Responsive tweaks', '2026-02-13 18:00:00'),
171(3, 2, '2026-03-03', 8.0, 30.00, 240.00, 'Auth module scaffolding', '2026-03-03 18:00:00'),
172(4, 2, '2026-03-04', 7.5, 30.00, 225.00, 'Login/register endpoints', '2026-03-04 18:00:00'),
173(5, 3, '2026-01-21', 4.0, 20.00, 80.00, 'CI pipeline setup', '2026-01-21 18:00:00');
174
175-- ---------------------------------------------------------------------------
176-- 15. notifications
177-- ---------------------------------------------------------------------------
178INSERT INTO project.notifications (id, user_id, type, title, message, read, link, source_role, task_id, thread_type, created_at) VALUES
179(1, 4, 'INVOICE_ISSUED', 'New invoice', 'Invoice INV-2026-001 has been issued.', TRUE, '/invoices/1', 'ADMIN', NULL, NULL, '2026-03-01 12:05:00'),
180(2, 5, 'TASK_UPDATED', 'Task in progress', 'Your task "Build authentication module" is now in progress.', FALSE, '/tasks/4', 'WORKER', 4, 'client', '2026-03-02 09:05:00'),
181(3, 2, 'TASK_ASSIGNED', 'New assignment', 'You have been assigned to "Develop landing page".', FALSE, '/tasks/2', 'ADMIN', 2, 'internal', '2026-02-21 09:15:00'),
182(4, 1, 'FILE_UPLOADED', 'File uploaded', 'A new file was uploaded to "Design homepage mockup".', TRUE, '/tasks/1', 'WORKER', 1, 'internal', '2026-02-18 14:05:00');
183
184-- ---------------------------------------------------------------------------
185-- 16. invites (token stored hashed; here demo SHA-like strings)
186-- ---------------------------------------------------------------------------
187INSERT INTO project.invites (id, email, role, token_hash, name, company, invited_by_id, used, cancelled, created_at, expires_at) VALUES
188(1, 'newclient@partner.mk', 'CLIENT', 'a1b2c3d4e5f60718293a4b5c6d7e8f9012345678abcdef1234567890abcdef12', 'Petar Kostov', 'Partner DOO', 1, FALSE, FALSE, '2026-06-20 10:00:00', '2026-07-20 10:00:00'),
189(2, 'newworker@ecore.mk', 'WORKER', 'f0e1d2c3b4a5968778695a4b3c2d1e0f9876543210fedcba0987654321fedcba', 'Sara Jovanova', 'Ecore', 1, FALSE, FALSE, '2026-06-25 10:00:00', '2026-07-25 10:00:00');
190
191-- ---------------------------------------------------------------------------
192-- 17. notification_preferences (unique per user + event_type)
193-- ---------------------------------------------------------------------------
194INSERT INTO project.notification_preferences (id, user_id, event_type, in_app_enabled, email_enabled) VALUES
195(1, 4, 'INVOICE_ISSUED', TRUE, TRUE),
196(2, 4, 'TASK_UPDATED', TRUE, FALSE),
197(3, 2, 'TASK_ASSIGNED', TRUE, TRUE),
198(4, 5, 'TASK_UPDATED', TRUE, TRUE);
199
200-- ---------------------------------------------------------------------------
201-- 18. Re-sync IDENTITY sequences to MAX(id) so future inserts don't collide
202-- ---------------------------------------------------------------------------
203SELECT setval(pg_get_serial_sequence('project.users', 'id'), (SELECT MAX(id) FROM project.users));
204SELECT setval(pg_get_serial_sequence('project.projects', 'id'), (SELECT MAX(id) FROM project.projects));
205SELECT setval(pg_get_serial_sequence('project.domains', 'id'), (SELECT MAX(id) FROM project.domains));
206SELECT setval(pg_get_serial_sequence('project.tasks', 'id'), (SELECT MAX(id) FROM project.tasks));
207SELECT setval(pg_get_serial_sequence('project.milestones', 'id'), (SELECT MAX(id) FROM project.milestones));
208SELECT setval(pg_get_serial_sequence('project.task_workers', 'id'), (SELECT MAX(id) FROM project.task_workers));
209SELECT setval(pg_get_serial_sequence('project.task_files', 'id'), (SELECT MAX(id) FROM project.task_files));
210SELECT setval(pg_get_serial_sequence('project.file_comments', 'id'), (SELECT MAX(id) FROM project.file_comments));
211SELECT setval(pg_get_serial_sequence('project.task_comments', 'id'), (SELECT MAX(id) FROM project.task_comments));
212SELECT setval(pg_get_serial_sequence('project.invoices', 'id'), (SELECT MAX(id) FROM project.invoices));
213SELECT setval(pg_get_serial_sequence('project.payments', 'id'), (SELECT MAX(id) FROM project.payments));
214SELECT setval(pg_get_serial_sequence('project.invoice_line_items', 'id'), (SELECT MAX(id) FROM project.invoice_line_items));
215SELECT setval(pg_get_serial_sequence('project.timesheet_projects', 'id'), (SELECT MAX(id) FROM project.timesheet_projects));
216SELECT setval(pg_get_serial_sequence('project.timesheet_entries', 'id'), (SELECT MAX(id) FROM project.timesheet_entries));
217SELECT setval(pg_get_serial_sequence('project.notifications', 'id'), (SELECT MAX(id) FROM project.notifications));
218SELECT setval(pg_get_serial_sequence('project.invites', 'id'), (SELECT MAX(id) FROM project.invites));
219SELECT setval(pg_get_serial_sequence('project.notification_preferences', 'id'), (SELECT MAX(id) FROM project.notification_preferences));
220
221-- ===========================================================================
222-- End of data_load.sql
223-- users 7 | projects 4 | domains 5 | tasks 10 | milestones 5 | task_workers 8
224-- task_files 4 | file_comments 3 | task_comments 3 | invoices 4 | line_items 6
225-- payments 2 | timesheet_projects 3 | timesheet_entries 5 | notifications 4
226-- invites 2 | notification_preferences 4
227-- ===========================================================================