use rusqlite::{OptionalExtension, named_params};
use tracing::{debug, error, trace};

use crate::{
    database::{
        db::{Db, DbError},
        macros::NewId,
    },
    types::{
        password::PasswordHash,
        user::{FromRow, Mail, SessionId, User, UserId, UserState, VerificationId},
    },
};

const PENDING_STATUS: &str = "019afcea-4650-7598-907e-de32fb2a2e97";
const VERIFIED_STATUS: &str = "019afcea-80fe-7ce9-b2dd-17c425d2fdbf";

const ADMIN_ROLE: &str = "019aeef4-bb5b-7f2e-abd5-89a8eb4be2d6";
const SUPERUSER_ROLE: &str = "019b3615-3620-789b-84fa-a3611d1da9d3";
const USER_ROLE: &str = "019aeef4-c7b6-7fe1-afb9-edec8dc5e9fe";

#[derive(Clone, Debug)]
pub struct AuthRepository {
    pub(crate) db: Db,
}

impl AuthRepository {
    pub(crate) fn register_user(
        &self,
        email: &Mail,
        hash: &PasswordHash,
    ) -> Result<VerificationId, DbError> {
        debug!("Check if user already exists");
        let user = self.get_user_by_email(email)?;

        if user.is_some() {
            debug!("There was already a user with this email. {:?}", user);
            return Err(DbError::UserAlreadyRegistered);
        }

        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        let user_id = UserId::new_id();
        let verification_id = VerificationId::new_id();

        let sql = r#"
            INSERT INTO user 
                (user_id, email, password_hash, user_state_id, role_id)
            VALUES
                (:user_id, :email, :password_hash, :user_state_id, :role_id)"#;

        let params = named_params! {
            ":user_id": user_id,
            ":email": email,
            ":password_hash": hash,
            ":user_state_id": PENDING_STATUS,
            ":role_id": USER_ROLE
        };

        debug!("Insert new user");
        tx.execute(sql, params)?;

        let sql = r#"
            INSERT INTO verification
                (verification_id, user_id)
            VALUES
                (:verification_id, :user_id)"#;

        let params = named_params! {
            ":verification_id": verification_id,
            ":user_id": user_id
        };

        debug!("Insert verification id {:?}", verification_id);
        tx.execute(sql, params)?;

        tx.commit()?;

        Ok(verification_id)
    }

    // pub(crate) fn login_user(&self) -> Result<User, DbError> {}

    pub(crate) fn get_hash_by_email(&self, email: &Mail) -> Result<PasswordHash, DbError> {
        let conn = self.db.connection()?;

        let query = r#"
            SELECT
                password_hash
            FROM
                user
            WHERE
                email = :email"#;

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

        let hash = conn.query_one(query, params, PasswordHash::try_from_row)?;

        Ok(hash)
    }

    pub(crate) fn get_user_by_email(&self, email: &Mail) -> Result<Option<User>, DbError> {
        let conn = self.db.connection()?;

        let user_query = r#"
            SELECT DISTINCT 
                user_id, 
                email, 
                user_state,
                role
            FROM 
                user_view 
            WHERE
                email = :email"#;

        let params = named_params! {
            ":email": email.to_string()
        };

        let user: Option<User> = conn
            .query_one(user_query, params, User::try_from_row)
            .optional()?;

        Ok(user)
    }

    pub(crate) fn verify_user(&self, verification_id: VerificationId) -> Result<(), DbError> {
        debug!("Verify User against database");

        let mut conn = self.db.connection()?;
        let transaction = conn.transaction()?;

        let sql = r#"
            SELECT
                verification.user_id
            FROM
                verification
            WHERE
                verification_id = :verification_id
            "#;

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

        let user_id = transaction.query_one(sql, params, UserId::try_from_row)?;

        debug!("Found user with id {}", user_id);

        let sql = r#"
            UPDATE user
                SET user_state_id = :user_state_id
                WHERE user_id = :user_id"#;

        let params = named_params! {
            ":user_state_id": VERIFIED_STATUS,
            ":user_id": user_id
        };

        transaction.execute(sql, params)?;

        transaction.commit()?;

        Ok(())
    }

    pub(crate) fn get_verified_user_by_email(&self, email: &Mail) -> Result<User, DbError> {
        let user = self.get_user_by_email(email)?;

        match user {
            Some(user) if user.user_state == UserState::Verified => Ok(user),
            Some(_) => Err(DbError::UserNotVerifiedYet),
            None => Err(DbError::UserNotFound),
        }
    }

    pub(crate) fn create_session_for_user(&self, user_id: &UserId) -> Result<SessionId, DbError> {
        let mut conn = self.db.connection()?;
        let tx = conn.transaction()?;

        let session_id = SessionId::new_id();

        let sql = r#"
            INSERT INTO session
                (session_id, user_id)
            VALUES
                (:session_id, :user_id)
            "#;

        let params = named_params! {
            ":session_id": session_id,
            ":user_id": user_id
        };

        tx.execute(sql, params)?;
        tx.commit()?;

        Ok(session_id)
    }

    pub(crate) fn get_user_by_session_id(&self, session_id: &SessionId) -> Result<User, DbError> {
        let conn = self.db.connection()?;

        let sql = r#"
            SELECT DISTINCT 
                user_id, 
                email, 
                user_state,
                role,
                session_id
            FROM 
                user_session_view 
            WHERE
                session_id = :session_id"#;

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

        let user: Option<User> = conn.query_one(sql, params, User::try_from_row).optional()?;

        match user {
            Some(user) => {
                trace!("Found user for session");
                Ok(user)
            }
            None => {
                error!(
                    "Was not able to find a user for session_id {:?}",
                    session_id
                );
                Err(DbError::UserNotFound)
            }
        }
    }
}
