# -*- coding: utf-8 -*-
"""重新生成 docs/sql/config_export_all_20260928.sql（全量重生成，替换 0918 版）。

阶段一：当前源库 ruoyi-vue-pro 全库 416 张表 CREATE TABLE + 6 类配置数据（mysqldump 字面量）。
阶段二：沿用 0918 文件的跨库身份/菜单/OAuth2 补导段（逐字复用）。
"""
import re
import subprocess
import sys

MYSQL_BIN = r"D:\MySQL\mysql-8.0.31-winx64\bin"
DB = "ruoyi-vue-pro"
USER = "root"
PWD = "1234"

BASE_DIR = r"D:\develop\XingRuan\MOM\mom-pro2-after\docs\sql"
SRC_0918 = BASE_DIR + r"\config_export_all_20260918.sql"
OUT = BASE_DIR + r"\config_export_all_20260928.sql"

MYSQL = MYSQL_BIN + r"\mysql.exe"
MYSQLDUMP = MYSQL_BIN + r"\mysqldump.exe"

COMMON = ["--default-character-set=utf8mb4", "--no-tablespaces", "--skip-comments",
          "--skip-add-locks", "--skip-disable-keys", "--skip-triggers",
          "--column-statistics=0", "--set-gtid-purged=OFF", "--single-transaction"]

# ---------------- 6 类配置表（表名, 过滤条件 None=全表） ----------------
SECTIONS = [
    ("① 协同审批流程（引擎元数据+定义，仅激活流程，剔历史/运行/身份）", [
        ("act_ge_property", None),
        ("act_id_property", None),
        ("act_re_procdef", "SUSPENSION_STATE_=1"),
        ("act_re_deployment", "ID_ IN (SELECT DEPLOYMENT_ID_ FROM act_re_procdef WHERE SUSPENSION_STATE_=1)"),
        ("act_ge_bytearray",
         "DEPLOYMENT_ID_ IN (SELECT DEPLOYMENT_ID_ FROM act_re_procdef WHERE SUSPENSION_STATE_=1) "
         "OR NAME_ IN ('source','source-extra')"),
        ("act_re_model", None),
        ("bpm_process_definition_info", "deleted=0"),
    ]),
    ("② 协同（业务附件配置 + 分类 + 表单 + 扩展配置）", [
        ("bpm_business_attach", "deleted=0"),
        ("bpm_category", "deleted=0"),
        ("bpm_form", "deleted=0"),
        ("bpm_user_group", "deleted=0"),
        ("bpm_process_listener", "deleted=0"),
        ("bpm_process_expression", "deleted=0"),
    ]),
    ("③ BI 决策大屏 / go-view", [
        ("bi_dashboard", "deleted=0"),
        ("bi_chart_config", "deleted=0"),
        ("bi_data_source_config", "deleted=0"),
        ("bi_warehouse_coordinate", "deleted=0"),
        ("report_go_view_project", "deleted=0"),
    ]),
    ("④ MES 编码规则（剔流水记录）", [
        ("mes_md_auto_code_rule", "deleted=0"),
        ("mes_md_auto_code_part", "deleted=0"),
    ]),
    ("⑤ 系统配置主数据（字典/租户/公司配置；剔身份/菜单/日志）", [
        ("system_dict_type", "deleted=0"),
        ("system_dict_data", "deleted=0"),
        ("system_tenant", "deleted=0"),
        ("system_tenant_package", "deleted=0"),
        ("system_company_config", "deleted=0"),
    ]),
    ("⑥ AI 配置（仅模型+key）+ 基础设施配置管理", [
        ("ai_model", "deleted=0"),
        ("ai_api_key", "deleted=0"),
        ("infra_config", "deleted=0"),
        ("infra_job", "deleted=0"),
        ("infra_file_config", "deleted=0"),
        ("infra_data_source_config", "deleted=0"),
        ("infra_codegen_table", "deleted=0"),
        ("infra_codegen_column", "deleted=0"),
    ]),
]


