<?php

require_once( dirname( __FILE__ ) . '/config.php' );

require_once( dirname( __FILE__ ) . '/revisions-cache.php' );

/**
 * Class which deals with revisions database
 *
 * @package lp-update
 *
 */
class RevisionsData {
	
	private $dbuser;
	private $dbpassword;
	private $dbname;
	private $dbhost;
	private $collate;
	private $charset;
	private $dbh;
	private $ready;
	private $real_escape = false;
	private $installed;
	
	/**
	 * Constructor of the class
	 *
	 * @param string $dbuser database username
	 * @param string $dbpassword database password
	 * @param string $dbname name of database
	 * @param string $dbhost database host
	 * @return none
	 */
	public function RevisionsData( $dbuser, $dbpassword, $dbname, $dbhost ) {
        $this->__construct( $dbuser, $dbpassword, $dbname, $dbhost );
    }
    
    /**
	 * Constructor of the class
	 *
	 * @param string $dbuser database username
	 * @param string $dbpassword database password
	 * @param string $dbname name of database
	 * @param string $dbhost database host
	 * @return none
	 */
    public function __construct( $dbuser, $dbpassword, $dbname, $dbhost ) {
    	register_shutdown_function( array( &$this, '__destruct' ) );

		$this->init_charset();

		$this->dbuser = $dbuser;
		$this->dbpassword = $dbpassword;
		$this->dbname = $dbname;
		$this->dbhost = $dbhost;

		$this->db_connect();
		
		$this->installed = false;
    }
    
    /**
	 * Destructor of the class
	 *
	 * @return bool success
	 */
    public function __destruct() {
		return true;
	}
	
	/**
	 * Function which initializes database charset
	 *
	 * @return none
	 */
	private function init_charset() {
		if ( defined( 'DB_COLLATE' ) ) {
			$this->collate = DB_COLLATE;
		}

		if ( defined( 'DB_CHARSET' ) )
			$this->charset = DB_CHARSET;
	}
    
    /**
	 * Function which connects to database
	 *
	 * @return none
	 */
    private function db_connect() {
    	$this->dbh = mysql_connect( $this->dbhost, $this->dbuser, $this->dbpassword, true );
    	
    	if ( !$this->dbh ) {
    		return;
    	}
    	
    	$this->set_charset( $this->dbh );

		$this->ready = true;

		$this->select( $this->dbname, $this->dbh );
    }
    
    /**
	 * Function which sets database charset
	 *
	 * @param object $dbh database host
	 * @param string $charset database charset
	 * @param string $collate database collation
	 * @return none
	 */
    private function set_charset( $dbh, $charset = null, $collate = null ) {
		if ( ! isset( $charset ) )
			$charset = $this->charset;
		if ( ! isset( $collate ) )
			$collate = $this->collate;
		if ( ! empty( $charset ) ) {
			if ( function_exists( 'mysql_set_charset' ) ) {
				mysql_set_charset( $charset, $dbh );
				$this->real_escape = true;
			} else {
				$query = $this->prepare( 'SET NAMES %s', $charset );
				if ( ! empty( $collate ) )
					$query .= $this->prepare( ' COLLATE %s', $collate );
				mysql_query( $query, $dbh );
			}
		}
	}
    
    /**
	 * Function which selects database
	 *
	 * @param string $db database name
	 * @param object $dbh database host
	 * @return none
	 */
    private function select( $db, $dbh = null ) {
		if ( is_null( $dbh ) )
			$dbh = $this->dbh;

		if ( ! @mysql_select_db( $db, $dbh ) ) {
			$this->ready = false;
			return;
		}
	}
	
	/**
	 * Function which escapes database string
	 *
	 * @param string $string string to escape
	 * @return string escaped string
	 */
	private function _real_escape( $string ) {
		if ( $this->dbh && $this->real_escape )
			return mysql_real_escape_string( $string, $this->dbh );
		else
			return addslashes( $string );
	}
	
	/**
	 * Function which escapes database string by reference
	 *
	 * @param string $string string to escape
	 * @return none
	 */
	private function escape_by_ref( &$string ) {
		$string = $this->_real_escape( $string );
	}
	
