mod configuration;
mod data_base_config;
mod info;
mod measurement;
mod meta_info;
mod result;
mod rights;
mod table_names;

pub use configuration::Configuration;
pub use data_base_config::DataBaseConfig;
pub use measurement::Measurement;
pub use meta_info::MetaInfo;
pub use rights::Rights;
pub use table_names::TableNames;

use anyhow::{anyhow, Result};
use mysql::{params, prelude::Queryable, Pool, PooledConn, Row};

use self::{
    configuration::ConfigID,
    info::{Info, InfoID},
    measurement::{MeasurementID, OutMeasurement},
    result::ResultID,
};
use result::Result as DBResult;

pub struct PTSDataBase<'a> {
    pool: Pool,
    table_names: TableNames<'a>,
    _config: DataBaseConfig<'a>,
}

impl<'a> PTSDataBase<'a> {
    pub fn new(config: DataBaseConfig<'a>, table_names: TableNames<'a>) -> Result<Self> {
        let url = format!(
            "mysql://{}:{}@{}:{}/{}",
            config.user, config.password, config.url, config.port, config.schema
        );

        let pool = Pool::new(url.as_str())?;

        pool.get_conn()?
            .exec_drop(&format!("USE {};", config.schema), ())?;

        Ok(Self {
            pool,
            table_names,
            _config: config,
        })
    }

    fn access<F, T>(&self, f: F) -> Result<T>
    where
        F: FnOnce(PooledConn) -> Result<T>,
    {
        let conn = self.pool.get_conn()?;

        let res = f(conn)?;

        Ok(res)
    }
}

// writer
impl<'a> PTSDataBase<'a> {
    pub fn write_measurement(
        &mut self,
        meta_info: &[MetaInfo],
        unix_time_stamp: i64,
        status: &str,
        measurements: &[Measurement],
    ) -> Result<(ConfigID, ResultID)> {
        let config_id = self.get_config(meta_info, unix_time_stamp)?;
        let result_id = self.write_result(status, unix_time_stamp, config_id)?;
        self.write_measurements(result_id, measurements)?;

        Ok((config_id, result_id))
    }

    pub fn remove_result(&mut self, result_id: ResultID) -> Result<()> {
        self.access(|mut conn| {
            let result_stmt = conn.prep(&format!(
                "DELETE
                FROM {}
                WHERE id=?;",
                self.table_names.results
            ))?;

            let measurement_stmt = conn.prep(&format!(
                "DELETE
                FROM {}
                WHERE result_id=?;",
                self.table_names.measurements
            ))?;

            conn.exec_drop(result_stmt, (result_id.0,))?;
            conn.exec_drop(measurement_stmt, (result_id.0,))?;

            Ok(())
        })
    }
}

// reader
impl<'a> PTSDataBase<'a> {
    pub fn configuration_ids(&mut self) -> Result<Vec<ConfigID>> {
        self.access(|mut conn| {
            let mut query = conn.query_iter(&format!(
                "SELECT *
                FROM {};",
                self.table_names.configurations
            ))?;

            let mut ids = Vec::new();

            while let Some(r) = query.iter() {
                for row in r {
                    ids.push(ConfigID(
                        row?.get("id")
                            .ok_or(anyhow!("could not convert config id"))?,
                    ));
                }
            }

            Ok(ids)
        })
    }

    pub fn configuration(&mut self, id: ConfigID) -> Result<Option<Configuration>> {
        Ok(self
            .access(|mut conn| {
                Ok(conn
                    .query_first(&format!(
                        "SELECT *
                        FROM {}
                        WHERE id=\"{}\";",
                        self.table_names.configurations, id.0
                    ))?
                    .map(|row: Row| -> Result<Configuration> {
                        let configs = Configuration {
                            id: ConfigID(
                                row.get("id")
                                    .ok_or(anyhow!("could not convert config id"))?,
                            ),
                            time: row
                                .get("time")
                                .ok_or(anyhow!("could not convert unix time"))?,
                            infos: Vec::new(),
                            results: Vec::new(),
                        };

                        Ok(configs)
                    })
                    .transpose()?)
            })?
            .map(|mut config| -> Result<Configuration> {
                self.infos(&mut config)?;
                self.results(&mut config)?;

                Ok(config)
            })
            .transpose()?)
    }

