using System.Globalization;
using JobTrackerApi.Data;
using Microsoft.EntityFrameworkCore;

namespace JobTrackerApi.Services;

public sealed record DocumentsPageDto(IReadOnlyList<DocumentIndexItemDto> Items, int Page, int PageSize, int Total, int TotalPages);

public sealed record DocumentIndexItemDto(
    string Id,
    string ResourceId,
    string Type,
    string Name,
    string State,
    string? Visibility,
    int? JobApplicationId,
    string? JobTitle,
    string? CompanyName,
    bool? JobIsDeleted,
    string? RecordedAt,
    string DateKind,
    long? Version,
    int? HistoryCount,
    int? AttachmentCount,
    int? CvVariantId,
    string? CvVariantName,
    int? CvVariantVersion,
    string? FileType,
    long? FileSize,
    string? Purpose,
    bool? UseForAi,
    string? Provider,
    string SourceHref,
    string? DownloadHref,
    string? PublicHref,
    bool? HasCurrentContent);

public sealed class DocumentsIndexService(JobTrackerContext db)
{
    public async Task<DocumentsPageDto> GetAsync(string owner, int page, int pageSize, string? q,
        string type, string state, string visibility, int? jobApplicationId, string sort, CancellationToken ct)
    {
        var pattern = (q ?? "").Trim().ToLowerInvariant().Replace("!", "!!").Replace("%", "!%").Replace("_", "!_");
        object[] parameters = [owner, $"%{pattern}%", type, state, visibility, jobApplicationId ?? 0, pageSize, (page - 1) * pageSize];
        var sqlite = db.Database.IsSqlite();
        var count = await db.Database.SqlQueryRaw<int>(BuildSql(sqlite, true, sort), parameters).ToListAsync(ct);
        var rows = await db.Database.SqlQueryRaw<DocumentRow>(BuildSql(sqlite, false, sort), parameters).ToListAsync(ct);
        var total = count.Single();
        return new DocumentsPageDto(rows.Select(ToDto).ToArray(), page, pageSize, total, (int)Math.Ceiling(total / (double)pageSize));
    }

