using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System.Linq;
using System.Text.Json;
using WiDAL.DTOs;
using WiDAL.Query.Wi;

namespace WiDAL.CustomCode.WiMaster;

public class WiMasterDAL : IWiMasterDAL
{
    private readonly IQueryExecutor _qe;
    private readonly IValidation _Validation;

    public WiMasterDAL(IQueryExecutor qe, IValidation validation)
        {
            _qe = qe;
            _Validation = validation;
        }

    public async Task<string> GetWiList(int wiProcessId, byte wiStatus, LoginDTO login, CancellationToken ct)
    {
        // wiStatus 255 = all statuses
        var result = await _qe.QueryAsync<WiListItemDTO>(login, WiMasterQB.GET_WI_LIST,
            new { TenantId = login.ClientId, WiProcessId = wiProcessId, WiStatus = wiStatus },
            cancellationToken: ct);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<WiHeaderDTO?> GetWiById(int wiId, LoginDTO login, CancellationToken ct)
    {
        return await _qe.QuerySingleAsync<WiHeaderDTO>(login, WiMasterQB.GET_WI_BY_ID,
            new { WiId = wiId, TenantId = login.ClientId });
    }

    public async Task<string> GetWiFullDetail(int wiId, LoginDTO login, CancellationToken ct)
    {
        var header = await _qe.QuerySingleAsync<WiHeaderDTO>(login, WiMasterQB.GET_WI_BY_ID,
            new { WiId = wiId, TenantId = login.ClientId });

        if (header == null) return JsonConvert.SerializeObject(null);

        var sections = (await _qe.QueryAsync<WiSectionDTO>(login, WiMasterQB.GET_SECTIONS,
            new { WiId = wiId }, cancellationToken: ct)).ToList();

        var allSteps = (await _qe.QueryAsync<WiStepDTO>(login, WiMasterQB.GET_STEPS,
            new { WiId = wiId }, cancellationToken: ct)).ToList();

        var allContents = (await _qe.QueryAsync<WiStepContentDTO>(login, WiMasterQB.GET_STEP_CONTENTS,
            new { WiId = wiId }, cancellationToken: ct)).ToList();

        // Assemble tree — flat Contents + grouped Containers (backward compatible)
        foreach (var step in allSteps)
        {
            var stepContents = allContents.Where(c => c.WiStepId == step.WiStepId).ToList();
            step.Contents = stepContents;
            step.Containers = stepContents
                .GroupBy(c => c.ContainerId)
                .Select(g => new WiStepContainerDTO
                {
                    ContainerId    = g.Key,
                    ContainerType  = g.First().ContainerType,
                    ContainerLabel = g.First().ContainerLabel,
                    Blocks         = g.OrderBy(c => c.ZoneName).ThenBy(c => c.BlockInContainerPos).ToList()
                })
                .OrderBy(ct => ct.ContainerId)
                .ToList();
        }

        foreach (var section in sections)
            section.Steps = allSteps.Where(s => s.WiSectionId == section.WiSectionId).ToList();

        var skillReqs = (await _qe.QueryAsync<WiSkillRequirementDTO>(login, WiMasterQB.GET_SKILL_REQS,
            new { WiId = wiId }, cancellationToken: ct)).ToList();

        var competencyReqs = (await _qe.QueryAsync<WiCompetencyRequirementDTO>(login, WiMasterQB.GET_COMPETENCY_REQS,
            new { WiId = wiId }, cancellationToken: ct)).ToList();

        var result = new
        {
            Header                 = header,
            Sections               = sections,
            SkillRequirements      = skillReqs,
            CompetencyRequirements = competencyReqs
        };

        return JsonConvert.SerializeObject(result);
    }


    // ── FullSave ─────────────────────────────────────────────────
    public async Task SaveWiFull(WiSaveRequestDTO req,LoginDTO login,CancellationToken ct)
    {
        var statements = new List<(string sql, object param)>();

        // ── 1. Upsert header ─────────────────────────────────────────────────
        statements.Add((
            WiMasterQB.UPSERT_WI_HEADER,
            new
            {
                WiId = req.Header.WiId,
                WiSubProcessId = req.Header.WiSubProcessId,
                WiProcessId = req.Header.WiProcessId,
                WiCode = req.Header.WiCode,
                WiTitle = req.Header.WiTitle,
                ActivityType = (byte)req.Header.ActivityType,
                ActivitySubType = req.Header.ActivitySubType,
                WiVersion = req.Header.WiVersion,
                WiStatus = (byte)req.Header.WiStatus,
                DisplayContext = (byte)req.Header.DisplayContext,
                StepAdvanceMode = (byte)req.Header.StepAdvanceMode,
                StdCycleTimeMins = req.Header.StdCycleTimeMins,
                EffectiveDate = req.Header.EffectiveDate,
                ReviewDueDate = req.Header.ReviewDueDate,
                Tags = req.Header.Tags,
                WiDescription = req.Header.WiDescription,
                IsActive = req.Header.IsActive,
                Status = req.Header.Status,
                SortOrder = req.Header.SortOrder,
                ApprovedById = req.Header.ApprovedById,
                CreatedById = req.Header.CreatedById,
                ModifiedById = req.Header.ModifiedById,
                SourceType = req.Header.SourceType,
                TenantId = login.ClientId   // ← always from login, never from DTO
            }));

        // ── 2. Soft-delete existing children (order matters for FK safety) ───
        statements.Add((WiMasterQB.SOFT_DELETE_CONTENTS, new { wiId = req.Header.WiId }));
        statements.Add((WiMasterQB.SOFT_DELETE_STEPS, new { wiId = req.Header.WiId }));
        statements.Add((WiMasterQB.SOFT_DELETE_SECTIONS, new { wiId = req.Header.WiId }));

        // ── 3. Sections → Steps → Contents ───────────────────────────────────
        foreach (var section in req.Sections ?? [])
        {
            statements.Add((
                WiMasterQB.INSERT_SECTION,
                new
                {
                    WiSectionId = section.WiSectionId,
                    WiId = req.Header.WiId,
                    SlNo = section.SectionSlNo,
                    SectionTitle = section.SectionTitle,
                    Description = section.SectionDescription
                }));

            foreach (var step in section.Steps ?? [])
            {
                statements.Add((
                    WiMasterQB.INSERT_STEP,
                    new
                    {
                        WiStepId = step.WiStepId,
                        WiId = step.WiId,
                        WiSectionId = step.WiSectionId,
                        SlNo = step.WiStepSlNo,
                        StepDisplayNo = step.StepDisplayNo,
                        StepTitle = step.StepTitle,
                        WarningLevel = (byte)step.WarningLevel,
                        StdDurationSecs = step.StdDurationSecs,
                        ToolsRequired = step.ToolsRequired,
                        IsOptional = step.IsOptional,
                        RequireSignOff = step.RequireSignOff,
                        AutoAdvanceSecs = step.AutoAdvanceSecs,
                        ContainerType = (byte)step.ContainerType,   // NEW
                        IsActive = step.IsActive
                    }));

                foreach (var content in step.Contents ?? [])
                {
                    statements.Add((
                        WiMasterQB.INSERT_STEP_CONTENT,
                        new
                        {
                            WiStepContentId = content.WiStepContentId,
                            WiStepId = step.WiStepId,
                            WiStepContentSlNo = content.WiStepContentSlNo,
                            ContentType = (byte)content.ContentType,
                            ContentSource = (byte)content.ContentSource,
                            ContentRef = content.ContentRef,
                            MediaCaption = content.MediaCaption,
                            DisplayDurationSecs = content.DisplayDurationSecs,
                            LoopMedia = content.LoopMedia,
                            ZoomRegion = content.ZoomRegion,
                            ApplicableModelIds = content.ApplicableModelIds,
                            ZoneName = content.ZoneName,                     // NEW
                            BlockInContainerPos = content.BlockInContainerPos, // NEW
                            ContainerType = (byte)content.ContainerType,      // NEW
                            ContainerLabel = content.ContainerLabel,          // NEW
                            ContainerId = content.ContainerId,
                            IsActive = content.IsActive
                        }));
                }
            }
        }

        // ── 4. Skill requirements ─────────────────────────────────────────────
        statements.Add((WiMasterQB.DELETE_SKILL_REQS, new { WiId = req.Header.WiId }));

        foreach (var skill in req.SkillRequirements ?? [])
        {
            statements.Add((
                WiMasterQB.INSERT_SKILL_REQ,
                new
                {
                    WiSkillRequirementId = skill.WiSkillRequirementId,
                    WiId = req.Header.WiId,
                    SkillId = skill.SkillId,
                    MinLevelNo = skill.MinLevelNo,
                    Enforcement = (byte)skill.Enforcement,
                    IsMandatory = skill.IsMandatory,
                    Remarks = skill.Remarks
                }));
        }

        // ── 5. Competency requirements ────────────────────────────────────────
        statements.Add((WiMasterQB.DELETE_COMPETENCY_REQS, new { WiId = req.Header.WiId }));

        foreach (var competency in req.CompetencyRequirements ?? [])
        {
            statements.Add((
                WiMasterQB.INSERT_COMPETENCY_REQ,
                new
                {
                    WiCompetencyRequirementId = competency.WiCompetencyRequirementId,
                    WiId = req.Header.WiId,
                    CompetencyId = competency.CompetencyId,
                    MinLevelNo = competency.MinLevelNo,
                    Enforcement = (byte)competency.Enforcement,
                    IsMandatory = competency.IsMandatory,
                    Remarks = competency.Remarks
                }));
        }

        // ── 6. Execute everything in one transaction ──────────────────────────
        await _qe.ExecuteInTransactionAsync(login, statements);
    }


    //----------------Update-------------------------------------------------------
    public async Task UpdateWiFull(WiSaveRequestDTO req, LoginDTO login, CancellationToken ct)
    {
        var statements = new List<(string sql, object param)>();

        // Update Header

        statements.Add((
            WiMasterQB.UPSERT_WI_HEADER,
            new
            {
                WiId = req.Header.WiId,
                WiSubProcessId = req.Header.WiSubProcessId,
                WiProcessId = req.Header.WiProcessId,
                WiCode = req.Header.WiCode,
                WiTitle = req.Header.WiTitle,
                ActivityType = (byte)req.Header.ActivityType,
                ActivitySubType = req.Header.ActivitySubType,
                WiVersion = req.Header.WiVersion,
                WiStatus = (byte)req.Header.WiStatus,
                DisplayContext = (byte)req.Header.DisplayContext,
                StepAdvanceMode = (byte)req.Header.StepAdvanceMode,
                StdCycleTimeMins = req.Header.StdCycleTimeMins,
                EffectiveDate = req.Header.EffectiveDate,
                ReviewDueDate = req.Header.ReviewDueDate,
                Tags = req.Header.Tags,
                WiDescription = req.Header.WiDescription,
                IsActive = req.Header.IsActive,
                Status = req.Header.Status,
                SortOrder = req.Header.SortOrder,
                ApprovedById = req.Header.ApprovedById,
                ModifiedById = login.UserId,
                SourceType = req.Header.SourceType,
                TenantId = login.ClientId
            }));

        // Remove Existing Children

        statements.Add((WiMasterQB.SOFT_DELETE_CONTENTS,
            new { wiId = req.Header.WiId }));

        statements.Add((WiMasterQB.SOFT_DELETE_STEPS,
            new { wiId = req.Header.WiId }));

        statements.Add((WiMasterQB.SOFT_DELETE_SECTIONS,
            new { wiId = req.Header.WiId }));

        statements.Add((WiMasterQB.DELETE_SKILL_REQS,
            new { wiId = req.Header.WiId }));

        statements.Add((WiMasterQB.DELETE_COMPETENCY_REQS,
            new { wiId = req.Header.WiId }));

        // Reinsert Latest Data

        foreach (var section in req.Sections ?? [])
        {
            statements.Add((
                WiMasterQB.INSERT_SECTION,
                new
                {
                    section.WiSectionId,
                    section.WiId,
                    SlNo = section.SectionSlNo,
                    section.SectionTitle,
                    Description = section.SectionDescription
                }));

            foreach (var step in section.Steps ?? [])
            {
                statements.Add((
                    WiMasterQB.INSERT_STEP,
                    new
                    {
                        step.WiStepId,
                        step.WiId,
                        step.WiSectionId,
                        SlNo = step.WiStepSlNo,
                        step.StepDisplayNo,
                        step.StepTitle,
                        WarningLevel = (byte)step.WarningLevel,
                        step.StdDurationSecs,
                        step.ToolsRequired,
                        step.IsOptional,
                        RequireSignOff = step.RequireSignOff,
                        step.AutoAdvanceSecs,
                        ContainerType = (byte)step.ContainerType,   // NEW
                        IsActive = step.IsActive
                    }));

                foreach (var content in step.Contents ?? [])
                {
                    statements.Add((
                        WiMasterQB.INSERT_STEP_CONTENT,
                        new
                        {
                            content.WiStepContentId,
                            step.WiStepId,
                            SlNo = content.WiStepContentSlNo,
                            ContentType = (byte)content.ContentType,
                            ContentSource = (byte)content.ContentSource,
                            content.ContentRef,
                            content.MediaCaption,
                            content.DisplayDurationSecs,
                            content.LoopMedia,
                            content.ZoomRegion,
                            content.ApplicableModelIds,
                            ZoneName = content.ZoneName,                     // NEW
                            BlockInContainerPos = content.BlockInContainerPos, // NEW
                            ContainerType = (byte)content.ContainerType,      // NEW
                            ContainerLabel = content.ContainerLabel,          // NEW
                            IsActive = content.IsActive
                        }));
                }
            }
        }

        foreach (var skill in req.SkillRequirements ?? [])
        {
            statements.Add((
                WiMasterQB.INSERT_SKILL_REQ,
                new
                {
                    skill.WiSkillRequirementId,
                    skill.WiId,
                    skill.SkillId,
                    skill.MinLevelNo,
                    Enforcement = (byte)skill.Enforcement,
                    skill.IsMandatory,
                    skill.Remarks
                }));
        }

        foreach (var competency in req.CompetencyRequirements ?? [])
        {
            statements.Add((
                WiMasterQB.INSERT_COMPETENCY_REQ,
                new
                {
                    competency.WiCompetencyRequirementId,
                    competency.WiId,
                    competency.CompetencyId,
                    competency.MinLevelNo,
                    Enforcement = (byte)competency.Enforcement,
                    competency.IsMandatory,
                    competency.Remarks
                }));
        }

        await _qe.ExecuteInTransactionAsync(login, statements);
    }

    public async Task<int> UpdateWiStatus(int wiId, byte newStatus, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(login, WiMasterQB.UPDATE_WI_STATUS,
            new { WiId = wiId, WiStatus = newStatus, ModifiedById = modifiedById, TenantId = login.ClientId },
            cancellationToken: ct);
    }

    public async Task<int> TransitionStatusWithSnapshot(
        int wiId, byte newStatus, int modifiedById, WiVersionLogDTO log, string snapshotJson, LoginDTO login, CancellationToken ct)
    {
        await using var tx = await _qe.BeginTransactionAsync(login);
        try
        {
            await _qe.ExecuteAsync(login, WiMasterQB.UPDATE_WI_STATUS,
                new { WiId = wiId, WiStatus = newStatus, ModifiedById = modifiedById, TenantId = login.ClientId },
                tx, ct);

            await _qe.ExecuteAsync(login, WiVersionQB.CLEAR_PREVIOUS_CURRENT,
                new { WiId = wiId }, tx, ct);

            var lwiVersionId = await _qe.ExecuteIdentityAsync(login, WiVersionQB.APPEND_VERSION, new
            {
                WiId          = log.WiId,
                WiVersion     = log.WiVersion,
                WiStatus      = (byte)log.WiStatus,
                ChangeSummary = log.ChangeSummary,
                ChangedById   = log.ChangedById,
                ReviewedById  = -1,
                ApprovedById  = -1,
                PublishedOn   = log.PublishedOn,
                ArchivedOn    = log.ArchivedOn,
                IsCurrent     = log.IsCurrent ? (byte)1 : (byte)0,
                CreatedById   = log.CreatedById
            }, tx);

            await _qe.ExecuteAsync(login, WiVersionQB.INSERT_SNAPSHOT, new
            {
                LwiVersionId = lwiVersionId,
                WiId         = wiId,
                SnapshotJson = snapshotJson
            }, tx, ct);

            await tx.CommitAsync(ct);
            return lwiVersionId;
        }
        catch
        {
            await tx.RollbackAsync(ct);
            throw;
        }
    }

    public async Task<int> InvalidateAcknowledgements(int wiId, int modifiedById, LoginDTO login, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(login, WiMasterQB.INVALIDATE_ACKS,
            new { WiId = wiId, ModifiedById = modifiedById, TenantId = login.ClientId },
            cancellationToken: ct);
    }

    public async Task<string> GetVersionHistory(int wiId, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<WiVersionLogDTO>(login, WiVersionQB.GET_VERSION_HISTORY,
            new { WiId = wiId }, cancellationToken: ct);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<string?> GetVersionSnapshot(int lwiVersionId, LoginDTO login, CancellationToken ct)
    {
        var rows = await _qe.QueryAsync<WiVersionSnapshotDTO>(login, WiVersionQB.GET_SNAPSHOT_BY_VERSION_ID,
            new { LwiVersionId = lwiVersionId }, cancellationToken: ct);
        return rows.FirstOrDefault()?.SnapshotJson;
    }
    public async Task<string> GetSelectListWiMaster(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<dynamic>(
            login, WiMasterQB.GET_SELECTLIST_WI);
        return JsonConvert.SerializeObject(result);
    }

    public async Task<string> DeleteWiMaster(int WiId, LoginDTO loginDTO, CancellationToken ct)
    {
        var parameter = new { wiId = WiId };

        await using var tx = await _qe.BeginTransactionAsync(loginDTO);
        try
        {
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_CHECKLIST_RESPONSES, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_STEP_EXECUTIONS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_EXECUTIONS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_STEP_CONTENT, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_STEPS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_SECTIONS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_ACTIVITY_SCHEDULES, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_ASSIGNMENTS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_ACKNOWLEDGEMENTS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_SKILL_REQUIREMENTS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_COMPETENCY_REQUIREMENTS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_VERSION_SNAPSHOTS, parameter, tx, ct);
            await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_CASCADE_VERSIONS, parameter, tx, ct);
            var result = await _qe.ExecuteAsync(loginDTO, WiMasterQB.DELETE_WIMASTER, parameter, tx, ct);

            await tx.CommitAsync(ct);

            return result > 0
                ? SuccessResponse.DeleteSuccessMessage
                : ErrorResponse.DeleteNotFoundMessage;
        }
        catch (Exception ex)
        {
            await tx.RollbackAsync(ct);
            string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
            throw new Exception(error);
        }
    }

    public async Task<string> GetStepsWithWorkstationAsync(int wiId, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<WiStepSwimLaneDTO>(
            login,
            WiMasterQB.GET_STEPS_WITH_EFFECTIVE_WORKSTATION,
            new { WiId = wiId, TenantId = login.ClientId },
            cancellationToken: ct).ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }
}