using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.ProductionPlan;
using MMDAL.Query.ProductionPlan;
using Newtonsoft.Json;
using System.Data.Common;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.ProductionPlan
{
    public class ProductionPlanDAL : IProductionPlanDAL
    {
        private readonly IQueryExecutor _qe;

        public ProductionPlanDAL(IQueryExecutor qe) => _qe = qe;

        public async Task<string> GetSelectListProductionPlan(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.GET_SELECTLIST_PRODUCTION_PLAN_SQL,
                DBType.PostGre => ProductionPlanQB.GET_SELECTLIST_PRODUCTION_PLAN_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            var result = await _qe.QueryAsync<dynamic>(login, sql,
                new { TenantId = login.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetSelectListProductionPlanDetail(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.GET_SELECTLIST_PRODUCTION_PLAN_DETAIL_SQL,
                DBType.PostGre => ProductionPlanQB.GET_SELECTLIST_PRODUCTION_PLAN_DETAIL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            var result = await _qe.QueryAsync<dynamic>(login, sql,
                new { TenantId = login.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<ProductionPlanSaveDTO?> GetProductionPlan(int productionPlanId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.GET_PRODUCTION_PLAN_SQL,
                DBType.PostGre => ProductionPlanQB.GET_PRODUCTION_PLAN_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            var items = await _qe.QueryAsync<ProductionPlanSaveDTO>(login, sql,
                new { ProductionPlanId = productionPlanId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return items.FirstOrDefault();
        }

        public async Task<IEnumerable<ProductionPlanDetailSaveDTO>> GetProductionPlanDetails(int productionPlanId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.GET_PRODUCTION_PLAN_DETAILS_SQL,
                DBType.PostGre => ProductionPlanQB.GET_PRODUCTION_PLAN_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            return await _qe.QueryAsync<ProductionPlanDetailSaveDTO>(login, sql,
                new { ProductionPlanId = productionPlanId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<(IEnumerable<ProductionPlanListItemDTO> Items, int TotalCount)> GetProductionPlanList(
            int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            var (sql, countSql) = login.DatabaseType switch
            {
                DBType.SQL     => (ProductionPlanQB.GET_PRODUCTION_PLAN_LIST_SQL,     ProductionPlanQB.GET_PRODUCTION_PLAN_LIST_COUNT_SQL),
                DBType.PostGre => (ProductionPlanQB.GET_PRODUCTION_PLAN_LIST_PG,      ProductionPlanQB.GET_PRODUCTION_PLAN_LIST_COUNT_PG),
                _              => throw new NotSupportedException("Unsupported database type")
            };
            var param = new { TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult };
            var items = await _qe.QueryAsync<ProductionPlanListItemDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
            var count = await _qe.ExecuteScalarAsync<int>(login, countSql, new { TenantId = login.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return (items, count);
        }

        public async Task<int> SaveProductionPlanHeader(ProductionPlanSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.SAVE_PRODUCTION_PLAN_SQL,
                DBType.PostGre => ProductionPlanQB.SAVE_PRODUCTION_PLAN_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            return await _qe.ExecuteScalarAsync<int>(login, sql, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task UpdateProductionPlanHeader(ProductionPlanSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.UPDATE_PRODUCTION_PLAN_SQL,
                DBType.PostGre => ProductionPlanQB.UPDATE_PRODUCTION_PLAN_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql, dto, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task DeleteProductionPlanDetails(int productionPlanId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.DELETE_PRODUCTION_PLAN_DETAILS_SQL,
                DBType.PostGre => ProductionPlanQB.DELETE_PRODUCTION_PLAN_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql,
                new { ProductionPlanId = productionPlanId, TenantId = login.ClientId },
                tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task BulkSaveProductionPlanDetails(IEnumerable<ProductionPlanDetailSaveDTO> details, LoginDTO login, DbTransaction tx)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.SAVE_PRODUCTION_PLAN_DETAIL_SQL,
                DBType.PostGre => ProductionPlanQB.SAVE_PRODUCTION_PLAN_DETAIL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.BulkInsertAsync(login, sql, details, tx).ConfigureAwait(false);
        }

        public async Task SoftDeleteProductionPlan(int productionPlanId, int modifiedById, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.SOFT_DELETE_PRODUCTION_PLAN_SQL,
                DBType.PostGre => ProductionPlanQB.SOFT_DELETE_PRODUCTION_PLAN_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql,
                new { ProductionPlanId = productionPlanId, ModifiedById = modifiedById, TenantId = login.ClientId },
                tx, cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<IEnumerable<ProductionPlanDetailForWODTO>> GetPlanDetailsForWOGeneration(int productionPlanId, LoginDTO login, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.GET_PLAN_DETAILS_FOR_WO_GENERATION_SQL,
                DBType.PostGre => ProductionPlanQB.GET_PLAN_DETAILS_FOR_WO_GENERATION_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            return await _qe.QueryAsync<ProductionPlanDetailForWODTO>(login, sql,
                new { ProductionPlanId = productionPlanId, TenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task LinkPlanDetailToIndent(int productionPlanDetailId, int indentDetailId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionPlanQB.LINK_PLAN_DETAIL_TO_INDENT_SQL,
                DBType.PostGre => ProductionPlanQB.LINK_PLAN_DETAIL_TO_INDENT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            await _qe.ExecuteAsync(login, sql,
                new { ProductionPlanDetailId = productionPlanDetailId, IndentDetailId = indentDetailId, TenantId = login.ClientId },
                tx, cancellationToken: ct).ConfigureAwait(false);
        }
    }
}