import subprocess
import csv
from datetime import datetime
from Controller import mysql_connect


def exchange (ip,name):

    exchange_commands = "Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn; Get-Mailbox | Select-Object PSComputerName, RunspaceId, PSShowComputerName, Database, UseDatabaseRetentionDefaults, RetainDeletedItemsUntilBackup, IsHierarchyReady, IsHierarchySyncEnabled,RetentionPolicy, ExchangeGuid, AdminDisplayVersion, ExchangeUserAccountControl, IsMailboxEnabled,ProhibitSendQuota, ProhibitSendReceiveQuota, RecoverableItemsQuota, RecoverableItemsWarningQuota,CalendarLoggingQuota, IsResource, IsLinked, IsShared, IsRootPublicFolderMailbox, LinkedMasterAccount, ResetPasswordOnNextLogon,ResourceType, RoomMailboxAccountEnabled, SamAccountName, ServerLegacyDN, UseDatabaseQuotaDefaults,IssueWarningQuota, RulesQuota, UserPrincipalName, RoleAssignmentPolicy, EffectivePublicFolderMailbox, SharingPolicy,ArchiveQuota, ArchiveWarningQuota, DisabledMailboxLocations, CalendarVersionStoreDisabled, AuditEnabled,AuditLogAgeLimit, WhenMailboxCreated, AccountDisabled, Alias, OrganizationalUnit, DisplayName,LegacyExchangeDN, MaxSendSize, MaxReceiveSize, EmailAddressPolicyEnabled, PrimarySmtpAddress,RecipientType, RecipientTypeDetails, WindowsEmailAddress, Identity, IsValid, ExchangeVersion,DistinguishedName, Guid, ObjectCategory, WhenChanged, WhenCreated, WhenChangedUTC, WhenCreatedUTC,OrganizationId, Id, OriginatingServer | Export-Csv -Path 'exuser.csv' -NoTypeInformation -Encoding UTF8"

    # PowerShell als Subprozess ausführen
    process = subprocess.Popen(["powershell", "-Command", exchange_commands], stdout=subprocess.PIPE, shell=True)

    # Warte auf den Abschluss des PowerShell-Prozesses
    process.communicate()

    # CSV-Datei einlesen und Feldnamen auslesen
    with open("exuser.csv", "r", encoding='utf-8-sig') as file:
        reader = csv.DictReader(file)
        fieldnames = reader.fieldnames

     # Tabelle erstellen, falls sie noch nicht existiert
    table_name = "Exchange-User"
    create_table_query = f"CREATE TABLE IF NOT EXISTS `{table_name}` (id INT AUTO_INCREMENT PRIMARY KEY, importdate INT(11), "
    for field in fieldnames:
        if "Id" == field:
             field = "ADUserPath"
        create_table_query += f"`{field}` TEXT, "
    create_table_query = create_table_query.rstrip(", ") + ")"
    create_table_query += " ROW_FORMAT=DYNAMIC"
    row_length = len(create_table_query)
    print(row_length)
    print(create_table_query)
    mysql_connect.create_database(create_table_query, name)

    with open("exuser.csv", "r", encoding='utf-8-sig') as file:
        reader = csv.reader(file)
        next(reader)
        for row in reader:
            print(row)
            if fieldnames[65] == 'Id':
                fieldnames[65] = "ADUserPath"
            print(fieldnames[65])
            row = [cell if cell.strip() else "-" for cell in row]
            unix_time = int(datetime.now().timestamp())
            row = [unix_time] + row

            # Führe das Einfüge-Query aus
            insert_query = f"INSERT INTO `{table_name}` (importdate, `{'`, `'.join(fieldnames)}`) VALUES (%s, {', '.join(['%s'] * len(fieldnames))})"
            mysql_connect.add_user(insert_query, name, row)