package com.artisanalapps;

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

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class RecordCollectionDataHelper {
	
	// Database constants
	private static final String DATABASE_NAME = "mycollection.db";
	private static final String DATABASE_TABLE = "myrecords";
	private static final int DATABASE_VERSION = 1;
	
	// Column Names
	private static final String KEY_ID = "_id";
	private static final String KEY_ARTIST = "artist";
	private static final String KEY_TITLE = "title";
	private static final String KEY_LABEL = "label";
	private static final String KEY_CATALOG = "catalog";
	private static final String KEY_YEAR = "year";
	private static final String KEY_CONDITION = "condition";
	
	// useful fields in the class
	private Context context;
	private SQLiteDatabase db;

	// a SQL statement to create a new table
	private static final String RECORD_COLLECTION_CREATE =
			"create table " + DATABASE_TABLE
			+ " (" + KEY_ID + " integer primary key autoincrement, " + KEY_ARTIST + " text, "
			+ KEY_TITLE + " text, " + KEY_LABEL + " text, " + KEY_CATALOG + " text, "
			+ KEY_YEAR + " text, " + KEY_CONDITION + " integer);";
	
	// a SQL statement to insert into a table
	private SQLiteStatement insertStmt;
	private static final String INSERT =
		"insert into " + DATABASE_TABLE
		+ "(" + KEY_ARTIST + "," + KEY_TITLE + "," + KEY_LABEL + "," 
		+ KEY_CATALOG + "," + KEY_YEAR + "," + KEY_CONDITION + ")"
		+ " values (?,?,?,?,?,?)";
	
	// a SQL statement to update an entry in the table
	// Specifically - this will be used to update the condition of a record
	private SQLiteStatement updateStmt;
	private static final String UPDATE =
		"update " + DATABASE_TABLE + " set " + KEY_CONDITION + "=?" 
		+ " where " + KEY_ID + "=?";
	
	// a SQL statement to delete an entry from a table
	// We are using the combination of catalog number and format to find
	// a unique entry to delete (will actually delete all of this type of entry
	// if there were more than one)
	private SQLiteStatement deleteStmt;
	private static final String DELETE =
		"delete from " + DATABASE_TABLE
		+ " where " + KEY_ID + "=?";

	// class constructor
	public RecordCollectionDataHelper(Context context) {
		this.context = context;
		MySQLiteHelper dbHelper = new MySQLiteHelper(this.context); //SQLiteOpenHelper
		this.db = dbHelper.getWritableDatabase();                   //SQLiteDatabase
		
		// compile the statements up front, then just bind variables before executing each time
		this.insertStmt = this.db.compileStatement(INSERT);
		this.updateStmt = this.db.compileStatement(UPDATE);
		this.deleteStmt = this.db.compileStatement(DELETE);
	}
	
	/** Insert a new record entry and set the record ID in the record class
	 * 
	 * @param record
	 * @return the row ID of the row inserted (-1 for a failed insert)
	 */
	public long insert(Record record) {
		long rowId = insert(record.getArtist(), record.getTitle(), record.getLabel(), 
				record.getCatalog(), record.getYear(), record.getCondition());
		// now that we have the incremented row ID we can set it to our record object
		if (rowId != -1){
			record.setId(rowId);
		}
		return rowId;
	}
	
	/** Private helper method to insert a new record entry
	 * 
	 * @param artist
	 * @param title
	 * @param label
	 * @param catalog, catalog #
	 * @param year
	 * @param condition (0 through 5, for F, G, VG, VG+, M-, M)
	 * @return the row ID of the row inserted, if this insert is successful. -1 otherwise.
	 */
	private long insert(String artist, String title, String label, String catalog, String year, int condition) {
		this.insertStmt.bindString(1, artist);
		this.insertStmt.bindString(2, title);
		this.insertStmt.bindString(3, label);
		this.insertStmt.bindString(4, catalog);
		this.insertStmt.bindString(5, year);
		this.insertStmt.bindLong(6, (long) condition);
		return this.insertStmt.executeInsert();
	}
	
	/** Update the condition of a record
	 * The record already exists - we are just going to update the condition
	 * of the record
	 * 
	 * @param record
	 * @param condition (0 through 5, for F, G, VG, VG+, M-, M)
	 */
	public void update(Record record, int condition) {
		update(record.getId(), condition);
	}
	
	/** Update an existing record
	 * 
	 * @param the record id
	 * @param condition
	 */
	private void update(long id, int condition ) {
		this.updateStmt.bindLong(1, (long) condition);
		this.updateStmt.bindLong(2, id);
		this.updateStmt.execute();
	}


	/** Delete a record
	 * 
	 * @param record
	 */
	public void delete(Record record) {
		delete(record.getId());
	}
	
	/** Private helper method to delete a record
	 * 
	 * @param recordId
	 */
	private void delete(long id) {
		this.deleteStmt.bindLong(1, (long) id);
		this.deleteStmt.execute();
	}
	
	/**
	 * Close the DB
	 */
	public void close () {
		this.db.close();
	}
	
	/**
	 * Pull all records in record collection.
	 * The records will be displayed in alphabetical order by artist.
	 * 
	 * @return a list of Records 
	 */
	public List<Record> viewCollection() {

		String[] result_columns = new String[] { KEY_ID, KEY_ARTIST, KEY_TITLE,
				KEY_LABEL, KEY_CATALOG, KEY_YEAR, KEY_CONDITION };
		
		List<Record> list = new ArrayList<Record>();

		// build the query, condition and params are set appropriately already
		// note that the results will be sorted alphabetically by artist
		Cursor cursor = this.db.query(DATABASE_TABLE, result_columns,
				null, null, null, null, KEY_ARTIST + " asc");

		if (cursor.moveToFirst()) { // do we have anything at all?
			do {
				list.add(entryFromCursor(cursor)); // build the entry and add to
													// the list
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			//must close the cursor
			cursor.close();
		}
		return list;
	}

	/**
	 * Pull the Records from the DB for a given artist.
	 * The records will be in order by year.
	 * 
	 * @param artist
	 * @return a list of Records
	 */
	public List<Record> getByArtist(String artist) {

		String[] result_columns = new String[] { KEY_ID, KEY_ARTIST, KEY_TITLE,
				KEY_LABEL, KEY_CATALOG, KEY_YEAR, KEY_CONDITION };
		String condition = KEY_ARTIST + "=?";
		String[] params = { artist };
		
		List<Record> list = new ArrayList<Record>();

		// build the query, condition and params are set appropriately already
		Cursor cursor = this.db.query(DATABASE_TABLE, result_columns,
				condition, params, null, null, KEY_YEAR + "asc");

		if (cursor.moveToFirst()) { // do we have anything at all?
			do {
				list.add(entryFromCursor(cursor)); // build the entry and add to
													// the list
			} while (cursor.moveToNext());
		}
		
		if (cursor != null && !cursor.isClosed()) {
			// always close the cursor
			cursor.close();
		}
		return list;
	}


	// pull all the relevant information from the cursor to create a Record
	// use getColumnIndexOrThrow to retrieve the correct pieces of data 
	private Record entryFromCursor(Cursor cursor) {
		return new Record(cursor.getLong(cursor
				.getColumnIndexOrThrow(KEY_ID)), cursor.getString(cursor
				.getColumnIndexOrThrow(KEY_ARTIST)), cursor.getString(cursor
				.getColumnIndexOrThrow(KEY_TITLE)), cursor.getString(cursor
				.getColumnIndexOrThrow(KEY_LABEL)), cursor.getString(cursor
				.getColumnIndexOrThrow(KEY_CATALOG)), cursor.getString(cursor
				.getColumnIndexOrThrow(KEY_YEAR)), cursor.getInt(cursor
				.getColumnIndexOrThrow(KEY_CONDITION)));
	}
	
	
	
	// define an extension of the SQLiteOpenHelper to handle the
	// creation and upgrade of a table
	private static class MySQLiteHelper extends SQLiteOpenHelper {

		// Class constructor
		public MySQLiteHelper(Context context) {
			// instantiate a SQLiteOpenHelper by passing it
			// the context, the database's name, a CursorFactory
			// (null by default), and the database version.			
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		// called by the parent class when a DB doesn't exist
		public void onCreate(SQLiteDatabase database) {
			database.execSQL(RECORD_COLLECTION_CREATE);
		}

		@Override
		// called by the parent when a DB needs to be upgraded
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(MySQLiteHelper.class.getName(),
					"Upgrading database from version " + oldVersion + " to "
							+ newVersion + ", which will destroy all old data");
			// remove the old version and create a new one.
			// If we were really upgrading we'd try to move data over			
			db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE);
			onCreate(db);
		}

	}

}