    private static string BuildSql(bool sqlite, bool count, string sort)
    {
        const string history = "FROM `CoverLetterVersions` h WHERE h.OwnerUserId = {0} AND h.JobApplicationId = j.Id";
        var letterDate = $"(SELECT CAST(h.CreatedAtUtc AS CHAR) {history} ORDER BY h.Version DESC, h.Id DESC LIMIT 1)";
        var letterCount = $"(SELECT COUNT(*) {history})";
        var letterVersion = $"(SELECT MAX(h.Version) {history})";
        var letterContent = HasContent("j.CoverLetterText");
        var answers = HasAnswers(sqlite);
        var recruiter = HasContent("j.RecruiterMessageDraft");
        var tailored = HasContent("j.TailoredCvText");
        var sources = $$"""
            SELECT 'cv' AS Type, CAST(v.Id AS CHAR) AS ResourceId, v.Name AS Name, 'current' AS State,
                CASE WHEN v.IsPublic = 1 THEN 'public' ELSE 'private' END AS Visibility,
                j.Id AS JobApplicationId, j.JobTitle AS JobTitle, c.Name AS CompanyName, j.IsDeleted AS JobIsDeleted,
                CAST(v.UpdatedAtUtc AS CHAR) AS RawRecordedAt, 'updated-utc' AS DateKind,
                v.Version AS Version, NULL AS HistoryCount, NULL AS AttachmentCount,
                NULL AS CvVariantId, NULL AS CvVariantName, NULL AS CvVariantVersion,
                NULL AS FileType, NULL AS FileSize, NULL AS Purpose, NULL AS UseForAi, NULL AS Provider,
                v.PublicSlug AS PublicSlug, NULL AS HasCurrentContent
            FROM `CvVariants` v
            LEFT JOIN `JobApplications` j ON j.Id = v.JobApplicationId AND j.OwnerUserId = {0}
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            WHERE v.OwnerUserId = {0}
            UNION ALL
            SELECT 'cover-letter', CAST(j.Id AS CHAR), j.JobTitle, 'draft', NULL,
                j.Id, j.JobTitle, c.Name, j.IsDeleted, {{letterDate}},
                CASE WHEN {{letterDate}} IS NULL THEN 'unknown' ELSE 'history-recorded-utc' END,
                {{letterVersion}}, {{letterCount}}, NULL, NULL, NULL, NULL,
                NULL, NULL, NULL, NULL, NULL, NULL, CASE WHEN {{letterContent}} THEN 1 ELSE 0 END
            FROM `JobApplications` j
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            WHERE j.OwnerUserId = {0} AND ({{letterContent}} OR {{letterCount}} > 0)
            UNION ALL
            SELECT 'application-material', CAST(j.Id AS CHAR), j.JobTitle, 'draft', NULL,
                j.Id, j.JobTitle, c.Name, j.IsDeleted, NULL, 'unknown',
                NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
            FROM `JobApplications` j
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            WHERE j.OwnerUserId = {0} AND ({{answers}} OR {{recruiter}})
            UNION ALL
            SELECT 'tailored-cv', CAST(j.Id AS CHAR), j.JobTitle, 'draft', NULL,
                j.Id, j.JobTitle, c.Name, j.IsDeleted,
                CAST(COALESCE(d.LastEditedAtUtc, d.LastGeneratedAtUtc, j.TailoredCvUpdatedAt) AS CHAR),
                CASE WHEN COALESCE(d.LastEditedAtUtc, d.LastGeneratedAtUtc, j.TailoredCvUpdatedAt) IS NULL THEN 'unknown' ELSE 'updated-utc' END,
                NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
            FROM `JobApplications` j
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            LEFT JOIN `TailoredCvDrafts` d ON d.JobApplicationId = j.Id AND d.OwnerUserId = {0}
            WHERE j.OwnerUserId = {0} AND (d.Id IS NOT NULL OR {{tailored}})
            UNION ALL
            SELECT 'attachment', CAST(a.Id AS CHAR), a.FileName, 'current', NULL,
                j.Id, j.JobTitle, c.Name, j.IsDeleted, CAST(a.UploadDate AS CHAR), 'uploaded-local',
                NULL, NULL, NULL, NULL, NULL, NULL, a.FileType, a.FileSize, a.Purpose, a.UseForAi, NULL, NULL, NULL
            FROM `Attachments` a
            JOIN `JobApplications` j ON j.Id = a.JobApplicationId AND j.OwnerUserId = {0}
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            UNION ALL
            SELECT 'submitted-package', CAST(p.Id AS CHAR), COALESCE(p.CvVariantName, j.JobTitle), 'frozen', NULL,
                j.Id, j.JobTitle, c.Name, j.IsDeleted, CAST(p.CreatedAtUtc AS CHAR), 'captured-utc',
                p.Version, NULL, (SELECT COUNT(*) FROM `SubmittedPackageAttachments` a WHERE a.SubmittedApplicationPackageId = p.Id),
                p.CvVariantId, p.CvVariantName, p.CvVariantVersion, NULL, NULL, NULL, NULL, NULL, NULL, NULL
            FROM `SubmittedApplicationPackages` p
            JOIN `JobApplications` j ON j.Id = p.JobApplicationId AND j.OwnerUserId = {0}
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            WHERE p.OwnerUserId = {0}
            UNION ALL
            SELECT 'email-draft', CAST(d.Id AS CHAR), d.Subject, 'draft', NULL,
                j.Id, j.JobTitle, c.Name, j.IsDeleted, CAST(d.UpdatedAtUtc AS CHAR), 'updated-utc',
                d.Revision, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, d.Provider, NULL, NULL
            FROM `EmailDrafts` d
            JOIN `JobApplications` j ON j.Id = d.JobApplicationId AND j.OwnerUserId = {0}
            LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
            WHERE d.OwnerUserId = {0}
            """;
        var filtered = $$"""
            FROM ({{sources}}) documents
            WHERE ({2} = 'all' OR Type = {2})
                AND ({3} = 'all' OR State = {3})
                AND ({4} = 'all' OR (Type = 'cv' AND Visibility = {4}))
                AND ({5} = 0 OR JobApplicationId = {5})
                AND ({{Fold("Name")}} LIKE {1} ESCAPE '!'
                    OR {{Fold("JobTitle")}} LIKE {1} ESCAPE '!'
                    OR {{Fold("CompanyName")}} LIKE {1} ESCAPE '!'
                    OR {{Fold("CvVariantName")}} LIKE {1} ESCAPE '!')
            """;
        if (count) return $"SELECT COUNT(*) AS Value {filtered}";
        var order = sort switch
        {
            "date-desc" => "(DateSortTicks IS NULL), DateSortTicks DESC",
            "date-asc" => "(DateSortTicks IS NULL), DateSortTicks ASC",
            "name-asc" => $"{Fold("Name")} ASC",
            "name-desc" => $"{Fold("Name")} DESC",
            _ => throw new ArgumentOutOfRangeException(nameof(sort))
        };
        return $$"""
            SELECT documents.*, {{DateTicks(sqlite)}} AS DateSortTicks {{filtered}}
            ORDER BY {{order}}, Type ASC, ResourceId ASC
            LIMIT {6} OFFSET {7}
            """;
    }

