-- ============================================================ -- Sample / Demo Data -- Run AFTER schema.sql. Safe to run once on a fresh database. -- All seeded users share the password: Password@123 -- (hash verified with Python bcrypt.checkpw() before being embedded here) -- ============================================================ USE task_management; SET @pw := '$2b$10$gXqvWLxRkbey8UOCB2u2g.AG60kEUX7KQmkISkdyO4SnKdF77kLSO'; -- ------------------------------------------------------------ -- Extra department (schema.sql already seeds 'IT Operations' and 'Network Infrastructure') -- ------------------------------------------------------------ INSERT INTO departments (name, description) VALUES ('Security & Compliance', 'InfoSec and compliance team'); SET @dept_it := (SELECT id FROM departments WHERE name = 'IT Operations'); SET @dept_net := (SELECT id FROM departments WHERE name = 'Network Infrastructure'); SET @dept_sec := (SELECT id FROM departments WHERE name = 'Security & Compliance'); -- ------------------------------------------------------------ -- Teams (created leaderless first; leader assigned after the leader user exists) -- ------------------------------------------------------------ INSERT INTO teams (name, department_id, description) VALUES ('Server Infrastructure', @dept_it, 'Manages physical and virtual server estate'), ('Network Engineering', @dept_net, 'Routing, switching, and WAN/LAN operations'), ('Security Operations', @dept_sec, 'Vulnerability management and incident response'); SET @team_server := (SELECT id FROM teams WHERE name = 'Server Infrastructure'); SET @team_net := (SELECT id FROM teams WHERE name = 'Network Engineering'); SET @team_sec := (SELECT id FROM teams WHERE name = 'Security Operations'); SET @role_leader := (SELECT id FROM roles WHERE name = 'team_leader'); SET @role_member := (SELECT id FROM roles WHERE name = 'team_member'); -- ------------------------------------------------------------ -- Team leaders -- ------------------------------------------------------------ INSERT INTO users (name, username, email, password_hash, mobile, position, role_id, department_id, team_id, is_active) VALUES ('Sarah Ahmed', 'sarah.leader', 'sarah.ahmed@example.com', @pw, '0501112222', 'Server Infrastructure Lead', @role_leader, @dept_it, @team_server, 1), ('Michael Chen', 'michael.leader','michael.chen@example.com', @pw, '0502223333', 'Network Engineering Lead', @role_leader, @dept_net, @team_net, 1), ('Layla Hassan', 'layla.leader', 'layla.hassan@example.com', @pw, '0503334444', 'Security Operations Lead', @role_leader, @dept_sec, @team_sec, 1); SET @leader_server := (SELECT id FROM users WHERE username = 'sarah.leader'); SET @leader_net := (SELECT id FROM users WHERE username = 'michael.leader'); SET @leader_sec := (SELECT id FROM users WHERE username = 'layla.leader'); UPDATE teams SET leader_id = @leader_server WHERE id = @team_server; UPDATE teams SET leader_id = @leader_net WHERE id = @team_net; UPDATE teams SET leader_id = @leader_sec WHERE id = @team_sec; -- ------------------------------------------------------------ -- Team members -- ------------------------------------------------------------ INSERT INTO users (name, username, email, password_hash, mobile, position, role_id, department_id, team_id, is_active) VALUES ('Omar Farouk', 'omar.member', 'omar.farouk@example.com', @pw, '0504445555', 'Systems Administrator', @role_member, @dept_it, @team_server, 1), ('Nadia Youssef', 'nadia.member', 'nadia.youssef@example.com', @pw, '0505556666', 'Systems Administrator', @role_member, @dept_it, @team_server, 1), ('Khalid Rahman', 'khalid.member', 'khalid.rahman@example.com', @pw, '0506667777', 'Network Engineer', @role_member, @dept_net, @team_net, 1), ('Fatima Zahra', 'fatima.member', 'fatima.zahra@example.com', @pw, '0507778888', 'Network Engineer', @role_member, @dept_net, @team_net, 1), ('Yusuf Ibrahim', 'yusuf.member', 'yusuf.ibrahim@example.com', @pw, '0508889999', 'Security Analyst', @role_member, @dept_sec, @team_sec, 1), ('Rania Malik', 'rania.member', 'rania.malik@example.com', @pw, '0509990000', 'Security Analyst', @role_member, @dept_sec, @team_sec, 1); SET @m_omar := (SELECT id FROM users WHERE username = 'omar.member'); SET @m_nadia := (SELECT id FROM users WHERE username = 'nadia.member'); SET @m_khalid := (SELECT id FROM users WHERE username = 'khalid.member'); SET @m_fatima := (SELECT id FROM users WHERE username = 'fatima.member'); SET @m_yusuf := (SELECT id FROM users WHERE username = 'yusuf.member'); SET @m_rania := (SELECT id FROM users WHERE username = 'rania.member'); SET @admin_id := (SELECT id FROM users WHERE username = 'admin'); -- ------------------------------------------------------------ -- Projects -- ------------------------------------------------------------ INSERT INTO projects (name, description, team_id, manager_id, start_date, end_date, status) VALUES ('Data Center Migration', 'Migrate on-prem servers to the new data center.', @team_server, @leader_server, DATE_SUB(CURDATE(), INTERVAL 45 DAY), DATE_ADD(CURDATE(), INTERVAL 30 DAY), 'active'), ('Core Network Upgrade', 'Upgrade core switches and WAN links to 10G.', @team_net, @leader_net, DATE_SUB(CURDATE(), INTERVAL 20 DAY), DATE_ADD(CURDATE(), INTERVAL 60 DAY), 'active'), ('Q3 Security Audit', 'Quarterly vulnerability assessment and remediation.', @team_sec, @leader_sec, DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_ADD(CURDATE(), INTERVAL 20 DAY), 'active'), ('Backup System Overhaul', 'Replace legacy backup solution across all sites.', @team_server, @leader_server, DATE_SUB(CURDATE(), INTERVAL 90 DAY), DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'completed'); SET @proj_migration := (SELECT id FROM projects WHERE name = 'Data Center Migration'); SET @proj_network := (SELECT id FROM projects WHERE name = 'Core Network Upgrade'); SET @proj_audit := (SELECT id FROM projects WHERE name = 'Q3 Security Audit'); SET @proj_backup := (SELECT id FROM projects WHERE name = 'Backup System Overhaul'); SET @cat_server := (SELECT id FROM task_categories WHERE name = 'Server Maintenance'); SET @cat_net := (SELECT id FROM task_categories WHERE name = 'Network Configuration'); SET @cat_sec := (SELECT id FROM task_categories WHERE name = 'Security'); SET @cat_general := (SELECT id FROM task_categories WHERE name = 'General'); -- ------------------------------------------------------------ -- Tasks — deliberately spread across every status/priority, with a mix of -- overdue, due-today, due-this-week, and already-completed tasks so the -- dashboard KPIs, charts, reports, and calendar all have something to show. -- ------------------------------------------------------------ INSERT INTO tasks (task_number, title, description, project_id, category_id, team_id, assigned_by, assigned_to, priority, status, start_date, due_date, completion_date, estimated_hours, actual_hours, progress) VALUES ('TSK-000001', 'Rack and cable new blade servers', 'Physically install and cable the new blade chassis in DC2.', @proj_migration, @cat_server, @team_server, @leader_server, @m_omar, 'high', 'in_progress', DATE_SUB(CURDATE(), INTERVAL 3 DAY), DATE_ADD(CURDATE(), INTERVAL 2 DAY), NULL, 8, 5, 60), ('TSK-000002', 'Migrate file server shares to new SAN', 'Move all department file shares with zero data loss.', @proj_migration, @cat_server, @team_server, @leader_server, @m_nadia, 'critical', 'accepted', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 5 DAY), NULL, 12, 0, 0), ('TSK-000003', 'Decommission legacy backup tapes', 'Securely wipe and dispose of retired tape media.', @proj_backup, @cat_server, @team_server, @leader_server, @m_omar, 'low', 'completed', DATE_SUB(CURDATE(), INTERVAL 20 DAY), DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_SUB(NOW(), INTERVAL 10 DAY), 4, 3.5, 100), ('TSK-000004', 'Patch Windows Server fleet', 'Apply monthly security patches to all production Windows servers.', NULL, @cat_server, @team_server, @leader_server, @m_nadia, 'high', 'new', NULL, DATE_SUB(CURDATE(), INTERVAL 2 DAY), NULL, 6, 0, 0), ('TSK-000005', 'Upgrade core switch firmware', 'Apply the latest stable firmware to the core switch stack.', @proj_network, @cat_net, @team_net, @leader_net, @m_khalid, 'critical', 'waiting', DATE_SUB(CURDATE(), INTERVAL 1 DAY), CURDATE(), NULL, 6, 1, 20), ('TSK-000006', 'Configure new WAN link failover', 'Set up BGP failover between primary and backup ISP circuits.', @proj_network, @cat_net, @team_net, @leader_net, @m_fatima, 'medium', 'in_progress', DATE_SUB(CURDATE(), INTERVAL 2 DAY), DATE_ADD(CURDATE(), INTERVAL 1 DAY), NULL, 10, 4, 40), ('TSK-000007', 'Document VLAN topology', 'Update network diagrams to reflect the current VLAN structure.', @proj_network, @cat_net, @team_net, @leader_net, @m_khalid, 'low', 'completed', DATE_SUB(CURDATE(), INTERVAL 35 DAY), DATE_SUB(CURDATE(), INTERVAL 25 DAY), DATE_SUB(NOW(), INTERVAL 25 DAY), 3, 2.5, 100), ('TSK-000008', 'Replace failing access switch in Building B', 'Field replace the switch reporting intermittent port errors.', NULL, @cat_net, @team_net, @leader_net, @m_fatima, 'high', 'on_hold', DATE_SUB(CURDATE(), INTERVAL 5 DAY), DATE_SUB(CURDATE(), INTERVAL 3 DAY), NULL, 4, 1, 10), ('TSK-000009', 'Run external vulnerability scan', 'Kick off the quarterly external-facing vulnerability scan.', @proj_audit, @cat_sec, @team_sec, @leader_sec, @m_yusuf, 'high', 'in_progress', DATE_SUB(CURDATE(), INTERVAL 4 DAY), DATE_ADD(CURDATE(), INTERVAL 3 DAY), NULL, 8, 5, 55), ('TSK-000010', 'Remediate critical CVE on web tier', 'Patch the critical RCE vulnerability flagged in the last scan.', @proj_audit, @cat_sec, @team_sec, @leader_sec, @m_rania, 'critical', 'assigned', CURDATE(), CURDATE(), NULL, 6, 0, 0), ('TSK-000011', 'Review firewall rule set', 'Audit and clean up stale firewall rules across all sites.', @proj_audit, @cat_sec, @team_sec, @leader_sec, @m_yusuf, 'medium', 'completed', DATE_SUB(CURDATE(), INTERVAL 15 DAY), DATE_SUB(CURDATE(), INTERVAL 8 DAY), DATE_SUB(NOW(), INTERVAL 8 DAY), 5, 6, 100), ('TSK-000012', 'Draft incident response runbook', 'Write the runbook for ransomware incident response.', NULL, @cat_sec, @team_sec, @leader_sec, @m_rania, 'medium', 'new', NULL, DATE_ADD(CURDATE(), INTERVAL 10 DAY), NULL, 8, 0, 0), ('TSK-000013', 'Cancelled: legacy VPN decommission', 'Superseded by the new SD-WAN rollout; no longer needed.', NULL, @cat_net, @team_net, @leader_net, @m_khalid, 'low', 'cancelled', DATE_SUB(CURDATE(), INTERVAL 30 DAY), DATE_SUB(CURDATE(), INTERVAL 20 DAY), NULL, 3, 0, 0), ('TSK-000014', 'Server room HVAC inspection', 'Coordinate with facilities for the annual HVAC inspection.', NULL, @cat_general, @team_server, @leader_server, @m_nadia, 'low', 'closed', DATE_SUB(CURDATE(), INTERVAL 60 DAY), DATE_SUB(CURDATE(), INTERVAL 50 DAY), DATE_SUB(NOW(), INTERVAL 50 DAY), 2, 2, 100), ('TSK-000015', 'Unassigned: evaluate backup cloud tiering', 'Investigate cold-tier cloud storage for long-term backup retention.', @proj_backup, @cat_server, @team_server, @leader_server, NULL, 'medium', 'new', NULL, DATE_ADD(CURDATE(), INTERVAL 14 DAY), NULL, 6, 0, 0); -- ------------------------------------------------------------ -- Task assignment history (mirrors the initial assignments above) -- ------------------------------------------------------------ INSERT INTO task_assignments (task_id, assigned_from, assigned_to, assigned_by, reason) SELECT id, NULL, assigned_to, assigned_by, 'Initial assignment' FROM tasks WHERE assigned_to IS NOT NULL; -- ------------------------------------------------------------ -- Progress timeline entries for a couple of in-progress tasks -- ------------------------------------------------------------ SET @task1 := (SELECT id FROM tasks WHERE task_number = 'TSK-000001'); SET @task9 := (SELECT id FROM tasks WHERE task_number = 'TSK-000009'); INSERT INTO task_progress (task_id, user_id, status, progress, notes, logged_at) VALUES (@task1, @m_omar, 'accepted', 0, 'Confirmed rack space and started pulling cable.', DATE_SUB(NOW(), INTERVAL 3 DAY)), (@task1, @m_omar, 'in_progress', 30, 'Chassis mounted, power cabling in progress.', DATE_SUB(NOW(), INTERVAL 2 DAY)), (@task1, @m_omar, 'in_progress', 60, 'Network cabling complete, awaiting switch port config.', DATE_SUB(NOW(), INTERVAL 6 HOUR)), (@task9, @m_yusuf, 'accepted', 0, 'Scan window confirmed with the network team.', DATE_SUB(NOW(), INTERVAL 4 DAY)), (@task9, @m_yusuf, 'in_progress', 55, 'Scan running, initial results being triaged.', DATE_SUB(NOW(), INTERVAL 1 DAY)); -- ------------------------------------------------------------ -- Comments -- ------------------------------------------------------------ INSERT INTO task_comments (task_id, user_id, comment, created_at) VALUES (@task1, @leader_server, 'Let me know once cabling is done so we can schedule the switch config window.', DATE_SUB(NOW(), INTERVAL 2 DAY)), (@task1, @m_omar, 'Will do — should be ready by tomorrow morning.', DATE_SUB(NOW(), INTERVAL 1 DAY)), (@task9, @leader_sec, 'Please flag anything critical immediately, don''t wait for the full report.', DATE_SUB(NOW(), INTERVAL 1 DAY)); -- ------------------------------------------------------------ -- A few sample notifications so the bell icon has content on first login -- ------------------------------------------------------------ INSERT INTO notifications (user_id, task_id, type, message, is_read, created_at) VALUES (@m_omar, @task1, 'new_assignment', 'You were assigned task TSK-000001: Rack and cable new blade servers.', 1, DATE_SUB(NOW(), INTERVAL 3 DAY)), (@m_yusuf, @task9, 'new_assignment', 'You were assigned task TSK-000009: Run external vulnerability scan.', 1, DATE_SUB(NOW(), INTERVAL 4 DAY)), (@leader_server, @task2 := (SELECT id FROM tasks WHERE task_number = 'TSK-000002'), 'progress_updated', 'Nadia Youssef accepted task TSK-000002.', 0, DATE_SUB(NOW(), INTERVAL 2 HOUR)), (@leader_sec, @task10 := (SELECT id FROM tasks WHERE task_number = 'TSK-000010'), 'due_today', 'Task TSK-000010 is due today.', 0, NOW());