# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# Mobius Forensic Toolkit
# Copyright (C) 2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020 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 json
import pymobius
import mobius
import message_parser

DEBUG = False

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# @brief Message types
# @see https://skpy.t.allofti.me/protocol/chat.html
# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
MESSAGE_TYPE_A = {
      2 : 'PlainText',
      4 : 'CONFERENCE_CALL_STARTED',
     10 : 'CHAT_MEMBER_ADDED',
     12 : 'CHAT_MEMBER_REMOVED',
     13 : 'CHAT_ENDED',
     30 : 'Event/CallStarted',
     39 : 'Event/CallEnded',
     50 : 'AUTHORIZATION_REQUESTED',
     51 : 'AUTHORIZATION_GIVEN',
     53 : 'BLOCKED',
     60 : 'EMOTICON_SENT',
     61 : 'RichText',
     63 : 'RichText/Contacts',
     64 : 'RichText/Sms',
     67 : 'VOICE_MSG_SENT',
     68 : 'RichText/Files',
     70 : 'VIDEO_MESSAGE',
    110 : 'BIRTHDATE',
    201 : 'RichText/UriObject',
    253 : 'VIDEO_SHARED',
}

SYSTEM_MESSAGES = {
      4 : 'Conference call started',
     10 : 'Chat member added',
     12 : 'Chat member removed',
     13 : 'Chat ended',
     30 : 'Call started',
     39 : 'Call ended',
     50 : 'Authorization requested',
     51 : 'Authorization given',
     53 : 'User blocked',
     63 : 'Contacts info sent',
     64 : 'SMS sent',
     68 : 'Files sent',
}

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# @brief Get participants from chatname
# chatname format = #participant1/$participant2;hash
# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
def get_participants_from_chatname (chatname):
  participants = set ()

  if chatname and chatname[0] == '#':
    pos = chatname.find ('/$')

    if pos != -1:
      participants.add (chatname[1:pos])
      
      pos2 = chatname.find (';', pos)
      if pos2 != -1:
        participants.add (chatname[pos+2:pos2])

  return participants

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

  return columns

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# @brief Skype Profile class
# @author Eduardo Aguiar
# @see https://arxiv.org/pdf/1603.05369.pdf
# @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.__schema_version = None
    self.__db = {}

    # set profile attributes
    self.name = folder.name
    self.path = folder.path.replace ('/', '\\')
    self.folder = folder

    # set data attributes
    self.__account_loaded = False
    self.__chat_messages_loaded = False
    self.__file_transfers_loaded = False
    self.__account = None
    self.__file_transfers = []
    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 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 file transfers
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_file_transfers (self):
    self.__file_transfers_loaded = True
    
    # check if table has transfer data columns
    db = self.__get_db ()
    if not db:
      return

    cols = get_table_columns (db, 'transfers')
    
    if 'status' not in cols:
      return

    # retrieve data
    account = self.get_account ()

    SQL_STATEMENT = '''
       SELECT partner_handle,
              partner_dispname,
              status,
              starttime,
              finishtime,
              filepath,
              filename,
              filesize,
              bytestransferred,
              type
         FROM transfers'''
  
    for row in db.execute (SQL_STATEMENT):
      ft = pymobius.Data ()
      ft.status = row[2]
      ft.start_time = mobius.datetime.new_datetime_from_unix_timestamp (row[3])
      ft.finish_time = mobius.datetime.new_datetime_from_unix_timestamp (row[4])
      ft.path = row[5]
      ft.filename = row[6]
      ft.size = row[7]
      ft.bytes_transferred = row[8]
      ft.type = row[9]
      
      # set from/to accounts
      if ft.type == 1:			# receive
        ft.from_skype_account = row[0]
        ft.from_skype_name = row[1]
        ft.to_skype_account = account.id
        ft.to_skype_name = account.name
        
      elif ft.type == 2:		# send
        ft.from_skype_account = account.id
        ft.from_skype_name = account.name
        ft.to_skype_account = row[0]
        ft.to_skype_name = row[1]

      self.__file_transfers.append (ft)

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Load chat messages
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_chat_messages (self):
    if not self.__chat_messages_loaded:          
      self.__load_chat_messages_from_main_db ()
      self.__chat_messages_loaded = True
    
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @brief Load chat messages from main.db
  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  def __load_chat_messages_from_main_db (self):
    db = self.__get_db ()

    if not db:
      return

    cols = get_table_columns (db, 'messages')
    if 'chatname' not in cols:
      return

    # get account, name from Messages table
    SQL_STATEMENT = '''
       SELECT DISTINCT author, from_dispname
                  FROM messages'''
     
    for row in db.execute (SQL_STATEMENT):
      account_id = row[0]
      account_name = row[1]
      self.__skype_names[account_id] = account_name

    # get conversation participants
    self.__participants = {}

    SQL_STATEMENT = '''
       SELECT convo_id, identity
         FROM participants'''

    for row in db.execute (SQL_STATEMENT):
      convo_id = row[0]
      identity = row[1]
      self.__participants.setdefault (convo_id, set ()).add (identity)

    # get messages
    SQL_STATEMENT = '''
       SELECT chatname,
              timestamp,
              author,
              from_dispname,
              body_xml,
              chatmsg_status,
              chatmsg_type,
              type,
              convo_id
         FROM messages'''
  
    for idx, row in enumerate (db.execute (SQL_STATEMENT)):
      message = pymobius.Data ()
      message.id = row[0]
      message.chatname = row[0]	# @deprecated
      message.timestamp = mobius.datetime.new_datetime_from_unix_timestamp (row[1])
      message.sender_id = row[2]
      message.sender_name = row[3]
      message.status = row[5]
      message.chatmsg_type = row[6]
      message.raw_text = (row[4] or u'').rstrip ()
      message.type = MESSAGE_TYPE_A.get (row[7], 'Unknown type (%d)' % row[7])

      if row[7] not in MESSAGE_TYPE_A:
        mobius.core.log ('app.skype: unknown MESSAGE_TYPE_A %d. Timestamp: %s. Message: %s' % (row[7], message.timestamp, message.raw_text.encode ('utf-8')))

      # recipients
      convo_id = row[8]

      if convo_id == 0:
        recipients = get_participants_from_chatname (message.chatname)
      else:
        recipients = set (p for p in self.__participants.get (convo_id, []))

      recipients.discard (message.sender_id) # discard sender from recipients

      message.recipients = [ (account_id, self.__skype_names.get (account_id)) for account_id in recipients ]

      # text
      parser = message_parser.MessageParser (message.raw_text)

      text = SYSTEM_MESSAGES.get (message.type)

      if text:
        text = text.replace ('_', ' ')
        text = text.capitalize ()
        parser.add_element ({u'type' : u'system', u'text' : text})

      message.text = parser.parse ()

      # add message to list
      self.__chat_messages.append (message)

  # =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  # @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 ()

      # development only
      if DEBUG:
        shutil.copy (path, '/tmp/skype/%s-main.db' % self.name)

      # connect to db
      self.__db = sqlite3.connect (path)

      #f.set_handled ()

    return self.__db
