//! SQLite implementation of the `ContextRepository`.
//!
//! Contexts are stored as spans and read per day, so every query here is a
//! range overlap and none of them is per-day. See
//! [`goingson_core::Context`] for why that is the model rather than a
//! convenience.

use chrono::{NaiveDate, Utc};
use goingson_core::{
    Context, ContextId, ContextKind, ContextRepository, CoreError, Result, UserId,
};
use rusqlite::params;

use crate::Db;
use crate::utils::{
    execute, format_datetime, parse_datetime, parse_uuid, parse_uuid_opt, query_all,
};

/// The columns every context query selects.
const CONTEXT_COLUMNS: &str =
    "id, user_id, label, kind, starts_on, ends_on, migrated_from_event_id, created_at, updated_at";

const DATE: &str = "%Y-%m-%d";

pub struct SqliteContextRepository {
    db: Db,
}

impl SqliteContextRepository {
    #[tracing::instrument(skip_all)]
    pub fn new(db: Db) -> Self {
        Self { db }
    }
}

fn parse_date(value: &str) -> Result<NaiveDate> {
    NaiveDate::parse_from_str(value, DATE).map_err(|_| CoreError::parse("Invalid date"))
}

fn from_row(
    row: &rusqlite::Row<'_>,
) -> rusqlite::Result<(
    String,
    String,
    String,
    String,
    String,
    String,
    Option<String>,
    String,
    String,
)> {
    Ok((
        row.get("id")?,
        row.get("user_id")?,
        row.get("label")?,
        row.get("kind")?,
        row.get("starts_on")?,
        row.get("ends_on")?,
        row.get("migrated_from_event_id")?,
        row.get("created_at")?,
        row.get("updated_at")?,
    ))
}

type Row = (
    String,
    String,
    String,
    String,
    String,
    String,
    Option<String>,
    String,
    String,
);

fn into_context(row: Row) -> Result<Context> {
    let (id, user_id, label, kind, starts_on, ends_on, migrated_from, created_at, updated_at) = row;
    Ok(Context {
        id: parse_uuid(&id)?.into(),
        user_id: parse_uuid(&user_id)?.into(),
        label,
        kind: ContextKind::parse(&kind),
        starts_on: parse_date(&starts_on)?,
        ends_on: parse_date(&ends_on)?,
        migrated_from_event_id: parse_uuid_opt(migrated_from.as_deref())?.map(Into::into),
        created_at: parse_datetime(&created_at)?,
        updated_at: parse_datetime(&updated_at)?,
    })
}

impl ContextRepository for SqliteContextRepository {
    #[tracing::instrument(skip(self))]
    fn list_overlapping(
        &self,
        user_id: UserId,
        from: NaiveDate,
        to: NaiveDate,
    ) -> Result<Vec<Context>> {
        let conn = self.db.conn()?;
        // Both ends inclusive on both sides: a context overlaps the window when
        // it starts before the window ends and ends after the window starts.
        let rows: Vec<Row> = query_all(
            &conn,
            &format!(
                "SELECT {CONTEXT_COLUMNS} FROM contexts
                 WHERE user_id = ?1 AND starts_on <= ?3 AND ends_on >= ?2
                 ORDER BY starts_on, label"
            ),
            params![
                user_id.to_string(),
                from.format(DATE).to_string(),
                to.format(DATE).to_string()
            ],
            from_row,
        )?;
        rows.into_iter().map(into_context).collect()
    }

    #[tracing::instrument(skip(self))]
    fn list_all(&self, user_id: UserId) -> Result<Vec<Context>> {
        let conn = self.db.conn()?;
        let rows: Vec<Row> = query_all(
            &conn,
            &format!(
                "SELECT {CONTEXT_COLUMNS} FROM contexts WHERE user_id = ?1 ORDER BY starts_on, label"
            ),
            params![user_id.to_string()],
            from_row,
        )?;
        rows.into_iter().map(into_context).collect()
    }

