mod connection_options;
mod error;

use self::connection_options::ConnectionOptions;
use self::error::{DatabaseError, DatabaseErrorKind};
use crate::models::{
    Article, ArticleFilter, ArticleID, ArticleOrder, Category, CategoryID, Enclosure, FatArticle, FavIcon, Feed, FeedCount, FeedID, FeedMapping,
    Headline, Marked, Read, SyncResult, Tag, TagID, Tagging,
};
use crate::schema::{articles, categories, enclosures, fav_icons, feed_mapping, feeds, taggings, tags};
use crate::util;
use chrono::{DateTime, Utc};
use diesel;
use diesel::dsl::*;
use diesel::prelude::*;
use diesel::r2d2::{ConnectionManager, Pool};
use diesel::sqlite::SqliteConnection;
use failure::ResultExt;
use log::warn;
use std::fs;
use std::path::{Path, PathBuf};

type DatabaseResult<T> = Result<T, DatabaseError>;

pub struct Database {
    connection_pool: Pool<ConnectionManager<SqliteConnection>>,
}

embed_migrations!("./migrations");

macro_rules! prepare_article_query {
    (
        $filter:expr
    ) => {{
        let mut query = articles::table
            .offset(match $filter.offset {
                Some(offset) => offset,
                None => 0,
            })
            .limit(match $filter.limit {
                Some(limit) => limit,
                None => 20,
            })
            .into_boxed();

        if let Some(order) = $filter.order {
            match order {
                ArticleOrder::NewestFirst => query = query.order(articles::timestamp.desc()),
                ArticleOrder::OldestFirst => query = query.order(articles::timestamp.asc()),
            }
        }

        if let Some(unread) = $filter.unread {
            query = query.filter(articles::unread.eq(unread.to_int()));
        }

        if let Some(marked) = $filter.marked {
            query = query.filter(articles::marked.eq(marked.to_int()));
        }

        if $filter.feed.is_some() && $filter.category.is_some() {
            warn!("querying article by feed AND category might not be very useful");
        }

        if $filter.feed.is_some() && $filter.feed_blacklist.is_some() {
            warn!("querying articles by feed AND blacklisting feeds");
        }

        if let Some(feed) = $filter.feed {
            query = query.filter(articles::feed_id.eq(feed.to_string()));
        }

        if let Some(feed_blacklist) = $filter.feed_blacklist {
            for blacklisted_feed in feed_blacklist {
                query = query.filter(articles::feed_id.ne(blacklisted_feed.to_string()));
            }
        }

        if $filter.category.is_some() && $filter.category_blacklist.is_some() {
            warn!("querying articles by category AND blacklisting categories");
        }

        if let Some(category) = $filter.category {
            let feed_ids_in_category = feed_mapping::table
                .select(feed_mapping::feed_id)
                .filter(feed_mapping::category_id.eq(category));

            query = query.filter(articles::feed_id.eq_any(feed_ids_in_category));
        }

        if let Some(category_blacklist) = $filter.category_blacklist {
            for blacklisted_category in category_blacklist {
                let feed_ids_in_category = feed_mapping::table
                    .select(feed_mapping::feed_id)
                    .filter(feed_mapping::category_id.eq(blacklisted_category));

                query = query.filter(articles::feed_id.ne_all(feed_ids_in_category));
            }
        }

        if let Some(tag) = $filter.tag {
            let tagged_article_ids = taggings::table.select(taggings::article_id).filter(taggings::tag_id.eq(tag));

            query = query.filter(articles::article_id.eq_any(tagged_article_ids));
        }

        if let Some(ids) = $filter.ids {
            query = query.filter(articles::article_id.eq_any(ids));
        }

        if let Some(newer_than) = $filter.newer_than {
            if let Some(older_than) = $filter.older_than {
                if older_than > newer_than {
                    warn!("Impossible constraint: Older than '{}' and newer than '{}'", older_than, newer_than);
                }
            }
        }

        if let Some(newer_than) = $filter.newer_than {
            query = query.filter(articles::timestamp.gt(newer_than.naive_utc()));
        }

        if let Some(older_than) = $filter.older_than {
            query = query.filter(articles::timestamp.lt(older_than.naive_utc()));
        }

        if let Some(search_term) = $filter.search_term {
            let search_term = util::prepare_search_term(&search_term);
            query = query.filter(sql(&format!(
                "article_id IN (SELECT article_id FROM fts_table WHERE fts_table MATCH '{}')",
                search_term
            )));
        }

        query
    }};
}

impl Database {
    fn new_with_file_name(data_dir: &Path, file_name: &str) -> DatabaseResult<Database> {
        if !data_dir.is_dir() {
            return Err(DatabaseErrorKind::InvalidPath)?;
        }

        fs::create_dir_all(&data_dir).context(DatabaseErrorKind::InvalidPath)?;
        let database_url = data_dir.join(file_name);
        let database_url = match database_url.to_str() {
            Some(url) => url.to_owned(),
            None => return Err(DatabaseErrorKind::InvalidPath)?,
        };

        let manager = ConnectionManager::<SqliteConnection>::new(database_url);
        let pool = Pool::builder()
            .connection_customizer(Box::new(ConnectionOptions::default()))
            .max_size(25)
            .build(manager)
            .context(DatabaseErrorKind::Unknown)?;

        let db = Database { connection_pool: pool };

        db.init()?;
        Ok(db)
    }