	/**
	 * Function which chechs if tables exist
	 *
	 * @return bool exists or not
	 */
	public function check_tables() {
		if ( !$this->ready )
			return false;
		
		$queries = array (
			"SELECT COUNT(*) FROM entities;",
			"SELECT COUNT(*) FROM revisions;"
		);
		
		$check = true;
		foreach ( $queries as $query ) {
			$result = @mysql_query( $query, $this->dbh );
			if ( $row = @mysql_fetch_array( $result ) ) {
				$check = $check && ( $row[0] > 0 );
			} else
				$check = false;
		}
		
		return $check;
	}
	
	/**
	 * Function which creates tables
	 *
	 * @return none
	 */
	public function create_tables() {
		if ( !$this->ready )
			return false;
		
		$queries = array (
			"DROP TABLE IF EXISTS revisions;",
			"CREATE TABLE revisions ( " .
			"id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, " .
			"eid BIGINT(20) NOT NULL, " .
			"version VARCHAR(20) NOT NULL, " .
			"locale VARCHAR(10) NOT NULL, " .
			"revision BIGINT(20) NOT NULL, " .
			"percent INT NOT NULL " .
			") ENGINE = MYISAM DEFAULT CHARSET={$this->charset} AUTO_INCREMENT=1;",
			"DROP TABLE IF EXISTS entities;",
			"CREATE TABLE entities ( " .
			"id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, " .
			"type VARCHAR(10) NOT NULL, " .
			"slug TEXT NOT NULL, " .
			"name TEXT NOT NULL, " .
			"textdomain TEXT NOT NULL " .
			") ENGINE = MYISAM DEFAULT CHARSET={$this->charset} AUTO_INCREMENT=1;" 
		);
		
		foreach ( $queries as $query )
			$result = @mysql_query( $query, $this->dbh );
	}
	
	/**
	 * Function which populates tables
	 *
	 * @return none
	 */
	public function populate_tables() {
		$entities = file_get_contents( './entities.csv', true );
		$entities = explode( "\n", $entities );
		
		foreach ( $entities as $i => $entity ) {
			$entities[$i] = explode( ",", $entity );
			if ( count( $entities[$i] ) < 4 )
				unset( $entities[$i] );
			else
				$this->insert_record_entity( $entities[$i][0], $entities[$i][1], $entities[$i][2], $entities[$i][3] );
		}
		
		$revisions = file_get_contents( './revisions.csv', true );
		$revisions = explode( "\n", $revisions );
		
		foreach ( $revisions as $i => $revision ) {
			$revisions[$i] = explode( ",", $revision );
			if ( count( $revisions[$i] ) < 5 )
				unset( $revisions[$i] );
			else
				$this->insert_record_revision( $revisions[$i][0], $revisions[$i][1], $revisions[$i][2], $revisions[$i][3], $revisions[$i][4] );
		}
	}
	
	/**
	 * Function which installs database tables
	 *
	 * @return none
	 */
	public function install() {
		if ( ! $this->installed ) {
			$check = $this->check_tables();
			
			if ( ! $check ) {
				$this->create_tables();
				$this->populate_tables();
				$this->installed = true;
			} else {
				$this->installed = false;
			}
		}
	}
	
	/**
	 * Function which inserts record entity
	 *
	 * @param string $type plugin, theme or core
	 * @param string $slug plugin or theme slug
	 * @param string $name plugin or theme name
	 * @param string $textdomain plugin or theme textdomain
	 * @return bool success
	 */
	public function insert_record_entity( $type, $slug, $name, $textdomain ) {
		if ( !$this->ready )
			return false;
		
		$this->escape_by_ref( $type );
		$this->escape_by_ref( $slug );
		$this->escape_by_ref( $name );
		$this->escape_by_ref( $textdomain );
		
		$query = "INSERT INTO entities(type, slug, name, textdomain) VALUES (" . 
			"'" . $type . "', " .
			"'" . $slug . "', " .
			"'" . $name . "', " .
			"'" . $textdomain . "');";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( 1 == mysql_affected_rows( $this->dbh ) ) {
			return true;
		}
		return false;
	}
	
