using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.Draft;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.ServerConfig;
using Microsoft.Data.SqlClient;
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 expires TDRAFT sessions inactive beyond
    /// DraftSettings.OrphanRetentionHours. Runs once per active tenant
    /// resolved from MSERVERCONFIG.
    /// </summary>
    public sealed class OrphanDraftCleanupJob : BackgroundService
    {
        private readonly IServiceProvider               _services;
        private readonly DraftSettings                  _cfg;
        private readonly ILogger<OrphanDraftCleanupJob> _logger;

        public OrphanDraftCleanupJob(
            IServiceProvider                        services,
            IOptions<DraftSettings>                 options,
            ILogger<OrphanDraftCleanupJob>          logger)
        {
            _services = services ?? throw new ArgumentNullException(nameof(services));
            _cfg      = options.Value;
            _logger   = logger   ?? throw new ArgumentNullException(nameof(logger));
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _logger.LogInformation(
                "OrphanDraftCleanupJob started. Interval={Interval}m Retention={Retention}h Batch={Batch}",
                _cfg.OrphanIntervalMinutes, _cfg.OrphanRetentionHours, _cfg.OrphanCleanupBatchSize);

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

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

            _logger.LogInformation("OrphanDraftCleanupJob 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,
                        "OrphanDraftCleanupJob failed for tenant {DatabaseName}", tenant.DatabaseName);
                }
            }
        }

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

            using var scope = _services.CreateScope();
            var draftRowDal = scope.ServiceProvider.GetRequiredService<IDraftRowDAL>();

            int expired = await draftRowDal
                .CleanupOrphansAsync(_cfg.OrphanRetentionHours, _cfg.OrphanCleanupBatchSize, login, ct)
                .ConfigureAwait(false);

            if (expired > 0)
                _logger.LogInformation(
                    "OrphanDraftCleanup [{Db}]: expired {Count} draft session(s) older than {Hours}h",
                    tenant.DatabaseName, expired, _cfg.OrphanRetentionHours);
        }

        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; this job previously
                  -- ran unconditionally against every registered tenant on every FrameworkSL
                  -- startup, including tenants missing the relevant tables entirely.
                  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               = "OrphanDraftCleanupJob"
        };
    }
}