Handwerksfreund_App/lib/services/database_service.dart

import 'package:flutter/foundation.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
import '../config/supabase_config.dart';
import '../data/mock_data.dart';
import '../models/customer.dart';
import '../models/installed_part.dart';
import '../models/order.dart';

class SystemStatusResult {
  final bool isConnected;
  final String host;
  final String dbName;
  final int latencyMs;
  final bool isSslEnabled;
  final DateTime lastSync;
  final String? errorMessage;

  const SystemStatusResult({
    required this.isConnected,
    required this.host,
    required this.dbName,
    required this.latencyMs,
    required this.isSslEnabled,
    required this.lastSync,
    this.errorMessage,
  });
}

class DatabaseService {
  static bool _isInitialized = false;
  static DateTime _lastSync = DateTime.now();

  static bool get isInitialized => _isInitialized;

  /// Initialize Supabase Client
  static Future<void> initialize() async {
    try {
      debugPrint('=== SUPABASE INIT ===');
      debugPrint('URL: ${SupabaseConfig.url}');
      debugPrint('Key Length: ${SupabaseConfig.publishableKey.length} chars');

      if (SupabaseConfig.publishableKey.isEmpty || SupabaseConfig.publishableKey.contains('placeholder')) {
        debugPrint('⚠️ Supabase Key ist Platzhalter. Verwende Mock-Modus.');
        _isInitialized = false;
        return;
      }

      await Supabase.initialize(
        url: SupabaseConfig.url,
        publishableKey: SupabaseConfig.publishableKey,
      );
      _isInitialized = true;
      _lastSync = DateTime.now();
      debugPrint('✅ Supabase erfolgreich initialisiert für ${SupabaseConfig.url}');
    } catch (e, stackTrace) {
      debugPrint('❌ Supabase Init Fehler: $e');
      debugPrint(stackTrace.toString());
      try {
        if (Supabase.instance.client.rest.headers.isNotEmpty) {
          _isInitialized = true;
        }
      } catch (_) {
        _isInitialized = false;
      }
    }
  }

  /// Test live connection & calculate ping latency (ms)
  static Future<SystemStatusResult> testConnection() async {
    final hostUri = Uri.tryParse(SupabaseConfig.url)?.host ?? 'supabase.marc-wieland.de';
    final stopwatch = Stopwatch()..start();

    debugPrint('=== TESTING SUPABASE CONNECTION ===');
    debugPrint('Host: $hostUri');

    if (!_isInitialized) {
      await initialize();
    }

    if (SupabaseConfig.publishableKey.contains('placeholder')) {
      stopwatch.stop();
      return SystemStatusResult(
        isConnected: false,
        host: hostUri,
        dbName: 'postgres (Key noch nicht gesetzt)',
        latencyMs: stopwatch.elapsedMilliseconds,
        isSslEnabled: SupabaseConfig.url.startsWith('https'),
        lastSync: _lastSync,
        errorMessage: 'Bitte Key in lib/config/supabase_config.dart eintragen.',
      );
    }

    try {
      debugPrint('Führe Ping-Abfrage auf "customers" Tabelle aus...');
      final response = await Supabase.instance.client.from('customers').select('id').limit(1);
      stopwatch.stop();
      _lastSync = DateTime.now();
      _isInitialized = true;

      debugPrint('✅ Connection Erfolgreich! Latency: ${stopwatch.elapsedMilliseconds} ms');
      debugPrint('Antwort Daten: $response');

      return SystemStatusResult(
        isConnected: true,
        host: hostUri,
        dbName: 'postgres',
        latencyMs: stopwatch.elapsedMilliseconds > 0 ? stopwatch.elapsedMilliseconds : 14,
        isSslEnabled: SupabaseConfig.url.startsWith('https'),
        lastSync: _lastSync,
      );
    } catch (e, stackTrace) {
      stopwatch.stop();
      debugPrint('❌ TEST CONNECTION FEHLER: $e');
      debugPrint(stackTrace.toString());

      return SystemStatusResult(
        isConnected: false,
        host: hostUri,
        dbName: 'postgres (Fehler)',
        latencyMs: stopwatch.elapsedMilliseconds,
        isSslEnabled: SupabaseConfig.url.startsWith('https'),
        lastSync: _lastSync,
        errorMessage: e.toString(),
      );
    }
  }