	/**
	 * Function which inserts record revision
	 *
	 * @param int $eid entity id
	 * @param string $version plugin, theme or core version
	 * @param string $locale locale of this translation
	 * @param int $revision revision of translation
	 * @param int $percent percent of translation
	 * @return bool success
	 */
	public function insert_record_revision( $eid, $version, $locale, $revision, $percent ) {
		if ( !$this->ready )
			return false;
		
		global $revisions_cache;
		$this->escape_by_ref( $eid );
		$this->escape_by_ref( $version );
		$this->escape_by_ref( $locale );
		$this->escape_by_ref( $revision );
		$this->escape_by_ref( $percent );
		
		$query = "INSERT INTO revisions(eid, version, locale, revision, percent) VALUES (" . 
			"" . $eid . ", " .
			"'" . $version . "', " .
			"'" . $locale . "', " .
			"" . $revision . ", " .
			"" . $percent . ");";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( 1 == mysql_affected_rows( $this->dbh ) ) {
			
			$query = "SELECT type, slug, name, textdomain FROM entities " . 
				"WHERE id = " . $eid . ";";
			
			$result = @mysql_query( $query, $this->dbh );
			
			if ( $row = @mysql_fetch_array( $result ) ) {
				
				$type = $row['type'];
				$slug = $row['slug'];
				$name = $row['name'];
				$textdomain = $row['textdomain'];
		
				if ( 1 == mysql_affected_rows( $this->dbh ) ) {
					if ( ! $revisions_cache->update_revision( $type, $slug, $name, $textdomain, $version, $locale, $revision, $percent ) ) {
						$revisions_cache->insert_revision( $type, $slug, $name, $textdomain, $version, $locale, $revision, $percent );
					}
					return true;
				}
				
			}
			
		}
		return false;
	}
	
	/**
	 * Function which updates record entity
	 *
	 * @param int $id entity id
	 * @param string $type plugin, theme or core
	 * @param string $slug plugin or theme slug
	 * @param string $name plugin or theme name
	 * @param string $textdomain plugin or theme textdomain
	 * @return bool success
	 */
	public function update_record_entity( $id, $type, $slug, $name, $textdomain ) {
		if ( !$this->ready )
			return false;
		
		global $revisions_cache;
		$this->escape_by_ref( $id );
		$this->escape_by_ref( $type );
		$this->escape_by_ref( $slug );
		$this->escape_by_ref( $name );
		$this->escape_by_ref( $textdomain );
		
		$query = "UPDATE entities SET " . 
			"type = '" . $type . "', " .
			"slug = '" . $slug . "', " .
			"name = '" . $name . "' " .
			"textdomain = '" . $textdomain . "' " .
			"WHERE id = " . $id . ";";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( 1 == mysql_affected_rows( $this->dbh ) ) {
			
			$query = "SELECT version, locale, revision, percent FROM revisions WHERE " .
				"eid = " . $eid . " GROUP BY version, locale;";
			
			$result = @mysql_query( $query, $this->dbh );
			
			while ( $row = @mysql_fetch_array( $result ) ) {
				
				$version = $row['version'];
				$locale = $row['locale'];
				$revision = $row['revision'];
				$percent = $row['percent'];
				
				$last = get_last_revision( $type, $slug, $version, $locale );
				
				if ( $last['percent'] < $percent ) {
					$revisions_cache->update_revision( $type, $slug, $name, $textdomain, $version, $locale, $revision, $percent );
				}
				
			}
			
			return true;
		}
		return false;
	}
	
