using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.DTO.ECM;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Storage;
using Microsoft.Extensions.Logging;

namespace GB5Shared.Attachment
{
    public interface IAttachmentUploadService
    {
        Task<AttachmentUploadResponse> UploadAsync(
            AttachmentUploadRequest request,
            Stream                  fileStream,
            string                  originalFileName,
            string                  contentType,
            LoginDTO                login,
            CancellationToken       ct = default);

        Task DeleteAsync(int attachmentId, LoginDTO login, CancellationToken ct = default);

        /// <summary>
        /// Confirms one deferred attachment: re-resolves its template now that ObjectId and/or
        /// PostDataObject field values (e.g. {EMP}, {Year}, {Month}) are known, moves the file
        /// in storage to its final path, and updates TATTACHMENT. Idempotent — a retried call
        /// with the same inputs is a safe no-op.
        /// </summary>
        Task<AttachmentResolvePathResponse> ResolveDeferredPathAsync(
            AttachmentResolvePathRequest request,
            LoginDTO                     login,
            CancellationToken            ct = default);

        /// <summary>
        /// Resolves the MDOCUMENTSETDETAILID for (documentSetCode, tenant), auto-provisioning
        /// MDOCUMENTTYPE/MDOCUMENTSET/MDOCUMENTSETDETAIL rows the first time a given tenant
        /// needs this document set — callers must not hardcode a DocumentSetDetailId, since
        /// these master-data rows are TENANTID-scoped with no cross-tenant fallback. Safe to
        /// call on every upload; the second and later calls just look the row up.
        /// </summary>
        Task<int> EnsureDocumentSetDetailAsync(
            string documentSetCode, string documentSetName,
            string documentTypeCode, string documentTypeName,
            int moduleId, LoginDTO login, CancellationToken ct = default);
    }

    public sealed class AttachmentUploadService : IAttachmentUploadService
    {
        private readonly IQueryExecutor                   _qe;
        private readonly ITemplateResolutionEngine        _engine;
        private readonly IAttachmentStorageResolver        _storageResolver;
        private readonly ILogger<AttachmentUploadService>  _log;

        private const string LoadForDeleteSql = @"
            SELECT CMSID, ATTACHMENTOPTION
            FROM   DBO.TATTACHMENT
            WHERE  ATTACHMENTID = @Id
              AND  STATUS       = 1";

        private const string LoadForResolveSql = @"
            SELECT
                ATTACHMENTID         AS AttachmentId,
                OBJECTID             AS ObjectId,
                OBJECTTYPEID         AS ObjectTypeId,
                BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                DOCUMENTSETDETAILID  AS DocumentSetDetailId,
                SLNO                 AS SlNo,
                VERSION              AS Version,
                CMSID                AS CmsId,
                FOLDERNAME           AS FolderName,
                ATTACHEDFILENAME     AS AttachedFileName,
                DISPLAYFILENAME      AS DisplayFileName,
                ISPATHDEFERRED       AS IsPathDeferred,
                ATTACHMENTOPTION     AS AttachmentOption,
                CREATEDON            AS CreatedOn
            FROM   DBO.TATTACHMENT
            WHERE  ATTACHMENTID = @AttachmentId
              AND  ROWGUID      = @RowGuid
              AND  STATUS       = 1";

        public AttachmentUploadService(
            IQueryExecutor                   qe,
            ITemplateResolutionEngine        engine,
            IAttachmentStorageResolver        storageResolver,
            ILogger<AttachmentUploadService> log)
        {
            _qe              = qe;
            _engine          = engine;
            _storageResolver = storageResolver;
            _log             = log;
        }

