// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
// Mobius Forensic Toolkit
// Copyright (C) 2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021 Eduardo Aguiar
//
// This program is free software; you can redistribute it and/or modify it
// under the terms of the GNU General Public License as published by the
// Free Software Foundation; either version 2, or (at your option) any later
// version.
//
// This program is distributed in the hope that it will be useful, but
// WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General
// Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program. If not, see <http://www.gnu.org/licenses/>.
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
#include <mobius/database/database.h>

namespace mobius
{
namespace model
{
namespace
{
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
// Constants
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
static constexpr int SCHEMA_VERSION = 6;

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
// History
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
/*
 * Version      Modifications
 * ---------------------------------------------------------------------------
 *       4      cookie.value modified from TEXT to BLOB
 * 
 *       5      New column item.metadata (BLOB)
 *              New table text_search
 * 
 *       6      New table evidence
 *              New table evidence_attribute
 *              Table text_search removed
 *              Table cookie removed
 *              Table password removed
 *              Table password_attribute removed
 *              Table password_hash removed
 *              Table password_hash_attribute removed
 */

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Upgrade schema to v05
//! \param db Case database object
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
static void
case_schema_upgrade_v05 (mobius::database::database db)
{
  db.execute ("ALTER TABLE item ADD COLUMN metadata BLOB NULL");
}

} // namespace

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Create database tables and indexes
//! \param db Case database object
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
void
case_schema (mobius::database::database db)
{
  db.execute ("PRAGMA foreign_keys = OFF;");
  auto transaction = db.new_transaction ();

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'meta'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS meta ("
                               "key TEXT PRIMARY KEY,"
                             "value TEXT NOT NULL"
    ");");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // set schema version
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  int current_version = 0;

  auto stmt = db.new_statement (
                "SELECT value "
                  "FROM meta "
                 "WHERE key = 'version'");

  if (stmt.fetch_row ())
    {
      current_version = stmt.get_column_int (0);

      if (current_version < SCHEMA_VERSION)
        {
          // update schema version
          stmt = db.new_statement (
                   "UPDATE meta "
                      "SET value = ? "
                    "WHERE key = 'version'");

          stmt.bind (1, SCHEMA_VERSION);
          stmt.execute ();
        }
    }

  else
    {
      // insert 'version' metadata
      stmt = db.new_statement (
               "INSERT INTO meta "
                    "VALUES ('version', ?)");

      stmt.bind (1, SCHEMA_VERSION);
      stmt.execute ();
      
      current_version = SCHEMA_VERSION;
    }

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'case'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS 'case' ("
                "uid INTEGER PRIMARY KEY,"
      "creation_time DATETIME NOT NULL);"
    );

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'item'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS item ("
                "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
         "parent_uid INTEGER,"
                "idx INTEGER NOT NULL,"
           "category TEXT NOT NULL,"
      "creation_time DATETIME NOT NULL,"
           "metadata BLOB NULL,"
    "FOREIGN KEY (parent_uid) REFERENCES item (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE INDEX IF NOT EXISTS idx_item "
           "ON item (parent_uid)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'attribute'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS attribute ("
           "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
      "item_uid INTEGER,"
            "id TEXT NOT NULL,"
         "value TEXT,"
    "FOREIGN KEY (item_uid) REFERENCES item (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_attribute "
           "ON attribute (item_uid, id)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'ant'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS ant ("
                      "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
                 "item_uid INTEGER,"
                       "id TEXT NOT NULL,"
                     "name TEXT,"
                  "version TEXT,"
      "last_execution_time DATETIME,"
    "FOREIGN KEY (item_uid) REFERENCES item (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_ant "
           "ON ant (item_uid, id)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'evidence'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS evidence ("
                      "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
                 "item_uid INTEGER,"
                     "type TEXT NOT NULL,"
    "FOREIGN KEY (item_uid) REFERENCES item (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE INDEX IF NOT EXISTS idx_evidence "
           "ON evidence (item_uid, type)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'evidence_attribute'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS evidence_attribute ("
              "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
     "evidence_uid INTEGER,"
               "id TEXT NOT NULL,"
            "value BLOB,"
       "FOREIGN KEY (evidence_uid) REFERENCES evidence (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_evidence_attribute "
           "ON evidence_attribute (evidence_uid, id)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'application'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS application ("
              "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
               "id TEXT NOT NULL,"
             "name TEXT NOT NULL);"
    );

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_application "
           "ON application (id)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'profile'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS profile ("
                   "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
              "item_uid INTEGER,"
                    "id TEXT,"
                  "path TEXT NOT NULL,"
              "username TEXT,"
         "creation_time DATETIME,"
       "application_uid INTEGER NOT NULL,"
    "FOREIGN KEY (item_uid) REFERENCES item (uid) ON DELETE CASCADE,"
    "FOREIGN KEY (application_uid) REFERENCES application (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_profile "
           "ON profile (item_uid, application_uid, path)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // upgrade database, if necessary
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  if (current_version < 5)
    case_schema_upgrade_v05 (db);

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // commit changes
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  transaction.commit ();
  db.execute ("PRAGMA foreign_keys = ON;");
}

} // namespace model
} // namespace mobius
