using Dapper;
using Sonex.Data.Database;
using System.Collections;
using System.Data;
using System.Globalization;
using System.Text;

namespace Sonex.Data.Records;

public sealed class WorkerScheduleRecord
{
    public const string ActionStart = "Start";
    public const string ActionStop = "Stop";
    public const string ActionRestart = "Restart";

    private const string JobNameSeparator = " :: ";
    private const string ScheduleName = "Default schedule";

    public string WorkerName { get; set; } = string.Empty;
    public int? JobId { get; set; }
    public string JobName { get; set; } = string.Empty;
    public bool JobEnabled { get; set; } = true;
    public DateTime? NextRun { get; set; }
    public DateTime? LastRun { get; set; }

    public int? StepId { get; set; }
    public string StepName { get; set; } = string.Empty;
    public bool StepEnabled { get; set; } = true;
    public string? StepCode { get; set; }

    public int? ScheduleId { get; set; }
    public bool ScheduleEnabled { get; set; } = true;
    public DateTime? StartAt { get; set; }
    public bool[] Minutes { get; set; } = CreateArray(60);
    public bool[] Hours { get; set; } = CreateArray(24);
    public bool[] WeekDays { get; set; } = CreateArray(7);
    public bool[] MonthDays { get; set; } = CreateArray(32);
    public bool[] Months { get; set; } = CreateArray(12);

    public string Action { get; set; } = ActionStart;

    public static async Task<DB.Result<WorkerScheduleRecord>> GetByWorkerName(
        string workerName,
        CancellationToken ct = default)
    {
        string normalizedWorkerName = Normalize(workerName);
        if (string.IsNullOrWhiteSpace(normalizedWorkerName))
            return FailedList("Worker name cannot be empty.");

        string workerNamePrefix = normalizedWorkerName + JobNameSeparator + "%";

        DB.Result<WorkerScheduleRecord> result = await DB.QueryListAsync<WorkerScheduleRecord>(
            """
            SELECT
                job.jobid,
                job.jobname,
                job.jobenabled,
                job.jobnextrun AS next_run,
                job.joblastrun AS last_run,
                step.jstid AS step_id,
                step.jstname AS step_name,
                COALESCE(step.jstenabled, false) AS step_enabled,
                step.jstcode AS step_code,
                schedule.jscid AS schedule_id,
                COALESCE(schedule.jscenabled, false) AS schedule_enabled,
                schedule.jscstart AS start_at,
                schedule.jscminutes AS minutes,
                schedule.jschours AS hours,
                schedule.jscweekdays AS week_days,
                schedule.jscmonthdays AS month_days,
                schedule.jscmonths AS months
            FROM pgagent.pga_job job
            LEFT JOIN LATERAL (
                SELECT *
                FROM pgagent.pga_jobstep
                WHERE jstjobid = job.jobid
                ORDER BY jstid
                LIMIT 1
            ) step ON true
            LEFT JOIN LATERAL (
                SELECT *
                FROM pgagent.pga_schedule
                WHERE jscjobid = job.jobid
                ORDER BY jscid
                LIMIT 1
            ) schedule ON true
            WHERE job.jobname = @WorkerName
               OR job.jobname LIKE @WorkerNamePrefix
            ORDER BY job.jobid;
            """,
            new
            {
                WorkerName = normalizedWorkerName,
                WorkerNamePrefix = workerNamePrefix
            },
            ct: ct).ConfigureAwait(false);

        if (!result.Success)
            return result;

        foreach (WorkerScheduleRecord item in result.Items)
        {
            item.WorkerName = ResolveWorkerName(item.JobName, normalizedWorkerName);
            item.Action = ResolveAction(item.StepName, item.StepCode);
            NormalizeArrays(item);
        }

        return result;
    }

