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

namespace AdminDAL.CustomCode.FormTemplate
{
    public class FormTemplateDAL : IFormTemplateDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetFormTemplate(int FormTemplateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            var dto = await _QueryExecutor.QuerySingleAsync<FormTemplateDTO>(
                LoginDTO, FormTemplateQB.GET_FORMTEMPLATE,
                new { formtemplateid = FormTemplateId }, cancellationToken: ct).ConfigureAwait(false);
            return JsonConvert.SerializeObject(dto);
        }

        public async Task<FormTemplateDTO?> GetFormTemplateByCode(string FormTemplateCode, LoginDTO LoginDTO, CancellationToken ct)
            => await _QueryExecutor.QuerySingleAsync<FormTemplateDTO>(
                LoginDTO, FormTemplateQB.GET_FORMTEMPLATE_BY_CODE,
                new { formtemplatecode = FormTemplateCode }, cancellationToken: ct).ConfigureAwait(false);

        public async Task<string> GetSelectListFormTemplate(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            // Matches AdminBLL.Branch's current simplification — CriteriaDTO-driven filtering
            // isn't wired here (no confirmed shared criteria-to-SQL helper found for this repo);
            // this returns the full active list, same as GetSelectListBranch does today.
            var result = await _QueryExecutor.QueryAsync<FormTemplateSelectlistDTO>(
                LoginDTO, FormTemplateQB.GET_FORMTEMPLATE_SELECTLIST, null!, cancellationToken: ct).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // FORMTEMPLATEID is a real IDENTITY column (confirmed against a live database — see
        // FormTemplateQB's header comment), so this returns the DB-assigned id rather than
        // accepting a pre-allocated one — same convention as MSUBSCRIPTION's own IDENTITY id,
        // not the AutoNumber-based pattern Branch uses.
        public async Task<int> SaveFormTemplate(FormTemplateDTO FormTemplateDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                var sql = LoginDTO.DatabaseType == DBTYPE.POSTGRESQL
                    ? FormTemplateQB.SAVE_FORMTEMPLATE_POSTGRESQL
                    : FormTemplateQB.SAVE_FORMTEMPLATE;
                return await _QueryExecutor.ExecuteIdentityAsync(
                    LoginDTO, sql, FormTemplateDTO).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(error);
            }
        }

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

        public async Task<string> DeleteFormTemplate(int FormTemplateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                int result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO, FormTemplateQB.DELETE_FORMTEMPLATE,
                    new { formtemplateid = FormTemplateId }, 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 CreateOrAlterDynamicTable(FormTemplateDTO FormTemplateDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            bool isPostgres = LoginDTO.DatabaseType == DBTYPE.POSTGRESQL;
            string tableName = DynamicTableSqlHelper.ValidateTableIdentifier(
                FormTemplateDTO.FormTemplateCode, nameof(FormTemplateDTO.FormTemplateCode));

            bool tableExists = await TableExistsAsync(tableName, LoginDTO, isPostgres, ct).ConfigureAwait(false);

            if (!tableExists)
            {
                // TEMPLATEDATAID links each row back to its owning TTEMPLATEDATA record — the
                // legacy D_<Code> table has no such column (its DELETE+INSERT in
                // TemplateDataBLL.PostDataToTable has no row-scoping key at all, which is
                // ambiguous for more than a single record). Adding it here is a deliberate,
                // documented improvement, not a shape carried over from the legacy table.
                string createSql = isPostgres
                    ? $"CREATE TABLE {tableName} (ID SERIAL PRIMARY KEY, TEMPLATEDATAID INT NOT NULL)"
                    : $"CREATE TABLE {tableName} (ID INT IDENTITY(1,1) PRIMARY KEY, TEMPLATEDATAID INT NOT NULL)";
                await _QueryExecutor.ExecuteAsync(LoginDTO, createSql, null!, cancellationToken: ct).ConfigureAwait(false);

                string indexSql = $"CREATE INDEX IX_{tableName}_TEMPLATEDATAID ON {tableName} (TEMPLATEDATAID)";
                await _QueryExecutor.ExecuteAsync(LoginDTO, indexSql, null!, cancellationToken: ct).ConfigureAwait(false);
            }

            var existingColumns = await GetExistingColumnsAsync(tableName, LoginDTO, isPostgres, ct).ConfigureAwait(false);
            var fields = DynamicTableSqlHelper.ExtractScalarFields(FormTemplateDTO.FormTemplateFormTemplateData);

            foreach (var field in fields)
            {
                string columnName = DynamicTableSqlHelper.ValidateColumnIdentifier(field.FieldId, "Field key");
                if (existingColumns.Contains(columnName)) continue;

                string sqlType = DynamicTableSqlHelper.MapFieldTypeToSqlType(field.Type, isPostgres);
                string alterSql = isPostgres
                    ? $"ALTER TABLE {tableName} ADD COLUMN {columnName} {sqlType} NULL"
                    : $"ALTER TABLE {tableName} ADD {columnName} {sqlType} NULL";

                // Deliberately NOT wrapped in a swallowing try/catch (unlike the legacy
                // CreateDynamicTable, which silently drops failures here) — a failed column
                // add must surface as a real error so the form author knows the table doesn't
                // fully match the design, rather than discovering it later when data is missing.
                await _QueryExecutor.ExecuteAsync(LoginDTO, alterSql, null!, cancellationToken: ct).ConfigureAwait(false);
            }
        }

        private async Task<bool> TableExistsAsync(string tableName, LoginDTO login, bool isPostgres, CancellationToken ct)
        {
            string sql = isPostgres
                ? "SELECT COUNT(1) FROM information_schema.tables WHERE table_name = @tablename"
                : "SELECT COUNT(1) FROM sys.tables WHERE name = @tablename";
            int count = await _QueryExecutor.ExecuteScalarAsync<int>(
                login, sql, new { tablename = isPostgres ? tableName.ToLowerInvariant() : tableName }).ConfigureAwait(false);
            return count > 0;
        }

        private async Task<HashSet<string>> GetExistingColumnsAsync(string tableName, LoginDTO login, bool isPostgres, CancellationToken ct)
        {
            string sql = isPostgres
                ? "SELECT column_name FROM information_schema.columns WHERE table_name = @tablename"
                : "SELECT c.name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = @tablename";
            var rows = await _QueryExecutor.QueryAsync<string>(
                login, sql, new { tablename = isPostgres ? tableName.ToLowerInvariant() : tableName },
                cancellationToken: ct).ConfigureAwait(false);
            return new HashSet<string>(rows ?? Enumerable.Empty<string>(), StringComparer.OrdinalIgnoreCase);
        }
    }
}