package com.jkanetwork.st.calendariodefrases;

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

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 9;
    private static final String DATABASE_NAME = "database.db";
    private final Context contextdb;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        contextdb = context;
    }

    /** Return the sentence passing the ID of it */
    public String getFraseFromID(int IDF){
        SQLiteDatabase db = this.getWritableDatabase();
        String sentence;

        if (IDF == 0){return "No hay más frases disponibles...";}
        else if (IDF == -1){return "No puedes ver aún esta frase";}
        else if (IDF == -2){return "A ocurrido un error";}
        else { //If there is a sentence

            try {
                Cursor c = db.rawQuery("SELECT Frase FROM frases WHERE _id = '" + IDF + "'", null);
                c.moveToFirst();
                sentence = c.getString(0);
                c.close();
            } catch (Exception e) {
                Log.e("Database", e.toString());
                sentence = "Error al buscar la frase";
            }
            return sentence;
        }
    }

    public String getAutorFromID(int IDF){
        SQLiteDatabase db = this.getWritableDatabase();
        String sentence;

        if (IDF <= 0){return "";}
        else { //If there is a sentence
            try {
                Cursor c = db.rawQuery("SELECT Autor FROM autores a WHERE _id=(SELECT AutorID from frases WHERE _id='"+ IDF + "')",null);
                c.moveToFirst();
                sentence = c.getString(0);
                c.close();
            } catch (Exception e) {
                Log.e("Database", e.getMessage());
                sentence = "";
            }
            return sentence;
        }
    }


    /** Add a sentence as favorite */
    public void addFav(int IDF){
        SQLiteDatabase db = this.getWritableDatabase();
        try{
            db.execSQL("INSERT INTO frasesFav VALUES('"+IDF+"')");
        }catch(Exception e){
            Log.e("Database",e.toString());
        }
    }

    /** Del a favorite sentence */
    public void delFav(int IDF){
        SQLiteDatabase db = this.getWritableDatabase();
        try{
            db.execSQL("DELETE FROM frasesFav WHERE IDFrase='"+IDF+"'");
        }catch(Exception e){
            Log.e("Database",e.toString());
        }
    }

    /** Returns ID from a day X sentence, if possible */
    public Integer getIDFraseDay(int year,int month,int dayOfMonth){
        Calendar cal = Calendar.getInstance();
        try {
            Integer ret;
            SQLiteDatabase db = this.getWritableDatabase();

            /* For ever have two digits month and day */
            String sYear = Integer.toString(year);
            String sMonth; /* month selected */
            String sDay; /* day selected */
            if (month < 10) {
                sMonth = "0" + Integer.toString(month);
            }else{
                sMonth = Integer.toString(month);
            }
            if (dayOfMonth < 10) {
                sDay = "0" + Integer.toString(dayOfMonth);
            }else{
                sDay = Integer.toString(dayOfMonth);
            }

            /* Same for calendar day */
            String cYear = Integer.toString(cal.get(Calendar.YEAR));
            String cMonth; /* Calendar month */
            String cDay; /* Calendar day */
            if ((cal.get(Calendar.MONTH)+1) < 10) {
                cMonth = "0" + Integer.toString(cal.get(Calendar.MONTH)+1);
            }else{
                cMonth = Integer.toString(cal.get(Calendar.MONTH)+1);
            }
            if (cal.get(Calendar.DAY_OF_MONTH) < 10) {
                cDay = "0" + Integer.toString(cal.get(Calendar.DAY_OF_MONTH));
            }else{
                cDay = Integer.toString(cal.get(Calendar.DAY_OF_MONTH));
            }

            String diaArg = sYear+sMonth+sDay; //Dia pasado a string yyyymmdd
            String diaAct = cYear+cMonth+cDay;
            Cursor query = db.rawQuery("SELECT IDFrase FROM frasesG WHERE Tday = '"+diaArg+"'", null);
            if(query.getCount() != 0) {
                query.moveToFirst();
                ret = query.getInt(0);
                query.close();
                return ret;
            //Else for make sentence if there is today or someday before has a sentence (This is possible if app was installed but not opened)
            }else if ((Integer.decode(diaArg) < Integer.decode(diaAct) && db.rawQuery("SELECT IDFrase FROM frasesG WHERE Tday < '"+diaArg+"'", null).getCount() > 0) || ( Integer.decode(diaArg).equals(Integer.decode(diaAct)) ) ) {
            //}else{ //Make sentence all times (DEBUG)
                try {
                    query = db.rawQuery("SELECT _id FROM frases WHERE _id NOT IN (SELECT IDFrase FROM frasesG) ORDER BY RANDOM() LIMIT 1", null);
                    query.moveToFirst();
                    db.rawQuery("INSERT INTO frasesG (Tday,IDFrase) VALUES('" + diaArg + "','" + query.getString(0) + "')", null).moveToFirst();
                    ret = query.getInt(0);
                    return ret;
                } catch (Exception e) {
                    return 0; //0 its no more sentences
                }
                }else{ //Not today, do not make one
                    return -1; //-1 its day not accesible now
                }
        } catch (Exception e) {
            Log.e("Database",e.toString());
            return -2; //BUG
        }
    }

    /** Returns ID of today's sentence */
    public Integer getIDFraseHoy(){
        Calendar cal = Calendar.getInstance();
        //Month + 1 because Android starts with month=0
        return getIDFraseDay(cal.get(Calendar.YEAR),cal.get(Calendar.MONTH)+1,cal.get(Calendar.DAY_OF_MONTH));
    }

    /** Returns an array of favorite IDs */
    public Integer[] getIDFavs(){
        SQLiteDatabase db = this.getWritableDatabase();
        List<Integer> a = new ArrayList<>();

        Cursor c = db.rawQuery("SELECT IDFrase FROM frasesFav",null);

        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
            a.add(c.getInt(0));
        }

        Integer[] Favorites = new Integer[a.size()];
        a.toArray(Favorites);
        c.close();
        return Favorites;
    }

    /** Returns an array of favorite IDs */
    public String[] getAllDaysYet(){
        SQLiteDatabase db = this.getWritableDatabase();
        List<String> a = new ArrayList<>();

        Cursor c = db.rawQuery("SELECT Tday FROM frasesG",null);

        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
            a.add(c.getString(0));
        }

        String[] Favorites = new String[a.size()];
        a.toArray(Favorites);
        c.close();
        return Favorites;
    }


    /* Here Startup of database. When changes, change number up in class */

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE frases (" +
                "`_id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                "`Frase` TEXT NOT NULL UNIQUE," +
                "`AutorID` INTEGER" +
                ")");
        db.execSQL("CREATE TABLE autores (" +
                "`_id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                "`Autor` TEXT UNIQUE" +
                ")");
        db.execSQL("CREATE TABLE frasesG (" +
                "`_id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                "`Tday` TEXT NOT NULL UNIQUE," +
                "`IDFrase` INTEGER NOT NULL" +
                ")");
        db.execSQL("CREATE TABLE frasesFav (" +
                "`IDFrase` INTEGER NOT NULL UNIQUE" +
                ")");
        db.execSQL(readSqldata("Frases"));
        db.execSQL(readSqldata("Autores"));
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE frases");
        db.execSQL("DROP TABLE autores");
        db.execSQL("CREATE TABLE frases (" +
                "`_id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                "`Frase` TEXT NOT NULL UNIQUE," +
                "`AutorID` INTEGER" +
                ")");
        db.execSQL("CREATE TABLE autores (" +
                "`_id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                "`Autor` TEXT UNIQUE" +
                ")");
        db.execSQL(readSqldata("Frases"));
        db.execSQL(readSqldata("Autores"));
    }

    private String readSqldata(String str){
        //getting the file
        InputStream inputStream;
        if (str.equals("Frases")) {
            inputStream = contextdb.getResources().openRawResource(R.raw.sqlfrases);
        }else{
            inputStream = contextdb.getResources().openRawResource(R.raw.sqlautores);
        }
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try {
            int i = inputStream.read();
            while (i != -1) {
                byteArrayOutputStream.write(i);
                i = inputStream.read();
            }
            inputStream.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
        return byteArrayOutputStream.toString();
    }

}