AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/DataPlatform/S0Dim/S0DimCatalog.cs

namespace Admin.NET.Plugin.AiDOP.DataPlatform.S0Dim;

/// <summary>
/// S0 主数据维度清单 —— **本模块唯一的业务知识落点**。
///
/// 新增一个维度 = 在此追加一个 <see cref="S0DimDefinition"/>，
/// 不得在 Materializer / Reconciler 中出现按 Key 分支的逻辑。
///
/// 🔴 JsonPath 一律是**源表物理列名**，不是实体属性名。改动前先核对 <c>SHOW CREATE TABLE</c>。
/// </summary>
public static class S0DimCatalog
{
    /// <summary>本库样板源（<c>mdp_source.source_code</c>），入站为只读 SELECT。</summary>
    public const string LocalSourceSystem = "AIDOPDEV_MYSQL";

    /// <summary>
    /// 工作中心。源 <c>WorkCtrMaster</c>（无 company/factory 列，故 dim 也不设 —— 不为统一而发明数据）。
    /// </summary>
    public static readonly S0DimDefinition WorkCenter = new(
        Key: "WORK_CENTER",
        EntityCode: "S0_WORK_CENTER",
        SourceTable: "WorkCtrMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_work_center",
        DimTable: "dim_work_center",
        BusinessKeyColumns: ["tenant_id", "domain_code", "work_center_code"],
        SourceBizKeyColumns: ["Domain", "WorkCtr"],
        Columns:
        [
            new("tenant_id",         S0DimValueKind.TenantIdColumn),
            new("domain_code",       S0DimValueKind.Str,         "Domain",     Required: true),
            new("work_center_code",  S0DimValueKind.Str,         "WorkCtr",    Required: true),
            new("work_center_name",  S0DimValueKind.Str,         "Descr"),
            new("department_code",   S0DimValueKind.Str,         "Department"),
            new("is_active",         S0DimValueKind.BoolTrue,    "IsActive"),
            new("source_updated_at", S0DimValueKind.DateTimeSec, "UpdateTime")
        ]);

    /// <summary>
    /// 部门。源 <c>DepartmentMaster</c>。
    /// company/factory 仅作属性透传：实测同一 (tenant, Domain) 下 factory 同时存在 legacy 数值与 SysOrg 雪花两种值，
    /// 与身份正交，本阶段不做组织域治理。
    /// </summary>
    public static readonly S0DimDefinition Department = new(
        Key: "DEPARTMENT",
        EntityCode: "S0_DEPARTMENT",
        SourceTable: "DepartmentMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_department",
        DimTable: "dim_department",
        BusinessKeyColumns: ["tenant_id", "domain_code", "department_code"],
        SourceBizKeyColumns: ["Domain", "Department"],
        Columns:
        [
            new("tenant_id",         S0DimValueKind.TenantIdColumn),
            new("company_id",        S0DimValueKind.Int,         "company_ref_id", Required: true),
            new("factory_id",        S0DimValueKind.Int,         "factory_ref_id", Required: true),
            new("domain_code",       S0DimValueKind.Str,         "Domain",         Required: true),
            new("department_code",   S0DimValueKind.Str,         "Department",     Required: true),
            new("department_name",   S0DimValueKind.Str,         "Descr"),
            new("is_active",         S0DimValueKind.BoolTrue,    "IsActive"),
            // 源侧 238/239 非空：UATDEMO 的「未分配」行 UpdateTime 恒 NULL。
            // 本 definition 走 FULL（mdp_entity.incr_column = NULL），不生成 "UpdateTime > cursor" 谓词，
            // 因此该行每轮都能进 stg 与 dim；切 INCR 前必须先落 keyset NullTimePhase 两阶段。
            new("source_updated_at", S0DimValueKind.DateTimeSec, "UpdateTime")
        ]);

