import socket
import sys
import time
import xml.etree.ElementTree as ET
from datetime import datetime, timedelta
import mariadb as mysql
from collections import defaultdict

# ========================= KONFIGURATION =========================
METRA_HOST = '192.168.16.100'
METRA_PORT = 6771
LOG_FILE = "/var/www/html/metra/python/log.txt"

DB_CONFIG = {
    'host': 'localhost',
    'user': 'metra',
    'password': 'DB-2026!!',
    'database': 'skidepot'
}

MAX_AGE_HOURS = 10000  # Zum Testen hoch, später auf 1 ändern

# ======================= SEND FUNCTION =======================
def send_to_metra(xml_payload, description="", lb=None):
    """Sendet XML an Metra und gibt Antwort zurück + Log bei Fehler"""
    print(f"\n → Sende XML ({description}):")
    print("-" * 80)
    print(xml_payload)
    print("-" * 80)

    frame = chr(2) + xml_payload + chr(3)
    sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sock.settimeout(25)

    try:
        sock.connect((METRA_HOST, METRA_PORT))
        sock.sendall(frame.encode('utf-8'))
        print(" → XML gesendet an Metra")

        response = b''
        while True:
            data = sock.recv(8192)
            if not data:
                break
            response += data
            if b'</package>' in response:
                break

        response_str = response.decode('utf-8', errors='ignore').strip()
        if response_str.startswith(chr(2)): response_str = response_str[1:]
        if response_str.endswith(chr(3)): response_str = response_str[:-1]

        print(f" → Antwort von Metra: {response_str}")

        if "<result>0</result>" not in response_str:
            log_error(lb, description, response_str)

        return response_str

    except Exception as e:
        print(f" → Fehler beim Senden: {e}")
        log_error(lb, description, f"Exception: {e}")
        return None
    finally:
        sock.close()


def log_error(lb, action, metra_response):
    """Schreibt Fehler in log.txt übersichtlich"""
    with open(LOG_FILE, "a", encoding="utf-8") as f:
        f.write("=" * 100 + "\n")
        f.write(f"FEHLER bei Aktion: {action}\n")
        f.write(f"Zeit: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        
        if lb:
            f.write(f"Buchung ID: {lb['booking_id']} | Schrank: {lb['locker_number']}\n")
            f.write(f"Gast: {lb['guest_name'] or lb['customer_id'] or 'Unbekannt'}\n")
            f.write(f"Zeitraum: {lb['start_datetime']} – {lb['end_datetime']}\n")
            
            local_cards = []
            if lb.get('card_ID'):
                local_cards.extend([c.strip() for c in lb['card_ID'].split(';') if c.strip()])
            if lb.get('dummy_id'):
                local_cards.append(lb['dummy_id'])
            f.write(f"Cards (lokal): {', '.join(local_cards) if local_cards else 'Keine'}\n")

        f.write(f"Metra Antwort: {metra_response}\n")
        f.write("=" * 100 + "\n\n")
    print(" → FEHLER ins Log geschrieben (/var/www/html/metra/python/log.txt)")


# ========================= MAIN =========================
print(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] Starte Synchronisation: Lokale DB → Metra\n")

# ======================= 1. LOKALE BUCHUNGEN HOLEN =======================
print("Lade lokale Buchungen der letzten Stunden...")

try:
    conn = mysql.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)

    age_limit = datetime.now() - timedelta(hours=MAX_AGE_HOURS)

    # WICHTIG: age_limit als String übergeben → verhindert Zeitzonen-Probleme bei mariadb
    query = """
        SELECT booking_id, locker_number, start_datetime, end_datetime,
               customer_id, guest_name, status, dummy_id, card_ID, last_change
        FROM sonnenbahn_booking
        WHERE last_change >= %s
          AND status IN ('pending', 'bezahlt', 'aktiv')
        ORDER BY locker_number, start_datetime
    """

    cursor.execute(query, (age_limit.strftime('%Y-%m-%d %H:%M:%S'),))
    local_bookings = cursor.fetchall()

    print(f"✅ {len(local_bookings)} lokale Buchungen gefunden (letzte {MAX_AGE_HOURS} Stunden).\n")

except mysql.Error as e:
    print(f"❌ Fehler bei der Datenbankverbindung: {e}")
    sys.exit(1)

