using System.ComponentModel.DataAnnotations;
using System.Text.Json;
using System.Text.RegularExpressions;

namespace AdminDAL.CustomCode.Shared
{
    // Shared by FormTemplateDAL (creates/alters D_<Code> at design time) and TemplateDataDAL
    // (writes rows into D_<Code> at data-entry time) for the "genericTable" storage strategy.
    //
    // This exists because the legacy GB4 equivalent (FormTemplate.CreateDynamicTable /
    // TemplateDataBLL.PostDataToTable) concatenates FormTemplateCode and field keys directly
    // into DDL/DML text with no validation at all — a real SQL-injection hole. SQL has no way
    // to parameterize a table/column *name* (only values), so the fix is a strict allowlist
    // applied before any identifier is ever interpolated into SQL text — see
    // /Users/venkatv/.claude/plans/we-have-done-a-adaptive-stroustrup.md.
    public static class DynamicTableSqlHelper
    {
        private static readonly Regex ValidIdentifier = new("^[A-Za-z_][A-Za-z0-9_]{0,63}$", RegexOptions.Compiled);

        // Not exhaustive — covers the reserved words most likely to appear as a field `key`
        // (form-authored names tend to be things like "select", "order", "group"). Anything
        // not on this list still has to pass ValidIdentifier above.
        private static readonly HashSet<string> ReservedWords = new(StringComparer.OrdinalIgnoreCase)
        {
            "select", "insert", "update", "delete", "table", "from", "where", "order", "group",
            "by", "join", "user", "column", "index", "primary", "key", "default", "null",
            "and", "or", "not", "values", "into", "create", "alter", "drop", "grant", "revoke",
            // D_<Code>'s own system columns (see FormTemplateDAL.CreateOrAlterDynamicTable) —
            // a field key literally named one of these would otherwise silently no-op (treated
            // as "already exists") instead of being rejected as a real naming conflict.
            "id", "templatedataid"
        };

        public static string ValidateTableIdentifier(string? value, string fieldNameForError)
        {
            var validated = ValidateColumnIdentifier(value, fieldNameForError);
            return "D_" + validated;
        }

        // Validates a bare identifier (a FormTemplateCode or a field key) intended to become
        // part of a table or column name. Throws ValidationException (never silently truncates
        // or "fixes" the value) so a rejected identifier is a visible error to the form author,
        // not a silently-missing column the way the legacy code behaves.
        public static string ValidateColumnIdentifier(string? value, string fieldNameForError)
        {
            if (string.IsNullOrWhiteSpace(value))
                throw new ValidationException($"{fieldNameForError} is required and cannot be blank.");

            if (!ValidIdentifier.IsMatch(value))
                throw new ValidationException(
                    $"{fieldNameForError} '{value}' is not a valid identifier — only letters, digits, and " +
                    "underscore are allowed, and it must start with a letter or underscore (max 64 chars).");

            if (ReservedWords.Contains(value))
                throw new ValidationException(
                    $"{fieldNameForError} '{value}' is a reserved SQL keyword and cannot be used as a table/column name.");

            return value;
        }

        // Maps gbmetaform's IMetaField.type vocabulary to a column type, per DB dialect. Wider
        // than the legacy 3-bucket map (text/number/tinyint only) — adds date/datetime and
        // distinguishes short text from long/multiline text.
        public static string MapFieldTypeToSqlType(string? metaFieldType, bool isPostgres)
        {
            return (metaFieldType ?? "").ToLowerInvariant() switch
            {
                "checkbox" or "boolean" or "radio" or "selectboxes"
                    => isPostgres ? "BOOLEAN" : "BIT",
                "number" or "numeric"
                    => "NUMERIC(18,4)",
                "date" or "datetime"
                    => isPostgres ? "TIMESTAMP" : "DATETIME2",
                "textarea" or "richtext" or "longtext"
                    => isPostgres ? "TEXT" : "NVARCHAR(MAX)",
                // text, select/combobox/picklist (stores the selected key), and anything
                // unrecognized default to a short text column.
                _ => isPostgres ? "VARCHAR(200)" : "NVARCHAR(200)"
            };
        }

        // Minimal, tolerant parse of an IMetaForm JSON document — only pulls what dynamic-table
        // DDL/DML needs (fieldId/Name + type), walking pages -> sections -> fields. Grid/table
        // fields are intentionally skipped (out of scope for v1 — a grid's rows don't map to a
        // single flat row in D_<Code>; flagged as a known limitation, not silently mishandled).
        public static List<MetaFormFieldDescriptor> ExtractScalarFields(string metaFormJson)
        {
            var result = new List<MetaFormFieldDescriptor>();
            if (string.IsNullOrWhiteSpace(metaFormJson)) return result;

            var opts = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
            var form = JsonSerializer.Deserialize<MetaFormJson>(metaFormJson, opts);
            if (form?.Pages == null) return result;

            foreach (var page in form.Pages)
            {
                if (page.Sections == null) continue;
                foreach (var section in page.Sections)
                {
                    if (section.Fields == null) continue;
                    foreach (var field in section.Fields)
                    {
                        var key = field.FieldId ?? field.Name;
                        if (string.IsNullOrWhiteSpace(key)) continue;
                        result.Add(new MetaFormFieldDescriptor { FieldId = key, Type = field.Type });
                    }
                }
            }
            return result;
        }

        public sealed class MetaFormFieldDescriptor
        {
            public string FieldId { get; set; } = "";
            public string? Type { get; set; }
        }

        private sealed class MetaFormJson
        {
            public List<MetaPageJson>? Pages { get; set; }
        }

        private sealed class MetaPageJson
        {
            public List<MetaSectionJson>? Sections { get; set; }
        }

        private sealed class MetaSectionJson
        {
            public List<MetaFieldJson>? Fields { get; set; }
        }

        private sealed class MetaFieldJson
        {
            public string? FieldId { get; set; }
            public string? Name { get; set; }
            public string? Type { get; set; }
        }
    }
}