use std::path::Path;
use rusqlite::{Connection, Error};

use crate::database::problem::Problem;

pub struct Database {
    connection: Connection,
}

impl Database {
    pub fn new(database_path: impl AsRef<Path>) -> Result<Self, Error> {
        let connection =  Connection::open(database_path)?;
        Ok(Database {
            connection,
        })
    }

    pub fn new_in_memory() -> Result<Self, Error> {
        let connection =  Connection::open_in_memory()?;
        Ok(Database {
            connection,
        })
    }

    pub fn insert_problem(&self, title: &str, description: &str) -> Result<Problem, Error> {
        static QUERY: &str = include_str!("sql/insert_problem.sql");
        let mut statement = self.connection.prepare(QUERY)?;

        let result = statement
            .query_one((title, description), |row| {
                Ok(Problem::new(
                    row.get("id")?,
                    title.to_owned(),
                    description.to_owned(),
                ))
            })?;

        Ok(result)
    }

    pub fn delete_problem(&self, id: i64) -> Result<(), Error> {
        todo!();
    }
    
    pub fn fetch_problems(&self) -> Result<Vec<Problem>, Error> {
        static QUERY: &str = include_str!("sql/fetch_problems.sql");
        let mut statement = self.connection.prepare(QUERY)?;

        let problems = statement
            .query_map([], |row| {
                Ok(Problem::new(
                    row.get("id")?,
                    row.get("title")?,
                    row.get("description")?,
                ))
            })?
            .collect::<Result<Vec<Problem>, _>>()?;
        
        Ok(problems)
    }
    
    pub fn initialize(&self) -> Result<(), Error> {
        static QUERY: &str = include_str!("sql/initialize.sql");        
        self.connection.execute_batch(QUERY)?;
        Ok(())
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    
    #[tokio::test]
    async fn test_problem_database() {
        let db = Database::new_in_memory().unwrap();

        db.initialize().unwrap();

        let title = "test problem 1";
        let description = "description of test problem 1";
        
        let problem = db.insert_problem(title, description).unwrap();
        assert_eq!(problem.title(), title);
        assert_eq!(problem.description(), description);

        let problems = db.fetch_problems().unwrap();
        assert_eq!(problems.len(), 1);
        assert_eq!(problems[0].title(), title);
        assert_eq!(problems[0].description(), description);
    }
}