package com.example.Maps013;


import java.util.ArrayList;
import java.util.List;

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

//https://android-arsenal.com/details/1/1636

public class DB_GPS extends SQLiteOpenHelper 
{
	
    public static final String DATABASE_NAME="database05";
    public static final int DATABASE_VERSION = 1;

    public static final String TABLE_GPS = "table_gps";
    public static final String TABLE_RUN = "table_run";
    
    
    public static final String KEY_ID = "_id";
    public static final String COL_NOM = "nom";
    public static final String COL_TIME = "time";
    public static final String COL_LATITUDE = "latitude";
	public static final String COL_LONGITUDE = "longitude";
	public static final String COL_ALTITUDE = "altitude";
	public static final String COL_VITESSE = "vitesse";
		
	public  static final String CREATE_TABLE_GPS = "CREATE TABLE "
	            + TABLE_GPS + "(" 
	    		+ KEY_ID + " INTEGER PRIMARY KEY,"
	    		+ COL_NOM + " TEXT," 
	    		+ COL_TIME + " INTEGER," 
	    		+ COL_LATITUDE + " INTEGER," 
	            + COL_LONGITUDE + " INTEGER," 
	            + COL_ALTITUDE + " INTEGER,"
	            + COL_VITESSE + " INTEGER" + ")";
	
		public static final String COL_NOM_RUN = "nom";
		public static final String COL_DATE = "date";
		public static final String COL_START = "start";
	    public static final String COL_STOP = "stop";
	    public static final String COL_STEPS_COUNT = "steps";
	    public static final String COL_MODE = "mode";  
	    public static final String COL_CALORIES = "calories";  
	    public static final String COL_VITESSE_MOYENNE = "vitesse_moyenne";  
	    public static final String COL_DISTANCE = "distance";
	    public static final String COL_DURATION = "duration";
	

	public  static final String CREATE_TABLE_RUN = "CREATE TABLE "
            + TABLE_RUN + "(" 
    		+ KEY_ID + " INTEGER PRIMARY KEY,"
    		+ COL_NOM_RUN + " TEXT," 
    		+ COL_DATE + " INTEGER," 
    		+ COL_START + " INTEGER," 
    		+ COL_STOP + " INTEGER," 
    		+ COL_STEPS_COUNT + " INTEGER," 
    		+ COL_MODE + " TEXT," 
    		+ COL_CALORIES + " INTEGER," 
            + COL_VITESSE_MOYENNE + " INTEGER," 
            + COL_DISTANCE + " INTEGER,"
            + COL_DURATION + " INTEGER" + ")";
	
	int keyid;
	String nom;
	long time;
	Double latitude;
	Double longitude;
	Double altitude;
	Double vitesse;
	
	 Context cont;

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

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL(CREATE_TABLE_GPS);
        db.execSQL(CREATE_TABLE_RUN);
        
       
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        db.execSQL("DROP TABLE IF EXISTS "+ TABLE_GPS);
        db.execSQL("DROP TABLE IF EXISTS "+ TABLE_RUN);
        onCreate(db);
    }
    
    public String getDBpath()
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	
        String databasePath =     	db.getPath();
