package com.appideas.base;

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

import com.sharefaith.thesharefaithapp.base.SFAppData;
import com.sharefaith.thesharefaithapp.base.SFApplication;
import com.sharefaith.thesharefaithapp.base.SFConfig;
import com.sharefaith.thesharefaithapp.base.SFUtil;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

/**
 * Provides a basic database abstraction layer for Android from the  APPideas library
 *
 * @author		costmo
 * @version		20111206
 * @since		20111206
 */
public class AiDb extends SQLiteOpenHelper
{
	/**
	 * The path to the database file, relative to the device storage folder
	 */
	public static String DB_PATH = "/databases/";

	/**
	 * The database file name
	 */
	public static String DB_NAME = "com.sharefaith.churchapp.sqlite";

	protected String mDbName;
	protected String mDbPath;

	/**
	 * The database instance
	 */
	private SQLiteDatabase mDatabase;

	/**
	 * The applicaton instance for holding global variables
	 */
	private SFApplication mApplication = SFApplication.getInstance();

	/**
	 * The Context
	 */
	public Context mContext;

	/**
	 * The version of the database file
	 */
	public int mDataVersion;

	/**
	 * The number of results from the current query. Only safe to use after immediately a query
	 */
	public int mQueryCount;

	/**
	 * The names of the fields in the query.
	 */
	public String[] mFieldNames;

	/**
	 *A string for debugging
	 */
	private String mTag = "Class_AiDb";

	/**
	 * The full path to the database file on this device
	 */
	public String mDeviceDbPath;

	/**
	 * If the database had to go through initial setup, other pieces probably will also .Set to true to let the initial caller act accordingly.
	 */
	public boolean neededSetup;

	private boolean mUpgradeInProgress;

	private boolean mUsingSecondaryDatabase;

	private boolean mShouldUpdate;

	/**
	 * Class constructor
	 *
	 * @since		20111206
	 */
	public AiDb( Context context )
	{
		super( context, DB_NAME, null, 1 );
		this.mContext = context;
		this.mUsingSecondaryDatabase = false;
		this.setupMembers();

		// Create the database path if it does not exist already
		File f = new File( this.mContext.getFilesDir() + DB_PATH );
		if( !f.exists() )
		{
			f.mkdir();
		}
		this.openDatabase();
	} // AiDb( Context context )

	/**
	 * Overridden class constructor to open an alternate database file
	 *
	 * @since		20151210
	 */
	public AiDb( Context context, String dbPath, String dbName )
	{
		super( context, dbName, null, 1 );
		this.mContext = context;
		this.mDbPath = dbPath;
		this.mDbName = dbName;
		this.mUsingSecondaryDatabase = true;
		this.setupMembers();

		this.openDatabase();
	}

	private void setupMembers()
	{
		this.mDataVersion = 0;
		this.mQueryCount = 0;
		this.mFieldNames = new String[0];
		this.neededSetup = false;
		this.mUpgradeInProgress = false;

		/* Keeping the syntax checker happy for now */
		if( this.mDataVersion < 0 ) { }

		this.mDbName = (this.mDbName == null) ? DB_NAME : this.mDbName;
		this.mDbPath = (this.mDbPath == null) ? DB_PATH : this.mDbPath;
	}

	/**
	 * Creates an empty database on the system and rewrites it with your own database
	 *
	 * @since		20111206
	 */
	public void createDatabase() throws IOException
	{
		boolean dbExists = checkDatabase();

		if( dbExists || this.mUsingSecondaryDatabase )
		{
			//do nothing - database already exist
		}
		else
		{
			Log.d( mTag, "Db does not exist" );
			// Create an empty database in the default system path
			try
			{
				this.getReadableDatabase();
			}
			catch( Exception e )
			{
				Log.d( mTag, "EXCEPTION D4: " + e.toString() );
			}

			// copy the default, V1 database from the app bundle into the system path
			try
			{
				this.copyDatabase();
			}
			catch (IOException e)
			{
				throw new Error( "Error copying database: " + e.toString() );
			}
		}

	} //  public void createDatabase() throws IOException


	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.<p/>
	 *
	 * Returns true if it exists, else false
	 *
	 * @since		20111206
	 */
	private boolean checkDatabase()
	{
		SQLiteDatabase checkDB = null;

		try
		{
			File f = new File( this.mDeviceDbPath );
			if( f.exists() )
			{
				checkDB = SQLiteDatabase.openDatabase( this.mDeviceDbPath, null, SQLiteDatabase.OPEN_READONLY );
			}
		}
		catch( Exception e )
		{
			checkDB = null;
		}

		if( checkDB != null )
		{
			checkDB.close();
		}

		if( checkDB != null )
		{
			return true;
		}
		else
		{
			return false;
		}

	} // checkDatabase()


