AiDOPWarehouse/doc/plan/sql/uat-data/run_approval_flow_tenant_isolate.py

#!/usr/bin/env python3
"""Stamp and clone ApprovalFlow definitions so each operational tenant has its own published set.

No global TenantId=NULL fallback after this. Idempotent.
"""

from __future__ import annotations

import json
from datetime import datetime

from apply_sql_file import connect


TENANTS = (
    (838257186181189, 838257186320453, "UATA"),
    (838257212780613, 838257212858437, "UATB"),
    (838257237606469, 838257237676101, "DEMO"),
)

BIZ_TYPES = (
    "CONTRACT_REVIEW",
    "ORDER_REVIEW",
    "ORDER_CHANGE_REVIEW",
    "EXCEPTION_REPORT",
    "EXCEPTION_ESCALATION",
    "EXCEPTION_CLOSURE",
    "MATERIAL_SHORTAGE",
    "S5_IQC_INSPBILL",
    "IPQC_INSPECTION",
    "S7_FQC_INSPBILL",
    "SMART_OPS_IMPROVEMENT",
)

ID_BASE = 9206082610000000


def main() -> None:
    conn = connect()
    conn.autocommit(False)
    result = {"stamped": 0, "cloned": []}
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE ApprovalFlow f
                INNER JOIN SysOrg o ON o.Id = f.OrgId
                SET f.TenantId = o.TenantId
                WHERE (f.TenantId IS NULL OR f.TenantId = 0)
                  AND f.OrgId > 0
                  AND o.TenantId IS NOT NULL
                  AND o.TenantId > 0
                """
            )
            result["stamped"] = cur.rowcount

            next_id = ID_BASE
            now = datetime.now().replace(microsecond=0)
            for tenant_id, org_id, prefix in TENANTS:
                for biz in BIZ_TYPES:
                    cur.execute(
                        """
                        SELECT Id FROM ApprovalFlow
                        WHERE TenantId=%s AND BizType=%s AND IsPublished=1 AND IFNULL(IsDelete,0)=0
                        LIMIT 1
                        """,
                        (tenant_id, biz),
                    )
                    if cur.fetchone():
                        continue
                    cur.execute(
                        """
                        SELECT Id, Code, Name FROM ApprovalFlow
                        WHERE BizType=%s AND IsPublished=1 AND IFNULL(IsDelete,0)=0
                          AND (BizType NOT LIKE 'E2E%%')
                        ORDER BY CASE WHEN TenantId IS NOT NULL AND TenantId>0 THEN 0 ELSE 1 END,
                                 Version DESC, Id DESC
                        LIMIT 1
                        """,
                        (biz,),
                    )
                    src = cur.fetchone()
                    if not src:
                        result["cloned"].append({"tenant": prefix, "biz": biz, "status": "no-source"})
                        continue
                    new_id = next_id
                    next_id += 1
                    new_code = f"{prefix}-{src['Code']}"[:64]
                    cur.execute(
                        "SELECT 1 FROM ApprovalFlow WHERE Code=%s AND TenantId=%s LIMIT 1",
                        (new_code, tenant_id),
                    )
                    if cur.fetchone():
                        result["cloned"].append({"tenant": prefix, "biz": biz, "status": "code-exists"})
                        continue
                    cur.execute(
                        """
                        INSERT INTO ApprovalFlow
                          (Id, Code, Name, FormJson, FlowJson, Status, Remark, BizType,
                           Version, IsPublished, OrgId, TenantId, CreateTime, UpdateTime,
                           CreateUserName, IsDelete)
                        SELECT
                          %s, %s, Name, FormJson, FlowJson, Status, Remark, BizType,
                          Version, 1, %s, %s, %s, %s,
                          'TENANT-ISOLATE', 0
                        FROM ApprovalFlow WHERE Id=%s
                        """,
                        (new_id, new_code, org_id, tenant_id, now, now, src["Id"]),
                    )
                    result["cloned"].append(
                        {
                            "tenant": prefix,
                            "biz": biz,
                            "status": "cloned",
                            "from": src["Code"],
                            "to": new_code,
                            "id": new_id,
                        }
                    )

            cur.execute(
                """
                SELECT TenantId, COUNT(*) c FROM ApprovalFlow
                WHERE IFNULL(IsDelete,0)=0 AND IsPublished=1
                  AND TenantId IN (838257186181189,838257212780613,838257237606469)
                GROUP BY TenantId
                """
            )
            result["publishedByTenant"] = cur.fetchall()
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()
    print(json.dumps(result, ensure_ascii=False, indent=2, default=str))


if __name__ == "__main__":
    main()