func (db *PodcastDBReader) SelectAll(page int64) (*models.PodcastList, error) {
	var numRows int64

	q := "SELECT COUNT(id) FROM podcasts"

	if err := db.QueryRowx(q).Scan(&numRows); err != nil {
		return nil, dbErr(err, q)
	}

	result := &models.PodcastList{
		Page: models.NewPage(page, numRows),
	}

	q = `SELECT p.id, p.title, p.enclosure_url, p.description,
    p.channel_id, c.title AS name, c.image, p.pub_date, p.source
FROM podcasts p
JOIN channels c ON c.id = p.channel_id
ORDER BY p.pub_date DESC
OFFSET $1 LIMIT $2`

	err := sqlx.Select(
		db,
		&result.Podcasts,
		q,
		result.Page.Offset,
		result.Page.PageSize)
	return result, dbErr(err, q)
}

func GetGalleries(db sqlx.Ext) ([]Gallery, error) {
	q := `SELECT * FROM galleries WHERE displayed`

	res := []Gallery{}
	err := sqlx.Select(db, &res, q)
	return res, tracederror.New(err)
}

func GetHiddenGalleries(db sqlx.Ext) ([]Gallery, error) {
	q := `SELECT * FROM galleries`

	res := []Gallery{}
	err := sqlx.Select(db, &res, q)
	return res, tracederror.New(err)
}

// GetAllFeeds returns all feeds from the database.
func (d *Handle) GetAllFeeds() ([]*FeedInfo, error) {
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()
	feeds := []*FeedInfo{}
	err := sqlx.Select(d.queryer, &feeds, "SELECT * FROM feed_info")
	return feeds, err
}

func (db *PodcastDBReader) SelectBookmarked(userID, page int64) (*models.PodcastList, error) {

	q := `SELECT COUNT(DISTINCT(p.id)) FROM podcasts p
JOIN bookmarks b ON b.podcast_id = p.id
WHERE b.user_id=$1`

	var numRows int64

	if err := db.QueryRowx(q, userID).Scan(&numRows); err != nil {
		return nil, dbErr(err, q)
	}

	result := &models.PodcastList{
		Page: models.NewPage(page, numRows),
	}

	q = `SELECT p.id, p.title, p.enclosure_url, p.description,
    p.channel_id, c.title AS name, c.image, p.pub_date, p.source
FROM podcasts p
JOIN channels c ON c.id = p.channel_id
JOIN bookmarks b ON b.podcast_id = p.id
WHERE b.user_id=$1
GROUP BY p.id, p.title, c.title, c.image, b.id
ORDER BY b.id DESC
OFFSET $2 LIMIT $3`

	err := sqlx.Select(
		db,
		&result.Podcasts,
		q,
		userID,
		result.Page.Offset,
		result.Page.PageSize)
	return result, dbErr(err, q)
}

func (db *PodcastDBReader) SelectByChannelID(channelID, page int64) (*models.PodcastList, error) {

	q := "SELECT COUNT(id) FROM podcasts WHERE channel_id=$1"

	var numRows int64

	if err := db.QueryRowx(q, channelID).Scan(&numRows); err != nil {
		return nil, dbErr(err, q)
	}

	result := &models.PodcastList{
		Page: models.NewPage(page, numRows),
	}

	q = `SELECT id, title, enclosure_url, description, pub_date, source
FROM podcasts
WHERE channel_id=$1
ORDER BY pub_date DESC
OFFSET $2 LIMIT $3`

	err := sqlx.Select(
		db,
		&result.Podcasts,
		q,
		channelID,
		result.Page.Offset,
		result.Page.PageSize)
	return result, dbErr(err, q)
}

// GetAllUsers returns all Users from the database.
func (d *Handle) GetAllUsers() ([]User, error) {
	var all []User
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()

	err := sqlx.Select(d.queryer, &all, "SELECT * FROM user")
	return all, err
}

// GetStaleFeeds returns all feeds that haven't gotten new content in more
// than 14 days.
func (d *Handle) GetStaleFeeds() ([]*FeedInfo, error) {
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()

	var res []*FeedInfo
	err := sqlx.Select(d.queryer, &res, `SELECT feed_info.id, feed_info.name, feed_info.url, feed_info.last_poll_error FROM (SELECT feed_info_id, MAX(added_on) as MaxTime FROM feed_item GROUP BY feed_info_id) r, feed_info INNER JOIN feed_item f ON f.feed_info_id = r.feed_info_id AND f.added_on = r.MaxTime AND r.MaxTime < datetime('now','-14 days') AND f.feed_info_id = feed_info.id group by f.feed_info_id;`)
	return res, err
}

// GetFeedsWithErrors returns all feeds that had an error on their last
// check.
func (d *Handle) GetFeedsWithErrors() ([]*FeedInfo, error) {
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()

	var feeds []*FeedInfo
	err := sqlx.Select(d.queryer, &feeds, "SELECT * from feed_info WHERE last_poll_error IS NOT NULL and last_poll_error <> ''")
	return feeds, err
}

func (balancer *Balancer) Select(result interface{}, query string, args ...interface{}) error {
	q, e := balancer.db()
	if e != nil {
		return e
	}

	return sqlx.Select(q, result, query, args...)
}

