/*  SPDX-License-Identifier: GPL-2.0-or-later */
/*!********************************************************************

  Audacity: A Digital Audio Editor

  CloudProjectsDatabase.cpp

  Dmitry Vedenko

**********************************************************************/
#include "CloudProjectsDatabase.h"

#include <cassert>

#include "CodeConversions.h"
#include "FileNames.h"

namespace audacity::cloud::audiocom::sync
{
namespace
{
const char* createTableQuery = R"(
CREATE TABLE IF NOT EXISTS projects
(
   project_id TEXT,
   snapshot_id TEXT,
   saves_count INTEGER,
   last_audio_preview_save INTEGER,
   local_path TEXT,
   last_modified INTEGER,
   last_read INTEGER,
   sync_status INTEGER,
   PRIMARY KEY (project_id)
);

CREATE INDEX IF NOT EXISTS local_path_index ON projects (local_path);

CREATE TABLE IF NOT EXISTS block_hashes
(
   project_id INTEGER,
   block_id INTEGER,
   hash TEXT,
   PRIMARY KEY (project_id, block_id)
);

CREATE INDEX IF NOT EXISTS block_hashes_index ON block_hashes (hash);

CREATE TABLE IF NOT EXISTS pending_snapshots
(
   project_id TEXT,
   snapshot_id TEXT,
   confirm_url TEXT,
   PRIMARY KEY (project_id, snapshot_id)
);

CREATE TABLE IF NOT EXISTS pending_project_blobs
(
   project_id TEXT,
   snapshot_id TEXT,

   upload_url TEXT,
   confirm_url TEXT,
   fail_url TEXT,

   blob BLOB,
   PRIMARY KEY (project_id, snapshot_id)
);

CREATE TABLE IF NOT EXISTS pending_project_blocks
(
   project_id TEXT,
   snapshot_id TEXT,

   upload_url TEXT,
   confirm_url TEXT,
   fail_url TEXT,

   block_id INTEGER,
   block_sample_format INTEGER,
   block_hash TEXT,
   PRIMARY KEY (project_id, snapshot_id, block_id)
);

CREATE TABLE IF NOT EXISTS project_users
(
   project_id INTEGER,
   user_name TEXT,
   PRIMARY KEY (project_id)
);

CREATE TABLE IF NOT EXISTS migration
(
   version INTEGER
);

INSERT INTO migration (version) VALUES (0);

DELETE FROM migration WHERE ROWID != 1;
)";

const auto addProjectSyncedDialogShownColumn = R"(
ALTER TABLE projects ADD COLUMN synced_dialog_shown INTEGER DEFAULT 1;
)";

const char* migrations[] = {
   addProjectSyncedDialogShownColumn,
};
} // namespace

CloudProjectsDatabase& CloudProjectsDatabase::Get()
{
   static CloudProjectsDatabase instance;
   return instance;
}

sqlite::SafeConnection::Lock CloudProjectsDatabase::GetConnection()
{
   auto lock = std::lock_guard { mConnectionMutex };

   if (!mConnection)
      OpenConnection();

   // It is safe to call lock on a null connection
   return sqlite::SafeConnection::Lock { mConnection };
}

const sqlite::SafeConnection::Lock CloudProjectsDatabase::GetConnection() const
{
   return const_cast<CloudProjectsDatabase*>(this)->GetConnection();
}

std::optional<DBProjectData>
CloudProjectsDatabase::GetProjectData(std::string_view projectId) const
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown FROM projects WHERE project_id = ? LIMIT 1");

   if (!statement)
      return {};

   return DoGetProjectData(statement->Prepare(projectId).Run());
}

std::optional<DBProjectData> CloudProjectsDatabase::GetProjectDataForPath(
   const std::string& projectFilePath) const
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown FROM projects WHERE local_path = ? LIMIT 1");

   if (!statement)
      return {};

   return DoGetProjectData(statement->Prepare(projectFilePath).Run());
}

std::vector<DBProjectData>
cloud::audiocom::sync::CloudProjectsDatabase::GetCloudProjects() const
{
   std::vector<DBProjectData> result;

   auto connection = GetConnection();

   if (!connection)
      return result;

   auto statement = connection->CreateStatement(
      "SELECT project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown FROM projects");

   if (!statement)
      return result;

   auto runResult = statement->Prepare().Run();

   for (auto row : runResult)
   {
      auto data = DoGetProjectData(row);

      if (data)
         result.push_back(*data);
   }

   return result;
}