	/**
	 * Copies your database from your local assets-folder to the just-created empty database in the
	 * system folder, from where it can be accessed and handled.
	 * This is done by transferring bytestream.
	 *
	 * @since		20111206
	 */
	private void copyDatabase() throws IOException
	{
		InputStream myInput = null;

		//Open your local db as the input stream
		try
		{
			myInput = mContext.getAssets().open( DB_NAME );
		}
		catch( Exception e )
		{
			Log.d( mTag, "Exception DA1: " + e.toString() );
		}

		//Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream( this.mDeviceDbPath );
		//Log.d( "DATABASE", "Install database to path: " + this.mDeviceDbPath );

		//transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		try
		{
			while( ( length = myInput.read( buffer ) ) > 0 )
			{
				myOutput.write( buffer, 0, length );
			}
		}
		catch( Exception e )
		{
			Log.d( mTag, "Exception DA2: " + e.toString() );
		}

		//Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

		this.neededSetup = true;

	} //  private void copyDatabase() throws IOException

	/**
	 * Opens the database
	 *
	 * @since		20111206
	 */
	public void openDatabase() throws SQLiteException
	{
		//Open the database
		this.mDeviceDbPath = this.mContext.getFilesDir() + this.mDbPath + this.mDbName;

		if( this.mDatabase == null || !this.mDatabase.isOpen() )
		{
			if( !this.checkDatabase() )
			{
				try
				{
					this.createDatabase();
				}
				catch( Exception e )
				{
					throw new Error( "Cannot create the database" );
				}
			}

			try
			{
				this.mDatabase = SQLiteDatabase.openDatabase( this.mDeviceDbPath, null, SQLiteDatabase.OPEN_READWRITE );
			}
			catch( Exception e )
			{
				Log.d( mTag, "EXCEPTION: " + e.getMessage() );
			}
		}
	}

	@Override
	public synchronized void close()
	{
		if( mDatabase != null )
		{
			if( mDatabase.isOpen() )
			{
				mDatabase.close();
				super.close();
			}
		}

		// super.close();
	}

	@Override
	public void onCreate( SQLiteDatabase db )
	{

	}

	@Override
	public void onUpgrade( SQLiteDatabase db, int oldVersion, int newVersion )
	{

	}

	/**
	 * Inserts a blank record into the supplied database table and returns the value of the idField.<p/>
	 *
	 * Use this method if there are foreign key constraints to fulfill on insert.<p/>
	 *
	 * Returns 0 if the record insertion failed.
	 *
	 * @since		20111206
	 * @return		int
	 * @param		tableName			The name of the table to insert into
	 * @param		idField				The name of the field containing the surrogate key
	 * @param		constraintFields	Array of fields containing foreign key constraints
	 * @param		constraintValues	Array of input values for foreign key constraint fields
	 */
	public int insertBlank( String tableName, String idField, String[] constraintFields, int[] constraintValues )
	{
		int returnValue = 0;


		return returnValue;
	} // insertBlank( String tableName, String idField, String[] constraintFields, int[] constraintValues )

