using Dapper;
using Microsoft.Extensions.Logging;
using Quartz;
using SIGCM2.Application.Audit;
using SIGCM2.Infrastructure.Persistence;

namespace SIGCM2.Infrastructure.Audit.Jobs;

/// <summary>
/// UDT-010 (#REQ-AUD-6) — weekly integrity verification:
/// - Validates SYSTEM_VERSIONING is ON in all cataloged tables.
/// - Validates partitions exist for the next 3 months on both event tables.
/// - Emits a SecurityEvent 'system.integrity_alert' with Result=failure when any
///   check fails; logs success otherwise.
/// - Intended schedule: cron '0 0 1 ? * SUN' (every Sunday at 01:00 UTC).
/// </summary>
[DisallowConcurrentExecution]
public sealed class AuditIntegrityCheckJob : IJob
{
    public const string CronSchedule = "0 0 1 ? * SUN";

    private readonly SqlConnectionFactory _factory;
    private readonly ISecurityEventLogger _security;
    private readonly ILogger<AuditIntegrityCheckJob> _logger;
    private readonly TimeProvider _timeProvider;

    public AuditIntegrityCheckJob(
        SqlConnectionFactory factory,
        ISecurityEventLogger security,
        ILogger<AuditIntegrityCheckJob> logger,
        TimeProvider timeProvider)
    {
        _factory = factory;
        _security = security;
        _logger = logger;
        _timeProvider = timeProvider;
    }

    public async Task Execute(IJobExecutionContext context)
    {
        var ct = context.CancellationToken;
        var failures = new List<string>();

        await using var conn = _factory.CreateConnection();
        await conn.OpenAsync(ct);

        // 1. SYSTEM_VERSIONING still ON
        var missing = (await conn.QueryAsync<string>("""
            SELECT name FROM sys.tables
            WHERE name IN ('Usuario','Rol','Permiso','RolPermiso') AND temporal_type <> 2;
            """)).ToList();
        if (missing.Any())
            failures.Add($"system_versioning_missing:{string.Join(',', missing)}");

        // 2. Next 3 months have partitions in both event tables
        var now = _timeProvider.GetUtcNow().UtcDateTime;
        var required = new[]
        {
            new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(1),
            new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(2),
            new DateTime(now.Year, now.Month, 1, 0, 0, 0, DateTimeKind.Utc).AddMonths(3),
        };

        foreach (var pf in new[] { "pf_AuditEvent_Monthly", "pf_SecurityEvent_Monthly" })
        {
            var existingBoundaries = (await conn.QueryAsync<DateTime>("""
                SELECT CAST(prv.value AS DATETIME2(3))
                FROM sys.partition_functions pf
                JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id
                WHERE pf.name = @Name;
                """, new { Name = pf })).ToHashSet();

            foreach (var req in required)
            {
                if (!existingBoundaries.Contains(req))
                    failures.Add($"partition_missing:{pf}:{req:yyyy-MM-dd}");
            }
        }

        if (failures.Any())
        {
            _logger.LogError("AuditIntegrityCheckJob detected {Count} integrity failures: {Failures}",
                failures.Count, string.Join(" | ", failures));

            await _security.LogAsync(
                action: "system.integrity_alert",
                result: "failure",
                actorUserId: null,
                failureReason: "integrity_check_failed",
                metadata: new { failures, checkedAt = now },
                ct: ct);
        }
        else
        {
            _logger.LogInformation("AuditIntegrityCheckJob completed — all checks passed");
        }
    }
}