//db.getDatabasePath(DATABASE_NAME).getPath();
        return databasePath;
    }
    
    public long save_infos_GPS(GPS obj) 
	{
    		SQLiteDatabase db = this.getWritableDatabase();

			ContentValues vals = new ContentValues();
			vals.put(COL_NOM, obj.getNom());
			vals.put(COL_TIME, obj.getTime());
			vals.put(COL_LATITUDE, obj.getLatitude());
			vals.put(COL_LONGITUDE, obj.getLongitude());
			vals.put(COL_ALTITUDE, obj.getAltitude());
			vals.put(COL_VITESSE, obj.getVitesse());

			long id = db.insert(TABLE_GPS, null, vals);
			return id;
	}
    
    public long save_infos_RUN(String nom, long date, long start , long stop, long steps, String mode, double calories, double vitesse_moyenne, double distance, long duration ) 
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	
        ContentValues vals = new ContentValues();
        vals.put( COL_NOM_RUN , nom);
        vals.put( COL_DATE , date);
        vals.put( COL_START , start);
        vals.put( COL_STOP , stop);
        vals.put( COL_STEPS_COUNT , steps);
        vals.put( COL_MODE, mode);
        vals.put( COL_CALORIES , calories);
        vals.put( COL_VITESSE_MOYENNE , vitesse_moyenne);
        vals.put( COL_DISTANCE , distance);
        vals.put( COL_DURATION , duration);
        
    	long id = db.insert(TABLE_RUN, null, vals);
		return id;
        
    }
    
    
    public Cursor DB_query(String query) 
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	    	
	   	 	Cursor cursor = db.rawQuery(query, null);
	   	 	if (cursor != null)
	   	 	{
	   	 		cursor.moveToFirst();
	   	 	}
	   	 	return cursor;
    }
    
    public void query(String element)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	
        Cursor cursor = db.query(TABLE_GPS, new String[] { KEY_ID, COL_NOM, COL_TIME, COL_LATITUDE, COL_LONGITUDE, COL_ALTITUDE, COL_VITESSE  }, COL_NOM + "=?",
                new String[] { element }, null, null, "time ASC", null); // ordre croissant 
        
        // pour la keyid String.valueOf(id)

        if (cursor != null)
        {
        	int val = cursor.getCount();
        
        	Control_Service.objets.clear();
        	
	        if (cursor.moveToFirst())
	        {
	            do {
	                
	                   keyid = Integer.parseInt(cursor.getString(0));
	                   nom = cursor.getString(1);
	                   time = cursor.getLong(2);
	                   latitude = cursor.getDouble(3);
	                   longitude = cursor.getDouble(4);
	                   altitude = cursor.getDouble(5);
	                   vitesse = cursor.getDouble(6);
	           		
	           			GPS titi = new GPS(nom, time, latitude, longitude , altitude, vitesse);
	           			Control_Service.objets.add(titi);
	                   
	            } while (cursor.moveToNext());
	        }        
        }
    }
    

    
    public List<GPS> query_gps(String run_nom)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        
        List<GPS> records = new ArrayList<GPS>();
     
        Cursor cursor = db.query(TABLE_GPS, new String[] { KEY_ID, COL_NOM, COL_TIME, COL_LATITUDE, COL_LONGITUDE, COL_ALTITUDE, COL_VITESSE }, COL_NOM + "=?",
                new String[] { run_nom }, null, null, null, null);
        
        cursor.moveToFirst();
        
    	int val = cursor.getCount();
        
        while (!cursor.isAfterLast()) 
        {
        	
			
			String nom = cursor.getString(cursor.getColumnIndex(COL_NOM));
			long time = cursor.getLong(cursor.getColumnIndex(COL_TIME));
			Double latitude = cursor.getDouble(cursor.getColumnIndex(COL_LATITUDE));
			Double longitude = cursor.getDouble(cursor.getColumnIndex(COL_LONGITUDE));
			Double altitude = cursor.getDouble(cursor.getColumnIndex(COL_ALTITUDE));
			Double vitesse = cursor.getDouble(cursor.getColumnIndex(COL_VITESSE));
			
			
			//meters/second  3600 / 1000 = 3.6
			vitesse =  vitesse * 3.6;
			
            GPS infos = new GPS(nom, time  , latitude, longitude ,altitude, vitesse);
        	
            records.add(infos);
            
          
            
            cursor.moveToNext();
        }
        cursor.close();
        
        return records;
     
      
    }
    
    public List<GPS> getAllRecords()
    {
        List<GPS> records = new ArrayList<GPS>();

        SQLiteDatabase db = this.getWritableDatabase();
        
        Cursor cursor =  db.query(TABLE_GPS, new String[] { KEY_ID, COL_NOM, COL_TIME, COL_LATITUDE, COL_LONGITUDE, COL_ALTITUDE, COL_VITESSE  }, null, null, null, null, COL_TIME + " DESC");

        cursor.moveToFirst();
        
    	int val = cursor.getCount();
        
        while (!cursor.isAfterLast()) 
        {
        	
			
			String nom = cursor.getString(cursor.getColumnIndex(COL_NOM));
			long time = cursor.getLong(cursor.getColumnIndex(COL_TIME));
			Double latitude = cursor.getDouble(cursor.getColumnIndex(COL_LATITUDE));
			Double longitude = cursor.getDouble(cursor.getColumnIndex(COL_LONGITUDE));
			Double altitude = cursor.getDouble(cursor.getColumnIndex(COL_ALTITUDE));
			Double vitesse = cursor.getDouble(cursor.getColumnIndex(COL_VITESSE));
            
            GPS infos = new GPS(nom, time  , latitude, longitude ,altitude, vitesse);
        	
            records.add(infos);
            
          
            
            cursor.moveToNext();
        }
        cursor.close();
        
        return records;
    }
    
    
    
    public ArrayList<GPS> getAllRecords2()
    {
//        List<GPS> records = new ArrayList<GPS>();
        
        ArrayList<GPS> records = new ArrayList<GPS>();


        SQLiteDatabase db = this.getWritableDatabase();
        
        Cursor cursor =  db.query(TABLE_GPS, new String[] { KEY_ID, COL_NOM, COL_TIME, COL_LATITUDE, COL_LONGITUDE, COL_ALTITUDE, COL_VITESSE  }, null, null, null, null, COL_TIME + " DESC");

        cursor.moveToFirst();
        
    	int val = cursor.getCount();
        
        while (!cursor.isAfterLast()) 
        {
        	
			
			String nom = cursor.getString(cursor.getColumnIndex(COL_NOM));
			long time = cursor.getLong(cursor.getColumnIndex(COL_TIME));
			Double latitude = cursor.getDouble(cursor.getColumnIndex(COL_LATITUDE));
			Double longitude = cursor.getDouble(cursor.getColumnIndex(COL_LONGITUDE));
			Double altitude = cursor.getDouble(cursor.getColumnIndex(COL_ALTITUDE));
			Double vitesse = cursor.getDouble(cursor.getColumnIndex(COL_VITESSE));
            
            GPS infos = new GPS(nom, time  , latitude, longitude ,altitude, vitesse);
        	
            records.add(infos);
            
          
            
            cursor.moveToNext();
        }
        cursor.close();
        
        return records;
    }
    
    /******************************************************************************************************/
    
    public ArrayList<RUN> Infos_RUN(String run_nom)
    {

        SQLiteDatabase db = this.getWritableDatabase();
        
        ArrayList<RUN> records = new ArrayList<RUN>();

        Cursor cursor =  db.query(TABLE_RUN, new String[] { KEY_ID, COL_NOM_RUN, COL_DATE ,
        		COL_START, COL_STOP, COL_STEPS_COUNT, COL_MODE, COL_CALORIES, COL_VITESSE_MOYENNE, COL_DISTANCE, COL_DURATION  }, COL_NOM + "=?",
                new String[] { run_nom }, null, null, null, null);
        
        cursor.moveToFirst();
        
    	int val = cursor.getCount();
			
		String nom = cursor.getString(cursor.getColumnIndex(COL_NOM_RUN));
		Long date = cursor.getLong(cursor.getColumnIndex(COL_DATE));
		Long start = cursor.getLong(cursor.getColumnIndex(COL_START));
		Long stop = cursor.getLong(cursor.getColumnIndex(COL_STOP));
		Long steps = cursor.getLong(cursor.getColumnIndex(COL_STEPS_COUNT));
		String mode = cursor.getString(cursor.getColumnIndex(COL_MODE));
			
			
		Double calories = cursor.getDouble(cursor.getColumnIndex(COL_CALORIES));
		Double vitesse_moyenne = cursor.getDouble(cursor.getColumnIndex(COL_VITESSE_MOYENNE));
		Double distance = cursor.getDouble(cursor.getColumnIndex(COL_DURATION));
		Double duration = cursor.getDouble(cursor.getColumnIndex(COL_DURATION));
			
		RUN infos = new RUN(nom, date, start , stop, steps, mode, calories,  vitesse_moyenne,  distance,  duration ); 
		records.add(infos);
         
        cursor.close();
	   
        return records;
    }
    
    
    public ArrayList<PIPO> zorro(String run_nom)
    {

        SQLiteDatabase db = this.getWritableDatabase();
        
        ArrayList<PIPO> records = new ArrayList<PIPO>();

        Cursor cursor =  db.query(TABLE_RUN, new String[] { KEY_ID, COL_NOM_RUN, COL_DATE ,
        		COL_START, COL_STOP, COL_STEPS_COUNT, COL_MODE, COL_CALORIES, COL_VITESSE_MOYENNE, COL_DISTANCE, COL_DURATION  }, COL_NOM + "=?",
                new String[] { run_nom }, null, null, null, null);
        
        cursor.moveToFirst();
        
    	int val = cursor.getCount();
			
		String nom = cursor.getString(cursor.getColumnIndex(COL_NOM_RUN));
		Long date = cursor.getLong(cursor.getColumnIndex(COL_DATE));
		Long start = cursor.getLong(cursor.getColumnIndex(COL_START));
		Long stop = cursor.getLong(cursor.getColumnIndex(COL_STOP));
		Long steps = cursor.getLong(cursor.getColumnIndex(COL_STEPS_COUNT));
		String mode = cursor.getString(cursor.getColumnIndex(COL_MODE));
			
			
		Double calories = cursor.getDouble(cursor.getColumnIndex(COL_CALORIES));
		Double vitesse_moyenne = cursor.getDouble(cursor.getColumnIndex(COL_VITESSE_MOYENNE));
		Double distance = cursor.getDouble(cursor.getColumnIndex(COL_DISTANCE));
		Double duration = cursor.getDouble(cursor.getColumnIndex(COL_DURATION));
			
		PIPO infos = new PIPO(nom, date, start , stop, steps, mode, calories,  vitesse_moyenne,  distance,  duration ); 
		records.add(infos);
         
        cursor.close();
	   
        return records;
    }
   
    /*****************************************************************************************************/
    
    public ArrayList<RUN> RUNgetAllRecords()
    {
        ArrayList<RUN> records = new ArrayList<RUN>();
        
      

        SQLiteDatabase db = this.getWritableDatabase();
        /*
        vals.put( COL_NOM_RUN , nom);
        vals.put( COL_DATE , date);
        vals.put( COL_START , start);
        vals.put( COL_STOP , stop);
        vals.put( COL_STEPS_COUNT , steps);
        vals.put( COL_MODE, mode);
        vals.put( COL_CALORIES , calories);
        vals.put( COL_VITESSE_MOYENNE , vitesse_moyenne);
        vals.put( COL_DISTANCE , distance);
        vals.put( COL_DURATION , duration);
    */    
        Cursor cursor =  db.query(TABLE_RUN, new String[] { KEY_ID, COL_NOM_RUN, COL_DATE ,
        		COL_START, COL_STOP, COL_STEPS_COUNT, COL_MODE, COL_CALORIES, COL_VITESSE_MOYENNE, COL_DISTANCE, COL_DURATION  }, null, null, null, null, COL_DATE + " ASC");

        //Cursor cursor =  db.query(TABLE_GPS, new String[] { KEY_ID, COL_NOM_RUN , COL_DISTANCE }, null, null, null, null, COL_NOM_RUN + " ASC");

        
        cursor.moveToFirst();
        
    	int val = cursor.getCount();
        

    	
        while (!cursor.isAfterLast()) 
        {
			
			String nom = cursor.getString(cursor.getColumnIndex(COL_NOM_RUN));
			Long date = cursor.getLong(cursor.getColumnIndex(COL_DATE));
			Long start = cursor.getLong(cursor.getColumnIndex(COL_START));
			Long stop = cursor.getLong(cursor.getColumnIndex(COL_STOP));
			Long steps = cursor.getLong(cursor.getColumnIndex(COL_STEPS_COUNT));
			String mode = cursor.getString(cursor.getColumnIndex(COL_MODE));
			
			
			Double calories = cursor.getDouble(cursor.getColumnIndex(COL_CALORIES));
			Double vitesse_moyenne = cursor.getDouble(cursor.getColumnIndex(COL_VITESSE_MOYENNE));
			Double distance = cursor.getDouble(cursor.getColumnIndex(COL_DURATION));
			Double duration = cursor.getDouble(cursor.getColumnIndex(COL_DURATION));
			
			
			 RUN infos = new RUN(nom, date, start , stop, steps, mode, calories,  vitesse_moyenne,  distance,  duration ); 
			
            
//            RUN infos = new RUN(nom, time  , latitude, longitude ,altitude, vitesse);
        	
            records.add(infos);
            
            cursor.moveToNext();
        }
        cursor.close();
        
        return records;
    }
    
    /***************************************************************************************************/
    
    
  /*  public GPS cursorToTextReport(Cursor cursor) 
    {
        TextReport tr = new TextReport();

        tr.setRunid(cursor.getString(cursor.getColumnIndex(DBtables.TextReport.COLUMN_ID)));
        tr.setReport(cursor.getString(cursor.getColumnIndex(DBtables.TextReport.COLUMN_REPORT)));
        tr.setDatetime(cursor.getLong(cursor.getColumnIndex(DBtables.TextReport.COLUMN_DATETIME)));
        tr.setLongitude(cursor.getDouble(cursor.getColumnIndex(DBtables.TextReport.COLUMN_LONGITUDE)));
        tr.setLatitude(cursor.getDouble(cursor.getColumnIndex(DBtables.TextReport.COLUMN_LATITUDE)));
        tr.setAltitude(cursor.getDouble(cursor.getColumnIndex(DBtables.TextReport.COLUMN_ALTITUDE)));
        tr.setSpeed(cursor.getDouble(cursor.getColumnIndex(DBtables.TextReport.COLUMN_SPEED)));
        
        return tr;
    }*/
   /* 
    mDb.query(true, SQLITE_TABLE, new String[] {
            KEY_ROWID,
            KEY_COMPANY,
            KEY_ORDER,
            KEY_SEQ,
            KEY_ITEM,
            KEY_DESCRIPTION,
            KEY_QUANTITY,
            KEY_PRICE,}, 
            KEY_COMPANY + "=?" + " and "  +
            KEY_ORDER + "=?", 
            new String[] {company,orderNumber},
            null, null, KEY_ITEM , null);
    */
    
    /******************************************************************************************************************************/
    
    
    public void update_appoitment(String id, ContentValues contentValues)
    {
    	
       	SQLiteDatabase db = this.getWritableDatabase();
    	db.update("TABLE_APPOINTMENT", contentValues, KEY_ID + " = ?", new String[]{id});
	   
    }
    
    public void update_repeat(String id, ContentValues contentValues)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.update("TABLE_REPEAT", contentValues, KEY_ID + " = ?", new String[]{id});
    }
    
    
    public void lulu(int index)
    {
    	
    	SQLiteDatabase db = this.getWritableDatabase();

    	Cursor cursor = db.query("table", null, DB_GPS.KEY_ID+" = ?", new String[]{String.valueOf(index)}, null, null, null);
		try 
		{
			if (cursor.moveToFirst()) 
			{
			}
			
		} 
		finally
		{
			cursor.close();
		}

        
    }
    
    
  
    
    
    
    
    public void update(String query)
    {
    	
    	SQLiteDatabase db = this.getWritableDatabase();
    	
    	/*
    	// OK
    	String id = "6";
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_NAME, "Patoche");
    	db.update(TABLE, contentValues, KEY_ID + " = ?", new String[]{id});
    	*/
    	
    	/* OK */ 
    	db.execSQL(query);
    	    	
    	
	   
    }
    
    public void delete(String id) 
    {
    	
        SQLiteDatabase db = this.getWritableDatabase();
        
       // String id = "6";
        
//       db.delete(TABLE, KEY_ID + " = ?", new String[] { String.valueOf(6) });
//        db.close();
        
        
        //db.delete("table", "_id = ?", new String[] {String.valueOf(id)});
        
        //String s = "DELETE FROM AddressList WHERE item_id=" + id;
        //db.execSQL(s);
    
//       db.execSQL("DELETE FROM table_alarm WHERE _id ='id'");
        
        db.execSQL("DELETE FROM table_alarm WHERE _id='" + id + "'");
        
        
    }
    
    
    public void delete_appointment(String id) 
    {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DELETE FROM table_appointment WHERE _id='" + id + "'");
    }
    
    public void delete_repeat(String id) 
    {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DELETE FROM table_repeat WHERE _id='" + id + "'");
    }
    
    
    
   
    
    
  
}


