using System.Text.Json;
using System.Text.RegularExpressions;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;

namespace Admin.NET.Plugin.AiDOP.DataPlatform;

public sealed record MdpConfigIssue(string Code, string Field, string Message);

public sealed record MdpMappedRow(Dictionary<string, object?> Values, IReadOnlyList<string> Errors);

public static class MdpDbSyncRules
{
    public const long SourceMenuId = 1320990000403L;
    public const long SyncTaskMenuId = 1320990000404L;
    public const long SyncLogMenuId = 1320990000405L;
    public static readonly string[] StagingColumns =
    [
        "tenant_id", "source_system", "source_table", "source_row_id", "source_biz_key",
        "raw_data", "sync_batch_id", "sync_time", "process_status", "create_time", "update_time"
    ];

    private static readonly Regex Identifier = new("^[A-Za-z_][A-Za-z0-9_]*$", RegexOptions.Compiled);
    private static readonly Regex SourceTable = new("^[A-Za-z0-9_\\.\\[\\]]+$", RegexOptions.Compiled);
    private static readonly Regex DotPath = new("^[A-Za-z_][A-Za-z0-9_]*(\\.[A-Za-z_][A-Za-z0-9_]*)*$", RegexOptions.Compiled);

    public static bool IsSupportedDbType(string? dbType)
    {
        var value = (dbType ?? "").Trim().ToUpperInvariant();
        return value is "SQLSERVER" or "MSSQL" or "MYSQL";
    }

    public static string? RequireIdentifier(string? value, string field)
    {
        var text = (value ?? "").Trim();
        if (!Identifier.IsMatch(text))
            throw new InvalidOperationException($"非法 {field}：{value}");
        return text;
    }

    public static string RequireSourceTable(string? value)
    {
        var text = (value ?? "").Trim();
        if (!SourceTable.IsMatch(text))
            throw new InvalidOperationException($"非法 source_table_name：{value}");
        return text;
    }

    public static IReadOnlyList<MdpConfigIssue> ValidateDbEntity(MdpEntity entity, string? sourceType)
    {
        var issues = new List<MdpConfigIssue>();
        if (!string.Equals(sourceType, "DB", StringComparison.OrdinalIgnoreCase))
            return issues;
        void Add(string code, string field, string message) => issues.Add(new MdpConfigIssue(code, field, message));

        if (string.IsNullOrWhiteSpace(entity.SourceTableName))
            Add("SOURCE_TABLE_MISSING", "sourceTableName", "源表或视图不能为空");
        else if (!SourceTable.IsMatch(entity.SourceTableName.Trim()))
            Add("SOURCE_TABLE_INVALID", "sourceTableName", "源表名含非法字符");
        if (!string.IsNullOrWhiteSpace(entity.TransformSourceTable)
            && !SourceTable.IsMatch(entity.TransformSourceTable.Trim()))
            Add("TRANSFORM_SOURCE_TABLE_INVALID", "transformSourceTable", "转换逻辑表名含非法字符");
        if (string.IsNullOrWhiteSpace(entity.TargetTableName))
            Add("TARGET_TABLE_MISSING", "targetTableName", "目标贴源表不能为空");
        else if (!Identifier.IsMatch(entity.TargetTableName.Trim()))
            Add("TARGET_TABLE_INVALID", "targetTableName", "目标贴源表名含非法字符");

        var mode = (entity.SyncMode ?? "").Trim().ToUpperInvariant();
        if (mode is not ("FULL" or "INCR" or "ROLLING"))
            Add("SYNC_MODE_INVALID", "syncMode", "同步模式只能是 FULL、INCR 或 ROLLING");
        if (string.IsNullOrWhiteSpace(entity.BizKeyExpr))
            Add("BIZ_KEY_MISSING", "bizKeyExpr", "业务键不能为空");
        if (entity.BatchSize is < 1 or > 10000)
            Add("BATCH_SIZE_INVALID", "batchSize", "批大小必须在 1 到 10000 之间");
        if (string.IsNullOrWhiteSpace(entity.TieBreakerColumn) || !Identifier.IsMatch(entity.TieBreakerColumn.Trim()))
            Add("TIE_BREAKER_MISSING", "tieBreakerColumn", "稳定排序列不能为空且只能是字段名");
        if (mode is "INCR" or "ROLLING" && (string.IsNullOrWhiteSpace(entity.IncrColumn) || !Identifier.IsMatch(entity.IncrColumn.Trim())))
            Add("INCR_COLUMN_MISSING", "incrColumn", "增量同步必须填写增量列");
        return issues;
    }

