//! SQLite implementation of the AttachmentRepository.

use async_trait::async_trait;
use sqlx::SqlitePool;
use goingson_core::{
    Attachment, AttachmentId, CoreError, EmailId, NewAttachment, ProjectId, Result, TaskId, UserId,
    AttachmentRepository,
};

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

/// Database row struct for Attachment.
#[derive(Debug, Clone, sqlx::FromRow)]
struct AttachmentRow {
    pub id: String,
    pub user_id: String,
    pub task_id: Option<String>,
    pub project_id: Option<String>,
    pub filename: String,
    pub file_size: i64,
    pub mime_type: String,
    pub blob_hash: String,
    pub source_email_id: Option<String>,
    pub created_at: String,
}

impl TryFrom<AttachmentRow> for Attachment {
    type Error = CoreError;

    fn try_from(row: AttachmentRow) -> std::result::Result<Self, Self::Error> {
        Ok(Attachment {
            id: parse_uuid(&row.id)?.into(),
            user_id: parse_uuid(&row.user_id)?.into(),
            task_id: parse_uuid_opt(row.task_id.as_deref())?.map(TaskId::from),
            project_id: parse_uuid_opt(row.project_id.as_deref())?.map(ProjectId::from),
            filename: row.filename,
            file_size: row.file_size,
            mime_type: row.mime_type,
            blob_hash: row.blob_hash,
            source_email_id: parse_uuid_opt(row.source_email_id.as_deref())?.map(EmailId::from),
            created_at: parse_datetime(&row.created_at)?,
        })
    }
}

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

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

const SELECT_COLS: &str = "id, user_id, task_id, project_id, filename, file_size, mime_type, blob_hash, source_email_id, created_at";

#[async_trait]
impl AttachmentRepository for SqliteAttachmentRepository {
    #[tracing::instrument(skip_all)]
    async fn create(&self, user_id: UserId, attachment: NewAttachment) -> Result<Attachment> {
        let id = AttachmentId::new();
        let now = format_datetime_now();

        sqlx::query(
            r#"
            INSERT INTO attachments (id, user_id, task_id, project_id, filename, file_size, mime_type, blob_hash, source_email_id, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .bind(attachment.task_id.map(|t| t.to_string()))
        .bind(attachment.project_id.map(|p| p.to_string()))
        .bind(&attachment.filename)
        .bind(attachment.file_size)
        .bind(&attachment.mime_type)
        .bind(&attachment.blob_hash)
        .bind(attachment.source_email_id.map(|e| e.to_string()))
        .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 attachment"))
    }

    #[tracing::instrument(skip_all)]
    async fn list_for_task(&self, task_id: TaskId, user_id: UserId) -> Result<Vec<Attachment>> {
        let sql = format!(
            "SELECT {} FROM attachments WHERE task_id = ? AND user_id = ? ORDER BY created_at ASC",
            SELECT_COLS
        );
        let rows = sqlx::query_as::<_, AttachmentRow>(&sql)
            .bind(task_id.to_string())
            .bind(user_id.to_string())
            .fetch_all(&self.pool)
            .await
            .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn list_for_project(&self, project_id: ProjectId, user_id: UserId) -> Result<Vec<Attachment>> {
        let sql = format!(
            "SELECT {} FROM attachments WHERE project_id = ? AND user_id = ? ORDER BY created_at ASC",
            SELECT_COLS
        );
        let rows = sqlx::query_as::<_, AttachmentRow>(&sql)
            .bind(project_id.to_string())
            .bind(user_id.to_string())
            .fetch_all(&self.pool)
            .await
            .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: AttachmentId, user_id: UserId) -> Result<Option<Attachment>> {
        let sql = format!(
            "SELECT {} FROM attachments WHERE id = ? AND user_id = ?",
            SELECT_COLS
        );
        let row = sqlx::query_as::<_, AttachmentRow>(&sql)
            .bind(id.to_string())
            .bind(user_id.to_string())
            .fetch_optional(&self.pool)
            .await
            .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn delete(&self, id: AttachmentId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query("DELETE FROM attachments 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 list_by_blob_hash(&self, blob_hash: &str, user_id: UserId) -> Result<Vec<Attachment>> {
        let sql = format!(
            "SELECT {} FROM attachments WHERE blob_hash = ? AND user_id = ? ORDER BY created_at ASC",
            SELECT_COLS
        );
        let rows = sqlx::query_as::<_, AttachmentRow>(&sql)
            .bind(blob_hash)
            .bind(user_id.to_string())
            .fetch_all(&self.pool)
            .await
            .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn list_all_blob_hashes(&self, user_id: UserId) -> Result<Vec<String>> {
        let rows: Vec<(String,)> = sqlx::query_as(
            "SELECT DISTINCT blob_hash FROM attachments WHERE user_id = ?"
        )
        .bind(user_id.to_string())
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

        Ok(rows.into_iter().map(|(h,)| h).collect())
    }
}