using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using MMDAL.DTO.Routing;
using MMDAL.Query.Routing;
using Newtonsoft.Json;
using System.Data.Common;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.Routing
{
    public class RoutingDAL : IRoutingDAL
    {
        private readonly IQueryExecutor _qe;
        private readonly IValidation    _validation;

        public RoutingDAL(IQueryExecutor qe, IValidation validation)
        {
            _qe         = qe;
            _validation = validation;
        }

        // ── Picklist ─────────────────────────────────────────────────────────

        public async Task<string> GetSelectListRouting(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var result = await _qe.QueryAsync<RoutingPicklistDTO>(loginDTO, RoutingQB.GET_SELECTLIST_ROUTING, null!).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Version Picklist ────────────────────────────────────────────────────

        public async Task<string> GetSelectListRoutingVersion(int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType switch
                {
                    DBType.SQL => RoutingQB.GET_SELECTLIST_ROUTINGVERSION_SQL,
                    DBType.PostGre => RoutingQB.GET_SELECTLIST_ROUTINGVERSION_PG,
                    _ => throw new Exception("Unsupported database type")
                };

                var parameters = new { FirstNumber = firstNumber, MaxResult = maxResult };
                var result = await _qe.QueryAsync<RoutingVersionPicklistDTO>(login, sql, parameters, cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Get ───────────────────────────────────────────────────────────────

        public async Task<RoutingSaveDTO?> GetRoutingHeader(int routingId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.GET_ROUTING_HEADER_SQL,
                DBType.PostGre => RoutingQB.GET_ROUTING_HEADER_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param  = new { RoutingId = routingId, TenantId = login.ClientId };
            var result = await _qe.QueryAsync<RoutingSaveDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        public async Task<IEnumerable<RoutingVersionSaveDTO>> GetRoutingVersions(int routingId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.GET_ROUTING_VERSIONS_SQL,
                DBType.PostGre => RoutingQB.GET_ROUTING_VERSIONS_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new { RoutingId = routingId, TenantId = login.ClientId };
            return await _qe.QueryAsync<RoutingVersionSaveDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<IEnumerable<RoutingVersionDetailSaveDTO>> GetRoutingVersionDetails(int routingVersionId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.GET_ROUTING_VERSION_DETAILS_SQL,
                DBType.PostGre => RoutingQB.GET_ROUTING_VERSION_DETAILS_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new { RoutingVersionId = routingVersionId, TenantId = login.ClientId };
            return await _qe.QueryAsync<RoutingVersionDetailSaveDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<(IEnumerable<RoutingListItemDTO> Items, int TotalCount)> GetRoutingList(int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            var (sql, countSql) = login.DatabaseType switch
            {
                DBType.SQL     => (RoutingQB.GET_ROUTING_LIST_SQL,    RoutingQB.GET_ROUTING_LIST_COUNT_SQL),
                DBType.PostGre => (RoutingQB.GET_ROUTING_LIST_PG,     RoutingQB.GET_ROUTING_LIST_COUNT_PG),
                _              => throw new Exception("Unsupported database type")
            };
            var param = new { TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult };
            var items      = await _qe.QueryAsync<RoutingListItemDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
            var totalCount = await _qe.ExecuteScalarAsync<int>(login, countSql, param, cancellationToken: ct).ConfigureAwait(false);
            return (items, totalCount);
        }

        // ── Save ──────────────────────────────────────────────────────────────

        public async Task SaveRoutingHeader(RoutingSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.SAVE_ROUTING_SQL,
                DBType.PostGre => RoutingQB.SAVE_ROUTING_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new
            {
                dto.RoutingId, dto.RoutingCode, dto.RoutingName, dto.ItemId,
                dto.ProcessId, dto.RoutingType, dto.Remarks, dto.IsBORPost, dto.Status, dto.TenantId,
                UserId = login.UserId
            };
            await _qe.ExecuteAsync(login, sql, param, tx, ct).ConfigureAwait(false);
        }

        public async Task UpdateRoutingHeader(RoutingSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.UPDATE_ROUTING_SQL,
                DBType.PostGre => RoutingQB.UPDATE_ROUTING_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new
            {
                dto.RoutingId, dto.RoutingCode, dto.RoutingName, dto.ItemId,
                dto.ProcessId, dto.RoutingType, dto.Remarks, dto.IsBORPost, dto.Status, dto.TenantId,
                UserId = login.UserId
            };
            await _qe.ExecuteAsync(login, sql, param, tx, ct).ConfigureAwait(false);
        }

        public async Task SaveRoutingVersion(RoutingVersionSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.SAVE_ROUTING_VERSION_SQL,
                DBType.PostGre => RoutingQB.SAVE_ROUTING_VERSION_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new
            {
                dto.RoutingVersionId, dto.RoutingId, dto.Version, dto.ActiveFromDate, dto.ActiveToDate,
                dto.Remarks, dto.WorkOrderType, dto.Quantity, dto.ItemId, dto.SkuId,
                dto.IsSKUPosting, dto.IsStageOrProcess, dto.IsLastProcessWIP, dto.TenantId,
                UserId = login.UserId
            };
            await _qe.ExecuteAsync(login, sql, param, tx, ct).ConfigureAwait(false);
        }

        public async Task BulkSaveRoutingVersionDetails(IEnumerable<RoutingVersionDetailSaveDTO> details, LoginDTO login, DbTransaction tx)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.SAVE_ROUTING_VERSION_DETAIL_SQL,
                DBType.PostGre => RoutingQB.SAVE_ROUTING_VERSION_DETAIL_PG,
                _              => throw new Exception("Unsupported database type")
            };
            await _qe.BulkInsertAsync(login, sql, details, tx).ConfigureAwait(false);
        }

        // ── Delete ────────────────────────────────────────────────────────────

        public async Task DeleteRoutingVersionDetails(int routingVersionId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.DELETE_ROUTING_VERSION_DETAILS_SQL,
                DBType.PostGre => RoutingQB.DELETE_ROUTING_VERSION_DETAILS_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new { RoutingVersionId = routingVersionId, TenantId = login.ClientId };
            await _qe.ExecuteAsync(login, sql, param, tx, ct).ConfigureAwait(false);
        }

        public async Task SoftDeleteRouting(int routingId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => RoutingQB.SOFT_DELETE_ROUTING_SQL,
                DBType.PostGre => RoutingQB.SOFT_DELETE_ROUTING_PG,
                _              => throw new Exception("Unsupported database type")
            };
            var param = new { RoutingId = routingId, TenantId = login.ClientId, UserId = login.UserId };
            await _qe.ExecuteAsync(login, sql, param, tx, ct).ConfigureAwait(false);
        }
    }
}