//! Task state-change and query methods delegated from SqliteTaskRepository.
//!
//! Covers snoozing, waiting-for-response, scheduling, focus mode, and
//! date-range reporting queries. Each function takes a borrowed `Connection`
//! directly, following the same pattern as `annotation_repo` and `subtask_repo`.

use chrono::{DateTime, NaiveDate, Utc};
use rusqlite::{Connection, params};

use goingson_core::{CoreError, Result, Task, TaskId, TaskStatus, UserId};

use crate::utils::{execute, format_datetime, format_datetime_now, format_datetime_opt, query_all};

use super::fetch::{get_task_by_id, query_tasks};
use super::row::TASK_SELECT_COLUMNS;

// Snooze

/// Snooze a task until the given time. Completed/deleted tasks cannot be snoozed.
pub(crate) fn snooze(
    conn: &Connection,
    id: TaskId,
    user_id: UserId,
    until: DateTime<Utc>,
) -> Result<Option<Task>> {
    let until_str = format_datetime(&until);

    // Atomically update only if task is not completed/deleted
    let changed = execute(
        conn,
        "UPDATE tasks SET snoozed_until = ? WHERE id = ? AND user_id = ? AND status NOT IN ('Completed', 'Deleted')",
        params![&until_str, id.to_string(), user_id.to_string()],
    )?;

    if changed > 0 {
        get_task_by_id(conn, id, user_id)
    } else {
        // Distinguish "not found" from "wrong status"
        if let Some(task) = get_task_by_id(conn, id, user_id)? {
            if task.status == TaskStatus::Completed {
                return Err(CoreError::validation(
                    "status",
                    "cannot snooze a completed task",
                ));
            }
            if task.status == TaskStatus::Deleted {
                return Err(CoreError::validation(
                    "status",
                    "cannot snooze a deleted task",
                ));
            }
        }
        Ok(None)
    }
}

/// Remove the snooze from a task.
pub(crate) fn unsnooze(conn: &Connection, id: TaskId, user_id: UserId) -> Result<Option<Task>> {
    let changed = execute(
        conn,
        "UPDATE tasks SET snoozed_until = NULL WHERE id = ? AND user_id = ?",
        params![id.to_string(), user_id.to_string()],
    )?;

    if changed > 0 {
        get_task_by_id(conn, id, user_id)
    } else {
        Ok(None)
    }
}

/// List all currently snoozed tasks.
pub(crate) fn list_snoozed(conn: &Connection, 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.user_id = ? AND t.status != 'Deleted' AND t.snoozed_until IS NOT NULL AND t.snoozed_until > datetime('now') ORDER BY t.snoozed_until ASC"
    );
    query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
}

// Waiting

/// Mark a task as waiting for response.
pub(crate) fn mark_waiting(
    conn: &Connection,
    id: TaskId,
    user_id: UserId,
    expected_response: Option<DateTime<Utc>>,
) -> Result<Option<Task>> {
    let now = format_datetime_now();
    let expected = format_datetime_opt(expected_response);

    let changed = execute(
        conn,
        "UPDATE tasks SET waiting_for_response = 1, waiting_since = ?, expected_response_date = ? WHERE id = ? AND user_id = ?",
        params![&now, &expected, id.to_string(), user_id.to_string()],
    )?;

    if changed > 0 {
        get_task_by_id(conn, id, user_id)
    } else {
        Ok(None)
    }
}

/// Clear the waiting-for-response state on a task.
pub(crate) fn clear_waiting(
    conn: &Connection,
    id: TaskId,
    user_id: UserId,
) -> Result<Option<Task>> {
    let changed = execute(
        conn,
        "UPDATE tasks SET waiting_for_response = 0, waiting_since = NULL, expected_response_date = NULL WHERE id = ? AND user_id = ?",
        params![id.to_string(), user_id.to_string()],
    )?;

    if changed > 0 {
        get_task_by_id(conn, id, user_id)
    } else {
        Ok(None)
    }
}

