use log::{debug, error};
use uuid::Uuid;

use crate::{util, Identity};

use super::{Store, StoreError};

pub struct SqliteClient {
    pool: sqlx::Pool<sqlx::Sqlite>,
}

impl SqliteClient {
    pub async fn new(pool: sqlx::Pool<sqlx::Sqlite>) -> Self {
        sqlx::migrate!("store/sqlite/migrations")
            .run(&pool)
            .await
            .expect(
                "Failed to execute migrations. This appears to be a secd issue. File a bug at https://www.github.com/secd-lib"
            );

        sqlx::query("pragma foreign_keys = on")
            .execute(&pool)
            .await
            .expect(
                "Failed to initialize FK pragma. File a bug at https://www.github.com/secd-lib",
            );

        SqliteClient { pool }
    }
}

impl SqliteClient {
    async fn read_identity_raw_id(&self, id: &Uuid) -> Result<i64, StoreError> {
        Ok(sqlx::query_as::<_, (i64,)>(
            "
select identity_id from identity where identity_public_id = ?",
        )
        .bind(id)
        .fetch_one(&self.pool)
        .await
        .map_err(util::log_err)?
        .0)
    }

    async fn read_email_raw_id(&self, address: &str) -> Result<i64, StoreError> {
        Ok(sqlx::query_as::<_, (i64,)>(
            "
select email_id from email where address = ?",
        )
        .bind(address)
        .fetch_one(&self.pool)
        .await
        .map_err(util::log_err)?
        .0)
    }
}

#[async_trait::async_trait]
impl Store for SqliteClient {
    async fn write_email(&self, identity_id: Uuid, email_address: &str) -> Result<(), StoreError> {
        let mut tx = self.pool.begin().await?;

        let identity_id = self.read_identity_raw_id(&identity_id).await?;

        let email_id: (i64,) = sqlx::query_as(
            "
insert into email (address) values (?) returning email_id",
        )
        .bind(email_address)
        .fetch_one(&mut tx)
        .await
        .map_err(util::log_err)?;

        debug!("identity: {}, email: {}", identity_id, email_id.0);

        sqlx::query(
            "
insert into identity_email (identity_id, email_id) values (?,?);",
        )
        .bind(identity_id)
        .bind(email_id.0)
        .execute(&mut tx)
        .await
        .map_err(util::log_err)?;

        tx.commit().await?;

        Ok(())
    }

    async fn write_email_validation_request(
        &self,
        identity_id: Uuid,
        email_address: &str,
    ) -> Result<Uuid, StoreError> {
        let identity_id = self.read_identity_raw_id(&identity_id).await?;
        let email_id = self.read_email_raw_id(email_address).await?;

        let request_id = Uuid::new_v4();
        sqlx::query("
insert into email_validation_request (email_validation_request_public_id, identity_email_id, is_validated)
values (?, (select identity_email_id from identity_email where identity_id = ? and email_id =?), ?)",
        )
            .bind(request_id)
            .bind(identity_id)
            .bind(email_id)
            .bind(false)
            .execute(&self.pool)
            .await
            .map_err(util::log_err)?;

        Ok(request_id)
    }

    async fn write_identity(&self, i: &Identity) -> Result<(), StoreError> {
        sqlx::query(
            "
insert into identity (identity_public_id, data, created_at) values (?, ?, ?)",
        )
        .bind(i.id)
        .bind(i.data.clone())
        .bind(i.created_at)
        .execute(&self.pool)
        .await
        .map_err(|e| {
            error!("{:?}", e);
            e
        })?;

        Ok(())
    }

    async fn read_identity(&self, id: &Uuid) -> Result<Identity, StoreError> {
        Ok(sqlx::query_as::<_, Identity>(
            "
select identity_public_id, data, created_at from identity where identity_public_id = ?",
        )
        .bind(id)
        .fetch_one(&self.pool)
        .await
        .map_err(util::log_err)?)
    }

    async fn find_identity(
        &self,
        id: Option<&Uuid>,
        email: Option<&str>,
    ) -> Result<Option<Identity>, StoreError> {
        Ok(
            match sqlx::query_as::<_, Identity>(
                "
select identity_public_id, data, i.created_at
from identity       i
join identity_email ie using (identity_id)
join email          e  using (email_id)
where ((? is null) or (i.identity_public_id = ?))
and   ((? is null) or (e.address = ?));",
            )
            .bind(id)
            .bind(id)
            .bind(email)
            .bind(email)
            .fetch_one(&self.pool)
            .await
            {
                Ok(i) => Some(i),
                Err(sqlx::Error::RowNotFound) => None,
                Err(e) => return Err(StoreError::SqlxError(e)),
            },
        )
    }
}