use std::{collections::HashSet, path::Path};

use chrono::NaiveDateTime;
use rusqlite::{params, params_from_iter, Connection, ToSql};
use uuid::Uuid;

use crate::{
    error::DatabaseOpenError,
    task::{Category, Log, Task},
    Result,
};

struct Db(Connection);

impl Db {
    pub fn create_connect_and_migrate(
        path: impl AsRef<Path>,
    ) -> std::result::Result<Self, DatabaseOpenError> {
        if let Some(dir) = path.as_ref().parent() {
            if dir.is_dir() {
            } else {
                std::fs::create_dir_all(dir)?;
            }
            let _file = std::fs::OpenOptions::new()
                .append(true)
                .create(true)
                .open(path.as_ref())?;
        }
        let url = format!(
            "{}",
            path.as_ref()
                .to_str()
                .ok_or(DatabaseOpenError::InvalidPath)?
        );
        // let db = SqlitePool::connect(&url).await?;
        // migrate!().run(&db).await?;
        // Ok(Self { db })
        let db = Connection::open(url)?;
        db.execute_batch(include_str!("../migrations/1.sql"))?;
        Ok(Self(db))
    }

    pub fn create_connect_memory() -> std::result::Result<Self, DatabaseOpenError> {
        let db = Connection::open_in_memory()?;
        db.execute_batch(include_str!("../migrations/1.sql"))?;
        Ok(Self(db))
    }

    pub fn create_task(&self, task: &Task) -> Result<()> {
        let task_id = Uuid::new_v4();
        self.0.execute(
            "insert into tasks ( id, name, cron, archived, description ) values ( ?1, ?2, ?3, ?4, ?5 )",
            (task_id, &task.name, &task.cron, task.archived, &task.description),
        )?;

        for category in &task.categories {
            let category_id = Uuid::new_v4();
            self.0.execute(
                "insert or ignore into categories ( id, name ) values ( ?1, ?2 )",
                (category_id, category),
            )?;

            self.0.execute(
                "insert into tasks_categories ( task_id, category_id ) values ( ?1, ?2 )",
                [task_id, category_id],
            )?;
        }

        Ok(())
    }

    pub fn read_task(&self, name: impl AsRef<str>) -> Result<Task> {
        let name = name.as_ref();
        let (task_id, mut task) = self
            .0
            .prepare("select id, name, cron, archived, description from tasks where name = ?1")?
            .query_one([name], |row| {
                Ok((
                    row.get::<_, Uuid>(0)?,
                    Task {
                        name: row.get(1)?,
                        cron: row.get(2)?,
                        archived: row.get(3)?,
                        description: row.get(4)?,
                        categories: HashSet::new(),
                    },
                ))
            })?;

        task.categories = self
            .0
            .prepare("select name from tasks_categories join categories where task_id = ?1")?
            .query_map([task_id], |row| Ok(Category::new(row.get(0)?)))?
            .collect::<std::result::Result<HashSet<_>, _>>()?;

        Ok(task)
    }

