// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
// Mobius Forensic Toolkit
// Copyright (C) 2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022 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>
#include <mobius/database/meta_table.h>
#include <mobius/pod/data.h>
#include <mobius/pod/list.h>
#include <mobius/pod/map.h>
#include <mobius/string_functions.h>

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

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
// 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
 * 
 *      7       Remove column item.metadata
 *              attribute.value modified from TEXT to BLOB
 * 
 *      8       Item attributes converted from bytes to string (Python 3)
 */

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Upgrade schema to v07
//! \param db Case database object
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
static void
_case_schema_upgrade_v07 (mobius::database::database db)
{
  // Remove column item.metadata
  if (db.table_has_column ("item", "metadata"))
    db.execute ("ALTER TABLE item DROP COLUMN metadata");

  // Rename old table
  db.execute ("ALTER TABLE attribute RENAME TO attribute_old");

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

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

  // Insert data back
  auto stmt = db.new_statement (
                  "SELECT item_uid, id, value "
                    "FROM attribute_old");

  while (stmt.fetch_row ())
    {
      auto item_uid = stmt.get_column_int64 (0);
      auto id = stmt.get_column_string (1);
      auto value = stmt.get_column_string (2);

      if (!mobius::string::startswith (value, "\x09JSON\x09"))
        {
          auto insert_stmt = db.new_statement ("INSERT INTO attribute VALUES (NULL, ?, ?, ?)");
          insert_stmt.bind (1, item_uid);
          insert_stmt.bind (2, id);
          insert_stmt.bind (3, mobius::pod::data (value));
          insert_stmt.execute ();
        }
    }

  // Drop old table
  db.execute ("DROP TABLE attribute_old");
}

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Convert attribute from bytes to string
//! \param value Attribute value
//! \return Old/New attribute value
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
static mobius::pod::data
_convert_attr (const mobius::pod::data& value)
{
  if (value.is_bytearray ())
    return mobius::pod::data (mobius::bytearray (value).to_string ());

  else if (value.is_list ())
    {
      mobius::pod::list l;

      for (const auto& v : mobius::pod::list (value))
        l.append (_convert_attr (v));
      
      return l;
    }
    
  else if (value.is_map ())
    {
      mobius::pod::map m;

      for (const auto& p : mobius::pod::map (value))
        m.set (p.first, _convert_attr (p.second));

      return m;
    }

  return value;
}

// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
//! \brief Upgrade schema to v08
//! \param db Case database object
// =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
static void
_case_schema_upgrade_v08 (mobius::database::database db)
{
  auto stmt = db.new_statement (
                  "SELECT uid, value "
                    "FROM attribute");

  while (stmt.fetch_row ())
    {
      auto uid = stmt.get_column_int64 (0);
      auto value = stmt.get_column_pod (1);
      auto new_value = _convert_attr (value);
      
      if (value != new_value)
        {
          auto upd_stmt = db.new_statement (
                   "UPDATE attribute "
                      "SET value = ? "
                    "WHERE uid = ?");

          upd_stmt.bind (1, new_value);
          upd_stmt.bind (2, uid);
          upd_stmt.execute ();
        }
    }
}

} // 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 '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 BLOB,"
    "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
  // =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  mobius::database::meta_table meta_table (db);
  int version = meta_table.get_version ();

  if (version == 0)
    ; // newly created database

  else if (version < 7)
    _case_schema_upgrade_v07 (db);

  else if (version < 8)
    _case_schema_upgrade_v08 (db);

  if (version < SCHEMA_VERSION)
    meta_table.set_version (SCHEMA_VERSION);

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

} // namespace model
} // namespace mobius
