using System.Data.Common;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Options;
using static GB5Shared.GB5Constant.Constant;

namespace GB5Shared.GenerateAutoNumber
{
    public class AutoNumber
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly int _dbType;

        public AutoNumber(IQueryExecutor QueryExecutor, IOptionsSnapshot<Gb5SystemDTO> dataBaseConfig)
        {
            _QueryExecutor = QueryExecutor;
            _dbType = dataBaseConfig.Value.DataBaseType;
        }

        public async Task<AutoNumberDTO> GetNumberAsync(int noOfId, string EntityCode, LoginDTO LoginDTO, DbTransaction? transaction = null)
        {
            if (noOfId <= 0)
                throw new MethodNotAllowedException($"Invalid input: Number of IDs ({noOfId}) cannot be negative or zero for entity {EntityCode}.");

            try
            {
                // SQL Server: OUTPUT INSERTED returns value AFTER increment.
                // PostgreSQL: RETURNING returns value AFTER increment — identical semantics, no calculation change.


                //Done by Vikash on 16 May 2026 Since Trigger is assosiated with Autonumber
                string sql = _dbType == DBTYPE.POSTGRESQL
                    //? @"UPDATE mautonumber
                    //       SET autoid = autoid + @NoOfId
                    //     WHERE entitycode = @EntityCode
                    //     RETURNING autoid"
                    //: @"UPDATE MAUTONUMBER
                    //       SET AutoId = AutoId + @NoOfId
                    //    OUTPUT INSERTED.AutoId
                    //     WHERE EntityCode = @EntityCode";

                     ? @"UPDATE mautonumber
                       SET autoid = autoid + @NoOfId
                     WHERE entitycode = @EntityCode
                     RETURNING autoid"
                : @"
                    DECLARE @OutputTable TABLE (AutoId BIGINT);

                    UPDATE MAUTONUMBER
                    SET AutoId = AutoId + @NoOfId
                    OUTPUT INSERTED.AutoId INTO @OutputTable
                    WHERE EntityCode = @EntityCode;

                    SELECT AutoId FROM @OutputTable;
                  ";




                var results = (await _QueryExecutor.QueryAsync<long>(
                    LoginDTO, sql, new { NoOfId = noOfId, EntityCode = EntityCode }, transaction)).ToList();

                if (results.Count == 0)
                    throw new NotFoundException($"AutoNumber entry not found for entity: {EntityCode}");

                long newAutoId  = results[0];                           // value AFTER increment
                int firstNumber = (int)(newAutoId - noOfId + LoginDTO.ServerConfigOffset);
                int lastNumber  = (int)(newAutoId - 1 + LoginDTO.ServerConfigOffset);

                return new AutoNumberDTO { StartNumber = firstNumber, EndNumber = lastNumber };
            }
            catch (MethodNotAllowedException) { throw; }
            catch (NotFoundException)         { throw; }
            catch (Exception ex)
            {
                throw new Exception($"An error occurred while generating auto numbers for {EntityCode}", ex);
            }
        }

        public async Task<AutoNumberDTO> GetAutoNumber(int noOfIds, string entityCode, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            if (noOfIds <= 0)
                throw new ArgumentException("NoOfIds must be greater than zero.", nameof(noOfIds));

            // SQL Server OUTPUT DELETED returns the value BEFORE the update (previousAutoId).
            // PostgreSQL RETURNING returns the value AFTER the update (newAutoId).
            // Normalise to previousAutoId so the firstNumber/lastNumber calculation is identical.
            bool isPg = _dbType == DBTYPE.POSTGRESQL;

            // Trigger-safe: MAUTONUMBER has a trigger, so SQL Server requires
            // OUTPUT ... INTO @table (bare OUTPUT is rejected with error 334).
            //
            // SQL Server sentinel: OUTPUT DELETED.AUTOID returns the PRE-update value, which is a
            // legitimate 0 the very first time an entity is ever allocated from a freshly-seeded
            // AUTOID=0 row — indistinguishable from "no row matched WHERE ENTITYCODE=..." via the
            // scalar result alone. Use @@ROWCOUNT to tell the two apart instead of trusting the value.
            string sql = isPg
                ? @"UPDATE mautonumber
                       SET autoid = autoid + @AutoNumNoOfIds
                     WHERE entitycode = @AutoNumEntityCode
                     RETURNING autoid"
                : @"
                    DECLARE @OutputTable TABLE (AutoId BIGINT);

                    UPDATE MAUTONUMBER
                    SET    AUTOID = AUTOID + @AutoNumNoOfIds
                    OUTPUT DELETED.AUTOID INTO @OutputTable
                    WHERE  ENTITYCODE = @AutoNumEntityCode;

                    IF @@ROWCOUNT = 0
                        SELECT CAST(-1 AS BIGINT) AS AutoId;
                    ELSE
                        SELECT AutoId FROM @OutputTable;
                  ";

            long resultId = await _QueryExecutor.ExecuteScalarAsync<long>(
                loginDTO,
                sql,
                new { AutoNumNoOfIds = noOfIds, AutoNumEntityCode = entityCode },
                transaction);

            // PostgreSQL: RETURNING with no matching row yields no rows, and ExecuteScalarAsync<long>
            // collapses that to 0 — but a legitimate result is always >= 1 there (previousAutoId >= 0
            // plus noOfIds > 0), so 0 unambiguously means "no row matched" on that path too.
            bool notFound = isPg ? resultId == 0 : resultId == -1;
            if (notFound)
                throw new Exception($"AutoNumber entry not found for entity: {entityCode}");

            // isPg  → resultId is newAutoId  → previousAutoId = resultId - noOfIds
            // !isPg → resultId is previousAutoId already
            long previousAutoId = isPg ? resultId - noOfIds : resultId;

            int firstNumber = (int)(previousAutoId + 1 + loginDTO.ServerConfigOffset);
            int lastNumber  = (int)(previousAutoId + noOfIds + loginDTO.ServerConfigOffset);

            return new AutoNumberDTO { StartNumber = firstNumber, EndNumber = lastNumber };
        }

        public async Task RollbackAutoNumber(string entityCode, int allocatedNumber, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            int rawAllocatedValue = allocatedNumber - loginDTO.ServerConfigOffset;

            // Plain UPDATE — no OUTPUT/RETURNING needed; compatible with both SQL Server and PostgreSQL.
            const string sql = @"
        UPDATE MAUTONUMBER
        SET    AUTOID = @AutoNumRollbackTarget
        WHERE  ENTITYCODE = @AutoNumRollbackEntityCode
          AND  AUTOID = @AutoNumRollbackGuard";

            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                sql,
                new
                {
                    AutoNumRollbackTarget     = rawAllocatedValue - 1,
                    AutoNumRollbackEntityCode = entityCode,
                    AutoNumRollbackGuard      = rawAllocatedValue
                },
                transaction);
        }
    }
}