using System.Data;
using System.Text;
using Dapper;
using PlotLine.Models;

namespace PlotLine.Data;

public interface IImportRepository
{
    Task<bool> ProjectTitleExistsAsync(string projectTitle, int userId);
    Task<ImportResult> ImportAsync(PlotLineImportPackage package, string? importedProjectTitle, int userId);
    Task<IReadOnlyDictionary<string, int>> GetIdentityMaxValuesAsync(IEnumerable<string> tableNames);
    Task<ProjectBackupRelationshipImportLookups> GetRelationshipImportLookupsAsync();
    Task<ProjectBackupRelationshipImportDiagnostics> GetRelationshipImportDiagnosticsAsync(int projectId);
    Task<int> CreateProjectShellAsync(string projectName, string? description, int userId);
    Task ApplyProjectBackupJsonAsync(int projectId, string jsonData);
}

public sealed class ImportRepository(ISqlConnectionFactory connectionFactory) : IImportRepository
{
    private const string ImportMarker = "Imported via JSON Import Phase 1F";

    public async Task<bool> ProjectTitleExistsAsync(string projectTitle, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        var count = await connection.ExecuteScalarAsync<int>(
            "dbo.Project_ExistsByNameForUser",
            new { ProjectName = projectTitle, UserID = userId },
            commandType: CommandType.StoredProcedure);

        return count > 0;
    }

    public async Task<IReadOnlyDictionary<string, int>> GetIdentityMaxValuesAsync(IEnumerable<string> tableNames)
    {
        using var connection = connectionFactory.CreateConnection();
        var requestedTables = tableNames.Distinct(StringComparer.OrdinalIgnoreCase).ToList();
        var rows = await connection.QueryAsync<IdentityMaxRow>(
            "dbo.ProjectBackup_GetIdentityMaxValues",
            new { TableNames = string.Join(',', requestedTables) },
            commandType: CommandType.StoredProcedure);

        return rows.ToDictionary(row => row.TableName, row => row.MaxID, StringComparer.OrdinalIgnoreCase);
    }

    public async Task<ProjectBackupRelationshipImportLookups> GetRelationshipImportLookupsAsync()
    {
        using var connection = connectionFactory.CreateConnection();
        using var result = await connection.QueryMultipleAsync(
            "dbo.ProjectBackup_GetRelationshipImportLookups",
            commandType: CommandType.StoredProcedure);

        return new ProjectBackupRelationshipImportLookups
        {
            RevisionStatuses = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            TimeModes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            DurationUnits = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            TimeConfidences = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            LocationTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            LocationRelationshipTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            PlotLineTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            PlotImportance = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            ThreadTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            ThreadStatuses = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            ThreadEventTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            PlotEventTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            AssetKinds = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            AssetStates = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            AssetEventTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            AssetDependencyTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            AssetCustodyEventTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            CustodyRoles = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            CharacterRoleTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            PresenceTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            KnowledgeStates = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            SceneDependencyTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            SceneNoteTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            SceneAttachmentTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            ScenePurposeTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            SceneMetricTypes = (await result.ReadAsync<ProjectBackupLookupRow>()).ToList(),
            RelationshipTypes = (await result.ReadAsync<RelationshipType>()).ToList(),
            RelationshipStates = (await result.ReadAsync<RelationshipState>()).ToList()
        };
    }

