-- ===========================================================================
-- 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.
-- ===========================================================================


-- ---------------------------------------------------------------------------
-- 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');

-- ---------------------------------------------------------------------------
-- 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);

-- ===========================================================================
-- 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
-- ===========================================================================
