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'
}

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)

# ======================= 1. METRA DATEN HOLEN =======================
print(f"[{time.strftime('%Y-%m-%d %H:%M:%S')}] Hole aktuelle Reservierungen von Metra...")

sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(15)

try:
    sock.connect((METRA_HOST, METRA_PORT))
    sock.sendall(frame.encode('utf-8'))
except Exception as e:
    print(f"Metra-Verbindungsfehler: {e}")
    sys.exit(1)

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

response_str = response.decode('utf-8', errors='ignore')
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 gruppieren: Schranknummer → Liste von Buchungen
metra_bookings = defaultdict(list)

for locker in root.findall(".//locker"):
    number_str = locker.find("number").text if locker.find("number") is not None else None
    if not number_str or not number_str.isdigit():
        continue
    number = int(number_str)

    # Sicheres Auslesen der Metra-ID
    metra_id_elem = locker.find("id")
    metra_id = metra_id_elem.text if metra_id_elem is not None and metra_id_elem.text else "Keine ID"

    # Sicheres Auslesen der Zeiträume
    from_elem = locker.find("datetimefrom")
    to_elem   = locker.find("datetimeto")

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

print(f"Metra: {sum(len(v) for v in metra_bookings.values())} Reservierungen gefunden.\n")

# ======================= 2. LOKALE BUCHUNGEN (letzte Stunde) =======================
print("Lade lokale Buchungen der letzten Stunde...")

try:
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)
except Exception as e:
    print(f"Datenbank-Verbindungsfehler: {e}")
    sys.exit(1)

one_hour_ago = datetime.now() - timedelta(hours=1)

query = """
    SELECT booking_id, locker_number, start_datetime, end_datetime, last_change, customer_id, guest_name
    FROM sonnenbahn_booking 
    WHERE last_change >= %s 
      AND status IN ('bezahlt', 'reserviert', 'pending')
    ORDER BY locker_number, start_datetime
"""

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

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

# ======================= 3. ABGLEICH =======================
print(f"{'='*110}")
print("   ABGLEICH: LOKALE BUCHUNG  ↔  METRA")
print(f"{'='*110}\n")

# 3.1 Lokale Buchungen prüfen
for lb in local_bookings:
    locker_num = lb['locker_number']
    local_start = lb['start_datetime']
    local_end   = lb['end_datetime']
    guest       = lb['guest_name'] or lb['customer_id'] or "Unbekannt"

    print(f"Buchung ID {lb['booking_id']} | Schrank {locker_num} | Gast: {guest}")
    print(f"   Lokaler Zeitraum: {local_start} – {local_end}")

    conflict_found = False
    if locker_num in metra_bookings:
        for mb in metra_bookings[locker_num]:
            if not (local_end <= mb['start'] or local_start >= mb['end']):
                conflict_found = True
                print(f"   → KONFLIKT mit Metra-ID {mb['metra_id']}")
                print(f"      Metra-Zeitraum: {mb['start']} – {mb['end']}")
                break

    if conflict_found:
        print("   → **SCHRANK IST BEREITS VERBUCHT IN METRA**\n")
    else:
        print("   → **Schrank ist FREI** in Metra\n")

# 3.2 Metra-Buchungen ohne lokalen Eintrag
print(f"{'-'*110}")
print("   METRA-BUCHUNGEN OHNE ENTSPRECHENDEN EINTRAG IN LOKALER DB")
print(f"{'-'*110}\n")

for number, mbookings in metra_bookings.items():
    for mb in mbookings:
        found = False
        for lb in local_bookings:
            if lb['locker_number'] == number and not (lb['end_datetime'] <= mb['start'] or lb['start_datetime'] >= mb['end']):
                found = True
                break
        if not found:
            print(f"Schrank {number:4} | Metra-ID: {mb['metra_id']}")
            print(f"   Metra-Zeitraum: {mb['start']} – {mb['end']}")
            print("   → **KEIN EINTRAG IN LOKALER DB** (versteckte Buchung!)\n")

cursor.close()
conn.close()

print("Abgleich abgeschlossen.")