using System;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.VoucherNumber;
using GB5Shared.Query.VoucherNumber;
using GB5Shared.QueryExecutor;

namespace GB5Shared.GenerateAutoNumber
{
    /// <summary>
    /// Dapper-based equivalent of GB4's VnoGenerationDLL.GetNextVnoWithSameSession.
    /// Generates formatted voucher numbers by atomically incrementing the MBIZTRANSACTIONKEYS
    /// counter for the applicable period slot (yearly / monthly / daily / continuous).
    ///
    /// Period sentinel values stored in MBIZTRANSACTIONKEYS.YEAR:
    ///   -2 → Yearly    (MONTH = 0, DAY = 0)
    ///   -1 → Continuous (MONTH = 0, DAY = 0)
    ///   actual year → Monthly or Daily
    ///
    /// Number format: SystemPrefix(date) + Prefix + PaddedLastNo + Suffix + SystemSuffix(date)
    /// where SystemPrefix/Suffix are .NET date-format strings (e.g. "yy" → "25").
    ///
    /// Registration: services.AddScoped&lt;VoucherNumberService&gt;()
    /// </summary>
    public class VoucherNumberService
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly AutoNumber _autoNumber;

        public VoucherNumberService(IQueryExecutor queryExecutor, AutoNumber autoNumber)
        {
            _queryExecutor = queryExecutor;
            _autoNumber    = autoNumber;
        }

        /// <summary>
        /// Returns the next formatted voucher number for the given BizTransactionTypeId,
        /// or <c>null</c> when NOGenerationType == 1 (Manual) — in that case the caller
        /// should keep the user-supplied number as-is.
        /// </summary>
        /// <param name="bizTransactionTypeId">BizTransactionType primary key.</param>
        /// <param name="voucherDate">The document / voucher date used for period bucketing and SystemPrefix/Suffix formatting.</param>
        /// <param name="loginDTO">Current user session.</param>
        /// <param name="transaction">
        /// Active DB transaction. Both the MBIZTRANSACTIONKEYS UPDATE/INSERT and the
        /// entity INSERT must share the same transaction so that a rollback reverts the
        /// counter increment along with the entity row.
        /// </param>
        /// <param name="ct">Cancellation token.</param>
        /// <param name="prefixOverride">
        /// When supplied (including ""), replaces config.Prefix in the formatted output.
        /// Pass "" from TaskBLL so BuildTaskNumber provides the sole type-letter prefix,
        /// matching GB4's pattern: Task.Number = 'T' + VnoGenerationDLL.GetNextVno(...)
        /// </param>
        // Overload for callers that still pass DateTime (other services not yet migrated)
        public Task<string?> GetNextVoucherNumberAsync(
            int bizTransactionTypeId,
            DateTime voucherDate,
            LoginDTO loginDTO,
            DbTransaction? transaction = null,
            CancellationToken ct = default,
            string? prefixOverride = null)
            => GetNextVoucherNumberAsync(
                bizTransactionTypeId,
                DateOnly.FromDateTime(voucherDate),
                loginDTO, transaction, ct, prefixOverride);

