use mysql_async::{self, from_row_opt, params, prelude::*, Pool};
use sha1::{Digest, Sha1};

use crate::handlers::{AccountInfo, Sha1Digest};

const CHECK_ACCOUNT_EXISTS_QUERY: &str =
    r"SELECT 1 FROM users WHERE users.name = :username LIMIT 1";

const GET_ACCOUNT_QUERY: &str = r"SELECT CASE WHEN users.status = 1 THEN users.pass ELSE '' END,
     (SELECT COUNT(users_roles.rid) FROM users_roles WHERE users.uid = users_roles.uid AND users_roles.rid = 3),
     (SELECT COUNT(users_roles.rid) FROM users_roles WHERE users.uid = users_roles.uid AND users_roles.rid = 13)
     FROM users WHERE users.name = :username";

const STORE_STATS_QUERY: &str = r"INSERT INTO gameserver_stats
      (players, rooms, last_update)
      VALUES
      (:players, :rooms, UNIX_TIMESTAMP())";

const GET_REPLAY_NAME_QUERY: &str = r"SELECT filename FROM achievements WHERE id = :id";

pub struct ServerStatistics {
    rooms: u32,
    players: u32,
}

pub struct Achievements {}

pub struct Database {
    pool: Pool,
}

impl Database {
    pub fn new(url: &str) -> Self {
        Self {
            pool: Pool::new(url),
        }
    }

    pub async fn get_is_registered(&mut self, nick: &str) -> mysql_async::Result<bool> {
        let mut connection = self.pool.get_conn().await?;
        let result = CHECK_ACCOUNT_EXISTS_QUERY
            .with(params! { "username" => nick })
            .first(&mut connection)
            .await?;
        Ok(!result.is_empty())
    }

    pub async fn get_account(
        &mut self,
        nick: &str,
        protocol: u16,
        password_hash: &str,
        client_salt: &str,
        server_salt: &str,
    ) -> mysql_async::Result<Option<AccountInfo>> {
        let mut connection = self.pool.get_conn().await?;
        if let Some((mut password, is_admin, is_contributor)) = GET_ACCOUNT_QUERY
            .with(params! { "username" => nick })
            .first::<(String, i32, i32), _>(&mut connection)
            .await?
        {
            let client_hash = get_hash(protocol, &password, &client_salt, &server_salt);
            let server_hash = get_hash(protocol, &password, &server_salt, &client_salt);
            password.replace_range(.., "🦔🦔🦔🦔🦔🦔🦔🦔");

            if client_hash == password_hash {
                Ok(Some(AccountInfo {
                    is_registered: true,
                    is_admin: is_admin == 1,
                    is_contributor: is_contributor == 1,
                    server_hash,
                }))
            } else {
                Ok(None)
            }
        } else {
            Ok(None)
        }
    }

    pub async fn get_checker_account(
        &mut self,
        nick: &str,
        checker_password: &str,
    ) -> mysql_async::Result<bool> {
        let mut connection = self.pool.get_conn().await?;
        if let Some((password, _, _)) = GET_ACCOUNT_QUERY
            .with(params! { "username" => nick })
            .first::<(String, i32, i32), _>(&mut connection)
            .await?
        {
            Ok(checker_password == password)
        } else {
            Ok(false)
        }
    }

    pub async fn store_stats(&mut self, stats: &ServerStatistics) -> mysql_async::Result<()> {
        let mut connection = self.pool.get_conn().await?;
        STORE_STATS_QUERY
            .with(params! {
                "players" => stats.players,
                "rooms" => stats.rooms,
            })
            .ignore(&mut connection)
            .await
    }

    pub async fn store_achievements(
        &mut self,
        achievements: &Achievements,
    ) -> mysql_async::Result<()> {
        Ok(())
    }

    pub async fn get_replay_name(&mut self, replay_id: u32) -> mysql_async::Result<Option<String>> {
        let mut connection = self.pool.get_conn().await?;
        GET_REPLAY_NAME_QUERY
            .with(params! { "id" => replay_id })
            .first::<String, _>(&mut connection)
            .await
    }
}

fn get_hash(protocol_number: u16, web_password: &str, salt1: &str, salt2: &str) -> Sha1Digest {
    let data = format!(
        "{}{}{}{}{}",
        salt1, salt2, web_password, protocol_number, "!hedgewars"
    );

    let mut sha1 = Sha1::new();
    sha1.update(&data);
    Sha1Digest::new(sha1.finalize().try_into().unwrap())
}