WordPress/src/main/java/org/wordpress/android/datasets/ReaderCommentTable.java

package org.wordpress.android.datasets;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import org.wordpress.android.models.ReaderComment;
import org.wordpress.android.models.ReaderCommentList;
import org.wordpress.android.models.ReaderPost;
import org.wordpress.android.util.SqlUtils;

/**
 * stores comments on reader posts
 */
public class ReaderCommentTable {
    private static final String COLUMN_NAMES =
                      " blog_id,"
                    + " post_id,"
                    + " comment_id,"
                    + " parent_id,"
                    + " author_name,"
                    + " author_avatar,"
                    + " author_url,"
                    + " author_id,"
                    + " author_blog_id,"
                    + " published,"
                    + " timestamp,"
                    + " status,"
                    + " text,"
                    + " num_likes,"
                    + " is_liked,"
                    + " page_number";


    protected static void createTables(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE tbl_comments ("
                + " blog_id             INTEGER DEFAULT 0,"
                + " post_id             INTEGER DEFAULT 0,"
                + "	comment_id		    INTEGER DEFAULT 0,"
                + " parent_id           INTEGER DEFAULT 0,"
                + "	author_name	        TEXT,"
                + " author_avatar       TEXT,"
                + "	author_url	        TEXT,"
                + " author_id           INTEGER DEFAULT 0,"
                + " author_blog_id      INTEGER DEFAULT 0,"
                + " published           TEXT,"
                + " timestamp           INTEGER DEFAULT 0,"
                + " status              TEXT,"
                + " text                TEXT,"
                + " num_likes           INTEGER DEFAULT 0,"
                + " is_liked            INTEGER DEFAULT 0,"
                + " page_number         INTEGER DEFAULT 0,"
                + " PRIMARY KEY (blog_id, post_id, comment_id))");
        db.execSQL("CREATE INDEX idx_page_number ON tbl_comments(page_number)");
    }

    protected static void dropTables(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS tbl_comments");
    }

    protected static void reset(SQLiteDatabase db) {
        dropTables(db);
        createTables(db);
    }

    protected static int purge(SQLiteDatabase db) {
        // purge comments attached to posts that no longer exist
        int numDeleted = db.delete("tbl_comments", "post_id NOT IN (SELECT DISTINCT post_id FROM tbl_posts)", null);

        // purge all but the first page of comments
        numDeleted += db.delete("tbl_comments", "page_number != 1", null);

        return numDeleted;
    }

    public static boolean isEmpty() {
        return (getNumComments()==0);
    }

    private static int getNumComments() {
        long count = SqlUtils.getRowCount(ReaderDatabase.getReadableDb(), "tbl_comments");
        return (int)count;
    }

