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
        {
            db.Ado.ExecuteCommand(
                """
                ALTER TABLE ado_smart_ops_kpi_atomic_day
                    MODIFY customer_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '客户编码',
                    MODIFY product_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产品编码',
                    MODIFY order_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '订单号',
                    MODIFY production_line VARCHAR(100) NOT NULL DEFAULT '' COMMENT '产线',
                    MODIFY supplier_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '供应商编码',
                    MODIFY material_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '物料编码',
                    MODIFY po_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '采购单号',
                    MODIFY warehouse_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '仓库编码',
                    MODIFY work_order_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '工单号',
                    MODIFY equipment_code VARCHAR(100) NOT NULL DEFAULT '' COMMENT '设备编码',
                    MODIFY outbound_no VARCHAR(100) NOT NULL DEFAULT '' COMMENT '出库单号',
                    MODIFY grain VARCHAR(20) NOT NULL DEFAULT 'day' COMMENT '统计粒度',
                    MODIFY is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标识'
                """);
        }
        catch (Exception ex)
        {
            Trace.TraceWarning($"AidopTenantMigration ensure KPI atomic column defaults: {ex.Message}");
        }
    }

    /// <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}");
        }
    }
}