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 lb = LeaderboardTable::new(leaderboard_id as usize);

		let queries = &[
			lb.sql_create_table(),
			lb.sql_create_history_table(),
			lb.sql_create_update_trigger_history_table(),
			lb.sql_create_insert_trigger_history_table(),
		];

		for query in queries {
			if let Err(e) = trans.execute(&query, params![]) {
				panic!("SQL statement:\n{query}\nPanic Message:\n{e}")
			}
		}

		trans.commit().unwrap();

		Ok(())
	}

	pub fn leaderboard_id(&self, guild_id: u64) -> Result<LeaderboardTable, 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(|id| LeaderboardTable { id }),
			)
			.map_err(|_| Error::TableNotExist);

		leaderboard_id
	}

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

		let mut query = conn
			.prepare(&lb.sql("SELECT * FROM leaderboard_LBID"))
			.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 lb = self.leaderboard_id(guild)?;
		let conn = self.conn.lock().unwrap();

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

		let sql = lb.sql(
			"INSERT INTO leaderboard_LBID(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 lb = self.leaderboard_id(guild_id)?;
		let conn = self.conn.lock().unwrap();

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

		let sql = lb.sql("UPDATE leaderboard_LBID 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
	);";

const CREATE_TABLE_LEADERBOARD_HISTORY: &'static str = "\
	CREATE TABLE IF NOT EXISTS leaderboard_history_LBID(
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		timestamp TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
		points INTEGER NOT NULL DEFAULT 0
	);";

const CREATE_TRIGGER_LDBHIST_UPDATE: &'static str = "\
	CREATE TRIGGER ldbdhist_update_LBID BEFORE UPDATE ON leaderboard_LBID
		BEGIN
			INSERT INTO leaderboard_history_LBID(user_id, points) VALUES(old.user_id, new.points - old.points);
		END;";

const CREATE_TRIGGER_LDBHIST_INSERT: &'static str = "\
	CREATE TRIGGER ldbdhist_insert_LBID BEFORE INSERT ON leaderboard_LBID
		BEGIN
			INSERT INTO leaderboard_history_LBID(user_id, points) VALUES(new.user_id, new.points);
		END;";

struct LeaderboardTable {
	id: usize,
}

impl LeaderboardTable {
	pub fn new(id: usize) -> Self {
		Self { id }
	}

	pub fn sql<S: AsRef<str>>(&self, query: S) -> String {
		query.as_ref().replace("LBID", &self.id.to_string())
	}

	pub fn sql_create_table(&self) -> String {
		CREATE_TABLE_LEADERBOARD.replace("LBID", &self.id.to_string())
	}

	pub fn sql_create_history_table(&self) -> String {
		CREATE_TABLE_LEADERBOARD_HISTORY.replace("LBID", &self.id.to_string())
	}

	pub fn sql_create_update_trigger_history_table(&self) -> String {
		CREATE_TRIGGER_LDBHIST_UPDATE.replace("LBID", &self.id.to_string())
	}

	pub fn sql_create_insert_trigger_history_table(&self) -> String {
		CREATE_TRIGGER_LDBHIST_INSERT.replace("LBID", &self.id.to_string())
	}
}

#[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,
}