using System.Text.Json;
using System.Text.RegularExpressions;
using GerbilManagerWebAPI.Models;
using Microsoft.EntityFrameworkCore;

namespace GerbilManagerWebAPI.Import
{
    /// <summary>
    /// FEAT-8c import loader. Consumes tools/import/output (animals.json + litters.json
    /// produced by extract.py) and loads conflict-free data into the database:
    /// litters first (Wurfchronik = authoritative), then animals matched onto them.
    ///
    /// Load policy:
    ///   - Litters: all created (idempotent by ExternalRef = source litter id).
    ///   - Animals: created when they have a DOB and are NOT in conflict. The birth-litter
    ///     link is set only for HIGH-confidence matches (litterRef.confidence == "hoch");
    ///     date-only/ambiguous links are quarantined (animal loads with LitterId = null).
    ///   - QUARANTINED (never loaded): conflicts + stubs (no DOB) — await the wife's review.
    /// Idempotent: re-running matches on ExternalRef and skips existing rows.
    /// Execute is gated by the endpoint; this service only acts when asked.
    /// </summary>
    public sealed class ImportService
    {
        private static readonly string[] LocusOrder = { "A", "C", "D", "E", "G", "P", "Sp", "Re" };
        private static readonly JsonSerializerOptions Json = new()
        {
            PropertyNameCaseInsensitive = true,
        };
        private const string ImportSourceTag = "FEAT-8 Stammbaum/Wurfchronik";

        private readonly ApplicationContext _db;
        private readonly string _sourceDir;
        private readonly string _photoRoot;

        /// <summary>Test-friendly constructor with explicit paths.</summary>
        public ImportService(ApplicationContext db, string sourceDir, string photoRoot)
        {
            _db = db;
            _sourceDir = sourceDir;
            _photoRoot = photoRoot;
        }

        public ImportService(ApplicationContext db, IConfiguration config, IWebHostEnvironment env)
            : this(db,
                   config["Import:SourcePath"]
                       ?? Path.GetFullPath(Path.Combine(env.ContentRootPath, "..", "tools", "import", "output")),
                   config["Photos:RootPath"] ?? Path.Combine(env.ContentRootPath, "photo-storage"))
        {
        }