    pub fn configurations(&mut self) -> Result<Vec<Configuration>> {
        let mut configs = self.access(|mut conn| {
            let mut query = conn.query_iter(&format!(
                "SELECT *
                FROM {};",
                self.table_names.configurations
            ))?;

            let mut configs = Vec::new();

            while let Some(r) = query.iter() {
                for row in r {
                    let row = row?;

                    configs.push(Configuration {
                        id: ConfigID(
                            row.get("id")
                                .ok_or(anyhow!("could not convert config id"))?,
                        ),
                        time: row
                            .get("time")
                            .ok_or(anyhow!("could not convert unix time"))?,
                        infos: Vec::new(),
                        results: Vec::new(),
                    });
                }
            }

            Ok(configs)
        })?;

        for config in configs.iter_mut() {
            self.infos(config)?;
            self.results(config)?;
        }

        Ok(configs)
    }

    pub fn infos(&mut self, configuration: &mut Configuration) -> Result<()> {
        self.access(|mut conn| {
            let mut query = conn.query_iter(&format!(
                "SELECT *
                FROM {}
                WHERE config_id=\"{}\";",
                self.table_names.infos, configuration.id.0,
            ))?;

            while let Some(r) = query.iter() {
                for row in r {
                    let row = row?;

                    configuration.infos.push(Info {
                        id: InfoID(row.get("id").ok_or(anyhow!("could not convert info id"))?),
                        config_id: ConfigID(
                            row.get("config_id")
                                .ok_or(anyhow!("could not convert config_id"))?,
                        ),
                        name: row.get("name").ok_or(anyhow!("could not convert name"))?,
                        value: row.get("value").ok_or(anyhow!("could not convert value"))?,
                    });
                }
            }

            Ok(())
        })
    }

    pub fn results(&mut self, configuration: &mut Configuration) -> Result<()> {
        self.access(|mut conn| {
            let mut query = conn.query_iter(&format!(
                "SELECT *
                FROM {}
                WHERE config_id=\"{}\";",
                self.table_names.results, configuration.id.0,
            ))?;

            while let Some(r) = query.iter() {
                for row in r {
                    let row = row?;

                    configuration.results.push(DBResult {
                        id: ResultID(
                            row.get("id")
                                .ok_or(anyhow!("could not convert result id"))?,
                        ),
                        config_id: ConfigID(
                            row.get("config_id")
                                .ok_or(anyhow!("could not convert config_id"))?,
                        ),
                        time: row
                            .get("time")
                            .ok_or(anyhow!("could not convert unix time"))?,
                        status: row
                            .get("status")
                            .ok_or(anyhow!("could not convert status"))?,
                        measurements: Vec::new(),
                    });
                }
            }

            Ok(())
        })?;

        for result in configuration.results.iter_mut() {
            self.measurements(result)?;
        }

        Ok(())
    }

    pub fn measurements(&mut self, result: &mut DBResult) -> Result<()> {
        self.access(|mut conn| {
            let mut query = conn.query_iter(&format!(
                "SELECT *
                FROM {}
                WHERE result_id=\"{}\";",
                self.table_names.measurements, result.id.0,
            ))?;

            while let Some(r) = query.iter() {
                for row in r {
                    let row = row?;

                    result.measurements.push(OutMeasurement {
                        id: MeasurementID(
                            row.get("id")
                                .ok_or(anyhow!("could not convert measurement id"))?,
                        ),
                        result_id: ResultID(
                            row.get("result_id")
                                .ok_or(anyhow!("could not convert result id"))?,
                        ),
                        name: row.get("name").ok_or(anyhow!("could not convert name"))?,
                        value: row.get("value").ok_or(anyhow!("could not convert value"))?,
                        rights: {
                            let tmp: Option<String> = row
                                .get("rights")
                                .ok_or(anyhow!("could not convert rights"))?;

                            tmp.map(|rights| Rights::try_from(rights)).transpose()?
                        },
                    });
                }
            }

            Ok(())
        })
    }

