using System.Diagnostics;
using Admin.NET.Plugin.AiDOP.Entity.S8;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Infrastructure;

/// <summary>
/// CONFIG-MONITOR-DICT-READONLY-SEED-1：S8 监控对象/指标 baseline 种子。
/// 6 个对象 + 10 条指标，与原前端 BUSINESS_MONITOR_OPTIONS 一一对应。
/// 幂等：按 (tenant_id=0, factory_id=0, object_code/metric_code) Any() 检查后插入。
/// 已存在记录会按 SOURCE-MAPPING-1 打补丁：补 source_table / 修正 demo 字段映射。
/// RATIO 类指标 enabled=false + is_result_kpi=true（S9 结果 KPI 范畴），不进入演示路径。
/// </summary>
public static class AidopMonitorDictionarySeed
{
    private const string DefaultObjectIdField = "source_object_id";
    private const string DefaultObjectCodeField = "related_object_code";
    private const string DefaultObjectNameField = "related_object_name";

    /// <summary>CONFIG-MONITOR-DICT-SOURCE-TABLE-SCHEMA-1：DEMO 演示用单表桥接。</summary>
    private const string DemoSourceTable = "demo_test_order";

    /// <summary>CONFIG-MONITOR-SOURCE-TABLE-EXTEND-1：库存监控真实库表（5k+ 行 demo 数据）。</summary>
    private const string InventoryStockSourceTable = "ic_item_stock";

    public static void EnsureSeed(ISqlSugarClient db)
    {
        try
        {
            var ct = DateTime.Parse("2026-05-09 00:00:00");

            // 6 监控对象（仅 ORDER_DELIVERY/IQC_INSPECTION 桥接到 demo_test_order，便于 DATE/VR 真实 SQL 演示）
            EnsureObject(db, "ORDER_DELIVERY",         "ORDER",          "订单交付",      DemoSourceTable, 10, ct);
            EnsureObject(db, "ORDER_CHANGE",           "ORDER",          "订单变更",      null,            20, ct);
            EnsureObject(db, "PURCHASE_DELIVERY",      "PURCHASE_ORDER", "供应商交付",    null,            30, ct);
            EnsureObject(db, "IQC_INSPECTION",         "IQC",            "IQC 检验",      DemoSourceTable, 40, ct);
            EnsureObject(db, "WORK_ORDER_PRODUCTION",  "WORK_ORDER",     "生产工单",      null,                       50, ct);
            EnsureObject(db, "INVENTORY_STOCK",        "INVENTORY",      "库存",          InventoryStockSourceTable,  60, ct);

            // DATE 指标 enabled=true / is_result_kpi=false
            // ORDER_DUE_AT 走 demo_test_order：id/order_no 真实列名（覆盖默认 alias 占位）
            EnsureMetricDate(db, "ORDER_DUE_AT", "计划交付时间", "ORDER_DELIVERY",
                "分钟", "due_at", "status",
                objectIdCol: "id", objectCodeCol: "order_no", objectNameCol: "order_no",
                sortNo: 110, ct: ct);
            EnsureMetricDate(db, "PO_DUE_AT",    "计划到货时间", "PURCHASE_DELIVERY",
                "分钟", "due_at", "status", null, null, null, 310, ct);
            EnsureMetricDate(db, "WO_DUE_AT",    "计划完工时间", "WORK_ORDER_PRODUCTION",
                "分钟", "due_at", "status", null, null, null, 510, ct);

            // VALUE_RANGE 指标 enabled=true / is_result_kpi=false
            // IQC_VALUE 走 demo_test_order：measured_size 是 demo 实际列；id/order_no 同上
            EnsureMetricValueRange(db, "ORDER_CHANGE_COUNT", "订单变更次数", "ORDER_CHANGE",
                "次", "measured_value", null, null, null, 210, ct);
            EnsureMetricValueRange(db, "IQC_VALUE",          "检验值",       "IQC_INSPECTION",
                null, "measured_size",
                objectIdCol: "id", objectCodeCol: "order_no", objectNameCol: "order_no",
                sortNo: 410, ct: ct);
            // INV_QTY 走 ic_item_stock：sqty 是当前库存量；Id/icitem_number/icitem_name 真实列
            EnsureMetricValueRange(db, "INV_QTY",            "当前库存量",   "INVENTORY_STOCK",
                null, "sqty",
                objectIdCol: "Id", objectCodeCol: "icitem_number", objectNameCol: "icitem_name",
                sortNo: 610, ct: ct);

            // RATIO 指标 enabled=false / is_result_kpi=true（S9 结果 KPI 范畴；本轮 seed 入库但默认禁用）
            EnsureMetricRatio(db, "ORDER_DELIVERY_RATE", "订单交付满足率", "ORDER_DELIVERY",        "%", "measured_value", 120, ct);
            EnsureMetricRatio(db, "PO_DELIVERY_RATE",    "到货达成率",     "PURCHASE_DELIVERY",     "%", "measured_value", 320, ct);
            EnsureMetricRatio(db, "IQC_PASS_RATE",       "检验合格率",     "IQC_INSPECTION",        "%", "measured_value", 420, ct);
            EnsureMetricRatio(db, "WO_COMPLETION_RATE",  "工单完工率",     "WORK_ORDER_PRODUCTION", "%", "measured_value", 520, ct);
        }
        catch (Exception ex)
        {
            Trace.TraceWarning("AidopMonitorDictionarySeed: " + ex);
        }
    }

