use chrono::Utc;
use flalingo_lib::models::{
    exercise::Exercise,
    node::Node,
    path::{Metadata, Path},
};
use sqlx::{migrate::MigrateDatabase, Sqlite, SqlitePool};
use tokio::fs;

/// Test database utilities for creating and managing test databases
pub struct TestDb {
    pub pool: SqlitePool,
    pub db_url: String,
}

impl TestDb {
    /// Create a new test database with a unique name
    pub async fn new() -> Result<Self, Box<dyn std::error::Error>> {
        let test_id = uuid::Uuid::new_v4().to_string();
        let db_url = format!("sqlite:./test_dbs/test_{}.db", test_id);

        // Ensure test_dbs directory exists
        fs::create_dir_all("./test_dbs").await?;

        // Create database if it doesn't exist
        if !Sqlite::database_exists(&db_url).await? {
            Sqlite::create_database(&db_url).await?;
        }

        let pool = SqlitePool::connect(&db_url).await?;

        // Run migrations
        sqlx::migrate!("./migrations").run(&pool).await?;

        Ok(TestDb { pool, db_url })
    }

    /// Close the database connection and delete the test database file
    pub async fn cleanup(self) -> Result<(), Box<dyn std::error::Error>> {
        self.pool.close().await;

        // Extract file path from URL
        let file_path = self.db_url.replace("sqlite:", "");
        if tokio::fs::metadata(&file_path).await.is_ok() {
            tokio::fs::remove_file(&file_path).await?;
        }

        Ok(())
    }

    /// Seed the database with test data
    pub async fn seed_test_data(&self) -> Result<(), Box<dyn std::error::Error>> {
        // Insert test path
        sqlx::query("INSERT INTO path (id, title, description) VALUES (?, ?, ?)")
            .bind("test_path_001")
            .bind("Test Path")
            .bind("A path for testing")
            .execute(&self.pool)
            .await?;

        // Insert test metadata
        sqlx::query(
            "INSERT INTO pathMetadata (pathId, version, created_at, updated_at) VALUES (?, ?, ?, ?)"
        )
        .bind("test_path_001")
        .bind("1.0.0")
        .bind("2024-01-01T10:00:00Z")
        .bind("2024-01-01T10:00:00Z")
        .execute(&self.pool)
        .await?;

        // Insert test node
        sqlx::query("INSERT INTO node (id, title, description, pathId) VALUES (?, ?, ?, ?)")
            .bind(1_i64)
            .bind("Test Node")
            .bind("A node for testing")
            .bind("test_path_001")
            .execute(&self.pool)
            .await?;

        // Insert test exercises
        sqlx::query(
            "INSERT INTO exercise (id, ex_type, content, nodeId, pathId) VALUES (?, ?, ?, ?, ?)",
        )
        .bind(1_i64)
        .bind("vocabulary")
        .bind("{\"word\": \"Test\", \"translation\": \"Test\"}")
        .bind(1_i64)
        .bind("test_path_001")
        .execute(&self.pool)
        .await?;

        sqlx::query(
            "INSERT INTO exercise (id, ex_type, content, nodeId, pathId) VALUES (?, ?, ?, ?, ?)",
        )
        .bind(2_i64)
        .bind("multiple_choice")
        .bind("{\"question\": \"Test?\", \"options\": [\"A\", \"B\"], \"correct\": 0}")
        .bind(1_i64)
        .bind("test_path_001")
        .execute(&self.pool)
        .await?;

        Ok(())
    }

    /// Clear all data from the test database
    pub async fn clear_data(&self) -> Result<(), Box<dyn std::error::Error>> {
        sqlx::query("DELETE FROM exercise")
            .execute(&self.pool)
            .await?;
        sqlx::query("DELETE FROM node").execute(&self.pool).await?;
        sqlx::query("DELETE FROM pathMetadata")
            .execute(&self.pool)
            .await?;
        sqlx::query("DELETE FROM path").execute(&self.pool).await?;
        Ok(())
    }
}

/// Helper functions for creating test data
pub mod test_data {
    use super::*;

