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