func (db *PodcastDBReader) Search(query string) ([]models.Podcast, error) {
	q := `SELECT p.id, p.title, p.enclosure_url, p.description,
    p.channel_id, p.pub_date, c.title AS name, c.image, p.source
FROM podcasts p, plainto_tsquery($1) as q, channels c
WHERE (p.tsv @@ q) AND p.channel_id = c.id
ORDER BY ts_rank_cd(p.tsv, plainto_tsquery($1)) DESC LIMIT $2`
	var podcasts []models.Podcast
	return podcasts, dbErr(sqlx.Select(db, &podcasts, q, query, maxSearchRows), q)
}

func GetCategories(db sqlx.Ext) ([]BlogCategory, error) {
	q := `
		SELECT * FROM blog_categories ORDER BY id;
	`

	res := []BlogCategory{}
	err := sqlx.Select(db, &res, q)
	return res, tracederror.New(err)
}

func (db *ChannelDBReader) Search(query string) ([]models.Channel, error) {

	q := `SELECT c.id, c.title, c.description, c.url, c.image, c.website
FROM channels c, plainto_tsquery($1) as q
WHERE (c.tsv @@ q)
ORDER BY ts_rank_cd(c.tsv, plainto_tsquery($1)) DESC LIMIT 20`
	var channels []models.Channel
	return channels, dbErr(sqlx.Select(db, &channels, q, query), q)
}

// GetFeedUsers returns all the users subscribed to a given feedURL
func (d *Handle) GetFeedUsers(feedURL string) ([]User, error) {
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()
	var all []User
	err := sqlx.Select(d.queryer, &all, `SELECT user.* FROM user
	INNER JOIN user_feeds ON user.id = user_feeds.user_id
	INNER JOIN feed_info ON user_feeds.feed_info_id = feed_info.id
	WHERE feed_info.url = ?`, feedURL)
	return all, err
}

// GetAllFeedsWithUsers returns all feeds from the database that have
// subscribers
func (d *Handle) GetAllFeedsWithUsers() ([]*FeedInfo, error) {
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()

	var feeds []*FeedInfo
	err := sqlx.Select(d.queryer, &feeds, `SELECT feed_info.* FROM feed_info
	LEFT JOIN user_feeds ON feed_info.id = user_feeds.feed_info_id
	GROUP BY user_feeds.feed_info_id
	HAVING COUNT(user_feeds.feed_info_id) > 0`)
	return feeds, err
}

func IsValidCategory(db sqlx.Ext, category string) (bool, error) {
	q := `SELECT * FROM blog_categories WHERE category = $1`

	res := []BlogCategory{}
	err := sqlx.Select(db, &res, q, category)

	if err != nil {
		return false, tracederror.New(err)
	}

	return len(res) == 1, nil
}

func (db *ChannelDBReader) SelectSubscribed(userID int64) ([]models.Channel, error) {

	q := `SELECT c.id, c.title, c.description, c.image, c.url, c.website
FROM channels c
JOIN subscriptions s ON s.channel_id = c.id
WHERE s.user_id=$1 AND title IS NOT NULL AND title != ''
GROUP BY c.id
ORDER BY title
`
	var channels []models.Channel
	return channels, dbErr(sqlx.Select(db, &channels, q, userID), q)
}

func (db *PodcastDBReader) SearchBookmarked(query string, userID int64) ([]models.Podcast, error) {
	q := `SELECT p.id, p.title, p.enclosure_url, p.description,
    p.channel_id, p.pub_date, c.title AS name, c.image, p.source
FROM podcasts p, plainto_tsquery($2) as q, channels c, bookmarks b
WHERE (p.tsv @@ q OR c.tsv @@ q) 
    AND p.channel_id = c.id 
    AND b.podcast_id = p.id 
    AND b.user_id=$1
ORDER BY ts_rank_cd(p.tsv, plainto_tsquery($2)) DESC LIMIT $3`
	var podcasts []models.Podcast
	return podcasts, dbErr(sqlx.Select(db, &podcasts, q, userID, query, maxSearchRows), q)

}

// GetFeedsByName returns all feeds that contain the given string in their
// name.
func (d *Handle) GetFeedsByName(name string) ([]*FeedInfo, error) {
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()
	var feeds []*FeedInfo

	err := sqlx.Select(d.queryer, &feeds, `SELECT feed_info.* FROM feed_info
	INNER JOIN user_feeds ON feed_info.id = user_feeds.feed_info_id
	INNER JOIN user ON user_feeds.user_id = user.id
	WHERE feed_info.name LIKE ?
	ORDER BY feed_info.name`, fmt.Sprintf("%%%s%%", name))

	return feeds, err
}

// GetMostRecentGUIDsForFeed retrieves the most recent GUIDs for a given feed
// up to max.  GUIDs are returned ordered with the most recent first.
func (d *Handle) GetMostRecentGUIDsForFeed(feedID int64, max int) ([]string, error) {
	var items []FeedItem
	d.syncMutex.Lock()
	defer d.syncMutex.Unlock()

	err := sqlx.Select(d.queryer, &items, "SELECT guid FROM feed_item WHERE feed_info_id = ? ORDER BY added_on DESC LIMIT ?", feedID, max)
	d.logger.Infof("Got last %d guids for feed_id: %d.", len(items), feedID)
	knownGuids := make([]string, len(items))
	for i, v := range items {
		knownGuids[i] = v.GUID
	}
	return knownGuids, err
}