        public async Task<AttachmentUploadResponse> UploadAsync(
            AttachmentUploadRequest request,
            Stream                  fileStream,
            string                  originalFileName,
            string                  contentType,
            LoginDTO                login,
            CancellationToken       ct = default)
        {
            // ── 1. Next SLNO + VERSION ────────────────────────────
            var slNoVersion = await _qe.QuerySingleAsync<SlNoVersionResult>(
                login,
                "EXEC DBO.SP_ATTACHMENT_GET_NEXT_SLNO_VERSION " +
                "@ObjectTypeId, @ObjectId, @DocumentSetDetailId, @RowGuid, @ClientId",
                new
                {
                    request.ObjectTypeId,
                    request.ObjectId,
                    request.DocumentSetDetailId,
                    request.RowGuid,
                    login.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);

            var slNo    = slNoVersion?.NextSlNo    ?? 1;
            var version = slNoVersion?.NextVersion ?? 1;

            // ── 2. Build resolution context ───────────────────────
            var ext = Path.GetExtension(originalFileName).ToLowerInvariant();
            var context = new AttachmentResolutionContext
            {
                Login                = login,
                ObjectTypeId         = request.ObjectTypeId,
                ObjectId             = request.ObjectId,
                RowGuid              = request.RowGuid,
                BizTransactionTypeId = request.BizTransactionTypeId,
                DocumentSetDetailId  = request.DocumentSetDetailId,
                FileExtension        = ext,
                OriginalFileName     = Path.GetFileNameWithoutExtension(originalFileName),
                SlNo                 = slNo,
                Version              = version,
                UploadedOn           = DateTime.UtcNow,
                ContextDictionary    = request.ContextDictionary
            };

            // ── 3. Resolve folder + filename ─────────────────────
            var resolved = await _engine.ResolveAsync(context, ct).ConfigureAwait(false);

            // ── 4. Buffer stream, compute MD5, measure size ───────
            // Must buffer because S3/network SaveAsync consumes the stream;
            // we need both hash and size before the insert.
            using var ms = new MemoryStream();
            await fileStream.CopyToAsync(ms, ct).ConfigureAwait(false);
            ms.Position = 0;

            var sizeBytes   = ms.Length;
            var contentHash = ComputeMd5Hex(ms.GetBuffer(), (int)ms.Length);
            ms.Position     = 0;

            // ── 5. Resolve this tenant's storage backend (LoginDTO.AttachmentOption /
            //      MDBLEVELSETTING.ATTACHMENTOPTION) and store the file ───────────────
            var storage = await _storageResolver.ResolveAsync(login, ct).ConfigureAwait(false);
            await storage.SaveAsync(ms, resolved.StorageKey, contentType, ct)
                         .ConfigureAwait(false);

            // ── 6. DocumentTypeId for TATTACHMENT insert ──────────
            var documentTypeId = await _qe.ExecuteScalarAsync<int>(
                login,
                "SELECT DOCUMENTTYPEID FROM DBO.MDOCUMENTSETDETAIL " +
                "WHERE DOCUMENTSETDETAILID = @Id",
                new { Id = request.DocumentSetDetailId },
                cancellationToken: ct).ConfigureAwait(false);

            // ── 7. Insert TATTACHMENT row ─────────────────────────
            var attachmentOption = ProviderToOption(storage.ProviderType);

            // Bound by name (@Param=@Param), not position — SP_ATTACHMENT_INSERT has
            // @ValidFrom/@ValidTo/@ClassificationMarkId/@LinkUrl/@ObjectHeaderTypeId/
            // @ObjectHeaderGuid spliced between the params this call actually supplies
            // (all optional with defaults), and has no @ClientId at all. A positional
            // EXEC here silently shifts every argument after @DocumentTypeId into the
            // wrong slot (e.g. Tags into @ValidFrom, CmsId into @ValidTo → datetime
            // conversion error).
            var attachmentId = await _qe.ExecuteScalarAsync<int>(
                login,
                "EXEC DBO.SP_ATTACHMENT_INSERT " +
                "@ObjectTypeId=@ObjectTypeId, @ObjectId=@ObjectId, " +
                "@BizTransactionTypeId=@BizTransactionTypeId, @SlNo=@SlNo, " +
                "@DocumentSetDetailId=@DocumentSetDetailId, @AttachmentType=@AttachmentType, " +
                "@DocumentTypeId=@DocumentTypeId, @Tags=@Tags, @CmsId=@CmsId, " +
                "@FolderName=@FolderName, @DisplayFileName=@DisplayFileName, " +
                "@AttachedFileName=@AttachedFileName, @Remarks=@Remarks, @Version=@Version, " +
                "@FileSizeBytes=@FileSizeBytes, @MimeType=@MimeType, @ContentHash=@ContentHash, " +
                "@AttachmentOption=@AttachmentOption, @IsPathDeferred=@IsPathDeferred, " +
                "@RowGuid=@RowGuid, @CreatedById=@CreatedById",
                new
                {
                    request.ObjectTypeId,
                    request.ObjectId,
                    request.BizTransactionTypeId,
                    SlNo             = slNo,
                    request.DocumentSetDetailId,
                    request.AttachmentType,
                    DocumentTypeId   = documentTypeId,
                    Tags             = request.Tags,
                    CmsId            = resolved.StorageKey,
                    FolderName       = resolved.FolderName,
                    DisplayFileName  = resolved.DisplayFileName,
                    AttachedFileName = resolved.AttachedFileName,
                    Remarks          = request.Remarks,
                    Version          = version,
                    FileSizeBytes    = sizeBytes,
                    MimeType         = contentType,
                    ContentHash      = contentHash,
                    AttachmentOption = (byte)attachmentOption,
                    IsPathDeferred   = (byte)(resolved.IsDeferred ? 1 : 0),
                    request.RowGuid,
                    CreatedById      = login.UserId
                },
                cancellationToken: ct).ConfigureAwait(false);

            _log.LogInformation(
                "Attachment uploaded. Id={Id} File={File} Size={Size} Deferred={D}",
                attachmentId, resolved.AttachedFileName, sizeBytes, resolved.IsDeferred);

            return new AttachmentUploadResponse
            {
                AttachmentId     = attachmentId,
                DisplayFileName  = resolved.DisplayFileName,
                FolderName       = resolved.FolderName,
                AttachedFileName = resolved.AttachedFileName,
                FileSizeBytes    = sizeBytes,
                IsDeferred       = resolved.IsDeferred,
                Version          = version,
                SlNo             = slNo
            };
        }

        public async Task DeleteAsync(
            int attachmentId, LoginDTO login, CancellationToken ct = default)
        {
            var row = await _qe.QuerySingleAsync<(string? CmsId, byte AttachmentOption)>(
                login, LoadForDeleteSql, new { Id = attachmentId },
                cancellationToken: ct).ConfigureAwait(false);

            if (row.CmsId is not null)
            {
                var storage = await _storageResolver
                    .ResolveAsync(login, row.AttachmentOption, ct).ConfigureAwait(false);
                await storage.DeleteAsync(row.CmsId, ct).ConfigureAwait(false);
            }

            await _qe.ExecuteAsync(
                login,
                "UPDATE DBO.TATTACHMENT SET STATUS=2, MODIFIEDON=GETDATE(), MODIFIEDBYID=@UserId " +
                "WHERE ATTACHMENTID=@Id",
                new { Id = attachmentId, UserId = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);

            _log.LogInformation("Attachment deleted. Id={Id}", attachmentId);
        }

        public async Task<AttachmentResolvePathResponse> ResolveDeferredPathAsync(
            AttachmentResolvePathRequest request,
            LoginDTO                     login,
            CancellationToken            ct = default)
        {
            if (request.ObjectId <= 0)
                throw new ArgumentException("ObjectId must be a real, positive id.", nameof(request));

            // ── 1. Load the row (scoped by AttachmentId + RowGuid — proves the caller
            //      actually owns the attachment it's trying to resolve) ─────────────
            var row = await _qe.QuerySingleAsync<AttachmentResolveRow>(
                login, LoadForResolveSql,
                new { request.AttachmentId, request.RowGuid },
                cancellationToken: ct).ConfigureAwait(false);

            if (row is null)
                throw new InvalidOperationException(
                    $"No attachment found for AttachmentId={request.AttachmentId} with the given RowGuid.");

            // ── 2. Re-resolve the template now that ObjectId + PostDataObject are known ──
            var context = new AttachmentResolutionContext
            {
                Login                = login,
                ObjectTypeId         = row.ObjectTypeId,
                ObjectId             = request.ObjectId,
                RowGuid              = request.RowGuid,
                BizTransactionTypeId = row.BizTransactionTypeId,
                DocumentSetDetailId  = row.DocumentSetDetailId,
                FileExtension        = Path.GetExtension(row.AttachedFileName),
                OriginalFileName     = Path.GetFileNameWithoutExtension(row.AttachedFileName),
                SlNo                 = row.SlNo,
                Version              = row.Version,
                UploadedOn           = row.CreatedOn,
                ContextDictionary    = request.PostDataObject ?? new Dictionary<string, object>()
            };

            var resolved = await _engine.ResolveAsync(context, ct).ConfigureAwait(false);

            // ── 3. Idempotency — already resolved by a prior call ───────────────────
            if (row.IsPathDeferred == 0)
            {
                if (string.Equals(row.CmsId, resolved.StorageKey, StringComparison.Ordinal))
                {
                    _log.LogInformation(
                        "Deferred path resolve: already resolved, no-op retry. AttachmentId={Id}",
                        row.AttachmentId);

                    return new AttachmentResolvePathResponse
                    {
                        AttachmentId     = row.AttachmentId,
                        ObjectId         = row.ObjectId,
                        FolderName       = row.FolderName,
                        AttachedFileName = row.AttachedFileName,
                        DisplayFileName  = row.DisplayFileName,
                        AlreadyResolved  = true
                    };
                }

                throw new InvalidOperationException(
                    $"Attachment {row.AttachmentId} was already resolved to '{row.CmsId}'. " +
                    "Re-resolving it with different data is not allowed.");
            }

            // ── 4. Move the physical file — old (placeholder) key -> new (final) key ──
            var storage = await _storageResolver
                .ResolveAsync(login, row.AttachmentOption, ct).ConfigureAwait(false);

            if (!string.Equals(row.CmsId, resolved.StorageKey, StringComparison.Ordinal))
            {
                bool moved;
                try
                {
                    moved = await storage.MoveAsync(row.CmsId, resolved.StorageKey, ct).ConfigureAwait(false);
                }
                catch (Exception ex)
                {
                    _log.LogError(ex,
                        "Deferred path resolve: storage move failed. AttachmentId={Id} Old={Old} New={New}",
                        row.AttachmentId, row.CmsId, resolved.StorageKey);
                    throw new InvalidOperationException(
                        $"Failed to move attachment {row.AttachmentId} in storage from " +
                        $"'{row.CmsId}' to '{resolved.StorageKey}': {ex.Message}", ex);
                }

                if (!moved)
                {
                    // Source missing. If the destination already exists, a previous attempt
                    // already completed the move (safe to treat this as a retry); otherwise
                    // this is a real data problem and must surface as an error, not a silent success.
                    var newExists = await storage.ExistsAsync(resolved.StorageKey, ct).ConfigureAwait(false);
                    if (!newExists)
                        throw new InvalidOperationException(
                            $"Cannot resolve attachment {row.AttachmentId}: source file " +
                            $"'{row.CmsId}' was not found in storage, and no file exists yet at " +
                            $"the resolved destination '{resolved.StorageKey}' either.");

                    _log.LogWarning(
                        "Deferred path resolve: source missing but destination already present " +
                        "— treating as a completed retry. AttachmentId={Id}", row.AttachmentId);
                }
                else
                {
                    // S3's "move" is CopyObject + DeleteObject, not atomic — verify the source is
                    // actually gone so a delete failure after a successful copy can't leave two
                    // copies behind. If cleanup fails, surface it loudly rather than reporting
                    // success while a duplicate silently exists in storage.
                    var oldStillExists = await storage.ExistsAsync(row.CmsId, ct).ConfigureAwait(false);
                    if (oldStillExists)
                    {
                        try
                        {
                            await storage.DeleteAsync(row.CmsId, ct).ConfigureAwait(false);
                        }
                        catch (Exception ex)
                        {
                            _log.LogError(ex,
                                "Deferred path resolve: cleanup delete of old key failed after move — " +
                                "MANUAL CLEANUP REQUIRED. AttachmentId={Id} OldKey={Old}",
                                row.AttachmentId, row.CmsId);
                            throw new InvalidOperationException(
                                $"Attachment {row.AttachmentId} was copied to '{resolved.StorageKey}' " +
                                $"but the original file at '{row.CmsId}' could not be removed — a " +
                                "duplicate currently exists in storage and needs manual cleanup.", ex);
                        }
                    }
                }
            }

            // ── 5. Update TATTACHMENT — guarded by ISPATHDEFERRED=1 so a concurrent
            //      duplicate call updates 0 rows instead of re-resolving twice ──────
            var updatedRows = await _qe.ExecuteScalarAsync<int>(
                login,
                "EXEC DBO.SP_ATTACHMENT_RESOLVE_UPDATE " +
                "@AttachmentId=@AttachmentId, @RowGuid=@RowGuid, @ObjectId=@ObjectId, " +
                "@NewFolderName=@NewFolderName, @NewAttachedFileName=@NewAttachedFileName, " +
                "@NewCmsId=@NewCmsId, @NewDisplayFileName=@NewDisplayFileName",
                new
                {
                    request.AttachmentId,
                    request.RowGuid,
                    request.ObjectId,
                    NewFolderName       = resolved.FolderName,
                    NewAttachedFileName = resolved.AttachedFileName,
                    NewCmsId            = resolved.StorageKey,
                    NewDisplayFileName  = resolved.DisplayFileName
                },
                cancellationToken: ct).ConfigureAwait(false);

            if (updatedRows == 0)
                throw new InvalidOperationException(
                    $"Attachment {row.AttachmentId} was resolved concurrently by another request — " +
                    "no update applied, to avoid resolving it twice.");

            _log.LogInformation(
                "Deferred path resolved. AttachmentId={Id} ObjectId={ObjId} OldKey={Old} NewKey={New}",
                row.AttachmentId, request.ObjectId, row.CmsId, resolved.StorageKey);

            return new AttachmentResolvePathResponse
            {
                AttachmentId     = row.AttachmentId,
                ObjectId         = request.ObjectId,
                FolderName       = resolved.FolderName,
                AttachedFileName = resolved.AttachedFileName,
                DisplayFileName  = resolved.DisplayFileName,
                AlreadyResolved  = false
            };
        }

        // MDOCUMENTSET/MDOCUMENTSETDETAIL/MDOCUMENTTYPE rows referenced by an attachment upload
        // are TENANTID-scoped with no cross-tenant fallback (SP_ATTACHMENT_LOAD_TEMPLATE filters
        // DS.TENANTID = @ClientId exactly), so a hardcoded DocumentSetDetailId only ever works
        // for the one tenant it was seeded for. This resolves/creates the row for whichever
        // tenant is actually calling, keyed by a stable DOCUMENTSETCODE — first caller for a
        // given tenant provisions it, every later caller (any tenant) just looks it up.
        //
        // IDs are allocated via MAX(id)+1 in the positive range, guarded by sp_getapplock so two
        // concurrent first-callers for the same tenant can't allocate the same id — existing
        // hand-seeded framework rows (e.g. CertificateSigningConfig.SignatoryModuleId) use
        // negative ids, so the positive range is free for dynamically-provisioned rows like this.
        public async Task<int> EnsureDocumentSetDetailAsync(
            string documentSetCode, string documentSetName,
            string documentTypeCode, string documentTypeName,
            int moduleId, LoginDTO login, CancellationToken ct = default)
        {
            const string sql = @"
                DECLARE @Result TABLE (DocumentSetDetailId INT);

                BEGIN TRAN;
                EXEC sp_getapplock @Resource = @LockName, @LockMode = 'Exclusive', @LockOwner = 'Transaction';

                DECLARE @DocumentTypeId INT = (
                    SELECT DOCUMENTTYPEID FROM DBO.MDOCUMENTTYPE
                    WHERE DOCUMENTTYPECODE = @DocumentTypeCode AND TENANTID = @ClientId);

                IF @DocumentTypeId IS NULL
                BEGIN
                    INSERT INTO DBO.MDOCUMENTTYPE
                    (
                        DOCUMENTTYPECODE, DOCUMENTTYPENAME, DOCUMENTCATEGORYID,
                        ISVALIDITY, MODULEID, ISVERSION, ISCHECKINOUT, ISRECORD,
                        SORTORDER, STATUS, VERSION,
                        CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                        SOURCETYPE, TENANTID
                    )
                    VALUES
                    (
                        @DocumentTypeCode, @DocumentTypeName, -1,
                        0, @ModuleId, 1, 0, 1,
                        9999, 1, 0,
                        @UserId, GETDATE(), @UserId, GETDATE(),
                        5, @ClientId
                    );
                    SET @DocumentTypeId = SCOPE_IDENTITY();
                END

                DECLARE @DocumentSetId INT = (
                    SELECT DOCUMENTSETID FROM DBO.MDOCUMENTSET
                    WHERE DOCUMENTSETCODE = @DocumentSetCode AND TENANTID = @ClientId);

                IF @DocumentSetId IS NULL
                BEGIN
                    SET @DocumentSetId = ISNULL((SELECT MAX(DOCUMENTSETID) FROM DBO.MDOCUMENTSET WHERE DOCUMENTSETID > 0), 0) + 1;

                    INSERT INTO DBO.MDOCUMENTSET
                    (
                        DOCUMENTSETID, DOCUMENTSETCODE, DOCUMENTSETNAME,
                        TAGFIELDS, PROPERTYFIELDS, MODULEID,
                        SORTORDER, STATUS, VERSION,
                        CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                        SOURCETYPE, TENANTID
                    )
                    VALUES
                    (
                        @DocumentSetId, @DocumentSetCode, @DocumentSetName,
                        '{TenantCode}/{EntityCode}/{Year}/{Month}/{ObjectId}',
                        '{DocTypeCode}_{SlNo}_{Version}',
                        @ModuleId,
                        9999, 1, 0,
                        @UserId, GETDATE(), @UserId, GETDATE(),
                        5, @ClientId
                    );
                END

                DECLARE @DocumentSetDetailId INT = (
                    SELECT DOCUMENTSETDETAILID FROM DBO.MDOCUMENTSETDETAIL
                    WHERE DOCUMENTSETID = @DocumentSetId AND DOCUMENTTYPEID = @DocumentTypeId);

                IF @DocumentSetDetailId IS NULL
                BEGIN
                    SET @DocumentSetDetailId = ISNULL((SELECT MAX(DOCUMENTSETDETAILID) FROM DBO.MDOCUMENTSETDETAIL WHERE DOCUMENTSETDETAILID > 0), 0) + 1;

                    INSERT INTO DBO.MDOCUMENTSETDETAIL
                    (
                        DOCUMENTSETDETAILID, DOCUMENTSETID, SLNO, DOCUMENTTYPEID,
                        ISMANDATORY, ISMULTIPLE
                    )
                    VALUES
                    (
                        @DocumentSetDetailId, @DocumentSetId, 1, @DocumentTypeId,
                        1, 0
                    );
                END

                INSERT INTO @Result VALUES (@DocumentSetDetailId);
                COMMIT TRAN;

                SELECT DocumentSetDetailId FROM @Result;";

            return await _qe.ExecuteScalarAsync<int>(
                login, sql,
                new
                {
                    LockName         = $"DocSetDetail:{documentSetCode}:{login.ClientId}",
                    DocumentTypeCode = documentTypeCode,
                    DocumentTypeName = documentTypeName,
                    DocumentSetCode  = documentSetCode,
                    DocumentSetName  = documentSetName,
                    ModuleId         = moduleId,
                    ClientId         = login.ClientId,
                    UserId           = login.UserId
                },
                cancellationToken: ct).ConfigureAwait(false);
        }

        private static AttachmentOptionType ProviderToOption(StorageProviderType providerType)
            => providerType switch
            {
                StorageProviderType.S3      => AttachmentOptionType.AmazonS3,
                StorageProviderType.Network => AttachmentOptionType.FileBased,
                _                           => AttachmentOptionType.FileBased
            };

        private static string ComputeMd5Hex(byte[] buffer, int length)
        {
            Span<byte> hash = stackalloc byte[16];
            MD5.HashData(buffer.AsSpan(0, length), hash);
            return Convert.ToHexString(hash).ToLowerInvariant();
        }
    }
}