        public async Task<ImportReport> RunAsync(bool execute)
        {
            var notes = new List<string>();
            var samples = new List<string>();

            var animals = Load<List<SourceAnimal>>("animals.json") ?? new();
            var litters = Load<List<SourceLitter>>("litters.json") ?? new();
            if (animals.Count == 0 && litters.Count == 0)
                notes.Add($"Keine Quelldaten gefunden in {_sourceDir} (animals.json/litters.json). extract.py zuerst ausführen.");

            // ---- categorise animals ----
            var loadable = new List<SourceAnimal>();
            int conflicts = 0, stubs = 0, dateOnly = 0, ambiguous = 0;
            foreach (var a in animals)
            {
                if (a.Conflict) { conflicts++; continue; }
                if (string.IsNullOrEmpty(a.Dob)) { stubs++; continue; }
                loadable.Add(a);
                var conf = a.LitterRef?.Confidence;
                if (a.LitterRef?.Candidates is { Count: > 0 }) ambiguous++;
                else if (conf == "niedrig") dateOnly++;
            }

            // existing rows (idempotency).
            var existingGerbilExtRefs = await _db.Gerbils
                .Where(g => g.ExternalRef != null)
                .Select(g => g.ExternalRef!).ToListAsync();
            var existingGerbilSet = existingGerbilExtRefs.ToHashSet();

            // DB-5: Litters now carry ExternalRef (= source litter id from extract.py).
            // Primary idempotency: ExternalRef. Fallback: Name+Date for litters created before DB-5.
            var existingLitterData = await _db.Litters
                .Select(l => new { l.Name, l.Date, l.ExternalRef }).ToListAsync();
            var existingLitterKeySet = existingLitterData
                .Select(x => $"{x.Name}|{x.Date:yyyy-MM-dd}").ToHashSet();
            var existingLitterExtRefSet = existingLitterData
                .Where(x => x.ExternalRef != null)
                .Select(x => x.ExternalRef!).ToHashSet();

            // colour-variety name -> id (case-insensitive)
            var varieties = await _db.ColorVarieties.Select(v => new { v.Id, v.Name }).ToListAsync();
            var varietyByName = varieties
                .GroupBy(v => v.Name.Trim().ToLowerInvariant())
                .ToDictionary(g => g.Key, g => g.First().Id);

            // gender inference from litter roles (sire -> male, dam -> female; both -> unknown)
            var sireNames = litters.Select(l => Normalize(StripZucht(l.SireName))).Where(s => s.Length > 0).ToHashSet();
            var damNames = litters.Select(l => Normalize(StripZucht(l.DamName))).Where(s => s.Length > 0).ToHashSet();

            // ---- litters: create map source.id -> Litter (for high-confidence animal links) ----
            // Idempotency (DB-5): ExternalRef-match is primary (stable source id); Name+Date is the
            // fallback for litters created before DB-5 (those have ExternalRef=null in the DB).
            // COUNTER-BUG FIX: undated litters (31 in the Wurfchronik) have no parseable date,
            // so skip them early — they can never be created or linked to animals.
            int littersCreated = 0, littersExisting = 0, littersWithoutDate = 0;
            var litterIdMap = new Dictionary<string, Guid>();    // source litter id -> Litter.Id
            foreach (var sl in litters)
            {
                var date = ParseDate(sl.Date);
                if (date is null) { littersWithoutDate++; continue; }   // undated: skip entirely

                var name = $"Wurf {sl.LitterId}".Trim();
                var key = $"{name}|{date:yyyy-MM-dd}";
                // DB-5: check ExternalRef first (stable, source-id-based); fall back to Name+Date
                // for litters imported before ExternalRef existed (those have ExternalRef = null).
                if (existingLitterExtRefSet.Contains(sl.Id) || existingLitterKeySet.Contains(key))
                { littersExisting++; continue; }

                var id = Guid.NewGuid();
                litterIdMap[sl.Id] = id;
                littersCreated++;
                if (execute)
                {
                    _db.Litters.Add(new Litter
                    {
                        Id = id,
                        Name = name,
                        Date = date.Value,
                        TotalBorn = sl.TotalBorn,
                        Notes = string.IsNullOrWhiteSpace(sl.Note) ? null : sl.Note,
                        PairingCode = string.IsNullOrWhiteSpace(sl.Zuchtnummer) ? null : sl.Zuchtnummer,
                        ExternalRef = sl.Id,   // DB-5: stable import key for future re-imports
                    });
                }
                if (samples.Count < 8)
                    samples.Add($"Wurf: {name} ({sl.Date}) — {sl.DamName} × {sl.SireName}");
            }
            if (execute) await _db.SaveChangesAsync();

            // ---- animals ----
            int animalsCreated = 0, linked = 0, fbMatched = 0, fbUnmatched = 0, animalsExisting = 0;
            int photosAttached = 0, photosMissing = 0;
            var createdAnimalByName = new Dictionary<string, Guid>();  // normalized name -> gerbil id (for litter back-link)

            // name+DOB -> gid index, across EXISTING rows AND this run's planned animals, so that
            // chart-position parentRefs (PEDIGREE-LINK) can resolve a parent to a real gerbil id.
            var existingRows = await _db.Gerbils
                .Select(g => new { g.Id, g.Name, g.DateOfBirth, g.ExternalRef, g.LitterId, g.ColorVarietyId }).ToListAsync();
            var gidByNameDob = new Dictionary<string, Guid>();
            foreach (var g in existingRows)
                gidByNameDob[NameDobKey(g.Name, g.DateOfBirth)] = g.Id;
            var existingLitterByExtRef = existingRows.Where(g => g.ExternalRef != null)
                .ToDictionary(g => g.ExternalRef!, g => g.LitterId);
            var existingColorVarietyByExtRef = existingRows.Where(g => g.ExternalRef != null)
                .ToDictionary(g => g.ExternalRef!, g => g.ColorVarietyId);
            // CR-9: ExternalRef → Gerbil.Id fallback for name/DOB drift on re-import
            var existingGidByExtRef = existingRows.Where(g => g.ExternalRef != null)
                .ToDictionary(g => g.ExternalRef!, g => g.Id);

            // CR-11: load CanonicalGenotype for genotype-derived Farbschlag matching
            var varietiesWithGeno = await _db.ColorVarieties
                .Select(v => new { v.Id, v.Name, v.CanonicalGenotype }).ToListAsync();

            // PASS 1: assign ids + resolve fb/gender/Wurfchronik link (no writes yet).
            var plan = new List<AnimalPlan>();
            int fbDerivedFromGenotype = 0;
            foreach (var a in loadable)
            {
                bool exists = existingGerbilSet.Contains(a.Id);
                // CR-9: use TryGetValue; fall back to ExternalRef lookup for name/DOB drift
                // (e.g. correctDob remap or manual rename). Prevents throwing KeyNotFoundException.
                Guid gid;
                if (exists)
                {
                    if (!gidByNameDob.TryGetValue(NameDobKey(a.Name, ParseDate(a.Dob)), out gid))
                    {
                        if (existingGidByExtRef.TryGetValue(a.Id, out gid))
                            notes.Add($"Hinweis: '{a.Name}' (*{a.Dob}) per ExternalRef gefunden trotz Name/DOB-Drift (correctDob oder UI-Umbenennung).");
                        else
                        {
                            notes.Add($"Warnung: ExternalRef '{a.Id}' in DB vorhanden aber nicht auflösbar — Tier übersprungen.");
                            continue;
                        }
                    }
                }
                else gid = Guid.NewGuid();

                Guid? wurfLitterId = null;
                if (a.LitterRef?.Confidence == "hoch" && a.LitterRef.Candidates is not { Count: > 0 }
                    && litterIdMap.TryGetValue(a.LitterRef.LitterId, out var lid))
                    wurfLitterId = lid;

                // CR-11: Farbschlag from explicit name-match first; fall back to genotype derivation
                // (fill-NULL-only — never overwrites an explicit name-match or manual assignment).
                Guid? colorVarietyId = null;
                var fbCandidates = new[] { a.Farbschlag }.Concat(a.FarbschlagVariants)
                    .Where(s => !string.IsNullOrWhiteSpace(s));
                foreach (var fb in fbCandidates)
                    if (varietyByName.TryGetValue(fb.Trim().ToLowerInvariant(), out var vid))
                    { colorVarietyId = vid; break; }
                if (colorVarietyId is null && a.Genotype.Mapped8locus.Count >= 8)
                {
                    // CR-11: only derive from a fully-specified genotype (all 8 loci known,
                    // no "??" wildcards). Partial genotypes (single-locus or sparse records)
                    // would match any catalog entry via wildcards and produce false positives.
                    var composed = ComposeGenotype(a.Genotype);
                    if (!composed.Contains("??"))
                        foreach (var v in varietiesWithGeno)
                            if (!string.IsNullOrWhiteSpace(v.CanonicalGenotype)
                                && GenotypePotentiallyMatches(composed, v.CanonicalGenotype))
                            { colorVarietyId = v.Id; fbDerivedFromGenotype++; break; }
                }

                var gender = InferGender(a, sireNames, damNames);
                var norm = Normalize(StripZucht(a.Name));
                if (norm.Length > 0) createdAnimalByName.TryAdd(norm, gid);
                if (!exists) gidByNameDob.TryAdd(NameDobKey(a.Name, ParseDate(a.Dob)), gid);

                var currentLitter = exists && existingLitterByExtRef.TryGetValue(a.Id, out var el) ? el : null;
                var currentColorVarietyId = exists && existingColorVarietyByExtRef.TryGetValue(a.Id, out var ecv) ? ecv : null;
                plan.Add(new AnimalPlan(a, gid, exists, wurfLitterId, currentLitter, colorVarietyId, gender, currentColorVarietyId));
            }

            // PASS 1.5: PEDIGREE-LINK — synthesize/reuse a litter from chart-position parentRefs for
            // any animal that has no Wurfchronik litter and isn't already litter-linked. Siblings
            // (same father+mother+dob) share one derived litter. Computed for dry-run counts too.
            int parentLinksAdded = 0, derivedLitters = 0;
            var synthLitterForGid = new Dictionary<Guid, Guid>();          // offspring gid -> synth litter id
            var synthLitters = new Dictionary<string, SynthLitter>();      // parents+date key -> synth litter
            foreach (var p in plan)
            {
                if (p.WurfLitterId is not null || p.CurrentLitterId is not null) continue;
                if (p.A.ParentRefs is not { Count: > 0 }) continue;
                var father = ResolveParentGid(p.A, "father", gidByNameDob);
                var mother = ResolveParentGid(p.A, "mother", gidByNameDob);
                if (father is null && mother is null) continue;            // nothing resolvable to link
                var dob = ParseDate(p.A.Dob);
                var conf = p.A.ParentRefs.FirstOrDefault()?.Confidence ?? "medium";
                var key = $"{father}|{mother}|{dob:yyyy-MM-dd}";
                if (!synthLitters.TryGetValue(key, out var sl))
                {
                    sl = new SynthLitter(Guid.NewGuid(), father, mother, dob, conf);
                    synthLitters[key] = sl;
                    derivedLitters++;
                }
                synthLitterForGid[p.Gid] = sl.Id;
                parentLinksAdded++;
            }

            // FK-INTEGRITY (PEDIGREE-LINK bug fix): a litter's Father/MotherId must resolve to a
            // gerbil that is created-or-existing, or Postgres throws FK_Litters_Gerbils_*. Compute
            // the persisted set (existing DB rows + this run's loadable animals) and drop any parent
            // FK that isn't in it; SKIP a derived litter whose BOTH parents are unresolvable (its
            // offspring then load with LitterId=null — still better than 'unbekannt' won't regress).
            // This runs in dry-run too, so a green dry-run GUARANTEES /import/execute won't FK-fault.
            var persisted = new HashSet<Guid>(existingRows.Select(r => r.Id));
            foreach (var p in plan) persisted.Add(p.Gid);
            int litterParentFksDropped = 0, derivedLittersSkipped = 0;
            foreach (var key in synthLitters.Keys.ToList())
            {
                var sl = synthLitters[key];
                var f = sl.Father is Guid gf && persisted.Contains(gf) ? sl.Father : null;
                var m = sl.Mother is Guid gm && persisted.Contains(gm) ? sl.Mother : null;
                if (sl.Father is not null && f is null) litterParentFksDropped++;
                if (sl.Mother is not null && m is null) litterParentFksDropped++;
                if (f is null && m is null)
                {
                    derivedLittersSkipped++; derivedLitters--;
                    foreach (var gid in synthLitterForGid.Where(kv => kv.Value == sl.Id).Select(kv => kv.Key).ToList())
                        synthLitterForGid.Remove(gid);
                    synthLitters.Remove(key);
                    continue;
                }
                synthLitters[key] = sl with { Father = f, Mother = m };
            }
            parentLinksAdded = synthLitterForGid.Count;

            // litter id -> (father, mother) gids, across synthesized + Wurfchronik (by name) litters,
            // each FK guarded by the persisted set. Used by residency rule (b) below; augmented with
            // existing DB litters under execute.
            var litterParents = new Dictionary<Guid, (Guid? F, Guid? M)>();
            foreach (var sl in synthLitters.Values)
                litterParents[sl.Id] = (sl.Father, sl.Mother);
            foreach (var sl in litters)
                if (litterIdMap.TryGetValue(sl.Id, out var lid))
                {
                    Guid? f = createdAnimalByName.TryGetValue(Normalize(StripZucht(sl.SireName)), out var fid) && persisted.Contains(fid) ? fid : null;
                    Guid? m = createdAnimalByName.TryGetValue(Normalize(StripZucht(sl.DamName)), out var mid) && persisted.Contains(mid) ? mid : null;
                    litterParents[lid] = (f, m);
                }

            // PASS 2: stage synthesized litters (parents already guarded above). DO NOT save them
            // before the animals — the offspring AND the parent gerbils are created in the loop
            // below, so a single SaveChanges at the end lets EF order parents→litters→offspring
            // (all FKs are nullable). Saving litters first is exactly what caused the FK fault.
            if (execute)
            {
                // reuse an existing litter with the same parents+date instead of duplicating.
                var existingLitterRows = await _db.Litters
                    .Select(l => new { l.Id, l.FatherId, l.MotherId, l.Date }).ToListAsync();
                var litterByParentsDate = new Dictionary<string, Guid>();
                foreach (var l in existingLitterRows)
                {
                    litterByParentsDate[$"{l.FatherId}|{l.MotherId}|{l.Date:yyyy-MM-dd}"] = l.Id;
                    litterParents[l.Id] = (l.FatherId, l.MotherId);
                }

                foreach (var sl in synthLitters.Values.ToList())
                {
                    var reuseKey = $"{sl.Father}|{sl.Mother}|{sl.Date:yyyy-MM-dd}";
                    if (litterByParentsDate.TryGetValue(reuseKey, out var existingId))
                    {
                        // remap offspring to the existing litter; don't create a duplicate.
                        foreach (var g in synthLitterForGid.Where(kv => kv.Value == sl.Id).Select(kv => kv.Key).ToList())
                            synthLitterForGid[g] = existingId;
                        derivedLitters--;
                        continue;
                    }
                    _db.Litters.Add(new Litter
                    {
                        Id = sl.Id,
                        Name = $"Wurf (aus Diagramm) {sl.Date:yyyy-MM-dd}".Trim(),
                        Date = sl.Date ?? default,
                        FatherId = sl.Father,
                        MotherId = sl.Mother,
                        Notes = $"aus Stammbaum-Diagramm abgeleitet (Konfidenz: {sl.Confidence})",
                    });
                }
                // NOTE: no SaveChanges here — staged with the gerbils below.
            }

            // OWNERSHIP/RESIDENCY (runs AFTER litter links exist): (a) Zuchtname matches the Clan
            // kennel (zuchtCanon contains 'kleinechaote'); (b) parent of a Clan offspring, even if
            // the parent's own Zuchtname is foreign. See hive/agents/god/OWNERSHIP-residency.md.
            static bool ClanCanon(string? zc) =>
                (zc ?? "").Contains("kleinechaote", StringComparison.OrdinalIgnoreCase);
            var resident = new HashSet<Guid>();
            foreach (var p in plan) if (ClanCanon(p.A.ZuchtCanon)) resident.Add(p.Gid);   // (a)
            int residentByA = resident.Count, flippedByParentRule = 0;
            foreach (var p in plan)
            {
                if (!ClanCanon(p.A.ZuchtCanon)) continue;
                var litId = p.WurfLitterId ?? (synthLitterForGid.TryGetValue(p.Gid, out var s) ? s : (Guid?)null);
                if (litId is null || !litterParents.TryGetValue(litId.Value, out var par)) continue;
                if (par.F is Guid gf && resident.Add(gf)) flippedByParentRule++;   // (b)
                if (par.M is Guid gm && resident.Add(gm)) flippedByParentRule++;
            }
            int residentTotal = plan.Count(p => resident.Contains(p.Gid));
            int externalTotal = plan.Count - residentTotal;

            foreach (var p in plan)
            {
                Guid? litterId = p.WurfLitterId
                    ?? (synthLitterForGid.TryGetValue(p.Gid, out var slid) ? slid : (Guid?)null);
                if (litterId is not null) linked++;
                bool isResident = resident.Contains(p.Gid);

                if (p.ColorVarietyId is null) fbUnmatched++; else fbMatched++;

                if (samples.Count < 16)
                    samples.Add($"Tier: {p.A.Name} (*{p.A.Dob}), Genotyp {ComposeGenotype(p.A.Genotype)}"
                        + (litterId is not null ? (p.WurfLitterId is not null ? ", Wurf-verknüpft" : ", Eltern aus Diagramm") : "")
                        + (p.ColorVarietyId is not null ? $", Farbschlag „{p.A.Farbschlag}\"" : ""));

                if (p.Exists)
                {
                    animalsExisting++;
                    // sweep idempotency: re-link a now-linkable animal + refresh its residency.
                    if (execute)
                    {
                        var row = await _db.Gerbils.FirstOrDefaultAsync(g => g.Id == p.Gid);
                        if (row is not null)
                        {
                            if (p.CurrentLitterId is null && litterId is not null) row.LitterId = litterId;
                            row.IsResident = isResident;
                        }
                    }
                    continue;
                }
                animalsCreated++;

                if (execute)
                {
                    _db.Gerbils.Add(new Gerbil
                    {
                        Id = p.Gid,
                        Name = p.A.Name,
                        Gender = p.Gender,
                        Status = GerbilStatus.Active,
                        DateOfBirth = ParseDate(p.A.Dob),
                        DateOfDeath = ParseDate(p.A.Death),
                        LitterId = litterId,
                        ColorVarietyId = p.ColorVarietyId,
                        Genotype = ComposeGenotype(p.A.Genotype),
                        IsDeaf = p.A.Deaf,
                        IsResident = isResident,
                        ImportSource = ImportSourceTag,
                        ExternalRef = p.A.Id,
                        OriginBreeder = string.IsNullOrWhiteSpace(p.A.Zucht) ? null : p.A.Zucht.Trim(),
                        RawImportData = JsonSerializer.Serialize(new
                        {
                            p.A.Genotype.RawGenotype,
                            p.A.Genotype.UnmappedTokens,
                            // GEN-3b: Sls (2nd spotting locus) preserved here until Kevin's GEN-3a
                            // parser adopts it into the compact Genotype contract; tags + deaf too.
                            Sls = p.A.Genotype.Mapped8locus.TryGetValue("Sls", out var sls) ? sls : null,
                            p.A.Tags,
                            p.A.Deaf,
                            p.A.Zucht,
                            p.A.SourceFiles,
                            FarbschlagRaw = p.A.Farbschlag,
                        }),
                    });
                }

                // photos
                foreach (var rel in p.A.Photos)
                {
                    var src = Path.Combine(_sourceDir, rel.Replace('/', Path.DirectorySeparatorChar));
                    if (!File.Exists(src)) { photosMissing++; continue; }
                    photosAttached++;
                    if (execute)
                    {
                        Directory.CreateDirectory(_photoRoot);
                        var fileName = $"{Guid.NewGuid():N}{Path.GetExtension(src)}";
                        File.Copy(src, Path.Combine(_photoRoot, fileName), overwrite: true);
                        _db.GerbilPhotos.Add(new GerbilPhoto
                        {
                            Id = Guid.NewGuid(),
                            GerbilId = p.Gid,
                            FileName = fileName,
                            SortOrder = 0,
                            CreatedAt = DateTimeOffset.UtcNow,
                        });
                    }
                }
            }
            if (execute) await _db.SaveChangesAsync();

            // ---- back-link Wurfchronik litter parents by name (best effort) ----
            if (execute)
            {
                foreach (var sl in litters)
                {
                    if (!litterIdMap.TryGetValue(sl.Id, out var lid)) continue;
                    var litter = await _db.Litters.FirstOrDefaultAsync(l => l.Id == lid);
                    if (litter is null) continue;
                    // guard: only link parents that are actually persisted (avoid an orphan FK).
                    if (createdAnimalByName.TryGetValue(Normalize(StripZucht(sl.SireName)), out var fId) && persisted.Contains(fId))
                        litter.FatherId = fId;
                    if (createdAnimalByName.TryGetValue(Normalize(StripZucht(sl.DamName)), out var mId) && persisted.Contains(mId))
                        litter.MotherId = mId;
                }
                await _db.SaveChangesAsync();
            }

            // PARENT-FK BACKFILL (idempotent re-run): already-imported Wurfchronik litters that
            // have null Father/MotherId because the parent was previously quarantined may now be
            // resolvable. Two lookup sources — must check BOTH:
            //   (a) createdAnimalByName: animals loaded/re-linked in THIS run (new or existing).
            //   (b) allDbNormToGid: ALL gerbils already in the DB, for parents loaded in an
            //       EARLIER run who are no longer in the current extract (e.g. alreadyImported
            //       animals absent from this run's animals.json, or name normalization mismatch
            //       between animals.json and the Wurfchronik sire/dam field).
            // Counted for dry-run too; writes only when execute=true.
            int parentFksBackfilled = 0;
            {
                // Build DB-wide normalized-name lookup (supplementary to createdAnimalByName).
                var allDbNormToGid = existingRows
                    .GroupBy(g => Normalize(StripZucht(g.Name)))
                    .ToDictionary(grp => grp.Key, grp => grp.First().Id);

                var existingWithNullParent = await _db.Litters
                    .Where(l => l.FatherId == null || l.MotherId == null)
                    .Select(l => new { l.Id, l.Name, l.FatherId, l.MotherId })
                    .ToListAsync();
                var sourceByName = litters
                    .GroupBy(sl => $"Wurf {sl.LitterId}".Trim())
                    .ToDictionary(g => g.Key, g => g.First());

                Guid? ResolveParentForBackfill(string rawName)
                {
                    var n = Normalize(StripZucht(rawName));
                    if (n.Length == 0) return null;
                    if (createdAnimalByName.TryGetValue(n, out var fromLoadable) && persisted.Contains(fromLoadable))
                        return fromLoadable;
                    if (allDbNormToGid.TryGetValue(n, out var fromDb) && persisted.Contains(fromDb))
                        return fromDb;
                    return null;
                }

                foreach (var el in existingWithNullParent)
                {
                    if (!sourceByName.TryGetValue(el.Name, out var sl)) continue;
                    var newF = el.FatherId == null ? ResolveParentForBackfill(sl.SireName) : null;
                    var newM = el.MotherId == null ? ResolveParentForBackfill(sl.DamName) : null;
                    if (newF is null && newM is null) continue;
                    parentFksBackfilled++;
                    if (execute)
                    {
                        var row = await _db.Litters.FirstOrDefaultAsync(l => l.Id == el.Id);
                        if (row is not null)
                        {
                            if (newF is not null) row.FatherId = newF;
                            if (newM is not null) row.MotherId = newM;
                        }
                    }
                }
                if (execute && parentFksBackfilled > 0) await _db.SaveChangesAsync();
            }

            // FARBSCHLAG-RE-MATCH — no-op counter (safety mechanism pending god/Julian sign-off).
            // Counts already-imported animals where the current extract matched a DIFFERENT
            // ColorVariety than what is currently stored in the DB. Does NOT update any row.
            int farbschlagWouldRebackfill = plan.Count(p =>
                p.Exists && p.ColorVarietyId is not null && p.ColorVarietyId != p.CurrentColorVarietyId);

            // CR-11: FARBSCHLAG FROM GENOTYPE post-sweep (fill-NULL-only, safe): existing DB animals
            // with null ColorVarietyId whose stored Genotype matches a catalog entry get filled.
            // Mirrors the plan-loop derivation; never overwrites a manually-set or name-matched value.
            {
                var noColor = await _db.Gerbils
                    .Where(g => g.ColorVarietyId == null && g.Genotype != null)
                    .Select(g => new { g.Id, g.Genotype })
                    .ToListAsync();
                foreach (var g in noColor)
                {
                    if (string.IsNullOrWhiteSpace(g.Genotype) || g.Genotype!.Contains("??")) continue;
                    Guid? derivedVid = null;
                    foreach (var v in varietiesWithGeno)
                        if (!string.IsNullOrWhiteSpace(v.CanonicalGenotype)
                            && GenotypePotentiallyMatches(g.Genotype, v.CanonicalGenotype))
                        { derivedVid = v.Id; break; }
                    if (derivedVid is null) continue;
                    fbDerivedFromGenotype++;
                    if (execute)
                    {
                        var row = await _db.Gerbils.FindAsync(g.Id);
                        if (row is not null && row.ColorVarietyId is null) row.ColorVarietyId = derivedVid;
                    }
                }
                if (execute && fbDerivedFromGenotype > 0) await _db.SaveChangesAsync();
            }

            // HERKUNFT BACKFILL (fill-NULL-only, safe): sweep all resident animals whose
            // OriginBreeder is null and fill it with a derived value or 'Zucht der Kleinen Chaoten'.
            // NEVER overwrites a non-null OriginBreeder (Julian: "alle Schreibweisen unterstützen").
            const string DefaultClanBreeder = "Zucht der Kleinen Chaoten";
            int herkunftBackfilled = 0;
            {
                var needsHerkunft = await _db.Gerbils
                    .Where(g => g.OriginBreeder == null && g.IsResident)
                    .Select(g => new { g.Id, g.LitterId })
                    .ToListAsync();

                foreach (var g in needsHerkunft)
                {
                    herkunftBackfilled++;
                    if (execute)
                    {
                        // Prefer the OriginBreeder of an existing parent (father first, then mother).
                        string? derived = null;
                        if (g.LitterId is not null)
                        {
                            var parentIds = await _db.Litters
                                .Where(l => l.Id == g.LitterId)
                                .Select(l => new { l.FatherId, l.MotherId })
                                .FirstOrDefaultAsync();
                            if (parentIds?.FatherId is Guid fid)
                                derived = await _db.Gerbils.Where(gb => gb.Id == fid && gb.OriginBreeder != null)
                                    .Select(gb => gb.OriginBreeder).FirstOrDefaultAsync();
                            if (derived is null && parentIds?.MotherId is Guid mid)
                                derived = await _db.Gerbils.Where(gb => gb.Id == mid && gb.OriginBreeder != null)
                                    .Select(gb => gb.OriginBreeder).FirstOrDefaultAsync();
                        }
                        var row = await _db.Gerbils.FindAsync(g.Id);
                        if (row is not null && row.OriginBreeder is null)
                            row.OriginBreeder = derived ?? DefaultClanBreeder;
                    }
                }
                if (execute && herkunftBackfilled > 0) await _db.SaveChangesAsync();
            }

            if (littersWithoutDate > 0)
                notes.Add($"Würfe ohne Datum: {littersWithoutDate} Wurfchronik-Einträge ohne parsbares Geburtsdatum übersprungen (weder erstellt noch verknüpft).");
            notes.Add("Quarantäne (kein Import): Konflikte + Stubs ohne Geburtsdatum + unsichere Wurf-Zuordnungen — warten auf die Prüfung durch die Züchterin.");
            if (parentLinksAdded > 0)
                notes.Add($"Stammbaum-Diagramm: {parentLinksAdded} Tiere über Eltern-Verknüpfung einem (abgeleiteten) Wurf zugeordnet ({derivedLitters} abgeleitete Würfe).");
            notes.Add($"FK-Integrität: {litterParentFksDropped} Eltern-Verknüpfung(en) verworfen (Elternteil nicht ladbar), {derivedLittersSkipped} abgeleitete Würfe übersprungen (kein ladbares Elternteil). Bei 0/0 ist /import/execute FK-sicher.");
            if (parentFksBackfilled > 0)
                notes.Add($"Parent-FK-Backfill: {parentFksBackfilled} bereits importierte Würfe haben jetzt eine Eltern-Verknüpfung (Elternteil war zuvor in Quarantäne, jetzt geladen).");
            notes.Add($"Bestand/Herkunft: {residentTotal} im Bestand (Clan Kleine Chaoten), {externalTotal} externe Ahnen ({flippedByParentRule} davon über die Eltern-Regel als Bestand erkannt).");
            if (herkunftBackfilled > 0)
                notes.Add($"Herkunft-Backfill: {herkunftBackfilled} Bestand-Tier(e) mit leerem Herkunft-Feld befüllt (Zucht der Kleinen Chaoten oder von Elternteil abgeleitet). Niemals überschrieben.");
            if (farbschlagWouldRebackfill > 0)
                notes.Add($"Farbschlag-Hinweis (kein Overwrite): {farbschlagWouldRebackfill} bereits importierte Tier(e) haben einen veralteten Farbschlag, den der aktuelle Extraktor korrigieren würde — Overwrite-Mechanismus ausstehend (god/Julian Freigabe).");
            int conflictsResolvedByDecision = loadable.Count(a => a.ResolvedByDecision);
            if (conflictsResolvedByDecision > 0)
                notes.Add($"Konfliktauflösungen: {conflictsResolvedByDecision} Tier(e) anhand von conflict-decisions.json un-quarantänet (Genotyp/Farbschlag der Züchterin ist maßgeblich).");
            if (fbDerivedFromGenotype > 0)
                notes.Add($"Farbschlag aus Genotyp: {fbDerivedFromGenotype} Tier(e) ohne expliziten Farbschlag-Namen wurden über den Katalog-Genotyp-Abgleich zugeordnet (band-aware Deep-Band-Tiere).");
            if (!execute) notes.Add("DRY-RUN: nichts gespeichert. /import/execute lädt die konfliktfreien Daten.");

            return new ImportReport(
                Executed: execute,
                Litters: new LitterSummary(litters.Count, littersCreated, littersExisting, derivedLitters, derivedLittersSkipped, litterParentFksDropped, parentFksBackfilled, littersWithoutDate),
                Animals: new AnimalSummary(
                    animals.Count, animalsCreated, linked, fbMatched, fbUnmatched, animalsExisting,
                    new QuarantineSummary(conflicts, stubs, dateOnly, ambiguous, conflicts + stubs),
                    parentLinksAdded, conflictsResolvedByDecision, fbDerivedFromGenotype),
                Photos: new PhotoSummary(photosAttached, photosMissing),
                Samples: samples,
                Notes: notes,
                Residency: new ResidencySummary(residentTotal, externalTotal, flippedByParentRule,
                    HerkunftBackfilled: herkunftBackfilled, FarbschlagWouldRebackfill: farbschlagWouldRebackfill));
        }