    pub fn new(data_dir: &PathBuf) -> DatabaseResult<Database> {
        Database::new_with_file_name(data_dir, "database.sqlite")
    }

    fn init(&self) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        embedded_migrations::run(&connection).context(DatabaseErrorKind::Migration)?;
        diesel::sql_query("PRAGMA journal_mode = WAL")
            .execute(&connection)
            .context(DatabaseErrorKind::Options)?;
        Ok(())
    }

    pub fn reset(&self) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            // delete articles
            diesel::delete(articles::table).execute(&connection).context(DatabaseErrorKind::Delete)?;

            // delete categories
            diesel::delete(categories::table)
                .execute(&connection)
                .context(DatabaseErrorKind::Delete)?;

            // delete enclosures
            diesel::delete(enclosures::table)
                .execute(&connection)
                .context(DatabaseErrorKind::Delete)?;

            // delete fav_icons
            diesel::delete(fav_icons::table).execute(&connection).context(DatabaseErrorKind::Delete)?;

            // delete feed_mapping
            diesel::delete(feed_mapping::table)
                .execute(&connection)
                .context(DatabaseErrorKind::Delete)?;

            // delete feeds
            diesel::delete(feeds::table).execute(&connection).context(DatabaseErrorKind::Delete)?;

            // delete taggings
            diesel::delete(taggings::table).execute(&connection).context(DatabaseErrorKind::Delete)?;

            // delete tags
            diesel::delete(tags::table).execute(&connection).context(DatabaseErrorKind::Delete)?;

            Ok(())
        })
    }

    pub fn is_empty(&self) -> Result<bool, DatabaseError> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;

        connection.transaction::<bool, DatabaseError, _>(|| {
            let article_count: i64 = articles::table
                .select(count_star())
                .first(&connection)
                .context(DatabaseErrorKind::Select)?;
            let feed_count: i64 = feeds::table.select(count_star()).first(&connection).context(DatabaseErrorKind::Select)?;
            Ok(feed_count == 0 && article_count == 0)
        })
    }

    pub fn write_tags(&self, tags: &[Tag]) -> Result<(), DatabaseError> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            // delete old tags
            diesel::delete(tags::table)
                .filter(tags::tag_id.ne_all(tags.iter().map(|tag| tag.tag_id.clone())))
                .execute(&connection)
                .context(DatabaseErrorKind::Delete)?;

            // write new tags
            diesel::replace_into(tags::table)
                .values(tags)
                .execute(&*connection)
                .context(DatabaseErrorKind::Insert)?;
            Ok(())
        })
    }

    pub fn insert_tag(&self, tag: &Tag) -> Result<(), DatabaseError> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(tags::table)
            .values(tag)
            .execute(&connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn insert_tags(&self, tags: &[Tag]) -> Result<(), DatabaseError> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(tags::table)
            .values(tags)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn read_tags(&self) -> DatabaseResult<Vec<Tag>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let tags = tags::table.load(&connection).context(DatabaseErrorKind::Select)?;
        Ok(tags)
    }

    pub fn read_tags_for_article(&self, article_id: &ArticleID) -> DatabaseResult<Vec<Tag>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let tag_ids_of_article = taggings::table.select(taggings::tag_id).filter(taggings::article_id.eq(article_id));
        let tags = tags::table
            .filter(tags::tag_id.eq_any(tag_ids_of_article))
            .load(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(tags)
    }

    pub fn drop_tag(&self, tag: &Tag) -> Result<(), DatabaseError> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(tags::table)
            .filter(tags::tag_id.eq(&tag.tag_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn set_tag_read(&self, tags: &[TagID]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::update(articles::table)
            .filter(articles::article_id.eq_any(taggings::table.filter(taggings::tag_id.eq_any(tags)).select(taggings::article_id)))
            .set(articles::unread.eq(Read::Read))
            .execute(&connection)
            .context(DatabaseErrorKind::Update)?;
        Ok(())
    }

    pub fn insert_tagging(&self, tagging: &Tagging) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(taggings::table)
            .values(tagging)
            .execute(&connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn insert_taggings(&self, taggings: &[Tagging]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(taggings::table)
            .values(taggings)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn read_taggings(&self, article: Option<&ArticleID>, tag: Option<&TagID>) -> DatabaseResult<Vec<Tagging>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let mut query = taggings::table.into_boxed();

        if article.is_some() && tag.is_some() {
            warn!("querying tagging by article AND tag might not be very useful");
        }

        if let Some(article) = article {
            query = query.filter(taggings::article_id.eq(article.to_string()));
        }

        if let Some(tag) = tag {
            query = query.filter(taggings::tag_id.eq(tag.to_string()));
        }

        let taggings = query.load(&connection).context(DatabaseErrorKind::Select)?;
        Ok(taggings)
    }

    pub fn drop_tagging(&self, tagging: &Tagging) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(taggings::table)
            .filter(taggings::tag_id.eq(&tagging.tag_id))
            .filter(taggings::article_id.eq(&tagging.article_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_taggings_of_tag(&self, tag: &Tag) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(taggings::table)
            .filter(taggings::tag_id.eq(&tag.tag_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_taggings_of_article(&self, article: &ArticleID) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(taggings::table)
            .filter(taggings::article_id.eq(article))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_taggings_of_articles(&self, articles: &[ArticleID]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(taggings::table)
            .filter(taggings::article_id.eq_any(articles))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn write_categories(&self, categories: &[Category]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            // delete old categories
            diesel::delete(categories::table)
                .filter(categories::category_id.ne_all(categories.iter().map(|category| category.category_id.clone())))
                .execute(&connection)
                .context(DatabaseErrorKind::Delete)?;

            // write new categories
            diesel::replace_into(categories::table)
                .values(categories)
                .execute(&*connection)
                .context(DatabaseErrorKind::Insert)?;

            Ok(())
        })
    }

    pub fn insert_category(&self, category: &Category) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(categories::table)
            .values(category)
            .execute(&connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn insert_categories(&self, categories: &[Category]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(categories::table)
            .values(categories)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn read_categories(&self) -> DatabaseResult<Vec<Category>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let categories = categories::table.load(&connection).context(DatabaseErrorKind::Select)?;
        Ok(categories)
    }

    pub fn drop_category(&self, category: &Category) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(categories::table)
            .filter(categories::category_id.eq(&category.category_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn set_category_read(&self, categories: &[CategoryID]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::update(articles::table)
            .filter(
                articles::feed_id.eq_any(
                    feed_mapping::table
                        .filter(feed_mapping::category_id.eq_any(categories))
                        .select(feed_mapping::feed_id),
                ),
            )
            .set(articles::unread.eq(Read::Read))
            .execute(&connection)
            .context(DatabaseErrorKind::Update)?;
        Ok(())
    }

    pub fn write_feeds(&self, feeds: &[Feed]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            // delete old feeds
            diesel::delete(feeds::table)
                .filter(feeds::feed_id.ne_all(feeds.iter().map(|feed| feed.feed_id.clone())))
                .execute(&connection)
                .context(DatabaseErrorKind::Delete)?;

            // write new feeds
            diesel::replace_into(feeds::table)
                .values(feeds)
                .execute(&*connection)
                .context(DatabaseErrorKind::Insert)?;

            Ok(())
        })
    }

    pub fn insert_feed(&self, feed: &Feed) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(feeds::table)
            .values(feed)
            .execute(&connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn insert_feeds(&self, feeds: &[Feed]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(feeds::table)
            .values(feeds)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn read_feeds(&self) -> DatabaseResult<Vec<Feed>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let feeds = feeds::table.load(&connection).context(DatabaseErrorKind::Select)?;
        Ok(feeds)
    }

    pub fn drop_feed(&self, feed_id: &FeedID) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(feeds::table)
            .filter(feeds::feed_id.eq(feed_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn set_feed_read(&self, feeds: &[FeedID]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::update(articles::table)
            .filter(articles::feed_id.eq_any(feeds))
            .set(articles::unread.eq(Read::Read.to_int()))
            .execute(&connection)
            .context(DatabaseErrorKind::Update)?;
        Ok(())
    }

    pub fn read_favicons(&self) -> DatabaseResult<Vec<FavIcon>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let favicons = fav_icons::table.load(&connection).context(DatabaseErrorKind::Select)?;
        Ok(favicons)
    }

    pub fn read_favicon(&self, feed_id: &FeedID) -> DatabaseResult<FavIcon> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let favicon = fav_icons::table.find(feed_id).first(&connection).context(DatabaseErrorKind::Select)?;
        Ok(favicon)
    }

    pub fn insert_favicon(&self, favicon: &FavIcon) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(fav_icons::table)
            .values(favicon)
            .execute(&connection)
            .map_err(|e| {
                log::error!("{}", e);
                e
            })
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn insert_favicons(&self, favicons: &[FavIcon]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(fav_icons::table)
            .values(favicons)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn drop_favicon(&self, feed_id: &FeedID) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(fav_icons::table)
            .filter(fav_icons::feed_id.eq(feed_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn write_mappings(&self, mappings: &[FeedMapping]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            let old_mappings = self.read_mappings(None, None)?;
            let diff: Vec<&FeedMapping> = old_mappings.iter().filter(|m| !mappings.contains(m)).collect();

            for mapping in diff {
                self.drop_mapping(mapping)?;
            }

            // write new mappings
            diesel::replace_into(feed_mapping::table)
                .values(mappings)
                .execute(&*connection)
                .context(DatabaseErrorKind::Insert)?;

            Ok(())
        })
    }

    pub fn insert_mapping(&self, mapping: &FeedMapping) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(feed_mapping::table)
            .values(mapping)
            .execute(&connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn insert_mappings(&self, mappings: &[FeedMapping]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(feed_mapping::table)
            .values(mappings)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;

        Ok(())
    }

    pub fn read_mappings(&self, feed: Option<&FeedID>, category: Option<&CategoryID>) -> DatabaseResult<Vec<FeedMapping>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let mut query = feed_mapping::table.into_boxed();

        if feed.is_some() && category.is_some() {
            warn!("querying mapping by feed AND category might not be very useful");
        }

        if let Some(feed) = feed {
            query = query.filter(feed_mapping::feed_id.eq(feed.to_string()));
        }

        if let Some(category) = category {
            query = query.filter(feed_mapping::category_id.eq(category.to_string()));
        }

        let mappings = query.load(&connection).context(DatabaseErrorKind::Select)?;
        Ok(mappings)
    }

    pub fn drop_mapping(&self, mapping: &FeedMapping) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(feed_mapping::table)
            .filter(feed_mapping::feed_id.eq(&mapping.feed_id))
            .filter(feed_mapping::category_id.eq(&mapping.category_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_mapping_of_feed(&self, feed_id: &FeedID) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(feed_mapping::table)
            .filter(feed_mapping::feed_id.eq(feed_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_mapping_of_category(&self, category_id: &CategoryID) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(feed_mapping::table)
            .filter(feed_mapping::category_id.eq(category_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn update_article_grabbed_content(&self, article: &FatArticle) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            diesel::update(articles::table)
                .filter(articles::article_id.eq(&article.article_id))
                .set((
                    articles::scraped_content.eq(&article.scraped_content),
                    articles::plain_text.eq(&article.plain_text),
                    articles::html.eq(&article.html),
                    articles::title.eq(&article.title),
                    articles::author.eq(&article.author),
                    articles::summary.eq(&article.summary),
                ))
                .execute(&connection)
                .context(DatabaseErrorKind::Update)?;

            sql_query("INSERT INTO fts_table(fts_table) VALUES('rebuild')")
                .execute(&connection)
                .context(DatabaseErrorKind::Unknown)?;
            Ok(())
        })
    }

    pub fn write_fat_articles(&self, articles: &[FatArticle]) -> DatabaseResult<i64> {
        let before = self.article_count()?;

        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            for article in articles {
                diesel::update(articles::table)
                    .filter(articles::article_id.eq(&article.article_id))
                    .set((
                        articles::timestamp.eq(&article.date),
                        articles::unread.eq(&article.unread),
                        articles::marked.eq(&article.marked),
                    ))
                    .execute(&connection)
                    .context(DatabaseErrorKind::Update)?;
            }

            diesel::insert_or_ignore_into(articles::table)
                .values(articles)
                .execute(&*connection)
                .context(DatabaseErrorKind::Insert)?;

            sql_query("INSERT INTO fts_table(fts_table) VALUES('rebuild')")
                .execute(&connection)
                .context(DatabaseErrorKind::Unknown)?;

            Ok(())
        })?;

        let after = self.article_count()?;
        Ok(after - before)
    }

    pub fn write_articles(&self, articles: &[Article]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            for article in articles {
                diesel::update(articles::table)
                    .filter(articles::article_id.eq(&article.article_id))
                    .set((
                        articles::timestamp.eq(&article.date),
                        articles::unread.eq(&article.unread),
                        articles::marked.eq(&article.marked),
                    ))
                    .execute(&connection)
                    .context(DatabaseErrorKind::Update)?;
            }

            diesel::insert_or_ignore_into(articles::table)
                .values(articles)
                .execute(&*connection)
                .context(DatabaseErrorKind::Insert)?;

            Ok(())
        })
    }

    pub fn read_fat_article(&self, id: &ArticleID) -> DatabaseResult<FatArticle> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let article = articles::table
            .filter(articles::article_id.eq(id))
            .first::<FatArticle>(&connection)
            .context(DatabaseErrorKind::Select)?;

        Ok(article)
    }

    pub fn read_fat_articles(&self, filter: ArticleFilter) -> DatabaseResult<Vec<FatArticle>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let query = prepare_article_query!(filter);

        let articles = query.load::<FatArticle>(&connection).context(DatabaseErrorKind::Select)?;
        Ok(articles)
    }

    pub fn read_article(&self, id: &ArticleID) -> DatabaseResult<Article> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let article = articles::table
            .filter(articles::article_id.eq(id))
            .select((
                articles::article_id,
                articles::title,
                articles::author,
                articles::feed_id,
                articles::url,
                articles::timestamp,
                articles::summary,
                articles::direction,
                articles::unread,
                articles::marked,
            ))
            .first::<Article>(&connection)
            .context(DatabaseErrorKind::Select)?;

        Ok(article)
    }

    pub fn read_articles(&self, filter: ArticleFilter) -> DatabaseResult<Vec<Article>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let query = prepare_article_query!(filter);

        let articles: Vec<Article> = query
            .select((
                articles::article_id,
                articles::title,
                articles::author,
                articles::feed_id,
                articles::url,
                articles::timestamp,
                articles::summary,
                articles::direction,
                articles::unread,
                articles::marked,
            ))
            .load::<Article>(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(articles)
    }

    pub fn article_exists(&self, article_id: &ArticleID) -> DatabaseResult<bool> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let count: i64 = articles::table
            .filter(articles::article_id.eq(article_id))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count != 0)
    }

    pub fn drop_article(&self, article: &Article) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(articles::table)
            .filter(articles::article_id.eq(&article.article_id))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_articles(&self, articles: &[ArticleID]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(articles::table)
            .filter(articles::article_id.eq_any(articles))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_articles_older_than(&self, older_than: DateTime<Utc>) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(articles::table)
            .filter(articles::timestamp.lt(older_than.naive_utc()))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn set_article_read(&self, articles: &[ArticleID], read: Read) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::update(articles::table)
            .filter(articles::article_id.eq_any(articles))
            .set(articles::unread.eq(read.to_int()))
            .execute(&connection)
            .context(DatabaseErrorKind::Update)?;
        Ok(())
    }

    pub fn set_all_read(&self) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::update(articles::table)
            .set(articles::unread.eq(Read::Read.to_int()))
            .execute(&connection)
            .context(DatabaseErrorKind::Update)?;
        Ok(())
    }

    pub fn set_article_marked(&self, articles: &[ArticleID], marked: Marked) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::update(articles::table)
            .filter(articles::article_id.eq_any(articles))
            .set(articles::marked.eq(marked.to_int()))
            .execute(&connection)
            .context(DatabaseErrorKind::Update)?;
        Ok(())
    }

    pub fn read_enclosures(&self, article: &ArticleID) -> DatabaseResult<Vec<Enclosure>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let enclosures = enclosures::table
            .filter(enclosures::article_id.eq(article))
            .load(&connection)
            .context(DatabaseErrorKind::Select)?;

        Ok(enclosures)
    }

    pub fn write_enclosures(&self, enclosures: &[Enclosure]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::replace_into(enclosures::table)
            .values(enclosures)
            .execute(&*connection)
            .context(DatabaseErrorKind::Insert)?;
        Ok(())
    }

    pub fn drop_enclosures_of_article(&self, article: &ArticleID) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(enclosures::table)
            .filter(enclosures::article_id.eq(&article))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn drop_enclosures_of_articles(&self, articles: &[ArticleID]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        diesel::delete(enclosures::table)
            .filter(enclosures::article_id.eq_any(articles))
            .execute(&connection)
            .context(DatabaseErrorKind::Delete)?;
        Ok(())
    }

    pub fn insert_headlines(&self, headlines: &[Headline]) -> DatabaseResult<()> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<(), DatabaseError, _>(|| {
            for headline in headlines {
                diesel::update(articles::table)
                    .filter(articles::article_id.eq(&headline.article_id))
                    .set((
                        articles::unread.eq(headline.unread.to_int()),
                        articles::marked.eq(headline.marked.to_int()),
                    ))
                    .execute(&connection)
                    .context(DatabaseErrorKind::Update)?;
            }
            Ok(())
        })
    }

    pub fn article_count(&self) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let count = articles::table
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn unread_count_feed(&self, feed_id: &FeedID) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let count = articles::table
            .filter(articles::unread.eq(Read::Unread))
            .filter(articles::feed_id.eq(feed_id.to_string()))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn unread_count_feed_map(&self) -> DatabaseResult<Vec<FeedCount>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let unread_map: Vec<FeedCount> = diesel::sql_query("SELECT feed_id, count(*) as count FROM articles where unread=1 GROUP BY feed_id")
            .load(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(unread_map)
    }

    pub fn marked_count_feed_map(&self) -> DatabaseResult<Vec<FeedCount>> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let marked_map: Vec<FeedCount> = diesel::sql_query("SELECT feed_id, count(*) as count FROM articles where marked=0 GROUP BY feed_id")
            .load(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(marked_map)
    }

    pub fn marked_count_feed(&self, feed_id: &FeedID) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let count = articles::table
            .filter(articles::marked.eq(Marked::Marked))
            .filter(articles::feed_id.eq(feed_id.to_string()))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn unread_count_category(&self, category_id: &CategoryID) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let feed_ids_in_category = feed_mapping::table
            .select(feed_mapping::feed_id)
            .filter(feed_mapping::category_id.eq(category_id));
        let count = articles::table
            .filter(articles::unread.eq(Read::Unread))
            .filter(articles::feed_id.eq_any(feed_ids_in_category))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn marked_count_category(&self, category_id: &CategoryID) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let feed_ids_in_category = feed_mapping::table
            .select(feed_mapping::feed_id)
            .filter(feed_mapping::category_id.eq(category_id));
        let count = articles::table
            .filter(articles::marked.eq(Marked::Marked))
            .filter(articles::feed_id.eq_any(feed_ids_in_category))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn unread_count_tag(&self, tag_id: &TagID) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let tagged_articles = taggings::table.select(taggings::article_id).filter(taggings::tag_id.eq(tag_id));
        let count = articles::table
            .filter(articles::unread.eq(Read::Unread))
            .filter(articles::article_id.eq_any(tagged_articles))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn marked_count_tag(&self, tag_id: &TagID) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let tagged_articles = taggings::table.select(taggings::article_id).filter(taggings::tag_id.eq(tag_id));
        let count = articles::table
            .filter(articles::marked.eq(Marked::Marked))
            .filter(articles::article_id.eq_any(tagged_articles))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn unread_count_all(&self) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let count = articles::table
            .filter(articles::unread.eq(Read::Unread))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn marked_count_all(&self) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        let count = articles::table
            .filter(articles::marked.eq(Marked::Marked))
            .select(count_star())
            .first(&connection)
            .context(DatabaseErrorKind::Select)?;
        Ok(count)
    }

    pub fn write_sync_result(&self, result: SyncResult) -> DatabaseResult<i64> {
        let connection = self.connection_pool.get().context(DatabaseErrorKind::Unknown)?;
        connection.transaction::<i64, DatabaseError, _>(|| {
            let mut new_article_count = 0;

            if let Some(categories) = result.categories {
                self.write_categories(&categories)?;
            }

            if let Some(feeds) = result.feeds {
                self.write_feeds(&feeds)?;
            }

            if let Some(mappings) = result.mappings {
                self.write_mappings(&mappings)?;
            }

            if let Some(tags) = result.tags {
                self.write_tags(&tags)?;
            }

            if let Some(articles) = result.articles {
                new_article_count = self.write_fat_articles(&articles)?;
            }

            if let Some(headlines) = result.headlines {
                self.insert_headlines(&headlines)?;
            }

            if let Some(taggings) = result.taggings {
                self.insert_taggings(&taggings)?;
            }

            if let Some(enclosures) = result.enclosures {
                self.write_enclosures(&enclosures)?;
            }

            Ok(new_article_count)
        })
    }
}

