Task Management Database Challenge

INSERT INTO users (username, email, first_name, last_name, role) VALUES
('schen', 'sarah.chen@taskmaster.com', 'Sarah', 'Chen', 'manager'),
('mjohnson', 'mike.j@taskmaster.com', 'Mike', 'Johnson', 'admin'),
('edavis', 'emma.davis@taskmaster.com', 'Emma', 'Davis', 'member'),
('arodriguez', 'alex.r@taskmaster.com', 'Alex', 'Rodriguez', 'member'),
('lwong', 'lisa.wong@taskmaster.com', 'Lisa', 'Wong', 'manager');

-- Insert projects
INSERT INTO projects (project_name, description, manager_id, start_date, status) VALUES
('Website Redesign', 'Complete overhaul of company website', 1, '2024-02-01', 'active'),
('Mobile App', 'Develop iOS and Android apps', 5, '2024-01-15', 'active'),
('Data Migration', 'Migrate legacy data to new system', 1, '2024-03-01', 'planning');

-- Insert categories
INSERT INTO categories (category_name, color_code, description) VALUES
('Bug', '#FF0000', 'Software defects and issues'),
('Feature', '#00FF00', 'New functionality requests'),
('Documentation', '#0000FF', 'Documentation tasks'),
('Testing', '#FFA500', 'QA and testing tasks'),
('Design', '#800080', 'UI/UX design tasks');

-- 1. Find all managers
SELECT * FROM users WHERE role = 'manager';

-- 2. List all active projects
SELECT project_name, description, start_date 
FROM projects 
WHERE status = 'active';

-- 3. Show categories with colors
SELECT category_name, color_code, description 
FROM categories 
ORDER BY category_name;

INSERT INTO tasks (title, description, project_id, assigned_to, created_by, category_id, priority, due_date) VALUES
('Fix login page bug', 'Users cannot login with special characters in password', 1, 3, 1, 1, 'high', '2024-02-10'),
('Design new homepage', 'Create mockups for new homepage layout', 1, 4, 1, 5, 'medium', '2024-02-15'),
('Implement user authentication', 'Add OAuth2 authentication for mobile app', 2, 3, 5, 2, 'high', '2024-01-30'),
('Write API documentation', 'Document all REST API endpoints', 2, 4, 5, 3, 'low', '2024-02-20'),
('Test data import process', 'Verify data integrity during migration', 3, 3, 1, 4, 'urgent', '2024-03-05'),
('Create responsive navbar', 'Mobile-friendly navigation menu', 1, 4, 1, 5, 'medium', '2024-02-12'),
('Fix database connection timeout', 'Connection drops after 5 minutes', 2, 3, 5, 1, 'urgent', '2024-01-25'),
('Add push notifications', 'Implement push notification service', 2, 3, 5, 2, 'medium', '2024-02-05'),
('Update user manual', 'Add new features to documentation', 1, 4, 1, 3, 'low', '2024-02-28'),
('Performance testing', 'Load test with 1000 concurrent users', 2, 3, 5, 4, 'high', '2024-02-01');

-- 1. Find urgent uncompleted tasks
SELECT title, description, due_date, status 
FROM tasks 
WHERE priority = 'urgent' 
AND status != 'completed'
ORDER BY due_date;

-- 2. Tasks assigned to Emma Davis
SELECT t.title, t.priority, t.due_date, p.project_name
FROM tasks t
JOIN users u ON t.assigned_to = u.user_id
JOIN projects p ON t.project_id = p.project_id
WHERE u.first_name = 'Emma' AND u.last_name = 'Davis';

-- 3. Tasks due within 7 days
SELECT title, due_date, priority, status
FROM tasks
WHERE due_date BETWEEN DATE('now') AND DATE('now', '+7 days')
AND status != 'completed'
ORDER BY due_date;

-- 4. Bug tasks in Website Redesign
SELECT t.title, t.priority, c.category_name
FROM tasks t
JOIN categories c ON t.category_id = c.category_id
JOIN projects p ON t.project_id = p.project_id
WHERE c.category_name = 'Bug' 
AND p.project_name = 'Website Redesign';

-- 1. Mark specific task as completed
UPDATE tasks 
SET status = 'completed', 
    completed_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE title = 'Fix database connection timeout';