    public async Task<ProjectBackupRelationshipImportDiagnostics> GetRelationshipImportDiagnosticsAsync(int projectId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<ProjectBackupRelationshipImportDiagnostics>(
            "dbo.ProjectBackup_RelationshipImportDiagnostics",
            new { ProjectID = projectId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<int> CreateProjectShellAsync(string projectName, string? description, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<int>(
            "dbo.Project_Save",
            new { ProjectID = 0, ProjectName = projectName, Description = description, UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task ApplyProjectBackupJsonAsync(int projectId, string jsonData)
    {
        using var connection = connectionFactory.CreateConnection();
        await connection.ExecuteAsync(
            "dbo.ProjectRestore_ApplyFromJson",
            new
            {
                ProjectID = projectId,
                SourceRestorePointID = (int?)null,
                SafetyRestorePointID = (int?)null,
                JsonData = jsonData
            },
            commandType: CommandType.StoredProcedure,
            commandTimeout: 180);
    }

    public async Task<ImportResult> ImportAsync(PlotLineImportPackage package, string? importedProjectTitle, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        connection.Open();
        using var transaction = connection.BeginTransaction();

        try
        {
            var booksCreated = 0;
            var chaptersCreated = 0;
            var scenesCreated = 0;
            var charactersCreated = 0;
            var locationsCreated = 0;
            var appearancesCreated = 0;
            var plotLinesCreated = 0;
            var plotThreadsCreated = 0;
            var threadEventsCreated = 0;
            var storyAssetsCreated = 0;
            var assetStatesCreated = 0;
            var assetEventsCreated = 0;
            var assetCustodyEventsCreated = 0;
            var sceneDependenciesCreated = 0;
            var skippedFields = GetSkippedOptionalFields(package);
            var unresolvedWarnings = GetUnresolvedWarnings(package);
            var sceneMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
            var projectName = string.IsNullOrWhiteSpace(importedProjectTitle)
                ? Clean(package.Project.Title)
                : Clean(importedProjectTitle);
            var lookupIds = await GetLookupIdsAsync(connection, transaction);
            var projectId = await connection.QuerySingleAsync<int>(
                "dbo.Project_Save",
                new
                {
                    ProjectID = 0,
                    ProjectName = projectName,
                    Description = CombineText(
                        package.Project.Description,
                        package.Project.Notes,
                        ImportMetadata(package)),
                    UserID = userId
                },
                transaction,
                commandType: CommandType.StoredProcedure);

            var characterMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
            foreach (var character in package.Characters)
            {
                var characterName = Clean(DisplayName(character.DisplayName, character.Name));
                if (characterName.Length == 0 || ResolveId(characterMap, character.Name, package.Aliases.Characters).HasValue)
                {
                    continue;
                }

                var characterId = await connection.QuerySingleAsync<int>(
                    "dbo.Character_Save",
                    new
                    {
                        CharacterID = 0,
                        ProjectID = projectId,
                        CharacterName = characterName,
                        ShortName = Truncate(character.ShortName, 100),
                        Sex = (string?)null,
                        BirthDate = (DateTime?)null,
                        AgeAtSeriesStart = (int?)null,
                        AgeReferenceSceneID = (int?)null,
                        Height = (string?)null,
                        EyeColour = (string?)null,
                        CharacterImportance = NormalizeCharacterImportance(character.Role, character.Importance),
                        DefaultDescription = CombineText(
                            character.Description,
                            character.Notes,
                            ImportMarker)
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);

                AddLookupKeys(characterMap, character.Name, characterId);
                AddLookupKeys(characterMap, character.DisplayName, characterId);
                charactersCreated++;
            }

            var locationMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
            foreach (var location in package.Locations)
            {
                var locationName = Clean(DisplayName(location.DisplayName, location.Name));
                if (locationName.Length == 0 || ResolveId(locationMap, location.Name, package.Aliases.Locations).HasValue)
                {
                    continue;
                }

                var locationId = await connection.QuerySingleAsync<int>(
                    "dbo.Location_Save",
                    new
                    {
                        LocationID = 0,
                        ProjectID = projectId,
                        ParentLocationID = (int?)null,
                        LocationName = locationName,
                        LocationTypeID = LookupLocationTypeId(lookupIds, location.Type),
                        Description = CombineText(location.Description, location.Notes, ImportMarker)
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);

                AddLookupKeys(locationMap, location.Name, locationId);
                AddLookupKeys(locationMap, location.DisplayName, locationId);
                locationsCreated++;
            }

            foreach (var location in package.Locations.Where(x => !string.IsNullOrWhiteSpace(x.ParentLocationName)))
            {
                var locationId = ResolveId(locationMap, location.Name, package.Aliases.Locations);
                var parentLocation = ResolveId(locationMap, location.ParentLocationName, package.Aliases.Locations);
                if (!locationId.HasValue || !parentLocation.HasValue)
                {
                    continue;
                }

                await connection.QuerySingleAsync<int>(
                    "dbo.Location_Save",
                    new
                    {
                        LocationID = locationId.Value,
                        ProjectID = projectId,
                        ParentLocationID = parentLocation.Value,
                        LocationName = Clean(DisplayName(location.DisplayName, location.Name)),
                        LocationTypeID = LookupLocationTypeId(lookupIds, location.Type),
                        Description = CombineText(location.Description, location.Notes, ImportMarker)
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);
            }

            foreach (var book in package.Books.OrderBy(x => x.SeriesOrder))
            {
                var bookId = await connection.QuerySingleAsync<int>(
                    "dbo.Book_Save",
                    new
                    {
                        BookID = 0,
                        ProjectID = projectId,
                        BookTitle = Clean(FormatBookTitle(book)),
                        BookNumber = book.SeriesOrder,
                        Description = CombineText(
                            book.Description,
                            book.DependsOnPreviousBook ? "Depends on previous book." : null,
                            book.Notes,
                            ImportMarker)
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);
                booksCreated++;

                foreach (var chapter in book.Chapters.OrderBy(x => x.Order))
                {
                    var chapterId = await connection.QuerySingleAsync<int>(
                        "dbo.Chapter_Save",
                        new
                        {
                            ChapterID = 0,
                            BookID = bookId,
                            chapter.ChapterNumber,
                            ChapterTitle = Clean(chapter.Title),
                            Summary = CombineText(chapter.Summary, chapter.Notes, ImportMarker),
                            RevisionStatusID = lookupIds.RevisionStatusID
                        },
                        transaction,
                        commandType: CommandType.StoredProcedure);
                    chaptersCreated++;

                    var orderedScenes = chapter.Scenes.OrderBy(x => x.Order).ToList();
                    var defaultSceneId = 0;
                    if (orderedScenes.Count > 0)
                    {
                        defaultSceneId = (await connection.QueryAsync<Scene>(
                            "dbo.Scene_ListByChapter",
                            new { ChapterID = chapterId },
                            transaction,
                            commandType: CommandType.StoredProcedure))
                            .OrderBy(x => x.SortOrder)
                            .ThenBy(x => x.SceneID)
                            .Select(x => x.SceneID)
                            .FirstOrDefault();
                    }

                    for (var sceneIndex = 0; sceneIndex < orderedScenes.Count; sceneIndex++)
                    {
                        var scene = orderedScenes[sceneIndex];
                        var primaryLocationId = ResolveId(locationMap, scene.LocationName, package.Aliases.Locations);
                        var povCharacterId = ResolveId(characterMap, scene.PovCharacterName, package.Aliases.Characters);
                        var sceneId = await connection.QuerySingleAsync<int>(
                            "dbo.Scene_Save",
                            new
                            {
                                SceneID = sceneIndex == 0 ? defaultSceneId : 0,
                                ChapterID = chapterId,
                                scene.SceneNumber,
                                SceneTitle = Clean(scene.Title),
                                Summary = CombineText(
                                    scene.Summary,
                                    povCharacterId.HasValue ? null : MetadataLine("POV", scene.PovCharacterName),
                                    primaryLocationId.HasValue ? null : MetadataLine("Location", scene.LocationName),
                                    scene.Notes,
                                    ImportMarker),
                                TimeModeID = lookupIds.TimeModeID,
                                StartDateTime = (DateTime?)null,
                                EndDateTime = (DateTime?)null,
                                DurationAmount = (decimal?)null,
                                DurationUnitID = (int?)null,
                                RelativeTimeText = Truncate(scene.DateTimeText, 200),
                                TimeConfidenceID = lookupIds.TimeConfidenceID,
                                ScenePurposeNotes = CombineText(scene.PurposeText),
                                SceneOutcomeNotes = CombineText(scene.OutcomeText),
                                RevisionStatusID = lookupIds.RevisionStatusID,
                                PrimaryLocationID = primaryLocationId
                            },
                            transaction,
                            commandType: CommandType.StoredProcedure);
                        if (povCharacterId.HasValue)
                        {
                            await connection.ExecuteAsync(
                                "dbo.Scene_UpdatePovCharacter",
                                new { SceneID = sceneId, POVCharacterID = povCharacterId.Value },
                                transaction,
                                commandType: CommandType.StoredProcedure);
                        }

                        foreach (var appearance in BuildSceneAppearances(scene, povCharacterId, characterMap, package.Aliases.Characters))
                        {
                            await connection.QuerySingleAsync<int>(
                                "dbo.SceneCharacter_Save",
                                new
                                {
                                    SceneCharacterID = 0,
                                    SceneID = sceneId,
                                    CharacterID = appearance.CharacterId,
                                    RoleInSceneTypeID = ResolveRoleTypeId(lookupIds, appearance.RoleInScene),
                                    PresenceTypeID = ResolvePresenceTypeId(lookupIds, appearance.RoleInScene),
                                    LocationID = primaryLocationId,
                                    EntryLocationID = (int?)null,
                                    ExitLocationID = (int?)null,
                                    AppearanceNotes = CombineText(
                                        MetadataLine("Imported role", appearance.RoleInScene),
                                        MetadataLine("Importance", appearance.Importance?.ToString()),
                                        appearance.Notes),
                                    OutfitDescription = (string?)null,
                                    PhysicalCondition = (string?)null,
                                    EmotionalState = (string?)null,
                                    KnowledgeNotes = (string?)null
                                },
                                transaction,
                                commandType: CommandType.StoredProcedure);
                            appearancesCreated++;
                        }
                        scenesCreated++;
                        sceneMap[SceneKey(book.SeriesOrder, chapter.Order, scene.Order)] = sceneId;
                    }
                }
            }

            foreach (var plotLine in package.PlotLines)
            {
                if (string.IsNullOrWhiteSpace(plotLine.Name))
                {
                    continue;
                }

                var plotLineId = await connection.QuerySingleAsync<int>(
                    "dbo.PlotLine_Save",
                    new
                    {
                        PlotLineID = 0,
                        ProjectID = projectId,
                        BookID = (int?)null,
                        PlotLineName = Clean(DisplayName(plotLine.DisplayName, plotLine.Name)),
                        PlotLineTypeID = ResolvePlotLineTypeId(lookupIds, plotLine.Type),
                        PlotImportanceID = ResolvePlotImportanceId(lookupIds, plotLine),
                        Description = CombineText(
                            plotLine.Description,
                            MetadataLine("Type", plotLine.Type),
                            MetadataLine("Importance", plotLine.Importance?.ToString()),
                            plotLine.Notes,
                            ImportMarker),
                        ParentPlotLineID = (int?)null,
                        EmergesFromPlotLineID = (int?)null,
                        SortOrder = (int?)null,
                        Colour = "#2f6f63",
                        IsVisibleOnTimeline = true
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);
                plotLinesCreated++;

                foreach (var thread in plotLine.Threads)
                {
                    if (string.IsNullOrWhiteSpace(thread.Name))
                    {
                        continue;
                    }

                    var threadId = await connection.QuerySingleAsync<int>(
                        "dbo.PlotThread_Save",
                        new
                        {
                            PlotThreadID = 0,
                            PlotLineID = plotLineId,
                            ThreadTitle = Clean(DisplayName(thread.DisplayName, thread.Name)),
                            ThreadTypeID = ResolveThreadTypeId(lookupIds, thread.Type),
                            ThreadStatusID = ResolveThreadStatusId(lookupIds, thread.Status),
                            Importance = ClampImportance(thread.Importance),
                            Summary = CombineText(thread.Description, MetadataLine("Status", thread.Status), thread.Notes, ImportMarker),
                            IntroducedSceneID = (int?)null,
                            PlannedResolutionSceneID = (int?)null,
                            ActualResolutionSceneID = (int?)null
                        },
                        transaction,
                        commandType: CommandType.StoredProcedure);
                    plotThreadsCreated++;

                    var importedEventSceneIds = new HashSet<int>();
                    foreach (var threadEvent in thread.Events)
                    {
                        if (!TryResolveThreadEventScene(sceneMap, threadEvent, out var sceneId) || importedEventSceneIds.Contains(sceneId))
                        {
                            continue;
                        }

                        await connection.QuerySingleAsync<int>(
                            "dbo.ThreadEvent_Save",
                            new
                            {
                                ThreadEventID = 0,
                                PlotThreadID = threadId,
                                SceneID = sceneId,
                                EventTypeID = ResolveThreadEventTypeId(lookupIds, threadEvent.EventType),
                                EventTitle = Truncate(Clean(threadEvent.Title).Length == 0 ? Clean(threadEvent.Summary) : threadEvent.Title, 200) ?? "Imported thread event",
                                EventDescription = CombineText(
                                    threadEvent.Summary,
                                    MetadataLine("Event type", threadEvent.EventType),
                                    MetadataLine("Impact", threadEvent.Impact),
                                    MetadataLine("Importance", threadEvent.Importance?.ToString()),
                                    threadEvent.Notes,
                                    ImportMarker)
                            },
                            transaction,
                            commandType: CommandType.StoredProcedure);
                        importedEventSceneIds.Add(sceneId);
                        threadEventsCreated++;
                    }
                }
            }

            var firstSceneId = sceneMap.Values.FirstOrDefault();
            foreach (var storyAsset in package.StoryAssets)
            {
                if (string.IsNullOrWhiteSpace(storyAsset.Name))
                {
                    continue;
                }

                var initialLocationId = ResolveId(locationMap, storyAsset.InitialLocationName, package.Aliases.Locations);
                var initialOwnerCharacterId = ResolveId(characterMap, storyAsset.InitialOwnerCharacterName, package.Aliases.Characters);
                var storyAssetId = await connection.QuerySingleAsync<int>(
                    "dbo.StoryAsset_Save",
                    new
                    {
                        StoryAssetID = 0,
                        ProjectID = projectId,
                        AssetName = Clean(DisplayName(storyAsset.DisplayName, storyAsset.Name)),
                        AssetKindID = ResolveAssetKindId(lookupIds, storyAsset.Type),
                        Description = CombineText(
                            storyAsset.Description,
                            MetadataLine("Type", storyAsset.Type),
                            initialOwnerCharacterId.HasValue ? null : MetadataLine("Initial owner", storyAsset.InitialOwnerCharacterName),
                            initialLocationId.HasValue ? null : MetadataLine("Initial location", storyAsset.InitialLocationName),
                            storyAsset.Notes,
                            ImportMarker),
                        Importance = ClampImportance(storyAsset.Importance),
                        CurrentStateID = ResolveAssetStateId(lookupIds, storyAsset.States.FirstOrDefault(x => !string.IsNullOrWhiteSpace(x.StateName))?.StateName),
                        CurrentLocationID = initialLocationId,
                        IsResolved = false
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);
                storyAssetsCreated++;

                if (initialOwnerCharacterId.HasValue && firstSceneId > 0)
                {
                    await connection.QuerySingleAsync<int>(
                        "dbo.AssetCustodyEvent_Save",
                        new
                        {
                            AssetCustodyEventID = 0,
                            StoryAssetID = storyAssetId,
                            SceneID = firstSceneId,
                            AssetCustodyEventTypeID = ResolveAssetCustodyEventTypeId(lookupIds, "Has"),
                            Description = CombineText("Initial owner imported from JSON.", ImportMarker),
                            CustodianNames = storyAsset.InitialOwnerCharacterName,
                            CustodyRoleID = ResolveCustodyRoleId(lookupIds, "Has Custody")
                        },
                        transaction,
                        commandType: CommandType.StoredProcedure);
                    assetCustodyEventsCreated++;
                }

                var importedAssetStateSceneIds = new HashSet<int>();
                foreach (var state in storyAsset.States)
                {
                    if (!TryResolveAssetStateScene(sceneMap, state, out var sceneId) || importedAssetStateSceneIds.Contains(sceneId))
                    {
                        continue;
                    }

                    await connection.QuerySingleAsync<int>(
                        "dbo.AssetEvent_Save",
                        new
                        {
                            AssetEventID = 0,
                            StoryAssetID = storyAssetId,
                            SceneID = sceneId,
                            AssetEventTypeID = ResolveAssetEventTypeId(lookupIds, "State Changed"),
                            FromStateID = (int?)null,
                            ToStateID = ResolveAssetStateId(lookupIds, state.StateName),
                            EventTitle = Truncate(Clean(state.StateName).Length == 0 ? "Imported asset state" : state.StateName, 200),
                            EventDescription = CombineText(state.Description, state.Notes, ImportMarker)
                        },
                        transaction,
                        commandType: CommandType.StoredProcedure);
                    importedAssetStateSceneIds.Add(sceneId);
                    assetStatesCreated++;
                }

                var importedAssetEventSceneIds = new HashSet<int>();
                foreach (var assetEvent in storyAsset.Events)
                {
                    if (!TryResolveAssetEventScene(sceneMap, assetEvent, out var sceneId) || importedAssetEventSceneIds.Contains(sceneId))
                    {
                        continue;
                    }

                    var eventLocationId = ResolveId(locationMap, assetEvent.LocationName, package.Aliases.Locations);
                    if (eventLocationId.HasValue)
                    {
                        await connection.QuerySingleAsync<int>(
                            "dbo.SceneAssetLocation_Save",
                            new
                            {
                                SceneAssetLocationID = 0,
                                SceneID = sceneId,
                                StoryAssetID = storyAssetId,
                                LocationID = eventLocationId.Value,
                                Description = CombineText(assetEvent.Summary, assetEvent.Notes, ImportMarker),
                                UpdateCurrentLocation = true
                            },
                            transaction,
                            commandType: CommandType.StoredProcedure);
                    }

                    await connection.QuerySingleAsync<int>(
                        "dbo.AssetEvent_Save",
                        new
                        {
                            AssetEventID = 0,
                            StoryAssetID = storyAssetId,
                            SceneID = sceneId,
                            AssetEventTypeID = ResolveAssetEventTypeId(lookupIds, assetEvent.EventType),
                            FromStateID = (int?)null,
                            ToStateID = (int?)null,
                            EventTitle = Truncate(Clean(assetEvent.Title).Length == 0 ? Clean(assetEvent.Summary) : assetEvent.Title, 200) ?? "Imported asset event",
                            EventDescription = CombineText(
                                assetEvent.Summary,
                                MetadataLine("Event type", assetEvent.EventType),
                                MetadataLine("Character", assetEvent.CharacterName),
                                eventLocationId.HasValue ? null : MetadataLine("Location", assetEvent.LocationName),
                                assetEvent.Notes,
                                ImportMarker)
                        },
                        transaction,
                        commandType: CommandType.StoredProcedure);
                    importedAssetEventSceneIds.Add(sceneId);
                    assetEventsCreated++;
                }
            }

            var importedSceneDependencyKeys = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            foreach (var dependency in package.SceneDependencies)
            {
                if (!TryResolveSceneReference(sceneMap, dependency.FromScene, out var sourceSceneId)
                    || !TryResolveSceneReference(sceneMap, dependency.ToScene, out var targetSceneId)
                    || sourceSceneId == targetSceneId)
                {
                    continue;
                }

                var dependencyTypeId = ResolveSceneDependencyTypeId(lookupIds, dependency.DependencyType);
                var dependencyKey = $"{sourceSceneId}/{targetSceneId}/{dependencyTypeId}";
                if (!importedSceneDependencyKeys.Add(dependencyKey))
                {
                    continue;
                }

                await connection.QuerySingleAsync<int>(
                    "dbo.SceneDependency_Save",
                    new
                    {
                        SceneDependencyID = 0,
                        ProjectID = projectId,
                        SourceSceneID = sourceSceneId,
                        TargetSceneID = targetSceneId,
                        SceneDependencyTypeID = dependencyTypeId,
                        Description = CombineText(
                            dependency.Description,
                            MetadataLine("Dependency type", dependency.DependencyType),
                            MetadataLine("Strength", dependency.Strength),
                            dependency.Notes,
                            ImportMarker),
                        IsHardDependency = ResolveSceneDependencyStrength(dependency.Strength)
                    },
                    transaction,
                    commandType: CommandType.StoredProcedure);
                sceneDependenciesCreated++;
            }

            transaction.Commit();
            return new ImportResult
            {
                Succeeded = true,
                ProjectID = projectId,
                ProjectName = projectName,
                BooksCreated = booksCreated,
                ChaptersCreated = chaptersCreated,
                ScenesCreated = scenesCreated,
                CharactersCreated = charactersCreated,
                LocationsCreated = locationsCreated,
                SceneCharacterAppearancesCreated = appearancesCreated,
                PlotLinesCreated = plotLinesCreated,
                PlotThreadsCreated = plotThreadsCreated,
                ThreadEventsCreated = threadEventsCreated,
                StoryAssetsCreated = storyAssetsCreated,
                AssetStatesCreated = assetStatesCreated,
                AssetEventsCreated = assetEventsCreated,
                AssetCustodyEventsCreated = assetCustodyEventsCreated,
                SceneDependenciesCreated = sceneDependenciesCreated,
                SkippedItemCount = skippedFields.Count,
                UnresolvedReferenceCount = unresolvedWarnings.Count,
                SkippedOptionalFields = skippedFields,
                UnresolvedWarnings = unresolvedWarnings,
                Message = "Import complete."
            };
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }

    private static async Task<ImportLookupIds> GetLookupIdsAsync(IDbConnection connection, IDbTransaction transaction)
    {
        using var result = await connection.QueryMultipleAsync(
            "dbo.Import_LookupIds",
            transaction: transaction,
            commandType: CommandType.StoredProcedure);
        var requiredLookups = await result.ReadSingleAsync<ImportRequiredLookupRow>();
        var locationTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var roleTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var presenceTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var plotLineTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var plotImportance = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var threadTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var threadStatuses = (await result.ReadAsync<ImportStatusLookupRow>()).ToDictionary(x => x.StatusName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var threadEventTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var sceneDependencyTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var assetKinds = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var assetStates = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var assetEventTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var assetCustodyEventTypes = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);
        var custodyRoles = (await result.ReadAsync<ImportLookupRow>()).ToDictionary(x => x.TypeName, x => x.LookupID, StringComparer.OrdinalIgnoreCase);

        if (!requiredLookups.RevisionStatusID.HasValue || !requiredLookups.TimeModeID.HasValue || !requiredLookups.TimeConfidenceID.HasValue)
        {
            throw new InvalidOperationException("Import lookup values are missing. Run the core database script before importing.");
        }

        return new ImportLookupIds(
            requiredLookups.RevisionStatusID.Value,
            requiredLookups.TimeModeID.Value,
            requiredLookups.TimeConfidenceID.Value,
            locationTypes,
            roleTypes,
            presenceTypes,
            plotLineTypes,
            plotImportance,
            threadTypes,
            threadStatuses,
            threadEventTypes,
            sceneDependencyTypes,
            assetKinds,
            assetStates,
            assetEventTypes,
            assetCustodyEventTypes,
            custodyRoles);
    }

    private static string FormatBookTitle(ImportBookDto book)
        => string.IsNullOrWhiteSpace(book.Subtitle) ? book.Title : $"{book.Title}: {book.Subtitle}";

    private static string ImportMetadata(PlotLineImportPackage package)
    {
        var source = string.IsNullOrWhiteSpace(package.Source) ? null : $"Source: {package.Source.Trim()}";
        return CombineText(
            ImportMarker,
            $"Imported at {DateTime.UtcNow:yyyy-MM-dd HH:mm} UTC",
            source,
            MetadataLine("Package created by", package.CreatedBy),
            MetadataLine("Package created date", package.CreatedDate),
            MetadataLine("Package description", package.Description)) ?? ImportMarker;
    }

    private static IReadOnlyList<string> GetSkippedOptionalFields(PlotLineImportPackage package)
    {
        var skipped = new List<string>();
        var characterNames = BuildNameKeySet(package.Characters.SelectMany(x => new[] { x.Name, x.DisplayName }));
        var locationNames = BuildNameKeySet(package.Locations.SelectMany(x => new[] { x.Name, x.DisplayName }));

        if (package.Books.Any(book => book.Chapters.Any(chapter => chapter.Scenes.Any(scene => !string.IsNullOrWhiteSpace(scene.PovCharacterName) && !ContainsNameKey(characterNames, ResolveAlias(scene.PovCharacterName, package.Aliases.Characters))))))
        {
            skipped.Add("POV character names were stored as plain text in scene summaries; Character records were not created.");
        }

        if (package.Books.Any(book => book.Chapters.Any(chapter => chapter.Scenes.Any(scene => !string.IsNullOrWhiteSpace(scene.LocationName) && !ContainsNameKey(locationNames, ResolveAlias(scene.LocationName, package.Aliases.Locations))))))
        {
            skipped.Add("Location names were stored as plain text in scene summaries; Location records were not created.");
        }

        if (package.PlotLines.Any(plotLine => plotLine.Importance.HasValue))
        {
            skipped.Add("Plot line importance was stored as text in plot line descriptions; PlotLine records do not have an importance column.");
        }

        if (package.PlotLines.Any(plotLine => plotLine.Threads.Any(thread => thread.Events.Any(threadEvent => threadEvent.Importance.HasValue || !string.IsNullOrWhiteSpace(threadEvent.Impact)))))
        {
            skipped.Add("Thread event impact and importance were stored in event descriptions.");
        }

        if (package.StoryAssets.Any(asset => asset.Events.Any(assetEvent => !string.IsNullOrWhiteSpace(assetEvent.CharacterName))))
        {
            skipped.Add("Asset event character names were stored in event descriptions.");
        }

        if (package.SceneDependencies.Any(dependency => !string.IsNullOrWhiteSpace(dependency.DependencyType) || !string.IsNullOrWhiteSpace(dependency.Strength)))
        {
            skipped.Add("Scene dependency type and strength labels were mapped to existing dependency fields and preserved in descriptions.");
        }

        return skipped;
    }

    private static IReadOnlyList<string> GetUnresolvedWarnings(PlotLineImportPackage package)
    {
        var warnings = new List<string>();
        var characterNames = BuildNameKeySet(package.Characters.SelectMany(x => new[] { x.Name, x.DisplayName }));
        var locationNames = BuildNameKeySet(package.Locations.SelectMany(x => new[] { x.Name, x.DisplayName }));

        foreach (var name in package.Books.SelectMany(book => book.Chapters).SelectMany(chapter => chapter.Scenes).Select(scene => scene.PovCharacterName?.Trim()).Where(name => !string.IsNullOrWhiteSpace(name) && !ContainsNameKey(characterNames, ResolveAlias(name, package.Aliases.Characters))).Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unmatched POV character: {name}");
        }

        foreach (var name in package.Books.SelectMany(book => book.Chapters).SelectMany(chapter => chapter.Scenes).Select(scene => scene.LocationName?.Trim()).Where(name => !string.IsNullOrWhiteSpace(name) && !ContainsNameKey(locationNames, ResolveAlias(name, package.Aliases.Locations))).Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unmatched scene location: {name}");
        }

        foreach (var name in package.Books.SelectMany(book => book.Chapters).SelectMany(chapter => chapter.Scenes).SelectMany(scene => scene.SceneCharacters).Select(sceneCharacter => sceneCharacter.CharacterName?.Trim()).Where(name => !string.IsNullOrWhiteSpace(name) && !ContainsNameKey(characterNames, ResolveAlias(name, package.Aliases.Characters))).Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unmatched scene character: {name}");
        }

        var sceneKeys = package.Books
            .SelectMany(book => book.Chapters.SelectMany(chapter => chapter.Scenes.Select(scene => SceneKey(book.SeriesOrder, chapter.Order, scene.Order))))
            .ToHashSet(StringComparer.OrdinalIgnoreCase);
        foreach (var reference in package.PlotLines.SelectMany(plotLine => plotLine.Threads.SelectMany(thread => thread.Events.Select((threadEvent, index) => new
                 {
                     PlotLineName = DisplayName(plotLine.DisplayName, plotLine.Name),
                     ThreadName = DisplayName(thread.DisplayName, thread.Name),
                     EventTitle = string.IsNullOrWhiteSpace(threadEvent.Title) ? $"Event {index + 1}" : threadEvent.Title.Trim(),
                     Event = threadEvent
                 })))
                 .Where(x => !x.Event.BookOrder.HasValue || !x.Event.ChapterOrder.HasValue || !x.Event.SceneOrder.HasValue || !sceneKeys.Contains(SceneKey(x.Event.BookOrder.GetValueOrDefault(), x.Event.ChapterOrder.GetValueOrDefault(), x.Event.SceneOrder.GetValueOrDefault())))
                 .Select(x => $"{x.PlotLineName} / {x.ThreadName} / {x.EventTitle}")
                 .Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unresolved thread event scene reference: {reference}");
        }

        foreach (var name in package.StoryAssets.Select(asset => asset.InitialOwnerCharacterName?.Trim())
                     .Concat(package.StoryAssets.SelectMany(asset => asset.Events.Select(assetEvent => assetEvent.CharacterName?.Trim())))
                     .Where(name => !string.IsNullOrWhiteSpace(name) && !ContainsNameKey(characterNames, ResolveAlias(name, package.Aliases.Characters)))
                     .Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unmatched asset character: {name}");
        }

        foreach (var name in package.StoryAssets.Select(asset => asset.InitialLocationName?.Trim())
                     .Concat(package.StoryAssets.SelectMany(asset => asset.Events.Select(assetEvent => assetEvent.LocationName?.Trim())))
                     .Where(name => !string.IsNullOrWhiteSpace(name) && !ContainsNameKey(locationNames, ResolveAlias(name, package.Aliases.Locations)))
                     .Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unmatched asset location: {name}");
        }

        foreach (var reference in package.StoryAssets.SelectMany(asset => asset.States.Select((state, index) => new
                 {
                     AssetName = DisplayName(asset.DisplayName, asset.Name),
                     Label = string.IsNullOrWhiteSpace(state.StateName) ? $"State {index + 1}" : state.StateName.Trim(),
                     State = state
                 }))
                 .Where(x => !x.State.BookOrder.HasValue || !x.State.ChapterOrder.HasValue || !x.State.SceneOrder.HasValue || !sceneKeys.Contains(SceneKey(x.State.BookOrder.GetValueOrDefault(), x.State.ChapterOrder.GetValueOrDefault(), x.State.SceneOrder.GetValueOrDefault())))
                 .Select(x => $"{x.AssetName} / state / {x.Label}")
                 .Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unresolved asset state scene reference: {reference}");
        }

        foreach (var reference in package.StoryAssets.SelectMany(asset => asset.Events.Select((assetEvent, index) => new
                 {
                     AssetName = DisplayName(asset.DisplayName, asset.Name),
                     Label = string.IsNullOrWhiteSpace(assetEvent.Title) ? $"Event {index + 1}" : assetEvent.Title.Trim(),
                     Event = assetEvent
                 }))
                 .Where(x => !x.Event.BookOrder.HasValue || !x.Event.ChapterOrder.HasValue || !x.Event.SceneOrder.HasValue || !sceneKeys.Contains(SceneKey(x.Event.BookOrder.GetValueOrDefault(), x.Event.ChapterOrder.GetValueOrDefault(), x.Event.SceneOrder.GetValueOrDefault())))
                 .Select(x => $"{x.AssetName} / event / {x.Label}")
                 .Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unresolved asset event scene reference: {reference}");
        }

        foreach (var reference in package.SceneDependencies.SelectMany((dependency, index) => new[]
                 {
                     new { Label = $"Dependency {index + 1} fromScene", Reference = dependency.FromScene },
                     new { Label = $"Dependency {index + 1} toScene", Reference = dependency.ToScene }
                 })
                 .Where(x => x.Reference?.BookOrder.HasValue != true
                             || !x.Reference.ChapterOrder.HasValue
                             || !x.Reference.SceneOrder.HasValue
                             || !sceneKeys.Contains(SceneKey(x.Reference.BookOrder.GetValueOrDefault(), x.Reference.ChapterOrder.GetValueOrDefault(), x.Reference.SceneOrder.GetValueOrDefault())))
                 .Select(x => $"{x.Label} -> {SceneReferenceKey(x.Reference)}")
                 .Distinct(StringComparer.OrdinalIgnoreCase))
        {
            warnings.Add($"Unresolved scene dependency reference: {reference}");
        }

        return warnings;
    }

    private static IEnumerable<ImportAppearance> BuildSceneAppearances(ImportSceneDto scene, int? povCharacterId, IReadOnlyDictionary<string, int> characterMap, Dictionary<string, string>? aliases)
    {
        var appearances = new Dictionary<int, ImportAppearance>();

        foreach (var sceneCharacter in scene.SceneCharacters)
        {
            if (string.IsNullOrWhiteSpace(sceneCharacter.CharacterName)
                || !ResolveId(characterMap, sceneCharacter.CharacterName, aliases).HasValue)
            {
                continue;
            }

            var characterId = ResolveId(characterMap, sceneCharacter.CharacterName, aliases)!.Value;
            appearances[characterId] = new ImportAppearance(characterId, sceneCharacter.RoleInScene, sceneCharacter.Importance, sceneCharacter.Notes);
        }

        if (povCharacterId.HasValue && !appearances.ContainsKey(povCharacterId.Value))
        {
            appearances[povCharacterId.Value] = new ImportAppearance(povCharacterId.Value, "POV", null, "Auto-added from povCharacterName.");
        }

        return appearances.Values;
    }

    private static int? ResolveId(IReadOnlyDictionary<string, int> map, string? name, Dictionary<string, string>? aliases)
    {
        var resolvedName = ResolveAlias(name, aliases);
        foreach (var key in CandidateNameKeys(resolvedName))
        {
            if (map.TryGetValue(key, out var id) && id > 0)
            {
                return id;
            }
        }

        return null;
    }

    private static void AddLookupKeys(Dictionary<string, int> map, string? name, int id)
    {
        foreach (var key in CandidateNameKeys(name))
        {
            if (map.TryGetValue(key, out var existingId) && existingId != id)
            {
                map[key] = 0;
            }
            else
            {
                map[key] = id;
            }
        }
    }

    private static HashSet<string> BuildNameKeySet(IEnumerable<string?> names)
    {
        var keys = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var name in names)
        {
            foreach (var key in CandidateNameKeys(name))
            {
                keys.Add(key);
            }
        }

        return keys;
    }

    private static bool ContainsNameKey(HashSet<string> keys, string? name)
        => CandidateNameKeys(name).Any(keys.Contains);

    private static string ResolveAlias(string? name, Dictionary<string, string>? aliases)
    {
        if (string.IsNullOrWhiteSpace(name))
        {
            return string.Empty;
        }

        var clean = CollapseSpaces(name);
        if (aliases is null || aliases.Count == 0)
        {
            return clean;
        }

        foreach (var key in CandidateNameKeys(clean))
        {
            foreach (var alias in aliases)
            {
                if (CandidateNameKeys(alias.Key).Contains(key, StringComparer.OrdinalIgnoreCase))
                {
                    return CollapseSpaces(alias.Value);
                }
            }
        }

        return clean;
    }

    private static IEnumerable<string> CandidateNameKeys(string? name)
    {
        var normalized = NormalizeNameKey(name);
        if (normalized.Length == 0)
        {
            yield break;
        }

        yield return normalized;

        var punctuationInsensitive = new string(normalized.Where(ch => !char.IsPunctuation(ch)).ToArray());
        punctuationInsensitive = CollapseSpaces(punctuationInsensitive);
        if (punctuationInsensitive.Length > 0 && punctuationInsensitive != normalized)
        {
            yield return punctuationInsensitive;
        }
    }

    private static string NormalizeNameKey(string? value)
        => CollapseSpaces(value).ToUpperInvariant();

    private static string CollapseSpaces(string? value)
    {
        var clean = Clean(value);
        if (clean.Length == 0)
        {
            return string.Empty;
        }

        var builder = new StringBuilder(clean.Length);
        var lastWasSpace = false;
        foreach (var ch in clean)
        {
            if (char.IsWhiteSpace(ch))
            {
                if (!lastWasSpace)
                {
                    builder.Append(' ');
                    lastWasSpace = true;
                }
            }
            else
            {
                builder.Append(ch);
                lastWasSpace = false;
            }
        }

        return builder.ToString();
    }

    private static int? LookupLocationTypeId(ImportLookupIds lookupIds, string? type)
    {
        if (!string.IsNullOrWhiteSpace(type) && lookupIds.LocationTypeIds.TryGetValue(type.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.LocationTypeIds.TryGetValue("Other", out var otherId) ? otherId : null;
    }

    private static int? ResolveRoleTypeId(ImportLookupIds lookupIds, string? role)
    {
        var normalized = Clean(role);
        if (normalized.Length == 0)
        {
            normalized = "Present";
        }

        var roleName = normalized.Equals("POV", StringComparison.OrdinalIgnoreCase)
            ? "POV Character"
            : normalized.Contains("protagonist", StringComparison.OrdinalIgnoreCase)
                || normalized.Contains("main", StringComparison.OrdinalIgnoreCase)
                || normalized.Contains("major", StringComparison.OrdinalIgnoreCase)
                || normalized.Contains("lead", StringComparison.OrdinalIgnoreCase)
                || normalized.Equals("Present", StringComparison.OrdinalIgnoreCase)
                ? "Main Participant"
                : normalized.Equals("Supporting", StringComparison.OrdinalIgnoreCase)
                    || normalized.Contains("secondary", StringComparison.OrdinalIgnoreCase)
                    ? "Supporting Participant"
                    : normalized.Equals("Mentioned", StringComparison.OrdinalIgnoreCase)
                        ? "Mentioned Only"
                        : normalized.Equals("Antagonist", StringComparison.OrdinalIgnoreCase)
                            ? "Main Participant"
                            : normalized;

        if (lookupIds.RoleTypeIds.TryGetValue(roleName, out var id))
        {
            return id;
        }

        return lookupIds.RoleTypeIds.TryGetValue("Supporting Participant", out var supportingId) ? supportingId : null;
    }

    private static int? ResolvePresenceTypeId(ImportLookupIds lookupIds, string? role)
    {
        var normalized = Clean(role);
        var presenceName = normalized.Equals("Mentioned", StringComparison.OrdinalIgnoreCase)
            ? "Mentioned Only"
            : normalized.Equals("Background", StringComparison.OrdinalIgnoreCase)
                ? "Present"
                : normalized.Equals("POV", StringComparison.OrdinalIgnoreCase) || normalized.Equals("Antagonist", StringComparison.OrdinalIgnoreCase) || normalized.Equals("Supporting", StringComparison.OrdinalIgnoreCase)
                    ? "Present"
                    : normalized.Length == 0 ? "Present" : normalized;

        if (lookupIds.PresenceTypeIds.TryGetValue(presenceName, out var id))
        {
            return id;
        }

        return lookupIds.PresenceTypeIds.TryGetValue("Present", out var presentId) ? presentId : null;
    }

    private static int ResolvePlotLineTypeId(ImportLookupIds lookupIds, string? type)
    {
        if (!string.IsNullOrWhiteSpace(type) && lookupIds.PlotLineTypeIds.TryGetValue(type.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.PlotLineTypeIds.TryGetValue("Side Plot", out var sidePlotId)
            ? sidePlotId
            : lookupIds.PlotLineTypeIds.Values.First();
    }

    private static int ResolvePlotImportanceId(ImportLookupIds lookupIds, ImportPlotLineDto plotLine)
    {
        if (string.Equals(plotLine.Type?.Trim(), "Main Plot", StringComparison.OrdinalIgnoreCase)
            && lookupIds.PlotImportanceIds.TryGetValue("Primary", out var primaryId))
        {
            return primaryId;
        }

        return lookupIds.PlotImportanceIds.TryGetValue("Secondary", out var secondaryId)
            ? secondaryId
            : lookupIds.PlotImportanceIds.Values.First();
    }

    private static int ResolveThreadTypeId(ImportLookupIds lookupIds, string? type)
    {
        if (!string.IsNullOrWhiteSpace(type) && lookupIds.ThreadTypeIds.TryGetValue(type.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.ThreadTypeIds.TryGetValue("Question", out var questionId)
            ? questionId
            : lookupIds.ThreadTypeIds.Values.First();
    }

    private static int ResolveThreadStatusId(ImportLookupIds lookupIds, string? status)
    {
        if (!string.IsNullOrWhiteSpace(status) && lookupIds.ThreadStatusIds.TryGetValue(status.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.ThreadStatusIds.TryGetValue("Planned", out var plannedId)
            ? plannedId
            : lookupIds.ThreadStatusIds.Values.First();
    }

    private static int ResolveThreadEventTypeId(ImportLookupIds lookupIds, string? eventType)
    {
        if (!string.IsNullOrWhiteSpace(eventType) && lookupIds.ThreadEventTypeIds.TryGetValue(eventType.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.ThreadEventTypeIds.TryGetValue("Mentioned", out var mentionedId)
            ? mentionedId
            : lookupIds.ThreadEventTypeIds.Values.First();
    }

    private static int? NormalizeCharacterImportance(string? role, int? importance)
    {
        var normalized = Clean(role).ToLowerInvariant();
        if (normalized.Contains("protagonist", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("main", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("major", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("lead", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("antagonist", StringComparison.OrdinalIgnoreCase))
        {
            return 10;
        }

        if (normalized.Contains("minor", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("background", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("cameo", StringComparison.OrdinalIgnoreCase))
        {
            return 2;
        }

        if (normalized.Contains("support", StringComparison.OrdinalIgnoreCase)
            || normalized.Contains("secondary", StringComparison.OrdinalIgnoreCase))
        {
            return 6;
        }

        if (importance >= 8)
        {
            return 10;
        }

        if (importance >= 5)
        {
            return 6;
        }

        return importance.HasValue ? 2 : null;
    }

    private static int ResolveSceneDependencyTypeId(ImportLookupIds lookupIds, string? dependencyType)
    {
        var normalized = Clean(dependencyType);
        if (normalized.Length > 0 && lookupIds.SceneDependencyTypeIds.TryGetValue(normalized, out var id))
        {
            return id;
        }

        var mappedType = normalized.ToLowerInvariant() switch
        {
            "causes" or "requires" or "blocks" or "follows" => "Must Occur Before",
            "reveals" or "foreshadows" or "paysoff" or "pays off" => "Reveal Setup",
            "contradicts" => "Knowledge Dependency",
            "mirrors" => "Emotional Setup",
            _ => "Must Occur Before"
        };

        return lookupIds.SceneDependencyTypeIds.TryGetValue(mappedType, out var mappedId)
            ? mappedId
            : lookupIds.SceneDependencyTypeIds.Values.First();
    }

    private static bool ResolveSceneDependencyStrength(string? strength)
    {
        var normalized = Clean(strength);
        if (normalized.Length == 0)
        {
            return false;
        }

        if (int.TryParse(normalized, out var numeric))
        {
            return numeric >= 7;
        }

        return normalized.Equals("Hard", StringComparison.OrdinalIgnoreCase)
            || normalized.Equals("Strong", StringComparison.OrdinalIgnoreCase)
            || normalized.Equals("Required", StringComparison.OrdinalIgnoreCase)
            || normalized.Equals("High", StringComparison.OrdinalIgnoreCase)
            || normalized.Equals("Critical", StringComparison.OrdinalIgnoreCase);
    }

    private static int ResolveAssetKindId(ImportLookupIds lookupIds, string? type)
    {
        if (!string.IsNullOrWhiteSpace(type) && lookupIds.AssetKindIds.TryGetValue(type.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.AssetKindIds.TryGetValue("Object", out var objectId)
            ? objectId
            : lookupIds.AssetKindIds.Values.First();
    }

    private static int? ResolveAssetStateId(ImportLookupIds lookupIds, string? state)
    {
        if (!string.IsNullOrWhiteSpace(state) && lookupIds.AssetStateIds.TryGetValue(state.Trim(), out var id))
        {
            return id;
        }

        return null;
    }

    private static int ResolveAssetEventTypeId(ImportLookupIds lookupIds, string? eventType)
    {
        if (!string.IsNullOrWhiteSpace(eventType) && lookupIds.AssetEventTypeIds.TryGetValue(eventType.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.AssetEventTypeIds.TryGetValue("Mentioned", out var mentionedId)
            ? mentionedId
            : lookupIds.AssetEventTypeIds.Values.First();
    }

    private static int ResolveAssetCustodyEventTypeId(ImportLookupIds lookupIds, string? eventType)
    {
        if (!string.IsNullOrWhiteSpace(eventType) && lookupIds.AssetCustodyEventTypeIds.TryGetValue(eventType.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.AssetCustodyEventTypesFallback();
    }

    private static int ResolveCustodyRoleId(ImportLookupIds lookupIds, string? role)
    {
        if (!string.IsNullOrWhiteSpace(role) && lookupIds.CustodyRoleIds.TryGetValue(role.Trim(), out var id))
        {
            return id;
        }

        return lookupIds.CustodyRoleIds.TryGetValue("Holder", out var holderId)
            ? holderId
            : lookupIds.CustodyRoleIds.Values.First();
    }

    private static int ClampImportance(int? importance)
        => Math.Clamp(importance ?? 5, 1, 10);

    private static bool TryResolveThreadEventScene(IReadOnlyDictionary<string, int> sceneMap, ImportThreadEventDto threadEvent, out int sceneId)
    {
        if (threadEvent.BookOrder.HasValue && threadEvent.ChapterOrder.HasValue && threadEvent.SceneOrder.HasValue)
        {
            return sceneMap.TryGetValue(SceneKey(threadEvent.BookOrder.Value, threadEvent.ChapterOrder.Value, threadEvent.SceneOrder.Value), out sceneId);
        }

        sceneId = 0;
        return false;
    }

    private static bool TryResolveAssetStateScene(IReadOnlyDictionary<string, int> sceneMap, ImportAssetStateDto state, out int sceneId)
    {
        if (state.BookOrder.HasValue && state.ChapterOrder.HasValue && state.SceneOrder.HasValue)
        {
            return sceneMap.TryGetValue(SceneKey(state.BookOrder.Value, state.ChapterOrder.Value, state.SceneOrder.Value), out sceneId);
        }

        sceneId = 0;
        return false;
    }

    private static bool TryResolveAssetEventScene(IReadOnlyDictionary<string, int> sceneMap, ImportAssetEventDto assetEvent, out int sceneId)
    {
        if (assetEvent.BookOrder.HasValue && assetEvent.ChapterOrder.HasValue && assetEvent.SceneOrder.HasValue)
        {
            return sceneMap.TryGetValue(SceneKey(assetEvent.BookOrder.Value, assetEvent.ChapterOrder.Value, assetEvent.SceneOrder.Value), out sceneId);
        }

        sceneId = 0;
        return false;
    }

    private static bool TryResolveSceneReference(IReadOnlyDictionary<string, int> sceneMap, ImportSceneReferenceDto? reference, out int sceneId)
    {
        if (reference?.BookOrder.HasValue == true && reference.ChapterOrder.HasValue && reference.SceneOrder.HasValue)
        {
            return sceneMap.TryGetValue(SceneKey(reference.BookOrder.Value, reference.ChapterOrder.Value, reference.SceneOrder.Value), out sceneId);
        }

        sceneId = 0;
        return false;
    }

    private static string SceneKey(int bookOrder, int chapterOrder, int sceneOrder)
        => $"{bookOrder}/{chapterOrder}/{sceneOrder}";

    private static string SceneReferenceKey(ImportSceneReferenceDto? reference)
        => reference is null
            ? "(missing)"
            : $"{reference.BookOrder?.ToString() ?? "?"}/{reference.ChapterOrder?.ToString() ?? "?"}/{reference.SceneOrder?.ToString() ?? "?"}";

    private static string Clean(string? value) => (value ?? string.Empty).Trim();

    private static string? MetadataLine(string label, string? value)
        => string.IsNullOrWhiteSpace(value) ? null : $"{label}: {value.Trim()}";

    private static string? Truncate(string? value, int maxLength)
    {
        var clean = Clean(value);
        return clean.Length == 0 ? null : clean[..Math.Min(clean.Length, maxLength)];
    }

    private static string? CombineText(params string?[] values)
    {
        var parts = values
            .Select(Clean)
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();

        if (parts.Count == 0)
        {
            return null;
        }

        var builder = new StringBuilder();
        foreach (var part in parts)
        {
            if (builder.Length > 0)
            {
                builder.AppendLine().AppendLine();
            }

            builder.Append(part);
        }

        return builder.ToString();
    }

    private static string DisplayName(string? displayName, string fallback)
        => string.IsNullOrWhiteSpace(displayName) ? fallback : displayName;

    private sealed record ImportLookupIds(
        int RevisionStatusID,
        int TimeModeID,
        int TimeConfidenceID,
        IReadOnlyDictionary<string, int> LocationTypeIds,
        IReadOnlyDictionary<string, int> RoleTypeIds,
        IReadOnlyDictionary<string, int> PresenceTypeIds,
        IReadOnlyDictionary<string, int> PlotLineTypeIds,
        IReadOnlyDictionary<string, int> PlotImportanceIds,
        IReadOnlyDictionary<string, int> ThreadTypeIds,
        IReadOnlyDictionary<string, int> ThreadStatusIds,
        IReadOnlyDictionary<string, int> ThreadEventTypeIds,
        IReadOnlyDictionary<string, int> SceneDependencyTypeIds,
        IReadOnlyDictionary<string, int> AssetKindIds,
        IReadOnlyDictionary<string, int> AssetStateIds,
        IReadOnlyDictionary<string, int> AssetEventTypeIds,
        IReadOnlyDictionary<string, int> AssetCustodyEventTypeIds,
        IReadOnlyDictionary<string, int> CustodyRoleIds)
    {
        public int AssetCustodyEventTypesFallback()
            => AssetCustodyEventTypeIds.TryGetValue("Has", out var hasId)
                ? hasId
                : AssetCustodyEventTypeIds.Values.First();
    }

    private sealed record ImportAppearance(int CharacterId, string? RoleInScene, int? Importance, string? Notes);

    private sealed class ImportLookupRow
    {
        public int LookupID { get; set; }
        public string TypeName { get; set; } = string.Empty;
    }

    private sealed class ImportRequiredLookupRow
    {
        public int? RevisionStatusID { get; set; }
        public int? TimeModeID { get; set; }
        public int? TimeConfidenceID { get; set; }
    }

    private sealed class ImportStatusLookupRow
    {
        public int LookupID { get; set; }
        public string StatusName { get; set; } = string.Empty;
    }

    private sealed class IdentityMaxRow
    {
        public string TableName { get; set; } = string.Empty;
        public int MaxID { get; set; }
    }
}