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

#!/usr/bin/env python3
"""Seed tenant-scoped contract review / flow / product design facts for S1 KPI pipeline.

Writes only business tables. S1 rebuild must extract STG and compute KPI day values.
"""

from __future__ import annotations

import argparse
import json
from datetime import datetime, timedelta
from typing import Any

from apply_sql_file import connect


DEFAULT_TENANT = 838257186181189
PREFIX = "UAT-CR-"
PD_PREFIX = "UAT-PD-"
PROCESS_PREFIX = "UAT-PROC-"
DELIVERY_PREFIX = "UAT-DEL-"
STAGES = (
    (1, "意见评审"),
    (2, "意见反馈"),
    (3, "二次评审"),
    (4, "领导意见"),
    (5, "合同盖章"),
)
DEPTS = (
    ("LAW", "法律事务部"),
    ("PRE_SALES", "技术售前组"),
    ("MPS", "综合主计划"),
    ("TEST", "试验站"),
)


def seed(tenant_id: int, dry_run: bool) -> dict[str, Any]:
    today = datetime.now().replace(microsecond=0)
    reviews = 14
    result: dict[str, Any] = {
        "tenantId": tenant_id,
        "dryRun": dry_run,
        "reviews": reviews,
        "insertedReviews": 0,
        "insertedFlows": 0,
        "insertedDesigns": 0,
    }
    conn = connect()
    try:
        conn.begin()
        with conn.cursor() as cur:
            cur.execute(
                "DELETE FROM ado_contract_review_flow WHERE tenant_id=%s AND ReviewBillNo LIKE %s",
                (tenant_id, f"{PREFIX}%"),
            )
            cur.execute(
                "DELETE FROM ado_contract_review WHERE tenant_id=%s AND BillNo LIKE %s",
                (tenant_id, f"{PREFIX}%"),
            )
            for branch_prefix in (PROCESS_PREFIX, DELIVERY_PREFIX):
                cur.execute(
                    "DELETE FROM ado_contract_review_flow WHERE tenant_id=%s AND ReviewBillNo LIKE %s",
                    (tenant_id, f"{branch_prefix}%"),
                )
                cur.execute(
                    "DELETE FROM ado_contract_review WHERE tenant_id=%s AND BillNo LIKE %s",
                    (tenant_id, f"{branch_prefix}%"),
                )
            cur.execute(
                "DELETE FROM ado_product_design WHERE tenant_id=%s AND BillNo LIKE %s",
                (tenant_id, f"{PD_PREFIX}%"),
            )
            if dry_run:
                conn.rollback()
                result["wouldDelete"] = True
                return result

            for index in range(reviews):
                day = today - timedelta(days=reviews - 1 - index)
                create_time = day.replace(hour=8, minute=0, second=0)
                update_time = create_time + timedelta(hours=18 + (index % 6))
                bill_no = f"{PREFIX}A-{index + 1:04d}"
                cur.execute(
                    """
                    INSERT INTO ado_contract_review
                      (BillNo, Title, CustomerName, CustomerNo, ResponsibleAccount, ResponsibleName,
                       CurrentStage, FlowStatus, CreateUser, CreateTime, UpdateUser, UpdateTime,
                       IsActive, tenant_id)
                    VALUES
                      (%s, %s, %s, %s, %s, %s, 5, 'completed', %s, %s, %s, %s, 1, %s)
                    """,
                    (
                        bill_no,
                        f"UAT合同评审 {index + 1:04d}",
                        f"UAT客户{(index % 4) + 1}",
                        f"UAT-CUS-{(index % 4) + 1:03d}",
                        f"uat.reviewer{(index % 3) + 1}",
                        f"评审人{(index % 3) + 1}",
                        f"uat.reviewer{(index % 3) + 1}",
                        create_time,
                        f"uat.reviewer{(index % 3) + 1}",
                        update_time,
                        tenant_id,
                    ),
                )
                review_id = int(cur.lastrowid)
                result["insertedReviews"] += 1

                cursor_time = create_time
                for stage_no, stage_name in STAGES:
                    stage_hours = 2 + (stage_no % 3)
                    if stage_no == 1:
                        for seq, (dept_no, dept_name) in enumerate(DEPTS, 1):
                            start = cursor_time + timedelta(hours=seq - 1)
                            complete = start + timedelta(hours=1, minutes=20 + seq * 5)
                            cur.execute(
                                """
                                INSERT INTO ado_contract_review_flow
                                  (ReviewRecID, ReviewBillNo, StageNo, StageName, Department, DeptNo, Seq,
                                   ReviewerAccount, ReviewerName, StartTime, CompleteTime, ActualDays,
                                   NodeStatus, tenant_id)
                                VALUES
                                  (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'approved', %s)
                                """,
                                (
                                    review_id, bill_no, stage_no, stage_name, dept_name, dept_no, seq,
                                    f"uat.{dept_no.lower()}", dept_name, start, complete,
                                    round((complete - start).total_seconds() / 86400, 4),
                                    tenant_id,
                                ),
                            )
                            result["insertedFlows"] += 1
                        cursor_time = cursor_time + timedelta(hours=stage_hours)
                    else:
                        start = cursor_time
                        complete = start + timedelta(hours=stage_hours)
                        cur.execute(
                            """
                            INSERT INTO ado_contract_review_flow
                              (ReviewRecID, ReviewBillNo, StageNo, StageName, Department, DeptNo, Seq,
                               ReviewerAccount, ReviewerName, StartTime, CompleteTime, ActualDays,
                               NodeStatus, tenant_id)
                            VALUES
                              (%s, %s, %s, %s, %s, 'MPS', 1, %s, %s, %s, %s, %s, 'approved', %s)
                            """,
                            (
                                review_id, bill_no, stage_no, stage_name, stage_name,
                                f"uat.stage{stage_no}", stage_name, start, complete,
                                round((complete - start).total_seconds() / 86400, 4),
                                tenant_id,
                            ),
                        )
                        result["insertedFlows"] += 1
                        cursor_time = complete

                for branch_index, (branch_prefix, branch_name) in enumerate(
                    ((PROCESS_PREFIX, "工艺评审"), (DELIVERY_PREFIX, "交期评审")),
                    1,
                ):
                    branch_bill_no = f"{branch_prefix}A-{index + 1:04d}"
                    branch_update = create_time + timedelta(hours=10 + branch_index * 4 + index % 5)
                    cur.execute(
                        """
                        INSERT INTO ado_contract_review
                          (BillNo, Title, CustomerName, CustomerNo, ResponsibleAccount, ResponsibleName,
                           CurrentStage, FlowStatus, CreateUser, CreateTime, UpdateUser, UpdateTime,
                           IsActive, tenant_id)
                        VALUES
                          (%s,%s,%s,%s,%s,%s,5,'completed',%s,%s,%s,%s,1,%s)
                        """,
                        (
                            branch_bill_no,
                            f"UAT{branch_name} {index + 1:04d}",
                            f"UAT客户{(index % 4) + 1}",
                            f"UAT-CUS-{(index % 4) + 1:03d}",
                            f"uat.{branch_name}{(index % 3) + 1}",
                            f"{branch_name}人{(index % 3) + 1}",
                            f"uat.{branch_name}{(index % 3) + 1}",
                            create_time,
                            f"uat.{branch_name}{(index % 3) + 1}",
                            branch_update,
                            tenant_id,
                        ),
                    )
                    branch_review_id = int(cur.lastrowid)
                    result["insertedReviews"] += 1
                    branch_cursor = create_time
                    for stage_no, stage_name in STAGES:
                        stage_hours = 1 + branch_index + stage_no % 4
                        if stage_no == 1:
                            for seq, (dept_no, dept_name) in enumerate(DEPTS, 1):
                                start = branch_cursor + timedelta(minutes=30 * (seq - 1))
                                complete = start + timedelta(hours=stage_hours, minutes=seq * 7)
                                cur.execute(
                                    """
                                    INSERT INTO ado_contract_review_flow
                                      (ReviewRecID,ReviewBillNo,StageNo,StageName,Department,DeptNo,Seq,
                                       ReviewerAccount,ReviewerName,StartTime,CompleteTime,ActualDays,
                                       NodeStatus,tenant_id)
                                    VALUES
                                      (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'approved',%s)
                                    """,
                                    (
                                        branch_review_id, branch_bill_no, stage_no, stage_name,
                                        dept_name, dept_no, seq, f"uat.{dept_no.lower()}",
                                        dept_name, start, complete,
                                        round((complete - start).total_seconds() / 86400, 4),
                                        tenant_id,
                                    ),
                                )
                                result["insertedFlows"] += 1
                            branch_cursor += timedelta(hours=stage_hours)
                        else:
                            start = branch_cursor
                            complete = start + timedelta(hours=stage_hours)
                            cur.execute(
                                """
                                INSERT INTO ado_contract_review_flow
                                  (ReviewRecID,ReviewBillNo,StageNo,StageName,Department,DeptNo,Seq,
                                   ReviewerAccount,ReviewerName,StartTime,CompleteTime,ActualDays,
                                   NodeStatus,tenant_id)
                                VALUES
                                  (%s,%s,%s,%s,%s,'MPS',1,%s,%s,%s,%s,%s,'approved',%s)
                                """,
                                (
                                    branch_review_id, branch_bill_no, stage_no, stage_name, stage_name,
                                    f"uat.{branch_prefix.lower()}.stage{stage_no}", stage_name,
                                    start, complete,
                                    round((complete - start).total_seconds() / 86400, 4),
                                    tenant_id,
                                ),
                            )
                            result["insertedFlows"] += 1
                            branch_cursor = complete

                if index == 0:
                    cur.execute("SELECT COALESCE(MAX(Id), 0) AS max_id FROM ado_product_design")
                    design_base = int(cur.fetchone()["max_id"])
                design_id = design_base + index + 1
                draw_start = create_time + timedelta(days=1)
                draw_end = draw_start + timedelta(hours=20 + index % 8)
                plan_end = draw_start + timedelta(hours=24)
                cur.execute(
                    """
                    INSERT INTO ado_product_design
                      (Id, BillNo, ContractNo, ProductKind, DesignLeadAccount, DesignLeadName,
                       DrawingNo, DrawingPlanStart, DrawingPlanEnd, DrawingDesignCycle,
                       DrawingActualStart, DrawingActualEnd, Applicant, ApplyDate,
                       ProductModel, ItemNum, ProductName, Qty, CreateUser, CreateTime,
                       UpdateUser, UpdateTime, IsActive, tenant_id)
                    VALUES
                      (%s, %s, %s, 1, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                       %s, %s, %s, 1, %s, %s, %s, %s, 1, %s)
                    """,
                    (
                        design_id,
                        f"{PD_PREFIX}A-{index + 1:04d}",
                        bill_no,
                        f"uat.design{(index % 2) + 1}",
                        f"设计人{(index % 2) + 1}",
                        f"DWG-{index + 1:04d}",
                        draw_start,
                        plan_end,
                        int((draw_end - draw_start).total_seconds() // 3600),
                        draw_start,
                        draw_end,
                        f"uat.reviewer{(index % 3) + 1}",
                        create_time,
                        f"UAT-MODEL-{(index % 5) + 1}",
                        f"UAT-ITEM-{(index % 5) + 1:03d}",
                        f"UAT产品{index + 1}",
                        f"uat.design{(index % 2) + 1}",
                        create_time,
                        f"uat.design{(index % 2) + 1}",
                        draw_end,
                        tenant_id,
                    ),
                )
                result["insertedDesigns"] += 1
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()
    return result


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--tenant", type=int, default=DEFAULT_TENANT)
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()
    print(json.dumps(seed(args.tenant, args.dry_run), ensure_ascii=False, indent=2, default=str))


if __name__ == "__main__":
    main()