-- 订单通知日志表
-- 用于防止定时任务重复发送相同通知
-- notifyType: reminder=提前提醒, overdue=超期未上传报警, upload_fail=上传失败
CREATE TABLE [dbo].[order_notification_log](
    [id]         [int] IDENTITY(1,1) NOT NULL,
    [orderId]    [int] NOT NULL,
    [notifyType] [varchar](20) NOT NULL,
    [notifyDate] [date] NOT NULL,
    [createTime] [datetime] NOT NULL DEFAULT GETDATE(),
    CONSTRAINT [PK_order_notification_log] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];

-- 唯一索引：同一订单同一类型同一天只允许一条记录
CREATE UNIQUE INDEX [UX_order_notification_log]
    ON [dbo].[order_notification_log] ([orderId], [notifyType], [notifyDate]);
GO