    /// <summary>
    /// 库位。源 <c>LocationMaster</c> —— **dual-column 复刻表**。
    ///
    /// 🔴 以下 JsonPath 全部是 PascalCase 真列，同表存在同名 snake_case 影子列且**恒空/已发散**，
    /// 取错即静默得到空值：
    /// <c>Domain</c>(真) vs <c>domain_code</c>(132 行全空串) ·
    /// <c>IsActive</c>(真) vs <c>is_active</c>(132 行中仅 1 行与真列相等) ·
    /// <c>PhysicalAddress</c>(42 非空) vs <c>physical_address</c>(0 非空) ·
    /// <c>CreateUser</c>(119 非空) vs <c>create_user</c>(0 非空) ·
    /// <c>RecID</c>(真主键) vs <c>rec_id</c>(恒 0)。
    ///
    /// 身份取 (tenant, domain_code, location_code)：与子表父引用一致，且在源侧索引口径变化时仍成立；
    /// 另用 <see cref="S0DimDefinition.MirrorUniqueColumns"/> 镜像当前源 UK (tenant_id, location)。
    /// </summary>
    public static readonly S0DimDefinition Location = new(
        Key: "LOCATION",
        EntityCode: "S0_LOCATION",
        SourceTable: "LocationMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_location",
        DimTable: "dim_location",
        BusinessKeyColumns: ["tenant_id", "domain_code", "location_code"],
        SourceBizKeyColumns: ["Domain", "location"],
        MirrorUniqueColumns: ["tenant_id", "location_code"],
        Columns:
        [
            new("tenant_id",         S0DimValueKind.TenantIdColumn),
            new("company_id",        S0DimValueKind.Int,         "company_ref_id",  Required: true),
            new("factory_id",        S0DimValueKind.Int,         "factory_ref_id",  Required: true),
            new("domain_code",       S0DimValueKind.Str,         "Domain",          Required: true),
            new("location_code",     S0DimValueKind.Str,         "location",        Required: true),
            new("location_name",     S0DimValueKind.Str,         "descr"),
            new("location_type",     S0DimValueKind.Str,         "typed"),
            new("storer",            S0DimValueKind.Str,         "storer"),
            new("physical_address",  S0DimValueKind.Str,         "PhysicalAddress"),
            new("is_active",         S0DimValueKind.BoolTrue,    "IsActive"),
            new("source_updated_at", S0DimValueKind.DateTimeSec, "UpdateTime")
        ]);

    /// <summary>
    /// 货架（库位子维度）。源 <c>LocationShelfMaster</c>。
    ///
    /// 与 <see cref="Location"/> 共用 <c>mdp_stg_s0_location</c>，靠 <c>source_table</c> 隔离，
    /// 两者是**两个独立 definition**，绝不合并成一次转换。
    ///
    /// 本表**全列 snake_case，没有影子列** —— 不可套用 LocationMaster 的禁用规则。
    /// 另：源 <c>update_time</c> 0/30 非空（无水位）、主键 <c>rec_id</c> 不在通用行标识候选列表中
    /// 且每次父表 FULL Replace 都会变，故物化只认 <c>sync_batch_id</c>，不依赖 source_row_id 稳定性。
    /// 无 is_active —— 源表没有该列，不发明。
    /// </summary>
    public static readonly S0DimDefinition LocationShelf = new(
        Key: "LOCATION_SHELF",
        EntityCode: "S0_LOCATION_SHELF",
        SourceTable: "LocationShelfMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_location",
        DimTable: "dim_location_shelf",
        BusinessKeyColumns: ["tenant_id", "domain_code", "location_code", "shelf_code"],
        SourceBizKeyColumns: ["domain_code", "location", "inv_shelf"],
        ParentDimTable: "dim_location",
        ParentKeyColumns: ["domain_code", "location_code"],
        Columns:
        [
            new("tenant_id",         S0DimValueKind.TenantIdColumn),
            new("company_id",        S0DimValueKind.Int,         "company_ref_id", Required: true),
            new("factory_id",        S0DimValueKind.Int,         "factory_ref_id", Required: true),
            new("domain_code",       S0DimValueKind.Str,         "domain_code",    Required: true),
            new("location_code",     S0DimValueKind.Str,         "location",       Required: true),
            new("shelf_code",        S0DimValueKind.Str,         "inv_shelf",      Required: true),
            new("shelf_name",        S0DimValueKind.Str,         "descr"),
            new("area",              S0DimValueKind.Str,         "area"),
            // 源恒 NULL（0/30），保留列以便源将来启用；不得为了“看起来有值”而回填
            new("source_updated_at", S0DimValueKind.DateTimeSec, "update_time")
        ]);

