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

namespace MMDAL.CustomCode.Casting
{
    public class CastingDAL : ICastingDAL
    {
        private readonly IQueryExecutor _qe;

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

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

        public async Task<CastingSaveDTO?> GetCasting(
            int castingId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.GET_CASTING_SQL,
                DBType.PostGre => CastingQB.GET_CASTING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

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

        public async Task<IEnumerable<CastingDetailSaveDTO>> GetCastingDetails(
            int castingId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.GET_CASTING_DETAILS_SQL,
                DBType.PostGre => CastingQB.GET_CASTING_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

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

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

            string countSql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.GET_CASTING_LIST_COUNT_SQL,
                DBType.PostGre => CastingQB.GET_CASTING_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<CastingListItemDTO>(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 SaveCastingHeader(
            CastingSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.SAVE_CASTING_SQL,
                DBType.PostGre => CastingQB.SAVE_CASTING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.CastingId, dto.BizTransactionTypeId, dto.OUId, dto.PeriodId,
                    dto.CastingNumber, dto.CastingDate, dto.StoreId, dto.Remarks,
                    dto.Status, dto.Version, dto.CreatedById, dto.ModifiedById,
                    TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

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

        public async Task UpdateCastingHeader(
            CastingSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.UPDATE_CASTING_SQL,
                DBType.PostGre => CastingQB.UPDATE_CASTING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.BizTransactionTypeId, dto.OUId, dto.PeriodId, dto.CastingDate,
                    dto.StoreId, dto.Remarks, dto.Status, dto.Version,
                    dto.ModifiedById, dto.CastingId, TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

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

        public async Task BulkSaveCastingDetails(
            IEnumerable<CastingDetailSaveDTO> rows, int tenantId,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.SAVE_CASTING_DETAIL_SQL,
                DBType.PostGre => CastingQB.SAVE_CASTING_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.CastingDetailId, d.CastingId, d.SlNo, d.PatternId, d.HeatId, d.HeatLaddleId,
                    d.BoxNumber, d.BoxStatus, d.MouldingRemarks, d.TotalPieces,
                    d.GoodPieces, d.RejectedPieces, TenantId = tenantId
                });
                await _qe.BulkInsertAsync(login, sql, records, tx).ConfigureAwait(false);
            }
            else
            {
                foreach (var d in rowList)
                {
                    await _qe.ExecuteAsync(login, sql,
                        new
                        {
                            d.CastingDetailId, d.CastingId, d.SlNo, d.PatternId, d.HeatId, d.HeatLaddleId,
                            d.BoxNumber, d.BoxStatus, d.MouldingRemarks, d.TotalPieces,
                            d.GoodPieces, d.RejectedPieces, TenantId = tenantId
                        }, tx, ct)
                        .ConfigureAwait(false);
                }
            }
        }

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

        public async Task DeleteCastingDetails(
            int castingId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.DELETE_CASTING_DETAILS_SQL,
                DBType.PostGre => CastingQB.DELETE_CASTING_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

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

        public async Task SoftDeleteCasting(
            int castingId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => CastingQB.SOFT_DELETE_CASTING_SQL,
                DBType.PostGre => CastingQB.SOFT_DELETE_CASTING_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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