void cloud::audiocom::sync::CloudProjectsDatabase::DeleteProject(
   std::string_view projectId)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto tx = connection->BeginTransaction("DeleteProject");
   if (DeleteProject(connection, projectId))
      tx.Commit();
}

bool CloudProjectsDatabase::MarkProjectAsSynced(
   std::string_view projectId, std::string_view snapshotId)
{
   auto connection = GetConnection();

   if (!connection)
      return false;

   auto statement = connection->CreateStatement(
      "UPDATE projects SET sync_status = ? WHERE project_id = ? AND snapshot_id = ?");

   if (!statement)
      return false;

   auto result = statement
                    ->Prepare(
                       static_cast<int>(DBProjectData::SyncStatusSynced),
                       projectId, snapshotId)
                    .Run();

   if (!result.IsOk())
      return false;

   return result.GetModifiedRowsCount() > 0;
}

void CloudProjectsDatabase::UpdateProjectBlockList(
   std::string_view projectId, const SampleBlockIDSet& blockSet)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto inProjectSet = connection->CreateScalarFunction(
      "inProjectSet", [&blockSet](int64_t blockIndex)
      { return blockSet.find(blockIndex) != blockSet.end(); });

   auto statement = connection->CreateStatement(
      "DELETE FROM block_hashes WHERE project_id = ? AND NOT inProjectSet(block_id)");

   auto result = statement->Prepare(projectId).Run();

   if (!result.IsOk())
   {
      assert(false);
   }
}

std::optional<std::string> CloudProjectsDatabase::GetBlockHash(
   std::string_view projectId, int64_t blockId) const
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT hash FROM block_hashes WHERE project_id = ? AND block_id = ? LIMIT 1");

   if (!statement)
      return {};

   auto result = statement->Prepare(projectId, blockId).Run();

   for (auto row : result)
   {
      std::string hash;

      if (!row.Get(0, hash))
         return {};

      return hash;
   }

   return {};
}

void CloudProjectsDatabase::UpdateBlockHashes(
   std::string_view projectId,
   const std::vector<std::pair<int64_t, std::string>>& hashes)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   const int localVar {};
   auto transaction = connection->BeginTransaction(
      std::string("UpdateBlockHashes_") +
      std::to_string(reinterpret_cast<size_t>(&localVar)));

   auto statement = connection->CreateStatement(
      "INSERT OR REPLACE INTO block_hashes (project_id, block_id, hash) VALUES (?, ?, ?)");

   for (const auto& [blockId, hash] : hashes)
      statement->Prepare(projectId, blockId, hash).Run();

   transaction.Commit();
}

bool CloudProjectsDatabase::UpdateProjectData(const DBProjectData& projectData)
{
   auto connection = GetConnection();

   if (!connection)
      return false;

   auto tx = connection->BeginTransaction("UpdateProjectData");

   auto updateProjectData = connection->CreateStatement(
      "INSERT OR REPLACE INTO projects (project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

   if (!updateProjectData)
      return false;

   auto result = updateProjectData
                    ->Prepare(
                       projectData.ProjectId, projectData.SnapshotId,
                       projectData.SavesCount, projectData.LastAudioPreview,
                       projectData.LocalPath, projectData.LastModified,
                       projectData.LastRead, projectData.SyncStatus,
                       projectData.FirstSyncDialogShown)
                    .Run();

   if (!result.IsOk())
      return false;

   auto listMissingProjects = connection->CreateStatement (
      "SELECT project_id FROM projects WHERE project_id != ? AND local_path = ?");

   if (!listMissingProjects)
      return false;

   auto missingProjects = listMissingProjects->Prepare(
      projectData.ProjectId, projectData.LocalPath).Run();

   for (auto row : missingProjects)
   {
      std::string missingProjectId;

      if (!row.Get(0, missingProjectId))
         return false;

      if (!DeleteProject(connection, missingProjectId))
         return false;
   }

   return tx.Commit().IsOk();
}

bool cloud::audiocom::sync::CloudProjectsDatabase::IsFirstSyncDialogShown(
   std::string_view projectId) const
{
   auto connection = GetConnection();

   if (!connection)
      return false;

   auto statement = connection->CreateStatement (
      "SELECT synced_dialog_shown FROM projects WHERE project_id = ? LIMIT 1");

   if (!statement)
      return false;

   auto result = statement->Prepare(projectId).Run();

   for (auto row : result)
   {
      bool shown;

      if (!row.Get(0, shown))
         return false;

      return shown;
   }

   return false;
}

void cloud::audiocom::sync::CloudProjectsDatabase::SetFirstSyncDialogShown(
   std::string_view projectId, bool shown)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement (
      "UPDATE projects SET synced_dialog_shown = ? WHERE project_id = ?");

   if (!statement)
      return;

   statement->Prepare(shown, projectId).Run();
}