    /// <summary>
    /// 雇员。源 <c>EmployeeMaster</c>（29 列，无影子列，2026-09-07 实测确认）。
    ///
    /// <para><b>身份 = tenant_id + Employee</b>。<c>Domain</c> 是属性，<b>不是</b>身份 ——
    /// 这条口径不是从旧物理唯一键推的，而是按「当前业务证据优先」重判的结果（2026-09-07）：</para>
    /// <list type="number">
    ///   <item>当前 CRUD 查重是 <c>tenant + FactoryRefId + Employee</c>
    ///         （<c>AdoS0EmployeesController</c>），**根本不含 Domain**；</item>
    ///   <item>其中 <c>FactoryRefId</c> 由 <c>AdoS0OrgScope.ResolveAsync(ctx, tenantId)</c>
    ///         **仅按租户**解析并强制 stamp（payload 值一律覆盖），是 tenant 的函数、零区分力，
    ///         故该查重等价于 <c>tenant + Employee</c>；</item>
    ///   <item>单条定位走 <c>ByIdScopedAsync(id, tenantId)</c>，不经 Domain；</item>
    ///   <item>真实 consumer 一致按工号关联：<c>LEFT JOIN EmployeeMaster e
    ///         ON p.Buyer = e.Employee AND e.tenant_id = p.tenant_id</c>
    ///         （DemandOrderService / OutsourceOrderService / ProcessOutsourceOrderService 共 5 处），
    ///         **无一处用 Domain**；</item>
    ///   <item>数据实测：<c>(tenant_id, Employee)</c> 租户内重复 = <b>0</b>，
    ///         且不存在「同租户同工号不同 Domain」的行。</item>
    /// </list>
    /// <para>租户仍必须入身份：<c>Employee</c> 跨租户碰撞实测 19 组 / 38 行
    /// （<c>G02795</c> / <c>ZHR01</c> / <c>RQTJ993</c> 等工号被多个租户各自使用）。
    /// 旧物理唯一键 <c>uk_EmployeeMaster_domain_employee</c> 含 Domain，属 legacy scope，
    /// 按 Owner 口径「不因存在于旧表/旧 UK 就自动成为 DIM identity」，**不予继承**。</para>
    ///
    /// <para>🔴 <c>sys_user_id</c> <b>只是属性，绝不参与业务键</b>：实测 1105 行中仅 <b>7 行</b>非空（0.6%），
    /// 它是「员工恰好也有系统账号」的可选链接，不是员工身份。把账号 id 当员工主键会让 99.4% 的员工无法标识。</para>
    ///
    /// <para>字段适用性分类：<c>employee_code</c>=CORE；<c>domain_code</c>=ATTRIBUTE
    /// （仍被部门父连接与 CRUD 的部门引用校验 <c>DepartmentExistsInTenantAsync(tenantId, domain, dept)</c> 使用）；
    /// <c>company_id</c>/<c>factory_id</c>=LEGACY_SCOPE（f(tenant)，保留仅为可追溯到 SysOrg 节点）；
    /// <c>sys_user_id</c>=可选 ATTRIBUTE，永远不入键。</para>
    /// </summary>
    public static readonly S0DimDefinition Employee = new(
        Key: "EMPLOYEE",
        EntityCode: "S0_EMPLOYEE",
        SourceTable: "EmployeeMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_employee",
        DimTable: "dim_employee",
        BusinessKeyColumns: ["tenant_id", "employee_code"],
        SourceBizKeyColumns: ["Employee"],
        ParentDimTable: "dim_department",
        ParentKeyColumns: ["domain_code", "department_code"],
        Columns:
        [
            new("tenant_id",          S0DimValueKind.TenantIdColumn),
            new("company_id",         S0DimValueKind.Int,         "company_ref_id", Required: true),
            new("factory_id",         S0DimValueKind.Int,         "factory_ref_id", Required: true),
            new("domain_code",        S0DimValueKind.Str,         "Domain",         Required: true),
            new("employee_code",      S0DimValueKind.Str,         "Employee",       Required: true),
            new("employee_name",      S0DimValueKind.Str,         "Name"),
            // 自由文本软引用：源 813 非空中约 97 在 DepartmentMaster 找不到，另 292 行未分配。
            // 未分配（NULL）不算孤儿，见 S0DimSqlBuilder.OrphanFromWhere。
            new("department_code",    S0DimValueKind.Str,         "Department"),
            new("job_title",          S0DimValueKind.Str,         "JobTitle"),
            new("work_center_code",   S0DimValueKind.Str,         "WorkCtr"),
            new("employment_status",  S0DimValueKind.Str,         "EmploymentStatus"),
            new("date_employed",      S0DimValueKind.DateTimeSec, "DateEmployed"),
            new("date_terminated",    S0DimValueKind.DateTimeSec, "DateTerminated"),
            // 稀疏（7/1105）。允许 NULL 透传，不得回填，也不得据此过滤行。
            new("sys_user_id",        S0DimValueKind.Int,         "sys_user_id"),
            new("is_active",          S0DimValueKind.BoolTrue,    "IsActive"),
            new("source_updated_at",  S0DimValueKind.DateTimeSec, "UpdateTime")
        ]);

