<?php
class Post {
	var $db;
	var $keyword;
	var $analyzer;
	function __construct() {
		include 'db.inc.php';
		$this->db = $db;
	}

	function postFilter($contents, $options) {
		if (! $contents)
			return $contents;
		if (empty($options['url']))
			return $contents;

		$u = '> '.$options['url']."\n";
		$url_len = strlen($u);
		foreach ($contents as $k => $v) {
			$p = strpos($v['content'], $u);
			if ($p !== FALSE) {
				if (strlen($v['content']) != $url_len) {
					$contents[$k]['content'] = substr($v['content'], 0, $p).substr($v['content'], $p+$url_len);
				}
			}
		}
		return $contents;
	}

	function getUnsafe($options=NULL) {
		return $this->postFilter($this->db->select(
			$this->_getFrom($options),
			$this->_getField($options),
			$this->_getWhere($options),
			$this->_getParam($options),
			array(
				'order' => (empty($options['order']) ? 'id desc' : $options['order']),
				'limit' => (empty($options['limit']) ? '20' : $options['limit'])
			)
		), $options);
	}

	function get($options=NULL, $limit=20) {
		return $this->postFilter($this->db->select(
			$this->_getFrom($options),
			$this->_getField($options),
			$this->_getWhere($options),
			$this->_getParam($options),
			array('order' => 'id desc', 'limit' => $limit)
		), $options);
	}

	function getNew($id, $options=NULL) {
		return $this->postFilter($this->db->select(
			$this->_getFrom($options),
			$this->_getField($options),
			$this->_getWhere($options, 'p.id>?'),
			$this->_getParam($options, array($id)),
			array('order' => 'id desc', 'limit' => 30)
		), $options);
	}

	function getOld($id, $options=NULL) {
		return $this->postFilter($this->db->select(
			$this->_getFrom($options),
			$this->_getField($options),
			$this->_getWhere($options, 'p.id<?'),
			$this->_getParam($options, array($id)),
			array('order' => 'id desc', 'limit' => 30)
		), $options);
	}

	function count($id, $options=NULL) {
		return $this->db->count(
			'^'.$this->db->getTableName($this->_getTable($options)).' p',
			$this->_getWhere($options, 'id>?'),
			$this->_getParam($options, array($id))
		);
	}

	function countOld($id, $options=NULL) {
		return $this->db->count(
			'^'.$this->db->getTableName($this->_getTable($options)).' p',
			$this->_getWhere($options, 'id<?'),
			$this->_getParam($options, array($id))
		);
	}

