IF NOT EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[asr_iot_publish_record]')
      AND type = N'U'
)
BEGIN
    CREATE TABLE [dbo].[asr_iot_publish_record] (
        [id] BIGINT IDENTITY(1,1) NOT NULL,
        [instruction_id] VARCHAR(64) NOT NULL,
        [direction] VARCHAR(16) NOT NULL,
        [message_type] VARCHAR(16) NOT NULL,
        [receive_topic] VARCHAR(255) NULL,
        [publish_topic] VARCHAR(255) NULL,
        [container_id] VARCHAR(128) NULL,
        [reference_id] VARCHAR(128) NULL,
        [source_location_id] VARCHAR(128) NULL,
        [destination_location_ids] VARCHAR(500) NULL,
        [message_creation_time] BIGINT NULL,
        [raw_payload] VARCHAR(MAX) NULL,
        [publish_payload] VARCHAR(MAX) NULL,
        [process_status] VARCHAR(32) NULL,
        [publish_status] VARCHAR(32) NULL,
        [feedback_status] VARCHAR(16) NULL,
        [error_code] VARCHAR(64) NULL,
        [error_message] VARCHAR(500) NULL,
        [ack_payload] VARCHAR(MAX) NULL,
        [ack_time] DATETIME NULL,
        [wrk_no] INT NULL,
        [order_no] VARCHAR(128) NULL,
        [create_time] DATETIME NULL,
        [update_time] DATETIME NULL,
        CONSTRAINT [PK_asr_iot_publish_record] PRIMARY KEY CLUSTERED ([id] ASC),
        CONSTRAINT [UK_asr_iot_publish_record_instruction_id] UNIQUE NONCLUSTERED ([instruction_id] ASC)
    );

    CREATE NONCLUSTERED INDEX [IDX_asr_iot_publish_record_container_type]
        ON [dbo].[asr_iot_publish_record] ([container_id] ASC, [message_type] ASC, [message_creation_time] DESC);

    CREATE NONCLUSTERED INDEX [IDX_asr_iot_publish_record_publish_status]
        ON [dbo].[asr_iot_publish_record] ([publish_status] ASC, [create_time] ASC);

    EXEC sys.sp_addextendedproperty
        @name = N'MS_Description', @value = N'ASRS IoT 发布与回执记录',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE',  @level1name = N'asr_iot_publish_record';
END;
