// Copyright 2024 The Chromium Authors
// Use of this source code is governed by a BSD-style license that can be
// found in the LICENSE file.

#include "content/browser/btm/btm_database_migrator.h"

#include "base/check_deref.h"
#include "base/strings/stringprintf.h"
#include "content/browser/btm/btm_database.h"
#include "sql/meta_table.h"
#include "sql/statement.h"

namespace content {

using internal::BtmDatabaseMigrator;

BtmDatabaseMigrator::BtmDatabaseMigrator(sql::Database* const db,
                                         sql::MetaTable* const meta_table)
    : db_(CHECK_DEREF(db)), meta_table_(CHECK_DEREF(meta_table)) {}

bool MigrateBtmSchemaToLatestVersion(sql::Database& db,
                                     sql::MetaTable& meta_table) {
  BtmDatabaseMigrator migrator(&db, &meta_table);

  for (int next_version = meta_table.GetVersionNumber() + 1;
       next_version <= BtmDatabase::kLatestSchemaVersion; next_version++) {
    switch (next_version) {
      case 2:
        if (!migrator.MigrateSchemaVersionFrom1To2()) {
          return false;
        }
        break;
      case 3:
        if (!migrator.MigrateSchemaVersionFrom2To3()) {
          return false;
        }
        break;
      case 4:
        if (!migrator.MigrateSchemaVersionFrom3To4()) {
          return false;
        }
        break;
      case 5:
        if (!migrator.MigrateSchemaVersionFrom4To5()) {
          return false;
        }
        break;
      case 6:
        if (!migrator.MigrateSchemaVersionFrom5To6()) {
          return false;
        }
        break;
      case 7:
        if (!migrator.MigrateSchemaVersionFrom6To7()) {
          return false;
        }
        break;
      case 8:
        if (!migrator.MigrateSchemaVersionFrom7To8()) {
          return false;
        }
        break;
      case 9:
        if (!migrator.MigrateSchemaVersionFrom8To9()) {
          return false;
        }
        break;
      case 10:
        if (!migrator.MigrateSchemaVersionFrom9To10()) {
          return false;
        }
        break;
      case 11:
        if (!migrator.MigrateSchemaVersionFrom10To11()) {
          return false;
        }
        break;
    }
  }
  return true;
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom1To2() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());
  // First make a new table that allows for null values in the timestamps
  // columns.
  static constexpr char kNewTableSql[] =  // clang-format off
      "CREATE TABLE new_bounces("
          "site TEXT PRIMARY KEY NOT NULL,"
          "first_site_storage_time INTEGER,"
          "last_site_storage_time INTEGER,"
          "first_user_interaction_time INTEGER,"
          "last_user_interaction_time INTEGER,"
          "first_stateful_bounce_time INTEGER,"
          "last_stateful_bounce_time INTEGER,"
          "first_stateless_bounce_time INTEGER,"
          "last_stateless_bounce_time INTEGER)";
  // clang-format on
  DCHECK(db_->IsSQLValid(kNewTableSql));
  if (!db_->Execute(kNewTableSql)) {
    return false;
  }

  static constexpr char kCopyEverythingSql[] =
      "INSERT INTO new_bounces "
      "SELECT * FROM bounces";
  DCHECK(db_->IsSQLValid(kCopyEverythingSql));
  if (!db_->Execute(kCopyEverythingSql)) {
    return false;
  }

  const std::array<std::string, 8> timestamp_columns{
      "first_site_storage_time",     "last_site_storage_time",
      "first_user_interaction_time", "last_user_interaction_time",
      "first_stateless_bounce_time", "last_stateless_bounce_time",
      "first_stateful_bounce_time",  "last_stateful_bounce_time"};

  for (const std::string& column : timestamp_columns) {
    std::string command = base::StringPrintf(
        "UPDATE new_bounces "
        "SET %s=NULL "
        "WHERE %s=0 ",
        column.c_str(), column.c_str());
    sql::Statement s_nullify(db_->GetUniqueStatement(command));

    if (!s_nullify.Run()) {
      return false;
    }
  }

  // Replace the first_stateless_bounce with the first bounce overall.
  // We have to first case on whether either of the bounce fields are NULL,
  // since MIN will return NULL if either are NULL.
  static constexpr char kReplaceFirstStatelessBounceSql[] =  // clang-format off
    "UPDATE new_bounces "
      "SET first_stateless_bounce_time = "
        "CASE "
          "WHEN first_stateful_bounce_time IS NULL "
            "THEN first_stateless_bounce_time "
          "WHEN first_stateless_bounce_time IS NULL "
            "THEN first_stateful_bounce_time "
          "ELSE MIN(first_stateful_bounce_time,first_stateless_bounce_time) "
        "END";
  // clang-format on
  DCHECK(db_->IsSQLValid(kReplaceFirstStatelessBounceSql));
  if (!db_->Execute(kReplaceFirstStatelessBounceSql)) {
    return false;
  }