    /// <summary>
    /// 生产线。源 <c>LineMaster</c>。
    ///
    /// <para><b>身份 = tenant_id + Line</b>。company / factory / Domain 全是属性，<b>不是</b>身份 ——
    /// 同样按「当前业务证据优先」重判（2026-09-07），旧物理唯一键
    /// <c>uk_LineMaster_scope_line(tenant_id, company_ref_id, factory_ref_id, Line)</c> 不予继承：</para>
    /// <list type="number">
    ///   <item>当前 CRUD <b>没有任何查重守卫</b>（<c>AdoS0MfgProductionLinesController</c> 无
    ///         <c>IsAnyAsync</c>/<c>DuplicateCode</c>），单条定位走 <c>ByIdScopedAsync(id, tenantId)</c>；</item>
    ///   <item><c>CompanyRefId</c>/<c>FactoryRefId</c> 在控制器里只是**可选查询筛选**
    ///         （<c>WhereIF(q.CompanyRefId.HasValue, ...)</c>），创建时由
    ///         <c>AdoS0OrgScope.ResolveAsync</c> 按租户强制 stamp，不是用户输入；</item>
    ///   <item>真实 consumer <c>S8MasterDataAdapter.GetLinesAsync</c> 是
    ///         <c>Where(x =&gt; x.TenantId == scope.TenantId).Select(code = x.Line)</c> —— 只有租户 + 产线码；</item>
    ///   <item>数据实测：四个候选（tenant+Line / +Domain / +factory / +company+factory）
    ///         租户内重复<b>全部为 0</b>；且**每个租户在本表只有 1 个 Domain、1 个 company、1 个 factory**
    ///         ⇒ 这三列在租户内是常量，<b>零区分力</b>。</item>
    /// </list>
    /// <para>租户仍必须入身份：<c>Line</c> 跨租户碰撞实测 20 组 / 80 行（如 <c>AE01</c> 在 4 个租户都存在）。</para>
    ///
    /// <para>字段适用性分类：<c>line_code</c>=CORE；<c>line_name</c>/<c>line_type</c>/<c>line_category</c>/
    /// <c>workshop</c>/<c>location_code</c>/<c>is_active</c>=ATTRIBUTE；
    /// <c>domain_code</c>/<c>company_id</c>/<c>factory_id</c>=LEGACY_SCOPE（租户内常量，保留仅为可追溯）。</para>
    ///
    /// <para>🔴 <c>Describe</c> 是真列（169/169 填充、63 distinct）；同表的 <c>line_describe</c>
    /// 是<b>恒空影子列</b>（0/169 填充），取错即静默得到全空的产线名称。
    /// 这是本仓第三例此型双列表（前两例：LocationMaster、StdOpMaster）。</para>
    ///
    /// <para>⚠️ <c>factory_ref_id</c> 参与身份，但**绝不能**设进 <c>MdpPullContext.FactoryId</c>：
    /// <c>MdpStagingWriter</c> 只识别 <c>factory_id</c>/<c>FactoryId</c> 两个列名，
    /// 本表叫 <c>factory_ref_id</c> → 解析为 null → 一旦 <c>ctx.FactoryId &gt; 0</c>，
    /// 比较基准会退化成 1 并让**整批行全部被跳过**（KNOWN-ISSUES I-015）。
    /// S0 的 pullCtx 只设 TenantId，该分支永不进入。</para>
    /// </summary>
    public static readonly S0DimDefinition Line = new(
        Key: "LINE",
        EntityCode: "S0_LINE",
        SourceTable: "LineMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_line",
        DimTable: "dim_line",
        BusinessKeyColumns: ["tenant_id", "line_code"],
        SourceBizKeyColumns: ["Line"],
        Columns:
        [
            new("tenant_id",         S0DimValueKind.TenantIdColumn),
            new("line_code",         S0DimValueKind.Str,         "Line",           Required: true),
            new("company_id",        S0DimValueKind.Int,         "company_ref_id", Required: true),
            new("factory_id",        S0DimValueKind.Int,         "factory_ref_id", Required: true),
            new("domain_code",       S0DimValueKind.Str,         "Domain",         Required: true),
            new("line_name",         S0DimValueKind.Str,         "Describe"),
            new("line_type",         S0DimValueKind.Str,         "LineType"),
            new("line_category",     S0DimValueKind.Str,         "LineCategory"),
            new("workshop",          S0DimValueKind.Str,         "Workshop"),
            new("location_code",     S0DimValueKind.Str,         "Location"),
            new("is_active",         S0DimValueKind.BoolTrue,    "IsActive"),
            new("source_updated_at", S0DimValueKind.DateTimeSec, "UpdateTime")
        ]);

