-- R45-P3.1 Oracle 30-character identifier compatibility repair.
-- Existing DASH_REVENUE_* tables remain unchanged.

CREATE OR REPLACE PROCEDURE DASH_REFRESH_REV_MONTH_SUM (
    P_BRANCH_ID IN NUMBER,
    P_FROM_DATE IN DATE,
    P_TO_DATE IN DATE,
    P_MOH_CUSTOMER_CODE IN VARCHAR2 DEFAULT '365'
) AS
    V_RUN_ID NUMBER;
    V_FROM_MONTH DATE;
    V_TO_MONTH_EXCLUSIVE DATE;
    V_SOURCE_ROW_COUNT NUMBER := 0;
    V_SUMMARY_ROW_COUNT NUMBER := 0;
    V_SUMMARY_MONTH_COUNT NUMBER := 0;
    V_SOURCE_TOTAL NUMBER := 0;
    V_SUMMARY_TOTAL NUMBER := 0;
    V_SOURCE_CASH NUMBER := 0;
    V_SUMMARY_CASH NUMBER := 0;
    V_SOURCE_INSURANCE NUMBER := 0;
    V_SUMMARY_INSURANCE NUMBER := 0;
    V_SOURCE_MOH NUMBER := 0;
    V_SUMMARY_MOH NUMBER := 0;