	/**
	 * Function which updates record revision
	 *
	 * @param $id revision id
	 * @param $eid entity id
	 * @param string $version plugin, theme or core version
	 * @param string $locale locale of this translation
	 * @param int $revision revision of translation
	 * @param int $percent percent of translation
	 * @return bool success
	 */
	public function update_record_revision( $id, $eid, $version, $locale, $revision, $percent ) {
		if ( !$this->ready )
			return false;
		
		global $revisions_cache;
		$this->escape_by_ref( $id );
		$this->escape_by_ref( $eid );
		$this->escape_by_ref( $version );
		$this->escape_by_ref( $locale );
		$this->escape_by_ref( $revision );
		$this->escape_by_ref( $percent );
		
		$query = "UPDATE revisions SET " . 
			"eid = " . $eid . ", " .
			"version = '" . $version . "', " .
			"locale = '" . $locale . "', " .
			"revision = " . $revision . ", " .
			"percent = " . $percent . " " .
			"WHERE id = " . $id . ";";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( 1 == mysql_affected_rows( $this->dbh ) ) {
		
			$query = "SELECT type, slug, name, textdomain FROM entities " . 
				"WHERE id = " . $eid . ";";
			
			$result = @mysql_query( $query, $this->dbh );
			
			if ( $row = @mysql_fetch_array( $result ) ) {
				
				$type = $row['type'];
				$slug = $row['slug'];
				$name = $row['name'];
				$textdomain = $row['textdomain'];
				
				$revisions_cache->update_revision( $type, $slug, $name, $textdomain, $version, $locale, $revision, $percent );
				return true;
			}
		}
		return false;
	}
	
	/**
	 * Function which deletes record entity
	 *
	 * @param int $id entity id
	 * @return bool success
	 */
	public function delete_record_entity( $id ) {
		if ( !$this->ready )
			return false;
		
		global $revisions_cache;
		$this->escape_by_ref( $id );
		
		$query = "SELECT type, slug FROM entities " . 
			"WHERE id = " . $id . ";";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( $row = @mysql_fetch_array( $result ) ) {
			
			$type = $row['type'];
			$slug = $row['slug'];
		
			$query = "DELETE FROM entities WHERE " . 
				"id = " . $id . ";";
		
			$result = @mysql_query( $query, $this->dbh );
		
			if ( 1 == mysql_affected_rows( $this->dbh ) ) {
				
				$query = "SELECT id FROM entities WHERE " .
					"slug = '" . $slug . "';";				
				
				$result = @mysql_query( $query, $this->dbh );				
				
				if ( 1 == mysql_affected_rows( $this->dbh ) ) {
					
					$eid = $row['id'];
					
					$query = "SELECT id, version, locale FROM revisions WHERE " .
						"eid = " . $eid . " GROUP BY version, locale;";
					
					$result = @mysql_query( $query, $this->dbh );
					
					while ( $row = @mysql_fetch_array( $result ) ) {
						
						$rid = $row['id'];
						$version = $row['version'];
						$locale = $row['locale'];
					
						$query = "DELETE FROM revisions WHERE " . 
							"id = " . $rid . ";";
						
						$result = @mysql_query( $query, $this->dbh );
				
						if ( 1 == mysql_affected_rows( $this->dbh ) ) {
							$revisions_cache->delete_revision( $type, $slug, $version, $locale );
						}
						
					}
					
					return true;
					
				}
			}
		
		}
		return false;
	}
	
	/**
	 * Function which deletes record revision
	 *
	 * @param int $id revision id
	 * @return bool success
	 */
	public function delete_record_revision( $id ) {
		if ( !$this->ready )
			return false;
		
		global $revisions_cache;
		$this->escape_by_ref( $id );
		
		$query = "SELECT eid, version, locale FROM revisions " . 
			"WHERE id = " . $id . ";";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( $row = @mysql_fetch_array( $result ) ) {
			
			$eid = $row['eid'];
			$version = $row['version'];
			$locale = $row['locale'];
		
			$query = "DELETE FROM revisions WHERE " . 
				"id = " . $id . ";";
		
			$result = @mysql_query( $query, $this->dbh );
		
			if ( 1 == mysql_affected_rows( $this->dbh ) ) {
				$query = "SELECT MAX(revision), percent FROM revisions WHERE " . 
					"eid = " . $eid . " AND " .
					"version = '" . $version . "' AND " .
					"locale = '" . $locale . "' GROUP BY eid, version, locale;";
				
				$result = @mysql_query( $query, $this->dbh );
				
				if ( $row = @mysql_fetch_array( $result ) ) {
					
					$query = "SELECT type, slug, name, textdomain FROM entities WHERE " .
						"id = " . $eid . ";";
					
					$result = @mysql_query( $query, $this->dbh );
					
					if ( $row = @mysql_fetch_array( $result ) ) {
						
						$type = $row['type'];
						$slug = $row['slug'];
						$name = $row['name'];
						$textdomain = $row['textdomain'];
										
						$revisions_cache->delete_revision( $type, $slug, $version, $locale );
						
						$revision = $row['revision'];
						$percent = $row['percent'];
						
						$revisions_cache->insert_revision( $type, $slug, $name, $textdomain, $version, $locale, $revision, $percent );
						return true;
					}
				} else {
					$revisions_cache->delete_revision( $type, $slug, $version, $locale );
					return true;
				}
			}
		
		}
		return false;
	}
	