        private T? Load<T>(string file)
        {
            var path = Path.Combine(_sourceDir, file);
            if (!File.Exists(path)) return default;
            using var fs = File.OpenRead(path);
            return JsonSerializer.Deserialize<T>(fs, Json);
        }

        public static string ComposeGenotype(SourceGenotype g)
        {
            var tokens = LocusOrder.Select(locus =>
            {
                if (g.Mapped8locus.TryGetValue(locus, out var pair) && pair.Count == 2)
                    return StripCaret(pair[0]) + StripCaret(pair[1]);
                return "??";
            }).ToList();

            // GEN-3a contract (Kevin): Sls is appended LAST and ONLY for carriers — the
            // wild-type sl/sl is omitted so existing 8-locus strings stay unchanged. WP het
            // renders as the trailing token "Slsl"; S(l)S(l) is lethal so never appears.
            if (g.Mapped8locus.TryGetValue("Sls", out var sls) && sls.Count == 2
                && !(sls[0] == "sl" && sls[1] == "sl"))
            {
                tokens.Add(StripCaret(sls[0]) + StripCaret(sls[1]));
            }
            return string.Join(' ', tokens);
        }

        private static string StripCaret(string allele) => allele.Replace("^", "");

        /// <summary>CR-11: check if a composed animal genotype is compatible with a catalog canonical
        /// genotype. Both are space-separated 8-locus tokens (e.g. "aa CC DD ee GG PP spsp rere").
        /// "??" in either position is a wildcard. The first 8 tokens are compared; any trailing
        /// Sls token is ignored (it is outside the base 8-locus contract).</summary>
        private static bool GenotypePotentiallyMatches(string animalGeno, string catalogGeno)
        {
            var a = animalGeno.Split(' ', StringSplitOptions.RemoveEmptyEntries);
            var c = catalogGeno.Split(' ', StringSplitOptions.RemoveEmptyEntries);
            if (a.Length < 8 || c.Length < 8) return false;
            for (int i = 0; i < 8; i++)
            {
                if (a[i] == "??" || c[i] == "??") continue;
                if (!string.Equals(a[i], c[i], StringComparison.OrdinalIgnoreCase)) return false;
            }
            return true;
        }