	function post($content, $anonymous, $user, $trip=NULL, $options=array()) {
		$content = trim(mb_convert_kana($content,'rnsKV'));
		if (empty($content))
			return FALSE;

		if (! $this->keyword) {
			require_once('TKeyword.class.php');
			$this->keyword = new TKeyword();
			$this->keyword->load();
		}

		if (! $this->analyzer) {
			require_once('TCommand.class.php');
			$this->analyzer = new TCommandAnalyzer();
		}

		//特定URLへの書き込みは自動的に冒頭にURLを付与
		if (! empty($options['url'])) {
			if (strpos($content, $options['url']) === FALSE) {
				$content = '> '.$options['url']."\n".$content;
			}
		}

		$themes = array();
		$urls = array();

		//コマンド解析
		$this->analyzer->clear($content);
		$this->analyzer->analysis();
		$del_commands = array();
		$first_theme = NULL;
		foreach ($this->analyzer->commands as $command) {
			if (empty($command->target))
				continue;

			if (is_a($command, 'ThemeCommand')) {
				$themes[] = $command->target;
				if (! $first_theme)
					$first_theme = $command->target;
				if (! POST_THEMETAG_KEYWORD)
					$del_commands[] = $command;
			} else if (is_a($command, 'UrlCommand')) {
				if (POST_MATCH_URL_LIMIT) {
					if (POST_MATCH_URL_LIMIT < 0)
						return FALSE;
					if (count($urls) >= POST_MATCH_URL_LIMIT)
						return FALSE;
				}
				if (substr($command->target, -1) == '/') {
					$urls[] = substr($command->target, 0, -1);
					$this->tryCreateUrl(substr($command->target, 0, -1));
				} else {
					$urls[] = $command->target;
					$this->tryCreateUrl($command->target);
				}
				if (! POST_URL_KEYWORD)
					$del_commands[] = $command;
			} else if (is_a($command, 'ReplyCommand')) {
				if (empty($options['t_id']))
					$options['t_id'] = $command->target;
				$del_commands[] = $command;
			}
		}

		//文中のコマンドを削除し、キーワード解析対象から除外
		$this->analyzer->deleteDist($del_commands);

		//キーワード解析
		$func = NULL;
		$keywords = NULL;
		switch (POST_MATCH_LONGEST) {
			case 0:
				$func = 'matchAll';
			break;
			case 1:
				$func = 'matchLongest';
			break;
			case 2:
				$func = 'matchLongest2';
			break;
		}
		if ($func)
			$keywords = $this->keyword->$func($this->analyzer->dist);
		$themes = array_merge($themes, $this->getThemesByKeywords($keywords));

		//メイン書き込み
		$basic_values = array(
			'content' => $content,
			'created' => '^CURRENT_TIMESTAMP',
			'anonymous' => $anonymous ? '1' : '0'
		);

		// ユーザ処理
		if ($user)
			$basic_values['user_id'] = $user['id'];

		// 匿名処理
		if ($anonymous) {
			$basic_values['display_name'] =  empty($_SESSION['uid']) ? 'anonymous' : $_SESSION['uid'];
			// トリップ処理（匿名のみ）
			if (! empty($trip))
				$basic_values['trip'] = hash_visualization(md5(BULLET_TRIP_SECRET_KEY.$trip));
		} else {
			//匿名じゃなければユーザ名を表示名に指定
			$basic_values['display_name'] =  $user['display_name'];
		}

		// リプライ（リプライは単一のみ可能）
		if (! empty($options['t_id'])) {
			$target_post = $this->read($options['t_id']);
			if ($target_post === FALSE)
				return FALSE;

			// 返信を元書き込みのURLに関連付ける
			$this->analyzer->clear($target_post['content']);
			$this->analyzer->analysis();
			foreach ($this->analyzer->commands as $command) {
				if (is_a($command, 'UrlCommand')) {
					// 既に存在している想定。削除された場合エラーケースになる
					if (substr($command->target, -1) == '/') {
						$urls[] = substr($command->target, 0, -1);
					} else {
						$urls[] = $command->target;
					}
				}
			}

			//返信元のメインテーマには関連付ける。
			//この書き込みのメインテーマが存在しない場合はメインテーマとして、メインテーマが存在する場合はサブテーマとして関連付け
			if (! empty($target_post['json_theme']))
				$themes = array_merge($themes, json_decode($target_post['json_theme']));
			if (! empty($target_post['t_theme']))
				$themes[] = $target_post['t_theme'];

			if ($target_post['t_id'])
				$options['t_id'] = $target_post['t_id'];

			$basic_values['t_id'] = $options['t_id'];
		}

		//テーマのdistinctと存在しないテーマの除外
		if (! empty($themes))
			$themes = $this->getThemesByThemes($themes);

		//メインテーマ設定
		if (! empty($options['theme']) || $first_theme) {
			$t = empty($options['theme']) ? $first_theme : $options['theme'];
			$t_theme = $this->readTheme($t);
			if (! empty($t_theme)) {
				$index = array_search($t_theme['name'], $themes, TRUE);
				if ($index !== FALSE)
					array_splice($themes, $index, 1);

				$basic_values['t_theme'] = $t_theme['name'];
			}
		}

		// テーマ数絞込み
		if (POST_MATCH_LIMIT) {
			if (POST_MATCH_LIMIT < 0) {
				$themes = array();
				unset($basic_values['t_theme']);
			} else {
				if (count($themes) > POST_MATCH_LIMIT) {
					usort($themes, '_tkeyword_strcomp');
					while (count($themes) > POST_MATCH_LIMIT)
						array_pop($themes);
				}
			}
		}
		$basic_values['json_theme'] = json_encode($themes);

		// urlのdistinctと存在しないurlの除外
		if (! empty($urls))
			$urls = $this->getUrlsByUrls($urls);

		$ret = $this->db->insert('posts', $basic_values);
		if ($ret === FALSE)
			return FALSE;

		if (! empty($basic_values['t_theme'])) {
			array_unshift($themes, $t_theme['name']);
		}

		//---------- サブ書き込み
		//Note: 本当は遅延書き込みにしたいが・・
		//Themes
		if (! empty($themes)) {
			foreach ($themes as $theme) {
				$this->db->insert(
					$this->getThemeTable($theme),
					array_merge($basic_values, array(
						'id' => $ret,
						'theme' => $theme,
					))
				);
			}

			$key_str = $this->_getKeyStr($themes);
			$this->db->pureUpdate(
				'themes',
				array('cnt' => '^cnt+1'),
				'`name` in ('.$key_str.')'
			);
		}

		//Keywords
		if (! empty($keywords)) {
			foreach ($keywords as $keyword) {
				$this->db->insert(
					$this->getKeywordTable($keyword),
					array_merge($basic_values, array(
						'id' => $ret,
						'keyword' => $keyword,
					))
				);
			}

			$key_str = $this->_getKeyStr($keywords);
			$this->db->pureUpdate(
				'keywords',
				array('matched' => '^CURRENT_TIMESTAMP', 'cnt' => '^cnt+1'),
				'keyword in ('.$key_str.')'
			);
		}

		//Urls
		if (! empty($urls)) {
			foreach ($urls as $url) {
				$this->db->insert(
					$this->getUrlTable($url),
					array_merge($basic_values, array(
						'id' => $ret,
						'url' => $url,
					))
				);
			}

			$key_str = $this->_getKeyStr($urls);
			$this->db->pureUpdate(
				'urls',
				array('cnt' => '^cnt+1'),
				'url in ('.$key_str.')'
			);
		}
		//サブ書き込み ----------

		return $ret;
	}

	function _getKeyStr($keywords) {
		$key_str = array();
		foreach ($keywords as $keyword) {
			$key_str[] = $this->db->escape($keyword);
		}
		return implode(',',$key_str);
	}

	function getThemesByThemes($themes) {
		return $this->db->selectList(
			'themes',
			'name',
			'name in ('.$this->_getKeyStr($themes).')'
		);
	}

	function getUrlsByUrls($urls) {
		return $this->db->selectList(
			'urls',
			'url',
			'url in ('.$this->_getKeyStr($urls).')'
		);
	}

	function getThemesByKeywords($keywords) {
		$key_str = $this->_getKeyStr($keywords);
		if (empty($key_str))
			return array();
		return $this->db->selectList(
			'themes',
			'name',
			'key1 in ('.$key_str.') or key2 in ('.$key_str.') or key3 in ('.$key_str.')'
		);
	}

	function read($id) {
		return $this->db->selectRow(
			'posts',
			NULL,
			array('id' => $id)
		);
	}

	function readBasic($id,$options=NULL) {
		return $this->db->selectRow(
			$this->_getFrom($options),
			$this->_getField($options),
			array('p.id' => $id)
		);
	}

	function readTheme($name) {
		return $this->db->selectRow(
			'themes',
			NULL,
			array('name' => $name)
		);
	}

	function readKeyword($keyword) {
		$tmp = $this->db->selectRow(
			'keywords',
			NULL,
			array('keyword' => $keyword)
		);
		if ($tmp === FALSE)
			return FALSE;
		$tmp['themes'] = $this->getThemesByKeywords(array($tmp['keyword']));
		return $tmp;
	}

	function readUrl($url) {
		return $this->db->selectRow(
			'urls',
			NULL,
			array('url' => $url)
		);
	}

	function getThemeTable($theme) {
		if (MULTI_THEME_TABLE)
			return 'theme_'.$this->getStringIdentifier128($theme).'_posts';
		return 'theme_posts';
	}

