<?php
	//資料庫類別，使用PDO
	class database {
		//資料庫版本號
		public string $version;
		//錯誤信息
		public string $error = "";
		//實際連結
		public PDO $dbh;

		//構造函數
		public function __construct($host, $user, $password, $name){
			$this->dbh = new PDO('mysql:host='.$host.';dbname='.$name, $user, $password, array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8 COLLATE utf8_unicode_ci, SESSION sql_mode='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"
            ));
			$this->dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
			$this->dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
			$this->dbh->query('SET NAMES UTF8');

			$this->version = @$this->dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
		}

		//Ping 一個服務器連接
		public function ping(){
			try{
				$this->dbh->query("SELECT 1");
			}catch(PDOException $e){
				return false;
			}
			return true;
		}

		//執行sql語句,返回結果集
		public function execute(string $sql, array $ary, bool $writeLog = false): ?PDOStatement {
			try {
				$sth = $this->dbh->prepare($sql);
				if ($sth === false) throw new PDOException("PDOStatement prepare failed.");

				foreach ($ary as $key => $rows) {
					$name = $rows["Name"];
					$value = $rows["Value"];
					$type = $rows["Type"];
					$number = $rows["Number"] ?? null;

					$number = is_numeric($number) ? (int) $number : null;
					
					$this->bindParam($sth, $name, $value, $type, $number);
					$sql = str_replace($name, $value, $sql);
				}

				$result = $sth->execute();
				if ($result === false) throw new PDOException("PDOStatement execute failed.");

				if ($writeLog) $this->logExecute($sql);
				
				return $sth;
			} catch (PDOException $e) {
				$this->error = $e->getMessage();
				$this->logError($e->getMessage() . "\r\nsql:" . $sql);
				return null;
			}
		}
		
		private function bindParam(PDOStatement $sth, string $name, ?string $value, string $type, ?int $number = null) {
			if ($value === null) $value = "";
			
			switch ($type) {
				case "str":
					if ($number !== null && preg_match("/^\d+$/", $number)) {
						$sth->bindParam($name, $value, PDO::PARAM_STR, (int) $number);
					} else {
						$sth->bindParam($name, $value, PDO::PARAM_STR);
					}
					break;
				case "int":
					$sth->bindParam($name, $value, PDO::PARAM_INT);
					break;
				case "null":
					$sth->bindParam($name, $value, PDO::PARAM_NULL);
					break;
				case "bool":
					$sth->bindParam($name, $value, PDO::PARAM_BOOL);
					break;
				default:
					$sth->bindParam($name, $value);
			}
		}
		
		//執行sql語句,返回第一行第一列記錄
		public function executeScalar(string $sql, array $ary): mixed {
			$sth = $this->execute($sql, $ary);
			$rs = $sth->fetch(PDO::FETCH_ASSOC);
			$rs = array_values($rs);
			return $rs[0];
		}
		
		//執行sql語句,返回第一行記錄
		public function executeReader(string $sql, array $ary): array {
			$sth = $this->execute($sql, $ary);
			$rs = $sth->fetch(PDO::FETCH_ASSOC);
		
			if ($rs === false) return [];
			return $rs;
		}
		
		//執行sql語句,返回影響記錄數
		public function executeNonQuery(string $sql, array $ary, bool $writeLog = false): mixed {
			$sth = $this->execute($sql, $ary, $writeLog);
			return $sth->fetchColumn();
		}

		//執行sql語句，以數組形式返回結果集
		//參數說明:$sql,SQL語句;$start,開始記錄號;$length,返回記錄數
		public function executeRecordset(string $sql, array $ary, int $start = 0, int $length = -1): array {
			if ($start >= 0 && $length >= 0) $sql .= " LIMIT $start, $length";
			$sth = $this->execute($sql, $ary);
			$rs = $sth->fetchAll(PDO::FETCH_ASSOC);
			return $rs;
		}

		//取得上一步 INSERT 操作產生的 ID
		public function getInsertID(): int {
			return $this->dbh->lastInsertId();
		}

		//開始事務
		public function beginTransaction(): void {
			$this->dbh->beginTransaction();
		}

		//提交事務
		public function commitTransaction(): void {
			$this->dbh->commit();
		}

		//回滾事務
		public function rollbackTransaction(): void {
			$this->dbh->rollBack();
		}

		// 寫入錯誤日誌
		private function logError(string $error) {
			$this->logMessage("err", $error);
		}

		// 寫入操作日誌
		private function logExecute(string $message) {
			$this->logMessage("query", $message);
		}

		// 日誌文件
		private function logMessage(string $prefix, string $message) {
			$msg = "\r\n=============================================================================\r\n";
			$msg .= "Time:" . date("Y-m-d H:i:s") . "\r\n";
			$msg .= "Type:sql " . $prefix . "message\r\n";
			$msg .= "Message:\r\n";
			$msg .= $message;
	
			$path = dirname(__FILE__) . "/../../logs/";
			if (!is_dir($path)) {
				mkdir($path, 0755, true);
			}
			$fn = $path . $prefix . "_" . date("Ymd") . ".log";
			file_put_contents($fn, $msg, FILE_APPEND);
		}

		/*寫入日誌文件
		private function logError($error){
			$msg = "\r\n=============================================================================\r\n";
			$msg .= "Time:".(string)date("Y-m-d H:i:s")."\r\n";
			$msg .= "Type:sql error\r\n";
			$msg .= "Message:\r\n";
			$msg .= $error;

			$path = dirname(__FILE__)."/../../logs/";
			if(!is_dir($path))
				@mkdir($path,0755);
			$fn = $path."err_".(string)date("Ymd").".log";
			$fp = fopen($fn,"ab");
			fwrite($fp,$msg);
			fclose($fp);
		}*/
/*
		//操作日誌記錄
		private function logExecute($message){
			$msg = "\r\n=============================================================================\r\n";
			$msg .= "Time:".(string)date("Y-m-d H:i:s")."\r\n";
			$msg .= "Type:sql query\r\n";
			$msg .= "Message:\r\n";
			$msg .= $message;

			$path = dirname(__FILE__)."/../../logs/";
			if(!is_dir($path))
				@mkdir($path,0755);
			$fn = $path."query_".(string)date("Ymd").".log";
			$fp = fopen($fn,"ab");
			fwrite($fp,$msg);
			fclose($fp);
		}*/
	}
?>