<?php
/**
 * BlogDataModule class file
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.pradosoft.com/
 * @copyright Copyright &copy; 2006 PradoSoft
 * @license http://www.pradosoft.com/license/
 */

/**
 * BlogDataModule class
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.pradosoft.com/
 * @copyright Copyright &copy; 2006 PradoSoft
 * @license http://www.pradosoft.com/license/
 */
class BlogDataModule extends TModule
{
	const DB_FILE_EXT='.db';
	const DEFAULT_DB_FILE='Application.Data.Blog';
	private $_db=null;
	private $_dbFile=null;

	public function init($config)
	{
		$this->connectDatabase();
	}

	public function getDbFile()
	{
		if($this->_dbFile===null)
			$this->_dbFile=Prado::getPathOfNamespace(self::DEFAULT_DB_FILE,self::DB_FILE_EXT);
		return $this->_dbFile;
	}

	public function setDbFile($value)
	{
		if(($this->_dbFile=Prado::getPathOfNamespace($value,self::DB_FILE_EXT))===null)
			throw new BlogException(500,'blogdatamodule_dbfile_invalid',$value);
	}

	protected function createDatabase()
	{
		$schemaFile=dirname(__FILE__).'/schema.sql';
		$statements=explode(';',file_get_contents($schemaFile));
		foreach($statements as $statement)
		{
			if(trim($statement)!=='')
			{
				try {
					$command=$this->_db->createCommand($statement);
					$command->execute();
				}
				catch(TDbException $e)
				{
					throw new BlogException(500,'blogdatamodule_createdatabase_failed',$e->getErrorMessage(),$statement);
				}
			}
		}
	}

	protected function connectDatabase()
	{
		$dbFile=$this->getDbFile();
		$newDb=!is_file($dbFile);

		try {
			$this->_db=new TDbConnection("sqlite:".$dbFile);
			$this->_db->Active=true;
		}
		catch(TDbException $e)
		{
			throw new BlogException(500,'blogdatamodule_dbconnect_failed',$e->getErrorMessage());
		}

		if($newDb)
			$this->createDatabase();
	}

	protected function generateModifier($filter,$orderBy,$limit)
	{
		$modifier='';
		if($filter!=='')
			$modifier=' WHERE '.$filter;
		if($orderBy!=='')
			$modifier.=' ORDER BY '.$orderBy;
		if($limit!=='')
			$modifier.=' LIMIT '.$limit;
		return $modifier;
	}

	public function query($sql)
	{
		try {
			$command=$this->_db->createCommand($sql);
			return $command->query();
		}
		catch(TDbException $e)
		{
			throw new BlogException(500,'blogdatamodule_query_failed',$e->getErrorMessage(),$sql);
		}
	}

	protected function populateUserRecord($row)
	{
		$userRecord=new UserRecord;
		$userRecord->ID=(integer)$row['id'];
		$userRecord->Name=$row['name'];
		$userRecord->FullName=$row['full_name'];
		$userRecord->Role=(integer)$row['role'];
		$userRecord->Password=$row['passwd'];
		$userRecord->VerifyCode=$row['vcode'];
		$userRecord->Email=$row['email'];
		$userRecord->CreateTime=(integer)$row['reg_time'];
		$userRecord->Status=(integer)$row['status'];
		$userRecord->Website=$row['website'];
		return $userRecord;
	}

	public function queryUsers($filter='',$orderBy='',$limit='')
	{
		if($filter!=='')
			$filter='WHERE '.$filter;
		$sql="SELECT * FROM tblUsers $filter $orderBy $limit";
		$rows=$this->query($sql);
		$users=array();
		foreach($rows as $row)
			$users[]=$this->populateUserRecord($row);
		return $users;
	}

	public function queryUserCount($filter)
	{
		if($filter!=='')
			$filter='WHERE '.$filter;
		$sql="SELECT COUNT(id) AS user_count FROM tblUsers $filter";
		$result=$this->query($sql);
		if(($row=$result->read())!==false)
			return $row['user_count'];
		else
			return 0;
	}

