#!/usr/bin/env python3
##%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%##
# Android SMS Extractor                      [Thomas Lange <code@nerdmind.de>] #
##%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%##
#                                                                              #
# Extract all SMS from the SQLite database of the com.android.messaging app as #
# simple plain text sorted by conversation to stdout.                          #
#                                                                              #
# USAGE INSTRUCTIONS (tested with SMS app in LineageOS 14.1 and 17.1):         #
# 1. Enable USB debugging in the developer options of your Android.            #
# 2. Enable "Root debugging" in the developer options of your Android.         #
# 3. Connect your Android to your PC and (re)start ADB in root mode:           #
#    $ adb root                                                                #
# 4. Pull the corresponding database file to your PC:                          #
#    $ adb pull /data/data/com.android.messaging/databases/bugle_db .          #
# 5. Pass the path to the extracted database file to this script:              #
#    $ android-sms-extractor bugle_db                                          #
#                                                                              #
##%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%##

import os
import sqlite3
from datetime import datetime
from argparse import ArgumentParser

#===============================================================================
# Initialize argument parser
#===============================================================================
parser = ArgumentParser(prog="android-sms-extractor",
	description="Android SMS Extractor")

parser.add_argument('db_file', action='store',
	help="Path to the SQLite database file")

parser.add_argument('--datetime-format', action='store', dest='dt_format',
	help="Set datetime (strftime) format for text output (default: %(default)s)",
	default="%Y-%m-%d %H:%M:%S")

#===============================================================================
# Parse arguments
#===============================================================================
args = parser.parse_args()

#===============================================================================
# Check if db_file argument is a file
#===============================================================================
if not os.path.isfile(args.db_file):
	exit("Could not open file »{}«".format(args.db_file))

#===============================================================================
# Establish connection to SQLite database
#===============================================================================
db_conn = sqlite3.connect(args.db_file)

# Get a cursor to the SQLite database connection
c = db_conn.cursor()

#===============================================================================
# Query and loop through all SMS conversations
#===============================================================================
c.execute("SELECT conversations._id, conversations.name, \
	conversations.participant_normalized_destination \
	FROM conversations WHERE latest_message_id NOT NULL ORDER BY name")

for conversation in c.fetchall():
	data_id = conversation[0]
	data_name = conversation[1]
	data_dest = conversation[2]

	print("==================================================")
	print("{} [{}]".format(data_name, data_dest))
	print("==================================================")

	# Query and loop through all messages in this conversation
	c.execute("SELECT messages._id, parts.text, parts.timestamp, \
		participants.sim_slot_id FROM messages \
		LEFT JOIN parts ON messages._id = parts.message_id \
		LEFT JOIN participants ON messages.sender_id = participants._id \
		WHERE messages.conversation_id = ? ORDER BY parts.timestamp", (data_id,))

	for result in c.fetchall():
		msg_id = result[0]
		msg_text = result[1]
		msg_time = result[2]

		# Remove the last 3 digits (milliseconds) from timestamp
		msg_time = int(str(msg_time)[:-3])
		msg_time_formatted = datetime.fromtimestamp(msg_time).strftime(args.dt_format)

		sim_slot_id = result[3]

		# A sim_slot_id equal to 0 indicates that the SMS was sent from the device
		if sim_slot_id == 0:
			msg_direction = "===>"
		else:
			msg_direction = "<---"

		print("[{} {}]:\n{}\n".format(
			msg_time_formatted, msg_direction, msg_text))