/// List all tasks currently waiting for a response.
pub(crate) fn list_waiting(conn: &Connection, 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.user_id = ? AND t.status != 'Deleted' AND t.waiting_for_response = 1 ORDER BY t.expected_response_date ASC NULLS LAST, t.waiting_since ASC"
    );
    query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
}

// Scheduling

/// List tasks scheduled for a specific date.
pub(crate) fn list_scheduled_for_date(
    conn: &Connection,
    user_id: UserId,
    date: NaiveDate,
) -> Result<Vec<Task>> {
    let date_start = format!("{date} 00:00:00");
    let date_end = format!("{date} 23:59:59");

    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.user_id = ? AND t.status != 'Deleted' AND t.status != 'Completed' AND t.scheduled_start IS NOT NULL AND t.scheduled_start >= ? AND t.scheduled_start <= ? ORDER BY t.scheduled_start ASC"
    );
    query_tasks(
        conn,
        &sql,
        &[
            user_id.to_string(),
            user_id.to_string(),
            date_start,
            date_end,
        ],
    )
}

/// List unscheduled tasks due on a specific date.
pub(crate) fn list_unscheduled_due_on_date(
    conn: &Connection,
    user_id: UserId,
    date: NaiveDate,
) -> Result<Vec<Task>> {
    let date_start = format!("{date} 00:00:00");
    let date_end = format!("{date} 23:59:59");

    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.user_id = ? AND t.status != 'Deleted' AND t.status != 'Completed' AND t.scheduled_start IS NULL AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.urgency DESC, t.due ASC"
    );
    query_tasks(
        conn,
        &sql,
        &[
            user_id.to_string(),
            user_id.to_string(),
            date_start,
            date_end,
        ],
    )
}

/// List unscheduled tasks whose `due` falls within an explicit UTC instant
/// window (inclusive upper bound). The caller maps a user-local civil day to
/// this range so the boundary respects the user's timezone, not UTC midnight.
pub(crate) fn list_unscheduled_due_between(
    conn: &Connection,
    user_id: UserId,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<Task>> {
    let start_str = format_datetime(&start);
    let end_str = format_datetime(&end);

    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.user_id = ? AND t.status != 'Deleted' AND t.status != 'Completed' AND t.scheduled_start IS NULL AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.urgency DESC, t.due ASC"
    );
    query_tasks(
        conn,
        &sql,
        &[user_id.to_string(), user_id.to_string(), start_str, end_str],
    )
}

/// Update the scheduled start time and duration for a task.
pub(crate) fn update_schedule(
    conn: &Connection,
    id: TaskId,
    user_id: UserId,
    start: Option<DateTime<Utc>>,
    duration: Option<i32>,
) -> Result<Option<Task>> {
    let start_str = format_datetime_opt(start);

    let changed = execute(
        conn,
        "UPDATE tasks SET scheduled_start = ?, scheduled_duration = ? WHERE id = ? AND user_id = ?",
        params![&start_str, duration, id.to_string(), user_id.to_string()],
    )?;

    if changed > 0 {
        get_task_by_id(conn, id, user_id)
    } else {
        Ok(None)
    }
}

// Focus

/// Set or clear focus on a task.
pub(crate) fn set_focus(
    conn: &Connection,
    id: TaskId,
    user_id: UserId,
    is_focus: bool,
) -> Result<Option<Task>> {
    let focus_set_at = if is_focus {
        Some(format_datetime(&Utc::now()))
    } else {
        None
    };

    let changed = execute(
        conn,
        "UPDATE tasks SET is_focus = ?, focus_set_at = ? WHERE id = ? AND user_id = ?",
        params![
            i32::from(is_focus),
            &focus_set_at,
            id.to_string(),
            user_id.to_string()
        ],
    )?;

    if changed > 0 {
        get_task_by_id(conn, id, user_id)
    } else {
        Ok(None)
    }
}