    pub fn search_infos(&mut self, meta_info: &[MetaInfo]) -> Result<Option<ConfigID>> {
        if meta_info.len() == 0 {
            return Ok(None);
        }

        let mut query = format!(
            "SELECT m.config_id \n FROM {} AS m ",
            self.table_names.infos
        );

        if meta_info.len() > 1 {
            for (index, _) in meta_info.iter().enumerate() {
                let letter = format!("n{}", index);

                query += &format!(
                    "LEFT JOIN {} AS {} \n ON m.config_id={}.config_id ",
                    self.table_names.infos, letter, letter
                );
            }
        }

        query += &format!(
            "WHERE m.name=\'{}\' AND m.value=\'{}\' ",
            meta_info[0].name, meta_info[0].value
        );

        if meta_info.len() > 1 {
            for (index, meta) in meta_info.iter().enumerate() {
                let letter = format!("n{}", index);

                query += &format!(
                    "AND {letter}.name=\'{}\' AND {letter}.value=\'{}\' ",
                    meta.name, meta.value
                );
            }
        }

        query += ";";

        self.access(|mut conn| Ok(conn.query_first(query)?.map(|i| ConfigID(i))))
    }

    pub fn check_for_infos(&mut self, meta_info: &[MetaInfo]) -> Result<bool> {
        Ok(self.search_infos(meta_info)?.is_some())
    }
}

// helper
impl<'a> PTSDataBase<'a> {
    fn write_result(
        &mut self,
        status: &str,
        unix_time_stamp: i64,
        config_id: ConfigID,
    ) -> Result<ResultID> {
        self.access(|mut conn| {
            conn.exec_drop(
                &format!(
                    "INSERT INTO `{}` (status, time, config_id)
                    VALUES(:status, :time_stamp, :config_id);",
                    self.table_names.results
                ),
                params! {
                    "status" => status,
                    "time_stamp" => unix_time_stamp,
                    "config_id" => config_id.0,
                },
            )?;

            Ok(ResultID(conn.last_insert_id() as i64))
        })
    }

    fn write_measurements(
        &mut self,
        result_id: ResultID,
        measurements: &[Measurement],
    ) -> Result<()> {
        self.access(|mut conn| {
            conn.exec_batch(
                &format!(
                    "INSERT INTO `{}` (result_id, name, value, rights)
                    VALUES(:result_id, :name, :value, :rights);",
                    self.table_names.measurements
                ),
                measurements.iter().map(|measurement| {
                    params! {
                        "result_id" => result_id.0,
                        "name" => measurement.name,
                        "value" => measurement.value,
                        "rights" => measurement.rights,
                    }
                }),
            )?;

            Ok(())
        })
    }

    fn new_config(&mut self, unix_time_stamp: i64) -> Result<ConfigID> {
        self.access(|mut conn| {
            let stmt = conn.prep(&format!(
                "INSERT INTO `{}` (time)
                VALUES(?);",
                self.table_names.configurations
            ))?;

            conn.exec_drop(stmt, (unix_time_stamp,))?;

            Ok(ConfigID(conn.last_insert_id() as i64))
        })
    }

    fn insert_info(&mut self, meta_info: &[MetaInfo], unix_time_stamp: i64) -> Result<ConfigID> {
        let id = self.new_config(unix_time_stamp)?;

        self.access(|mut conn| {
            conn.exec_batch(
                &format!(
                    "INSERT INTO `{}` (name, value, config_id)
                    VALUES(:name, :value, :config_id);",
                    self.table_names.infos
                ),
                meta_info.iter().map(|meta| {
                    params! {
                        "name" => &meta.name,
                        "value" => &meta.value,
                        "config_id" => id.0
                    }
                }),
            )?;

            Ok(())
        })?;

        Ok(id)
    }

    fn get_config<'b>(&mut self, meta_info: &[MetaInfo], unix_time_stamp: i64) -> Result<ConfigID> {
        match self.search_infos(meta_info)? {
            Some(id) => Ok(id),
            None => self.insert_info(&meta_info, unix_time_stamp),
        }
    }
}

