package com.example.db01;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class DbAdapter 
{
	 
	 	public static final String TABLE_ALARM = "alarm";
		public static final String COL_ID = "_id";
		public static final String COL_CREATEDTIME = "created_time";
		public static final String COL_MODIFIEDTIME = "modified_time";
		public static final String COL_NAME = "name";
		public static final String COL_FROMDATE = "from_date";
		public static final String COL_TODATE = "to_date";
		public static final String COL_RULE = "rule";
		public static final String COL_INTERVAL = "interval";
		public static final String COL_SOUND = "sound";
/**/		
		public static final String TABLE_ALARMTIME = "alarmtime";
		public static final String COL_ALARMID = "alarm_id";
		public static final String COL_AT = "at";
/**/
		
		public static final String TABLE_ALARMMSG = "alarmmsg";
		public static final String COL_DATETIME = "datetime";
		public static final String COL_STATUS = "status";
/**/		
		
		private static final String TAG = "ContactsDbAdapter";
		private DatabaseHelper mDbHelper;
		private SQLiteDatabase mDb;

		private static final String DATABASE_NAME = "Agenda04.db";
		private static final String SQLITE_TABLE = "alarm";
		private static final int DATABASE_VERSION = 1;
		
		public static final String ACTIVE = "A";
		public static final String EXPIRED = "E";
		public static final String CANCELLED = "C";
		public static final String DEFERRED = "D";

		private final Context mCtx;

		public static final String DEFAULT_DATE_FORMAT = "yyyy-M-d";

		public static final SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_FORMAT);

		
		String DATABASE_CREATE_ALARM = Sql_table_alarm();
		String DATABASE_CREATE_ALARMTIME = Sql_table_alarmTime();
		String DATABASE_CREATE_ALARMMSG = Sql_table_alarmMsg();
		
		AlarmMsg alarmMsg = new AlarmMsg();
		
		
		String Sql_table_alarm()
		{
			return Utils.concat("CREATE TABLE ", TABLE_ALARM, " (",
					COL_ID, " INTEGER PRIMARY KEY AUTOINCREMENT, ",
					COL_CREATEDTIME, " INTEGER, ",
					COL_MODIFIEDTIME, " INTEGER, ",
					COL_NAME, " TEXT, ",
					COL_FROMDATE, " DATE, ",
					COL_TODATE, " DATE, ",
					COL_RULE, " TEXT, ",				
					COL_INTERVAL, " TEXT, ",
					COL_SOUND, " INTEGER",
					");");
		}
		
		String Sql_table_alarmTime() 
		{
			return Utils.concat("CREATE TABLE ", TABLE_ALARMTIME, " (",
					COL_ID, " INTEGER PRIMARY KEY AUTOINCREMENT, ",
					COL_ALARMID, " INTEGER, ",
					COL_AT, " INTEGER",
					");");
		}
		
		String Sql_table_alarmMsg() 
		{
			return Utils.concat("CREATE TABLE ", TABLE_ALARMMSG, " (",
					COL_ID, " INTEGER PRIMARY KEY AUTOINCREMENT, ",
					COL_ALARMID, " INTEGER, ",
					COL_DATETIME, " INTEGER, ",
					COL_STATUS, " TEXT",
					");");
		}

		 private class DatabaseHelper extends SQLiteOpenHelper // private static class
		 {
		
				  DatabaseHelper(Context context)
				  {
					  super(context, DATABASE_NAME, null, DATABASE_VERSION);
				  }
		
		
				  @Override
				  public void onCreate(SQLiteDatabase db)
				  {
					  Log.w(TAG, DATABASE_CREATE_ALARM);
					  db.execSQL(DATABASE_CREATE_ALARM);
					  
					  Log.w(TAG, DATABASE_CREATE_ALARMTIME);
					  db.execSQL(DATABASE_CREATE_ALARMTIME);
					  
					  Log.w(TAG, DATABASE_CREATE_ALARMTIME);
					  db.execSQL(DATABASE_CREATE_ALARMMSG);

				  }
		
				  @Override
				  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
				  {
					   //Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					   //  + newVersion + ", which will destroy all old data");
					   //db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
					   
					   db.execSQL("DROP TABLE IF EXISTS " + TABLE_ALARM);
					   db.execSQL("DROP TABLE IF EXISTS " + TABLE_ALARMTIME);
					   db.execSQL("DROP TABLE IF EXISTS " + TABLE_ALARMMSG);
					   
					   onCreate(db);
				  }
		 }

		 public DbAdapter(Context ctx)
		 {
			this.mCtx = ctx;
		 }

		 public DbAdapter open() throws SQLException 
		 {
			 mDbHelper = new DatabaseHelper(mCtx);
			 mDb = mDbHelper.getWritableDatabase();
			 return this;
		 }

		 public void close()
		 {
			 if(mDbHelper != null)
			 {
				 mDbHelper.close();
			 }
		 }
		 
		 
		

		 public void populate(long alarmId) 
		 {
			 
			 String populateSQL = Utils.concat("SELECT ",
						"a."+ COL_FROMDATE+", ",
						"a."+ COL_TODATE+", ",
						"a."+ COL_RULE+", ",
						"a."+ COL_INTERVAL+", ",
						"at."+ COL_AT,
						" FROM "+ TABLE_ALARM +" AS a",
						" JOIN "+ TABLE_ALARMTIME +" AS at",
						" ON a."+ COL_ID+" = at."+ COL_ALARMID,
						" WHERE a."+ COL_ID+" = ?"); 
			 

			 String[] selectionArgs = {String.valueOf(alarmId)};
			 Cursor c = mDb.rawQuery(populateSQL, selectionArgs);


			 if (c.moveToFirst()) 
			 {			
				 Calendar cal = Calendar.getInstance(); 			
				// AlarmMsg alarmMsg = new AlarmMsg();
				 long now = System.currentTimeMillis();
				 mDb.beginTransaction();

				 try {
					 	do {
					 			//String toto= c.getString(0);
					 		
					 			java.util.Date fromDate = sdf.parse(c.getString(0)); //yyyy-M-d
					 			cal.setTime(fromDate);

								//at
								String[] tokens = c.getString(4).split(":"); //hh:mm
								cal.set(Calendar.HOUR_OF_DAY, Integer.parseInt(tokens[0]));
								cal.set(Calendar.MINUTE, Integer.parseInt(tokens[1]));
								cal.set(Calendar.SECOND, 0);
								cal.set(Calendar.MILLISECOND, 0);
								
								String rule = c.getString(2); //every day month
								String interval = c.getString(3); //mm hh dd MM yy
								
								
								int l1 = rule.length();
								int l2 = interval.length();
								
								//if (rule == null && interval == null) 
								if(rule.equals("null") && interval.equals("null") )
								{
									//one time
									//alarmMsg.reset();
									alarmMsg.setAlarmId(alarmId);
									alarmMsg.setDateTime(cal.getTimeInMillis());
									if (alarmMsg.getDateTime() < now-Utils.MIN)
									{	
										alarmMsg.setStatus(AlarmMsg.EXPIRED);
									}
									else
									{
										alarmMsg.setStatus(AlarmMsg.ACTIVE);

									}
										
									save_msg();
								} 


					 	} while(c.moveToNext());

					 	mDb.setTransactionSuccessful();
				 } 
				 
				 catch (Exception e) 
				 {
					 //Log.e(TAG, e.getMessage(), e);
				 } 
				 
				 finally 
				 {
					 mDb.endTransaction();
				 }
			 }
			 
			 c.close();
		 }
		 
		 public void infos()
		 {
			 Cursor c = mDb.rawQuery("select * from alarm",null);
			 String [] toto = c.getColumnNames();
			 int cnt = c.getCount();
			 
			 if (c.moveToFirst())
			 {
                    do 
                    {
                    	 long idx = c.getLong(c.getColumnIndex("created_time"));
            			 String pipo = c.getString(c.getColumnIndex("name"));
                    
                    } while (c.moveToNext());
				 
			  }
		 }
		 
		 
		 public Cursor listNotifications(String... args) 
		 {
			 
			 String listSQL = Utils.concat("SELECT ",
						"a."+Alarm.COL_NAME+", ",
						"am."+AlarmMsg.COL_ID+", ",
						"am."+AlarmMsg.COL_DATETIME+", ",
						"am."+AlarmMsg.COL_STATUS,
				" FROM "+ Alarm.TABLE_NAME+" AS a",
				" JOIN "+ AlarmMsg.TABLE_NAME+" AS am",
				" ON a."+ Alarm.COL_ID+" = am."+AlarmMsg.COL_ALARMID);	
			 
				String selection = "am."+AlarmMsg.COL_STATUS+" != '"+AlarmMsg.CANCELLED+"'";
				selection += (args!=null && args.length>0 && args[0]!=null) ? " AND am."+AlarmMsg.COL_DATETIME+" >= "+args[0] : "";
				selection += (args!=null && args.length>1 && args[1]!=null) ? " AND am."+AlarmMsg.COL_DATETIME+" <= "+args[1] : "";		
				
				String sql = Utils.concat(listSQL,
										" WHERE "+selection,
										" ORDER BY am."+AlarmMsg.COL_DATETIME+" ASC");

				return mDb.rawQuery(sql, null);		
		}
		 
		 
		 
	
		 public long addAlarm(Alarm obj)
		 {
		 					ContentValues vals = new ContentValues();
		 					long now = System.currentTimeMillis();
		 					vals.put(COL_CREATEDTIME, now);
		 					vals.put(COL_MODIFIEDTIME, now);
		 					vals.put(COL_NAME, obj.getName());
		 					vals.put(COL_FROMDATE, obj.getFromDate());
		 					vals.put(COL_TODATE, obj.getToDate());
		 					vals.put(COL_RULE, obj.getRule());
		 					vals.put(COL_INTERVAL, obj.getInterval());
		 					vals.put(COL_SOUND, obj.getSound());
		 					long id = mDb.insert(TABLE_ALARM, null, vals);
		 					return id;
		 					//mDb.close();
		 }
		 
		
		 
		 public long addAlarmTime(AlarmTime obj)
		 {
		 					ContentValues vals = new ContentValues();
		 					vals.put(COL_ALARMID, obj.getAlarmId());
		 					vals.put(COL_AT, obj.getAt());
		 					long id = mDb.insert(TABLE_ALARMTIME, null, vals);
		 					return id;
		 					//mDb.close();
		 }
		 
	
		 
		 
		 public Cursor query(String query) 
		 {
				return mDb.rawQuery(query, null);
		 }
		 
		 public Cursor requete(String alarmMsgId) 
		 {
			 Cursor c;
			 String COL_ID = "_id";
			 String TABLE_ALARMMSG = "alarmmsg";
			 
			 c = mDb.query(AlarmMsg.TABLE_NAME, null, COL_ID+" = ?", new String[]{alarmMsgId}, null, null, null);
			 return c;
			 //return mDb.rawQuery(query, null);
		 }

		 
		 public long save_alarm(Alarm obj) 
		 {
				ContentValues vals = new ContentValues();
				long now = System.currentTimeMillis();
				vals.put(COL_CREATEDTIME, now);
				vals.put(COL_MODIFIEDTIME, now);
				vals.put(COL_NAME, obj.getName());
				vals.put(COL_FROMDATE, obj.getFromDate());
				vals.put(COL_TODATE, obj.getToDate());
				vals.put(COL_RULE, obj.getRule());
				vals.put(COL_INTERVAL, obj.getInterval());
				vals.put(COL_SOUND, obj.getSound());
				long id = mDb.insert(TABLE_ALARM, null, vals);
				return id;
			}
		 
		 public long save_msg()
		 {
				//AlarmMsg alarm_msg = new AlarmMsg();
			 
				long alarmId = alarmMsg.getAlarmId();
				long datetime = alarmMsg.getDateTime();
				String status = alarmMsg.getStatus();
				ContentValues cv = new ContentValues();
				cv.put(COL_ALARMID, alarmId);
				cv.put(COL_DATETIME, datetime);
				cv.put(COL_STATUS,  ACTIVE );
				return mDb.insert(TABLE_ALARMMSG, null, cv);
			}
		 
		 
		 AlarmMsg load_msg(long id) 
		 {
			    //long id = alarmMsg.getId();
				AlarmMsg toto = new AlarmMsg();

			 
				Cursor cursor = mDb.query(TABLE_ALARMMSG, null, COL_ID+" = ?", new String[]{String.valueOf(id)}, null, null, null);
				try 
				{
					if (cursor.moveToFirst()) 
					{
						toto.reset();
						//super.load(cursor);
						//long a = cursor.getLong(cursor.getColumnIndex(COL_ALARMID));
						toto.setAlarmId(cursor.getLong(cursor.getColumnIndex(COL_ALARMID)));
						
						//datetime = cursor.getLong(cursor.getColumnIndex(COL_DATETIME));
						toto.setDateTime(cursor.getLong(cursor.getColumnIndex(COL_DATETIME)));
						
						//status = cursor.getString(cursor.getColumnIndex(COL_STATUS));
						toto.setStatus(cursor.getString(cursor.getColumnIndex(COL_STATUS)));
					}
					
				} 
				finally
				{
					cursor.close();
				}
				
				return toto;
		 }
		 
		 
		 
		 
		 public Cursor fetchAllContacts() 
		 {
			    
			    //String countQuery = "SELECT  * FROM " + SQLITE_TABLE;
		        //Cursor cursor = mDb.rawQuery(countQuery, null);
		        //cursor.close();
			    
				//Integer result = cursor.getCount();
			 	//cursor = mDb.query(SQLITE_TABLE, columns, null, null, null, null, null, null  );
			 	
			 	Cursor cursor = mDb.query(SQLITE_TABLE, new String[] { Alarm.COL_NAME, AlarmMsg.COL_DATETIME, AlarmMsg.COL_DATETIME, AlarmMsg.COL_DATETIME, AlarmMsg.COL_DATETIME }, null, null, null, null, null);
			 
			 	//Cursor cursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID, KEY_CODE, KEY_NAME, KEY_CONTINENT, KEY_REGION}, null, null, null, null, null);

			 	if (cursor != null)
			 	{
			 		cursor.moveToFirst();
			 	}
			 	return cursor;
		 }
		 
		 
		 public Cursor list_msg(String... args) 
		 {
				String[] columns = {COL_ID, COL_ALARMID, COL_DATETIME, COL_STATUS};
				String selection = "1 = 1";
				selection += (args!=null && args.length>0 && args[0]!=null) ? " AND "+COL_ALARMID+" = "+args[0] : "";
				selection += (args!=null && args.length>1 && args[1]!=null) ? " AND "+COL_DATETIME+" >= "+args[1] : "";
				selection += (args!=null && args.length>2 && args[2]!=null) ? " AND "+COL_DATETIME+" <= "+args[2] : "";
				selection += (args!=null && args.length>3 && args[3]!=null) ? " AND "+COL_STATUS+" = '"+args[3]+"'" : "";
				String orderBy = (args!=null && args.length>4) ? args[4] : COL_DATETIME+" DESC";
				
				return mDb.query(TABLE_ALARMMSG, columns, selection, null, null, null, orderBy);
			}
		 
		 
		 public String alarm_load(long id) 
		 {
				Cursor cursor = mDb.query(TABLE_ALARM, null, COL_ID+" = ?", new String[]{String.valueOf(id)}, null, null, null);
				String name = "";
				
					if (cursor.moveToFirst())
					{
						//reset();
						//super.load(cursor);
						//createdTime = cursor.getLong(cursor.getColumnIndex(COL_CREATEDTIME));
						//modifiedTime = cursor.getLong(cursor.getColumnIndex(COL_MODIFIEDTIME));
						name = cursor.getString(cursor.getColumnIndex(COL_NAME));
						//fromDate = cursor.getString(cursor.getColumnIndex(COL_FROMDATE));
						//toDate = cursor.getString(cursor.getColumnIndex(COL_TODATE));
						//rule = cursor.getString(cursor.getColumnIndex(COL_RULE));
						//interval = cursor.getString(cursor.getColumnIndex(COL_INTERVAL));
						//sound = cursor.getInt(cursor.getColumnIndex(COL_SOUND)) == 1 ? true : false;
						cursor.close();
						
					}
					
					return name;
				
			}
		 
		 
		 public Alarm load_alarm(long id) 
		 {
				Cursor cursor = mDb.query(TABLE_ALARM, null, COL_ID+" = ?", new String[]{String.valueOf(id)}, null, null, null);
				Alarm toto = new Alarm();		
				
					if (cursor.moveToFirst())
					{
						//reset();
						//super.load(cursor);
						toto.setId(id);
						toto.setCreatedTime(cursor.getLong(cursor.getColumnIndex(COL_CREATEDTIME)) ) ;
						toto.setModifiedTime(cursor.getLong(cursor.getColumnIndex(COL_MODIFIEDTIME)) );
						toto.setName(cursor.getString(cursor.getColumnIndex(COL_NAME)) ) ;
						toto.setFromDate(cursor.getString(cursor.getColumnIndex(COL_FROMDATE)) );
						toto.setToDate(cursor.getString(cursor.getColumnIndex(COL_TODATE)) );
						toto.setRule(cursor.getString(cursor.getColumnIndex(COL_RULE)));
						toto.setInterval(cursor.getString(cursor.getColumnIndex(COL_INTERVAL)) );
						/////////int plouf = cursor.getInt(cursor.getColumnIndex(COL_SOUND)); 
						////////toto.setSound(false);
						boolean sound = cursor.getInt(cursor.getColumnIndex(COL_SOUND)) == 1 ? true : false;
						toto.setSound(sound);
						cursor.close();
						
					}
					
				return toto;	
				
			}
		 
		 boolean update(Alarm obj)
		 {
			 	/*
			 	ContentValues cv = new ContentValues();
				super.update(cv);
				cv.put(COL_MODIFIEDTIME, System.currentTimeMillis());
				if (name != null)
					cv.put(COL_NAME, name);
				if (fromDate != null)
					cv.put(COL_FROMDATE, fromDate);
				if (toDate != null)
					cv.put(COL_TODATE, toDate);
				if (rule != null)
					cv.put(COL_RULE, rule);
				if (interval != null)
					cv.put(COL_INTERVAL, interval);		
				if (sound != null)
					cv.put(COL_SOUND, sound ? 1 : 0);
				*/	
			 
				ContentValues cv = new ContentValues();
				cv.put(COL_MODIFIEDTIME, System.currentTimeMillis());
				
				long id = obj.getId();
				
				String name = obj.getName();
				if (name != null)
					cv.put(COL_NAME, name);
				
				String fromDate = obj.getFromDate();
				if (fromDate != null)
					cv.put(COL_FROMDATE, fromDate);

				String toDate = obj.getToDate();
				Boolean a = toDate.equals("null");
				if ( a != true) 
				//if (toDate != null)
					cv.put(COL_TODATE, toDate);

				String rule = obj.getRule();
				Boolean b = rule.equals("null");
				if ( b != true) 
				//if (rule != null)
					cv.put(COL_RULE, rule);
					
				String interval = obj.getInterval();
				Boolean c = interval.equals("null");
				if ( c != true) 
				//if (interval != null)
					cv.put(COL_INTERVAL, interval);	
						
				Boolean sound = obj.getSound();
				//Boolean d = sound.equals("null"); ///////////////////////////////// mettre 0 
				//if ( sound != false) 
				//if (sound != null)
				cv.put(COL_SOUND, sound ? 1 : 0);		
				
				return mDb.update(TABLE_ALARM, cv, COL_ID+" = ?", new String[]{String.valueOf(id)}) == 1 ? true : false;
			}
		 
		
		 public int cancelNotification(long id, boolean isAlarmId) 
		 {
				ContentValues cv = new ContentValues();
				cv.put(AlarmMsg.COL_STATUS, AlarmMsg.CANCELLED);
				return mDb.update(TABLE_ALARMMSG,
									cv, 
									(isAlarmId ? AlarmMsg.COL_ALARMID : AlarmMsg.COL_ID)+" = ?", 
									new String[]{String.valueOf(id)});
		}
			
		public int cancelNotification(String startTime, String endTime)
		{
				ContentValues cv = new ContentValues();
				cv.put(AlarmMsg.COL_STATUS, AlarmMsg.CANCELLED);
				return mDb.update(AlarmMsg.TABLE_NAME, 
									cv, 
									AlarmMsg.COL_DATETIME+" >= ? AND "+AlarmMsg.COL_DATETIME+" <= ?", 
									new String[]{startTime, endTime});
		}	
		
		public void shred()
		{
			mDb.delete(TABLE_ALARMMSG, AlarmMsg.COL_STATUS+" = ?", new String[]{AlarmMsg.CANCELLED});
		}
}