//! SQLite implementation of the TaskRepository.
//!
//! Manages tasks with full support for:
//! - Status tracking (pending, in_progress, completed, deleted)
//! - Priority and urgency calculations
//! - Due dates and recurrence patterns
//! - Annotations and subtasks (delegated to annotation_repo and subtask_repo)
//! - Snoozing and waiting-for-response states
//! - Day planning with scheduled time blocks

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

use goingson_core::{
    AnnotationId, Annotation, ContactId, CoreError, DbValue, MilestoneId, NewTask, ParseableEnum, Priority,
    ProjectId, Recurrence, Result, SortDirection, SubtaskId, Subtask, Task, TaskFilterQuery,
    TaskId, TaskRepository, TaskSortColumn, TaskStatus, TimeSession,
    TimeTrackingSummary, UpdateTask, UserId,
};

use crate::utils::{format_datetime, format_datetime_now, format_datetime_opt, parse_datetime, parse_tags, parse_uuid, parse_uuid_opt};

use super::annotation_repo;
use super::subtask_repo;
use super::task_repo_state;
use super::time_session_repo;

/// Returns the SQL column expression for a [`TaskSortColumn`].
fn sort_column_sql(col: &TaskSortColumn) -> &'static str {
    match col {
        TaskSortColumn::Description => "t.description",
        TaskSortColumn::Project => "p.name",
        TaskSortColumn::Priority => "CASE t.priority WHEN 'High' THEN 3 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 1 ELSE 0 END",
        TaskSortColumn::Due => "t.due",
        TaskSortColumn::Urgency => "t.urgency",
    }
}

/// Returns whether NULLs should sort last for the given column.
fn sort_column_nulls_last(col: &TaskSortColumn) -> bool {
    matches!(col, TaskSortColumn::Project | TaskSortColumn::Due)
}

/// Common SELECT columns for task queries with project JOIN.
///
/// This constant ensures consistent column ordering across all task queries.
/// Usage: `format!("SELECT {} FROM tasks t LEFT JOIN projects p ON ...", TASK_SELECT_COLUMNS)`
pub(crate) const TASK_SELECT_COLUMNS: &str = r#"t.id, t.project_id, p.name as project_name,
       t.contact_id, ct.display_name as contact_name,
       t.milestone_id,
       t.description, t.status,
       t.priority, t.due, t.tags, t.urgency, t.recurrence, t.recurrence_rule, t.recurrence_parent_id, t.source_email_id,
       t.snoozed_until, t.waiting_for_response, t.waiting_since, t.expected_response_date,
       t.scheduled_start, t.scheduled_duration,
       t.estimated_minutes, t.actual_minutes,
       t.created_at, t.completed_at, t.is_focus, t.focus_set_at"#;

/// Row struct for task with project name from JOIN
#[derive(Debug, Clone, sqlx::FromRow)]
pub(crate) struct TaskRowWithProject {
    pub id: String,
    pub project_id: Option<String>,
    pub project_name: Option<String>,
    pub contact_id: Option<String>,
    pub contact_name: Option<String>,
    pub milestone_id: Option<String>,
    pub description: String,
    pub status: String,
    pub priority: String,
    pub due: Option<String>,
    pub tags: String,
    pub urgency: f64,
    pub recurrence: String,
    pub recurrence_rule: Option<String>,
    pub recurrence_parent_id: Option<String>,
    pub source_email_id: Option<String>,
    pub snoozed_until: Option<String>,
    pub waiting_for_response: i32,
    pub waiting_since: Option<String>,
    pub expected_response_date: Option<String>,
    pub scheduled_start: Option<String>,
    pub scheduled_duration: Option<i32>,
    pub estimated_minutes: Option<i32>,
    pub actual_minutes: i32,
    pub created_at: String,
    pub completed_at: Option<String>,
    pub is_focus: i32,
    pub focus_set_at: Option<String>,
}