#[cfg(test)]
mod tests {
    use crate::database::Database;
    use crate::models::{
        ArticleFilter, ArticleID, Category, CategoryID, CategoryType, Direction, FatArticle, FavIcon, Feed, FeedID, FeedMapping, Marked, Read,
        SyncResult, Tag, TagID, Tagging, Url, NEWSFLASH_TOPLEVEL,
    };
    use chrono::{Duration, Utc};
    use tempfile::{tempdir, TempDir};

    fn setup_db(database_url: &TempDir) -> Database {
        Database::new_with_file_name(database_url.path(), "test.sqlite").unwrap()
    }

    fn get_tags() -> Vec<Tag> {
        vec![
            Tag {
                tag_id: TagID::new("tag_1"),
                label: String::from("tag_1_label"),
                color: Some("#FF00FF".to_string()),
                sort_index: None,
            },
            Tag {
                tag_id: TagID::new("tag_2"),
                label: String::from("tag_2_label"),
                color: Some("#FF00FF".to_string()),
                sort_index: None,
            },
        ]
    }

    fn get_taggings() -> Vec<Tagging> {
        vec![
            Tagging {
                article_id: ArticleID::new("article_1"),
                tag_id: TagID::new("tag_1"),
            },
            Tagging {
                article_id: ArticleID::new("article_2"),
                tag_id: TagID::new("tag_2"),
            },
            // Tag article 1 with two tags
            Tagging {
                article_id: ArticleID::new("article_1"),
                tag_id: TagID::new("tag_2"),
            },
        ]
    }

