use std::{path::Path, sync::Mutex};

use rusqlite::{Connection, OptionalExtension, params};
use twilight_http::request::guild;

#[derive(Debug)]
pub struct Database {
	conn: Mutex<Connection>,
}

impl Database {
	pub fn new<P: AsRef<Path>>(db_path: P) -> Self {
		Self {
			conn: Mutex::new(Connection::open(db_path).unwrap()),
		}
	}

	pub fn create_tables(&self) {
		let conn = self.conn.lock().unwrap();
		conn.execute(CREATE_TABLE_LEADERBOARDS, ()).unwrap();
	}

	pub fn create_leaderboard(&self, guild_id: u64) -> Result<(), Error> {
		let mut conn = self.conn.lock().unwrap();
		let trans = conn.transaction().unwrap();

		trans
			.execute(
				"INSERT INTO leaderboards(guild_id) VALUES(?1)",
				params![guild_id],
			)
			.unwrap();

		let leaderboard_id = trans.last_insert_rowid();
		let leaderboard_create_sql =
			CREATE_TABLE_LEADERBOARD.replace("LBID", &leaderboard_id.to_string());

		trans.execute(&leaderboard_create_sql, params![]).unwrap();
		trans.commit().unwrap();

		Ok(())
	}

	pub fn leaderboard_id(&self, guild_id: u64) -> Result<u64, Error> {
		let conn = self.conn.lock().unwrap();

		let leaderboard_id = conn
			.query_row(
				"SELECT * FROM leaderboards WHERE guild_id=?1",
				params![guild_id],
				|row| row.get(0),
			)
			.map_err(|_| Error::TableNotExist);

		leaderboard_id
	}

	pub fn get_leaderboard(&self, guild_id: u64) -> Result<Vec<BoardRow>, Error> {
		// Don't deadlock!
		let leaderboard_id = self.leaderboard_id(guild_id)?;
		let conn = self.conn.lock().unwrap();

		let mut query = conn
			.prepare(&format!("SELECT * FROM leaderboard_{leaderboard_id}"))
			.unwrap();
		let vec = query
			.query_map((), |row| {
				Ok(BoardRow {
					user_id: row.get(0)?,
					user_handle: row.get(1)?,
					user_nickname: row.get(2)?,
					points: row.get(3)?,
				})
			})
			.optional()
			.unwrap()
			.map(|iter| iter.map(|e| e.unwrap()).collect::<Vec<BoardRow>>())
			.unwrap();

		Ok(vec)
	}

	pub fn add_user_to_leaderboard(&self, guild: u64, row: BoardRow) -> Result<(), Error> {
		// Don't deadlock!
		let leaderboard_id = self.leaderboard_id(guild)?;
		let table = format!("leaderboard_{leaderboard_id}");
		let conn = self.conn.lock().unwrap();

		let BoardRow {
			user_id,
			user_handle,
			user_nickname,
			points,
		} = row;

		let sql = format!(
			"INSERT INTO {table}(user_id, user_handle, user_nickname, points) VALUES(?1, ?2, ?3, ?4)"
		);
		conn.execute(&sql, params![user_id, user_handle, user_nickname, points])
			.unwrap();

		Ok(())
	}

	pub fn give_user_points(&self, guild_id: u64, user: u64, points: i64) -> Result<(), Error> {
		// Don't deadlock!
		let leaderboard_id = self.leaderboard_id(guild_id)?;
		let table = format!("leaderboard_{leaderboard_id}");
		let conn = self.conn.lock().unwrap();

		let sql = format!("SELECT * FROM {table} WHERE user_id=?1");
		let user_handle: String = conn
			.query_row(&sql, params![user], |row| row.get(1))
			.map_err(|_| Error::UserNotExist)?;

		let sql = format!("UPDATE {table} SET points = points + ?1 WHERE user_id=?2");
		conn.execute(&sql, params![points, user]).unwrap();

		Ok(())
	}
}

const CREATE_TABLE_LEADERBOARDS: &'static str = "\
	CREATE TABLE IF NOT EXISTS leaderboards(
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		guild_id INTEGER
	);";

const CREATE_TABLE_LEADERBOARD: &'static str = "\
	CREATE TABLE IF NOT EXISTS leaderboard_LBID(
		user_id INTEGER PRIMARY KEY NOT NULL,
		user_handle TEXT NOT NULL,
		user_nickname TEXT,
		points INTEGER NOT NULL DEFAULT 0
	);";

#[derive(Clone, Debug)]
pub struct BoardRow {
	pub user_id: u64,
	pub user_handle: String,
	pub user_nickname: Option<String>,
	pub points: i64,
}

#[derive(Debug)]
pub enum Error {
	TableNotExist,
	UserNotExist,
}