//! Task read queries: the plain listings and the dynamic filter builder.

use goingson_core::{
    ContactId, CoreError, DbValue, ProjectId, Result, SortDirection, Task, TaskFilterQuery, TaskId,
    TaskSortColumn, UserId,
};
use rusqlite::{Connection, params_from_iter};

use crate::utils::query_all;

use super::fetch::{query_tasks, rows_to_tasks};
use super::row::{
    TASK_SELECT_COLUMNS, TaskRowWithProject, sort_column_nulls_last, sort_column_sql,
};

pub(super) fn list_all(conn: &Connection, user_id: UserId) -> Result<Vec<Task>> {
    let sql = format!(
        r"
        SELECT {TASK_SELECT_COLUMNS}
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
        LEFT JOIN contacts ct ON ct.id = t.contact_id
        WHERE t.user_id = ? AND t.status != 'Deleted'
        ORDER BY t.urgency DESC, t.created_at DESC
        "
    );
    query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
}

pub(super) fn list_all_for_backup(conn: &Connection, user_id: UserId) -> Result<Vec<Task>> {
    let sql = format!(
        r"
        SELECT {TASK_SELECT_COLUMNS}
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
        LEFT JOIN contacts ct ON ct.id = t.contact_id
        WHERE t.user_id = ?
        ORDER BY t.urgency DESC, t.created_at DESC
        "
    );
    query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
}

pub(super) fn list_by_project(
    conn: &Connection,
    user_id: UserId,
    project_id: ProjectId,
) -> Result<Vec<Task>> {
    let sql = format!(
        r"
        SELECT {TASK_SELECT_COLUMNS}
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
        LEFT JOIN contacts ct ON ct.id = t.contact_id
        WHERE t.user_id = ? AND t.project_id = ? AND t.status != 'Deleted'
        ORDER BY t.urgency DESC, t.created_at DESC
        "
    );
    query_tasks(
        conn,
        &sql,
        &[
            user_id.to_string(),
            user_id.to_string(),
            project_id.to_string(),
        ],
    )
}

pub(super) fn list_by_contact(
    conn: &Connection,
    user_id: UserId,
    contact_id: ContactId,
) -> Result<Vec<Task>> {
    let sql = format!(
        r"
        SELECT {TASK_SELECT_COLUMNS}
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
        LEFT JOIN contacts ct ON ct.id = t.contact_id
        WHERE t.user_id = ? AND t.contact_id = ? AND t.status != 'Deleted'
        ORDER BY t.created_at DESC
        "
    );
    query_tasks(
        conn,
        &sql,
        &[
            user_id.to_string(),
            user_id.to_string(),
            contact_id.to_string(),
        ],
    )
}