	function getKeywordTable($keyword) {
		if (MULTI_KEYWORD_TABLE)
			return 'keyword_'.$this->getStringIdentifier128($keyword).'_posts';
		return 'keyword_posts';
	}

	function getUrlTable($url) {
		if (MULTI_URL_TABLE)
			return 'url_'.$this->getUrlIdentifier128($url).'_posts';
		return 'url_posts';
	}

	function getFavoriteTable($favorite_id) {
		//TODO: support multi table?
		return 'favorite_posts';
	}

	function getUrlIdentifier128($s) {
		//128 character
		$seed = '0123456789abcdefghijklmnopqrstuvwxyzあいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわをんアイウエオカキクケコサシセスソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲン';
		$hash = md5($s, TRUE);
		$hash = unpack('C', $hash);
		return mb_substr($seed, $hash[1] % 128, 1);
	}

	function getStringIdentifier128($s) {
		//128 character
		$seed = '0123456789abcdefghijklmnopqrstuvwxyzあいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわをんアイウエオカキクケコサシセスソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲン';
		$len = mb_strlen($seed);
		$c = mb_substr($s, 0, 1);
		if (strlen($c) == 1)
			$c = strtolower($c);
		if (mb_strpos($seed, $c) !== FALSE)
			return $c;

		$c = ord(substr($c, -1));
		return mb_substr($seed, $c % $len, 1);
	}

	function updateTheme($old_name, $theme, $description, $key1=NULL, $key2=NULL, $key3=NULL) {
		$theme = trim(mb_convert_kana($theme,'rnsKV'));
		if (empty($theme))
			return FALSE;

		if (strpos($theme, '/') !== FALSE)
			return FALSE;

		if (mb_strlen($theme) < 2)
			return FALSE;

		$old = $this->readTheme($old_name);
		if (empty($old))
			return FALSE;

		$_keys = array();
		if ($key1)
			$_keys[] = $key1;
		if ($key2)
			$_keys[] = $key2;
		if ($key3)
			$_keys[] = $key3;
		$keys = array();
		foreach ($_keys as $k => $v) {
			$v = mb_strtoupper(trim(mb_convert_kana($v,'rnsKV')));
			if (empty($v))
				continue;
			if (in_array($v, $keys, TRUE))
				continue;
			$keys[] = $v;
		}

		$del_candidate_keys = array();
		$old_keys = array($old['key1'], $old['key2'], $old['key3']);
		foreach ($old_keys as $old_key) {
			if (empty($old_key))
				continue;

			if (in_array($old_key, $keys, TRUE) === FALSE) {
				$del_candidate_keys[] = $old_key;
			}
		}

		foreach ($del_candidate_keys as $del_candidate_key) {
			$cnt = $this->db->count('themes', 'key1=? or key2=? or key3=?', array($del_candidate_key, $del_candidate_key, $del_candidate_key));
			if ($cnt == 1) {
				//Note: 消すべきか消さぬべきか？
				//$this->db->delete('keywords', array('keyword' => $del_candidate_key));
			}
		}

		try {
			$this->db->begin();
			foreach ($keys as $k) {
				if ($this->tryCreateKeyword($k) === FALSE)
					throw new Exception('Can not create keyword');
			}

			$ret = $this->db->update(
				'themes',
				array(
					'name' => $theme,
					'description' => $description,
					'key1' => empty($keys[0]) ? '' : $keys[0],
					'key2' => empty($keys[1]) ? '' : $keys[1],
					'key3' => empty($keys[2]) ? '' : $keys[2],
					'modified' => '^CURRENT_TIMESTAMP'
				),
				array('name'  => $old['name'])
			);
			if ($ret === FALSE)
				throw new Exception('Can not create theme data');

			$this->db->commit();
		} catch (Exception $ex) {
			var_dump($ex->getMessage());
			$this->db->rollback();
			return FALSE;
		}

		return TRUE;
	}

	// danger method
	function clearTables($options=NULL) {
		$seed = '0123456789abcdefghijklmnopqrstuvwxyzあいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわをんアイウエオカキクケコサシセスソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲン';
		$len = mb_strlen($seed);
		for ($i=0; $i<$len; $i++) {
			$this->db->query('drop table `'.$this->db->getTableName('theme_'.mb_substr($seed, $i, 1).'_posts').'`');
			$this->db->query('drop table `'.$this->db->getTableName('keyword_'.mb_substr($seed, $i, 1).'_posts').'`');
			$this->db->query('drop table `'.$this->db->getTableName('url_'.mb_substr($seed, $i, 1).'_posts').'`');
		}
		$this->db->pureDelete('posts');
		$this->db->pureDelete('keyword_posts');
		$this->db->pureDelete('theme_posts');
		$this->db->pureDelete('url_posts');
		if (! empty($options['urls'])) {
			$this->db->pureDelete('urls');
		}
		if (! empty($options['keywords'])) {
			$this->db->pureDelete('keywords');
		}
		if (! empty($options['themes'])) {
			$this->db->pureDelete('themes');
		}
		if (! empty($options['users'])) {
			$this->db->pureDelete('users', 'user_type_id!=1');
		}
		if (! empty($options['users2'])) {
			$this->db->pureDelete('users');
		}
	}

	// danger method
	function refreshTables() {
		$themes = $this->db->select('themes');
		foreach ($themes as $theme)
			$this->_tryCreateTable($this->getThemeTable($theme['name']), 'theme_posts');

		$keywords = $this->db->select('keywords');
		foreach ($keywords as $keyword)
			$this->_tryCreateTable($this->getKeywordTable($keyword['keyword']), 'keyword_posts');

		$urls = $this->db->select('urls');
		foreach ($urls as $url)
			$this->_tryCreateTable($this->getUrlTable($url['url']), 'url_posts');
	}