    private static void EnsureObject(ISqlSugarClient db, string objectCode, string objectType, string objectName, string? sourceTable, int sortNo, DateTime ct)
    {
        var existing = db.Queryable<AdoS8MonitorObject>()
            .First(x => x.TenantId == 0 && x.FactoryId == 0 && x.ObjectCode == objectCode);

        if (existing == null)
        {
            db.Insertable(new AdoS8MonitorObject
            {
                TenantId = 0,
                FactoryId = 0,
                ObjectCode = objectCode,
                ObjectType = objectType,
                ObjectName = objectName,
                SourceTable = sourceTable,
                Enabled = true,
                SortNo = sortNo,
                CreatedAt = ct,
            }).ExecuteCommand();
            return;
        }

        // 已存在记录：仅当 source_table 为空时回填，避免覆盖运维已修改的值
        if (string.IsNullOrWhiteSpace(existing.SourceTable) && !string.IsNullOrWhiteSpace(sourceTable))
        {
            db.Updateable<AdoS8MonitorObject>()
                .SetColumns(x => x.SourceTable == sourceTable)
                .SetColumns(x => x.UpdatedAt == DateTime.Now)
                .Where(x => x.Id == existing.Id)
                .ExecuteCommand();
        }
    }

    private static void EnsureMetricDate(ISqlSugarClient db, string metricCode, string metricName, string objectCode, string? unit, string dueAtField, string statusField, string? objectIdCol, string? objectCodeCol, string? objectNameCol, int sortNo, DateTime ct)
    {
        var existing = db.Queryable<AdoS8MonitorMetric>()
            .First(x => x.TenantId == 0 && x.FactoryId == 0 && x.MetricCode == metricCode);

        if (existing == null)
        {
            db.Insertable(new AdoS8MonitorMetric
            {
                TenantId = 0,
                FactoryId = 0,
                ObjectCode = objectCode,
                MetricCode = metricCode,
                MetricName = metricName,
                Mechanism = "DATE",
                Unit = unit,
                DueAtField = dueAtField,
                StatusField = statusField,
                ObjectIdField = objectIdCol ?? DefaultObjectIdField,
                ObjectCodeField = objectCodeCol ?? DefaultObjectCodeField,
                ObjectNameField = objectNameCol ?? DefaultObjectNameField,
                IsResultKpi = false,
                Enabled = true,
                SortNo = sortNo,
                CreatedAt = ct,
            }).ExecuteCommand();
            return;
        }

        BackfillMetricColumns(db, existing, objectIdCol, objectCodeCol, objectNameCol);
    }

