<?php

// make sure the database is at the requested revision
// performed whenever a module is upgraded or enabled

// To do: deleted field must be "is null" or have a default value
// to do: ignore current hash if table missing (so table will be created)
// to do: display errors when trying to alter table.

class MigrateException extends Exception {
	private $fieldname;
	
    // Redefine the exception so message isn't optional
    public function __construct($message, $code = 0, Exception $previous = null) {
        parent::__construct($message, $code, $previous);
    }

    public function Fieldname() {
		if (func_num_args() > 0) {
			$this->fieldname = func_get_arg(0);
		} else {
			return $this->fieldname;
		}
        
    }
}

class Upgrade_model extends Database_model {

	private $purge = FALSE;
	private $thelastsql;
	
	function purge() {		// if set to true, unused fields are dropped
		if (func_num_args > 0) {
			$this->purge = func_get_arg(0);
			return $this;
		} else {
			return $this->purge;
		}
	}

	public function __construct() {
		parent::__construct();
		$this->engine('mysql');
		$this->tablename('_migration');

		// create definitions table, if missing
		if (empty($this->_database_engine)) $this->_database_engine = GetEngine($this->engine());
		if (!$this->_database_engine->TableExists($this->tablename())) {
			$this->createMigrationTable();
		}
	}
	
	private function createMigrationTable() {
		$tablename = $this->tablename();
		$sql = <<<BLOCK
create table `{$tablename}` (
  `tablehash` varchar(40) COLLATE utf8_bin NOT NULL,
  `lasthash` varchar(40) COLLATE utf8_bin NOT NULL,
  `tablename` varchar(128) COLLATE utf8_bin NOT NULL,
  `definition` mediumtext COLLATE utf8_bin NOT NULL,
  primary key (`tablehash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='current table definitions';
BLOCK;
		$this->_database_engine->ExecuteRaw($sql);
		$this->dbErr();
	}
	
	// get desired table definitions
	// for each table:
	// 	calculate hash of definitions for table
	//	if new hash different from current hash:
	//		fetch existing definitions
	//		work out what has changed
	//			create sql for changes (fields may be dropped or renamed)
	//			execute sql
	//		save hash as current hash
	// to do: appropriate message when field missing from index

	public function TableMigration() {
		$output = '';
		$current_hashes = $this->getCurrentHashes();
		$desired = $this->GetDefinitions_Desired();
		foreach($desired as $tablename => $definition) {
			$output .= "Checking table $tablename ...\n";
			$temp_definition = $this->_sortDefinition($definition);
			$hash = $this->GenerateDesiredHash($temp_definition);
			$tablehash = sha1($tablename);
			$current_hash = isset($current_hashes[$tablehash]) ? $current_hashes[$tablehash] : '';
			if ($hash != $current_hash) {
				$output .= "   ... applying database changes\n";
				$current = $this->GetDefinitions_Current($tablename);
				try {
					$res = $this->ApplyDifferences($tablename, $current, $definition);
				} catch(MigrateException $e) {
                    error_log('Migration error: ' . $e->getMessage());
					$output .= '   Error: ' . $e->getMessage() . ' [field "' . $e->fieldname() . "\"]\n";
					$res = FALSE;
				}
				if ($res) {
					 $this->tablename('_migration')->InsertOrUpdate([
						'tablehash' => $tablehash,
					],[
						'lasthash' => $hash,
						'tablename' => $tablename,
						'definition' => serialize($definition),
					]);
				} else {
					$output .= "  {$this->thelastsql}\n  {$this->_database_engine->errmsg}\n";
				}
			} else {
				$output .= "   ... no updates required\n";
			}
			$status = $this->_isEmptyTable($tablename);
			if (is_null($status)) {
				die("<pre>$output</pre><br>Unable to create the table");
			} elseif ($status) {
				$output .= $this->PopulateTable($tablename, $definition);
			}
		}
		return $output;
	}
	
	private function _isEmptyTable($tablename) {
		$data = $this->Query("select * from `$tablename`")->limit(1)->first();
		if ($this->_database_engine->errnum == 1146) return NULL;
		$this->dbErr();
		return $data->Empty();
	}
	
	private function getCurrentHashes() {
		$result = [];
		// tablename is a hash
		$data = $this->Query('select tablehash,lasthash from _migration')->get();
		foreach($data as $data_item) {
			$result[$data_item->tablehash] = $data_item->lasthash;
		}
		return $result;
		
	}

	// desired definitions are stored in index.php for each module
	private function GetDefinitions_Desired() {
		$result = [];
		$modulelist = $GLOBALS['loader']->getModuleList();
		foreach($modulelist as $module_info) {
			if (isset($module_info['.info']['database'])) {
				$node = &$module_info['.info']['database'];
				foreach($node as $tablename => $tabledefinition) {
					$this->_mergeTableDefinition($result, $tablename, 'fields', $tabledefinition['fields']);
					$this->_mergeTableDefinition($result, $tablename, 'index', $tabledefinition['index']);
					if (isset($tabledefinition['populate'])) {
						if (!isset($result[$tablename]['populate'])) $result[$tablename]['populate'] = [];
						$result[$tablename]['populate'][] = $tabledefinition['populate'];
					}
				}
			}
		}
		return $result;
	}
	
	private function _mergeTableDefinition(&$result, $tablename, $entryname, $definitions) {
		if (!isset($result[$tablename][$entryname])) {
			$result[$tablename][$entryname] = [];
		}
		foreach($definitions as $fieldname => $definition) {
			if (is_int($fieldname)) {
				$fieldname = $definition['name'];
			} else {
				$definition['name'] = $fieldname;
			}
			$result[$tablename][$entryname][$fieldname] = $definition;
		}
	}
	
	private function GenerateDesiredHash($definition) {
		$hash = sha1(var_export($definition, 1));
		return $hash;
	}

	private function _sortDefinition(&$definition) {
		foreach($definition['fields'] as $name => &$field_def) {
			if (!array_key_exists('null', $field_def)) $field_def['null'] = TRUE;
		}
		if (isset($definition['index']['PRIMARY'])) {
			$fieldnames = $definition['index']['PRIMARY']['fields'];
			foreach($fieldnames as $fieldname) {
				$definition['fields'][$fieldname]['null'] = FALSE;			// fields in the primary index cannot be null
			}
		}
		$temp_definition = $definition;
		ksort($temp_definition['index']);
		ksort($temp_definition['fields']);
		//to do: handle encrypted flag
		//to do: add default values for missing fields
		//   new column is NULLable by default
		return $temp_definition;
	}
	
	// get the current table definition, (fields and index)
	private function GetDefinitions_Current($tablename) {
		return $this->_database_engine->GetTableDefinition($tablename);
	}
	
	// returns TRUE if all ok
	private function ApplyDifferences($tablename, $current, $definition) {
        $isNew = !count($current['fields']);
			// Create table and exit
        
        // a table - generic format
        $result = [];   
        foreach($definition['fields'] as $fieldname => $fielddef) {
            $node = $this->_generateFieldDefinition($fieldname, $fielddef, $isNew, $current['fields']);
			if ($node === FALSE) {
				continue;		// unchanged 
			} elseif ($node !== NULL) {
				$result[] = $node;
			} else {
				echo "unchanged field:  $tablename/$fieldname\n";
			}
        }
		if ($this->purge) {	// fields that are no longer required will be dropped
			foreach($current['fields'] as $fieldname => $fielddef) {
				$result[] = ['type' => 'dropcolumn', 'name' => $fieldname];
			}
		}
		
		$indexes = [];
		foreach($current['index'] as $keyname => $indexdef) {
			$indexes[strtolower($keyname)] = $indexdef;
		}
        foreach($definition['index'] as $fieldname => $indexdef) {
            $node = $this->_generateIndexDefinition($fieldname, $indexdef, $isNew, $indexes);
			if ($node === FALSE) {
				continue;		// unchanged 
            } elseif ($node !== NULL) {
				$result[] = $node;
			} else {
				echo "unchanged index:  $tablename/$fieldname\n";
			}
        }
		if (count($result)) {
			$res = $this->_database_engine->SetTableDefinition($tablename, $result, $isNew);
			$this->thelastsql = $this->_database_engine->lastsql; //$this->lastsql;
		} else {
			$res = TRUE;
		}
		return $res;
	}

	// do to: default values
	// these should be classes/objects
	private function _asInternalFormat($fieldname, $fielddef, $isExisting = FALSE) {
        $default = array_key_exists('default', $fielddef) ? $fielddef['default'] : NULL;
        $opt = NULL;
		switch(strtolower($fielddef['type'])) {
			case 'string':
				if (empty($fielddef['encrypted'])) {
					$type = 'varchar(' . $fielddef['size'] . ')';
				} else {
					$type = 'varchar(1025)';
				}
				break;
			case 'integer':
			case 'int':
				$size = $fielddef['size'];
				if (empty($size)) $size = 11;
                $type = "int($size)";
				if (!empty($fielddef['unsigned'])) $type .= ' unsigned';
				if (!empty($fielddef['autoinc'])) $type .= ' auto_increment';
				break;

			case 'bigint':
				$size = $fielddef['size'];
				if (empty($size)) $size = 11;
                $type = "bigint($size)";
				if (!empty($fielddef['unsigned'])) $type .= ' unsigned';
				if (!empty($fielddef['autoinc'])) $type .= ' auto_increment';
				break;
                
			case 'float':
			case 'double':
                $type = 'double';
                break;
            
			case 'time':
				$type = 'timestamp';
				break;
			case 'text':
				$type = 'mediumtext';
				break;
			case 'blob':
				$type = 'longblob';
				break;
			case 'datetime':
				$type = 'datetime';
				break;
			case 'object':			// serialized text
				$type = 'mediumtext';
				break;
			case 'boolean':
				$type = 'tinyint(1)';
				break;
            case 'enum':
                foreach($fielddef['size'] as &$item) {
                    $item = "\"{$item}\"";
                }
                $type = 'enum(' . join(',', $fielddef['size']) . ')';
                break;
		// date, tinyint, medium text, etc
			default:
				$e = new MigrateException('Unrecognized field type.');
				$e->fieldname($fieldname);
				throw $e;
		}
		if (!is_null($default) && is_numeric($default)) $default = "$default";
        $null = array_key_exists('null', $fielddef) ? $fielddef['null'] : NULL;
		return [
            'name' => $fieldname, 
            'old' => $oldfield, 
            'def' => $type, 
            'default' => $default,
            'null' => $null,
            'exist' => $isExisting,
            'comment' => $fielddef['comment'] ?? '',
            'type' => 'column',
        ];
        return $node;
	}
	
    private function _generateFieldDefinition($fieldname, $fielddef, $isNew, &$current) {
        $oldfield = NULL;
		$old_format = NULL;
		$isExisting = FALSE;
        if (!$isNew) {      // if a table is missing, we are not renaming a field
            if (isset($fielddef['oldfield'])) {     // are we renaming an old field
                $oldfield = $this->_isRenamingOldField($current, $fielddef['oldfield']);
				if ($oldfield !== NULL) {
					unset($current[$oldfield]);
				}
            } elseif (isset($current[$fieldname])) {		// field already defined?  skip it
				// make sure definition is unchanged
				$isExisting = TRUE;
				$old_format = $this->_asInternalFormat($fieldname, $current[$fieldname]);
				unset($current[$fieldname]);
			}
        }
		$node = $this->_asInternalFormat($fieldname, $fielddef, $isExisting);
		if ($old_format !== NULL && $this->isSameFormat($old_format, $node)) {
			return FALSE;		// definition is unchanged. 
		} else {
//		echo "\nDifferent ---old----------------------new-----------\n";
//		var_dump($old_format, $node);
//		echo "\n\n";
		}
		
        return $node;
    }

    private function _generateIndexDefinition($fieldname, $indexdef, $isNew, $current) {
		$fieldname = strtolower($fieldname);
        if (isset($current[$fieldname])) {
            $old_format = $this->_asInternalFormat_index($fieldname, $current[$fieldname]);
        } else {
            $old_format = NULL;
        }
		$node = $this->_asInternalFormat_index($fieldname, $indexdef, $old_format !== NULL);
		if ($old_format !== NULL && $this->isSameFormat($old_format, $node)) return FALSE;		// definition is unchanged. 
        return $node;
    }
    
    // to do: add hash and keyword search index types
    private function _asInternalFormat_index($fieldname, $indexdef, $isExisting = FALSE) {
        $node = [
            'type'    => 'index',
            'subtype' => $indexdef['type'],
            'name'    => $fieldname,  // name of index
            'exist'   => $isExisting,
            'fields'  => join(',', $indexdef['fields']),
        ];
        return $node;
    }
	
	private function isSameFormat($current_definition, $new_definition) {
        if ($current_definition['type'] == 'column') {
            $cd_comment = $current_definition['comment'] ?? '';
            $nd_comment = $new_definition['comment'] ?? '';
            return ($current_definition['def'] == $new_definition['def']) 
					&& ($current_definition['default'] === $new_definition['default'])
					&& ($cd_comment === $nd_comment)
					&& ($current_definition['null'] === $new_definition['null']);
		} elseif ($current_definition['type'] == 'index') {
            return ($current_definition['subtype'] == $new_definition['subtype']) 
					&& ($current_definition['fields'] === $new_definition['fields']);
        } else {
            return FALSE;
        }
	}

    private function _isRenamingOldField($current, $oldfields) {
        if (!is_array($oldfields)) $oldfields = [$oldfields];
        foreach($oldfields as $fieldname) {
            if (isset($current[$fieldname])) return $fieldname;
        }
        return NULL;
    }
	
	protected function PopulateTable($tablename, $definition) {
		$output = '';
		if (isset($definition['populate'])) {
			$output .= "   ... populating table\n";
			foreach($definition['populate'] as $entrypoint) {
				$output .= $GLOBALS['loader']->LoadAndExecuteFunction($entrypoint);
			}
		}
		return $output;
	}

	
}