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.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;

/**
 * Created by Vinz on 06.10.2015.
 */
public class DatabaseHandler extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 2;
    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[] 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};
    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};

    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_FOUNTAIN_SIZE = "_size";
    private static final String KEY_GP_S_ID = "_gp_s_id";
    private static final String KEY_GP_E_ID = "_gp_e_id";
    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_IMAGE_ASSOC = "_associated_poi";

    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_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_GP_ID + " INTEGER,"
                + 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_GP_S_ID + " INTEGER,"
                + KEY_GP_E_ID + " INTEGER,"
                + 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_GP_ID + " INTEGER,"
                + 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_GP_ID + " INTEGER,"
                + 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_GP_ID + " INTEGER,"
                + 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_GP_ID + " INTEGER,"
                + 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_GP_ID + " INTEGER,"
                + 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_GP_S_ID + " INTEGER,"
                + KEY_GP_E_ID + " INTEGER,"
                + 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_GP_ID + " INTEGER,"
                + KEY_IMAGE_IMG + " BLOB,"
                + KEY_IMAGE_ASSOC + " INTEGER" + ")";
        db.execSQL(CREATE_IMAGE_TABLE);
        //create trash bin table
        String CREATE_TRASH_TABLE = "CREATE TABLE " + TABLE_TRASH_BIN + "("
                + KEY_POI_ID + " INTEGER PRIMARY KEY,"
                + KEY_GP_ID + " INTEGER" + ")";
        db.execSQL(CREATE_TRASH_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);
        onCreate(db);
    }

    private ContentValues getValues(Poi poi){
        ContentValues values = new ContentValues();
        switch(poi.getType()){
            case Types.FOUNTAIN:
                Fountain f = (Fountain) poi;
                values.put(KEY_GP_ID, f.getGpId());
                values.put(KEY_FOUNTAIN_SIZE, f.getSize());
                break;
            case Types.PATH:
                Path p = (Path) poi;
                values.put(KEY_GP_S_ID, p.getStartGpId());
                values.put(KEY_GP_E_ID, p.getEndGpId());
                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_GP_ID, bp.getGpId());
                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;
                values.put(KEY_GP_ID, tb.getGpId());
                break;
            case Types.PIC:
                Image i = (Image) poi;
                values.put(KEY_GP_ID, i.getGpId());
                values.put(KEY_IMAGE_IMG, i.getImage());
                break;
            case Types.CELL_RECEPTION:
                CellReception cr = (CellReception) poi;
                values.put(KEY_GP_ID, cr.getGpId());
                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_GP_ID, w.getGpId());
                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_GP_S_ID, l.getStartGpId());
                values.put(KEY_GP_E_ID, l.getEndGpId());
                values.put(KEY_LIFT_NAME, l.getName());
                break;
            case Types.PEAK:
                Peak peak = (Peak) poi;
                values.put(KEY_GP_ID, peak.getGpId());
                values.put(KEY_PEAK_NAME, peak.getName());
                break;
            case Types.HUT:
                Hut h = (Hut) poi;
                values.put(KEY_GP_ID, h.getGpId());
                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());
                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);
        }
        return null;
    }

    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;
        return new DbGeoPoint(gp_id, lat, lon, alt, time);
    }

    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_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);
        long _gp_id = cursor.getLong(1);
        int size = cursor.getInt(2);
        return new Fountain(_id, size, getGeoPoint(_gp_id));
    }

    private Fountain getFountain(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_FOUNTAIN,
                new String[]{KEY_POI_ID, KEY_GP_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 _gp_s_id = cursor.getLong(1);
        long _gp_e_id = cursor.getLong(2);
        long _length = cursor.getLong(3);
        int exposed = cursor.getInt(4);
        int diff_wet = cursor.getInt(5);
        int giddiness = cursor.getInt(6);
        int climbing = cursor.getInt(7);
        return new Path(_id, _length, exposed, diff_wet, giddiness, climbing, getGeoPoint(_gp_s_id), getGeoPoint(_gp_e_id));
    }

    private Path getPath(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_PATH,
                new String[]{KEY_POI_ID, KEY_GP_S_ID, KEY_GP_E_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);
        long _gp_id = cursor.getLong(1);
        int stone = cursor.getInt(2);
        int bench = cursor.getInt(3);
        int table = cursor.getInt(4);
        int roofed = cursor.getInt(5);
        return new BreakPoint(_id, stone, bench, table, roofed, getGeoPoint(_gp_id));
    }

    private BreakPoint getBreakpoint(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_BREAK_POINT,
                new String[]{KEY_POI_ID, KEY_GP_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);
        long _gp_id = cursor.getLong(1);
        return new TrashBin(_id, getGeoPoint(_gp_id));
    }

    private TrashBin getTrashbin(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_TRASH_BIN,
                new String[]{KEY_POI_ID, KEY_GP_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 _gp_id = cursor.getLong(1);
        byte[] img = cursor.getBlob(2);
        int assoc_poi = cursor.getInt(3);
        return new Image(_id, img, assoc_poi, getGeoPoint(_gp_id));
    }

    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_IMAGE_ASSOC},
                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);
        long _gp_id = cursor.getLong(1);
        int strength = cursor.getInt(2);
        String opName = cursor.getString(3);
        String opCountry = cursor.getString(4);
        int roaming = cursor.getInt(5);
        String nwType = cursor.getString(6);
        return new CellReception(_id, strength, opName, opCountry, roaming, nwType, getGeoPoint(_gp_id));
    }

    private CellReception getCellReception(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_CELL_RECEPTION,
                new String[]{KEY_POI_ID, KEY_GP_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);
        long _gp_id = cursor.getLong(1);
        String ssid = cursor.getString(2);
        int levels = cursor.getInt(3);
        return new Wifi(_id, ssid, levels, getGeoPoint(_gp_id));
    }

    private Wifi getWifi(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_WIFI,
                new String[]{KEY_POI_ID, KEY_GP_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);
        long _gp_s_id = cursor.getLong(1);
        long _gp_e_id = cursor.getLong(2);
        String name = cursor.getString(3);
        return new Lift(_id, name, getGeoPoint(_gp_s_id), getGeoPoint(_gp_e_id));
    }

    private Lift getLift(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_LIFT,
                new String[]{KEY_POI_ID, KEY_GP_S_ID, KEY_GP_E_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);
        long _gp_id = cursor.getLong(1);
        String name = cursor.getString(2);
        return new Peak(_id, name, getGeoPoint(_gp_id));
    }

    private Peak getPeak(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_PEAK,
                new String[]{KEY_POI_ID, KEY_GP_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);
        long _gp_id = cursor.getLong(1);
        int hut_type = cursor.getInt(2);
        int winterroom = cursor.getInt(3);
        String name = cursor.getString(4);
        return new Hut(_id, hut_type, winterroom, name, getGeoPoint(_gp_id));
    }

    private Hut getHut(SQLiteDatabase db, long id){
        Cursor cursor = db.query(TABLE_HUT,
                new String[]{KEY_POI_ID, KEY_GP_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 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);
        }

        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(int i=0; i<TABLES.length; ++i){
            String query = "SELECT * FROM " + TABLES[i];
            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;
    }

    //TODO recursive delete
    public void deletePoi(long _id, int type){
        SQLiteDatabase db = this.getWritableDatabase();
        switch (type){
            case Types.FOUNTAIN:
                db.delete(TABLE_FOUNTAIN, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.PATH:
                db.delete(TABLE_PATH, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.BREAK_POINT:
                db.delete(TABLE_BREAK_POINT, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.TRASH_BIN:
                db.delete(TABLE_TRASH_BIN, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.PIC:
                db.delete(TABLE_IMAGE, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.CELL_RECEPTION:
                db.delete(TABLE_CELL_RECEPTION, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.WIFI:
                db.delete(TABLE_WIFI, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.LIFT:
                db.delete(TABLE_LIFT, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.PEAK:
                db.delete(TABLE_PEAK, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.HUT:
                db.delete(TABLE_HUT, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
            case Types.GP:
                db.delete(TABLE_GP, KEY_POI_ID + "=?",
                        new String[]{String.valueOf(_id)});
                break;
        }
    }

    /*
    *
    * 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(int i=0; i<TABLES.length; ++i){
            String table = TABLES[i];
            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;
                    if(table.equals(TABLE_FOUNTAIN)){
                        poi = getFountainFromCursor(cursor);
                    } else if(table.equals(TABLE_PATH)){
                        poi = getPathFromCursor(cursor);
                    } else if(table.equals(TABLE_BREAK_POINT)){
                        poi = getBreakpointFromCursor(cursor);
                    } else if(table.equals(TABLE_TRASH_BIN)){
                        poi = getTrashbinFromCursor(cursor);
                    } else if(table.equals(TABLE_CELL_RECEPTION)){
                        poi = getCellReceptionFromCursor(cursor);
                    } else if(table.equals(TABLE_WIFI)){
                        poi = getWifiFromCursor(cursor);
                    } else if(table.equals(TABLE_LIFT)){
                        poi = getLiftFromCursor(cursor);
                    } else if(table.equals(TABLE_PEAK)){
                        poi = getPeakFromCursor(cursor);
                    } else if(table.equals(TABLE_HUT)){
                        poi = getHutFromCursor(cursor);
                    }
                    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;
        }
        return table;
    }
}