    private static string Fold(string value) => $"REPLACE(REPLACE(REPLACE(LOWER(COALESCE({value}, '')), 'Æ', 'æ'), 'Ø', 'ø'), 'Å', 'å')";

    private static string HasContent(string value)
    {
        // SQL TRIM does not implement .NET's Unicode whitespace semantics.
        var expression = $"COALESCE({value}, '')";
        foreach (var character in "\t\n\v\f\r \u0085\u00a0\u1680\u2000\u2001\u2002\u2003\u2004\u2005\u2006\u2007\u2008\u2009\u200a\u2028\u2029\u202f\u205f\u3000")
        {
            // EF formats SQL line endings; CHAR preserves the exact control whitespace being removed.
            var needle = character is >= '\t' and <= '\r' ? $"CHAR({(int)character})" : $"'{character}'";
            expression = $"REPLACE({expression}, {needle}, '')";
        }
        return $"LENGTH({expression}) > 0";
    }

    private static string HasAnswers(bool sqlite)
    {
        string Position(string needle, string value) => sqlite ? $"INSTR({value}, '{needle}')"
            : $"LOCATE(CONVERT('{needle}' USING utf8mb4) COLLATE utf8mb4_bin, {value})";
        const string startMarker = "<<<APPLICATION_ANSWER_DRAFT>>>";
        const string endMarker = "<<<END_APPLICATION_ANSWER_DRAFT>>>";
        const string legacyPrefix = "application answer draft:";
        // Keep character offsets aligned while matching modern markers with ordinal case sensitivity.
        var notes = sqlite ? "COALESCE(j.Notes, '')" : "CONVERT(COALESCE(j.Notes, '') USING utf8mb4) COLLATE utf8mb4_bin";
        var start = Position(startMarker, notes);
        var end = Position(endMarker, notes);
        var legacy = Position(legacyPrefix, $"LOWER({notes})" + (sqlite ? "" : " COLLATE utf8mb4_bin"));
        return $"CASE WHEN {start} > 0 AND {end} > {start} THEN {HasContent($"SUBSTR({notes}, {start} + {startMarker.Length}, {end} - {start} - {startMarker.Length})")} "
            + $"WHEN {legacy} > 0 THEN {HasContent($"SUBSTR({notes}, {legacy} + {legacyPrefix.Length})")} ELSE 0 END";
    }

