<?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";
		$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";
			$condition="";
		}

		echo "select from ".$table."\n";


        $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 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){
	$query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')";	
	$result=mysql_query($query);
	if(!$result){
		echo json_encode("Failed during inserting version records.");
		return;
	}
	$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){
		$str="(";
		$str.="'".$row['name']."',";
		$str.="'".sprintf("%05d",$row['booksId'])."',";
		$str.="'".$row['sectionAfter']."',";
		$str.=$row['startPage'].",";
		$str.=$row['endPage'].",";
		$str.=$row['level'].",";
		$str.=$row['splitFrom'].",";
		$str.=$row['id'].",";
		$str.=$versionId.",";
		$str.=$row['deleted'];
		$str.=")";
		if($idx!=sizeof($sectionArray)-1){
			$str.=", ";
		}
		$query.=$str;	
	}
	$result=mysql_query($query);
	if(!$result){
		echo json_encode("Failed during inserting section records.");
		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 ON books.id=sections.books_id WHERE sections.books_id IS NULL";
	
	$result=mysql_query($query);
	if(!$result){
		echo json_encode("Failed during inserting section records.");
		return;
	}

	while($row=mysql_fetch_assoc($result)){
		//echo $row['books_id'], ", ";
		//echo $row.", ";		
	}
	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){

	/* 
	// get Auto_increment 
	$query = mysql_query("SHOW TABLE STATUS WHERE name='sections'");
	if (mysql_num_rows($query)) {
		$result = mysql_fetch_assoc($query);
		echo $result['Auto_increment'];
	} else {//error
		//error control here
		echo "error in getting Auto_increment";
	}
	*/

	// id should be autoincremated 
	$query="INSERT INTO sections (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"){		
		$bookId=$_POST['bookId'];
		$sectionArray=$_POST['sectionArray'];
		$version=$_POST['version'];
		$editor=$_POST['editor'];

		foreach ($sectionArray as $key => $value) {
			//echo $key.',';
			foreach ($value as $key => $inner_val) {
				//echo $key.",".$inner_val."\n";
			}
		}
		
		//echo "bookId:".$bookId.", "."editor:".$editor;
		if(isset($bookId)&&isset($sectionArray)&&isset($version)&&isset($editor)&&\
			is_numeric($bookId) && sizeof($sectionArray)!=0 && is_numeric($version) && $editor!=""){
			
			updateSectionArray($bookId,$sectionArray,$version,$editor);
		}
		/*
		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'];
			
			echo "updating db...";
			// updateSectionArray($bookId,$sectionArray,$version,$editor);
		}
		*/
	}
}

?>