<commit_before>"""

Author: <NAME> <<EMAIL> at g<EMAIL> dot <EMAIL>>

This module abstracts away a lot of the logic of dealing with the sqlite databases that contain text messages and
contacts on an iPhone.

For further exploring, I highly recommend using sqlitebrowser or DbVisualizer to connect to the databases directly. As
a convenience, you can find a visualization of both the address book DB and message DB adjacent to this file.

"""

import os
import pandas as pd
import re
import sqlite3

from IPython.display import display

BASE_DIR = '~/Library/Application Support/MobileSync/Backup'
MESSAGE_DB = '3d0d7e5fb2ce288813306e4d4636395e047a3d28'
ADDRESS_DB = '31bb7ba8914766d4ba40d6dfb6113c8b614be442'

# START SIMPLE HELPER METHODS
# --------------


# Get's the most recently updated directory within the passed directory.
def __get_latest_dir_in_dir(directory):
    directory_path = os.path.expanduser(directory)
    newest_path, newest_date = ('', -1)
    for relative_path in os.listdir(directory_path):
        full_path = os.path.join(directory_path, relative_path)
        if not os.path.isdir(full_path):  # Ignore non-directories.
            continue
        if os.path.getmtime(full_path) > newest_date:
            newest_path, newest_date = (full_path, os.path.getmtime(full_path))
    return newest_path


latest_sync_dir = __get_latest_dir_in_dir(BASE_DIR)
print 'Latest directory: {0}'.format(latest_sync_dir)
message_con = sqlite3.connect('{0}/{1}'.format(latest_sync_dir, MESSAGE_DB))
address_con = sqlite3.connect('{0}/{1}'.format(latest_sync_dir, ADDRESS_DB))


# Removes a leading one if it exists.
def __strip_initial_1_in_phone(row):
    phone_or_email = row.phone_or_email
    return re.sub(r'(\+?1)(\d{10})', r'\2', phone_or_email)


# --------------
# END SIMPLE HELPER METHODS


# Joins messages with the phone numbers/emails that sent them.  This handles group messages as well where one
# message could be sent to multiple people.
def __get_message_id_joined_to_phone_or_email():
    message_id_joined_to_phone_or_email = pd.read_sql_query('''
    SELECT
      handle.id AS phone_or_email, handle.service, handle.country,
      chat_message_join.message_id, chat.ROWID AS chat_id
    FROM handle, chat_handle_join, chat, chat_message_join
    WHERE
      handle.ROWID = chat_handle_join.handle_id
      AND chat_handle_join.chat_id = chat.ROWID
      AND chat.ROWID = chat_message_join.chat_id''', message_con)

    # Clean it up a bit.
    message_id_joined_to_phone_or_email['country'] = message_id_joined_to_phone_or_email['country'].str.lower()
    message_id_joined_to_phone_or_email['phone_or_email'] = message_id_joined_to_phone_or_email.apply(
        __strip_initial_1_in_phone, axis=1)
    return message_id_joined_to_phone_or_email


# Join the table that has message IDs and phones numbers/emails with the address book in order to get the full
# name and additional info.
def __get_address_joined_with_message_id(address_book):
    address_joined_with_message_id = (
        __get_message_id_joined_to_phone_or_email().merge(address_book, how='left', left_on='phone_or_email',
                                                          right_index=True, indicator='merge_chat_with_address')
    )
    address_joined_with_message_id = address_joined_with_message_id.drop(['ROWID'], axis=1)

    key_fields = ['message_id', 'chat_id', 'phone_or_email']
    duplicates = address_joined_with_message_id.duplicated(subset=key_fields,
                                                           keep=False)
    error_message = ("WARNING: tuple (message_id, chat_id, and phone_or_email) "
                     "do not form a composite key")
    if not sum(duplicates) == 0:
        print error_message

    return address_joined_with_message_id


def get_message_df():
    """<commit_msg>print duplicates, reformat<commit_after>"""

Author: <NAME> <<EMAIL>>

