//! SQLite implementation of the ProjectRepository.
//!
//! Manages projects, which are the top-level organizational unit in GoingsOn.
//! Projects can be of various types (Job, SideProject, Company, etc.) and have
//! associated tasks, events, and emails.

use async_trait::async_trait;
use sqlx::SqlitePool;
use goingson_core::{
    CoreError, DbValue, NewProject, ParseableEnum, Project, ProjectId, ProjectRepository,
    ProjectStatus, ProjectType, Result, UpdateProject, UserId,
};

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

/// Database row struct for Project
#[derive(Debug, Clone, sqlx::FromRow)]
struct ProjectRow {
    pub id: String,
    pub name: String,
    pub description: String,
    pub project_type: String,
    pub status: String,
    pub created_at: String,
}

impl TryFrom<ProjectRow> for Project {
    type Error = CoreError;

    fn try_from(row: ProjectRow) -> std::result::Result<Self, Self::Error> {
        Ok(Project {
            id: parse_uuid(&row.id)?.into(),
            name: row.name,
            description: row.description,
            project_type: ProjectType::from_str_or_default(&row.project_type),
            status: ProjectStatus::from_str_or_default(&row.status),
            created_at: parse_datetime(&row.created_at)?,
        })
    }
}

/// SQLite-backed implementation of [`ProjectRepository`].
///
/// Provides CRUD operations for projects with automatic UUID generation
/// and timestamp management.
pub struct SqliteProjectRepository {
    pool: SqlitePool,
}

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

#[async_trait]
impl ProjectRepository for SqliteProjectRepository {
    #[tracing::instrument(skip_all)]
    async fn list_all(&self, user_id: UserId) -> Result<Vec<Project>> {
        let rows = sqlx::query_as::<_, ProjectRow>(
            r#"
            SELECT id, name, description, project_type, status, created_at
            FROM projects
            WHERE user_id = ?
            ORDER BY created_at DESC
            "#,
        )
        .bind(user_id.to_string())
        .fetch_all(&self.pool)
        .await
        .map_err(CoreError::database)?;

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

    #[tracing::instrument(skip_all)]
    async fn get_by_id(&self, id: ProjectId, user_id: UserId) -> Result<Option<Project>> {
        let row = sqlx::query_as::<_, ProjectRow>(
            r#"
            SELECT id, name, description, project_type, status, created_at
            FROM projects
            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(Project::try_from).transpose()
    }

    #[tracing::instrument(skip_all)]
    async fn create(&self, user_id: UserId, project: NewProject) -> Result<Project> {
        let id = ProjectId::new();
        let now = format_datetime_now();

        sqlx::query(
            r#"
            INSERT INTO projects (id, user_id, name, description, project_type, status, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(id.to_string())
        .bind(user_id.to_string())
        .bind(&project.name)
        .bind(&project.description)
        .bind(project.project_type.db_value())
        .bind(project.status.db_value())
        .bind(&now)
        .execute(&self.pool)
        .await
        .map_err(CoreError::database)?;

        // Fetch the created project
        self.get_by_id(id, user_id)
            .await?
            .ok_or_else(|| CoreError::internal("Failed to retrieve created project"))
    }

    #[tracing::instrument(skip_all)]
    async fn update(
        &self,
        id: ProjectId,
        user_id: UserId,
        project: UpdateProject,
    ) -> Result<Option<Project>> {
        let result = sqlx::query(
            r#"
            UPDATE projects
            SET name = ?, description = ?, project_type = ?, status = ?
            WHERE id = ? AND user_id = ?
            "#,
        )
        .bind(&project.name)
        .bind(&project.description)
        .bind(project.project_type.db_value())
        .bind(project.status.db_value())
        .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: ProjectId, user_id: UserId) -> Result<bool> {
        let result = sqlx::query("DELETE FROM projects 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 find_by_name(&self, user_id: UserId, name: &str) -> Result<Option<Project>> {
        let row = sqlx::query_as::<_, ProjectRow>(
            r#"
            SELECT id, name, description, project_type, status, created_at
            FROM projects
            WHERE user_id = ? AND LOWER(name) = LOWER(?)
            "#,
        )
        .bind(user_id.to_string())
        .bind(name)
        .fetch_optional(&self.pool)
        .await
        .map_err(CoreError::database)?;

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