package com.example.moslah_hamza.statsdemo;

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;

/**
 * Created by Moslah_Hamza on 15/03/2016.
 */
public class DataBaseTranHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "TransactionsManager";

    // table names
    private static final String TABLE_DIPENSE = "dipense";
    private static final String TABLE_REVENUES = "revenues";

    // Tables4 Columns names
    private static final String KEY_ID_CATEGORIE = "id_categorie";
    private static final String KEY_DATE = "date";
    private static final String KEY_SOMME = "somme";

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

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_REVENUES_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_REVENUES + " ("
                + KEY_ID_CATEGORIE + " TEXT," + KEY_DATE + " TEXT,"
                + KEY_SOMME + " REAL" + "," +
                "PRIMARY KEY(" + KEY_ID_CATEGORIE + "," + KEY_DATE + "))";

        String CREATE_DIPENSES_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_DIPENSE + "("
                + KEY_ID_CATEGORIE + " TEXT," + KEY_DATE + " TEXT,"
                + KEY_SOMME + " REAL" + "," +
                "PRIMARY KEY(" + KEY_ID_CATEGORIE + "," + KEY_DATE + "))";
        db.execSQL(CREATE_DIPENSES_TABLE);
        db.execSQL(CREATE_REVENUES_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older tables if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DIPENSE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_REVENUES);

        // Create tables again
        onCreate(db);
    }

    // Adding new revenue
    public void addRevenue(Transaction transaction) {
        SQLiteDatabase db = this.getWritableDatabase();
        Transaction transaction1 =getRevenue(transaction.getId_categorie(), transaction.getDate());
        if(transaction1.getDate().equals("")){
            ContentValues values = new ContentValues();
            values.put(KEY_ID_CATEGORIE, transaction.getId_categorie()); // transaction catégorie
            values.put(KEY_DATE, String.valueOf(transaction.getDate())); // transaction date
            values.put(KEY_SOMME, transaction.getSomme()); // transaction somme

            // Inserting Row
            db.insert(TABLE_REVENUES, null, values);
        }
        else
            updateRevenue(transaction, transaction1.getSomme());
        db.close(); // Closing database connection
    }

    // Getting single revenue
    public Transaction getRevenue(String id,String date) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_REVENUES, new String[]{
                        KEY_ID_CATEGORIE, KEY_DATE, KEY_SOMME}, KEY_ID_CATEGORIE + " =? and " + KEY_DATE + " =?",
                new String[]{id, date}, null, null, null, null);

        if (!cursor.moveToFirst())
            return new Transaction();

        Transaction transaction = new Transaction(cursor.getFloat(2),
                cursor.getString(0), cursor.getString(1));
        // return revenue
        return transaction;
    }

    // Getting All Revenues
    public ArrayList<Transaction> getAllRevenues(String id_categorie) {
        ArrayList<Transaction> transactionList = new ArrayList<Transaction>();
        // Select All Query
        SQLiteDatabase db = this.getWritableDatabase();
        // Select All Query
        Cursor cursor = db.query(TABLE_REVENUES, new String[]{KEY_ID_CATEGORIE,
                        KEY_DATE, KEY_SOMME}, KEY_ID_CATEGORIE + "=?",
                new String[]{id_categorie}, null, null, null, null);

        Log.d("revenue categ", id_categorie);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Transaction transaction = new Transaction();
                transaction.setId_categorie(cursor.getString(0));
                transaction.setDate(cursor.getString(1));
                transaction.setSomme(cursor.getFloat(2));
                Log.d("transaction", String.valueOf(transaction.getSomme()));
                // Adding contact to list
                transactionList.add(transaction);
            } while (cursor.moveToNext());
        }

        // return revenues list
        return transactionList;
    }

    // Getting revenues Count
    public int getRevenuesCount() {
        String countQuery = "SELECT  * FROM " + TABLE_REVENUES;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    // Updating single revenue
    public int updateRevenue(Transaction transaction,float oldSomme) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_SOMME, transaction.getSomme() + oldSomme);

        // updating row
        return db.update(TABLE_REVENUES, values, KEY_ID_CATEGORIE + " = ? and " + KEY_DATE + " = ?",
                new String[]{transaction.getId_categorie(), transaction.getDate()});
    }

    // Deleting single revenue
    public void deleteRevenue(Transaction transaction) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_REVENUES, KEY_DATE + " = ?," + KEY_SOMME + " =?",
                new String[]{transaction.getDate(), String.valueOf(KEY_SOMME)});
        db.close();
    }

    // Adding new dipense
    public void addDipense(Transaction transaction) {
        SQLiteDatabase db = this.getWritableDatabase();
        Log.d("transaction date ", transaction.getDate());
        Log.d("transaction sum ",String.valueOf(transaction.getSomme()));
        Transaction transaction1 =getDipense(transaction.getId_categorie(), transaction.getDate());
        if(transaction1.getDate().equals("")){
            ContentValues values = new ContentValues();
            values.put(KEY_ID_CATEGORIE, transaction.getId_categorie()); // transaction catégorie
            values.put(KEY_DATE, String.valueOf(transaction.getDate())); // transaction date
            values.put(KEY_SOMME, transaction.getSomme()); // transaction somme

            // Inserting Row
            db.insert(TABLE_DIPENSE, null, values);
        }
        else{
            Log.d("update "," transaction");
            updateDipense(transaction, transaction1.getSomme());
        }

        db.close(); // Closing database connection
    }

    // Getting single dipense
    public Transaction getDipense(String id,String date) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_DIPENSE, new String[]{
                        KEY_ID_CATEGORIE, KEY_DATE, KEY_SOMME}, KEY_ID_CATEGORIE + " =? and " + KEY_DATE + " =?",
                new String[]{String.valueOf(id), date}, null, null, null, null);

        if (!cursor.moveToFirst()){
            return new Transaction();
        }

        Transaction transaction = new Transaction(cursor.getFloat(2),
                cursor.getString(0), cursor.getString(1));
        Log.d("sum ",transaction.getDate());
        Log.d("sum ",String.valueOf(transaction.getSomme()));
        // return contact
        return transaction;
    }

    // Getting All Dipenses
    public ArrayList<Transaction> getAllDipenses(String id_categorie) {
        ArrayList<Transaction> transactionList = new ArrayList<Transaction>();
        // Select All Query
        SQLiteDatabase db = this.getWritableDatabase();
        // Select All Query
        Cursor cursor = db.query(TABLE_DIPENSE, new String[]{KEY_ID_CATEGORIE,
                        KEY_DATE, KEY_SOMME}, KEY_ID_CATEGORIE + "=?",
                new String[]{id_categorie}, null, null, null, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Transaction transaction = new Transaction();
                transaction.setId_categorie(cursor.getString(0));
                transaction.setDate(cursor.getString(1));
                transaction.setSomme(cursor.getFloat(2));
                // Adding contact to list
                Log.d("transaction", String.valueOf(transaction.getSomme()));
                transactionList.add(transaction);
            } while (cursor.moveToNext());
        }

        // return dipense list
        return transactionList;
    }

    // Getting dipenses Count
    public int getDipensesCount() {
        String countQuery = "SELECT  * FROM " + TABLE_DIPENSE;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

    // Updating single dipense
    public int updateDipense(Transaction transaction,float oldSomme) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_SOMME, transaction.getSomme() + oldSomme);
        Log.d("update sum 1 ", String.valueOf(transaction.getSomme()));
        Log.d("update sum 2 ", String.valueOf(oldSomme));
        // updating row
        return db.update(TABLE_DIPENSE, values, KEY_ID_CATEGORIE + " = ? and " + KEY_DATE + " = ?",
                new String[]{transaction.getId_categorie(), transaction.getDate()});
    }

    // Deleting single dipense
    public void deleteDipense(Transaction transaction) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_DIPENSE, KEY_DATE + " = ?," + KEY_SOMME + " =?",
                new String[]{transaction.getDate(), String.valueOf(KEY_SOMME)});
        db.close();
    }

    public void createdb() {
        this.onCreate(getWritableDatabase());
    }
}

