//! Subtask repository methods for SqliteTaskRepository.
//!
//! Handles subtask CRUD: listing, adding, toggling, updating, deleting,
//! and linking tasks as subtasks.

use sqlx::SqlitePool;
use std::collections::HashMap;

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

use crate::utils::{parse_uuid, parse_uuid_opt};

/// Row struct for subtasks from SQLite.
#[derive(Debug, Clone, sqlx::FromRow)]
pub(crate) struct SubtaskRow {
    pub id: String,
    pub task_id: String,
    pub text: String,
    pub linked_task_id: Option<String>,
    pub is_completed: i32,
    pub position: i32,
}

impl TryFrom<SubtaskRow> for Subtask {
    type Error = CoreError;

    fn try_from(row: SubtaskRow) -> std::result::Result<Self, Self::Error> {
        Ok(Subtask {
            id: parse_uuid(&row.id)?.into(),
            task_id: parse_uuid(&row.task_id)?.into(),
            text: row.text,
            linked_task_id: parse_uuid_opt(row.linked_task_id.as_deref())?.map(Into::into),
            is_completed: row.is_completed != 0,
            position: row.position,
        })
    }
}

/// Batch-fetch subtasks for multiple tasks by their IDs.
pub(crate) async fn get_subtasks_for_tasks(
    pool: &SqlitePool,
    task_ids: &[String],
) -> Result<HashMap<TaskId, Vec<Subtask>>> {
    if task_ids.is_empty() {
        return Ok(HashMap::new());
    }

    let placeholders: Vec<String> = task_ids.iter().map(|_| "?".to_string()).collect();
    let query = format!(
        r#"
        SELECT id, task_id, text, linked_task_id, is_completed, position
        FROM subtasks
        WHERE task_id IN ({})
        ORDER BY position ASC, created_at ASC
        "#,
        placeholders.join(",")
    );

    let mut q = sqlx::query_as::<_, SubtaskRow>(&query);
    for id in task_ids {
        q = q.bind(id);
    }

    let rows = q.fetch_all(pool).await.map_err(CoreError::database)?;

    let mut map: HashMap<TaskId, Vec<Subtask>> = HashMap::new();
    for row in rows {
        let subtask = Subtask::try_from(row)?;
        map.entry(subtask.task_id).or_default().push(subtask);
    }

    Ok(map)
}

/// Get all subtasks for a single task.
pub(crate) async fn get_subtasks_for_task(
    pool: &SqlitePool,
    task_id: TaskId,
) -> Result<Vec<Subtask>> {
    let rows = sqlx::query_as::<_, SubtaskRow>(
        r#"
        SELECT id, task_id, text, linked_task_id, is_completed, position
        FROM subtasks
        WHERE task_id = ?
        ORDER BY position ASC, created_at ASC
        "#,
    )
    .bind(task_id.to_string())
    .fetch_all(pool)
    .await
    .map_err(CoreError::database)?;

    rows.into_iter().map(Subtask::try_from).collect()
}

/// Add a subtask to a task (verifies task ownership).
pub(crate) async fn add_subtask(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
    text: &str,
) -> Result<Option<Subtask>> {
    let task_exists: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM tasks WHERE id = ? AND user_id = ?"
    )
        .bind(task_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(pool)
        .await
        .map_err(CoreError::database)?;

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

    let max_position: (Option<i32>,) = sqlx::query_as(
        "SELECT MAX(position) FROM subtasks WHERE task_id = ?"
    )
        .bind(task_id.to_string())
        .fetch_one(pool)
        .await
        .map_err(CoreError::database)?;

    let position = max_position.0.unwrap_or(-1) + 1;
    let id = SubtaskId::new();

    sqlx::query(
        r#"
        INSERT INTO subtasks (id, task_id, text, position)
        VALUES (?, ?, ?, ?)
        "#,
    )
    .bind(id.to_string())
    .bind(task_id.to_string())
    .bind(text)
    .bind(position)
    .execute(pool)
    .await
    .map_err(CoreError::database)?;

    Ok(Some(Subtask {
        id,
        task_id,
        text: text.to_string(),
        linked_task_id: None,
        is_completed: false,
        position,
    }))
}