-- 2. Update documentation priority
UPDATE tasks 
SET priority = 'medium',
    updated_at = CURRENT_TIMESTAMP
WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Documentation')
AND priority = 'low';

-- 3. Reassign Emma's tasks to Alex
UPDATE tasks 
SET assigned_to = (SELECT user_id FROM users WHERE username = 'arodriguez'),
    updated_at = CURRENT_TIMESTAMP
WHERE assigned_to = (SELECT user_id FROM users WHERE username = 'edavis');

-- 4. Update overdue tasks to urgent
UPDATE tasks 
SET priority = 'urgent',
    updated_at = CURRENT_TIMESTAMP
WHERE due_date < DATE('now') 
AND status NOT IN ('completed', 'cancelled');

-- 1. Count tasks by priority for each project
SELECT p.project_name, t.priority, COUNT(*) as task_count
FROM tasks t
JOIN projects p ON t.project_id = p.project_id
GROUP BY p.project_name, t.priority
ORDER BY p.project_name, t.priority;

-- 2. User with most assigned tasks
SELECT u.first_name, u.last_name, COUNT(t.task_id) as assigned_tasks
FROM users u
LEFT JOIN tasks t ON u.user_id = t.assigned_to
GROUP BY u.user_id, u.first_name, u.last_name
ORDER BY assigned_tasks DESC
LIMIT 1;

-- 3. Completion rate by project
SELECT 
    p.project_name,
    COUNT(t.task_id) as total_tasks,
    SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
    ROUND(CAST(SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) AS FLOAT) / COUNT(t.task_id) * 100, 2) as completion_rate
FROM projects p
LEFT JOIN tasks t ON p.project_id = t.project_id
GROUP BY p.project_id, p.project_name;

-- 4. Projects with overdue tasks
SELECT DISTINCT p.project_name, COUNT(t.task_id) as overdue_count
FROM projects p
JOIN tasks t ON p.project_id = t.project_id
WHERE t.due_date < DATE('now') 
AND t.status NOT IN ('completed', 'cancelled')
GROUP BY p.project_id, p.project_name
HAVING COUNT(t.task_id) > 0;

-- 1. Delete old cancelled tasks
DELETE FROM tasks
WHERE status = 'cancelled'
AND created_at < DATE('now', '-30 days');

-- 2. Remove unused categories (first, identify them)
-- Check which categories have no tasks
SELECT c.category_name
FROM categories c
LEFT JOIN tasks t ON c.category_id = t.category_id
WHERE t.task_id IS NULL;

-- Then delete them
DELETE FROM categories
WHERE category_id NOT IN (
    SELECT DISTINCT category_id 
    FROM tasks 
    WHERE category_id IS NOT NULL
);

-- 3. Identify inactive users with no tasks
SELECT u.username, u.email, u.is_active
FROM users u
LEFT JOIN tasks t ON u.user_id = t.assigned_to OR u.user_id = t.created_by
WHERE u.is_active = FALSE
GROUP BY u.user_id, u.username, u.email, u.is_active
HAVING COUNT(t.task_id) = 0;

SELECT 
    p.project_name,
    u.first_name || ' ' || u.last_name as manager_name,
    COUNT(t.task_id) as total_tasks,
    SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
    ROUND(CAST(SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) AS FLOAT) / NULLIF(COUNT(t.task_id), 0) * 100, 2) as completion_percentage,
    SUM(CASE WHEN t.due_date < DATE('now') AND t.status NOT IN ('completed', 'cancelled') THEN 1 ELSE 0 END) as overdue_tasks,
    ROUND(AVG(
        CASE 
            WHEN t.status = 'completed' AND t.completed_at IS NOT NULL 
            THEN JULIANDAY(t.completed_at) - JULIANDAY(t.created_at)
            ELSE NULL 
        END
    ), 2) as avg_completion_days
FROM projects p
LEFT JOIN users u ON p.manager_id = u.user_id
LEFT JOIN tasks t ON p.project_id = t.project_id
GROUP BY p.project_id, p.project_name, u.first_name, u.last_name
ORDER BY p.project_name;