    #[tracing::instrument(skip(self))]
    fn create(
        &self,
        user_id: UserId,
        label: &str,
        kind: ContextKind,
        starts_on: NaiveDate,
        ends_on: NaiveDate,
    ) -> Result<Context> {
        let (starts_on, ends_on) = ordered(starts_on, ends_on);
        // Truncated to what the column holds, so the record handed back is the
        // record a re-read gives. Returning a sub-second `now()` that the store
        // rounded away is how a struct comes to disagree with its own row.
        let now = stored_now();
        let context = Context {
            id: ContextId::new(),
            user_id,
            label: label.to_owned(),
            kind,
            starts_on,
            ends_on,
            migrated_from_event_id: None,
            created_at: now,
            updated_at: now,
        };

        let conn = self.db.conn()?;
        execute(
            &conn,
            "INSERT INTO contexts (id, user_id, label, kind, starts_on, ends_on, created_at, updated_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
            params![
                context.id.to_string(),
                user_id.to_string(),
                context.label,
                kind.as_str(),
                starts_on.format(DATE).to_string(),
                ends_on.format(DATE).to_string(),
                format_datetime(&now),
                format_datetime(&now),
            ],
        )?;
        Ok(context)
    }

    #[tracing::instrument(skip(self))]
    fn update(
        &self,
        user_id: UserId,
        id: ContextId,
        label: &str,
        kind: ContextKind,
        starts_on: NaiveDate,
        ends_on: NaiveDate,
    ) -> Result<Context> {
        let (starts_on, ends_on) = ordered(starts_on, ends_on);
        let now = stored_now();
        let conn = self.db.conn()?;
        let changed = execute(
            &conn,
            "UPDATE contexts SET label = ?3, kind = ?4, starts_on = ?5, ends_on = ?6, updated_at = ?7
             WHERE id = ?1 AND user_id = ?2",
            params![
                id.to_string(),
                user_id.to_string(),
                label,
                kind.as_str(),
                starts_on.format(DATE).to_string(),
                ends_on.format(DATE).to_string(),
                format_datetime(&now),
            ],
        )?;
        if changed == 0 {
            return Err(CoreError::not_found("Context", id));
        }

        let rows: Vec<Row> = query_all(
            &conn,
            &format!("SELECT {CONTEXT_COLUMNS} FROM contexts WHERE id = ?1"),
            params![id.to_string()],
            from_row,
        )?;
        rows.into_iter()
            .next()
            .ok_or_else(|| CoreError::not_found("Context", id))
            .and_then(into_context)
    }

    #[tracing::instrument(skip(self))]
    fn delete(&self, user_id: UserId, id: ContextId) -> Result<()> {
        let conn = self.db.conn()?;
        // The event this was migrated from comes back to the timeline. That is
        // the whole of what makes migration 067's guess reversible, and it is
        // one statement because the migration hid the event rather than
        // deleting it.
        execute(
            &conn,
            "UPDATE events SET converted_to_context_id = NULL WHERE converted_to_context_id = ?1",
            params![id.to_string()],
        )?;
        let changed = execute(
            &conn,
            "DELETE FROM contexts WHERE id = ?1 AND user_id = ?2",
            params![id.to_string(), user_id.to_string()],
        )?;
        if changed == 0 {
            return Err(CoreError::not_found("Context", id));
        }
        Ok(())
    }
}

/// Now, at the precision the store keeps.
///
/// SQLite holds `YYYY-MM-DD HH:MM:SS`, so a `DateTime` with sub-second
/// precision is not what comes back out.
fn stored_now() -> chrono::DateTime<Utc> {
    let now = Utc::now();
    now - chrono::Duration::nanoseconds(i64::from(now.timestamp_subsec_nanos()))
}

/// The two ends, earliest first.
///
/// A span typed backwards is a slip rather than a question: the days it names
/// are unambiguous, so it is straightened instead of refused.
fn ordered(starts_on: NaiveDate, ends_on: NaiveDate) -> (NaiveDate, NaiveDate) {
    if ends_on < starts_on {
        (ends_on, starts_on)
    } else {
        (starts_on, ends_on)
    }
}