package com.gracecode.android.presentation.helper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import com.gracecode.android.presentation.Huaban;
import com.gracecode.android.presentation.dao.Pin;
import com.gracecode.android.common.Logger;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.PreparedQuery;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

import java.sql.SQLException;
import java.util.ArrayList;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

    public static final String FIELD_ID = "id";
    public static final String FIELD_BOARD_ID = "board_id";
    public static final String FIELD_TEXT = "text";
    public static final String FIELD_KEY = "key";
    public static final String FIELD_CREATE_AT = "create_at";
    public static final String FIELD_LINK = "link";
    public static final String FIELD_HEIGHT = "height";
    public static final String FIELD_WIDTH = "width";


    public DatabaseHelper(Context context) {
        super(context, Huaban.DATABASE_NAME, null, Huaban.APP_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, Pin.class);
        } catch (SQLException e) {
            Logger.e(e.getMessage());
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int i, int i2) {

    }


    public Dao<Pin, Integer> getPinsDAO() throws SQLException {
        return getDao(Pin.class);
    }


    public ArrayList<Pin> getPinsBeforeMaxId(long maxId) throws SQLException {
        ArrayList<Pin> result = new ArrayList<Pin>();

        QueryBuilder<Pin, Integer> queryBuilder = getPinsDAO().queryBuilder()
                .orderBy(DatabaseHelper.FIELD_ID, false)
                .limit(Huaban.PAGE_SIZE);

        PreparedQuery<Pin> query;
        if (maxId > 0) {
            query = queryBuilder
                    .where()
                    .lt(DatabaseHelper.FIELD_ID, maxId)
                    .prepare();
        } else {
            query = queryBuilder.prepare();
        }

        result.addAll(getPinsDAO().query(query));
        return result;
    }

    public ArrayList<Pin> getPinsAfterSinceId(long sinceId) throws SQLException {
        ArrayList<Pin> result = new ArrayList<Pin>();

        QueryBuilder<Pin, Integer> queryBuilder = getPinsDAO().queryBuilder()
                .orderBy(DatabaseHelper.FIELD_ID, false)
                .limit(Huaban.PAGE_SIZE);

        PreparedQuery<Pin> query;
        if (sinceId > 0) {
            query = queryBuilder
                    .where()
                    .gt(DatabaseHelper.FIELD_ID, sinceId)
                    .prepare();
        } else {
            query = queryBuilder.prepare();
        }

        result.addAll(getPinsDAO().query(query));
        return result;
    }

    public Pin getPin(int id) {
        try {
            return getPinsDAO().queryForId(id);
        } catch (SQLException e) {
            return new Pin();
        }
    }
}