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

namespace MMDAL.CustomCode.Heat
{
    public class HeatDAL : IHeatDAL
    {
        private readonly IQueryExecutor _qe;

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

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

        public async Task<HeatSaveDTO?> GetHeat(
            int heatId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.GET_HEAT_SQL,
                DBType.PostGre => HeatQB.GET_HEAT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var results = await _qe
                .QueryAsync<HeatSaveDTO>(login, sql,
                    new { HeatId = heatId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return results?.FirstOrDefault();
        }

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

        public async Task<IEnumerable<HeatDetailSaveDTO>> GetHeatDetails(
            int heatId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.GET_HEAT_DETAILS_SQL,
                DBType.PostGre => HeatQB.GET_HEAT_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

        // ── Get laddles ───────────────────────────────────────────────────────

        public async Task<IEnumerable<HeatLaddleSaveDTO>> GetHeatLaddles(
            int heatId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.GET_HEAT_LADDLES_SQL,
                DBType.PostGre => HeatQB.GET_HEAT_LADDLES_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

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

        public async Task<(IEnumerable<HeatListItemDTO> Items, int TotalCount)> GetHeatList(
            int ouId, DateTime? fromDate, DateTime? toDate,
            int firstNumber, int maxResult,
            LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.GET_HEAT_LIST_SQL,
                DBType.PostGre => HeatQB.GET_HEAT_LIST_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

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

            var items = await _qe
                .QueryAsync<HeatListItemDTO>(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 SaveHeatHeader(
            HeatSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.SAVE_HEAT_SQL,
                DBType.PostGre => HeatQB.SAVE_HEAT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.HeatId, dto.BizTransactionTypeId, dto.OUId, dto.PeriodId,
                    dto.HeatNumber, dto.HeatDate, dto.GradeId, dto.StoreId,
                    dto.StartTime, dto.EndTime, dto.MaterialWeight, dto.PourWeight,
                    dto.RemerentWeight, dto.MeltingLoss, dto.LossPercentage,
                    dto.UnitConsumed, dto.SpectrumNumber, dto.TappingTemperature,
                    dto.OperatorsId, dto.Remarks, dto.Status, dto.Version,
                    dto.CreatedById, dto.ModifiedById, TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Update header ─────────────────────────────────────────────────────

        public async Task UpdateHeatHeader(
            HeatSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.UPDATE_HEAT_SQL,
                DBType.PostGre => HeatQB.UPDATE_HEAT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.BizTransactionTypeId, dto.OUId, dto.PeriodId, dto.HeatDate,
                    dto.GradeId, dto.StoreId, dto.StartTime, dto.EndTime,
                    dto.MaterialWeight, dto.PourWeight, dto.RemerentWeight,
                    dto.MeltingLoss, dto.LossPercentage, dto.UnitConsumed,
                    dto.SpectrumNumber, dto.TappingTemperature, dto.OperatorsId,
                    dto.Remarks, dto.Status, dto.Version, dto.ModifiedById,
                    dto.HeatId, TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Bulk save details ─────────────────────────────────────────────────

        public async Task BulkSaveHeatDetails(
            IEnumerable<HeatDetailSaveDTO> rows, int tenantId,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.SAVE_HEAT_DETAIL_SQL,
                DBType.PostGre => HeatQB.SAVE_HEAT_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.HeatDetailId, d.HeatId, d.SlNo, d.ItemId, d.SkuId, d.Quantity,
                    TenantId = tenantId
                });
                await _qe.BulkInsertAsync(login, sql, records, tx).ConfigureAwait(false);
            }
            else
            {
                foreach (var d in rowList)
                {
                    await _qe.ExecuteAsync(login, sql,
                        new
                        {
                            d.HeatDetailId, d.HeatId, d.SlNo, d.ItemId, d.SkuId, d.Quantity,
                            TenantId = tenantId
                        }, tx, ct)
                        .ConfigureAwait(false);
                }
            }
        }

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

        public async Task DeleteHeatDetails(
            int heatId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.DELETE_HEAT_DETAILS_SQL,
                DBType.PostGre => HeatQB.DELETE_HEAT_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

        // ── Bulk save laddles ─────────────────────────────────────────────────

        public async Task BulkSaveHeatLaddles(
            IEnumerable<HeatLaddleSaveDTO> rows, int tenantId,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.SAVE_HEAT_LADDLE_SQL,
                DBType.PostGre => HeatQB.SAVE_HEAT_LADDLE_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

            if (rowList.Count >= 100)
            {
                var records = rowList.Select(l => new
                {
                    l.HeatLaddleId, l.HeatId, l.SlNo, l.LaddleNumber, l.Weight,
                    TenantId = tenantId
                });
                await _qe.BulkInsertAsync(login, sql, records, tx).ConfigureAwait(false);
            }
            else
            {
                foreach (var l in rowList)
                {
                    await _qe.ExecuteAsync(login, sql,
                        new
                        {
                            l.HeatLaddleId, l.HeatId, l.SlNo, l.LaddleNumber, l.Weight,
                            TenantId = tenantId
                        }, tx, ct)
                        .ConfigureAwait(false);
                }
            }
        }

        // ── Delete laddles ────────────────────────────────────────────────────

        public async Task DeleteHeatLaddles(
            int heatId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.DELETE_HEAT_LADDLES_SQL,
                DBType.PostGre => HeatQB.DELETE_HEAT_LADDLES_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

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

        public async Task SoftDeleteHeat(
            int heatId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => HeatQB.SOFT_DELETE_HEAT_SQL,
                DBType.PostGre => HeatQB.SOFT_DELETE_HEAT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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