use std::sync::Mutex;

use camino::Utf8PathBuf;
use rusqlite::{params, Connection, OptionalExtension};
use time::OffsetDateTime;

use crate::gatherer::Meminfo;

pub struct Database {
	db_path: Utf8PathBuf,
	conn: Mutex<Connection>,
}

impl Database {
	pub fn new(db_path: Utf8PathBuf) -> Self {
		Self {
			conn: Mutex::new(Connection::open(&db_path).unwrap()),
			db_path,
		}
	}

	pub fn create_tables(&self) {
		let conn = self.conn.lock().unwrap();
		conn.execute(CREATE_TABLE_HOSTMEM, params![]).unwrap();
		conn.execute(CREATE_TABLE_HOSTNET, params![]).unwrap();
	}

	pub fn insert_host_meminfo(&self, meminfo: Meminfo) {
		let conn = self.conn.lock().unwrap();

		conn.execute(
			"INSERT INTO stats_hostmem(total_kb, available_kb) VALUES (?1, ?2)",
			params![meminfo.total, meminfo.avaialable],
		)
		.unwrap();
	}

	pub fn get_last_host_meminfo(&self) -> DbMeminfo {
		let conn = self.conn.lock().unwrap();

		conn.query_row(
			"SELECT * FROM stats_hostmem ORDER BY stamp DESC LIMIT 1",
			[],
			|row| {
				let (stamp, total_kb, available_kb) = row.try_into().unwrap();

				Ok(DbMeminfo {
					stamp,
					total_kb,
					available_kb,
				})
			},
		)
		.optional()
		.unwrap()
		.unwrap()
	}

	pub fn get_last_n_host_meminfo(&self, count: usize) -> Vec<DbMeminfo> {
		let conn = self.conn.lock().unwrap();
		let mut stmt = conn
			.prepare("SELECT * FROM stats_hostmem ORDER BY stamp ASC LIMIT ?1")
			.unwrap();

		stmt.query_map(params![count], |row| {
			Ok(DbMeminfo {
				stamp: row.get(0)?,
				total_kb: row.get(1)?,
				available_kb: row.get(2)?,
			})
		})
		.unwrap()
		.map(|r| r.unwrap())
		.collect()
	}

	pub fn get_date_bound_n_host_meminfo(
		&self,
		since: OffsetDateTime,
		until: OffsetDateTime,
		count: usize,
	) -> Vec<DbMeminfo> {
		let conn = self.conn.lock().unwrap();
		let mut stmt = conn
			.prepare("SELECT * FROM stats_hostmem WHERE stamp > ?1 AND stamp < ?2 ORDER BY stamp ASC LIMIT ?3")
			.unwrap();

		stmt.query_map(params![since, until, count], |row| {
			Ok(DbMeminfo {
				stamp: row.get(0)?,
				total_kb: row.get(1)?,
				available_kb: row.get(2)?,
			})
		})
		.unwrap()
		.map(|r| r.unwrap())
		.collect()
	}

	pub fn insert_hostnet(&self, span_sec: usize, rx_delta: usize, tx_delta: usize) {
		let conn = self.conn.lock().unwrap();

		conn.execute(
			"INSERT INTO stats_hostnet(span_sec, rx_delta, tx_delta) VALUES (?1, ?2, ?3)",
			params![span_sec, rx_delta, tx_delta],
		)
		.unwrap();
	}

	pub fn get_last_n_hostnet(&self, count: usize) -> Vec<DbNetinfo> {
		let conn = self.conn.lock().unwrap();
		let mut stmt = conn
			.prepare("SELECT * FROM stats_hostnet ORDER BY stamp ASC LIMIT ?1")
			.unwrap();

		stmt.query_map(params![count], |row| {
			Ok(DbNetinfo {
				stamp: row.get(0)?,
				span_sec: row.get(1)?,
				rx_delta: row.get(2)?,
				tx_delta: row.get(3)?,
			})
		})
		.unwrap()
		.map(|r| r.unwrap())
		.collect()
	}
}

pub const CREATE_TABLE_HOSTMEM: &'static str = "\
    CREATE TABLE IF NOT EXISTS stats_hostmem (
        stamp TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        total_kb INTEGER NOT NULL,
        available_kb INTEGER NOT NULL
    );";

pub const CREATE_TABLE_HOSTNET: &'static str = "\
    CREATE TABLE IF NOT EXISTS stats_hostnet(
        stamp TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        span_sec INTEGER NOT NULL,
        rx_delta INTEGER NOT NULL,
        tx_delta INTEGER NOT NULL
    );";

pub struct DbMeminfo {
	pub stamp: OffsetDateTime,
	pub total_kb: usize,
	pub available_kb: usize,
}

impl DbMeminfo {
	pub fn usage(&self) -> usize {
		self.total_kb - self.available_kb
	}
}

#[derive(Copy, Clone, Debug)]
pub struct DbNetinfo {
	pub stamp: OffsetDateTime,
	pub span_sec: usize,
	pub rx_delta: usize,
	pub tx_delta: usize,
}

impl DbNetinfo {
	pub fn rx_bytes_per_sec(&self) -> f32 {
		self.rx_delta as f32 / self.span_sec as f32
	}

	pub fn tx_bytes_per_sec(&self) -> f32 {
		self.tx_delta as f32 / self.span_sec as f32
	}
}