using Lagerbestand.Data;
using Lagerbestand.Models;
using Microsoft.EntityFrameworkCore;

namespace Lagerbestand.Services;

/// <summary>
/// Service interface for managing tire stacks
/// </summary>
public interface ITireStackService
{
    Task<List<TireStack>> GetAllAsync(TireStackStatus? status = null);
    Task<List<TireStack>> GetAllAsync(TireStackStatus? status, bool? isInventory);
    Task<TireStack?> GetByIdAsync(Guid id);
    Task<TireStack?> GetByTicketNumberAsync(string ticketNumber);
    Task<List<TireStack>> GetByCustomerAsync(Guid customerId, TireStackStatus? status = null);
    Task<List<TireStack>> GetByCategoryAsync(Guid catalogItemId, TireStackStatus? status = null);
    Task<List<TireStack>> GetByStorageLocationAsync(Guid locationId);
    Task<List<TireStack>> SearchAsync(string searchTerm);
    Task<TireStack> CreateAsync(TireStack tireStack);
    Task<TireStack> UpdateAsync(TireStack tireStack);
    Task<TireStack> StoreAsync(TireStack tireStack, Guid storageLocationId, Guid? storedByUserId = null);
    Task<TireStack> CreateAndStoreAsync(TireStack tireStack, Guid storageLocationId, Guid? storedByUserId = null);
    Task<TireStack> PickupAsync(Guid tireStackId, Guid? pickedUpByUserId = null);
    Task<List<TireStack>> PickupBatchAsync(IEnumerable<Guid> tireStackIds, Guid? pickedUpByUserId = null);
    Task<SwapResult> SwapAsync(Guid outgoingTireStackId, TireStack incomingTireStack, Guid? targetLocationId = null, Guid? userId = null);
    Task<bool> DeleteAsync(Guid id);
    Task<string> GenerateTicketNumberAsync(bool isInventory = false);
    Task<StorageStatistics> GetStatisticsAsync();
    Task<List<TireStack>> GetExpiringPickupsAsync(int daysAhead = 14);
    Task<TireInspection> AddInspectionAsync(TireInspection inspection);
    Task<List<TireInspection>> GetInspectionsAsync(Guid tireStackId);
    Task<TireInspection?> GetLatestInspectionAsync(Guid tireStackId);
    Task<TireInspection> UpdateInspectionAsync(TireInspection inspection);
    Task<bool> DeleteInspectionAsync(Guid inspectionId);
}

/// <summary>
/// Result of a seasonal tire swap (one stack out, another stack in)
/// </summary>
public class SwapResult
{
    public required TireStack Outgoing { get; init; }
    public required TireStack Incoming { get; init; }
    public string? FreedLocationCode { get; init; }
    public string? NewLocationCode { get; init; }
}

/// <summary>
/// Statistics for the tire storage system
/// </summary>
public class StorageStatistics
{
    /// <summary>
    /// Total number of stacks currently stored
    /// </summary>
    public int TotalStacksStored { get; set; }

    /// <summary>
    /// Total number of individual tires stored (stacks * 4)
    /// </summary>
    public int TotalTiresStored => TotalStacksStored * TireStack.TiresPerStack;

    /// <summary>
    /// Total stacks picked up (historical)
    /// </summary>
    public int TotalStacksPickedUp { get; set; }

    /// <summary>
    /// Number of active customers
    /// </summary>
    public int TotalCustomers { get; set; }

    /// <summary>
    /// Number of available storage locations
    /// </summary>
    public int AvailableLocations { get; set; }

    /// <summary>
    /// Number of occupied storage locations
    /// </summary>
    public int OccupiedLocations { get; set; }

    /// <summary>
    /// Summer tire stacks stored
    /// </summary>
    public int SummerStacksStored { get; set; }

    /// <summary>
    /// Winter tire stacks stored
    /// </summary>
    public int WinterStacksStored { get; set; }

    /// <summary>
    /// All-season tire stacks stored
    /// </summary>
    public int AllSeasonStacksStored { get; set; }

    /// <summary>
    /// Inventory stacks (shop stock)
    /// </summary>
    public int InventoryStackCount { get; set; }

    /// <summary>
    /// Customer tire stacks
    /// </summary>
    public int CustomerStackCount { get; set; }
}

/// <summary>
/// Service implementation for managing tire stacks
/// </summary>
public class TireStackService : ITireStackService
{
    private readonly AppDbContext _context;
    private readonly ILogger<TireStackService> _logger;

