package com.seafile.seadroid2.transfer;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.google.common.collect.Lists;

import java.util.List;

/**
 * Utils for {@link com.seafile.seadroid2.transfer.TransferDatabaseHelper}
 *
 * Created by Logan on 14/12/27.
 */
public class TransferDatabaseUtils {

    private static final String DEBUG_TAG = "TransferDatabaseUtils";

    public static final String LOCAL_PATH_IS_EMPTY = "not applicable";
    public static final int INVALID_DOWNLOAD_SIZE = 0;

    public static String downloadWhereClause = TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_ID + "=? and " +
            TransferDatabaseHelper.DOWNLOAD_COLUMN_PATH_IN_REPO + "=? and " +
            TransferDatabaseHelper.DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE + "=?";

    public static String uploadWhereClause = TransferDatabaseHelper.UPLOAD_COLUMN_REPO_ID + "=? and " +
            TransferDatabaseHelper.UPLOAD_COLUMN_PARENT_DIR + "=? and " +
            TransferDatabaseHelper.UPLOAD_COLUMN_LOCAL_PATH + "=? and " +
            TransferDatabaseHelper.UPLOAD_COLUMN_ACCOUNT_SIGNATURE + "=?";



    // avoid duplicate inserting request
    public static boolean isRowDuplicate(SQLiteDatabase db, String repoID, String pathInRepo, String actSignature) {

        String whereClause = TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_ID + "=? and " +
                TransferDatabaseHelper.DOWNLOAD_COLUMN_PATH_IN_REPO + "=? and " +
                TransferDatabaseHelper.DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE + "=?";

        String[] whereArgs = new String[] {repoID, pathInRepo, actSignature};

        return isUploadRowDuplicate(db,whereClause, whereArgs, TransferDatabaseHelper.DOWNLOAD_TABLE_NAME);
    }

    // avoid duplicate inserting request
    public static boolean isUploadRowDuplicate(SQLiteDatabase db, String whereClause, String[] whereArgs, String tableName) {

        long count = DatabaseUtils.queryNumEntries(
                db,
                tableName,
                whereClause,
                whereArgs);
        return count > 0;

    }

