// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
// Mobius Forensic Toolkit
// Copyright (C) 2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019 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 = 3;

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Create database tables (v03)
//! \param db Case database object
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
static void
case_schema_v03 (mobius::database::database db)
{
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // 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)");

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'cookie'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS cookie ("
                   "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
              "item_uid INTEGER,"
                  "name TEXT NOT NULL,"
                 "value TEXT NOT NULL,"
                "domain TEXT,"
         "creation_time DATETIME,"
       "expiration_time DATETIME,"
      "last_access_time DATETIME,"
         "evidence_path TEXT,"
            "is_deleted BOOLEAN,"
          "is_encrypted BOOLEAN,"
           "profile_uid INTEGER,"
    "FOREIGN KEY (item_uid) REFERENCES item (uid) ON DELETE CASCADE,"
    "FOREIGN KEY (profile_uid) REFERENCES profile (uid) ON DELETE SET NULL);"
    );

  db.execute (
    "CREATE INDEX IF NOT EXISTS idx_cookie "
    "ON cookie (item_uid)");
}

} // namespace

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Create database tables and indexes
//! \param db Case database object
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
void
case_schema (mobius::database::database db)
{
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // create table 'meta'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS meta ("
                               "key TEXT PRIMARY KEY,"
                             "value TEXT NOT NULL"
    ");");

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

  if (stmt.fetch_row ())
    {
      int version = stmt.get_column_int (0);

      if (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 ();
    }

  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  // 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,"
    "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 'password'
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  db.execute (
    "CREATE TABLE IF NOT EXISTS password ("
              "uid INTEGER PRIMARY KEY AUTOINCREMENT,"
         "item_uid INTEGER,"
             "type TEXT NOT NULL,"
            "value TEXT NOT NULL,"
      "description TEXT,"
    "FOREIGN KEY (item_uid) REFERENCES item (uid) ON DELETE CASCADE);"
    );

  db.execute (
    "CREATE INDEX IF NOT EXISTS idx_password "
    "ON password (item_uid)");

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

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_password_attribute "
    "ON password_attribute (password_uid, id)");

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

  db.execute (
    "CREATE INDEX IF NOT EXISTS idx_password_hash "
    "ON password_hash (item_uid)");

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

  db.execute (
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_password_hash_attribute "
    "ON password_hash_attribute (password_hash_uid, id)");
  
  case_schema_v03 (db);
}

} // namespace model
} // namespace mobius