    fn get_categories() -> Vec<Category> {
        vec![
            Category {
                category_id: CategoryID::new("category_1"),
                label: String::from("category_1_label"),
                sort_index: Some(1),
                parent_id: NEWSFLASH_TOPLEVEL.clone(),
                category_type: CategoryType::Default,
            },
            Category {
                category_id: CategoryID::new("category_2"),
                label: String::from("category_2_label"),
                sort_index: Some(2),
                parent_id: NEWSFLASH_TOPLEVEL.clone(),
                category_type: CategoryType::Default,
            },
        ]
    }

    fn get_feeds() -> Vec<Feed> {
        vec![
            Feed {
                feed_id: FeedID::new("feed_1"),
                label: String::from("feed_1_label"),
                website: Some(Url::parse("http://feed-1.com").unwrap()),
                feed_url: Some(Url::parse("http://feed-1.com/rss").unwrap()),
                icon_url: Some(Url::parse("http://feed-1.com/fav.ico").unwrap()),
                sort_index: Some(1),
            },
            Feed {
                feed_id: FeedID::new("feed_2"),
                label: String::from("feed_2_label"),
                website: Some(Url::parse("http://feed-2.com").unwrap()),
                feed_url: Some(Url::parse("http://feed-2.com/rss").unwrap()),
                icon_url: Some(Url::parse("http://feed-2.com/fav.ico").unwrap()),
                sort_index: Some(2),
            },
        ]
    }

