using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml.Linq;
using DMSDAL.DTO.Migration;
using DMSDAL.Query.Migration;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;

namespace DMSDAL.CustomCode.Migration
{
    public class AlfrescoMigrationDAL : IAlfrescoMigrationDAL
    {
        private readonly IQueryExecutor                  _qe;
        private readonly IHttpClientFactory              _httpFactory;
        private readonly ILogger<AlfrescoMigrationDAL>  _log;

        private const string CmisQueryPath  = "/cmis/atom/query";
        private const string ContentPathTpl = "/alfresco/api/-default-/public/alfresco/versions/1/nodes/{0}/content";

        public AlfrescoMigrationDAL(
            IQueryExecutor qe,
            IHttpClientFactory httpFactory,
            ILogger<AlfrescoMigrationDAL> log)
        {
            _qe          = qe;
            _httpFactory = httpFactory;
            _log         = log;
        }

        public async Task<EntityTableInfoDTO?> GetEntityTableInfoAsync(
            int objectTypeId, LoginDTO login, CancellationToken ct = default)
            => await _qe.QuerySingleAsync<EntityTableInfoDTO>(
                login, AlfrescoMigrationQB.GET_ENTITY_TABLE_INFO,
                new { ObjectTypeId = objectTypeId },
                cancellationToken: ct).ConfigureAwait(false);

        public async Task<IEnumerable<int>> GetEntityIdsBatchAsync(
            EntityTableInfoDTO entityInfo, int pageSize, int offset,
            LoginDTO login, CancellationToken ct = default)
        {
            // Table and column names come from MENTITY metadata, not user input — safe to interpolate.
            var sql = string.Format(
                AlfrescoMigrationQB.GET_ENTITY_IDS_PAGED_TEMPLATE,
                entityInfo.PKColumn, entityInfo.SourceTable);

            var result = await _qe.QueryAsync<int>(
                login, sql,
                new { login.ClientId, Offset = offset, PageSize = pageSize },
                cancellationToken: ct).ConfigureAwait(false);

            return result ?? Enumerable.Empty<int>();
        }

        public async Task<bool> IsAlreadyMigratedAsync(
            int objectTypeId, int objectId, LoginDTO login, CancellationToken ct = default)
        {
            var count = await _qe.ExecuteScalarAsync<int>(
                login, AlfrescoMigrationQB.CHECK_ALREADY_MIGRATED,
                new { ObjectTypeId = objectTypeId, ObjectId = objectId, login.DatabaseName },
                cancellationToken: ct).ConfigureAwait(false);
            return count > 0;
        }