    /// <summary>
    /// 客户。源 <c>CustMaster</c>（32 列，无影子列对；空列极多，只物化有语义且有数据的）。
    ///
    /// <para><b>身份 = tenant_id + Cust</b>。同样按「当前业务证据优先」重判：</para>
    /// <list type="number">
    ///   <item>CRUD 查重 <c>tenant + FactoryRefId + Cust</c>（<c>AdoS0CustomersController:70,117</c>），
    ///         而 <c>FactoryRefId</c> 由 <c>AdoS0OrgScope</c> 仅按租户解析并强制 stamp ⇒ 等价 <c>tenant + Cust</c>；</item>
    ///   <item><c>DomainCode</c> 在控制器里只是**可选列表筛选**（<c>WhereIF(...)</c>），不参与查重；</item>
    ///   <item>单条定位 <c>ByIdScopedAsync(id, tenantId)</c>；</item>
    ///   <item>数据实测：三个候选（tenant+Cust / +Domain / +factory）租户内重复**全部为 0**，
    ///         且 company/factory 在每个租户内均为**单一取值**（常量、零区分力）。</item>
    /// </list>
    /// <para>租户必须入身份：<c>Cust</c> 跨租户碰撞实测 <b>4 个编码</b>。</para>
    ///
    /// <para>🔴 源表另有一条 <b>不含 tenant_id</b> 的旧唯一键 <c>uk_CustMaster_factory_cust(factory_ref_id, Cust)</c>，
    /// 且 <c>factory_ref_id=1000</c> 被两个租户共用 —— 属源表治理缺陷（<c>KNOWN-ISSUES I-016</c>），
    /// **它不能反过来决定 DIM identity**；dim 侧自带 <c>tenant_id</c> 前缀，不受其影响。
    /// 本批不动源表 DDL。</para>
    ///
    /// <para>字段适用性：<c>customer_code</c>=CORE；<c>customer_name</c>/<c>is_active</c>/
    /// <c>is_confirmed</c>/<c>shipping_lead_time</c>=ATTRIBUTE；
    /// <c>domain_code</c>/<c>company_id</c>/<c>factory_id</c>=LEGACY_SCOPE。
    /// 全空列（address / contact / Curr / Salesperson1 / Terms / cust_class / shipping_address 等 13 列，
    /// 填充率 0/1333）与近空列（Class 7 · TaxClass 3 · CustFullName 2 · Remarks 1）
    /// = DROP_FROM_STANDARD，不物化。</para>
    /// </summary>
    public static readonly S0DimDefinition Customer = new(
        Key: "CUSTOMER",
        EntityCode: "S0_CUSTOMER",
        SourceTable: "CustMaster",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_customer",
        DimTable: "dim_customer",
        BusinessKeyColumns: ["tenant_id", "customer_code"],
        SourceBizKeyColumns: ["Cust"],
        Columns:
        [
            new("tenant_id",          S0DimValueKind.TenantIdColumn),
            new("customer_code",      S0DimValueKind.Str,         "Cust",           Required: true),
            new("customer_name",      S0DimValueKind.Str,         "SortName"),
            new("domain_code",        S0DimValueKind.Str,         "Domain"),
            new("company_id",         S0DimValueKind.Int,         "company_ref_id", Required: true),
            new("factory_id",         S0DimValueKind.Int,         "factory_ref_id", Required: true),
            new("shipping_lead_time", S0DimValueKind.Int,         "CustShippingLT"),
            new("is_active",          S0DimValueKind.BoolTrue,    "IsActive"),
            new("is_confirmed",       S0DimValueKind.BoolTrue,    "IsConfirm"),
            new("source_updated_at",  S0DimValueKind.DateTimeSec, "UpdateTime")
        ]);

