using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.Connection;
using GB5Shared.DTO.ECM;
using GB5Shared.FileUpload;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.ServerConfig;
using GB5Shared.QueryExecutor;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Npgsql;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkBLL.OrphanCleanup
{
    /// <summary>
    /// Periodic background job that deletes TATTACHMENT rows with OBJECTID=0
    /// older than OrphanCleanupConfig.OrphanAgeHours and removes their physical files.
    /// Runs once per active tenant resolved from MSERVERCONFIG.
    /// </summary>
    public sealed class OrphanAttachmentCleanupJob : BackgroundService
    {
        private readonly IServiceProvider                    _services;
        private readonly IConfiguration                      _config;
        private readonly OrphanCleanupConfig                 _cfg;
        private readonly ILogger<OrphanAttachmentCleanupJob> _logger;

        public OrphanAttachmentCleanupJob(
            IServiceProvider                      services,
            IConfiguration                        config,
            IOptions<OrphanCleanupConfig>         options,
            ILogger<OrphanAttachmentCleanupJob>   logger)
        {
            _services = services ?? throw new ArgumentNullException(nameof(services));
            _config   = config   ?? throw new ArgumentNullException(nameof(config));
            _cfg      = options.Value;
            _logger   = logger   ?? throw new ArgumentNullException(nameof(logger));
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _logger.LogInformation(
                "OrphanAttachmentCleanupJob started. Interval={Interval}m, OrphanAge={Age}h, Batch={Batch}",
                _cfg.IntervalMinutes, _cfg.OrphanAgeHours, _cfg.BatchSize);

            while (!stoppingToken.IsCancellationRequested)
            {
                try
                {
                    await RunCycleAsync(stoppingToken).ConfigureAwait(false);
                }
                catch (OperationCanceledException)
                {
                    break;
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "OrphanAttachmentCleanupJob cycle failed at {Time:HH:mm:ss}", DateTime.UtcNow);
                }

                try
                {
                    await Task.Delay(TimeSpan.FromMinutes(_cfg.IntervalMinutes), stoppingToken)
                        .ConfigureAwait(false);
                }
                catch (TaskCanceledException)
                {
                    break;
                }
            }

            _logger.LogInformation("OrphanAttachmentCleanupJob stopped.");
        }

        private async Task RunCycleAsync(CancellationToken ct)
        {
            var tenants = await GetTenantsAsync().ConfigureAwait(false);

            foreach (var tenant in tenants)
            {
                try
                {
                    await RunTenantCycleAsync(tenant, ct).ConfigureAwait(false);
                }
                catch (OperationCanceledException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex,
                        "OrphanAttachmentCleanupJob failed for tenant {DatabaseName}", tenant.DatabaseName);
                }
            }
        }

        private async Task RunTenantCycleAsync(ServerConfigDTO tenant, CancellationToken ct)
        {
            var login = BuildLogin(tenant);

            using var scope   = _services.CreateScope();
            var attachmentDAL = scope.ServiceProvider.GetRequiredService<IAttachmentDAL>();
            var queryExecutor = scope.ServiceProvider.GetRequiredService<IQueryExecutor>();

            var olderThan = DateTime.UtcNow.AddHours(-_cfg.OrphanAgeHours);

            IEnumerable<TAttachmentDTO> orphans = await attachmentDAL
                .GetOrphanedAttachmentsAsync(olderThan, _cfg.BatchSize, login, ct)
                .ConfigureAwait(false);

            var orphanList = orphans.ToList();
            if (orphanList.Count == 0)
                return;

            string basePath = GetBasePath();

            foreach (var orphan in orphanList)
                DeletePhysicalFile(orphan, basePath);

            var ids = orphanList.Select(o => o.AttachmentId).ToList();

            var tx = await queryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
            try
            {
                await attachmentDAL.DeleteAttachmentsAsync(ids, login, tx, ct).ConfigureAwait(false);
                await queryExecutor.CommitAsync(tx).ConfigureAwait(false);
            }
            catch
            {
                await queryExecutor.RollbackAsync(tx).ConfigureAwait(false);
                throw;
            }

            _logger.LogInformation(
                "OrphanCleanup [{Db}]: deleted {Count} attachment(s) older than {Hours}h",
                tenant.DatabaseName, orphanList.Count, _cfg.OrphanAgeHours);
        }

        private async Task<List<ServerConfigDTO>> GetTenantsAsync()
        {
            using var scope       = _services.CreateScope();
            var appConnection     = scope.ServiceProvider.GetRequiredService<IApplicationConnection>();
            var databaseDTO       = scope.ServiceProvider.GetRequiredService<IOptionsMonitor<Gb5SystemDTO>>();
            var systemConn        = await appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
            int dbType            = databaseDTO.CurrentValue.DataBaseType;

            const string sql = @"
                SELECT
                    SERVERCONFIG1.CLIENTID       AS ClientId,
                    SERVERCONFIG1.DATABASENAME   AS DatabaseName,
                    SERVERCONFIG1.DATABASETYPE   AS DbType,
                    SERVERCONFIG1.CONNECTIONNAME AS ConnectionName
                FROM MSERVERCONFIG SERVERCONFIG1
                JOIN MSERVER SERVER1
                    ON SERVERCONFIG1.SERVERID = SERVER1.SERVERID
                WHERE SERVERCONFIG1.STATUS = 1
                  AND SERVERCONFIG1.CONNECTIONNAME <> 'ACTIVITI'
                  -- Tracker §47/§48 — per-tenant opt-in, default Disabled; same fix as this
                  -- job's own sibling, OrphanDraftCleanupJob.
                  AND SERVERCONFIG1.SCHEDULERENABLED = 1";

            using IDbConnection connection = dbType switch
            {
                DBTYPE.SQL        => new SqlConnection(systemConn),
                DBTYPE.POSTGRESQL => new NpgsqlConnection(systemConn),
                _                 => throw new NotSupportedException($"Unsupported DB type: {dbType}")
            };

            return (await connection.QueryAsync<ServerConfigDTO>(sql).ConfigureAwait(false)).ToList();
        }

        private static LoginDTO BuildLogin(ServerConfigDTO tenant) => new()
        {
            UserId                 = -1,
            ClientId               = tenant.ClientId,
            ConnectionDatabaseName = tenant.ConnectionName,
            DatabaseName           = tenant.DatabaseName,
            DatabaseType           = tenant.DbType,
            UserName               = "OrphanCleanupJob"
        };

        private string GetBasePath()
        {
            string osKey = System.Runtime.InteropServices.RuntimeInformation.IsOSPlatform(
                System.Runtime.InteropServices.OSPlatform.Windows) ? "Windows" : "Linux";

            string raw = _config[$"FileStorage:PhysicalFilePath:{osKey}"]
                ?? _config["FileStorage:PhysicalFilePath"]
                ?? throw new InvalidOperationException(
                    $"FileStorage:PhysicalFilePath:{osKey} (or FileStorage:PhysicalFilePath) is not configured.");

            return raw.Replace('\\', Path.DirectorySeparatorChar)
                      .Replace('/', Path.DirectorySeparatorChar)
                      .TrimEnd(Path.DirectorySeparatorChar);
        }

        private static void DeletePhysicalFile(TAttachmentDTO attachment, string basePath)
        {
            if (string.IsNullOrWhiteSpace(attachment.CMSId) ||
                string.IsNullOrWhiteSpace(attachment.DisplayFileName))
                return;

            string filePath = Path.Combine(
                basePath, attachment.FolderName, attachment.CMSId, attachment.DisplayFileName);

            if (System.IO.File.Exists(filePath))
                System.IO.File.Delete(filePath);

            string cmsDir = Path.GetDirectoryName(filePath)!;
            if (Directory.Exists(cmsDir) && Directory.GetFileSystemEntries(cmsDir).Length == 0)
                Directory.Delete(cmsDir);
        }
    }
}