def run(cmd):
    p = subprocess.run(cmd, capture_output=True)
    if p.returncode != 0:
        sys.stderr.write("FAILED: %s\n%s\n" % (" ".join(cmd), p.stderr.decode("utf-8", "replace")))
        sys.exit(1)
    return p.stdout.decode("utf-8")


def strip_mysqldump_noise(text):
    out = []
    for line in text.splitlines():
        if line.startswith("/*!") or line.startswith("--"):
            continue
        out.append(line)
    return out


def fetch_tables():
    sql = ("SELECT table_name FROM information_schema.tables "
           "WHERE table_schema='%s' AND table_type='BASE TABLE' ORDER BY table_name" % DB)
    out = run([MYSQL, "-u" + USER, "-p" + PWD, "--default-character-set=utf8mb4", "-N", "-B", "-e", sql])
    return [l.strip() for l in out.splitlines() if l.strip()]


def fetch_view_count():
    sql = ("SELECT COUNT(*) FROM information_schema.views WHERE table_schema='%s'" % DB)
    out = run([MYSQL, "-u" + USER, "-p" + PWD, "--default-character-set=utf8mb4", "-N", "-B", "-e", sql])
    return int(out.strip())


def dump_ddl(tables):
    """一次导出全部表结构，切分成 {表名: ddl块}。"""
    out = run([MYSQLDUMP, "-u" + USER, "-p" + PWD] + COMMON + ["--no-data", DB] + tables)
    lines = strip_mysqldump_noise(out)
    blocks = {}
    cur = None
    buf = []
    for line in lines:
        m = re.match(r"^DROP TABLE IF EXISTS `([^`]+)`;", line)
        if m:
            if cur:
                blocks[cur] = "\n".join(buf).rstrip()
            cur = m.group(1)
            buf = [line]
        elif cur is not None:
            buf.append(line)
    if cur:
        blocks[cur] = "\n".join(buf).rstrip()
    return blocks


def dump_data(tables_filters):
    """tables_filters: [(table, where)] 相同 where 的分组调用；返回 {表名: insert语句}。"""
    groups = {}
    for t, w in tables_filters:
        if w is None:
            groups.setdefault(None, []).append(t)
        else:
            groups.setdefault(w, []).append(t)
    data = {}
    for where, tables in groups.items():
        cmd = [MYSQLDUMP, "-u" + USER, "-p" + PWD] + COMMON + \
              ["--no-create-info", "--complete-insert", "--hex-blob", "--order-by-primary"]
        if where is not None:
            cmd.append("--where=" + where)
        cmd.append(DB)
        cmd += tables
        out = run(cmd)
        lines = strip_mysqldump_noise(out)
        stmt_lines = []
        for line in lines:
            if not line.strip():
                continue
            if line.startswith("INSERT INTO "):
                stmt_lines = [line]
            elif stmt_lines:
                stmt_lines.append(line)
            else:
                sys.stderr.write("UNEXPECTED line: %s\n" % line[:120])
                continue
            stmt = "\n".join(stmt_lines)
            if stmt.rstrip().endswith(";"):
                m = re.match(r"^INSERT INTO `([^`]+)`", stmt)
                data[m.group(1)] = stmt.rstrip()
                stmt_lines = []
    return data


def fetch_counts(tables_filters):
    parts = []
    for t, w in tables_filters:
        parts.append("SELECT '%s' AS t, COUNT(*) AS c FROM `%s`%s"
                     % (t, t, (" WHERE " + w) if w else ""))
    sql = " UNION ALL ".join(parts)
    out = run([MYSQL, "-u" + USER, "-p" + PWD, "--default-character-set=utf8mb4", "-N", "-B", DB, "-e", sql])
    counts = {}
    for line in out.splitlines():
        if not line.strip():
            continue
        k, v = line.split("\t")
        counts[k] = int(v)
    return counts