	/**
	 * Inserts a blank record into the supplied database table and returns the value of the idField.<p/>
	 *
	 * Use this method when the table's ID field is the only constraint<p/>
	 *
	 * Returns 0 if the record insertion failed.
	 *
	 * @since		20111206
	 * @return		int
	 * @param		tableName			The name of the table to insert into
	 * @param		idField				The name of the field containing the surrogate key
	 */
	public int insertBlank( String tableName, String idField )
	{
		int returnValue = 0;

		String sql = "INSERT INTO " + tableName + " ( " + idField + " ) VALUES ( NULL )";
		this.noReturnQuery( sql );

		sql = "SELECT last_insert_rowid() FROM " + tableName;
		String[][] result = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue = Integer.valueOf( result[i][0] );
		}

		return returnValue;
	} // insertBlank( String tableName, String idField )

	/**
	 * Performs a query that does not require a return
	 *
	 * @since		20111206
	 * @param		sql				The query to perform
	 */
	public void noReturnQuery( String sql )
	{
		if( this.mDatabase == null || !this.mDatabase.isOpen() )
		{
			this.openDatabase();
		}

		try
		{
			this.mDatabase.execSQL( sql );
		}
		catch( SQLiteException e )
		{
			Log.d( mTag, "Exception D1: " + e.getMessage() );
		}
	} // public void noReturnQuery( String sql )


	/**
	 * Performs a query and returns an array of results.  All results are cast to String
	 *
	 * @since		20111206
	 * @return		String[][]
	 * @param		sql				The query to perform
	 */
	public String[][] query( String sql )
	{
		Cursor cursor = null;

		if( this.mDatabase == null || !this.mDatabase.isOpen() )
		{
			Log.d( mTag, "Had to open the database" );
			this.openDatabase();
		}

		try
		{
			cursor = this.mDatabase.rawQuery( sql, null );
			this.mQueryCount = cursor.getCount();
			int numRows = cursor.getCount();
			int numCols = cursor.getColumnCount();

			this.mFieldNames = null;
			//this.mFieldNames = new String[numCols];
			this.mFieldNames = cursor.getColumnNames();

			String[][] returnValue = new String[ numRows ][ numCols ];

			int $row = 0;

			while( cursor.moveToNext() )
			{
				for( int i = 0; i < numCols; i++ )
				{
					if( cursor.getType( i ) != Cursor.FIELD_TYPE_INTEGER &&
							cursor.getType( i ) != Cursor.FIELD_TYPE_FLOAT )
					{
						returnValue[ $row ][ i ] = cursor.getString( i );
					}
					else
					{
						returnValue[ $row ][ i ] = String.valueOf( cursor.getInt( i ) );
					}
				}
				$row++;
			} // for( int i = 0; cursor.moveToNext(); i++ )

			return returnValue;
		}
		catch( SQLiteException e )
		{
			Log.d( mTag, "Exception RmDb.query(): " + e.getMessage() );
			return new String[0][0];
		}
		finally
		{
			if( cursor != null )
			{
				cursor.close();
			}
		}
	} // public String[][] query( String sql, String[] fieldNames, String[] returnTypes )

	public void queriesFromAttached( String[] queries, String dbPath )
	{
		if( this.mDatabase == null || !this.mDatabase.isOpen() )
		{
			this.openDatabase();
		}

		try
		{
			String sql = "ATTACH DATABASE '" + dbPath + "' AS attachedDb";
			this.mDatabase.execSQL( sql );
			for( int i = 0; i < queries.length; i++ )
			{
				this.mDatabase.execSQL( queries[i] );
			}
		}
		catch( SQLiteException e )
		{
			Log.d( mTag, "Exception D1: " + e.getMessage() );
		}

		this.mDatabase.close();


	}

	/**
	 * Gets the query count of the latest query
	 *
	 * @since		20111206
	 * @return		int
	 */
	public int getQueryCount()
	{
		return this.mQueryCount;
	} // getQueryCount( String tableName, String whereClause )

	/**
	 * Copies a test database from the local assets-folder to the device/simulator
	 *
	 * This is a means of replacing an on-device database with one that can be manipulated from the desktop
	 *
	 * @since		20111206
	 * @param 		fileName			The name of the file, relative to the assets directory
	 */
	public void copyTestDatabase( String fileName ) throws IOException
	{
		InputStream myInput = null;

		//Open your local db as the input stream
		try
		{
			myInput = mContext.getAssets().open( fileName );
		}
		catch( Exception e )
		{
			Log.d( mTag, "Exception DA1: " + e.toString() );
		}

		//Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream( this.mDeviceDbPath );

		//transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		try
		{
			while( ( length = myInput.read( buffer ) ) > 0 )
			{
				myOutput.write( buffer, 0, length );
			}
		}
		catch( Exception e )
		{
			Log.d( mTag, "Exception DA2: " + e.toString() );
		}

		//Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	} //  private void copyTestDatabase() throws IOException

	public void doUpgrade()
	{
		//Log.d("mTag","Hit doUpgrade");
		if( this.mUpgradeInProgress )
		{
			return;
		}
		this.mUpgradeInProgress = true;
		mApplication.isUpdatingDB = true ;
		SFConfig.databaseFileSync();

		int currentVersion = this.getCurrentVersion();
		int maxVersion = this.getMaxUpdateVersion();

		//Log.d("mTag","current db version: " + this.getCurrentVersion() + ", max db version: " + getMaxUpdateVersion());
		if( maxVersion > currentVersion )
		{
			this.upgradeToMaxVersion( currentVersion, maxVersion );
		}
		this.mUpgradeInProgress = false;
		mApplication.isUpdatingDB = false ;
	}

	public boolean checkForV2update()
	{
		boolean isUpdated = false;
		if(isFieldEmpty( "playlist_section_content_id", "playlist_to_section" ))
		{
			isUpdated = true;
		}
		if(isFieldEmpty( "post_section_content_id", "post_to_section" ))
		{
			isUpdated = true;
		}
		if(isFieldEmpty( "id", "bible_versions" ))
		{
			isUpdated = true;
		}
		return  isUpdated;


	}

	private int getCurrentVersion()
	{
		int returnValue = 0;

		String sql =
				"SELECT     MAX( version_number ) as version " +
				"FROM       schema_versions ";
		String[][] result = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue = AiSTr.denullifyInt( result[ i ][ 0 ] );
		}

		return returnValue;
	}

	private int getMaxUpdateVersion()
	{
		int returnValue = 0;

		SFConfig config = new SFConfig();

		File dir = new File( this.mContext.getFilesDir() +config.mDbUpdatesPath );
		File[] directoryListing = dir.listFiles();
		if( directoryListing != null )
		{
			for( File child : directoryListing )
			{
				String path = child.getPath();
				String fileName = path.substring( path.lastIndexOf( '/' ) + 1 );
				if( fileName.endsWith( ".sql" ) )
				{
					String newChild = fileName.toString().replace( ".sql", "" );
					int versionNumber = Integer.parseInt( newChild );
					if( versionNumber > 0 && versionNumber > returnValue )
					{
						returnValue = versionNumber;
					}
				}
			}
		}

		return returnValue;
	}

	private void upgradeToMaxVersion( int currentVersion, int maxVersion )
	{
		SFConfig config = new SFConfig();

		for( int i = (currentVersion + 1); i <= maxVersion; i++ )
		{
			File file = new File( this.mContext.getFilesDir() + config.mDbUpdatesPath + i + ".sql" );

			// This will reference one line at a time
			String output = "";
			String line = null;

			if(file.exists())
			{

				mShouldUpdate = true;

				if (i == 2 && !shouldUpdateTwo())
				{
					mShouldUpdate = false;
				} else if (i == 3 && !shouldUpdateThree())
				{
					mShouldUpdate = false;
				}
				else if (i == 5 && !shouldUpdateFive())
				{
					mShouldUpdate = false;
				}

				if( mShouldUpdate )
				{
					try
					{
						// FileReader reads text files in the default encoding.
						FileReader fileReader = new FileReader( file );

						// Always wrap FileReader in BufferedReader.
						BufferedReader bufferedReader = new BufferedReader( fileReader );

						while ((line = bufferedReader.readLine()) != null)
						{
							output += line + "\n";
						}

						// Always close files.
						bufferedReader.close();
					} catch (Exception e)
					{
						Log.e( mTag, "Exception reading file: " + e.getLocalizedMessage() );
					}

					String queries[] = output.split( ";\n" );

					for (int j = 0; j < queries.length; j++)
					{
						if (queries[j].length() > 0)
						{
							this.query( queries[j] );
						}
					}
				}
				this.recordSchemaVersion( i );
			}
		}
	}

	private boolean shouldUpdateTwo()
	{
		if( !fieldExistsInTable( "external_id", "ondevice_sections" ) )
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	private  boolean shouldUpdateThree()
	{
		if( ! tableExists( "bible_access_history" ) )
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	private boolean shouldUpdateFive()
	{
		if( !tableExists( "post_to_section" ))
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	private boolean isFieldEmpty( String fieldName, String tableName )
	{
		String sql = "SELECT COUNT(*) " +
				" FROM " + tableName + " " +
				" WHERE " + fieldName + " != '' ";

		String[][] result = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			int count = AiSTr.denullifyInt( result[0][0] );
			if( count == 0 )
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		return false;
	}

	private boolean fieldExistsInTable( String fieldName, String tableName )
	{
		String sql = "PRAGMA table_info( '" + tableName + "' )";

		String[][] result = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			 String[] row = result[i];
			for(int j = 0; j< row.length; j++)
			{
				//Log.d("mTag","row " + j +" = " + row[j]);
				if( row[j] != null)
				{
					if (row[j].equals( fieldName ))
					{
						return true;
					}
				}
			}
		}
		return false;
	}

	private boolean tableExists( String tableName )
	{
		String sql = "SELECT COUNT(*) as count FROM sqlite_master WHERE type='table' AND name='" + tableName + "'";
		String[][] result = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			int count = AiSTr.denullifyInt( result[i][0] );
			if( count > 0 )
			{
				return true;
			}
		}
		return false;
	}

	private void recordSchemaVersion( int schemaVersion )
	{

		int versionCount = 0;

		String sql = "SELECT COUNT(*) AS count FROM schema_versions WHERE version_number = " + schemaVersion;
		String[][] result = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			versionCount = AiSTr.denullifyInt( result[ i ][ 0 ] );
		}

		// If this version number has already been recorded, update the time
		if( versionCount > 0 )
		{
			sql = "UPDATE schema_versions SET update_time = " + SFUtil.currentTimestamp() + " WHERE version_number = " + schemaVersion;
		}
		else // or else insert a new record
		{
			sql = "INSERT INTO schema_versions ( version_number, update_time ) values( " + schemaVersion + ", " + SFUtil.currentTimestamp() + " )";
		}
		this.noReturnQuery( sql );
	} // [recordSchemaVersion]

	/**
	 * Convert a database boolean flag to native java
	 * @param input
	 * @return
	 */
	public static boolean fixBoolean( int input )
	{
		return (input == 0) ? false : true;
	}


	/**
	 * Convert a native java boolean to a database flag
	 * @param input
	 * @return
	 */
	public static int fixDbBoolean( boolean input )
	{
		return (input == false ) ? 0 : 1;
	}

	public void CheckDatabaseForV2()
	{

		boolean updated = this.checkForV2update();
		if(!updated)
		{
			//Log.d("mTag","setup Database for v2 ");
			SFAppData data = new SFAppData();
			SFConfig config = new SFConfig();
			data.deleteAllDatabaseData();
			config.mLastSync = 0;
		}
		else
		{
			return;
		}
	}

	public Integer getLastIdFromTable( String tableName )
	{
		Integer returnValue = 0;
		String sql = "SELECT ROWID FROM " + tableName + " ORDER BY ROWID DESC LIMIT 1";
		String result[][] = this.query( sql );
		for( int i = 0; i < result.length; i++ )
		{
			returnValue =  AiSTr.denullifyInt( result[ i ][ 0 ] );
		}

		return returnValue;
	}

} // End class