	function createTheme($theme, $description, $key1=NULL, $key2=NULL, $key3=NULL) {
		$theme = trim(mb_convert_kana($theme,'rnsKV'));
		if (empty($theme))
			return FALSE;

		if (strpos($theme, '/') !== FALSE)
			return FALSE;

		if (mb_strlen($theme) < 2)
			return FALSE;

		$_keys = array();
		if ($key1)
			$_keys[] = $key1;
		if ($key2)
			$_keys[] = $key2;
		if ($key3)
			$_keys[] = $key3;
		$keys = array();
		foreach ($_keys as $k => $v) {
			$v = mb_strtoupper(trim(mb_convert_kana($v,'rnsKV')));
			if (empty($v))
				continue;
			if (in_array($v, $keys, TRUE))
				continue;
			$keys[] = $v;
		}

		$t = $this->getThemeTable($theme);
		$ret = $this->_tryCreateTable($t, 'theme_posts');
		if ($ret === FALSE)
			return FALSE;

		try {
			$this->db->begin();
			foreach ($keys as $k) {
				if ($this->tryCreateKeyword($k) === FALSE)
					throw new Exception('Can not create keyword');
			}

			$ret = $this->db->insert(
				'themes',
				array(
					'name' => $theme,
					'description' => $description,
					'key1' => empty($keys[0]) ? '' : $keys[0],
					'key2' => empty($keys[1]) ? '' : $keys[1],
					'key3' => empty($keys[2]) ? '' : $keys[2],
					'owner_id' => empty($_SESSION['user']['id']) ? '0' : $_SESSION['user']['id'],
					'created' => '^CURRENT_TIMESTAMP',
					'modified' => '^CURRENT_TIMESTAMP'
				)
			);
			if ($ret === FALSE)
				throw new Exception('Can not create theme data');

			$this->db->commit();
		} catch (Exception $ex) {
			$this->db->rollback();
			return FALSE;
		}

		return TRUE;
	}

	function tryCreateKeyword($keyword) {
		$exists = $this->db->selectRow(
			'keywords',
			array('keyword'),
			array('keyword' => $keyword)
		);
		if ($exists === FALSE)
			return $this->createKeyword($keyword);

		return TRUE;
	}

	function createKeyword($keyword) {
		$keyword = trim(mb_convert_kana($keyword,'rnsKV'));
		if (empty($keyword))
			return FALSE;

		if (strpos($keyword, '/') !== FALSE)
			return FALSE;

		if (mb_strlen($keyword) < 2)
			return FALSE;

		$t = $this->getKeywordTable($keyword);
		$ret = $this->_tryCreateTable($t, 'keyword_posts');
		if ($ret === FALSE)
			return FALSE;

		return $this->db->insert(
			'keywords',
			array(
				'keyword' => $keyword,
				'owner_id' => empty($_SESSION['user']['id']) ? '0' : $_SESSION['user']['id'],
				'created' => '^CURRENT_TIMESTAMP'
			)
		);
	}

	function tryCreateUrl($url) {
		$exists = $this->db->selectRow(
			'urls',
			array('url'),
			array('url' => $url)
		);
		if ($exists === FALSE)
			$this->createUrl($url);

		return TRUE;
	}

	function createUrl($url) {
		$url = trim($url);
		if (empty($url))
			return FALSE;
		if (substr($url,-1) == '/')
			$url = substr($url,0,-1);
		if (empty($url))
			return FALSE;

		$t = $this->getUrlTable($url);
		$ret = $this->_tryCreateTable($t, 'url_posts');
		if ($ret === FALSE)
			return FALSE;

		$contents = @file_get_contents($url);
		$title = '';
		$summary = '';
		if ($contents) {
			mb_detect_order('UTF-8,eucjp-win,sjis-win,ascii');
			$contents = @mb_convert_encoding(
				$contents,
				mb_internal_encoding(),
				mb_detect_encoding($contents)
			);
			$contents = str_replace(array("\r","\n"), '', $contents);
			$matches = array();
			if ( preg_match( "/<title>(.*?)<\/title>/i", $contents, $matches) )
				$title = $matches[1];
		}

		$log_ret = _a_log('url', 'create', array(
			'target_key' => $url
		));
		if ($log_ret) {
			$ret = $this->db->insert(
				'urls',
				array(
					'url' => $url,
					'title' => $title,
					'summary' => $summary,
					'owner_id' => empty($_SESSION['user']['id']) ? '0' : $_SESSION['user']['id'],
					'created' => '^CURRENT_TIMESTAMP'
				)
			);
			_a_log_write($log_ret, array('success' => $ret === FALSE ? '0' : '1'));
			return $ret !== FALSE;
		} else {
			return FALSE;
		}
	}

	function _tryCreateTable($new_table_name, $master_table_name) {
		if ($new_table_name == $master_table_name)
			return TRUE;
		$ret = $this->db->execute('select 1 from '.$this->db->getTableName($new_table_name));
		if ($ret === FALSE) {
			$mtb_name = $this->db->getTableName($master_table_name);
			$create_table = $this->db->getAll('show create table `'.$mtb_name.'`');
			if (empty($create_table))
				return FALSE;

			$create_table = str_replace(
				'`'.$mtb_name.'`',
				'`'.$this->db->getTableName($new_table_name).'`',
				$create_table[0]['Create Table']
			);

			$ret = $this->db->execute($create_table);
			if ($ret === FALSE)
				return FALSE;
		}

		return TRUE;
	}

	function getActiveKeyword($limit=10, $w=NULL, $params=array()) {
		return $this->db->select(
			'keywords',
			NULL,
			$w,
			$params,
			array('limit' => $limit, 'order' => 'matched desc')
		);
	}

	function getRecentTheme($limit=10, $w=NULL, $params=array()) {
		return $this->db->select(
			'themes',
			NULL,
			$w,
			$params,
			array('limit' => $limit, 'order' => 'created desc')
		);
	}

	function getRecentUrl($limit=10, $w=NULL, $params=array()) {
		return $this->db->select(
			'urls',
			NULL,
			$w,
			$params,
			array('limit' => $limit, 'order' => 'created desc')
		);
	}

	function getRecentUrlUrls($limit=3) {
		return $this->db->selectList('urls', 'url', array('order' => 'created desc', 'limit' => $limit));
	}

