using System.Diagnostics;
using Admin.NET.Core;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.Infrastructure;

/// <summary>
/// 一次性迁移：将历史 TenantId=1 的 AiDOP 业务数据更新为框架默认租户。
/// 幂等——若已无 TenantId=1 的行则不做任何事。
/// </summary>
public static class AidopTenantMigration
{
    private static readonly (string Table, string TenantColumn)[] Tables =
    {
        ("ado_smart_ops_kpi_master", "TenantId"),
        ("ado_smart_ops_layout_item", "TenantId"),
        ("ado_smart_ops_home_module", "TenantId"),
        ("ado_s9_kpi_value_l1_day", "tenant_id"),
        ("ado_s9_kpi_value_l2_day", "tenant_id"),
        ("ado_s9_kpi_value_l3_day", "tenant_id"),
        ("ado_s9_kpi_value_l4_day", "tenant_id"),
        ("ado_smart_ops_kpi_atomic_day", "tenant_id")
    };

    public static void MigrateOldTenantId(ISqlSugarClient db)
    {
        EnsureKpiValueL4Table(db);
        EnsureKpiAtomicTable(db);

        var target = SqlSugarConst.DefaultTenantId;
        if (target == 1) return;

        foreach (var (table, tenantColumn) in Tables)
        {
            try
            {
                db.Ado.ExecuteCommand(
                    $"UPDATE `{table}` SET `{tenantColumn}` = @target WHERE `{tenantColumn}` = 1",
                    new { target });
            }
            catch (Exception ex)
            {
                Trace.TraceWarning($"AidopTenantMigration [{table}]: {ex.Message}");
            }
        }

        DropOldUniqueIndex(db);
        BackfillThresholds(db);
    }

    public static void EnsureKpiValueL4Table(ISqlSugarClient db)
    {
        try
        {
            db.Ado.ExecuteCommand(
                "CREATE TABLE IF NOT EXISTS `ado_s9_kpi_value_l4_day` LIKE `ado_s9_kpi_value_l3_day`");
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration ensure L4 value table: {ex.Message}");
        }
    }

    public static void EnsureKpiAtomicTable(ISqlSugarClient db)
    {
        RepairKpiAtomicTableSchemaIfNeeded(db);

        try
        {
            db.Ado.ExecuteCommand(
                """
                CREATE TABLE IF NOT EXISTS ado_smart_ops_kpi_atomic_day (
                    id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
                    tenant_id BIGINT NOT NULL COMMENT '租户ID',
                    factory_id BIGINT NOT NULL DEFAULT 1 COMMENT '工厂ID',
                    domain_code VARCHAR(50) NOT NULL COMMENT '业务对象域',
                    metric_code VARCHAR(50) NOT NULL COMMENT '指标编码',
                    metric_level TINYINT NOT NULL DEFAULT 1 COMMENT '指标层级',
                    stat_date DATE NOT NULL COMMENT '统计日期',
                    customer_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '客户编码',
                    product_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产品编码',
                    order_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '订单号',
                    production_line VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产线',
                    supplier_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '供应商编码',
                    material_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '物料编码',
                    po_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '采购单号',
                    warehouse_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '仓库编码',
                    work_order_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '工单号',
                    equipment_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '设备编码',
                    outbound_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '出库单号',
                    sum_value DECIMAL(18, 6) NULL COMMENT '可累加值',
                    numerator_value DECIMAL(18, 6) NULL COMMENT '比率分子',
                    denominator_value DECIMAL(18, 6) NULL COMMENT '比率分母',
                    sample_count INT NULL COMMENT '样本数',
                    min_value DECIMAL(18, 6) NULL COMMENT '最小值',
                    max_value DECIMAL(18, 6) NULL COMMENT '最大值',
                    batch_id VARCHAR(100) NULL COMMENT 'MDP批次',
                    calc_time DATETIME(3) NULL COMMENT '计算时间',
                    grain VARCHAR(20) NOT NULL DEFAULT 'day' COMMENT '统计粒度',
                    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标识',
                    PRIMARY KEY (id),
                    UNIQUE KEY uk_atomic_grain (
                        tenant_id, factory_id, domain_code, metric_code, stat_date,
                        customer_code, product_code, order_no, production_line,
                        supplier_code, material_code, po_no, warehouse_code,
                        work_order_no, equipment_code, outbound_no
                    ),
                    KEY idx_tenant_domain_metric_date (tenant_id, domain_code, metric_code, stat_date),
                    KEY idx_tenant_module_lookup (tenant_id, factory_id, metric_code, stat_date)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI原子聚合层(日)'
                """);
            EnsureKpiAtomicColumnDefaults(db);
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration ensure KPI atomic table: {ex.Message}");
        }
    }

