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

namespace MMDAL.CustomCode.JobCard
{
    public class JobCardDAL : IJobCardDAL
    {
        private readonly IQueryExecutor _qe;

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

        public async Task SaveJobCard(JobCardSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.SAVE_JOBCARD_SQL,
                DBType.PostGre => JobCardQB.SAVE_JOBCARD_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.JobCardId, dto.BizTransactionTypeId, dto.OUId, dto.PeriodId,
                    dto.JobCardDate, dto.JobCardNumber, dto.WorkCenterId, dto.MachineId,
                    dto.ShiftId, dto.OperatorId, dto.Remarks,
                    TenantId = login.ClientId
                }, tx, ct).ConfigureAwait(false);
        }

        public async Task BulkSaveJobCardDetails(IEnumerable<JobCardDetailSaveDTO> details, LoginDTO login, DbTransaction tx)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.SAVE_JOBCARD_DETAIL_SQL,
                DBType.PostGre => JobCardQB.SAVE_JOBCARD_DETAIL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.BulkInsertAsync(login, sql, details, tx).ConfigureAwait(false);
        }

        public async Task BulkSaveJobCardMaterials(IEnumerable<JobCardMaterialSaveDTO> materials, LoginDTO login, DbTransaction tx)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.SAVE_JOBCARD_MATERIAL_SQL,
                DBType.PostGre => JobCardQB.SAVE_JOBCARD_MATERIAL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.BulkInsertAsync(login, sql, materials, tx).ConfigureAwait(false);
        }

        public async Task BulkSaveJobCardProcesses(IEnumerable<JobCardProcessSaveDTO> processes, LoginDTO login, DbTransaction tx)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.SAVE_JOBCARD_PROCESS_SQL,
                DBType.PostGre => JobCardQB.SAVE_JOBCARD_PROCESS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.BulkInsertAsync(login, sql, processes, tx).ConfigureAwait(false);
        }

        public async Task<JobCardSaveDTO?> GetJobCard(int jobCardId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.GET_JOBCARD_SQL,
                DBType.PostGre => JobCardQB.GET_JOBCARD_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

        public async Task<IEnumerable<JobCardDetailSaveDTO>> GetJobCardDetails(int jobCardId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.GET_JOBCARD_DETAILS_SQL,
                DBType.PostGre => JobCardQB.GET_JOBCARD_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

        public async Task<(IEnumerable<JobCardSaveDTO> Items, int TotalCount)> GetJobCardList(
            int ouId, int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            string sql      = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.GET_JOBCARD_LIST_SQL,
                DBType.PostGre => JobCardQB.GET_JOBCARD_LIST_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            string countSql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.GET_JOBCARD_LIST_COUNT_SQL,
                DBType.PostGre => JobCardQB.GET_JOBCARD_LIST_COUNT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var param  = new { OUId = ouId, FirstNumber = firstNumber, MaxResult = maxResult, TenantId = login.ClientId };
            var items  = await _qe.QueryAsync<JobCardSaveDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false) ?? [];
            var total  = await _qe.ExecuteScalarAsync<int>(login, countSql, param, cancellationToken: ct).ConfigureAwait(false);
            return (items, total);
        }

        public async Task<IEnumerable<JobCardSaveDTO>> GetJobCardsForOperator(int operatorId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.GET_JOBCARDS_FOR_OPERATOR_SQL,
                DBType.PostGre => JobCardQB.GET_JOBCARDS_FOR_OPERATOR_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

        public async Task CompleteJobCard(int jobCardId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.COMPLETE_JOBCARD_SQL,
                DBType.PostGre => JobCardQB.COMPLETE_JOBCARD_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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

        public async Task SoftDeleteJobCard(int jobCardId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => JobCardQB.SOFT_DELETE_JOBCARD_SQL,
                DBType.PostGre => JobCardQB.SOFT_DELETE_JOBCARD_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

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