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

namespace PlotLine.Data;

public interface IWordCompanionRepository
{
    Task<IReadOnlyList<WordCompanionProjectDto>> ListProjectsAsync(int userId);
    Task<IReadOnlyList<WordCompanionBookDto>> ListBooksAsync(int projectId, int userId);
    Task<WordCompanionCreateBookResponse?> CreateBookAsync(int projectId, int userId, WordCompanionCreateBookRequest request);
    Task<WordCompanionBookStructureResponse?> GetBookStructureAsync(int bookId, int userId);
    Task<IReadOnlyList<WordCompanionChapterDto>> ListChaptersAsync(int bookId, int userId);
    Task<WordCompanionResolveSceneResponse> ResolveSceneAsync(int bookId, int userId, string chapterTitle, string sceneTitle);
    Task<WordCompanionSceneCompanionResponse?> GetSceneCompanionAsync(int sceneId, int userId);
    Task<WordCompanionCreateChapterResponse?> CreateChapterAsync(int bookId, int userId, WordCompanionCreateChapterRequest request);
    Task<WordCompanionCreateSceneResponse?> CreateSceneAsync(int bookId, int chapterId, int userId, WordCompanionCreateSceneRequest request);
    Task<WordCompanionSyncSceneResponse?> SyncSceneAsync(int bookId, int userId, WordCompanionSyncSceneRequest request);
    Task<WordCompanionSyncManuscriptResponse?> SyncManuscriptAsync(int bookId, int userId, WordCompanionSyncManuscriptRequest request);
    Task<WordCompanionUpdateSceneLinksResponse?> UpdateSceneLinksAsync(int sceneId, int userId, WordCompanionUpdateSceneLinksRequest request);
    Task<WordCompanionUpdateWordCountResponse?> UpdateWordCountAsync(int sceneId, int userId, int actualWords, DateTime? lastWorkedOn);
    Task<WordCompanionUpdateWordCountResponse?> UpdateRuntimeSceneWordCountAsync(int userId, WordCompanionRuntimeSceneWordCountRequest request);
    Task<WordCompanionRuntimeCurrentSceneResponse?> ValidateRuntimeCurrentSceneAsync(int userId, WordCompanionRuntimeCurrentSceneRequest request);
    Task<IReadOnlyList<WordCompanionRuntimeCharacterAliasDto>> ListRuntimeCharacterAliasesAsync(int userId, int projectId, Guid documentGuid);
    Task<WordCompanionRuntimeCharacterSuggestionsResponse?> CreateRuntimeCharacterSuggestionsAsync(int userId, WordCompanionRuntimeCharacterSuggestionsRequest request);
    Task<IReadOnlyList<WordCompanionRuntimeAssetAliasDto>> ListRuntimeAssetAliasesAsync(int userId, int projectId, Guid documentGuid);
    Task<WordCompanionRuntimeAssetSuggestionsResponse?> CreateRuntimeAssetSuggestionsAsync(int userId, WordCompanionRuntimeAssetSuggestionsRequest request);
    Task<IReadOnlyList<WordCompanionRuntimeLocationAliasDto>> ListRuntimeLocationAliasesAsync(int userId, int projectId, Guid documentGuid);
    Task<WordCompanionRuntimeLocationSuggestionsResponse?> CreateRuntimeLocationSuggestionsAsync(int userId, WordCompanionRuntimeLocationSuggestionsRequest request);
    Task<WordCompanionManuscriptAnalyseResponse?> AnalyseManuscriptAsync(int userId, WordCompanionManuscriptAnalyseRequest request);
    Task<WordCompanionManuscriptImportResponse?> ImportManuscriptAsync(int userId, WordCompanionManuscriptImportRequest request);
    Task<bool> HasProjectAccessAsync(int projectId, int userId);
    Task<WordCompanionCreateDiscoveredCharactersResponse?> CreateDiscoveredCharactersAsync(int userId, WordCompanionCreateDiscoveredCharactersRequest request);
}

