Activity 2: TaskFlow Startup Data Crisis

-- Teams table: Organizational structure
CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    budget DECIMAL(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Users table: Team members and stakeholders
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL CHECK(role IN ('Developer', 'Designer', 'Manager', 'QA', 'Product')),
    team_id INTEGER REFERENCES teams(team_id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Projects table: High-level work containers
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    owner_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    team_id INTEGER REFERENCES teams(team_id) ON DELETE CASCADE,
    status VARCHAR(50) DEFAULT 'Planning' CHECK(status IN ('Planning', 'Active', 'On Hold', 'Completed', 'Cancelled')),
    deadline DATE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tasks table: Individual work items
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    assignee_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    project_id INTEGER REFERENCES projects(project_id) ON DELETE CASCADE,
    status VARCHAR(50) DEFAULT 'Open' CHECK(status IN ('Open', 'In Progress', 'Blocked', 'Review', 'Done')),
    priority VARCHAR(20) DEFAULT 'Medium' CHECK(priority IN ('Low', 'Medium', 'High', 'Urgent')),
    due_date DATE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Task comments: Collaboration and updates
CREATE TABLE task_comments (
    comment_id SERIAL PRIMARY KEY,
    task_id INTEGER REFERENCES tasks(task_id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Performance indexes for production speed
CREATE INDEX idx_users_team ON users(team_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_projects_owner ON projects(owner_id);
CREATE INDEX idx_projects_team ON projects(team_id);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_priority ON tasks(priority);
CREATE INDEX idx_task_comments_task ON task_comments(task_id);

-- Insert teams (Engineering, Product, Design)
INSERT INTO teams (name, department, budget) VALUES
('Platform Engineering', 'Engineering', 150000.00),
('Mobile Team', 'Engineering', 120000.00),
('Product Strategy', 'Product', 90000.00),
('UX Design', 'Design', 80000.00),
('QA & Testing', 'Engineering', 70000.00);

-- Insert team members (15+ users)
INSERT INTO users (name, email, role, team_id) VALUES
('Sarah Chen', 'sarah@taskflow.io', 'Manager', 1),
('Mike Rodriguez', 'mike@taskflow.io', 'Developer', 1),
('Emma Watson', 'emma@taskflow.io', 'Developer', 1),
('James Kim', 'james@taskflow.io', 'Developer', 2),
('Lisa Anderson', 'lisa@taskflow.io', 'Designer', 2),
('David Park', 'david@taskflow.io', 'Product', 3),
('Rachel Green', 'rachel@taskflow.io', 'Designer', 4),
('Tom Baker', 'tom@taskflow.io', 'QA', 5),
('Nina Patel', 'nina@taskflow.io', 'Developer', 1),
('Alex Turner', 'alex@taskflow.io', 'Product', 3),
('Sophie Martin', 'sophie@taskflow.io', 'Designer', 4),
('Carlos Santos', 'carlos@taskflow.io', 'Developer', 2),
('Maya Johnson', 'maya@taskflow.io', 'QA', 5),
('Oliver Smith', 'oliver@taskflow.io', 'Manager', 2),
('Zoe Williams', 'zoe@taskflow.io', 'Developer', 1);

-- Insert projects (10+ active projects)
INSERT INTO projects (name, description, owner_id, team_id, status, deadline) VALUES
('Mobile App v2.0', 'Complete redesign of mobile interface', 14, 2, 'Active', '2024-03-15'),
('API Performance Optimization', 'Reduce API latency by 50%', 1, 1, 'Active', '2024-02-28'),
('User Dashboard Redesign', 'New analytics dashboard for users', 7, 4, 'Planning', '2024-04-01'),
('Real-time Collaboration', 'Add live editing capabilities', 2, 1, 'Active', '2024-03-30'),
('Security Audit 2024', 'Complete security review and fixes', 1, 1, 'Active', '2024-02-15'),
('Enterprise Features', 'Build enterprise customer features', 6, 3, 'Planning', '2024-05-01'),
('Performance Testing Suite', 'Automated performance testing', 8, 5, 'Active', '2024-03-10'),
('Dark Mode Implementation', 'Add dark mode to all interfaces', 11, 4, 'Active', '2024-02-20'),
('Data Export Tools', 'Let users export their data', 10, 3, 'Planning', '2024-04-15'),
('Notification System v3', 'Rebuild notification infrastructure', 9, 1, 'Active', '2024-03-05');

-- Insert tasks (30+ tasks across projects)
INSERT INTO tasks (title, description, assignee_id, project_id, status, priority, due_date) VALUES
-- Mobile App v2.0 tasks
('Design new navigation system', 'Create wireframes for mobile nav', 5, 1, 'Done', 'High', '2024-01-20'),
('Implement swipe gestures', 'Add gesture controls to UI', 4, 1, 'In Progress', 'High', '2024-02-10'),
('Test on iOS devices', 'QA testing on iPhone and iPad', 8, 1, 'Open', 'Medium', '2024-02-25'),
-- API Performance tasks
('Profile slow API endpoints', 'Identify performance bottlenecks', 2, 2, 'Done', 'Urgent', '2024-01-25'),
('Implement caching layer', 'Add Redis caching for common queries', 9, 2, 'In Progress', 'Urgent', '2024-02-15'),
('Load testing', 'Test API under production load', 13, 2, 'Open', 'High', '2024-02-20'),
-- Dashboard Redesign tasks
('User research interviews', 'Talk to 10 enterprise customers', 10, 3, 'In Progress', 'High', '2024-02-05'),
('Create dashboard mockups', 'Design 5 dashboard variations', 7, 3, 'Open', 'Medium', '2024-02-15'),
('Analytics integration', 'Connect to data analytics service', 3, 3, 'Open', 'Medium', '2024-03-01'),
-- Real-time Collaboration tasks
('WebSocket infrastructure', 'Set up WebSocket server', 2, 4, 'Done', 'Urgent', '2024-01-30'),
('Conflict resolution logic', 'Handle concurrent edits', 9, 4, 'In Progress', 'High', '2024-02-18'),
('Presence indicators', 'Show who is online', 15, 4, 'Open', 'Medium', '2024-02-25'),
-- Security Audit tasks
('Dependency vulnerability scan', 'Check all npm packages', 2, 5, 'Done', 'Urgent', '2024-01-28'),
('Implement rate limiting', 'Prevent API abuse', 9, 5, 'In Progress', 'Urgent', '2024-02-10'),
('Security documentation', 'Write security best practices', 1, 5, 'Open', 'Medium', '2024-02-14'),
-- Enterprise Features tasks
('SSO integration', 'Add SAML authentication', 3, 6, 'Open', 'High', '2024-03-01'),
('Advanced permissions', 'Role-based access control', 15, 6, 'Open', 'High', '2024-03-15'),
('Audit logging', 'Track all user actions', 2, 6, 'Open', 'Medium', '2024-04-01'),
-- Performance Testing tasks
('Set up load testing tools', 'Configure K6 and JMeter', 13, 7, 'Done', 'High', '2024-01-22'),
('Create test scenarios', 'Write realistic load test cases', 8, 7, 'In Progress', 'High', '2024-02-08'),
('CI/CD integration', 'Run tests on every deploy', 13, 7, 'Open', 'Medium', '2024-02-28'),
-- Dark Mode tasks
('Design dark color palette', 'Choose accessible dark colors', 11, 8, 'Done', 'High', '2024-01-18'),
('Update CSS variables', 'Implement theme switching', 4, 8, 'In Progress', 'High', '2024-02-12'),
('Test accessibility', 'Verify WCAG compliance', 8, 8, 'Open', 'Medium', '2024-02-18'),
-- Data Export tasks
('Define export formats', 'Support JSON, CSV, Excel', 6, 9, 'Open', 'Medium', '2024-03-01'),
('Build export API', 'Create download endpoints', 3, 9, 'Open', 'High', '2024-03-20'),
-- Notification System tasks
('Database schema design', 'Design notification tables', 9, 10, 'Done', 'Urgent', '2024-01-25'),
('Email templates', 'Create responsive email designs', 11, 10, 'In Progress', 'High', '2024-02-15'),
('Push notification service', 'Integrate Firebase Cloud Messaging', 12, 10, 'Open', 'High', '2024-02-28'),
('Notification preferences UI', 'Let users customize notifications', 4, 10, 'Open', 'Medium', '2024-03-03');

-- Add some task comments for realism
INSERT INTO task_comments (task_id, user_id, comment) VALUES
(2, 4, 'Making good progress - swipe left/right working!'),
(5, 1, 'Redis caching reduced response time from 800ms to 120ms'),
(11, 15, 'Need to handle edge case when 3+ users edit simultaneously'),
(14, 9, 'Rate limiting implemented - max 100 requests per minute'),
(20, 13, 'Load tests show we can handle 10K concurrent users'),
(28, 9, 'Schema supports batching 1000+ notifications efficiently');

-- Confirm data loaded correctly
SELECT
    'Teams' as entity,
    COUNT(*) as count
FROM teams
UNION ALL
SELECT 'Users', COUNT(*) FROM users
UNION ALL
SELECT 'Projects', COUNT(*) FROM projects
UNION ALL
SELECT 'Tasks', COUNT(*) FROM tasks
UNION ALL
SELECT 'Comments', COUNT(*) FROM task_comments;

-- Expected: 5 teams, 15 users, 10 projects, 30 tasks, 6 comments

-- Single row insert: Add one new user
INSERT INTO users (name, email, role, team_id)
VALUES ('Anna Martinez', 'anna@taskflow.io', 'Developer', 1);

-- Multi-row insert: Add multiple tasks at once
INSERT INTO tasks (title, assignee_id, project_id, status, priority, due_date) VALUES
('Write integration tests', 3, 2, 'Open', 'High', '2024-02-18'),
('Update API documentation', 9, 2, 'Open', 'Medium', '2024-02-20'),
('Code review for caching', 1, 2, 'Open', 'High', '2024-02-16');

-- INSERT with SELECT: Copy data from another table
INSERT INTO tasks (title, description, assignee_id, project_id, status, priority)
SELECT
    'Review: ' || name,
    'Review project completion',
    owner_id,
    project_id,
    'Open',
    'High'
FROM projects
WHERE status = 'Completed';

-- INSERT with DEFAULT values
INSERT INTO projects (name, owner_id, team_id, deadline)
VALUES ('Emergency Bug Fixes', 1, 1, '2024-02-10');
-- status automatically gets 'Planning', created_at gets NOW()

-- Basic SELECT: Get all projects
SELECT * FROM projects;

-- Specific columns: Get user contact info
SELECT name, email, role FROM users;

-- SELECT with WHERE: Find high-priority tasks
SELECT title, status, due_date
FROM tasks
WHERE priority = 'Urgent';

-- SELECT with multiple conditions: Active projects with upcoming deadlines
SELECT name, status, deadline
FROM projects
WHERE status = 'Active'
AND deadline < '2024-03-01';

-- SELECT with JOIN: Get tasks with assignee names
SELECT
    t.title,
    u.name as assignee,
    t.status,
    t.priority
FROM tasks t
JOIN users u ON t.assignee_id = u.user_id
WHERE t.status != 'Done'
ORDER BY t.priority DESC;

-- Single column update: Mark task complete
UPDATE tasks
SET status = 'Done'
WHERE task_id = 15;

-- Multiple column update: Update task with timestamp
UPDATE tasks
SET status = 'In Progress',
    updated_at = NOW()
WHERE task_id = 20;

-- UPDATE with WHERE: Mark all overdue tasks as urgent
UPDATE tasks
SET priority = 'Urgent'
WHERE due_date < CURRENT_DATE
AND status NOT IN ('Done', 'Blocked');

-- UPDATE with calculation: Increase team budgets by 10%
UPDATE teams
SET budget = budget * 1.10
WHERE department = 'Engineering';

-- UPDATE with JOIN (PostgreSQL): Assign tasks to team managers
UPDATE tasks
SET assignee_id = u.user_id
FROM users u
WHERE tasks.project_id IN (1, 2, 3)
AND tasks.assignee_id IS NULL
AND u.role = 'Manager';

-- DELETE with WHERE: Remove specific task
DELETE FROM tasks
WHERE task_id = 50;

-- DELETE with condition: Remove completed tasks older than 90 days
DELETE FROM tasks
WHERE status = 'Done'
AND updated_at < NOW() - INTERVAL '90 days';

-- DELETE with subquery: Remove tasks from cancelled projects
DELETE FROM tasks
WHERE project_id IN (
    SELECT project_id
    FROM projects
    WHERE status = 'Cancelled'
);

-- Safe deletion practice: Always test with SELECT first!
-- WRONG: DELETE FROM tasks WHERE status = 'Done';
-- RIGHT:
SELECT COUNT(*) FROM tasks WHERE status = 'Done';  -- See how many will be deleted
-- If count looks right, then run DELETE
DELETE FROM tasks WHERE status = 'Done';

-- ON DELETE CASCADE: Deleting a project automatically deletes its tasks
-- (Already configured in our schema)

-- ON DELETE SET NULL: Deleting a user sets assignee_id to NULL
-- (Preserves tasks but removes assignment)

-- ON DELETE RESTRICT: Prevents deletion if dependencies exist
-- Example: Can't delete a team if it has users
ALTER TABLE teams
ADD CONSTRAINT prevent_delete_with_users
CHECK (team_id NOT IN (SELECT team_id FROM users WHERE team_id IS NOT NULL));

-- ✅ WORKING: Insert 2 new developers
INSERT INTO users (name, email, role, team_id) VALUES
('Jessica Lee', 'jessica@taskflow.io', 'Developer', 1),
('Marcus Brown', 'marcus@taskflow.io', 'QA', 5);

-- Verify they were added
SELECT name, email, role FROM users WHERE user_id > 15;

-- 🎯 YOUR TURN: Add 1 more designer to the UX Design team (team_id = 4)
-- TODO: Write INSERT statement for "Taylor Kim", "taylor@taskflow.io", "Designer", team 4
-- Expected result: 1 row inserted

-- Then assign them their first task
-- TODO: Insert task "Design onboarding flow" for Taylor on project 3, priority High, due 2024-02-28
-- Expected result: 1 row inserted

-- First, see which tasks are overdue and not urgent
SELECT
    task_id,
    title,
    due_date,
    priority,
    status
FROM tasks
WHERE due_date < CURRENT_DATE
AND status NOT IN ('Done', 'Blocked')
AND priority != 'Urgent';

-- 🎯 YOUR TURN: Update these overdue tasks to Urgent priority
-- TODO: Write UPDATE statement to set priority = 'Urgent' for overdue tasks
-- HINT: Use WHERE with due_date < CURRENT_DATE and status conditions
-- Expected result: 3-5 rows updated

-- Verify the update worked
SELECT task_id, title, priority, due_date
FROM tasks
WHERE priority = 'Urgent'
ORDER BY due_date;

-- First, check how many tasks will be deleted (ALWAYS do this!)
SELECT
    COUNT(*) as tasks_to_delete,
    MIN(updated_at) as oldest_task,
    MAX(updated_at) as newest_task
FROM tasks
WHERE status = 'Done'
AND updated_at < NOW() - INTERVAL '30 days';

-- 🎯 YOUR TURN: Delete old completed tasks
-- TODO: Write DELETE statement for tasks with status = 'Done' older than 30 days
-- HINT: Use WHERE status = 'Done' AND updated_at < NOW() - INTERVAL '30 days'
-- Expected result: 2-4 rows deleted

-- Verify tasks were deleted
SELECT COUNT(*) as remaining_done_tasks
FROM tasks
WHERE status = 'Done';

-- See which tasks need assignment
SELECT
    t.task_id,
    t.title,
    p.name as project_name,
    u.name as project_owner
FROM tasks t
JOIN projects p ON t.project_id = p.project_id
JOIN users u ON p.owner_id = u.user_id
WHERE t.assignee_id IS NULL
AND t.status = 'Open';

-- 🎯 YOUR TURN: Assign these tasks to project owners
-- TODO: Write UPDATE with JOIN to set assignee_id = project owner_id
-- HINT: UPDATE tasks SET assignee_id = (SELECT owner_id FROM projects WHERE...)
-- Expected result: 3-6 rows updated

SELECT COUNT(*) as new_users FROM users WHERE user_id > 15;
-- Expected: 3 (Jessica, Marcus, Taylor)

SELECT COUNT(*) as taylors_tasks
FROM tasks t
JOIN users u ON t.assignee_id = u.user_id
WHERE u.name = 'Taylor Kim';
-- Expected: 1 (Design onboarding flow)

SELECT
    priority,
    COUNT(*) as task_count
FROM tasks
GROUP BY priority
ORDER BY
    CASE priority
        WHEN 'Urgent' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
    END;
-- Expected: Urgent count increased by 3-5 tasks

SELECT
    status,
    COUNT(*) as count,
    MIN(updated_at) as oldest
FROM tasks
GROUP BY status;
-- Expected: No 'Done' tasks older than 30 days

SELECT
    'Active Projects' as metric,
    COUNT(*) as value
FROM projects
WHERE status = 'Active'
UNION ALL
SELECT 'Open Tasks', COUNT(*) FROM tasks WHERE status = 'Open'
UNION ALL
SELECT 'Tasks In Progress', COUNT(*) FROM tasks WHERE status = 'In Progress'
UNION ALL
SELECT 'Total Users', COUNT(*) FROM users
UNION ALL
SELECT 'Total Teams', COUNT(*) FROM teams;

-- Expected: Healthy metrics for investor demo

BEGIN;
    INSERT INTO projects (...) VALUES (...);
    INSERT INTO tasks (...) VALUES (...);
    UPDATE teams SET budget = budget - 5000 WHERE team_id = 1;
COMMIT;  -- All succeed together, or all fail together