	/**
	 * Function which returns last revision for the given parameters
	 *
	 * @param string $type plugin, theme or core
	 * @param string $slug plugin or theme slug
	 * @param string $version plugin, theme or core version
	 * @param string $locale locale of this translation
	 * @return array last revision for the given parameters
	 */
	public function get_last_revision( $type, $slug, $version, $locale ) {
		if ( !$this->ready )
			return false;
		
		$output["type"] = $type;
		$output["slug"] = $slug;
		$output["version"] = $version;
		$output["locale"] = $locale;
		
		$this->escape_by_ref( $type );
		$this->escape_by_ref( $slug );
		$this->escape_by_ref( $version );
		$this->escape_by_ref( $locale );
		
		$query = "SELECT id, name, textdomain FROM entities WHERE" . 
			"type = '" . $type . "' AND " .
			"slug = '" . $slug . "';";
		
		$result = @mysql_query( $query, $this->dbh );
		
		if ( $row = @mysql_fetch_array( $result ) ) {
			
			$eid = $row["id"];
			$output["name"] = $row["name"];
			$output["textdomain"] = $row["textdomain"];
		
			$query = "SELECT eid, version, locale, MAX(revision), percent FROM revisions WHERE " . 
				"eid = " . $eid . " AND " .
				"version = '" . $version . "' AND " .
				"locale = '" . $locale . "' GROUP BY eid, version, locale;";
		
			$result = @mysql_query( $query, $this->dbh );
		
			if ( $row = @mysql_fetch_array( $result ) ) {
				$output["revision"] = $row["revision"];
				$output["percent"] = $row["percent"];
			
				return $output;
			}
			
		}
		
		return NULL;
	}
	
	/**
	 * Function which returns last revisions for all plugins and themes
	 *
	 * @return array last revisions for all plugins and themes
	 */
	public function get_all_last_revisions() {
		if ( ! $this->ready )
			return false;
		
		$query = "SELECT r.eid, e.type, e.slug, e.name, e.textdomain, r.version, r.locale, r.percent, MAX(r.revision) FROM revisions r LEFT JOIN entities e ON (e.id = r.eid) GROUP BY r.eid, r.version, r.locale;";
		
		$result = @mysql_query( $query, $this->dbh );
		
		$output = NULL;
		
		while ( $row = @mysql_fetch_array( $result ) ) {
			
			if ( 'core' == $row['type'] ) {
				$output[$row['type']][$row['version']][$row['locale']]['name'] = $row['name'];
				$output[$row['type']][$row['version']][$row['locale']]['revision'] = $row['revision'];
				$output[$row['type']][$row['version']][$row['locale']]['percent'] = $row['percent'];
			} else if ( 'plugin' == $row['type'] || 'theme' == $row['type'] ) {
				$output[$row['type']][$row['slug']][$row['version']][$row['locale']]['name'] = $row['name'];
				$output[$row['type']][$row['slug']][$row['version']][$row['locale']]['textdomain'] = $row['textdomain'];
				$output[$row['type']][$row['slug']][$row['version']][$row['locale']]['revision'] = $row['MAX(r.revision)'];
				$output[$row['type']][$row['slug']][$row['version']][$row['locale']]['percent'] = $row['percent'];
			}
			
		}
		
		return $output;
	}
}

?>