using AdminDAL.CustomCode.Shared;
using AdminDAL.DTO.FormTemplate;
using AdminDAL.DTO.TemplateData;
using AdminDAL.Query.TemplateData;
using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using static GB5Shared.GB5Constant.Constant;

namespace AdminDAL.CustomCode.TemplateData
{
    public class TemplateDataDAL : ITemplateDataDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public TemplateDataDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<string> GetTemplateData(int TemplateDataId, LoginDTO LoginDTO, CancellationToken ct)
        {
            var dto = await _QueryExecutor.QuerySingleAsync<TemplateDataDTO>(
                LoginDTO, TemplateDataQB.GET_TEMPLATEDATA,
                new { templatedataid = TemplateDataId }, cancellationToken: ct).ConfigureAwait(false);
            return JsonConvert.SerializeObject(dto);
        }

        public async Task<string> GetSelectListTemplateData(int FormTemplateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<TemplateDataSelectlistDTO>(
                LoginDTO, TemplateDataQB.GET_TEMPLATEDATA_SELECTLIST,
                new { formtemplateid = FormTemplateId }, cancellationToken: ct).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // TEMPLATEDATAID is a real IDENTITY column (confirmed against a live database — see
        // TemplateDataQB's header comment), so this returns the DB-assigned id.
        public async Task<int> SaveTemplateData(TemplateDataDTO TemplateDataDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                var sql = LoginDTO.DatabaseType == DBTYPE.POSTGRESQL
                    ? TemplateDataQB.SAVE_TEMPLATEDATA_POSTGRESQL
                    : TemplateDataQB.SAVE_TEMPLATEDATA;
                return await _QueryExecutor.ExecuteIdentityAsync(
                    LoginDTO, sql, TemplateDataDTO).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task UpdateTemplateData(TemplateDataDTO TemplateDataDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO, TemplateDataQB.UPDATE_TEMPLATEDATA, TemplateDataDTO, cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<string> DeleteTemplateData(int TemplateDataId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                int result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO, TemplateDataQB.DELETE_TEMPLATEDATA,
                    new { templatedataid = TemplateDataId }, cancellationToken: ct).ConfigureAwait(false);

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

        public async Task PostDataToDynamicTable(
            TemplateDataDTO TemplateDataDTO, FormTemplateDTO FormTemplateDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            bool isPostgres = LoginDTO.DatabaseType == DBTYPE.POSTGRESQL;
            string tableName = DynamicTableSqlHelper.ValidateTableIdentifier(
                FormTemplateDTO.FormTemplateCode, nameof(FormTemplateDTO.FormTemplateCode));

            // Row-scoped DELETE+INSERT (unlike the legacy PostDataToTable, which deletes/inserts
            // with no row-scoping key at all — see the TEMPLATEDATAID column added by
            // FormTemplateDAL.CreateOrAlterDynamicTable).
            string deleteSql = $"DELETE FROM {tableName} WHERE TEMPLATEDATAID = @TemplateDataId";
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, deleteSql, new { TemplateDataId = TemplateDataDTO.TemplateDataId }, cancellationToken: ct)
                .ConfigureAwait(false);

            var fields = DynamicTableSqlHelper.ExtractScalarFields(FormTemplateDTO.FormTemplateFormTemplateData);
            var submittedValues = ParseSubmittedValues(TemplateDataDTO.TemplateDataData);

            var columnNames = new List<string> { "TEMPLATEDATAID" };
            var parameters = new DynamicParameters();
            parameters.Add("TemplateDataId", TemplateDataDTO.TemplateDataId);

            foreach (var field in fields)
            {
                string columnName = DynamicTableSqlHelper.ValidateColumnIdentifier(field.FieldId, "Field key");
                string paramName = "COL_" + columnName;

                columnNames.Add(columnName);
                // Every value is a real Dapper parameter — never concatenated into SQL text —
                // regardless of what the submitted value contains (quotes, SQL keywords, etc.).
                submittedValues.TryGetValue(field.FieldId, out var value);
                parameters.Add(paramName, value);
            }

            string columnList = string.Join(", ", columnNames);
            string paramList = string.Join(", ", columnNames.Select(c => "@" + (c == "TEMPLATEDATAID" ? "TemplateDataId" : "COL_" + c)));
            string insertSql = $"INSERT INTO {tableName} ({columnList}) VALUES ({paramList})";

            await _QueryExecutor.ExecuteAsync(LoginDTO, insertSql, parameters, cancellationToken: ct).ConfigureAwait(false);
        }

        // TemplateDataDTO.TemplateDataData holds the submitted values as a flat JSON object
        // ({ "fieldKey": value, ... }) — the shape MetaBaseFormGroup.collectData() produces on
        // the Angular side. Tolerant of a missing/malformed payload (returns an empty map rather
        // than throwing) since a field with no submitted value should just insert NULL.
        private static Dictionary<string, object?> ParseSubmittedValues(string? templateDataJson)
        {
            var result = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
            if (string.IsNullOrWhiteSpace(templateDataJson)) return result;

            try
            {
                var jObject = JObject.Parse(templateDataJson);
                foreach (var prop in jObject.Properties())
                {
                    result[prop.Name] = prop.Value.Type switch
                    {
                        JTokenType.Null => null,
                        JTokenType.Object or JTokenType.Array => prop.Value.ToString(Newtonsoft.Json.Formatting.None),
                        _ => prop.Value.ToObject<object>()
                    };
                }
            }
            catch (JsonException)
            {
                // Malformed payload — treated as "no values submitted" rather than failing the
                // whole save; the JSON-blob column (TTEMPLATEDATA.TEMPLATEDATADATA) still keeps
                // the raw submitted string regardless, so nothing is silently lost.
            }

            return result;
        }
    }
}