    /// <summary>
    /// 补齐维度列 DEFAULT ''，避免 CodeFirst 误建表后 INSERT 缺列报错。
    /// </summary>
    private static void EnsureKpiAtomicColumnDefaults(ISqlSugarClient db)
    {
        try
        {
            var columns = db.Ado.SqlQuery<KpiAtomicColumnState>(
                """
                SELECT COLUMN_NAME AS Name, COLUMN_DEFAULT AS DefaultValue, IS_NULLABLE AS IsNullable
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_smart_ops_kpi_atomic_day'
                """);
            var clauses = BuildKpiAtomicDefaultAlterClauses(columns);
            if (clauses.Count == 0) return;

            db.Ado.ExecuteCommand(
                $"ALTER TABLE ado_smart_ops_kpi_atomic_day {string.Join(", ", clauses)}");
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration ensure KPI atomic column defaults: {ex.Message}");
        }
    }

    internal static IReadOnlyList<string> BuildKpiAtomicDefaultAlterClauses(
        IEnumerable<KpiAtomicColumnState> columns)
    {
        var actual = columns.ToDictionary(x => x.Name, StringComparer.OrdinalIgnoreCase);
        var expected = new (string Name, string DefaultValue, string Clause)[]
        {
            ("customer_code", "", "MODIFY customer_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '客户编码'"),
            ("product_code", "", "MODIFY product_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产品编码'"),
            ("order_no", "", "MODIFY order_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '订单号'"),
            ("production_line", "", "MODIFY production_line VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产线'"),
            ("supplier_code", "", "MODIFY supplier_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '供应商编码'"),
            ("material_code", "", "MODIFY material_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '物料编码'"),
            ("po_no", "", "MODIFY po_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '采购单号'"),
            ("warehouse_code", "", "MODIFY warehouse_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '仓库编码'"),
            ("work_order_no", "", "MODIFY work_order_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '工单号'"),
            ("equipment_code", "", "MODIFY equipment_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '设备编码'"),
            ("outbound_no", "", "MODIFY outbound_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '出库单号'"),
            ("grain", "day", "MODIFY grain VARCHAR(20) NOT NULL DEFAULT 'day' COMMENT '统计粒度'"),
            ("is_deleted", "0", "MODIFY is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标识'")
        };

        return expected
            .Where(item =>
                !actual.TryGetValue(item.Name, out var column) ||
                !string.Equals(column.IsNullable, "NO", StringComparison.OrdinalIgnoreCase) ||
                !string.Equals(column.DefaultValue ?? "", item.DefaultValue, StringComparison.OrdinalIgnoreCase))
            .Select(item => item.Clause)
            .ToList();
    }

    /// <summary>
    /// 早期 SqlSugar 误建 PascalCase 列名时，DROP 后由 EnsureKpiAtomicTable 按 snake_case 重建。
    /// </summary>
    private static void RepairKpiAtomicTableSchemaIfNeeded(ISqlSugarClient db)
    {
        try
        {
            var columns = db.Ado.SqlQuery<string>(
                """
                SELECT COLUMN_NAME
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'ado_smart_ops_kpi_atomic_day'
                """);

            if (columns == null || columns.Count == 0)
                return;

            var hasSnakeDomain = columns.Any(c => string.Equals(c, "domain_code", StringComparison.OrdinalIgnoreCase));
            var hasPascalDomain = columns.Any(c => string.Equals(c, "DomainCode", StringComparison.OrdinalIgnoreCase));
            if (hasSnakeDomain || !hasPascalDomain)
                return;

            Trace.TraceWarning("AidopTenantMigration: ado_smart_ops_kpi_atomic_day 检测到 PascalCase 列名，将 DROP 后重建为 snake_case。");
            db.Ado.ExecuteCommand("DROP TABLE ado_smart_ops_kpi_atomic_day");
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration repair KPI atomic schema: {ex.Message}");
        }
    }

    private static void BackfillThresholds(ISqlSugarClient db)
    {
        try
        {
            db.Ado.ExecuteCommand(
                "UPDATE ado_smart_ops_kpi_master SET YellowThreshold=95, RedThreshold=80 " +
                "WHERE YellowThreshold IS NULL AND Direction='higher_is_better'");
            db.Ado.ExecuteCommand(
                "UPDATE ado_smart_ops_kpi_master SET YellowThreshold=110, RedThreshold=120 " +
                "WHERE YellowThreshold IS NULL AND Direction='lower_is_better'");
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration backfill thresholds: {ex.Message}");
        }
    }

    private static void DropOldUniqueIndex(ISqlSugarClient db)
    {
        try
        {
            var rows = db.Ado.SqlQuery<dynamic>(
                "SHOW INDEX FROM ado_smart_ops_kpi_master WHERE Key_name = 'uk_kpi_master_code'");
            if (rows != null && rows.Count > 0)
                db.Ado.ExecuteCommand("ALTER TABLE ado_smart_ops_kpi_master DROP INDEX uk_kpi_master_code");
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration drop old index: {ex.Message}");
        }
    }
}

internal sealed class KpiAtomicColumnState
{
    public string Name { get; set; } = string.Empty;
    public string? DefaultValue { get; set; }
    public string IsNullable { get; set; } = string.Empty;
}