std::string
CloudProjectsDatabase::GetProjectUserSlug(std::string_view projectId)
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT user_name FROM project_users WHERE project_id = ? LIMIT 1");

   if (!statement)
      return {};

   auto result = statement->Prepare(projectId).Run();

   for (auto row : result)
   {
      std::string slug;

      if (!row.Get(0, slug))
         return {};

      return slug;
   }

   return {};
}

void CloudProjectsDatabase::SetProjectUserSlug(
   std::string_view projectId, std::string_view slug)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement(
      "INSERT OR REPLACE INTO project_users (project_id, user_name) VALUES (?, ?)");

   if (!statement)
      return;

   statement->Prepare(projectId, slug).Run();
}

bool CloudProjectsDatabase::IsProjectBlockLocked(
   std::string_view projectId, int64_t blockId) const
{
   auto connection = GetConnection();

   if (!connection)
      return false;

   auto statement = connection->CreateStatement(
      "SELECT 1 FROM pending_project_blocks WHERE project_id = ? AND block_id = ? LIMIT 1");

   if (!statement)
      return false;

   auto result = statement->Prepare(projectId, blockId).Run();

   for (auto row : result)
      return true;

   return false;
}

void CloudProjectsDatabase::AddPendingSnapshot(
   const PendingSnapshotData& snapshotData)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement(
      "INSERT OR REPLACE INTO pending_snapshots (project_id, snapshot_id, confirm_url) VALUES (?, ?, ?)");

   if (!statement)
      return;

   statement
      ->Prepare(
         snapshotData.ProjectId, snapshotData.SnapshotId,
         snapshotData.ConfirmUrl)
      .Run();
}

void CloudProjectsDatabase::RemovePendingSnapshot(
   std::string_view projectId, std::string_view snapshotId)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   static const char* queries[] = {
      "DELETE FROM pending_snapshots WHERE project_id = ? AND snapshot_id = ?",
      "DELETE FROM pending_project_blobs WHERE project_id = ? AND snapshot_id = ?",
      "DELETE FROM pending_project_blocks WHERE project_id = ? AND snapshot_id = ?",
   };

   auto tx = connection->BeginTransaction("RemovePendingSnapshot");

   for (auto query : queries)
   {
      auto statement = connection->CreateStatement(query);

      if (!statement)
         return;

      if (!statement->Prepare(projectId, snapshotId).Run().IsOk())
         return;
   }

   tx.Commit();
}

std::vector<PendingSnapshotData>
CloudProjectsDatabase::GetPendingSnapshots(std::string_view projectId) const
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT project_id, snapshot_id, confirm_url FROM pending_snapshots WHERE project_id = ?");

   if (!statement)
      return {};

   auto result = statement->Prepare(projectId).Run();

   std::vector<PendingSnapshotData> snapshots;

   for (auto row : result)
   {
      PendingSnapshotData data;

      if (!row.Get(0, data.ProjectId))
         return {};

      if (!row.Get(1, data.SnapshotId))
         return {};

      if (!row.Get(2, data.ConfirmUrl))
         return {};

      snapshots.push_back(data);
   }

   return snapshots;
}