        public async Task<string?> GetNextVoucherNumberAsync(
            int bizTransactionTypeId,
            DateOnly voucherDate,
            LoginDTO loginDTO,
            DbTransaction? transaction = null,
            CancellationToken ct = default,
            string? prefixOverride = null)
        {
            // ── Step 1: load BizTransactionType config ─────────────────────────────────
            var configs = (await _queryExecutor.QueryAsync<VoucherNumberConfigDTO>(
                loginDTO,
                VoucherNumberQB.GET_BIZTRANSACTIONTYPE_CONFIG,
                new { BizTransactionTypeId = bizTransactionTypeId })).ToList();

            if (configs.Count == 0)
                throw new InvalidOperationException(
                    $"BizTransactionType (Id={bizTransactionTypeId}) not found in MBIZTRANSACTIONTYPE. " +
                    "Contact Administrator.");

            var config = configs[0];

            // Manual generation — caller keeps user-supplied number unchanged
            if (config.NOGenerationType == 1)
                return null;

            // ── Step 2: resolve period-slot sentinel values ────────────────────────────
            BuildPeriodValues(config.NOGenerationPeriodType, voucherDate,
                out int yearVal, out int monthVal, out int dayVal);

            // ── Steps 3+4: atomically increment the counter ───────────────────────────
            // UPDATE ... OUTPUT INSERTED.LASTNO returns the new value in one statement.
            // SQL Server serialises concurrent UPDATEs on the same row so no two requests
            // can receive the same LASTNO.  If 0 rows returned the period slot does not
            // exist yet — INSERT it with LASTNO = 1.  A retry handles the narrow race
            // where two requests both see 0 rows and race to INSERT.
            var keyParam = new
            {
                BizTransactionTypeId = bizTransactionTypeId,
                Year                 = yearVal,
                Month                = monthVal,
                Day                  = dayVal
            };

            int lastNo = await IncrementOrInsertAsync(
                loginDTO, config, keyParam, bizTransactionTypeId,
                yearVal, monthVal, dayVal, transaction, ct);

            // ── Step 5: format and return ──────────────────────────────────────────────
            return FormatVoucherNumber(config, lastNo, voucherDate, prefixOverride);
        }

        // ── Private helpers ───────────────────────────────────────────────────────────

        /// <summary>
        /// Atomically increments MBIZTRANSACTIONKEYS.LASTNO for the given period slot and
        /// returns the new value. On the very first use of a period the row is created with
        /// LASTNO = 1. A retry handles the rare race where two concurrent requests both
        /// see a missing row and race to INSERT.
        /// </summary>
        private async Task<int> IncrementOrInsertAsync(
            LoginDTO loginDTO,
            VoucherNumberConfigDTO config,
            object keyParam,
            int bizTransactionTypeId,
            int yearVal, int monthVal, int dayVal,
            DbTransaction? transaction,
            CancellationToken ct)
        {
            // Attempt 1: atomic increment on an existing row.
            int lastNo = (await _queryExecutor.QueryAsync<int>(
                loginDTO,
                VoucherNumberQB.INCREMENT_LASTNO,
                keyParam,
                transaction)).FirstOrDefault();

            if (lastNo > 0)
                return lastNo;

            // Row doesn't exist yet — create it with LASTNO = 1.
            // Two concurrent requests can both reach this branch; catch the unique-key
            // violation the loser gets and retry the UPDATE (which now finds the winner's row).
            try
            {
                var newKeyId = await _autoNumber.GetNumberAsync(1, "BIZTRANSACTIONKEYS", loginDTO);

                await _queryExecutor.ExecuteAsync(
                    loginDTO,
                    VoucherNumberQB.INSERT_BIZTRANSACTIONKEYS,
                    new
                    {
                        BizTransactionKeysId = newKeyId.StartNumber,
                        BizTransactionTypeId = bizTransactionTypeId,
                        Year                 = yearVal,
                        Month                = monthVal,
                        Day                  = dayVal,
                        Prefix               = config.Prefix,
                        Suffix               = config.Suffix,
                        SystemPrefix         = config.SystemPrefix
                    },
                    transaction);

                return 1;
            }
            catch (SqlException ex) when (ex.Number == 2627 || ex.Number == 2601)
            {
                // Another request inserted the row first. Retry the atomic increment.
                lastNo = (await _queryExecutor.QueryAsync<int>(
                    loginDTO,
                    VoucherNumberQB.INCREMENT_LASTNO,
                    keyParam,
                    transaction)).FirstOrDefault();

                if (lastNo > 0)
                    return lastNo;

                throw new InvalidOperationException(
                    $"Failed to generate voucher number for BizTransactionTypeId={bizTransactionTypeId} " +
                    $"after INSERT collision. Contact Administrator.", ex);
            }
        }