pub(super) fn list_filtered(
    conn: &Connection,
    user_id: UserId,
    query: &TaskFilterQuery,
) -> Result<(Vec<Task>, i64)> {
    // Build dynamic WHERE clause
    let mut conditions = vec![
        "t.user_id = ?".to_string(),
        "t.status != 'Deleted'".to_string(),
    ];
    let mut bind_values: Vec<String> = vec![user_id.to_string()];

    // Status filter
    if let Some(ref status) = query.status {
        conditions.push("t.status = ?".to_string());
        bind_values.push(status.db_value().to_string());
    }

    // Project filter
    if let Some(ref project_id) = query.project_id {
        conditions.push("t.project_id = ?".to_string());
        bind_values.push(project_id.to_string());
    }

    // Priority filter
    if let Some(ref priority) = query.priority {
        conditions.push("t.priority = ?".to_string());
        bind_values.push(priority.db_value().to_string());
    }

    // Milestone filter
    if let Some(ref milestone_id) = query.milestone_id {
        conditions.push("t.milestone_id = ?".to_string());
        bind_values.push(milestone_id.to_string());
    }

    // Snoozed filter - hide snoozed tasks unless explicitly requested
    if !query.show_snoozed {
        conditions.push(
            "(t.snoozed_until IS NULL OR datetime(t.snoozed_until) <= datetime('now'))".to_string(),
        );
    }

    // Waiting only filter
    if query.waiting_only {
        conditions.push("t.waiting_for_response = 1".to_string());
    }

    let where_clause = conditions.join(" AND ");

    // Get total count for pagination
    let count_sql = format!("SELECT COUNT(*) FROM tasks t WHERE {where_clause}");
    let total: i64 = conn
        .query_row(&count_sql, params_from_iter(&bind_values), |row| row.get(0))
        .map_err(CoreError::database)?;

    if total == 0 {
        return Ok((vec![], 0));
    }

    // Build paginated query with parameterized LIMIT/OFFSET.
    //
    // Defense-in-depth: clamp before binding. A negative LIMIT means
    // "unbounded" in SQLite and an absurd LIMIT/negative OFFSET could blow up
    // the result set. The UI already paginates; this is a backstop against a
    // bad caller, not the primary guard.
    const MAX_PAGE_LIMIT: i64 = 1000;
    let mut pagination_binds: Vec<i64> = Vec::new();
    let limit = query.limit.map(|l| l.clamp(0, MAX_PAGE_LIMIT));
    let offset = query.offset.map(|o| o.max(0));
    let pagination = match (limit, offset) {
        (Some(limit), Some(offset)) => {
            pagination_binds.push(limit);
            pagination_binds.push(offset);
            " LIMIT ? OFFSET ?".to_string()
        }
        (Some(limit), None) => {
            pagination_binds.push(limit);
            " LIMIT ?".to_string()
        }
        _ => String::new(),
    };

    // Build dynamic ORDER BY clause
    let sort_column = query.sort_column.unwrap_or(TaskSortColumn::Urgency);
    let sort_direction = query.sort_direction.unwrap_or_else(|| {
        // Default to DESC for urgency (highest first), ASC for others
        if sort_column == TaskSortColumn::Urgency {
            SortDirection::Desc
        } else {
            SortDirection::Asc
        }
    });

    let order_by = if sort_column_nulls_last(sort_column) {
        // For nullable columns (project, due), put NULLs last regardless of sort direction
        format!(
            "{} {} NULLS LAST, t.created_at DESC",
            sort_column_sql(sort_column),
            sort_direction.sql()
        )
    } else {
        format!(
            "{} {}, t.created_at DESC",
            sort_column_sql(sort_column),
            sort_direction.sql()
        )
    };

    let sql = format!(
        r"
        SELECT {TASK_SELECT_COLUMNS}
        FROM tasks t
        LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ?
        LEFT JOIN contacts ct ON ct.id = t.contact_id
        WHERE {where_clause}
        ORDER BY {order_by}{pagination}
        "
    );

    // Bind, in the order the placeholders appear: user_id for the JOIN, then
    // every WHERE clause value, then LIMIT/OFFSET.
    let mut binds: Vec<rusqlite::types::Value> =
        Vec::with_capacity(bind_values.len() + pagination_binds.len() + 1);
    binds.push(user_id.to_string().into());
    binds.extend(bind_values.into_iter().map(rusqlite::types::Value::from));
    binds.extend(
        pagination_binds
            .into_iter()
            .map(rusqlite::types::Value::from),
    );

    let rows = query_all(
        conn,
        &sql,
        params_from_iter(binds),
        TaskRowWithProject::from_row,
    )?;

    let tasks = rows_to_tasks(conn, rows)?;
    Ok((tasks, total))
}

pub(super) fn list_recurrence_chain(
    conn: &Connection,
    root_id: TaskId,
    user_id: UserId,
) -> Result<Vec<Task>> {
    let sql = format!(
        "SELECT {TASK_SELECT_COLUMNS} FROM tasks t LEFT JOIN projects p ON t.project_id = p.id AND p.user_id = ? LEFT JOIN contacts ct ON ct.id = t.contact_id WHERE (t.recurrence_parent_id = ? OR t.id = ?) AND t.user_id = ? ORDER BY t.created_at DESC"
    );
    query_tasks(
        conn,
        &sql,
        &[
            user_id.to_string(),
            root_id.to_string(),
            root_id.to_string(),
            user_id.to_string(),
        ],
    )
}