def load_stage2_block():
    with open(SRC_0918, encoding="utf-8") as f:
        lines = f.read().splitlines()
    for i, line in enumerate(lines):
        if "阶段二：补导身份" in line:
            start = i - 1  # 上方 =============== 横线
            return "\n".join(lines[start:]).rstrip() + "\n"
    raise RuntimeError("0918 文件未找到阶段二段落")


HEADER = """-- =======================================================================
-- 文件：config_export_all_20260928.sql
-- 用途：ruoyi-vue-pro「全库表结构 + 6 类配置数据」导出，供同结构全新库初始化
--       阶段一：全库 {n_tables} 张表 CREATE TABLE + 6 类配置数据（字面量，自包含，不依赖源库在线）
--       阶段二：跨库补导 菜单/角色/用户/OAuth2 等身份数据（执行期从源库拷贝）
-- 来源库：ruoyi-vue-pro
-- 基线：2026-09-18 版（419 张表）→ 本次为**全量重生成**（不再基于旧文件打补丁）：
--       直接按当前源库最新状态重导，一次性覆盖 0918 之后全部结构与配置漂移。
-- 本次变更（相对 0918，2026-09-28）：
--   ① 表结构：419 → {n_tables} 张
--      - 删除 6 张历史备份表（bpm_bak_20260904_bytearray/category/deployment/model/procdef/procinfo，源库已无）
--      + 新增 3 张表（after_sale_problem、after_sale_visit、bpm_process_auto_approve_config，仅结构，源库无数据）
--      + 10 张表 DDL 与 0918 不同，以当前源库为准（after_sale_ticket、mes_qc_indicator、mes_qc_indicator_result_detail、
--        mes_qc_ipqc、mes_qc_iqc、mes_qc_oqc、mes_qc_rqc、mes_qc_template、mes_qc_template_indicator、mes_wm_return_issue_line）
--   ② 配置数据以当前源库为准重导（下列为 0918 声明值 → 本次导出值）：
--      - system_dict_type 241→{n_dicttype}、system_dict_data 1157→{n_dictdata}（含 hrm_holiday_type 节假日类型等新增字典）
--      - system_company_config 0→1、bi_chart_config 36→37、bi_warehouse_coordinate 1→2、ai_api_key 1→3、bpm_user_group 3→4
--      - bpm_business_attach 11→4、bpm_category 22→15、bpm_form 4→3、bpm_process_listener 1→0（源库已删除的行不再导出）
--      - 协同流程定义按源库当前**激活**集合重导（{n_procdef} 个流程定义 + 对应部署/字节资源 + 设计态模型）
--   ③ 菜单/权限节点（节假日配置 6014/60141-60145、各模块导入按钮 1075311/1075417-1075420 等）由阶段二
--      跨库全量拷贝自动携带，无需改动本文件数据段；在此登记以说明本版文件确实覆盖这些变更。
-- 验证：{verify}
-- 范围：全库 BASE TABLE 共 {n_tables} 张，全部输出 CREATE TABLE；
--       仅以下配置类表带数据(INSERT)，其余业务/系统/运行历史表只保留空表结构：
--     ① 协同审批流程（引擎元数据+定义，仅激活流程，剔历史/运行/身份）：act_ge_property、act_id_property、act_re_procdef、act_re_deployment、act_ge_bytearray、act_re_model、bpm_process_definition_info
--     ② 协同（业务附件配置 + 分类 + 表单 + 扩展配置）：bpm_business_attach、bpm_category、bpm_form、bpm_user_group、bpm_process_listener、bpm_process_expression
--     ③ BI 决策大屏 / go-view：bi_dashboard、bi_chart_config、bi_data_source_config、bi_warehouse_coordinate、report_go_view_project
--     ④ MES 编码规则（剔流水记录）：mes_md_auto_code_rule、mes_md_auto_code_part
--     ⑤ 系统配置主数据（字典/租户/公司配置；剔身份/菜单/日志）：system_dict_type、system_dict_data、system_tenant、system_tenant_package、system_company_config
--     ⚠ 身份/权限类表（system_users/role/menu/role_menu/user_role/dept/post/oauth2_client）此处仅建空表结构，数据由文件末尾"阶段二"跨库补导。
--     ⑥ AI 配置（仅模型+key）+ 基础设施配置管理：ai_model、ai_api_key、infra_config、infra_job、infra_file_config、infra_data_source_config、infra_codegen_table、infra_codegen_column
-- 过滤：协同流程仅保留激活(SUSPENSION_STATE_=1)定义及其部署资源（bpmn/png 字节）；
--       设计态模型 act_re_model 与模型源码字节（NAME_=source/source-extra）按源库全量导出（含个别已删除流程残留的模型，
--       源库实际存在即保留，供流程设计器打开）；运行期变量字节（var-*/hist.var-*）一律不导出；
--       其余配置表仅导 deleted=0；编码规则剔除流水记录 mes_md_auto_code_record。
-- ⚠ 脚本含全库 DROP TABLE IF EXISTS，会把除本脚本建表外的所有表删除重建，
--   业务表数据将全部清空，仅可在全新/可覆盖的空库执行！
-- =======================================================================
"""