	function getHotUrl($limit=100, $ng_urls=array()) {
		$old_id = $this->db->pureSelectOne(
			'posts',
			'id',
			array('order' => 'id desc', 'limit' => '5000,1')
		);
		$sql = 'select p.url, u.title, u.cnt, count( * ) as rcnt
from `'.$this->db->getTableName('url_posts').'` p
inner join `'.$this->db->getTableName('urls').'` u on p.url = u.url';

		$con = array();
		if ($old_id)
			$con[] = 'p.id > '.$old_id;

		if ($ng_urls)
			$con[] = 'u.url not in ('.$this->_getKeyStr($ng_urls).')';

		if (! empty($con))
			$sql .= ' where '.implode(' and ', $con);

		$sql .= ' group by url order by rcnt desc';
		if ($limit)
			$sql .= ' limit '.((int)$limit);

		return $this->db->getAll($sql);
	}

	function getThemeCloud($limit=30, $order=FALSE) {
		$post_table = $this->db->getTableName('posts');

		$old_id = $this->db->pureSelectOne(
			'posts',
			'id',
			array('order' => 'id desc', 'limit' => '5000,1')
		);

		$sql = 'select p.t_theme, count(*) as rcnt, t.description, t.cnt from '.$post_table. ' p inner join '.$this->db->getTableName('themes').' t on p.t_theme=t.`name` where p.t_theme is not null';
		if ($old_id) {
			$sql .= ' and id > '.$old_id;
		}
		$sql .= ' group by t_theme';
		if ($order)
			$sql .= ' order by rcnt desc';
		if ($limit) {
			$sql .= ' limit ?';
			return $this->db->getAll($sql, $limit);
		}

		return $this->db->getAll($sql);
	}

	function countTheme($w=NULL, $params=array()) {
		return $this->db->count('themes', $w, $params);
	}

	function countActiveKeyword($w=NULL, $params=array()) {
		return $this->db->count('keywords', $w, $params);
	}

	function countUrl($w=NULL, $params=array()) {
		return $this->db->count('urls', $w, $params);
	}

	function deletePost($id) {
		$this->db->delete(
			'posts',
			array('id' => $id)
		);
		$this->db->delete(
			'url_posts',
			array('id' => $id)
		);
		$this->db->delete(
			'theme_posts',
			array('id' => $id)
		);
		$this->db->delete(
			'keyword_posts',
			array('id' => $id)
		);
		return TRUE;
	}

	function deleteTheme($name) {
		$this->db->delete(
			'themes',
			array('name' => $name)
		);
		$this->db->delete(
			'theme_posts',
			array('theme' => $name)
		);
		//delでもいいかもしれんけど。
		//json_themeは更新されない点に注意。
		//json_themeを更新する場合、対象をtheme_postsから引っ張る → 再計算で実装は可能。物によっては非常に遅い
		$this->db->update(
			'posts',
			array('t_theme' => NULL),
			array('t_theme' => $name)
		);
		$this->db->update(
			'url_posts',
			array('t_theme' => NULL),
			array('t_theme' => $name)
		);
		$this->db->update(
			'keyword_posts',
			array('t_theme' => NULL),
			array('t_theme' => $name)
		);
		$this->db->update(
			'theme_posts',
			array('t_theme' => NULL),
			array('t_theme' => $name)
		);
		return TRUE;
	}

	function deleteKeyword($keyword) {
		$this->db->delete(
			'keywords',
			array('keyword' => $keyword)
		);
		$this->db->delete(
			'keyword_posts',
			array('keyword' => $keyword)
		);
		//マッチしたテーマは変更してない
		//変更する場合、keyword_postsから再計算することは可能だが、マッチ当初のデータは失われているので、
		//再現は不可能で、再計算になる
		$this->db->update(
			'themes',
			array('key1' => NULL),
			array('key1' => $keyword)
		);
		$this->db->update(
			'themes',
			array('key2' => NULL),
			array('key2' => $keyword)
		);
		$this->db->update(
			'themes',
			array('key3' => NULL),
			array('key3' => $keyword)
		);
		return TRUE;
	}

	function deleteUrl($url) {
		$this->db->delete(
			'urls',
			array('url' => $url)
		);
		$this->db->delete(
			'url_posts',
			array('url' => $url)
		);
		//コンテンツアップデートは現状パフォーマンス的にちょっと無理
		return TRUE;
	}

	function isValidFavoriteType($target_type) {
		switch($target_type) {
		case 'post':
		case 'my':
		case 'user':
		case 'theme':
		case 'keyword':
		case 'url':
		case 'trip':
		case 'name':
			return TRUE;
		}
		return FALSE;
	}

	function getFavorites($user_id=NULL, $no_items=FALSE) {
		if (empty($user_id))
			$user_id = @$_SESSION['user']['id'];
		$ret = $this->db->select(
			'user_favorites',
			NULL,
			'user_id=?',
			array($user_id),
			array(
				'order' => 'created asc'
			)
		);

		if (! $no_items) {
			foreach ($ret as $k => $v) {
				if (! empty($v['item_cache'])) {
					$ret[$k]['item_cache'] = unserialize($v['item_cache']);
					$ret[$k]['items'] = array();
					foreach ($ret[$k]['item_cache'] as $item_cache) {
						$ret[$k]['items'][] = $item_cache['fi'];
					}
				} else {
					$ret[$k]['items'] = array();
				}
			}
		}
		return $ret;
	}

	function getFavoriteItems($id=NULL) {
		if (empty($id)) {
			$exists_ids = $this->db->selectList(
				'favorites_items',
				'item_id',
				'user_id=?',
				array(@$_SESSION['user']['id']),
				array('distinct' => TRUE, 'order' => 'item_id asc')
			);
			return $this->db->select(
				'user_favorite_items',
				NULL,
				'user_id=?'.($exists_ids ? ' and id not in ('.implode(',', $exists_ids).')' : ''),
				array(@$_SESSION['user']['id']),
				array('order' => 'id desc')
			);
		}

		return $this->db->select(
			'^'.$this->db->getTableName('user_favorite_items').' ufi inner join '.$this->db->getTableName('favorites_items').' fi on ufi.id=fi.item_id',
			array('^ufi.*'),
			'fi.favorite_id=?',
			array($id)
		);
	}

