//! SQLite implementation of the WeeklyReviewRepository.
//!
//! Provides weekly review tracking functionality including:
//! - Getting reviews for specific weeks
//! - Creating/updating reviews
//! - Checking if current week is completed

use async_trait::async_trait;
use chrono::{Datelike, NaiveDate, Utc};
use sqlx::SqlitePool;
use goingson_core::{CoreError, Result, UserId, WeeklyReview, WeeklyReviewId, WeeklyReviewRepository};

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

/// SQLite-backed implementation of [`WeeklyReviewRepository`].
///
/// Tracks weekly review completion with notes. Reviews are keyed by
/// the Monday of each ISO week.
pub struct SqliteWeeklyReviewRepository {
    pool: SqlitePool,
}

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

/// Gets the Monday of the current ISO week.
fn current_week_start() -> NaiveDate {
    let today = Utc::now().date_naive();
    // NaiveDate::week returns the ISO week, which starts on Monday
    let days_from_monday = today.weekday().num_days_from_monday();
    today - chrono::Duration::days(days_from_monday as i64)
}

#[derive(sqlx::FromRow)]
struct WeeklyReviewRow {
    id: String,
    user_id: String,
    week_start_date: String,
    completed_at: String,
    notes: String,
    vacation_days: String,
}

/// Parse comma-separated day indices into Vec<u8>.
fn parse_vacation_days(s: &str) -> Vec<u8> {
    if s.is_empty() {
        return Vec::new();
    }
    s.split(',')
        .filter_map(|d| d.trim().parse::<u8>().ok())
        .filter(|&d| d <= 6)
        .collect()
}

/// Serialize Vec<u8> into comma-separated string.
fn serialize_vacation_days(days: &[u8]) -> String {
    days.iter()
        .filter(|&&d| d <= 6)
        .map(|d| d.to_string())
        .collect::<Vec<_>>()
        .join(",")
}

impl TryFrom<WeeklyReviewRow> for WeeklyReview {
    type Error = CoreError;

    fn try_from(row: WeeklyReviewRow) -> Result<Self> {
        Ok(WeeklyReview {
            id: parse_uuid(&row.id)?.into(),
            user_id: parse_uuid(&row.user_id)?.into(),
            week_start_date: NaiveDate::parse_from_str(&row.week_start_date, "%Y-%m-%d")
                .map_err(|_| CoreError::parse("Invalid date"))?,
            completed_at: parse_datetime(&row.completed_at)?,
            notes: row.notes,
            vacation_days: parse_vacation_days(&row.vacation_days),
        })
    }
}

#[async_trait]
impl WeeklyReviewRepository for SqliteWeeklyReviewRepository {
    #[tracing::instrument(skip_all)]
    async fn get_for_week(&self, user_id: UserId, week_start: NaiveDate) -> Result<Option<WeeklyReview>> {
        let user_id_str = user_id.to_string();
        let week_start_str = week_start.format("%Y-%m-%d").to_string();

        let row: Option<WeeklyReviewRow> = sqlx::query_as(
            "SELECT id, user_id, week_start_date, completed_at, notes, vacation_days
             FROM weekly_reviews
             WHERE user_id = ? AND week_start_date = ?"
        )
        .bind(&user_id_str)
        .bind(&week_start_str)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn upsert(&self, user_id: UserId, week_start: NaiveDate, notes: &str) -> Result<WeeklyReview> {
        let user_id_str = user_id.to_string();
        let week_start_str = week_start.format("%Y-%m-%d").to_string();
        let now = Utc::now();
        let completed_at_str = format_datetime(&now);

        // Atomic upsert on the (user_id, week_start_date) unique key. Avoids the
        // get-then-insert/update race that could double-insert under a
        // concurrent first write (sync apply + a UI write). vacation_days and
        // the original id are preserved by only updating notes + completed_at.
        sqlx::query(
            "INSERT INTO weekly_reviews (id, user_id, week_start_date, completed_at, notes)
             VALUES (?, ?, ?, ?, ?)
             ON CONFLICT(user_id, week_start_date)
             DO UPDATE SET notes = excluded.notes, completed_at = excluded.completed_at",
        )
        .bind(WeeklyReviewId::new().to_string())
        .bind(&user_id_str)
        .bind(&week_start_str)
        .bind(&completed_at_str)
        .bind(notes)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        // Re-read to return the persisted row (its id may predate this call).
        self.get_for_week(user_id, week_start)
            .await?
            .ok_or_else(|| CoreError::database_msg("weekly review vanished after upsert"))
    }

    #[tracing::instrument(skip_all)]
    async fn is_current_week_completed(&self, user_id: UserId) -> Result<bool> {
        let week_start = current_week_start();
        let review = self.get_for_week(user_id, week_start).await?;
        Ok(review.is_some())
    }

    #[tracing::instrument(skip_all)]
    async fn set_vacation_days(&self, user_id: UserId, week_start: NaiveDate, days: &[u8]) -> Result<()> {
        let user_id_str = user_id.to_string();
        let week_start_str = week_start.format("%Y-%m-%d").to_string();
        let vacation_str = serialize_vacation_days(days);
        let now = format_datetime(&Utc::now());

        // Atomic upsert: insert a vacation-only row or update the existing
        // week's vacation_days, without the get-then-write race.
        sqlx::query(
            "INSERT INTO weekly_reviews (id, user_id, week_start_date, completed_at, notes, vacation_days)
             VALUES (?, ?, ?, ?, '', ?)
             ON CONFLICT(user_id, week_start_date) DO UPDATE SET vacation_days = excluded.vacation_days",
        )
        .bind(WeeklyReviewId::new().to_string())
        .bind(&user_id_str)
        .bind(&week_start_str)
        .bind(&now)
        .bind(&vacation_str)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(())
    }

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

        let rows: Vec<WeeklyReviewRow> = sqlx::query_as(
            "SELECT id, user_id, week_start_date, completed_at, notes, vacation_days
             FROM weekly_reviews
             WHERE user_id = ?
             ORDER BY week_start_date ASC",
        )
        .bind(&user_id_str)
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

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