//! SQLite implementation of the MilestoneRepository.
//!
//! Manages milestones within projects, providing:
//! - CRUD operations
//! - Ordering/reordering within a project
//! - Status tracking (open/completed)

use async_trait::async_trait;
use chrono::NaiveDate;
use sqlx::SqlitePool;
use goingson_core::{
    CoreError, DbValue, Milestone, MilestoneId, MilestoneRepository, MilestoneStatus,
    NewMilestone, ParseableEnum, ProjectId, Result, UserId,
};

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

/// Database row struct for Milestone.
#[derive(Debug, Clone, sqlx::FromRow)]
struct MilestoneRow {
    pub id: String,
    pub user_id: String,
    pub project_id: String,
    pub name: String,
    pub description: String,
    pub position: i32,
    pub target_date: Option<String>,
    pub status: String,
    pub created_at: String,
}

impl TryFrom<MilestoneRow> for Milestone {
    type Error = CoreError;

    fn try_from(row: MilestoneRow) -> std::result::Result<Self, Self::Error> {
        let target_date = row.target_date
            .as_deref()
            .filter(|s| !s.is_empty())
            .map(|s| NaiveDate::parse_from_str(s, "%Y-%m-%d"))
            .transpose()
            .map_err(|_| CoreError::parse("Invalid milestone target_date"))?;

        Ok(Milestone {
            id: parse_uuid(&row.id)?.into(),
            user_id: parse_uuid(&row.user_id)?.into(),
            project_id: parse_uuid(&row.project_id)?.into(),
            name: row.name,
            description: row.description,
            position: row.position,
            target_date,
            status: MilestoneStatus::from_str_or_default(&row.status),
            created_at: parse_datetime(&row.created_at)?,
        })
    }
}

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

impl SqliteMilestoneRepository {
    /// 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 MilestoneRepository for SqliteMilestoneRepository {
    #[tracing::instrument(skip_all)]
    async fn list_by_project(&self, project_id: ProjectId, user_id: UserId) -> Result<Vec<Milestone>> {
        let rows = sqlx::query_as::<_, MilestoneRow>(
            r#"
            SELECT id, user_id, project_id, name, description, position, target_date, status, created_at
            FROM milestones
            WHERE project_id = ? AND user_id = ?
            ORDER BY position ASC, created_at ASC
            "#,
        )
        .bind(project_id.to_string())
        .bind(user_id.to_string())
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: MilestoneId, user_id: UserId) -> Result<Option<Milestone>> {
        let row = sqlx::query_as::<_, MilestoneRow>(
            r#"
            SELECT id, user_id, project_id, name, description, position, target_date, status, created_at
            FROM milestones
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn create(&self, user_id: UserId, milestone: NewMilestone) -> Result<Milestone> {
        let id = MilestoneId::new();
        let now = format_datetime_now();
        let target_date_str = milestone.target_date.map(|d| d.format("%Y-%m-%d").to_string());

        sqlx::query(
            r#"
            INSERT INTO milestones (id, user_id, project_id, name, description, position, target_date, status, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, 'open', ?)
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .bind(milestone.project_id.to_string())
        .bind(&milestone.name)
        .bind(&milestone.description)
        .bind(milestone.position)
        .bind(&target_date_str)
        .bind(&now)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        self.get_by_id(id, user_id)
            .await?
            .ok_or_else(|| CoreError::internal("Failed to retrieve created milestone"))
    }

    #[tracing::instrument(skip_all)]
    async fn update(
        &self,
        id: MilestoneId,
        user_id: UserId,
        name: &str,
        description: &str,
        target_date: Option<NaiveDate>,
        status: &MilestoneStatus,
    ) -> Result<Option<Milestone>> {
        let target_date_str = target_date.map(|d| d.format("%Y-%m-%d").to_string());

        let result = sqlx::query(
            r#"
            UPDATE milestones
            SET name = ?, description = ?, target_date = ?, status = ?
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(name)
        .bind(description)
        .bind(&target_date_str)
        .bind(status.db_value())
        .bind(id.to_string())
        .bind(user_id.to_string())
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn delete(&self, id: MilestoneId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query("DELETE FROM milestones 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 reorder(&self, project_id: ProjectId, user_id: UserId, milestone_ids: &[MilestoneId]) -> Result<()> {
        let mut tx = self.pool.begin().await.map_err(CoreError::database)?;
        for (i, id) in milestone_ids.iter().enumerate() {
            sqlx::query(
                "UPDATE milestones SET position = ? WHERE id = ? AND user_id = ? AND project_id = ?"
            )
            .bind(i as i32)
            .bind(id.to_string())
            .bind(user_id.to_string())
            .bind(project_id.to_string())
            .execute(&mut *tx)
            .await
            .map_err(CoreError::database)?;
        }
        tx.commit().await.map_err(CoreError::database)?;
        Ok(())
    }
}