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

namespace WiDAL.CustomCode.WiAssignment;

public class WiAssignmentDAL : IWiAssignmentDAL
{
    private readonly IQueryExecutor _qe;
    private readonly IValidation _Validation;
    public WiAssignmentDAL(IQueryExecutor qe, IValidation validation)
    {
        _qe = qe;
        _Validation = validation;
    }
    public async Task<string> GetAssignmentsById(int WiAssignmentId, LoginDTO login, CancellationToken ct)
    {
        var assignments = (await _qe.QueryAsync<WiAssignmentDTO>(login, WiAssignmentQB.GET_BY_ID,
            new { wiAssignmentId = WiAssignmentId, TenantId = login.ClientId }, cancellationToken: ct)).ToList();

        foreach (var a in assignments)
        {
            a.Schedules = (await _qe.QueryAsync<WiActivityScheduleDTO>(login,
                WiAssignmentQB.GET_SCHEDULES_BY_ASSIGNMENT,
                new { wiAssignmentId = a.WiAssignmentId }, cancellationToken: ct)).ToList();
        }

        return JsonConvert.SerializeObject(assignments);
    }

    //public async Task<int> SaveAssignment(WiAssignmentDTO dto, LoginDTO login, CancellationToken ct)
    //{
    //    bool isNew = dto.WiAssignmentId == 0;
    //    var statements = new List<(string sql, object param)>
    //    {
    //        isNew
    //            ? (WiAssignmentQB.INSERT_ASSIGNMENT, (object)dto)
    //            : (WiAssignmentQB.UPDATE_ASSIGNMENT, (object)dto)
    //    };

    //    // Replace schedules
    //    statements.Add((WiAssignmentQB.DELETE_SCHEDULES_BY_ASSIGNMENT,
    //        new { WiAssignmentId = dto.WiAssignmentId }));

    //    foreach (var schedule in dto.Schedules ?? [])
    //        statements.Add((WiAssignmentQB.INSERT_SCHEDULE, (object)schedule));

    //    await _qe.ExecuteInTransactionAsync(login, statements);
    //    return dto.WiAssignmentId;
    //}


    public async Task<int> SaveAssignment(WiAssignmentDTO dto, bool isNew, LoginDTO login, CancellationToken ct)
    {
        try
        {
            string sql = isNew
                ? WiAssignmentQB.INSERT_ASSIGNMENT
                : WiAssignmentQB.UPDATE_ASSIGNMENT;

            await using var tx = await _qe.BeginTransactionAsync(login);
            try
            {
                var rows = await _qe.ExecuteAsync(login, sql, dto, tx, ct);

                // 🔥 important check
                if (rows == 0)
                {
                    throw new Exception(isNew
                        ? "Insert failed!"
                        : "Update failed! No matching record.");
                }

                // Replace this assignment's schedules wholesale — same "delete then reinsert"
                // approach as the rest of this codebase's child-collection saves.
                await _qe.ExecuteAsync(login, WiAssignmentQB.DELETE_SCHEDULES_BY_ASSIGNMENT,
                    new { WiAssignmentId = dto.WiAssignmentId }, tx, ct);

                foreach (var schedule in dto.Schedules ?? [])
                {
                    await _qe.ExecuteAsync(login, WiAssignmentQB.INSERT_SCHEDULE, new
                    {
                        WiActivityScheduleId = schedule.WiActivityScheduleId,
                        WiAssignmentId       = dto.WiAssignmentId,
                        ActivityType         = (byte)schedule.ActivityType,
                        TriggerEvent         = (byte)schedule.TriggerEvent,
                        TriggerFrequencyN    = schedule.TriggerFrequencyN,
                        ExecutionOrder       = schedule.ExecutionOrder,
                        IsBlockingNext       = schedule.IsBlockingNext,
                        RequiresSupervisor   = schedule.RequiresSupervisor,
                        AllowSkip            = schedule.AllowSkip,
                        IsActive             = schedule.IsActive,
                        Remarks              = schedule.Remarks
                    }, tx, ct);
                }

                await tx.CommitAsync(ct);
                return dto.WiAssignmentId;
            }
            catch
            {
                await tx.RollbackAsync(ct);
                throw;
            }
        }
        catch (Exception ex)
        {
            string error = await _Validation.HandleException(ex,
                isNew ? ErrorResponse.SaveErrorMessage : ErrorResponse.UpdateErrorMessage);

            throw new Exception(error);
        }
    }

    public async Task<WorkstationConfigDTO?> GetWorkstationConfig(int workStationId, LoginDTO login, CancellationToken ct)
    {
        return await _qe.QuerySingleAsync<WorkstationConfigDTO>(login, WorkstationConfigQB.GET_BY_WORKSTATION,
            new { WorkStationId = workStationId, TenantId = login.ClientId });
    }