#[cfg(test)]
mod test {
    use std::net::Ipv4Addr;

    use crate::rights::Rights;

    use super::*;
    use anyhow::Result;
    use serde_json::{from_str, to_string_pretty};

    const T: &str = "
    {
        \"configurations\": \"api_configurations\",
        \"infos\": \"api_info\",
        \"measurements\": \"api_measurements\",
        \"results\": \"api_results\"
    }";

    const C: &str = "
    {
        \"user\": \"device\",
        \"password\": \"password\",
        \"url\": \"127.0.0.1\",
        \"port\": 3306,
        \"schema\": \"ptsdata\"
    }";

    #[test]
    fn connect_to_pts_db() -> Result<()> {
        let db_info: &str = "
        {
            \"user\": \"device\",
            \"password\": \"password\",
            \"url\": \"127.0.0.1\",
            \"port\": 3306,
            \"schema\": \"ptsdata\"
        }";

        let table_names = from_str(T)?;
        let config = from_str(db_info)?;

        PTSDataBase::new(config, table_names)?;

        Ok(())
    }

    #[test]
    fn db_config_read_write() -> Result<()> {
        const CONF_FILE: &str = "db_test_config.json";

        let lh = Ipv4Addr::LOCALHOST.to_string();
        let conf = DataBaseConfig::new(
            "test_user",
            "test_password",
            lh.as_str(),
            62345,
            "test_schema",
        );

        std::fs::write(CONF_FILE, to_string_pretty(&conf)?)?;
        let s = std::fs::read_to_string(CONF_FILE)?;

        let c = from_str(&s)?;

        assert_eq!(conf, c);

        Ok(())
    }

    #[test]
    fn table_names_read_write() -> Result<()> {
        const TB_NAME_FILE: &str = "table_names_test.json";

        let table_names = TableNames {
            configurations: "configs",
            infos: "infos",
            measurements: "measurements",
            results: "results",
        };

        std::fs::write(TB_NAME_FILE, to_string_pretty(&table_names)?)?;
        let s = std::fs::read_to_string(TB_NAME_FILE)?;

        let c = from_str(&s)?;

        assert_eq!(table_names, c);

        Ok(())
    }

    #[test]
    fn establish_db_connection() -> Result<()> {
        let table_names = from_str(T)?;
        let config = from_str(C)?;

        assert!(PTSDataBase::new(config, table_names).is_ok());

        Ok(())
    }

    #[test]
    fn test_read() -> Result<()> {
        let table_names = from_str(T)?;
        let config = from_str(C)?;

        let mut db = PTSDataBase::new(config, table_names)?;

        let ids = db.configuration_ids()?;

        if let Some(id) = ids.first() {
            assert!(db.configuration(*id).is_ok());
        }

        Ok(())
    }

    #[test]
    fn test_write() -> Result<()> {
        let table_names = from_str(T)?;
        let config = from_str(C)?;

        let mut db = PTSDataBase::new(config, table_names)?;

        let meta_info = [("Test", "1.0").into()];

        let measurements = [Measurement {
            name: "TestKey",
            value: 2.0,
            rights: Some(Rights::PremiumUser),
        }];

        let (config_id, result_id) = db.write_measurement(&meta_info, 0, "Ok", &measurements)?;

        // check values written to DB
        {
            let config = db
                .configuration(config_id)?
                .expect("config id should be available!");

            let result = config
                .results
                .iter()
                .find(|result| result.id == result_id)
                .expect("result is expected to be available");

            assert_eq!(result.measurements.len(), 1);
            assert_eq!(result.measurements[0].name, measurements[0].name);
            assert_eq!(result.measurements[0].value, measurements[0].value);
            assert_eq!(result.measurements[0].rights, measurements[0].rights);
        }

        db.remove_result(result_id)?;

        // check that values are successfully cleared afterwards
        {
            let config = db
                .configuration(config_id)?
                .expect("config id should be available");

            assert!(config
                .results
                .iter()
                .find(|result| result.id == result_id)
                .is_none());
        }

        Ok(())
    }
}