VERIFY_PLACEHOLDER = "（待空库整份执行验证后回填；见文件末尾生成备注）"


def main():
    tables = fetch_tables()
    views = fetch_view_count()
    if views:
        print("WARNING: 源库存在 %d 个视图，本生成器未处理" % views)

    ddl = dump_ddl(tables)
    missing = [t for t in tables if t not in ddl]
    extra = [t for t in ddl if t not in tables]
    if missing or extra:
        print("DDL 集合不一致 missing=%s extra=%s" % (missing, extra))
        sys.exit(1)
    print("tables=%d ddl_blocks=%d" % (len(tables), len(ddl)))

    all_cfg = [tf for _, tfs in SECTIONS for tf in tfs]
    cfg_names = [t for t, _ in all_cfg]
    counts = fetch_counts(all_cfg)
    data = dump_data(all_cfg)
    for t in cfg_names:
        if counts.get(t, 0) > 0 and t not in data:
            print("MISSING data for %s (count=%d)" % (t, counts[t]))
            sys.exit(1)
    print("config tables=%d, with data=%d" % (len(cfg_names), len(data)))

    rest = [t for t in tables if t not in set(cfg_names)]
    print("rest tables=%d" % len(rest))

    parts = []
    header = HEADER.format(
        n_tables=len(tables),
        n_dicttype=counts["system_dict_type"],
        n_dictdata=counts["system_dict_data"],
        n_procdef=counts["act_re_procdef"],
        verify=VERIFY_PLACEHOLDER,
    )
    parts.append(header)
    parts.append("SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS = 0;\n"
                 "SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n\n")

    for title, tfs in SECTIONS:
        parts.append("\n-- ***********************************************************************\n")
        parts.append("-- %s（DDL + 数据）\n" % title)
        parts.append("-- ***********************************************************************\n\n")
        for t, w in tfs:
            n = counts.get(t, 0)
            parts.append("-- ============ 表：%s（导出 %d 行） ============\n" % (t, n))
            parts.append(ddl[t] + "\n")
            if n > 0:
                parts.append(data[t] + "\n")
            parts.append("\n")

    parts.append("\n-- ***********************************************************************\n")
    parts.append("-- 其余 %d 张表（仅表结构，无数据）\n" % len(rest))
    parts.append("-- ***********************************************************************\n\n")
    for t in rest:
        parts.append("-- ============ 表：%s（仅 DDL，无数据） ============\n" % t)
        parts.append(ddl[t] + "\n\n")

    parts.append("\nSET FOREIGN_KEY_CHECKS = 1;\nSET SQL_MODE = @OLD_SQL_MODE;\n\n\n")
    parts.append(load_stage2_block())

    text = "".join(parts)
    with open(OUT, "w", encoding="utf-8", newline="\n") as f:
        f.write(text)
    print("WROTE %s bytes=%d lines=%d" % (OUT, len(text.encode("utf-8")), text.count("\n")))


if __name__ == "__main__":
    main()