	function countFavoriteItems($id) {
		if (empty($id)) {
			$exists_ids = $this->db->selectList(
				'favorites_items',
				'item_id',
				'user_id=?',
				array(@$_SESSION['user']['id'])
			);
			return $this->db->count(
				'user_favorite_items',
				'id not in ('.implode(',', $exists_ids).')'
			);
		}

		return $this->db->count(
			'^'.$this->db->getTableName('user_favorite_items').' ufi inner join '.$this->db->getTableName('favorites_items').' fi on ufi.id=fi.item_id',
			'fi.favorite_id=?',
			array($id)
		);
	}

	function isFavorited($target_type, $target_key=NULL) {
		$w = 'user_id=? and target_type=?';
		$params = array(@$_SESSION['user']['id'], $target_type);
		if ($target_key) {
			$w .= ' and target_key=?';
			$params[] = $target_key;
		}
		return $this->db->pureSelectRow(
			'user_favorite_items',
			NULL,
			$w,
			$params
		) !== FALSE;
	}

	function readFavoriteItemById($user_id, $id) {
		return $this->db->pureSelectRow(
			'user_favorite_items',
			NULL,
			'user_id=? and id=?',
			array($user_id, $id)
		);
	}

	function readFavoriteItem($user_id, $target_type, $target_key=NULL) {
		$w = 'user_id=? and target_type=?';
		$params = array($user_id, $target_type);
		if ($target_key) {
			$w .= ' and target_key=?';
			$params[] = $target_key;
		}
		return $this->db->pureSelectRow(
			'user_favorite_items',
			NULL,
			$w,
			$params
		);
	}

	function countFavorite($target_type, $target_key=NULL) {
		$w = 'target_type=?';
		$params = array($target_type);
		if ($target_key) {
			$w .= ' and target_key=?';
			$params[] = $target_key;
		}
		return $this->db->pureSelectOne(
			'user_favorite_items',
			'^count(*) as c',
			$w,
			$params
		);
	}

	function addFavoriteItem($target_type, $target_key=NULL) {
		$info = NULL;
		switch($target_type) {
		case 'post':
			$post = $this->read($target_key);
			$info = mb_truncate($post['content'], 61);
		break;
		case 'my':
			//null
		break;
		case 'user':
			$info = $this->db->selectOne('users', 'display_name', array('login_id' => $target_key));
		break;
		case 'theme':
			//null
		break;
		case 'keyword':
			//null
		break;
		case 'url':
			$info = $this->db->selectOne(
				'urls',
				'title',
				array('url' => $target_key)
			);
			if (empty($info))
				$info = $target_key;
		break;
		case 'trip':
			//null
		break;
		case 'name':
			//null
		break;
		}

		return $this->db->insert(
			'user_favorite_items',
			array(
				'user_id' => @$_SESSION['user']['id'],
				'target_type' => $target_type,
				'target_key' => $target_key,
				'info' => $info,
				'created' => '^CURRENT_TIMESTAMP'
			)
		);
	}

	function readFavorite($id) {
		$favorite = $this->db->selectRow(
			'user_favorites',
			array('id', 'user_id', 'name', 'last_id', 'created', 'modified'),
			array('id' => $id)
		);
		if ($favorite === FALSE)
			return FALSE;

		$favorite['items'] = $this->db->select(
			'^'.$this->db->getTableName('favorites_items').' fi inner join '.$this->db->getTableName('user_favorite_items').' ufi on fi.item_id=ufi.id',
			array(
				'ufi.id',
				'ufi.target_type',
				'ufi.target_key',
				'ufi.info',
				'ufi.created'
			),
			'fi.favorite_id=?',
			array($id)
		);

		return $favorite;
	}

	function updateFavoritePost($id, $favo=NULL) {
		$id = (int)$id;
		if (! $favo)
			$favo = $this->readFavoriteCache($id);
		if (empty($favo['item_cache']))
			return FALSE;

		$unions = array();
		$params = array();
		$field = $id.', p.id, p.user_id, p.anonymous, p.display_name, p.trip, p.content, p.json_theme, p.t_id, p.t_theme, p.t_user_id, p.created';
		foreach ($favo['item_cache'] as $item) {
			$unions[] = 'select '.$field.' from '.$item['t'].' p where p.id > ? and ('.$item['w'].')';
			$params = array_merge($params, array($favo['last_id']), $item['p']);
		}
		$sql = 'insert into '.$this->db->getTableName($this->getFavoriteTable($id));
		$sql .= ' (favorite_id, id, user_id, anonymous, display_name, trip, content, json_theme, t_id, t_theme, t_user_id, created)';
		$sql .= ' select '.$field.' from (('.implode(') union (', $unions).') order by id) p group by id';
		$ret = $this->db->execute($sql, $params, TRUE);
		if (empty($ret))	//false or 0
			return FALSE;

		$max_id = $this->db->selectOne(
			'favorite_posts',
			'^max(id)',
			array('favorite_id' => $id)
		);
		$this->db->update(
			'user_favorites',
			array(
				'last_id' => $max_id,
				'modified' => '^CURRENT_TIMESTAMP'
			),
			array('id' => $id) 
		);

		return TRUE;
	}

	function clearFavoritePosts($id) {
		$this->db->delete(
			$this->getFavoriteTable($id),
			array('favorite_id' => $id)
		);
		$this->db->update(
			'user_favorites',
			array(
				'last_id' => 0,
				'modified' => '^CURRENT_TIMESTAMP'
			),
			array('id' => $id) 
		);
		return TRUE;
	}

	function favorite($id, $offset=0, $limit=20) {
		return $this->db->select(
			'favorite_posts',
			NULL,
			'favorite_id=?',
			array($id),
			array(
				'order' => 'id desc',
				'limit' => $this->db->safeLimit($offset.','.$limit)
			)
		);
	}


