-- =========================================================================== -- Ecore - Project Management System -- PHASE P2 : Sample data load (DML) -- Target : PostgreSQL, schema "project" (run schema_creation.sql first) -- -- Re-runnable: every table is TRUNCATEd (RESTART IDENTITY CASCADE) before load. -- Explicit IDs are used so cross-references stay readable; identity sequences -- are re-synced at the end so future app inserts don't collide. -- =========================================================================== SET search_path TO project; -- --------------------------------------------------------------------------- -- 0. Clean slate (order-independent thanks to CASCADE; RESTART IDENTITY resets seqs) -- --------------------------------------------------------------------------- TRUNCATE TABLE project.notification_preferences, project.invites, project.notifications, project.timesheet_entries, project.timesheet_projects, project.invoice_line_items, project.payments, project.invoices, project.task_comments, project.file_comments, project.task_files, project.task_workers, project.milestones, project.tasks, project.domains, project.projects, project.users RESTART IDENTITY CASCADE; -- --------------------------------------------------------------------------- -- 1. users (1 admin, 3 workers, 3 clients) password hash = demo bcrypt hash -- --------------------------------------------------------------------------- INSERT INTO project.users (id, email, password, role, name, company, phone_number, skills, profile_status, created_at) VALUES (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'), (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'), (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'), (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'), (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'), (6, 'stefan@bluewave.mk', '$2b$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'CLIENT', 'Stefan Dimitrov', 'BlueWave', '+389 71 300 300', ARRAY[]::TEXT[], 'INCOMPLETE', '2026-02-05 10:00:00'), (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'); -- --------------------------------------------------------------------------- -- 2. projects (linked to client users) -- --------------------------------------------------------------------------- INSERT INTO project.projects (id, name, description, client_id, status, service_type, metadata, start_date, end_date, created_at) VALUES (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'), (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'), (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'), (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'); -- --------------------------------------------------------------------------- -- 3. domains (linked to clients; cascade on client delete) -- --------------------------------------------------------------------------- INSERT 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 (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'), (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'), (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'), (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'), (5, 6, 'blog.bluewave.mk', FALSE, FALSE,'MKhost', 'Starter', NULL, '2027-04-02', '2027-04-02', 1, 'PENDING', '2026-04-02 12:10:00'); -- --------------------------------------------------------------------------- -- 4. tasks (linked to clients and projects) -- --------------------------------------------------------------------------- INSERT INTO project.tasks (id, title, description, status, due_date, client_id, project_id, created_at) VALUES (1, 'Design homepage mockup', 'High-fidelity mockup of the ACME homepage', 'DONE', '2026-02-20', 4, 1, '2026-02-11 09:00:00'), (2, 'Develop landing page', 'Implement responsive landing page from mockup', 'IN_PROGRESS', '2026-03-05', 4, 1, '2026-02-21 09:00:00'), (3, 'Set up hosting', 'Provision hosting and deploy staging build', 'PENDING', '2026-03-10', 4, 1, '2026-02-21 09:10:00'), (4, 'Build authentication module', 'JWT login/register for the Nova app', 'IN_PROGRESS', '2026-03-20', 5, 2, '2026-03-02 09:00:00'), (5, 'API integration', 'Integrate Nova backend REST API', 'PENDING', '2026-03-28', 5, 2, '2026-03-02 09:20:00'), (6, 'Create content calendar', 'Q2 posting schedule for BlueWave', 'DONE', '2026-04-05', 6, 3, '2026-04-02 09:00:00'), (7, 'Design ad creatives', 'Static + carousel creatives for campaign', 'IN_PROGRESS', '2026-04-15', 6, 3, '2026-04-03 09:00:00'), (8, 'Product catalog schema', 'Model products, variants and stock', 'PENDING', '2026-02-10', 4, 4, '2026-01-16 09:00:00'), (9, 'Payment gateway integration', 'Integrate CPay/Stripe checkout', 'DONE', '2026-04-20', 4, 4, '2026-01-20 09:00:00'), (10, 'SEO optimization', 'On-page SEO for BlueWave blog', 'PENDING', '2026-05-01', 6, 3, '2026-04-10 09:00:00'); -- --------------------------------------------------------------------------- -- 5. milestones (under tasks 1, 2, 4) -- --------------------------------------------------------------------------- INSERT 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 (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'), (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'), (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'), (4, 2, 'Responsive styling', 'Mobile + tablet breakpoints', FALSE, NULL, NULL, 2, FALSE, FALSE, 1, '2026-02-28 10:00:00'), (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'); -- --------------------------------------------------------------------------- -- 6. task_workers (assign workers 2,3,7 to tasks; M:N) -- --------------------------------------------------------------------------- INSERT INTO project.task_workers (id, task_id, user_id) VALUES (1, 1, 2), (2, 2, 2), (3, 2, 7), (4, 4, 3), (5, 5, 3), (6, 6, 7), (7, 7, 2), (8, 9, 3); -- --------------------------------------------------------------------------- -- 7. task_files (various review statuses) -- --------------------------------------------------------------------------- INSERT 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 (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'), (2, 2, 'landing-page.zip', '/uploads/landing-page.zip', 'application/zip', 'build', 'PENDING', NULL, FALSE, 2, 2, FALSE, '2026-03-01 14:00:00'), (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'), (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'); -- --------------------------------------------------------------------------- -- 8. file_comments (on task_files; author = user) -- --------------------------------------------------------------------------- INSERT INTO project.file_comments (id, file_id, user_id, content, visible_to_client, created_at) VALUES (1, 1, 4, 'Looks great, approved!', TRUE, '2026-02-18 15:00:00'), (2, 3, 1, 'Please revise the token expiry handling.', FALSE, '2026-03-08 16:00:00'), (3, 3, 3, 'Understood, will update by tomorrow.', FALSE, '2026-03-08 16:30:00'); -- --------------------------------------------------------------------------- -- 9. task_comments (on tasks; author = user) -- --------------------------------------------------------------------------- INSERT INTO project.task_comments (id, task_id, user_id, content, visible_to_client, created_at) VALUES (1, 2, 4, 'When will the landing page be ready?', TRUE, '2026-02-25 10:00:00'), (2, 2, 2, 'Aiming for end of this week.', TRUE, '2026-02-25 10:20:00'), (3, 4, 1, 'Please prioritize the auth module.', FALSE, '2026-03-05 09:00:00'); -- --------------------------------------------------------------------------- -- 10. invoices (billed to clients) -- --------------------------------------------------------------------------- INSERT 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 (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'), (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'), (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'), (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'); -- --------------------------------------------------------------------------- -- 11. invoice_line_items -- --------------------------------------------------------------------------- INSERT INTO project.invoice_line_items (id, invoice_id, name, description, quantity, unit_price, sort_order) VALUES (1, 1, 'Website design', 'Homepage + 7 subpages', 1, 800.00, 0), (2, 1, 'Hosting setup', 'Provisioning + deployment', 1, 400.00, 1), (3, 2, 'Mobile app development', 'Auth + core screens', 1, 3000.00, 0), (4, 2, 'UI/UX design', 'App design system', 1, 500.00, 1), (5, 3, 'SMM management', 'Monthly management fee', 1, 800.00, 0), (6, 4, 'E-commerce development', 'Catalog + checkout + payments',1, 2500.00, 0); -- --------------------------------------------------------------------------- -- 12. payments (invoice 1 paid in full; invoice 3 partial) -- --------------------------------------------------------------------------- INSERT INTO project.payments (id, invoice_id, amount, paid_at, note, created_at) VALUES (1, 1, 1416.00, '2026-03-10 12:00:00', 'Full payment - bank transfer', '2026-03-10 12:00:00'), (2, 3, 500.00, '2026-05-02 09:00:00', 'Partial payment', '2026-05-02 09:00:00'); -- --------------------------------------------------------------------------- -- 13. timesheet_projects -- --------------------------------------------------------------------------- INSERT INTO project.timesheet_projects (id, project_name, client_id, description, is_paid, paid_at, created_at) VALUES (1, 'ACME Website Build', 4, 'Time tracking for ACME website', FALSE, NULL, '2026-02-11 09:00:00'), (2, 'Nova App Sprint 1', 5, 'Sprint 1 for Nova app', TRUE, '2026-04-01 10:00:00', '2026-03-02 09:00:00'), (3, 'Internal Tooling', NULL, 'Internal dev tooling (non-billable)', FALSE, NULL, '2026-01-20 09:00:00'); -- --------------------------------------------------------------------------- -- 14. timesheet_entries -- --------------------------------------------------------------------------- INSERT INTO project.timesheet_entries (id, project_id, date, hours_worked, hourly_rate, total_pay, notes, created_at) VALUES (1, 1, '2026-02-12', 8.0, 25.00, 200.00, 'Homepage layout', '2026-02-12 18:00:00'), (2, 1, '2026-02-13', 6.0, 25.00, 150.00, 'Responsive tweaks', '2026-02-13 18:00:00'), (3, 2, '2026-03-03', 8.0, 30.00, 240.00, 'Auth module scaffolding', '2026-03-03 18:00:00'), (4, 2, '2026-03-04', 7.5, 30.00, 225.00, 'Login/register endpoints', '2026-03-04 18:00:00'), (5, 3, '2026-01-21', 4.0, 20.00, 80.00, 'CI pipeline setup', '2026-01-21 18:00:00'); -- --------------------------------------------------------------------------- -- 15. notifications -- --------------------------------------------------------------------------- INSERT INTO project.notifications (id, user_id, type, title, message, read, link, source_role, task_id, thread_type, created_at) VALUES (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'), (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'), (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'), (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'); -- --------------------------------------------------------------------------- -- 16. invites (token stored hashed; here demo SHA-like strings) -- --------------------------------------------------------------------------- INSERT INTO project.invites (id, email, role, token_hash, name, company, invited_by_id, used, cancelled, created_at, expires_at) VALUES (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'), (2, 'newworker@ecore.mk', 'WORKER', 'f0e1d2c3b4a5968778695a4b3c2d1e0f9876543210fedcba0987654321fedcba', 'Sara Jovanova', 'Ecore', 1, FALSE, FALSE, '2026-06-25 10:00:00', '2026-07-25 10:00:00'); -- --------------------------------------------------------------------------- -- 17. notification_preferences (unique per user + event_type) -- --------------------------------------------------------------------------- INSERT INTO project.notification_preferences (id, user_id, event_type, in_app_enabled, email_enabled) VALUES (1, 4, 'INVOICE_ISSUED', TRUE, TRUE), (2, 4, 'TASK_UPDATED', TRUE, FALSE), (3, 2, 'TASK_ASSIGNED', TRUE, TRUE), (4, 5, 'TASK_UPDATED', TRUE, TRUE); -- --------------------------------------------------------------------------- -- 18. Re-sync IDENTITY sequences to MAX(id) so future inserts don't collide -- --------------------------------------------------------------------------- SELECT setval(pg_get_serial_sequence('project.users', 'id'), (SELECT MAX(id) FROM project.users)); SELECT setval(pg_get_serial_sequence('project.projects', 'id'), (SELECT MAX(id) FROM project.projects)); SELECT setval(pg_get_serial_sequence('project.domains', 'id'), (SELECT MAX(id) FROM project.domains)); SELECT setval(pg_get_serial_sequence('project.tasks', 'id'), (SELECT MAX(id) FROM project.tasks)); SELECT setval(pg_get_serial_sequence('project.milestones', 'id'), (SELECT MAX(id) FROM project.milestones)); SELECT setval(pg_get_serial_sequence('project.task_workers', 'id'), (SELECT MAX(id) FROM project.task_workers)); SELECT setval(pg_get_serial_sequence('project.task_files', 'id'), (SELECT MAX(id) FROM project.task_files)); SELECT setval(pg_get_serial_sequence('project.file_comments', 'id'), (SELECT MAX(id) FROM project.file_comments)); SELECT setval(pg_get_serial_sequence('project.task_comments', 'id'), (SELECT MAX(id) FROM project.task_comments)); SELECT setval(pg_get_serial_sequence('project.invoices', 'id'), (SELECT MAX(id) FROM project.invoices)); SELECT setval(pg_get_serial_sequence('project.payments', 'id'), (SELECT MAX(id) FROM project.payments)); SELECT setval(pg_get_serial_sequence('project.invoice_line_items', 'id'), (SELECT MAX(id) FROM project.invoice_line_items)); SELECT setval(pg_get_serial_sequence('project.timesheet_projects', 'id'), (SELECT MAX(id) FROM project.timesheet_projects)); SELECT setval(pg_get_serial_sequence('project.timesheet_entries', 'id'), (SELECT MAX(id) FROM project.timesheet_entries)); SELECT setval(pg_get_serial_sequence('project.notifications', 'id'), (SELECT MAX(id) FROM project.notifications)); SELECT setval(pg_get_serial_sequence('project.invites', 'id'), (SELECT MAX(id) FROM project.invites)); SELECT setval(pg_get_serial_sequence('project.notification_preferences', 'id'), (SELECT MAX(id) FROM project.notification_preferences)); -- =========================================================================== -- End of data_load.sql -- users 7 | projects 4 | domains 5 | tasks 10 | milestones 5 | task_workers 8 -- task_files 4 | file_comments 3 | task_comments 3 | invoices 4 | line_items 6 -- payments 2 | timesheet_projects 3 | timesheet_entries 5 | notifications 4 -- invites 2 | notification_preferences 4 -- ===========================================================================