        private static Gender InferGender(SourceAnimal a, HashSet<string> sires, HashSet<string> dams)
        {
            // Box colour (blue=male, white=female) is the authoritative breeder signal — prefer it
            // over sire/dam name inference (PEDIGREE-LINK, Julian 2026-06-06).
            if (string.Equals(a.Gender, "male", StringComparison.OrdinalIgnoreCase)) return Gender.male;
            if (string.Equals(a.Gender, "female", StringComparison.OrdinalIgnoreCase)) return Gender.female;

            var n = Normalize(StripZucht(a.Name));
            bool isSire = sires.Contains(n), isDam = dams.Contains(n);
            if (isSire && !isDam) return Gender.male;
            if (isDam && !isSire) return Gender.female;
            return Gender.unknown;
        }

        public static DateOnly? ParseDate(string s)
        {
            if (string.IsNullOrWhiteSpace(s)) return null;
            var m = Regex.Match(s, @"(\d{1,2})\.(\d{1,2})\.(\d{2,4})");
            if (!m.Success) return null;
            int d = int.Parse(m.Groups[1].Value), mo = int.Parse(m.Groups[2].Value);
            int y = int.Parse(m.Groups[3].Value);
            if (y < 100) y += 2000;
            try { return new DateOnly(y, mo, d); }
            catch { return null; }
        }

