use anyhow::Result;
use rusqlite::fallible_iterator::FallibleIterator;

use crate::database::{DBExecutable, QueryResult};

#[derive(PartialEq, Debug)]
struct User {
    name: Option<String>,
    email: String,
}

impl User {
    fn new(email: &str) -> Self {
        Self {
            name: None,
            email: String::from(email),
        }
    }

    fn insert(&self) -> UserQuery<'_> {
        let name = self.name.as_deref();
        UserQuery::Insert(name, self.email.as_str())
    }

    fn delete(&self) -> UserQuery<'_> {
        UserQuery::Delete(&self.email)
    }

    fn query_by_email(&self) -> UserQuery<'_> {
        UserQuery::QueryByEmail(&self.email)
    }

    fn query_all<'a>() -> UserQuery<'a> {
        UserQuery::QueryAll
    }

    fn subscribe<'a>(&'a self, list: &'a List) -> SubscriptionQuery<'a> {
        SubscriptionQuery::Insert(&self.email, &list.name)
    }

    fn unsubscribe<'a>(&'a self, list: &'a List) -> SubscriptionQuery<'a> {
        SubscriptionQuery::Delete(&self.email, &list.name)
    }

    fn subscribed_to(&self) -> SubscriptionQuery<'_> {
        SubscriptionQuery::Lists(&self.email)
    }
}

enum UserQuery<'a> {
    Insert(Option<&'a str>, &'a str),
    Delete(&'a str),
    QueryByEmail(&'a str),
    QueryAll,
}

impl DBExecutable for UserQuery<'_> {
    type T = User;
    fn execute(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<Self::T>> {
        match self {
            UserQuery::Insert(_, _) => self.db_insert(tx),
            UserQuery::Delete(_) => self.db_delete(tx),
            UserQuery::QueryByEmail(_) => self.db_query_by_email(tx),
            UserQuery::QueryAll => self.db_query_all(tx),
        }
    }
}

impl UserQuery<'_> {
    fn db_insert(&self, tx: &rusqlite::Transaction<'_>) -> Result<QueryResult<User>> {
        let (name, email) = if let UserQuery::Insert(name, email) = self {
            (name, email)
        } else {
            unreachable!("this should only be called by a UserQuery::Insert")
        };

        if let Some(name) = name {
            let q = "INSERT INTO user (name, email) VALUES (?, ?)";
            tx.execute(q, [name, email])?;
            return Ok(QueryResult::Empty);
        }

        let q = "INSERT INTO user (email) VALUES (?)";
        tx.execute(q, [email])?;
        Ok(QueryResult::Empty)
    }

    fn db_delete(&self, tx: &rusqlite::Transaction<'_>) -> Result<QueryResult<User>> {
        let email = if let UserQuery::Delete(email) = self {
            email
        } else {
            unreachable!("this should only be called by a UserQuery::Delete")
        };

        let q = "DELETE FROM user WHERE email = ?";
        tx.execute(q, [email])?;
        Ok(QueryResult::Empty)
    }

    fn db_query_by_email(&self, tx: &rusqlite::Transaction<'_>) -> Result<QueryResult<User>> {
        let email = if let UserQuery::QueryByEmail(email) = self {
            email
        } else {
            unreachable!("this should only be called by a UserQuery::QueryByEmail")
        };

        let q = "SELECT * FROM user WHERE email LIKE ?";
        let mut stmt = tx.prepare(q)?;
        let ris = stmt
            .query([email])?
            .map(|row| {
                Ok(User {
                    name: row.get(1).unwrap(),
                    email: row.get(2).unwrap(),
                })
            })
            .collect()?;
        Ok(QueryResult::Vec(ris))
    }

    fn db_query_all(&self, tx: &rusqlite::Transaction<'_>) -> Result<QueryResult<User>> {
        let q = "SELECT * FROM user";
        let mut stmt = tx.prepare(q)?;
        let ris = stmt
            .query(())?
            .map(|row| {
                Ok(User {
                    name: row.get(1).unwrap(),
                    email: row.get(2).unwrap(),
                })
            })
            .collect()?;
        Ok(QueryResult::Vec(ris))
    }
}

#[derive(Debug, PartialEq)]
struct List {
    name: String,
    desc: Option<String>,
}

