import socket
import sys
import time
import xml.etree.ElementTree as ET
from datetime import datetime
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'
}

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

# ======================= 1. METRA DATEN HOLEN =======================
xml_request = '''<?xml version="1.0" encoding="utf-8"?>
<package>
  <header>
    <name>LockerELSNETInfo</name>
    <version>1.0</version>
  </header>
  <parameters>
    <item>00001</item>
  </parameters>
  <userdata>xyz</userdata>
</package>'''

frame = chr(2) + xml_request + chr(3)

print("Verbinde mit Metra-Gerät...")
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(20)

try:
    sock.connect((METRA_HOST, METRA_PORT))
    sock.sendall(frame.encode('utf-8'))
    print("✅ Verbindung hergestellt und Anfrage gesendet.")
except Exception as e:
    print(f"❌ Verbindungsfehler zu Metra: {e}")
    sys.exit(1)

response = b''
try:
    while True:
        data = sock.recv(8192)
        if not data:
            break
        response += data
except socket.timeout:
    pass
finally:
    sock.close()

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 None

    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,
                'usedcount': int(locker.find("usedcount").text or 0)
            })
        except:
            continue

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

# ======================= 2. LOKALE BUCHUNGEN =======================
print("Lade lokale Buchungen aus der Datenbank...")

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

query = """
    SELECT booking_id, locker_number, start_datetime, end_datetime,
           customer_id, guest_name, status, dummy_id, total_price
    FROM sonnenbahn_booking
    WHERE end_datetime >= NOW() - INTERVAL 60 DAY
      AND status IN ('pending', 'bezahlt', 'aktiv')
    ORDER BY locker_number, start_datetime
"""

cursor.execute(query)
local_bookings = cursor.fetchall()

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

# ======================= 3. ABGLEICH & AUSGABE =======================
print("=" * 120)
print("                     ABGLEICH METRA ↔ LOKALE DB")
print("=" * 120)

# === 1. Alle Metra-Buchungen anzeigen ===
print("\n1. BUCHUNGEN IN METRA:")
for locker_num in sorted(metra_bookings.keys()):
    for booking in metra_bookings[locker_num]:
        cards_str = ", ".join(booking['cards']) if booking['cards'] else "Keine"
        
        print(f"Schrank {locker_num:3d} | Metra-ID: {booking['metra_id']}")
        print(f"   Zeitraum : {booking['start'].strftime('%Y-%m-%d %H:%M')} – {booking['end'].strftime('%Y-%m-%d %H:%M')}")
        print(f"   Karten   : {cards_str}")
        print(f"   Used     : {booking['usedcount']}")

        # Prüfen ob in lokaler DB
        found = any(
            lb['locker_number'] == locker_num and
            not (lb['end_datetime'] <= booking['start'] or lb['start_datetime'] >= booking['end'])
            for lb in local_bookings
        )
        print("   → Status : " + ("✅ In lokaler DB" if found else "❌ FEHLT in lokaler DB"))
        print("-" * 100)

# === 2. Alle lokalen Buchungen anzeigen ===
print("\n2. BUCHUNGEN IN LOKALER DB:")
for lb in local_bookings:
    locker_num = lb['locker_number']
    guest = lb['guest_name'] or lb['customer_id'] or "Unbekannt"
   
    print(f"Schrank {locker_num:3d} | Buchung ID: {lb['booking_id']} | Gast: {guest}")
    print(f"   Zeitraum : {lb['start_datetime']} – {lb['end_datetime']}")
    print(f"   Status   : {lb['status']} | Dummy-ID: {lb['dummy_id'] or '—'}")

    # Prüfen ob in Metra vorhanden
    found_in_metra = False
    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
                break

    print("   → Status : " + ("✅ In Metra vorhanden" if found_in_metra else "❌ FEHLT in Metra"))
    print("-" * 100)

cursor.close()
conn.close()
print("\nAbgleich abgeschlossen.")