/// List all focused tasks.
pub(crate) fn list_focused(conn: &Connection, 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.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.is_focus = 1 ORDER BY t.focus_set_at DESC"
    );
    query_tasks(conn, &sql, &[user_id.to_string(), user_id.to_string()])
}

/// Clear focus from all tasks for a user.
pub(crate) fn clear_all_focus(conn: &Connection, user_id: UserId) -> Result<u64> {
    let changed = execute(
        conn,
        "UPDATE tasks SET is_focus = 0, focus_set_at = NULL WHERE user_id = ? AND is_focus = 1",
        params![user_id.to_string()],
    )?;

    Ok(changed as u64)
}

// Reporting

/// List tasks completed within a date range.
pub(crate) fn list_completed_between(
    conn: &Connection,
    user_id: UserId,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<Task>> {
    let start_str = format_datetime(&start);
    let end_str = format_datetime(&end);

    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.user_id = ? AND t.status = 'Completed' AND t.completed_at IS NOT NULL AND t.completed_at >= ? AND t.completed_at <= ? ORDER BY t.completed_at DESC"
    );
    query_tasks(
        conn,
        &sql,
        &[user_id.to_string(), user_id.to_string(), start_str, end_str],
    )
}

/// List tasks created within a date range (for monthly review stats).
pub(crate) fn list_created_between(
    conn: &Connection,
    user_id: UserId,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<Task>> {
    let start_str = format_datetime(&start);
    let end_str = format_datetime(&end);

    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.user_id = ? AND t.status != 'Deleted' AND t.created_at >= ? AND t.created_at <= ? ORDER BY t.created_at DESC"
    );
    query_tasks(
        conn,
        &sql,
        &[user_id.to_string(), user_id.to_string(), start_str, end_str],
    )
}

/// List tasks that became overdue within a date range.
pub(crate) fn list_became_overdue_between(
    conn: &Connection,
    user_id: UserId,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<Task>> {
    let start_str = format_datetime(&start);
    let end_str = format_datetime(&end);

    // Tasks whose due date is in the given range and are still pending/started (overdue)
    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.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.due ASC"
    );
    query_tasks(
        conn,
        &sql,
        &[user_id.to_string(), user_id.to_string(), start_str, end_str],
    )
}

/// List tasks due within a date range.
pub(crate) fn list_due_between(
    conn: &Connection,
    user_id: UserId,
    start: DateTime<Utc>,
    end: DateTime<Utc>,
) -> Result<Vec<Task>> {
    let start_str = format_datetime(&start);
    let end_str = format_datetime(&end);

    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.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.due IS NOT NULL AND t.due >= ? AND t.due <= ? ORDER BY t.due ASC, t.urgency DESC"
    );
    query_tasks(
        conn,
        &sql,
        &[user_id.to_string(), user_id.to_string(), start_str, end_str],
    )
}

/// List tasks available for focus (high priority, not snoozed, not waiting, not focused).
pub(crate) fn list_available_for_focus(
    conn: &Connection,
    user_id: UserId,
    limit: i64,
) -> Result<Vec<Task>> {
    // Clamp the caller-supplied limit: a negative value would otherwise mean
    // "unbounded" to SQLite and a huge value an unbounded allocation.
    let limit = limit.clamp(0, 1000);
    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.user_id = ? AND t.status NOT IN ('Completed', 'Deleted') AND t.is_focus = 0 AND (t.snoozed_until IS NULL OR t.snoozed_until <= datetime('now')) AND t.waiting_for_response = 0 ORDER BY t.urgency DESC, t.priority DESC, t.due ASC NULLS LAST LIMIT ?"
    );

    // This query has an extra i64 bind, so we handle it directly
    let rows = query_all(
        conn,
        &sql,
        params![user_id.to_string(), user_id.to_string(), limit],
        super::row::TaskRowWithProject::from_row,
    )?;

    super::fetch::rows_to_tasks(conn, rows)
}