//! Annotation repository methods for SqliteTaskRepository.
//!
//! Handles annotation CRUD: listing, adding, and deleting annotations on tasks.

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

use goingson_core::{AnnotationId, Annotation, CoreError, Result, TaskId, UserId};

use crate::utils::{format_datetime_now, parse_datetime, parse_uuid};

/// Row struct for annotations from SQLite.
#[derive(Debug, Clone, sqlx::FromRow)]
pub(crate) struct AnnotationRow {
    pub id: String,
    pub task_id: String,
    pub timestamp: String,
    pub note: String,
}

impl TryFrom<AnnotationRow> for Annotation {
    type Error = CoreError;

    fn try_from(row: AnnotationRow) -> std::result::Result<Self, Self::Error> {
        Ok(Annotation {
            id: parse_uuid(&row.id)?.into(),
            task_id: parse_uuid(&row.task_id)?.into(),
            timestamp: parse_datetime(&row.timestamp)?,
            note: row.note,
        })
    }
}

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

    // SQLite doesn't have ANY(), use IN with placeholder generation
    let placeholders: Vec<String> = task_ids.iter().map(|_| "?".to_string()).collect();
    let query = format!(
        r#"
        SELECT id, task_id, timestamp, note
        FROM annotations
        WHERE task_id IN ({})
        ORDER BY timestamp DESC
        "#,
        placeholders.join(",")
    );

    let mut q = sqlx::query_as::<_, AnnotationRow>(&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<Annotation>> = HashMap::new();
    for row in rows {
        let annotation = Annotation::try_from(row)?;
        map.entry(annotation.task_id).or_default().push(annotation);
    }

    Ok(map)
}

/// Get all annotations for a single task.
pub(crate) async fn get_annotations_for_task(
    pool: &SqlitePool,
    task_id: TaskId,
) -> Result<Vec<Annotation>> {
    let rows = sqlx::query_as::<_, AnnotationRow>(
        r#"
        SELECT id, task_id, timestamp, note
        FROM annotations
        WHERE task_id = ?
        ORDER BY timestamp DESC
        "#,
    )
    .bind(task_id.to_string())
    .fetch_all(pool)
    .await
    .map_err(CoreError::database)?;

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

/// Add an annotation to a task (verifies task ownership).
pub(crate) async fn add_annotation(
    pool: &SqlitePool,
    task_id: TaskId,
    user_id: UserId,
    note: &str,
) -> Result<Option<Annotation>> {
    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 id = AnnotationId::new();
    let now = format_datetime_now();

    sqlx::query(
        r#"
        INSERT INTO annotations (id, task_id, timestamp, note)
        VALUES (?, ?, ?, ?)
        "#,
    )
    .bind(id.to_string())
    .bind(task_id.to_string())
    .bind(&now)
    .bind(note)
    .execute(pool)
    .await
    .map_err(CoreError::database)?;

    Ok(Some(Annotation {
        id,
        task_id,
        timestamp: Utc::now(),
        note: note.to_string(),
    }))
}

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

    Ok(result.rows_affected() > 0)
}