    // TODO: filter
    pub fn read_tasks(&self, filter: Vec<TaskFilter>) -> Result<Vec<Task>> {
        let mut tasks = self
            .0
            .prepare("select name, cron, archived, description, id from tasks")?
            .query_map([], |row| {
                Ok((
                    row.get::<_, Uuid>(4)?,
                    Task {
                        name: row.get(0)?,
                        cron: row.get(1)?,
                        archived: row.get(2)?,
                        description: row.get(3)?,
                        categories: HashSet::new(),
                    },
                ))
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        for (task_id, task) in &mut tasks {
            let categories = self
                .0
                .prepare("select name from tasks_categories join categories where task_id = ?1")?
                .query_map([*task_id], |row| Ok(Category::new(row.get(0)?)))?
                .collect::<std::result::Result<HashSet<_>, _>>()?;
            task.categories = categories;
        }

        Ok(tasks.into_iter().map(|(_id, task)| task).collect())
    }

    pub fn update_task(&self, name: impl AsRef<str>, updated_task: &Task) -> Result<()> {
        let task_id = self.0
            .prepare("update tasks set name = ?1, cron = ?2, archived = ?3, description = ?4 where name = ?5 returning id")?.query_one( (&updated_task.name, &updated_task.cron, updated_task.archived, &updated_task.description, name.as_ref()), |row| {
                Ok(row.get::<_, Uuid>(0)?)
            })?;
        self.0
            .execute("delete from tasks_categories where task_id = ?1", [task_id])?;
        // TODO: clean up orphaned groups....
        for category in &updated_task.categories {
            let category_id = Uuid::new_v4();
            self.0.execute(
                "insert or ignore into categories ( id, name ) values ( ?1, ?2 )",
                (category_id, category),
            )?;

            self.0.execute(
                "insert into tasks_categories ( task_id, category_id ) values ( ?1, ?2 )",
                [task_id, category_id],
            )?;
        }
        Ok(())
    }

    pub fn delete_task(&self, name: impl AsRef<str>) -> Result<()> {
        self.0
            .execute("delete from tasks where name = ?1", [name.as_ref()])?;
        Ok(())
    }

    pub fn create_log(&self, task_name: impl AsRef<str>, log: &Log) -> Result<()> {
        self.0.execute("insert into log ( id, task_id, timestamp ) values ( ?1, (select id from tasks where name = ?2), ?3 )", (log.id, task_name.as_ref(), log.timestamp))?;
        Ok(())
    }

    pub fn read_logs(&self, filter: Vec<LogFilter>) -> Result<Vec<Log>> {
        let mut query = "select id, timestamp from log".to_string();

        if !filter.is_empty() {
            query.push_str(" where ")
        }
        query.push_str(
            &filter
                .iter()
                .map(|filter| filter.where_clause())
                .collect::<Vec<_>>()
                .join(", "),
        );

        let logs = self
            .0
            .prepare(&query)?
            .query_map(params_from_iter(filter), |row| {
                Ok(Log {
                    id: row.get(0)?,
                    timestamp: row.get(1)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(logs)
    }

    pub fn delete_log(&self, log: &Log) -> Result<()> {
        self.0.execute("delete from logs where id = ?1", [log.id])?;
        Ok(())
    }
}

pub enum TaskFilter {
    Archived(bool),
    Category(String),
}

impl ToSql for TaskFilter {
    fn to_sql(&self) -> rusqlite::Result<rusqlite::types::ToSqlOutput<'_>> {
        match self {
            TaskFilter::Archived(a) => a.to_sql(),
            TaskFilter::Category(c) => c.to_sql(),
        }
    }
}

pub enum LogFilter {
    Task(String),
    Before(NaiveDateTime),
    After(NaiveDateTime),
}

impl LogFilter {
    fn where_clause(&self) -> &'static str {
        match self {
            LogFilter::Task(_) => "task_id = (select id from tasks where name = ?)",
            LogFilter::Before(naive_date_time) => "timestamp < ?",
            LogFilter::After(naive_date_time) => "timestamp > ?",
        }
    }
}

impl ToSql for LogFilter {
    fn to_sql(&self) -> rusqlite::Result<rusqlite::types::ToSqlOutput<'_>> {
        match self {
            LogFilter::Task(n) => n.to_sql(),
            LogFilter::Before(naive_date_time) => naive_date_time.to_sql(),
            LogFilter::After(naive_date_time) => naive_date_time.to_sql(),
        }
    }
}

#[cfg(test)]
mod tests {
    use std::collections::HashSet;

    use crate::task::{Category, Task};

    use super::Db;

    #[test]
    fn create_task() {
        let db = Db::create_connect_memory().unwrap();

        let task = Task::new(
            "piano".to_string(),
            None,
            Some("practice piano".to_string()),
            Some(HashSet::from([Category::new("music".to_string())])),
        );

        db.create_task(&task).unwrap();
    }

    #[test]
    fn read_task() {
        let db = Db::create_connect_memory().unwrap();

        let task = Task::new(
            "piano".to_string(),
            None,
            Some("practice piano".to_string()),
            Some(HashSet::from([Category::new("music".to_string())])),
        );

        db.create_task(&task).unwrap();

        assert_eq!(task, db.read_task("piano").unwrap());
    }

    #[test]
    fn read_tasks() {
        let db = Db::create_connect_memory().unwrap();

        let piano = Task::new(
            "piano".to_string(),
            None,
            Some("practice piano".to_string()),
            Some(HashSet::from([Category::new("music".to_string())])),
        );

        db.create_task(&piano).unwrap();

        let german = Task::new(
            "german".to_string(),
            None,
            Some("study german".to_string()),
            None,
        );

        db.create_task(&german).unwrap();

        assert_eq!(vec![piano, german], db.read_tasks(Vec::new()).unwrap());
    }

    // TODO: task orderings: by day, by week, by what's most urgent.

    #[test]
    fn filter_tasks() {}

    #[test]
    fn update_task() {}

    #[test]
    fn delete_task() {}

    #[test]
    fn create_log() {}

    #[test]
    fn read_logs() {}

    #[test]
    fn filter_logs() {}

    #[test]
    fn delete_log() {}
}