    /// <summary>
    /// 人员技能字典。源 <c>PersonSkill</c>（11 列）—— 是**技能主数据**，不是关系表。
    ///
    /// <para><b>身份 = tenant_id + Code</b>。最强的一条证据来自现有消费方的注释与实现
    /// （<c>AdoS0MfgLinePostsController.ResolvePersonSkillCodesAsync</c>）：
    /// 「PersonSkill 表没有 Domain 列，故不按 Domain 收窄；产线岗位主表也不带
    /// CompanyRefId / FactoryRefId，无法据此再收窄，**因此按 (租户, Code) 解析**并对多义显式报错」——
    /// 即当前业务**已经**把技能码当作租户级字典，并把「同租户跨公司/工厂重码」视为
    /// <b>歧义错误</b>而非两个不同技能。</para>
    /// <para>数据佐证：三个候选（tenant+Code / +Factory / +Company+Factory）租户内重复全部为 0，
    /// company/factory 每租户单一取值。<c>Code</c> 跨租户碰撞 5 个编码 ⇒ 租户必须入身份。</para>
    ///
    /// <para>⚠️ <b>不设父维度</b>：<c>EmpSkills.SkillNo</c> 与本表 <c>Code</c> 同租户实测
    /// 650 行中 <b>632 行对不上</b>（仅 18 命中）—— 两者当前是各自独立的编码体系。
    /// **不因为都叫 Skill 就建立虚假父子关系。**</para>
    ///
    /// <para><c>source_updated_at ← UpdatedAt</c>：源 46 行中 <c>CreatedAt</c> 全非空、
    /// <c>UpdatedAt</c> 仅 5 行非空 —— 这正是「维护时间」的形态（只有被改过的行才有值），
    /// 故映射之，NULL 表示自建以来未修改。**不用 CreatedAt 冒充，也不回填。**</para>
    ///
    /// <para>字段适用性：<c>skill_code</c>=CORE；<c>skill_name</c>/<c>skill_level</c>/
    /// <c>remark</c>/<c>is_active</c>=ATTRIBUTE；<c>company_id</c>/<c>factory_id</c>=LEGACY_SCOPE。</para>
    /// </summary>
    public static readonly S0DimDefinition PersonSkill = new(
        Key: "PERSON_SKILL",
        EntityCode: "S0_PERSON_SKILL",
        SourceTable: "PersonSkill",
        SourceSystem: LocalSourceSystem,
        StagingTable: "mdp_stg_s0_person_skill",
        DimTable: "dim_person_skill",
        BusinessKeyColumns: ["tenant_id", "skill_code"],
        SourceBizKeyColumns: ["Code"],
        Columns:
        [
            new("tenant_id",         S0DimValueKind.TenantIdColumn),
            new("skill_code",        S0DimValueKind.Str,         "Code",         Required: true),
            new("skill_name",        S0DimValueKind.Str,         "Name"),
            new("skill_level",       S0DimValueKind.Str,         "SkillLevel"),
            new("remark",            S0DimValueKind.Str,         "Remark"),
            new("company_id",        S0DimValueKind.Int,         "CompanyRefId", Required: true),
            new("factory_id",        S0DimValueKind.Int,         "FactoryRefId", Required: true),
            new("is_active",         S0DimValueKind.BoolTrue,    "IsEnabled"),
            new("source_updated_at", S0DimValueKind.DateTimeSec, "UpdatedAt")
        ]);

