-- 自动创建设备参数月分表与 RT 触发器
-- 兼容：Oracle 11 / Oracle 19
-- 说明：
-- 1. 数据库在每月 26 日提前创建下个月物理分表与触发器，避免跨月首日出现写入空窗。
-- 2. C# 采集程序使用 sqlSugar 按 collect_time 自动路由到对应月分表，不再负责建表/补触发器。
-- 3. 自动采集月分表结构必须与 BASE_DEVICE_PARAM_VAL_202603.sql 保持一致，所以这里显式按该表结构建表。

CREATE OR REPLACE PACKAGE PKG_DEVICE_PARAM_MONTHLY AS

    PROCEDURE PREPARE_MONTH(
        P_SUFFIX IN VARCHAR2
    );

    PROCEDURE AUTO_PREPARE_NEXT_MONTH;

END PKG_DEVICE_PARAM_MONTHLY;
/

CREATE OR REPLACE PACKAGE BODY PKG_DEVICE_PARAM_MONTHLY AS

    C_TABLE_PREFIX          CONSTANT VARCHAR2(64) := 'BASE_DEVICE_PARAM_VAL_';
    C_TRIGGER_PREFIX        CONSTANT VARCHAR2(64) := 'TRG_BDPV_';
    PROCEDURE VALIDATE_SUFFIX(P_SUFFIX IN VARCHAR2) IS
    BEGIN
        IF P_SUFFIX IS NULL OR NOT REGEXP_LIKE(P_SUFFIX, '^\d{6}$') THEN
            RAISE_APPLICATION_ERROR(-20021, '非法月份后缀: ' || NVL(P_SUFFIX, 'NULL'));
        END IF;
    END VALIDATE_SUFFIX;

    FUNCTION TABLE_EXISTS(P_TABLE_NAME IN VARCHAR2) RETURN NUMBER IS
        V_COUNT NUMBER;
    BEGIN
        SELECT COUNT(1)
          INTO V_COUNT
          FROM USER_TABLES
         WHERE TABLE_NAME = UPPER(P_TABLE_NAME);
        RETURN V_COUNT;
    END TABLE_EXISTS;

    PROCEDURE EXECUTE_DDL(P_SQL IN CLOB) IS
        V_CURSOR INTEGER;
    BEGIN
        V_CURSOR := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(V_CURSOR, P_SQL, DBMS_SQL.NATIVE);
        DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
    EXCEPTION
        WHEN OTHERS THEN
            IF DBMS_SQL.IS_OPEN(V_CURSOR) THEN
                DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
            END IF;
            RAISE;
    END EXECUTE_DDL;

    PROCEDURE ENSURE_MONTH_TABLE(
        P_SUFFIX IN VARCHAR2
    ) IS
        V_TABLE_NAME VARCHAR2(128);
        V_PK_NAME    VARCHAR2(128);
        V_SQL        CLOB;
    BEGIN
        V_TABLE_NAME := C_TABLE_PREFIX || P_SUFFIX;
        V_PK_NAME := 'PK_' || V_TABLE_NAME || '_RECORD_ID';

        IF TABLE_EXISTS(V_TABLE_NAME) = 0 THEN
            -- 为什么显式按 202603 表建：
            -- BASE_DEVICE_PARAM_VAL 主表的 RECORD_ID 是 NUMBER，
            -- 但自动采集月分表使用 C# GUID，必须与现有 BASE_DEVICE_PARAM_VAL_202603 保持同构。
            V_SQL := 'CREATE TABLE ' || V_TABLE_NAME || ' (' || CHR(10) ||
                     '  RECORD_ID VARCHAR2(32 BYTE) NOT NULL,' || CHR(10) ||
                     '  PARAM_CODE VARCHAR2(255 BYTE) NOT NULL,' || CHR(10) ||
                     '  DEVICE_CODE VARCHAR2(255 BYTE) NOT NULL,' || CHR(10) ||
                     '  DEVICE_ID NUMBER NOT NULL,' || CHR(10) ||
                     '  PARAM_NAME VARCHAR2(255 BYTE) NOT NULL,' || CHR(10) ||
                     '  PARAM_VALUE VARCHAR2(255 BYTE) NOT NULL,' || CHR(10) ||
                     '  COLLECT_TIME DATE NOT NULL,' || CHR(10) ||
                     '  RECORD_TIME DATE NOT NULL,' || CHR(10) ||
                     '  PARAM_TYPE VARCHAR2(255 BYTE) NOT NULL' || CHR(10) ||
                     ')' || CHR(10) ||
                     'LOGGING' || CHR(10) ||
                     'NOCOMPRESS' || CHR(10) ||
                     'PCTFREE 10' || CHR(10) ||
                     'INITRANS 1' || CHR(10) ||
                     'STORAGE (' || CHR(10) ||
                     '  INITIAL 65536 ' || CHR(10) ||
                     '  NEXT 1048576 ' || CHR(10) ||
                     '  MINEXTENTS 1' || CHR(10) ||
                     '  MAXEXTENTS 2147483645' || CHR(10) ||
                     '  BUFFER_POOL DEFAULT' || CHR(10) ||
                     ')' || CHR(10) ||
                     'PARALLEL 1' || CHR(10) ||
                     'NOCACHE' || CHR(10) ||
                     'DISABLE ROW MOVEMENT';
            EXECUTE_DDL(V_SQL);
            EXECUTE IMMEDIATE 'ALTER TABLE ' || V_TABLE_NAME ||
                              ' ADD CONSTRAINT ' || V_PK_NAME ||
                              ' PRIMARY KEY (RECORD_ID)';
        END IF;
    END ENSURE_MONTH_TABLE;

    PROCEDURE CREATE_OR_REPLACE_TRIGGER(
        P_SUFFIX IN VARCHAR2
    ) IS
        V_TABLE_NAME   VARCHAR2(128);
        V_TRIGGER_NAME VARCHAR2(128);
        V_SQL          CLOB;
    BEGIN
        V_TABLE_NAME := C_TABLE_PREFIX || P_SUFFIX;
        V_TRIGGER_NAME := C_TRIGGER_PREFIX || P_SUFFIX || '_RT';

        V_SQL := 'CREATE OR REPLACE TRIGGER ' || V_TRIGGER_NAME || CHR(10) ||
                 'AFTER INSERT ON ' || V_TABLE_NAME || CHR(10) ||
                 'FOR EACH ROW' || CHR(10) ||
                 'DECLARE' || CHR(10) ||
                 '    V_PROD_DATE   DATE;' || CHR(10) ||
                 '    V_NEW_VAL     NUMBER(18,4);' || CHR(10) ||
                 '    V_LAST_VAL    NUMBER(18,4);' || CHR(10) ||
                 '    V_CUR_TOTAL   NUMBER(18,4);' || CHR(10) ||
                 '    V_RESET_COUNT NUMBER(10);' || CHR(10) ||
                 '    V_DELTA       NUMBER(18,4);' || CHR(10) ||
                 'BEGIN' || CHR(10) ||
                 '    IF :NEW.PARAM_NAME <> ''机台状态-实际产出数量'' THEN' || CHR(10) ||
                 '        RETURN;' || CHR(10) ||
                 '    END IF;' || CHR(10) ||
                 '    IF :NEW.DEVICE_CODE LIKE ''OLD-%'' THEN' || CHR(10) ||
                 '        RETURN;' || CHR(10) ||
                 '    END IF;' || CHR(10) ||
                 '    BEGIN' || CHR(10) ||
                 '        V_NEW_VAL := TO_NUMBER(:NEW.PARAM_VALUE);' || CHR(10) ||
                 '    EXCEPTION' || CHR(10) ||
                 '        WHEN OTHERS THEN' || CHR(10) ||
                 '            RETURN;' || CHR(10) ||
                 '    END;' || CHR(10) ||
                 '    V_PROD_DATE := TRUNC(:NEW.COLLECT_TIME);' || CHR(10) ||
                 '    BEGIN' || CHR(10) ||
                 '        SELECT LAST_PARAM_VAL, CURRENT_TOTAL, RESET_COUNT' || CHR(10) ||
                 '          INTO V_LAST_VAL, V_CUR_TOTAL, V_RESET_COUNT' || CHR(10) ||
                 '          FROM RT_DAILY_PROD_STATE' || CHR(10) ||
                 '         WHERE PROD_DATE = V_PROD_DATE' || CHR(10) ||
                 '           AND DEVICE_CODE = :NEW.DEVICE_CODE' || CHR(10) ||
                 '           AND PARAM_NAME = :NEW.PARAM_NAME' || CHR(10) ||
                 '         FOR UPDATE;' || CHR(10) ||
                 '        IF V_NEW_VAL > V_LAST_VAL THEN' || CHR(10) ||
                 '            V_DELTA := V_NEW_VAL - V_LAST_VAL;' || CHR(10) ||
                 '        ELSIF V_NEW_VAL < V_LAST_VAL THEN' || CHR(10) ||
                 '            V_DELTA := V_NEW_VAL;' || CHR(10) ||
                 '            V_RESET_COUNT := NVL(V_RESET_COUNT, 0) + 1;' || CHR(10) ||
                 '        ELSE' || CHR(10) ||
                 '            V_DELTA := 0;' || CHR(10) ||
                 '        END IF;' || CHR(10) ||
                 '        UPDATE RT_DAILY_PROD_STATE' || CHR(10) ||
                 '           SET LAST_PARAM_VAL    = V_NEW_VAL,' || CHR(10) ||
                 '               CURRENT_TOTAL     = NVL(V_CUR_TOTAL, 0) + NVL(V_DELTA, 0),' || CHR(10) ||
                 '               RESET_COUNT       = NVL(V_RESET_COUNT, 0),' || CHR(10) ||
                 '               LAST_COLLECT_TIME = :NEW.COLLECT_TIME,' || CHR(10) ||
                 '               UPDATE_TIME       = SYSDATE' || CHR(10) ||
                 '         WHERE PROD_DATE = V_PROD_DATE' || CHR(10) ||
                 '           AND DEVICE_CODE = :NEW.DEVICE_CODE' || CHR(10) ||
                 '           AND PARAM_NAME = :NEW.PARAM_NAME;' || CHR(10) ||
                 '    EXCEPTION' || CHR(10) ||
                 '        WHEN NO_DATA_FOUND THEN' || CHR(10) ||
                 '            INSERT INTO RT_DAILY_PROD_STATE (' || CHR(10) ||
                 '                PROD_DATE, DEVICE_CODE, PARAM_NAME, LAST_PARAM_VAL,' || CHR(10) ||
                 '                CURRENT_TOTAL, RESET_COUNT, DIRTY_FLAG, LAST_COLLECT_TIME, UPDATE_TIME' || CHR(10) ||
                 '            ) VALUES (' || CHR(10) ||
                 '                V_PROD_DATE, :NEW.DEVICE_CODE, :NEW.PARAM_NAME, V_NEW_VAL,' || CHR(10) ||
                 '                0, 0, 0, :NEW.COLLECT_TIME, SYSDATE' || CHR(10) ||
                 '            );' || CHR(10) ||
                 '    END;' || CHR(10) ||
                 'END;';

        EXECUTE_DDL(V_SQL);
    END CREATE_OR_REPLACE_TRIGGER;

    PROCEDURE PREPARE_MONTH(
        P_SUFFIX IN VARCHAR2
    ) IS
    BEGIN
        VALIDATE_SUFFIX(P_SUFFIX);
        ENSURE_MONTH_TABLE(P_SUFFIX);
        CREATE_OR_REPLACE_TRIGGER(P_SUFFIX);
    END PREPARE_MONTH;

    PROCEDURE AUTO_PREPARE_NEXT_MONTH IS
        V_NEXT_SUFFIX VARCHAR2(6);
    BEGIN
        -- 为什么取“下个月月初再格式化”：
        -- 这样语义上明确是“目标月份后缀”，不会受当前日号影响。
        V_NEXT_SUFFIX := TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1), 'YYYYMM');
        PREPARE_MONTH(V_NEXT_SUFFIX);
    END AUTO_PREPARE_NEXT_MONTH;

END PKG_DEVICE_PARAM_MONTHLY;
/
