//! SQLite implementation of the SavedViewRepository.
//!
//! Manages saved views (custom filtered/sorted task lists) that users can
//! create, pin to the sidebar, and reorder.

use async_trait::async_trait;
use chrono::Utc;
use sqlx::SqlitePool;
use goingson_core::{
    CoreError, DbValue, NewSavedView, ParseableEnum, Result, SavedView, SavedViewId,
    SavedViewRepository, SortDirection, UserId, ViewFilters, ViewType,
};

use crate::utils::parse_uuid;

#[derive(sqlx::FromRow)]
struct ViewRow {
    id: String,
    user_id: String,
    name: String,
    view_type: String,
    filters: String,
    sort_by: Option<String>,
    sort_order: String,
    is_pinned: i32,
    position: i32,
    created_at: String,
    updated_at: String,
}

/// SQLite-backed implementation of [`SavedViewRepository`].
///
/// Stores view configurations as JSON-serialized filters with support
/// for pinning and custom ordering.
pub struct SqliteSavedViewRepository {
    pool: SqlitePool,
}

impl SqliteSavedViewRepository {
    /// Creates a new repository instance with the given connection pool.
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }
}

#[async_trait]
impl SavedViewRepository for SqliteSavedViewRepository {
    #[tracing::instrument(skip_all)]
    async fn list_all(&self, user_id: UserId) -> Result<Vec<SavedView>> {
        let user_id_str = user_id.to_string();

        let rows: Vec<ViewRow> = sqlx::query_as(
            r#"
            SELECT id, user_id, name, view_type, filters, sort_by, sort_order,
                   is_pinned, position, created_at, updated_at
            FROM saved_views
            WHERE user_id = ?
            ORDER BY position ASC, name ASC
            "#,
        )
        .bind(&user_id_str)
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

        rows.into_iter().map(row_to_saved_view).collect()
    }

