package com.whitelab.colorquant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.text.Html;
import android.util.Log;

import com.opencsv.CSVWriter;

import java.io.File;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

public class StandardsTableHelper extends SQLiteOpenHelper {

    private Context context;
    private static String logtag = "STH";
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "colorquant.db";
    private static String TABLE_NAME;
    private static String GROUP_TABLE;
    private static String COLUMN_ID;
    private static String COLUMN_STAMP;
    private static String COLUMN_ALIAS;
    private static String COLUMN_GROUP;
    private static String COLUMN_R;
    private static String COLUMN_G;
    private static String COLUMN_B;
    private static String COLUMN_L;
    private static String COLUMN_a;
    private static String COLUMN_b;
    private static String COLUMN_CONC;
    private static String COLUMN_CONC_ER;
    private static String COLUMN_DIST;
    private static String COLUMN_DIST_ER;
    private static String COLUMN_SLOPE;
    private static String COLUMN_SLOPE_ER;
    private static String COLUMN_INTERCEPT;
    private static String COLUMN_INTERCEPT_ER;

    StandardsTableHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        TABLE_NAME = context.getString(R.string.table_standards);
        GROUP_TABLE = context.getString(R.string.table_groups);
        COLUMN_ID = context.getString(R.string.column_id);
        COLUMN_STAMP = context.getString(R.string.column_stamp);
        COLUMN_ALIAS = context.getString(R.string.column_alias);
        COLUMN_GROUP = context.getString(R.string.column_group);
        COLUMN_R = context.getString(R.string.column_R);
        COLUMN_G = context.getString(R.string.column_G);
        COLUMN_B = context.getString(R.string.column_B);
        COLUMN_L = context.getString(R.string.column_L);
        COLUMN_a = context.getString(R.string.column_a);
        COLUMN_b = context.getString(R.string.column_b);
        COLUMN_CONC = context.getString(R.string.column_conc);
        COLUMN_CONC_ER = context.getString(R.string.column_conc_er);
        COLUMN_DIST = context.getString(R.string.column_dist);
        COLUMN_DIST_ER = context.getString(R.string.column_dist_er);
        COLUMN_SLOPE = context.getString(R.string.column_slope);
        COLUMN_SLOPE_ER = context.getString(R.string.column_slope_er);
        COLUMN_INTERCEPT = context.getString(R.string.column_intercept);
        COLUMN_INTERCEPT_ER = context.getString(R.string.column_intercept_er);

        String COLUMNS = COLUMN_ID + " INTEGER PRIMARY KEY, " +
                COLUMN_STAMP + " TEXT, " +
                COLUMN_ALIAS + " TEXT, " +
                COLUMN_GROUP + " TEXT, " +
                COLUMN_R + " TEXT, " +
                COLUMN_G + " TEXT, " +
                COLUMN_B + " TEXT, " +
                COLUMN_L + " TEXT, " +
                COLUMN_a + " TEXT, " +
                COLUMN_b + " TEXT, " +
                COLUMN_CONC + " TEXT, " +
//                COLUMN_CONC_ER + " TEXT, " +
                COLUMN_DIST + " TEXT, " +
                COLUMN_DIST_ER + " TEXT";

