use std::fmt::{Debug};
use std::time::Duration;
use rglw::log::LogLevel;
use serde::{Deserialize, Serialize};
use sqlx::any::AnyPoolOptions;
use sqlx::{Any, Error, Pool};

#[derive(Debug, Clone, PartialEq, Deserialize, Serialize)]
pub struct DatabaseConfiguration {
    pub uri: Option<String>,
    pub max_connections: Option<u32>,
    pub min_connections: Option<u32>,
    pub acquire_timeout: Option<i64>,
    pub idle_timeout: Option<i64>,
    pub max_lifetime: Option<i64>,
    pub logging: Option<bool>,
    pub log_level: Option<LogLevel>,
    pub output_schemas: Option<bool>,
    pub schemas_location: Option<String>,
}

pub fn load_configuration(connection_name: Option<String>) -> DatabaseConfiguration {
    let mut options = rglw::configuration::ConfigurationLoadOptions::new()
        .file_name("database".to_string())
        .prefix("RGLW_ORM".to_string());
    if connection_name.is_some() {
        options = options.table_name(format!("database.{}", connection_name.unwrap()));
    }
    rglw::configuration::load_configuration::<DatabaseConfiguration>(options)
}

pub async fn get_pool(connection_name: String) -> Result<Pool<Any>, Error> {
    let database_configuration = load_configuration(Some(connection_name));
    sqlx::any::install_default_drivers();
    let mut max_connections: u32 = 100;
    let mut min_connections: u32 = 5;
    let mut acquire_timeout = Duration::from_secs(8);
    let mut idle_timeout = Duration::from_secs(8);
    let mut max_lifetime = Duration::from_secs(8);
    let mut connection_options = AnyPoolOptions::new();
    if database_configuration.max_connections.is_some() {
        max_connections = database_configuration.max_connections.unwrap();
    }
    if database_configuration.min_connections.is_some() {
        min_connections = database_configuration.min_connections.unwrap();
    }
    if database_configuration.acquire_timeout.is_some() {
        acquire_timeout = Duration::from_secs(database_configuration.acquire_timeout.unwrap() as u64);
    }
    if database_configuration.idle_timeout.is_some() {
        idle_timeout = Duration::from_secs(database_configuration.idle_timeout.unwrap() as u64);
    }
    if database_configuration.max_lifetime.is_some() {
        max_lifetime = Duration::from_secs(database_configuration.max_lifetime.unwrap() as u64);
    }
    connection_options = connection_options
        .max_connections(max_connections)
        .min_connections(min_connections)
        .acquire_timeout(acquire_timeout)
        .idle_timeout(idle_timeout)
        .max_lifetime(max_lifetime).clone();
    if database_configuration.uri.is_none() {
        panic!("Database configuration needs the uri parameter");
    }
    connection_options.connect(database_configuration.uri.unwrap().as_str()).await
}

#[rglw::codegen::test(name="Test Connection")]
mod connection_tests {
    use std::{env, fs};
    use std::path::Path;
    use crate::{get_pool};
    use crate::query::{Query, WhereStatement};
    use crate::query::WhereType::And;

    #[derive(Debug, Clone, sqlx::FromRow)]
    struct User {
        pub id: i64,
        pub username: String,
        pub email: String,
    }
    
    #[before_all]
    pub async fn before_all() {
        env::set_var("RGLW_ORM_ENVIRONMENT", "../database.toml");
        let pool = get_pool("connection".to_string()).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let result = sqlx::query(
            "CREATE TABLE IF NOT EXISTS User(\
                    id INTEGER PRIMARY KEY AUTOINCREMENT,\
                    username TEXT NOT NULL,
                    email TEXT NOT NULL
                )").execute(&pool).await;
        assert!(result.is_ok());
        let result = sqlx::query(
            "\
                INSERT INTO User(username,email) VALUES (
                    'Jonathan',
                    'email@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 1',
                    'user1@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 2',
                    'user2@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 3',
                    'user3@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 4',
                    'user4@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 5',
                    'user5@rustyglw.org'
                );
            ").execute(&pool).await;
        assert!(result.is_ok());
    }

    #[after_all]
    pub async fn after_all() {
        fs::remove_file(Path::new("./connection.sqlite")).unwrap();
    }
    
    pub async fn test_connection() {
        let pool = get_pool("connection".to_string()).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let query = Query::select(vec!["id".to_string(), "username".to_string(), "email".to_string()], "User".to_string())
            ._where(WhereStatement{
                where_type: And,
                statement: "User.username = $1".to_string()
            });
        let user = sqlx::query_as::<_, User>(
            query.build().as_str())
            .bind("Jonathan"
        ).fetch_one(&pool).await;
        assert!(user.is_ok());
        let user: User = user.unwrap();
        assert_eq!(user.id, 1);
        assert_eq!(user.username, "Jonathan");
        assert_eq!(user.email, "email@rustyglw.org");
    }
}