    public static void saveDownloadStatus(int taskID,
                                          String repoID,
                                          String repoName,
                                          String pathInRepo,
                                          long totalSize,
                                          String actSignature) {

        // Log.d(DEBUG_TAG, "add to database >> " + taskID);

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getWritableDatabase();
        boolean isDuplicate = isRowDuplicate(db, repoID, pathInRepo, actSignature);
        if (isDuplicate) {
            // if current state is CANCELLED, update to INIT
            updateDownloadInitStatus(repoID, pathInRepo, actSignature);
            return;
        }

        try {

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_TASKID, taskID);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE, TransferManager.TaskState.INIT.toString());
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_ID, repoID);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_NAME, repoName);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_PATH_IN_REPO, pathInRepo);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_LOCAL_PATH, LOCAL_PATH_IS_EMPTY);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_TOTAL_SIZE, totalSize);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_DOWNLOAD_SIZE, INVALID_DOWNLOAD_SIZE);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE, actSignature);

            db.replaceOrThrow(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME, null, cv);

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }
    }

    public static void saveUploadStatus(int taskID,
                                          String repoID,
                                          String repoName,
                                          String parentDir,
                                          String localPath,
                                          int isUpdate,
                                          int isCopyToLocal,
                                          long uploadSize,
                                          long totalSize,
                                          String actSignature) {

        Log.d(DEBUG_TAG, "add to database >> " + taskID);

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getWritableDatabase();

        String[] whereArgs = new String[] {repoID, parentDir, localPath, actSignature};

        boolean isDuplicate = isUploadRowDuplicate(db, uploadWhereClause, whereArgs, TransferDatabaseHelper.UPLOAD_TABLE_NAME);
        if (isDuplicate) {
            String where = TransferDatabaseHelper.UPLOAD_COLUMN_REPO_ID + "=? and " +
                    TransferDatabaseHelper.UPLOAD_COLUMN_STATE + "=? and " +
                    TransferDatabaseHelper.UPLOAD_COLUMN_PARENT_DIR + "=? and " +
                    TransferDatabaseHelper.UPLOAD_COLUMN_LOCAL_PATH + "=? and " +
                    TransferDatabaseHelper.UPLOAD_COLUMN_ACCOUNT_SIGNATURE + "=?";
            String[] args = new String[] {repoID,
                                        TransferManager.TaskState.CANCELLED.toString(),
                                        parentDir,
                                        localPath,
                                        actSignature};
            // if current state is CANCELLED, update to INIT
            updateUploadInitStatus(where, args, TransferDatabaseHelper.UPLOAD_TABLE_NAME);
            return;
        }

        try {

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_TASKID, taskID);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_STATE, TransferManager.TaskState.INIT.toString());
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_REPO_ID, repoID);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_REPO_NAME, repoName);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_PARENT_DIR, parentDir);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_LOCAL_PATH, localPath);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_IS_UPDATE, isUpdate);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_IS_COPY_TO_LOCAL, isCopyToLocal);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_TOTAL_SIZE, totalSize);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_UPLOAD_SIZE, uploadSize);
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_ACCOUNT_SIGNATURE, actSignature);

            db.replaceOrThrow(TransferDatabaseHelper.UPLOAD_TABLE_NAME, null, cv);

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }
    }

    private static void updateDownloadInitStatus(String repoID,
                                                 String pathInRepo,
                                                 String actSignature) {
        String whereClause = TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_ID + "=? and " +
                TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE + "=? and " +
                TransferDatabaseHelper.DOWNLOAD_COLUMN_PATH_IN_REPO + "=? and " +
                TransferDatabaseHelper.DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE + "=?";

        String[] whereArgs = new String[] {repoID,
                                            TransferManager.TaskState.CANCELLED.toString(),
                                            pathInRepo,
                                            actSignature};

        updateUploadInitStatus(whereClause, whereArgs, TransferDatabaseHelper.DOWNLOAD_TABLE_NAME);
    }

    private static void updateUploadInitStatus(String whereClause,
                                                 String[] whereArgs,
                                                 String tableName) {

        SQLiteDatabase db = null;

        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_STATE, TransferManager.TaskState.INIT.toString());

            db.update(tableName,
                    cv,
                    whereClause,
                    whereArgs);
            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }

    }

    public static void updateDownloadingStatus(long totalSize, long downloadedSize,
                                               String repoID,
                                               String pathInRepo,
                                               String actSignature) {
        SQLiteDatabase db = null;
        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE, TransferManager.TaskState.TRANSFERRING.toString());
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_TOTAL_SIZE, totalSize);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_DOWNLOAD_SIZE, downloadedSize);

            db.update(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME,
                    cv,
                    downloadWhereClause,
                    new String[] {repoID,
                                  pathInRepo,
                                  actSignature});

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }

    }

    public static void updateUploadingStatus(long uploadedSize,
                                               String repoID,
                                               String parentDir,
                                               String localPath,
                                               String actSignature) {
        SQLiteDatabase db = null;
        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_STATE, TransferManager.TaskState.TRANSFERRING.toString());
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_UPLOAD_SIZE, uploadedSize);

            db.update(TransferDatabaseHelper.UPLOAD_TABLE_NAME,
                    cv,
                    uploadWhereClause,
                    new String[] {repoID,
                                  parentDir,
                                  localPath,
                                  actSignature});

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }

    }

    public static void updateDownloadPauseStatus(long downloadedSize,
                                                 String repoID,
                                                 String pathInRepo,
                                                 String actSignature) {

        SQLiteDatabase db = null;
        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE, TransferManager.TaskState.CANCELLED.toString()); // should create PAUSED value
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_DOWNLOAD_SIZE, downloadedSize);

            db.update(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME,
                    cv,
                    downloadWhereClause,
                    new String[] {repoID,
                            pathInRepo,
                            actSignature});

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }

    }

    public static void updateDownloadCancelStatus(String repoID,
                                                  String pathInRepo,
                                                  String actSignature) {

        SQLiteDatabase db = null;

        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE, TransferManager.TaskState.CANCELLED.toString());

            db.update(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME,
                    cv,
                    downloadWhereClause,
                    new String[] {repoID,
                            pathInRepo,
                            actSignature});
            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }
    }

    public static void updateUploadCancelStatus(String repoID,
                                                  String parentDir,
                                                  String localPath,
                                                  String actSignature) {

        SQLiteDatabase db = null;

        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_STATE, TransferManager.TaskState.CANCELLED.toString());

            db.update(TransferDatabaseHelper.UPLOAD_TABLE_NAME,
                    cv,
                    uploadWhereClause,
                    new String[] {repoID,
                            parentDir,
                            localPath,
                            actSignature});
            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }
    }

    public static void updateDownloadCompleteStatus(String localPath,
                                                    long downloadSize,
                                                    String repoID,
                                                    String pathInRepo,
                                                    String actSignature) {

        // Log.d(DEBUG_TAG, "Download complete >> " + localPath.substring(localPath.lastIndexOf("/"), localPath.length()));

        SQLiteDatabase db = null;
        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE, TransferManager.TaskState.FINISHED.toString());
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_LOCAL_PATH, localPath);
            cv.put(TransferDatabaseHelper.DOWNLOAD_COLUMN_DOWNLOAD_SIZE, downloadSize);

            db.update(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME,
                    cv,
                    downloadWhereClause,
                    new String[] {repoID,
                            pathInRepo,
                            actSignature});

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }
    }

    public static void updateUploadCompleteStatus(String localPath,
                                                    String repoID,
                                                    String parentDir,
                                                    long uploadSize,
                                                    String actSignature) {

        Log.d(DEBUG_TAG, "Upload complete >> " + localPath.substring(localPath.lastIndexOf("/"), localPath.length()));

        SQLiteDatabase db = null;
        try {
            db = TransferDatabaseHelper.getInstance().getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_STATE, TransferManager.TaskState.FINISHED.toString());
            cv.put(TransferDatabaseHelper.UPLOAD_COLUMN_UPLOAD_SIZE, uploadSize);

            db.update(TransferDatabaseHelper.UPLOAD_TABLE_NAME,
                    cv,
                    uploadWhereClause,
                    new String[] {repoID,
                            parentDir,
                            localPath,
                            actSignature});

            db.close();
        } catch (Exception e) {
            if (db != null)
                db.close();

            Log.e(DEBUG_TAG, e.toString());
        }
    }

    public static final String[] PROJECTION = new String[]{TransferDatabaseHelper.DOWNLOAD_COLUMN_TASKID,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_ID,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_REPO_NAME,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_PATH_IN_REPO,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_LOCAL_PATH,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_TOTAL_SIZE,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_DOWNLOAD_SIZE,
                                                        TransferDatabaseHelper.DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE};

    public static List<DownloadTaskInfo> getDownloadTaskInfos() {

        List<DownloadTaskInfo> downloadTaskInfos = Lists.newArrayList();

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getReadableDatabase();

        Cursor cursor = db.query(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME,
                                PROJECTION, // columns
                                null,    // where clause
                                null,    // where args
                                null,
                                null,
                                null,
                                null);

        if (cursor == null || cursor.isClosed())
            return downloadTaskInfos;

        if(!cursor.moveToFirst())
            return downloadTaskInfos;

        do {
            DownloadTaskInfo downloadTaskInfo = new DownloadTaskInfo(null,                                      // account
                                                                    cursor.getInt(0),                           // task iD
                                                                    convertTaskStateByString(cursor.getString(1)),  // state
                                                                    cursor.getString(2),                        // repo id
                                                                    cursor.getString(3),                        // repo name
                                                                    cursor.getString(4),                        // path in repo
                                                                    cursor.getString(5),                        // local path
                                                                    cursor.getLong(6),                          // total size
                                                                    cursor.getLong(7),                          // download size
                                                                    null);                                      // error
            downloadTaskInfos.add(downloadTaskInfo);
        } while (cursor.moveToNext());

        cursor.close();
        db.close();
        // Log.d(DEBUG_TAG, "sqlite stores " + downloadTaskInfos.size() + "download tasks");
        return downloadTaskInfos;
    }

    public static void removeDownloadTask(int taskID) {

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getWritableDatabase();

        String whereClause = TransferDatabaseHelper.DOWNLOAD_COLUMN_TASKID + "=?";

        int row = db.delete(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME,
                whereClause,
                new String[]{String.valueOf(taskID)});
        // Log.d(DEBUG_TAG, "delete row " + row);
        db.close();
    }

    public static void removeUploadTask(int taskID) {

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getWritableDatabase();

        String whereClause = TransferDatabaseHelper.UPLOAD_COLUMN_TASKID + "=?";

        int row = db.delete(TransferDatabaseHelper.UPLOAD_TABLE_NAME,
                whereClause,
                new String[]{String.valueOf(taskID)});
        Log.d(DEBUG_TAG, "delete row " + row);
        db.close();
    }

    public static void removeALLDownloadTaskByState(TransferManager.TaskState taskState) {
        String whereClause = TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE + "=?";

        removeALLTasksByState(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME, whereClause, taskState);
    }

    public static void removeALLTasksByState(String tableName, String whereClause, TransferManager.TaskState taskState) {
        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getWritableDatabase();

        // String whereClause = TransferDatabaseHelper.DOWNLOAD_COLUMN_STATE + "=?";

        int row = db.delete(tableName,
                whereClause,
                new String[]{taskState.toString()}
        );
        // Log.d(DEBUG_TAG, "delete row " + row);
        db.close();
    }

    public static TransferManager.TaskState convertTaskStateByString(String state) {
        if (state.equals(TransferManager.TaskState.INIT.toString())) {
            return TransferManager.TaskState.INIT;
        } else if (state.equals(TransferManager.TaskState.TRANSFERRING.toString())) {
            return TransferManager.TaskState.TRANSFERRING;
        } else if (state.equals(TransferManager.TaskState.FINISHED.toString())) {
            return TransferManager.TaskState.FINISHED;
        } else if (state.equals(TransferManager.TaskState.CANCELLED.toString())) {
            return TransferManager.TaskState.CANCELLED;
        } else //state.equals(TransferManager.TaskState.FAILED.toString()
            return TransferManager.TaskState.FAILED;
    }

    public static boolean isDownloading() {
        return isUploading(TransferDatabaseHelper.DOWNLOAD_TABLE_NAME);
    }

    public static List<UploadTaskInfo> getUploadTaskInfos() {

        String[] PROJECTION = new String[]{TransferDatabaseHelper.UPLOAD_COLUMN_TASKID,
                TransferDatabaseHelper.UPLOAD_COLUMN_STATE,
                TransferDatabaseHelper.UPLOAD_COLUMN_REPO_ID,
                TransferDatabaseHelper.UPLOAD_COLUMN_REPO_NAME,
                TransferDatabaseHelper.UPLOAD_COLUMN_PARENT_DIR,
                TransferDatabaseHelper.UPLOAD_COLUMN_LOCAL_PATH,
                TransferDatabaseHelper.UPLOAD_COLUMN_IS_UPDATE,
                TransferDatabaseHelper.UPLOAD_COLUMN_IS_COPY_TO_LOCAL,
                TransferDatabaseHelper.UPLOAD_COLUMN_UPLOAD_SIZE,
                TransferDatabaseHelper.UPLOAD_COLUMN_TOTAL_SIZE,
                TransferDatabaseHelper.UPLOAD_COLUMN_ACCOUNT_SIGNATURE};

        List<UploadTaskInfo> uploadTaskInfos = Lists.newArrayList();

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getReadableDatabase();

        Cursor cursor = db.query(TransferDatabaseHelper.UPLOAD_TABLE_NAME,
                                PROJECTION, // columns
                                null,       // where clause
                                null,       // where args
                                null,
                                null,
                                null,
                                null);

        if (cursor == null || cursor.isClosed())
            return uploadTaskInfos;

        if (!cursor.moveToFirst())
            return uploadTaskInfos;

        do {
            UploadTaskInfo uploadTaskInfo = new UploadTaskInfo(null,                                    // account
                                                            cursor.getInt(0),                           // task iD
                                                            convertTaskStateByString(cursor.getString(1)),  // state
                                                            cursor.getString(2),                        // repo id
                                                            cursor.getString(3),                        // repo name
                                                            cursor.getString(4),                        // parent dir
                                                            cursor.getString(5),                        // local path
                                                            convertBoolByInt(cursor.getInt(6)),            // isUpdate
                                                            convertBoolByInt(cursor.getInt(7)),            // isCopyToLocal
                                                            cursor.getLong(8),                          // uploadSize
                                                            cursor.getLong(9),                          // total size
                                                            null);

            uploadTaskInfos.add(uploadTaskInfo);
        } while (cursor.moveToNext());

        cursor.close();
        db.close();
        // Log.d(DEBUG_TAG, "sqlite stores " + uploadTaskInfos.size() + " upload tasks");
        return uploadTaskInfos;
    }

    private static boolean convertBoolByInt(int value) {
        if (value == 0)
            return false;
        else
            return true;
    }

    public static boolean isUploading(String tableName) {

        SQLiteDatabase db = TransferDatabaseHelper.getInstance().getReadableDatabase();

        String whereTransferState = TransferDatabaseHelper.UPLOAD_COLUMN_STATE + "=?";

        long count = DatabaseUtils.queryNumEntries(
                db,
                tableName,
                whereTransferState,
                new String[]{TransferManager.TaskState.INIT.toString()});
        db.close();
        return count > 0;

    }
}