    /*
     * returns the highest page_number for comments on the passed post
     */
    public static int getLastPageNumberForPost(long blogId, long postId) {
        String[] args = {Long.toString(blogId), Long.toString(postId)};
        return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(),
                "SELECT MAX(page_number) FROM tbl_comments WHERE blog_id=? AND post_id=?", args);
    }

    /*
     * returns the page number for a specific comment
     */
    public static int getPageNumberForComment(long blogId, long postId, long commentId) {
        String[] args = {Long.toString(blogId), Long.toString(postId), Long.toString(commentId)};
        return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(),
                "SELECT page_number FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=?", args);
    }

    /*
     * removes all comments for the passed post
     */
    public static void purgeCommentsForPost(long blogId, long postId) {
        String[] args = {Long.toString(blogId), Long.toString(postId)};
        ReaderDatabase.getWritableDb().delete("tbl_comments", "blog_id=? AND post_id=?", args);
    }

    /*
     * returns the #comments stored locally for this post, which may differ from ReaderPostTable.getNumCommentsOnPost
     * (which is the #comments the server says exist for this post)
     */
    public static int getNumCommentsForPost(ReaderPost post) {
        if (post == null) {
            return 0;
        }
        return getNumCommentsForPost(post.blogId, post.postId);
    }
    private static int getNumCommentsForPost(long blogId, long postId) {
        String[] args = {Long.toString(blogId), Long.toString(postId)};
        return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), "SELECT count(*) FROM tbl_comments WHERE blog_id=? AND post_id=?", args);
    }

    public static ReaderCommentList getCommentsForPost(ReaderPost post) {
        if (post == null) {
            return new ReaderCommentList();
        }

        String[] args = {Long.toString(post.blogId), Long.toString(post.postId)};
        Cursor c = ReaderDatabase.getReadableDb().rawQuery("SELECT * FROM tbl_comments WHERE blog_id=? AND post_id=? ORDER BY timestamp", args);
        try {
            ReaderCommentList comments = new ReaderCommentList();
            if (c.moveToFirst()) {
                do {
                    comments.add(getCommentFromCursor(c));
                } while (c.moveToNext());
            }
            return comments;
        } finally {
            SqlUtils.closeCursor(c);
        }
    }

    public static void addOrUpdateComment(ReaderComment comment) {
        if (comment == null) {
            return;
        }
        ReaderCommentList comments = new ReaderCommentList();
        comments.add(comment);
        addOrUpdateComments(comments);
    }

    public static void addOrUpdateComments(ReaderCommentList comments) {
        if (comments == null || comments.size() == 0) {
            return;
        }

        SQLiteDatabase db = ReaderDatabase.getWritableDb();
        db.beginTransaction();
        SQLiteStatement stmt = db.compileStatement("INSERT OR REPLACE INTO tbl_comments ("
                                                  + COLUMN_NAMES
                                                  + ") VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16)");
        try {
            for (ReaderComment comment: comments) {
                stmt.bindLong  (1,  comment.blogId);
                stmt.bindLong  (2,  comment.postId);
                stmt.bindLong  (3,  comment.commentId);
                stmt.bindLong  (4,  comment.parentId);
                stmt.bindString(5,  comment.getAuthorName());
                stmt.bindString(6,  comment.getAuthorAvatar());
                stmt.bindString(7,  comment.getAuthorUrl());
                stmt.bindLong  (8,  comment.authorId);
                stmt.bindLong  (9,  comment.authorBlogId);
                stmt.bindString(10, comment.getPublished());
                stmt.bindLong  (11, comment.timestamp);
                stmt.bindString(12, comment.getStatus());
                stmt.bindString(13, comment.getText());
                stmt.bindLong  (14, comment.numLikes);
                stmt.bindLong  (15, SqlUtils.boolToSql(comment.isLikedByCurrentUser));
                stmt.bindLong  (16, comment.pageNumber);

                stmt.execute();
            }

            db.setTransactionSuccessful();

        } finally {
            db.endTransaction();
            SqlUtils.closeStatement(stmt);
        }
    }

    public static ReaderComment getComment(long blogId, long postId, long commentId) {
        String[] args = new String[] {Long.toString(blogId), Long.toString(postId), Long.toString(commentId)};
        Cursor c = ReaderDatabase.getReadableDb().rawQuery(
                "SELECT * FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=? LIMIT 1", args);
        try {
            if (!c.moveToFirst()) {
                return null;
            }
            return getCommentFromCursor(c);
        } finally {
            SqlUtils.closeCursor(c);
        }
    }

    public static void deleteComment(ReaderPost post, long commentId) {
        if (post == null) {
            return;
        }
        String[] args = {Long.toString(post.blogId), Long.toString(post.postId), Long.toString(commentId)};
        ReaderDatabase.getWritableDb().delete("tbl_comments", "blog_id=? AND post_id=? AND comment_id=?", args);
    }

    /*
     * returns true if any of the passed comments don't already exist
     * IMPORTANT: assumes passed comments are all for the same post
     */
    public static boolean hasNewComments(ReaderCommentList comments) {
        if (comments == null || comments.size() == 0) {
            return false;
        }

        StringBuilder sb = new StringBuilder(
                "SELECT COUNT(*) FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id IN (");
        boolean isFirst = true;
        for (ReaderComment comment: comments) {
            if (isFirst) {
                isFirst = false;
            } else {
                sb.append(",");
            }
            sb.append(comment.commentId);
        }
        sb.append(")");

        String[] args = {Long.toString(comments.get(0).blogId),
                         Long.toString(comments.get(0).postId)};
        int numExisting = SqlUtils.intForQuery(ReaderDatabase.getReadableDb(), sb.toString(), args);
        return numExisting != comments.size();
    }

    /*
     * returns the #likes known to exist for this comment
     */
    public static int getNumLikesForComment(long blogId, long postId, long commentId) {
        String[] args = {Long.toString(blogId),
                         Long.toString(postId),
                         Long.toString(commentId)};
        return SqlUtils.intForQuery(ReaderDatabase.getReadableDb(),
                "SELECT num_likes FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=?",
                args);
    }

    /*
     * updates both the like count for a comment and whether it's liked by the current user
     */
    public static void setLikesForComment(ReaderComment comment, int numLikes, boolean isLikedByCurrentUser) {
        if (comment == null) {
            return;
        }

        String[] args =
               {Long.toString(comment.blogId),
                Long.toString(comment.postId),
                Long.toString(comment.commentId)};

        ContentValues values = new ContentValues();
        values.put("num_likes", numLikes);
        values.put("is_liked", SqlUtils.boolToSql(isLikedByCurrentUser));

        ReaderDatabase.getWritableDb().update(
                "tbl_comments",
                values,
                "blog_id=? AND post_id=? AND comment_id=?",
                args);
    }

    public static boolean isCommentLikedByCurrentUser(ReaderComment comment) {
        if (comment == null) {
            return false;
        }
        return isCommentLikedByCurrentUser(comment.blogId, comment.postId, comment.commentId);
    }
    public static boolean isCommentLikedByCurrentUser(long blogId, long postId, long commentId) {
        String[] args = {Long.toString(blogId),
                Long.toString(postId),
                Long.toString(commentId)};
        return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(),
                "SELECT is_liked FROM tbl_comments WHERE blog_id=? AND post_id=? and comment_id=?",
                args);
    }

    public static boolean commentExists(long blogId, long postId, long commentId) {
        String[] args = {Long.toString(blogId),
                Long.toString(postId),
                Long.toString(commentId)};

        return SqlUtils.boolForQuery(ReaderDatabase.getReadableDb(),
                "SELECT 1 FROM tbl_comments WHERE blog_id=? AND post_id=? AND comment_id=?", args);
    }

    private static ReaderComment getCommentFromCursor(Cursor c) {
        if (c == null) {
            throw new IllegalArgumentException("null comment cursor");
        }

        ReaderComment comment = new ReaderComment();

        comment.commentId = c.getLong(c.getColumnIndex("comment_id"));
        comment.blogId = c.getLong(c.getColumnIndex("blog_id"));
        comment.postId = c.getLong(c.getColumnIndex("post_id"));
        comment.parentId = c.getLong(c.getColumnIndex("parent_id"));

        comment.setPublished(c.getString(c.getColumnIndex("published")));
        comment.timestamp = c.getLong(c.getColumnIndex("timestamp"));

        comment.setAuthorAvatar(c.getString(c.getColumnIndex("author_avatar")));
        comment.setAuthorName(c.getString(c.getColumnIndex("author_name")));
        comment.setAuthorUrl(c.getString(c.getColumnIndex("author_url")));
        comment.authorId = c.getLong(c.getColumnIndex("author_id"));
        comment.authorBlogId = c.getLong(c.getColumnIndex("author_blog_id"));

        comment.setStatus(c.getString(c.getColumnIndex("status")));
        comment.setText(c.getString(c.getColumnIndex("text")));

        comment.numLikes = c.getInt(c.getColumnIndex("num_likes"));
        comment.isLikedByCurrentUser = SqlUtils.sqlToBool(c.getInt(c.getColumnIndex("is_liked")));
        comment.pageNumber = c.getInt(c.getColumnIndex("page_number"));

        return comment;
    }
}