//! SQLite implementation of the ProblemRepository.
//!
//! Backs the Problems inbox: a mirror of problems pulled from external sources
//! (wam tickets, /audit and /fuzz findings), ranked by painhours and promoted by
//! hand into tasks.
//!
//! Two rules shape the SQL here. Ingest upserts on the provenance key and
//! touches only the columns the source owns, so a re-pull never undoes triage.
//! And ordering happens in Rust, not `ORDER BY`, because painhours depends on
//! the current time.

use async_trait::async_trait;
use chrono::{DateTime, Utc};
use goingson_core::{
    CoreError, DbValue, NewProblem, ParseableEnum, Problem, ProblemFilter, ProblemId,
    ProblemRepository, ProblemStatus, ProjectId, Result, TaskId, UserId,
};
use sqlx::SqlitePool;

use crate::utils::{
    format_datetime, format_datetime_now, parse_datetime, parse_tags, parse_uuid, parse_uuid_opt,
};

const COLUMNS: &str = "id, source, source_ref, title, body, pain, scale, status, project_id, \
     tags, promoted_task_id, created_at, updated_at, settled_at, last_seen_at";

/// Database row struct for Problem
#[derive(Debug, Clone, sqlx::FromRow)]
struct ProblemRow {
    pub id: String,
    pub source: String,
    pub source_ref: String,
    pub title: String,
    pub body: String,
    pub pain: i64,
    pub scale: i64,
    pub status: String,
    pub project_id: Option<String>,
    pub tags: String,
    pub promoted_task_id: Option<String>,
    pub created_at: String,
    pub updated_at: String,
    pub settled_at: Option<String>,
    pub last_seen_at: String,
}

impl TryFrom<ProblemRow> for Problem {
    type Error = CoreError;

    fn try_from(row: ProblemRow) -> std::result::Result<Self, Self::Error> {
        Ok(Problem {
            id: parse_uuid(&row.id)?.into(),
            source: row.source,
            source_ref: row.source_ref,
            title: row.title,
            body: row.body,
            // The 1-5 factors are clamped by the painhours crate on use, so a
            // source that wrote something wild ranks as the nearest valid
            // factor instead of failing the whole fetch.
            pain: row.pain.clamp(0, 255) as u8,
            scale: row.scale.clamp(0, 255) as u8,
            status: ProblemStatus::from_str_or_default(&row.status),
            project_id: parse_uuid_opt(row.project_id.as_deref())?.map(Into::into),
            tags: parse_tags(&row.tags),
            promoted_task_id: parse_uuid_opt(row.promoted_task_id.as_deref())?.map(Into::into),
            created_at: parse_datetime(&row.created_at)?,
            updated_at: parse_datetime(&row.updated_at)?,
            settled_at: row.settled_at.as_deref().map(parse_datetime).transpose()?,
            last_seen_at: parse_datetime(&row.last_seen_at)?,
        })
    }
}

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

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

    /// Applies a status transition and returns the updated problem.
    ///
    /// Shared by [`ProblemRepository::promote`] and
    /// [`ProblemRepository::set_status`], because both have to keep `status`,
    /// `settled_at`, and `promoted_task_id` consistent: settling stamps the
    /// freeze instant, reopening clears both it and the backlink.
    async fn transition(
        &self,
        id: ProblemId,
        user_id: UserId,
        status: ProblemStatus,
        task_id: Option<TaskId>,
    ) -> Result<Option<Problem>> {
        let settled_at = status.is_settled().then(format_datetime_now);

        let result = sqlx::query(
            r"
            UPDATE problems
            SET status = ?, settled_at = ?, promoted_task_id = ?, updated_at = ?
            WHERE id = ? AND user_id = ?
            ",
        )
        .bind(status.db_value())
        .bind(&settled_at)
        .bind(task_id.map(|t| t.to_string()))
        .bind(format_datetime_now())
        .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)
        }
    }
}