BEGIN
    IF P_FROM_DATE IS NULL OR P_TO_DATE IS NULL OR P_FROM_DATE > P_TO_DATE THEN
        RAISE_APPLICATION_ERROR(-20031, 'Invalid Revenue Summary date range.');
    END IF;

    V_FROM_MONTH := TRUNC(P_FROM_DATE, 'MONTH');
    V_TO_MONTH_EXCLUSIVE := ADD_MONTHS(TRUNC(P_TO_DATE, 'MONTH'), 1);
    SELECT DASH_REVENUE_SUMMARY_RUN_SEQ.NEXTVAL INTO V_RUN_ID FROM DUAL;

    INSERT INTO DASH_REVENUE_SUMMARY_RUN (
        RUN_ID, BRANCH_ID, FROM_DATE, TO_DATE, MOH_CUSTOMER_CODE, STATUS, STARTED_AT
    ) VALUES (
        V_RUN_ID, P_BRANCH_ID, V_FROM_MONTH, V_TO_MONTH_EXCLUSIVE - 1,
        P_MOH_CUSTOMER_CODE, 'RUNNING', SYSTIMESTAMP
    );
    COMMIT;

    DELETE FROM DASH_REVENUE_MONTHLY_STAGE WHERE RUN_ID = V_RUN_ID;

    INSERT INTO DASH_REVENUE_MONTHLY_STAGE (
        RUN_ID, BRANCH_ID, YEAR_ID, MONTH_ID,
        TOTAL_REVENUE, CASH_REVENUE, INSURANCE_REVENUE, MOH_REVENUE,
        SOURCE_ROW_COUNT, SOURCE_MAX_TRANSACTION_DATE
    )
    SELECT
        V_RUN_ID,
        P_BRANCH_ID,
        EXTRACT(YEAR FROM VISIT_END_DATE),
        EXTRACT(MONTH FROM VISIT_END_DATE),
        SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)),
        SUM(NVL(CASH_VALUE, 0)),
        SUM(NVL(CREDIT_VALUE, 0)),
        SUM(CASE WHEN CUSTOMER_CODE = P_MOH_CUSTOMER_CODE
                 THEN NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)
                 ELSE 0 END),
        COUNT(*),
        MAX(TRANSACTION_DATE)
    FROM T_INP_OUTP_REVENUE_BATCH
    WHERE VISIT_END_DATE >= V_FROM_MONTH
      AND VISIT_END_DATE < V_TO_MONTH_EXCLUSIVE
    GROUP BY
        EXTRACT(YEAR FROM VISIT_END_DATE),
        EXTRACT(MONTH FROM VISIT_END_DATE);

    UPDATE DASH_REVENUE_SUMMARY_RUN
       SET STATUS = 'VALIDATING'
     WHERE RUN_ID = V_RUN_ID;

    SELECT
        COUNT(*),
        NVL(SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)), 0),
        NVL(SUM(NVL(CASH_VALUE, 0)), 0),
        NVL(SUM(NVL(CREDIT_VALUE, 0)), 0),
        NVL(SUM(CASE WHEN CUSTOMER_CODE = P_MOH_CUSTOMER_CODE
                     THEN NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)
                     ELSE 0 END), 0)
    INTO
        V_SOURCE_ROW_COUNT,
        V_SOURCE_TOTAL,
        V_SOURCE_CASH,
        V_SOURCE_INSURANCE,
        V_SOURCE_MOH
    FROM T_INP_OUTP_REVENUE_BATCH
    WHERE VISIT_END_DATE >= V_FROM_MONTH
      AND VISIT_END_DATE < V_TO_MONTH_EXCLUSIVE;

    SELECT
        COUNT(*),
        NVL(SUM(SOURCE_ROW_COUNT), 0),
        NVL(SUM(TOTAL_REVENUE), 0),
        NVL(SUM(CASH_REVENUE), 0),
        NVL(SUM(INSURANCE_REVENUE), 0),
        NVL(SUM(MOH_REVENUE), 0)
    INTO
        V_SUMMARY_MONTH_COUNT,
        V_SUMMARY_ROW_COUNT,
        V_SUMMARY_TOTAL,
        V_SUMMARY_CASH,
        V_SUMMARY_INSURANCE,
        V_SUMMARY_MOH
    FROM DASH_REVENUE_MONTHLY_STAGE
    WHERE RUN_ID = V_RUN_ID;

    SELECT NVL(SUM(SOURCE_ROW_COUNT), 0)
      INTO V_SUMMARY_ROW_COUNT
      FROM DASH_REVENUE_MONTHLY_STAGE
     WHERE RUN_ID = V_RUN_ID;

    IF V_SOURCE_ROW_COUNT <> V_SUMMARY_ROW_COUNT
       OR ABS(V_SOURCE_TOTAL - V_SUMMARY_TOTAL) > 0.01
       OR ABS(V_SOURCE_CASH - V_SUMMARY_CASH) > 0.01
       OR ABS(V_SOURCE_INSURANCE - V_SUMMARY_INSURANCE) > 0.01
       OR ABS(V_SOURCE_MOH - V_SUMMARY_MOH) > 0.01 THEN
        RAISE_APPLICATION_ERROR(-20032, 'Revenue Summary numeric reconciliation failed.');
    END IF;

    DELETE FROM DASH_REVENUE_MONTHLY_SUMMARY
     WHERE BRANCH_ID = P_BRANCH_ID
       AND TO_DATE(YEAR_ID || LPAD(MONTH_ID, 2, '0'), 'YYYYMM') >= V_FROM_MONTH
       AND TO_DATE(YEAR_ID || LPAD(MONTH_ID, 2, '0'), 'YYYYMM') < V_TO_MONTH_EXCLUSIVE;

    INSERT INTO DASH_REVENUE_MONTHLY_SUMMARY (
        BRANCH_ID, YEAR_ID, MONTH_ID,
        TOTAL_REVENUE, CASH_REVENUE, INSURANCE_REVENUE, MOH_REVENUE,
        SOURCE_ROW_COUNT, SOURCE_MAX_TRANSACTION_DATE,
        REFRESH_RUN_ID, REFRESHED_AT
    )
    SELECT
        BRANCH_ID, YEAR_ID, MONTH_ID,
        TOTAL_REVENUE, CASH_REVENUE, INSURANCE_REVENUE, MOH_REVENUE,
        SOURCE_ROW_COUNT, SOURCE_MAX_TRANSACTION_DATE,
        V_RUN_ID, SYSTIMESTAMP
    FROM DASH_REVENUE_MONTHLY_STAGE
    WHERE RUN_ID = V_RUN_ID;

    UPDATE DASH_REVENUE_SUMMARY_RUN
       SET STATUS = 'READY',
           SOURCE_ROW_COUNT = V_SOURCE_ROW_COUNT,
           SUMMARY_ROW_COUNT = V_SUMMARY_ROW_COUNT,
           SOURCE_TOTAL = V_SOURCE_TOTAL,
           SUMMARY_TOTAL = V_SUMMARY_TOTAL,
           SOURCE_CASH = V_SOURCE_CASH,
           SUMMARY_CASH = V_SUMMARY_CASH,
           SOURCE_INSURANCE = V_SOURCE_INSURANCE,
           SUMMARY_INSURANCE = V_SUMMARY_INSURANCE,
           SOURCE_MOH = V_SOURCE_MOH,
           SUMMARY_MOH = V_SUMMARY_MOH,
           FINISHED_AT = SYSTIMESTAMP,
           ERROR_MESSAGE = NULL
     WHERE RUN_ID = V_RUN_ID;

    DELETE FROM DASH_REVENUE_MONTHLY_STAGE
     WHERE RUN_ID <> V_RUN_ID
       AND RUN_ID < V_RUN_ID - 10;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        UPDATE DASH_REVENUE_SUMMARY_RUN
           SET STATUS = 'FAILED',
               FINISHED_AT = SYSTIMESTAMP,
               ERROR_MESSAGE = SUBSTR(SQLERRM, 1, 4000)
         WHERE RUN_ID = V_RUN_ID;
        COMMIT;
        RAISE;
END;
/
