/*
 * Copyright (C) 2012 Brian Muramatsu
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.btmura.android.reddit.provider;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.btmura.android.reddit.provider.SubredditProvider.Subreddits;
import com.btmura.android.reddit.provider.VoteProvider.Votes;

class DbHelper extends SQLiteOpenHelper {

    static final String DATABASE_REDDIT = "reddit";
    static final String DATABASE_TEST = "test";
    static final int LATEST_VERSION = 2;

    /** Version kept to control what tables are created mostly for testing. */
    private final int version;

    DbHelper(Context context, String name, int version) {
        super(context, name, null, version);
        this.version = version;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.beginTransaction();
        try {
            if (version > 1) {
                createSubredditsV2(db);
                createVotes(db);
            } else {
                createSubredditsV1(db);
            }
            insertDefaultSubreddits(db);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void createVotes(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + Votes.TABLE_NAME + " ("
                + Votes._ID + " INTEGER PRIMARY KEY, "
                + Votes.COLUMN_ACCOUNT + " TEXT DEFAULT '', "
                + Votes.COLUMN_NAME + " TEXT NOT NULL, "
                + Votes.COLUMN_VOTE + " INTEGER DEFAULT 0, "
                + Votes.COLUMN_STATE + " INTEGER DEFAULT 0, "
                + "UNIQUE (" + Votes.COLUMN_ACCOUNT + "," + Votes.COLUMN_NAME + "))");
        db.execSQL("CREATE INDEX accountVotes " + "ON " + Votes.TABLE_NAME + " ("
                + Votes.COLUMN_ACCOUNT + ", "
                + Votes.COLUMN_NAME + " COLLATE NOCASE ASC)");
    }

    private void createSubredditsV2(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + Subreddits.TABLE_NAME + " ("
                + Subreddits._ID + " INTEGER PRIMARY KEY, "
                + Subreddits.COLUMN_ACCOUNT + " TEXT DEFAULT '', "
                + Subreddits.COLUMN_NAME + " TEXT NOT NULL, "
                + Subreddits.COLUMN_STATE + " INTEGER DEFAULT 0, "
                + Subreddits.COLUMN_EXPIRATION + " INTEGER DEFAULT 0, "
                + "UNIQUE (" + Subreddits.COLUMN_ACCOUNT + "," + Subreddits.COLUMN_NAME + "))");
        db.execSQL("CREATE INDEX accountSubreddits " + "ON " + Subreddits.TABLE_NAME + " ("
                + Subreddits.COLUMN_ACCOUNT + ", "
                + Subreddits.COLUMN_NAME + " COLLATE NOCASE ASC)");
    }

    private void createSubredditsV1(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + Subreddits.TABLE_NAME + " ("
                + Subreddits._ID + " INTEGER PRIMARY KEY, "
                + Subreddits.COLUMN_NAME + " TEXT UNIQUE NOT NULL)");
        db.execSQL("CREATE UNIQUE INDEX " + Subreddits.COLUMN_NAME
                + " ON " + Subreddits.TABLE_NAME + " ("
                + Subreddits.COLUMN_NAME + " ASC)");
    }

    private void insertDefaultSubreddits(SQLiteDatabase db) {
        String[] defaultSubreddits = {
                "",
                "AdviceAnimals",
                "announcements",
                "AskReddit",
                "askscience",
                "atheism",
                "aww",
                "blog",
                "funny",
                "gaming",
                "IAmA",
                "movies",
                "Music",
                "pics",
                "politics",
                "science",
                "technology",
                "todayilearned",
                "videos",
                "worldnews",
                "WTF",};

        for (int i = 0; i < defaultSubreddits.length; i++) {
            ContentValues values = new ContentValues(1);
            values.put(Subreddits.COLUMN_NAME, defaultSubreddits[i]);
            db.insert(Subreddits.TABLE_NAME, null, values);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion == 1 && newVersion == 2) {
            db.beginTransaction();
            try {
                upgradeSubredditsV2(db);
                createVotes(db);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        }
    }

    private void upgradeSubredditsV2(SQLiteDatabase db) {
        // 1. Back up the old subreddit rows into ContentValues.
        ArrayList<ContentValues> rows = getSubredditNames(db);

        // 2. Drop the old table and index.
        db.execSQL("DROP INDEX " + Subreddits.COLUMN_NAME);
        db.execSQL("DROP TABLE " + Subreddits.TABLE_NAME);

        // 3. Create the new table and import the backed up subreddits.
        createSubredditsV2(db);
        int count = rows.size();
        for (int i = 0; i < count; i++) {
            db.insert(Subreddits.TABLE_NAME, null, rows.get(i));
        }
    }

    private ArrayList<ContentValues> getSubredditNames(SQLiteDatabase db) {
        ArrayList<ContentValues> rows = new ArrayList<ContentValues>();
        Cursor c = db.query(Subreddits.TABLE_NAME,
                new String[] {Subreddits.COLUMN_NAME},
                null,
                null,
                null,
                null,
                null);
        while (c.moveToNext()) {
            ContentValues values = new ContentValues(1);
            values.put(Subreddits.COLUMN_NAME, c.getString(0));
            rows.add(values);
        }
        c.close();
        return rows;
    }
}
@@ -2290,285 +2290,8 @@
 LT 0
-, %22%0A                + %22UNIQUE (%22 + Votes.COLUMN_ACCOUNT + %22,%22 + Votes.COLUMN_NAME + %22))%22);%0A        db.execSQL(%22CREATE INDEX accountVotes %22 + %22ON %22 + Votes.TABLE_NAME + %22 (%22%0A                + Votes.COLUMN_ACCOUNT + %22, %22%0A                + Votes.COLUMN_NAME + %22 COLLATE NOCASE ASC
 )%22);
@@ -2850,219 +2850,8 @@
 %22);%0A
-        db.execSQL(%22CREATE INDEX accountSubreddits %22 + %22ON %22 + Subreddits.TABLE_NAME + %22 (%22%0A                + Subreddits.COLUMN_ACCOUNT + %22, %22%0A                + Subreddits.COLUMN_NAME + %22 COLLATE NOCASE ASC)%22);%0A
     

Remove some constraints and indices