import subprocess
import re
import sys
import hashlib
import csv
import os
import pandas as pd
import time
from pathlib import Path
import mariadb

# connect to mariadb
try:
    conn = mariadb.connect(
        host= '192.168.100.186',
        port= 3306,
        user= 'archivepk',
        password= 'ArchivePK123!',
        database= 'ArchivePK'
    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB: {e}")
    sys.exit(1)

md5hashes = []
categories = []

cur = conn.cursor()
def begin_processing():
    cur.execute("TRUNCATE TABLE ArchivePK.CategoryBackup")
    conn.commit()
    md5hashes = []
    categories = []
    cur.execute("SELECT * FROM ArchivePK.APKs")
    for rows in cur:
        md5hashes.append(rows[5])
        categories.append(rows[8])
        print(rows[5])
        print(rows[8])

    for i in range(len(categories)):
        cur.execute(
            "INSERT INTO ArchivePK.CategoryBackup "
            "(MD5Hash, Category) "
            "VALUES (?, ?)",
            (md5hashes[i], categories[i])
        )
        conn.commit()


    cur.execute("TRUNCATE TABLE ArchivePK.APKs")
    conn.commit()



fields = ['Application Name', 'Minimum Android SDK Version', 'Application Version', 'MD5 Hash', 'HTTPPath']
info = []


erroredapks = []
totalapksprocessed = 0

def contains_multiple_words(s):
    return len(s.split()) > 1

def writeCSV():
    filename = "apk_information.csv"
    
    if (os.path.isfile(filename)):
        df = pd.read_csv(filename)
        with open(filename, 'a+') as file:
            csvwriter = csv.writer(file)
            duplicate = False
            
            for values in df['MD5 Hash'].values:
                if(values == info[-1]):
                    duplicate = True
                    break
            if(not duplicate):
                csvwriter.writerow(info)       
    else:
        with open(filename, 'w') as csvfile:
            csvwriter = csv.writer(csvfile)
            
            csvwriter.writerow(fields)
            csvwriter.writerow(info)

def writeDB():
        cur.execute(
            "INSERT INTO APKs "
            "(Name,HTTPPath,DownloadCount,MinimumSDKVersion,MD5Hash,AppVersion, Recommended, Category) "
            "VALUES (?,?,DEFAULT,?,?,?,DEFAULT,DEFAULT)",
            (info[0], info[4], info[1], info[3], info[2]))
        conn.commit()


def processApk(path):
    global totalapksprocessed
    global erroredapks
    totalapksprocessed += 1
    info.clear()
    try:
        p = subprocess.run("/usr/lib/android-sdk/build-tools/34.0.0/aapt d badging " + '"' + path + '"', shell=True, capture_output=True, check=True)
    except Exception as e:
        print("APK SCANNING FAILED!")
        print("SKIPPING APK..")
        print(e)
        erroredapks.append(path)
        return


    match = re.search(r"application-label:'([^']+)'", p.stdout.decode(encoding="utf-8"))
    if match:
        print("Application Name: " + match.group(1))
        info.append(match.group(1))
    else:
        info.append('null')

    match = re.search(r"sdkVersion:'([^']+)'", p.stdout.decode(encoding="utf-8"))
    if match:
        print("Minimum Android Version: " + match.group(1))
        info.append(match.group(1))
    elif info.append(match) == '':
        info.append(0)

    match = re.search(r"versionName='([^']+)'", p.stdout.decode(encoding="utf-8"))
    if match:
        print("Application Version: " + match.group(1))
        info.append(match.group(1))
    elif info.append(match) == '':
        info.append(0)




    with open(path, "rb") as file:
        b = file.read()
        res = hashlib.md5(b)
        print("MD5 Hash: " + res.hexdigest())
        info.append(res.hexdigest())

    info.append('https://archivepk.filipag.org/files/' + path.replace(" ", "%20"))
    print('HTTP Path: ' + 'https://archivepk.filipag.org/files/' + path.replace(" ", "%20"))
    
    writeCSV()
    writeDB()

if(os.path.isdir("APKs")):
    begin_processing()
    files = os.listdir("APKs")

    for p in Path("APKs").rglob('*'):
        if str(p.resolve()).endswith('.apk'):
            processApk(str(p))

    for x in range(10):
        print(" ")
    
    print("Total APKs Processed: " + str(totalapksprocessed))
    print("Sucessfully Processed APKs: " + str(totalapksprocessed - len(erroredapks)))
    print("Errored out APKs: " + str(len(erroredapks)))
    cur.execute(
        "DELETE FROM ArchivePK.APKs "
        "WHERE id IN ("
        "    SELECT id FROM ("
        "        SELECT id,"
        "               ROW_NUMBER() OVER (PARTITION BY MD5Hash ORDER BY id) AS rn"
        "        FROM ArchivePK.APKs"
        "    ) AS ranked"
        "    WHERE rn > 1"
        ");"
    )
    conn.commit()
    md5hashes = []
    categories = []
    cur.execute("SELECT * FROM ArchivePK.CategoryBackup")
    for row in cur:
        md5hashes.append(row[0])
        categories.append(row[1])
    for i in range(len(categories)):
        cur.execute(
            "UPDATE ArchivePK.APKs "
            "SET Category = (?) "
            "WHERE MD5Hash = (?)",
            (categories[i], md5hashes[i])
            )
        conn.commit()
    categories = []
    names = []
    cur.execute("SELECT Name, Category FROM ArchivePK.APKs WHERE Category <> 'None'")
    for row in cur:
        names.append(row[0])
        categories.append(row[1])
    for i in range(len(names)):
        cur.execute(
            "UPDATE ArchivePK.APKs "
            "SET Category = (?)"
            "WHERE Name = (?)",
            (categories[i],names[i])
        )
        conn.commit()

    conn.close()


    if(len(erroredapks) > 0):
        print("Do you wish to remove the errored apks? [Y/n]")
        userinp = input("")
        if str(userinp).lower() == '' or str(userinp).lower() == 'y':
            for files in erroredapks:
                os.remove(files)
        else:
            print("No changes have been done.")
         

else:
    os.mkdir("APKs")
    print("Please put all of your APKs into the 'APKs' folder that was created in the same directory as where you are running the file.")