//! Database migration runner and management
//!
//! This module handles database schema migrations for the `PostgreSQL`
//! trading database with proper rollback and validation capabilities.

use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::{PgPool, Row};
use std::collections::HashMap;
use std::fs;
use std::path::Path;
use thiserror::Error;
use tokio::time::Instant;

/// Migration-specific errors
#[derive(Debug, Error)]
/// MigrationError
///
/// Auto-generated documentation placeholder - enhance with specifics
pub enum MigrationError {
    #[error("Database error: {0}")]
    // Database variant
    Database(#[from] sqlx::Error),
    #[error("Migration file not found: {0}")]
    // FileNotFound variant
    FileNotFound(String),
    #[error("Invalid migration format: {0}")]
    // InvalidFormat variant
    InvalidFormat(String),
    #[error("Migration validation failed: {0}")]
    // ValidationFailed variant
    ValidationFailed(String),
    #[error("Rollback failed: {0}")]
    // RollbackFailed variant
    RollbackFailed(String),
    #[error("IO error: {0}")]
    // Io variant
    Io(#[from] std::io::Error),
}

/// Migration metadata and execution information
#[derive(Debug, Clone, Serialize, Deserialize)]
/// Migration
///
/// Auto-generated documentation placeholder - enhance with specifics
pub struct Migration {
    /// Id
    pub id: String,
    /// Name
    pub name: String,
    /// Up Sql
    pub up_sql: String,
    /// Down Sql
    pub down_sql: Option<String>,
    /// Checksum
    pub checksum: String,
    /// Applied At
    pub applied_at: Option<DateTime<Utc>>,
    /// Execution Time Ms
    pub execution_time_ms: Option<u64>,
}

/// Migration execution result
#[derive(Debug, Clone, Serialize, Deserialize)]
/// MigrationResult
///
/// Auto-generated documentation placeholder - enhance with specifics
pub struct MigrationResult {
    /// Migration Id
    pub migration_id: String,
    /// Success
    pub success: bool,
    /// Execution Time Ms
    pub execution_time_ms: u64,
    /// Error Message
    pub error_message: Option<String>,
    /// Rows Affected
    pub rows_affected: Option<u64>,
}

/// Migration runner with validation and rollback capabilities
#[derive(Debug, Clone)]
pub struct MigrationRunner {
    pool: PgPool,
    migrations_path: String,
    schema_table: String,
}

impl MigrationRunner {
    /// Create a new migration runner
    pub fn new(pool: PgPool, migrations_path: String) -> Self {
        Self {
            pool,
            migrations_path,
            schema_table: "schema_migrations".to_owned(),
        }
    }

    /// Initialize the migrations table if it doesn't exist
    pub async fn initialize(&self) -> Result<(), MigrationError> {
        let create_table_sql = format!(
            "
            CREATE TABLE IF NOT EXISTS {} (
                id VARCHAR(255) PRIMARY KEY,
                name VARCHAR(500) NOT NULL,
                checksum VARCHAR(64) NOT NULL,
                applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                execution_time_ms BIGINT NOT NULL,
    // UNIQUE variant
                UNIQUE(name)
            );
            
            CREATE INDEX IF NOT EXISTS idx_schema_migrations_applied_at 
            ON {} (applied_at);
            ",
            self.schema_table, self.schema_table
        );

        sqlx::query(&create_table_sql).execute(&self.pool).await?;

        Ok(())
    }

    /// Load all migration files from the migrations directory
    pub async fn load_migrations(&self) -> Result<Vec<Migration>, MigrationError> {
        let migrations_dir = Path::new(&self.migrations_path);
        if !migrations_dir.exists() {
            return Err(MigrationError::FileNotFound(format!(
                "Migrations directory not found: {}",
                self.migrations_path
            )));
        }

        let mut migrations = Vec::new();
        let entries = fs::read_dir(migrations_dir)?;

        for entry in entries {
            let entry = entry?;
            let path = entry.path();

            if path.extension().and_then(|s| s.to_str()) == Some("sql") {
                if let Some(file_name) = path.file_stem().and_then(|s| s.to_str()) {
                    // Skip down migrations for now, we'll pair them later
                    if file_name.ends_with("_down") {
                        continue;
                    }

                    let migration = self.load_migration_from_file(&path, file_name).await?;
                    migrations.push(migration);
                }
            }
        }

        // Sort migrations by ID to ensure consistent ordering
        migrations.sort_by(|a, b| a.id.cmp(&b.id));

        // Ok variant
        Ok(migrations)
    }

    /// Load a single migration from a file
    async fn load_migration_from_file(
        &self,
        path: &Path,
        file_name: &str,
    ) -> Result<Migration, MigrationError> {
        let up_sql = fs::read_to_string(path)?;

        // Look for corresponding down migration
        let down_path = path.with_file_name(format!("{}_down.sql", file_name));
        let down_sql = down_path
            .exists()
            .then(|| fs::read_to_string(down_path))
            .transpose()?;

        // Extract ID and name from filename (e.g., "001_up_create_tables.sql")
        let parts: Vec<&str> = file_name.split('_').collect();
        let id = if parts.len() >= 2 && parts[1] == "up" {
            parts[0].to_owned()
        } else {
            parts[0].to_owned()
        };

        let name = if parts.len() > 2 {
            parts[2..].join("_")
        } else {
            file_name.to_owned()
        };

        // Calculate checksum for validation
        let checksum = self.calculate_checksum(&up_sql);

        Ok(Migration {
            id,
            name,
            up_sql,
            down_sql,
            checksum,
            applied_at: None,
            execution_time_ms: None,
        })
    }

    /// Get list of applied migrations from the database
    pub async fn get_applied_migrations(
        &self,
    ) -> Result<HashMap<String, Migration>, MigrationError> {
        let query = format!(
            "SELECT id, name, checksum, applied_at, execution_time_ms FROM {} ORDER BY applied_at",
            self.schema_table
        );

        let rows = sqlx::query(&query).fetch_all(&self.pool).await?;

        let mut applied = HashMap::new();

        for row in rows {
            let migration = Migration {
                id: row.get("id"),
                name: row.get("name"),
                up_sql: String::new(), // Not stored in the table
                down_sql: None,
                checksum: row.get("checksum"),
                applied_at: Some(row.get("applied_at")),
                execution_time_ms: Some(row.get::<i64, _>("execution_time_ms") as u64),
            };

            applied.insert(migration.id.clone(), migration);
        }

        // Ok variant
        Ok(applied)
    }

    /// Run all pending migrations
    pub async fn run_pending_migrations(&self) -> Result<Vec<MigrationResult>, MigrationError> {
        self.initialize().await?;

        let all_migrations = self.load_migrations().await?;
        let applied_migrations = self.get_applied_migrations().await?;

        let mut results = Vec::new();

        for migration in all_migrations {
            if !applied_migrations.contains_key(&migration.id) {
                println!("Running migration: {} - {}", migration.id, migration.name);

                let result = self.execute_migration(&migration).await?;
                let success = result.success;
                results.push(result);

                if !success {
                    break; // Stop on first failure
                }
            } else {
                // Validate checksum for applied migrations
                if let Some(applied) = applied_migrations.get(&migration.id) {
                    if applied.checksum != migration.checksum {
                        return Err(MigrationError::ValidationFailed(format!(
                            "Checksum mismatch for migration {}: expected {}, got {}",
                            migration.id, applied.checksum, migration.checksum
                        )));
                    }
                }
            }
        }

        // Ok variant
        Ok(results)
    }

    /// Execute a single migration
    async fn execute_migration(
        &self,
        migration: &Migration,
    ) -> Result<MigrationResult, MigrationError> {
        let start = Instant::now();

        // Begin transaction
        let mut tx = self.pool.begin().await?;

        let result = match sqlx::query(&migration.up_sql).execute(&mut *tx).await {
            Ok(query_result) => {
                // Record the migration in the schema table
                let insert_sql = format!(
                    "INSERT INTO {} (id, name, checksum, execution_time_ms) VALUES ($1, $2, $3, $4)",
                    self.schema_table
                );

                let execution_time = start.elapsed().as_millis() as u64;

                sqlx::query(&insert_sql)
                    .bind(&migration.id)
                    .bind(&migration.name)
                    .bind(&migration.checksum)
                    .bind(execution_time as i64)
                    .execute(&mut *tx)
                    .await?;

                // Commit transaction
                tx.commit().await?;

                MigrationResult {
                    migration_id: migration.id.clone(),
                    success: true,
                    execution_time_ms: execution_time,
                    error_message: None,
                    rows_affected: Some(query_result.rows_affected()),
                }
            },
            Err(e) => {
                // Rollback transaction
                tx.rollback().await?;

                MigrationResult {
                    migration_id: migration.id.clone(),
                    success: false,
                    execution_time_ms: start.elapsed().as_millis() as u64,
                    error_message: Some(e.to_string()),
                    rows_affected: None,
                }
            },
        };

        // Ok variant
        Ok(result)
    }

    /// Rollback a specific migration (if down migration exists)
    pub async fn rollback_migration(
        &self,
        migration_id: &str,
    ) -> Result<MigrationResult, MigrationError> {
        let migrations = self.load_migrations().await?;
        let migration = migrations
            .iter()
            .find(|m| m.id == migration_id)
            .ok_or_else(|| {
                MigrationError::FileNotFound(format!("Migration {} not found", migration_id))
            })?;

        let down_sql = migration.down_sql.as_ref().ok_or_else(|| {
            MigrationError::RollbackFailed(format!(
                "No down migration available for {}",
                migration_id
            ))
        })?;

        let start = Instant::now();

        // Begin transaction
        let mut tx = self.pool.begin().await?;

        let result = match sqlx::query(down_sql).execute(&mut *tx).await {
            Ok(query_result) => {
                // Remove the migration record
                let delete_sql = format!("DELETE FROM {} WHERE id = $1", self.schema_table);
                sqlx::query(&delete_sql)
                    .bind(migration_id)
                    .execute(&mut *tx)
                    .await?;

                // Commit transaction
                tx.commit().await?;

                MigrationResult {
                    migration_id: migration_id.to_owned(),
                    success: true,
                    execution_time_ms: start.elapsed().as_millis() as u64,
                    error_message: None,
                    rows_affected: Some(query_result.rows_affected()),
                }
            },
            Err(e) => {
                // Rollback transaction
                tx.rollback().await?;

                MigrationResult {
                    migration_id: migration_id.to_owned(),
                    success: false,
                    execution_time_ms: start.elapsed().as_millis() as u64,
                    error_message: Some(e.to_string()),
                    rows_affected: None,
                }
            },
        };

        // Ok variant
        Ok(result)
    }

    /// Validate all applied migrations against their files
    pub async fn validate_migrations(&self) -> Result<Vec<String>, MigrationError> {
        let all_migrations = self.load_migrations().await?;
        let applied_migrations = self.get_applied_migrations().await?;

        let mut errors = Vec::new();

        for migration in &all_migrations {
            if let Some(applied) = applied_migrations.get(&migration.id) {
                if applied.checksum != migration.checksum {
                    errors.push(format!(
                        "Migration {} checksum mismatch: expected {}, got {}",
                        migration.id, applied.checksum, migration.checksum
                    ));
                }
            }
        }

        // Check for applied migrations without corresponding files
        for (id, _) in applied_migrations {
            if !all_migrations.iter().any(|m| m.id == id) {
                errors.push(format!(
                    "Applied migration {} has no corresponding file",
                    id
                ));
            }
        }

        // Ok variant
        Ok(errors)
    }

    /// Calculate SHA-256 checksum of migration content
    fn calculate_checksum(&self, content: &str) -> String {
        use sha2::{Digest, Sha256};
        let mut hasher = Sha256::new();
        hasher.update(content.as_bytes());
        format!("{:x}", hasher.finalize())
    }
}

/// Convenience function to run pending migrations
pub async fn run_pending_migrations(pool: &PgPool) -> Result<Vec<MigrationResult>, MigrationError> {
    let migrations_path = std::env::var("MIGRATIONS_PATH")
        .unwrap_or_else(|_| "/home/jgrusewski/Work/foxhunt/migrations".to_owned());

    let runner = MigrationRunner::new(pool.clone(), migrations_path);
    runner.run_pending_migrations().await
}

/// Convenience function to validate migrations
pub async fn validate_migrations(pool: &PgPool) -> Result<Vec<String>, MigrationError> {
    let migrations_path = std::env::var("MIGRATIONS_PATH")
        .unwrap_or_else(|_| "/home/jgrusewski/Work/foxhunt/migrations".to_owned());

    let runner = MigrationRunner::new(pool.clone(), migrations_path);
    runner.validate_migrations().await
}
