package com.grundfos.android.people;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.util.StringTokenizer;

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 android.os.Handler;
import android.os.Message;
import android.util.Log;

public class PeopleDatabase  {

	class PeopleDatabaseHelper extends SQLiteOpenHelper
	{
		/** The name of the database file on the file system */
		private static final String DATABASE_NAME = "People";
		/** The version of the database that this class understands. */
		private static final int DATABASE_VERSION = 6;

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

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.beginTransaction();
			try {
				db.execSQL("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT,  lowername TEXT, name TEXT, inits TEXT, title TEXT, dept TEXT, company TEXT, phone TEXT, cell TEXT, email TEXT)");
				db.setTransactionSuccessful();
			} catch (SQLException e) {
				Log.e("Error creating tables and debug data", e.toString());
			} finally {
				db.endTransaction();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.beginTransaction();
			try {
				db.execSQL("DROP TABLE people");
				db.setTransactionSuccessful();
			} catch (SQLException e) {
				Log.e("Error creating tables and debug data", e.toString());
			} finally {
				db.endTransaction();
			}
			onCreate(db);
		}
	}

	PeopleDatabaseHelper dbHelper;

	public PeopleDatabase(Context context) {
		dbHelper = new PeopleDatabaseHelper(context);
	}


	public Cursor getPeopleList(String search) {
		search = search.replace(' ', '%').toLowerCase();		
		search = "%" + search + "%";

		String sql = "SELECT id AS _id, name, inits, company FROM people WHERE lowername LIKE ? ORDER BY name LIMIT 200";
		SQLiteDatabase d = dbHelper.getReadableDatabase();

		try {
			String[] args = {search};
			Cursor c = d.rawQuery(sql, args);
			return c;
		} catch (Exception e) {
			Log.e("people", "-", e);
		}

		return null;
	}

	public PeopleBean getPeople(long  id) {
		String sql = "SELECT id, name, inits, title, dept, company,phone,cell,email FROM people WHERE id = " + id + "";
		SQLiteDatabase d = dbHelper.getReadableDatabase();

		try {
			Cursor c = d.rawQuery(sql, null);

			c.moveToNext();
			PeopleBean people = new PeopleBean();
			people.id = c.getInt(0);
			people.name = c.getString(1);
			people.inits = c.getString(2);
			people.title = c.getString(3);
			people.dept = c.getString(4);
			people.company = c.getString(5);
			people.phone = c.getString(6);
			people.cell = c.getString(7);
			people.email = c.getString(8);

			c.close();
			d.close();
			return people;
		} catch (Exception e) {
			Log.e("people", "-", e);
		}

		return null;
	}

	/** Returns the number of Jobs */
	public int getPeopleCount(){

		Cursor c = null;
		try {
			SQLiteDatabase d = dbHelper.getReadableDatabase(); 
			c = d.rawQuery("SELECT count(*) FROM people", null);
			if (0 >= c.getCount()) { return 0; }
			c.moveToFirst();
			int count = c.getInt(0);

			c.close();
			d.close();

			return count;
		}
		finally {
			if (null != c) {
				try { c.close(); }
				catch (SQLException e) { }
			}
		}
	}


	class DataLoader
	{
		SQLiteDatabase db; 
		Handler handler;
		int count = 0;
		int maxCount = 30000;
		SQLiteStatement stmt;

		DataLoader(Handler hndl) {
			handler = hndl;
		}

		private void sendCount() {
			Message msg = Message.obtain();
			msg.what = 1;
			msg.arg1 = count;
			handler.sendMessage(msg);
		}		
		
		private void sendMaxCount()
		{
			Message msg = Message.obtain();
			msg.what = 0;
			msg.arg1 = maxCount;
			handler.sendMessage(msg);			
		}

		private void insertLine(String line)
		{
			try
			{
				StringTokenizer fields = new StringTokenizer(line, ";");
				String name = fields.nextToken().trim();
				stmt.bindString(1, name.toLowerCase() );
				stmt.bindString(2, name );
				stmt.bindString(3, fields.nextToken().trim() );
				stmt.bindString(4, fields.nextToken().trim() );
				stmt.bindString(5, fields.nextToken().trim() );
				stmt.bindString(6, fields.nextToken().trim() );
				stmt.bindString(7, fields.nextToken().trim() );    			
				stmt.bindString(8, fields.nextToken().trim() );
				stmt.bindString(8, fields.nextToken().trim() );
				stmt.execute();
			} catch (Exception e) {
				Log.e("PeopleDataBase", "Token error: " + line, e);
			}
		}

		public void loadData() throws Exception
		{
			try
			{
				db = dbHelper.getWritableDatabase();
				db.beginTransaction();
				db.execSQL("DELETE FROM people");			
				stmt = db.compileStatement("INSERT INTO people (lowername,name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?,?);");

				URL url = new URL("http://t-hoerup.dk/peopledata.txt");

				URLConnection conn = url.openConnection();
				conn.setConnectTimeout(1000);			
				InputStream is = conn.getInputStream();

				BufferedReader input = new BufferedReader( new InputStreamReader(is), 32768);
				String line;


				sendMaxCount();
				while ( (line = input.readLine()) != null) {
					if (line.charAt(0) == '#') { //Lines starting with # are comments and should not be parsed by loaddata worker func.
						if (line.contains("count:")) {
							maxCount = Integer.parseInt(line.substring(line.indexOf(':')+1).trim());
							sendMaxCount();
						}
						Log.i("PeopleDB", "Count:"+maxCount);

					} else {
						count ++;
						if ((count % 100) == 0)
							sendCount();
						insertLine(line);
					}
				}			
				is.close();
				db.setTransactionSuccessful();

			} finally {
				stmt.close();
				db.endTransaction();
				db.close();
			}

		}			
	}



	public void loadData(Handler hndl) throws Exception
	{
		DataLoader dl = new DataLoader(hndl);
		dl.loadData();
	}

	public void close()
	{
		dbHelper.close();
	}


}