using Dapper;
using FrameworkBLL.Archive;
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.Logging;
using Microsoft.Extensions.Options;
using Npgsql;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkSL.Controllers.Archive
{
    /// <summary>
    /// Nightly Quartz job that moves TEVENTLOG rows older than the configured retention
    /// boundary to TEVENTLOG_ARCHIVE, per tenant.
    ///
    /// Runs regardless of the schedulerEnabled flag — data retention is a critical path.
    ///
    /// Registration in Program.cs (add alongside OutboxPublishQuartzJob):
    /// <code>
    ///   services.AddQuartz(q =>
    ///   {
    ///       var key = new JobKey("EventLogArchiveJob");
    ///       q.AddJob&lt;EventLogArchiveQuartzJob&gt;(o => o.WithIdentity(key));
    ///       q.AddTrigger(o => o
    ///           .ForJob(key)
    ///           .WithCronSchedule("0 0 2 * * ?"));  // 2 AM UTC daily
    ///   });
    ///   services.AddScoped&lt;EventLogArchiveQuartzJob&gt;();
    /// </code>
    /// </summary>
    [DisallowConcurrentExecution]
    public sealed class EventLogArchiveQuartzJob : IJob
    {
        private readonly IEventLogArchiveBLL            _archiveBll;
        private readonly IApplicationConnection         _appConnection;
        private readonly IOptionsSnapshot<Gb5SystemDTO> _systemDto;
        private readonly ILogger<EventLogArchiveQuartzJob> _logger;

        public EventLogArchiveQuartzJob(
            IEventLogArchiveBLL archiveBll,
            IApplicationConnection appConnection,
            IOptionsSnapshot<Gb5SystemDTO> systemDto,
            ILogger<EventLogArchiveQuartzJob> logger)
        {
            _archiveBll    = archiveBll;
            _appConnection = appConnection;
            _systemDto     = systemDto;
            _logger        = logger;
        }

        public async Task Execute(IJobExecutionContext context)
        {
            _logger.LogInformation("EventLogArchiveJob: starting");

            try
            {
                var tenants = await LoadTenantsAsync().ConfigureAwait(false);

                foreach (var tenant in tenants)
                {
                    try
                    {
                        var login = new LoginDTO
                        {
                            ClientId               = tenant.ClientId,
                            DatabaseName           = tenant.DatabaseName,
                            DatabaseType           = tenant.DbType,
                            ConnectionDatabaseName = tenant.ConnectionName
                        };

                        await _archiveBll.ArchiveForTenantAsync(login, context.CancellationToken)
                            .ConfigureAwait(false);
                    }
                    catch (Exception ex)
                    {
                        // Log and continue — one tenant failure must not block others
                        _logger.LogError(ex,
                            "EventLogArchiveJob: failed for TenantId={TenantId}", tenant.ClientId);
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "EventLogArchiveJob: unexpected failure");
                throw new JobExecutionException(ex, refireImmediately: false);
            }

            _logger.LogInformation("EventLogArchiveJob: done");
        }

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

        private async Task<IEnumerable<ServerConfigDTO>> LoadTenantsAsync()
        {
            var systemConn = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
            int dbType     = _systemDto.Value.DataBaseType;

            const string sql = @"
                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'";

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

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