# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# 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/>.
# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
import os
import os.path
import tempfile
import sqlite3
import shutil
import pymobius
import mobius
import decoder_calls
import decoder_messages
import decoder_transfers

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# @brief Get column names from table
# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
def get_table_columns (db, table):
  columns = set ()
  
  if db:
    SQL_STATEMENT = 'PRAGMA TABLE_INFO (%s)' % table
  
    for row in db.execute (SQL_STATEMENT):
      columns.add (row[1])

  return columns

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# @brief Get database schema version
# @param db Database object
# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
def get_schema_version (db):
  version = None

  stmt = '''
       SELECT SQLiteSchemaVersion
         FROM AppSchemaVersion'''

  try:
    cursor = db.execute (stmt)
    row = cursor.fetchone ()
    
    if row:
      version = int (row[0])

  except Exception, e:
    mobius.core.logf ('WRN app.skype: %s' % str (e))

  return version

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# @brief Skype Profile class
# @author Eduardo Aguiar
# @see https://sqliteforensictoolkit.com/using-group_concat-to-amalgamate-the-results-of-queries/
# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
class Profile (object):

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Initialize object
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __init__ (self, folder, item):
    self.__folder = folder
    self.__item = item
    self.__db = {}

    # set profile attributes
    self.name = folder.name
    self.path = mobius.io.to_win_path (folder.path)
    self.folder = folder
    self.metadata = mobius.pod.map ()

    # set data attributes
    self.__account_loaded = False
    self.__calls_loaded = False
    self.__chat_messages_loaded = False
    self.__file_transfers_loaded = False
    self.__account = None
    self.__file_transfers = []
    self.__calls = []
    self.__chat_messages = []
    self.__skype_names = {}

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Get account
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def get_account (self):
    if not self.__account_loaded:
      self.__load_account ()

    return self.__account

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Get calls
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def get_calls (self):
    if not self.__calls_loaded:
      self.__load_calls ()

    return self.__calls

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Get chat messages
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def get_chat_messages (self):
    if not self.__chat_messages_loaded:
      self.__load_chat_messages ()

    return self.__chat_messages

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Get file transfers
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def get_file_transfers (self):
    if not self.__file_transfers_loaded:
      self.__load_file_transfers ()

    return self.__file_transfers

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Load account
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_account (self):
    db = self.__get_db ()

    cursor = db.execute ('''
       SELECT skypename,
              fullname
         FROM accounts
        WHERE id = 1''')

    row = cursor.fetchone ()

    if row:
      self.__account = pymobius.Data ()
      self.__account.id = row[0]
      self.__account.name = row[1]
      self.__account_loaded = True
      self.__skype_names[self.__account.id] = self.__account.name

    self.__account_loaded = True

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Load calls
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_calls (self):
    if not self.__calls_loaded:          
      db = self.__get_db ()

      if db:
        account = self.get_account ()
        self.__calls = decoder_calls.decode (db, account.id)

      self.__calls_loaded = True
    
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Load chat messages
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_chat_messages (self):
    if not self.__chat_messages_loaded: 
      db = self.__get_db ()
      cols = get_table_columns (db, 'messages')

      if 'chatname' in cols:
        self.__chat_messages, self.__skype_names = decoder_messages.decode (db)

      self.__chat_messages_loaded = True
    
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Load file transfers
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_file_transfers (self):
    if not self.__file_transfers_loaded:
      db = self.__get_db ()
      cols = get_table_columns (db, 'transfers')
    
      if 'status' in cols:
        account = self.get_account ()
        self.__file_transfers = decoder_transfers.decode (db, account)

      self.__file_transfers_loaded = True
    
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Get database files
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def get_database_files (self):
    files = []

    f = self.__folder.get_child_by_name ('main.db')
    if f and not f.is_reallocated ():
      db = self.__get_db ()
      db_schema_version = get_schema_version (db)
      files.append (('main.db', db_schema_version, f))

    return files

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Retrieve sqlite database
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __get_db (self):
    
    if not self.__db:
      f = self.__folder.get_child_by_name ('main.db')
      if not f:
        return

      reader = f.new_reader ()
      if not reader:
        return

      # create temporary .sqlite local file
      ext = os.path.splitext (f.name)[1]
      fd, path = tempfile.mkstemp (suffix=ext)

      fp = open (path, 'w')
      fp.write (reader.read ())
      fp.close ()

      # connect to db
      self.__db = sqlite3.connect (path)
      
      # set metadata
      self.metadata.set ('main.db_path', mobius.io.to_win_path (f.path))
      db_schema_version = get_schema_version (self.__db)

      if db_schema_version:
        self.metadata.set ('main.db_schema_version', db_schema_version)
        mobius.core.logf ('INF main.db schema version=%d' % db_schema_version)

      #f.set_handled ()

    return self.__db
