package com.ghroupdrive.app;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

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

import java.text.SimpleDateFormat;
import java.util.Calendar;


public class Database {
	
	public static final String ID="id";
	public static final String TYPE="data_type";
	public static final String SERVER_ID="server_id";
	public static final String JSONSTRING="jsonString";
	public static final String NAME="Name";
	public static final String USERID="userID";
	public static final String DIRECTION="usdDirection";
	public static final String DATE="messageDate";
	public static final String TIMESTAMP="messageTypeStamp";
	public static final String CONTENT="content";
	public static final String STATUS="status";




	
	
	
	
	private static final int DATABASE_VERSION=1;
	private static final String DATABASE_NAME="piczler_db";
	private static final String TABLE_SAMPLE_DETAILS="tb_sample_details";

    private static final String TABLE_USERS="Users";
    private static final String TABLE_MESSAGES="tble_Messages";



private DbHandler ourHandler;
private final Context mContext;
private SQLiteDatabase ourDatabase;


private class DbHandler extends SQLiteOpenHelper {

	public DbHandler(Context context, String DATABASE_NAME, SQLiteDatabase.CursorFactory k, int DATABASE_VERSION) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

        //we ar adding audio table


        db.execSQL("CREATE TABLE "+TABLE_SAMPLE_DETAILS+" ( "+
                ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+
                SERVER_ID+ " TEXT NOT NULL, "+
                TYPE+ " INTEGER NOT NULL, "+
                JSONSTRING+ " TEXT NOT NULL, "+
                " CONSTRAINT dist_const UNIQUE ("+SERVER_ID+" ,"+TYPE+"));");


        db.execSQL("CREATE TABLE "+TABLE_USERS+" ( "+
                ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+
                SERVER_ID+ " TEXT NOT NULL UNIQUE, "+
                NAME+ " TEXT NOT NULL, "+
                TIMESTAMP + " TEXT NOT NULL, "+
                JSONSTRING+ " TEXT NOT NULL);");

        db.execSQL("CREATE TABLE "+TABLE_MESSAGES+" ( "+
                ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+
                SERVER_ID+ " TEXT NOT NULL, "+
                USERID+ " TEXT NOT NULL, "+
                DIRECTION+ " INTEGER NOT NULL, "+
                STATUS+ " INTEGER NOT NULL, "+
                TIMESTAMP+ " TEXT NOT NULL, "+
                CONTENT+ " TEXT NOT NULL, "+
                TYPE+ " INTEGER NOT NULL, "+
                DATE+ " TEXT NOT NULL);");


	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub


	}
	
}


public Database(Context c){
	mContext=c;
}

public Database open()throws SQLException {
	ourHandler=new DbHandler(mContext, DATABASE_NAME, null, DATABASE_VERSION);
	ourDatabase=ourHandler.getWritableDatabase();
	return this;
}