impl List {
    fn new(name: &str) -> Self {
        List {
            name: String::from(name),
            desc: None,
        }
    }

    fn insert(&self) -> ListQuery<'_> {
        ListQuery::Insert(&self.name, self.desc.as_deref())
    }

    fn delete(&self) -> ListQuery<'_> {
        ListQuery::Delete(&self.name)
    }

    fn query_all<'a>() -> ListQuery<'a> {
        ListQuery::QueryAll
    }

    fn subscribers(&self) -> SubscriptionQuery<'_> {
        SubscriptionQuery::Subscribers(&self.name)
    }
}

enum ListQuery<'a> {
    Insert(&'a str, Option<&'a str>),
    Delete(&'a str),
    QueryByAddress(&'a str),
    QueryAll,
}

impl DBExecutable for ListQuery<'_> {
    type T = List;

    fn execute(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<Self::T>> {
        match self {
            ListQuery::Insert(_, _) => self.db_insert(tx),
            ListQuery::Delete(_) => self.db_delete(tx),
            ListQuery::QueryByAddress(_) => self.db_query_by_address(tx),
            ListQuery::QueryAll => self.db_query_all(tx),
        }
    }
}

impl ListQuery<'_> {
    fn db_insert(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<List>> {
        let (name, desc) = if let ListQuery::Insert(name, desc) = self {
            (name, desc)
        } else {
            unreachable!("this should only be called by ListQuery::Insert");
        };

        if let Some(desc) = desc {
            let q = "INSERT INTO list (name, description) VALUES (?, ?)";
            tx.execute(q, [name, desc])?;
        } else {
            let q = "INSERT INTO list (name) VALUES (?)";
            tx.execute(q, [name])?;
        }
        Ok(QueryResult::Empty)
    }

    fn db_delete(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<List>> {
        let name = if let ListQuery::Delete(name) = self {
            name
        } else {
            unreachable!("this should only be called by ListQuery::Delete")
        };

        let q = "DELETE FROM list WHERE name = ?";
        tx.execute(q, [name])?;
        Ok(QueryResult::Empty)
    }

    fn db_query_by_address(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<List>> {
        let name = if let ListQuery::QueryByAddress(name) = self {
            name
        } else {
            unreachable!("this should only be called by ListQuery::QueryByName")
        };

        let q = "SELECT * FROM list WHERE name = ?";
        let mut stmt = tx.prepare(q)?;
        let ris = stmt
            .query([name])?
            .map(|row| {
                Ok(List {
                    name: row.get(1).unwrap(),
                    desc: row.get(2).unwrap(),
                })
            })
            .collect()?;
        Ok(QueryResult::Vec(ris))
    }

    fn db_query_all(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<List>> {
        let q = "SELECT * FROM list";
        let mut stmt = tx.prepare(q)?;
        let ris = stmt
            .query(())?
            .map(|row| {
                Ok(List {
                    name: row.get(1).unwrap(),
                    desc: row.get(2).unwrap(),
                })
            })
            .collect()?;
        Ok(QueryResult::Vec(ris))
    }
}

enum SubscriptionResult {
    List(List),
    Subscriber(User),
    Count(i64),
}

enum SubscriptionQuery<'a> {
    Insert(&'a str, &'a str),
    Delete(&'a str, &'a str),
    /// This is just for debug
    Count,
    Lists(&'a str),
    Subscribers(&'a str),
}

impl DBExecutable for SubscriptionQuery<'_> {
    type T = SubscriptionResult;

    fn execute(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<Self::T>> {
        match self {
            SubscriptionQuery::Insert(_, _) => self.db_insert(tx),
            SubscriptionQuery::Count => self.db_query_all(tx),
            SubscriptionQuery::Delete(_, _) => self.db_delete(tx),
            SubscriptionQuery::Lists(_) => self.db_lists(tx),
            SubscriptionQuery::Subscribers(_) => self.db_subscribers(tx),
        }
    }
}

impl SubscriptionQuery<'_> {
    fn db_insert(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<SubscriptionResult>> {
        let (email, list_name) = if let SubscriptionQuery::Insert(a, b) = self {
            (a, b)
        } else {
            unreachable!("this should only be called by SubscriptionQuery::Insert")
        };

        let q = "INSERT INTO subscription
            SELECT user.id, list.id FROM user CROSS JOIN list
            WHERE user.email = ? AND list.name = ?";
        tx.execute(q, [email, list_name])?;

        Ok(QueryResult::Empty)
    }

    fn db_query_all(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<SubscriptionResult>> {
        match self {
            SubscriptionQuery::Count => (),
            _ => unreachable!("this should only be called by SubscriptionQuery::Count"),
        };

        let q = "SELECT COUNT(*) FROM subscription";
        let ris = tx.query_one(q, (), |row| Ok(row.get(0).unwrap()))?;
        Ok(QueryResult::Single(SubscriptionResult::Count(ris)))
    }

    fn db_delete(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<SubscriptionResult>> {
        let (email, name) = if let SubscriptionQuery::Delete(e, n) = self {
            (e, n)
        } else {
            unreachable!("this should only be called by SubscriptionQuery::Delete")
        };

        let q = "WITH helper AS (SELECT user.id AS user_id, list.id AS list_id FROM user CROSS JOIN list
            WHERE user.email = ? AND list.name = ?)
            DELETE FROM subscription WHERE EXISTS (
                SELECT 1 FROM helper
                WHERE helper.user_id = subscription.user_id AND helper.list_id = subscription.list_id)";
        tx.execute(q, [email, name])?;
        Ok(QueryResult::Empty)
    }

    fn db_lists(&self, tx: &rusqlite::Transaction) -> Result<QueryResult<SubscriptionResult>> {
        let email = if let SubscriptionQuery::Lists(email) = self {
            email
        } else {
            unreachable!("this should only be called by SubscriptionQuery::Lists")
        };

        let q = "SELECT list.name, list.description
            FROM subscription JOIN list ON list.id = subscription.list_id
            JOIN user ON user.id = subscription.user_id
            WHERE user.email = ?";
        let ris = tx
            .prepare(q)?
            .query([email])?
            .map(|row| {
                Ok(SubscriptionResult::List(List {
                    name: row.get(0).unwrap(),
                    desc: row.get(1).unwrap(),
                }))
            })
            .collect()?;

        Ok(QueryResult::Vec(ris))
    }

    fn db_subscribers(
        &self,
        tx: &rusqlite::Transaction,
    ) -> Result<QueryResult<SubscriptionResult>> {
        let name = if let SubscriptionQuery::Subscribers(name) = self {
            name
        } else {
            unreachable!("this should only be called by SubscriptionQuery::Subscribers")
        };

        let q = "SELECT user.name, user.email
            FROM subscription JOIN list ON list.id = subscription.list_id
            JOIN user ON user.id = subscription.user_id
            WHERE list.name = ?";
        let ris = tx
            .prepare(q)?
            .query([name])?
            .map(|row| {
                Ok(SubscriptionResult::Subscriber(User {
                    name: row.get(0).unwrap(),
                    email: row.get(1).unwrap(),
                }))
            })
            .collect()?;

        Ok(QueryResult::Vec(ris))
    }
}

#[cfg(test)]
mod tests {
    use anyhow::Result;

    use crate::{
        database::{DB_NAME, Database},
        list::{List, SubscriptionQuery, User},
    };

    fn setup() -> Result<Database> {
        std::fs::create_dir("test")?;
        Database::new(&format!("test/{}", DB_NAME))
    }

    fn cleanup() -> Result<()> {
        std::fs::remove_dir_all("test")?;
        Ok(())
    }

    #[test]
    fn user_insert_wo_name() {
        let mut database = setup().expect("Failed setup");

        let user = User {
            name: None,
            email: String::from("mroik@poul.org"),
        };
        database.execute(user.insert()).expect("Failed insert");
        let user2 = database
            .execute(user.query_by_email())
            .expect("Failed query");
        match user2 {
            crate::database::QueryResult::Empty => assert!(false),
            crate::database::QueryResult::Vec(items) => {
                assert_eq!(items.len(), 1);
                assert_eq!(items[0], user);
            }
            crate::database::QueryResult::Single(_) => assert!(false),
        }

        drop(database);
        cleanup().expect("Failed cleanup");
    }

    #[test]
    fn user_insert_with_name() {
        let mut database = setup().expect("Failed setup");

        let user = User {
            name: Some(String::from("Mirko Faina")),
            email: String::from("mroik@poul.org"),
        };
        database.execute(user.insert()).expect("Failed insert");
        let user2 = database
            .execute(user.query_by_email())
            .expect("Failed query");
        match &user2 {
            crate::database::QueryResult::Empty => assert!(false),
            crate::database::QueryResult::Vec(items) => {
                assert_eq!(items.len(), 1);
                assert_eq!(items[0], user);
            }
            crate::database::QueryResult::Single(_) => assert!(false),
        }

        drop(database);
        cleanup().expect("Failed cleanup");
    }

    #[test]
    fn user_insert_twice() {
        let mut database = setup().expect("Failed setup");

        let user = User {
            name: Some(String::from("Mirko Faina")),
            email: String::from("mroik@poul.org"),
        };
        database.execute(user.insert()).expect("Failed insert");
        let ris = database.execute(user.insert());
        assert!(ris.is_err());

        drop(database);
        cleanup().expect("Failed cleanup");
    }

    #[test]
    fn user_delete() {
        let mut database = setup().expect("Failed setup");

        let user = User {
            name: Some(String::from("Mirko Faina")),
            email: String::from("mroik@poul.org"),
        };
        database.execute(user.insert()).expect("Failed insert");
        database.execute(user.delete()).expect("Failed delete");

        let ris = database
            .execute(User::query_all())
            .expect("Failed query all");
        match ris {
            crate::database::QueryResult::Empty => assert!(false),
            crate::database::QueryResult::Vec(items) => assert!(items.is_empty()),
            crate::database::QueryResult::Single(_) => assert!(false),
        }

        drop(database);
        cleanup().expect("Failed cleanup");
    }

    #[test]
    fn list_insert_wo_desc() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: None,
        };
        database.execute(list.insert()).expect("failed insert");
        let ris = database.execute(List::query_all()).expect("failed query");
        match ris {
            crate::database::QueryResult::Empty => assert!(false),
            crate::database::QueryResult::Vec(items) => {
                assert_eq!(items.len(), 1);
                assert_eq!(items[0], list);
            }
            crate::database::QueryResult::Single(_) => assert!(false),
        }

        cleanup().expect("failed cleanup");
    }

    #[test]
    fn list_insert_with_desc() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: Some(String::from("The mailing list of the POuL")),
        };
        database.execute(list.insert()).expect("failed insert");
        let ris = database.execute(List::query_all()).expect("failed query");
        match ris {
            crate::database::QueryResult::Empty => assert!(false),
            crate::database::QueryResult::Vec(items) => {
                assert_eq!(items.len(), 1);
                assert_eq!(items[0], list);
            }
            crate::database::QueryResult::Single(_) => assert!(false),
        }

        drop(database);
        cleanup().expect("failed cleanup");
    }

    #[test]
    fn list_insert_twice() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: Some(String::from("The mailing list of the POuL")),
        };
        database.execute(list.insert()).expect("failed insert");
        assert!(database.execute(list.insert()).is_err());

        drop(database);
        cleanup().expect("failed cleanup");
    }

    #[test]
    fn list_delete() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: Some(String::from("The mailing list of the POuL")),
        };
        database.execute(list.insert()).expect("failed insert");
        database.execute(list.delete()).expect("failed delete");

        let ris = database
            .execute(List::query_all())
            .expect("Failed query all");
        match ris {
            crate::database::QueryResult::Empty => assert!(false),
            crate::database::QueryResult::Vec(items) => assert!(items.is_empty()),
            crate::database::QueryResult::Single(_) => assert!(false),
        }

        drop(database);
        cleanup().expect("failed cleanup");
    }

    #[test]
    fn subscription_insert() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: None,
        };
        let list2 = List {
            name: String::from("dev"),
            desc: None,
        };

        database.execute(list.insert()).expect("failed insert");
        database.execute(list2.insert()).expect("failed insert");

        let user = User {
            name: None,
            email: String::from("mroik@delayed.space"),
        };
        let user2 = User {
            name: None,
            email: String::from("mroik@poul.org"),
        };

        database.execute(user.insert()).expect("failed insert");
        database.execute(user2.insert()).expect("failed insert");

        database
            .execute(user.subscribe(&list))
            .expect("failed insert");

        let ris = database
            .execute(SubscriptionQuery::Count)
            .expect("failed query");
        match ris {
            crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => {
                assert_eq!(c, 1)
            }
            _ => assert!(false),
        }

        drop(database);
        cleanup().expect("failed cleanup");
    }

    #[test]
    fn subscription_delete() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: None,
        };
        let list2 = List {
            name: String::from("dev"),
            desc: None,
        };

        database.execute(list.insert()).expect("failed insert");
        database.execute(list2.insert()).expect("failed insert");

        let user = User {
            name: None,
            email: String::from("mroik@delayed.space"),
        };
        let user2 = User {
            name: None,
            email: String::from("mroik@poul.org"),
        };

        database.execute(user.insert()).expect("failed insert");
        database.execute(user2.insert()).expect("failed insert");

        database
            .execute(user.subscribe(&list))
            .expect("failed insert");
        database
            .execute(user.subscribe(&list2))
            .expect("failed insert");
        database
            .execute(user2.subscribe(&list))
            .expect("failed insert");
        database
            .execute(user2.subscribe(&list2))
            .expect("failed insert");

        let mut ris = database
            .execute(SubscriptionQuery::Count)
            .expect("failed query");
        match ris {
            crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => {
                assert_eq!(c, 4)
            }
            _ => assert!(false),
        }

        database
            .execute(user.unsubscribe(&list))
            .expect("failed delete");

        ris = database
            .execute(SubscriptionQuery::Count)
            .expect("failed query");
        match ris {
            crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => {
                assert_eq!(c, 3)
            }
            _ => assert!(false),
        }

        drop(database);
        cleanup().expect("failed cleanup");
    }

    #[test]
    fn subscription_lists() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: None,
        };
        let list2 = List {
            name: String::from("dev"),
            desc: None,
        };

        database.execute(list.insert()).expect("failed insert");
        database.execute(list2.insert()).expect("failed insert");

        let user = User {
            name: None,
            email: String::from("mroik@delayed.space"),
        };
        let user2 = User {
            name: None,
            email: String::from("mroik@poul.org"),
        };

        database.execute(user.insert()).expect("failed insert");
        database.execute(user2.insert()).expect("failed insert");

        database
            .execute(user.subscribe(&list))
            .expect("failed insert");
        database
            .execute(user.subscribe(&list2))
            .expect("failed insert");
        database
            .execute(user2.subscribe(&list))
            .expect("failed insert");
        database
            .execute(user2.subscribe(&list2))
            .expect("failed insert");

        let mut ris = database
            .execute(SubscriptionQuery::Count)
            .expect("failed query");
        match ris {
            crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => {
                assert_eq!(c, 4)
            }
            _ => assert!(false),
        }

        ris = database
            .execute(user.subscribed_to())
            .expect("failed query");
        match ris {
            crate::database::QueryResult::Vec(items) => assert_eq!(items.len(), 2),
            _ => assert!(false),
        }

        drop(database);
        cleanup().expect("failed cleanup");
    }

    #[test]
    fn subscription_subscribers() {
        let mut database = setup().expect("failed setup");

        let list = List {
            name: String::from("poul"),
            desc: None,
        };
        let list2 = List {
            name: String::from("dev"),
            desc: None,
        };

        database.execute(list.insert()).expect("failed insert");
        database.execute(list2.insert()).expect("failed insert");

        let user = User {
            name: None,
            email: String::from("mroik@delayed.space"),
        };
        let user2 = User {
            name: None,
            email: String::from("mroik@poul.org"),
        };

        database.execute(user.insert()).expect("failed insert");
        database.execute(user2.insert()).expect("failed insert");

        database
            .execute(user.subscribe(&list))
            .expect("failed insert");
        database
            .execute(user.subscribe(&list2))
            .expect("failed insert");
        database
            .execute(user2.subscribe(&list))
            .expect("failed insert");
        database
            .execute(user2.subscribe(&list2))
            .expect("failed insert");

        let mut ris = database
            .execute(SubscriptionQuery::Count)
            .expect("failed query");
        match ris {
            crate::database::QueryResult::Single(crate::list::SubscriptionResult::Count(c)) => {
                assert_eq!(c, 4)
            }
            _ => assert!(false),
        }

        ris = database.execute(list.subscribers()).expect("failed query");
        match ris {
            crate::database::QueryResult::Vec(items) => assert_eq!(items.len(), 2),
            _ => assert!(false),
        }

        drop(database);
        cleanup().expect("failed cleanup");
    }
}