    pub fn create_test_path() -> Path {
        let now = Utc::now();

        let metadata = vec![Metadata {
            path_id: "test_path_001".to_string(),
            version: "1.0.0".to_string(),
            created_at: now,
            updated_at: now,
        }];

        let exercises = vec![
            Exercise {
                id: 1,
                ex_type: "vocabulary".to_string(),
                content: r#"{"word": "Hallo", "translation": "Hello", "example": "Hallo, wie geht's?"}"#.to_string(),
                node_id: 1,
            },
            Exercise {
                id: 2,
                ex_type: "multiple_choice".to_string(),
                content: r#"{"question": "How do you say 'goodbye' in German?", "options": ["Tschüss", "Hallo", "Bitte", "Danke"], "correct": 0}"#.to_string(),
                node_id: 1,
            },
        ];

        let nodes = vec![Node {
            id: 1,
            title: "Basic Greetings".to_string(),
            description: "Learn essential German greetings".to_string(),
            path_id: "test_path_001".to_string(),
            exercises,
        }];

        Path {
            id: "test_path_001".to_string(),
            title: "German Basics Test".to_string(),
            description: "A test path for demonstrating functionality".to_string(),
            metadata,
            nodes,
        }
    }

    pub fn create_test_exercise(id: u32, node_id: u32) -> Exercise {
        Exercise {
            id,
            ex_type: "vocabulary".to_string(),
            content: format!(
                r#"{{"word": "TestWord{}", "translation": "TestTranslation{}", "example": "This is test {}."}}"#,
                id, id, id
            ),
            node_id,
        }
    }

    pub fn create_test_node(id: u32, path_id: &str) -> Node {
        Node {
            id,
            title: format!("Test Node {}", id),
            description: format!("Description for test node {}", id),
            path_id: path_id.to_string(),
            exercises: vec![create_test_exercise(id, id)],
        }
    }

    pub fn create_test_metadata(path_id: &str, version: &str) -> Metadata {
        let now = Utc::now();
        Metadata {
            path_id: path_id.to_string(),
            version: version.to_string(),
            created_at: now,
            updated_at: now,
        }
    }
}

/// Test assertions and utilities
pub mod assertions {
    use super::*;

    pub fn assert_paths_equal(expected: &Path, actual: &Path) {
        assert_eq!(expected.id, actual.id);
        assert_eq!(expected.title, actual.title);
        assert_eq!(expected.description, actual.description);
        assert_eq!(expected.metadata.len(), actual.metadata.len());
        assert_eq!(expected.nodes.len(), actual.nodes.len());

        for (expected_node, actual_node) in expected.nodes.iter().zip(actual.nodes.iter()) {
            assert_nodes_equal(expected_node, actual_node);
        }
    }

    pub fn assert_nodes_equal(expected: &Node, actual: &Node) {
        assert_eq!(expected.id, actual.id);
        assert_eq!(expected.title, actual.title);
        assert_eq!(expected.description, actual.description);
        assert_eq!(expected.path_id, actual.path_id);
        assert_eq!(expected.exercises.len(), actual.exercises.len());

        for (expected_ex, actual_ex) in expected.exercises.iter().zip(actual.exercises.iter()) {
            assert_exercises_equal(expected_ex, actual_ex);
        }
    }

    pub fn assert_exercises_equal(expected: &Exercise, actual: &Exercise) {
        assert_eq!(expected.ex_type, actual.ex_type);
        assert_eq!(expected.content, actual.content);
        assert_eq!(expected.node_id, actual.node_id);
        // Note: IDs might be different after save/load, so we don't compare them
    }

    pub fn assert_metadata_equal(expected: &Metadata, actual: &Metadata) {
        assert_eq!(expected.path_id, actual.path_id);
        assert_eq!(expected.version, actual.version);
        // Note: Timestamps might have slight differences, so we check they're close
        let time_diff = (expected.created_at.timestamp() - actual.created_at.timestamp()).abs();
        assert!(time_diff < 60, "Created timestamps too different");
    }
}

/// Async test setup macro
#[macro_export]
macro_rules! async_test {
    ($test_name:ident, $test_body:expr) => {
        #[tokio::test]
        async fn $test_name() -> Result<(), Box<dyn std::error::Error>> {
            let test_db = common::TestDb::new().await?;

            let result = { $test_body(&test_db).await };

            test_db.cleanup().await?;
            result
        }
    };
}

/// Setup logging for tests
pub fn setup_test_logging() {
    let _ = env_logger::builder()
        .filter_level(log::LevelFilter::Debug)
        .is_test(true)
        .try_init();
}