/**
 * This file is part of MountainNavigation.
 *
 * MountainNavigation is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * MountainNavigation is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with MountainNavigation.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @copyright Copyright (c) 2016 Vinzenz Rosenkanz <vinzenz.rosenkranz@gmail.com>
 *
 * @author Vinzenz Rosenkranz <vinzenz.rosenkranz@gmail.com>
 */

package de.apps4ics.mountainnavigation.handlers;

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

import java.util.ArrayList;
import java.util.List;

import de.apps4ics.mountainnavigation.MainActivity;
import de.apps4ics.mountainnavigation.pois.Endangered;
import de.apps4ics.mountainnavigation.pois.Biota;
import de.apps4ics.mountainnavigation.pois.Hike;
import de.apps4ics.mountainnavigation.pois.Types;
import de.apps4ics.mountainnavigation.pois.BreakPoint;
import de.apps4ics.mountainnavigation.pois.CellReception;
import de.apps4ics.mountainnavigation.pois.DbGeoPoint;
import de.apps4ics.mountainnavigation.pois.Fountain;
import de.apps4ics.mountainnavigation.pois.Hut;
import de.apps4ics.mountainnavigation.pois.Image;
import de.apps4ics.mountainnavigation.pois.Lift;
import de.apps4ics.mountainnavigation.pois.Path;
import de.apps4ics.mountainnavigation.pois.Peak;
import de.apps4ics.mountainnavigation.pois.Poi;
import de.apps4ics.mountainnavigation.pois.TrashBin;
import de.apps4ics.mountainnavigation.pois.Wifi;

