class ActivityDiaryContentProvider extends ContentProvider {

    @Nullable
    @Override
    public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
        SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder();
        boolean useRawQuery = false;
        String grouping = null;
        String sql = "";
        Cursor c;
        int id = 0;
        if(selection == null){
            selection = "";
        }

        MatrixCursor result = new MatrixCursor(new String[]{
                BaseColumns._ID,
                SUGGEST_COLUMN_TEXT_1,
                SUGGEST_COLUMN_ICON_1,
                SUGGEST_COLUMN_INTENT_ACTION,
                SUGGEST_COLUMN_INTENT_DATA,
                SUGGEST_COLUMN_QUERY
        });

        if (sUriMatcher.match(uri) < 1) {
            /* URI is not recognized, return an empty Cursor */
            return null;
        }
        switch (sUriMatcher.match(uri)) {
            case activities_ID:
            case conditions_ID:
            case diary_ID:
            case diary_image_ID:
            case diary_location_ID:
                if (selection != null) {
                    selection = selection + " AND ";
                } else {
                    selection = "";
                }
                selection = selection + "_id=" + uri.getLastPathSegment();
            default:
                /* empty */
        }

        switch (sUriMatcher.match(uri)) {
            case activities_ID: /* intended fall through */
            case activities:
                int n;
                boolean hasDiaryJoin = false;
                String tables = ActivityDiaryContract.DiaryActivity.TABLE_NAME;
                if (TextUtils.isEmpty(sortOrder)) {
                    sortOrder = ActivityDiaryContract.DiaryActivity.SORT_ORDER_DEFAULT;
                }
                n = 0;
                while(n < projection.length){
                    if(ActivityDiaryContract.DiaryActivity.X_AVG_DURATION.equals(projection[n])){
                        projection[n] = "AVG(" + ActivityDiaryContract.Diary.END + " - "
                                + ActivityDiaryContract.Diary.START + ") AS "
                                + ActivityDiaryContract.DiaryActivity.X_AVG_DURATION;
                        hasDiaryJoin = true;
                    }
                    if(ActivityDiaryContract.DiaryActivity.X_START_OF_LAST.equals(projection[n])){
                        projection[n] = "xx_start AS "
                                + ActivityDiaryContract.DiaryActivity.X_START_OF_LAST;
                        hasDiaryJoin = true;
                    }
                    n++;
                }
                if(hasDiaryJoin){
                    n = 0;
                    while(n < projection.length) {
                        if(ActivityDiaryContract.DiaryActivity._ID.equals(projection[n])){
                            projection[n] = ActivityDiaryContract.DiaryActivity.TABLE_NAME + "."
                                    + ActivityDiaryContract.DiaryActivity._ID;
                        }
                        n++;
                    }
                    selection = selection.replaceAll(" " + ActivityDiaryContract.DiaryActivity._ID, " " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID);
                    selection = selection.replaceAll(ActivityDiaryContract.DiaryActivity._DELETED, ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._DELETED);

                    tables = tables + ", " + ActivityDiaryContract.Diary.TABLE_NAME;
                    tables = tables + ", (SELECT xx_ref, " + ActivityDiaryContract.Diary.START + " as xx_start FROM " + ActivityDiaryContract.Diary.TABLE_NAME + ","
                                    +     "(SELECT " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " AS xx_ref,"
                                                 + " MAX(" + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.END + ") AS xx_ref_end"
                                    +     " FROM " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + ", " + ActivityDiaryContract.Diary.TABLE_NAME
                                    +     " WHERE " +  ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID
                                    +           " = " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID
                                    +     " GROUP BY " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID
                                    +     ")"
                                    +    " WHERE " +  ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.END + " = xx_ref_end"
                                    +  ")"
                                        ;

                    selection = selection + " AND " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " = " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID
                                          + " AND " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " = xx_ref";

                    grouping = ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID;

                }
                qBuilder.setTables(tables);
                break;

            case diary_image_ID: /* intended fall through */
            case diary_image:
                qBuilder.setTables(ActivityDiaryContract.DiaryImage.TABLE_NAME);
                if (TextUtils.isEmpty(sortOrder))
                    sortOrder = ActivityDiaryContract.DiaryImage.SORT_ORDER_DEFAULT;
                break;
            case diary_location_ID: /* intended fall through */
            case diary_location:
                qBuilder.setTables(ActivityDiaryContract.DiaryLocation.TABLE_NAME);
                if (TextUtils.isEmpty(sortOrder))
                    sortOrder = ActivityDiaryContract.DiaryLocation.SORT_ORDER_DEFAULT;
                break;
            case diary_ID: /* intended fall through */
            case diary:
                /* rewrite projection, to prefix with tables */
                qBuilder.setTables(ActivityDiaryContract.Diary.TABLE_NAME + " INNER JOIN " +
                        ActivityDiaryContract.DiaryActivity.TABLE_NAME + " ON " +
                        ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID + " = " +
                        ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID
                );
                if (TextUtils.isEmpty(sortOrder))
                    sortOrder = ActivityDiaryContract.Diary.SORT_ORDER_DEFAULT;
                break;
            case diary_stats:
                useRawQuery = true;
                List<String> l = uri.getPathSegments();
                String start;
                String end;

                if(l.size() == 3){
                    // we have a range query with start and end timestamps here
                    start = l.get(1);
                    end = l.get(2);
                }else{
                    start = "0";
                    end = "6156000000000"; // this is roughly 200 year since epoch, congratulations if this lasted so long...
                }

                String subselect = "SELECT SUM(MIN(IFNULL(" + ActivityDiaryContract.Diary.END + ",strftime('%s','now') * 1000), " + end + ") - "
                        + "MAX(" + ActivityDiaryContract.Diary.START + ", " + start + ")) from " + ActivityDiaryContract.Diary.TABLE_NAME
                        + " WHERE ((start >= " + start + " AND start < " + end + ") OR (end > " + start + " AND end <= " + end + ") OR (start < " + start + " AND end > " + end + "))";

                if (selection != null && selection.length() > 0) {
                    subselect += " AND (" + selection + ")";
                }

                sql = "SELECT " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity.NAME + " as " + ActivityDiaryContract.DiaryStats.NAME
                        + ", " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity.COLOR + " as " + ActivityDiaryContract.DiaryStats.COLOR
                        + ", SUM(MIN(IFNULL(" + ActivityDiaryContract.Diary.END + ",strftime('%s','now') * 1000), " + end + ") - MAX(" + start + ", " + ActivityDiaryContract.Diary.START + ")) as " + ActivityDiaryContract.DiaryStats.DURATION
                        + ", (SUM(MIN(IFNULL(" + ActivityDiaryContract.Diary.END + ",strftime('%s','now') * 1000), " + end + ") - MAX(" + start + ", " + ActivityDiaryContract.Diary.START + ")) * 100.0 " +
                        "/ (" + subselect + ")) as " + ActivityDiaryContract.DiaryStats.PORTION
                        + " FROM " + ActivityDiaryContract.Diary.TABLE_NAME + ", " + ActivityDiaryContract.DiaryActivity.TABLE_NAME
                        + " WHERE " + ActivityDiaryContract.Diary.TABLE_NAME + "." + ActivityDiaryContract.Diary.ACT_ID + " = " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID + " AND"
                        + " ((start >= " + start + " AND start < " + end + ") OR (end > " + start + " AND end <= " + end + ") OR (start < " + start + " AND end > " + end + "))"
                ;
                if(selection != null && selection.length() > 0) {
                    sql += " AND (" + selection + ")";
                    String[] newArgs = Arrays.copyOf(selectionArgs, selectionArgs.length * 2);
                    System.arraycopy(selectionArgs, 0, newArgs, selectionArgs.length, selectionArgs.length);
                    selectionArgs = newArgs;
                }
                sql += " GROUP BY " + ActivityDiaryContract.DiaryActivity.TABLE_NAME + "." + ActivityDiaryContract.DiaryActivity._ID;
                if (sortOrder != null && sortOrder.length() > 0) {
                    sql += " ORDER by " + sortOrder;
                }
                break;

            case search_recent_suggestion:

                sql = "SELECT " + ActivityDiaryContract.DiarySearchSuggestion.SUGGESTION + ", " +
                        ActivityDiaryContract.DiarySearchSuggestion.ACTION + " FROM " +
                        ActivityDiaryContract.DiarySearchSuggestion.TABLE_NAME +
                        " ORDER BY " + ActivityDiaryContract.DiarySearchSuggestion._ID + " DESC";

                c = mOpenHelper.getReadableDatabase().rawQuery(sql, selectionArgs);
                if (c != null && c.moveToFirst()) {
                    do {
                        Object icon = null;
                        String action = c.getString(1);
                        String q = c.getString(0); // what do we want to display

                        if(action.equals(SEARCH_ACTIVITY)) {
                            /* icon stays null */
                            int i = Integer.parseInt(q);
                            q = ActivityHelper.helper.activityWithId(i).getName();
                        }else if(action.equals(SEARCH_NOTE)){
                            q = getContext().getResources().getString(R.string.search_notes, q);
                            icon = R.drawable.ic_search;
                        }else if(action.equals(SEARCH_GLOBAL) || action.equals(Intent.ACTION_SEARCH)){
                            q = getContext().getResources().getString(R.string.search_diary, q);
                            icon = R.drawable.ic_search;
                        }else if(action.equals(SEARCH_DATE)){
                            q = getContext().getResources().getString(R.string.search_date, q);
                            icon = R.drawable.ic_calendar;
                        }

                        result.addRow(new Object[]{id++,
                                q,
                                /* icon */ icon,
                                /* intent action */ action,
                                /* intent data */ Uri.withAppendedPath(SEARCH_URI, c.getString(0)),
                                /* rewrite query */c.getString(0)
                        });
                    } while (c.moveToNext());
                }

                return result;


            case search_suggestion:
                String query = uri.getLastPathSegment(); //.toLowerCase();

                if (query != null && query.length() > 0) {
                    // activities matching the current search
                    ArrayList<DiaryActivity> filtered = ActivityHelper.helper.sortedActivities(query);

                    // TODO: make the amount of activities shown configurable
                    for (int i = 0; i < 3; i++) {
                        if (i < filtered.size()) {
                            result.addRow(new Object[]{id++,
                                    filtered.get(i).getName(),
                                    /* icon */ null,
                                    /* intent action */ SEARCH_ACTIVITY,
                                    /* intent data */ Uri.withAppendedPath(SEARCH_URI, Integer.toString(filtered.get(i).getId())),
                                    /* rewrite query */filtered.get(i).getName()
                            });
                        }
                    }
                    // Notes
                    result.addRow(new Object[]{id++,
                            getContext().getResources().getString(R.string.search_notes, query),
                            /* icon */ R.drawable.ic_search,
                            /* intent action */ SEARCH_NOTE,
                            /* intent data */ Uri.withAppendedPath(SEARCH_URI, query),
                            /* rewrite query */ query
                    });

                    // Global search
                    result.addRow(new Object[]{id++,
                            getContext().getResources().getString(R.string.search_diary, query),
                            /* icon */ R.drawable.ic_search,
                            /* intent action */ SEARCH_GLOBAL,
                            /* intent data */ Uri.withAppendedPath(SEARCH_URI, query),
                            /* rewrite query */ query
                    });

                    // Date
                    result.addRow(new Object[]{id++,
                            getContext().getResources().getString(R.string.search_date, query),
                            /* icon */ R.drawable.ic_calendar,
                            /* intent action */ SEARCH_DATE,
                            /* intent data */ Uri.withAppendedPath(SEARCH_URI, query),
                            /* rewrite query */ query
                    });

                    // has Pictures
                    // TODO: add picture search

                    // Location (GPS)
                    // TODO: add location search

                }
                return result;

            case conditions_ID:
                /* intended fall through */
            case conditions:
//                qBuilder.setTables(ActivityDiaryContract.Condition.TABLE_NAME);
                /* TODO #18               if (TextUtils.isEmpty(sortOrder)) sortOrder = ActivityDiaryContract.Conditions.SORT_ORDER_DEFAULT; */
            default:
                /* empty */
        }

        if (useRawQuery) {
            c = mOpenHelper.getReadableDatabase().rawQuery(sql, selectionArgs);
        } else {
            c = qBuilder.query(mOpenHelper.getReadableDatabase(),
                    projection,
                    selection,
                    selectionArgs,
                    grouping,
                    null,
                    sortOrder);
        }
        c.setNotificationUri(getContext().getContentResolver(), uri);
        return c;
    }

}