-- 1. User workload by priority
SELECT 
    u.username,
    u.first_name || ' ' || u.last_name as full_name,
    SUM(CASE WHEN t.priority = 'urgent' THEN 1 ELSE 0 END) as urgent_tasks,
    SUM(CASE WHEN t.priority = 'high' THEN 1 ELSE 0 END) as high_tasks,
    SUM(CASE WHEN t.priority = 'medium' THEN 1 ELSE 0 END) as medium_tasks,
    SUM(CASE WHEN t.priority = 'low' THEN 1 ELSE 0 END) as low_tasks,
    COUNT(t.task_id) as total_active_tasks
FROM users u
LEFT JOIN tasks t ON u.user_id = t.assigned_to 
    AND t.status NOT IN ('completed', 'cancelled')
WHERE u.is_active = TRUE
GROUP BY u.user_id, u.username, u.first_name, u.last_name
ORDER BY total_active_tasks DESC;

-- 2. Overloaded users
SELECT 
    u.username,
    u.first_name || ' ' || u.last_name as full_name,
    COUNT(t.task_id) as active_tasks
FROM users u
JOIN tasks t ON u.user_id = t.assigned_to
WHERE t.status NOT IN ('completed', 'cancelled')
AND u.is_active = TRUE
GROUP BY u.user_id, u.username, u.first_name, u.last_name
HAVING COUNT(t.task_id) > 5;

-- 3. Users with capacity
SELECT 
    u.username,
    u.first_name || ' ' || u.last_name as full_name,
    COUNT(t.task_id) as active_tasks,
    u.role
FROM users u
LEFT JOIN tasks t ON u.user_id = t.assigned_to 
    AND t.status NOT IN ('completed', 'cancelled')
WHERE u.is_active = TRUE
GROUP BY u.user_id, u.username, u.first_name, u.last_name, u.role
HAVING COUNT(t.task_id) < 2
ORDER BY active_tasks;

-- 1. Create new project
INSERT INTO projects (project_name, description, manager_id, start_date, end_date, status)
VALUES ('Q2 Feature Release', 'Major features for Q2 2024', 1, '2024-04-01', '2024-06-30', 'planning');

-- 2. Add tasks (assuming project_id = 4)
INSERT INTO tasks (title, description, project_id, assigned_to, created_by, category_id, priority, due_date) VALUES
('Implement dark mode', 'Add dark mode theme option', 4, 3, 1, 2, 'high', '2024-04-15'),
('Optimize database queries', 'Improve query performance', 4, 3, 1, 1, 'urgent', '2024-04-10'),
('Add export functionality', 'Export data to CSV/PDF', 4, 4, 1, 2, 'medium', '2024-04-20'),
('Security audit', 'Complete security assessment', 4, 3, 1, 4, 'high', '2024-04-12'),
('Update user guide', 'Document new features', 4, 5, 1, 3, 'low', '2024-04-30');

-- 3. Update progress
UPDATE tasks 
SET status = 'in_progress', updated_at = CURRENT_TIMESTAMP
WHERE title IN ('Implement dark mode', 'Optimize database queries', 'Security audit')
AND project_id = 4;

UPDATE tasks 
SET status = 'completed', 
    completed_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE title IN ('Optimize database queries', 'Security audit')
AND project_id = 4;

-- 4. Sprint report
SELECT 
    'Q2 Feature Release Sprint Report' as report_title,
    COUNT(*) as total_tasks,
    SUM(CASE WHEN status = 'todo' THEN 1 ELSE 0 END) as todo,
    SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
    ROUND(CAST(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) * 100, 2) as completion_rate,
    SUM(CASE WHEN priority IN ('urgent', 'high') THEN 1 ELSE 0 END) as high_priority_tasks
FROM tasks
WHERE project_id = 4;

-- Team performance
SELECT 
    u.first_name || ' ' || u.last_name as team_member,
    COUNT(t.task_id) as assigned_tasks,
    SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks
FROM users u
LEFT JOIN tasks t ON u.user_id = t.assigned_to AND t.project_id = 4
GROUP BY u.user_id, u.first_name, u.last_name
HAVING COUNT(t.task_id) > 0;

-- 5. Archive old completed tasks (create archive table first)
CREATE TABLE archived_tasks AS
SELECT * FROM tasks
WHERE status = 'completed'
AND completed_at < DATE('now', '-60 days');

DELETE FROM tasks
WHERE status = 'completed'
AND completed_at < DATE('now', '-60 days');