    public TireStackService(AppDbContext context, ILogger<TireStackService> logger)
    {
        _context = context;
        _logger = logger;
    }

    public async Task<List<TireStack>> GetAllAsync(TireStackStatus? status = null)
    {
        return await GetAllAsync(status, null);
    }

    public async Task<List<TireStack>> GetAllAsync(TireStackStatus? status, bool? isInventory)
    {
        var query = _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.StorageLocation)
            .Include(ts => ts.CatalogItem)
            .Where(ts => ts.IsActive);

        if (status.HasValue)
            query = query.Where(ts => ts.Status == status.Value);

        if (isInventory.HasValue)
        {
            if (isInventory.Value)
                query = query.Where(ts => ts.CustomerId == null);
            else
                query = query.Where(ts => ts.CustomerId != null);
        }

        return await query
            .OrderByDescending(ts => ts.StoredAt)
            .ToListAsync();
    }

    public async Task<TireStack?> GetByIdAsync(Guid id)
    {
        return await _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.StorageLocation)
            .Include(ts => ts.CatalogItem)
            .FirstOrDefaultAsync(ts => ts.Id == id && ts.IsActive);
    }

    public async Task<TireStack?> GetByTicketNumberAsync(string ticketNumber)
    {
        return await _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.StorageLocation)
            .Include(ts => ts.CatalogItem)
            .FirstOrDefaultAsync(ts => ts.TicketNumber == ticketNumber && ts.IsActive);
    }

    public async Task<List<TireStack>> GetByCustomerAsync(Guid customerId, TireStackStatus? status = null)
    {
        var query = _context.TireStacks
            .Include(ts => ts.StorageLocation)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.CatalogItem)
            .Where(ts => ts.CustomerId == customerId && ts.IsActive);

        if (status.HasValue)
            query = query.Where(ts => ts.Status == status.Value);

        return await query
            .OrderByDescending(ts => ts.StoredAt)
            .ToListAsync();
    }

    public async Task<List<TireStack>> GetByCategoryAsync(Guid catalogItemId, TireStackStatus? status = null)
    {
        var query = _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.StorageLocation)
            .Include(ts => ts.CatalogItem)
            .Where(ts => ts.CatalogItemId == catalogItemId && ts.IsActive);

        if (status.HasValue)
            query = query.Where(ts => ts.Status == status.Value);

        return await query
            .OrderByDescending(ts => ts.StoredAt)
            .ToListAsync();
    }

    public async Task<List<TireStack>> GetByStorageLocationAsync(Guid locationId)
    {
        return await _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.CatalogItem)
            .Where(ts => ts.StorageLocationId == locationId && ts.Status == TireStackStatus.Stored && ts.IsActive)
            .OrderBy(ts => ts.StoredAt)
            .ToListAsync();
    }

    public async Task<List<TireStack>> SearchAsync(string searchTerm)
    {
        if (string.IsNullOrWhiteSpace(searchTerm))
            return await GetAllAsync(TireStackStatus.Stored);

        var term = searchTerm.Trim().ToLower();

        return await _context.TireStacks
            .AsNoTracking()
            .Include(ts => ts.Customer)
            .Include(ts => ts.Vehicle)
            .Include(ts => ts.StorageLocation)
            .Include(ts => ts.CatalogItem)
            .Where(ts => ts.IsActive && ts.Status == TireStackStatus.Stored && (
                ts.TicketNumber.ToLower().Contains(term) ||
                (ts.TireBrand != null && ts.TireBrand.ToLower().Contains(term)) ||
                (ts.TireSize != null && ts.TireSize.ToLower().Contains(term)) ||
                (ts.Customer != null && ts.Customer.FirstName != null && ts.Customer.FirstName.ToLower().Contains(term)) ||
                (ts.Customer != null && ts.Customer.LastName.ToLower().Contains(term)) ||
                (ts.Customer != null && ts.Customer.CustomerNumber.ToLower().Contains(term)) ||
                (ts.Vehicle != null && ts.Vehicle.Make.ToLower().Contains(term)) ||
                (ts.Vehicle != null && ts.Vehicle.Model != null && ts.Vehicle.Model.ToLower().Contains(term)) ||
                (ts.StorageLocation != null && ts.StorageLocation.Code.ToLower().Contains(term)) ||
                (ts.CatalogItem != null && ts.CatalogItem.Name.ToLower().Contains(term))
            ))
            .OrderByDescending(ts => ts.StoredAt)
            .Take(50)
            .ToListAsync();
    }

    public async Task<TireStack> CreateAsync(TireStack tireStack)
    {
        var isInventory = tireStack.CustomerId == null;
        
        if (string.IsNullOrEmpty(tireStack.TicketNumber))
            tireStack.TicketNumber = await GenerateTicketNumberAsync(isInventory);

        tireStack.CreatedAt = DateTime.UtcNow;
        tireStack.StoredAt = DateTime.UtcNow;

        _context.TireStacks.Add(tireStack);
        await _context.SaveChangesAsync();

        _logger.LogInformation("Tire stack created: {TicketNumber}, IsInventory: {IsInventory}", 
            tireStack.TicketNumber, isInventory);

        return tireStack;
    }

    public async Task<TireStack> UpdateAsync(TireStack tireStack)
    {
        tireStack.UpdatedAt = DateTime.UtcNow;
        _context.TireStacks.Update(tireStack);
        await _context.SaveChangesAsync();

        _logger.LogInformation("Tire stack updated: {TicketNumber}", tireStack.TicketNumber);

        return tireStack;
    }

    public async Task<TireStack> StoreAsync(TireStack tireStack, Guid storageLocationId, Guid? storedByUserId = null)
    {
        // Verify storage location is available (force fresh values in case a
        // stale tracked instance exists in this long-lived scoped context)
        var location = await _context.StorageLocations
            .FirstOrDefaultAsync(s => s.Id == storageLocationId);

        if (location == null)
            throw new InvalidOperationException("Lagerplatz nicht gefunden.");

        await _context.Entry(location).ReloadAsync();

        if (!location.IsActive || !location.IsAvailable)
            throw new InvalidOperationException("Lagerplatz ist nicht verfügbar.");

        // AssignTireStack handles the IsOccupied check and sets TireStackId/IsOccupied/OccupiedSince
        location.AssignTireStack(tireStack.Id);

        tireStack.StorageLocationId = storageLocationId;
        tireStack.Status = TireStackStatus.Stored;
        tireStack.StoredAt = DateTime.UtcNow;
        tireStack.StoredByUserId = storedByUserId;
        tireStack.UpdatedAt = DateTime.UtcNow;

        _context.TireStacks.Update(tireStack);
        try
        {
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            throw new InvalidOperationException($"Lagerplatz {location.Code} wurde soeben anderweitig belegt.");
        }

        _logger.LogInformation("Tire stack {TicketNumber} stored at location {LocationCode}", 
            tireStack.TicketNumber, location.Code);

        return tireStack;
    }

    public async Task<TireStack> PickupAsync(Guid tireStackId, Guid? pickedUpByUserId = null)
    {
        var tireStack = await _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.StorageLocation)
            .FirstOrDefaultAsync(ts => ts.Id == tireStackId && ts.IsActive);

        if (tireStack == null)
            throw new InvalidOperationException("Reifenstapel nicht gefunden.");

        if (tireStack.Status != TireStackStatus.Stored && tireStack.Status != TireStackStatus.Reserved)
            throw new InvalidOperationException($"Reifenstapel {tireStack.TicketNumber} ist nicht eingelagert.");

        // Clear the storage location (both sides)
        if (tireStack.StorageLocation != null)
            tireStack.StorageLocation.ClearLocation();

        tireStack.Status = TireStackStatus.PickedUp;
        tireStack.PickedUpAt = DateTime.UtcNow;
        tireStack.PickedUpByUserId = pickedUpByUserId;
        tireStack.StorageLocationId = null;
        tireStack.UpdatedAt = DateTime.UtcNow;

        await _context.SaveChangesAsync();

        _logger.LogInformation("Tire stack {TicketNumber} picked up", tireStack.TicketNumber);

        return tireStack;
    }

    public async Task<TireStack> CreateAndStoreAsync(TireStack tireStack, Guid storageLocationId, Guid? storedByUserId = null)
    {
        // MySqlRetryingExecutionStrategy requires user transactions to run
        // inside the execution strategy as a single retriable unit.
        var hadTicketNumber = !string.IsNullOrEmpty(tireStack.TicketNumber);
        var strategy = _context.Database.CreateExecutionStrategy();
        try
        {
            return await strategy.ExecuteAsync(async () =>
            {
                await using var transaction = await _context.Database.BeginTransactionAsync();
                try
                {
                    var created = await CreateAsync(tireStack);
                    var stored = await StoreAsync(created, storageLocationId, storedByUserId);
                    await transaction.CommitAsync();
                    return stored;
                }
                catch
                {
                    await transaction.RollbackAsync();
                    // Detach the failed entities so a retry (by the strategy or
                    // the user) does not re-insert stale Added/Modified state.
                    _context.ChangeTracker.Clear();
                    // Regenerate the ticket number on retry if we created it here
                    // (avoids permanent unique-index collisions).
                    if (!hadTicketNumber)
                        tireStack.TicketNumber = string.Empty;
                    throw;
                }
            });
        }
        catch (DbUpdateConcurrencyException)
        {
            throw new InvalidOperationException("Der Lagerplatz wurde soeben anderweitig belegt. Bitte anderen Platz wählen.");
        }
    }

    public async Task<List<TireStack>> PickupBatchAsync(IEnumerable<Guid> tireStackIds, Guid? pickedUpByUserId = null)
    {
        var ids = tireStackIds.Distinct().ToList();
        if (ids.Count == 0)
            return new List<TireStack>();

        var strategy = _context.Database.CreateExecutionStrategy();
        try
        {
            return await strategy.ExecuteAsync(async () =>
            {
                await using var transaction = await _context.Database.BeginTransactionAsync();
                try
                {
                    var pickedUp = new List<TireStack>(ids.Count);
                    foreach (var id in ids)
                    {
                        pickedUp.Add(await PickupAsync(id, pickedUpByUserId));
                    }
                    await transaction.CommitAsync();
                    return pickedUp;
                }
                catch
                {
                    await transaction.RollbackAsync();
                    _context.ChangeTracker.Clear();
                    throw;
                }
            });
        }
        catch (DbUpdateConcurrencyException)
        {
            throw new InvalidOperationException("Ein Lagerplatz wurde zwischenzeitlich geändert. Bitte erneut versuchen.");
        }
    }

    public async Task<SwapResult> SwapAsync(Guid outgoingTireStackId, TireStack incomingTireStack, Guid? targetLocationId = null, Guid? userId = null)
    {
        var strategy = _context.Database.CreateExecutionStrategy();
        try
        {
            return await strategy.ExecuteAsync(async () =>
            {
                await using var transaction = await _context.Database.BeginTransactionAsync();
                try
                {
                    var outgoing = await _context.TireStacks
                        .Include(ts => ts.Customer)
                        .Include(ts => ts.Vehicle)
                        .Include(ts => ts.StorageLocation)
                        .FirstOrDefaultAsync(ts => ts.Id == outgoingTireStackId && ts.IsActive);

                    if (outgoing == null)
                        throw new InvalidOperationException("Herauszugebender Reifenstapel nicht gefunden.");
                    if (outgoing.Status != TireStackStatus.Stored && outgoing.Status != TireStackStatus.Reserved)
                        throw new InvalidOperationException("Herauszugebender Reifenstapel ist nicht eingelagert.");
                    if (outgoing.CustomerId == null)
                        throw new InvalidOperationException("Reifenwechsel ist nur für Kundenreifen möglich.");

                    var freedLocationId = outgoing.StorageLocationId;
                    var freedLocationCode = outgoing.StorageLocation?.Code;

                    // 1. Release outgoing stack: mark as mounted on vehicle, free the location
                    if (outgoing.StorageLocation != null)
                        outgoing.StorageLocation.ClearLocation();

                    outgoing.Status = TireStackStatus.OnVehicle;
                    outgoing.PickedUpAt = DateTime.UtcNow;
                    outgoing.PickedUpByUserId = userId;
                    outgoing.StorageLocationId = null;
                    outgoing.UpdatedAt = DateTime.UtcNow;
                    await _context.SaveChangesAsync();

                    // 2. Resolve incoming stack: existing (from history) or new
                    TireStack incoming;
                    var existing = await _context.TireStacks
                        .FirstOrDefaultAsync(ts => ts.Id == incomingTireStack.Id && ts.IsActive);

                    if (existing != null)
                    {
                        if (existing.Status != TireStackStatus.OnVehicle && existing.Status != TireStackStatus.PickedUp)
                            throw new InvalidOperationException($"Reifenstapel {existing.TicketNumber} kann nicht wieder eingelagert werden (Status: {existing.StatusText}).");
                        if (existing.CustomerId != outgoing.CustomerId)
                            throw new InvalidOperationException("Der einzulagernde Reifenstapel gehört einem anderen Kunden.");

                        existing.PickedUpAt = null;
                        existing.PickedUpByUserId = null;
                        existing.ExpectedPickupDate = incomingTireStack.ExpectedPickupDate;
                        if (!string.IsNullOrWhiteSpace(incomingTireStack.Notes))
                            existing.Notes = incomingTireStack.Notes;
                        existing.Condition = incomingTireStack.Condition;
                        if (incomingTireStack.VehicleId != null)
                            existing.VehicleId = incomingTireStack.VehicleId;
                        incoming = existing;
                    }
                    else
                    {
                        incomingTireStack.CustomerId = outgoing.CustomerId;
                        incomingTireStack.VehicleId ??= outgoing.VehicleId;
                        incomingTireStack.CatalogItemId = null;
                        incomingTireStack.ItemCount = TireStack.TiresPerStack;
                        incomingTireStack.PickedUpAt = null;
                        incomingTireStack.PickedUpByUserId = null;
                        incoming = await CreateAsync(incomingTireStack);
                    }

                    // 3. Store incoming stack on target (default: the location just freed)
                    var locationId = targetLocationId ?? freedLocationId
                        ?? throw new InvalidOperationException("Kein Lagerplatz für die Einlagerung angegeben.");

                    incoming = await StoreAsync(incoming, locationId, userId);
                    if (incoming.StorageLocation == null)
                        incoming.StorageLocation = await _context.StorageLocations.FindAsync(locationId);

                    await transaction.CommitAsync();

                    _logger.LogInformation("Tire swap: {Outgoing} -> vehicle, {Incoming} -> location {Location}",
                        outgoing.TicketNumber, incoming.TicketNumber, incoming.StorageLocation?.Code);

                    return new SwapResult
                    {
                        Outgoing = outgoing,
                        Incoming = incoming,
                        FreedLocationCode = freedLocationCode,
                        NewLocationCode = incoming.StorageLocation?.Code
                    };
                }
                catch
                {
                    await transaction.RollbackAsync();
                    _context.ChangeTracker.Clear();
                    throw;
                }
            });
        }
        catch (DbUpdateConcurrencyException)
        {
            throw new InvalidOperationException("Der Lagerplatz wurde zwischenzeitlich geändert. Bitte erneut versuchen.");
        }
    }

    public async Task<bool> DeleteAsync(Guid id)
    {
        var tireStack = await _context.TireStacks.FindAsync(id);
        if (tireStack == null)
            return false;

        // Block deletion of stored or reserved tire stacks
        if (tireStack.Status == TireStackStatus.Stored || tireStack.Status == TireStackStatus.Reserved)
        {
            _logger.LogWarning("Cannot delete {Status} tire stack {TicketNumber}", tireStack.Status, tireStack.TicketNumber);
            return false;
        }

        // Soft delete
        tireStack.IsActive = false;
        tireStack.UpdatedAt = DateTime.UtcNow;
        await _context.SaveChangesAsync();

        _logger.LogInformation("Tire stack soft-deleted: {TicketNumber}", tireStack.TicketNumber);

        return true;
    }

    public async Task<string> GenerateTicketNumberAsync(bool isInventory = false)
    {
        var year = DateTime.UtcNow.Year;
        var prefix = isInventory ? $"INV-{year}-" : $"TS-{year}-";

        var lastNumber = await _context.TireStacks
            .Where(ts => ts.TicketNumber.StartsWith(prefix))
            .OrderByDescending(ts => ts.TicketNumber)
            .Select(ts => ts.TicketNumber)
            .FirstOrDefaultAsync();

        var nextNumber = 1;
        if (lastNumber != null)
        {
            var numPart = lastNumber.Replace(prefix, "");
            if (int.TryParse(numPart, out var lastNum))
                nextNumber = lastNum + 1;
        }

        return $"{prefix}{nextNumber:D5}";
    }

    public async Task<StorageStatistics> GetStatisticsAsync()
    {
        var storedStacks = await _context.TireStacks
            .Where(ts => ts.IsActive && ts.Status == TireStackStatus.Stored)
            .GroupBy(ts => ts.Season)
            .Select(g => new { Season = g.Key, Count = g.Count() })
            .ToListAsync();

        var availableLocations = await _context.StorageLocations
            .Where(s => s.IsActive && s.IsAvailable && !s.IsOccupied)
            .CountAsync();

        var occupiedLocations = await _context.TireStacks
            .Where(ts => ts.IsActive && ts.Status == TireStackStatus.Stored && ts.StorageLocationId != null)
            .Select(ts => ts.StorageLocationId)
            .Distinct()
            .CountAsync();

        var inventoryCount = await _context.TireStacks
            .CountAsync(ts => ts.IsActive && ts.Status == TireStackStatus.Stored && ts.CustomerId == null);

        var customerStackCount = await _context.TireStacks
            .CountAsync(ts => ts.IsActive && ts.Status == TireStackStatus.Stored && ts.CustomerId != null);

        return new StorageStatistics
        {
            TotalStacksStored = storedStacks.Sum(x => x.Count),
            TotalStacksPickedUp = await _context.TireStacks.CountAsync(ts => ts.IsActive && ts.Status == TireStackStatus.PickedUp),
            TotalCustomers = await _context.Customers.CountAsync(c => c.IsActive),
            AvailableLocations = availableLocations,
            OccupiedLocations = occupiedLocations,
            SummerStacksStored = storedStacks.FirstOrDefault(x => x.Season == TireSeason.Sommer)?.Count ?? 0,
            WinterStacksStored = storedStacks.FirstOrDefault(x => x.Season == TireSeason.Winter)?.Count ?? 0,
            AllSeasonStacksStored = storedStacks.FirstOrDefault(x => x.Season == TireSeason.Allrad)?.Count ?? 0,
            InventoryStackCount = inventoryCount,
            CustomerStackCount = customerStackCount
        };
    }

    public async Task<List<TireStack>> GetExpiringPickupsAsync(int daysAhead = 14)
    {
        var cutoffDate = DateTime.UtcNow.AddDays(daysAhead);

        return await _context.TireStacks
            .Include(ts => ts.Customer)
            .Include(ts => ts.StorageLocation)
            .Where(ts => ts.IsActive && 
                         ts.Status == TireStackStatus.Stored && 
                         ts.CustomerId != null &&
                         ts.ExpectedPickupDate != null && 
                         ts.ExpectedPickupDate <= cutoffDate)
            .OrderBy(ts => ts.ExpectedPickupDate)
            .ToListAsync();
    }

    public async Task<TireInspection> AddInspectionAsync(TireInspection inspection)
    {
        var exists = await _context.TireStacks.AnyAsync(ts => ts.Id == inspection.TireStackId);
        if (!exists)
            throw new InvalidOperationException("Reifenstapel nicht gefunden.");

        if (inspection.Id == Guid.Empty) inspection.Id = Guid.NewGuid();
        inspection.CreatedAt = DateTime.UtcNow;

        _context.TireInspections.Add(inspection);
        await _context.SaveChangesAsync();
        return inspection;
    }

    public async Task<TireInspection> UpdateInspectionAsync(TireInspection inspection)
    {
        var existing = await _context.TireInspections.FirstOrDefaultAsync(i => i.Id == inspection.Id)
            ?? throw new InvalidOperationException("Messung nicht gefunden.");

        existing.MeasuredAt = inspection.MeasuredAt;
        existing.LicensePlate = inspection.LicensePlate;
        existing.Mileage = inspection.Mileage;
        existing.TreadDepthFL = inspection.TreadDepthFL;
        existing.TreadDepthFR = inspection.TreadDepthFR;
        existing.TreadDepthRL = inspection.TreadDepthRL;
        existing.TreadDepthRR = inspection.TreadDepthRR;
        existing.RatingFL = TireInspection.RateDepth(inspection.TreadDepthFL);
        existing.RatingFR = TireInspection.RateDepth(inspection.TreadDepthFR);
        existing.RatingRL = TireInspection.RateDepth(inspection.TreadDepthRL);
        existing.RatingRR = TireInspection.RateDepth(inspection.TreadDepthRR);
        existing.Recommendation = inspection.Recommendation;

        await _context.SaveChangesAsync();
        return existing;
    }

    public async Task<bool> DeleteInspectionAsync(Guid inspectionId)
    {
        var existing = await _context.TireInspections.FirstOrDefaultAsync(i => i.Id == inspectionId);
        if (existing == null) return false;

        _context.TireInspections.Remove(existing);
        await _context.SaveChangesAsync();
        return true;
    }

    public async Task<List<TireInspection>> GetInspectionsAsync(Guid tireStackId)
    {
        return await _context.TireInspections
            .Where(i => i.TireStackId == tireStackId)
            .OrderByDescending(i => i.MeasuredAt ?? i.CreatedAt)
            .ToListAsync();
    }

    public async Task<TireInspection?> GetLatestInspectionAsync(Guid tireStackId)
    {
        return await _context.TireInspections
            .Where(i => i.TireStackId == tireStackId)
            .OrderByDescending(i => i.MeasuredAt ?? i.CreatedAt)
            .FirstOrDefaultAsync();
    }
}