package com.jcf.sportpager;

/**
 * Created by jon on 12/09/2016.
 */

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

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.util.Vector;

public class DatosSQLite  extends SQLiteOpenHelper
{
    private static final String TAG = "JCDBG";

    public DatosSQLite(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
    {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        String sql_stat;

        sql_stat = "DROP TABLE IF EXISTS sportpager";
        db.execSQL(sql_stat);

        sql_stat = "CREATE TABLE IF NOT EXISTS sportpager (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "tipo TEXT, " +
                "fechaPronostico INTEGER, " +
                "fechaPronosticoString TEXT, " +
                "fechaAviso INTEGER, " +
                "fechaAvisoString TEXT, " +
                "tempMin INTEGER, " +
                "tempMax INTEGER, " +
                "probLluviaMin INTEGER, " +
                "probLluviaMax INTEGER, " +
                "velVientoMin INTEGER, " +
                "velVientoMax INTEGER, " +
                "dirViento TEXT, " +
                "sinNubes INTEGER, " +
                "ParcialmenteNublado INTEGER, " +
                "Nublado INTEGER, " +
                "latitud REAL, " +
                "longitud REAL, " +
                "lugar TEXT, " +
                "resultado TEXT)";

        db.execSQL(sql_stat);

        sql_stat = "DROP TABLE IF EXISTS dispositivo";
        db.execSQL(sql_stat);

        sql_stat = "CREATE TABLE IF NOT EXISTS dispositivo (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "deviceID TEXT, " +
                "deviceIDold TEXT)";

        db.execSQL(sql_stat);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {

    }
    public boolean actualizarTipoPorId(String id, String tipo, String resultado)
    {
        long rowID;
        boolean ret = true;

        SQLiteDatabase db = getWritableDatabase();
        ContentValues updateValues = new ContentValues();

        //db.update(TABLE_LATLONG, values, " WHERE "+Loc_lati +" = " + lats+" AND "+Loc_longi+" = "+longs, null);
        updateValues.put("tipo", tipo);
        updateValues.put("resultado", resultado);
        String whereClause = "_id = '" + id + "'";
        //String whereClause = FIELD_VEN9 + " = '" + veng + "'";
        rowID = db.update("sportpager", updateValues, whereClause, null);
        if(rowID == 0)
        {
            ret = false;
        }
        else
        {
            Aplicacion.setActualizarRecibidos(true);
        }
        return ret;
    }

    public void borrarDispositivoIDs()
    {
        long rowID;
        boolean ret = true;

        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete * from 'dispositivo'");
    }

    public boolean actualizarDispositivoIDs(String id, String old)
    {
        long rowID;
        boolean ret = true;

        SQLiteDatabase db = getWritableDatabase();
        ContentValues insertValues = new ContentValues();

        insertValues.put("deviceID", id);
        insertValues.put("deviceIDold", old);
        rowID = db.insert("dispositivo", null, insertValues);
        if(rowID < 0)
        {
            ret = false;
        }
        return ret;
    }

    public int contarIDs()
    {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM 'dispositivo'", null);
        int count = cursor.getCount();

        return count;
    }

    public String getDeviceId(){
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM 'dispositivo'", null);
        cursor.moveToNext();
        String ret = cursor.getString(cursor.getColumnIndex("deviceID"));
        return ret;
    }

    public String getDeviceIdOld(){
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM 'dispositivo'", null);
        cursor.moveToNext();
        String ret = cursor.getString(cursor.getColumnIndex("deviceIDold"));
        return ret;
    }


    public long insertarSP(JSONObject sp, String tipo)
    {
        long rowID;
        boolean ret = true;

        SQLiteDatabase db = getWritableDatabase();
        ContentValues insertValues = new ContentValues();

        try
        {
            insertValues.put("tipo", tipo);
            insertValues.put("fechaPronostico", sp.getLong("fechaPronostico"));
            insertValues.put("fechaPronosticoString", sp.getString("fechaPronosticoString"));
            insertValues.put("fechaAviso", sp.getLong("fechaAviso"));
            insertValues.put("fechaAvisoString", sp.getString("fechaAvisoString"));
            insertValues.put("tempMin", sp.getLong("tempMin"));
            insertValues.put("tempMax", sp.getLong("tempMax"));
            insertValues.put("probLluviaMin", sp.getLong("probLluviaMin"));
            insertValues.put("probLluviaMax", sp.getLong("probLluviaMax"));
            insertValues.put("velVientoMin", sp.getLong("velVientoMin"));
            insertValues.put("velVientoMax", sp.getLong("velVientoMax"));
            insertValues.put("dirViento", sp.getString("dirViento"));
            insertValues.put("sinNubes", (sp.getInt("sinNubes")));
            insertValues.put("ParcialmenteNublado", (sp.getInt("ParcialmenteNublado")));
            insertValues.put("Nublado", (sp.getInt("Nublado")));
            insertValues.put("latitud", sp.getDouble("latitud"));
            insertValues.put("longitud", sp.getDouble("longitud"));
            String lugar = sp.getString("lugar");
            insertValues.put("lugar", sp.getString("lugar"));
        }
        catch (JSONException e)
        {
            e.printStackTrace();
        }

        rowID = db.insert("sportpager", null, insertValues);

        if(rowID < 0)
        {
            ret = false;
        }

        return rowID;
    }

    public int getIdRegistroMasAntiguo()
    {
        int result = -1;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT _id FROM sportpager " +
                "ORDER BY _id ASC LIMIT 1", null);

        if(cursor != null)
        {
            if(cursor.getCount() == 1)
            {
                cursor.moveToNext();
                result = cursor.getInt(0);
            }
            else
            {
                result = -1;
            }
            cursor.close();
        }

        return result;
    }

    public boolean borrarRegistro(int id)
    {
        int cantidad_borrados;
        boolean result;

        SQLiteDatabase db = getWritableDatabase();
        cantidad_borrados = db.delete("sportpager", "_id=" + id, null);
        if(cantidad_borrados == 1)
        {
            result = true;
        }
        else
        {
            result = false;
        }

        return result;
    }

    public int borrarTodosLosRegistrosDeLaBase()
    {
        boolean result = false;
        int cantidad_borrados;

        SQLiteDatabase db = getWritableDatabase();

        cantidad_borrados = db.delete("sportpager", "", null);

        return cantidad_borrados;
    }

    public void ObtenerArraySportPagers(JSONArray spa)
    {
        JSONObject JSONsp = new JSONObject();;
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursorSP = db.rawQuery("SELECT * FROM 'sportpager'", null);
        int countSP = cursorSP.getCount();

        for(int i=0; i<countSP; i++)
        {
            cursorSP.moveToNext();
            try
            {
                JSONsp.put("tipo", cursorSP.getString(cursorSP.getColumnIndex("tipo")));
                JSONsp.put("fechaPronostico", cursorSP.getLong(cursorSP.getColumnIndex("fechaPronostico")));
                JSONsp.put("fechaPronosticoString", cursorSP.getString(cursorSP.getColumnIndex("fechaPronosticoString")));
                JSONsp.put("fechaAviso", cursorSP.getLong(cursorSP.getColumnIndex("fechaAviso")));
                JSONsp.put("fechaAvisoString", cursorSP.getString(cursorSP.getColumnIndex("fechaAvisoString")));
                JSONsp.put("tempMin", cursorSP.getLong(cursorSP.getColumnIndex("tempMin")));
                JSONsp.put("tempMax", cursorSP.getLong(cursorSP.getColumnIndex("tempMax")));
                JSONsp.put("probLluviaMin", cursorSP.getLong(cursorSP.getColumnIndex("probLluviaMin")));
                JSONsp.put("probLluviaMax", cursorSP.getLong(cursorSP.getColumnIndex("probLluviaMax")));
                JSONsp.put("velVientoMin", cursorSP.getLong(cursorSP.getColumnIndex("velVientoMin")));
                JSONsp.put("velVientoMax", cursorSP.getLong(cursorSP.getColumnIndex("velVientoMax")));
                JSONsp.put("dirViento", cursorSP.getString(cursorSP.getColumnIndex("dirViento")));
                JSONsp.put("sinNubes", cursorSP.getLong(cursorSP.getColumnIndex("sinNubes")));
                JSONsp.put("ParcialmenteNublado", cursorSP.getLong(cursorSP.getColumnIndex("ParcialmenteNublado")));
                JSONsp.put("Nublado", cursorSP.getLong(cursorSP.getColumnIndex("Nublado")));
                JSONsp.put("latitud", cursorSP.getDouble(cursorSP.getColumnIndex("latitud")));
                JSONsp.put("longitud", cursorSP.getDouble(cursorSP.getColumnIndex("longitud")));
                JSONsp.put("lugar", cursorSP.getString(cursorSP.getColumnIndex("lugar")));
                JSONsp.put("resultado", cursorSP.getString(cursorSP.getColumnIndex("resultado")));
            }
            catch (JSONException e)
            {
                e.printStackTrace();
            }
            SportPager sp = new SportPager(JSONsp);
            spa.put(sp);
        }
    }


    public void ObtenerSportPagers(Vector<SportPager> spv, String tipo)
    {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursorSP = db.rawQuery("SELECT * FROM 'sportpager' WHERE tipo = '" + tipo + "'", null);
        int countSP = cursorSP.getCount();

        for(int i=0; i<countSP; i++)
        {
            JSONObject JSONsp = new JSONObject();;
            cursorSP.moveToNext();
            try
            {
                JSONsp.put("rowId", cursorSP.getInt(cursorSP.getColumnIndex("_id")));
                JSONsp.put("tipo", cursorSP.getString(cursorSP.getColumnIndex("tipo")));
                JSONsp.put("fechaPronostico", cursorSP.getLong(cursorSP.getColumnIndex("fechaPronostico")));
                JSONsp.put("fechaPronosticoString", cursorSP.getString(cursorSP.getColumnIndex("fechaPronosticoString")));
                JSONsp.put("fechaAviso", cursorSP.getLong(cursorSP.getColumnIndex("fechaAviso")));
                JSONsp.put("fechaAvisoString", cursorSP.getString(cursorSP.getColumnIndex("fechaAvisoString")));
                JSONsp.put("tempMin", cursorSP.getLong(cursorSP.getColumnIndex("tempMin")));
                JSONsp.put("tempMax", cursorSP.getLong(cursorSP.getColumnIndex("tempMax")));
                JSONsp.put("probLluviaMin", cursorSP.getLong(cursorSP.getColumnIndex("probLluviaMin")));
                JSONsp.put("probLluviaMax", cursorSP.getLong(cursorSP.getColumnIndex("probLluviaMax")));
                JSONsp.put("velVientoMin", cursorSP.getLong(cursorSP.getColumnIndex("velVientoMin")));
                JSONsp.put("velVientoMax", cursorSP.getLong(cursorSP.getColumnIndex("velVientoMax")));
                JSONsp.put("dirViento", cursorSP.getString(cursorSP.getColumnIndex("dirViento")));
                JSONsp.put("sinNubes", cursorSP.getLong(cursorSP.getColumnIndex("sinNubes")));
                JSONsp.put("ParcialmenteNublado", cursorSP.getLong(cursorSP.getColumnIndex("ParcialmenteNublado")));
                JSONsp.put("Nublado", cursorSP.getLong(cursorSP.getColumnIndex("Nublado")));
                JSONsp.put("latitud", cursorSP.getDouble(cursorSP.getColumnIndex("latitud")));
                JSONsp.put("longitud", cursorSP.getDouble(cursorSP.getColumnIndex("longitud")));
                JSONsp.put("lugar", cursorSP.getString(cursorSP.getColumnIndex("lugar")));
                JSONsp.put("resultado", cursorSP.getString(cursorSP.getColumnIndex("resultado")));
            }
            catch (JSONException e)
            {
                e.printStackTrace();
            }
            Aplicacion.addSportPager(JSONsp);
        }
    }

    public boolean eliminarSP(int rowId)
    {
        boolean ret = false;
        int count = 0;

        SQLiteDatabase db = getWritableDatabase();
        String whereClause = "_id = '" + rowId + "'";

        try
        {
            count = db.delete("sportpager", whereClause, null);
        }
        catch (Exception e)
        {
            Log.d(TAG, "eliminarSP NOK: " + e.toString());
        }

        if(count == 1)
        {
            ret = true;
            Log.d(TAG, "eliminarSP OK");
        }
        else
        {
            Log.d(TAG, "eliminarSP falló delete. rows = " + count);
        }

        return ret;
    }
}