public sealed class WordCompanionRepository(ISqlConnectionFactory connectionFactory) : IWordCompanionRepository
{
    public async Task<IReadOnlyList<WordCompanionProjectDto>> ListProjectsAsync(int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<WordCompanionProjectDto>(
            "dbo.WordCompanion_Project_List",
            new { UserID = userId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<IReadOnlyList<WordCompanionBookDto>> ListBooksAsync(int projectId, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<WordCompanionBookDto>(
            "dbo.WordCompanion_Book_ListByProject",
            new { ProjectID = projectId, UserID = userId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<WordCompanionCreateBookResponse?> CreateBookAsync(int projectId, int userId, WordCompanionCreateBookRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionCreateBookResponse>(
            "dbo.WordCompanion_Book_Create",
            new { ProjectID = projectId, UserID = userId, request.Title },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionBookStructureResponse?> GetBookStructureAsync(int bookId, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        using var result = await connection.QueryMultipleAsync(
            "dbo.WordCompanion_Book_Structure",
            new { BookID = bookId, UserID = userId },
            commandType: CommandType.StoredProcedure);

        var header = await result.ReadSingleOrDefaultAsync<WordCompanionBookStructureResponse>();
        if (header is null)
        {
            return null;
        }

        var chapters = (await result.ReadAsync<WordCompanionChapterStructureDto>()).ToList();
        var scenes = (await result.ReadAsync<WordCompanionSceneStructureRow>()).ToList();
        var scenesByChapter = scenes.GroupBy(x => x.ChapterId).ToDictionary(x => x.Key, x => x.Select(scene => new WordCompanionSceneStructureDto
        {
            SceneId = scene.SceneId,
            Title = scene.Title,
            SortOrder = scene.SortOrder,
            RevisionStatus = scene.RevisionStatus,
            EstimatedWords = scene.EstimatedWords,
            ActualWords = scene.ActualWords
        }).ToList());

        foreach (var chapter in chapters)
        {
            chapter.Scenes = scenesByChapter.TryGetValue(chapter.ChapterId, out var chapterScenes) ? chapterScenes : [];
        }

        return new WordCompanionBookStructureResponse { BookId = header.BookId, Chapters = chapters };
    }

    public async Task<IReadOnlyList<WordCompanionChapterDto>> ListChaptersAsync(int bookId, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<WordCompanionChapterDto>(
            "dbo.WordCompanion_Chapter_List",
            new { BookID = bookId, UserID = userId },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<WordCompanionResolveSceneResponse> ResolveSceneAsync(int bookId, int userId, string chapterTitle, string sceneTitle)
    {
        using var connection = connectionFactory.CreateConnection();
        var response = await connection.QuerySingleAsync<WordCompanionResolveSceneResponse>(
            "dbo.WordCompanion_Scene_Resolve",
            new { BookID = bookId, UserID = userId, ChapterTitle = chapterTitle, SceneTitle = sceneTitle },
            commandType: CommandType.StoredProcedure);
        return response;
    }

    public async Task<WordCompanionSceneCompanionResponse?> GetSceneCompanionAsync(int sceneId, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        using var result = await connection.QueryMultipleAsync(
            "dbo.WordCompanion_Scene_Companion",
            new { SceneID = sceneId, UserID = userId },
            commandType: CommandType.StoredProcedure);

        var scene = await result.ReadSingleOrDefaultAsync<WordCompanionSceneCompanionResponse>();
        if (scene is null)
        {
            return null;
        }

        return new WordCompanionSceneCompanionResponse
        {
            SceneId = scene.SceneId,
            ChapterId = scene.ChapterId,
            ChapterTitle = scene.ChapterTitle,
            SceneTitle = scene.SceneTitle,
            ActualWords = scene.ActualWords,
            EstimatedWords = scene.EstimatedWords,
            WritingBrief = scene.WritingBrief,
            Blocked = scene.Blocked,
            BlockedReason = scene.BlockedReason,
            PrimaryLocationId = scene.PrimaryLocationId,
            Characters = (await result.ReadAsync<WordCompanionLinkItemDto>()).ToList(),
            Assets = (await result.ReadAsync<WordCompanionLinkItemDto>()).ToList(),
            Locations = (await result.ReadAsync<WordCompanionLinkItemDto>()).ToList()
        };
    }

    public async Task<WordCompanionCreateChapterResponse?> CreateChapterAsync(int bookId, int userId, WordCompanionCreateChapterRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionCreateChapterResponse>(
            "dbo.WordCompanion_Chapter_Create",
            new { BookID = bookId, UserID = userId, request.Title, request.SortOrder },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionCreateSceneResponse?> CreateSceneAsync(int bookId, int chapterId, int userId, WordCompanionCreateSceneRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionCreateSceneResponse>(
            "dbo.WordCompanion_Scene_Create",
            new { BookID = bookId, ChapterID = chapterId, UserID = userId, request.SceneTitle },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionSyncSceneResponse?> SyncSceneAsync(int bookId, int userId, WordCompanionSyncSceneRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionSyncSceneResponse>(
            "dbo.WordCompanion_Scene_Sync",
            new
            {
                BookID = bookId,
                UserID = userId,
                request.ChapterTitle,
                request.ChapterSortOrder,
                request.SceneTitle,
                request.SceneSortOrder,
                request.ActualWords,
                request.DocumentId,
                request.ContentHash
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionSyncManuscriptResponse?> SyncManuscriptAsync(int bookId, int userId, WordCompanionSyncManuscriptRequest request)
    {
        var chapters = request.Chapters.Select(chapter => new
        {
            chapter.Title,
            chapter.SortOrder,
            chapter.WordCount,
            Scenes = chapter.Scenes.Select(scene => new
            {
                scene.Title,
                scene.SortOrder,
                scene.WordCount,
                scene.ContentHash
            })
        });

        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionSyncManuscriptResponse>(
            "dbo.WordCompanion_Manuscript_Sync",
            new
            {
                BookID = bookId,
                UserID = userId,
                request.DocumentId,
                StructureJson = System.Text.Json.JsonSerializer.Serialize(chapters)
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionUpdateSceneLinksResponse?> UpdateSceneLinksAsync(int sceneId, int userId, WordCompanionUpdateSceneLinksRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionUpdateSceneLinksResponse>(
            "dbo.WordCompanion_SceneLinks_Update",
            new
            {
                SceneID = sceneId,
                UserID = userId,
                CharacterIds = ToCsv(request.CharacterIds),
                AssetIds = ToCsv(request.AssetIds),
                LocationIds = ToCsv(request.LocationIds),
                request.PrimaryLocationId
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionUpdateWordCountResponse?> UpdateWordCountAsync(int sceneId, int userId, int actualWords, DateTime? lastWorkedOn)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionUpdateWordCountResponse>(
            "dbo.WordCompanion_Scene_UpdateWordCount",
            new { SceneID = sceneId, UserID = userId, ActualWords = actualWords, LastWorkedOn = lastWorkedOn },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionUpdateWordCountResponse?> UpdateRuntimeSceneWordCountAsync(int userId, WordCompanionRuntimeSceneWordCountRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionUpdateWordCountResponse>(
            "dbo.WordCompanion_Runtime_SceneWordCount_Update",
            new
            {
                UserID = userId,
                request.DocumentGuid,
                request.BookId,
                request.ChapterId,
                request.SceneId,
                request.WordCount
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionRuntimeCurrentSceneResponse?> ValidateRuntimeCurrentSceneAsync(int userId, WordCompanionRuntimeCurrentSceneRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionRuntimeCurrentSceneResponse>(
            "dbo.WordCompanion_Runtime_CurrentScene_Validate",
            new
            {
                UserID = userId,
                request.DocumentGuid,
                request.ProjectId,
                request.BookId,
                request.ChapterId,
                request.SceneId
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<WordCompanionRuntimeCharacterAliasDto>> ListRuntimeCharacterAliasesAsync(int userId, int projectId, Guid documentGuid)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<WordCompanionRuntimeCharacterAliasDto>(
            "dbo.WordCompanion_Runtime_CharacterAlias_List",
            new { UserID = userId, ProjectID = projectId, DocumentGuid = documentGuid },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<WordCompanionRuntimeCharacterSuggestionsResponse?> CreateRuntimeCharacterSuggestionsAsync(int userId, WordCompanionRuntimeCharacterSuggestionsRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionRuntimeCharacterSuggestionsResponse>(
            "dbo.WordCompanion_Runtime_CharacterSuggestions_Create",
            new
            {
                UserID = userId,
                request.DocumentGuid,
                request.SceneId,
                CharacterIds = ToCsv(request.CharacterIds)
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<WordCompanionRuntimeAssetAliasDto>> ListRuntimeAssetAliasesAsync(int userId, int projectId, Guid documentGuid)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<WordCompanionRuntimeAssetAliasDto>(
            "dbo.WordCompanion_Runtime_AssetAlias_List",
            new { UserID = userId, ProjectID = projectId, DocumentGuid = documentGuid },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<WordCompanionRuntimeAssetSuggestionsResponse?> CreateRuntimeAssetSuggestionsAsync(int userId, WordCompanionRuntimeAssetSuggestionsRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionRuntimeAssetSuggestionsResponse>(
            "dbo.WordCompanion_Runtime_AssetSuggestions_Create",
            new
            {
                UserID = userId,
                request.DocumentGuid,
                request.SceneId,
                AssetIds = ToCsv(request.AssetIds)
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<IReadOnlyList<WordCompanionRuntimeLocationAliasDto>> ListRuntimeLocationAliasesAsync(int userId, int projectId, Guid documentGuid)
    {
        using var connection = connectionFactory.CreateConnection();
        var rows = await connection.QueryAsync<WordCompanionRuntimeLocationAliasDto>(
            "dbo.WordCompanion_Runtime_LocationAlias_List",
            new { UserID = userId, ProjectID = projectId, DocumentGuid = documentGuid },
            commandType: CommandType.StoredProcedure);
        return rows.ToList();
    }

    public async Task<WordCompanionRuntimeLocationSuggestionsResponse?> CreateRuntimeLocationSuggestionsAsync(int userId, WordCompanionRuntimeLocationSuggestionsRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionRuntimeLocationSuggestionsResponse>(
            "dbo.WordCompanion_Runtime_LocationSuggestions_Create",
            new
            {
                UserID = userId,
                request.DocumentGuid,
                request.SceneId,
                LocationIds = ToCsv(request.LocationIds)
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionManuscriptAnalyseResponse?> AnalyseManuscriptAsync(int userId, WordCompanionManuscriptAnalyseRequest request)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<WordCompanionManuscriptAnalyseResponse>(
            "dbo.WordCompanion_Manuscript_Analyse",
            new
            {
                UserID = userId,
                request.ProjectId,
                request.BookId,
                DocumentGuid = request.DocumentGuid,
                request.ChapterCount,
                request.SceneCount,
                request.WordCount
            },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionManuscriptImportResponse?> ImportManuscriptAsync(int userId, WordCompanionManuscriptImportRequest request)
    {
        var chapters = request.Chapters.Select(chapter => new
        {
            chapter.Title,
            chapter.SortOrder,
            chapter.WordCount,
            Scenes = chapter.Scenes.Select(scene => new
            {
                scene.Title,
                scene.SortOrder,
                scene.WordCount
            })
        });

        using var connection = connectionFactory.CreateConnection();
        using var result = await connection.QueryMultipleAsync(
            "dbo.WordCompanion_Manuscript_Import",
            new
            {
                UserID = userId,
                request.ProjectId,
                request.BookId,
                DocumentGuid = request.DocumentGuid.GetValueOrDefault(),
                request.BindingVersion,
                request.ReplacePlanned,
                StructureJson = System.Text.Json.JsonSerializer.Serialize(chapters)
            },
            commandType: CommandType.StoredProcedure);

        var response = await result.ReadSingleOrDefaultAsync<WordCompanionManuscriptImportResponse>();
        if (response is null)
        {
            return null;
        }

        var document = await result.ReadSingleOrDefaultAsync<WordCompanionManuscriptDocumentDto>();
        return new WordCompanionManuscriptImportResponse
        {
            Imported = response.Imported,
            RequiresReplaceConfirmation = response.RequiresReplaceConfirmation,
            Message = response.Message,
            ProjectId = response.ProjectId,
            BookId = response.BookId,
            ChaptersCreated = response.ChaptersCreated,
            ScenesCreated = response.ScenesCreated,
            ArchivedChapters = response.ArchivedChapters,
            ArchivedScenes = response.ArchivedScenes,
            ManuscriptDocument = document
        };
    }

    public async Task<bool> HasProjectAccessAsync(int projectId, int userId)
    {
        using var connection = connectionFactory.CreateConnection();
        return await connection.QuerySingleAsync<bool>(
            "dbo.WordCompanion_Project_HasAccess",
            new { ProjectID = projectId, UserID = userId },
            commandType: CommandType.StoredProcedure);
    }

    public async Task<WordCompanionCreateDiscoveredCharactersResponse?> CreateDiscoveredCharactersAsync(int userId, WordCompanionCreateDiscoveredCharactersRequest request)
    {
        var names = request.CandidateNames
            .Select(name => new { Name = name })
            .ToList();

        using var connection = connectionFactory.CreateConnection();
        using var result = await connection.QueryMultipleAsync(
            "dbo.WordCompanion_Character_CreateDiscovered",
            new
            {
                UserID = userId,
                request.ProjectId,
                CandidatesJson = System.Text.Json.JsonSerializer.Serialize(names)
            },
            commandType: CommandType.StoredProcedure);

        var header = await result.ReadSingleOrDefaultAsync<WordCompanionCreateDiscoveredCharactersResponse>();
        if (header is null)
        {
            return null;
        }

        var characters = (await result.ReadAsync<WordCompanionCreatedCharacterDto>()).ToList();
        return new WordCompanionCreateDiscoveredCharactersResponse
        {
            ProjectId = header.ProjectId,
            CreatedCount = header.CreatedCount,
            SkippedCount = header.SkippedCount,
            Message = header.Message,
            Characters = characters
        };
    }

    private static string ToCsv(IEnumerable<int> ids) => string.Join(',', ids.Where(id => id > 0).Distinct());

    private sealed class WordCompanionSceneStructureRow
    {
        public int ChapterId { get; set; }
        public int SceneId { get; set; }
        public string Title { get; set; } = string.Empty;
        public int SortOrder { get; set; }
        public string RevisionStatus { get; set; } = string.Empty;
        public int? EstimatedWords { get; set; }
        public int? ActualWords { get; set; }
    }
}