void CloudProjectsDatabase::AddPendingProjectBlob(
   const PendingProjectBlobData& blobData)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement(
      "INSERT OR REPLACE INTO pending_project_blobs (project_id, snapshot_id, upload_url, confirm_url, fail_url, blob) VALUES (?, ?, ?, ?, ?, ?)");

   if (!statement)
      return;

   statement->Prepare()
      .Bind(1, blobData.ProjectId)
      .Bind(2, blobData.SnapshotId)
      .Bind(3, blobData.UploadUrl)
      .Bind(4, blobData.ConfirmUrl)
      .Bind(5, blobData.FailUrl)
      .Bind(6, blobData.BlobData.data(), blobData.BlobData.size(), false)
      .Run();
}

void CloudProjectsDatabase::RemovePendingProjectBlob(
   std::string_view projectId, std::string_view snapshotId)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement(
      "DELETE FROM pending_project_blobs WHERE project_id = ? AND snapshot_id = ?");

   if (!statement)
      return;

   statement->Prepare(projectId, snapshotId).Run();
}

std::optional<PendingProjectBlobData>
CloudProjectsDatabase::GetPendingProjectBlob(
   std::string_view projectId, std::string_view snapshotId) const
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT project_id, snapshot_id, upload_url, confirm_url, fail_url, blob FROM pending_project_blobs WHERE project_id = ? AND snapshot_id = ?");

   if (!statement)
      return {};

   auto result = statement->Prepare(projectId, snapshotId).Run();

   for (auto row : result)
   {
      PendingProjectBlobData data;

      if (!row.Get(0, data.ProjectId))
         return {};

      if (!row.Get(1, data.SnapshotId))
         return {};

      if (!row.Get(2, data.UploadUrl))
         return {};

      if (!row.Get(3, data.ConfirmUrl))
         return {};

      if (!row.Get(4, data.FailUrl))
         return {};

      const auto size = row.GetColumnBytes(6);
      data.BlobData.resize(size);

      if (size != row.ReadData(6, data.BlobData.data(), size))
         return {};

      return data;
   }

   return {};
}

void CloudProjectsDatabase::AddPendingProjectBlocks(
   const std::vector<PendingProjectBlockData>& blockData)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto tx = connection->BeginTransaction("AddPendingProjectBlocks");

   auto statement = connection->CreateStatement(
      "INSERT OR REPLACE INTO pending_project_blocks (project_id, snapshot_id, upload_url, confirm_url, fail_url, block_id, block_sample_format, block_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

   if (!statement)
      return;

   for (const auto& data : blockData)
      statement->Prepare()
         .Bind(1, data.ProjectId)
         .Bind(2, data.SnapshotId)
         .Bind(3, data.UploadUrl)
         .Bind(4, data.ConfirmUrl)
         .Bind(5, data.FailUrl)
         .Bind(6, data.BlockId)
         .Bind(7, data.BlockSampleFormat)
         .Bind(8, data.BlockHash)
         .Run();

   tx.Commit();
}

void CloudProjectsDatabase::RemovePendingProjectBlock(
   std::string_view projectId, int64_t blockId)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement(
      "DELETE FROM pending_project_blocks WHERE project_id = ? AND block_id = ?");

   if (!statement)
      return;

   statement->Prepare(projectId, blockId).Run();
}

void CloudProjectsDatabase::RemovePendingProjectBlocks(
   std::string_view projectId, std::string_view snapshotId)
{
   auto connection = GetConnection();

   if (!connection)
      return;

   auto statement = connection->CreateStatement(
      "DELETE FROM pending_project_blocks WHERE project_id = ? AND snapshot_id = ?");

   if (!statement)
      return;

   statement->Prepare(projectId, snapshotId).Run();
}

std::vector<PendingProjectBlockData>
CloudProjectsDatabase::GetPendingProjectBlocks(
   std::string_view projectId, std::string_view snapshotId)
{
   auto connection = GetConnection();

   if (!connection)
      return {};

   auto statement = connection->CreateStatement(
      "SELECT project_id, snapshot_id, upload_url, confirm_url, fail_url, block_id, block_sample_format, block_hash FROM pending_project_blocks WHERE project_id = ? AND snapshot_id = ?");

   if (!statement)
      return {};

   auto result = statement->Prepare(projectId, snapshotId).Run();

   std::vector<PendingProjectBlockData> blocks;

   for (auto row : result)
   {
      PendingProjectBlockData data;

      if (!row.Get(0, data.ProjectId))
         return {};

      if (!row.Get(1, data.SnapshotId))
         return {};

      if (!row.Get(2, data.UploadUrl))
         return {};

      if (!row.Get(3, data.ConfirmUrl))
         return {};

      if (!row.Get(4, data.FailUrl))
         return {};

      if (!row.Get(5, data.BlockId))
         return {};

      if (!row.Get(6, data.BlockSampleFormat))
         return {};

      if (!row.Get(7, data.BlockHash))
         return {};

      blocks.push_back(data);
   }

   return blocks;
}

