-- name: CountWorkspaces :one
SELECT COUNT(*) FROM workspaces;

-- name: ListWorkspaces :many
SELECT id, slug, name, role, created_at
FROM workspaces
ORDER BY created_at ASC;

-- name: CreateWorkspace :exec
INSERT INTO workspaces (id, slug, name, role, created_at)
VALUES ($1, $2, $3, $4, $5);

-- name: ListMembers :many
SELECT id, workspace_slug, name, email, role, status
FROM members
WHERE workspace_slug = $1
ORDER BY name ASC;

-- name: CreateMember :exec
INSERT INTO members (id, workspace_slug, name, email, role, status)
VALUES ($1, $2, $3, $4, $5, $6);

-- name: GetMemberByWorkspaceAndEmail :one
SELECT id, workspace_slug, name, email, role, status
FROM members
WHERE workspace_slug = $1 AND email = $2;

-- name: ListInvites :many
SELECT id, workspace_slug, email, role, token, created_at, status
FROM invites
WHERE workspace_slug = $1
ORDER BY created_at DESC;

-- name: GetInviteByToken :one
SELECT id, workspace_slug, email, role, token, created_at, status
FROM invites
WHERE token = $1;

-- name: CreateInvite :one
INSERT INTO invites (id, workspace_slug, email, role, token, created_at, status)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id, workspace_slug, email, role, token, created_at, status;

-- name: AcceptInvite :one
UPDATE invites
SET status = 'accepted'
WHERE token = $1
RETURNING id, workspace_slug, email, role, token, created_at, status;

-- name: ListActivities :many
SELECT id, workspace_slug, title, detail, created_at
FROM activity_entries
WHERE workspace_slug = $1
ORDER BY created_at DESC
LIMIT 40;

-- name: CreateActivity :exec
INSERT INTO activity_entries (id, workspace_slug, title, detail, created_at)
VALUES ($1, $2, $3, $4, $5);

-- name: TrimActivities :exec
DELETE FROM activity_entries
WHERE id IN (
  SELECT id
  FROM activity_entries
  WHERE activity_entries.workspace_slug = $1
  ORDER BY created_at DESC
  OFFSET 40
);

-- name: ListBoardGroups :many
SELECT id, workspace_slug, name, color, sort_order
FROM board_groups
WHERE workspace_slug = $1
ORDER BY sort_order ASC, name ASC;

-- name: GetBoardGroupByID :one
SELECT id, workspace_slug, name, color, sort_order
FROM board_groups
WHERE id = $1;

-- name: CreateBoardGroup :one
INSERT INTO board_groups (id, workspace_slug, name, color, sort_order)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, workspace_slug, name, color, sort_order;

-- name: UpdateBoardGroup :one
UPDATE board_groups
SET name = $2,
    color = $3,
    sort_order = $4
WHERE id = $1
RETURNING id, workspace_slug, name, color, sort_order;

-- name: CountBoardGroupsByWorkspace :one
SELECT COUNT(*) FROM board_groups WHERE workspace_slug = $1;

-- name: ListLabels :many
SELECT id, workspace_slug, name, color
FROM labels
WHERE workspace_slug = $1
ORDER BY name ASC;

-- name: CreateLabel :one
INSERT INTO labels (id, workspace_slug, name, color)
VALUES ($1, $2, $3, $4)
RETURNING id, workspace_slug, name, color;

-- name: ListTasks :many
SELECT id, workspace_slug, board_group_id, title, description, status, color, due_at, scheduled_start, scheduled_end, assignee_id, label_ids, attachments, comments, created_at, updated_at
FROM tasks
WHERE workspace_slug = $1
ORDER BY updated_at DESC;

-- name: GetTaskByID :one
SELECT id, workspace_slug, board_group_id, title, description, status, color, due_at, scheduled_start, scheduled_end, assignee_id, label_ids, attachments, comments, created_at, updated_at
FROM tasks
WHERE id = $1;

-- name: CreateTask :one
INSERT INTO tasks (id, workspace_slug, board_group_id, title, description, status, color, due_at, scheduled_start, scheduled_end, assignee_id, label_ids, attachments, comments, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
RETURNING id, workspace_slug, board_group_id, title, description, status, color, due_at, scheduled_start, scheduled_end, assignee_id, label_ids, attachments, comments, created_at, updated_at;

-- name: UpdateTask :one
UPDATE tasks
SET title = $2,
    description = $3,
    status = $4,
    board_group_id = $5,
    color = $6,
    due_at = $7,
    scheduled_start = $8,
    scheduled_end = $9,
    assignee_id = $10,
    label_ids = $11,
    attachments = $12,
    comments = $13,
    updated_at = $14
WHERE id = $1
RETURNING id, workspace_slug, board_group_id, title, description, status, color, due_at, scheduled_start, scheduled_end, assignee_id, label_ids, attachments, comments, created_at, updated_at;

-- name: ListCalendarEvents :many
SELECT id, workspace_slug, title, description, starts_at, ends_at, color, linked_task_id, attachments
FROM calendar_events
WHERE workspace_slug = $1
ORDER BY starts_at ASC;

-- name: GetCalendarEventByID :one
SELECT id, workspace_slug, title, description, starts_at, ends_at, color, linked_task_id, attachments
FROM calendar_events
WHERE id = $1;

-- name: CreateCalendarEvent :one
INSERT INTO calendar_events (id, workspace_slug, title, description, starts_at, ends_at, color, linked_task_id, attachments)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING id, workspace_slug, title, description, starts_at, ends_at, color, linked_task_id, attachments;

-- name: UpdateCalendarEvent :one
UPDATE calendar_events
SET title = $2,
    description = $3,
    starts_at = $4,
    ends_at = $5,
    color = $6,
    linked_task_id = $7,
    attachments = $8
WHERE id = $1
RETURNING id, workspace_slug, title, description, starts_at, ends_at, color, linked_task_id, attachments;

-- name: ListNotes :many
SELECT id, workspace_slug, title, content, updated_at
FROM notes
WHERE workspace_slug = $1
ORDER BY updated_at DESC;

-- name: GetNoteByID :one
SELECT id, workspace_slug, title, content, updated_at
FROM notes
WHERE id = $1;

-- name: CreateNote :one
INSERT INTO notes (id, workspace_slug, title, content, updated_at)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, workspace_slug, title, content, updated_at;

-- name: UpdateNote :one
UPDATE notes
SET title = $2,
    content = $3,
    updated_at = $4
WHERE id = $1
RETURNING id, workspace_slug, title, content, updated_at;

-- name: ListFocusSessions :many
SELECT id, workspace_slug, task_id, mode, started_at, completed_at, paused_at, paused_total_seconds, duration_seconds
FROM focus_sessions
WHERE workspace_slug = $1
ORDER BY started_at DESC;

-- name: GetFocusSessionByID :one
SELECT id, workspace_slug, task_id, mode, started_at, completed_at, paused_at, paused_total_seconds, duration_seconds
FROM focus_sessions
WHERE id = $1;

-- name: CreateFocusSession :one
INSERT INTO focus_sessions (id, workspace_slug, task_id, mode, started_at, completed_at, paused_at, paused_total_seconds, duration_seconds)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING id, workspace_slug, task_id, mode, started_at, completed_at, paused_at, paused_total_seconds, duration_seconds;

-- name: UpdateFocusSession :one
UPDATE focus_sessions
SET completed_at = $2,
    paused_at = $3,
    paused_total_seconds = $4
WHERE id = $1
RETURNING id, workspace_slug, task_id, mode, started_at, completed_at, paused_at, paused_total_seconds, duration_seconds;