impl TaskRowWithProject {
    fn into_task(self, annotations: Vec<Annotation>, subtasks: Vec<Subtask>) -> Result<Task> {
        Ok(Task {
            id: parse_uuid(&self.id)?.into(),
            project_id: parse_uuid_opt(self.project_id.as_deref())?.map(Into::into),
            project_name: self.project_name,
            contact_id: parse_uuid_opt(self.contact_id.as_deref())?.map(Into::into),
            contact_name: self.contact_name,
            milestone_id: parse_uuid_opt(self.milestone_id.as_deref())?.map(Into::into),
            description: self.description,
            status: TaskStatus::from_str_or_default(&self.status),
            priority: Priority::from_str_or_default(&self.priority),
            due: self.due.as_ref().map(|s| parse_datetime(s)).transpose()?,
            tags: parse_tags(&self.tags),
            urgency: self.urgency,
            recurrence: Recurrence::from_str_or_default(&self.recurrence),
            recurrence_rule: self.recurrence_rule
                .as_deref()
                .and_then(|s| serde_json::from_str(s).ok()),
            recurrence_parent_id: parse_uuid_opt(self.recurrence_parent_id.as_deref())?.map(Into::into),
            source_email_id: parse_uuid_opt(self.source_email_id.as_deref())?.map(Into::into),
            snoozed_until: self.snoozed_until.as_ref().map(|s| parse_datetime(s)).transpose()?,
            waiting_for_response: self.waiting_for_response != 0,
            waiting_since: self.waiting_since.as_ref().map(|s| parse_datetime(s)).transpose()?,
            expected_response_date: self.expected_response_date.as_ref().map(|s| parse_datetime(s)).transpose()?,
            scheduled_start: self.scheduled_start.as_ref().map(|s| parse_datetime(s)).transpose()?,
            scheduled_duration: self.scheduled_duration,
            estimated_minutes: self.estimated_minutes,
            actual_minutes: self.actual_minutes,
            active_session: None,
            annotations,
            subtasks,
            created_at: parse_datetime(&self.created_at)?,
            completed_at: self.completed_at.as_ref().map(|s| parse_datetime(s)).transpose()?,
            is_focus: self.is_focus != 0,
            focus_set_at: self.focus_set_at.as_ref().map(|s| parse_datetime(s)).transpose()?,
        })
    }
}

/// SQLite-backed implementation of [`TaskRepository`].
///
/// The most complex repository in the system, handling tasks with all their
/// related data (annotations, subtasks) and supporting advanced filtering,
/// sorting, and recurrence logic.
pub struct SqliteTaskRepository {
    pool: SqlitePool,
}

impl SqliteTaskRepository {
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }
}

/// Converts task rows to Task objects with annotations, subtasks, and active sessions.
///
/// This helper encapsulates the common pattern of:
/// 1. Extracting task IDs from rows
/// 2. Batch-fetching annotations, subtasks, and active sessions for all tasks
/// 3. Converting each row to a Task with its related data
///
/// Returns an empty vec if rows is empty (no database calls made).
pub(crate) async fn rows_to_tasks(pool: &SqlitePool, rows: Vec<TaskRowWithProject>) -> Result<Vec<Task>> {
    if rows.is_empty() {
        return Ok(vec![]);
    }

    let task_ids: Vec<String> = rows.iter().map(|r| r.id.clone()).collect();
    let annotations_map = annotation_repo::get_annotations_for_tasks(pool, &task_ids).await?;
    let subtasks_map = subtask_repo::get_subtasks_for_tasks(pool, &task_ids).await?;
    let active_sessions = time_session_repo::get_active_sessions_for_tasks(pool, &task_ids).await?;

    let mut tasks = Vec::with_capacity(rows.len());
    for row in rows {
        let id: TaskId = parse_uuid(&row.id)?.into();
        let annotations = annotations_map.get(&id).cloned().unwrap_or_default();
        let subtasks = subtasks_map.get(&id).cloned().unwrap_or_default();
        let mut task = row.into_task(annotations, subtasks)?;
        task.active_session = active_sessions.get(&id).cloned();
        tasks.push(task);
    }

    Ok(tasks)
}

