use std::{
    fs::{read, read_dir},
    str::FromStr,
    vec::Vec,
};

use axum::{http::StatusCode, response::IntoResponse};
use brevo_mail::brevo_options::BrevoApiKey;
use include_dir::Dir;
use r2d2::{Pool, PooledConnection};
use r2d2_sqlite::SqliteConnectionManager;
use rusqlite::{OptionalExtension, Transaction, named_params};
use tracing::{debug, info, instrument, trace};

use crate::{
    database::{db_types::MigrationId, macros::NewId},
    helpers::now,
    types::cookie::CookieOptions,
};

static DB_NAME: &str = "cms.db";

static MIGRATION_DIR: Dir = include_dir::include_dir!("$CARGO_MANIFEST_DIR/migrations");

#[derive(Debug, thiserror::Error)]
pub enum DbError {
    #[error(transparent)]
    Pool(#[from] r2d2::Error),
    #[error(transparent)]
    SqLite(#[from] r2d2_sqlite::rusqlite::Error),
    #[error("{0}")]
    Generic(String),
    #[error("User is already registered")]
    UserAlreadyRegistered,
    #[error("{0}")]
    SettingNotFound(String),
    #[error("Was not able to deserialise setting. Got error {0}")]
    SettingNotDeserializable(#[from] serde_json::Error),
    #[error("Brevo Api Key not valid")]
    BrevoApiKeyNotValid,
    #[error("User is not verified yet. Please verify your user")]
    UserNotVerifiedYet,
    #[error("User was not found")]
    UserNotFound,
    #[error("Was not able to create file or directory")]
    CreateDirFile(#[from] std::io::Error),
}

impl IntoResponse for DbError {
    fn into_response(self) -> axum::response::Response {
        (StatusCode::INTERNAL_SERVER_ERROR, self.to_string()).into_response()
    }
}

#[derive(Debug, Clone)]
pub struct Db {
    db: Pool<SqliteConnectionManager>,
}

impl Db {
    pub fn new() -> Result<Self, DbError> {
        info!("Create DB with name {}", DB_NAME);
        let manager = SqliteConnectionManager::file(DB_NAME);
        let pool = r2d2::Pool::new(manager)?;

        Ok(Self { db: pool })
    }
}

impl Db {
    #[instrument(skip(self))]
    pub fn init(&self) -> Result<(), DbError> {
        info!("Initialize DB");
        let mut conn = self.db.get()?;
        let tx = conn.transaction()?;

        // let SplitSqlFilesResult(always, migrations) = split_sql_files();
        let migrations = MIGRATION_DIR.files();

        debug!("Execute migrations");
        for migration in migrations {
            // SAFETY: included in binary. Safe to unwrap
            let filename = migration.path().file_name().unwrap().to_str().unwrap();
            let content = migration.contents_utf8();

            if Db::has_migration_already_happened(&tx, filename)? {
                debug!("Migration has already happened. Continue");
            } else if let Some(content) = content {
                debug!("Execute Migration {}", filename);
                tx.execute_batch(content)?;
                Db::insert_migration(&tx, filename)?;
            } else {
                debug!(
                    "Migration {} has not happened, but the migration file had no content. Skipped this migration and all after that.",
                    &filename
                );
                break;
            }
        }

        debug!("Execute always migrations");
        let files = read_dir("./always")?;
        for file in files {
            let filepath = file?.path();
            let filename = filepath.file_name().unwrap();
            let content = read(&filepath)?;
            let content = String::from_utf8(content).map_err(|_| {
                DbError::Generic(
                    "Not able to convert to a string while reading from always dir".into(),
                )
            })?;

            debug!("Execute {:?}", filename);
            tx.execute_batch(&content)?;
        }

        debug!("Commit migrations");
        tx.commit()?;

        Ok(())
    }

    #[instrument(skip(tx))]
    fn insert_migration(tx: &Transaction, filename: &str) -> Result<(), DbError> {
        let mut stmt = tx.prepare_cached(
            r#"INSERT INTO migration 
                (migration_id, migration_name, created)
                VALUES 
                (:migration_id, :migration_name, :created)"#,
        )?;

        let migration_id = MigrationId::new_id();
        let params = named_params! {
            ":migration_id": migration_id,
            ":migration_name": filename,
            ":created": now()
        };

        debug!("Insert Migration {} into migration table", &filename);
        stmt.execute(params)?;

        Ok(())
    }

    #[instrument(skip(tx))]
    fn has_migration_already_happened(tx: &Transaction, filename: &str) -> Result<bool, DbError> {
        debug!(
            "Check if migration with name {} has already happened",
            &filename
        );

        if !(tx.table_exists(None, "migration")?) {
            debug!(
                "There is no migration table. So not even the first migration has happened. Continue with migration"
            );
            return Ok(false);
        }

        let query = r#"
            SELECT
                migration_id
            FROM
                migration
            WHERE
                migration_name = :migration_name"#;

        let params = named_params! {":migration_name": filename};

        let migration_id: Option<MigrationId> =
            tx.query_one(query, params, |row| row.get(0)).optional()?;

        match migration_id {
            Some(migration_id) => {
                debug!("Found migration with migration_id {:?}", migration_id);
                Ok(true)
            }
            None => {
                debug!("Did not find the migration");
                Ok(false)
            }
        }
    }

    pub(crate) fn get_setting(&self, setting_name: &str) -> Result<String, DbError> {
        let conn = self.connection()?;

        let query = r#"
            SELECT
                setting
            FROM
                setting
            WHERE
                setting_name = :setting_name"#;

        let params = named_params! {
            ":setting_name": setting_name
        };

        let setting: Option<String> = conn.query_one(query, params, |row| row.get(0))?;

        match setting {
            Some(setting) => Ok(setting),
            None => Err(DbError::SettingNotFound(setting_name.to_owned())),
        }
    }

    pub(crate) fn get_brevo_key(&self) -> Result<BrevoApiKey, DbError> {
        let key = self.get_setting("brevo-key")?;
        let key = BrevoApiKey::from_str(&key).map_err(|_| DbError::BrevoApiKeyNotValid)?;

        Ok(key)
    }

    pub fn get_cookie_options(&self) -> Result<CookieOptions, DbError> {
        let json = self.get_setting("cookie-options")?;
        trace!("Json String from DB for cookie-options: {:?}", json);
        let cookie_options = serde_json::from_str(&json)?;
        trace!("Cookie Options. {:?}", cookie_options);

        Ok(cookie_options)
    }

    pub(crate) fn connection(&self) -> Result<PooledConnection<SqliteConnectionManager>, DbError> {
        let conn = self.db.get()?;

        Ok(conn)
    }
}
