//! 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 `&SqlitePool` directly,
//! following the same pattern as `annotation_repo` and `subtask_repo`.

use chrono::{DateTime, NaiveDate, Utc};
use sqlx::SqlitePool;

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

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

use super::task_repo::{get_task_by_id, query_tasks, TASK_SELECT_COLUMNS};

// ---- Snooze ----

/// Snooze a task until the given time. Completed/deleted tasks cannot be snoozed.
pub(crate) async fn snooze(
    pool: &SqlitePool,
    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 result = sqlx::query(
        "UPDATE tasks SET snoozed_until = ? WHERE id = ? AND user_id = ? AND status NOT IN ('Completed', 'Deleted')"
    )
        .bind(&until_str)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    if result.rows_affected() > 0 {
        get_task_by_id(pool, id, user_id).await
    } else {
        // Distinguish "not found" from "wrong status"
        if let Some(task) = get_task_by_id(pool, id, user_id).await? {
            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) async fn unsnooze(
    pool: &SqlitePool,
    id: TaskId,
    user_id: UserId,
) -> Result<Option<Task>> {
    let result = sqlx::query(
        "UPDATE tasks SET snoozed_until = NULL WHERE id = ? AND user_id = ?"
    )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    if result.rows_affected() > 0 {
        get_task_by_id(pool, id, user_id).await
    } else {
        Ok(None)
    }
}

/// List all currently snoozed tasks.
pub(crate) async fn list_snoozed(
    pool: &SqlitePool,
    user_id: UserId,
) -> Result<Vec<Task>> {
    let sql = format!(
        "SELECT {} 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 datetime(t.snoozed_until) > datetime('now') ORDER BY t.snoozed_until ASC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
}

// ---- Waiting ----

/// Mark a task as waiting for response.
pub(crate) async fn mark_waiting(
    pool: &SqlitePool,
    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 result = sqlx::query(
        "UPDATE tasks SET waiting_for_response = 1, waiting_since = ?, expected_response_date = ? WHERE id = ? AND user_id = ?"
    )
        .bind(&now)
        .bind(&expected)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    if result.rows_affected() > 0 {
        get_task_by_id(pool, id, user_id).await
    } else {
        Ok(None)
    }
}

/// Clear the waiting-for-response state on a task.
pub(crate) async fn clear_waiting(
    pool: &SqlitePool,
    id: TaskId,
    user_id: UserId,
) -> Result<Option<Task>> {
    let result = sqlx::query(
        "UPDATE tasks SET waiting_for_response = 0, waiting_since = NULL, expected_response_date = NULL WHERE id = ? AND user_id = ?"
    )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    if result.rows_affected() > 0 {
        get_task_by_id(pool, id, user_id).await
    } else {
        Ok(None)
    }
}

/// List all tasks currently waiting for a response.
pub(crate) async fn list_waiting(
    pool: &SqlitePool,
    user_id: UserId,
) -> Result<Vec<Task>> {
    let sql = format!(
        "SELECT {} 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",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
}

// ---- Scheduling ----

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

    let sql = format!(
        "SELECT {} 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 datetime(t.scheduled_start) >= datetime(?) AND datetime(t.scheduled_start) <= datetime(?) ORDER BY t.scheduled_start ASC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), date_start, date_end]).await
}

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

    let sql = format!(
        "SELECT {} 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 datetime(t.due) >= datetime(?) AND datetime(t.due) <= datetime(?) ORDER BY t.urgency DESC, t.due ASC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), date_start, date_end]).await
}

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

    let result = sqlx::query(
        "UPDATE tasks SET scheduled_start = ?, scheduled_duration = ? WHERE id = ? AND user_id = ?"
    )
        .bind(&start_str)
        .bind(duration)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    if result.rows_affected() > 0 {
        get_task_by_id(pool, id, user_id).await
    } else {
        Ok(None)
    }
}

// ---- Focus ----

/// Set or clear focus on a task.
pub(crate) async fn set_focus(
    pool: &SqlitePool,
    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 result = sqlx::query(
        "UPDATE tasks SET is_focus = ?, focus_set_at = ? WHERE id = ? AND user_id = ?"
    )
        .bind(is_focus as i32)
        .bind(&focus_set_at)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    if result.rows_affected() > 0 {
        get_task_by_id(pool, id, user_id).await
    } else {
        Ok(None)
    }
}

/// List all focused tasks.
pub(crate) async fn list_focused(
    pool: &SqlitePool,
    user_id: UserId,
) -> Result<Vec<Task>> {
    let sql = format!(
        "SELECT {} 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",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
}

/// Clear focus from all tasks for a user.
pub(crate) async fn clear_all_focus(
    pool: &SqlitePool,
    user_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        "UPDATE tasks SET is_focus = 0, focus_set_at = NULL WHERE user_id = ? AND is_focus = 1"
    )
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    Ok(result.rows_affected())
}

// ---- Reporting ----

/// List tasks completed within a date range.
pub(crate) async fn list_completed_between(
    pool: &SqlitePool,
    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 {} 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 datetime(t.completed_at) >= datetime(?) AND datetime(t.completed_at) <= datetime(?) ORDER BY t.completed_at DESC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
}

/// List tasks created within a date range (for monthly review stats).
pub(crate) async fn list_created_between(
    pool: &SqlitePool,
    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 {} 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 datetime(t.created_at) >= datetime(?) AND datetime(t.created_at) <= datetime(?) ORDER BY t.created_at DESC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
}

/// List tasks that became overdue within a date range.
pub(crate) async fn list_became_overdue_between(
    pool: &SqlitePool,
    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 {} 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 datetime(t.due) >= datetime(?) AND datetime(t.due) <= datetime(?) ORDER BY t.due ASC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
}

/// List tasks due within a date range.
pub(crate) async fn list_due_between(
    pool: &SqlitePool,
    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 {} 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 datetime(t.due) >= datetime(?) AND datetime(t.due) <= datetime(?) ORDER BY t.due ASC, t.urgency DESC",
        TASK_SELECT_COLUMNS
    );
    query_tasks(pool, &sql, &[user_id.to_string(), user_id.to_string(), start_str, end_str]).await
}

/// List tasks available for focus (high priority, not snoozed, not waiting, not focused).
pub(crate) async fn list_available_for_focus(
    pool: &SqlitePool,
    user_id: UserId,
    limit: i64,
) -> Result<Vec<Task>> {
    let sql = format!(
        "SELECT {} 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 datetime(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 ?",
        TASK_SELECT_COLUMNS
    );

    // This query has an extra i64 bind, so we handle it directly
    let rows = sqlx::query_as::<_, super::task_repo::TaskRowWithProject>(&sql)
        .bind(user_id.to_string())
        .bind(user_id.to_string())
        .bind(limit)
        .fetch_all(pool)
        .await
        .map_err(CoreError::database)?;

    super::task_repo::rows_to_tasks(pool, rows).await
}