use std::error::Error;
use thiserror::Error;
use diesel::{Connection, ExpressionMethods, Insertable, OptionalExtension, QueryDsl, RunQueryDsl, SqliteConnection};
use diesel::associations::HasTable;
use diesel::r2d2::{ConnectionManager, Pool};
use diesel::sqlite::Sqlite;
use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};
use crate::sql::models::{NewUser, User};
use crate::sql::schema::users::password;
use crate::users::UserDb;

const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations");

pub struct SqliteUserDB {
    connection: Pool<ConnectionManager<SqliteConnection>>,
}

#[derive(Debug, Error)]
pub enum SqliteError {
    #[error("failed to open new connection")]
    PoolError(#[from] #[source] diesel::r2d2::PoolError),
    #[error("failed to execute SQL commands")]
    SqlError(#[from] #[source] diesel::r2d2::Error),
    #[error("failed to apply migration")]
    MigrationError(#[source] Box<dyn Error + Send + Sync>),
}

impl SqliteUserDB {
    fn new(connection: Pool<ConnectionManager<SqliteConnection>>) -> Self {
        Self { connection }
    }

    fn run_migrations(db: &mut impl MigrationHarness<Sqlite>) -> Result<(), SqliteError> {
        let applied_migrations = db
            .run_pending_migrations(MIGRATIONS)
            .map_err(SqliteError::MigrationError)?;
        for version in applied_migrations {
            tracing::info!(?version, "applied migration");
        }
        Ok(())
    }

    pub fn setup(url: impl Into<String>) -> Result<Self, SqliteError> {
        let mut manager = ConnectionManager::<SqliteConnection>::new(url);
        let pool = Pool::builder()
            .test_on_check_out(true)
            .build(manager)?;

        let mut conn = pool.get()?;
        Self::run_migrations(&mut conn)?;

        Ok(Self::new(pool))
    }

    pub fn lookup(&self, uid: &str) -> Result<Option<User>, SqliteError> {
        use super::schema::users::dsl::*;

        let mut conn = self.connection.get()?;
        users
            .filter(name.eq(uid))
            .first(&mut conn)
            .optional()
            .map_err(|e| SqliteError::SqlError(diesel::r2d2::Error::QueryError(e)))
    }

    pub fn insert(&self, new_user: NewUser) -> Result<(), SqliteError> {
        use super::schema::users::dsl::*;

        let mut conn = self.connection.get()?;
        new_user
            .insert_into(users)
            .execute(&mut conn)
            .map(|_| ())
            .map_err(|e| SqliteError::SqlError(diesel::r2d2::Error::QueryError(e)))
    }
}