<?php

/**
 * SQL files must have a semi-colon at the end of each SQL statement
 * 
 * PHP files can also be used as a patch but are optional
 * 
 * The SQL file for a given patch number is applied before the PHP file
 * 
 * If the PHP file bombs out, then there will be no way to recover. This can be seen in the PARAMS table where
 * db_apply_patch_php is the number of the patch which failed.
 * 
*/
	
$g_database_path = '../html/data/data.db';
$g_patch_dir = 'db';

apply_patches();
	
function apply_patches(){

	global $g_database_path;
	
	//clear any previous php errors
	$db = new SQLite3( $g_database_path );
	$db->exec("delete from PARAMS where P_Name='db_apply_patch_php'");

	//which patch to start with
	$curr_version = get_curr_patch_ver( );
	$st_patch_val = $curr_version;
	
	print( "Current version is $curr_version\n" );
	
	do{
		$st_patch_val++;
		
		//process the SQL patch first
		$patch_name = get_patch_name( $st_patch_val );	
		$patch_data = read_patch( $patch_name );
		if($patch_data!=null){
			apply_patch_sql( $st_patch_val, $patch_name, $patch_data );
		}
		
		//process the PHP patch
		$patch_name = get_patch_name( $st_patch_val, "php");
		$patch_data = read_patch( $patch_name );
		if($patch_data!=null){
			apply_patch_php( $st_patch_val, $patch_name, $patch_data );
		}
					
	}while($patch_data!=null);
	
	$applied = $st_patch_val - $curr_version -1;
	print( "Applied $applied patches\n" );


}

function apply_patch_php( $st_patch_val, $patch_name, $patch_data ){
	
	print("Applying patch: $patch_name \n");
	
	global $g_database_path;
		
	//first mark the database so that we know a patch has started	
	$db = new SQLite3( $g_database_path );
	$db->exec("insert into PARAMS(P_Name, P_Value) values('db_apply_patch_php', $st_patch_val )");
	try{
		
		eval($patch_data);
		$db->exec("delete from PARAMS where P_Name='db_apply_patch_php'");
		
	}catch( Exception $e ){
		print( "Exception: " . $e->getMessage() );
		throw $e;
	}
}


function apply_patch_sql( $st_patch_val, $patch_name, $patch_data ){

	print("Applying patch: $patch_name \n");

	$arr_sql_stat = split_sql_data( $patch_data );
	if( count($arr_sql_stat) == 0){
		print("Patch empty\n");
		return;
	}
	
	global $g_database_path;
	
	//first mark the database so that we know a patch has started	
	$db = new SQLite3( $g_database_path );
	$db->exec("insert into PARAMS(P_Name, P_Value) values('db_apply_patch', $st_patch_val )");		
	
	try{
		for( $i=0; $i<count($arr_sql_stat); $i++ ){
			$db->exec( $arr_sql_stat[$i] );
		}

		// update the version
		$db->exec("delete from PARAMS where P_Name='db_apply_patch'");
		$db->exec("update PARAMS set P_Value=$st_patch_val where P_Name='db_curr_patch'");		
		
	}catch( Exception $e ){
		print( "Exception: " . $e->getMessage() );
		throw $e;
	}
	

}

/**
 * Simple return the patch contents as a string.
 * 
 * Returns null if no such patch exists.
 * 
 */
function read_patch( $patch_name ){
	global $g_patch_dir;
	$fn = $g_patch_dir."/$patch_name";	
	if(!file_exists($fn)) return null;
	return file_get_contents($fn);
}

/**
 * Get the file name of the patch given it's integer number.
 * 
 */
function get_patch_name( $patch_int, $patch_type="sql"){
	return "p-$patch_int.$patch_type";
}

/**
 * Read the database patch version from the params table.
 * Will create the table if it does not exists thus assuming that this the first run, setting db_curr_patch=0
 */
function get_curr_patch_ver( ){

	global $g_database_path;
	
	$db = new SQLite3( $g_database_path );
	$results = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='PARAMS'");
	$row = $results->fetchArray();
	if( !$row ) {
		//create the table and the the version number
		print "Creating table PARAMS\n";
		$db->exec("CREATE TABLE \"PARAMS\" ( \"P_Name\"	TEXT NOT NULL UNIQUE, \"P_Value\"	TEXT, PRIMARY KEY(\"P_Name\"))");
		$db->exec("insert into PARAMS(P_Name, P_Value) values('db_curr_patch', 0 )");		
		return 0;
	}

	//read the version number
	$results = $db->query("SELECT P_Value FROM PARAMS WHERE P_Name='db_curr_patch'");
	$row = $results->fetchArray();
	if( !$row ) {
		print "Param db_curr_patch not found\n";
		exit(1);
	}
	
	return $row['P_Value'];
}

/**
 * Return an array of strings, one for each SQL statement. Comments are ignored.
 */
function split_sql_data( $patch_data ){
	
	$arr_sql_stat = array();
	
	$lines = preg_split("/\r\n|\n|\r/", $patch_data);
	$stat_nxt = '';
	$i=0;
	while( $i<count($lines) ){
		if( str_starts_with( trim($lines[$i]), '--') ) { $i++; continue; }
		$stat_nxt .= trim($lines[$i]);
		if( str_ends_with( trim($lines[$i]), ';') ) {
			array_push( $arr_sql_stat, $stat_nxt );
			$stat_nxt = '';
		}
		$i++;
	}
	
	return $arr_sql_stat;
}


?>