    #[tracing::instrument(skip_all)]
    async fn list_pinned(&self, user_id: UserId) -> Result<Vec<SavedView>> {
        let user_id_str = user_id.to_string();

        let rows: Vec<ViewRow> = sqlx::query_as(
            r#"
            SELECT id, user_id, name, view_type, filters, sort_by, sort_order,
                   is_pinned, position, created_at, updated_at
            FROM saved_views
            WHERE user_id = ? AND is_pinned = 1
            ORDER BY position ASC, name ASC
            "#,
        )
        .bind(&user_id_str)
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

        rows.into_iter().map(row_to_saved_view).collect()
    }

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: SavedViewId, user_id: UserId) -> Result<Option<SavedView>> {
        let id_str = id.to_string();
        let user_id_str = user_id.to_string();

        let row: Option<ViewRow> = sqlx::query_as(
            r#"
            SELECT id, user_id, name, view_type, filters, sort_by, sort_order,
                   is_pinned, position, created_at, updated_at
            FROM saved_views
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(&id_str)
        .bind(&user_id_str)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

        match row {
            Some(r) => Ok(Some(row_to_saved_view(r)?)),
            None => Ok(None),
        }
    }

    #[tracing::instrument(skip_all)]
    async fn create(&self, user_id: UserId, view: NewSavedView) -> Result<SavedView> {
        let id = SavedViewId::new();
        let now = Utc::now();
        let id_str = id.to_string();
        let user_id_str = user_id.to_string();
        let now_str = now.to_rfc3339();
        let filters_json = serde_json::to_string(&view.filters)
            .map_err(|e| CoreError::database_msg(e.to_string()))?;

        sqlx::query(
            r#"
            INSERT INTO saved_views (id, user_id, name, view_type, filters, sort_by, sort_order,
                                     is_pinned, position, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?)
            "#,
        )
        .bind(&id_str)
        .bind(&user_id_str)
        .bind(&view.name)
        .bind(view.view_type.db_value())
        .bind(&filters_json)
        .bind(view.sort_by.map(|sf| {
            serde_json::to_value(sf).ok()
                .and_then(|v| v.as_str().map(String::from))
                .unwrap_or_default()
        }))
        .bind(match view.sort_order {
            Some(SortDirection::Desc) => "desc",
            _ => "asc",
        })
        .bind(if view.is_pinned.unwrap_or(false) { 1 } else { 0 })
        .bind(&now_str)
        .bind(&now_str)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(SavedView {
            id,
            user_id,
            name: view.name,
            view_type: view.view_type,
            filters: view.filters,
            sort_by: view.sort_by,
            sort_order: view.sort_order.unwrap_or(SortDirection::Asc),
            is_pinned: view.is_pinned.unwrap_or(false),
            position: 0,
            created_at: now,
            updated_at: now,
        })
    }

    #[tracing::instrument(skip_all)]
    async fn update(&self, id: SavedViewId, user_id: UserId, view: NewSavedView) -> Result<Option<SavedView>> {
        let id_str = id.to_string();
        let user_id_str = user_id.to_string();
        let now = Utc::now();
        let now_str = now.to_rfc3339();
        let filters_json = serde_json::to_string(&view.filters)
            .map_err(|e| CoreError::database_msg(e.to_string()))?;

        let result = sqlx::query(
            r#"
            UPDATE saved_views
            SET name = ?, view_type = ?, filters = ?, sort_by = ?, sort_order = ?,
                is_pinned = ?, updated_at = ?
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(&view.name)
        .bind(view.view_type.db_value())
        .bind(&filters_json)
        .bind(view.sort_by.map(|sf| {
            serde_json::to_value(sf).ok()
                .and_then(|v| v.as_str().map(String::from))
                .unwrap_or_default()
        }))
        .bind(match view.sort_order {
            Some(SortDirection::Desc) => "desc",
            _ => "asc",
        })
        .bind(if view.is_pinned.unwrap_or(false) { 1 } else { 0 })
        .bind(&now_str)
        .bind(&id_str)
        .bind(&user_id_str)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

        self.get_by_id(id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn delete(&self, id: SavedViewId, user_id: UserId) -> Result<bool> {
        let id_str = id.to_string();
        let user_id_str = user_id.to_string();

        let result = sqlx::query("DELETE FROM saved_views WHERE id = ? AND user_id = ?")
            .bind(&id_str)
            .bind(&user_id_str)
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

        Ok(result.rows_affected() > 0)
    }

    #[tracing::instrument(skip_all)]
    async fn toggle_pinned(&self, id: SavedViewId, user_id: UserId) -> Result<Option<SavedView>> {
        let id_str = id.to_string();
        let user_id_str = user_id.to_string();
        let now_str = Utc::now().to_rfc3339();

        let result = sqlx::query(
            r#"
            UPDATE saved_views
            SET is_pinned = CASE WHEN is_pinned = 1 THEN 0 ELSE 1 END, updated_at = ?
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(&now_str)
        .bind(&id_str)
        .bind(&user_id_str)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

        self.get_by_id(id, user_id).await
    }

    #[tracing::instrument(skip_all)]
    async fn update_position(&self, id: SavedViewId, user_id: UserId, position: i32) -> Result<Option<SavedView>> {
        let id_str = id.to_string();
        let user_id_str = user_id.to_string();
        let now_str = Utc::now().to_rfc3339();

        let result = sqlx::query(
            "UPDATE saved_views SET position = ?, updated_at = ? WHERE id = ? AND user_id = ?",
        )
        .bind(position)
        .bind(&now_str)
        .bind(&id_str)
        .bind(&user_id_str)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

        self.get_by_id(id, user_id).await
    }
}

/// Converts a database row to a [`SavedView`] domain object.
fn row_to_saved_view(row: ViewRow) -> Result<SavedView> {
    let view_type = ViewType::from_str_or_default(&row.view_type);

    let filters: ViewFilters = serde_json::from_str(&row.filters)
        .unwrap_or_else(|e| {
            tracing::warn!(view_id = %row.id, error = %e, "Malformed saved view filters, using defaults");
            ViewFilters::default()
        });

    Ok(SavedView {
        id: parse_uuid(&row.id)?.into(),
        user_id: parse_uuid(&row.user_id)?.into(),
        name: row.name,
        view_type,
        filters,
        sort_by: row.sort_by.as_deref().and_then(|s| {
            let json = serde_json::Value::String(s.to_string());
            serde_json::from_value(json).ok()
        }),
        sort_order: SortDirection::from_str_or_default(&row.sort_order),
        is_pinned: row.is_pinned == 1,
        position: row.position,
        created_at: chrono::DateTime::parse_from_rfc3339(&row.created_at)
            .map(|dt| dt.with_timezone(&Utc))
            .unwrap_or_else(|_| Utc::now()),
        updated_at: chrono::DateTime::parse_from_rfc3339(&row.updated_at)
            .map(|dt| dt.with_timezone(&Utc))
            .unwrap_or_else(|_| Utc::now()),
    })
}