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.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class UsbDbAdapter 
{
	public static final String DB_NAME = "usbIds.db";
	public static final int DB_VERSION_1 = 1;
	
	private static final String VERSION_TABLE = "versionTable";
	private static final String VENDOR_TABLE = "vendorTable";
	private static final String PRODUCT_TABLE = "productTable";
	
	//Version Table Columns
	private static final String VERSION_COLUMN = "version";
	private static final String DATE_COLUMN = "date";
	private static final String ID_VERSION = "id_version";
	
	// Vendor Table Columns
	private static final String KEY_ID = "_id";
	private static final String VID_COLUMN = "vid";
	private static final String VID_NAME_COLUMN = "vid_name";
	
	// Product Table Columns
	private static final String KEY_ID_PRODUCT = "_id";
	private static final String VID_PRODUCT_COLUMN = "vid";
	private static final String PID_PRODUCT_COLUMN = "pid";
	private static final String PID_NAME_PRODUCT_COLUMN = "pid_name";
	
	// SQL Statements
	private static final String CREATE_VERSION_TABLE = "create table " + VERSION_TABLE + "("
			+ KEY_ID + " integer primary key autoincrement, " + VERSION_COLUMN + " text not null, "
			+ DATE_COLUMN + " text not null, " + ID_VERSION + " integer);";
	
	private static final String CREATE_VENDOR_TABLE = "create table " + VENDOR_TABLE + "(" 
			 +KEY_ID + " integer primary key autoincrement, " + VID_COLUMN + " text not null, " + VID_NAME_COLUMN + " text not null);";
	
	private static final String CREATE_PRODUCT_TABLE = "create table " + PRODUCT_TABLE + "("
			+ KEY_ID_PRODUCT + " integer primary key autoincrement, " + VID_PRODUCT_COLUMN + " text not null, "
			+ PID_PRODUCT_COLUMN + " text not null, " + PID_NAME_PRODUCT_COLUMN + " text not null);" ;
	
	private static final String SELECT_VENDOR_PRODUCT = "select vid_name, pid_name from " 
	+ VENDOR_TABLE + ", " + PRODUCT_TABLE + " where " + VENDOR_TABLE +".vid = ?" + " and " + PRODUCT_TABLE + ".vid = ? " + "and pid = ?"; 
	
	private static final String SELECT_VENDOR = "select vid, vid_name from " + VENDOR_TABLE + " where vid = ?";
	
	private static final String SELECT_VERSION = "select " + VERSION_COLUMN + " from " + VERSION_TABLE;
	
	private static final String SELECT_VERSION_ID = "select " + ID_VERSION + " from " + VERSION_TABLE;
	
	private static final String INSERT_VENDOR = "insert into " + VENDOR_TABLE + " (" + VID_COLUMN + ", " + VID_NAME_COLUMN
			+ ") values(?,?)";
	
	private static final String INSERT_PRODUCT = "insert into " + PRODUCT_TABLE + " (" + VID_PRODUCT_COLUMN 
			+ ", " + PID_PRODUCT_COLUMN + ", " + PID_NAME_PRODUCT_COLUMN  + ") values(?,?,?)";
	
	// Compiled SQL Statements
	private static SQLiteStatement insertVendor;
	private static SQLiteStatement insertProduct;
	
	
	private SQLiteDatabase db;
	private DbHelper dbHelper;
	private int currentVersion;
	
	public UsbDbAdapter(Context context, int version)
	{
		this.currentVersion = version;
		dbHelper = new DbHelper(context, DB_NAME, null, version);
	}
	
	public UsbDbAdapter open() throws SQLException
	{
		db = dbHelper.getWritableDatabase();
		return this;
	}
	
	public void close()
	{
		db.close();
	}
	
	public void beginTransaction()
	{
		db.beginTransaction();
	}
	
	public void endTransaction()
	{
		db.endTransaction();
	}
	
	public void setTransactionSuccesful()
	{
		db.setTransactionSuccessful();
	}
	
	public long insertEntryVersion(String version, String date)
	{
		ContentValues values = new ContentValues();
		values.put(VERSION_COLUMN, version);
		values.put(DATE_COLUMN, date);
		values.put(ID_VERSION, currentVersion);
		return db.insert(VERSION_TABLE, null, values);
	}
	
	public void insertEntryVendor(String vid, String vidName)
	{
		insertVendor.bindString(1, vid);
		insertVendor.bindString(2, vidName);
		insertVendor.execute();
		insertVendor.clearBindings();
	}
	
	public void insertEntryProduct(UsbData data)
	{
		insertProduct.bindString(1, data.getVendorId());
		insertProduct.bindString(2, data.getProductId());
		insertProduct.bindString(3, data.getProductName());
		insertProduct.execute();
		insertProduct.clearBindings();
	}
	
	public String queryLocalVersion()
	{
		Cursor cursor = db.rawQuery(SELECT_VERSION, null);
		cursor.moveToFirst();
		return cursor.getString(0);
	}
	
	public int queryLocalVersionId()
	{
		Cursor cursor = db.rawQuery(SELECT_VERSION_ID, null);
		cursor.moveToFirst();
		return cursor.getInt(0);
	}
	
	public UsbData query(String vid, String pid)
	{
		if(validateInput(vid, pid))
		{
			Cursor cursor = db.rawQuery(SELECT_VENDOR_PRODUCT, new String[]{vid, vid, pid});
			cursor.moveToFirst();
			if(cursor.getCount() > 0)
			{
				String vidName = cursor.getString(0);
				String pidName = cursor.getString(1);
				return new UsbData(vid, vidName, pid, pidName);
			}else
			{
				cursor = db.rawQuery(SELECT_VENDOR, new String[]{vid});
				cursor.moveToFirst();
				if(cursor.getCount() > 0)
				{
					String stringName = cursor.getString(1);
					return new UsbData(vid, stringName, pid, "None");
				}else
				{
					return new UsbData("None", "None", "None", "None");
				}
			}
		}else
		{
			return null;
		}
	}
	
	
	private boolean validateInput(String vid, String pid)
	{
		if((vid.length() > 4 || pid.length() > 4))
			return false;
		if(checkHexValue(vid) && checkHexValue(pid))
			return true;
		else
			return false;
	}
	
	private boolean checkHexValue(String value)
	{
		
		for(int i=0;i<=value.length()-1;i++)
		{
			if(value.charAt(i) < 0x30 || value.charAt(i) > 0x66)
			{
				return false;
			}else
			{
				if(value.charAt(i) > 0x39 && value.charAt(i) < 0x41)
				{
					return false;
				}else
				{
					if(value.charAt(i) > 0x46 && value.charAt(i) < 0x61)
					{
						return false;
					}
				}
			}
		}
		return true;
	}
	
	
	private static class DbHelper extends SQLiteOpenHelper
	{

		public DbHelper(Context context, String name, CursorFactory factory,
				int version) 
		{
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) 
		{
			db.execSQL(CREATE_VERSION_TABLE);
			db.execSQL(CREATE_VENDOR_TABLE);
			db.execSQL(CREATE_PRODUCT_TABLE);
			
			insertVendor = db.compileStatement(INSERT_VENDOR);
			insertProduct = db.compileStatement(INSERT_PRODUCT);
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			if(newVersion > oldVersion)
			{
				db.execSQL("DROP TABLE IF EXISTS " + VERSION_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + VENDOR_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + PRODUCT_TABLE);
                                db.execSQL("VACUUM");
				onCreate(db);
			}else
			{
				db.setVersion(oldVersion);
			}
		}
                
                @Override
		public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)
		{
			onUpgrade(db, oldVersion, newVersion);
		}
	}
}