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

# ========================= KONFIGURATION =========================
METRA_HOST = '192.168.16.100'
METRA_PORT = 6771

DB_CONFIG = {
    'host': '192.168.60.90',
    'user': 'pi2',
    'password': 'Iwatch4you#',
    'database': 'skidepot'
}

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

# ======================= SEND FUNCTION =======================
def send_to_metra(xml_payload, description=""):
    """Sendet XML an Metra und gibt Antwort zurück"""
    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}")
        return response_str
        
    except Exception as e:
        print(f"   → Fehler beim Senden: {e}")
        return None
    finally:
        sock.close()

# ========================= 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...")

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

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

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,))
local_bookings = cursor.fetchall()

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

# ======================= 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}")
    sock.close()
    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 & AUTOMATISCHES UPDATE =======================
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['card_ID']:
        local_cards.extend([c.strip() for c in lb['card_ID'].split(';') if c.strip()])
    if lb['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'}")

    # Prüfen ob in Metra vorhanden
    found_in_metra = False
    metra_cards = []
    metra_internal_id = None

    if locker_num in metra_bookings:
        for mb in metra_bookings[locker_num]:
            if not (lb['end_datetime'] <= mb['start'] or lb['start_datetime'] >= mb['end']):
                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")

        # Dynamisches XML für LockerELSNETSale erstellen
        cards_xml = ""
        for card in local_cards:
            cards_xml += f"      <card>{card}</card>\n"

        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("   → Erstelltes XML zum Anlegen:")
        print("-" * 80)
        print(sale_xml)
        print("-" * 80)

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

    # === Card-Vergleich: Wenn nicht gleich → Löschen + Neu anlegen ===
    if set(local_cards) != set(metra_cards):
        print("   → **AKTION**: Card-Einträge stimmen nicht überein → Update wird durchgeführt")

        # 1. Alte Buchung in Metra 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")

        # 2. Neue Buchung mit lokalen Cards anlegen
        cards_xml = ""
        for card in local_cards:
            cards_xml += f"      <card>{card}</card>\n"

        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")
        print("   → Erstelltes XML zum Anlegen:")
        print("-" * 80)
        print(sale_xml)
        print("-" * 80)

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

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

# === 2. Metra-Buchungen, die NICHT in der lokalen DB sind ===
print("2. BUCHUNGEN NUR IN METRA (fehlen in lokaler DB):")
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 and not (lb['end_datetime'] <= mb['start'] or lb['start_datetime'] >= mb['end']):
                found = True
                break
        if not found:
            metra_only_count += 1
            cards_str = ", ".join(mb['cards']) if mb['cards'] else "Keine"
            print(f"Schrank {locker_num:3d} | Metra-ID: {mb['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 → Aktion wird ausgeführt\n")

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

print("=" * 130)
print("Synchronisation abgeschlossen.")
cursor.close()
conn.close()