"""
生成字段补全复核文档：ODS + DWD 现有字段 + 待新增字段
输出到 FIELD_AUDIT_ROOT 目录
"""
import os
import sys
from pathlib import Path

# 加载 .env
sys.path.insert(0, str(Path(__file__).resolve().parent))
from _env_paths import get_output_path

output_dir = get_output_path("FIELD_AUDIT_ROOT")
output_file = os.path.join(output_dir, "field_review_for_user.md")

# ── 数据定义 ──────────────────────────────────────────────────────────

# 每张表: { ods_table, dwd_tables: [{name, columns: [(col, type, desc)]}], new_fields: [(col, type, target_table, desc, action)] }
TABLES = [
    # ═══════════════════════════════════════════════════════════════════
    # 1. assistant_accounts_master → dim_assistant + dim_assistant_ex
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "1. assistant_accounts_master（助教账号档案）",
        "ods_table": "ods.assistant_accounts_master",
        "ods_columns": [
            ("id", "bigint", "PK，助教 ID"),
            ("tenant_id", "bigint", "租户 ID"),
            ("site_id", "bigint", "门店 ID"),
            ("assistant_no", "text", "助教编号"),
            ("nickname", "text", "昵称"),
            ("real_name", "text", "真实姓名"),
            ("mobile", "text", "手机号"),
            ("team_id", "bigint", "团队 ID"),
            ("team_name", "text", "团队名称"),
            ("user_id", "bigint", "用户 ID"),
            ("level", "text", "等级"),
            ("assistant_status", "integer", "助教状态"),
            ("work_status", "integer", "工作状态"),
            ("leave_status", "integer", "请假状态"),
            ("entry_time", "timestamp", "入职时间"),
            ("resign_time", "timestamp", "离职时间"),
            ("start_time", "timestamp", "排班开始"),
            ("end_time", "timestamp", "排班结束"),
            ("create_time", "timestamp", "创建时间"),
            ("update_time", "timestamp", "更新时间"),
            ("order_trade_no", "text", "订单交易号"),
            ("staff_id", "bigint", "员工 ID"),
            ("staff_profile_id", "bigint", "员工档案 ID"),
            ("system_role_id", "bigint", "系统角色 ID"),
            ("avatar", "text", "头像 URL"),
            ("birth_date", "timestamp", "出生日期"),
            ("gender", "integer", "性别"),
            ("height", "numeric(18,2)", "身高"),
            ("weight", "numeric(18,2)", "体重"),
            ("job_num", "text", "备用工号"),
            ("show_status", "integer", "展示状态"),
            ("show_sort", "integer", "展示排序"),
            ("sum_grade", "numeric(18,2)", "总评分"),
            ("assistant_grade", "numeric(18,2)", "助教评分"),
            ("get_grade_times", "integer", "被评次数"),
            ("introduce", "text", "个人介绍"),
            ("video_introduction_url", "text", "视频介绍 URL"),
            ("group_id", "bigint", "分组 ID"),
            ("group_name", "text", "分组名称"),
            ("shop_name", "text", "门店名称快照"),
            ("charge_way", "integer", "计费方式"),
            ("entry_type", "integer", "入职类型"),
            ("allow_cx", "integer", "允许促销"),
            ("is_guaranteed", "integer", "是否保底"),
            ("salary_grant_enabled", "integer", "工资发放启用"),
            ("light_status", "integer", "灯控状态"),
            ("online_status", "integer", "在线状态"),
            ("is_delete", "integer", "删除标记"),
            ("cx_unit_price", "numeric(18,2)", "促销时段单价"),
            ("pd_unit_price", "numeric(18,2)", "普通时段单价"),
            ("last_table_id", "bigint", "最后台桌 ID"),
            ("last_table_name", "text", "最后台桌名"),
            ("person_org_id", "bigint", "人事组织 ID"),
            ("serial_number", "bigint", "序列号"),
            ("is_team_leader", "integer", "是否组长"),
            ("criticism_status", "integer", "批评状态"),
            ("last_update_name", "text", "最后更新人"),
            ("ding_talk_synced", "integer", "钉钉同步"),
            ("site_light_cfg_id", "bigint", "灯控配置 ID"),
            ("light_equipment_id", "text", "灯控设备 ID"),
            ("entry_sign_status", "integer", "入职签约状态"),
            ("resign_sign_status", "integer", "离职签约状态"),
        ],
        "dwd_tables": [
            {
                "name": "dwd.dim_assistant（主表）",
                "columns": [
                    ("assistant_id", "bigint", "PK"),
                    ("user_id", "bigint", "用户 ID"),
                    ("assistant_no", "text", "助教编号"),
                    ("real_name", "text", "真实姓名"),
                    ("nickname", "text", "昵称"),
                    ("mobile", "text", "手机号"),
                    ("tenant_id", "bigint", "租户 ID"),
                    ("site_id", "bigint", "门店 ID"),
                    ("team_id", "bigint", "团队 ID"),
                    ("team_name", "text", "团队名称"),
                    ("level", "integer", "等级"),
                    ("entry_time", "timestamptz", "入职时间"),
                    ("resign_time", "timestamptz", "离职时间"),
                    ("leave_status", "integer", "请假状态"),
                    ("assistant_status", "integer", "助教状态"),
                    ("scd2_start_time", "timestamptz", "SCD2 开始"),
                    ("scd2_end_time", "timestamptz", "SCD2 结束"),
                    ("scd2_is_current", "integer", "SCD2 当前"),
                    ("scd2_version", "integer", "SCD2 版本"),
                ],
            },
            {
                "name": "dwd.dim_assistant_ex（扩展表）",
                "columns": [
                    ("assistant_id", "bigint", "PK"),
                    ("gender", "integer", "性别"),
                    ("birth_date", "timestamptz", "出生日期"),
                    ("avatar", "text", "头像"),
                    ("introduce", "text", "介绍"),
                    ("video_introduction_url", "text", "视频介绍"),
                    ("height", "numeric(5,2)", "身高"),
                    ("weight", "numeric(5,2)", "体重"),
                    ("shop_name", "text", "门店名"),
                    ("group_id", "bigint", "分组 ID"),
                    ("group_name", "text", "分组名"),
                    ("person_org_id", "bigint", "人事组织 ID"),
                    ("staff_id", "bigint", "员工 ID"),
                    ("staff_profile_id", "bigint", "员工档案 ID"),
                    ("assistant_grade", "double", "助教评分"),
                    ("sum_grade", "double", "总评分"),
                    ("get_grade_times", "integer", "被评次数"),
                    ("charge_way", "integer", "计费方式"),
                    ("allow_cx", "integer", "允许促销"),
                    ("is_guaranteed", "integer", "是否保底"),
                    ("salary_grant_enabled", "integer", "工资发放"),
                    ("entry_type", "integer", "入职类型"),
                    ("entry_sign_status", "integer", "入职签约"),
                    ("resign_sign_status", "integer", "离职签约"),
                    ("work_status", "integer", "工作状态"),
                    ("show_status", "integer", "展示状态"),
                    ("show_sort", "integer", "展示排序"),
                    ("online_status", "integer", "在线状态"),
                    ("is_delete", "integer", "删除标记"),
                    ("criticism_status", "integer", "批评状态"),
                    ("create_time", "timestamptz", "创建时间"),
                    ("update_time", "timestamptz", "更新时间"),
                    ("start_time", "timestamptz", "排班开始"),
                    ("end_time", "timestamptz", "排班结束"),
                    ("last_table_id", "bigint", "最后台桌 ID"),
                    ("last_table_name", "text", "最后台桌名"),
                    ("last_update_name", "text", "最后更新人"),
                    ("order_trade_no", "bigint", "订单交易号"),
                    ("ding_talk_synced", "integer", "钉钉同步"),
                    ("site_light_cfg_id", "bigint", "灯控配置 ID"),
                    ("light_equipment_id", "text", "灯控设备 ID"),
                    ("light_status", "integer", "灯控状态"),
                    ("is_team_leader", "integer", "是否组长"),
                    ("serial_number", "bigint", "序列号"),
                    ("scd2_*", "", "SCD2 四列"),
                ],
            },
        ],
        "new_fields": [
            ("system_role_id", "bigint", "dim_assistant_ex", "系统角色 ID，关联角色权限。采样值: 10", "新增 DWD 列 + FACT_MAPPINGS"),
            ("job_num", "text", "dim_assistant_ex", "备用工号。采样值: 全部 NULL（未启用）", "新增 DWD 列 + FACT_MAPPINGS"),
            ("cx_unit_price", "numeric(18,2)", "dim_assistant_ex", "促销时段单价。采样值: 0.00", "新增 DWD 列 + FACT_MAPPINGS"),
            ("pd_unit_price", "numeric(18,2)", "dim_assistant_ex", "普通时段单价。采样值: 0.00", "新增 DWD 列 + FACT_MAPPINGS"),
        ],
        "skipped_fields": [],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 2. assistant_service_records → dwd_assistant_service_log + _ex
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "2. assistant_service_records（助教服务流水）",
        "ods_table": "ods.assistant_service_records",
        "ods_columns": [
            ("id", "bigint", "PK，服务记录 ID"),
            ("tenant_id", "bigint", "租户 ID"),
            ("site_id", "bigint", "门店 ID"),
            ("siteprofile", "jsonb", "门店信息 JSON"),
            ("site_table_id", "bigint", "台桌 ID"),
            ("order_settle_id", "bigint", "结算单 ID"),
            ("order_trade_no", "text", "订单交易号"),
            ("order_pay_id", "bigint", "支付单 ID"),
            ("order_assistant_id", "bigint", "订单助教 ID"),
            ("order_assistant_type", "integer", "助教类型"),
            ("assistantname", "text", "助教名称"),
            ("assistantno", "text", "助教编号"),
            ("assistant_level", "text", "助教等级"),
            ("levelname", "text", "等级名称"),
            ("site_assistant_id", "bigint", "门店助教档案 ID"),
            ("skill_id", "bigint", "技能 ID"),
            ("skillname", "text", "技能名称"),
            ("system_member_id", "bigint", "系统会员 ID"),
            ("tablename", "text", "台桌名称"),
            ("tenant_member_id", "bigint", "租户会员 ID"),
            ("user_id", "bigint", "用户 ID"),
            ("assistant_team_id", "bigint", "助教团队 ID"),
            ("nickname", "text", "昵称"),
            ("ledger_name", "text", "台账名称"),
            ("ledger_group_name", "text", "台账组名"),
            ("ledger_amount", "numeric(18,2)", "台账金额"),
            ("ledger_count", "numeric(18,4)", "台账数量"),
            ("ledger_unit_price", "numeric(18,4)", "台账单价"),
            ("ledger_status", "integer", "台账状态"),
            ("ledger_start_time", "timestamp", "台账开始"),
            ("ledger_end_time", "timestamp", "台账结束"),
            ("manual_discount_amount", "numeric(18,2)", "手动折扣"),
            ("member_discount_amount", "numeric(18,2)", "会员折扣"),
            ("coupon_deduct_money", "numeric(18,2)", "券抵扣"),
            ("service_money", "numeric(18,2)", "服务费"),
            ("projected_income", "numeric(18,2)", "预计收入"),
            ("real_use_seconds", "integer", "实际使用秒数"),
            ("income_seconds", "integer", "计费秒数"),
            ("start_use_time", "timestamp", "开始使用时间"),
            ("last_use_time", "timestamp", "最后使用时间"),
            ("create_time", "timestamp", "创建时间"),
            ("is_single_order", "integer", "是否单点"),
            ("is_delete", "integer", "删除标记"),
            ("is_trash", "integer", "废除标记"),
            ("trash_reason", "text", "废除原因"),
            ("trash_applicant_id", "bigint", "废除申请人 ID"),
            ("trash_applicant_name", "text", "废除申请人"),
            ("operator_id", "bigint", "操作员 ID"),
            ("operator_name", "text", "操作员名称"),
            ("salesman_name", "text", "销售员名称"),
            ("salesman_org_id", "bigint", "销售组织 ID"),
            ("salesman_user_id", "bigint", "销售员用户 ID"),
            ("person_org_id", "bigint", "人事组织 ID"),
            ("add_clock", "integer", "加钟次数"),
            ("returns_clock", "integer", "退钟次数"),
            ("composite_grade", "numeric(10,2)", "综合评分"),
            ("composite_grade_time", "timestamp", "评分时间"),
            ("skill_grade", "numeric(10,2)", "技能评分"),
            ("service_grade", "numeric(10,2)", "服务评分"),
            ("sum_grade", "numeric(10,2)", "总评分"),
            ("grade_status", "integer", "评分状态"),
            ("get_grade_times", "integer", "被评次数"),
            ("is_not_responding", "integer", "未响应"),
            ("is_confirm", "integer", "已确认"),
            ("assistantteamname", "text", "助教团队名"),
            ("real_service_money", "numeric(18,2)", "实际服务费"),
        ],
        "dwd_tables": [
            {
                "name": "dwd.dwd_assistant_service_log（主表）",
                "columns": [
                    ("assistant_service_id", "bigint", "PK"),
                    ("order_trade_no", "bigint", "订单交易号"),
                    ("order_settle_id", "bigint", "结算单 ID"),
                    ("order_pay_id", "bigint", "支付单 ID"),
                    ("order_assistant_id", "bigint", "订单助教 ID"),
                    ("order_assistant_type", "integer", "助教类型"),
                    ("tenant_id", "bigint", "租户 ID"),
                    ("site_id", "bigint", "门店 ID"),
                    ("site_table_id", "bigint", "台桌 ID"),
                    ("tenant_member_id", "bigint", "租户会员 ID"),
                    ("system_member_id", "bigint", "系统会员 ID"),
                    ("assistant_no", "varchar(64)", "助教编号"),
                    ("nickname", "varchar(64)", "昵称"),
                    ("site_assistant_id", "bigint", "门店助教 ID（映射自 order_assistant_id）"),
                    ("user_id", "bigint", "用户 ID"),
                    ("assistant_team_id", "bigint", "团队 ID"),
                    ("person_org_id", "bigint", "人事组织 ID"),
                    ("assistant_level", "integer", "等级"),
                    ("level_name", "varchar(64)", "等级名称"),
                    ("skill_id", "bigint", "技能 ID"),
                    ("skill_name", "varchar(64)", "技能名称"),
                    ("ledger_unit_price", "numeric(10,2)", "台账单价"),
                    ("ledger_amount", "numeric(10,2)", "台账金额"),
                    ("projected_income", "numeric(10,2)", "预计收入"),
                    ("coupon_deduct_money", "numeric(10,2)", "券抵扣"),
                    ("income_seconds", "integer", "计费秒数"),
                    ("real_use_seconds", "integer", "实际秒数"),
                    ("add_clock", "integer", "加钟"),
                    ("create_time", "timestamptz", "创建时间"),
                    ("start_use_time", "timestamptz", "开始时间"),
                    ("last_use_time", "timestamptz", "结束时间"),
                    ("is_delete", "integer", "删除标记"),
                    ("real_service_money", "numeric(18,2)", "实际服务费"),
                ],
            },
            {
                "name": "dwd.dwd_assistant_service_log_ex（扩展表）",
                "columns": [
                    ("assistant_service_id", "bigint", "PK"),
                    ("table_name", "varchar(64)", "台桌名"),
                    ("assistant_name", "varchar(64)", "助教名"),
                    ("ledger_name", "varchar(128)", "台账名"),
                    ("ledger_group_name", "varchar(128)", "台账组名"),
                    ("ledger_count", "integer", "台账数量"),
                    ("member_discount_amount", "numeric(10,2)", "会员折扣"),
                    ("manual_discount_amount", "numeric(10,2)", "手动折扣"),
                    ("service_money", "numeric(10,2)", "服务费"),
                    ("returns_clock", "integer", "退钟"),
                    ("ledger_start_time", "timestamptz", "台账开始"),
                    ("ledger_end_time", "timestamptz", "台账结束"),
                    ("ledger_status", "integer", "台账状态"),
                    ("is_confirm", "integer", "已确认"),
                    ("is_single_order", "integer", "单点"),
                    ("is_not_responding", "integer", "未响应"),
                    ("is_trash", "integer", "废除"),
                    ("trash_applicant_id", "bigint", "废除申请人 ID"),
                    ("trash_applicant_name", "varchar(64)", "废除申请人"),
                    ("trash_reason", "varchar(255)", "废除原因"),
                    ("salesman_user_id", "bigint", "销售员 ID"),
                    ("salesman_name", "varchar(64)", "销售员名"),
                    ("salesman_org_id", "bigint", "销售组织 ID"),
                    ("skill_grade", "integer", "技能评分"),
                    ("service_grade", "integer", "服务评分"),
                    ("composite_grade", "numeric(5,2)", "综合评分"),
                    ("sum_grade", "numeric(10,2)", "总评分"),
                    ("get_grade_times", "integer", "被评次数"),
                    ("grade_status", "integer", "评分状态"),
                    ("composite_grade_time", "timestamptz", "评分时间"),
                    ("assistant_team_name", "text", "团队名"),
                ],
            },
        ],
        "new_fields": [
            ("operator_id", "bigint", "dwd_assistant_service_log_ex", "操作员 ID（如收银员）。采样值: 2790687322443013", "新增 DWD 列 + FACT_MAPPINGS"),
            ("operator_name", "text", "dwd_assistant_service_log_ex", "操作员名称。采样值: 收银员:郑丽珊", "新增 DWD 列 + FACT_MAPPINGS"),
        ],
        "skipped_fields": [
            ("site_assistant_id", "已通过 FACT_MAPPINGS 映射（ODS order_assistant_id → DWD site_assistant_id），无需变更"),
        ],
    },
    # ═══════════════════════════════════════════════════════════════════
    # 4. store_goods_sales_records → dwd_store_goods_sale + _ex
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "4. store_goods_sales_records（门店商品销售流水）",
        "ods_table": "ods.store_goods_sales_records",
        "ods_columns": [
            ("id", "bigint", "PK"),
            ("tenant_id", "bigint", "租户 ID"),
            ("site_id", "bigint", "门店 ID"),
            ("siteid", "bigint", "门店 ID（冗余）"),
            ("sitename", "text", "门店名"),
            ("site_goods_id", "bigint", "门店商品 ID"),
            ("tenant_goods_id", "bigint", "租户商品 ID"),
            ("order_settle_id", "bigint", "结算单 ID"),
            ("order_trade_no", "text", "订单交易号"),
            ("order_goods_id", "bigint", "订单商品 ID"),
            ("ordergoodsid", "bigint", "订单商品 ID（冗余）"),
            ("order_pay_id", "bigint", "支付单 ID"),
            ("order_coupon_id", "bigint", "订单券 ID"),
            ("ledger_name", "text", "台账名"),
            ("ledger_group_name", "text", "台账组名"),
            ("ledger_amount", "numeric(18,2)", "台账金额"),
            ("ledger_count", "numeric(18,4)", "台账数量"),
            ("ledger_unit_price", "numeric(18,4)", "台账单价"),
            ("ledger_status", "integer", "台账状态"),
            ("discount_money", "numeric(18,2)", "折扣金额"),
            ("discount_price", "numeric(18,2)", "折后单价"),
            ("coupon_deduct_money", "numeric(18,2)", "券抵扣"),
            ("member_discount_amount", "numeric(18,2)", "会员折扣"),
            ("option_coupon_deduct_money", "numeric(18,2)", "选项券抵扣"),
            ("option_member_discount_money", "numeric(18,2)", "选项会员折扣"),
            ("point_discount_money", "numeric(18,2)", "积分抵扣"),
            ("point_discount_money_cost", "numeric(18,2)", "积分抵扣成本"),
            ("real_goods_money", "numeric(18,2)", "实际商品金额"),
            ("cost_money", "numeric(18,2)", "成本金额"),
            ("push_money", "numeric(18,2)", "推送费"),
            ("sales_type", "integer", "销售类型"),
            ("is_single_order", "integer", "单点"),
            ("is_delete", "integer", "删除标记"),
            ("goods_remark", "text", "商品备注"),
            ("option_price", "numeric(18,2)", "选项价格"),
            ("option_value_name", "text", "选项值名"),
            ("member_coupon_id", "bigint", "会员券 ID"),
            ("package_coupon_id", "bigint", "套餐券 ID"),
            ("sales_man_org_id", "bigint", "销售组织 ID"),
            ("salesman_name", "text", "销售员名"),
            ("salesman_role_id", "bigint", "销售角色 ID"),
            ("salesman_user_id", "bigint", "销售员 ID"),
            ("operator_id", "bigint", "操作员 ID"),
            ("operator_name", "text", "操作员名"),
            ("opensalesman", "text", "开单销售"),
            ("returns_number", "integer", "退货数量"),
            ("site_table_id", "bigint", "台桌 ID"),
            ("tenant_goods_business_id", "bigint", "商品业务 ID"),
            ("tenant_goods_category_id", "bigint", "商品分类 ID"),
            ("create_time", "timestamp", "创建时间"),
            ("coupon_share_money", "numeric(18,2)", "券分摊金额"),
        ],
        "dwd_tables": [
            {"name": "dwd.dwd_store_goods_sale（主表）", "columns": [
                ("store_goods_sale_id", "bigint", "PK"),
                ("order_trade_no / order_settle_id / order_pay_id / order_goods_id", "", "订单关联 ID"),
                ("site_id / tenant_id / site_goods_id / tenant_goods_id", "", "门店/商品 ID"),
                ("tenant_goods_category_id / tenant_goods_business_id / site_table_id", "", "分类/台桌"),
                ("ledger_name / ledger_group_name / ledger_unit_price / ledger_count / ledger_amount", "", "台账信息"),
                ("discount_price", "numeric(18,2)", "⚠️ 实际映射自 ODS discount_money（折扣金额），非折后单价"),
                ("real_goods_money / cost_money / coupon_share_money", "", "金额字段"),
                ("ledger_status / is_delete / create_time", "", "状态/时间"),
            ]},
            {"name": "dwd.dwd_store_goods_sale_ex（扩展表）", "columns": [
                ("store_goods_sale_id", "bigint", "PK"),
                ("legacy_order_goods_id / site_name / legacy_site_id", "", "快照"),
                ("goods_remark / option_value_name / operator_name", "", "备注/操作员"),
                ("open_salesman_flag / salesman_user_id / salesman_name / salesman_role_id / salesman_org_id", "", "销售员"),
                ("discount_money / returns_number / coupon_deduct_money / member_discount_amount", "", "折扣"),
                ("point_discount_money / point_discount_money_cost", "", "积分"),
                ("package_coupon_id / order_coupon_id / member_coupon_id", "", "券 ID"),
                ("option_price / option_member_discount_money / option_coupon_deduct_money", "", "选项"),
                ("push_money / is_single_order / sales_type / operator_id", "", "其他"),
            ]},
        ],
        "new_fields": [],
        "skipped_fields": [
            ("discount_price", "DWD discount_price 列名已被占用（映射自 ODS discount_money）。ODS discount_price（折后单价）未映射。如需可后续新增 unit_discount_price"),
        ],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 5. member_balance_changes → dwd_member_balance_change + _ex
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "5. member_balance_changes（会员余额变动）",
        "ods_table": "ods.member_balance_changes",
        "ods_columns": [
            ("id", "bigint", "PK"),
            ("tenant_id", "bigint", "租户 ID"),
            ("site_id", "bigint", "门店 ID"),
            ("register_site_id", "bigint", "注册门店 ID"),
            ("registersitename", "text", "注册门店名"),
            ("paysitename", "text", "支付门店名"),
            ("tenant_member_id", "bigint", "租户会员 ID"),
            ("tenant_member_card_id", "bigint", "会员卡 ID"),
            ("system_member_id", "bigint", "系统会员 ID"),
            ("membername", "text", "会员名"),
            ("membermobile", "text", "会员手机"),
            ("card_type_id", "bigint", "卡类型 ID"),
            ("membercardtypename", "text", "卡类型名"),
            ("account_data", "numeric(18,2)", "变动金额"),
            ("before", "numeric(18,2)", "变动前余额"),
            ("after", "numeric(18,2)", "变动后余额"),
            ("refund_amount", "numeric(18,2)", "退款金额"),
            ("from_type", "integer", "来源类型"),
            ("payment_method", "integer", "支付方式"),
            ("relate_id", "bigint", "关联 ID（充值/订单）"),
            ("remark", "text", "备注"),
            ("operator_id", "bigint", "操作员 ID"),
            ("operator_name", "text", "操作员名"),
            ("is_delete", "integer", "删除标记"),
            ("create_time", "timestamp", "创建时间"),
            ("principal_after", "numeric(18,2)", "本金变动后"),
            ("principal_before", "numeric(18,2)", "本金变动前"),
            ("principal_data", "text", "本金变动数据"),
        ],
        "dwd_tables": [
            {"name": "dwd.dwd_member_balance_change（主表）", "columns": [
                ("balance_change_id", "bigint", "PK"),
                ("tenant_id", "bigint", "租户 ID"),
                ("site_id", "bigint", "门店 ID"),
                ("register_site_id", "bigint", "注册门店 ID"),
                ("tenant_member_id", "bigint", "租户会员 ID"),
                ("system_member_id", "bigint", "系统会员 ID"),
                ("tenant_member_card_id", "bigint", "会员卡 ID"),
                ("card_type_id", "bigint", "卡类型 ID"),
                ("card_type_name", "varchar", "卡类型名"),
                ("member_name", "varchar", "会员名"),
                ("member_mobile", "varchar", "会员手机"),
                ("balance_before", "numeric(18,2)", "变动前余额"),
                ("change_amount", "numeric(18,2)", "变动金额"),
                ("balance_after", "numeric(18,2)", "变动后余额"),
                ("from_type", "integer", "来源类型"),
                ("payment_method", "integer", "支付方式"),
                ("change_time", "timestamptz", "变动时间"),
                ("is_delete", "integer", "删除标记"),
                ("remark", "varchar", "备注"),
                ("principal_before", "numeric(18,2)", "本金变动前"),
                ("principal_after", "numeric(18,2)", "本金变动后"),
                ("principal_change_amount", "numeric(18,2)", "本金变动额"),
            ]},
            {"name": "dwd.dwd_member_balance_change_ex（扩展表）", "columns": [
                ("balance_change_id", "bigint", "PK"),
                ("pay_site_name", "varchar", "支付门店名"),
                ("register_site_name", "varchar", "注册门店名"),
                ("refund_amount", "numeric(18,2)", "退款金额"),
                ("operator_id", "bigint", "操作员 ID"),
                ("operator_name", "varchar", "操作员名"),
                ("principal_data", "text", "本金变动数据"),
            ]},
        ],
        "new_fields": [
            ("relate_id", "bigint", "dwd_member_balance_change_ex", "关联 ID（充值记录/订单 ID）。采样值: 2993181992014533, 0, 2888539288521221", "新增 DWD 列 + FACT_MAPPINGS"),
        ],
        "skipped_fields": [],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 6. tenant_goods_master → dim_tenant_goods + _ex
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "6. tenant_goods_master（租户商品档案）",
        "ods_table": "ods.tenant_goods_master",
        "ods_columns": [
            ("id", "bigint", "PK"),
            ("tenant_id", "bigint", "租户 ID"),
            ("goods_name", "text", "商品名"),
            ("goods_bar_code", "text", "条码"),
            ("goods_category_id", "bigint", "一级分类 ID"),
            ("goods_second_category_id", "bigint", "二级分类 ID"),
            ("categoryname", "text", "分类名"),
            ("unit", "text", "单位"),
            ("goods_number", "text", "商品编号"),
            ("out_goods_id", "text", "外部商品 ID"),
            ("goods_state", "integer", "商品状态"),
            ("sale_channel", "integer", "销售渠道"),
            ("able_discount", "integer", "可折扣"),
            ("able_site_transfer", "integer", "可门店调拨"),
            ("is_delete", "integer", "删除标记"),
            ("is_warehousing", "integer", "是否入库"),
            ("isinsite", "integer", "是否在门店"),
            ("cost_price", "numeric(18,4)", "成本价"),
            ("cost_price_type", "integer", "成本价类型"),
            ("market_price", "numeric(18,4)", "市场价"),
            ("min_discount_price", "numeric(18,4)", "最低折扣价"),
            ("common_sale_royalty", "numeric(18,4)", "普通销售提成"),
            ("point_sale_royalty", "numeric(18,4)", "积分销售提成"),
            ("pinyin_initial", "text", "拼音首字母"),
            ("commoditycode", "text", "商品编码集合（花括号包裹）"),
            ("commodity_code", "text", "商品编码（单个）"),
            ("goods_cover", "text", "商品封面"),
            ("supplier_id", "bigint", "供应商 ID"),
            ("remark_name", "text", "备注名"),
            ("create_time", "timestamp", "创建时间"),
            ("update_time", "timestamp", "更新时间"),
            ("not_sale", "integer", "禁售标记"),
        ],
        "dwd_tables": [
            {"name": "dwd.dim_tenant_goods（主表）", "columns": [
                ("tenant_goods_id", "bigint", "PK"),
                ("tenant_id", "bigint", "租户 ID"),
                ("supplier_id", "bigint", "供应商 ID"),
                ("category_name", "varchar", "分类名"),
                ("goods_category_id", "bigint", "一级分类 ID"),
                ("goods_second_category_id", "bigint", "二级分类 ID"),
                ("goods_name", "varchar", "商品名"),
                ("goods_number", "varchar", "商品编号"),
                ("unit", "varchar", "单位"),
                ("market_price", "numeric(18,2)", "市场价"),
                ("goods_state", "integer", "商品状态"),
                ("create_time", "timestamptz", "创建时间"),
                ("update_time", "timestamptz", "更新时间"),
                ("is_delete", "integer", "删除标记"),
                ("not_sale", "integer", "禁售标记"),
                ("scd2_*", "", "SCD2 四列"),
            ]},
            {"name": "dwd.dim_tenant_goods_ex（扩展表）", "columns": [
                ("tenant_goods_id", "bigint", "PK"),
                ("remark_name", "varchar", "备注名"),
                ("pinyin_initial", "varchar", "拼音首字母"),
                ("goods_cover", "varchar", "商品封面"),
                ("goods_bar_code", "varchar", "条码"),
                ("commodity_code", "varchar", "商品编码（自动映射）"),
                ("commodity_code_list", "TEXT[]", "商品编码数组（FACT_MAPPINGS 映射自 ODS commoditycode，CAST TEXT[]）"),
                ("min_discount_price", "numeric(18,2)", "最低折扣价"),
                ("cost_price", "numeric(18,2)", "成本价"),
                ("cost_price_type", "integer", "成本价类型"),
                ("able_discount", "integer", "可折扣"),
                ("sale_channel", "integer", "销售渠道"),
                ("is_warehousing", "integer", "是否入库"),
                ("is_in_site", "boolean", "是否在门店"),
                ("able_site_transfer", "integer", "可门店调拨"),
                ("common_sale_royalty", "integer", "普通销售提成"),
                ("point_sale_royalty", "integer", "积分销售提成"),
                ("out_goods_id", "bigint", "外部商品 ID"),
                ("scd2_*", "", "SCD2 四列"),
            ]},
        ],
        "new_fields": [],
        "skipped_fields": [
            ("commoditycode", "已映射。commodity_code_list 现从 commoditycode CAST TEXT[] 获取（2026-02-21 迁移）。commodity_code 仍自动映射为单值冗余"),
        ],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 7. site_tables_master → dim_table + dim_table_ex
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "7. site_tables_master（台桌档案）",
        "ods_table": "ods.site_tables_master",
        "ods_columns": [
            ("id", "bigint", "PK，台桌 ID"),
            ("site_id", "bigint", "门店 ID"),
            ("sitename", "text", "门店名"),
            ("appletQrCodeUrl", "text", "小程序二维码 URL"),
            ("areaname", "text", "台区名"),
            ("audit_status", "integer", "审核状态"),
            ("charge_free", "integer", "是否免费"),
            ("create_time", "timestamp", "创建时间"),
            ("delay_lights_time", "integer", "延迟关灯时间（秒）"),
            ("is_online_reservation", "integer", "是否在线预约"),
            ("is_rest_area", "integer", "是否休息区"),
            ("light_status", "integer", "灯控状态"),
            ("only_allow_groupon", "integer", "仅允许团购"),
            ("order_delay_time", "integer", "订单延迟时间（秒）"),
            ("self_table", "integer", "是否自助台桌"),
            ("show_status", "integer", "展示状态"),
            ("site_table_area_id", "bigint", "台区 ID"),
            ("tablestatusname", "text", "台桌状态名"),
            ("table_cloth_use_cycle", "integer", "台布使用周期"),
            ("table_cloth_use_time", "timestamp", "台布使用时间"),
            ("table_name", "text", "台桌名"),
            ("table_price", "numeric(18,2)", "台桌价格"),
            ("table_status", "integer", "台桌状态"),
            ("temporary_light_second", "integer", "临时灯光秒数"),
            ("virtual_table", "integer", "是否虚拟台桌"),
            ("order_id", "bigint", "当前订单 ID"),
        ],
        "dwd_tables": [
            {"name": "dwd.dim_table（主表）", "columns": [
                ("table_id", "bigint", "PK"),
                ("site_id", "bigint", "门店 ID"),
                ("table_name", "text", "台桌名"),
                ("site_table_area_id", "bigint", "台区 ID"),
                ("site_table_area_name", "text", "台区名"),
                ("tenant_table_area_id", "bigint", "租户台区 ID"),
                ("table_price", "numeric(18,2)", "台桌价格"),
                ("order_id", "bigint", "当前订单 ID"),
                ("scd2_*", "", "SCD2 四列"),
            ]},
            {"name": "dwd.dim_table_ex（扩展表）", "columns": [
                ("table_id", "bigint", "PK"),
                ("show_status", "integer", "展示状态"),
                ("is_online_reservation", "integer", "是否在线预约"),
                ("table_cloth_use_time", "integer", "台布使用时间"),
                ("table_cloth_use_cycle", "integer", "台布使用周期"),
                ("table_status", "integer", "台桌状态"),
                ("scd2_*", "", "SCD2 四列"),
            ]},
        ],
        "new_fields": [
            ("sitename", "text", "dim_table_ex", "门店名称快照。采样值: 朗朗桌球", "新增 DWD 列 + FACT_MAPPINGS"),
            ("appletqrcodeurl", "text", "dim_table_ex", "小程序二维码 URL。采样值: 全部 NULL", "新增 DWD 列 + FACT_MAPPINGS"),
            ("audit_status", "integer", "dim_table_ex", "审核状态。采样值: 2", "新增 DWD 列 + FACT_MAPPINGS"),
            ("charge_free", "integer", "dim_table_ex", "是否免费（0=否）。采样值: 0", "新增 DWD 列 + FACT_MAPPINGS"),
            ("create_time", "timestamptz", "dim_table_ex", "台桌创建时间。采样值: 2025-07-16 11:30:20", "新增 DWD 列 + FACT_MAPPINGS"),
            ("delay_lights_time", "integer", "dim_table_ex", "延迟关灯时间（秒）。采样值: 0", "新增 DWD 列 + FACT_MAPPINGS"),
            ("is_rest_area", "integer", "dim_table_ex", "是否休息区（0=否）。采样值: 0", "新增 DWD 列 + FACT_MAPPINGS"),
            ("light_status", "integer", "dim_table_ex", "灯控状态。采样值: 1, 2", "新增 DWD 列 + FACT_MAPPINGS"),
            ("only_allow_groupon", "integer", "dim_table_ex", "仅允许团购。采样值: 2", "新增 DWD 列 + FACT_MAPPINGS"),
            ("order_delay_time", "integer", "dim_table_ex", "订单延迟时间（秒）。采样值: 0", "新增 DWD 列 + FACT_MAPPINGS"),
            ("self_table", "integer", "dim_table_ex", "是否自助台桌。采样值: 1", "新增 DWD 列 + FACT_MAPPINGS"),
            ("tablestatusname", "text", "dim_table_ex", "台桌状态名。采样值: 空闲中, 使用中, 暂停中", "新增 DWD 列 + FACT_MAPPINGS"),
            ("temporary_light_second", "integer", "dim_table_ex", "临时灯光秒数。采样值: 0", "新增 DWD 列 + FACT_MAPPINGS"),
            ("virtual_table", "integer", "dim_table_ex", "是否虚拟台桌（0=否）。采样值: 0", "新增 DWD 列 + FACT_MAPPINGS"),
        ],
        "skipped_fields": [],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 8. recharge_settlements → dwd_recharge_order + _ex（B 类）
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "8. recharge_settlements（充值结算单）— B 类",
        "ods_table": "ods.recharge_settlements",
        "ods_columns": [
            ("id", "bigint", "PK"),
            ("tenantid", "bigint", "租户 ID"),
            ("siteid", "bigint", "门店 ID"),
            ("sitename", "text", "门店名"),
            ("memberid", "bigint", "会员 ID"),
            ("membername", "text", "会员名"),
            ("memberphone", "text", "会员手机"),
            ("tenantmembercardid", "bigint", "会员卡 ID"),
            ("membercardtypename", "text", "卡类型名"),
            ("tableid", "bigint", "台桌 ID"),
            ("settletype", "integer", "结算类型"),
            ("settlename", "text", "结算名称"),
            ("settlestatus", "integer", "结算状态"),
            ("settlerelateid", "bigint", "结算关联 ID"),
            ("isfirst", "integer", "是否首次"),
            ("payamount", "numeric(18,2)", "支付金额"),
            ("refundamount", "numeric(18,2)", "退款金额"),
            ("pointamount", "numeric(18,2)", "积分金额"),
            ("cashamount", "numeric(18,2)", "现金金额"),
            ("paymentmethod", "integer", "支付方式"),
            ("createtime", "timestamptz", "创建时间"),
            ("paytime", "timestamptz", "支付时间"),
            ("onlineamount", "numeric(18,2)", "在线金额"),
            ("balanceamount", "numeric(18,2)", "余额金额"),
            ("cardamount", "numeric(18,2)", "卡金额"),
            ("couponamount", "numeric(18,2)", "券金额"),
            ("rechargecardamount", "numeric(18,2)", "充值卡金额"),
            ("giftcardamount", "numeric(18,2)", "礼品卡金额"),
            ("prepaymoney", "numeric(18,2)", "预付金额"),
            ("consumemoney", "numeric(18,2)", "消费金额"),
            ("goodsmoney", "numeric(18,2)", "商品金额"),
            ("realgoodsmoney", "numeric(18,2)", "实际商品金额"),
            ("tablechargemoney", "numeric(18,2)", "台费"),
            ("servicemoney", "numeric(18,2)", "服务费"),
            ("activitydiscount", "numeric(18,2)", "活动折扣"),
            ("allcoupondiscount", "numeric(18,2)", "全部券折扣"),
            ("goodspromotionmoney", "numeric(18,2)", "商品促销金额"),
            ("assistantpromotionmoney", "numeric(18,2)", "助教促销金额"),
            ("assistantpdmoney", "numeric(18,2)", "助教普通时段费"),
            ("assistantcxmoney", "numeric(18,2)", "助教促销时段费"),
            ("assistantmanualdiscount", "numeric(18,2)", "助教手动折扣"),
            ("couponsaleamount", "numeric(18,2)", "券销售金额"),
            ("memberdiscountamount", "numeric(18,2)", "会员折扣金额"),
            ("pointdiscountprice", "numeric(18,2)", "积分折扣价"),
            ("pointdiscountcost", "numeric(18,2)", "积分折扣成本"),
            ("adjustamount", "numeric(18,2)", "调整金额"),
            ("roundingamount", "numeric(18,2)", "抹零金额"),
            ("operatorid", "bigint", "操作员 ID"),
            ("operatorname", "text", "操作员名"),
            ("salesmanname", "text", "销售员名"),
            ("salesmanuserid", "bigint", "销售员 ID"),
            ("orderremark", "text", "订单备注"),
            ("revokeorderid", "bigint", "撤销订单 ID"),
            ("revokeordername", "text", "撤销订单名"),
            ("revoketime", "timestamptz", "撤销时间"),
            ("canberevoked", "boolean", "可撤销"),
            ("isusecoupon", "boolean", "使用券"),
            ("isusediscount", "boolean", "使用折扣"),
            ("isactivity", "boolean", "是否活动"),
            ("isbindmember", "boolean", "绑定会员"),
            ("serialnumber", "bigint", "序列号"),
            ("plcouponsaleamount", "numeric(18,2)", "平台券销售金额"),
            ("mervousalesamount", "numeric(18,2)", "美团券销售金额"),
            ("electricitymoney", "numeric(18,2)", "电费"),
            ("realelectricitymoney", "numeric(18,2)", "实际电费"),
            ("electricityadjustmoney", "numeric(18,2)", "电费调整金额"),
        ],
        "dwd_tables": [
            {"name": "dwd.dwd_recharge_order（主表）", "columns": [
                ("recharge_order_id", "bigint", "PK"),
                ("tenant_id / site_id / member_id", "", "租户/门店/会员 ID"),
                ("member_name_snapshot / member_phone_snapshot", "", "会员快照"),
                ("tenant_member_card_id / member_card_type_name", "", "会员卡"),
                ("settle_relate_id / settle_type / settle_name / is_first", "", "结算信息"),
                ("pay_amount / refund_amount / point_amount / cash_amount", "", "金额"),
                ("payment_method / create_time / pay_time", "", "支付/时间"),
                ("pl_coupon_sale_amount", "numeric(18,2)", "⚠️ DWD 列已存在，但 FACT_MAPPINGS 未配置"),
                ("mervou_sales_amount", "numeric(18,2)", "⚠️ DWD 列已存在，但 FACT_MAPPINGS 未配置"),
                ("electricity_money", "numeric(18,2)", "⚠️ DWD 列已存在，但 FACT_MAPPINGS 未配置"),
                ("real_electricity_money", "numeric(18,2)", "⚠️ DWD 列已存在，但 FACT_MAPPINGS 未配置"),
                ("electricity_adjust_money", "numeric(18,2)", "⚠️ DWD 列已存在，但 FACT_MAPPINGS 未配置"),
            ]},
            {"name": "dwd.dwd_recharge_order_ex（扩展表）", "columns": [
                ("recharge_order_id", "bigint", "PK"),
                ("site_name_snapshot / settle_status / is_bind_member / is_activity", "", "快照/状态"),
                ("is_use_coupon / is_use_discount / can_be_revoked", "", "布尔标记"),
                ("online_amount / balance_amount / card_amount / coupon_amount", "", "支付渠道金额"),
                ("recharge_card_amount / gift_card_amount / prepay_money", "", "卡/预付"),
                ("consume_money / goods_money / real_goods_money / table_charge_money / service_money", "", "消费明细"),
                ("activity_discount / all_coupon_discount / goods_promotion_money / assistant_promotion_money", "", "折扣"),
                ("assistant_pd_money / assistant_cx_money / assistant_manual_discount", "", "助教费用"),
                ("coupon_sale_amount / member_discount_amount", "", "券/会员折扣"),
                ("point_discount_price / point_discount_cost / adjust_amount / rounding_amount", "", "积分/调整"),
                ("operator_id / operator_name_snapshot / salesman_user_id / salesman_name / order_remark", "", "操作员/销售"),
                ("table_id / serial_number / revoke_order_id / revoke_order_name / revoke_time", "", "台桌/撤销"),
            ]},
        ],
        "new_fields": [
            ("plcouponsaleamount → pl_coupon_sale_amount", "numeric(18,2)", "dwd_recharge_order", "平台券销售金额。DWD 列已存在，仅缺 FACT_MAPPINGS 映射条目", "仅补 FACT_MAPPINGS"),
            ("mervousalesamount → mervou_sales_amount", "numeric(18,2)", "dwd_recharge_order", "美团券销售金额。DWD 列已存在，仅缺 FACT_MAPPINGS 映射条目", "仅补 FACT_MAPPINGS"),
            ("electricitymoney → electricity_money", "numeric(18,2)", "dwd_recharge_order", "电费。DWD 列已存在，仅缺 FACT_MAPPINGS 映射条目", "仅补 FACT_MAPPINGS"),
            ("realelectricitymoney → real_electricity_money", "numeric(18,2)", "dwd_recharge_order", "实际电费。DWD 列已存在，仅缺 FACT_MAPPINGS 映射条目", "仅补 FACT_MAPPINGS"),
            ("electricityadjustmoney → electricity_adjust_money", "numeric(18,2)", "dwd_recharge_order", "电费调整金额。DWD 列已存在，仅缺 FACT_MAPPINGS 映射条目", "仅补 FACT_MAPPINGS"),
        ],
        "skipped_fields": [],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 9. store_goods_master → dim_store_goods + _ex（B 类）
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "9. store_goods_master（门店商品档案）— B 类",
        "ods_table": "ods.store_goods_master",
        "ods_columns": [
            ("id", "bigint", "PK"),
            ("tenant_id", "bigint", "租户 ID"),
            ("site_id", "bigint", "门店 ID"),
            ("sitename", "text", "门店名"),
            ("tenant_goods_id", "bigint", "租户商品 ID"),
            ("goods_name", "text", "商品名"),
            ("goods_bar_code", "text", "条码"),
            ("goods_category_id", "bigint", "一级分类 ID"),
            ("goods_second_category_id", "bigint", "二级分类 ID"),
            ("onecategoryname", "text", "一级分类名"),
            ("twocategoryname", "text", "二级分类名"),
            ("unit", "text", "单位"),
            ("sale_price", "numeric(18,4)", "售价"),
            ("cost_price", "numeric(18,4)", "成本价"),
            ("cost_price_type", "integer", "成本价类型"),
            ("min_discount_price", "numeric(18,4)", "最低折扣价"),
            ("safe_stock", "numeric(18,4)", "安全库存"),
            ("stock", "numeric(18,4)", "当前库存"),
            ("stock_a", "numeric(18,4)", "副库存"),
            ("sale_num", "numeric(18,4)", "销售数量"),
            ("total_purchase_cost", "numeric(18,4)", "采购总成本"),
            ("total_sales", "numeric(18,4)", "总销售额"),
            ("average_monthly_sales", "numeric(18,4)", "月均销售"),
            ("batch_stock_quantity", "numeric(18,2)", "批次库存数量"),
            ("days_available", "integer", "可用天数"),
            ("provisional_total_cost", "numeric(18,2)", "暂估总成本"),
            ("enable_status", "integer", "启用状态"),
            ("audit_status", "integer", "审核状态"),
            ("goods_state", "integer", "商品状态"),
            ("is_delete", "integer", "删除标记"),
            ("is_warehousing", "integer", "是否入库"),
            ("able_discount", "integer", "可折扣"),
            ("able_site_transfer", "integer", "可门店调拨"),
            ("forbid_sell_status", "integer", "禁售状态"),
            ("freeze", "integer", "冻结"),
            ("send_state", "integer", "配送状态"),
            ("custom_label_type", "integer", "自定义标签"),
            ("option_required", "integer", "选项必填"),
            ("sale_channel", "integer", "销售渠道"),
            ("sort", "integer", "排序"),
            ("remark", "text", "备注"),
            ("pinyin_initial", "text", "拼音首字母"),
            ("goods_cover", "text", "商品封面"),
            ("create_time", "timestamp", "创建时间"),
            ("update_time", "timestamp", "更新时间"),
            ("commodity_code", "text", "商品编码"),
            ("not_sale", "integer", "禁售标记"),
        ],
        "dwd_tables": [
            {"name": "dwd.dim_store_goods（主表）", "columns": [
                ("site_goods_id", "bigint", "PK"),
                ("tenant_id / site_id / tenant_goods_id", "", "租户/门店/商品 ID"),
                ("goods_name / goods_category_id / goods_second_category_id", "", "商品/分类"),
                ("category_level1_name / category_level2_name", "", "分类名"),
                ("batch_stock_qty / sale_qty / total_sales_qty", "", "库存/销售"),
                ("sale_price / avg_monthly_sales", "", "价格/月均"),
                ("goods_state / enable_status / send_state / is_delete / commodity_code / not_sale", "", "状态"),
                ("created_at / updated_at / scd2_*", "", "时间/SCD2"),
            ]},
            {"name": "dwd.dim_store_goods_ex（扩展表）", "columns": [
                ("site_goods_id", "bigint", "PK"),
                ("site_name / unit / goods_barcode / goods_cover_url / pinyin_initial", "", "快照/基础"),
                ("stock_qty / stock_secondary_qty / safety_stock_qty", "", "库存"),
                ("cost_price / cost_price_type / provisional_total_cost / total_purchase_cost", "", "成本"),
                ("min_discount_price / is_discountable / days_on_shelf", "", "折扣/天数"),
                ("audit_status / sale_channel / is_warehousing / freeze_status", "", "状态"),
                ("forbid_sell_status / able_site_transfer / custom_label_type / option_required", "", "标记"),
                ("remark / sort_order / time_slot_sale / scd2_*", "", "备注/排序/分时段/SCD2"),
            ]},
        ],
        "new_fields": [
            ("batch_stock_quantity", "numeric(18,2)", "dim_store_goods_ex", "批次库存数量（与 stock 不同）。采样值: 35.00, 270.00, 8.00", "新增 DWD 列 + FACT_MAPPINGS"),
        ],
        "skipped_fields": [
            ("time_slot_sale", "已映射。ODS + DWD 均已新增 time_slot_sale INTEGER（2026-02-21 迁移）"),
            ("provisional_total_cost", "DWD 列名已被占用（映射自 ODS total_purchase_cost）。ODS provisional_total_cost（暂估总成本）未映射"),
        ],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 10. goods_stock_summary（C 类 — 需新建 DWD 表）
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "10. goods_stock_summary（库存汇总）— C 类，需新建 DWD 表",
        "ods_table": "ods.goods_stock_summary",
        "ods_columns": [
            ("sitegoodsid", "bigint", "门店商品 ID"),
            ("goodsname", "text", "商品名"),
            ("goodsunit", "text", "单位"),
            ("goodscategoryid", "bigint", "一级分类 ID"),
            ("goodscategorysecondid", "bigint", "二级分类 ID"),
            ("categoryname", "text", "分类名"),
            ("rangestartstock", "numeric(18,4)", "期初库存"),
            ("rangeendstock", "numeric(18,4)", "期末库存"),
            ("rangein", "numeric(18,4)", "入库数量"),
            ("rangeout", "numeric(18,4)", "出库数量"),
            ("rangesale", "numeric(18,4)", "销售数量"),
            ("rangesalemoney", "numeric(18,2)", "销售金额"),
            ("rangeinventory", "numeric(18,4)", "盘点数量"),
            ("currentstock", "numeric(18,4)", "当前库存"),
        ],
        "dwd_tables": [
            {"name": "（无 DWD 表 — 需新建 dwd_goods_stock_summary）", "columns": []},
        ],
        "new_fields": [
            ("全部 14 个 ODS 列", "", "dwd_goods_stock_summary（新建）", "需新建 DWD 表，全部 ODS 业务列映射（驼峰 → 蛇形）", "新建 DWD 表 + TABLE_MAP + FACT_MAPPINGS + loader/task"),
        ],
        "skipped_fields": [],
    },

    # ═══════════════════════════════════════════════════════════════════
    # 11. goods_stock_movements（C 类 — 需新建 DWD 表）
    # ═══════════════════════════════════════════════════════════════════
    {
        "title": "11. goods_stock_movements（库存变动明细）— C 类，需新建 DWD 表",
        "ods_table": "ods.goods_stock_movements",
        "ods_columns": [
            ("sitegoodsstockid", "bigint", "门店商品库存 ID"),
            ("tenantid", "bigint", "租户 ID"),
            ("siteid", "bigint", "门店 ID"),
            ("sitegoodsid", "bigint", "门店商品 ID"),
            ("goodsname", "text", "商品名"),
            ("goodscategoryid", "bigint", "一级分类 ID"),
            ("goodssecondcategoryid", "bigint", "二级分类 ID"),
            ("unit", "text", "单位"),
            ("price", "numeric(18,4)", "单价"),
            ("stocktype", "integer", "库存变动类型"),
            ("changenum", "numeric(18,4)", "变动数量"),
            ("startnum", "numeric(18,4)", "变动前数量"),
            ("endnum", "numeric(18,4)", "变动后数量"),
            ("changenuma", "numeric(18,4)", "副变动数量"),
            ("startnuma", "numeric(18,4)", "副变动前数量"),
            ("endnuma", "numeric(18,4)", "副变动后数量"),
            ("remark", "text", "备注"),
            ("operatorname", "text", "操作员名"),
            ("createtime", "timestamp", "创建时间"),
        ],
        "dwd_tables": [
            {"name": "（无 DWD 表 — 需新建 dwd_goods_stock_movement）", "columns": []},
        ],
        "new_fields": [
            ("全部 19 个 ODS 列", "", "dwd_goods_stock_movement（新建）", "需新建 DWD 表，全部 ODS 业务列映射（驼峰 → 蛇形）", "新建 DWD 表 + TABLE_MAP + FACT_MAPPINGS + loader/task"),
        ],
        "skipped_fields": [],
    },
]