        /// <summary>
        /// Converts NOGenerationPeriodType to the sentinel Year/Month/Day values that
        /// MBIZTRANSACTIONKEYS uses to partition counters by period.
        /// </summary>
        private static void BuildPeriodValues(
            byte periodType, DateOnly date,
            out int year, out int month, out int day)
        {
            switch (periodType)
            {
                case 0: // Yearly — single row per BizTransactionType, YEAR sentinel = -2
                    year  = -2;
                    month = 0;
                    day   = 0;
                    break;

                case 1: // Monthly — one row per year+month
                    year  = date.Year;
                    month = date.Month;
                    day   = 0;
                    break;

                case 2: // Daily — one row per year+month+day
                    year  = date.Year;
                    month = date.Month;
                    day   = date.Day;
                    break;

                case 3: // Continuous — single ever-incrementing row, YEAR sentinel = -1
                default:
                    year  = -1;
                    month = 0;
                    day   = 0;
                    break;
            }
        }

        /// <summary>
        /// Builds the formatted number string:
        ///   SystemPrefix(date-formatted) + Prefix + ZeroPaddedLastNo + Suffix + SystemSuffix(date-formatted)
        ///
        /// The numeric portion is left-padded with zeros so the total string length equals
        /// NumberSize. Throws if the fixed parts already exceed NumberSize.
        /// </summary>
        private static string FormatVoucherNumber(
            VoucherNumberConfigDTO config, int lastNo, DateOnly voucherDate,
            string? prefixOverride = null)
        {
            string systemPrefix = FormatDatePattern(config.SystemPrefix, voucherDate, nameof(config.SystemPrefix));
            string systemSuffix = FormatDatePattern(config.SystemSuffix, voucherDate, nameof(config.SystemSuffix));
            // prefixOverride: when supplied (including ""), replaces config.Prefix.
            // This lets callers (e.g. TaskBLL) skip the DB-configured prefix and apply
            // their own type letter via BuildTaskNumber, matching GB4's pattern of
            //   Task.Number = 'T' + VnoGenerationDLL.GetNextVnoWithSameSession(...)
            string prefix    = prefixOverride ?? (config.Prefix ?? string.Empty);
            string suffix    = config.Suffix ?? string.Empty;
            string numberStr = lastNo.ToString();

            if (config.NumberSize > 0)
            {
                int fixedLength    = systemPrefix.Length + prefix.Length + suffix.Length + systemSuffix.Length;
                int availableWidth = config.NumberSize - fixedLength;

                if (availableWidth < numberStr.Length)
                    throw new InvalidOperationException(
                        $"Generated voucher number ({numberStr.Length} digits) exceeds the available " +
                        $"numeric width ({availableWidth}) within NumberSize={config.NumberSize}. " +
                        "Increase NumberSize in BizTransactionType settings.");

                numberStr = numberStr.PadLeft(availableWidth, '0');
            }

            return $"{systemPrefix}{prefix}{numberStr}{suffix}{systemSuffix}";
        }

        /// <summary>
        /// Formats a date using the given pattern (e.g. "yy" → "25", "yyyyMM" → "202501").
        /// Returns empty string if the pattern is null or empty.
        /// Throws a descriptive exception on invalid format strings.
        /// </summary>
        private static string FormatDatePattern(string? pattern, DateOnly date, string fieldName)
        {
            if (string.IsNullOrEmpty(pattern))
                return string.Empty;

            try
            {
                return date.ToString(pattern, CultureInfo.GetCultureInfo("en-US"));
            }
            catch (FormatException ex)
            {
                throw new InvalidOperationException(
                    $"Invalid date-format pattern '{pattern}' in BizTransactionType '{fieldName}'. " +
                    "Use standard .NET date-format strings (e.g. \"yy\", \"yyyyMM\"). " +
                    "Contact Administrator.", ex);
            }
        }
    }
}