  // Replace the last_stateless_bounce with the last bounce overall.
  // We have to first case on whether either of the bounce fields are NULL,
  // since MAX will return NULL if either are NULL.
  static constexpr char kReplaceLastStatelessBounceSql[] =  // clang-format off
      "UPDATE new_bounces "
        "SET last_stateless_bounce_time = "
          "CASE "
            "WHEN last_stateful_bounce_time IS NULL "
              "THEN last_stateless_bounce_time "
            "WHEN last_stateless_bounce_time IS NULL "
              "THEN last_stateful_bounce_time "
            "ELSE MAX(last_stateful_bounce_time,last_stateless_bounce_time) "
          "END";
  // clang-format on
  DCHECK(db_->IsSQLValid(kReplaceLastStatelessBounceSql));
  if (!db_->Execute(kReplaceLastStatelessBounceSql)) {
    return false;
  }
  // Rename this column to be reflect its new purpose.
  static constexpr char kRenameFirstStatelessBounceTimeSql[] =
      "ALTER TABLE new_bounces RENAME COLUMN first_stateless_bounce_time TO "
      "first_bounce_time";
  DCHECK(db_->IsSQLValid(kRenameFirstStatelessBounceTimeSql));
  if (!db_->Execute(kRenameFirstStatelessBounceTimeSql)) {
    return false;
  }

  // Rename this column to be reflect its new purpose.
  static constexpr char kRenameLastStatelessBounceTimeSql[] =
      "ALTER TABLE new_bounces RENAME COLUMN last_stateless_bounce_time TO "
      "last_bounce_time";
  if (!db_->Execute(kRenameLastStatelessBounceTimeSql)) {
    return false;
  }

  // Replace the old `bounces` table with the new one.
  static constexpr char kDropOldTableSql[] = "DROP TABLE bounces";
  if (!db_->Execute(kDropOldTableSql)) {
    return false;
  }

  static constexpr char kReplaceOldTable[] =
      "ALTER TABLE new_bounces RENAME TO bounces";
  if (!db_->Execute(kReplaceOldTable)) {
    return false;
  }

