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.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 = 5;

		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, 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(' ', '%');		
		search = "%" + search + "%";

		String sql = "SELECT id AS _id, name, inits, company FROM people WHERE name 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) { }
			}
		}
	}

	private void loadData(String data)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		db.execSQL("DELETE FROM people");
		StringTokenizer lines = new StringTokenizer( data, "|" );

		SQLiteStatement stmt = db.compileStatement("INSERT INTO people (name,inits,title,dept,company,phone,cell,email) VALUES (?,?,?,?,?,?,?,?);");
		while (lines.hasMoreTokens())
		{  
			String line = lines.nextToken(); 

			try
			{
				StringTokenizer fields = new StringTokenizer(line, ";");
				stmt.bindString(1, fields.nextToken().trim() );
				stmt.bindString(2, fields.nextToken().trim() );
				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.execute();
			} catch (Exception e) {
				Log.e("PeopleDataBase", "Token error: " + line, e);
			}

		}
		stmt.close();
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();

	}

	public void loadData() throws Exception
	{
		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);
		StringBuilder sb = new StringBuilder(1024000);
		String line;
		
		while ( (line = input.readLine()) != null) {
			sb.append(line);
			sb.append("\n");
		}
		
		is.close();
		Log.i("PeopleDatabase", "Parsing data");

		loadData(sb.toString());

		Log.i("PeopleDatabase", "Parsing finished");
	}
	
	public void close()
	{
		dbHelper.close();
	}
	

}