	public function queryUserByID($id)
	{
		$sql="SELECT * FROM tblUsers WHERE id=$id";
		$result=$this->query($sql);
		if(($row=$result->read())!==false)
			return $this->populateUserRecord($row);
		else
			return null;
	}

	public function queryUserByName($name)
	{
		$command=$this->_db->createCommand("SELECT * FROM tblUsers WHERE name=?");
		$command->bindValue(1, $name);

		$result=$command->query();

		if(($row=$result->read())!==false)
			return $this->populateUserRecord($row);
		else
			return null;
	}

	public function insertUser($user)
	{
		$command=$this->_db->createCommand("INSERT INTO tblUsers ".
				"(name,full_name,role,passwd,email,reg_time,status,website) ".
				"VALUES (?,?,?,?,?,?,?,?)");
		$command->bindValue(1, $user->Name);
		$command->bindValue(2, $user->FullName);
		$command->bindValue(3, $user->Role);
		$command->bindValue(4, $user->Password);
		$command->bindValue(5, $user->Email);
		$command->bindValue(6, time());
		$command->bindValue(7, $user->Status);
		$command->bindValue(8, $user->Website);
		$command->execute();

		$user->ID=$this->_db->getLastInsertID();
	}

	public function updateUser($user)
	{
		$command=$this->_db->createCommand("UPDATE tblUsers SET
				name=?,
				full_name=?,
				role=?,
				passwd=?,
				vcode=?,
				email=?,
				status=?,
				website=?
				WHERE id=?");
		$command->bindValue(1, $user->Name);
		$command->bindValue(2, $user->FullName);
		$command->bindValue(3, $user->Role);
		$command->bindValue(4, $user->Password);
		$command->bindValue(5, $user->VerifyCode);
		$command->bindValue(6, $user->Email);
		$command->bindValue(7, $user->Status);
		$command->bindValue(8, $user->Website);
		$command->bindValue(9, $user->ID);
		$command->execute();
	}

	public function deleteUser($id)
	{
		$command=$this->_db->createCommand("DELETE FROM tblUsers WHERE id=?");
		$command->bindValue(1, $id);
		$command->execute();
	}

	protected function populatePostRecord($row)
	{
		$postRecord=new PostRecord;
		$postRecord->ID=(integer)$row['id'];
		$postRecord->AuthorID=(integer)$row['author_id'];
		if($row['author_full_name']!=='')
			$postRecord->AuthorName=$row['author_full_name'];
		else
			$postRecord->AuthorName=$row['author_name'];
		$postRecord->CreateTime=(integer)$row['create_time'];
		$postRecord->ModifyTime=(integer)$row['modify_time'];
		$postRecord->Title=$row['title'];
		$postRecord->Content=$row['content'];
		$postRecord->Status=(integer)$row['status'];
		$postRecord->CommentCount=(integer)$row['comment_count'];
		return $postRecord;
	}

	public function queryPosts($postFilter,$categoryFilter,$orderBy,$limit)
	{
		//FIXME this is insecure by design since it misses proper escaping
		$filter='';
		if($postFilter!=='')
			$filter.=" AND $postFilter";
		if($categoryFilter!=='')
			$filter.=" AND a.id IN (SELECT post_id AS id FROM tblPost2Category WHERE $categoryFilter)";
		$sql="SELECT a.id AS id,
					a.author_id AS author_id,
					b.name AS author_name,
					b.full_name AS author_full_name,
					a.create_time AS create_time,
					a.modify_time AS modify_time,
					a.title AS title,
					a.content AS content,
					a.status AS status,
					a.comment_count AS comment_count
				FROM tblPosts a, tblUsers b
				WHERE a.author_id=b.id $filter $orderBy $limit";
		$rows=$this->query($sql);
		$posts=array();
		foreach($rows as $row)
			$posts[]=$this->populatePostRecord($row);
		return $posts;
	}

	public function queryPostsSearch($keywords,$orderBy,$limit)
	{
		$sql="SELECT a.id AS id,
					a.author_id AS author_id,
					b.name AS author_name,
					b.full_name AS author_full_name,
					a.create_time AS create_time,
					a.modify_time AS modify_time,
					a.title AS title,
					a.content AS content,
					a.status AS status,
					a.comment_count AS comment_count
				FROM tblPosts a, tblUsers b
				WHERE a.author_id=b.id AND a.status=0";

		foreach($keywords as $keyword)
			$sql.=" AND (content LIKE ? OR title LIKE ?)";

		$sql.=" $orderBy $limit";

		$command=$this->_db->createCommand($sql);

		$i=1;
		foreach($keywords as $keyword)
		{
			$command->bindValue($i, "%".$keyword."%");
			$i++;
		}

		$rows=$command->query();

		$posts=array();
		foreach($rows as $row)
			$posts[]=$this->populatePostRecord($row);
		return $posts;

	}

	public function queryPostCount($postFilter,$categoryFilter)
	{
		//FIXME this is insecure by design since it misses proper escaping
		$filter='';
		if($postFilter!=='')
			$filter.=" AND $postFilter";
		if($categoryFilter!=='')
			$filter.=" AND a.id IN (SELECT post_id AS id FROM tblPost2Category WHERE $categoryFilter)";
		$sql="SELECT COUNT(a.id) AS post_count
				FROM tblPosts a, tblUsers b
				WHERE a.author_id=b.id $filter";
		$result=$this->query($sql);
		if(($row=$result->read())!==false)
			return $row['post_count'];
		else
			return 0;
	}

	public function queryPostByID($id)
	{
		$sql="SELECT a.id AS id,
		             a.author_id AS author_id,
		             b.name AS author_name,
		             b.full_name AS author_full_name,
		             a.create_time AS create_time,
		             a.modify_time AS modify_time,
		             a.title AS title,
		             a.content AS content,
		             a.status AS status,
		             a.comment_count AS comment_count
		      FROM tblPosts a, tblUsers b
		      WHERE a.id=? AND a.author_id=b.id";

		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $id);

		$result=$command->query();

		if(($row=$result->read())!==false)
			return $this->populatePostRecord($row);
		else
			return null;
	}