  return meta_table_->SetVersionNumber(2) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(2, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom2To3() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  return db_->Execute(
             "ALTER TABLE bounces ADD COLUMN first_web_authn_assertion_time "
             "INTEGER DEFAULT NULL") &&
         db_->Execute(
             "ALTER TABLE bounces ADD COLUMN last_web_authn_assertion_time "
             "INTEGER DEFAULT NULL") &&
         meta_table_->SetVersionNumber(3) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(3, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom3To4() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  static constexpr char kCreatePopupsTableSql[] =  // clang-format off
    "CREATE TABLE popups("
      "opener_site TEXT NOT NULL,"
      "popup_site TEXT NOT NULL,"
      "access_id INT64,"
      "last_popup_time INTEGER,"
      "PRIMARY KEY (`opener_site`,`popup_site`)"
    ")";
  // clang-format on
  DCHECK(db_->IsSQLValid(kCreatePopupsTableSql));

  return db_->Execute(kCreatePopupsTableSql) &&
         meta_table_->SetVersionNumber(4) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(4, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom4To5() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  return db_->Execute(
             "ALTER TABLE popups ADD COLUMN is_current_interaction "
             "BOOLEAN DEFAULT NULL") &&
         meta_table_->SetVersionNumber(5) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(5, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom5To6() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  static constexpr char kCreateConfigTableSql[] =  // clang-format off
    "CREATE TABLE config("
      "key TEXT NOT NULL,"
      "int_value INTEGER,"
      "PRIMARY KEY (`key`)"
    ")";
  // clang-format on
  DCHECK(db_->IsSQLValid(kCreateConfigTableSql));

  if (!db_->Execute(kCreateConfigTableSql)) {
    return false;
  }

  if (int result;
      meta_table_->GetValue(BtmDatabase::kPrepopulatedKey, &result)) {
    static constexpr char kInsertValueSql[] =
        "INSERT OR REPLACE INTO config(key,int_value) VALUES(?,?)";
    DCHECK(db_->IsSQLValid(kInsertValueSql));
    sql::Statement statement(db_->GetUniqueStatement(kInsertValueSql));
    statement.BindString(0, BtmDatabase::kPrepopulatedKey);
    statement.BindInt64(1, result);

    if (!statement.Run()) {
      return false;
    }

    if (!meta_table_->DeleteKey(BtmDatabase::kPrepopulatedKey)) {
      return false;
    }
  }

  return meta_table_->SetVersionNumber(6) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(6, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom6To7() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  CHECK(db_->HasActiveTransactions());

  static constexpr char kDeleteConfigSql[] = "DELETE FROM config WHERE key = ?";
  CHECK(db_->IsSQLValid(kDeleteConfigSql));
  sql::Statement statement(db_->GetUniqueStatement(kDeleteConfigSql));
  statement.BindString(0, BtmDatabase::kPrepopulatedKey);

  if (!statement.Run()) {
    return false;
  }

  return meta_table_->SetVersionNumber(7) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(6, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom7To8() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  return db_->Execute(
             "ALTER TABLE popups ADD COLUMN is_authentication_interaction "
             "BOOLEAN DEFAULT NULL") &&
         meta_table_->SetVersionNumber(8) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(8, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom8To9() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  static constexpr char kRenameFirstUserInteractionTimeSql[] =
      "ALTER TABLE bounces RENAME COLUMN first_user_interaction_time TO "
      "first_user_activation_time";
  DCHECK(db_->IsSQLValid(kRenameFirstUserInteractionTimeSql));
  if (!db_->Execute(kRenameFirstUserInteractionTimeSql)) {
    return false;
  }

  static constexpr char kRenameLastUserInteractionTimeSql[] =
      "ALTER TABLE bounces RENAME COLUMN last_user_interaction_time TO "
      "last_user_activation_time";
  DCHECK(db_->IsSQLValid(kRenameLastUserInteractionTimeSql));
  if (!db_->Execute(kRenameLastUserInteractionTimeSql)) {
    return false;
  }

  return meta_table_->SetVersionNumber(9) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(9, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom9To10() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  static constexpr char kDropFirstSiteStorageTimeColumnSql[] =
      "ALTER TABLE bounces DROP COLUMN first_site_storage_time";
  DCHECK(db_->IsSQLValid(kDropFirstSiteStorageTimeColumnSql));
  if (!db_->Execute(kDropFirstSiteStorageTimeColumnSql)) {
    return false;
  }

  static constexpr char kDropLastSiteStorageTimeColumnSql[] =
      "ALTER TABLE bounces DROP COLUMN last_site_storage_time";
  DCHECK(db_->IsSQLValid(kDropLastSiteStorageTimeColumnSql));
  if (!db_->Execute(kDropLastSiteStorageTimeColumnSql)) {
    return false;
  }

  static constexpr char kDropFirstStatefulBounceTimeColumnSql[] =
      "ALTER TABLE bounces DROP COLUMN first_stateful_bounce_time";
  DCHECK(db_->IsSQLValid(kDropFirstStatefulBounceTimeColumnSql));
  if (!db_->Execute(kDropFirstStatefulBounceTimeColumnSql)) {
    return false;
  }

  static constexpr char kDropLastStatefulBounceTimeColumnSql[] =
      "ALTER TABLE bounces DROP COLUMN last_stateful_bounce_time";
  DCHECK(db_->IsSQLValid(kDropLastStatefulBounceTimeColumnSql));
  if (!db_->Execute(kDropLastStatefulBounceTimeColumnSql)) {
    return false;
  }

  return meta_table_->SetVersionNumber(10) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(10, BtmDatabase::kMinCompatibleSchemaVersion));
}

bool BtmDatabaseMigrator::MigrateSchemaVersionFrom10To11() {
  DCHECK_CALLED_ON_VALID_SEQUENCE(sequence_checker_);
  DCHECK(db_->HasActiveTransactions());

  static constexpr char kDeleteEmptyRowsSql[] =  //clang-format off
      "DELETE FROM bounces "
      "WHERE "
      "(first_user_activation_time IS NULL "
      "OR last_user_activation_time IS NULL)"
      "AND (first_bounce_time IS NULL OR last_bounce_time IS NULL)"
      "AND (first_web_authn_assertion_time IS NULL "
      "OR last_web_authn_assertion_time IS NULL)";
  //clang-format on
  DCHECK(db_->IsSQLValid(kDeleteEmptyRowsSql));
  if (!db_->Execute(kDeleteEmptyRowsSql)) {
    return false;
  }

  return meta_table_->SetVersionNumber(11) &&
         meta_table_->SetCompatibleVersionNumber(
             std::min(11, BtmDatabase::kMinCompatibleSchemaVersion));
}

}  // namespace content