<?php
include_once('Lib_mb_utf8.php');
include_once('config.php');
set_time_limit(0);
ini_set('memory_limit', '-1');

$link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password);
mysql_query("SET NAMES utf8");

if (!$link_mysql) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($mysql_database, $link_mysql);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}
function checkSectionInfo($bookId){
	$query="SELECT * FROM sections_versions WHERE books_id=".$bookId." ORDER BY version DESC";
	$result=mysql_query($query);
	if(mysql_num_rows($result)==0){
		$row['version']=0;
		$row['editor']="";
		$row['date']="";
		return $row;
	}else{
		$row=mysql_fetch_assoc($result);
		return $row;
	}
}
function checkMissingPage($bookInfo,$versionInfo){
	$bookId=$bookInfo['id'];
	$bookPage=$bookInfo['line'];
        if($versionInfo['version']!=0){
		$table="sections_revisions";
		$condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
		$versionId=$versionInfo['id'];
	}else{
		// $table="sections";
		$table="sections_index";
		$condition="";
	}
	$query="SELECT *
		FROM ".$table." 
		WHERE books_id=".$bookId." ".$condition." 
		GROUP BY books_id,start_page,end_page 
		ORDER BY books_id,start_page,level,end_page";
	$result=mysql_query($query);
	$lastPage=1;
	$i=0;
	$pageArray=array();
	while($row=mysql_fetch_assoc($result)){
		if($row['start_page']-1>$lastPage){
			$pageArray[$i]['start_page']=$lastPage;
			$pageArray[$i]['end_page']=$row['start_page'];
			//echo $pageArray[$i]['start_page']." ".$pageArray[$i]['end_page']."<br>";
			$i++;
		}
		$lastPage=$row['end_page'];
	}
	if($bookPage>$lastPage){
		$pageArray[$i]['start_page']=$lastPage;
                $pageArray[$i]['end_page']=$bookPage;
	}
	return $pageArray;
}
function getSectionArray($bookId,$count,$versionInfo){
		
        $str="";
       	if($versionInfo['version']!=0){
			$table="sections_revisions";
			$condition="AND deleted=0 ";
			$condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
		}else{
			// $table="sections";
			$table="sections_index";
			$condition="";
		}

        $query="SELECT * 
                FROM ".$table."
                WHERE books_id=".$bookId." ".$condition."
                GROUP BY books_id, start_page, end_page
                HAVING COUNT( * ) >=".$count."
                ORDER BY id ASC";
                /*ORDER BY start_page ASC, level ASC, id ASC,end_page DESC";*/
        $result=mysql_query($query);

        $i=0;
	$pageArray=array();
        while($row=mysql_fetch_assoc($result)){
                $pageArray[$i]['start_page']=$row['start_page'];
                $pageArray[$i]['end_page']=$row['end_page'];
                $i++;
        }
        if($versionInfo['version']!=0){
                $query="SELECT id AS revisions_id, name,books_id,section_after,start_page,end_page,level,split_from,sections_id AS id, deleted FROM sections_revisions WHERE versions_id=".$versionInfo['id']." ORDER BY revisions_id ASC";
        }else{
                $query="SELECT * FROM sections_index WHERE books_id=".$bookId." ORDER BY id ASC";
        }
        $result=mysql_query($query);

        $i=0;
	$sectionArray=array();
        while($row=mysql_fetch_assoc($result)){
                $flag="";
                if($i!=sizeof($pageArray) && $pageArray[$i]['start_page']==$row['start_page'] && $pageArray[$i]['end_page']==$row['end_page']){
                        $flag="overlapper";
                        $i++;
                }
                if($i!=0 && $pageArray[$i-1]['start_page']==$row['start_page'] && $pageArray[$i-1]['end_page']==$row['end_page']){
                        $flag="overlapper";
                }
                $row['flag']=$flag;
		if(!isset($row['deleted'])){
			$row['deleted']="";
		}else{
			if($row['deleted']==0){
				$row['deleted']="";
			}else{
				$row['deleted']="deleted";
			}
		}
                $sectionArray[]=$row;
        }
        return $sectionArray;
}
function updateSectionArray($bookId,$sectionArray,$version,$editor){
	
	// -- Start Transaction -- 
	mysql_query("BEGIN");

	$query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')";	
	$result_versions=mysql_query($query);
	if(!$result_versions){
		echo json_encode("Failed during inserting sections_version records."); // .mysql_error();
	}
	$versionId=mysql_insert_id();



	$query="INSERT INTO sections_revisions (name,books_id,section_after,start_page,end_page,level,split_from,sections_id,versions_id,deleted) VALUES ";
	foreach($sectionArray as $idx=>$row){
		// get the section_id
		$section_id = $row['id'];
		$name = $row['name'];
		$books_id = sprintf("%05d",$row['booksId']);
		$section_after = $row['sectionAfter'];
		$start_page = $row['startPage'];
		$end_page = $row['endPage'];
		$level = $row['level'];
		$split_from = $row['splitFrom'];
		$deleted = $row['deleted'];

		// --- update to section_index table, which is the up-to-date section table ---
		$qry = "";
		if ($section_id == 0) { // section_id = 0 means it is a new record created by user
			// insert new row to section_index table
			$qry = "INSERT INTO sections_index (name,books_id,section_after,start_page,end_page,level,split_from) VALUES 
				('".$name."','".$books_id."','".$section_after."',".$start_page.
				",".$end_page.",".$level.",".$split_from.")";

		} else {
			// update
			$qry = "UPDATE sections_index SET name='".$name."',books_id='".$books_id."',section_after='".$section_after.
				"',start_page=".$start_page.",end_page=".$end_page.",level=".$level.",split_from=".$split_from." WHERE id=".$section_id;
		}

		$rst = mysql_query($qry);
		if (!$rst) {
			break;	// handle to ROLLBACK
		}

		if ($section_id == 0) {
			// set section_id from section_index if this is a new section
			$section_id = mysql_insert_id();	
		}

		
		if ($deleted == 1) {
			// deleted section from sections_index where id = $section_id
			$qry = "DELETE FROM sections_index WHERE id=".$section_id;
			$rst = mysql_query($qry);
			if (!$rst) {
				break;	// handle to ROLLBACK
			}

		} else {
			$qry = "SELECT * FROM sections_index WHERE id=".$section_id;
			$rst = mysql_query($qry);
			if (!$rst) {
				break;	// handle to ROLLBACK
			}
			if (mysql_num_rows($rst) == 0) {
				$qry = "INSERT INTO sections_index (id,name,books_id,section_after,start_page,end_page,level,split_from) VALUES 
				(".$section_id.",'".$name."','".$books_id."','".$section_after."',".$start_page.
				",".$end_page.",".$level.",".$split_from.")";
				// recover section in sections_index table where id = $section_id
				$rst = mysql_query($qry);
				if (!$rst) {
					break;	// handle to ROLLBACK
				}	
			}

		}
		
		// query for sections_revisions table

		$str="(";
		$str.="'".$name."',";
		$str.="'".$books_id."',";
		$str.="'".$section_after."',";
		$str.=$start_page.",";
		$str.=$end_page.",";
		$str.=$level.",";
		$str.=$split_from.",";
		$str.=$section_id.",";	// sections_id should get from section_index table
		$str.=$versionId.",";
		$str.=$deleted;
		$str.=")";
		if($idx!=sizeof($sectionArray)-1){
			$str.=", ";
		}

		$query.=$str;	

	}
	$result=mysql_query($query);

	// --- End Transaction ---
	if ($result and $rst) {
		mysql_query("COMMIT");  
		echo json_encode("Succeeded."); // important returning info to js

	} else {
		mysql_query("ROLLBACK");  
		echo json_encode("Failed during updating sections.");
		return;
	}

	/*
	if(!$result){
		echo json_encode("Failed during inserting sections_revisions records."); // .mysql_error();
		
		// Delete the sections_versions for this user
		// delete $versionId in table sections_versions
		$query="DELETE FROM sections_versions WHERE id=".$versionId;
		$result_deleting=mysql_query($query);
		if(!$result_deleting){
			echo json_encode("Failed during deleting wrongly inserted sections_version record.");
		}
		return;
	}else{
		echo json_encode("Succeeded.");
	}
	*/
	


}


function _select_distinct(){
	/*
	$query="SELECT DISTINCT (books_id) FROM sections";
	
	$result=mysql_query($query);
	if(!$result){
		echo json_encode("Failed during inserting section records.");
		return;
	}else{
		echo json_encode("Succeeded.");
	}

	while($row=mysql_fetch_assoc($result)){
		// echo $row['books_id'], ", ";		
	}

	echo "section num: ".mysql_num_rows($result);
	echo '\n';
	*/


	//$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)"; 
	$query="SELECT books.id, books.name FROM books LEFT JOIN sections_index ON books.id=sections_index.books_id WHERE sections_index.books_id IS NULL";
	
	$result=mysql_query($query);
	if(!$result){
		echo json_encode("Failed during querying records.");
		return;
	}

	echo "missing books num: ".mysql_num_rows($result);
	echo '\n';

	
	
	
}



function add_missing_books_to_sections(){
	// find missing books
	// Run the following line/mysql to find out what books are missing in sections table
	// _select_distinct();
	// Or load missing_books_id.csv
	if (($missing_books = fopen("intermediate_results/missing_books.csv", "r")) !== FALSE) {
	    while (($data = fgetcsv($missing_books, ",")) !== FALSE) {

	    	$bookId = $data[0];
	    	$name = $data[1];

			_add_book_to_sections($name, $bookId);
	    	
	    }
	    fclose($missing_books);	
	}

}


function _add_book_to_sections($name, $bookId){

	$query="INSERT INTO sections_index (name,books_id,section_after) VALUES ";

	$str="(";
	$str.="'".$name."','";
	$str.=$bookId."',";
	$str.="''";
	$str.=")";
	$query.=$str;	
	
	// echo $name.", ".$bookId."\n";

	$result=mysql_query($query);
	if(!$result){
		echo json_encode("Failed during inserting section records.");
		return;
	}
	// echo mysql_insert_id();
	
}


if(isset($_POST['command']) && $_POST['command']!=''){
	$command=$_POST['command'];
	if($command=="write"){		
	
		if(isset($_POST['bookId'])&&isset($_POST['sectionArray'])&&isset($_POST['version'])&&isset($_POST['editor'])&&\
			is_numeric($_POST['bookId']) && sizeof($_POST['sectionArray'])!=0 && is_numeric($_POST['version']) && $_POST['editor']!=""){
			$bookId=$_POST['bookId'];
			$sectionArray=$_POST['sectionArray'];
			$version=$_POST['version'];
			$editor=$_POST['editor'];
			
			updateSectionArray($bookId,$sectionArray,$version,$editor);
		}
		else{
			echo json_encode("Editing info not correct.");

		}
	}
	else{
		echo json_encode("Wrong command.");
		return;
	}
}

?>