	public function insertPost($post,$catIDs)
	{
		$command=$this->_db->createCommand("INSERT INTO tblPosts
				(author_id,create_time,modify_time,title,content,status)
				VALUES (?,?,?,?,?,?)");
		$command->bindValue(1, $post->AuthorID);
		$command->bindValue(2, $post->CreateTime);
		$command->bindValue(3, $post->ModifyTime);
		$command->bindValue(4, $post->Title);
		$command->bindValue(5, $post->Content);
		$command->bindValue(6, $post->Status);

		$command->execute();
		$post->ID=$this->_db->getLastInsertID();
		foreach($catIDs as $catID)
			$this->insertPostCategory($post->ID,$catID);
	}

	public function updatePost($post,$newCatIDs=null)
	{
		if($newCatIDs!==null)
		{
			$cats=$this->queryCategoriesByPostID($post->ID);
			$catIDs=array();
			foreach($cats as $cat)
				$catIDs[]=$cat->ID;
			$deleteIDs=array_diff($catIDs,$newCatIDs);
			foreach($deleteIDs as $id)
				$this->deletePostCategory($post->ID,$id);
			$insertIDs=array_diff($newCatIDs,$catIDs);
			foreach($insertIDs as $id)
				$this->insertPostCategory($post->ID,$id);
		}

		$command=$this->_db->createCommand("UPDATE tblPosts SET
				modify_time=?,
				title=?,
				content=?,
				status=?
				WHERE id=?");
		$command->bindValue(1, $post->ModifyTime);
		$command->bindValue(2, $post->Title);
		$command->bindValue(3, $post->Content);
		$command->bindValue(4, $post->Status);
		$command->bindValue(5, $post->ID);

		$command->execute();
	}

	public function deletePost($id)
	{
		$cats=$this->queryCategoriesByPostID($id);
		foreach($cats as $cat)
			$this->deletePostCategory($id,$cat->ID);

		$command=$this->_db->createCommand("DELETE FROM tblComments WHERE post_id=?");
		$command->bindValue(1, $id);
		$command->execute();

		$command=$this->_db->createCommand("DELETE FROM tblPosts WHERE id=?");
		$command->bindValue(1, $id);
		$command->execute();
	}

	protected function populateCommentRecord($row)
	{
		$commentRecord=new CommentRecord;
		$commentRecord->ID=(integer)$row['id'];
		$commentRecord->PostID=(integer)$row['post_id'];
		$commentRecord->AuthorName=$row['author_name'];
		$commentRecord->AuthorEmail=$row['author_email'];
		$commentRecord->AuthorWebsite=$row['author_website'];
		$commentRecord->AuthorIP=$row['author_ip'];
		$commentRecord->CreateTime=(integer)$row['create_time'];
		$commentRecord->Content=$row['content'];
		$commentRecord->Status=(integer)$row['status'];
		return $commentRecord;
	}

	public function queryComments($filter,$orderBy,$limit)
	{
		//FIXME this is insecure by design since it misses proper escaping
		if($filter!=='')
			$filter='WHERE '.$filter;
		$sql="SELECT * FROM tblComments $filter $orderBy $limit";
		$rows=$this->query($sql);
		$comments=array();
		foreach($rows as $row)
			$comments[]=$this->populateCommentRecord($row);
		return $comments;
	}

	public function queryCommentsByPostID($id)
	{
		$sql="SELECT * FROM tblComments WHERE post_id=? ORDER BY create_time DESC";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $id);

		$rows=$command->query();

		$comments=array();
		foreach($rows as $row)
			$comments[]=$this->populateCommentRecord($row);
		return $comments;
	}

	public function insertComment($comment)
	{
		$sql="INSERT INTO tblComments
				(post_id,author_name,author_email,author_website,author_ip,create_time,status,content)
				VALUES (?,?,?,?,?,?,?,?)";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $comment->PostID);
		$command->bindValue(2, $comment->AuthorName);
		$command->bindValue(3, $comment->AuthorEmail);
		$command->bindValue(4, $comment->AuthorWebsite);
		$command->bindValue(5, $comment->AuthorIP);
		$command->bindValue(6, $comment->CreateTime);
		$command->bindValue(7, $comment->Status);
		$command->bindValue(8, $comment->Content);

		$command->execute();
		$comment->ID=$this->_db->getLastInsertID();
		$this->query("UPDATE tblPosts SET comment_count=comment_count+1 WHERE id={$comment->PostID}");
	}

	public function updateComment($comment)
	{
		$sql="UPDATE tblComments SET status=? WHERE id=?";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $comment->Status);
		$command->bindValue(2, $comment->ID);

		$command->execute();
	}

