/*
  تحسين أداء Revenue Dashboard
  نفّذ هذا الملف على كل قاعدة بيانات/مستشفى حسب الحاجة.
  بعد التنفيذ غيّر USE_REVENUE_MV=1 في ملف .env.
*/

CREATE MATERIALIZED VIEW MV_REVENUE_MONTHLY
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
    YEARID,
    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')) AS MONTHID,
    CUSTOMER_CODE,
    SUM(NVL(CREDIT_VALUE, 0)) AS CREDIT_AMOUNT,
    SUM(NVL(CASH_VALUE, 0)) AS CASH_AMOUNT,
    SUM(NVL(CREDIT_VALUE, 0) + NVL(CASH_VALUE, 0)) AS TOTAL_AMOUNT,
    COUNT(*) AS ROW_COUNT
FROM T_INP_OUTP_REVENUE_BATCH
GROUP BY
    YEARID,
    TO_NUMBER(TO_CHAR(VISIT_END_DATE, 'MM')),
    CUSTOMER_CODE;

CREATE INDEX IDX_MV_REVENUE_MONTHLY_01
ON MV_REVENUE_MONTHLY (YEARID, MONTHID, CUSTOMER_CODE);

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => USER,
        tabname => 'MV_REVENUE_MONTHLY',
        cascade => TRUE
    );
END;
/

/* Refresh يدوي بعد تحميل الباتش */
BEGIN
    DBMS_MVIEW.REFRESH('MV_REVENUE_MONTHLY', 'C');
END;
/