public class DatabaseHandler extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 8;
    private static final float MAX_POI_DISTANCE = 12.5f;
    private static final float MAX_POI_DISTANCE_COS = (float) Math.cos(MAX_POI_DISTANCE / 6371);

    private static final String DATABASE_NAME = "mountainNavigation.db";
    private static final String TABLE_GP = "geopoint";
    private static final String TABLE_FOUNTAIN = "fountain";
    private static final String TABLE_PATH = "path";
    private static final String TABLE_HUT = "hut";
    private static final String TABLE_PEAK = "peak";
    private static final String TABLE_BREAK_POINT = "break_point";
    private static final String TABLE_TRASH_BIN = "trash_bin";
    private static final String TABLE_IMAGE = "image";
    private static final String TABLE_CELL_RECEPTION = "cell_reception";
    private static final String TABLE_WIFI = "wifi";
    private static final String TABLE_LIFT = "lift";
    private static final String TABLE_HIKE = "hike";
    private static final String TABLE_BIOTA = "biota";
    private static final String TABLE_ENDANGERED = "endangered";
    private static final String[] TABLES = new String[]{TABLE_FOUNTAIN, TABLE_PATH, TABLE_HUT, TABLE_PEAK, TABLE_BREAK_POINT, TABLE_TRASH_BIN, TABLE_IMAGE, TABLE_CELL_RECEPTION, TABLE_WIFI, TABLE_LIFT, TABLE_HIKE, TABLE_BIOTA};
    private static final Integer[] TABLE_TYPES = new Integer[]{Types.FOUNTAIN, Types.PATH, Types.HUT, Types.PEAK, Types.BREAK_POINT, Types.TRASH_BIN, Types.PIC, Types.CELL_RECEPTION, Types.WIFI, Types.LIFT, Types.HIKE, Types.BIOTA};

    private static final String KEY_POI_ID = "_id";
    private static final String KEY_POI_LAT = "_lat";
    private static final String KEY_POI_LON = "_lon";
    private static final String KEY_POI_ALT = "_alt";
    private static final String KEY_POI_TYPE = "_type";
    private static final String KEY_POI_TIME = "_time";

    private static final String KEY_POI_LAT_SIN = "_lat_sin";
    private static final String KEY_POI_LAT_COS = "_lat_cos";
    private static final String KEY_POI_LON_SIN = "_lon_sin";
    private static final String KEY_POI_LON_COS = "_lon_cos";
    private static final String KEY_POI_EXT_ID = "_poi_id";
    private static final String KEY_FOUNTAIN_SIZE = "_size";
    private static final String KEY_PATH_LENGTH = "_length";
    private static final String KEY_PATH_EXPOSED = "_path_exp";
    private static final String KEY_PATH_DIFF_WET = "_path_diff";
    private static final String KEY_PATH_GIDDINESS = "_path_giddiness";
    private static final String KEY_PATH_CLIMBING = "_path_climbing";
    private static final String KEY_BREAK_POINT_STONE = "_break_point_stone";
    private static final String KEY_BREAK_POINT_BENCH = "_break_point_bench";
    private static final String KEY_BREAK_POINT_TABLE = "_break_point_table";
    private static final String KEY_BREAK_POINT_ROOFED = "_break_point_roofed";
    private static final String KEY_HUT_TYPE = "_hut_type";
    private static final String KEY_HUT_WINTER_ROOM = "_hut_winter_room";
    private static final String KEY_HUT_NAME = "_hut_name";
    private static final String KEY_PEAK_NAME = "_peak_name";
    private static final String KEY_LIFT_NAME = "_lift_name";
    private static final String KEY_CELL_RECEPTION_STRENGTH = "_cell_reception_strength";
    private static final String KEY_CELL_RECEPTION_OP_NAME = "_cell_reception_op_name";
    private static final String KEY_CELL_RECEPTION_OP_COUNTRY = "_cell_reception_op_country";
    private static final String KEY_CELL_RECEPTION_NW_TYPE = "_cell_reception_nw_type";
    private static final String KEY_CELL_RECEPTION_ROAMING = "_cell_reception_roaming";
    private static final String KEY_WIFI_SSID = "_wifi_ssid";
    private static final String KEY_WIFI_LEVELS = "_wifi_levels";
    private static final String KEY_IMAGE_IMG = "_img_src";
    private static final String KEY_HIKE_LENGTH = "_hike_length";
    private static final String KEY_HIKE_TIME = "_hike_time";
    private static final String KEY_HIKE_DIFFICULTY = "_hike_difficulty";
    private static final String KEY_HIKE_NAME = "_hike_name";
    private static final String KEY_HIKE_AUTHOR = "_hike_author";
    private static final String KEY_HIKE_TIMESTAMP = "_hike_timestamp";
    private static final String KEY_HIKE_HEIGHT_UP = "_hike_height_up";
    private static final String KEY_HIKE_HEIGHT_DOWN = "_hike_height_down";
    private static final String KEY_HIKE_FLOORING = "_flooring";
    private static final String KEY_BIOTA_RADIUS = "_radius";
    private static final String KEY_BIOTA_EXT_ID = "_biota_id";
    private static final String KEY_ENDANGERED_NAME = "_endangered_name";
    private static final String KEY_ENDANGERED_IMAGE = "_endangered_image";

    public DatabaseHandler(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //create geo point table
        String CREATE_GP_TABLE = "CREATE TABLE " + TABLE_GP + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_POI_LAT + " REAL,"
                + KEY_POI_LON + " REAL,"
                + KEY_POI_ALT + " REAL,"
                + KEY_POI_LAT_SIN + " REAL,"
                + KEY_POI_LAT_COS + " REAL,"
                + KEY_POI_LON_SIN + " REAL,"
                + KEY_POI_LON_COS + " REAL,"
                + KEY_POI_EXT_ID + " INTEGER,"
                + KEY_POI_TYPE + " INTEGER,"
                + KEY_POI_TIME + " INTEGER" + ")";
        db.execSQL(CREATE_GP_TABLE);
        //create Fountain table
        String CREATE_FOUNTAIN_TABLE = "CREATE TABLE " + TABLE_FOUNTAIN + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_FOUNTAIN_SIZE + " INTEGER" + ")";
        db.execSQL(CREATE_FOUNTAIN_TABLE);
        //create Fountain table
        String CREATE_PATH_TABLE = "CREATE TABLE " + TABLE_PATH + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_PATH_LENGTH + " INTEGER,"
                + KEY_PATH_EXPOSED + " INTEGER,"
                + KEY_PATH_DIFF_WET + " INTEGER,"
                + KEY_PATH_GIDDINESS + " INTEGER,"
                + KEY_PATH_CLIMBING + " INTEGER" + ")";
        db.execSQL(CREATE_PATH_TABLE);
        //create break point table
        String CREATE_BREAK_POINT_TABLE = "CREATE TABLE " + TABLE_BREAK_POINT + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_BREAK_POINT_STONE + " INTEGER,"
                + KEY_BREAK_POINT_BENCH + " INTEGER,"
                + KEY_BREAK_POINT_TABLE + " INTEGER,"
                + KEY_BREAK_POINT_ROOFED + " INTEGER" + ")";
        db.execSQL(CREATE_BREAK_POINT_TABLE);
        //create hut table
        String CREATE_HUT_TABLE = "CREATE TABLE " + TABLE_HUT + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_HUT_TYPE + " INTEGER,"
                + KEY_HUT_WINTER_ROOM + " INTEGER,"
                + KEY_HUT_NAME + " TEXT" + ")";
        db.execSQL(CREATE_HUT_TABLE);
        //create peak table
        String CREATE_PEAK_TABLE = "CREATE TABLE " + TABLE_PEAK + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_PEAK_NAME + " TEXT" + ")";
        db.execSQL(CREATE_PEAK_TABLE);
        //create cell reception table
        String CREATE_CR_TABLE = "CREATE TABLE " + TABLE_CELL_RECEPTION + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_CELL_RECEPTION_STRENGTH + " INTEGER,"
                + KEY_CELL_RECEPTION_OP_NAME + " TEXT,"
                + KEY_CELL_RECEPTION_OP_COUNTRY + " TEXT,"
                + KEY_CELL_RECEPTION_ROAMING + " INTEGER,"
                + KEY_CELL_RECEPTION_NW_TYPE + " TEXT" + ")";
        db.execSQL(CREATE_CR_TABLE);
        //create wifi table
        String CREATE_WIFI_TABLE = "CREATE TABLE " + TABLE_WIFI + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_WIFI_SSID + " TEXT,"
                + KEY_WIFI_LEVELS + " INTEGER" + ")";
        db.execSQL(CREATE_WIFI_TABLE);
        //create lift table
        String CREATE_LIFT_TABLE = "CREATE TABLE " + TABLE_LIFT + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_LIFT_NAME + " TEXT" + ")";
        db.execSQL(CREATE_LIFT_TABLE);
        //create image table
        String CREATE_IMAGE_TABLE = "CREATE TABLE " + TABLE_IMAGE + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_POI_EXT_ID + " INTEGER,"
                + KEY_POI_TYPE + " INTEGER,"
                + KEY_IMAGE_IMG + " BLOB" + ")";
        db.execSQL(CREATE_IMAGE_TABLE);
        //create trash bin table
        String CREATE_TRASH_TABLE = "CREATE TABLE " + TABLE_TRASH_BIN + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY" + ")";
        db.execSQL(CREATE_TRASH_TABLE);
        //create hike table
        String CREATE_HIKE_TABLE = "CREATE TABLE " + TABLE_HIKE + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_HIKE_LENGTH + " REAL,"
                + KEY_HIKE_TIME + " INTEGER,"
                + KEY_HIKE_DIFFICULTY + " INTEGER,"
                + KEY_HIKE_NAME + " TEXT,"
                + KEY_HIKE_AUTHOR + " TEXT,"
                + KEY_HIKE_TIMESTAMP + " INTEGER,"
                + KEY_HIKE_HEIGHT_UP + " REAL,"
                + KEY_HIKE_HEIGHT_DOWN + " REAL,"
                + KEY_HIKE_FLOORING + " INTEGER" + ")";
        db.execSQL(CREATE_HIKE_TABLE);
        //create biota table
        String CREATE_BIOTA_TABLE = "CREATE TABLE " + TABLE_BIOTA + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_BIOTA_RADIUS + " REAL" + ")";
        db.execSQL(CREATE_BIOTA_TABLE);
        //create endangered table
        String CREATE_ENDANGERED_TABLE = "CREATE TABLE " + TABLE_ENDANGERED + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_BIOTA_EXT_ID + " INTEGER,"
                + KEY_ENDANGERED_NAME + " TEXT,"
                + KEY_ENDANGERED_IMAGE + " BLOB" + ")";
        db.execSQL(CREATE_ENDANGERED_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_GP);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FOUNTAIN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PATH);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_HUT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PEAK);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BREAK_POINT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRASH_BIN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CELL_RECEPTION);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_WIFI);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LIFT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_HIKE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BIOTA);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ENDANGERED);
        onCreate(db);
    }

    private ContentValues getValues(Poi poi){
        ContentValues values = new ContentValues();
        switch(poi.getType()){
            case Types.FOUNTAIN:
                Fountain f = (Fountain) poi;
                values.put(KEY_FOUNTAIN_SIZE, f.getSize());
                break;
            case Types.PATH:
                Path p = (Path) poi;
                values.put(KEY_PATH_LENGTH, p.getLength());
                values.put(KEY_PATH_EXPOSED, p.getExposed());
                values.put(KEY_PATH_DIFF_WET, p.getDiffWet());
                values.put(KEY_PATH_GIDDINESS, p.getGiddiness());
                values.put(KEY_PATH_CLIMBING, p.getClimbing());
                break;
            case Types.BREAK_POINT:
                BreakPoint bp = (BreakPoint) poi;
                values.put(KEY_BREAK_POINT_STONE, bp.getStone());
                values.put(KEY_BREAK_POINT_BENCH, bp.getBench());
                values.put(KEY_BREAK_POINT_TABLE, bp.getTable());
                values.put(KEY_BREAK_POINT_ROOFED, bp.getRoofed());
                break;
            case Types.TRASH_BIN:
                TrashBin tb = (TrashBin) poi;
                break;
            case Types.PIC:
                Image i = (Image) poi;
                values.put(KEY_POI_EXT_ID, i.getPoiId());
                values.put(KEY_POI_TYPE, i.getPoiType());
                values.put(KEY_IMAGE_IMG, i.getImage());
                break;
            case Types.CELL_RECEPTION:
                CellReception cr = (CellReception) poi;
                values.put(KEY_CELL_RECEPTION_STRENGTH, cr.getStrength());
                values.put(KEY_CELL_RECEPTION_OP_NAME, cr.getOpName());
                values.put(KEY_CELL_RECEPTION_OP_COUNTRY, cr.getOpCountry());
                values.put(KEY_CELL_RECEPTION_ROAMING, cr.getRoaming());
                values.put(KEY_CELL_RECEPTION_NW_TYPE, cr.getNwType());
                break;
            case Types.WIFI:
                Wifi w = (Wifi) poi;
                values.put(KEY_WIFI_SSID, w.getSsid());
                values.put(KEY_WIFI_LEVELS, w.getLevels());
                break;
            case Types.LIFT:
                Lift l = (Lift) poi;
                values.put(KEY_LIFT_NAME, l.getName());
                break;
            case Types.PEAK:
                Peak peak = (Peak) poi;
                values.put(KEY_PEAK_NAME, peak.getName());
                break;
            case Types.HUT:
                Hut h = (Hut) poi;
                values.put(KEY_HUT_TYPE, h.getHutType());
                values.put(KEY_HUT_WINTER_ROOM, h.getWinterRoom());
                values.put(KEY_HUT_NAME, h.getName());
                break;
            case Types.GP:
                DbGeoPoint dbgp = (DbGeoPoint) poi;
                values.put(KEY_POI_LAT, dbgp.getLat());
                values.put(KEY_POI_LON, dbgp.getLon());
                values.put(KEY_POI_ALT, dbgp.getAlt());
                values.put(KEY_POI_LAT_SIN, dbgp.getLatSin());
                values.put(KEY_POI_LAT_COS, dbgp.getLatCos());
                values.put(KEY_POI_LON_SIN, dbgp.getLonSin());
                values.put(KEY_POI_LON_COS, dbgp.getLonCos());
                values.put(KEY_POI_TIME, dbgp.getTime());
                values.put(KEY_POI_EXT_ID, dbgp.get_poiId());
                values.put(KEY_POI_TYPE, dbgp.get_poiType());
                break;
            case Types.HIKE:
                Hike hike = (Hike) poi;
                values.put(KEY_HIKE_LENGTH, hike.getLength());
                values.put(KEY_HIKE_TIME, hike.getTime());
                values.put(KEY_HIKE_DIFFICULTY, hike.getDifficulty());
                values.put(KEY_HIKE_NAME, hike.getName());
                values.put(KEY_HIKE_AUTHOR, hike.getAuthor());
                values.put(KEY_HIKE_TIMESTAMP, hike.getTimestamp());
                values.put(KEY_HIKE_HEIGHT_UP, hike.getHeightUp());
                values.put(KEY_HIKE_HEIGHT_DOWN, hike.getHeightDown());
                values.put(KEY_HIKE_FLOORING, hike.getFlooring());
                break;
            case Types.BIOTA:
                Biota biota = (Biota) poi;
                values.put(KEY_BIOTA_RADIUS, biota.getRadius());
                break;
            case Types.ENDANGERED:
                Endangered end = (Endangered) poi;
                values.put(KEY_BIOTA_EXT_ID, end.getBiotaId());
                values.put(KEY_ENDANGERED_NAME, end.getName());
                values.put(KEY_ENDANGERED_IMAGE, end.getImage());
                break;
        }
        return values;
    }

    public long addPoi(Poi poi){
        SQLiteDatabase db = this.getWritableDatabase();
        String table = getTableName(poi.getType());
        ContentValues values = getValues(poi);
        long rowId = db.insert(table, null, values);
        db.close();
        return rowId;
    }

    private Poi getPoiFromCursor(Cursor cursor, int type){
        switch(type){
            case Types.FOUNTAIN:
                return getFountainFromCursor(cursor);
            case Types.PATH:
                return getPathFromCursor(cursor);
            case Types.BREAK_POINT:
                return getBreakpointFromCursor(cursor);
            case Types.TRASH_BIN:
                return getTrashbinFromCursor(cursor);
            case Types.PIC:
                return getImageFromCursor(cursor);
            case Types.CELL_RECEPTION:
                return getCellReceptionFromCursor(cursor);
            case Types.WIFI:
                return getWifiFromCursor(cursor);
            case Types.LIFT:
                return getLiftFromCursor(cursor);
            case Types.PEAK:
                return getPeakFromCursor(cursor);
            case Types.HUT:
                return getHutFromCursor(cursor);
            case Types.GP:
                return getGeoPointFromCursor(cursor);
            case Types.HIKE:
                return getHikeFromCursor(cursor);
            case Types.BIOTA:
                return getBiotaFromCursor(cursor);
            case Types.ENDANGERED:
                return getEndangeredFromCursor(cursor);
        }
        return null;
    }

    private Biota getBiotaFromCursor(Cursor cursor) {
        long id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        float radius = cursor.getFloat(cursor.getColumnIndex(KEY_BIOTA_RADIUS));
        return new Biota(id, radius);
    }

    private Biota getBiota(SQLiteDatabase db, long id) {
        Cursor cursor = db.query(TABLE_BIOTA,
                new String[]{KEY_POI_ID, KEY_BIOTA_RADIUS},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if(cursor != null) {
            cursor.moveToFirst();
        }
        return getBiotaFromCursor(cursor);
    }

    private Endangered getEndangeredFromCursor(Cursor cursor) {
        long id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        long biotaId = cursor.getLong(cursor.getColumnIndex(KEY_BIOTA_EXT_ID));
        String name = cursor.getString(cursor.getColumnIndex(KEY_ENDANGERED_NAME));
        byte[] image = cursor.getBlob(cursor.getColumnIndex(KEY_ENDANGERED_IMAGE));
        return new Endangered(id, image, biotaId, name);
    }

    private Endangered getEndangered(SQLiteDatabase db, long id) {
        Cursor cursor = db.query(TABLE_ENDANGERED,
                new String[]{KEY_POI_ID, KEY_BIOTA_EXT_ID, KEY_ENDANGERED_NAME, KEY_ENDANGERED_IMAGE},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if(cursor != null) {
            cursor.moveToFirst();
        }
        return getEndangeredFromCursor(cursor);
    }

    private Hike getHikeFromCursor(Cursor cursor) {
        long id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        float length = cursor.getFloat(cursor.getColumnIndex(KEY_HIKE_LENGTH));
        long time = cursor.getLong(cursor.getColumnIndex(KEY_HIKE_TIME));
        int difficulty = cursor.getInt(cursor.getColumnIndex(KEY_HIKE_DIFFICULTY));
        String name = cursor.getString(cursor.getColumnIndex(KEY_HIKE_NAME));
        String author = cursor.getString(cursor.getColumnIndex(KEY_HIKE_AUTHOR));
        long timestamp = cursor.getLong(cursor.getColumnIndex(KEY_HIKE_TIMESTAMP));
        float heightUp = cursor.getFloat(cursor.getColumnIndex(KEY_HIKE_HEIGHT_UP));
        float heightDown = cursor.getFloat(cursor.getColumnIndex(KEY_HIKE_HEIGHT_DOWN));
        int flooring = cursor.getInt(cursor.getColumnIndex(KEY_HIKE_FLOORING));
        return new Hike(id, length, time, difficulty, name, author, timestamp, heightUp, heightDown, flooring);
    }

    private Hike getHike(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_HIKE,
                new String[]{KEY_POI_ID, KEY_HIKE_LENGTH, KEY_HIKE_TIME, KEY_HIKE_DIFFICULTY, KEY_HIKE_NAME, KEY_HIKE_AUTHOR, KEY_HIKE_TIMESTAMP, KEY_HIKE_HEIGHT_UP, KEY_HIKE_HEIGHT_DOWN, KEY_HIKE_FLOORING},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getHikeFromCursor(cursor);
    }

    private DbGeoPoint getGeoPointFromCursor(Cursor cursor){
        long gp_id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        double lat = cursor.getDouble(cursor.getColumnIndex(KEY_POI_LAT));
        double lon = cursor.getDouble(cursor.getColumnIndex(KEY_POI_LON));
        double alt = cursor.getDouble(cursor.getColumnIndex(KEY_POI_ALT));
        double lat_sin = cursor.getDouble(cursor.getColumnIndex(KEY_POI_LAT_SIN));
        double lat_cos = cursor.getDouble(cursor.getColumnIndex(KEY_POI_LAT_COS));
        double lon_sin = cursor.getDouble(cursor.getColumnIndex(KEY_POI_LON_SIN));
        double lon_cos = cursor.getDouble(cursor.getColumnIndex(KEY_POI_LON_COS));
        long time = cursor.getLong(cursor.getColumnIndex(KEY_POI_TIME));
        long poiId = cursor.getLong(cursor.getColumnIndex(KEY_POI_EXT_ID));
        int poiType = cursor.getInt(cursor.getColumnIndex(KEY_POI_TYPE));
        return new DbGeoPoint(gp_id, lat, lon, alt, lat_sin, lat_cos, lon_sin, lon_cos, time, poiId, poiType);
    }

    private DbGeoPoint getGeoPoint(long gp_id){
        SQLiteDatabase db = getReadableDatabase();
        Cursor gpCursor = db.query(TABLE_GP,
                new String[]{KEY_POI_ID, KEY_POI_LAT, KEY_POI_LON, KEY_POI_ALT, KEY_POI_LAT_SIN, KEY_POI_LAT_COS, KEY_POI_LON_SIN, KEY_POI_LON_COS, KEY_POI_TIME, KEY_POI_EXT_ID, KEY_POI_TYPE},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(gp_id)}, null, null, null, null);
        if(gpCursor != null){
            gpCursor.moveToFirst();
            return getGeoPointFromCursor(gpCursor);
        } else {
            Log.e(MainActivity.TAG, "No geopoint with id " + gp_id + " found");
            return null;
        }
    }

    private Fountain getFountainFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        int size = cursor.getInt(cursor.getColumnIndex(KEY_FOUNTAIN_SIZE));
        return new Fountain(_id, size);
    }

    private Fountain getFountain(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_FOUNTAIN,
                new String[]{KEY_POI_ID, KEY_FOUNTAIN_SIZE},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getFountainFromCursor(cursor);
    }

    private Path getPathFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        long _length = cursor.getLong(cursor.getColumnIndex(KEY_PATH_LENGTH));
        int exposed = cursor.getInt(cursor.getColumnIndex(KEY_PATH_EXPOSED));
        int diff_wet = cursor.getInt(cursor.getColumnIndex(KEY_PATH_DIFF_WET));
        int giddiness = cursor.getInt(cursor.getColumnIndex(KEY_PATH_GIDDINESS));
        int climbing = cursor.getInt(cursor.getColumnIndex(KEY_PATH_CLIMBING));
        return new Path(_id, _length, exposed, diff_wet, giddiness, climbing);
    }

    private Path getPath(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_PATH,
                new String[]{KEY_POI_ID, KEY_PATH_LENGTH, KEY_PATH_EXPOSED, KEY_PATH_DIFF_WET, KEY_PATH_GIDDINESS, KEY_PATH_CLIMBING},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getPathFromCursor(cursor);
    }

    private BreakPoint getBreakpointFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        int stone = cursor.getInt(cursor.getColumnIndex(KEY_BREAK_POINT_STONE));
        int bench = cursor.getInt(cursor.getColumnIndex(KEY_BREAK_POINT_BENCH));
        int table = cursor.getInt(cursor.getColumnIndex(KEY_BREAK_POINT_TABLE));
        int roofed = cursor.getInt(cursor.getColumnIndex(KEY_BREAK_POINT_ROOFED));
        return new BreakPoint(_id, stone, bench, table, roofed);
    }

    private BreakPoint getBreakpoint(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_BREAK_POINT,
                new String[]{KEY_POI_ID, KEY_BREAK_POINT_STONE, KEY_BREAK_POINT_BENCH, KEY_BREAK_POINT_TABLE, KEY_BREAK_POINT_ROOFED},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getBreakpointFromCursor(cursor);
    }

    private TrashBin getTrashbinFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        return new TrashBin(_id);
    }

    private TrashBin getTrashbin(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_TRASH_BIN,
                new String[]{KEY_POI_ID},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getTrashbinFromCursor(cursor);
    }

    private Image getImageFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        long poiId = cursor.getLong(cursor.getColumnIndex(KEY_POI_EXT_ID));
        int poiType = cursor.getInt(cursor.getColumnIndex(KEY_POI_TYPE));
        byte[] img = cursor.getBlob(cursor.getColumnIndex(KEY_IMAGE_IMG));
        return new Image(_id, img, poiId, poiType);
    }

    private Image getImage(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_IMAGE,
                new String[]{KEY_POI_ID, KEY_POI_EXT_ID, KEY_POI_TYPE, KEY_IMAGE_IMG},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getImageFromCursor(cursor);
    }

    private CellReception getCellReceptionFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        int strength = cursor.getInt(cursor.getColumnIndex(KEY_CELL_RECEPTION_STRENGTH));
        String opName = cursor.getString(cursor.getColumnIndex(KEY_CELL_RECEPTION_OP_NAME));
        String opCountry = cursor.getString(cursor.getColumnIndex(KEY_CELL_RECEPTION_OP_COUNTRY));
        int roaming = cursor.getInt(cursor.getColumnIndex(KEY_CELL_RECEPTION_ROAMING));
        String nwType = cursor.getString(cursor.getColumnIndex(KEY_CELL_RECEPTION_NW_TYPE));
        return new CellReception(_id, strength, opName, opCountry, roaming, nwType);
    }

    private CellReception getCellReception(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_CELL_RECEPTION,
                new String[]{KEY_POI_ID, KEY_CELL_RECEPTION_STRENGTH, KEY_CELL_RECEPTION_OP_NAME, KEY_CELL_RECEPTION_OP_COUNTRY, KEY_CELL_RECEPTION_ROAMING, KEY_CELL_RECEPTION_NW_TYPE},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getCellReceptionFromCursor(cursor);
    }

    private Wifi getWifiFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        String ssid = cursor.getString(cursor.getColumnIndex(KEY_WIFI_SSID));
        int levels = cursor.getInt(cursor.getColumnIndex(KEY_WIFI_LEVELS));
        return new Wifi(_id, ssid, levels);
    }

    private Wifi getWifi(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_WIFI,
                new String[]{KEY_POI_ID, KEY_WIFI_SSID, KEY_WIFI_LEVELS},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getWifiFromCursor(cursor);
    }

    private Lift getLiftFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        String name = cursor.getString(cursor.getColumnIndex(KEY_LIFT_NAME));
        return new Lift(_id, name);
    }

    private Lift getLift(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_LIFT,
                new String[]{KEY_POI_ID, KEY_LIFT_NAME},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getLiftFromCursor(cursor);
    }

    private Peak getPeakFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        String name = cursor.getString(cursor.getColumnIndex(KEY_PEAK_NAME));
        return new Peak(_id, name);
    }

    private Peak getPeak(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_PEAK,
                new String[]{KEY_POI_ID, KEY_PEAK_NAME},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getPeakFromCursor(cursor);
    }

    private Hut getHutFromCursor(Cursor cursor){
        long _id = cursor.getLong(cursor.getColumnIndex(KEY_POI_ID));
        int hut_type = cursor.getInt(cursor.getColumnIndex(KEY_HUT_TYPE));
        int winterroom = cursor.getInt(cursor.getColumnIndex(KEY_HUT_WINTER_ROOM));
        String name = cursor.getString(cursor.getColumnIndex(KEY_HUT_NAME));
        return new Hut(_id, hut_type, winterroom, name);
    }

    private Hut getHut(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_HUT,
                new String[]{KEY_POI_ID, KEY_HUT_TYPE, KEY_HUT_WINTER_ROOM, KEY_HUT_NAME},
                KEY_POI_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return getHutFromCursor(cursor);
    }

    public List<Endangered> getEndageredForBiota(long biotaId) {
        List<Endangered> images = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_ENDANGERED,
                  null,
                  KEY_BIOTA_EXT_ID + "=?",
                  new String[]{String.valueOf(biotaId)},
                  null,
                  null,
                  null);
        if(cursor != null && cursor.moveToFirst()) {
            do {
                images.add(getEndangeredFromCursor(cursor));
            } while(cursor.moveToNext());
        }
        return images;
    }

    public List<DbGeoPoint> getGeoPointsForPoi(long poiId, int poiType) {
        List<DbGeoPoint> points = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_GP,
                null,
                KEY_POI_EXT_ID + "=? AND " + KEY_POI_TYPE + "=?",
                new String[]{String.valueOf(poiId), String.valueOf(poiType)},
                null,
                null,
                null);
        if(cursor != null && cursor.moveToFirst()) {
            do {
                points.add(getGeoPointFromCursor(cursor));
            } while(cursor.moveToNext());

        }
        return points;
    }

    public Poi getPoi(long id, int type){
        SQLiteDatabase db = this.getReadableDatabase();
        switch(type){
            case Types.FOUNTAIN:
                return getFountain(db, id);
            case Types.PATH:
                return getPath(db, id);
            case Types.BREAK_POINT:
                return getBreakpoint(db, id);
            case Types.TRASH_BIN:
                return getTrashbin(db, id);
            case Types.PIC:
                return getImage(db, id);
            case Types.CELL_RECEPTION:
                return getCellReception(db, id);
            case Types.WIFI:
                return getWifi(db, id);
            case Types.LIFT:
                return getLift(db , id);
            case Types.PEAK:
                return getPeak(db, id);
            case Types.HUT:
                return getHut(db, id);
            case Types.GP:
                return getGeoPoint(id);
            case Types.HIKE:
                return getHike(db, id);
            case Types.BIOTA:
                return getBiota(db, id);
            case Types.ENDANGERED:
                return getEndangered(db, id);
        }

        db.close();
        return null;
    }

    public List<Poi> getAllPois() {
        List<Poi> poiList = new ArrayList<>();
        for(int i=0; i<TABLES.length; ++i){
            String query = "SELECT * FROM " + TABLES[i];
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(query, null);
            if(cursor.moveToFirst()){
                do {
                    poiList.add(getPoiFromCursor(cursor, TABLE_TYPES[i]));
                } while(cursor.moveToNext());
            }

            db.close();
            cursor.close();
        }

        return poiList;
    }

    public int getPoiCount(){
        SQLiteDatabase db = this.getReadableDatabase();
        int count = 0;
        for(String table : TABLES){
            String query = "SELECT * FROM " + table;
            Cursor cursor = db.rawQuery(query, null);
            count += cursor.getCount();
            cursor.close();
        }

        return count;
    }

    public long updatePoi(Poi poi){
        SQLiteDatabase db = this.getWritableDatabase();
        String table = getTableName(poi.getType());
        ContentValues values = getValues(poi);
        long rowId = db.update(table, values, KEY_POI_ID + "=?",
                new String[]{String.valueOf(poi.getId())});;
        db.close();
        return rowId;
    }

    public void deletePoi(long _id, int type){
        SQLiteDatabase db = this.getWritableDatabase();
        String table = getTableName(type);
        db.delete(table, KEY_POI_ID + "=?",
                new String[]{String.valueOf(_id)});
        db.delete(TABLE_GP,
                KEY_POI_EXT_ID + "=? AND " + KEY_POI_TYPE + "=?",
                new String[]{String.valueOf(_id), String.valueOf(type)});
        db.close();
    }

    /*
    *
    * SELECT id, ( 6371 * acos( cos( radians(37) ) * cos( radians( lat ) ) *
        cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) *
        sin( radians( lat ) ) ) ) AS distance FROM your_table_name HAVING
        distance < 25 ORDER BY distance LIMIT 0 , 20;

        WHERE CUR_sin_lat * sin_lat + CUR_cos_lat * cos_lat * (cos_lng* CUR_cos_lng + sin_lng * CUR_sin_lng) > cos_allowed_distance;
    * */
    public List<Poi> getPoisAround(double lat, double lon, int limit, int type) {
        List<Poi> poisAround = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        double lat_sin = Math.sin(lat * Math.PI / 180);
        double lat_cos = Math.cos(lat * Math.PI / 180);
        double lon_sin = Math.sin(lon * Math.PI / 180);
        double lon_cos = Math.cos(lon * Math.PI / 180);
        String whereClause = null;
        String[] whereArgs = null;
        if(type >= 0) {
            whereClause = KEY_POI_TYPE + "=?";
            whereArgs = new String[]{ String.valueOf(type) };
        }
        Cursor cursor = db.query(TABLE_GP,
                null,
                whereClause,
                whereArgs,
                KEY_POI_EXT_ID + "," + KEY_POI_TYPE,
                null,
                null,
                limit == -1 ? null : String.valueOf(limit));
        if(cursor != null && cursor.getCount() > 0) {
            cursor.moveToFirst();
            do {
                DbGeoPoint dbgp = getGeoPointFromCursor(cursor);
                double dist = lat_sin * dbgp.getLatSin() + lat_cos * dbgp.getLatCos() * (dbgp.getLonCos() * lon_cos + dbgp.getLonSin() * lon_sin);
                if(dist < MAX_POI_DISTANCE_COS) continue;
                Cursor poiCursor = db.query(getTableName(dbgp.get_poiType()),
                        null,
                        KEY_POI_ID + "=?",
                        new String[]{ String.valueOf(dbgp.get_poiId()) },
                        null,
                        null,
                        null);
                if(poiCursor != null && poiCursor.getCount() > 0 && poiCursor.moveToFirst()) {
                    poisAround.add(getPoiFromCursor(poiCursor, dbgp.get_poiType()));
                }
                poiCursor.close();
            } while(cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return poisAround;
    }

    public List<Poi> getPoisAround(double lat, double lon, int limit) {
        return getPoisAround(lat, lon, limit, -1);
    }

    public List<Poi> getPoiByType(int _type){
        List<Poi> poiList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String table = getTableName(_type);

        String query = "SELECT * FROM " + table;
        Cursor cursor = db.rawQuery(query, null);
        if(cursor.moveToFirst()){
            do {
                poiList.add(getPoiFromCursor(cursor, _type));
            } while(cursor.moveToNext());
        }

        db.close();
        cursor.close();

        return poiList;
    }

    public int getPoiCountByType(int _type){
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + getTableName(_type);
        Cursor cursor = db.rawQuery(query, null);
        int count = cursor.getCount();
        cursor.close();

        return count;
    }

    private String getTableName(int _type){
        String table = "";
        switch (_type){
            case Types.FOUNTAIN:
                table = TABLE_FOUNTAIN;
                break;
            case Types.PATH:
                table = TABLE_PATH;
                break;
            case Types.BREAK_POINT:
                table = TABLE_BREAK_POINT;
                break;
            case Types.TRASH_BIN:
                table = TABLE_TRASH_BIN;
                break;
            case Types.PIC:
                table = TABLE_IMAGE;
                break;
            case Types.CELL_RECEPTION:
                table = TABLE_CELL_RECEPTION;
                break;
            case Types.WIFI:
                table = TABLE_WIFI;
                break;
            case Types.LIFT:
                table = TABLE_LIFT;
                break;
            case Types.PEAK:
                table = TABLE_PEAK;
                break;
            case Types.HUT:
                table = TABLE_HUT;
                break;
            case Types.GP:
                table = TABLE_GP;
                break;
            case Types.HIKE:
                table = TABLE_HIKE;
                break;
            case Types.BIOTA:
                table = TABLE_BIOTA;
                break;
            case Types.ENDANGERED:
                table = TABLE_ENDANGERED;
                break;
        }
        return table;
    }
}