using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Telemetry;
using Microsoft.Extensions.Logging;
using MMDAL.CustomCode.Production;
using MMDAL.DTO.Production;
using MMDAL.Query.Production;
using Newtonsoft.Json;
using static GB5Shared.GB5Constant.Constant;

namespace MMBLL.Production
{
    public class ProductionReportBLL : IProductionReportBLL
    {
        private readonly IQueryExecutor          _queryExecutor;
        private readonly ILogger<ProductionReportBLL> _logger;

        public ProductionReportBLL(
            IQueryExecutor queryExecutor,
            ILogger<ProductionReportBLL> logger)
        {
            _queryExecutor = queryExecutor;
            _logger        = logger;
        }

        // ── Production Status Report ──────────────────────────────────────────

        public async Task<string> GetProductionStatusReport(
            int ouId, DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-production-status-report", new { ouId, fromDate, toDate });

                string sql = login.DatabaseType switch
                {
                    DBType.SQL     => ProductionReportsQB.GET_PRODUCTION_STATUS_REPORT_SQL,
                    DBType.PostGre => ProductionReportsQB.GET_PRODUCTION_STATUS_REPORT_PG,
                    _              => throw new NotSupportedException("Unsupported database type")
                };

                var result = await _queryExecutor
                    .QueryAsync<ProductionStatusReportItemDTO>(login, sql,
                        new { OUId = ouId, FromDate = fromDate, ToDate = toDate, TenantId = login.ClientId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-production-status-report-failed", ex);
                _logger.LogError(ex, "GetProductionStatusReport failed for OUId {OUId}", ouId);
                throw;
            }
        }

        // ── Stoppage Register ─────────────────────────────────────────────────

        public async Task<string> GetStoppageRegister(
            int ouId, DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-stoppage-register", new { ouId, fromDate, toDate });

                string sql = login.DatabaseType switch
                {
                    DBType.SQL     => ProductionReportsQB.GET_STOPPAGE_REGISTER_SQL,
                    DBType.PostGre => ProductionReportsQB.GET_STOPPAGE_REGISTER_PG,
                    _              => throw new NotSupportedException("Unsupported database type")
                };

                var result = await _queryExecutor
                    .QueryAsync<ProductionStoppageRegisterDTO>(login, sql,
                        new { OUId = ouId, FromDate = fromDate, ToDate = toDate, TenantId = login.ClientId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-stoppage-register-failed", ex);
                _logger.LogError(ex, "GetStoppageRegister failed for OUId {OUId}", ouId);
                throw;
            }
        }

        // ── Work Order Register ───────────────────────────────────────────────

        public async Task<string> GetWORegister(
            int ouId, DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-wo-register", new { ouId, fromDate, toDate });

                var result = await GetWorkOrderRegister(ouId, fromDate, toDate, Array.Empty<int>(), login, ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-wo-register-failed", ex);
                _logger.LogError(ex, "GetWORegister failed for OUId {OUId}", ouId);
                throw;
            }
        }

        // GB4 Production.svc/WorkOrderRegister compatibility.  The report shell posts the
        // selected BIZTransactionClassId values in CriteriaDTO; applying them here is important
        // because an OU can contain several work-order document classes.
        public async Task<IReadOnlyList<WORegisterDTO>> GetWorkOrderRegister(
            int ouId, DateTime fromDate, DateTime toDate, IReadOnlyCollection<int> bizTransactionClassIds,
            LoginDTO login, CancellationToken ct)
        {
            if (fromDate.Date > toDate.Date)
                throw new ArgumentException("PeriodFromDate cannot be later than PeriodToDate.");

            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionReportsQB.GET_WO_REGISTER_SQL,
                DBType.PostGre => ProductionReportsQB.GET_WO_REGISTER_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var classFilter = bizTransactionClassIds.Count == 0
                ? string.Empty
                : " AND bt.BIZTRANSACTIONCLASSID IN @BizTransactionClassIds";

            var result = await _queryExecutor.QueryAsync<WORegisterDTO>(login,
                    sql.Replace("{BizTransactionClassFilter}", classFilter),
                    new
                    {
                        OUId = ouId,
                        FromDate = fromDate.Date,
                        ToDate = toDate.Date.AddDays(1).AddTicks(-1),
                        BizTransactionClassIds = bizTransactionClassIds,
                        TenantId = login.ClientId
                    }, cancellationToken: ct)
                .ConfigureAwait(false);

            return result.ToList();
        }

        // ── Resource Utilization (StreamAsync — can exceed 5k rows) ──────────

        public async Task<string> GetResourceUtilization(
            int ouId, DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-resource-utilization", new { ouId, fromDate, toDate });

                string sql = login.DatabaseType switch
                {
                    DBType.SQL     => ProductionReportsQB.GET_RESOURCE_UTILIZATION_SQL,
                    DBType.PostGre => ProductionReportsQB.GET_RESOURCE_UTILIZATION_PG,
                    _              => throw new NotSupportedException("Unsupported database type")
                };

                var param  = new { OUId = ouId, FromDate = fromDate, ToDate = toDate, TenantId = login.ClientId };
                var result = new List<ResourceUtilizationDTO>();

                await foreach (var row in _queryExecutor.StreamAsync<ResourceUtilizationDTO>(login, sql, param)
                                                        .ConfigureAwait(false))
                {
                    result.Add(row);
                }

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-resource-utilization-failed", ex);
                _logger.LogError(ex, "GetResourceUtilization failed for OUId {OUId}", ouId);
                throw;
            }
        }

        // ── Production Summary ────────────────────────────────────────────────

        public async Task<string> GetProductionSummary(
            int ouId, DateTime fromDate, DateTime toDate, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-production-summary", new { ouId, fromDate, toDate });

                string sql = login.DatabaseType switch
                {
                    DBType.SQL     => ProductionReportsQB.GET_PRODUCTION_SUMMARY_SQL,
                    DBType.PostGre => ProductionReportsQB.GET_PRODUCTION_SUMMARY_PG,
                    _              => throw new NotSupportedException("Unsupported database type")
                };

                var result = await _queryExecutor
                    .QueryAsync<ProductionSummaryDTO>(login, sql,
                        new { OUId = ouId, FromDate = fromDate, ToDate = toDate, TenantId = login.ClientId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-production-summary-failed", ex);
                _logger.LogError(ex, "GetProductionSummary failed for OUId {OUId}", ouId);
                throw;
            }
        }

        // ── WIP Status ────────────────────────────────────────────────────────

        public async Task<string> GetWIPStatus(
            int ouId, DateTime asOnDate, LoginDTO login, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-wip-status", new { ouId, asOnDate });

                string sql = login.DatabaseType switch
                {
                    DBType.SQL     => ProductionReportsQB.GET_WIP_STATUS_SQL,
                    DBType.PostGre => ProductionReportsQB.GET_WIP_STATUS_PG,
                    _              => throw new NotSupportedException("Unsupported database type")
                };

                var result = await _queryExecutor
                    .QueryAsync<WIPStatusDTO>(login, sql,
                        new { OUId = ouId, AsOnDate = asOnDate, TenantId = login.ClientId },
                        cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-wip-status-failed", ex);
                _logger.LogError(ex, "GetWIPStatus failed for OUId {OUId}", ouId);
                throw;
            }
        }

        // ── Production Summary Register (GB4 Production.svc ProductionSummaryRegister) ──────────

        public async Task<(string Json, int Total)> ProductionSummaryRegister(
            int type, int firstNumber, int maxResult, ProductionSummaryRegisterCriteria criteria, LoginDTO login, CancellationToken ct)
        {
            criteria ??= new ProductionSummaryRegisterCriteria();
            try
            {
                GB5Trace.Step("production-summary-register", new { type, firstNumber, maxResult });

                switch (type)
                {
                    case 0: return await GetSummary(firstNumber, maxResult, criteria, login, ct).ConfigureAwait(false);
                    case 1: return await GetRegister(firstNumber, maxResult, criteria, login, ct).ConfigureAwait(false);
                    case 2: return await GetMachineWiseSummary(firstNumber, maxResult, criteria, login, ct).ConfigureAwait(false);
                    case 3: return await GetScaleReadingRegister(criteria, login, ct).ConfigureAwait(false);
                    default: throw new NotSupportedException($"Unsupported ProductionSummaryRegister Type: {type}");
                }
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("production-summary-register-failed", ex);
                _logger.LogError(ex, "ProductionSummaryRegister failed for Type {Type}", type);
                throw;
            }
        }

        private async Task<(string Json, int Total)> GetSummary(
            int firstNumber, int maxResult, ProductionSummaryRegisterCriteria criteria, LoginDTO login, CancellationToken ct)
        {
            var (filter, parameters) = ProductionSummaryRegisterFilterBuilder.BuildSummaryCriteria(criteria);
            filter += ProductionSummaryRegisterFilterBuilder.BuildSkuTypeFilter(criteria);

            string sqlTemplate = login.DatabaseType switch
            {
                DBType.SQL     => ProductionSummaryRegisterQB.PRODUCTION_SUMMARY_REPORT_SQL,
                DBType.PostGre => ProductionSummaryRegisterQB.PRODUCTION_SUMMARY_REPORT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            string sql = sqlTemplate.Replace("{DynamicFilter}", filter);

            var rows = (await _queryExecutor.QueryAsync<ProductionSummaryRegisterDTO>(login, sql, parameters, cancellationToken: ct).ConfigureAwait(false)).ToList();

            string totalsSqlTemplate = login.DatabaseType switch
            {
                DBType.SQL     => ProductionSummaryRegisterQB.PRODUCTION_SUMMARY_REPORT_TOTAL_SQL,
                DBType.PostGre => ProductionSummaryRegisterQB.PRODUCTION_SUMMARY_REPORT_TOTAL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            string totalsSql = totalsSqlTemplate.Replace("{DynamicFilter}", filter);

            var totals = await _queryExecutor.QuerySingleAsync<ProductionSummaryRegisterDTO>(login, totalsSql, parameters, cancellationToken: ct).ConfigureAwait(false);
            ApplyTotals(rows, totals);
            ApplyDurationFormatting(rows, includeDetailTimes: false);

            // GB4 applies FirstNumber/MaxResult paging in-memory for Type=0 (single grouped query, no
            // separate count/data SQL) — replicate that instead of a DB-side OFFSET/FETCH for this branch.
            var paged = PageInMemory(rows, firstNumber, maxResult);
            return (JsonConvert.SerializeObject(paged), rows.Count);
        }

        private async Task<(string Json, int Total)> GetRegister(
            int firstNumber, int maxResult, ProductionSummaryRegisterCriteria criteria, LoginDTO login, CancellationToken ct)
        {
            var (filter, parameters) = criteria.ReportType switch
            {
                "0" => ProductionSummaryRegisterFilterBuilder.BuildSummaryCriteria(criteria),
                "2" => ProductionSummaryRegisterFilterBuilder.BuildSummaryCriteria(criteria),
                _   => ProductionSummaryRegisterFilterBuilder.BuildResourceCriteria(criteria)
            };
            if (criteria.ReportType == "2") filter += " and sr.RowFilter=1";
            filter += ProductionSummaryRegisterFilterBuilder.BuildSkuTypeFilter(criteria);

            string resourceReqFilter = criteria.ResourceReq switch
            {
                "0" => " and rr.PRODUCTIONDETAILID is not null",
                "1" => " and rr.PRODUCTIONDETAILID is null",
                _   => string.Empty
            };

            string orderBy = criteria.OrderBy == 1
                ? "MA.ALLOCATIONNAME, PRODUCTIONDATE, I.WORKCENTERCODE, f.PROCESSCODE"
                : "h.INDENTDATE, h.INDENTNUMBER, a.PRODUCTIONDATE, a.PRODUCTIONNUMBER, I.WORKCENTERCODE, f.PROCESSCODE";

            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            string sql = ProductionSummaryRegisterQB.PRODUCTION_REGISTER_REPORT_SQL
                .Replace("{ResourceReqFilter}", resourceReqFilter)
                .Replace("{DynamicFilter}", filter)
                .Replace("{SkuTypeFilter}", string.Empty)
                .Replace("{OrderBy}", orderBy);

            var paged = await _queryExecutor.QueryPagedAsync<ProductionSummaryRegisterDTO>(login, sql, parameters).ConfigureAwait(false);
            var rows = (paged.Items ?? Enumerable.Empty<ProductionSummaryRegisterDTO>()).ToList();

            string totalsSql = ProductionSummaryRegisterQB.PRODUCTION_REGISTER_REPORT_TOTAL_SQL
                .Replace("{ResourceReqFilter}", resourceReqFilter)
                .Replace("{DynamicFilter}", filter)
                .Replace("{SkuTypeFilter}", string.Empty);

            var totals = await _queryExecutor.QuerySingleAsync<ProductionSummaryRegisterDTO>(login, totalsSql, parameters, cancellationToken: ct).ConfigureAwait(false);
            ApplyTotals(rows, totals);
            ApplyDurationFormatting(rows, includeDetailTimes: true);

            return (JsonConvert.SerializeObject(rows), paged.TotalCount);
        }

        private async Task<(string Json, int Total)> GetMachineWiseSummary(
            int firstNumber, int maxResult, ProductionSummaryRegisterCriteria criteria, LoginDTO login, CancellationToken ct)
        {
            var (filter, parameters) = ProductionSummaryRegisterFilterBuilder.BuildSummaryCriteria(criteria);
            filter += ProductionSummaryRegisterFilterBuilder.BuildSkuTypeFilter(criteria);

            string sql = ProductionSummaryRegisterQB.MACHINE_WISE_PRODUCTION_SUMMARY_REPORT_SQL.Replace("{DynamicFilter}", filter).Replace("{SkuTypeFilter}", string.Empty);
            var rows = (await _queryExecutor.QueryAsync<ProductionSummaryRegisterDTO>(login, sql, parameters, cancellationToken: ct).ConfigureAwait(false)).ToList();

            string totalsSql = ProductionSummaryRegisterQB.MACHINE_WISE_PRODUCTION_SUMMARY_REPORT_TOTAL_SQL.Replace("{DynamicFilter}", filter).Replace("{SkuTypeFilter}", string.Empty);
            var totals = await _queryExecutor.QuerySingleAsync<ProductionSummaryRegisterDTO>(login, totalsSql, parameters, cancellationToken: ct).ConfigureAwait(false);
            ApplyTotals(rows, totals);

            var paged = PageInMemory(rows, firstNumber, maxResult);
            return (JsonConvert.SerializeObject(paged), rows.Count);
        }

        private async Task<(string Json, int Total)> GetScaleReadingRegister(
            ProductionSummaryRegisterCriteria criteria, LoginDTO login, CancellationToken ct)
        {
            var (filter1, parameters1) = ProductionSummaryRegisterFilterBuilder.BuildScaleReadingCriteria(criteria);
            string sql1 = ProductionSummaryRegisterQB.PRODUCTION_REGISTER_SCALE_READING_SQL.Replace("{DynamicFilter}", filter1);

            var (filter2, parameters2) = ProductionSummaryRegisterFilterBuilder.BuildScaleReadingStockTransferCriteria(criteria);
            string sql2 = ProductionSummaryRegisterQB.PRODUCTION_REGISTER_SCALE_READING_STOCK_TRANSFER_SQL.Replace("{DynamicFilter}", filter2);

            var rows1 = await _queryExecutor.QueryAsync<ProductionSummaryRegisterDTO>(login, sql1, parameters1, cancellationToken: ct).ConfigureAwait(false);
            var rows2 = await _queryExecutor.QueryAsync<ProductionSummaryRegisterDTO>(login, sql2, parameters2, cancellationToken: ct).ConfigureAwait(false);

            var rows = rows1.Concat(rows2).ToList();
            return (JsonConvert.SerializeObject(rows), rows.Count);
        }

        private static (List<ProductionSummaryRegisterDTO> Items, int Total) PageInMemory(
            List<ProductionSummaryRegisterDTO> rows, int firstNumber, int maxResult)
        {
            int skip = Math.Max(0, firstNumber - 1);
            var page = maxResult > 0 ? rows.Skip(skip).Take(maxResult).ToList() : rows;
            return (page, rows.Count);
        }

        private static void ApplyTotals(List<ProductionSummaryRegisterDTO> rows, ProductionSummaryRegisterDTO totals)
        {
            if (totals == null) return;
            foreach (var row in rows)
            {
                row.TotalProcessCost = totals.TotalProcessCost;
                row.TotalProcessValue = totals.TotalProcessValue;
                row.TotalMaterialCost = totals.TotalMaterialCost;
                row.TotalMaterialValue = totals.TotalMaterialValue;
                row.TotalActualResourceCost = totals.TotalActualResourceCost;
            }
        }

        private static void ApplyDurationFormatting(List<ProductionSummaryRegisterDTO> rows, bool includeDetailTimes)
        {
            foreach (var row in rows)
            {
                row.CallResourceDuration = MinutesIntoTime(row.ResourceDuration);
                row.CallResourceStartTime = MinutesIntoTime(row.ResourceStartTime);
                row.CallResourceEndTime = MinutesIntoTime(row.ResourceEndTime);
                row.CallStoppageDuration = MinutesIntoTime(row.StoppageDuration);
                row.CallExcludeStoppage = MinutesIntoTime(row.ExcludeStoppageResource);
                row.CallPlannedDuration = MinutesIntoTime(row.PlannedDuration);
                row.CallEstimatedDuration = MinutesIntoTime(row.EstimatedDuration);
                if (includeDetailTimes)
                {
                    row.CallDetailStartTime = MinutesIntoTime(row.DetailStartTime);
                    row.CallDetailEndTime = MinutesIntoTime(row.DetailEndTime);
                }
            }
        }

        // Matches GB4's CommonFunctionFrameDAL.MinutesIntoTime — total minutes since midnight -> "HH:mm".
        private static string MinutesIntoTime(int totalMinutes)
        {
            if (totalMinutes < 0) totalMinutes = 0;
            int hours = totalMinutes / 60;
            int minutes = totalMinutes % 60;
            return $"{hours:D2}:{minutes:D2}";
        }
    }
}