/// Fetch only the fields needed for update logic — avoids annotation/subtask/session sub-queries.
pub(crate) async fn get_task_update_context(pool: &SqlitePool, id: TaskId, user_id: UserId) -> Result<Option<goingson_core::models::TaskUpdateContext>> {
    #[derive(sqlx::FromRow)]
    struct Row {
        created_at: String,
        status: String,
        completed_at: Option<String>,
        scheduled_start: Option<String>,
        scheduled_duration: Option<i32>,
    }

    let row = sqlx::query_as::<_, Row>(
        "SELECT created_at, status, completed_at, scheduled_start, scheduled_duration FROM tasks WHERE id = ? AND user_id = ?"
    )
    .bind(id.to_string())
    .bind(user_id.to_string())
    .fetch_optional(pool)
    .await
    .map_err(CoreError::database)?;

    match row {
        Some(r) => Ok(Some(goingson_core::models::TaskUpdateContext {
            created_at: parse_datetime(&r.created_at)?,
            status: TaskStatus::from_str_or_default(&r.status),
            completed_at: r.completed_at.as_ref().map(|s| parse_datetime(s)).transpose()?,
            scheduled_start: r.scheduled_start.as_ref().map(|s| parse_datetime(s)).transpose()?,
            scheduled_duration: r.scheduled_duration,
        })),
        None => Ok(None),
    }
}

/// Fetch a single task by ID and user, with annotations and subtasks.
pub(crate) async fn get_task_by_id(pool: &SqlitePool, id: TaskId, user_id: UserId) -> Result<Option<Task>> {
    let sql = format!(
        r#"
        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.id = ? AND t.user_id = ?
        "#,
        TASK_SELECT_COLUMNS
    );
    let row = sqlx::query_as::<_, TaskRowWithProject>(&sql)
        .bind(user_id.to_string())
        .bind(id.to_string())
        .bind(user_id.to_string())
        .fetch_optional(pool)
        .await
        .map_err(CoreError::database)?;

    match row {
        Some(row) => {
            let annotations = annotation_repo::get_annotations_for_task(pool, id).await?;
            let subtasks = subtask_repo::get_subtasks_for_task(pool, id).await?;
            let active_sessions = time_session_repo::get_active_sessions_for_tasks(pool, std::slice::from_ref(&row.id)).await?;
            let mut task = row.into_task(annotations, subtasks)?;
            task.active_session = active_sessions.get(&task.id).cloned();
            Ok(Some(task))
        }
        None => Ok(None),
    }
}

/// Run a task query with string bind parameters and convert rows to tasks.
///
/// Handles the common pattern of: format SQL with TASK_SELECT_COLUMNS,
/// bind string params in order, fetch rows, convert via rows_to_tasks.
pub(crate) async fn query_tasks(pool: &SqlitePool, sql: &str, binds: &[String]) -> Result<Vec<Task>> {
    let mut query = sqlx::query_as::<_, TaskRowWithProject>(sql);
    for b in binds {
        query = query.bind(b);
    }
    let rows = query.fetch_all(pool).await.map_err(CoreError::database)?;
    rows_to_tasks(pool, rows).await
}