    fn get_mappings() -> Vec<FeedMapping> {
        vec![
            FeedMapping {
                feed_id: FeedID::new("feed_1"),
                category_id: CategoryID::new("category_1"),
            },
            FeedMapping {
                feed_id: FeedID::new("feed_2"),
                category_id: CategoryID::new("category_2"),
            },
            // assign feed 1 to two categories
            FeedMapping {
                feed_id: FeedID::new("feed_1"),
                category_id: CategoryID::new("category_2"),
            },
        ]
    }

    fn get_articles() -> Vec<FatArticle> {
        vec![
            FatArticle {
                article_id: ArticleID::new("article_1"),
                title: Some(String::from("article_1_title")),
                author: Some(String::from("article_1_author")),
                feed_id: FeedID::new("feed_1"),
                url: None,
                date: Utc::now().naive_utc(),
                html: Some("test html".to_owned()),
                summary: None,
                direction: Some(Direction::LeftToRight),
                unread: Read::Unread,
                marked: Marked::Unmarked,
                scraped_content: None,
                plain_text: None,
            },
            FatArticle {
                article_id: ArticleID::new("article_2"),
                title: Some(String::from("article_2_title")),
                author: Some(String::from("article_2_author")),
                feed_id: FeedID::new("feed_2"),
                url: None,
                date: Utc::now().naive_utc(),
                html: None,
                summary: None,
                direction: Some(Direction::LeftToRight),
                unread: Read::Unread,
                marked: Marked::Unmarked,
                scraped_content: None,
                plain_text: None,
            },
        ]
    }