    public static MdpMappedRow Apply(IDictionary<string, object?> source, IReadOnlyList<MdpFieldMapping> mappings)
    {
        var values = new Dictionary<string, object?>(source, StringComparer.OrdinalIgnoreCase);
        var errors = new List<string>();
        foreach (var map in mappings.OrderBy(x => x.SortOrder).ThenBy(x => x.Id))
        {
            var type = (map.FieldType ?? "DIRECT").Trim().ToUpperInvariant();
            object? value = null;
            switch (type)
            {
                case "DIRECT":
                    values.TryGetValue(map.SourceField, out value);
                    break;
                case "CONST":
                    value = map.ConstValue;
                    break;
                case "JSONPATH":
                    if (!values.TryGetValue(map.SourceField, out var raw) || raw == null)
                    {
                        errors.Add($"{map.TargetField} 缺少 JSON 源字段");
                        continue;
                    }
                    var path = string.IsNullOrWhiteSpace(map.TransformScript) ? map.SourceField : map.TransformScript.Trim();
                    if (!DotPath.IsMatch(path))
                    {
                        errors.Add($"{map.TargetField} 的 JSON 路径不合法");
                        continue;
                    }
                    value = ReadDotPath(raw.ToString(), path, out var pathError);
                    if (pathError != null)
                    {
                        errors.Add($"{map.TargetField} {pathError}");
                        continue;
                    }
                    break;
                case "SCRIPT":
                case "LOOKUP":
                    errors.Add($"{map.TargetField} 使用了数据库同步不支持的映射类型 {type}");
                    continue;
                default:
                    errors.Add($"{map.TargetField} 使用了未知映射类型 {type}");
                    continue;
            }

            if (IsEmpty(value) && !string.IsNullOrWhiteSpace(map.DefaultValue))
                value = map.DefaultValue;
            values[map.TargetField] = value;
            if (map.IsRequired == 1 && IsEmpty(value))
                errors.Add($"{map.TargetField} 为必填");
        }
        return new MdpMappedRow(values, errors);
    }

    public static string Redact(string? text)
    {
        if (string.IsNullOrEmpty(text)) return "";
        var value = Regex.Replace(text, @"\b1[3-9]\d{9}\b", "***");
        value = Regex.Replace(value, @"[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}", "***");
        value = Regex.Replace(value, @"\b\d{17}[\dXx]\b", "***");
        return value.Length > 300 ? value[..300] : value;
    }

    public static string? BuildJobId(long tenantId, string taskCode)
    {
        var token = Regex.Replace((taskCode ?? "").Trim().ToUpperInvariant(), "[^A-Z0-9]", "_");
        var jobId = $"job_mdp_dbsync_{tenantId}_{token}";
        return jobId.Length <= 64 ? jobId : null;
    }

    public static string? BuildTriggerId(long tenantId, string taskCode)
    {
        var jobId = BuildJobId(tenantId, taskCode);
        if (jobId == null) return null;
        var triggerId = "trigger_" + jobId["job_".Length..];
        return triggerId.Length <= 64 ? triggerId : null;
    }

    private static object? ReadDotPath(string? json, string path, out string? error)
    {
        error = null;
        try
        {
            using var doc = JsonDocument.Parse(string.IsNullOrWhiteSpace(json) ? "null" : json);
            JsonElement current = doc.RootElement;
            foreach (var part in path.Split('.'))
            {
                if (current.ValueKind != JsonValueKind.Object || !current.TryGetProperty(part, out current))
                {
                    error = "JSON 路径不存在";
                    return null;
                }
            }
            return current.ValueKind switch
            {
                JsonValueKind.String => current.GetString(),
                JsonValueKind.Number => current.TryGetInt64(out var n) ? n : current.GetDecimal(),
                JsonValueKind.True => true,
                JsonValueKind.False => false,
                JsonValueKind.Null => null,
                _ => current.GetRawText()
            };
        }
        catch (JsonException)
        {
            error = "源字段不是合法 JSON";
            return null;
        }
    }

    private static bool IsEmpty(object? value) =>
        value == null || value == DBNull.Value || string.IsNullOrWhiteSpace(value.ToString());
}