# ======================= 2. METRA DATEN HOLEN =======================
print("Verbinde mit Metra-Gerät...")

xml_request = '''<?xml version="1.0" encoding="utf-8"?>
<package>
  <header>
    <name>LockerELSNETInfo</name>
    <version>1.0</version>
  </header>
  <parameters>
    <item>W001</item>
  </parameters>
  <userdata>xyz</userdata>
</package>'''

frame = chr(2) + xml_request + chr(3)
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(10)
response = b''
start_time = time.time()
last_data_time = start_time

try:
    sock.connect((METRA_HOST, METRA_PORT))
    print("✅ Verbindung hergestellt und Anfrage gesendet.")
    sock.sendall(frame.encode('utf-8'))
    print("Warte auf Antwort von Metra...")

    while True:
        try:
            data = sock.recv(16384)
            if data:
                response += data
                last_data_time = time.time()
            else:
                break
        except socket.timeout:
            if time.time() - last_data_time > 12:
                break
            continue
        if time.time() - start_time > 35:
            break

except Exception as e:
    print(f"❌ Fehler bei Verbindung/Empfang: {e}")
    sys.exit(1)
finally:
    sock.close()

print(f"Antwort empfangen ({len(response)} Bytes).")
response_str = response.decode('utf-8', errors='ignore').strip()

if response_str.startswith(chr(2)):
    response_str = response_str[1:]
if response_str.endswith(chr(3)):
    response_str = response_str[:-1]

try:
    root = ET.fromstring(response_str)
except Exception as e:
    print(f"❌ XML-Parsing-Fehler: {e}")
    sys.exit(1)

# Metra-Buchungen verarbeiten
metra_bookings = defaultdict(list)
for locker in root.findall(".//locker"):
    number_str = locker.find("number").text
    if not number_str or not number_str.isdigit():
        continue

    locker_number = int(number_str)
    metra_id = locker.find("id").text if locker.find("id") is not None else "Keine ID"

    datetimefrom = locker.find("datetimefrom")
    datetimeto = locker.find("datetimeto")
    cards = [card.text.strip() for card in locker.findall(".//card") if card.text]

    if datetimefrom is not None and datetimeto is not None and datetimefrom.text and datetimeto.text:
        try:
            start = datetime.fromisoformat(datetimefrom.text.replace("T", " "))
            end = datetime.fromisoformat(datetimeto.text.replace("T", " "))
            metra_bookings[locker_number].append({
                'metra_id': metra_id,
                'start': start,
                'end': end,
                'cards': cards
            })
        except:
            continue

print(f"✅ {sum(len(v) for v in metra_bookings.values())} Buchungen von Metra geladen.\n")

# ======================= 3. ABGLEICH & AUTOMATISCHE AKTIONEN =======================
print("=" * 130)
print(" SYNCHRONISATION: Lokale DB → Metra")
print("=" * 130 + "\n")

