755 lines
43 KiB
C#
755 lines
43 KiB
C#
using System.Text.Json;
|
||
using System.Text.RegularExpressions;
|
||
using GerbilManagerWebAPI.Models;
|
||
using GerbilManagerWebAPI.Services;
|
||
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.HasValue ? x.Date.Value.ToString("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);
|
||
}
|
||
|
||
// FIX-IMPORT-CYCLE: track deferred synth litter parent FKs (populated in PASS 2 below).
|
||
// Synth litters are added with null FatherId/MotherId to break the Gerbil↔Litter cycle;
|
||
// the actual FKs are applied AFTER SaveChanges once all gerbils are persisted.
|
||
var synthLitterPendingParents = new Dictionary<Guid, (Guid? Father, Guid? Mother)>();
|
||
|
||
// PASS 2: stage synthesized litters (parents already guarded above). Litters are added
|
||
// with FatherId/MotherId = null (deferred) so that the SaveChanges below has only a
|
||
// one-directional Gerbil→Litter dependency — no Litter→Gerbil FKs in the same batch,
|
||
// which would cause EF's topo-sort to throw "circular dependency detected".
|
||
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.HasValue ? l.Date.Value.ToString("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.HasValue ? sl.Date.Value.ToString("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;
|
||
}
|
||
// Defer FatherId/MotherId: both parent gerbils and offspring gerbils may be [Added]
|
||
// in this same batch. Setting them now causes EF circular dependency
|
||
// (Gerbil[Added] ← Litter.MotherId [Added] ← Gerbil.LitterId [Added]).
|
||
synthLitterPendingParents[sl.Id] = (sl.Father, sl.Mother);
|
||
_db.Litters.Add(new Litter
|
||
{
|
||
Id = sl.Id,
|
||
Name = $"Wurf (aus Diagramm) {(sl.Date.HasValue ? sl.Date.Value.ToString("yyyy-MM-dd") : "")}".Trim(),
|
||
Date = sl.Date,
|
||
FatherId = null, // deferred — applied after gerbils SaveChanges
|
||
MotherId = null, // deferred — applied after gerbils SaveChanges
|
||
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)
|
||
{
|
||
var importedGerbil = new Gerbil
|
||
{
|
||
Id = p.Gid,
|
||
Name = p.A.Name,
|
||
Gender = p.Gender,
|
||
Status = GerbilStatus.Breeding,
|
||
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,
|
||
}),
|
||
};
|
||
GerbilStatusService.Apply(importedGerbil, DateOnly.FromDateTime(DateTime.UtcNow));
|
||
_db.Gerbils.Add(importedGerbil);
|
||
}
|
||
|
||
// 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();
|
||
|
||
// Apply deferred synth litter parent FKs — all new gerbils are now persisted in the DB,
|
||
// so no cycle. FK guard already applied above (persisted set); values in the dict are safe.
|
||
if (execute && synthLitterPendingParents.Count > 0)
|
||
{
|
||
foreach (var (litId, (f, m)) in synthLitterPendingParents)
|
||
{
|
||
var row = await _db.Litters.FindAsync(litId);
|
||
if (row is null) continue;
|
||
if (f is not null) row.FatherId = f;
|
||
if (m is not null) row.MotherId = m;
|
||
}
|
||
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 (Zucht der kleinen 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);
|
||
}
|
||
}
|