    public async Task<int> SaveWorkstationConfig(WorkstationConfigDTO dto, LoginDTO login, CancellationToken ct)
    {
        bool isNew = dto.WorkStationConfigId == 0;
        var statements = new List<(string sql, object param)>
        {
            isNew
                ? (WorkstationConfigQB.INSERT, (object)dto)
                : (WorkstationConfigQB.UPDATE, (object)dto)
        };
        await _qe.ExecuteInTransactionAsync(login, statements);
        return dto.WorkStationConfigId;
    }

    public async Task<IEnumerable<WiAssignmentDTO>> ResolveForWorkstation(
        int workStationId, int triggerEvent, LoginDTO login, CancellationToken ct,
        int workCenterId = -1, int routingId = -1, int routingOperationId = -1,
        int modelId = -1, int itemGroupId = -1)
    {
        return await _qe.QueryAsync<WiAssignmentDTO>(login, WiAssignmentQB.RESOLVE_FOR_WORKSTATION,
            new
            {
                TenantId      = login.ClientId,
                WorkStationId = workStationId,
                TriggerEvent  = triggerEvent,
                WorkCenterId  = workCenterId,
                RoutingId     = routingId,
                RoutingOpId   = routingOperationId,
                ModelId       = modelId,
                ItemGroupId   = itemGroupId
            }, cancellationToken: ct);
    }

    public async Task<IEnumerable<WiAssignmentDTO>> ResolveForEntity(int entityType, int entityId, int triggerEvent, LoginDTO login, CancellationToken ct)
    {
        return await _qe.QueryAsync<WiAssignmentDTO>(login, WiAssignmentQB.RESOLVE_FOR_ENTITY,
            new
            {
                TenantId     = login.ClientId,
                EntityType   = entityType,
                EntityId     = entityId,
                TriggerEvent = triggerEvent
            }, cancellationToken: ct);
    }

    public async Task<IEnumerable<WiResolveDiagnosticDTO>> DiagnoseForWorkstation(
        int workStationId, LoginDTO login, CancellationToken ct,
        int workCenterId = -1, int routingId = -1, int routingOperationId = -1,
        int modelId = -1, int itemGroupId = -1)
    {
        return await _qe.QueryAsync<WiResolveDiagnosticDTO>(login, WiAssignmentQB.DIAGNOSE_FOR_WORKSTATION,
            new
            {
                TenantId      = login.ClientId,
                WorkStationId = workStationId,
                WorkCenterId  = workCenterId,
                RoutingId     = routingId,
                RoutingOpId   = routingOperationId,
                ModelId       = modelId,
                ItemGroupId   = itemGroupId
            }, cancellationToken: ct);
    }

    public async Task<IEnumerable<WiResolveDiagnosticDTO>> DiagnoseForEntity(int entityType, int entityId, LoginDTO login, CancellationToken ct)
    {
        return await _qe.QueryAsync<WiResolveDiagnosticDTO>(login, WiAssignmentQB.DIAGNOSE_FOR_ENTITY,
            new { TenantId = login.ClientId, EntityType = entityType, EntityId = entityId }, cancellationToken: ct);
    }

    public async Task<string> GetSelectListAssignment(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<dynamic>(
            login, WiAssignmentQB.GET_SELECTLIST_WIASSIGNMENT);
        return JsonConvert.SerializeObject(result);
    }
    public async Task<string> DeleteAssignment(int AssignmentId, LoginDTO loginDTO, CancellationToken ct)
    {
        var parameter = new { wiAssignmentId = AssignmentId };

        await using var tx = await _qe.BeginTransactionAsync(loginDTO);
        try
        {
            await _qe.ExecuteAsync(loginDTO, WiAssignmentQB.DELETE_CASCADE_ACTIVITY_SCHEDULES, parameter, tx, ct);
            var result = await _qe.ExecuteAsync(loginDTO, WiAssignmentQB.DELETE_WIASSIGNMENT, 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> GetSelectListWorkstationConfig(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<dynamic>(
            login, WiProcessQB.GET_SELECTLIST_WIPROCESS);
        return JsonConvert.SerializeObject(result);
    }
    public async Task<string> DeleteWorkstationConfig(int WiProcessId, LoginDTO loginDTO)
    {
        try
        {
            string sql = WiProcessQB.DELETE_WIPROCESS;

            var parameter = new { wiProcessId = WiProcessId };

            var result = await _qe.ExecuteAsync(loginDTO, sql, parameter);

            if (result > 0)
                return SuccessResponse.DeleteSuccessMessage;
            else
                return ErrorResponse.DeleteNotFoundMessage;
        }
        catch (Exception ex)
        {
            string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
            throw new Exception(error);
        }
    }

    public async Task<string> GetAssignmentList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    {
        var result = await _qe.QueryAsync<dynamic>(
            login, WiAssignmentQB.GET_ASSIGNMENT_LIST);
        return JsonConvert.SerializeObject(result);
    }
}