        private static string StripZucht(string name)
        {
            var n = Regex.Replace(name ?? "", @"\[.*?\]", " ");
            n = Regex.Replace(n, @"\s+(?:of|von\s+den|von\s+der|v\.\s?d\.|von)\s+.*$", "", RegexOptions.IgnoreCase);
            return n.Trim();
        }

        private static string Normalize(string name)
        {
            var n = (name ?? "").ToLowerInvariant();
            n = Regex.Replace(n, @"\bgen\.\b", " ");
            n = Regex.Replace(n, @"[^a-z0-9äöüß]", "");
            return n;
        }

        /// <summary>Dedup identity for parent resolution: normalized call-name + DOB.</summary>
        private static string NameDobKey(string name, DateOnly? dob) =>
            $"{Normalize(StripZucht(name))}|{dob:yyyy-MM-dd}";

        /// <summary>Resolve a chart-position parentRef (by role) to a known gerbil id, or null.</summary>
        private static Guid? ResolveParentGid(SourceAnimal a, string role, Dictionary<string, Guid> gidByNameDob)
        {
            var pr = a.ParentRefs.FirstOrDefault(p =>
                string.Equals(p.RoleGuess, role, StringComparison.OrdinalIgnoreCase));
            if (pr is null || string.IsNullOrWhiteSpace(pr.Name)) return null;
            return gidByNameDob.TryGetValue(NameDobKey(pr.Name, ParseDate(pr.Dob)), out var id) ? id : null;
        }

        /// <summary>Per-animal plan computed before any write so synthesis can run in dry-run too.</summary>
        private sealed record AnimalPlan(
            SourceAnimal A, Guid Gid, bool Exists, Guid? WurfLitterId,
            Guid? CurrentLitterId, Guid? ColorVarietyId, Gender Gender,
            Guid? CurrentColorVarietyId = null);

        /// <summary>A litter synthesized from chart-position parentRefs (PEDIGREE-LINK).</summary>
        private sealed record SynthLitter(Guid Id, Guid? Father, Guid? Mother, DateOnly? Date, string Confidence);
    }
}