using Dapper;
using FrameworkDAL.CustomCode.SchedulerTaskGenerator;
using FrameworkDAL.DTO.SchedulerTaskGenerator;
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.Logging;
using Microsoft.Extensions.Options;
using Npgsql;
using Quartz;
using Quartz.Impl.Matchers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkBLL.SchedulerTaskGenerator
{
    public sealed class QuartzSyncService : IQuartzSyncService
    {
        // Must match the QRTZ_LOCKS seed rows (SCHED_NAME='GB5Scheduler') and
        // Program.cs's AddQuartz(q => q.SchedulerId = "GB5Scheduler").
        public const string SchedulerName = "GB5Scheduler";

        // Registered Scoped (not Singleton) — it directly consumes scoped services
        // (IApplicationConnection, IOptionsSnapshot<>, ISchedulerTaskDAL). A caller with
        // singleton lifetime (QuartzBootstrapHostedService) must resolve this through its
        // own IServiceScopeFactory.CreateScope(), not via direct constructor injection.
        private readonly ISchedulerFactory _schedulerFactory;
        private readonly ISchedulerTaskDAL _dal;
        private readonly IApplicationConnection _appConnection;
        private readonly IOptionsSnapshot<Gb5SystemDTO> _databaseDTO;
        private readonly ILogger<QuartzSyncService> _logger;
        // 0 = all tenants (single-server / dev default); set to a specific MSERVER.SERVERID
        // in appsettings.json ("Scheduler:ServerId") on each FrameworkSL host to restrict
        // Quartz job sync to only the tenants whose DBs live on that physical server.
        private readonly int _schedulerServerId;

        public QuartzSyncService(
            ISchedulerFactory schedulerFactory,
            ISchedulerTaskDAL dal,
            IApplicationConnection appConnection,
            IOptionsSnapshot<Gb5SystemDTO> databaseDTO,
            ILogger<QuartzSyncService> logger,
            IConfiguration config)
        {
            _schedulerFactory    = schedulerFactory ?? throw new ArgumentNullException(nameof(schedulerFactory));
            _dal                 = dal ?? throw new ArgumentNullException(nameof(dal));
            _appConnection       = appConnection ?? throw new ArgumentNullException(nameof(appConnection));
            _databaseDTO         = databaseDTO ?? throw new ArgumentNullException(nameof(databaseDTO));
            _logger              = logger ?? throw new ArgumentNullException(nameof(logger));
            _schedulerServerId   = (config ?? throw new ArgumentNullException(nameof(config)))
                                       .GetValue<int>("Scheduler:ServerId", 0);
        }

        // ─────────────────────────────────────────────────────────────────
        // JobKey convention — tenant-scoped group so per-tenant bulk
        // operations (pause-all, list-all) stay isolated even if JobId
        // is not guaranteed globally unique across tenants.
        // ─────────────────────────────────────────────────────────────────
        private static JobKey Key(int jobId, int tenantId) =>
            new($"job-{jobId}", $"tenant-{tenantId}");

        // ─────────────────────────────────────────────────────────────────
        // SyncJobAsync
        // ─────────────────────────────────────────────────────────────────

        public async Task SyncJobAsync(int jobId, LoginDTO login, CancellationToken ct = default)
        {
            var job = await _dal.LoadJobForSyncAsync(jobId, login, ct).ConfigureAwait(false);
            if (job is null)
            {
                _logger.LogWarning("QuartzSyncService: JobId={JobId} not found for TenantId={TenantId} — nothing to sync",
                    jobId, login.ClientId);
                return;
            }

            var scheduler = await _schedulerFactory.GetScheduler(ct).ConfigureAwait(false);
            var key = Key(job.JobId, job.TenantId);

            // CORRECTED (tracker §46) — Status=1 is Active for MJOBDEFINE/TSCHEDULER, matching
            // GB5's standard entity-status enum and confirmed live against real fleet data; this
            // check previously required Status==0, meaning it treated ~95% of real, legitimate
            // jobs as inactive and tore down (or never built) their triggers. See
            // JobDefineOperationsBLL.cs's own corrected comment for the full live-verification.
            if (job.Status != 1 || job.SchedulerStatus != 1)
            {
                await RemoveJobAsync(jobId, login, ct).ConfigureAwait(false);
                _logger.LogInformation(
                    "QuartzSyncService: JobId={JobId} inactive (Job.Status={JobStatus} Scheduler.Status={SchedulerStatus}) — trigger removed",
                    job.JobId, job.Status, job.SchedulerStatus);
                return;
            }

            // Clean slate — delete any existing trigger/job before rebuilding, so an edited
            // cron/interval takes effect immediately rather than being layered on top of stale state.
            if (await scheduler.CheckExists(key, ct).ConfigureAwait(false))
                await scheduler.DeleteJob(key, ct).ConfigureAwait(false);

            var trigger = BuildTrigger(job, key);
            if (trigger is null)
            {
                _logger.LogWarning(
                    "QuartzSyncService: JobId={JobId} has no recognized schedule configuration (no CronExpression, OccursAtTime, EveryNoOfHours/Minutes, or ONE_TIME ScheduledDate) — skipping registration",
                    job.JobId);
                return;
            }

            // All values must be strings — the persistent ADO job store is configured with
            // UseProperties=true (Program.cs), which requires string-only JobDataMap entries
            // and throws IOException otherwise. SchedulerJobExecutorQuartzJob reads these back
            // via JobDataMap.GetInt/GetString, which handle the string-stored values correctly.
            var jobDetail = JobBuilder.Create<SchedulerJobExecutorQuartzJob>()
                .WithIdentity(key)
                .UsingJobData("JobId", job.JobId.ToString())
                .UsingJobData("TenantId", job.TenantId.ToString())
                .UsingJobData("DatabaseName", login.DatabaseName ?? string.Empty)
                .UsingJobData("ConnectionName", login.ConnectionDatabaseName ?? login.DatabaseName ?? string.Empty)
                .StoreDurably(false)
                .Build();

            await scheduler.ScheduleJob(jobDetail, trigger, ct).ConfigureAwait(false);

            _logger.LogInformation(
                "QuartzSyncService: synced JobId={JobId} TenantId={TenantId} TriggerType={TriggerType} NextFireUtc={NextFireUtc}",
                job.JobId, job.TenantId, job.SchedulerType, trigger.GetNextFireTimeUtc());
        }

        // ─────────────────────────────────────────────────────────────────
        // RemoveJobAsync
        // ─────────────────────────────────────────────────────────────────

        public async Task RemoveJobAsync(int jobId, LoginDTO login, CancellationToken ct = default)
        {
            var scheduler = await _schedulerFactory.GetScheduler(ct).ConfigureAwait(false);
            var key = Key(jobId, login.ClientId);

            if (await scheduler.CheckExists(key, ct).ConfigureAwait(false))
                await scheduler.DeleteJob(key, ct).ConfigureAwait(false);
        }

        // ─────────────────────────────────────────────────────────────────
        // IsJobRegisteredAsync
        // ─────────────────────────────────────────────────────────────────

        public async Task<bool> IsJobRegisteredAsync(int jobId, int tenantId, CancellationToken ct = default)
        {
            var scheduler = await _schedulerFactory.GetScheduler(ct).ConfigureAwait(false);
            return await scheduler.CheckExists(Key(jobId, tenantId), ct).ConfigureAwait(false);
        }

        // ─────────────────────────────────────────────────────────────────
        // BootstrapAllAsync
        // ─────────────────────────────────────────────────────────────────

        public async Task BootstrapAllAsync(CancellationToken ct = default)
        {
            List<ServerConfigDTO> tenants;
            try
            {
                tenants = await LoadActiveTenantsAsync(ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "QuartzSyncService.BootstrapAllAsync: failed to enumerate tenants — aborting bootstrap");
                return;
            }

            _logger.LogInformation("QuartzSyncService: bootstrapping {Count} tenant(s)", tenants.Count);

            foreach (var tenant in tenants)
            {
                var login = BuildLogin(tenant);
                List<int> jobIds;

                try
                {
                    var jobs = await _dal.LoadReadyJobsAsync(login, ct).ConfigureAwait(false);
                    // LoadReadyJobsAsync already filters STATUS=1 (active) on both job and
                    // scheduler — reused here purely as "give me every active job for this
                    // tenant", ignoring its due-now/concurrency filtering (irrelevant for a
                    // one-time bootstrap sync).
                    jobIds = jobs.Select(j => j.JobId).Distinct().ToList();
                }
                catch (Exception ex)
                {
                    _logger.LogWarning(ex,
                        "QuartzSyncService.BootstrapAllAsync: failed to load jobs for tenant {ClientId} — skipping",
                        tenant.ClientId);
                    continue;
                }

                foreach (var jobId in jobIds)
                {
                    try
                    {
                        await SyncJobAsync(jobId, login, ct).ConfigureAwait(false);
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError(ex,
                            "QuartzSyncService.BootstrapAllAsync: failed to sync JobId={JobId} TenantId={ClientId} — skipping",
                            jobId, tenant.ClientId);
                    }
                }

                try
                {
                    await PruneOrphanedTriggersAsync(tenant.ClientId, jobIds, ct).ConfigureAwait(false);
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex,
                        "QuartzSyncService.BootstrapAllAsync: failed to prune orphaned triggers for TenantId={ClientId} — skipping",
                        tenant.ClientId);
                }
            }

            _logger.LogInformation("QuartzSyncService: bootstrap complete");
        }

        // ─────────────────────────────────────────────────────────────────
        // PruneOrphanedTriggersAsync
        //
        // BootstrapAllAsync's main loop only ever ADDS/refreshes triggers for jobs
        // LoadReadyJobsAsync currently reports active — it never visits a job that fell
        // OUT of that set, so SyncJobAsync's own inactive-removal branch (line ~78) never
        // runs for it. Because the Quartz ADO job store is persistent, a trigger created
        // while a job was active keeps firing forever after a raw DB status flip (a direct
        // SQL UPDATE to MJOBDEFINE.STATUS, bypassing PauseAsync) — surviving even repeated
        // app restarts, since every restart re-derives the same "currently active" set and
        // still never looks at what's already registered. This walks every trigger already
        // registered for the tenant and removes any whose JobId is not in the active set.
        // ─────────────────────────────────────────────────────────────────

        private async Task PruneOrphanedTriggersAsync(int tenantClientId, List<int> activeJobIds, CancellationToken ct)
        {
            var scheduler = await _schedulerFactory.GetScheduler(ct).ConfigureAwait(false);
            var groupName = $"tenant-{tenantClientId}";
            var activeKeyNames = new HashSet<string>(activeJobIds.Select(id => $"job-{id}"));

            var registeredKeys = await scheduler.GetJobKeys(
                GroupMatcher<JobKey>.GroupEquals(groupName), ct).ConfigureAwait(false);

            foreach (var key in registeredKeys)
            {
                if (activeKeyNames.Contains(key.Name))
                    continue;

                await scheduler.DeleteJob(key, ct).ConfigureAwait(false);
                _logger.LogInformation(
                    "QuartzSyncService: pruned orphaned trigger {Key} — job is no longer in the active set for TenantId={ClientId} (inactive, deleted, or status changed outside PauseAsync)",
                    key, tenantClientId);
            }
        }

        // ─────────────────────────────────────────────────────────────────
        // Trigger construction — same priority order as SchedulerTaskServiceBLL.IsDue,
        // deliberately NOT trusting TSCHEDULER.SCHEDULERTYPE alone.
        // ─────────────────────────────────────────────────────────────────

        private ITrigger? BuildTrigger(SchedulerTaskDTO job, JobKey key)
        {
            var builder = TriggerBuilder.Create().WithIdentity(key.Name, key.Group);

            // Misfire handling instructions matter specifically for a trigger whose computed
            // NextFireTime lands within the brief window between QuartzBootstrapHostedService
            // syncing jobs and the scheduler's own internal thread actually starting to poll —
            // without an explicit instruction, a trigger that misses its fire time this way
            // goes to the ADO job store's ERROR state instead of just firing/catching up, and
            // never recovers on its own (observed directly this session).
            if (!string.IsNullOrWhiteSpace(job.CronExpression))
            {
                // Quartz's own CronScheduleBuilder wants native Quartz-format cron (7-part,
                // '?' wildcard) — pass as-is. Do NOT run through SchedulerCronHelper.NormalizeQuartz,
                // which converts the OTHER direction (Quartz format -> Cronos format) for IsDue/
                // ComputeNextRun's own cron evaluation — feeding that normalized form back into
                // Quartz's builder would be wrong.
                return builder.WithSchedule(CronScheduleBuilder.CronSchedule(job.CronExpression)
                    .WithMisfireHandlingInstructionFireAndProceed()).Build();
            }

            if (TimeSpan.TryParse(job.OccursAtTime, out var time))
            {
                return builder.WithSchedule(
                    CronScheduleBuilder.DailyAtHourAndMinute(time.Hours, time.Minutes)
                        .WithMisfireHandlingInstructionFireAndProceed()).Build();
            }

            if (job.EveryNoOfHours > 0 || job.EveryNoOfMinutes > 0)
            {
                var interval = TimeSpan.FromHours(job.EveryNoOfHours).Add(TimeSpan.FromMinutes(job.EveryNoOfMinutes));
                return builder.WithSchedule(
                    SimpleScheduleBuilder.Create().WithInterval(interval).RepeatForever()
                        .WithMisfireHandlingInstructionFireNow()).Build();
            }

            if (string.Equals(job.SchedulerType, "ONE_TIME", StringComparison.OrdinalIgnoreCase)
                && job.ScheduledDate.HasValue)
            {
                return builder.StartAt(new DateTimeOffset(DateTime.SpecifyKind(job.ScheduledDate.Value, DateTimeKind.Utc))).Build();
            }

            return null;
        }

        // ─────────────────────────────────────────────────────────────────
        // Tenant enumeration — same MSERVERCONFIG/MSERVER query used by
        // SysJobExecutorQuartzJob / BaseJobQueueProcessor. No shared
        // abstraction exists for this in the codebase; duplicated here
        // following the same established convention.
        // ─────────────────────────────────────────────────────────────────

        private async Task<List<ServerConfigDTO>> LoadActiveTenantsAsync(CancellationToken ct)
        {
            var systemConn = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
            int dbType = _databaseDTO.Value.DataBaseType;

            const string tenantSql = @"
                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'
                  AND (@ServerId = 0 OR SERVERCONFIG1.SERVERID = @ServerId)
                  -- Tracker §47/§48 — per-tenant opt-in, default Disabled; found live that
                  -- flipping SchedulerRun:Enable=Y for one tenant's own verification caused
                  -- real polling attempts against every other registered tenant.
                  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 SqlMapper.QueryAsync<ServerConfigDTO>(
                connection, tenantSql, new { ServerId = _schedulerServerId }).ConfigureAwait(false)).ToList();
        }

        // ApplicationConnection.DBConnectionStringCached resolves connection strings by
        // treating LoginDTO.DatabaseName as a lookup key against MSERVERCONFIG.CONNECTIONNAME
        // (confusingly named, but confirmed elsewhere — see ActionProcessorWorker.BuildLogin's
        // comment). Using the literal tenant.DatabaseName here would fail resolution for every
        // tenant, same bug already fixed there.
        private static LoginDTO BuildLogin(ServerConfigDTO tenant) => new()
        {
            UserId                 = -1,
            UserName               = "SchedulerService",
            ClientId               = tenant.ClientId,
            ConnectionDatabaseName = tenant.ConnectionName,
            DatabaseName           = tenant.ConnectionName,
            DatabaseType           = tenant.DbType
        };
    }
}