-- ============================================================
-- TIMESHEET_NOTIFICATION_LOG
-- In-app notification log for timesheet module.
-- Follows the same pattern as TTM_NOTIFICATION_LOG.
--
-- Run this SQL against the Oremus database to create the table.
-- ============================================================

CREATE TABLE IF NOT EXISTS TIMESHEET_NOTIFICATION_LOG (
  ID                    BIGINT        NOT NULL AUTO_INCREMENT,
  NOTIFICATION_TYPE     ENUM(
                          'TIMESHEET_SUBMITTED',
                          'TIMESHEET_APPROVED',
                          'TIMESHEET_REJECTED'
                        )             NOT NULL,
  ENTITY_TYPE           VARCHAR(50)   NOT NULL DEFAULT 'TIMESHEET_SUBMISSION',
  ENTITY_REF_ID         BIGINT        NOT NULL DEFAULT 0
                        COMMENT 'ID of the timesheet_submissions record',
  RECIPIENT_EMP_REF_ID  BIGINT        NOT NULL
                        COMMENT 'users.ID of the recipient (RM or employee)',
  SENDER_EMP_REF_ID     BIGINT        NOT NULL DEFAULT 0
                        COMMENT 'users.ID of the person who triggered this',
  CHANNEL               ENUM('EMAIL','IN_APP','SMS')
                        NOT NULL DEFAULT 'IN_APP',
  SUBJECT               VARCHAR(500)  DEFAULT NULL,
  MESSAGE               TEXT          DEFAULT NULL,
  IS_READ               TINYINT(1)    NOT NULL DEFAULT 0,
  READ_AT               DATETIME      DEFAULT NULL,
  STATUS                ENUM('QUEUED','SENT','FAILED')
                        NOT NULL DEFAULT 'SENT',
  SENT_AT               DATETIME      DEFAULT NULL,
  ERROR_MESSAGE         TEXT          DEFAULT NULL,
  CREATED_AT            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (ID),
  INDEX IDX_TS_NOTIF_RECIPIENT (RECIPIENT_EMP_REF_ID, IS_READ),
  INDEX IDX_TS_NOTIF_TYPE      (NOTIFICATION_TYPE),
  INDEX IDX_TS_NOTIF_STATUS    (STATUS)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