    fn get_favicons() -> Vec<FavIcon> {
        vec![
            FavIcon {
                feed_id: FeedID::new("feed_1"),
                expires: Utc::now().naive_utc() + Duration::days(10),
                format: Some(String::from("image/png")),
                etag: None,
                source_url: None,
                data: None,
            },
            FavIcon {
                feed_id: FeedID::new("feed_2"),
                expires: Utc::now().naive_utc() + Duration::days(10),
                format: Some(String::from("image/png")),
                etag: None,
                source_url: None,
                data: None,
            },
        ]
    }

    fn setup_full_db(db: &Database) {
        let feeds = get_feeds();
        db.write_feeds(&feeds).unwrap();
        let articles = get_articles();
        let articles_written = db.write_fat_articles(&articles).unwrap();
        assert_eq!(articles_written, 2);
        let tags = get_tags();
        db.write_tags(&tags).unwrap();
        let taggings = get_taggings();
        db.insert_taggings(&taggings).unwrap();
        let favicons = get_favicons();
        db.insert_favicons(&favicons).unwrap();
    }

    #[test]
    fn write_read_tags() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        let tags = get_tags();
        db.write_tags(&tags).unwrap();
        let read_tags = db.read_tags().unwrap();

        assert_eq!(tags, read_tags);
    }

    #[test]
    fn drop_tag() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);
        let tags = get_tags();
        db.drop_tag(&tags[0]).unwrap();
        let read_tags = db.read_tags().unwrap();
        assert_eq!(&tags[1..], read_tags.as_slice());
    }

    #[test]
    fn update_tags() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);
        // delete tag_1 and add tag_3
        let tags = vec![
            Tag {
                tag_id: TagID::new("tag_3"),
                label: String::from("tag_3_label"),
                color: Some("#FF00FF".to_string()),
                sort_index: None,
            },
            Tag {
                tag_id: TagID::new("tag_2"),
                label: String::from("tag_2_label"),
                color: Some("#FF00FF".to_string()),
                sort_index: None,
            },
        ];
        db.write_tags(&tags).unwrap();
        let read_tags = db.read_tags().unwrap();
        assert_eq!(tags, read_tags);
    }

    #[test]
    fn write_read_taggings() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        let feeds = get_feeds();
        db.write_feeds(&feeds).unwrap();
        let articles = get_articles();
        db.write_fat_articles(&articles).unwrap();
        let tags = get_tags();
        db.write_tags(&tags).unwrap();
        let taggings = get_taggings();
        db.insert_taggings(&taggings).unwrap();
        let read_taggings = db.read_taggings(None, None).unwrap();

        assert_eq!(taggings, read_taggings);
    }

    #[test]
    fn write_read_categories() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        let categories = get_categories();
        db.write_categories(&categories).unwrap();
        let read_categories = db.read_categories().unwrap();

        assert_eq!(categories, read_categories);
    }

    #[test]
    fn update_categories() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);
        // delete category_1 and add category_3
        let categories = vec![
            Category {
                category_id: CategoryID::new("category_3"),
                label: String::from("category_3_label"),
                sort_index: Some(3),
                parent_id: NEWSFLASH_TOPLEVEL.clone(),
                category_type: CategoryType::Default,
            },
            Category {
                category_id: CategoryID::new("category_2"),
                label: String::from("category_2_label"),
                sort_index: Some(2),
                parent_id: NEWSFLASH_TOPLEVEL.clone(),
                category_type: CategoryType::Default,
            },
        ];
        db.write_categories(&categories).unwrap();
        let read_categories = db.read_categories().unwrap();
        assert_eq!(categories, read_categories);
    }

    #[test]
    fn write_read_feeds() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        let feeds = get_feeds();
        db.write_feeds(&feeds).unwrap();
        let read_feeds = db.read_feeds().unwrap();

        assert_eq!(feeds, read_feeds);
    }

    #[test]
    fn update_feeds() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);
        // delete feed_1 and add feed_3
        let feeds = vec![
            Feed {
                feed_id: FeedID::new("feed_3"),
                label: String::from("feed_3_label"),
                website: Some(Url::parse("http://feed-3.com").unwrap()),
                feed_url: Some(Url::parse("http://feed-3.com/rss").unwrap()),
                icon_url: Some(Url::parse("http://feed-3.com/fav.ico").unwrap()),
                sort_index: Some(1),
            },
            Feed {
                feed_id: FeedID::new("feed_2"),
                label: String::from("feed_2_label"),
                website: Some(Url::parse("http://feed-2.com").unwrap()),
                feed_url: Some(Url::parse("http://feed-2.com/rss").unwrap()),
                icon_url: Some(Url::parse("http://feed-2.com/fav.ico").unwrap()),
                sort_index: Some(2),
            },
        ];
        db.write_feeds(&feeds).unwrap();
        let read_feeds = db.read_feeds().unwrap();
        assert_eq!(feeds, read_feeds);
    }

    #[test]
    fn write_read_mappings() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        let categories = get_categories();
        db.write_categories(&categories).unwrap();
        let feeds = get_feeds();
        db.write_feeds(&feeds).unwrap();
        let mappings = get_mappings();
        db.write_mappings(&mappings).unwrap();
        let read_mappings = db.read_mappings(None, None).unwrap();

        assert_eq!(mappings, read_mappings);
    }

    #[test]
    fn write_read_articles() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        let feeds = get_feeds();
        db.write_feeds(&feeds).unwrap();
        let articles = get_articles();
        db.write_fat_articles(&articles).unwrap();
        let read_articles = db
            .read_fat_articles(ArticleFilter {
                limit: None,
                offset: None,
                order: None,
                unread: None,
                marked: None,
                feed: None,
                feed_blacklist: None,
                category: None,
                category_blacklist: None,
                tag: None,
                ids: None,
                newer_than: None,
                older_than: None,
                search_term: None,
            })
            .unwrap();

        assert_eq!(articles, read_articles);
    }

    #[test]
    fn favicons_after_sync() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);
        let favicons = get_favicons();
        db.insert_favicons(&favicons).unwrap();
        let sync_data = SyncResult {
            feeds: Some(get_feeds()),
            categories: Some(get_categories()),
            mappings: Some(get_mappings()),
            tags: Some(get_tags()),
            headlines: None,
            articles: Some(get_articles()),
            enclosures: None,
            taggings: Some(get_taggings()),
        };
        db.write_sync_result(sync_data).unwrap();
        let read_favicons = db.read_favicons().unwrap();
        assert_eq!(read_favicons.len(), favicons.len());
    }

    #[test]
    fn delete_triggers() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);
        let tags = get_tags();
        db.drop_tag(&tags.first().unwrap()).unwrap();
        let taggings = db.read_taggings(None, None).unwrap();

        assert_eq!(2, taggings.len());
    }

    #[test]
    fn unread_count_map() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);

        let count_map = db.unread_count_feed_map().unwrap();

        assert_eq!(count_map.get(0).unwrap().count, 1);
        assert_eq!(count_map.get(1).unwrap().count, 1);
    }

    #[test]
    fn fat_articles() {
        let database_url = tempdir().unwrap();
        let db = setup_db(&database_url);
        setup_full_db(&db);

        let fat_articles = get_articles();
        let fat_article = fat_articles.get(0).unwrap();

        let slim_article = db
            .read_articles(ArticleFilter {
                limit: None,
                offset: None,
                order: None,
                unread: None,
                marked: None,
                feed: None,
                feed_blacklist: None,
                category: None,
                category_blacklist: None,
                tag: None,
                ids: Some(&vec![fat_article.article_id.clone()]),
                newer_than: None,
                older_than: None,
                search_term: None,
            })
            .unwrap();

        db.write_articles(&slim_article).unwrap();

        let read_fat_articles = db
            .read_fat_articles(ArticleFilter {
                limit: None,
                offset: None,
                order: None,
                unread: None,
                marked: None,
                feed: None,
                feed_blacklist: None,
                category: None,
                category_blacklist: None,
                tag: None,
                ids: Some(&vec![fat_article.article_id.clone()]),
                newer_than: None,
                older_than: None,
                search_term: None,
            })
            .unwrap();
        let read_fat_article = read_fat_articles.first().unwrap();

        assert_eq!(fat_article.html, read_fat_article.html);
    }
}
