using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;
using PayRollDAL.DTO.ShiftPattern;
using PayRollDAL.Query.ShiftPattern;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.ShiftPattern
{
    public class ShiftPatternDAL : IShiftPatternDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public ShiftPatternDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // ── Get header + days rows + (per days row) detail rows ─────────────

        public async Task<ShiftPatternDTO> GetShiftPattern(int ShiftPatternId, LoginDTO LoginDTO)
        {
            var param = new { shiftpatternid = ShiftPatternId };

            var header = await _QueryExecutor.QuerySingleAsync<ShiftPatternDTO>(
                LoginDTO, ShiftPatternQB.GET_SHIFTPATTERN, param).ConfigureAwait(false);

            if (header != null)
            {
                var days = (await _QueryExecutor.QueryAsync<ShiftPatternDaysDTO>(
                    LoginDTO, ShiftPatternQB.GET_SHIFTPATTERNDAYS, param).ConfigureAwait(false)).ToList();

                foreach (var day in days)
                {
                    var details = await _QueryExecutor.QueryAsync<ShiftPatternDetailDTO>(
                        LoginDTO, ShiftPatternQB.GET_SHIFTPATTERNDETAIL,
                        new { shiftpatterndaysid = day.ShiftPatternDaysId }).ConfigureAwait(false);

                    day.ShiftPatternDetailArray = details?.ToList() ?? new List<ShiftPatternDetailDTO>();
                }

                header.ShiftPatternDaysArray = days;
            }

            return header!;
        }

        // ── Insert header + days rows + detail rows (transaction provided by BLL) ─

        public async Task<int> SaveShiftPattern(ShiftPatternDTO ShiftPatternDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.SAVE_SHIFTPATTERN, ShiftPatternDTO, DbTransaction).ConfigureAwait(false);

            if (rows > 0)
                await InsertDaysAndDetails(ShiftPatternDTO, LoginDTO, DbTransaction).ConfigureAwait(false);

            return rows;
        }

        // ── Update header + replace days/detail rows (transaction provided by BLL) ─

        public async Task<int> UpdateShiftPattern(ShiftPatternDTO ShiftPatternDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            int rows = await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.UPDATE_SHIFTPATTERN, ShiftPatternDTO, DbTransaction).ConfigureAwait(false);

            var param = new { shiftpatternid = ShiftPatternDTO.ShiftPatternId };

            // Detail rows first — they reference days rows, which reference the pattern.
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.DELETE_SHIFTPATTERNDETAIL_BY_PATTERN, param, DbTransaction).ConfigureAwait(false);

            await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.DELETE_SHIFTPATTERNDAYS, param, DbTransaction).ConfigureAwait(false);

            await InsertDaysAndDetails(ShiftPatternDTO, LoginDTO, DbTransaction).ConfigureAwait(false);

            return rows;
        }

        // ── Shared days/detail insert loop used by both Save and Update ─────

        private async Task InsertDaysAndDetails(ShiftPatternDTO ShiftPatternDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            if (ShiftPatternDTO.ShiftPatternDaysArray == null || ShiftPatternDTO.ShiftPatternDaysArray.Count == 0)
                return;

            short daysSlNo = 1;
            foreach (var day in ShiftPatternDTO.ShiftPatternDaysArray)
            {
                day.ShiftPatternId = ShiftPatternDTO.ShiftPatternId;
                day.ShiftPatternDaysSlNo = daysSlNo++;

                await _QueryExecutor.ExecuteAsync(
                    LoginDTO, ShiftPatternQB.SAVE_SHIFTPATTERNDAYS, day, DbTransaction).ConfigureAwait(false);

                if (day.ShiftPatternDetailArray == null || day.ShiftPatternDetailArray.Count == 0)
                    continue;

                short detailSlNo = 1;
                foreach (var detail in day.ShiftPatternDetailArray)
                {
                    detail.ShiftPatternDaysId = day.ShiftPatternDaysId;
                    detail.ShiftPatternDetailSlNo = detailSlNo++;

                    await _QueryExecutor.ExecuteAsync(
                        LoginDTO, ShiftPatternQB.SAVE_SHIFTPATTERNDETAIL, detail, DbTransaction).ConfigureAwait(false);
                }
            }
        }

        // ── Delete header (days/detail removed explicitly first) ────────────

        public async Task<string> DeleteShiftPattern(int ShiftPatternId, LoginDTO LoginDTO)
        {
            var param = new { shiftpatternid = ShiftPatternId };

            await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.DELETE_SHIFTPATTERNDETAIL_BY_PATTERN, param).ConfigureAwait(false);

            await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.DELETE_SHIFTPATTERNDAYS, param).ConfigureAwait(false);

            int result = await _QueryExecutor.ExecuteAsync(
                LoginDTO, ShiftPatternQB.DELETE_SHIFTPATTERN, param).ConfigureAwait(false);

            return result > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
        }

        // ── Full-DTO header list (no days/detail hierarchy) ──────────────────

        public async Task<string> GetShiftPatternList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            var parameters = new DynamicParameters();
            string filter = BuildFilter(CriteriaDTO, parameters);

            string sql = ShiftPatternQB.GET_SHIFTPATTERN_LIST + filter + "\r\n ORDER BY A.SORTORDER";

            var list = (await _QueryExecutor.QueryAsync<ShiftPatternDTO>(LoginDTO, sql, parameters)
                                            .ConfigureAwait(false)).ToList();
            return JsonConvert.SerializeObject(list);
        }

        // ── All detail rows for a pattern (through its days rows) ───────────

        public async Task<List<ShiftPatternDetailDTO>> LoadShiftPatternDetails(int ShiftPatternId, LoginDTO LoginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<ShiftPatternDetailDTO>(
                LoginDTO, ShiftPatternQB.LOAD_SHIFTPATTERNDETAILS,
                new { shiftpatternid = ShiftPatternId }).ConfigureAwait(false);

            return result?.ToList() ?? new List<ShiftPatternDetailDTO>();
        }

        // ── Employee-in-use guard ─────────────────────────────────────────

        public async Task<List<int>> GetEmployeesUsingShiftPattern(int ShiftPatternId, LoginDTO LoginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                LoginDTO, ShiftPatternQB.GET_EMPLOYEES_USING_SHIFTPATTERN,
                new { shiftpatternid = ShiftPatternId }).ConfigureAwait(false);

            return result?.ToList() ?? new List<int>();
        }

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

        public async Task<string> GetSelectListShiftPattern(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            var parameters = new DynamicParameters();
            string filter = BuildFilter(CriteriaDTO, parameters);

            if (IsCount)
            {
                string countSql = ShiftPatternQB.GET_SELECTLIST_SHIFTPATTERN_COUNT_SQL
                    + filter
                    + ShiftPatternQB.GET_SELECTLIST_SHIFTPATTERN_COUNT_FOOTER;

                int total = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, parameters)
                    .ConfigureAwait(false);
                return total.ToString();
            }

            string body = LoginDTO.DatabaseType switch
            {
                DBType.SQL => ShiftPatternQB.GET_SELECTLIST_SHIFTPATTERN_SQL,
                DBType.PostGre => ShiftPatternQB.GET_SELECTLIST_SHIFTPATTERN_PG,
                _ => throw new Exception("Unsupported database type")
            };

            string footer = LoginDTO.DatabaseType switch
            {
                DBType.SQL => ShiftPatternQB.GET_SELECTLIST_SHIFTPATTERN_SQL_FOOTER,
                DBType.PostGre => ShiftPatternQB.GET_SELECTLIST_SHIFTPATTERN_PG_FOOTER,
                _ => throw new Exception("Unsupported database type")
            };

            string sql = body + filter + footer;

            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult", MaxResult);

            var result = await _QueryExecutor.QueryAsync<ShiftPatternPicklistDTO>(LoginDTO, sql, parameters)
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ── Criteria → parameterized filter clause ─────────────────────────

        private string BuildFilter(CriteriaDTO CriteriaDTO, DynamicParameters parameters)
        {
            var sb = new System.Text.StringBuilder();

            string code = GetCriteriaStringValue(CriteriaDTO, "Code");
            if (!string.IsNullOrWhiteSpace(code))
            {
                sb.Append("\r\n AND a.SHIFTPATTERNCODE LIKE @code");
                parameters.Add("code", $"%{code}%");
            }

            string name = GetCriteriaStringValue(CriteriaDTO, "Name");
            if (!string.IsNullOrWhiteSpace(name))
            {
                sb.Append("\r\n AND a.SHIFTPATTERNNAME LIKE @name");
                parameters.Add("name", $"%{name}%");
            }

            return sb.ToString();
        }

        private string GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.String ? je.GetString() : je.ToString();

                return attr?.FieldValue?.ToString();
            }
            catch { return null; }
        }
    }
}