# ── Markdown 生成 ─────────────────────────────────────────────────────

def gen_md():
    lines = []
    lines.append("# 字段补全复核文档（ODS + DWD 全量对照）\n")
    lines.append(f"> 生成时间：{__import__('datetime').datetime.now().strftime('%Y-%m-%d %H:%M')}")
    lines.append("> 用途：供人工复核字段排查结论，确认后再执行代码变更\n")
    lines.append("---\n")

    # 全局汇总
    total_new = sum(len(t["new_fields"]) for t in TABLES)
    total_skip = sum(len(t["skipped_fields"]) for t in TABLES)
    lines.append("## 全局汇总\n")
    lines.append(f"| 指标 | 数量 |")
    lines.append(f"|------|------|")
    lines.append(f"| 涉及 ODS 表 | {len(TABLES)} |")
    lines.append(f"| 待新增/补映射字段 | {total_new} |")
    lines.append(f"| 跳过字段 | {total_skip} |")
    lines.append("")

    for t in TABLES:
        lines.append(f"\n---\n")
        lines.append(f"## {t['title']}\n")

        # ODS 现有字段
        lines.append(f"### ODS 现有字段（{t['ods_table']}）\n")
        lines.append("| 列名 | 类型 | 说明 |")
        lines.append("|------|------|------|")
        for col, dtype, desc in t["ods_columns"]:
            lines.append(f"| `{col}` | {dtype} | {desc} |")
        lines.append(f"\n共 {len(t['ods_columns'])} 列（不含 ODS 元数据列 payload/content_hash/source_file/source_endpoint/fetched_at）\n")

        # DWD 现有字段
        lines.append(f"### DWD 现有字段\n")
        for dwd in t["dwd_tables"]:
            lines.append(f"#### {dwd['name']}\n")
            if dwd["columns"]:
                lines.append("| 列名 | 类型 | 说明 |")
                lines.append("|------|------|------|")
                for col, dtype, desc in dwd["columns"]:
                    lines.append(f"| `{col}` | {dtype} | {desc} |")
                lines.append(f"\n共 {len(dwd['columns'])} 列\n")
            else:
                lines.append("（无 — 需新建）\n")

        # 待新增字段
        lines.append(f"### ✅ 待新增 / 补映射字段\n")
        if t["new_fields"]:
            lines.append("| ODS 列 → DWD 列 | 类型 | 目标表 | 说明 | 操作 |")
            lines.append("|-----------------|------|--------|------|------|")
            for col, dtype, target, desc, action in t["new_fields"]:
                lines.append(f"| `{col}` | {dtype} | {target} | {desc} | {action} |")
            lines.append("")
        else:
            lines.append("无\n")

        # 跳过字段
        lines.append(f"### ⏭️ 跳过字段（含原因）\n")
        if t["skipped_fields"]:
            lines.append("| ODS 列 | 跳过原因 |")
            lines.append("|--------|---------|")
            for item in t["skipped_fields"]:
                if len(item) == 2:
                    col, reason = item
                else:
                    col, reason = item[0], item[1]
                lines.append(f"| `{col}` | {reason} |")
            lines.append("")
        else:
            lines.append("无\n")

    # 写入文件
    content = "\n".join(lines)
    with open(output_file, "w", encoding="utf-8") as f:
        f.write(content)
    print(f"✅ 复核文档已生成: {output_file}")
    print(f"   共 {len(TABLES)} 张表，{total_new} 个待新增字段，{total_skip} 个跳过字段")


if __name__ == "__main__":
    gen_md()
