//! Dataset repository for PostgreSQL with pgvector support.
//!
//! # Testing
//!
//! TODO(#12): Improve test coverage for repository methods
//! Current tests only cover struct/serialization. Integration tests needed for:
//! - `upsert()` - insert and update paths
//! - `search()` - vector similarity queries
//! - `get_hashes_for_portal()` - delta detection queries
//! - `update_timestamp_only()` - timestamp-only updates
//!
//! Consider using testcontainers-rs for isolated PostgreSQL instances:
//! <https://github.com/testcontainers/testcontainers-rs>
//!
//! See: <https://github.com/AndreaBozzo/Ceres/issues/12>

use ceres_core::error::AppError;
use ceres_core::models::{DatabaseStats, Dataset, NewDataset, SearchResult};
use chrono::{DateTime, Utc};
use pgvector::Vector;
use sqlx::types::Json;
use sqlx::{PgPool, Pool, Postgres};
use std::collections::HashMap;
use uuid::Uuid;

/// Column list for SELECT queries. Must remain a const literal to ensure SQL safety
/// since format!() bypasses sqlx compile-time validation.
const DATASET_COLUMNS: &str = "id, original_id, source_portal, url, title, description, embedding, metadata, first_seen_at, last_updated_at, content_hash";

/// Repository for dataset persistence in PostgreSQL with pgvector.
///
/// # Examples
///
/// ```no_run
/// use sqlx::postgres::PgPoolOptions;
/// use ceres_db::DatasetRepository;
///
/// # async fn example() -> Result<(), Box<dyn std::error::Error>> {
/// let pool = PgPoolOptions::new()
///     .max_connections(5)
///     .connect("postgresql://localhost/ceres")
///     .await?;
///
/// let repo = DatasetRepository::new(pool);
/// # Ok(())
/// # }
/// ```
#[derive(Clone)]
pub struct DatasetRepository {
    pool: Pool<Postgres>,
}

impl DatasetRepository {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    /// Inserts or updates a dataset. Returns the UUID of the affected row.
    ///
    /// TODO(robustness): Return UpsertOutcome to distinguish insert vs update
    /// Currently returns only UUID without indicating operation type.
    /// Consider: `pub enum UpsertOutcome { Created(Uuid), Updated(Uuid) }`
    /// This enables accurate progress reporting in sync statistics.
    pub async fn upsert(&self, new_data: &NewDataset) -> Result<Uuid, AppError> {
        let embedding_vector = new_data.embedding.as_ref().cloned();

        let rec: (Uuid,) = sqlx::query_as(
            r#"
            INSERT INTO datasets (
                original_id,
                source_portal,
                url,
                title,
                description,
                embedding,
                metadata,
                content_hash,
                last_updated_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())
            ON CONFLICT (source_portal, original_id)
            DO UPDATE SET
                title = EXCLUDED.title,
                description = EXCLUDED.description,
                url = EXCLUDED.url,
                embedding = COALESCE(EXCLUDED.embedding, datasets.embedding),
                metadata = EXCLUDED.metadata,
                content_hash = EXCLUDED.content_hash,
                last_updated_at = NOW()
            RETURNING id
            "#,
        )
        .bind(&new_data.original_id)
        .bind(&new_data.source_portal)
        .bind(&new_data.url)
        .bind(&new_data.title)
        .bind(&new_data.description)
        .bind(embedding_vector)
        .bind(serde_json::to_value(&new_data.metadata).unwrap_or(serde_json::json!({})))
        .bind(&new_data.content_hash)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::DatabaseError)?;

