ESIM/EsimLao/Database/message_queue_his.sql

-- =============================================
-- MESSAGE_QUEUE_HIS - History table for processed messages
-- This table stores completed (success/failed) messages
-- to keep MESSAGE_QUEUE table light for optimal performance
-- =============================================

CREATE TABLE LAOS_ESIM.MESSAGE_QUEUE_HIS (
    ID NUMBER(10) NOT NULL,
    MESSAGE_TYPE NUMBER(1) DEFAULT 1 NOT NULL,
    RECIPIENT VARCHAR2(500) NOT NULL,
    SUBJECT NVARCHAR2(500),
    CONTENT NCLOB,
    TEMPLATE_CODE VARCHAR2(100),
    TEMPLATE_DATA CLOB,
    PRIORITY NUMBER(1) DEFAULT 0,
    STATUS NUMBER(1) DEFAULT 0 NOT NULL,
    SCHEDULED_AT TIMESTAMP,
    PROCESSED_AT TIMESTAMP,
    RETRY_COUNT NUMBER(3) DEFAULT 0,
    MAX_RETRY NUMBER(3) DEFAULT 3,
    ERROR_MESSAGE VARCHAR2(2000),
    CREATED_BY NUMBER(10),
    CREATED_DATE TIMESTAMP DEFAULT SYSTIMESTAMP,
    MOVED_DATE TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT MESSAGE_QUEUE_HIS_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.MESSAGE_QUEUE_HIS IS 'History table for processed messages (success/failed)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.ID IS 'Original ID from MESSAGE_QUEUE';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.MESSAGE_TYPE IS 'Message type: 1=Email, 2=SMS, 3=Push';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.RECIPIENT IS 'Recipient address (email/phone)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.SUBJECT IS 'Email subject';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.CONTENT IS 'Message content';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.TEMPLATE_CODE IS 'Template code reference';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.TEMPLATE_DATA IS 'Template parameters (JSON)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.PRIORITY IS 'Priority: 1=High, 0=Normal';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.STATUS IS 'Final status: 2=Success, 3=Failed';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.SCHEDULED_AT IS 'Scheduled send time';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.PROCESSED_AT IS 'Actual processing time';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.RETRY_COUNT IS 'Number of retries attempted';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.MAX_RETRY IS 'Maximum retries allowed';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.ERROR_MESSAGE IS 'Error message if failed';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.CREATED_BY IS 'User who created the message';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.CREATED_DATE IS 'Original creation date';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE_HIS.MOVED_DATE IS 'Date moved to history';

-- Indexes for reporting and cleanup
CREATE INDEX LAOS_ESIM.MESSAGE_QUEUE_HIS_DATE_IDX ON LAOS_ESIM.MESSAGE_QUEUE_HIS(MOVED_DATE);
CREATE INDEX LAOS_ESIM.MESSAGE_QUEUE_HIS_STATUS_IDX ON LAOS_ESIM.MESSAGE_QUEUE_HIS(STATUS, MOVED_DATE);
CREATE INDEX LAOS_ESIM.MESSAGE_QUEUE_HIS_RECIPIENT_IDX ON LAOS_ESIM.MESSAGE_QUEUE_HIS(RECIPIENT, MOVED_DATE);

-- Optional: Partition by MOVED_DATE for large volume (millions of records)
-- ALTER TABLE LAOS_ESIM.MESSAGE_QUEUE_HIS MODIFY
-- PARTITION BY RANGE (MOVED_DATE)
-- INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
-- (PARTITION p_init VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')));

-- Cleanup job (run monthly to delete old history)
-- DELETE FROM LAOS_ESIM.MESSAGE_QUEUE_HIS WHERE MOVED_DATE < ADD_MONTHS(SYSDATE, -6);