//! SQLite implementation of the DailyNoteRepository.

use async_trait::async_trait;
use chrono::{NaiveDate, Utc};
use sqlx::SqlitePool;
use goingson_core::{CoreError, DailyNote, DailyNoteId, DailyNoteRepository, Result, UserId};

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

pub struct SqliteDailyNoteRepository {
    pool: SqlitePool,
}

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

#[derive(sqlx::FromRow)]
struct DailyNoteRow {
    id: String,
    user_id: String,
    note_date: String,
    went_well: String,
    could_improve: String,
    is_reviewed: i32,
    reviewed_at: Option<String>,
    created_at: String,
    updated_at: String,
}

impl TryFrom<DailyNoteRow> for DailyNote {
    type Error = CoreError;

    fn try_from(row: DailyNoteRow) -> Result<Self> {
        Ok(DailyNote {
            id: parse_uuid(&row.id)?.into(),
            user_id: parse_uuid(&row.user_id)?.into(),
            note_date: NaiveDate::parse_from_str(&row.note_date, "%Y-%m-%d")
                .map_err(|_| CoreError::parse("Invalid date"))?,
            went_well: row.went_well,
            could_improve: row.could_improve,
            is_reviewed: row.is_reviewed != 0,
            reviewed_at: row.reviewed_at.as_deref().map(parse_datetime).transpose()?,
            created_at: parse_datetime(&row.created_at)?,
            updated_at: parse_datetime(&row.updated_at)?,
        })
    }
}

#[async_trait]
impl DailyNoteRepository for SqliteDailyNoteRepository {
    #[tracing::instrument(skip_all)]
    async fn get_by_date(&self, user_id: UserId, date: NaiveDate) -> Result<Option<DailyNote>> {
        let user_id_str = user_id.to_string();
        let date_str = date.format("%Y-%m-%d").to_string();

        let row: Option<DailyNoteRow> = sqlx::query_as(
            "SELECT id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, created_at, updated_at
             FROM daily_notes
             WHERE user_id = ? AND note_date = ?"
        )
        .bind(&user_id_str)
        .bind(&date_str)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn list_all(&self, user_id: UserId) -> Result<Vec<DailyNote>> {
        let rows: Vec<DailyNoteRow> = sqlx::query_as(
            "SELECT id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, created_at, updated_at
             FROM daily_notes
             WHERE user_id = ?
             ORDER BY note_date ASC"
        )
        .bind(user_id.to_string())
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn upsert(
        &self,
        user_id: UserId,
        date: NaiveDate,
        went_well: &str,
        could_improve: &str,
        is_reviewed: bool,
    ) -> Result<DailyNote> {
        let user_id_str = user_id.to_string();
        let date_str = date.format("%Y-%m-%d").to_string();
        let now = Utc::now();
        let now_str = format_datetime(&now);
        let reviewed_at_str = if is_reviewed { Some(now_str.clone()) } else { None };

        let id = DailyNoteId::new();

        // Atomic upsert. A non-transactional get-then-insert/update let two
        // concurrent first-writes for the same (user, date) both see "none" and
        // both INSERT, surfacing a raw UNIQUE violation (ultra-fuzz Run #28). The
        // conflict now folds into an UPDATE; created_at is preserved on that path.
        sqlx::query(
            "INSERT INTO daily_notes (id, user_id, note_date, went_well, could_improve, is_reviewed, reviewed_at, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
             ON CONFLICT(user_id, note_date) DO UPDATE SET
                 went_well = excluded.went_well,
                 could_improve = excluded.could_improve,
                 is_reviewed = excluded.is_reviewed,
                 reviewed_at = excluded.reviewed_at,
                 updated_at = excluded.updated_at"
        )
        .bind(id.to_string())
        .bind(&user_id_str)
        .bind(&date_str)
        .bind(went_well)
        .bind(could_improve)
        .bind(is_reviewed as i32)
        .bind(&reviewed_at_str)
        .bind(&now_str)
        .bind(&now_str)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        // Read back the canonical row — on the conflict path the stored id and
        // created_at are the pre-existing ones, not the values we just generated.
        self.get_by_date(user_id, date)
            .await?
            .ok_or_else(|| CoreError::internal("daily note missing immediately after upsert"))
    }
}