for lb in local_bookings:
    locker_num = lb['locker_number']
    booking_id = lb['booking_id']
    guest = lb['guest_name'] or lb['customer_id'] or "Unbekannt"

    local_cards = []
    if lb.get('card_ID'):
        local_cards.extend([c.strip() for c in lb['card_ID'].split(';') if c.strip()])
    if lb.get('dummy_id'):
        local_cards.append(lb['dummy_id'])

    print(f"Buchung {booking_id} | Schrank {locker_num} | Gast: {guest}")
    print(f" Zeitraum : {lb['start_datetime']} – {lb['end_datetime']}")
    print(f" Cards : {', '.join(local_cards) if local_cards else 'Keine'}")

    # Vergleichslogik
    found_in_metra = False
    metra_cards = []
    metra_internal_id = None

    if locker_num in metra_bookings:
        local_start_date = lb['start_datetime'].date()
        local_end_date = lb['end_datetime'].date()

        for mb in metra_bookings[locker_num]:
            metra_start_date = mb['start'].date()
            metra_end_date = mb['end'].date()

            if local_start_date == metra_start_date and local_end_date == metra_end_date:
                found_in_metra = True
                metra_cards = mb['cards']
                metra_internal_id = mb['metra_id']
                break

    if not found_in_metra:
        print(" → **AKTION**: Lokaler Eintrag fehlt in Metra → wird automatisch angelegt")
        cards_xml = "".join(f" <card>{card}</card>\n" for card in local_cards)

        sale_xml = f'''<?xml version="1.0" encoding="utf-8"?>
<package>
  <header>
    <name>LockerELSNETSale</name>
    <version>1.0</version>
  </header>
  <parameters>
    <item>W001</item>
    <locker>{locker_num}</locker>
    <name>{guest}</name>
    <cards>
{cards_xml} </cards>
    <datetimefrom>{lb['start_datetime'].strftime('%Y-%m-%dT%H:%M:%S')}</datetimefrom>
    <datetimeto>{lb['end_datetime'].strftime('%Y-%m-%dT%H:%M:%S')}</datetimeto>
  </parameters>
  <userdata>System</userdata>
</package>'''

        send_to_metra(sale_xml, description="Neue Buchung anlegen", lb=lb)
        print("")
        continue

    # Card-Vergleich → Update bei Unterschied
    if set(local_cards) != set(metra_cards):
        print(" → **AKTION**: Card-Einträge stimmen nicht überein → Update wird durchgeführt")

        # Alte Buchung löschen
        delete_xml = f'''<?xml version="1.0" encoding="utf-8"?>
<package>
  <header>
    <name>LockerELSNETFree</name>
    <version>1.0</version>
  </header>
  <parameters>
    <id>{metra_internal_id}</id>
  </parameters>
  <userdata>System</userdata>
</package>'''

        print(f" → Lösche alte Buchung (Metra-ID: {metra_internal_id})")
        send_to_metra(delete_xml, description="Alte Buchung löschen", lb=lb)

        # Neue Buchung anlegen
        cards_xml = "".join(f" <card>{card}</card>\n" for card in local_cards)
        sale_xml = f'''<?xml version="1.0" encoding="utf-8"?>
<package>
  <header>
    <name>LockerELSNETSale</name>
    <version>1.0</version>
  </header>
  <parameters>
    <item>W001</item>
    <locker>{locker_num}</locker>
    <name>{guest}</name>
    <cards>
{cards_xml} </cards>
    <datetimefrom>{lb['start_datetime'].strftime('%Y-%m-%dT%H:%M:%S')}</datetimefrom>
    <datetimeto>{lb['end_datetime'].strftime('%Y-%m-%dT%H:%M:%S')}</datetimeto>
  </parameters>
  <userdata>System</userdata>
</package>'''

        print(" → Lege neue Buchung mit aktuellen Cards an")
        send_to_metra(sale_xml, description="Neue Buchung anlegen", lb=lb)
        print("")
        continue

    print(f" → Alles stimmt überein → Keine Aktion nötig (Metra-ID: {metra_internal_id})\n")

# === 4. BUCHUNGEN NUR IN METRA (LÖSCHEN) ===
print("4. BUCHUNGEN NUR IN METRA (fehlen in lokaler DB → werden gelöscht):")
metra_only_count = 0

for locker_num in sorted(metra_bookings.keys()):
    for mb in metra_bookings[locker_num]:
        found = False
        for lb in local_bookings:
            if lb['locker_number'] == locker_num:
                if (lb['start_datetime'].date() == mb['start'].date() and
                    lb['end_datetime'].date() == mb['end'].date()):
                    found = True
                    break
        if not found:
            metra_only_count += 1
            cards_str = ", ".join(mb['cards']) if mb['cards'] else "Keine"
            metra_id = mb['metra_id']

            print(f"Schrank {locker_num:3d} | Metra-ID: {metra_id}")
            print(f" Zeitraum : {mb['start'].strftime('%Y-%m-%d %H:%M')} – {mb['end'].strftime('%Y-%m-%d %H:%M')}")
            print(f" Karten : {cards_str}")
            print(" → **AKTION**: Eintrag existiert nur in Metra → wird gelöscht")

            delete_xml = f'''<?xml version="1.0" encoding="utf-8"?>
<package>
  <header>
    <name>LockerELSNETFree</name>
    <version>1.0</version>
  </header>
  <parameters>
    <id>{metra_id}</id>
  </parameters>
  <userdata>System</userdata>
</package>'''

            send_to_metra(delete_xml, description="Löschen nur in Metra vorhandener Buchung")
            print("")

if metra_only_count == 0:
    print("✅ Keine Einträge gefunden, die nur in Metra existieren.\n")

print("=" * 130)
print("Synchronisation abgeschlossen.")

# Aufräumen
cursor.close()
conn.close()