//! SQLite implementation of the MonthlyReviewRepository.
//!
//! Provides monthly goal and reflection persistence.

use async_trait::async_trait;
use chrono::Utc;
use sqlx::SqlitePool;
use goingson_core::{
    CoreError, MonthlyGoal, MonthlyGoalId, MonthlyGoalStatus, MonthlyReflection,
    MonthlyReflectionId, MonthlyReviewRepository, Result, UserId,
};

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

/// SQLite-backed implementation of [`MonthlyReviewRepository`].
pub struct SqliteMonthlyReviewRepository {
    pool: SqlitePool,
}

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

// ============ Row Types ============

#[derive(sqlx::FromRow)]
struct MonthlyGoalRow {
    id: String,
    user_id: String,
    month: String,
    text: String,
    status: String,
    position: i32,
    created_at: String,
    updated_at: String,
}

#[derive(sqlx::FromRow)]
struct MonthlyReflectionRow {
    id: String,
    user_id: String,
    month: String,
    highlight_text: String,
    change_text: String,
    completed_at: String,
}

// ============ Conversions ============

impl TryFrom<MonthlyGoalRow> for MonthlyGoal {
    type Error = CoreError;

    fn try_from(row: MonthlyGoalRow) -> Result<Self> {
        Ok(MonthlyGoal {
            id: parse_uuid(&row.id)?.into(),
            user_id: parse_uuid(&row.user_id)?.into(),
            month: row.month,
            text: row.text,
            status: row.status.parse()?,
            position: row.position,
            created_at: parse_datetime(&row.created_at)?,
            updated_at: parse_datetime(&row.updated_at)?,
        })
    }
}

impl TryFrom<MonthlyReflectionRow> for MonthlyReflection {
    type Error = CoreError;

    fn try_from(row: MonthlyReflectionRow) -> Result<Self> {
        Ok(MonthlyReflection {
            id: parse_uuid(&row.id)?.into(),
            user_id: parse_uuid(&row.user_id)?.into(),
            month: row.month,
            highlight_text: row.highlight_text,
            change_text: row.change_text,
            completed_at: parse_datetime(&row.completed_at)?,
        })
    }
}

// ============ Repository Implementation ============

#[async_trait]
impl MonthlyReviewRepository for SqliteMonthlyReviewRepository {
    #[tracing::instrument(skip_all)]
    async fn list_goals(&self, user_id: UserId, month: &str) -> Result<Vec<MonthlyGoal>> {
        let user_id_str = user_id.to_string();

        let rows: Vec<MonthlyGoalRow> = sqlx::query_as(
            "SELECT id, user_id, month, text, status, position, created_at, updated_at
             FROM monthly_goals
             WHERE user_id = ? AND month = ?
             ORDER BY position"
        )
        .bind(&user_id_str)
        .bind(month)
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn upsert_goal(&self, user_id: UserId, month: &str, text: &str, position: i32) -> Result<MonthlyGoal> {
        let user_id_str = user_id.to_string();
        let now = format_datetime(&Utc::now());

        // Check if a goal exists at this position for this month
        let existing: Option<MonthlyGoalRow> = sqlx::query_as(
            "SELECT id, user_id, month, text, status, position, created_at, updated_at
             FROM monthly_goals
             WHERE user_id = ? AND month = ? AND position = ?"
        )
        .bind(&user_id_str)
        .bind(month)
        .bind(position)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

        if let Some(existing) = existing {
            let id = existing.id.clone();
            sqlx::query(
                "UPDATE monthly_goals SET text = ?, updated_at = ? WHERE id = ?"
            )
            .bind(text)
            .bind(&now)
            .bind(&id)
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

            let mut goal = MonthlyGoal::try_from(existing)?;
            goal.text = text.to_string();
            goal.updated_at = Utc::now();
            Ok(goal)
        } else {
            let id = MonthlyGoalId::new();
            sqlx::query(
                "INSERT INTO monthly_goals (id, user_id, month, text, status, position, created_at, updated_at)
                 VALUES (?, ?, ?, ?, 'active', ?, ?, ?)"
            )
            .bind(id.to_string())
            .bind(&user_id_str)
            .bind(month)
            .bind(text)
            .bind(position)
            .bind(&now)
            .bind(&now)
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

            let now_dt = Utc::now();
            Ok(MonthlyGoal {
                id,
                user_id,
                month: month.to_string(),
                text: text.to_string(),
                status: MonthlyGoalStatus::Active,
                position,
                created_at: now_dt,
                updated_at: now_dt,
            })
        }
    }

    #[tracing::instrument(skip_all)]
    async fn update_goal_status(&self, id: MonthlyGoalId, user_id: UserId, status: &MonthlyGoalStatus) -> Result<Option<MonthlyGoal>> {
        let user_id_str = user_id.to_string();
        let id_str = id.to_string();
        let now = format_datetime(&Utc::now());

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

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

        let row: MonthlyGoalRow = sqlx::query_as(
            "SELECT id, user_id, month, text, status, position, created_at, updated_at
             FROM monthly_goals WHERE id = ?"
        )
        .bind(&id_str)
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(Some(MonthlyGoal::try_from(row)?))
    }

    #[tracing::instrument(skip_all)]
    async fn delete_goal(&self, id: MonthlyGoalId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query(
            "DELETE FROM monthly_goals 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 get_reflection(&self, user_id: UserId, month: &str) -> Result<Option<MonthlyReflection>> {
        let row: Option<MonthlyReflectionRow> = sqlx::query_as(
            "SELECT id, user_id, month, highlight_text, change_text, completed_at
             FROM monthly_reflections
             WHERE user_id = ? AND month = ?"
        )
        .bind(user_id.to_string())
        .bind(month)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn upsert_reflection(&self, user_id: UserId, month: &str, highlight: &str, change: &str) -> Result<MonthlyReflection> {
        let user_id_str = user_id.to_string();
        let now = Utc::now();
        let now_str = format_datetime(&now);

        let existing = self.get_reflection(user_id, month).await?;

        let id = if let Some(existing) = existing {
            sqlx::query(
                "UPDATE monthly_reflections SET highlight_text = ?, change_text = ?, completed_at = ?
                 WHERE id = ?"
            )
            .bind(highlight)
            .bind(change)
            .bind(&now_str)
            .bind(existing.id.to_string())
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

            existing.id
        } else {
            let id = MonthlyReflectionId::new();
            sqlx::query(
                "INSERT INTO monthly_reflections (id, user_id, month, highlight_text, change_text, completed_at)
                 VALUES (?, ?, ?, ?, ?, ?)"
            )
            .bind(id.to_string())
            .bind(&user_id_str)
            .bind(month)
            .bind(highlight)
            .bind(change)
            .bind(&now_str)
            .execute(&self.pool)
            .await
            .map_err(CoreError::database)?;

            id
        };

        Ok(MonthlyReflection {
            id,
            user_id,
            month: month.to_string(),
            highlight_text: highlight.to_string(),
            change_text: change.to_string(),
            completed_at: now,
        })
    }
}