-- DEVICE_DAILY_PRODUCTION：设备日产量权威汇总表
-- 兼容：Oracle 11 / Oracle 19

CREATE TABLE DEVICE_DAILY_PRODUCTION (
    PROD_DATE    DATE            NOT NULL,
    DEVICE_CODE  VARCHAR2(64)    NOT NULL,
    PARAM_NAME   VARCHAR2(128)   NOT NULL,
    DAILY_PROD   NUMBER(18,4)    DEFAULT 0 NOT NULL,
    RESET_COUNT  NUMBER(10)      DEFAULT 0 NOT NULL,
    CALC_TIME    DATE            DEFAULT SYSDATE NOT NULL
);

COMMENT ON TABLE DEVICE_DAILY_PRODUCTION IS '设备日产量权威汇总表';
COMMENT ON COLUMN DEVICE_DAILY_PRODUCTION.PROD_DATE IS '生产日期，按自然日';
COMMENT ON COLUMN DEVICE_DAILY_PRODUCTION.DEVICE_CODE IS '设备编码';
COMMENT ON COLUMN DEVICE_DAILY_PRODUCTION.PARAM_NAME IS '产量参数名称';
COMMENT ON COLUMN DEVICE_DAILY_PRODUCTION.DAILY_PROD IS '该设备该日最终日产量';
COMMENT ON COLUMN DEVICE_DAILY_PRODUCTION.RESET_COUNT IS '该设备该日计数器回退次数';
COMMENT ON COLUMN DEVICE_DAILY_PRODUCTION.CALC_TIME IS '汇总计算时间';

ALTER TABLE DEVICE_DAILY_PRODUCTION
    ADD CONSTRAINT PK_DEVICE_DAILY_PRODUCTION
    PRIMARY KEY (PROD_DATE, DEVICE_CODE, PARAM_NAME);

CREATE INDEX IDX_DEVICE_DAILY_PRODUCTION_MONTH
    ON DEVICE_DAILY_PRODUCTION (PROD_DATE, PARAM_NAME);

CREATE INDEX IDX_DEVICE_DAILY_PRODUCTION_DEVICE
    ON DEVICE_DAILY_PRODUCTION (DEVICE_CODE, PROD_DATE);
