func AddPost(requesterID string, post *types.Post) httperr.Error {
	jsonFields, err := json.Marshal(post.Fields)
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	err = db.Exec("INSERT INTO posts "+
		"(p_postid, p_userid_creator, p_channelname, p_title, p_thumbnail, p_time, p_location, p_fields) "+
		"VALUES (?, ?, ?, ?, ?, ?, ST_MakePoint(?,?), ?)",
		post.ID, post.CreatorID, post.Channel, post.Title, post.Thumbnail, post.Time,
		post.Location.Longitude, post.Location.Latitude, jsonFields).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

func AddComment(requesterID string, comment *types.Comment) httperr.Error {
	err := db.Create(comment).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

func RemoveComment(requesterID, commentID string) httperr.Error {
	err := db.Where("cmd_commentid = ?", commentID).Delete(&types.Comment{}).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

func AddChannel(channel *types.Channel) httperr.Error {
	err := db.Create(channel).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

// AddUser adds the given User to the database, or returns an error if the
// insertion was unsuccessful.
func AddUser(user *types.User) httperr.Error {
	err := db.Create(user).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

func GetComments(requesterID, postID string) ([]*types.Comment, httperr.Error) {
	var comments []*types.Comment
	err := db.Where("cmt_postid = ?", postID).Order("cmt_time").Find(&comments).Error
	if err != nil {
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return comments, nil
}

func IsCommentCreator(requesterID, userID, commentID string) (bool, httperr.Error) {
	var comment types.Comment
	err := db.Where("cmt_commentid = ?", commentID).First(&comment).Error
	if err == gorm.ErrRecordNotFound {
		return false, nil
	} else if err != nil {
		return false, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return true, nil
}

// GetComment returns a comment with the given ID, or nil if it does not exist.
// An error is returned if some issue occurred with the database.
func GetComment(requesterID, commentID string) (*types.Comment, httperr.Error) {
	var comment types.Comment
	err := db.Where("cmt_commentid = ?", commentID).First(&comment).Error
	if err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, nil
		}
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return &comment, nil
}

// GetUserByID returns the user with the given ID if one exists, nil otherwise.
// Returns an error if some error occurs within the database.
func GetUserByID(userID string) (*types.User, httperr.Error) {
	var user types.User
	err := db.Where("u_userid = ?", userID).First(&user).Error
	if err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, nil
		}
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return &user, nil
}

func GetChannels(requesterID string) ([]*types.PersonalizedChannelInfo, httperr.Error) {
	var channels []*types.PersonalizedChannelInfo
	err := db.Table("channels").
		Joins("LEFT JOIN user_subscriptions ON (ch_channelname = us_channelname AND us_userid = ?)", requesterID).
		Joins("LEFT JOIN users ON (u_userid = ch_userid_creator)").
		Select("*, (us_channelname IS NOT NULL) AS subscribed").
		Order("ch_channelname").Find(&channels).Error
	if err != nil {
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return channels, nil
}

func GetPost(requesterID, postID string) (*types.PersonalizedPost, httperr.Error) {
	var post types.PersonalizedPost
	err := db.Table("posts").
		Where("p_postid = ?", postID).
		Joins("LEFT JOIN user_favorites ON (p_postid = uf_postid AND uf_userid = ?)", requesterID).
		Joins("LEFT JOIN users ON (u_userid = p_userid_creator)").
		Select("*, (uf_postid IS NOT NULL) AS favorited, ST_AsText(p_location) AS location").
		First(&post).Error
	if err != nil {
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return &post, nil
}

// RemoveFavorite removes the given post from the user with ID userID's set of
// favorites. This transaction is executed under the permission level of the
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func RemoveFavorite(requesterID, userID, postID string) httperr.Error {
	permission, httpErr := CanModifyFavorites(requesterID, userID)
	if httpErr != nil {
		return httpErr
	} else if !permission {
		return ErrInsufficientPermission
	}
	err := db.Exec("DELETE FROM user_favorites WHERE uf_userid = ? AND uf_postid = ?", requesterID, postID).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

// AddFavorite adds the given post to the set of favorites for the user with ID
// userID. This transaction is executed under the permission level of the
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func AddFavorite(requesterID, userID, postID string) httperr.Error {
	permission, httpErr := CanModifyFavorites(requesterID, userID)
	if httpErr != nil {
		return httpErr
	} else if !permission {
		return ErrInsufficientPermission
	}
	err := db.Exec("INSERT INTO user_favorites (uf_userid, uf_postid) VALUES (?, ?)", requesterID, postID).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

func RemoveSubscription(requesterID, userID, channelname string) httperr.Error {
	permission, httpErr := CanModifySubscriptions(requesterID, userID)
	if httpErr != nil {
		return httpErr
	} else if !permission {
		return ErrInsufficientPermission
	}
	err := db.Exec("DELETE FROM user_subscriptions WHERE us_userid = ? AND us_channelname = ?", requesterID, channelname).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

// RemoveBan removes the user with ID userID from the ban list for the given
// channel. This transaction is executed under the permission level of the given
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func RemoveBan(requesterID, userID, channelname string) error {
	permission, httpErr := CanModifyBans(requesterID, channelname)
	if httpErr != nil {
		return httpErr
	} else if !permission {
		return ErrInsufficientPermission
	}
	err := db.Exec("DELETE FROM channel_bans WHERE chb_userid = ? and chb_channelname = ?", userID, channelname).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

func AddSubscription(requesterID, userID, channelname string) httperr.Error {
	permission, httpErr := CanModifySubscriptions(requesterID, userID)
	if httpErr != nil {
		return httpErr
	} else if !permission {
		return ErrInsufficientPermission
	}
	err := db.Exec("INSERT INTO user_subscriptions (us_userid, us_channelname) VALUES (?, ?)", requesterID, channelname).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

// AddBan adds the user with ID userID to the ban list for the given channel.
// This transaction is executed under the permission level of the given
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func AddBan(requesterID, userID, channelname string) httperr.Error {
	permission, httpErr := CanModifyBans(requesterID, channelname)
	if httpErr != nil {
		return httpErr
	} else if !permission {
		return ErrInsufficientPermission
	}
	err := db.Exec("INSERT INTO channel_bans (chb_userid, chb_channelname) VALUES (?, ?);", userID, channelname).Error
	if err != nil {
		return httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return nil
}

// GetBans returns the list of users which are banned from the given channel.
// This transaction is executed under the permission level of the given
// requester. Returns an error if the requester does not have sufficient
// permission, or if some other error occurs within the database.
func GetBans(requesterID, channelname string) ([]string, httperr.Error) {
	permission, httpErr := CanViewBans(requesterID, channelname)
	if httpErr != nil {
		return nil, httpErr
	} else if !permission {
		return nil, ErrInsufficientPermission
	}
	var bans []string
	err := db.Table("channel_bans").Select("chb_userid").Where("chb_channelname = ?", channelname).Scan(&bans).Error
	if err != nil {
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return bans, nil
}

func GetChannel(requesterID, channelname string) (*types.PersonalizedChannel, httperr.Error) {
	// TODO: Account for requester permission
	var channel types.PersonalizedChannel
	err := db.Table("channels").
		Joins("LEFT JOIN user_subscriptions ON (ch_channelname = us_channelname AND us_userid = ?)", requesterID).
		Joins("LEFT JOIN users ON (u_userid = ch_userid_creator)").
		Select("*, (us_channelname IS NOT NULL) AS subscribed").
		Where("ch_channelname = ?", channelname).
		First(&channel).Error
	if err != nil {
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return &channel, nil
}

func GetPosts(requesterID string, postQueryParams *PostQueryParams) ([]*types.PersonalizedPostInfo, httperr.Error) {
	var posts []*types.PersonalizedPostInfo
	query := db.Table("posts").
		Joins("LEFT JOIN user_favorites ON (p_postid = uf_postid AND uf_userid = ?)", requesterID).
		Joins("LEFT JOIN user_subscriptions ON (p_channelname = us_channelname AND us_userid = ?)", requesterID).
		Joins("LEFT JOIN users ON (u_userid = p_userid_creator)").
		Select("*, (uf_postid IS NOT NULL) AS favorited, ST_AsText(p_location) AS location, (us_channelname IS NOT NULL) AS subscribed").
		Order("p_time desc")

	if postQueryParams.Flags != nil {
		if postQueryParams.Flags.Mine {
			query = query.Where("p_userid_creator = ?", requesterID)
		}
		if postQueryParams.Flags.Favorites {
			query = query.Where("uf_postid IS NOT NULL")
		}
		if postQueryParams.Flags.Subscriptions {
			query = query.Where("us_channelname IS NOT NULL")
		}
	}
	if postQueryParams.LocationRange != nil {
		query = query.Where("p_location && ST_MakeEnvelope (?,?,?,?)",
			postQueryParams.LocationRange.Min.Longitude,
			postQueryParams.LocationRange.Min.Latitude,
			postQueryParams.LocationRange.Max.Longitude,
			postQueryParams.LocationRange.Max.Latitude)
	}
	if postQueryParams.TimeRange != nil {
		query = query.Where("p_time > ? AND p_time < ?",
			postQueryParams.TimeRange.Min,
			postQueryParams.TimeRange.Max)
	}
	if postQueryParams.Limit != nil {
		query = query.Limit(*postQueryParams.Limit)
	}
	if postQueryParams.Offset != nil {
		query = query.Offset(*postQueryParams.Offset)
	}

	err := query.Find(&posts).Error
	if err != nil {
		return nil, httperr.New(err.Error(), http.StatusInternalServerError)
	}
	return posts, nil
}