use crate::ids::WorkspaceId;
use anyhow::Result;
use chrono::Local;
use sqlx::{QueryBuilder, Sqlite, SqliteConnection};
use crate::choices::{TaskPriority, TaskStatus};
use crate::db::task_from_row;
use crate::labels::ensure_label_exists_in_workspace;
use crate::projects::resolve_existing_project_in_workspace;
use crate::queue::{now_seconds, queue_order};
use crate::refs::DisplayRefContext;
use super::fragments;
use super::hydration::build_task_list_items;
use super::sorting::push_sort;
use super::{
SortDirection, TaskAvailabilityFilter, TaskFilters, TaskListItem, TaskQueryMode, TaskSort,
};
pub async fn list_task_items_in_workspace(
conn: &mut SqliteConnection,
workspace_id: &WorkspaceId,
filters: TaskFilters,
mode: TaskQueryMode,
sort: TaskSort,
direction: SortDirection,
) -> Result<Vec<TaskListItem>> {
let display_refs = DisplayRefContext::for_workspace(conn, workspace_id).await?;
list_task_items_with_display_refs(
conn,
workspace_id,
filters,
mode,
sort,
direction,
&display_refs,
)
.await
}
pub async fn list_task_items_with_display_refs(
conn: &mut SqliteConnection,
workspace_id: &WorkspaceId,
filters: TaskFilters,
mode: TaskQueryMode,
sort: TaskSort,
direction: SortDirection,
display_refs: &DisplayRefContext,
) -> Result<Vec<TaskListItem>> {
if let Some(status) = filters.status.as_deref() {
TaskStatus::parse(status)?;
}
for status in &filters.statuses {
TaskStatus::parse(status)?;
}
let hide_done = filters.hide_done && filters.status.is_none() && filters.statuses.is_empty();
if let Some(priority) = filters.priority.as_deref() {
TaskPriority::parse(priority)?;
}
let project = if let Some(project) = filters.project.as_deref() {
Some(resolve_existing_project_in_workspace(conn, workspace_id, project).await?)
} else {
None
};
let label = if let Some(label) = filters.label.as_deref() {
Some(ensure_label_exists_in_workspace(conn, workspace_id, label).await?)
} else {
None
};
let mut query = QueryBuilder::<Sqlite>::new(
"SELECT t.id, t.workspace_id, t.title, t.description, t.project_id,
p.key AS project_key, p.prefix AS project_prefix, t.status, t.priority, t.created_at, t.updated_at,
t.queue_activity_at, t.available_at, t.due_on, t.deleted, t.is_epic
FROM tasks t JOIN projects p ON p.workspace_id = t.workspace_id AND p.id = t.project_id",
);
let mut filters_added = 0;
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.workspace_id = ");
query.push_bind(workspace_id.to_string());
if filters.deleted_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.deleted = 1");
} else if !filters.include_deleted {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.deleted = 0");
}
if hide_done {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.status NOT IN ('done', 'canceled')");
}
if let Some(project) = project {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.project_id = ");
query.push_bind(project.id);
}
if let Some(status) = filters.status {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.status = ");
query.push_bind(status);
}
if !filters.statuses.is_empty() {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.status IN (");
let mut separated = query.separated(", ");
for status in filters.statuses {
separated.push_bind(status);
}
separated.push_unseparated(")");
}
if let Some(priority) = filters.priority {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.priority = ");
query.push_bind(priority);
}
if let Some(label) = label {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.id IN (SELECT tl.task_id FROM task_labels tl INDEXED BY idx_task_labels_workspace_label_task WHERE tl.workspace_id = ");
query.push_bind(workspace_id);
query.push(" AND tl.label = ");
query.push_bind(label);
query.push(")");
}
push_availability_filter(&mut query, &mut filters_added, filters.availability);
if filters.overdue_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.deleted = 0 AND t.status NOT IN ('done', 'canceled') AND t.due_on != '' AND t.due_on < ");
query.push_bind(Local::now().date_naive().format("%Y-%m-%d").to_string());
}
if filters.conflicts_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push("EXISTS (SELECT 1 FROM conflicts c WHERE c.workspace_id = t.workspace_id AND c.task_id = t.id AND c.resolved = 0)");
}
if !filters.task_ids.is_empty() {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.id IN (");
let mut separated = query.separated(", ");
for task_id in &filters.task_ids {
separated.push_bind(task_id);
}
separated.push_unseparated(")");
}
if filters.ready_only || filters.blocked_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push(fragments::open_task_clause("t"));
}
if filters.ready_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push(format!(
"NOT EXISTS (SELECT 1 FROM task_dependencies d
JOIN tasks blocker ON blocker.workspace_id = d.workspace_id AND blocker.id = d.depends_on_task_id
WHERE d.workspace_id = t.workspace_id AND d.task_id = t.id
AND {})",
fragments::open_task_clause("blocker"),
));
}
if filters.blocked_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push(format!(
"EXISTS (SELECT 1 FROM task_dependencies d
JOIN tasks blocker ON blocker.workspace_id = d.workspace_id AND blocker.id = d.depends_on_task_id
WHERE d.workspace_id = t.workspace_id AND d.task_id = t.id
AND {})",
fragments::open_task_clause("blocker"),
));
}
if filters.epics_only {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.is_epic = 1");
}
if filters.exclude_epics {
push_filter_prefix(&mut query, &mut filters_added);
query.push("t.is_epic = 0");
}
if let Some(search) = filters.search.filter(|search| !search.is_empty()) {
push_filter_prefix(&mut query, &mut filters_added);
query.push("(t.title LIKE ");
query.push_bind(format!("%{search}%"));
query.push(" OR t.description LIKE ");
query.push_bind(format!("%{search}%"));
query.push(")");
}
if mode == TaskQueryMode::Flat {
push_sort(&mut query, sort, direction);
}
let rows = query.build().fetch_all(&mut *conn).await?;
let tasks = rows
.into_iter()
.map(|row| task_from_row(&row))
.collect::<Result<Vec<_>>>()?;
let now_seconds = now_seconds();
let local_today = Local::now().date_naive();
let mut items = build_task_list_items(
conn,
workspace_id,
tasks,
now_seconds,
local_today,
display_refs,
)
.await?;
if mode == TaskQueryMode::RankedQueue {
items.sort_by(|a, b| queue_order((&a.task, a.queue), (&b.task, b.queue)));
}
if !filters.task_ids.is_empty() {
let order = filters.task_ids;
items.sort_by_key(|item| {
order
.iter()
.position(|task_id| task_id == &item.task.id)
.unwrap_or(order.len())
});
}
Ok(items)
}
fn push_availability_filter(
query: &mut QueryBuilder<Sqlite>,
filters_added: &mut usize,
availability: TaskAvailabilityFilter,
) {
match availability {
TaskAvailabilityFilter::All => {}
TaskAvailabilityFilter::Available => {
push_filter_prefix(query, filters_added);
query.push("(t.available_at = '' OR t.available_at <= ");
query.push_bind(crate::ids::now());
query.push(")");
}
TaskAvailabilityFilter::Upcoming => {
push_filter_prefix(query, filters_added);
query.push("t.deleted = 0 AND t.status NOT IN ('done', 'canceled') AND t.available_at != '' AND t.available_at > ");
query.push_bind(crate::ids::now());
}
}
}
fn push_filter_prefix(query: &mut QueryBuilder<Sqlite>, filters: &mut usize) {
if *filters == 0 {
query.push(" WHERE ");
} else {
query.push(" AND ");
}
*filters += 1;
}
#[cfg(test)]
#[path = "tasks_tests.rs"]
mod tests;