using CostingDAL.DTO.Biztransactionkeys;
using CostingDAL.Query.Biztransaction;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CostingDAL.CustomCode.VnoGeneration
{
    /// <summary>
    /// GB5 VNO Generation Service.
    /// Migrated from GB4 VnoGenerationDLL (AdminDAL.CustomeCode.VnoGeneration).
    ///
    /// GB4 had 3 methods — GetNextVno, GetNextVnoWithSameSession, GetMultiVno.
    /// GetNextVno was dead code (immediately delegated to GetNextVnoWithSameSession).
    /// GB5 consolidates into 2 clean async methods:
    ///   - GetNextVnoAsync    → single VNO (was GetNextVnoWithSameSession)
    ///   - GetMultiVnoAsync   → N VNOs in one DB hit (was GetMultiVno)
    ///
    /// Both always require a DbTransaction — callers (BLL) own the transaction scope.
    /// </summary>
    public class VnoGenerationService : IVnoGenerationService
    {
        private readonly IQueryExecutor _qe;
        private readonly AutoNumber _autoNumber;

        public VnoGenerationService(IQueryExecutor qe, AutoNumber autoNumber)
        {
            _qe = qe;
            _autoNumber = autoNumber;
        }

        // ════════════════════════════════════════════════════════════════
        // GET NEXT VNO — single document number
        // GB4 parity: GetNextVnoWithSameSession
        // ════════════════════════════════════════════════════════════════

        public async Task<string> GetNextVnoAsync(
            int bizTransactionTypeId,
            DateTime vdate,
            LoginDTO login,
            DbTransaction tx,
            CancellationToken ct = default)
        {
            var biz = await LoadBizTransactionType(bizTransactionTypeId, login, tx, ct);

            // GB4 parity: NOGenerationType==1 means manual — throw, do not generate
            if (biz.NOGenerationType == 1)
                throw new InvalidOperationException(
                    $"BizTransactionType [{bizTransactionTypeId}] is set to Manual number generation. " +
                    "Auto-generation is not permitted.");

            var (lastNo, _) = await IncrementKeys(
                count: 1,
                bizTransactionTypeId,
                vdate,
                biz,
                login,
                tx,
                ct);

            // lastNo returned is the NEW number assigned to this document
            return FormatVno(lastNo, vdate, biz);
        }

        // ════════════════════════════════════════════════════════════════
        // GET MULTI VNO — N sequential numbers, one DB round-trip
        // GB4 parity: GetMultiVno
        // ════════════════════════════════════════════════════════════════

        public async Task<List<string>> GetMultiVnoAsync(
            int count,
            int bizTransactionTypeId,
            DateTime vdate,
            LoginDTO login,
            DbTransaction tx,
            CancellationToken ct = default)
        {
            if (count <= 0)
                throw new ArgumentOutOfRangeException(nameof(count), "VNO count must be greater than zero.");

            var biz = await LoadBizTransactionType(bizTransactionTypeId, login, tx, ct);

            if (biz.NOGenerationType == 1)
                throw new InvalidOperationException(
                    $"BizTransactionType [{bizTransactionTypeId}] is set to Manual number generation. " +
                    "Auto-generation is not permitted.");

            // GB4 parity: GetMultiVno increments LastNo by N in one update,
            // then loops from (oldLastNo+1) to (oldLastNo+N) to build formatted numbers.
            var (baseLastNo, _) = await IncrementKeys(
                count,
                bizTransactionTypeId,
                vdate,
                biz,
                login,
                tx,
                ct);

            // baseLastNo is the LAST number in the range assigned.
            // First number = baseLastNo - count + 1.
            // GB4 parity: loop nextvno from 1 to N, number = lastNoBeforeIncrement + nextvno.
            var result = new List<string>(count);
            int firstNo = baseLastNo - count + 1;

            for (int i = 0; i < count; i++)
            {
                result.Add(FormatVno(firstNo + i, vdate, biz));
            }

            return result;
        }

        // ════════════════════════════════════════════════════════════════
        // CORE: INCREMENT KEYS TABLE
        // GB4 parity: reads MBIZTRANSACTIONKEYS, updates or inserts,
        // returns the assigned LastNo value (single) or end-of-range (multi).
        // ════════════════════════════════════════════════════════════════

        private async Task<(int assignedLastNo, int keysId)> IncrementKeys(
            int count,
            int bizTransactionTypeId,
            DateTime vdate,
            BizTransactionTypeVnoDTO biz,
            LoginDTO login,
            DbTransaction tx,
            CancellationToken ct)
        {
            // Build the SELECT query based on period type — same as GB4 switch
            var (sql, param) = BuildKeysSelectQuery(
                biz.NOGenerationPeriodType,
                bizTransactionTypeId,
                vdate);

            var existing = await _qe.QuerySingleAsync<BIZTransactionKeysDTO>(
                login, sql, param);

            int lastNo;
            int keysId;

            if (existing != null)
            {
                // ── EXISTING ROW: UPDATE LastNo += count ─────────────────
                // GB4 parity: GetNextVno does LastNo+1, GetMultiVno does LastNo+count
                lastNo = existing.LastNo + count;
                keysId = existing.BIZTransactionKeysId;

                await _qe.ExecuteAsync(
                    login,
                    BizTransactionQB.UPDATE_BIZTRANSACTIONKEYS_LASTNO,
                    new { LastNo = lastNo, BIZTransactionKeysId = keysId },
                    tx, ct);

                // For single VNO: assigned = lastNo (was LastNo+1)
                // For multi VNO:  range is [existing.LastNo+1 .. lastNo]
            }
            else
            {
                // ── NO ROW YET: INSERT first key row ─────────────────────
                // GB4 parity: LastNo=1 for single, LastNo=count for multi
                var auto = await _autoNumber.GetNumberAsync(1, "BIZTRANSACTIONKEYS", login);
                keysId = auto.StartNumber;
                lastNo = count; // first number assigned is 1 (single) or count (multi end)

                var insertParam = BuildKeysInsertParam(
                    keysId,
                    bizTransactionTypeId,
                    biz,
                    vdate,
                    lastNo);

                await _qe.ExecuteAsync(
                    login,
                    BizTransactionQB.INSERT_BIZTRANSACTIONKEYS,
                    insertParam,
                    tx, ct);
            }

            return (lastNo, keysId);
        }

        // ════════════════════════════════════════════════════════════════
        // FORMAT VNO
        // GB4 parity: FinalSystemPrefix + Prefix + ZeroPad(lastNo) + Suffix + FinalSystemSuffix
        // SystemPrefix/SystemSuffix are DateTime format strings ("yy/MM" etc.)
        // Zero-padding fills to NumberSize total length.
        // Throws if formatted length exceeds NumberSize.
        // ════════════════════════════════════════════════════════════════

        private static string FormatVno(
            int lastNo,
            DateTime vdate,
            BizTransactionTypeVnoDTO biz)
        {
            string finalSystemPrefix = FormatDatePart(vdate, biz.SystemPrefix, "SystemPrefix");
            string finalSystemSuffix = FormatDatePart(vdate, biz.SystemSuffix, "SystemSuffix");
            string prefix = biz.Prefix ?? "";
            string suffix = biz.Suffix ?? "";
            string number = lastNo.ToString();

            int fixedLength = finalSystemPrefix.Length + prefix.Length + suffix.Length + finalSystemSuffix.Length;
            int numberLength = number.Length;
            int totalLength = fixedLength + numberLength;

            if (totalLength == biz.NumberSize)
            {
                // Exact fit — no padding needed
                return finalSystemPrefix + prefix + number + suffix + finalSystemSuffix;
            }
            else if (totalLength < biz.NumberSize)
            {
                // Pad the number with leading zeros to fill NumberSize
                int padCount = biz.NumberSize - fixedLength;
                string paddedNumber = number.PadLeft(padCount, '0');
                return finalSystemPrefix + prefix + paddedNumber + suffix + finalSystemSuffix;
            }
            else
            {
                // Generated length exceeds configured NumberSize — same exception as GB4
                throw new InvalidOperationException(
                    $"Generated VNO exceeds the configured NumberSize [{biz.NumberSize}] for " +
                    $"BizTransactionType. Increase NumberSize or reduce Prefix/Suffix length.");
            }
        }

        // ════════════════════════════════════════════════════════════════
        // BUILD SELECT QUERY FOR KEYS TABLE
        // GB4 parity: 4 period types
        //   0 = Yearly      → Year=-2 (all years, no year filter)  
        //   1 = Monthly     → filter by Year + Month
        //   2 = Daily       → filter by Year + Month + Day
        //   3 = Continuously → Year=-1 (no date filter at all)
        // ════════════════════════════════════════════════════════════════

        private static (string sql, object param) BuildKeysSelectQuery(
            int periodType,
            int bizTransactionTypeId,
            DateTime vdate)
        {
            return periodType switch
            {
                0 => (   // Yearly — GB4 Year=-2 sentinel
                    BizTransactionQB.GET_BIZTRANSACTIONKEYS_YEARLY,
                    (object)new { BIZTransactionTypeId = bizTransactionTypeId }),

                1 => (   // Monthly
                    BizTransactionQB.GET_BIZTRANSACTIONKEYS_MONTHLY,
                    (object)new
                    {
                        BIZTransactionTypeId = bizTransactionTypeId,
                        Year = vdate.Year,
                        Month = vdate.Month
                    }),

                2 => (   // Daily
                    BizTransactionQB.GET_BIZTRANSACTIONKEYS_DAILY,
                    (object)new
                    {
                        BIZTransactionTypeId = bizTransactionTypeId,
                        Year = vdate.Year,
                        Month = vdate.Month,
                        Day = vdate.Day
                    }),

                3 => (   // Continuously — no date filter
                    BizTransactionQB.GET_BIZTRANSACTIONKEYS_CONTINUOUS,
                    (object)new { BIZTransactionTypeId = bizTransactionTypeId }),

                _ => throw new InvalidOperationException(
                    $"Unknown NOGenerationPeriodType [{periodType}].")
            };
        }

        // ════════════════════════════════════════════════════════════════
        // BUILD INSERT PARAM FOR NEW KEYS ROW
        // GB4 parity: Year/Month/Day sentinel values per period type
        //   Yearly:       Year=-2, Month=0, Day=0
        //   Monthly:      Year=actual, Month=actual, Day=0
        //   Daily:        Year=actual, Month=actual, Day=actual
        //   Continuously: Year=-1, Month=0, Day=0
        // ════════════════════════════════════════════════════════════════

        private static object BuildKeysInsertParam(
    int keysId,
    int bizTransactionTypeId,
    BizTransactionTypeVnoDTO biz,
    DateTime vdate,
    int lastNo)
        {
            (short year, byte month, byte day) = biz.NOGenerationPeriodType switch
            {
                0 => ((short)-2, (byte)0, (byte)0),
                1 => ((short)vdate.Year, (byte)vdate.Month, (byte)0),
                2 => ((short)vdate.Year, (byte)vdate.Month, (byte)vdate.Day),
                3 => ((short)-1, (byte)0, (byte)0),
                _ => throw new InvalidOperationException(
                    $"Unknown NOGenerationPeriodType [{biz.NOGenerationPeriodType}].")
            };

            return new
            {
                BIZTransactionKeysId = keysId,
                BIZTransactionTypeId = bizTransactionTypeId,
                Year = year,
                Month = month,
                Day = day,
                Prefix = biz.Prefix ?? "",
                Suffix = biz.Suffix ?? "",
                SystemPrefix = biz.SystemPrefix ?? "",
                LastNo = lastNo
                // NOTE: SystemSuffix is NOT stored in MBIZTRANSACTIONKEYS table.
                // It is read from MBIZTRANSACTIONTYPE and used only in FormatVno().
            };
        }

        // ════════════════════════════════════════════════════════════════
        // LOAD BIZ TRANSACTION TYPE
        // GB4 parity: throws on not-found or duplicate — same messages.
        // ════════════════════════════════════════════════════════════════

        private async Task<BizTransactionTypeVnoDTO> LoadBizTransactionType(
            int bizTransactionTypeId,
            LoginDTO login,
            DbTransaction tx,
            CancellationToken ct)
        {
            var result = await _qe.QuerySingleAsync<BizTransactionTypeVnoDTO>(
                login,
                BizTransactionQB.GET_BIZTRANSACTIONTYPE_FOR_VNO,
                new { BIZTransactionTypeId = bizTransactionTypeId });

            if (result == null)
                throw new KeyNotFoundException(
                    $"BizTransactionType not found for Id [{bizTransactionTypeId}]. " +
                    "Contact Administrator.");

            return result;
        }

        // ════════════════════════════════════════════════════════════════
        // FORMAT DATE PART (SystemPrefix / SystemSuffix)
        // GB4 parity: applies Vdate.ToString(format, en-US culture).
        // Empty/null → returns "".
        // Invalid format → throws with same message as GB4.
        // ════════════════════════════════════════════════════════════════

        private static string FormatDatePart(DateTime vdate, string? format, string fieldName)
        {
            if (string.IsNullOrEmpty(format))
                return "";

            try
            {
                return vdate.ToString(format, CultureInfo.GetCultureInfo("en-US"));
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException(
                    $"Please check {fieldName} — format is not correct. " +
                    $"Check BizTransactionType entry. Format: [{format}].",
                    ex);
            }
        }
    }
}