using Newtonsoft.Json.Linq;
using System.Collections;
using System.Data;
using System.Net;
using System.Text;
using System.Text.Json.Nodes;

namespace Tango.Portal.Chat.Web.Utils
{
    public static class DataHelper
    {
        /// <summary>
        /// Converts a DataTable into a robust Markdown table string.
        /// - HTML-encodes (&, <, >) to avoid HTML injection.
        /// - Replaces '|' with &#124; so it doesn't break cell boundaries.
        /// - Normalizes CR/LF and turns newlines into <br/> so multi-line cells stay in-row.
        /// - Replaces tabs with spaces.
        /// - Optionally truncates very long cells.
        /// </summary>
        public static string ToMarkdownTable(DataTable table, int maxCellChars = 100)
        {
            if (table == null || table.Columns.Count == 0) return string.Empty;

            string Esc(string? s)
            {
                if (string.IsNullOrEmpty(s)) return string.Empty;

                // HTML-encode first (safer in Markdown renderers that allow HTML)
                string t = WebUtility.HtmlEncode(s);

                // Normalize newlines and tabs
                t = t.Replace("\r\n", "\n").Replace("\r", "\n");
                t = t.Replace("\t", "    ");
                t = t.Replace("\n", "<br/>");

                // Escape Markdown table pipes via HTML entity (more reliable than backslash)
                t = t.Replace("|", "&#124;");

                if (maxCellChars > 0 && t.Length > maxCellChars)
                    t = t.Substring(0, maxCellChars) + "…";

                return t;
            }

            var sb = new StringBuilder();

            // Header row
            for (int c = 0; c < table.Columns.Count; c++)
                sb.Append("| ").Append(Esc(table.Columns[c].ColumnName)).Append(' ');
            sb.AppendLine("|");

            // Separator row
            for (int c = 0; c < table.Columns.Count; c++)
                sb.Append("|---");
            sb.AppendLine("|");

            // Data rows
            foreach (DataRow row in table.Rows)
            {
                for (int c = 0; c < table.Columns.Count; c++)
                    sb.Append("| ").Append(Esc(row[c]?.ToString())).Append(' ');
                sb.AppendLine("|");
            }

            return sb.ToString();
        }

        public static object ToPreview(DataTable dt, int maxRows)
        {
            var cols = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToArray();
            var rows = new List<Dictionary<string, object?>>();
            int count = 0;

            foreach (DataRow r in dt.Rows)
            {
                if (count++ >= maxRows) break;

                var d = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
                foreach (var c in cols)
                {
                    var cell = r[c];
                    d[c] = NormalizeCell(cell);
                }
                rows.Add(d);
            }

            // (Optional but helpful) include the full row count
            return new { columns = cols, rows, totalRows = dt.Rows.Count };
        }

        private static object? NormalizeCell(object? v)
        {
            if (v is null || v is DBNull) return null;

            // Newtonsoft JToken -> System.Text.Json node
            if (v is JToken jt)
            {
                // Preserves arrays/objects rather than stringifying
                return JsonNode.Parse(jt.ToString(Newtonsoft.Json.Formatting.None));
            }

            // Parse JSON strings (dynamic often arrives as text)
            if (v is string s)
            {
                s = s.Trim();
                if ((s.StartsWith("[") && s.EndsWith("]")) || (s.StartsWith("{") && s.EndsWith("}")))
                {
                    try { return JsonNode.Parse(s); } catch { /* fall through */ }
                }
                return s; // plain string
            }

            // Flatten enumerables (but not strings)
            if (v is IEnumerable en && v is not string)
            {
                var list = new List<object?>();
                foreach (var item in en) list.Add(NormalizeCell(item));
                return list;
            }

            // Optional: normalize DateTime -> ISO 8601 for consistency
            if (v is DateTime dt) return dt.ToUniversalTime().ToString("o");

            // Numbers, bools, etc. pass through
            return v;
        }
    }
}