    private static string DateTicks(bool sqlite)
    {
        string ticks;
        if (sqlite)
        {
            // Split whole seconds from seven fractional digits; SQLite's fractional date arithmetic rounds.
            const string offsetLength = "CASE WHEN SUBSTR(RawRecordedAt, -6, 1) IN ('+', '-') THEN 6 WHEN SUBSTR(RawRecordedAt, -1) = 'Z' THEN 1 ELSE 0 END";
            const string offset = "CASE WHEN SUBSTR(RawRecordedAt, -6, 1) IN ('+', '-') THEN SUBSTR(RawRecordedAt, -6) ELSE '+00:00' END";
            var fraction = $"CASE WHEN SUBSTR(RawRecordedAt, 20, 1) = '.' THEN SUBSTR(RawRecordedAt, 21, LENGTH(RawRecordedAt) - 20 - ({offsetLength})) ELSE '0' END";
            ticks = $"CAST(STRFTIME('%s', SUBSTR(RawRecordedAt, 1, 19) || ({offset})) AS INTEGER) * 10000000 "
                + $"+ CAST(SUBSTR(({fraction}) || '0000000', 1, 7) AS INTEGER)";
        }
        else ticks = "TIMESTAMPDIFF(MICROSECOND, '1970-01-01 00:00:00', RawRecordedAt) * 10";
        return $"CASE WHEN DateKind IN ('unknown', 'uploaded-local') OR RawRecordedAt IS NULL THEN NULL ELSE {ticks} END";
    }

    private static DocumentIndexItemDto ToDto(DocumentRow row)
    {
        var resourceId = row.Type == "email-draft" ? Guid.Parse(row.ResourceId).ToString("D") : row.ResourceId;
        var source = row.Type switch
        {
            "cv" => $"/career/builder/{resourceId}",
            "attachment" => $"/jobs/{row.JobApplicationId}?section=documents",
            "cover-letter" => $"/jobs/{row.JobApplicationId}?section=application&sub=letter",
            "application-material" => $"/jobs/{row.JobApplicationId}?section=application&sub=answers",
            "tailored-cv" => $"/jobs/{row.JobApplicationId}?section=application&sub=cv",
            "submitted-package" => $"/jobs/{row.JobApplicationId}?section=application&sub=submission",
            "email-draft" => "/correspondence",
            _ => throw new InvalidOperationException("Unsupported document source.")
        };
        var recordedAt = row.DateSortTicks.HasValue
            ? DateTimeOffset.UnixEpoch.AddTicks(row.DateSortTicks.Value).UtcDateTime.ToString("O", CultureInfo.InvariantCulture)
            : row.DateKind == "uploaded-local" && row.RawRecordedAt is not null
                ? DateTime.Parse(row.RawRecordedAt, CultureInfo.InvariantCulture).ToString("yyyy-MM-dd'T'HH:mm:ss.fffffff", CultureInfo.InvariantCulture)
                : null;
        return new DocumentIndexItemDto($"{row.Type}:{resourceId}", resourceId, row.Type, row.Name, row.State, row.Visibility,
            row.JobApplicationId, row.JobTitle, row.CompanyName, row.JobIsDeleted, recordedAt, row.DateKind, row.Version,
            row.HistoryCount, row.AttachmentCount, row.CvVariantId, row.CvVariantName, row.CvVariantVersion,
            row.FileType, row.FileSize, row.Purpose, row.UseForAi, row.Provider, source,
            row.Type == "attachment" ? $"/api/attachments/download/{resourceId}" : null,
            row.Type == "cv" && row.Visibility == "public" && !string.IsNullOrWhiteSpace(row.PublicSlug) ? $"/cv/{Uri.EscapeDataString(row.PublicSlug)}" : null,
            row.HasCurrentContent);
    }

    private sealed class DocumentRow
    {
        public string Type { get; set; } = "";
        public string ResourceId { get; set; } = "";
        public string Name { get; set; } = "";
        public string State { get; set; } = "";
        public string? Visibility { get; set; }
        public int? JobApplicationId { get; set; }
        public string? JobTitle { get; set; }
        public string? CompanyName { get; set; }
        public bool? JobIsDeleted { get; set; }
        public string? RawRecordedAt { get; set; }
        public string DateKind { get; set; } = "";
        public long? Version { get; set; }
        public int? HistoryCount { get; set; }
        public int? AttachmentCount { get; set; }
        public int? CvVariantId { get; set; }
        public string? CvVariantName { get; set; }
        public int? CvVariantVersion { get; set; }
        public string? FileType { get; set; }
        public long? FileSize { get; set; }
        public string? Purpose { get; set; }
        public bool? UseForAi { get; set; }
        public string? Provider { get; set; }
        public string? PublicSlug { get; set; }
        public bool? HasCurrentContent { get; set; }
        public long? DateSortTicks { get; set; }
    }
}