        public async Task<AlfrescoSettings?> GetAlfrescoSettingsAsync(
            LoginDTO login, CancellationToken ct = default)
        {
            async Task<string?> GetKey(string key) =>
                await _qe.ExecuteScalarAsync<string?>(
                    login, AlfrescoMigrationQB.GET_ALFRESCO_SETTINGS,
                    new { Key = key, login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

            var baseUrl  = await GetKey("AlfrescoCMS");
            var userId   = await GetKey("AlfrescoCMSUserId");
            var password = await GetKey("AlfrescoCMSPassword");

            if (string.IsNullOrWhiteSpace(baseUrl)) return null;

            return new AlfrescoSettings(
                baseUrl.TrimEnd('/'),
                userId   ?? string.Empty,
                password ?? string.Empty);
        }

        public async Task<IEnumerable<AlfrescoAttachmentInfoDTO>> DiscoverAlfrescoAttachmentsAsync(
            AlfrescoSettings settings, int objectTypeId, int objectId,
            int clientId, CancellationToken ct = default)
        {
            // CMIS SQL query to find all documents for this entity
            var cmisSql =
                $"SELECT cmis:objectId, cmis:name, cmis:contentStreamMimeType, cmis:contentStreamLength " +
                $"FROM custom:doc " +
                $"WHERE custom:ObjectId='{objectId}' " +
                $"AND custom:ObjectTypeId='{objectTypeId}' " +
                $"AND custom:ClientId='{clientId}'";

            var body     = $"cmisquery={Uri.EscapeDataString(cmisSql)}";
            var url      = settings.BaseUrl + CmisQueryPath;
            var http     = _httpFactory.CreateClient("alfresco");
            var request  = new HttpRequestMessage(HttpMethod.Post, url)
            {
                Content = new StringContent(body, Encoding.UTF8, "application/x-www-form-urlencoded")
            };
            AddBasicAuth(request, settings);

            var response = await http.SendAsync(request, ct).ConfigureAwait(false);
            if (!response.IsSuccessStatusCode)
            {
                _log.LogWarning("Alfresco CMIS query returned {Status} for OT={OT} OId={OId}",
                    response.StatusCode, objectTypeId, objectId);
                return Enumerable.Empty<AlfrescoAttachmentInfoDTO>();
            }

            var xml = await response.Content.ReadAsStringAsync(ct).ConfigureAwait(false);
            return ParseCmisAtomFeed(xml);
        }

        public async Task<Stream> DownloadFromAlfrescoAsync(
            AlfrescoSettings settings, string nodeId, CancellationToken ct = default)
        {
            var url     = settings.BaseUrl + string.Format(ContentPathTpl, nodeId);
            var http    = _httpFactory.CreateClient("alfresco");
            var request = new HttpRequestMessage(HttpMethod.Get, url);
            AddBasicAuth(request, settings);

            var response = await http.SendAsync(request, HttpCompletionOption.ResponseHeadersRead, ct)
                                     .ConfigureAwait(false);
            response.EnsureSuccessStatusCode();

            return await response.Content.ReadAsStreamAsync(ct).ConfigureAwait(false);
        }

        public async Task<long> InsertMigrationLogAsync(
            MigrationLogDTO dto, LoginDTO login, CancellationToken ct = default)
            => await _qe.ExecuteScalarAsync<long>(
                login, AlfrescoMigrationQB.INSERT_MIGRATION_LOG,
                new
                {
                    dto.ObjectTypeId,
                    dto.ObjectId,
                    dto.AlfrescoNodeId,
                    dto.DisplayFileName,
                    login.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);

        public async Task UpdateMigrationLogStatusAsync(
            long migrationLogId, byte status, int? attachmentId, string? error,
            LoginDTO login, CancellationToken ct = default)
            => await _qe.ExecuteAsync(
                login, AlfrescoMigrationQB.UPDATE_MIGRATION_LOG,
                new { MigrationLogId = migrationLogId, Status = status, AttachmentId = attachmentId, ErrorMessage = error },
                cancellationToken: ct).ConfigureAwait(false);

        public async Task<int> InsertTAttachmentAsync(
            int objectTypeId, int objectId, int documentSetDetailId,
            string folderName, string attachedFileName, string displayFileName,
            string cmsId, string mimeType, long fileSizeBytes, string? contentHash,
            byte attachmentOption, LoginDTO login, CancellationToken ct = default)
        {
            // Use the same SP that the template upload uses; slno/version=1 for migrated files.
            return await _qe.ExecuteScalarAsync<int>(
                login,
                "EXEC DBO.SP_ATTACHMENT_INSERT " +
                "@ObjectTypeId, @ObjectId, @BizTransactionTypeId, @SlNo, " +
                "@DocumentSetDetailId, @AttachmentType, @DocumentTypeId, " +
                "@Tags, @CmsId, @FolderName, @DisplayFileName, @AttachedFileName, " +
                "@Remarks, @Version, @FileSizeBytes, @ContentHash, " +
                "@AttachmentOption, @IsPathDeferred, @RowGuid, @ClientId, @CreatedById",
                new
                {
                    ObjectTypeId         = objectTypeId,
                    ObjectId             = objectId,
                    BizTransactionTypeId = -1,
                    SlNo                 = (short)1,
                    DocumentSetDetailId  = documentSetDetailId,
                    AttachmentType       = (byte)0,
                    DocumentTypeId       = -1,
                    Tags                 = string.Empty,
                    CmsId                = cmsId,
                    FolderName           = folderName,
                    DisplayFileName      = displayFileName,
                    AttachedFileName     = attachedFileName,
                    Remarks              = "Migrated from Alfresco",
                    Version              = (short)1,
                    FileSizeBytes        = fileSizeBytes,
                    ContentHash          = contentHash,
                    AttachmentOption     = attachmentOption,
                    IsPathDeferred       = (byte)0,
                    RowGuid              = Guid.NewGuid(),
                    login.ClientId,
                    CreatedById          = login.UserId
                },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<MigrationStatusDTO?> GetMigrationStatusAsync(
            int objectTypeId, LoginDTO login, CancellationToken ct = default)
            => await _qe.QuerySingleAsync<MigrationStatusDTO>(
                login, AlfrescoMigrationQB.GET_MIGRATION_STATUS,
                new { ObjectTypeId = objectTypeId, login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

        // ── Helpers ──────────────────────────────────────────────────────

        private static void AddBasicAuth(HttpRequestMessage request, AlfrescoSettings settings)
        {
            var encoded = Convert.ToBase64String(
                Encoding.UTF8.GetBytes($"{settings.UserId}:{settings.Password}"));
            request.Headers.Authorization = new AuthenticationHeaderValue("Basic", encoded);
        }

        private static IEnumerable<AlfrescoAttachmentInfoDTO> ParseCmisAtomFeed(string atomXml)
        {
            // Alfresco returns Atom XML; each <entry> holds one document node.
            XNamespace atom = "http://www.w3.org/2005/Atom";
            XNamespace cmis = "http://docs.oasis-open.org/ns/cmis/core/200908/";

            var results = new List<AlfrescoAttachmentInfoDTO>();
            try
            {
                var doc = XDocument.Parse(atomXml);
                foreach (var entry in doc.Descendants(atom + "entry"))
                {
                    var objectId  = entry.Descendants(cmis + "value")
                                        .FirstOrDefault(v =>
                                            v.Parent?.Attribute(cmis + "name")?.Value == "cmis:objectId")
                                        ?.Value;
                    var name      = entry.Descendants(cmis + "value")
                                        .FirstOrDefault(v =>
                                            v.Parent?.Attribute(cmis + "name")?.Value == "cmis:name")
                                        ?.Value;
                    var mimeType  = entry.Descendants(cmis + "value")
                                        .FirstOrDefault(v =>
                                            v.Parent?.Attribute(cmis + "name")?.Value == "cmis:contentStreamMimeType")
                                        ?.Value;
                    var sizeStr   = entry.Descendants(cmis + "value")
                                        .FirstOrDefault(v =>
                                            v.Parent?.Attribute(cmis + "name")?.Value == "cmis:contentStreamLength")
                                        ?.Value;

                    if (string.IsNullOrWhiteSpace(objectId)) continue;

                    // Strip Alfresco's "workspace://SpacesStore/" prefix if present
                    var nodeId = objectId.Contains(';')
                        ? objectId.Split(';')[0].Split('/').Last()
                        : objectId.Split('/').Last();

                    results.Add(new AlfrescoAttachmentInfoDTO
                    {
                        NodeId          = nodeId,
                        DisplayFileName = name ?? nodeId,
                        MimeType        = mimeType ?? "application/octet-stream",
                        FileSize        = long.TryParse(sizeStr, out var sz) ? sz : 0,
                        CreatedOn       = DateTime.UtcNow
                    });
                }
            }
            catch (Exception)
            {
                // Malformed XML — return empty; caller will log and skip
            }

            return results;
        }
    }
}