std::optional<DBProjectData>
CloudProjectsDatabase::DoGetProjectData(const sqlite::Row& row) const
{
   DBProjectData data;

   if (!row.Get(0, data.ProjectId))
      return {};

   if (!row.Get(1, data.SnapshotId))
      return {};

   if (!row.Get(2, data.SavesCount))
      return {};

   if (!row.Get(3, data.LastAudioPreview))
      return {};

   if (!row.Get(4, data.LocalPath))
      return {};

   if (!row.Get(5, data.LastModified))
      return {};

   if (!row.Get(6, data.LastRead))
      return {};

   int status;
   if (!row.Get(7, status))
      return {};

   data.SyncStatus = static_cast<DBProjectData::SyncStatusType>(status);

   if (!row.Get(8, data.FirstSyncDialogShown))
      return {};

   return data;
}

std::optional<DBProjectData>
cloud::audiocom::sync::CloudProjectsDatabase::DoGetProjectData(
   sqlite::RunResult result) const
{
   for (auto row : result)
   {
      if (auto data = DoGetProjectData(row))
         return data;
   }

   return {};
}

bool CloudProjectsDatabase::OpenConnection()
{
   if (mConnection)
      return true;

   const auto configDir  = FileNames::ConfigDir();
   const auto configPath = configDir + "/audiocom_sync.db";

   mConnection = sqlite::SafeConnection::Open(audacity::ToUTF8(configPath));

   if (!mConnection)
      return false;

   auto connection = mConnection->Acquire();

   auto result = connection->Execute(createTableQuery);

   if (!result)
   {
      mConnection = {};
      return false;
   }

   return RunMigrations();
}

bool CloudProjectsDatabase::RunMigrations()
{
   auto connection = mConnection->Acquire();

   auto getMigrationVersion =
      connection->CreateStatement("SELECT version FROM migration LIMIT 1");

   if (!getMigrationVersion)
      return false;

   auto versionResult = getMigrationVersion->Prepare().Run();

   if (!versionResult.IsOk())
      return false;

   int version = 0;

   for (auto row : versionResult)
   {
      if (!row.Get(0, version))
         return false;
      break;
   }

   const auto migrationsCount = std::size(migrations);

   if (version >= migrationsCount)
      return true;

   auto tx = connection->BeginTransaction("RunMigrations");

   for (int i = version; i < migrationsCount; ++i)
   {
      auto result = connection->Execute(migrations[i]);

      if (!result)
         return false;
   }

   auto updateVersion =
      connection->CreateStatement("UPDATE migration SET version = ?");

   if (!updateVersion)
      return false;

   if (!updateVersion->Prepare(migrationsCount).Run().IsOk())
      return false;

   return tx.Commit().IsOk();
}

bool cloud::audiocom::sync::CloudProjectsDatabase::DeleteProject(
   sqlite::SafeConnection::Lock& connection, std::string_view projectId)
{
   static const char* queries[] = {
      "DELETE FROM projects WHERE project_id = ?",
      "DELETE FROM block_hashes WHERE project_id = ?",
      "DELETE FROM pending_snapshots WHERE project_id = ?",
      "DELETE FROM pending_project_blobs WHERE project_id = ?",
      "DELETE FROM pending_project_blocks WHERE project_id = ?",
      "DELETE FROM project_users WHERE project_id = ?",
   };

   for (auto query : queries)
   {
      auto statement = connection->CreateStatement(query);

      if (!statement)
         return false;

      if (!statement->Prepare(projectId).Run().IsOk())
         return false;
   }

   return true;
}

} // namespace audacity::cloud::audiocom::sync