/**
 * 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;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;

public class WeatherDatabase extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "weathers.db";
    private static final String TABLE_WEATHER = "weather";
    private static final String TABLE_LOCATION = "locations";
    private static final String TABLE_WIND = "winds";

    private static final String KEY_ID = "_id";
    private static final String KEY_TIMESTAMP = "_timestamp";
    private static final String KEY_TEMP_MIN = "_temp_min";
    private static final String KEY_TEMP_MAX = "_temp_max";
    private static final String KEY_LOCATION_ID = "_loc_id";
    private static final String KEY_SUNRISE = "_sunrise";
    private static final String KEY_SUNSET = "_sunset";
    private static final String KEY_WEATHER_ID = "_weather_id";
    private static final String KEY_WEATHER_CODE = "_weather_code";
    private static final String KEY_PRESSURE = "_pressure";
    private static final String KEY_HUMIDITY = "_humidity";
    private static final String KEY_MAIN = "_main";
    private static final String KEY_DESC = "_desc";
    private static final String KEY_WIND_ID = "_wind_id";
    private static final String KEY_RAIN = "_rain"; //Rain volume for the last 3 hours
    private static final String KEY_SNOW = "_snow"; //Snow volume for the last 3 hours
    private static final String KEY_CLOUDS = "_clouds"; //cloudiness in %

    private static final String KEY_LAT = "_lat";
    private static final String KEY_LON = "_lon";
    private static final String KEY_NAME = "_name";
    private static final String KEY_COUNTRY = "_country";

    private static final String KEY_SPEED = "_speed";
    private static final String KEY_DIR = "_dir";

    private static final float MAX_WEATHER_DISTANCE = 5.0f; // max distance in km

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_WEATHER_TABLE = "CREATE TABLE " + TABLE_WEATHER + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                +  KEY_TIMESTAMP + " INTEGER,"
                +  KEY_TEMP_MIN + " REAL,"
                +  KEY_TEMP_MAX + " REAL,"
                +  KEY_LOCATION_ID + " INTEGER,"
                +  KEY_SUNRISE + " INTEGER,"
                +  KEY_SUNSET + " INTEGER,"
                +  KEY_WEATHER_ID + " INTEGER,"
                +  KEY_WEATHER_CODE + " INTEGER,"
                +  KEY_PRESSURE + " REAL,"
                +  KEY_HUMIDITY + " REAL,"
                +  KEY_MAIN + " TEXT,"
                +  KEY_DESC + " TEXT,"
                +  KEY_WIND_ID + " INTEGER,"
                +  KEY_RAIN + " REAL,"
                +  KEY_SNOW + " REAL,"
                +  KEY_CLOUDS + " INTEGER" + ")";
        db.execSQL(CREATE_WEATHER_TABLE);
        String CREATE_LOCATION_TABLE = "CREATE TABLE " + TABLE_LOCATION + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_LAT + " REAL,"
                + KEY_LON + " REAL,"
                + KEY_NAME + " TEXT,"
                + KEY_COUNTRY + " TEXT" + ")";
        db.execSQL(CREATE_LOCATION_TABLE);
        String CREATE_WIND_TABLE = "CREATE TABLE " + TABLE_WIND + "("
                + KEY_ID + " INTEGER PRIMARY KEY,"
                + KEY_SPEED + " REAL,"
                + KEY_DIR + " REAL" + ")";
        db.execSQL(CREATE_WIND_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //TODO, not part of core
    }

    private MyWeather addLocationToWeather(Cursor cursor, MyWeather weather) {
        Location l = new Location("");
        l.setLatitude(cursor.getDouble(cursor.getColumnIndex(KEY_LAT)));
        l.setLongitude(cursor.getDouble(cursor.getColumnIndex(KEY_LON)));
        weather.setCity(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
        weather.setCountry(cursor.getString(cursor.getColumnIndex(KEY_COUNTRY)));
        weather.setLocation(l);
        return weather;
    }

    private MyWeather addWindToWeather(Cursor cursor, MyWeather weather) {
        weather.setWindSpeed(cursor.getFloat(cursor.getColumnIndex(KEY_SPEED)));
        weather.setWindDir(cursor.getFloat(cursor.getColumnIndex(KEY_DIR)));
        return weather;
    }

    private MyWeather addWeatherToWeather(Cursor cursor, MyWeather weather) {
        weather.setTimestamp(cursor.getLong(cursor.getColumnIndex(KEY_TIMESTAMP)));
        weather.setMinTemp(cursor.getFloat(cursor.getColumnIndex(KEY_TEMP_MIN)));
        weather.setMaxTemp(cursor.getFloat(cursor.getColumnIndex(KEY_TEMP_MAX)));
        weather.setSunrise(cursor.getLong(cursor.getColumnIndex(KEY_SUNRISE)));
        weather.setSunset(cursor.getLong(cursor.getColumnIndex(KEY_SUNSET)));
        weather.setWeatherId(cursor.getInt(cursor.getColumnIndex(KEY_WEATHER_ID)));
        weather.setWeatherCode(cursor.getInt(cursor.getColumnIndex(KEY_WEATHER_CODE)));
        weather.setPressure(cursor.getFloat(cursor.getColumnIndex(KEY_PRESSURE)));
        weather.setHumidity(cursor.getFloat(cursor.getColumnIndex(KEY_HUMIDITY)));
        weather.setWeatherTitle(cursor.getString(cursor.getColumnIndex(KEY_MAIN)));
        weather.setWeatherDesc(cursor.getString(cursor.getColumnIndex(KEY_DESC)));
        weather.setRain(cursor.getFloat(cursor.getColumnIndex(KEY_RAIN)));
        weather.setSnow(cursor.getFloat(cursor.getColumnIndex(KEY_SNOW)));
        weather.setClouds(cursor.getInt(cursor.getColumnIndex(KEY_CLOUDS)));
        return weather;
    }

    private long getId(Cursor cursor) {
        return cursor.getLong(cursor.getColumnIndex(KEY_ID));
    }

    private long getWindId(Cursor cursor) {
        return cursor.getLong(cursor.getColumnIndex(KEY_WIND_ID));
    }

    public long insertWeather(MyWeather weather) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = getLocationValues(weather);
        long locId = db.insert(TABLE_LOCATION, null, values);
        values = getWindValues(weather);
        long windId = db.insert(TABLE_WIND, null, values);
        values = getWeatherValues(weather);
        values.put(KEY_LOCATION_ID, locId);
        values.put(KEY_WIND_ID, windId);
        long rowId = db.insert(TABLE_WEATHER, null, values);
        db.close();
        return rowId;
    }

    public MyWeather getWeather(double lat, double lon, long timestamp, int offset) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor locCursor = db.query(TABLE_LOCATION,
                new String[]{"*", "(6371 * acos(cos(radians(" + lat + ")) * cos(radians(" + KEY_LAT + ")) * cos(radians(" + KEY_LON + ") - radians(" + lon + ")) + sin(radians(" + lat + ")) * sin(radians(" + KEY_LAT + ")))) AS distance"},
                null,
                null,
                null,
                "distance < " + MAX_WEATHER_DISTANCE, //having
                "distance", //orderby
                "1"); //only get closest entry
        MyWeather weather = new MyWeather();
        if(locCursor == null) {
            return null;
        }
        locCursor.moveToFirst();
        long locId = getId(locCursor);
        weather = addLocationToWeather(locCursor, weather);
        locCursor.close();

        Cursor weatherCursor = db.query(TABLE_WEATHER,
                null,
                "WHERE " + KEY_LOCATION_ID + " =? AND ABS(" + KEY_TIMESTAMP + "-?) < ?",
                new String[]{String.valueOf(locId), String.valueOf(timestamp), String.valueOf(offset*1000*60*60)}, null, null, null, "1");
        if(weatherCursor == null) {
            return null;
        }
        weatherCursor.moveToFirst();
        weather = addWeatherToWeather(weatherCursor, weather);
        long windId = getWindId(weatherCursor);
        weatherCursor.close();

        Cursor windCursor = db.query(TABLE_WIND,
                null,
                "WHERE " + KEY_ID + " =?",
                new String[]{String.valueOf(windId)}, null, null, null, "1");
        if(windCursor == null) {
            return null;
        }
        windCursor.moveToFirst();
        weather = addWindToWeather(windCursor, weather);
        windCursor.close();
        db.close();

        return weather;
    }

    public MyWeather getWeather(double lat, double lon, int offset) {
        return getWeather(lat, lon, System.currentTimeMillis(), offset);
    }

    private ContentValues getLocationValues(MyWeather weather){
        ContentValues values = new ContentValues();
        values.put(KEY_LAT, weather.getLocation().getLatitude());
        values.put(KEY_LON, weather.getLocation().getLongitude());
        values.put(KEY_NAME, weather.getCity());
        return values;
    }

    private ContentValues getWindValues(MyWeather weather){
        ContentValues values = new ContentValues();
        values.put(KEY_SPEED, weather.getWindSpeed());
        values.put(KEY_DIR, weather.getWindDir());
        return values;
    }

    private ContentValues getWeatherValues(MyWeather weather){
        ContentValues values = new ContentValues();
        values.put(KEY_TIMESTAMP, weather.getTimestamp());
        values.put(KEY_TEMP_MIN, weather.getMinTemp());
        values.put(KEY_TEMP_MAX, weather.getMaxTemp());
        values.put(KEY_SUNRISE, weather.getSunrise());
        values.put(KEY_SUNSET, weather.getSunset());
        values.put(KEY_WEATHER_ID, weather.getWeatherId());
        values.put(KEY_WEATHER_CODE, weather.getWeatherCode());
        values.put(KEY_PRESSURE, weather.getPressure());
        values.put(KEY_HUMIDITY, weather.getHumidity());
        values.put(KEY_MAIN, weather.getWeatherTitle());
        values.put(KEY_DESC, weather.getWeatherDesc());
        values.put(KEY_RAIN, weather.getRain());
        values.put(KEY_SNOW, weather.getSnow());
        values.put(KEY_CLOUDS, weather.getClouds());
        return values;
    }
}