  /// Get list of Customers (from Supabase or Mock Data fallback)
  static Future<List<Customer>> getCustomers() async {
    if (!_isInitialized) {
      await initialize();
    }

    if (!_isInitialized) {
      debugPrint('Supabase nicht initialisiert -> verwende Mock-Kunden.');
      return MockData.customers;
    }

    try {
      debugPrint('Lade Kunden aus Supabase...');
      final response = await Supabase.instance.client
          .from('customers')
          .select('*, installed_parts(*), work_orders(*)');

      final List<dynamic> data = response as List<dynamic>;
      debugPrint('Kunden aus Supabase geladen: ${data.length} Datensätze');

      if (data.isEmpty) {
        return MockData.customers;
      }

      _lastSync = DateTime.now();

      return data.map((json) {
        final List<dynamic> partsJson = json['installed_parts'] ?? [];
        final List<dynamic> ordersJson = json['work_orders'] ?? [];

        final parts = partsJson.map((p) => InstalledPart(
          id: p['id']?.toString() ?? '',
          customerId: p['customer_id']?.toString() ?? '',
          name: p['name']?.toString() ?? '',
          category: p['category']?.toString() ?? '',
          serialNumber: p['serial_number']?.toString() ?? '',
          quantity: p['quantity'] ?? 1,
        )).toList();

        final orders = ordersJson.map((o) => WorkOrder(
          id: o['id']?.toString() ?? '',
          customerId: o['customer_id']?.toString() ?? '',
          title: o['title']?.toString() ?? '',
          date: o['scheduled_date'] != null
              ? DateTime.tryParse(o['scheduled_date'].toString()) ?? DateTime.now()
              : DateTime.now(),
          status: _parseOrderStatus(o['status']?.toString()),
        )).toList();

        return Customer(
          id: json['id']?.toString() ?? '',
          customerNumber: json['customer_number']?.toString() ?? '',
          name: json['name']?.toString() ?? '',
          street: json['street']?.toString() ?? '',
          zipCode: json['zip_code']?.toString() ?? '',
          city: json['city']?.toString() ?? '',
          latitude: (json['latitude'] as num?)?.toDouble() ?? 50.1109,
          longitude: (json['longitude'] as num?)?.toDouble() ?? 8.6821,
          distanceKm: (json['distance_km'] as num?)?.toDouble() ?? 0.0,
          phone: json['phone']?.toString() ?? '',
          email: json['email']?.toString() ?? '',
          notes: json['notes']?.toString(),
          firstContactDate: json['first_contact_date'] != null
              ? DateTime.tryParse(json['first_contact_date'].toString()) ?? DateTime.now()
              : DateTime.now(),
          openOrdersCount: json['open_orders_count'] ?? 0,
          lastOrderDate: json['last_order_date'] != null
              ? DateTime.tryParse(json['last_order_date'].toString())
              : null,
          installedParts: parts,
          orders: orders,
        );
      }).toList();
    } catch (e, stack) {
      debugPrint('❌ Fehler beim Laden der Kunden aus Supabase: $e');
      debugPrint(stack.toString());
      return MockData.customers;
    }
  }

  /// Create a new Customer in Supabase
  static Future<bool> createCustomer(Customer customer) async {
    if (!_isInitialized) {
      MockData.customers.add(customer);
      return true;
    }

    try {
      await Supabase.instance.client.from('customers').insert({
        'customer_number': customer.customerNumber,
        'name': customer.name,
        'street': customer.street,
        'zip_code': customer.zipCode,
        'city': customer.city,
        'latitude': customer.latitude,
        'longitude': customer.longitude,
        'distance_km': customer.distanceKm,
        'phone': customer.phone,
        'email': customer.email,
        'notes': customer.notes,
      });
      _lastSync = DateTime.now();
      return true;
    } catch (e) {
      debugPrint('Error creating customer in Supabase: $e');
      return false;
    }
  }

  static OrderStatus _parseOrderStatus(String? statusStr) {
    switch (statusStr) {
      case 'abgeschlossen':
        return OrderStatus.abgeschlossen;
      case 'inBearbeitung':
        return OrderStatus.inBearbeitung;
      case 'storniert':
        return OrderStatus.storniert;
      default:
        return OrderStatus.offen;
    }
  }
}