        Ok(rec.0)
    }

    /// Returns a map of original_id → content_hash for all datasets from a portal.
    ///
    /// TODO(performance): Optimize for large portals (100k+ datasets)
    /// Currently loads entire HashMap into memory. Consider:
    /// (1) Streaming hash comparison during sync, or
    /// (2) Database-side hash check with WHERE clause, or
    /// (3) Bloom filter for approximate membership testing
    pub async fn get_hashes_for_portal(
        &self,
        portal_url: &str,
    ) -> Result<HashMap<String, Option<String>>, AppError> {
        let rows: Vec<HashRow> = sqlx::query_as(
            r#"
            SELECT original_id, content_hash
            FROM datasets
            WHERE source_portal = $1
            "#,
        )
        .bind(portal_url)
        .fetch_all(&self.pool)
        .await
        .map_err(AppError::DatabaseError)?;

        let hash_map: HashMap<String, Option<String>> = rows
            .into_iter()
            .map(|row| (row.original_id, row.content_hash))
            .collect();

        Ok(hash_map)
    }

    /// Updates only the timestamp for unchanged datasets. Returns true if a row was updated.
    pub async fn update_timestamp_only(
        &self,
        portal_url: &str,
        original_id: &str,
    ) -> Result<bool, AppError> {
        let result = sqlx::query(
            r#"
            UPDATE datasets
            SET last_updated_at = NOW()
            WHERE source_portal = $1 AND original_id = $2
            "#,
        )
        .bind(portal_url)
        .bind(original_id)
        .execute(&self.pool)
        .await
        .map_err(AppError::DatabaseError)?;

        Ok(result.rows_affected() > 0)
    }

    /// Retrieves a dataset by UUID.
    pub async fn get(&self, id: Uuid) -> Result<Option<Dataset>, AppError> {
        let query = format!("SELECT {} FROM datasets WHERE id = $1", DATASET_COLUMNS);
        let result = sqlx::query_as::<_, Dataset>(&query)
            .bind(id)
            .fetch_optional(&self.pool)
            .await
            .map_err(AppError::DatabaseError)?;

        Ok(result)
    }

    /// Semantic search using cosine similarity. Returns results ordered by similarity.
    pub async fn search(
        &self,
        query_vector: Vector,
        limit: usize,
    ) -> Result<Vec<SearchResult>, AppError> {
        let query = format!(
            "SELECT {}, 1 - (embedding <=> $1) as similarity_score FROM datasets WHERE embedding IS NOT NULL ORDER BY embedding <=> $1 LIMIT $2",
            DATASET_COLUMNS
        );
        let results = sqlx::query_as::<_, SearchResultRow>(&query)
            .bind(query_vector)
            .bind(limit as i64)
            .fetch_all(&self.pool)
            .await
            .map_err(AppError::DatabaseError)?;

        Ok(results
            .into_iter()
            .map(|row| SearchResult {
                dataset: Dataset {
                    id: row.id,
                    original_id: row.original_id,
                    source_portal: row.source_portal,
                    url: row.url,
                    title: row.title,
                    description: row.description,
                    embedding: row.embedding,
                    metadata: row.metadata,
                    first_seen_at: row.first_seen_at,
                    last_updated_at: row.last_updated_at,
                    content_hash: row.content_hash,
                },
                similarity_score: row.similarity_score as f32,
            })
            .collect())
    }

    /// Lists datasets with optional portal filter and limit.
    ///
    /// TODO(config): Make default limit configurable via DEFAULT_EXPORT_LIMIT env var
    /// Currently hardcoded to 10000. For large exports, consider streaming instead.
    ///
    /// TODO(performance): Implement streaming/pagination for memory efficiency
    /// Loading all datasets into memory doesn't scale. Consider returning
    /// `impl Stream<Item = Result<Dataset, AppError>>` or cursor-based pagination.
    pub async fn list_all(
        &self,
        portal_filter: Option<&str>,
        limit: Option<usize>,
    ) -> Result<Vec<Dataset>, AppError> {
        // TODO(config): Read default from DEFAULT_EXPORT_LIMIT env var
        let limit_val = limit.unwrap_or(10000) as i64;

        let datasets = if let Some(portal) = portal_filter {
            let query = format!(
                "SELECT {} FROM datasets WHERE source_portal = $1 ORDER BY last_updated_at DESC LIMIT $2",
                DATASET_COLUMNS
            );
            sqlx::query_as::<_, Dataset>(&query)
                .bind(portal)
                .bind(limit_val)
                .fetch_all(&self.pool)
                .await
                .map_err(AppError::DatabaseError)?
        } else {
            let query = format!(
                "SELECT {} FROM datasets ORDER BY last_updated_at DESC LIMIT $1",
                DATASET_COLUMNS
            );
            sqlx::query_as::<_, Dataset>(&query)
                .bind(limit_val)
                .fetch_all(&self.pool)
                .await
                .map_err(AppError::DatabaseError)?
        };

        Ok(datasets)
    }

    /// Returns aggregated database statistics.
    pub async fn get_stats(&self) -> Result<DatabaseStats, AppError> {
        let row: StatsRow = sqlx::query_as(
            r#"
            SELECT
                COUNT(*) as total,
                COUNT(embedding) as with_embeddings,
                COUNT(DISTINCT source_portal) as portals,
                MAX(last_updated_at) as last_update
            FROM datasets
            "#,
        )
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::DatabaseError)?;

        Ok(DatabaseStats {
            total_datasets: row.total.unwrap_or(0),
            datasets_with_embeddings: row.with_embeddings.unwrap_or(0),
            total_portals: row.portals.unwrap_or(0),
            last_update: row.last_update,
        })
    }
}