	function readFavoriteCache($id) {
		$favorite = $this->db->selectRow(
			'user_favorites',
			array('id', 'user_id', 'name', 'last_id', 'item_cache', 'created', 'modified'),
			array('id' => $id)
		);
		if ($favorite === FALSE)
			return FALSE;

		if (! empty($favorite['item_cache'])) {
			$favorite['item_cache'] = unserialize($favorite['item_cache']);
			$favorite['items'] = array();
			foreach ($favorite['item_cache'] as $item_cache) {
				$favorite['items'][] = $item_cache['fi'];
			}
		} else {
			$favorite['items'] = array();
		}

		//items;
		return $favorite;
	}

	function isValidFavoriteIds($item_ids) {
		if ($item_ids) {
			$key_str = $this->_getKeyStr($item_ids);
			$favorite_items = $this->db->select('user_favorite_items', array('user_id'), 'id in ('.$key_str.')');
			foreach ($favorite_items as $favorite_item) {
				if ($favorite_item['user_id'] != @$_SESSION['user']['id']) {
					return FALSE;
				}
			}
		}

		return TRUE;
	}

	function addFavorite($name, $item_ids=NULL) {
		if ($this->isValidFavoriteIds($item_ids) === FALSE)
			return FALSE;

		$favorite_id = $this->db->insert(
			'user_favorites',
			array(
				'user_id' => @$_SESSION['user']['id'],
				'name' => $name,
				'created' => '^CURRENT_TIMESTAMP',
				'modified' => '^CURRENT_TIMESTAMP'
			)
		);
		if ($favorite_id === FALSE)
			return FALSE;

		if ($item_ids) {
			$this->db->begin();
			foreach ($item_ids as $item_id) {
				$ret = $this->db->insert(
					'favorites_items',
					array(
						'favorite_id' => $favorite_id,
						'item_id' => $item_id,
						'user_id' => @$_SESSION['user']['id'],
						'created' => '^CURRENT_TIMESTAMP'
					)
				);
				if ($ret === FALSE) {
					$this->db->rollback();
					return FALSE;
				}
			}
			$this->db->commit();

			$this->updateFavoriteCache($favorite_id);
		}
		return $favorite_id;
	}

	function updateFavorite($id, $update_values) {
		$favorite = $this->readFavoriteCache($id);
		if ($favorite['user_id'] != @$_SESSION['user']['id'])
			return FALSE;

		$this->db->begin();
		if (! empty($update_values['item_ids'])) {
			if ($this->isValidFavoriteIds($update_values['item_ids']) === FALSE) {
				$this->db->rollback();
				return FALSE;
			}

			$del_ids = array();
			$add_ids = array();
			$exists_ids = array();
			foreach ($favorite['items'] as $exists_item) {
				$exists_ids[] = $exists_item['id'];
			}
			foreach ($exists_ids as $exists_id) {
				if (! in_array($exists_id, $update_values['item_ids'], TRUE))
					$del_ids[] = $exists_id;
			}
			foreach ($update_values['item_ids'] as $new_id) {
				if (! in_array($new_id, $exists_ids, TRUE))
					$add_ids[] = $exists_id;
			}

			$this->db->pureDelete('favorites_items', 'favorite_id=? and item_id in ('.implode(',', $del_ids).')', array($id));
			foreach ($add_ids as $add_id) {
				$ret = $this->db->insert(
					'favorites_items',
					array(
						'favorite_id' => $favorite['id'],
						'item_id' => $add_id,
						'user_id' => @$_SESSION['user']['id'],
						'created' => '^CURRENT_TIMESTAMP'
					)
				);
				if ($ret === FALSE) {
					$this->db->rollback();
					return FALSE;
				}
			}
		}

		if (! empty($update_values['add_id'])) {
			if ($this->isValidFavoriteIds(array($update_values['add_id'])) === FALSE) {
				$this->db->rollback();
				return FALSE;
			}
			$ret = $this->db->insert(
				'favorites_items',
				array(
					'favorite_id' => $favorite['id'],
					'item_id' => $update_values['add_id'],
					'user_id' => @$_SESSION['user']['id'],
					'created' => '^CURRENT_TIMESTAMP'
				)
			);
			if ($ret === FALSE) {
				$this->db->rollback();
				return FALSE;
			}
		}

		if (! empty($update_values['del_id'])) {
			$this->db->pureDelete('favorites_items', 'favorite_id=? and item_id=?', array($id, $update_values['del_id']));
		}

		$valid_update_values = array('modified' => '^CURRENT_TIMESTAMP');
		if (! empty($update_values['name'])) {
			$valid_update_values['name'] = $update_values['name'];
		}
		$ret = $this->db->update(
			'user_favorites',
			$valid_update_values,
			array('id' => $favorite['id'])
		);
		if ($ret === FALSE) {
			$this->db->rollback();
			return FALSE;
		}

		if (! empty($update_values['item_ids']) || !empty($update_values['del_id']) || !empty($update_values['add_id'])) {
			$ret = $this->updateFavoriteCache($favorite['id']);
			if ($ret === FALSE) {
				$this->db->rollback();
				return FALSE;
			}
		}

		$this->db->commit();

		return TRUE;
	}