    private static void EnsureMetricValueRange(ISqlSugarClient db, string metricCode, string metricName, string objectCode, string? unit, string measuredValueField, string? objectIdCol, string? objectCodeCol, string? objectNameCol, int sortNo, DateTime ct)
    {
        var existing = db.Queryable<AdoS8MonitorMetric>()
            .First(x => x.TenantId == 0 && x.FactoryId == 0 && x.MetricCode == metricCode);

        if (existing == null)
        {
            db.Insertable(new AdoS8MonitorMetric
            {
                TenantId = 0,
                FactoryId = 0,
                ObjectCode = objectCode,
                MetricCode = metricCode,
                MetricName = metricName,
                Mechanism = "VALUE_RANGE",
                Unit = unit,
                MeasuredValueField = measuredValueField,
                ObjectIdField = objectIdCol ?? DefaultObjectIdField,
                ObjectCodeField = objectCodeCol ?? DefaultObjectCodeField,
                ObjectNameField = objectNameCol ?? DefaultObjectNameField,
                IsResultKpi = false,
                Enabled = true,
                SortNo = sortNo,
                CreatedAt = ct,
            }).ExecuteCommand();
            return;
        }

        BackfillMetricColumns(db, existing, objectIdCol, objectCodeCol, objectNameCol, measuredValueField);
    }

    private static void EnsureMetricRatio(ISqlSugarClient db, string metricCode, string metricName, string objectCode, string? unit, string measuredValueField, int sortNo, DateTime ct)
    {
        if (db.Queryable<AdoS8MonitorMetric>()
            .Any(x => x.TenantId == 0 && x.FactoryId == 0 && x.MetricCode == metricCode)) return;

        db.Insertable(new AdoS8MonitorMetric
        {
            TenantId = 0,
            FactoryId = 0,
            ObjectCode = objectCode,
            MetricCode = metricCode,
            MetricName = metricName,
            Mechanism = "RATIO",
            Unit = unit,
            MeasuredValueField = measuredValueField,
            ObjectIdField = DefaultObjectIdField,
            ObjectCodeField = DefaultObjectCodeField,
            ObjectNameField = DefaultObjectNameField,
            IsResultKpi = true,
            Enabled = false,
            SortNo = sortNo,
            CreatedAt = ct,
            Remark = "[RESERVED] S9 result KPI; enable explicitly when S8/S9 boundary task lands",
        }).ExecuteCommand();
    }

    /// <summary>已存在指标行：仅在原值仍是默认 alias 占位时回填真实列名，避免覆盖运维改过的字段。</summary>
    private static void BackfillMetricColumns(ISqlSugarClient db, AdoS8MonitorMetric existing, string? objectIdCol, string? objectCodeCol, string? objectNameCol, string? measuredValueCol = null)
    {
        var changed = false;

        if (!string.IsNullOrWhiteSpace(objectIdCol)
            && (string.IsNullOrWhiteSpace(existing.ObjectIdField) || existing.ObjectIdField == DefaultObjectIdField))
        {
            existing.ObjectIdField = objectIdCol;
            changed = true;
        }
        if (!string.IsNullOrWhiteSpace(objectCodeCol)
            && (string.IsNullOrWhiteSpace(existing.ObjectCodeField) || existing.ObjectCodeField == DefaultObjectCodeField))
        {
            existing.ObjectCodeField = objectCodeCol;
            changed = true;
        }
        if (!string.IsNullOrWhiteSpace(objectNameCol)
            && (string.IsNullOrWhiteSpace(existing.ObjectNameField) || existing.ObjectNameField == DefaultObjectNameField))
        {
            existing.ObjectNameField = objectNameCol;
            changed = true;
        }
        if (!string.IsNullOrWhiteSpace(measuredValueCol)
            && (string.IsNullOrWhiteSpace(existing.MeasuredValueField) || existing.MeasuredValueField == "measured_value"))
        {
            // 仅当原值是默认占位 measured_value 时才覆盖
            if (existing.MeasuredValueField != measuredValueCol)
            {
                existing.MeasuredValueField = measuredValueCol;
                changed = true;
            }
        }

        if (!changed) return;

        existing.UpdatedAt = DateTime.Now;
        db.Updateable(existing).ExecuteCommand();
    }
}