    /// <summary>全部维度，按依赖顺序（父在前，子在后 —— 孤儿判定需要父维度先就绪）。</summary>
    public static readonly IReadOnlyList<S0DimDefinition> All =
        [WorkCenter, Department, Location, LocationShelf, Employee, Line, Customer, PersonSkill];

    /// <summary>按 Key 取维度（大小写不敏感）；不存在返回 null。</summary>
    public static S0DimDefinition? Find(string? key) =>
        string.IsNullOrWhiteSpace(key)
            ? null
            : All.FirstOrDefault(d => string.Equals(d.Key, key.Trim(), StringComparison.OrdinalIgnoreCase));

    /// <summary>校验全部声明；任一不合法即抛出（供启动自检与单测使用）。</summary>
    public static void ValidateAll()
    {
        foreach (var d in All) d.Validate();

        var dupDim = All.GroupBy(d => d.DimTable, StringComparer.Ordinal).FirstOrDefault(g => g.Count() > 1);
        if (dupDim is not null)
            throw new InvalidOperationException(
                $"dim 表被多个 definition 共用：{dupDim.Key} —— FULL Replace 按 tenant 整表删除，会互相清空");

        var dupEntity = All.GroupBy(d => d.EntityCode, StringComparer.Ordinal).FirstOrDefault(g => g.Count() > 1);
        if (dupEntity is not null)
            throw new InvalidOperationException($"entity_code 重复：{dupEntity.Key}");

        // 同一 staging 被多个 definition 共用时，source_table 必须互异，否则 purge/物化会互相污染
        foreach (var g in All.GroupBy(d => d.StagingTable, StringComparer.Ordinal).Where(g => g.Count() > 1))
        {
            var tables = g.Select(d => d.SourceTable).ToList();
            if (tables.Distinct(StringComparer.Ordinal).Count() != tables.Count)
                throw new InvalidOperationException($"staging {g.Key} 被多个 definition 共用且 source_table 重复");
        }
    }
}