//! # Batch Pattern Operations - Core
//!
//! Core batch operations for patterns using transactions.

use crate::TursoStorage;
use do_memory_core::{Error, Pattern, Result, episode::PatternId};
use tracing::{debug, error, info, warn};

use super::pattern_types::{BatchProgress, BatchResult};
#[path = "pattern_helpers.rs"]
mod pattern_helpers;
use pattern_helpers::extract_pattern_data;

impl TursoStorage {
    /// Store multiple patterns in a single transaction
    ///
    /// Uses prepared statements and transactions for 4-6x throughput improvement.
    /// All patterns are stored atomically - if any fails, all are rolled back.
    pub async fn store_patterns_batch(&self, patterns: Vec<Pattern>) -> Result<()> {
        if patterns.is_empty() {
            debug!("Empty patterns batch received, skipping");
            return Ok(());
        }

        debug!("Storing patterns batch: {} items", patterns.len());
        let conn = self.get_connection().await?;

        // Begin transaction
        conn.execute("BEGIN TRANSACTION", ()).await.map_err(|e| {
            Error::Storage(format!(
                "Failed to begin transaction for patterns batch: {}",
                e
            ))
        })?;

        let sql = r#"
            INSERT OR REPLACE INTO patterns (
                pattern_id, pattern_type, pattern_data, success_rate,
                context_domain, context_language, context_tags, occurrence_count,
                created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#;

        for pattern in &patterns {
            let (description, context, heuristic, success_rate, occurrence_count) =
                extract_pattern_data(pattern)?;

            let pattern_data = crate::storage::patterns::PatternDataJson {
                description,
                context: context.clone(),
                heuristic,
            };
            let pattern_data_json =
                serde_json::to_string(&pattern_data).map_err(Error::Serialization)?;

            let context_tags_json =
                serde_json::to_string(&context.tags).map_err(Error::Serialization)?;

            let now = chrono::Utc::now();

            if let Err(e) = conn
                .execute(
                    sql,
                    libsql::params![
                        pattern.id().to_string(),
                        format!("{:?}", pattern),
                        pattern_data_json,
                        success_rate,
                        context.domain.clone(),
                        context.language.clone(),
                        context_tags_json,
                        occurrence_count as i64,
                        now.timestamp(),
                        now.timestamp(),
                    ],
                )
                .await
            {
                if let Err(rollback_err) = conn.execute("ROLLBACK", ()).await {
                    error!("Failed to rollback transaction: {}", rollback_err);
                }
                return Err(Error::Storage(format!(
                    "Failed to store pattern in batch: {}",
                    e
                )));
            }
        }

        conn.execute("COMMIT", ()).await.map_err(|e| {
            Error::Storage(format!(
                "Failed to commit patterns batch transaction: {}",
                e
            ))
        })?;

        info!(
            "Successfully stored patterns batch: {} items",
            patterns.len()
        );
        Ok(())
    }

    /// Update multiple patterns in a single transaction
    pub async fn update_patterns_batch(&self, patterns: Vec<Pattern>) -> Result<()> {
        if patterns.is_empty() {
            debug!("Empty patterns update batch received, skipping");
            return Ok(());
        }

        debug!("Updating patterns batch: {} items", patterns.len());
        let conn = self.get_connection().await?;

        conn.execute("BEGIN TRANSACTION", ()).await.map_err(|e| {
            Error::Storage(format!(
                "Failed to begin transaction for patterns update batch: {}",
                e
            ))
        })?;

        // Verify all patterns exist
        for pattern in &patterns {
            let check_sql = "SELECT 1 FROM patterns WHERE pattern_id = ?";
            let mut rows = conn
                .query(check_sql, libsql::params![pattern.id().to_string()])
                .await
                .map_err(|e| {
                    Error::Storage(format!("Failed to check pattern existence in batch: {}", e))
                })?;

            let exists = rows
                .next()
                .await
                .map_err(|e| Error::Storage(format!("Failed to fetch row: {}", e)))?
                .is_some();

            if !exists {
                if let Err(rollback_err) = conn.execute("ROLLBACK", ()).await {
                    error!("Failed to rollback transaction: {}", rollback_err);
                }
                return Err(Error::Storage(format!(
                    "Pattern {} does not exist for update",
                    pattern.id()
                )));
            }
        }

        let sql = r#"
            UPDATE patterns SET
                pattern_type = ?,
                pattern_data = ?,
                success_rate = ?,
                context_domain = ?,
                context_language = ?,
                context_tags = ?,
                occurrence_count = ?,
                updated_at = ?
            WHERE pattern_id = ?
        "#;

        for pattern in &patterns {
            let (description, context, heuristic, success_rate, occurrence_count) =
                extract_pattern_data(pattern)?;

            let pattern_data = crate::storage::patterns::PatternDataJson {
                description,
                context: context.clone(),
                heuristic,
            };
            let pattern_data_json =
                serde_json::to_string(&pattern_data).map_err(Error::Serialization)?;

            let context_tags_json =
                serde_json::to_string(&context.tags).map_err(Error::Serialization)?;

            let now = chrono::Utc::now();

            if let Err(e) = conn
                .execute(
                    sql,
                    libsql::params![
                        format!("{:?}", pattern),
                        pattern_data_json,
                        success_rate,
                        context.domain.clone(),
                        context.language.clone(),
                        context_tags_json,
                        occurrence_count as i64,
                        now.timestamp(),
                        pattern.id().to_string(),
                    ],
                )
                .await
            {
                if let Err(rollback_err) = conn.execute("ROLLBACK", ()).await {
                    error!("Failed to rollback transaction: {}", rollback_err);
                }
                return Err(Error::Storage(format!(
                    "Failed to update pattern in batch: {}",
                    e
                )));
            }
        }

        conn.execute("COMMIT", ()).await.map_err(|e| {
            Error::Storage(format!(
                "Failed to commit patterns update batch transaction: {}",
                e
            ))
        })?;

        info!(
            "Successfully updated patterns batch: {} items",
            patterns.len()
        );
        Ok(())
    }

    /// Store patterns in batches with progress tracking
    pub async fn store_patterns_batch_with_progress(
        &self,
        patterns: Vec<Pattern>,
        batch_size: usize,
    ) -> Result<BatchResult> {
        if patterns.is_empty() {
            return Ok(BatchResult::success(0));
        }

        let batch_size = batch_size.max(1);
        let total = patterns.len();
        let mut progress = BatchProgress::new(total, batch_size);
        let mut errors = Vec::new();

        info!(
            "Starting batch pattern storage: {} items in {} batches",
            total, progress.total_batches
        );

        for chunk in patterns.chunks(batch_size) {
            let chunk_vec = chunk.to_vec();
            let chunk_len = chunk_vec.len();

            match self.store_patterns_batch(chunk_vec).await {
                Ok(()) => {
                    progress.update(chunk_len, chunk_len, 0);
                    debug!(
                        "Batch {}/{} complete: {} items",
                        progress.current_batch, progress.total_batches, chunk_len
                    );
                }
                Err(e) => {
                    progress.update(chunk_len, 0, chunk_len);
                    let error_msg = format!("Batch {} failed: {}", progress.current_batch, e);
                    warn!("{}", error_msg);
                    errors.push(error_msg);
                }
            }

            if progress.current_batch % 10 == 0 || progress.is_complete() {
                info!(
                    "Progress: {:.1}% ({}/{} batches, {}/{} items)",
                    progress.percent_complete(),
                    progress.current_batch,
                    progress.total_batches,
                    progress.processed,
                    total
                );
            }
        }

        let all_succeeded = errors.is_empty();
        let result = BatchResult {
            total_processed: progress.processed,
            succeeded: progress.succeeded,
            failed: progress.failed,
            all_succeeded,
            errors,
        };

        info!(
            "Batch pattern storage complete: {} succeeded, {} failed",
            result.succeeded, result.failed
        );

        Ok(result)
    }

    /// Retrieve multiple patterns by IDs in a single query
    ///
    /// Uses a single IN query for efficient bulk retrieval (4-6x improvement over individual queries).
    ///
    /// # Arguments
    ///
    /// * `pattern_ids` - Vector of pattern IDs to retrieve
    ///
    /// # Returns
    ///
    /// Vector of patterns (only patterns that exist are returned)
    ///
    /// # Example
    ///
    /// ```no_run
    /// # use do_memory_storage_turso::TursoStorage;
    /// # use do_memory_core::episode::PatternId;
    /// # use uuid::Uuid;
    /// # async fn example() -> anyhow::Result<()> {
    /// let storage = TursoStorage::new("file:test.db", "").await?;
    ///
    /// let ids = vec![
    ///     PatternId::new_v4(),
    ///     PatternId::new_v4(),
    /// ];
    ///
    /// let patterns = storage.get_patterns_batch(&ids).await?;
    /// # Ok(())
    /// # }
    /// ```
    pub async fn get_patterns_batch_by_ids(
        &self,
        pattern_ids: Vec<PatternId>,
    ) -> Result<Vec<Pattern>> {
        if pattern_ids.is_empty() {
            debug!("Empty pattern IDs batch received, returning empty vec");
            return Ok(Vec::new());
        }

        debug!("Retrieving patterns batch: {} items", pattern_ids.len());
        let conn = self.get_connection().await?;

        // Build IN clause with placeholders
        let placeholders = pattern_ids
            .iter()
            .map(|_| "?")
            .collect::<Vec<_>>()
            .join(",");

        let sql = format!(
            r#"
            SELECT pattern_id, pattern_type, pattern_data, success_rate,
                   context_domain, context_language, context_tags, occurrence_count,
                   created_at, updated_at
            FROM patterns WHERE pattern_id IN ({})
            ORDER BY success_rate DESC
        "#,
            placeholders
        );

        // Convert IDs to strings for libsql
        let id_strings: Vec<String> = pattern_ids.iter().map(|id| id.to_string()).collect();

        let params = libsql::params_from_iter(id_strings);

        let mut rows = conn
            .query(&sql, params)
            .await
            .map_err(|e| Error::Storage(format!("Failed to query patterns batch: {}", e)))?;

        let mut patterns = Vec::new();
        while let Some(row) = rows
            .next()
            .await
            .map_err(|e| Error::Storage(format!("Failed to fetch pattern row: {}", e)))?
        {
            patterns.push(crate::storage::patterns::row_to_pattern(&row)?);
        }

        info!(
            "Retrieved {} patterns from batch of {} requested",
            patterns.len(),
            pattern_ids.len()
        );
        Ok(patterns)
    }

    /// Delete multiple patterns in a single transaction
    ///
    /// All deletions are atomic - if any fails, all are rolled back.
    ///
    /// # Arguments
    ///
    /// * `pattern_ids` - Vector of pattern IDs to delete
    ///
    /// # Returns
    ///
    /// Number of patterns actually deleted
    ///
    /// # Example
    ///
    /// ```no_run
    /// # use do_memory_storage_turso::TursoStorage;
    /// # use do_memory_core::episode::PatternId;
    /// # use uuid::Uuid;
    /// # async fn example() -> anyhow::Result<()> {
    /// let storage = TursoStorage::new("file:test.db", "").await?;
    ///
    /// let ids = vec![
    ///     PatternId::new_v4(),
    ///     PatternId::new_v4(),
    /// ];
    ///
    /// let deleted = storage.delete_patterns_batch(ids).await?;
    /// println!("Deleted {} patterns", deleted);
    /// # Ok(())
    /// # }
    /// ```
    pub async fn delete_patterns_batch(&self, pattern_ids: Vec<PatternId>) -> Result<usize> {
        if pattern_ids.is_empty() {
            debug!("Empty pattern IDs batch received for deletion, skipping");
            return Ok(0);
        }

        debug!("Deleting patterns batch: {} items", pattern_ids.len());
        let conn = self.get_connection().await?;

        // Begin transaction
        conn.execute("BEGIN TRANSACTION", ()).await.map_err(|e| {
            Error::Storage(format!(
                "Failed to begin transaction for patterns deletion batch: {}",
                e
            ))
        })?;

        // Build IN clause with placeholders
        let placeholders = pattern_ids
            .iter()
            .map(|_| "?")
            .collect::<Vec<_>>()
            .join(",");

        let sql = format!(
            "DELETE FROM patterns WHERE pattern_id IN ({})",
            placeholders
        );

        // Convert IDs to strings for libsql
        let id_strings: Vec<String> = pattern_ids.iter().map(|id| id.to_string()).collect();

        let params = libsql::params_from_iter(id_strings);

        let result = match conn.execute(&sql, params).await {
            Ok(r) => r,
            Err(e) => {
                // Rollback on error
                if let Err(rollback_err) = conn.execute("ROLLBACK", ()).await {
                    error!("Failed to rollback transaction: {}", rollback_err);
                }
                return Err(Error::Storage(format!(
                    "Failed to delete patterns batch: {}",
                    e
                )));
            }
        };

        // Commit transaction
        conn.execute("COMMIT", ()).await.map_err(|e| {
            Error::Storage(format!(
                "Failed to commit patterns deletion batch transaction: {}",
                e
            ))
        })?;

        info!(
            "Successfully deleted {} patterns from batch of {} requested",
            result,
            pattern_ids.len()
        );

        Ok(result.try_into().unwrap_or(0))
    }
}
