Mini-challenge: dashboard query TaskFlow — diagnose 3 bug từ M02 concepts

-- Naive attempt -- has 3 bugs
SELECT id, title, status, due_at,
       (due_at - now()) AS time_to_due
FROM tasks
WHERE assignee_id != 5          -- BUG 1: wrong operator
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at
LIMIT 20 OFFSET (2 - 1) * 20;  -- BUG 2: OFFSET pagination
-- BUG 3: project_name missing, needs JOIN with projects table

-- BEFORE (wrong): tra ve moi task KHONG phai cua user 5
WHERE assignee_id != 5

-- AFTER (correct): chi tra ve task cua user 5
WHERE assignee_id = 5

-- Include unassigned tasks in "re-assign" view
WHERE (assignee_id != 5 OR assignee_id IS NULL)

-- BEFORE (OFFSET): cost tang tuyen tinh, page 1000 skip 19980 rows
LIMIT 20 OFFSET (page - 1) * 20

-- AFTER (keyset): cursor = (due_at, id) cua row cuoi page truoc
-- Page 1: cursor = NULL (no previous row)
-- Page 2+: cursor = last row of previous page
AND (
  $cursor_due_at IS NULL
  OR (due_at, id) > ($cursor_due_at, $cursor_id)
)
ORDER BY due_at ASC NULLS LAST, id ASC
LIMIT 20

-- Tease: version sau khi hoc Module 3
SELECT t.id, t.title, t.status, t.due_at,
       p.name AS project_name  -- JOIN column from projects table
FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE t.assignee_id = 5
  -- ...rest of WHERE clause

SELECT
  id,
  title,
  status,
  due_at,
  -- days_to_due: so ngay con lai, am neu da qua han
  EXTRACT(DAY FROM (due_at - now()))::int AS days_to_due,
  project_id  -- placeholder, Module 3 se JOIN ra project_name
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
  -- Keyset pagination: cursor truyen vao tu client
  -- $cursor_due_at = NULL cho page 1, timestamptz cho page 2+
  -- $cursor_id = 0 cho page 1, id cua row cuoi cho page 2+
  AND (
    $cursor_due_at IS NULL
    OR (due_at, id) > ($cursor_due_at, $cursor_id)
  )
ORDER BY due_at ASC NULLS LAST, id ASC
LIMIT 20;

// api/dashboard/tasks.ts
type CursorPayload = { dueAt: string; id: number };

async function getDashboardTasks(
  pool: Pool,
  userId: number,
  cursor: CursorPayload | null
) {
  const sql = `
    SELECT id, title, status, due_at,
           EXTRACT(DAY FROM (due_at - now()))::int AS days_to_due,
           project_id
    FROM tasks
    WHERE assignee_id = $1
      AND status IN ('todo', 'doing')
      AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
      AND ($2::timestamptz IS NULL OR (due_at, id) > ($2, $3))
    ORDER BY due_at ASC NULLS LAST, id ASC
    LIMIT 20
  `;

  // Page 1: cursor = null -> $2 = null, $3 = 0 (ignored when $2 is null)
  // Page N: cursor carries dueAt + id from last row of previous page
  const params = [userId, cursor?.dueAt ?? null, cursor?.id ?? 0];
  const result = await pool.query(sql, params);

  // Encode next cursor from last row
  const lastRow = result.rows[result.rows.length - 1];
  const nextCursor = lastRow
    ? Buffer.from(
        JSON.stringify({ dueAt: lastRow.due_at, id: lastRow.id })
      ).toString('base64')
    : null;

  return { items: result.rows, nextCursor };
}

-- Seed 100k tasks cho user 5 voi due date ngau nhien
INSERT INTO tasks (project_id, assignee_id, title, status, due_at)
SELECT
  (random() * 5 + 1)::int,
  5,
  'Task ' || gs,
  (ARRAY['todo', 'doing', 'done', 'archived'])[(random() * 3 + 1)::int],
  now() + (random() * 14 - 7) * INTERVAL '1 day'
FROM generate_series(1, 100000) AS gs;

-- Bat timing
\timing on

-- OFFSET page 1: skip 0 rows
SELECT * FROM tasks
WHERE assignee_id = 5 AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at NULLS LAST, id
LIMIT 20 OFFSET 0;
-- ~15ms

-- OFFSET page 100: skip 1980 rows
SELECT * FROM tasks
WHERE assignee_id = 5 AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at NULLS LAST, id
LIMIT 20 OFFSET 1980;
-- ~80ms (khoang 5x cham hon page 1)

-- Keyset page 100: seek thang den cursor, khong skip
-- (thay $cursor_due_at va $cursor_id bang gia tri thuc tu page 99)
SELECT * FROM tasks
WHERE assignee_id = 5 AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
  AND (due_at, id) > ('2025-05-06 10:00:00', 1980)
ORDER BY due_at NULLS LAST, id
LIMIT 20;
-- ~12ms (xap xi constant, khong phu thuoc page number)

-- dashboard_naive.sql
-- Bug 1: assignee_id != 5 should be = 5
-- Bug 2: OFFSET pagination, replace with keyset
-- Bug 3: missing project_name, needs JOIN (Module 3)
SELECT id, title, status, due_at, (due_at - now()) AS time_to_due
FROM tasks
WHERE assignee_id != 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at
LIMIT 20 OFFSET 0;

-- dashboard_fixed.sql
-- Fixed: assignee_id = 5 (Bug 1)
-- Fixed: keyset pagination (Bug 2)
-- TODO (Module 3): JOIN projects p ON tasks.project_id = p.id -> project_name
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now()))::int AS days_to_due,
       project_id  -- replace with p.name after Module 3
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
  AND ($cursor_due_at IS NULL OR (due_at, id) > ($cursor_due_at, $cursor_id))
ORDER BY due_at ASC NULLS LAST, id ASC
LIMIT 20;