services/DatabaseService.php - master - memoryspeak/MemoryspeakServer

services/DatabaseService.php
479 строк14 KB
memoryspeak
refactor share note
14 дек 2025, 11:31
14 дек 2025, 11:313e63e9d
<?php

require_once '../config.php';
require_once '../utils/Utils.php';
require_once 'LoggerService.php';
require_once 'JWTService.php';

class DatabaseService {

  private const CONNECTION_FAILED = 'Database connection failed';
  private const USER_NOT_FOUND = 'User not found';
  private const NOTE_NOT_FOUND = 'Note not found';
  private const INVALID_JWT = 'Invalid or expired JWT';
  private const INVALID_FILE_ID = 'Invalid file id';

  private $connection;

  public function __construct() {
    try {
      $this->connection = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
      $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $this->initDatabase();
    } catch(PDOException $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getAuthenticatedUser($verifyJWT) {
    try {
      if (!$verifyJWT || !isset($verifyJWT['user_id'])) return ['id' => 0, 'exception' => self::INVALID_JWT];
      $user = $this->getUserById($verifyJWT['user_id']);
      return $user
        ? ['id' => $user['id'], 'username' => $user['username'], 'exception' => '']
        : ['id' => 0, 'exception' => self::USER_NOT_FOUND];
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function deactivateFcmToken($fcmToken) {
    try {
      $this->exec(
        "UPDATE users_fcm_tokens SET active = 0 WHERE fcm_token = :fcm_token;",
        ['fcm_token' => $fcmToken]
      );
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function deactivateFcmTokenByUserId($userId) {
    try {
      $this->exec(
        "UPDATE users_fcm_tokens SET active = 0 WHERE user_id = :user_id;",
        ['user_id' => $userId]
      );
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function activateFcmToken($fcmTokenId, $deviceInfo, $platform) {
    try {
      $this->exec(
        "UPDATE users_fcm_tokens SET active = 1, device_info = :device_info, platform = :platform, updated_at = CURRENT_TIMESTAMP WHERE id = :fcm_token_id;",
        [
          'device_info' => $deviceInfo,
          'platform' => $platform,
          'fcm_token_id' => $fcmTokenId,
        ]
      );
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getFcmTokenIdByUserIdAndFcmToken($userId, $fcmToken) {
    try {
      $fcmTokenId = $this->getFetch(
        "SELECT id FROM users_fcm_tokens WHERE user_id = :user_id AND fcm_token = :fcm_token;",
        [
          'user_id' => $userId,
          'fcm_token' => $fcmToken
        ]
      );
      return $fcmTokenId;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function createFcmToken($userId, $fcmToken, $deviceInfo, $platform) {
    try {
      $lastInsertFcmTokenId = $this->exec(
        "INSERT INTO users_fcm_tokens (user_id, fcm_token, device_info, platform) VALUES (:user_id, :fcm_token, :device_info, :platform);",
        [
          'user_id' => $userId,
          'fcm_token' => $fcmToken,
          'device_info' => $deviceInfo,
          'platform' => $platform
        ]
      );
      return $lastInsertFcmTokenId;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getUserByUsernameAndHash($username, $hash) {
    try {
      $user = $this->getFetch(
        "SELECT * FROM users WHERE username = :username AND md5password = :hash;",
        [
          'username' => $username,
          'hash' => $hash
        ]
      );
      return $user;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getUserById($id) {
    try {
      $user = $this->getFetch(
        "SELECT * FROM users WHERE id = :user_id;",
        ['user_id' => $id]
      );
      return $user;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getFileById($fileId) {
    try {
      if ($fileId === 0) throw new Exception(self::INVALID_FILE_ID);
      $file = $this->getFetch(
        "SELECT id, base64 FROM files WHERE id = :file_id;",
        ['file_id' => $fileId]
      );
      return $file;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function insertNote($title, $body, $noteToken, $userId) {
    try {
      $lastInsertNoteId = $this->exec(
        "INSERT INTO notes (title, body, token, users_id) VALUES (:title, :body, :note_token, :user_id);",
        [
          'title' => $title,
          'body' => $body,
          'note_token' => $noteToken,
          'user_id' => $userId
        ]
      );
      return $lastInsertNoteId;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function insertTags($tags, $userId, $noteId) {
    try {
      foreach($tags as $tag) {
        $this->exec(
          "INSERT INTO tags (tag, users_id, notes_id) VALUES (:tag, :user_id, :note_id);",
          [
            'tag' => $tag,
            'user_id' => $userId,
            'note_id' => $noteId
          ]
        );
      }
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function insertFiles($files, $userId, $noteId) {
    try {
      foreach($files as $file) {
        $this->exec(
          "INSERT INTO files (filename, size, type, extention, base64, users_id, notes_id) VALUES (:filename, :size, :type, :extention, :base64, :user_id, :note_id);",
          [
            'filename' => $file->filename,
            'size' => $file->size,
            'type' => $file->type,
            'extention' => $file->extention,
            'base64' => $file->base64,
            'user_id' => $userId,
            'note_id' => $noteId
          ]
        );
      }
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getTotalNumberOfNotes($userId) {
    try {
      $number = $this->getFetch(
        "SELECT COUNT(id) AS total FROM notes WHERE users_id = :user_id;",
        ['user_id' => $userId]
      );
      return $number["total"];
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function addViewToNote($noteId) {
    try {
      $this->exec(
        "UPDATE notes SET views = views + 1 WHERE id = :note_id;",
        ['note_id' => $noteId]
      );
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getNoteById($noteId) {
    try {
      $note = $this->getFetch(
        "SELECT title, body, created, datetime, token, views, users_id FROM notes WHERE id = :note_id;",
        ['note_id' => $noteId]
      );
      return $note;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getNoteByNoteToken($noteToken) {
    try {
      $note = $this->getFetch(
        "SELECT id, title, body, token, users_id FROM notes WHERE token = :note_token;",
        ['note_token' => $noteToken]
      );
      if (!$note) throw new Exception(self::NOTE_NOT_FOUND);
      return $note;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getAllUsers() {
    try {
      $allUsers = $this->getFetchAll(
        "SELECT id, username FROM users;",
        []
      );
      return $allUsers;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getTagsByNoteId($noteId) {
    try {
      $tags = $this->getFetchAll(
        "SELECT id, tag FROM tags WHERE notes_id = :note_id;",
        ['note_id' => $noteId]
      );
      return $tags;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getTagsByUserId($userId) {
    try {
      $tags = $this->getFetchAll(
        "SELECT id, tag FROM tags WHERE users_id = :user_id;",
        ['user_id' => $userId]
      );
      return $tags;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getFcmTokensByUserId($userId) {
    try {
      $fcmTokens = $this->getFetchAll(
        "SELECT fcm_token FROM users_fcm_tokens WHERE user_id = :user_id AND active = 1 AND updated_at > NOW() - INTERVAL 3 MONTH;",
        ['user_id' => $userId]
      );
      $tokens = [];
      foreach ($fcmTokens as $tokenData) {
        $tokens[] = $tokenData['fcm_token'];
      }
      return $tokens;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getFilesByNoteId($noteId) {
    try {
      $files = $this->getFetchAll(
        "SELECT id, filename, size, type, extention FROM files WHERE notes_id = :note_id;",
        ['note_id' => $noteId]
      );
      return $files;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getNotesIdByTagsLike($tags, $userId) {
    try {
      $sql = "SELECT notes_id FROM tags WHERE users_id = :user_id";
      $exec = ['user_id' => $userId];
      if ($tags) {
        $sql .= " AND (";
        for ($i = 0; $i < count($tags); $i++) {
          $queryNameTagLike = "query_tag_like_".$i;
          $sql .= "tag LIKE :".$queryNameTagLike;
          if ($i != (count($tags) - 1)) {
            $sql .= " OR ";
          }
          $exec[$queryNameTagLike] = '%'.str_replace(['%', '_'], ['\%', '\_'], $tags[$i]).'%';
        }
        $sql .= ")";
      }
      $sql .= ";";
      $notesId = $this->getFetchAll($sql, $exec);
      return array_column($notesId, 'notes_id');
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  public function getNotesIdByQueriesAndTagsLike($queries, $tags, $userId) {
    try {
      [
        $sqlTitleAndBodyLike,
        $execTitleAndBodyLike,
        $sqlFilenameLike,
        $execFilenameLike
      ] = $this->getSqlAndExecNoteFieldsLike($queries, $userId);
      $notesIdByTitleAndBodyLike = $this->getFetchAll(
        $sqlTitleAndBodyLike,
        $execTitleAndBodyLike
      );
      $notesIdByFilenameLike = $this->getFetchAll(
        $sqlFilenameLike,
        $execFilenameLike
      );
      $notesIdByQueriesLike = array_merge($notesIdByTitleAndBodyLike, $notesIdByFilenameLike);
      $notesIdByTagsLike = $this->getNotesIdByTagsLike($tags, $userId);
      $notesCross = array_intersect($notesIdByQueriesLike, $notesIdByTagsLike);
      return Utils::sortedArray($notesCross);
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  private function getSqlAndExecNoteFieldsLike($queries, $userId) {
    try {
      $sqlTitleAndBodyLike = "SELECT id FROM notes WHERE (users_id = :user_id";
      $sqlTitleLike = "";
      $sqlBodyLike = "";
      $sqlFilenameLike = "SELECT notes_id FROM files WHERE users_id = :user_id";
      $execTitleAndBodyLike = ['user_id' => $userId];
      $execFilenameLike = ['user_id' => $userId];
      for ($i = 0; $i < count($queries); $i++) {
        $queryNameTitleLike = "query_title_like_".$i;
        $queryNameBodyLike = "query_body_like_".$i;
        $queryNameFilenameLike = "query_filename_like_".$i;
        $sqlTitleLike .= " AND title LIKE :".$queryNameTitleLike;
        $sqlBodyLike .= " AND body LIKE :".$queryNameBodyLike;
        $sqlFilenameLike .= " AND filename LIKE :".$queryNameFilenameLike;
        $execTitleAndBodyLike[$queryNameTitleLike] = '%'.str_replace(['%', '_'], ['\%', '\_'], $queries[$i]).'%';
        $execTitleAndBodyLike[$queryNameBodyLike] = '%'.str_replace(['%', '_'], ['\%', '\_'], $queries[$i]).'%';
        $execFilenameLike[$queryNameFilenameLike] = '%'.str_replace(['%', '_'], ['\%', '\_'], $queries[$i]).'%';
      };
      $sqlTitleAndBodyLike .= $sqlTitleLike.") OR (users_id = :user_id".$sqlBodyLike.");";
      $sqlFilenameLike .= ";";
      return [
        $sqlTitleAndBodyLike,
        $execTitleAndBodyLike,
        $sqlFilenameLike,
        $execFilenameLike
      ];
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  private function getFetchAll($sql, $exec) {
    try {
      if (!$this->connection) throw new Exception(self::CONNECTION_FAILED);
      $stmt = $this->connection-> prepare($sql);
      $stmt->execute($exec);
      $elements = $stmt->fetchAll(PDO::FETCH_ASSOC);
      $stmt-> closeCursor();
      return $elements;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  private function getFetch($sql, $exec) {
    try {
      if (!$this->connection) throw new Exception(self::CONNECTION_FAILED);
      $stmt = $this->connection-> prepare($sql);
      $stmt->execute($exec);
      $element = $stmt->fetch(PDO::FETCH_ASSOC);
      $stmt-> closeCursor();
      return $element;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  private function exec($sql, $exec) {
    try {
      if (!$this->connection) throw new Exception(self::CONNECTION_FAILED);
      $stmt = $this->connection-> prepare($sql);
      $stmt->execute($exec);
      $lastId = $this->connection->lastInsertId();
      $stmt-> closeCursor();
      return $lastId;
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

  private function initDatabase() {
    try {
      if (!$this->connection) throw new Exception(self::CONNECTION_FAILED);
      $stmt = $this->connection->prepare(DATABASE_INIT_SQL);
      $stmt->execute(DEFAULT_DATABASE_USERS_DATA);
      $stmt->fetchAll(PDO::FETCH_ASSOC);
      $stmt->closeCursor();
    } catch(Exception $e) {
      LoggerService::write(debug_backtrace(), $e->getMessage());
      throw $e;
    }
  }

}

?>