/**
 * 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.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 = 4;
    private static final float MAX_POI_DISTANCE = 12.5f;

    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[] 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};
    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};

    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_GP_ID = "_gp_id";
    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 = "_hike_height";

    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_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 + " INTEGER" + ")";
        db.execSQL(CREATE_HIKE_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);
        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_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, hike.getHeight());
                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);
        }
        return null;
    }

    private Hike getHikeFromCursor(Cursor cursor) {
        long id = cursor.getLong(0);
        float length = cursor.getFloat(1);
        long time = cursor.getLong(2);
        int difficulty = cursor.getInt(3);
        String name = cursor.getString(4);
        String author = cursor.getString(5);
        long timestamp = cursor.getLong(6);
        float height = cursor.getFloat(7);
        return new Hike(id, length, time, difficulty, name, author, timestamp, height);
    }

    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},
                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(0);
        double lat = cursor.getDouble(1);
        double lon = cursor.getDouble(2);
        double alt = cursor.getDouble(3);
        long time = cursor.getLong(4) * 1000;
        long poiId = cursor.getLong(5);
        int poiType = cursor.getInt(6);
        return new DbGeoPoint(gp_id, lat, lon, alt, 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_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(0);
        int size = cursor.getInt(1);
        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(0);
        long _length = cursor.getLong(1);
        int exposed = cursor.getInt(2);
        int diff_wet = cursor.getInt(3);
        int giddiness = cursor.getInt(4);
        int climbing = cursor.getInt(5);
        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(0);
        int stone = cursor.getInt(1);
        int bench = cursor.getInt(2);
        int table = cursor.getInt(3);
        int roofed = cursor.getInt(4);
        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(0);
        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(0);
        long poiId = cursor.getLong(1);
        int poiType = cursor.getInt(2);
        byte[] img = cursor.getBlob(3);
        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_GP_ID, 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(0);
        int strength = cursor.getInt(1);
        String opName = cursor.getString(2);
        String opCountry = cursor.getString(3);
        int roaming = cursor.getInt(4);
        String nwType = cursor.getString(5);
        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(0);
        String ssid = cursor.getString(1);
        int levels = cursor.getInt(2);
        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(0);
        String name = cursor.getString(3);
        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(0);
        String name = cursor.getString(1);
        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(0);
        int hut_type = cursor.getInt(1);
        int winterroom = cursor.getInt(2);
        String name = cursor.getString(3);
        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<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);
        }

        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;
    * */
    public List<Poi> getPoisAround(double lat, double lon, int limit) {
        List<Poi> poisAround = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        for(String table : TABLES){
            Cursor cursor = db.query(table,
                    new String[]{"*", "(6371 * acos(cos(radians(" + lat + ")) * cos(radians(" + KEY_POI_LAT + ")) * cos(radians(" + KEY_POI_LON + ") - radians(" + lon + ")) + sin(radians(" + lat + ")) * sin(radians(" + KEY_POI_LAT + ")))) AS distance"},
                    null,
                    null,
                    null,
                    "distance < " + MAX_POI_DISTANCE, //having
                    "distance", //orderby
                    limit == -1 ? null : String.valueOf(limit)); //limit
            if(cursor != null) {
                cursor.moveToFirst();
                do {
                    Poi poi = null;
                    switch (table) {
                        case TABLE_FOUNTAIN:
                            poi = getFountainFromCursor(cursor);
                            break;
                        case TABLE_PATH:
                            poi = getPathFromCursor(cursor);
                            break;
                        case TABLE_BREAK_POINT:
                            poi = getBreakpointFromCursor(cursor);
                            break;
                        case TABLE_TRASH_BIN:
                            poi = getTrashbinFromCursor(cursor);
                            break;
                        case TABLE_CELL_RECEPTION:
                            poi = getCellReceptionFromCursor(cursor);
                            break;
                        case TABLE_WIFI:
                            poi = getWifiFromCursor(cursor);
                            break;
                        case TABLE_LIFT:
                            poi = getLiftFromCursor(cursor);
                            break;
                        case TABLE_PEAK:
                            poi = getPeakFromCursor(cursor);
                            break;
                        case TABLE_HUT:
                            poi = getHutFromCursor(cursor);
                            break;
                    }
                    poisAround.add(poi);
                } while(cursor.moveToNext());
                cursor.close();
            }
        }
        db.close();
        return poisAround;
    }

    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;
        }
        return table;
    }
}