        String TABLE_CREATE =
                "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + COLUMNS + ")";

        Log.d(logtag,TABLE_CREATE);
        db.execSQL(TABLE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(StandardsTableHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public void insertStandard(HashMap<String,String> queryValues){

        Log.d(logtag,"inserting standard");
        SQLiteDatabase db = this.getWritableDatabase();
        onCreate(db);
        Log.d(logtag,db.toString());
        ContentValues values = new ContentValues();

        values.put(COLUMN_STAMP,queryValues.get(COLUMN_STAMP));
        values.put(COLUMN_ALIAS,queryValues.get(COLUMN_ALIAS));
        values.put(COLUMN_GROUP,queryValues.get(COLUMN_GROUP));
        values.put(COLUMN_R,queryValues.get(COLUMN_R));
        values.put(COLUMN_G,queryValues.get(COLUMN_G));
        values.put(COLUMN_B,queryValues.get(COLUMN_B));
        values.put(COLUMN_L,queryValues.get(COLUMN_L));
        values.put(COLUMN_a,queryValues.get(COLUMN_a));
        values.put(COLUMN_b,queryValues.get(COLUMN_b));
        values.put(COLUMN_CONC,queryValues.get(COLUMN_CONC));
//        values.put(COLUMN_CONC_ER,queryValues.get(COLUMN_CONC_ER));
        values.put(COLUMN_DIST,queryValues.get(COLUMN_DIST));
        values.put(COLUMN_DIST_ER,queryValues.get(COLUMN_DIST_ER));

        db.insert(TABLE_NAME,null,values);

        db.close();
    }

    public int updateStandard(HashMap<String,String> queryValues) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("Alias", queryValues.get("Alias"));
        values.put("ExpGroup", queryValues.get("ExpGroup"));

        int update = db.update(TABLE_NAME, values,
                COLUMN_ID + " = ?", new String[]{queryValues.get(COLUMN_ID)});
        db.close();
        return update;
    }

    public ArrayList<HashMap<String,String>> getGroupStandards(String group) {
        ArrayList<HashMap<String, String>> stdsList = new ArrayList<HashMap<String, String>>();

        SQLiteDatabase db = this.getWritableDatabase();
        onCreate(db);
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_GROUP + " = '" + group + "' ORDER BY " + COLUMN_ID + " DESC;", null);
        if(cursor.moveToFirst()){
            do{
                HashMap<String,String> stdsMap = new HashMap<String,String>();
                stdsMap.put(COLUMN_ID, cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
                stdsMap.put(COLUMN_STAMP, cursor.getString(cursor.getColumnIndex(COLUMN_STAMP)));
                stdsMap.put(COLUMN_ALIAS, cursor.getString(cursor.getColumnIndex(COLUMN_ALIAS)));
                stdsMap.put(COLUMN_GROUP, cursor.getString(cursor.getColumnIndex(COLUMN_GROUP)));
                stdsMap.put(COLUMN_R, cursor.getString(cursor.getColumnIndex(COLUMN_R)));
                stdsMap.put(COLUMN_G, cursor.getString(cursor.getColumnIndex(COLUMN_G)));
                stdsMap.put(COLUMN_B, cursor.getString(cursor.getColumnIndex(COLUMN_B)));
                stdsMap.put(COLUMN_L, cursor.getString(cursor.getColumnIndex(COLUMN_L)));
                stdsMap.put(COLUMN_a, cursor.getString(cursor.getColumnIndex(COLUMN_a)));
                stdsMap.put(COLUMN_b, cursor.getString(cursor.getColumnIndex(COLUMN_b)));
                stdsMap.put(COLUMN_CONC, cursor.getString(cursor.getColumnIndex(COLUMN_CONC)));
                stdsMap.put(COLUMN_DIST, cursor.getString(cursor.getColumnIndex(COLUMN_DIST)));
                stdsMap.put(COLUMN_DIST_ER, cursor.getString(cursor.getColumnIndex(COLUMN_DIST_ER)));

                stdsList.add(stdsMap);
            } while(cursor.moveToNext());
        }
        cursor.close();

        return stdsList;
    }

    public ArrayList<HashMap<String,String>> getAllStandards(){
        ArrayList<HashMap<String,String>> standardsList = new ArrayList<HashMap<String,String>>();

        SQLiteDatabase db = this.getWritableDatabase();
        onCreate(db);

        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " ORDER BY " + COLUMN_ID + " DESC;", null);

        if(cursor.moveToFirst()){
            do{
                HashMap<String,String> standardsMap = new HashMap<String,String>();
                standardsMap.put(COLUMN_ID, cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
                standardsMap.put(COLUMN_STAMP, cursor.getString(cursor.getColumnIndex(COLUMN_STAMP)));
                standardsMap.put(COLUMN_ALIAS, cursor.getString(cursor.getColumnIndex(COLUMN_ALIAS)));
                standardsMap.put(COLUMN_GROUP, cursor.getString(cursor.getColumnIndex(COLUMN_GROUP)));
                standardsMap.put(COLUMN_R, cursor.getString(cursor.getColumnIndex(COLUMN_R)));
                standardsMap.put(COLUMN_G, cursor.getString(cursor.getColumnIndex(COLUMN_G)));
                standardsMap.put(COLUMN_B, cursor.getString(cursor.getColumnIndex(COLUMN_B)));
                standardsMap.put(COLUMN_L, cursor.getString(cursor.getColumnIndex(COLUMN_L)));
                standardsMap.put(COLUMN_a, cursor.getString(cursor.getColumnIndex(COLUMN_a)));
                standardsMap.put(COLUMN_b, cursor.getString(cursor.getColumnIndex(COLUMN_b)));
                String[] conc = cursor.getString(cursor.getColumnIndex(COLUMN_CONC)).split("M");
                if (conc[0].equals("u")){
                    conc[0] = "µ";
                }
                standardsMap.put(COLUMN_CONC, conc[1] + conc[0] + "M");
//                standardsMap.put(COLUMN_CONC_ER, cursor.getString(cursor.getColumnIndex(COLUMN_CONC_ER)));
                standardsMap.put(COLUMN_DIST, cursor.getString(cursor.getColumnIndex(COLUMN_DIST)));
                standardsMap.put(COLUMN_DIST_ER, cursor.getString(cursor.getColumnIndex(COLUMN_DIST_ER)));

                standardsList.add(standardsMap);
            } while(cursor.moveToNext());
        }
        cursor.close();

        return standardsList;

    }

    public HashMap<String,String> getStandard(String id){

        HashMap<String,String> standardsMap = new HashMap<String,String>();

        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + "WHERE"  + COLUMN_ID + "='" + id + "'", null);

        if(cursor.moveToFirst()){
            do{
                standardsMap.put(COLUMN_ID, cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
                standardsMap.put(COLUMN_STAMP, cursor.getString(cursor.getColumnIndex(COLUMN_STAMP)));
                standardsMap.put(COLUMN_ALIAS, cursor.getString(cursor.getColumnIndex(COLUMN_ALIAS)));
                standardsMap.put(COLUMN_GROUP, cursor.getString(cursor.getColumnIndex(COLUMN_GROUP)));
                standardsMap.put(COLUMN_R, cursor.getString(cursor.getColumnIndex(COLUMN_R)));
                standardsMap.put(COLUMN_G, cursor.getString(cursor.getColumnIndex(COLUMN_G)));
                standardsMap.put(COLUMN_B, cursor.getString(cursor.getColumnIndex(COLUMN_B)));
                standardsMap.put(COLUMN_L, cursor.getString(cursor.getColumnIndex(COLUMN_L)));
                standardsMap.put(COLUMN_a, cursor.getString(cursor.getColumnIndex(COLUMN_a)));
                standardsMap.put(COLUMN_b, cursor.getString(cursor.getColumnIndex(COLUMN_b)));
                standardsMap.put(COLUMN_CONC, cursor.getString(cursor.getColumnIndex(COLUMN_CONC)));
                standardsMap.put(COLUMN_CONC_ER, cursor.getString(cursor.getColumnIndex(COLUMN_CONC_ER)));
                standardsMap.put(COLUMN_DIST, cursor.getString(cursor.getColumnIndex(COLUMN_DIST)));
                standardsMap.put(COLUMN_DIST_ER, cursor.getString(cursor.getColumnIndex(COLUMN_DIST_ER)));

            } while(cursor.moveToNext());
        }
        cursor.close();

        return standardsMap;


    }

    public void exportTable(File file){
        SQLiteDatabase db = this.getWritableDatabase();
        try
        {
            file.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
            Cursor curCSV = db.rawQuery("SELECT * FROM " + context.getString(R.string.table_standards) ,null);
            csvWrite.writeNext(curCSV.getColumnNames());
            int columns = curCSV.getColumnNames().length;
            String[] values = new String[columns];
            while(curCSV.moveToNext())
            {
                //Which column you want to export
                for (int i=0;i<columns;i++){
                    values[i] = curCSV.getString(i);
                }
                csvWrite.writeNext(values);
            }
            csvWrite.close();
            curCSV.close();
            db.close();
        }
        catch(Exception sqlEx)
        {
            Log.e(logtag, sqlEx.getMessage(), sqlEx);
        }
        Log.d(logtag,Uri.fromFile(file).toString());
    }

    public void exportGroup(String group, File file){
        SQLiteDatabase db = this.getWritableDatabase();
        try
        {
            file.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
            Cursor curCSV = db.rawQuery("SELECT * FROM " + context.getString(R.string.table_standards) + " WHERE " + context.getString(R.string.column_group) + " = '" + group + "'",null);
            csvWrite.writeNext(curCSV.getColumnNames());
            int columns = curCSV.getColumnNames().length;
            String[] values = new String[columns];
            while(curCSV.moveToNext())
            {
                //Which column you want to export
                for (int i=0;i<columns;i++){
                    values[i] = curCSV.getString(i);
                }
                csvWrite.writeNext(values);
            }
            csvWrite.close();
            curCSV.close();
            db.close();
        }
        catch(Exception sqlEx)
        {
            Log.e(logtag, sqlEx.getMessage(), sqlEx);
        }
    }

    public void dropTable(){
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + context.getString(R.string.table_standards));
        db.close();
    }

    public void dropGroup(String group){
        SQLiteDatabase db = this.getWritableDatabase();
        ArrayList<HashMap<String, String>> stdsList = getGroupStandards(group);

        for (int std = 0; std < stdsList.size(); std++) {
            deleteStandard(stdsList.get(std).get(COLUMN_ID));
        }

        db.close();
    }

    public void deleteStandard(String id){
        Log.d(logtag,"Dropping StandardsTable entries");

        SQLiteDatabase db = this.getWritableDatabase();

        db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_ID + " = '" + id + "'");
        db.close();
    }
}
