using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.Indent;
using MMDAL.Query.Indent;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.Indent
{
    public class ProcessGroupTransactionDAL : IProcessGroupTransactionDAL
    {
        private readonly IQueryExecutor _qe;

        public ProcessGroupTransactionDAL(IQueryExecutor queryExecutor)
            => _qe = queryExecutor;

        // ── Get header ────────────────────────────────────────────────────────

        public async Task<ProcessGroupTransactionViewDTO?> GetCWO(
            int processGroupId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.GET_CWO_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.GET_CWO_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _qe
                .QuerySingleAsync<ProcessGroupTransactionViewDTO>(login, sql,
                    new { ProcessGroupId = processGroupId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Get details ───────────────────────────────────────────────────────

        public async Task<IEnumerable<ProcessGroupTransactionDetailDTO>> GetCWODetails(
            int processGroupId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.GET_CWO_DETAILS_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.GET_CWO_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _qe
                .QueryAsync<ProcessGroupTransactionDetailDTO>(login, sql,
                    new { ProcessGroupId = processGroupId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        // ── Get paged list ────────────────────────────────────────────────────

        public async Task<(IEnumerable<ProcessGroupTransactionListItemDTO> Items, int TotalCount)> GetCWOList(
            int ouId, int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.GET_CWO_LIST_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.GET_CWO_LIST_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            string countSql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.GET_CWO_LIST_COUNT_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.GET_CWO_LIST_COUNT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var param      = new { OUId = ouId, TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult };
            var countParam = new { OUId = ouId, TenantId = login.ClientId };

            var items = await _qe
                .QueryAsync<ProcessGroupTransactionListItemDTO>(login, sql, param, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];

            var total = await _qe
                .ExecuteScalarAsync<int>(login, countSql, countParam, cancellationToken: ct)
                .ConfigureAwait(false);

            return (items, total);
        }

        // ── Save header ───────────────────────────────────────────────────────

        public async Task SaveCWOHeader(
            ProcessGroupTransactionSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.SAVE_CWO_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.SAVE_CWO_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.ProcessGroupId,
                    dto.BizTransactionTypeId,
                    dto.OUId,
                    dto.PeriodId,
                    dto.ProcessGroupNumber,
                    dto.ProcessGroupDate,
                    dto.ReferenceNumber,
                    dto.ReferenceDate,
                    dto.ProcessId,
                    dto.GroupedById,
                    dto.GroupType,
                    dto.Remarks,
                    dto.TotalGroupInputQuantity,
                    dto.TotalGroupOutputQuantity,
                    dto.Status,
                    dto.Version,
                    dto.CreatedById,
                    dto.CreatedOn,
                    dto.ModifiedById,
                    dto.ModifiedOn,
                    dto.Priority,
                    TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task UpdateCWOHeader(
            ProcessGroupTransactionSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.UPDATE_CWO_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.UPDATE_CWO_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.BizTransactionTypeId, dto.PeriodId,
                    dto.ProcessGroupDate, dto.ReferenceNumber, dto.ReferenceDate,
                    dto.ProcessId, dto.GroupedById, dto.Priority,
                    dto.TotalGroupInputQuantity, dto.TotalGroupOutputQuantity,
                    dto.Remarks, dto.ProcessGroupId, TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Bulk save details — BulkInsert when ≥100 rows ────────────────────

        public async Task BulkSaveCWODetails(
            IEnumerable<ProcessGroupTransactionDetailDTO> rows, int tenantId,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.SAVE_CWO_DETAIL_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.SAVE_CWO_DETAIL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var rowList = rows.ToList();
            if (rowList.Count == 0) return;

            if (rowList.Count >= 100)
            {
                var records = rowList.Select(d => new
                {
                    d.ProcessGroupDetailId, d.ProcessGroupId, d.SlNo,
                    d.DocumentDetailId, d.IndentMaterialId, d.IndentDetailId,
                    d.SkuId, d.ProcessInputQuantity, d.ProcessOutputQuantity,
                    d.SequenceOrder, TenantId = tenantId
                });
                await _qe.BulkInsertAsync(login, sql, records, tx).ConfigureAwait(false);
            }
            else
            {
                foreach (var d in rowList)
                {
                    await _qe.ExecuteAsync(login, sql,
                        new
                        {
                            d.ProcessGroupDetailId, d.ProcessGroupId, d.SlNo,
                            d.DocumentDetailId, d.IndentMaterialId, d.IndentDetailId,
                            d.SkuId, d.ProcessInputQuantity, d.ProcessOutputQuantity,
                            d.SequenceOrder, TenantId = tenantId
                        }, tx, ct)
                        .ConfigureAwait(false);
                }
            }
        }

        // ── Delete details ────────────────────────────────────────────────────

        public async Task DeleteCWODetails(
            int processGroupId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.DELETE_CWO_DETAILS_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.DELETE_CWO_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };  

            await _qe.ExecuteAsync(login, sql,
                new { ProcessGroupId = processGroupId, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Soft delete ───────────────────────────────────────────────────────

        public async Task SoftDeleteCWO(
            int processGroupId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProcessGroupTransactionQB.SOFT_DELETE_CWO_SQL,
                DBType.PostGre => ProcessGroupTransactionQB.SOFT_DELETE_CWO_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new { ProcessGroupId = processGroupId, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }
    }
}