	public function deleteComment($id)
	{
		$sql="SELECT post_id FROM tblComments WHERE id=?";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $id);
		$result=$command->query();

		if(($row=$result->read())!==false)
		{
			$command=$this->_db->createCommand("DELETE FROM tblComments WHERE id=?");
			$command->bindValue(1, $id);
			$command->execute();

			$command=$this->_db->createCommand("UPDATE tblPosts SET comment_count=comment_count-1 WHERE id=?");
			$command->bindValue(1, $row['post_id']);
			$command->execute();
		}
	}

	protected function populateCategoryRecord($row)
	{
		$catRecord=new CategoryRecord;
		$catRecord->ID=(integer)$row['id'];
		$catRecord->Name=$row['name'];
		$catRecord->Description=$row['description'];
		$catRecord->PostCount=$row['post_count'];
		return $catRecord;
	}

	public function queryCategories()
	{
		$sql="SELECT * FROM tblCategories ORDER BY name ASC";
		$rows=$this->query($sql);
		$cats=array();
		foreach($rows as $row)
			$cats[]=$this->populateCategoryRecord($row);
		return $cats;
	}

	public function queryCategoriesByPostID($postID)
	{
		$sql="SELECT a.id AS id,
				a.name AS name,
				a.description AS description,
				a.post_count AS post_count
				FROM tblCategories a, tblPost2Category b
				WHERE a.id=b.category_id AND b.post_id=? ORDER BY a.name";

		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $postID);
		$rows=$command->query();

		$cats=array();
		foreach($rows as $row)
			$cats[]=$this->populateCategoryRecord($row);
		return $cats;
	}

	public function queryCategoryByID($id)
	{
		$sql="SELECT * FROM tblCategories WHERE id=?";

		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $id);
		$result=$command->query();

		if(($row=$result->read())!==false)
			return $this->populateCategoryRecord($row);
		else
			return null;
	}

	public function queryCategoryByName($name)
	{
		$sql="SELECT * FROM tblCategories WHERE name=?";

		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $name);
		$result=$command->query();

		if(($row=$result->read())!==false)
			return $this->populateCategoryRecord($row);
		else
			return null;
	}

	public function insertCategory($category)
	{
		$sql="INSERT INTO tblCategories
				(name,description)
				VALUES (?,?)";

		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $category->Name);
		$command->bindValue(2, $category->Description);
		$command->execute();

		$category->ID=$this->_db->getLastInsertID();
	}

	public function updateCategory($category)
	{
		$sql="UPDATE tblCategories SET name=?, description=?, post_count=? WHERE id=?";

		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $category->Name);
		$command->bindValue(2, $category->Description);
		$command->bindValue(3, $category->PostCount);
		$command->bindValue(4, $category->ID);

		$command->execute();
	}

	public function deleteCategory($id)
	{
		$sql="DELETE FROM tblPost2Category WHERE category_id=?";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $id);
		$command->execute();

		$sql="DELETE FROM tblCategories WHERE id=?";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $id);
		$command->execute();
	}

	public function insertPostCategory($postID,$categoryID)
	{
		$sql="INSERT INTO tblPost2Category (post_id, category_id) VALUES (?, ?)";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $postID);
		$command->bindValue(2, $categoryID);
		$command->execute();

		$sql="UPDATE tblCategories SET post_count=post_count+1 WHERE id=?";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $categoryID);
		$command->execute();

	}

	public function deletePostCategory($postID,$categoryID)
	{
		$sql="DELETE FROM tblPost2Category WHERE post_id=? AND category_id=?";
		$command=$this->_db->createCommand($sql);
		$command->bindValue(1, $postID);
		$command->bindValue(2, $categoryID);
		$result=$command->query();

		if($result->getRowCount()>0)
		{
			$sql="UPDATE tblCategories SET post_count=post_count-1 WHERE id=?";
			$command=$this->_db->createCommand($sql);
			$command->bindValue(1, $categoryID);
			$command->execute();
		}
	}

	public function queryEarliestPostTime()
	{
		$sql="SELECT MIN(create_time) AS create_time FROM tblPosts";
		$result=$this->query($sql);
		if(($row=$result->read())!==false)
			return $row['create_time'];
		else
			return time();
	}
}

class UserRecord
{
	const ROLE_USER=0;
	const ROLE_ADMIN=1;
	const STATUS_NORMAL=0;
	const STATUS_DISABLED=1;
	const STATUS_PENDING=2;
	public $ID;
	public $Name;
	public $FullName;
	public $Role;
	public $Password;
	public $VerifyCode;
	public $Email;
	public $CreateTime;
	public $Status;
	public $Website;
}

class PostRecord
{
	const STATUS_PUBLISHED=0;
	const STATUS_DRAFT=1;
	const STATUS_PENDING=2;
	const STATUS_STICKY=3;
	public $ID;
	public $AuthorID;
	public $AuthorName;
	public $CreateTime;
	public $ModifyTime;
	public $Title;
	public $Content;
	public $Status;
	public $CommentCount;
}

class CommentRecord
{
	public $ID;
	public $PostID;
	public $AuthorName;
	public $AuthorEmail;
	public $AuthorWebsite;
	public $AuthorIP;
	public $CreateTime;
	public $Status;
	public $Content;
}

class CategoryRecord
{
	public $ID;
	public $Name;
	public $Description;
	public $PostCount;
}