    public static async Task<DB.SingleResult<bool>> Save(
        WorkerScheduleRecord schedule,
        CancellationToken ct = default)
    {
        ArgumentNullException.ThrowIfNull(schedule);

        string workerName = Normalize(schedule.WorkerName);
        if (string.IsNullOrWhiteSpace(workerName))
            return FailedBool("Worker name cannot be empty.");

        string validationError = Validate(schedule);
        if (!string.IsNullOrWhiteSpace(validationError))
            return FailedBool(validationError);

        try
        {
            using IDbConnection connection = DB.Open();
            using IDbTransaction transaction = connection.BeginTransaction();

            int jobClassId = await GetJobClassId(connection, transaction, ct).ConfigureAwait(false);
            int jobId = await EnsureJob(connection, transaction, schedule, workerName, jobClassId, ct).ConfigureAwait(false);
            int stepId = await EnsureStep(connection, transaction, jobId, schedule, workerName, ct).ConfigureAwait(false);
            int scheduleId = await EnsureSchedule(connection, transaction, jobId, schedule, ct).ConfigureAwait(false);

            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    DELETE FROM pgagent.pga_jobstep
                    WHERE jstjobid = @JobId
                      AND jstid <> @StepId;

                    DELETE FROM pgagent.pga_schedule
                    WHERE jscjobid = @JobId
                      AND jscid <> @ScheduleId;
                    """,
                    new
                    {
                        JobId = jobId,
                        StepId = stepId,
                        ScheduleId = scheduleId
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            transaction.Commit();
            return new DB.SingleResult<bool>
            {
                Success = true,
                Item = true
            };
        }
        catch (OperationCanceledException)
        {
            throw;
        }
        catch (Exception ex)
        {
            return ToBoolError(ex);
        }
    }

    public static async Task<DB.SingleResult<bool>> Delete(
        string workerName,
        int jobId,
        CancellationToken ct = default)
    {
        string normalizedWorkerName = Normalize(workerName);
        if (string.IsNullOrWhiteSpace(normalizedWorkerName))
            return FailedBool("Worker name cannot be empty.");

        if (jobId <= 0)
            return FailedBool("Invalid job id.");

        try
        {
            using IDbConnection connection = DB.Open();
            using IDbTransaction transaction = connection.BeginTransaction();

            string workerNamePrefix = normalizedWorkerName + JobNameSeparator + "%";

            int? matchedJobId = await connection.QuerySingleOrDefaultAsync<int?>(
                new CommandDefinition(
                    """
                    SELECT jobid
                    FROM pgagent.pga_job
                    WHERE jobid = @JobId
                      AND (
                          jobname = @WorkerName
                          OR jobname LIKE @WorkerNamePrefix
                      )
                    LIMIT 1;
                    """,
                    new
                    {
                        JobId = jobId,
                        WorkerName = normalizedWorkerName,
                        WorkerNamePrefix = workerNamePrefix
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            if (!matchedJobId.HasValue)
            {
                transaction.Rollback();
                return FailedBool("Schedule line was not found for this worker.");
            }

            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    DELETE FROM pgagent.pga_schedule
                    WHERE jscjobid = @JobId;

                    DELETE FROM pgagent.pga_jobstep
                    WHERE jstjobid = @JobId;

                    DELETE FROM pgagent.pga_job
                    WHERE jobid = @JobId;
                    """,
                    new
                    {
                        JobId = matchedJobId.Value
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            transaction.Commit();

            return new DB.SingleResult<bool>
            {
                Success = true,
                Item = true
            };
        }
        catch (OperationCanceledException)
        {
            throw;
        }
        catch (Exception ex)
        {
            return ToBoolError(ex);
        }
    }

    private static async Task<int> GetJobClassId(
        IDbConnection connection,
        IDbTransaction transaction,
        CancellationToken ct)
    {
        int? jobClassId = await connection.QuerySingleOrDefaultAsync<int?>(
            new CommandDefinition(
                """
                SELECT jclid
                FROM pgagent.pga_jobclass
                WHERE jclname = 'Miscellaneous'
                ORDER BY jclid
                LIMIT 1;
                """,
                transaction: transaction,
                cancellationToken: ct)).ConfigureAwait(false);

        jobClassId ??= await connection.QuerySingleOrDefaultAsync<int?>(
            new CommandDefinition(
                """
                SELECT jclid
                FROM pgagent.pga_jobclass
                ORDER BY jclid
                LIMIT 1;
                """,
                transaction: transaction,
                cancellationToken: ct)).ConfigureAwait(false);

        if (!jobClassId.HasValue)
            throw new InvalidOperationException("No pgAgent job class found.");

        return jobClassId.Value;
    }

    private static async Task<int> EnsureJob(
        IDbConnection connection,
        IDbTransaction transaction,
        WorkerScheduleRecord schedule,
        string workerName,
        int jobClassId,
        CancellationToken ct)
    {
        int? jobId = schedule.JobId;

        if (jobId.HasValue)
        {
            int? existingJobId = await connection.QuerySingleOrDefaultAsync<int?>(
                new CommandDefinition(
                    """
                    SELECT jobid
                    FROM pgagent.pga_job
                    WHERE jobid = @JobId
                    LIMIT 1;
                    """,
                    new
                    {
                        JobId = jobId.Value
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            if (!existingJobId.HasValue)
                jobId = null;
        }

        string jobName = string.IsNullOrWhiteSpace(schedule.JobName)
            ? BuildJobName(workerName, schedule.Action)
            : schedule.JobName.Trim();

        string jobDescription = $"Sonex worker schedule for {workerName}. Action: {NormalizeAction(schedule.Action)}.";

        if (jobId.HasValue)
        {
            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    UPDATE pgagent.pga_job
                    SET
                        jobjclid = @JobClassId,
                        jobname = @JobName,
                        jobdesc = @JobDescription,
                        jobenabled = @JobEnabled,
                        jobchanged = current_timestamp,
                        jobnextrun = NULL
                    WHERE jobid = @JobId;
                    """,
                    new
                    {
                        JobId = jobId.Value,
                        JobClassId = jobClassId,
                        JobName = jobName,
                        JobDescription = jobDescription,
                        JobEnabled = schedule.JobEnabled
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            return jobId.Value;
        }

        return await connection.QuerySingleAsync<int>(
            new CommandDefinition(
                """
                INSERT INTO pgagent.pga_job (
                    jobjclid,
                    jobname,
                    jobdesc,
                    jobenabled
                )
                VALUES (
                    @JobClassId,
                    @JobName,
                    @JobDescription,
                    @JobEnabled
                )
                RETURNING jobid;
                """,
                new
                {
                    JobClassId = jobClassId,
                    JobName = jobName,
                    JobDescription = jobDescription,
                    JobEnabled = schedule.JobEnabled
                },
                transaction: transaction,
                cancellationToken: ct)).ConfigureAwait(false);
    }

    private static async Task<int> EnsureStep(
        IDbConnection connection,
        IDbTransaction transaction,
        int jobId,
        WorkerScheduleRecord schedule,
        string workerName,
        CancellationToken ct)
    {
        int? stepId = schedule.StepId;

        if (!stepId.HasValue)
        {
            stepId = await connection.QuerySingleOrDefaultAsync<int?>(
                new CommandDefinition(
                    """
                    SELECT jstid
                    FROM pgagent.pga_jobstep
                    WHERE jstjobid = @JobId
                    ORDER BY jstid
                    LIMIT 1;
                    """,
                    new
                    {
                        JobId = jobId
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);
        }

        string action = NormalizeAction(schedule.Action);
        string stepName = BuildStepName(action);
        string stepDescription = $"{action} Windows service {workerName}.";
        string stepCode = BuildStepCode(action, workerName);

        if (stepId.HasValue)
        {
            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    UPDATE pgagent.pga_jobstep
                    SET
                        jstname = @StepName,
                        jstdesc = @StepDescription,
                        jstenabled = @StepEnabled,
                        jstkind = 'b',
                        jstcode = @StepCode,
                        jstconnstr = '',
                        jstdbname = '',
                        jstonerror = 'f'
                    WHERE jstid = @StepId;
                    """,
                    new
                    {
                        StepId = stepId.Value,
                        StepName = stepName,
                        StepDescription = stepDescription,
                        StepEnabled = schedule.StepEnabled,
                        StepCode = stepCode
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            return stepId.Value;
        }

        return await connection.QuerySingleAsync<int>(
            new CommandDefinition(
                """
                INSERT INTO pgagent.pga_jobstep (
                    jstjobid,
                    jstname,
                    jstdesc,
                    jstenabled,
                    jstkind,
                    jstcode,
                    jstconnstr,
                    jstdbname,
                    jstonerror
                )
                VALUES (
                    @JobId,
                    @StepName,
                    @StepDescription,
                    @StepEnabled,
                    'b',
                    @StepCode,
                    '',
                    '',
                    'f'
                )
                RETURNING jstid;
                """,
                new
                {
                    JobId = jobId,
                    StepName = stepName,
                    StepDescription = stepDescription,
                    StepEnabled = schedule.StepEnabled,
                    StepCode = stepCode
                },
                transaction: transaction,
                cancellationToken: ct)).ConfigureAwait(false);
    }

    private static async Task<int> EnsureSchedule(
        IDbConnection connection,
        IDbTransaction transaction,
        int jobId,
        WorkerScheduleRecord schedule,
        CancellationToken ct)
    {
        int? scheduleId = schedule.ScheduleId;

        if (!scheduleId.HasValue)
        {
            scheduleId = await connection.QuerySingleOrDefaultAsync<int?>(
                new CommandDefinition(
                    """
                    SELECT jscid
                    FROM pgagent.pga_schedule
                    WHERE jscjobid = @JobId
                    ORDER BY jscid
                    LIMIT 1;
                    """,
                    new
                    {
                        JobId = jobId
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);
        }

        DateTime startAt = ToPostgreSqlTimestamp(schedule.StartAt!.Value);

        var parameters = new
        {
            ScheduleId = scheduleId,
            JobId = jobId,
            ScheduleName,
            ScheduleEnabled = schedule.ScheduleEnabled,
            StartAt = startAt,
            Minutes = NormalizeArray(schedule.Minutes, 60),
            Hours = NormalizeArray(schedule.Hours, 24),
            WeekDays = NormalizeArray(schedule.WeekDays, 7),
            MonthDays = NormalizeArray(schedule.MonthDays, 32),
            Months = NormalizeArray(schedule.Months, 12)
        };

        if (scheduleId.HasValue)
        {
            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    UPDATE pgagent.pga_schedule
                    SET
                        jscname = @ScheduleName,
                        jscdesc = '',
                        jscenabled = @ScheduleEnabled,
                        jscstart = @StartAt,
                        jscend = NULL,
                        jscminutes = @Minutes,
                        jschours = @Hours,
                        jscweekdays = @WeekDays,
                        jscmonthdays = @MonthDays,
                        jscmonths = @Months
                    WHERE jscid = @ScheduleId;
                    """,
                    parameters,
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            return scheduleId.Value;
        }

        return await connection.QuerySingleAsync<int>(
            new CommandDefinition(
                """
                INSERT INTO pgagent.pga_schedule (
                    jscjobid,
                    jscname,
                    jscdesc,
                    jscenabled,
                    jscstart,
                    jscend,
                    jscminutes,
                    jschours,
                    jscweekdays,
                    jscmonthdays,
                    jscmonths
                )
                VALUES (
                    @JobId,
                    @ScheduleName,
                    '',
                    @ScheduleEnabled,
                    @StartAt,
                    NULL,
                    @Minutes,
                    @Hours,
                    @WeekDays,
                    @MonthDays,
                    @Months
                )
                RETURNING jscid;
                """,
                parameters,
                transaction: transaction,
                cancellationToken: ct)).ConfigureAwait(false);
    }

    private static string Validate(WorkerScheduleRecord schedule)
    {
        if (!schedule.StartAt.HasValue)
            return "Schedule start date is required.";

        if (!HasSelectedValue(schedule.Minutes, 60))
            return "Schedule must contain at least one minute.";

        if (!HasSelectedValue(schedule.Hours, 24))
            return "Schedule must contain at least one hour.";

        if (!HasSelectedValue(schedule.WeekDays, 7))
            return "Schedule must contain at least one weekday.";

        if (!IsKnownAction(schedule.Action))
            return "Schedule action must be Start, Stop or Restart.";

        return string.Empty;
    }

    private static string BuildJobName(string workerName, string action)
    {
        string timestamp = DateTime.UtcNow.ToString("yyyyMMddHHmmssfff", CultureInfo.InvariantCulture);
        return $"{workerName}{JobNameSeparator}{NormalizeAction(action)}{JobNameSeparator}{timestamp}";
    }

    private static string BuildStepName(string action)
    {
        return action switch
        {
            ActionStop => "Stop Windows service",
            ActionRestart => "Restart Windows service",
            _ => "Start Windows service"
        };
    }

    private static string BuildStepCode(string action, string workerName)
    {
        string serviceName = Normalize(workerName).Replace("\"", string.Empty);
        return action switch
        {
            ActionStop => $"""
                @echo off
                sc.exe stop "{serviceName}"
                exit /b %ERRORLEVEL%
                """,
            ActionRestart => $"""
                @echo off
                sc.exe stop "{serviceName}"
                if %ERRORLEVEL% NEQ 0 if %ERRORLEVEL% NEQ 1062 exit /b %ERRORLEVEL%
                timeout /t 2 /nobreak >nul
                sc.exe start "{serviceName}"
                exit /b %ERRORLEVEL%
                """,
            _ => $"""
                @echo off
                sc.exe start "{serviceName}"
                exit /b %ERRORLEVEL%
                """
        };
    }

    private static string ResolveAction(string? stepName, string? stepCode)
    {
        string normalizedName = Normalize(stepName).ToUpperInvariant();
        string normalizedCode = Normalize(stepCode).ToUpperInvariant();

        if (normalizedName.Contains("RESTART", StringComparison.Ordinal) ||
            (normalizedCode.Contains("SC.EXE STOP", StringComparison.Ordinal) &&
             normalizedCode.Contains("SC.EXE START", StringComparison.Ordinal)))
        {
            return ActionRestart;
        }

        if (normalizedName.Contains("STOP", StringComparison.Ordinal) ||
            normalizedCode.Contains("SC.EXE STOP", StringComparison.Ordinal))
        {
            return ActionStop;
        }

        return ActionStart;
    }

    private static string ResolveWorkerName(string? jobName, string fallbackWorkerName)
    {
        string normalizedJobName = Normalize(jobName);
        if (string.IsNullOrWhiteSpace(normalizedJobName))
            return fallbackWorkerName;

        int separatorIndex = normalizedJobName.IndexOf(JobNameSeparator, StringComparison.Ordinal);
        if (separatorIndex <= 0)
            return normalizedJobName;

        return normalizedJobName[..separatorIndex].Trim();
    }

    private static bool IsKnownAction(string? action)
    {
        return string.Equals(action, ActionStart, StringComparison.OrdinalIgnoreCase) ||
               string.Equals(action, ActionStop, StringComparison.OrdinalIgnoreCase) ||
               string.Equals(action, ActionRestart, StringComparison.OrdinalIgnoreCase);
    }

    private static string NormalizeAction(string? action)
    {
        if (string.Equals(action, ActionStop, StringComparison.OrdinalIgnoreCase))
            return ActionStop;

        if (string.Equals(action, ActionRestart, StringComparison.OrdinalIgnoreCase))
            return ActionRestart;

        return ActionStart;
    }

    private static void NormalizeArrays(WorkerScheduleRecord schedule)
    {
        schedule.Minutes = NormalizeArray(schedule.Minutes, 60);
        schedule.Hours = NormalizeArray(schedule.Hours, 24);
        schedule.WeekDays = NormalizeArray(schedule.WeekDays, 7);
        schedule.MonthDays = NormalizeArray(schedule.MonthDays, 32);
        schedule.Months = NormalizeArray(schedule.Months, 12);
    }

    private static bool[] NormalizeArray(bool[]? values, int length)
    {
        bool[] result = CreateArray(length);
        if (values == null)
            return result;

        int copyLength = Math.Min(values.Length, result.Length);
        Array.Copy(values, result, copyLength);
        return result;
    }

    private static bool HasSelectedValue(bool[]? values, int length)
    {
        bool[] normalized = NormalizeArray(values, length);
        return normalized.Any(static value => value);
    }

    private static bool[] CreateArray(int length)
    {
        return new bool[length];
    }

    private static DateTime ToPostgreSqlTimestamp(DateTime value)
    {
        if (value.Kind == DateTimeKind.Utc)
            return value;

        if (value.Kind == DateTimeKind.Local)
            return value.ToUniversalTime();

        return DateTime.SpecifyKind(value, DateTimeKind.Local).ToUniversalTime();
    }

    private static string Normalize(string? value)
    {
        return string.IsNullOrWhiteSpace(value)
            ? string.Empty
            : value.Trim();
    }

    private static DB.Result<WorkerScheduleRecord> FailedList(string errorMessage)
    {
        return new DB.Result<WorkerScheduleRecord>
        {
            Success = false,
            ErrorMessage = errorMessage
        };
    }

    private static DB.SingleResult<bool> FailedBool(string errorMessage)
    {
        return new DB.SingleResult<bool>
        {
            Success = false,
            Item = false,
            ErrorMessage = errorMessage
        };
    }

    private static DB.SingleResult<bool> ToBoolError(Exception exception)
    {
        return new DB.SingleResult<bool>
        {
            Success = false,
            Item = false,
            ErrorMessage = exception.Message,
            ErrorType = exception.GetType().FullName,
            ErrorStackTrace = exception.StackTrace,
            ErrorData = BuildErrorData(exception)
        };
    }

    private static string? BuildErrorData(Exception exception)
    {
        if (exception.Data is null || exception.Data.Count == 0)
            return null;

        var builder = new StringBuilder();
        foreach (DictionaryEntry entry in exception.Data)
            builder.AppendLine($"{entry.Key}: {entry.Value}");

        return builder.ToString();
    }
}