#[async_trait]
impl ProblemRepository for SqliteProblemRepository {
    #[tracing::instrument(skip_all)]
    async fn list(&self, user_id: UserId, filter: &ProblemFilter) -> Result<Vec<Problem>> {
        let mut sql = format!("SELECT {COLUMNS} FROM problems WHERE user_id = ?");
        if filter.source.is_some() {
            sql.push_str(" AND source = ?");
        }
        if filter.status.is_some() {
            sql.push_str(" AND status = ?");
        }
        if filter.project_id.is_some() {
            sql.push_str(" AND project_id = ?");
        }

        let mut query =
            sqlx::query_as::<_, ProblemRow>(sqlx::AssertSqlSafe(sql)).bind(user_id.to_string());
        if let Some(source) = &filter.source {
            query = query.bind(source.clone());
        }
        if let Some(status) = filter.status {
            query = query.bind(status.db_value());
        }
        if let Some(project_id) = filter.project_id {
            query = query.bind(project_id.to_string());
        }

        let rows = query
            .fetch_all(&self.pool)
            .await
            .map_err(CoreError::database)?;

        let mut problems: Vec<Problem> = rows
            .into_iter()
            .map(Problem::try_from)
            .collect::<Result<_>>()?;

        // Highest painhours first, ties broken by newest. Sorted here rather
        // than in SQL because the score depends on the current time.
        problems.sort_by(|a, b| {
            b.painhours()
                .cmp(&a.painhours())
                .then_with(|| b.created_at.cmp(&a.created_at))
        });

        Ok(problems)
    }

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: ProblemId, user_id: UserId) -> Result<Option<Problem>> {
        let row = sqlx::query_as::<_, ProblemRow>(sqlx::AssertSqlSafe(format!(
            "SELECT {COLUMNS} FROM problems 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(Problem::try_from).transpose()
    }

    #[tracing::instrument(skip_all)]
    async fn find_by_source_ref(
        &self,
        user_id: UserId,
        source: &str,
        source_ref: &str,
    ) -> Result<Option<Problem>> {
        let row = sqlx::query_as::<_, ProblemRow>(sqlx::AssertSqlSafe(format!(
            "SELECT {COLUMNS} FROM problems WHERE user_id = ? AND source = ? AND source_ref = ?"
        )))
        .bind(user_id.to_string())
        .bind(source)
        .bind(source_ref)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn ingest(&self, user_id: UserId, problem: NewProblem) -> Result<Problem> {
        let tags_json = serde_json::to_string(&problem.tags).unwrap_or_else(|_| "[]".to_string());
        let now = format_datetime_now();

        // A source reporting the problem as fixed settles it, but only if the
        // human has not already ruled on it: `status = 'Open'` in the CASE
        // guards promoted and dismissed rows from being overwritten.
        let (status, settled_at) = if problem.resolved_upstream {
            (ProblemStatus::Resolved, Some(now.clone()))
        } else {
            (ProblemStatus::Open, None)
        };

        sqlx::query(
            r"
            INSERT INTO problems (
                id, user_id, source, source_ref, title, body, pain, scale, status,
                project_id, tags, created_at, updated_at, settled_at, last_seen_at
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(user_id, source, source_ref) DO UPDATE SET
                title = excluded.title,
                body = excluded.body,
                pain = excluded.pain,
                scale = excluded.scale,
                tags = excluded.tags,
                created_at = excluded.created_at,
                updated_at = excluded.updated_at,
                last_seen_at = excluded.last_seen_at,
                project_id = COALESCE(problems.project_id, excluded.project_id),
                status = CASE
                    WHEN excluded.status = 'Resolved' AND problems.status = 'Open'
                        THEN 'Resolved'
                    ELSE problems.status
                END,
                settled_at = CASE
                    WHEN excluded.status = 'Resolved' AND problems.status = 'Open'
                        THEN excluded.settled_at
                    ELSE problems.settled_at
                END
            ",
        )
        .bind(ProblemId::new().to_string())
        .bind(user_id.to_string())
        .bind(&problem.source)
        .bind(&problem.source_ref)
        .bind(&problem.title)
        .bind(&problem.body)
        .bind(i64::from(problem.pain))
        .bind(i64::from(problem.scale))
        .bind(status.db_value())
        .bind(problem.project_id.map(|p| p.to_string()))
        .bind(&tags_json)
        .bind(format_datetime(&problem.created_at))
        .bind(format_datetime(&problem.updated_at))
        .bind(&settled_at)
        .bind(&now)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        // Re-read by provenance key rather than by the id above: on a conflict
        // that id was discarded and the existing row kept its own.
        self.find_by_source_ref(user_id, &problem.source, &problem.source_ref)
            .await?
            .ok_or_else(|| CoreError::internal("Failed to retrieve ingested problem"))
    }

    #[tracing::instrument(skip_all)]
    async fn promote(
        &self,
        id: ProblemId,
        user_id: UserId,
        task_id: TaskId,
    ) -> Result<Option<Problem>> {
        self.transition(id, user_id, ProblemStatus::Promoted, Some(task_id))
            .await
    }

    #[tracing::instrument(skip_all)]
    async fn set_status(
        &self,
        id: ProblemId,
        user_id: UserId,
        status: ProblemStatus,
    ) -> Result<Option<Problem>> {
        // Reopening drops the backlink; a problem sent back to triage has no
        // solution attached. Promoting through this path is a caller error
        // (there is no task to link), so it lands as Promoted with no backlink
        // rather than silently inventing one.
        self.transition(id, user_id, status, None).await
    }

    #[tracing::instrument(skip_all)]
    async fn set_project(
        &self,
        id: ProblemId,
        user_id: UserId,
        project_id: Option<ProjectId>,
    ) -> Result<Option<Problem>> {
        let result = sqlx::query(
            r"
            UPDATE problems SET project_id = ?, updated_at = ?
            WHERE id = ? AND user_id = ?
            ",
        )
        .bind(project_id.map(|p| p.to_string()))
        .bind(format_datetime_now())
        .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: ProblemId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query("DELETE FROM problems 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 last_pulled_at(&self, user_id: UserId, source: &str) -> Result<Option<DateTime<Utc>>> {
        let raw: Option<String> = sqlx::query_scalar(
            "SELECT MAX(last_seen_at) FROM problems WHERE user_id = ? AND source = ?",
        )
        .bind(user_id.to_string())
        .bind(source)
        .fetch_one(&self.pool)
        .await
        .map_err(CoreError::database)?;

        raw.as_deref().map(parse_datetime).transpose()
    }
}