<?php

class database {

	static PDO $connection;

	static string $nullValue='NULL';
	static int $lastAffectedRowCount;
	/**
	 * Connect to the database using credentials supplied in the config file.
	 */
	public static function connect(): void {
		try {
			$conf=parse_ini_file(realpath(__DIR__).'/../../conf/config.ini');
			if(!$conf){ die('Could not read database connection details from config file'); }
			$db = new PDO('mysql:host='.$conf['dbHost'].';dbname='.$conf['dbName'], $conf['dbUser'], $conf['dbPass'], array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
			$conf=null; //for security, do not keep the DB credentials
			$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			self::$connection=$db;
			self::$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		} catch (PDOException) {
			die("Could not connect to database");
		}
	}
	
	/**
	 * Begin a database transaction.
     */
	public static function begin(): void {
		if(!self::$connection){ self::connect(); }
		if(!self::$connection->inTransaction()){ self::$connection->beginTransaction(); }
	}
	/**
	 * Commit a database transaction.
     */
	public static function commit(): void {
		if(self::$connection->inTransaction()){ self::$connection->commit(); }
	}
	/**
	 * Abort a database transaction.
     */
	public static function abort(): void {
		if(self::$connection->inTransaction()){ self::$connection->rollBack(); }
	}
	
	/**
	 * After a SELECT, return the number of rows that would have matched, regardless of the LIMIT clause.
	 * For MYSQL, the query must begin SELECT SQL_CALC_FOUND_ROWS.
     * @throws ServerException
     * @throws BadRequestException
	 */
	public static function getFoundRows(): int {
		$result=self::doQuery('SELECT FOUND_ROWS()', array(), 'one');
		if(!$result){ return 0; }
		return (int)$result['FOUND_ROWS()'];
	}
	
	public static function getAffectedRows(): int {
		return (int)self::$lastAffectedRowCount;
	}

	/**
	 * @param string $sqlStatement
	 * @param array $parameters
	 * @param string|null $returnType
	 * @return array|int|null
	 * @throws BadRequestException
	 * @throws ServerException
	 */
    private static function doQuery(string $sqlStatement, array $parameters, string $returnType=null): mixed {
	    try {
	        //Original - cant handle null
	        foreach($parameters as $k=>$v){
				if(is_bool($v)){
					$parameters[$k]=(int)$v;
				} else if(strtoupper($v)==database::$nullValue){
				    $parameters[$k]=null;
				}
			}
			$stmt=self::$connection->prepare($sqlStatement);
			$stmt->execute($parameters);
			static::$lastAffectedRowCount=$stmt->rowCount();
			
			if('one'==$returnType){
				$result=$stmt->fetch();
				if(!$result){return null; }
				$keys=array_keys($result);
				foreach($keys as $k){
					if(is_int($k)){
						unset($result[$k]);
					}
				}
				return $result;
			} else if('all'==$returnType){
				$result=$stmt->fetchAll();
				if(!$result){return null; }
				foreach ($result as &$row){ //by reference
					$keys=array_keys($row);
					foreach($keys as $k){
						if(is_int($k)){
							unset($row[$k]);
						}
					}
				}
				return $result;
			} else {
				return $stmt->rowCount();
			}
		} catch (PDOException $e) {
			self::handlePDOException($e, $sqlStatement, $parameters);
		}
	}

    /**
     * Prepares and executes a PDO statement using the supplied SQL and parameters, and returns the number of affected rows.
     * Note that a row "updated" with the same values counts toward the total affected rows.
     * @param string $sqlStatement The SQL statement, with placeholders
     * @param array $parameters The array of placeholders and values
     * @return array|int|null The number of rows affected by the query.
     * @throws BadRequestException
     * @throws ServerException
     */
	public static function query(string $sqlStatement, array $parameters=[]): mixed {
		return self::doQuery($sqlStatement, $parameters, null);
	}

	/**
	 * Prepares and executes a PDO statement using the supplied SQL and parameters, and returns an array representing a single row.
	 * @param string $sqlStatement The SQL statement, with placeholders
	 * @param array $parameters The array of placeholders and values
	 * @return array|null The first match.
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function queryGetOne(string $sqlStatement, array $parameters=[]): ?array {
		return self::doQuery($sqlStatement, $parameters, 'one');
	}

	/**
	 * Prepares and executes a PDO statement using the supplied SQL and parameters, and returns an array containing two keys: "total" and "rows".
	 * "total" is the number of rows that would have been matched, ignoring any LIMIT clause.
	 * "rows" contains the actual data, one item per result row, as key-value pairs.
	 * @param string $sqlStatement The SQL statement, with placeholders
	 * @param array $parameters The array of placeholders and values
	 * @return array|null The results, along with the total rows matched
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function queryGetAll(string $sqlStatement, array $parameters=[]): ?array {
		$result=self::doQuery($sqlStatement, $parameters, 'all');
		if(!$result){ return null; }
		$total=self::getFoundRows();
        return array(
                'total'=>$total,
                'rows'=>$result
        );
	}

    /**
     * @return mixed
     */
	public static function getLastInsertId(): mixed {
		return self::$connection->lastInsertId();
	}

    /**
     * @param Exception $e
     * @param string $sqlStatement
     * @param array $parameters
     * @throws BadRequestException
     * @throws ServerException
     */
	private static function handlePDOException(Exception $e, string $sqlStatement, array $parameters){
		$message=$e->getMessage();
		database::abort();
		if(str_contains($message, 'constraint violation')){
			throw new BadRequestException('Item may already exist, try another name '.$message);
		} else {
 			if(!session::isAdmin()){
 				$message='Database error, could not continue';
 			} else {
				$message.="\nStatement: $sqlStatement\nParameters:";
				foreach($parameters as $k=>$v){
					$message.=' '.$k.'='.$v;
				}
 			}
			throw new ServerException($message);
		}
	}

	/**
	 * Returns a LIMIT clause for pagination, using $request['pagenumber'] and $request['pagesize'].
	 * If either is missing, or if $request['all'] is present, returns an empty string.
	 * @param array $requestParameters
	 * @return string the Limit clause if both parameters found, or an empty string
	 */
	public static function getLimitClause(array $requestParameters=[]): string {
		if(isset($requestParameters["all"])){ return ''; }
		if(!isset($requestParameters['pagenumber']) && !isset($requestParameters['pagesize'])){
			$requestParameters['pagenumber']=1;
			$requestParameters['pagesize']=25;
		}
		if(isset($requestParameters['pagenumber']) && isset($requestParameters['pagesize'])){
			$pageNum=(int)($requestParameters['pagenumber']);
			$pageSize=(int)($requestParameters['pagesize']);
 			$start=(($pageNum-1)*$pageSize);
			return ' LIMIT '.$start.', '.$pageSize.' ';
		}
		return '';
	}

	/**
	 * @param array $requestParameters
	 * @param string $className
	 * @return string
	 * @throws BadRequestException
	 */
	public static function getOrderClause(array $requestParameters, string $className=''): string {
		$sortOrder='ASC';
		if(''!=$className && !preg_match('/^[a-z0-9_]+$/',$className)){
			throw new BadRequestException('Class name not recognised');
		}
		if(isset($requestParameters['sortby'])){ 
			$sortBy=$requestParameters['sortby']; 
			if(isset($requestParameters['sortdescending']) && in_array(strtolower($requestParameters['sortdescending']), ["1","true","yes"])){
				$sortOrder='DESC'; 
			}
			if(!preg_match('/^[a-z0-9_.]+$/',$sortBy)){
				throw new BadRequestException('Sort field not recognised');
			}
			if(''!=$className){
				$className.='.';
			}
			$sql=' ORDER BY LOWER('.$className.$sortBy.') '.$sortOrder.' ';
			if(stripos($sortBy,'.')!==FALSE){
			    $sql=' ORDER BY '.$sortBy.' '.$sortOrder.' ';
			} else if('id'==$sortBy){
				$sql=' ORDER BY '.$className.$sortBy.' '.$sortOrder.' ';
			} else {
				$validations=forward_static_call_array(array(trim($className,'.'), 'getFieldValidations'), array());
				$validation=(array)$validations[$sortBy];
				if(in_array(validator::INTEGER, $validation) || in_array(validator::FLOAT, $validation)){
					$sql=' ORDER BY '.$className.$sortBy.' '.$sortOrder.' ';
				}
			}
		} else if(isset($className::$defaultSortOrder)){
			$sql=' ORDER BY '.$className::$defaultSortOrder;
		} else {
			$sql='';
		}
		return $sql;
	}

    /**
     * Filter the results against supplied text values. This function expects the request parameters to contain a key
     * "filter", whose value is a JSON object with keys being property names and values being the text that must appear
     * in the corresponding property. For example {"name":"ab"} will only return rows whose name contains ab. This filter
     * is CASE-INSENSITIVE.
     *
     * Note that the standard UI tables do not use this functionality, retrieving all rows and filtering client-side.
     *
     * @param $requestParameters array The request parameters.
     * @param string $className
     * @return string
     * @throws BadRequestException
     */
	public static function getFilterClause(array $requestParameters, string $className=''): string {
		if(!isset($requestParameters['filter'])){
			return '';
		}

        if(''!=$className){
            if(!preg_match('/^[a-z0-9_]+$/',$className)){
                throw new BadRequestException('Class name not recognised');
            }
            $className.='.';
        }

		$filters=json_decode($requestParameters['filter']);
		if(!$filters){
		    throw new BadRequestException('Filter is not JSON');
        }
		$filterClause='';
        foreach ($filters as $filterBy=>$filterText) {
            if(!preg_match('/^[A-Za-z0-9]*$/',$filterBy)){
                throw new BadRequestException('Bad filter key '.$filterBy);
            }
            if(!preg_match('/^[\sA-Za-z0-9_-]*$/',$filterText)){
                throw new BadRequestException('Bad filter text '.$filterText);
            }
            $aliasedColumns=forward_static_call_array(array($filterBy,'getAliasedColumns'),array());
            if(in_array($filterBy, $aliasedColumns)){
                $filterClause.=' AND LOWER('.$filterBy.') LIKE "%'.strtolower($filterText).'%" ';
            } else {
                $filterClause.=' AND LOWER('.$className.$filterBy.') LIKE "%'.strtolower($filterText).'%" ';
            }
		}
        return $filterClause;
	}

    /**
     * Returns an AND clause with the projects for which the current user is authorised to perform the specified operation.
     *
	 * Returns the same clause for admins and non-admins, because session::refreshProjectPermissions populates a project
	 * list for both that depends on whether session::showArchivedProjects is true or false.
     * ' AND 1=0 ' for users who cannot do this operation on any project
     * ' AND project.id=123 ' for users who can do this on only one project
     * ' AND project.id IN (123,124) ' for users who can do this on more than one project
     *
     * @param string $accessType One of 'create','read','update' or 'delete';
     * @param bool $forceSharedProject
     * @return string The SQL clause
	 * @throws ServerException
     * @throws BadRequestException
     */
	public static function getProjectClause(string $accessType, bool $forceSharedProject=false): string {
		if(!in_array($accessType, ['read','readOne','create','update','delete'])){
			throw new ServerException("Bad accessType $accessType in database::getProjectClause");
		}
		$projects=session::getProjectPermissions($accessType);
		if($forceSharedProject){
		    $sharedProjectId=project::getSharedProjectId();
		    if(!in_array($sharedProjectId, $projects)){
		        $projects[]=$sharedProjectId;
		    }
		}
		if('read'==$accessType && basesession::getShowArchivedProjects()){
			$projects=array_unique(array_merge($projects, session::get('archivedProjectIds')));
		}
		if(empty($projects)){ 
			return ' AND 1=0 '; //Always evaluates to false, so they won't get anything.
		} else if(1==count($projects)){
			return ' AND project.id='.(int)($projects[0]).' ';
		} else {
			$projects=implode(',', $projects);
			if(preg_match('/[^0-9,]/',$projects)){
				throw new ServerException('Internal list of user projects corrupted');
			}
			return ' AND project.id IN('.$projects.') ';
		}
	}

	/**
	 * Adds the column to the table, if it doesn't exist, otherwise alters it to the supplied definition.
	 * @param string $table
	 * @param string $column
	 * @param string $definition
	 * @param string $comment
	 * @return boolean true if created, false if already existed
	 * @throws BadRequestException
	 * @throws ServerException
	 */
	public static function addOrAlterColumn(string $table, string $column, string $definition, string $comment=''): bool {
	    if(!Log::isInited()){
	        throw new ServerException('Log must be init()ed before calling database::addOrAlterColumn');
	    }
	    Log::write(Log::LOGLEVEL_DEBUG, "In database:addOrAlterColumn, $table, $column, $definition");
	    if(!preg_match('/^[A-Za-z0-9_-]+$/', $table)){ throw new BadRequestException('Bad table name "'.htmlentities($table).'" got to addOrAlterColumn'); }
	    if(!preg_match('/^[A-Za-z0-9_-]+$/', $column)){ throw new BadRequestException('Bad column name "'.htmlentities($column).'" got to addOrAlterColumn'); }
	    if(!preg_match('/^[()\s.,\'\"\/A-Za-z0-9_-]+$/', $definition)){ throw new BadRequestException('Bad definition "'.htmlentities($definition).'" got to addOrAlterColumn'); }
	    if(!preg_match('/^[()\s.,\/A-Za-z0-9_-]*$/', $comment)){ throw new BadRequestException('Bad comment "'.htmlentities($comment).'" got to addOrAlterColumn'); }
	    if(!empty(database::queryGetAll('SHOW COLUMNS in '.$table.' LIKE "'.$column.'"'))){
	        Log::write(Log::LOGLEVEL_INFO, "Table $table already has column $column");
	        Log::write(Log::LOGLEVEL_INFO, "Altering to $definition...");
			$sql="ALTER TABLE $table CHANGE $column $column $definition ";
			if(!empty($comment)){
				$sql.="COMMENT '$comment'";
			}
	        database::query($sql);
	        Log::write(Log::LOGLEVEL_INFO, "Altered $table column $column");
	        return false;
	    }
	    Log::write(Log::LOGLEVEL_INFO, "Table $table does not have column $column");
	    Log::write(Log::LOGLEVEL_INFO, "Adding column $column to $table");
	    Log::write(Log::LOGLEVEL_INFO, "Definition: $definition");
		$sql="ALTER TABLE $table ADD $column $definition ";
		if(!empty($comment)){
			$sql.="COMMENT '$comment'";
		}
	    database::query($sql);
        Log::write(Log::LOGLEVEL_INFO, "Added column $column to $table");
	    return true;
	}

	/**
	 * 
	 * @param string $table The table name
	 * @param string $column The column name
	 * @return boolean true if column existed and was dropped, false if column did not exist
	 *@throws BadRequestException
	 * @throws ServerException
	 */
	public static function dropColumnIfExists(string $table, string $column): bool {
	    if(!Log::isInited()){
	        throw new ServerException('Log must be init()ed before calling dropColumnIfExists');
	    }
	    Log::write(Log::LOGLEVEL_DEBUG, "In database:dropColumnIfExists, $table, $column");
	    if(!preg_match('/^[A-Za-z0-9_-]+$/', $table)){ throw new BadRequestException('Bad table name '.htmlentities($table).' got to dropColumnIfExists'); }
	    if(!preg_match('/^[A-Za-z0-9_-]+$/', $column)){ throw new BadRequestException('Bad column name '.htmlentities($column).' got to dropColumnIfExists'); }
	    if(empty(database::queryGetAll('SHOW COLUMNS in '.$table.' LIKE "'.$column.'"'))){
	        Log::write(Log::LOGLEVEL_WARN, "Table $table has no column $column");
	        return false;
	    }
	    database::query("ALTER TABLE $table DROP $column ");
	    Log::write(Log::LOGLEVEL_INFO, "Dropped column $column in $table");
	    return true;
	}
	
}