#[async_trait]
impl TaskRepository for SqliteTaskRepository {
    #[tracing::instrument(skip_all)]
    async fn list_all(&self, user_id: UserId) -> Result<Vec<Task>> {
        let sql = format!(
            r#"
            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'
            ORDER BY t.urgency DESC, t.created_at DESC
            "#,
            TASK_SELECT_COLUMNS
        );
        query_tasks(&self.pool, &sql, &[user_id.to_string(), user_id.to_string()]).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_by_project(&self, user_id: UserId, project_id: ProjectId) -> Result<Vec<Task>> {
        let sql = format!(
            r#"
            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.project_id = ? AND t.status != 'Deleted'
            ORDER BY t.urgency DESC, t.created_at DESC
            "#,
            TASK_SELECT_COLUMNS
        );
        query_tasks(&self.pool, &sql, &[user_id.to_string(), user_id.to_string(), project_id.to_string()]).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_by_contact(&self, user_id: UserId, contact_id: ContactId) -> Result<Vec<Task>> {
        let sql = format!(
            r#"
            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.contact_id = ? AND t.status != 'Deleted'
            ORDER BY t.created_at DESC
            "#,
            TASK_SELECT_COLUMNS
        );
        query_tasks(&self.pool, &sql, &[user_id.to_string(), user_id.to_string(), contact_id.to_string()]).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_filtered(&self, 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 ");

        // First, get total count for pagination
        let count_sql = format!("SELECT COUNT(*) FROM tasks t WHERE {}", where_clause);
        let mut count_query = sqlx::query_as::<_, (i64,)>(&count_sql);
        for value in &bind_values {
            count_query = count_query.bind(value);
        }
        let (total,): (i64,) = count_query.fetch_one(&self.pool).await.map_err(CoreError::database)?;

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

        // Build paginated query with parameterized LIMIT/OFFSET
        let mut pagination_binds: Vec<i64> = Vec::new();
        let pagination = match (query.limit, query.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 {}
            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 {}
            ORDER BY {}{}
            "#,
            TASK_SELECT_COLUMNS, where_clause, order_by, pagination
        );

        // Build query with dynamic bindings
        let mut sqlx_query = sqlx::query_as::<_, TaskRowWithProject>(&sql);

        // Bind user_id for the JOIN
        sqlx_query = sqlx_query.bind(user_id.to_string());

        // Bind all WHERE clause values
        for value in bind_values {
            sqlx_query = sqlx_query.bind(value);
        }

        // Bind LIMIT/OFFSET values
        for value in pagination_binds {
            sqlx_query = sqlx_query.bind(value);
        }

        let rows = sqlx_query
            .fetch_all(&self.pool)
            .await
            .map_err(CoreError::database)?;

        let tasks = rows_to_tasks(&self.pool, rows).await?;
        Ok((tasks, total))
    }

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: TaskId, user_id: UserId) -> Result<Option<Task>> {
        get_task_by_id(&self.pool, id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn get_update_context(&self, id: TaskId, user_id: UserId) -> Result<Option<goingson_core::models::TaskUpdateContext>> {
        get_task_update_context(&self.pool, id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn create(&self, user_id: UserId, task: NewTask) -> Result<Task> {
        let id = TaskId::new();
        let now = format_datetime_now();
        let due_str = format_datetime_opt(task.due);
        let scheduled_start_str = format_datetime_opt(task.scheduled_start);
        let tags_json = serde_json::to_string(&task.tags).unwrap_or_else(|_| "[]".to_string());

        sqlx::query(
            r#"
            INSERT INTO tasks (id, user_id, project_id, contact_id, milestone_id, description, priority, due, tags, recurrence, recurrence_rule, urgency, source_email_id, scheduled_start, scheduled_duration, estimated_minutes, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .bind(task.project_id.map(|p| p.to_string()))
        .bind(task.contact_id.map(|c| c.to_string()))
        .bind(task.milestone_id.map(|m| m.to_string()))
        .bind(&task.description)
        .bind(task.priority.db_value())
        .bind(&due_str)
        .bind(&tags_json)
        .bind(task.recurrence.db_value())
        .bind(task.recurrence_rule.as_ref().map(|r| serde_json::to_string(r).unwrap_or_default()))
        .bind(task.urgency)
        .bind(task.source_email_id.map(|e| e.to_string()))
        .bind(&scheduled_start_str)
        .bind(task.scheduled_duration)
        .bind(task.estimated_minutes)
        .bind(&now)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        get_task_by_id(&self.pool, id, user_id)
            .await?
            .ok_or_else(|| CoreError::internal("Failed to retrieve created task"))
    }

    #[tracing::instrument(skip_all)]
    async fn update(&self, id: TaskId, user_id: UserId, task: UpdateTask) -> Result<Option<Task>> {
        let due_str = format_datetime_opt(task.due);
        let scheduled_start_str = format_datetime_opt(task.scheduled_start);
        let tags_json = serde_json::to_string(&task.tags).unwrap_or_else(|_| "[]".to_string());

        // Set completed_at when transitioning to Completed, clear when leaving Completed.
        // Uses lightweight query (no annotation/subtask/session sub-queries).
        let completed_at_str: Option<String> = if task.status == TaskStatus::Completed {
            let ctx = get_task_update_context(&self.pool, id, user_id).await?;
            match ctx {
                Some(ref c) if c.status == TaskStatus::Completed => {
                    c.completed_at.as_ref().map(format_datetime)
                }
                _ => Some(format_datetime_now()),
            }
        } else {
            None
        };

        let result = sqlx::query(
            r#"
            UPDATE tasks
            SET project_id = ?, contact_id = ?, milestone_id = ?, description = ?, status = ?, priority = ?, due = ?, tags = ?, recurrence = ?, urgency = ?, scheduled_start = ?, scheduled_duration = ?, estimated_minutes = ?, completed_at = ?
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(task.project_id.map(|p| p.to_string()))
        .bind(task.contact_id.map(|c| c.to_string()))
        .bind(task.milestone_id.map(|m| m.to_string()))
        .bind(&task.description)
        .bind(task.status.db_value())
        .bind(task.priority.db_value())
        .bind(&due_str)
        .bind(&tags_json)
        .bind(task.recurrence.db_value())
        .bind(task.urgency)
        .bind(&scheduled_start_str)
        .bind(task.scheduled_duration)
        .bind(task.estimated_minutes)
        .bind(&completed_at_str)
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn delete(&self, id: TaskId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query("UPDATE tasks SET status = 'Deleted' WHERE id = ? AND user_id = ?")
            .bind(id.to_string())
            .bind(user_id.to_string())
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn start(&self, id: TaskId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query(
            "UPDATE tasks SET status = 'Started' WHERE id = ? AND user_id = ? AND status = 'Pending'"
        )
            .bind(id.to_string())
            .bind(user_id.to_string())
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn complete(&self, id: TaskId, user_id: UserId) -> Result<Option<Task>> {
        let task = match get_task_by_id(&self.pool, id, user_id).await? {
            Some(t) => t,
            None => return Ok(None),
        };

        if task.status == TaskStatus::Completed {
            return Ok(None);
        }

        let now = format_datetime_now();

        let result = sqlx::query("UPDATE tasks SET status = 'Completed', completed_at = ? WHERE id = ? AND user_id = ?")
            .bind(&now)
            .bind(id.to_string())
            .bind(user_id.to_string())
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

        if result.rows_affected() == 0 {
            return Ok(None);
        }

        get_task_by_id(&self.pool, id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn complete_recurring(&self, id: TaskId, user_id: UserId, next: Option<NewTask>) -> Result<(Option<Task>, Option<Task>)> {
        let task = match get_task_by_id(&self.pool, id, user_id).await? {
            Some(t) => t,
            None => return Ok((None, None)),
        };

        if task.status == TaskStatus::Completed {
            return Ok((None, None));
        }

        let mut tx = self.pool.begin().await.map_err(CoreError::database)?;

        // Mark complete
        let now = format_datetime_now();
        sqlx::query("UPDATE tasks SET status = 'Completed', completed_at = ? WHERE id = ? AND user_id = ?")
            .bind(&now)
            .bind(id.to_string())
            .bind(user_id.to_string())
            .execute(&mut *tx)
            .await
            .map_err(CoreError::database)?;

        // Create next recurring instance if provided
        let next_id = if let Some(new_task) = &next {
            let nid = TaskId::new();
            let due_str = format_datetime_opt(new_task.due);
            let scheduled_start_str = format_datetime_opt(new_task.scheduled_start);
            let tags_json = serde_json::to_string(&new_task.tags).unwrap_or_else(|_| "[]".to_string());

            sqlx::query(
                r#"
                INSERT INTO tasks (id, user_id, project_id, contact_id, milestone_id, description, priority, due, tags, recurrence, recurrence_rule, urgency, source_email_id, scheduled_start, scheduled_duration, estimated_minutes, recurrence_parent_id, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                "#,
            )
            .bind(nid.to_string())
            .bind(user_id.to_string())
            .bind(new_task.project_id.map(|p| p.to_string()))
            .bind(new_task.contact_id.map(|c| c.to_string()))
            .bind(new_task.milestone_id.map(|m| m.to_string()))
            .bind(&new_task.description)
            .bind(new_task.priority.db_value())
            .bind(&due_str)
            .bind(&tags_json)
            .bind(new_task.recurrence.db_value())
            .bind(new_task.recurrence_rule.as_ref().map(|r| serde_json::to_string(r).unwrap_or_default()))
            .bind(new_task.urgency)
            .bind(new_task.source_email_id.map(|e| e.to_string()))
            .bind(&scheduled_start_str)
            .bind(new_task.scheduled_duration)
            .bind(new_task.estimated_minutes)
            .bind(new_task.recurrence_parent_id.map(|p| p.to_string()))
            .bind(&now)
            .execute(&mut *tx)
            .await
            .map_err(CoreError::database)?;

            Some(nid)
        } else {
            None
        };

        tx.commit().await.map_err(CoreError::database)?;

        // Fetch the completed task and new task (outside transaction, committed)
        let completed = get_task_by_id(&self.pool, id, user_id).await?;
        let next_task = match next_id {
            Some(nid) => get_task_by_id(&self.pool, nid, user_id).await?,
            None => None,
        };

        Ok((completed, next_task))
    }

    #[tracing::instrument(skip_all)]
    async fn count_incomplete_by_milestone(&self, milestone_id: MilestoneId, user_id: UserId) -> Result<i64> {
        let (count,): (i64,) = sqlx::query_as(
            "SELECT COUNT(*) FROM tasks WHERE milestone_id = ? AND user_id = ? AND status != 'Deleted' AND status != 'Completed'"
        )
        .bind(milestone_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(count)
    }

    // ---- Annotations (delegated to annotation_repo) ----

    #[tracing::instrument(skip_all)]
    async fn get_annotations_for_task(&self, task_id: TaskId) -> Result<Vec<Annotation>> {
        annotation_repo::get_annotations_for_task(&self.pool, task_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn add_annotation(&self, task_id: TaskId, user_id: UserId, note: &str) -> Result<Option<Annotation>> {
        annotation_repo::add_annotation(&self.pool, task_id, user_id, note).await
    }

    #[tracing::instrument(skip_all)]
    async fn delete_annotation(&self, annotation_id: AnnotationId, user_id: UserId) -> Result<bool> {
        annotation_repo::delete_annotation(&self.pool, annotation_id, user_id).await
    }

    // ---- Subtasks (delegated to subtask_repo) ----

    #[tracing::instrument(skip_all)]
    async fn get_subtasks_for_task(&self, task_id: TaskId) -> Result<Vec<Subtask>> {
        subtask_repo::get_subtasks_for_task(&self.pool, task_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn add_subtask(&self, task_id: TaskId, user_id: UserId, text: &str) -> Result<Option<Subtask>> {
        subtask_repo::add_subtask(&self.pool, task_id, user_id, text).await
    }

    #[tracing::instrument(skip_all)]
    async fn toggle_subtask(&self, subtask_id: SubtaskId, user_id: UserId) -> Result<Option<Subtask>> {
        subtask_repo::toggle_subtask(&self.pool, subtask_id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn update_subtask(&self, subtask_id: SubtaskId, user_id: UserId, text: &str) -> Result<Option<Subtask>> {
        subtask_repo::update_subtask(&self.pool, subtask_id, user_id, text).await
    }

    #[tracing::instrument(skip_all)]
    async fn delete_subtask(&self, subtask_id: SubtaskId, user_id: UserId) -> Result<bool> {
        subtask_repo::delete_subtask(&self.pool, subtask_id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn add_subtask_link(&self, task_id: TaskId, user_id: UserId, linked_task_id: TaskId) -> Result<Option<Subtask>> {
        // Verify linked task exists and belongs to user
        let linked_task = get_task_by_id(&self.pool, linked_task_id, user_id).await?
            .ok_or_else(|| CoreError::not_found("linked task", linked_task_id.to_string()))?;

        subtask_repo::add_subtask_link(
            &self.pool,
            task_id,
            user_id,
            linked_task_id,
            &linked_task.description,
            &linked_task.status,
        ).await
    }

    // ---- Snooze (delegated to task_repo_state) ----

    #[tracing::instrument(skip_all)]
    async fn snooze(&self, id: TaskId, user_id: UserId, until: DateTime<Utc>) -> Result<Option<Task>> {
        task_repo_state::snooze(&self.pool, id, user_id, until).await
    }

    #[tracing::instrument(skip_all)]
    async fn unsnooze(&self, id: TaskId, user_id: UserId) -> Result<Option<Task>> {
        task_repo_state::unsnooze(&self.pool, id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_snoozed(&self, user_id: UserId) -> Result<Vec<Task>> {
        task_repo_state::list_snoozed(&self.pool, user_id).await
    }

    // ---- Waiting (delegated to task_repo_state) ----

    #[tracing::instrument(skip_all)]
    async fn mark_waiting(&self, id: TaskId, user_id: UserId, expected_response: Option<DateTime<Utc>>) -> Result<Option<Task>> {
        task_repo_state::mark_waiting(&self.pool, id, user_id, expected_response).await
    }

    #[tracing::instrument(skip_all)]
    async fn clear_waiting(&self, id: TaskId, user_id: UserId) -> Result<Option<Task>> {
        task_repo_state::clear_waiting(&self.pool, id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_waiting(&self, user_id: UserId) -> Result<Vec<Task>> {
        task_repo_state::list_waiting(&self.pool, user_id).await
    }

    // ---- Scheduling (delegated to task_repo_state) ----

    #[tracing::instrument(skip_all)]
    async fn list_scheduled_for_date(&self, user_id: UserId, date: NaiveDate) -> Result<Vec<Task>> {
        task_repo_state::list_scheduled_for_date(&self.pool, user_id, date).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_unscheduled_due_on_date(&self, user_id: UserId, date: NaiveDate) -> Result<Vec<Task>> {
        task_repo_state::list_unscheduled_due_on_date(&self.pool, user_id, date).await
    }

    #[tracing::instrument(skip_all)]
    async fn update_schedule(&self, id: TaskId, user_id: UserId, start: Option<DateTime<Utc>>, duration: Option<i32>) -> Result<Option<Task>> {
        task_repo_state::update_schedule(&self.pool, id, user_id, start, duration).await
    }

    // ---- Focus (delegated to task_repo_state) ----

    #[tracing::instrument(skip_all)]
    async fn set_focus(&self, id: TaskId, user_id: UserId, is_focus: bool) -> Result<Option<Task>> {
        task_repo_state::set_focus(&self.pool, id, user_id, is_focus).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_focused(&self, user_id: UserId) -> Result<Vec<Task>> {
        task_repo_state::list_focused(&self.pool, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn clear_all_focus(&self, user_id: UserId) -> Result<u64> {
        task_repo_state::clear_all_focus(&self.pool, user_id).await
    }

    // ---- Reporting (delegated to task_repo_state) ----

    #[tracing::instrument(skip_all)]
    async fn list_completed_between(&self, user_id: UserId, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<Vec<Task>> {
        task_repo_state::list_completed_between(&self.pool, user_id, start, end).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_became_overdue_between(&self, user_id: UserId, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<Vec<Task>> {
        task_repo_state::list_became_overdue_between(&self.pool, user_id, start, end).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_due_between(&self, user_id: UserId, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<Vec<Task>> {
        task_repo_state::list_due_between(&self.pool, user_id, start, end).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_created_between(&self, user_id: UserId, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<Vec<Task>> {
        task_repo_state::list_created_between(&self.pool, user_id, start, end).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_available_for_focus(&self, user_id: UserId, limit: i64) -> Result<Vec<Task>> {
        task_repo_state::list_available_for_focus(&self.pool, user_id, limit).await
    }

    // ---- Time Tracking (delegated to time_session_repo) ----

    #[tracing::instrument(skip_all)]
    async fn start_timer(&self, task_id: TaskId, user_id: UserId) -> Result<TimeSession> {
        time_session_repo::start_timer(&self.pool, task_id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn stop_timer(&self, task_id: TaskId, user_id: UserId) -> Result<Option<TimeSession>> {
        time_session_repo::stop_timer(&self.pool, task_id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn discard_timer(&self, task_id: TaskId, user_id: UserId) -> Result<bool> {
        time_session_repo::discard_timer(&self.pool, task_id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn get_active_timer(&self, user_id: UserId) -> Result<Option<(TimeSession, String)>> {
        time_session_repo::get_active_timer(&self.pool, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_time_sessions(&self, task_id: TaskId, user_id: UserId) -> Result<Vec<TimeSession>> {
        time_session_repo::list_time_sessions(&self.pool, task_id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn log_manual_time(&self, task_id: TaskId, user_id: UserId, minutes: i32, date: DateTime<Utc>) -> Result<TimeSession> {
        time_session_repo::log_manual_time(&self.pool, task_id, user_id, minutes, date).await
    }

    #[tracing::instrument(skip_all)]
    async fn get_time_summary(&self, user_id: UserId, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<Vec<TimeTrackingSummary>> {
        time_session_repo::get_time_summary(&self.pool, user_id, start, end).await
    }

    #[tracing::instrument(skip_all)]
    async fn list_recurrence_chain(&self, root_id: TaskId, 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.recurrence_parent_id = ? OR t.id = ?) AND t.user_id = ? ORDER BY t.created_at DESC",
            TASK_SELECT_COLUMNS
        );
        query_tasks(&self.pool, &sql, &[
            user_id.to_string(),
            root_id.to_string(),
            root_id.to_string(),
            user_id.to_string(),
        ]).await
    }
}