using Dapper;
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.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Npgsql;
using System;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkBLL.WorkFlow
{
    // BackgroundService is Singleton — scoped services resolved via IServiceScopeFactory.
    // Tenant list loaded dynamically from MSERVERCONFIG — same pattern as OutboxPublishQuartzJob.
    public class WorkflowAutoApproveService : BackgroundService
    {
        private readonly IServiceScopeFactory _scopeFactory;
        private readonly ILogger              _logger;
        private readonly TimeSpan             _interval;

        private const string TenantsSQL = @"
            SELECT
                SC.CLIENTID       AS ClientId,
                SC.DATABASENAME   AS DatabaseName,
                SC.DATABASETYPE   AS DbType,
                SC.CONNECTIONNAME AS ConnectionName
            FROM MSERVERCONFIG SC
            JOIN MSERVER S ON SC.SERVERID = S.SERVERID
            WHERE SC.STATUS = 1
              AND SC.CONNECTIONNAME <> 'ACTIVITI'
              -- Tracker §47/§48 — per-tenant opt-in, default Disabled; this service previously
              -- ran unconditionally against every registered tenant on every FrameworkSL
              -- startup, including tenants missing TWORKFLOWTASK entirely (confirmed live).
              AND SC.SCHEDULERENABLED = 1";

        public WorkflowAutoApproveService(
            IServiceScopeFactory                scopeFactory,
            ILogger<WorkflowAutoApproveService> logger,
            IConfiguration                      configuration)
        {
            _scopeFactory = scopeFactory;
            _logger       = logger;
            _interval     = TimeSpan.FromSeconds(
                configuration.GetValue<int>("WorkflowAutoApprove:IntervalSeconds", 60));
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _logger.LogInformation(
                "WorkflowAutoApproveService started. Interval={Interval}s", _interval.TotalSeconds);

            while (!stoppingToken.IsCancellationRequested)
            {
                try
                {
                    // New scope per cycle — all scoped services (IOptionsSnapshot, IWorkFlowBLL etc.) resolve cleanly.
                    using var scope = _scopeFactory.CreateScope();

                    var appConnection = scope.ServiceProvider.GetRequiredService<IApplicationConnection>();
                    var dbDto         = scope.ServiceProvider.GetRequiredService<IOptionsSnapshot<Gb5SystemDTO>>();
                    var bll           = scope.ServiceProvider.GetRequiredService<IWorkFlowBLL>();

                    string systemConn = await appConnection.Gb5SystemConnectionString();
                    int    dbType     = dbDto.Value.DataBaseType;

                    using IDbConnection connection = dbType == DBTYPE.POSTGRESQL
                        ? (IDbConnection)new NpgsqlConnection(systemConn)
                        : new SqlConnection(systemConn);

                    var tenants = (await connection.QueryAsync<ServerConfigDTO>(TenantsSQL)).ToList();

                    _logger.LogInformation(
                        "WorkflowAutoApprove: {Count} tenant(s) found.", tenants.Count);

                    foreach (var tenant in tenants)
                    {
                        if (stoppingToken.IsCancellationRequested) break;

                        var login = new LoginDTO
                        {
                            UserId                 = -1,
                            ClientId               = tenant.ClientId,
                            ConnectionDatabaseName = tenant.ConnectionName,
                            DatabaseName           = tenant.DatabaseName,
                            DatabaseType           = tenant.DbType,
                            UserName               = "AutoApproveService"
                        };

                        try
                        {
                            await bll.ProcessAutoApprovalsAsync(login, stoppingToken);
                        }
                        catch (Exception ex)
                        {
                            _logger.LogError(ex,
                                "WorkflowAutoApprove: error processing tenant ClientId={ClientId}.",
                                tenant.ClientId);
                        }
                    }
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "WorkflowAutoApproveService: unhandled error in cycle.");
                }

                await Task.Delay(_interval, stoppingToken);
            }

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