	function updateFavoriteCache($id) {
		$favorite = $this->readFavorite($id);
		$cache = array();
		foreach ($favorite['items'] as $fi) {
			$item = array();
			$item['fi'] = $fi;
			switch($fi['target_type']) {
			case 'post':
				$item['t'] = $this->db->getTableName('posts');

				//投稿のお気に入りで投稿ツリーをとってくる場合のコード
				//$parent_id = $this->db->selectOne('posts', 't_id', array('id' => $fi['target_key']));
				//if (empty($parent_id))
				//	$parent_id = $fi['target_key'];
				//$item['w'] = 'p.id=? or p.t_id=?';
				//$item['p'] = array($parent_id, $parent_id);
				
				//熟考した結果、投稿単体のお気に入りで関連書き込みをとってくるべきではないということで、この形に修正した
				$item['w'] = 'p.id=?';
				$item['p'] = array($fi['target_key']);
			break;
			case 'my':
				$item['t'] = $this->db->getTableName('posts');
				$item['w'] = 'p.user_id='.$favorite['user_id'];
				$item['p'] = array();
			break;
			case 'user':
				$user_id = $this->db->selectOne('users', 'id', array('login_id' => $fi['target_key']));
				$item['t'] = $this->db->getTableName('posts');
				$item['w'] = 'p.anonymous=0 and p.user_id=?';
				$item['p'] = array($user_id);
			break;
			case 'theme':
				$item['t'] = $this->db->getTableName($this->getThemeTable($fi['target_key']));
				$item['w'] = 'p.theme=?';
				$item['p'] = array($fi['target_key']);
			break;
			case 'keyword':
				$item['t'] = $this->db->getTableName($this->getKeywordTable($fi['target_key']));
				$item['w'] = 'p.keyword=?';
				$item['p'] = array($fi['target_key']);
			break;
			case 'url':
				$item['t'] = $this->db->getTableName($this->getUrlTable($fi['target_key']));
				$item['w'] = 'p.url=?';
				$item['p'] = array($fi['target_key']);
			break;
			case 'trip':
				$item['t'] = $this->db->getTableName('posts');
				$item['w'] = 'p.anonymous=1 and p.trip=?';
				$item['p'] = array($fi['target_key']);
			break;
			case 'name':
				$item['t'] = $this->db->getTableName('posts');
				$item['w'] = 'p.display_name=?';
				$item['p'] = array($fi['target_key']);
			break;
			}
			$cache[] = $item;
		}
		return $this->db->update(
			'user_favorites',
			array('item_cache' => serialize($cache), 'modified' => '^CURRENT_TIMESTAMP'),
			array('id' => $id)
		);
	}

	function deleteFavoriteItem($id) {
		$linked_favorites = $this->db->selectList(
			//'^'.$this->db->getTableName('user_favorite_items').' ufi inner join '.$this->db->getTableName('favorites_items').' fi on ufi.id=fi.item_id',
			'favorites_items',
			array('favorite_id'),
			'item_id=?',
			//array('fi.favorite_id'),
			//'fi.item_id=?',
			array($id)
		);
		$item = $this->db->selectRow('user_favorite_items', NULL, array('id' => $id));
		if ($item['user_id'] != @$_SESSION['user']['id'])
			return FALSE;

		$ret = $this->db->delete('user_favorite_items', array('id' => $id));
		if ($ret === FALSE)
			return FALSE;

		if ($linked_favorites) {
			$this->db->delete('favorites_items', array('item_id' => $id));
			foreach ($linked_favorites as $linked_favorite_id) {
				$this->updateFavoriteCache($linked_favorite_id);
			}
		}

		return TRUE;
	}

	function deleteFavorite($id) {
		$favorite = $this->readFavoriteCache($id);
		if (@$_SESSION['user']['id'] != $favorite['user_id'])
			return FALSE;

		$ret = $this->db->delete('user_favorites', array('id' => $favorite['id']));
		if ($ret === FALSE)
			return FALSE;

		$this->db->delete('favorites_items', array('favorite_id' => $favorite['id']));
		return TRUE;
	}

	function _getTable($options) {
		if (! $options)
			return 'posts';

		if (array_key_exists('theme', $options))
			return $this->getThemeTable($options['theme']);

		if (array_key_exists('keyword', $options))
			return $this->getKeywordTable($options['keyword']);

		if (array_key_exists('url', $options))
			return $this->getUrlTable($options['url']);

		if (array_key_exists('favorite', $options))
			return $this->getFavoriteTable($options['favorite']);

		return 'posts';
	}

	function _getWhere($options, $base=NULL) {
		if (! $options)
			return $base;

		if (array_key_exists('theme', $options))
			return 'p.theme=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('keyword', $options))
			return 'p.keyword=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('url', $options))
			return 'p.url=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('name', $options))
			return 'p.display_name=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('trip', $options))
			return 'p.trip=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('user', $options))
			return 'p.user_id=? and p.anonymous=0'. ($base ? ' and '.$base : '');

		if (array_key_exists('my', $options))
			return 'p.user_id=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('pp', $options))
			return 'p.id=? or p.t_id=?'. ($base ? ' and '.$base : '');

		if (array_key_exists('favorite', $options))
			return 'p.favorite_id=?'. ($base ? ' and '.$base : '');

		return $base;
	}

	function _getParam($options, $base=array()) {
		if (! $options)
			return $base;

		if (array_key_exists('theme', $options))
			return array_merge(array($options['theme']), $base);

		if (array_key_exists('keyword', $options))
			return array_merge(array($options['keyword']), $base);

		if (array_key_exists('url', $options))
			return array_merge(array($options['url']), $base);

		if (array_key_exists('name', $options))
			return array_merge(array($options['name']), $base);

		if (array_key_exists('trip', $options))
			return array_merge(array($options['trip']), $base);

		if (array_key_exists('user', $options))
			return array_merge(array($options['user']), $base);

		if (array_key_exists('my', $options))
			return array_merge(array(@$_SESSION['user']['id']), $base);

		if (array_key_exists('pp', $options))
			return array_merge(array($options['pp'], $options['pp']), $base);

		if (array_key_exists('favorite', $options)) {
			//Note: Auto check permission
			$user_id = $this->db->selectOne(
				'user_favorites',
				'user_id',
				array('id' => $options['favorite'])
			);
			if ($user_id != @$_SESSION['user']['id'])
				return array_merge(array(-1), $base);
			return array_merge(array($options['favorite']), $base);
		}

		return $base;
	}

	function _getField($options) {
		return array(
			'p.id',
			'^p.display_name as n',
			'p.trip',
			'p.content',
			'p.t_theme',
			'p.t_id',
			'^p.json_theme as jt',
			'p.created',
			'^case p.anonymous when 1 then NULL else p.user_id end as user_id',
			'^case p.anonymous when 1 then NULL else u.login_id end as login_id',
			'^case p.anonymous when 1 then NULL else u.user_type_id end as t'
		);
	}

	function _getFrom($options) {
		return 
			'^'.$this->db->getTableName($this->_getTable($options)).
			' p left join '.
			$this->db->getTableName('users').
			' u on p.user_id=u.id'
		;
	}
}
?>