/// Toggle a subtask's completion status.
pub(crate) async fn toggle_subtask(
    pool: &SqlitePool,
    subtask_id: SubtaskId,
    user_id: UserId,
) -> Result<Option<Subtask>> {
    // First check if subtask exists and belongs to user's task
    let row = sqlx::query_as::<_, SubtaskRow>(
        r#"
        SELECT s.id, s.task_id, s.text, s.linked_task_id, s.is_completed, s.position
        FROM subtasks s
        JOIN tasks t ON s.task_id = t.id
        WHERE s.id = ? AND t.user_id = ?
        "#
    )
        .bind(subtask_id.to_string())
        .bind(user_id.to_string())
        .fetch_optional(pool)
        .await
        .map_err(CoreError::database)?;

    let Some(subtask) = row else {
        return Ok(None);
    };

    let new_completed = if subtask.is_completed != 0 { 0 } else { 1 };

    sqlx::query("UPDATE subtasks SET is_completed = ? WHERE id = ?")
        .bind(new_completed)
        .bind(subtask_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    Ok(Some(Subtask {
        id: parse_uuid(&subtask.id)?.into(),
        task_id: parse_uuid(&subtask.task_id)?.into(),
        text: subtask.text,
        linked_task_id: parse_uuid_opt(subtask.linked_task_id.as_deref())?.map(Into::into),
        is_completed: new_completed != 0,
        position: subtask.position,
    }))
}

/// Update a subtask's text.
pub(crate) async fn update_subtask(
    pool: &SqlitePool,
    subtask_id: SubtaskId,
    user_id: UserId,
    text: &str,
) -> Result<Option<Subtask>> {
    let result = sqlx::query(
        r#"
        UPDATE subtasks
        SET text = ?
        WHERE id = ?
        AND task_id IN (SELECT id FROM tasks WHERE user_id = ?)
        "#
    )
        .bind(text)
        .bind(subtask_id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

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

    let row = sqlx::query_as::<_, SubtaskRow>(
        "SELECT id, task_id, text, linked_task_id, is_completed, position FROM subtasks WHERE id = ?"
    )
        .bind(subtask_id.to_string())
        .fetch_optional(pool)
        .await
        .map_err(CoreError::database)?;

    row.map(Subtask::try_from).transpose()
}

/// Delete a subtask (verifies task ownership).
pub(crate) async fn delete_subtask(
    pool: &SqlitePool,
    subtask_id: SubtaskId,
    user_id: UserId,
) -> Result<bool> {
    let result = sqlx::query(
        r#"
        DELETE FROM subtasks
        WHERE id = ?
        AND task_id IN (SELECT id FROM tasks WHERE user_id = ?)
        "#
    )
        .bind(subtask_id.to_string())
        .bind(user_id.to_string())
        .execute(pool)
        .await
        .map_err(CoreError::database)?;

    Ok(result.rows_affected() > 0)
}

/// Link an existing task as a subtask of another task.
pub(crate) async fn add_subtask_link(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
    linked_task_id: TaskId,
    linked_task_description: &str,
    linked_task_status: &TaskStatus,
) -> Result<Option<Subtask>> {
    // Verify parent task exists and belongs to user
    let task_exists: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM tasks WHERE id = ? AND user_id = ?"
    )
        .bind(task_id.to_string())
        .bind(user_id.to_string())
        .fetch_one(pool)
        .await
        .map_err(CoreError::database)?;

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

    // Get next position
    let max_position: (Option<i32>,) = sqlx::query_as(
        "SELECT MAX(position) FROM subtasks WHERE task_id = ?"
    )
        .bind(task_id.to_string())
        .fetch_one(pool)
        .await
        .map_err(CoreError::database)?;

    let position = max_position.0.unwrap_or(-1) + 1;
    let id = SubtaskId::new();

    // Determine completion status based on linked task
    let is_completed = *linked_task_status == TaskStatus::Completed;

    sqlx::query(
        r#"
        INSERT INTO subtasks (id, task_id, text, linked_task_id, is_completed, position)
        VALUES (?, ?, ?, ?, ?, ?)
        "#,
    )
    .bind(id.to_string())
    .bind(task_id.to_string())
    .bind(linked_task_description)
    .bind(linked_task_id.to_string())
    .bind(is_completed as i32)
    .bind(position)
    .execute(pool)
    .await
    .map_err(CoreError::database)?;

    Ok(Some(Subtask {
        id,
        task_id,
        text: linked_task_description.to_string(),
        linked_task_id: Some(linked_task_id),
        is_completed,
        position,
    }))
}