public void close()throws SQLException {
    if(ourDatabase.inTransaction())
    {
        System.out.println("in trasaction so end here");
        ourDatabase.endTransaction();
    }

	ourDatabase.close();
	
}




    public void deleteSampleJson(String serverID, int type) throws SQLException
    {
       ourDatabase.delete(TABLE_SAMPLE_DETAILS,TYPE+"="+type+" AND "+SERVER_ID+"="+serverID,null);
    }





    public void insertSampleDetails(String serverID, int type, String jsonString)throws SQLException
    {


        try
        {
            String sql = "";



                sql = "INSERT OR REPLACE INTO "+ TABLE_SAMPLE_DETAILS +"("+SERVER_ID+", "+
                        TYPE+", "+
                        JSONSTRING+") VALUES (?,?,?);";

            //String params [] = {serverID,""+type,jsonString};

            //ourDatabase.rawQuery(sql,params);



            SQLiteStatement statement = ourDatabase.compileStatement(sql);


            ourDatabase.beginTransactionNonExclusive();
            try{
                statement.clearBindings();

                statement.bindString(1, serverID);
                statement.bindString(2, ""+type);
                statement.bindString(3, jsonString);
                statement.execute();
            }catch (Exception e){
                e.printStackTrace();
            }


            ourDatabase.setTransactionSuccessful();
            ourDatabase.endTransaction();

        }catch (Exception ed)
        {
            ed.printStackTrace();
        }


    }


    public Cursor getSampleDetails(int type, String serverId)throws SQLException
    {
       String query = "SELECT * FROM "+ TABLE_SAMPLE_DETAILS+" WHERE "+SERVER_ID+"=? AND "+TYPE+"= ?";
        String params[] ={serverId,""+type};
       return ourDatabase.rawQuery(query,params);
    }



    public void deleteAllData()
    {
        ourDatabase.delete(TABLE_SAMPLE_DETAILS,null,null);
    }







    //jandling messages



    public void insertUsers(String rides) throws SQLException
    {
        try
        {


            UserFunctions functions = new UserFunctions(mContext);





            JSONArray rideArray = new JSONArray(rides);


            String sql = "";
            sql = "INSERT OR REPLACE INTO "+ TABLE_USERS +"("+SERVER_ID+", "+
                    NAME+", "+
                    TIMESTAMP+", "+
                    JSONSTRING+") VALUES (?,?,?,?);";

            SQLiteStatement statement = ourDatabase.compileStatement(sql);


            ourDatabase.beginTransactionNonExclusive();

            for(int i=0; i<rideArray.length(); i++)
            {
                JSONObject oneRide = rideArray.getJSONObject(i);
                RideObject rideObj = new RideObject(oneRide.toString(), functions);
                JSONArray passengers = rideObj.Passengers;

                for(int a=0; a<passengers.length(); a++)
                {
                    JSONObject pass = passengers.getJSONObject(i);
                    ProfileObject oprofObj = new ProfileObject(pass.toString(),functions);

                    if(oprofObj.AccessCode.contentEquals(functions.getPref(StaticVariables.ACCESSCODE,"")))
                    {

                        try{

                            statement.clearBindings();

                            statement.bindString(1, oprofObj.AccessCode);
                            statement.bindString(2, oprofObj.Fullname);
                            statement.bindString(3, functions.getPref(StaticVariables.MESSAGE+oprofObj.AccessCode,""));
                            statement.bindString(4, pass.toString());
                            statement.execute();


                        }catch (Exception ex)
                        {
                            ex.printStackTrace();
                        }

                    }

                }



            }

            ourDatabase.setTransactionSuccessful();
            ourDatabase.endTransaction();


        }catch (Exception ex)
        {

        }
    }



    public void insertSingleUser(String jsonObject)
    {
        try
        {

            UserFunctions functions = new UserFunctions(mContext);

            String sql = "";
            sql = "INSERT OR REPLACE INTO "+ TABLE_USERS +"("+SERVER_ID+", "+
                    NAME+", "+
                    TIMESTAMP+", "+
                    JSONSTRING+") VALUES (?,?,?,?);";

            ProfileObject oprofObj = new ProfileObject(jsonObject,functions);
            //String params[] = {oprofObj.AccessCode, oprofObj.Fullname,functions.getPref(StaticVariables.MESSAGE+oprofObj.AccessCode,""),jsonObject};

           // ourDatabase.rawQuery(sql,params);


            SQLiteStatement statement = ourDatabase.compileStatement(sql);


            ourDatabase.beginTransactionNonExclusive();



            try{

                statement.clearBindings();

                statement.bindString(1, oprofObj.AccessCode);
                statement.bindString(2, oprofObj.Fullname);
                statement.bindString(3, functions.getPref(StaticVariables.MESSAGE+oprofObj.AccessCode,""));
                statement.bindString(4, jsonObject);
                statement.execute();


            }catch (Exception ex)
            {
                ex.printStackTrace();
            }

System.out.println("User data inserted now thank you.");

            ourDatabase.setTransactionSuccessful();
            ourDatabase.endTransaction();


        }catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }




    public void  prepareGhroupDrive() throws SQLException
    {


        Calendar c = Calendar.getInstance();
        System.out.println("Current time => " + c.getTime());
        SimpleDateFormat df = new SimpleDateFormat("dd-MMM-yyyy");
        String formattedDate = df.format(c.getTime());

        String timeStamp = ""+System.currentTimeMillis();
      ContentValues  cv = new ContentValues();

        cv.put(SERVER_ID, StaticVariables.GROUPDRIVEID);
        cv.put(NAME, "Nicole @Ghroupdrive");
        cv.put(TIMESTAMP, formattedDate);
        cv.put(JSONSTRING, "");
        ourDatabase.insert(TABLE_USERS,null,cv);


        Message mess = new Message();
        mess.setServerID(StaticVariables.GROUPDRIVEID);
        mess.setUserID(StaticVariables.GROUPDRIVEID);
        mess.setDirection(StaticVariables.YOURMESSAGE);
        mess.setStatus(StaticVariables.UNREADMESSAGE);
        mess.setTimeStamp(timeStamp);
        mess.setContent("Welcome to ghroupdrive!");
        mess.setDate(formattedDate);
        mess.setType(StaticVariables.NOTIFICATIONMESSAGE);
        insertMesage(mess);
    }


    public void insertMesage(Message message)throws  SQLException
    {

        UserFunctions functions = new UserFunctions(mContext);
        ContentValues cv = new ContentValues();
        cv.put(SERVER_ID, message.serverID);
        cv.put(USERID, message.userID);
        cv.put(DIRECTION, message.direction);
        cv.put(STATUS, message.status);
        cv.put(TIMESTAMP, message.timeStamp);
        cv.put(CONTENT, message.content);
        cv.put(DATE, message.date);
        cv.put(TYPE, message.type);

        functions.setPref(StaticVariables.MESSAGE+message.userID,message.timeStamp);
        ourDatabase.insert(TABLE_MESSAGES,null,cv);

        cv = new ContentValues();
        cv.put(TIMESTAMP,message.timeStamp);

        ourDatabase.update(TABLE_USERS,cv,SERVER_ID+"='"+message.userID+"'",null);

    }




    public Cursor getUsers() throws SQLException
    {

        String sub = "SELECT "+STATUS+"  FROM "+TABLE_MESSAGES+" WHERE "+USERID+"="+TABLE_USERS+"."+SERVER_ID+" ORDER BY "+TIMESTAMP+" DESC LIMIT 1";
        String sql = "SELECT *, (SELECT "+CONTENT+"  FROM "+TABLE_MESSAGES+" WHERE "+USERID+"="+TABLE_USERS+"."+SERVER_ID+" ORDER BY "+TIMESTAMP+" DESC LIMIT 1 ) as lastMess, ("+sub+") as status FROM "+TABLE_USERS+"  ORDER BY "+TIMESTAMP+" DESC";
        System.out.println("lllllllllllllllllll:  "+sql);

        return  ourDatabase.rawQuery(sql,null);
    }



    public boolean userExist(String serverID) throws  SQLException
    {
        String query = "SELECT * FROM "+TABLE_USERS+" WHERE "+SERVER_ID+" = ?";

        String parmas[] ={serverID};
        Cursor c = ourDatabase.rawQuery(query,parmas);
        boolean exist = false;

        if(c.getCount()>0)
        {
            exist = true;
        }

        return  exist;
    }

    public void updateReadStatus(String userID) throws SQLException
    {
        ContentValues cv = new ContentValues();
        cv.put(STATUS, StaticVariables.READEMESSAGE);
        String[] params = {userID};
        ourDatabase.update(TABLE_MESSAGES,cv,USERID+"=?",params);

    }




    public Cursor getMessages(String userID) throws SQLException
    {
       String query = "SELECT * FROM "+TABLE_MESSAGES+" WHERE "+USERID+" = ?";
        String[] params = {userID};

        return  ourDatabase.rawQuery(query,params);
    }
}