This module abstracts away a lot of the logic of dealing with the sqlite databases that contain text messages and
contacts on an iPhone.

For further exploring, I highly recommend using sqlitebrowser or DbVisualizer to connect to the databases directly. As
a convenience, you can find a visualization of both the address book DB and message DB adjacent to this file.

"""

import os
import pandas as pd
import re
import sqlite3

from IPython.display import display

BASE_DIR = '~/Library/Application Support/MobileSync/Backup'
MESSAGE_DB = '3d0d7e5fb2ce288813306e4d4636395e047a3d28'
ADDRESS_DB = '31bb7ba8914766d4ba40d6dfb6113c8b614be442'

# START SIMPLE HELPER METHODS
# --------------


# Get's the most recently updated directory within the passed directory.
def __get_latest_dir_in_dir(directory):
    directory_path = os.path.expanduser(directory)
    newest_path, newest_date = ('', -1)
    for relative_path in os.listdir(directory_path):
        full_path = os.path.join(directory_path, relative_path)
        if not os.path.isdir(full_path):  # Ignore non-directories.
            continue
        if os.path.getmtime(full_path) > newest_date:
            newest_path, newest_date = (full_path, os.path.getmtime(full_path))
    return newest_path


latest_sync_dir = __get_latest_dir_in_dir(BASE_DIR)
print 'Latest directory: {0}'.format(latest_sync_dir)
message_con = sqlite3.connect('{0}/{1}'.format(latest_sync_dir, MESSAGE_DB))
address_con = sqlite3.connect('{0}/{1}'.format(latest_sync_dir, ADDRESS_DB))


# Removes a leading one if it exists.
def __strip_initial_1_in_phone(row):
    phone_or_email = row.phone_or_email
    return re.sub(r'(\+?1)(\d{10})', r'\2', phone_or_email)


# --------------
# END SIMPLE HELPER METHODS


# Joins messages with the phone numbers/emails that sent them.  This handles group messages as well where one
# message could be sent to multiple people.
def __get_message_id_joined_to_phone_or_email():
    message_id_joined_to_phone_or_email = pd.read_sql_query('''
    SELECT
      handle.id AS phone_or_email, handle.service, handle.country,
      chat_message_join.message_id, chat.ROWID AS chat_id
    FROM handle, chat_handle_join, chat, chat_message_join
    WHERE
      handle.ROWID = chat_handle_join.handle_id
      AND chat_handle_join.chat_id = chat.ROWID
      AND chat.ROWID = chat_message_join.chat_id''', message_con)

    # Clean it up a bit.
    message_id_joined_to_phone_or_email['country'] = message_id_joined_to_phone_or_email['country'].str.lower()
    message_id_joined_to_phone_or_email['phone_or_email'] = message_id_joined_to_phone_or_email.apply(
        __strip_initial_1_in_phone, axis=1)
    return message_id_joined_to_phone_or_email


# Join the table that has message IDs and phones numbers/emails with the address book in order to get the full
# name and additional info.
def __get_address_joined_with_message_id(address_book):
    address_joined_with_message_id = (
        __get_message_id_joined_to_phone_or_email().merge(address_book, how='left', left_on='phone_or_email',
                                                          right_index=True, indicator='merge_chat_with_address')
    )
    address_joined_with_message_id = address_joined_with_message_id.drop(['ROWID'], axis=1)

    key_fields = ['message_id', 'chat_id', 'phone_or_email']
    duplicates = address_joined_with_message_id.duplicated(subset=key_fields,
                                                           keep=False)
    if not sum(duplicates) == 0:
        error_message = ('WARNING: tuple (message_id, chat_id, and phone_or_email) '
                         'do not form a composite key. There are %i duplicates.')
        print error_message % duplicates

    return address_joined_with_message_id


def get_message_df():
    """
{'fraction_non_alphanumeric': 0.08775351014040561, 'fraction_numerical': 0.01846073842953718, 'mean_word_length': 3.853627760252366, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 9, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1025347', 'n_tokens_mistral': 2563, 'n_tokens_neox': 2436, 'n_words': 593}