/*
Phase 10.2 — Executive KPI Summary Layer
Run this script once in each hospital Oracle schema used by the dashboard, usually WATANY.
It creates a small key-value summary table read by the Executive Command Center home page.
*/

CREATE TABLE DASH_EXEC_SUMMARY_KPI (
    HOSPITAL_CODE VARCHAR2(20) NOT NULL,
    SUMMARY_DATE  DATE NOT NULL,
    KPI_CODE      VARCHAR2(80) NOT NULL,
    KPI_VALUE     NUMBER,
    KPI_TEXT      VARCHAR2(4000),
    KPI_STATUS    VARCHAR2(20) DEFAULT 'ok' NOT NULL,
    SOURCE_NAME   VARCHAR2(120),
    UPDATED_AT    DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT PK_DASH_EXEC_SUMMARY_KPI PRIMARY KEY (HOSPITAL_CODE, SUMMARY_DATE, KPI_CODE)
);

CREATE INDEX IDX_DASH_EXEC_SUMMARY_UPD
    ON DASH_EXEC_SUMMARY_KPI (HOSPITAL_CODE, UPDATED_AT);

COMMENT ON TABLE DASH_EXEC_SUMMARY_KPI IS 'Fast executive dashboard KPI cache. Refreshed by PHP scheduled job.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.HOSPITAL_CODE IS 'Branch/hospital code such as NJCH, JCKH, MNH.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.SUMMARY_DATE IS 'The KPI date, normally TRUNC(SYSDATE).';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.KPI_CODE IS 'KPI key, e.g. opd, admissions, today_total, active_doctors.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.KPI_VALUE IS 'Numeric KPI value.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.KPI_TEXT IS 'Optional label/value text, e.g. top doctor name.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.KPI_STATUS IS 'ok, pending, warning, error.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.SOURCE_NAME IS 'Original source table/module.';
COMMENT ON COLUMN DASH_EXEC_SUMMARY_KPI.UPDATED_AT IS 'Refresh timestamp.';

/*
If the table already exists, do not run CREATE TABLE again.
To reset safely in a test environment only:
DROP TABLE DASH_EXEC_SUMMARY_KPI PURGE;
*/