/// Helper struct for deserializing stats query results
#[derive(sqlx::FromRow)]
struct StatsRow {
    total: Option<i64>,
    with_embeddings: Option<i64>,
    portals: Option<i64>,
    last_update: Option<DateTime<Utc>>,
}

/// Helper struct for deserializing search query results
#[derive(sqlx::FromRow)]
struct SearchResultRow {
    id: Uuid,
    original_id: String,
    source_portal: String,
    url: String,
    title: String,
    description: Option<String>,
    embedding: Option<Vector>,
    metadata: Json<serde_json::Value>,
    first_seen_at: DateTime<Utc>,
    last_updated_at: DateTime<Utc>,
    content_hash: Option<String>,
    similarity_score: f64,
}

/// Helper struct for deserializing hash lookup query results
#[derive(sqlx::FromRow)]
struct HashRow {
    original_id: String,
    content_hash: Option<String>,
}

#[cfg(test)]
mod tests {
    use super::*;
    use serde_json::json;

    #[test]
    fn test_new_dataset_structure() {
        let title = "Test Dataset";
        let description = Some("Test description".to_string());
        let content_hash = NewDataset::compute_content_hash(title, description.as_deref());

        let new_dataset = NewDataset {
            original_id: "test-id".to_string(),
            source_portal: "https://example.com".to_string(),
            url: "https://example.com/dataset/test".to_string(),
            title: title.to_string(),
            description,
            embedding: Some(Vector::from(vec![0.1, 0.2, 0.3])),
            metadata: json!({"key": "value"}),
            content_hash,
        };

        assert_eq!(new_dataset.original_id, "test-id");
        assert_eq!(new_dataset.title, "Test Dataset");
        assert!(new_dataset.embedding.is_some());
        assert_eq!(new_dataset.content_hash.len(), 64);
    }

    #[test]
    fn test_embedding_vector_conversion() {
        let vec_f32 = vec![0.1_f32, 0.2, 0.3, 0.4];
        let vector = Vector::from(vec_f32.clone());
        assert_eq!(vector.as_slice().len(), vec_f32.len());
    }

    #[test]
    fn test_metadata_serialization() {
        let metadata = json!({
            "organization": "test-org",
            "tags": ["tag1", "tag2"]
        });

        let serialized = serde_json::to_value(&metadata).unwrap();
        assert!(serialized.is_object());
        assert_eq!(serialized["organization"], "test-org");
    }
}
