use std::env;
use std::fmt::{Debug};
use std::path::Path;
use std::time::Duration;
use dotenv::{dotenv, var};
use figment::Figment;
use figment::providers::{Env, Format, Json, Toml, Yaml};
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: 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>,
}

static PREFIX: &str = "GLWAPI";
static CONFIGURATION_NAME: &str = "database";
static CONFIGURATION_NAME_ENVIRONMENT_VAR: &str = "GLWAPI_CONFIGURATION_LOCATION";

fn verify_configuration_file_exists() {
    let configuration_file_location = env::var(CONFIGURATION_NAME_ENVIRONMENT_VAR);
    if configuration_file_location.is_ok() && !Path::new(configuration_file_location.clone().unwrap().as_str()).exists() {
        panic!("Wrong value provided on the GLWAPI_CONFIGURATION_LOCATION environment variable, you need the full path to the database configuration file");
    }
    if configuration_file_location.is_err() {
        let profile_env = var(format!("{}_ENVIRONMENT", PREFIX));
        if !Path::new(format!("{}.toml", CONFIGURATION_NAME).as_str()).exists() &&
            !Path::new(format!("{}.yml", CONFIGURATION_NAME).as_str()).exists() &&
            !Path::new(format!("{}.yaml", CONFIGURATION_NAME).as_str()).exists() &&
            !Path::new(format!("{}.json", CONFIGURATION_NAME).as_str()).exists() && profile_env.is_err() {
            panic!(
                "{}.toml,{}.yml,{}.yaml,{}.json configuration file or environment variables prefixed with {}_ found, please refer to docs in order to know how to configure it.",
                CONFIGURATION_NAME,
                CONFIGURATION_NAME,
                CONFIGURATION_NAME,
                CONFIGURATION_NAME,
                PREFIX,
            );
        }
    }
}

pub fn load_configuration(connection_name: String) -> DatabaseConfiguration {
    dotenv().ok();
    let profile_env = var(format!("{}_ENVIRONMENT", PREFIX));
    verify_configuration_file_exists();
    let configuration_file_location = env::var(CONFIGURATION_NAME_ENVIRONMENT_VAR);
    let mut figment: Figment;
    if configuration_file_location.is_err() {
        figment = Figment::new()
            .merge(Toml::file(format!("{}.toml", CONFIGURATION_NAME).as_str()).nested())
            .merge(Yaml::file(format!("{}.yml", CONFIGURATION_NAME).as_str()).nested())
            .merge(Yaml::file(format!("{}.yaml", CONFIGURATION_NAME).as_str()).nested())
            .merge(Env::prefixed(format!("{}_", PREFIX).as_str()))
            .merge(Json::file(format!("{}.json", CONFIGURATION_NAME).as_str()).nested());
    } else {
        let configuration_file_location = configuration_file_location.unwrap();
        figment = Figment::new();
        let extension = &configuration_file_location[configuration_file_location.rfind('.').unwrap()..configuration_file_location.len()];
        figment = match extension { 
            ".toml" => {
                figment.merge(Toml::file(configuration_file_location.as_str()).nested())
            },
            ".yml" | ".yaml" => {
                figment.merge(Yaml::file(configuration_file_location.as_str()).nested())
            },
            ".json" => {
                figment.merge(Json::file(configuration_file_location.as_str()).nested())
            }
            _=> {
                panic!("Unsupported extension, supported extensions are .toml, .yml, .yaml, .json");
            }
        };
    }
    let configuration = match profile_env {
        Ok(profile) => figment.clone().select(profile).extract_inner::<DatabaseConfiguration>(format!("database.{}", connection_name).as_str()),
        Err(_error) => figment.clone().extract_inner::<DatabaseConfiguration>(format!("database.{}", connection_name).as_str())
    };
    if configuration.is_err() {
        let error = configuration.err().unwrap();
        panic!("Fail loading database configuration: {:?}", error.to_string());
    }
    configuration.unwrap()
}

pub fn get_connection(connection: String) -> DatabaseConfiguration {
    load_configuration(connection)
}

pub async fn get_pool(database_configuration: DatabaseConfiguration) -> Result<Pool<Any>, Error> {
    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();
    connection_options.connect(database_configuration.uri.as_str()).await
}

pub fn get_test_db_configuration(uri: String) -> DatabaseConfiguration {
    DatabaseConfiguration {
        uri,
        logging: None,
        log_level: None,
        max_connections: None,
        min_connections: None,
        max_lifetime: None,
        idle_timeout: None,
        acquire_timeout: None
    }
}

#[rglw::codegen::test(name="Test Connection")]
mod connection_tests {
    use std::fs;
    use std::path::Path;
    use crate::{get_pool, get_test_db_configuration};
    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() {
        let pool = get_pool(get_test_db_configuration("sqlite://connection.sqlite?mode=rwc".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(get_test_db_configuration("sqlite://connection.sqlite?mode=rwc".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");
    }
}